145 lines
7.4 KiB
PHP
145 lines
7.4 KiB
PHP
<?php
|
|
/**
|
|
* End-to-End Verification Script for Medical Store & Multi-Building Pharmacy System
|
|
*/
|
|
$db = new mysqli('localhost', 'root', '', 'hms_data_ci4');
|
|
if ($db->connect_error) {
|
|
die("Connection failed: " . $db->connect_error . "\n");
|
|
}
|
|
|
|
echo "=== 1. VERIFYING ZERO DISTURBANCE TO LEGACY PHARMACY TABLES ===\n";
|
|
$legacyTables = ['invoice_med_master', 'inv_med_item', 'med_product_master', 'med_supplier', 'med_supplier_ledger'];
|
|
foreach ($legacyTables as $lt) {
|
|
$res = $db->query("SELECT COUNT(*) as cnt FROM `$lt`");
|
|
if ($res) {
|
|
$row = $res->fetch_assoc();
|
|
echo " ✓ Legacy table `$lt` intact (row count: {$row['cnt']})\n";
|
|
} else {
|
|
echo " ! Legacy table `$lt` check failed: " . $db->error . "\n";
|
|
}
|
|
}
|
|
|
|
echo "\n=== 2. VERIFYING MULTI-STORE CONFIGURATION (DL 20B/21B & GSTIN) ===\n";
|
|
$stores = $db->query("SELECT store_id, store_code, store_name, building_name, drug_license_no_20b, drug_license_no_21b, gstin, registered_pharmacist_name FROM `mst_stores`");
|
|
while ($s = $stores->fetch_assoc()) {
|
|
echo " ✓ Store: {$s['store_name']} ({$s['store_code']})\n";
|
|
echo " - Building: {$s['building_name']} | DL 20B: {$s['drug_license_no_20b']} | DL 21B: {$s['drug_license_no_21b']}\n";
|
|
echo " - GSTIN: {$s['gstin']} | Pharmacist: {$s['registered_pharmacist_name']}\n";
|
|
}
|
|
|
|
echo "\n=== 3. SIMULATING FULL POS TRANSACTION LIFECYCLE ===\n";
|
|
// Step 3a: Select patient with UHID
|
|
$ptRes = $db->query("SELECT id, p_code, p_fname, p_lname, mphone1 FROM patient_master LIMIT 1");
|
|
$pt = $ptRes->fetch_assoc();
|
|
echo " - Patient selected: {$pt['p_fname']} {$pt['p_lname']} (UHID: {$pt['p_code']}, Mob: {$pt['mphone1']})\n";
|
|
|
|
// Step 3b: Select Augmentin 625 Duo (Schedule H1) with FEFO batch
|
|
$batchRes = $db->query("
|
|
SELECT b.batch_id, b.batch_no, b.expiry_date, b.mrp, b.ptr, b.gst_rate, s.current_qty, i.item_id, i.item_name, i.drug_schedule, i.hsn_code
|
|
FROM mst_batches b
|
|
JOIN mst_stock s ON s.batch_id = b.batch_id AND s.store_id = 1
|
|
JOIN mst_items i ON i.item_id = b.item_id
|
|
WHERE i.item_id = 1 AND s.current_qty > 0
|
|
ORDER BY b.expiry_date ASC
|
|
LIMIT 1
|
|
");
|
|
$batch = $batchRes->fetch_assoc();
|
|
echo " - FEFO Batch selected: {$batch['item_name']} | Batch: {$batch['batch_no']} | Exp: {$batch['expiry_date']} | Stock Before: {$batch['current_qty']}\n";
|
|
|
|
$dispenseQty = 2;
|
|
$mrp = (float)$batch['mrp'];
|
|
$lineTotal = $dispenseQty * $mrp;
|
|
$gstRate = (float)$batch['gst_rate'];
|
|
$taxable = round($lineTotal / (1 + ($gstRate / 100)), 2);
|
|
$tax = round($lineTotal - $taxable, 2);
|
|
$cgst = round($tax / 2, 2);
|
|
$sgst = round($tax - $cgst, 2);
|
|
|
|
// Step 3c: Insert Sale Invoice
|
|
$invoiceNo = 'MAIN/26-27/' . strtoupper(substr(uniqid(), -5));
|
|
$db->query("
|
|
INSERT INTO `mst_sales` (
|
|
`store_id`, `invoice_no`, `sale_date`, `patient_type`, `uhid`, `patient_id`, `patient_name`,
|
|
`doctor_name`, `doctor_reg_no`, `gross_amount`, `discount_amount`, `taxable_amount`,
|
|
`cgst_amount`, `sgst_amount`, `igst_amount`, `round_off`, `net_amount`, `payment_mode`,
|
|
`cash_paid`, `schedule_h1_flag`, `status`
|
|
) VALUES (
|
|
1, '$invoiceNo', NOW(), 'OPD', '{$pt['p_code']}', {$pt['id']}, '{$pt['p_fname']} {$pt['p_lname']}',
|
|
'Dr. Rajesh Verma', 'MCI-12994', $lineTotal, 0, $taxable,
|
|
$cgst, $sgst, 0, 0, $lineTotal, 'Cash',
|
|
$lineTotal, 1, 'completed'
|
|
)
|
|
");
|
|
$saleId = $db->insert_id;
|
|
|
|
// Insert Sale Item
|
|
$db->query("
|
|
INSERT INTO `mst_sales_items` (
|
|
`sale_id`, `item_id`, `batch_id`, `batch_no`, `expiry_date`, `qty`,
|
|
`unit_mrp`, `discount_pct`, `hsn_code`, `gst_rate`, `taxable_value`,
|
|
`cgst_amount`, `sgst_amount`, `igst_amount`, `total_amount`
|
|
) VALUES (
|
|
$saleId, {$batch['item_id']}, {$batch['batch_id']}, '{$batch['batch_no']}', '{$batch['expiry_date']}', $dispenseQty,
|
|
$mrp, 0, '{$batch['hsn_code']}', $gstRate, $taxable,
|
|
$cgst, $sgst, 0, $lineTotal
|
|
)
|
|
");
|
|
|
|
// Decrement stock
|
|
$db->query("UPDATE `mst_stock` SET `current_qty` = `current_qty` - $dispenseQty WHERE `batch_id` = {$batch['batch_id']} AND `store_id` = 1");
|
|
|
|
// Check new stock
|
|
$newStockRes = $db->query("SELECT current_qty FROM `mst_stock` WHERE `batch_id` = {$batch['batch_id']} AND `store_id` = 1");
|
|
$newStock = $newStockRes->fetch_assoc()['current_qty'];
|
|
echo " ✓ Stock decremented from {$batch['current_qty']} to $newStock (Sold $dispenseQty units)\n";
|
|
|
|
// Step 3d: Insert Double-Entry Ledgers
|
|
$cashHead = $db->query("SELECT head_id FROM `mst_account_heads` WHERE `head_code` = '1001'")->fetch_assoc()['head_id'];
|
|
$salesHead = $db->query("SELECT head_id FROM `mst_account_heads` WHERE `head_code` = '3001'")->fetch_assoc()['head_id'];
|
|
$cgstHead = $db->query("SELECT head_id FROM `mst_account_heads` WHERE `head_code` = '2002'")->fetch_assoc()['head_id'];
|
|
$sgstHead = $db->query("SELECT head_id FROM `mst_account_heads` WHERE `head_code` = '2003'")->fetch_assoc()['head_id'];
|
|
|
|
// Dr Cash
|
|
$db->query("INSERT INTO `mst_ledger_entries` (`store_id`, `voucher_no`, `voucher_type`, `voucher_date`, `account_head_id`, `debit_amount`, `credit_amount`, `narration`) VALUES (1, '$invoiceNo', 'SALE', CURDATE(), $cashHead, $lineTotal, 0, 'Cash received for bill $invoiceNo')");
|
|
// Cr Sales
|
|
$db->query("INSERT INTO `mst_ledger_entries` (`store_id`, `voucher_no`, `voucher_type`, `voucher_date`, `account_head_id`, `debit_amount`, `credit_amount`, `narration`) VALUES (1, '$invoiceNo', 'SALE', CURDATE(), $salesHead, 0, $taxable, 'Pharmacy Sales $invoiceNo')");
|
|
// Cr CGST & SGST
|
|
$db->query("INSERT INTO `mst_ledger_entries` (`store_id`, `voucher_no`, `voucher_type`, `voucher_date`, `account_head_id`, `debit_amount`, `credit_amount`, `narration`) VALUES (1, '$invoiceNo', 'SALE', CURDATE(), $cgstHead, 0, $cgst, 'Output CGST on $invoiceNo')");
|
|
$db->query("INSERT INTO `mst_ledger_entries` (`store_id`, `voucher_no`, `voucher_type`, `voucher_date`, `account_head_id`, `debit_amount`, `credit_amount`, `narration`) VALUES (1, '$invoiceNo', 'SALE', CURDATE(), $sgstHead, 0, $sgst, 'Output SGST on $invoiceNo')");
|
|
|
|
echo " ✓ Double-Entry Ledgers posted: Debit Cash ₹$lineTotal = Credit (Sales ₹$taxable + CGST ₹$cgst + SGST ₹$sgst)\n";
|
|
|
|
echo "\n=== 4. VERIFYING STATUTORY SCHEDULE H1 REGISTER ===\n";
|
|
$h1Query = $db->query("
|
|
SELECT s.sale_date, s.invoice_no, s.patient_name, s.doctor_name, s.doctor_reg_no, i.item_name, si.batch_no, si.qty
|
|
FROM mst_sales_items si
|
|
JOIN mst_sales s ON s.sale_id = si.sale_id
|
|
JOIN mst_items i ON i.item_id = si.item_id
|
|
WHERE i.drug_schedule = 'Schedule H1'
|
|
");
|
|
while ($h1 = $h1Query->fetch_assoc()) {
|
|
echo " ✓ Schedule H1 Entry: {$h1['item_name']} | Batch: {$h1['batch_no']} | Qty: {$h1['qty']} | Patient: {$h1['patient_name']} | Doctor: {$h1['doctor_name']} (Reg: {$h1['doctor_reg_no']})\n";
|
|
}
|
|
|
|
echo "\n=== 5. VERIFYING DAYBOOK RECONCILIATION ===\n";
|
|
$daybook = $db->query("
|
|
SELECT
|
|
COUNT(*) as bill_count,
|
|
SUM(net_amount) as total_sales,
|
|
SUM(cash_paid) as cash_collected,
|
|
SUM(cgst_amount) as cgst,
|
|
SUM(sgst_amount) as sgst
|
|
FROM mst_sales
|
|
WHERE store_id = 1 AND DATE(sale_date) = CURDATE()
|
|
")->fetch_assoc();
|
|
|
|
echo " ✓ Daybook for today:\n";
|
|
echo " - Invoices: {$daybook['bill_count']}\n";
|
|
echo " - Total Sales: ₹{$daybook['total_sales']}\n";
|
|
echo " - Cash Collected: ₹{$daybook['cash_collected']}\n";
|
|
echo " - Output CGST: ₹{$daybook['cgst']}\n";
|
|
echo " - Output SGST: ₹{$daybook['sgst']}\n";
|
|
|
|
$db->close();
|
|
echo "\n=== ALL VERIFICATIONS COMPLETED SUCCESSFULLY ===\n";
|