JezK
Edit File: manual_migration.php
<?php /** * Manual Database Migration Script for Sellers Passbook * This script will run the migration step by step with better error handling */ require_once 'config/database.php'; echo "<h2>PAYMIND Manual Migration - Sellers Passbook</h2>"; echo "<p>Running migration step by step...</p>"; $steps = [ "Step 1: Add new columns to sellers table" => " ALTER TABLE sellers ADD COLUMN gst_number VARCHAR(15) AFTER phone, ADD COLUMN total_payment DECIMAL(12,2) DEFAULT 0.00 AFTER gst_number, ADD COLUMN received_amount DECIMAL(12,2) DEFAULT 0.00 AFTER total_payment, ADD COLUMN pending_amount DECIMAL(12,2) DEFAULT 0.00 AFTER received_amount, ADD COLUMN due_date DATE AFTER pending_amount, ADD COLUMN last_payment_date DATE AFTER due_date, ADD COLUMN payment_notes TEXT AFTER last_payment_date ", "Step 2: Update contact data" => " UPDATE sellers SET phone = COALESCE(contact_number, phone) WHERE phone IS NULL OR phone = '' ", "Step 3: Remove old columns" => " ALTER TABLE sellers DROP COLUMN employee_id, DROP COLUMN email, DROP COLUMN commission_percentage, DROP COLUMN status ", "Step 4: Handle contact_number column" => " ALTER TABLE sellers DROP COLUMN contact_number ", "Step 5: Rename phone to contact_number" => " ALTER TABLE sellers CHANGE COLUMN phone contact_number VARCHAR(20) ", "Step 6: Add indexes" => " CREATE INDEX idx_sellers_gst_number ON sellers(gst_number); CREATE INDEX idx_sellers_pending_amount ON sellers(pending_amount); CREATE INDEX idx_sellers_due_date ON sellers(due_date) ", "Step 7: Create seller_payments table" => " CREATE TABLE seller_payments ( id INT AUTO_INCREMENT PRIMARY KEY, seller_id INT NOT NULL, payment_type ENUM('credit', 'debit') NOT NULL, amount DECIMAL(12,2) NOT NULL, payment_date DATE NOT NULL, payment_mode ENUM('cash', 'upi', 'bank_transfer', 'cheque', 'card', 'other') DEFAULT 'cash', reference_number VARCHAR(100), description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (seller_id) REFERENCES sellers(id) ON DELETE CASCADE ) ", "Step 8: Create seller_payments indexes" => " CREATE INDEX idx_seller_payments_seller_id ON seller_payments(seller_id); CREATE INDEX idx_seller_payments_payment_date ON seller_payments(payment_date); CREATE INDEX idx_seller_payments_payment_type ON seller_payments(payment_type) ", "Step 9: Create seller_passbook view" => " CREATE VIEW seller_passbook AS SELECT s.id, s.name, s.contact_number, s.gst_number, s.address, s.total_payment, s.received_amount, s.pending_amount, s.due_date, s.last_payment_date, CASE WHEN s.pending_amount > 0 AND s.due_date < CURDATE() THEN 'Overdue' WHEN s.pending_amount > 0 AND s.due_date >= CURDATE() THEN 'Pending' WHEN s.pending_amount = 0 THEN 'Paid' ELSE 'Unknown' END as payment_status FROM sellers s ", "Step 10: Create seller_payment_history view" => " CREATE VIEW seller_payment_history AS SELECT sp.id, sp.seller_id, s.name as seller_name, sp.payment_type, sp.amount, sp.payment_date, sp.payment_mode, sp.reference_number, sp.description, sp.created_at FROM seller_payments sp JOIN sellers s ON sp.seller_id = s.id ORDER BY sp.payment_date DESC, sp.created_at DESC " ]; $successCount = 0; $errorCount = 0; foreach ($steps as $stepName => $sql) { echo "<h3>$stepName</h3>"; // Split multiple SQL commands $commands = array_filter(array_map('trim', explode(';', $sql))); foreach ($commands as $command) { if (empty($command)) continue; try { if ($conn->query($command)) { echo "<p style='color: green;'>✓ Success: " . substr($command, 0, 80) . "...</p>"; $successCount++; } else { $error = $conn->error; if (strpos($error, "doesn't exist") !== false || strpos($error, "Duplicate column name") !== false || strpos($error, "already exists") !== false) { echo "<p style='color: orange;'>⚠ Skipped (already exists): " . substr($command, 0, 80) . "...</p>"; echo "<p style='color: orange;'>Reason: $error</p>"; $successCount++; // Count as success } else { throw new Exception($error); } } } catch (Exception $e) { $error = $e->getMessage(); if (strpos($error, "doesn't exist") !== false || strpos($error, "Duplicate column name") !== false || strpos($error, "already exists") !== false) { echo "<p style='color: orange;'>⚠ Skipped (already exists): " . substr($command, 0, 80) . "...</p>"; echo "<p style='color: orange;'>Reason: $error</p>"; $successCount++; // Count as success } else { $errorCount++; echo "<p style='color: red;'>✗ Error: $error</p>"; echo "<p style='color: red;'>Command: " . substr($command, 0, 100) . "...</p>"; } } } echo "<hr>"; } echo "<h3>Migration Summary:</h3>"; echo "<p><strong>Successful operations:</strong> $successCount</p>"; echo "<p><strong>Failed operations:</strong> $errorCount</p>"; if ($errorCount === 0) { echo "<p style='color: green; font-weight: bold;'>🎉 Migration completed successfully!</p>"; echo "<p>You can now use the sellers.php page with full passbook functionality.</p>"; } else { echo "<p style='color: orange; font-weight: bold;'>⚠️ Migration completed with some issues.</p>"; echo "<p>Most functionality should work, but please review any errors above.</p>"; } echo "<hr>"; echo "<p><a href='sellers.php' class='btn btn-primary'>Go to Sellers Page</a> | <a href='index.php' class='btn btn-secondary'>Go to Dashboard</a></p>"; ?>