Enhanced PHP File Manager

⬆️ Go Up (..)

📊 Server Information

Server:

LiteSpeed

System:

Linux 5.14.0-503.34.1.el9_5.x86_64

PHP Version:

8.2.30

User:

u329947844

Directory:

/home/u329947844/domains/hifi11.in/public_html/medical/admin

Free Space:

10.27 TB

🔧 Quick Actions

✏️ Editing: export-narcotic.php

<?php
include('includes/db_connection.php');
session_start();

// Set headers for Excel file download
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="narcotic-register-' . date('Y-m-d-His') . '.xls"');
header('Cache-Control: max-age=0');
header('Pragma: public');

// Define Narcotic drugs list
$narcotic_drugs = [
    'Morphine', 'Pethidine', 'Fentanyl', 'Methadone', 'Buprenorphine', 'Codeine',
    'Oxycodone', 'Hydrocodone', 'Tramadol', 'Tapentadol',
    'Alprazolam', 'Clonazepam', 'Diazepam', 'Lorazepam', 'Nitrazepam', 'Zolpidem',
    'Zopiclone', 'Methylphenidate', 'Ketamine', 'Phenobarbitone',
    'Pseudoephedrine', 'Ephedrine', 'Dextromethorphan'
];

// Get filter parameters from POST or GET
$start_date = isset($_REQUEST['start_date']) ? $_REQUEST['start_date'] : date('Y-m-d', strtotime('-30 days'));
$end_date = isset($_REQUEST['end_date']) ? $_REQUEST['end_date'] : date('Y-m-d');
$doctor_name = isset($_REQUEST['doctor_name']) ? trim($_REQUEST['doctor_name']) : '';
$patient_name = isset($_REQUEST['patient_name']) ? trim($_REQUEST['patient_name']) : '';
$medicine_name = isset($_REQUEST['medicine_name']) ? trim($_REQUEST['medicine_name']) : '';
$bill_number = isset($_REQUEST['bill_number']) ? trim($_REQUEST['bill_number']) : '';

// Build WHERE clause
$where_conditions = ["s.status = 'completed'"];
$params = [];
$param_types = '';

// Date range
if (!empty($start_date) && !empty($end_date)) {
    $where_conditions[] = "DATE(s.sale_date) BETWEEN ? AND ?";
    $params[] = $start_date;
    $params[] = $end_date;
    $param_types .= 'ss';
}

// Doctor name
if (!empty($doctor_name)) {
    $where_conditions[] = "pd.doctor_name LIKE ?";
    $params[] = "%$doctor_name%";
    $param_types .= 's';
}

// Patient name
if (!empty($patient_name)) {
    $where_conditions[] = "(s.customer_name LIKE ? OR pd.patient_name LIKE ?)";
    $params[] = "%$patient_name%";
    $params[] = "%$patient_name%";
    $param_types .= 'ss';
}

// Medicine name
if (!empty($medicine_name)) {
    $where_conditions[] = "m.medicine_name LIKE ?";
    $params[] = "%$medicine_name%";
    $param_types .= 's';
}

// Bill number
if (!empty($bill_number)) {
    $where_conditions[] = "s.id LIKE ?";
    $params[] = "%" . str_replace('INV-', '', $bill_number) . "%";
    $param_types .= 's';
}

// Narcotic drugs filter
$narcotic_list = "'" . implode("','", array_map(function($drug) use ($conn) {
    return $conn->real_escape_string($drug);
}, $narcotic_drugs)) . "'";

$where_conditions[] = "m.medicine_name IN ($narcotic_list)";
$where_conditions[] = "pd.id IS NOT NULL";

$where_clause = implode(" AND ", $where_conditions);

// Get shop info
$shop_info = [];
$shop_query = "SELECT * FROM billing_settings WHERE setting_key IN ('shop_name', 'shop_address', 'shop_contact', 'shop_gst')";
$shop_result = $conn->query($shop_query);
while ($row = $shop_result->fetch_assoc()) {
    $shop_info[$row['setting_key']] = $row['setting_value'];
}

// Main query
$query = "
    SELECT 
        s.id as sale_id,
        DATE(s.sale_date) as sale_date,
        TIME(s.sale_date) as sale_time,
        s.customer_name,
        s.customer_phone,
        m.medicine_name,
        m.generic_name,
        m.batch_number,
        DATE_FORMAT(m.expiry_date, '%b %Y') as expiry_date,
        m.manufacturer,
        si.quantity,
        si.unit_price,
        ROUND(si.quantity * si.unit_price, 2) as item_total,
        pd.doctor_name,
        pd.doctor_license,
        pd.patient_name,
        DATE(pd.prescription_date) as prescription_date,
        CASE WHEN pd.doctor_signature IS NOT NULL AND pd.doctor_signature != '' THEN 'SIGNED' ELSE 'PENDING' END as doctor_signature_status,
        CASE WHEN pd.pharmacist_signature IS NOT NULL AND pd.pharmacist_signature != '' THEN pd.pharmacist_signature ELSE 'PENDING' END as pharmacist_signature,
        CASE WHEN pd.patient_signature IS NOT NULL AND pd.patient_signature != '' THEN 'SIGNED' ELSE 'PENDING' END as patient_signature_status,
        pd.witness_name,
        DATE(pd.signature_date) as signature_date,
        pd.narcotic_verified,
        c.customer_address,
        mc.category_name
    FROM sales s
    JOIN sale_items si ON s.id = si.sale_id
    JOIN medicines m ON si.medicine_id = m.id
    LEFT JOIN prescription_details pd ON s.id = pd.sale_id AND m.id = pd.medicine_id
    LEFT JOIN customers c ON s.customer_phone = c.customer_phone
    LEFT JOIN medicine_categories mc ON m.category_id = mc.id
    WHERE $where_clause
    ORDER BY s.sale_date DESC, s.id DESC
";

// Execute query
if (!empty($params)) {
    $stmt = $conn->prepare($query);
    $stmt->bind_param($param_types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $conn->query($query);
}

// Get statistics
$stats_query = "
    SELECT 
        COUNT(DISTINCT s.id) as total_transactions,
        COUNT(DISTINCT s.customer_name) as total_patients,
        COUNT(DISTINCT pd.doctor_name) as total_doctors,
        SUM(si.quantity) as total_quantity,
        SUM(si.quantity * si.unit_price) as total_value,
        MIN(s.sale_date) as first_sale,
        MAX(s.sale_date) as last_sale,
        COUNT(DISTINCT m.medicine_name) as unique_drugs
    FROM sales s
    JOIN sale_items si ON s.id = si.sale_id
    JOIN medicines m ON si.medicine_id = m.id
    LEFT JOIN prescription_details pd ON s.id = pd.sale_id AND m.id = pd.medicine_id
    WHERE $where_clause
";

if (!empty($params)) {
    $stats_stmt = $conn->prepare($stats_query);
    $stats_stmt->bind_param($param_types, ...$params);
    $stats_stmt->execute();
    $stats_result = $stats_stmt->get_result();
} else {
    $stats_result = $conn->query($stats_query);
}

$stats = $stats_result ? $stats_result->fetch_assoc() : [];

// Start Excel output
echo '<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Narcotic Drugs Register Export</title>
    <style>
        body { font-family: Arial, sans-serif; font-size: 11px; }
        .header { font-size: 16px; font-weight: bold; text-align: center; color: #d00; }
        .subheader { font-size: 12px; text-align: center; margin-bottom: 15px; }
        .info { font-size: 10px; margin-bottom: 10px; text-align: center; }
        .legal-notice { font-size: 9px; color: #d00; font-weight: bold; text-align: center; margin: 10px 0; padding: 5px; background-color: #ffcccc; }
        table { width: 100%; border-collapse: collapse; font-size: 9px; }
        th { background-color: #f2f2f2; border: 1px solid #000; padding: 4px; text-align: left; font-weight: bold; }
        td { border: 1px solid #000; padding: 4px; }
        .total-row { font-weight: bold; background-color: #e8e8e8; }
        .signature-section { margin-top: 30px; padding-top: 20px; border-top: 2px solid #000; }
        .signature-box { display: inline-block; width: 45%; margin: 10px 2%; vertical-align: top; }
        .text-center { text-align: center; }
        .text-right { text-align: right; }
        .bg-red { background-color: #ffcccc; }
        .bg-yellow { background-color: #ffffcc; }
        .bg-green { background-color: #ccffcc; }
        .nowrap { white-space: nowrap; }
        .bold { font-weight: bold; }
        .small { font-size: 8px; }
    </style>
</head>
<body>';

// Pharmacy Header with Legal Notice
echo '<div class="header">NARCOTIC DRUGS & PSYCHOTROPIC SUBSTANCES REGISTER</div>';
echo '<div class="legal-notice">CONFIDENTIAL DOCUMENT - NDPS ACT, 1985 - FOR AUTHORIZED PERSONS ONLY</div>';
echo '<div class="subheader">' . ($shop_info['shop_name'] ?? 'Medical Store') . '</div>';
echo '<div class="info">';
echo ($shop_info['shop_address'] ?? '') . ' | ' . ($shop_info['shop_contact'] ?? '') . ' | ' . ($shop_info['shop_gst'] ?? '');
echo '<br>';
echo 'Period: ' . date('d/m/Y', strtotime($start_date)) . ' to ' . date('d/m/Y', strtotime($end_date));
echo ' | Generated: ' . date('d/m/Y h:i A');
echo '</div>';

// Export Information
echo '<div class="info" style="background-color: #f0f0f0; padding: 5px; margin-bottom: 10px;">';
echo '<strong>EXPORT INFORMATION:</strong> ';
echo 'Generated by: ' . ($_SESSION['user_name'] ?? 'System') . ' | ';
echo 'Total Records: ' . ($result ? $result->num_rows : 0) . ' | ';
echo 'Document ID: NDPS-' . date('Ymd-His');
echo '</div>';

// Summary Statistics
echo '<div style="margin-bottom: 15px; border: 1px solid #000; padding: 5px; background-color: #f9f9f9;">';
echo '<table style="width: 100%; border: none;">';
echo '<tr>';
echo '<td width="25%" class="bold">Total Transactions:</td><td>' . ($stats['total_transactions'] ?? 0) . '</td>';
echo '<td width="25%" class="bold">Total Patients:</td><td>' . ($stats['total_patients'] ?? 0) . '</td>';
echo '<td width="25%" class="bold">Total Doctors:</td><td>' . ($stats['total_doctors'] ?? 0) . '</td>';
echo '<td width="25%" class="bold">Unique Drugs:</td><td>' . ($stats['unique_drugs'] ?? 0) . '</td>';
echo '</tr>';
echo '<tr>';
echo '<td class="bold">Total Quantity:</td><td>' . ($stats['total_quantity'] ?? 0) . '</td>';
echo '<td class="bold">Total Value:</td><td>₹' . number_format($stats['total_value'] ?? 0, 2) . '</td>';
echo '<td class="bold">First Sale:</td><td>' . ($stats['first_sale'] ? date('d/m/Y', strtotime($stats['first_sale'])) : 'N/A') . '</td>';
echo '<td class="bold">Last Sale:</td><td>' . ($stats['last_sale'] ? date('d/m/Y', strtotime($stats['last_sale'])) : 'N/A') . '</td>';
echo '</tr>';
echo '</table>';
echo '</div>';

// Export filters used
if (!empty($doctor_name) || !empty($patient_name) || !empty($medicine_name) || !empty($bill_number)) {
    echo '<div style="background-color: #ffffcc; padding: 5px; margin-bottom: 10px; border: 1px solid #ffcc00;">';
    echo '<strong>Filters Applied:</strong> ';
    $filters = [];
    if (!empty($doctor_name)) $filters[] = 'Doctor: ' . $doctor_name;
    if (!empty($patient_name)) $filters[] = 'Patient: ' . $patient_name;
    if (!empty($medicine_name)) $filters[] = 'Medicine: ' . $medicine_name;
    if (!empty($bill_number)) $filters[] = 'Bill: ' . $bill_number;
    echo implode(' | ', $filters);
    echo '</div>';
}

// Main Table
echo '<table>';
echo '<thead>
    <tr>
        <th width="30">#</th>
        <th width="70">Date</th>
        <th width="50">Time</th>
        <th width="60">Bill No</th>
        <th width="100">Patient Name</th>
        <th width="80">Patient Phone</th>
        <th width="120">Medicine Name</th>
        <th width="80">Generic Name</th>
        <th width="60">Batch No</th>
        <th width="50">Expiry</th>
        <th width="100">Manufacturer</th>
        <th width="100">Doctor Name</th>
        <th width="80">Doctor License</th>
        <th width="70">Prescription Date</th>
        <th width="40">Qty</th>
        <th width="50">Unit Price</th>
        <th width="60">Amount</th>
        <th width="60">Doctor Sign</th>
        <th width="60">Pharmacist Sign</th>
        <th width="60">Patient Sign</th>
        <th width="80">Witness Name</th>
        <th width="70">Signature Date</th>
        <th width="60">Verification</th>
        <th width="120">Patient Address</th>
    </tr>
</thead>
<tbody>';

$counter = 1;
$grand_total_qty = 0;
$grand_total_amount = 0;

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        // Calculate totals
        $item_total = $row['quantity'] * $row['unit_price'];
        $grand_total_qty += $row['quantity'];
        $grand_total_amount += $item_total;
        
        // Determine row color based on signature status
        $row_color = '';
        if ($row['doctor_signature_status'] == 'SIGNED' && $row['pharmacist_signature'] != 'PENDING' && $row['patient_signature_status'] == 'SIGNED') {
            $row_color = 'bg-green';
        } elseif ($row['doctor_signature_status'] == 'SIGNED' || $row['pharmacist_signature'] != 'PENDING' || $row['patient_signature_status'] == 'SIGNED') {
            $row_color = 'bg-yellow';
        } else {
            $row_color = 'bg-red';
        }
        
        echo '<tr class="' . $row_color . '">';
        echo '<td>' . $counter++ . '</td>';
        echo '<td class="nowrap">' . $row['sale_date'] . '</td>';
        echo '<td class="nowrap">' . $row['sale_time'] . '</td>';
        echo '<td>INV-' . str_pad($row['sale_id'], 5, '0', STR_PAD_LEFT) . '</td>';
        echo '<td>' . htmlspecialchars($row['customer_name'] ?: $row['patient_name']) . '</td>';
        echo '<td>' . htmlspecialchars($row['customer_phone']) . '</td>';
        echo '<td>' . htmlspecialchars($row['medicine_name']) . '</td>';
        echo '<td>' . htmlspecialchars($row['generic_name']) . '</td>';
        echo '<td>' . htmlspecialchars($row['batch_number']) . '</td>';
        echo '<td>' . $row['expiry_date'] . '</td>';
        echo '<td>' . htmlspecialchars($row['manufacturer']) . '</td>';
        echo '<td>Dr. ' . htmlspecialchars($row['doctor_name']) . '</td>';
        echo '<td>' . htmlspecialchars($row['doctor_license'] ?? '') . '</td>';
        echo '<td>' . $row['prescription_date'] . '</td>';
        echo '<td class="text-right">' . $row['quantity'] . '</td>';
        echo '<td class="text-right">' . number_format($row['unit_price'], 2) . '</td>';
        echo '<td class="text-right">' . number_format($item_total, 2) . '</td>';
        echo '<td class="text-center">' . $row['doctor_signature_status'] . '</td>';
        echo '<td>' . ($row['pharmacist_signature'] != 'PENDING' ? htmlspecialchars($row['pharmacist_signature']) : 'PENDING') . '</td>';
        echo '<td class="text-center">' . $row['patient_signature_status'] . '</td>';
        echo '<td>' . htmlspecialchars($row['witness_name'] ?? '') . '</td>';
        echo '<td>' . ($row['signature_date'] ?: '') . '</td>';
        echo '<td class="text-center">' . ($row['narcotic_verified'] ? 'VERIFIED' : 'PENDING') . '</td>';
        echo '<td>' . htmlspecialchars($row['customer_address'] ?? '') . '</td>';
        echo '</tr>';
    }
} else {
    echo '<tr><td colspan="24" class="text-center">No narcotic drug records found for the selected period</td></tr>';
}

// Grand Total Row
echo '<tr class="total-row">';
echo '<td colspan="14" class="text-right"><strong>GRAND TOTAL:</strong></td>';
echo '<td class="text-right"><strong>' . $grand_total_qty . '</strong></td>';
echo '<td></td>';
echo '<td class="text-right"><strong>₹' . number_format($grand_total_amount, 2) . '</strong></td>';
echo '<td colspan="8"></td>';
echo '</tr>';

echo '</tbody>';
echo '</table>';

// Narcotic Drugs Covered List
echo '<div style="margin-top: 20px; page-break-inside: avoid;">';
echo '<h4 style="color: #d00;">List of Narcotic & Psychotropic Substances Covered:</h4>';
echo '<table style="width: 100%; border: 1px solid #000;">';
echo '<tr><th>Category</th><th>Drug Names</th><th>Schedule</th><th>Legal Status</th></tr>';

$drug_categories = [
    'Strong Narcotics' => ['Morphine', 'Pethidine', 'Fentanyl', 'Methadone', 'Buprenorphine', 'Codeine', 'Oxycodone', 'Hydrocodone'],
    'Psychotropic Substances' => ['Alprazolam', 'Clonazepam', 'Diazepam', 'Lorazepam', 'Nitrazepam', 'Zolpidem', 'Zopiclone', 'Methylphenidate'],
    'Controlled Substances' => ['Tramadol', 'Tapentadol', 'Ketamine', 'Phenobarbitone'],
    'Precursor Chemicals' => ['Pseudoephedrine', 'Ephedrine', 'Dextromethorphan']
];

foreach ($drug_categories as $category => $drugs) {
    echo '<tr>';
    echo '<td>' . $category . '</td>';
    echo '<td>' . implode(', ', $drugs) . '</td>';
    echo '<td>' . ($category == 'Strong Narcotics' ? 'NDPS Act' : 'Schedule X') . '</td>';
    echo '<td>' . ($category == 'Strong Narcotics' ? 'Highly Restricted' : 'Restricted') . '</td>';
    echo '</tr>';
}

echo '</table>';
echo '</div>';

// NDPS Act Declaration
echo '<div class="signature-section">';
echo '<h4 style="color: #d00; text-align: center;">DECLARATION AS PER NARCOTIC DRUGS & PSYCHOTROPIC SUBSTANCES ACT, 1985</h4>';
echo '<div style="text-align: justify; margin-bottom: 20px; font-size: 10px;">';
echo 'I hereby declare that the entries made in this register are true and correct to the best of my knowledge and belief. ';
echo 'All narcotic drugs and psychotropic substances listed above have been dispensed strictly against valid prescriptions ';
echo 'from registered medical practitioners. Proper identification of patients has been verified as required by law. ';
echo 'Maximum permissible quantity limits as per NDPS Act have been strictly observed. The narcotic drugs are stored ';
echo 'in separate locked cabinets as per regulations. This register is maintained in compliance with Sections 8, 9 & 10 ';
echo 'of the Narcotic Drugs and Psychotropic Substances Act, 1985 and Rule 65 of the Drugs and Cosmetics Rules, 1945.';
echo '</div>';

// Signatures Area
echo '<div style="margin-top: 20px;">';
echo '<div class="signature-box">';
echo '<p class="bold">PHARMACIST IN CHARGE:</p>';
echo '<div style="height: 30px; border-bottom: 1px solid #000; margin-bottom: 5px;"></div>';
echo '<p>Name: _______________________________________</p>';
echo '<p>Registration No: ____________________________</p>';
echo '<p>Signature: _________________________________</p>';
echo '<p>Date: _____________________________________</p>';
echo '</div>';

echo '<div class="signature-box">';
echo '<p class="bold">PROPRIETOR/OWNER:</p>';
echo '<div style="height: 30px; border-bottom: 1px solid #000; margin-bottom: 5px;"></div>';
echo '<p>Name: _______________________________________</p>';
echo '<p>Signature: _________________________________</p>';
echo '<p>Date: _____________________________________</p>';
echo '<p>Official Stamp/Seal:</p>';
echo '<div style="height: 40px; border: 1px dashed #000; margin-top: 5px;"></div>';
echo '</div>';

echo '<div style="clear: both;"></div>';

// Inspector Section
echo '<div style="margin-top: 30px; border: 2px solid #000; padding: 10px; background-color: #f9f9f9;">';
echo '<p class="bold" style="text-align: center; color: #d00;">FOR OFFICIAL USE ONLY - DRUG INSPECTOR/DEPUTY DRUGS CONTROLLER</p>';
echo '<div style="display: flex; justify-content: space-between; margin-top: 10px;">';
echo '<div style="width: 48%;">';
echo '<p>Name & Designation: ______________________________</p>';
echo '<p>Signature: _______________________________________</p>';
echo '</div>';
echo '<div style="width: 48%;">';
echo '<p>Date of Inspection: ______________________________</p>';
echo '<p>Official Stamp:</p>';
echo '<div style="height: 50px; border: 1px solid #000; margin-top: 5px;"></div>';
echo '</div>';
echo '</div>';
echo '<p style="margin-top: 10px;">Remarks/Observations:</p>';
echo '<div style="height: 60px; border: 1px solid #000; margin-top: 5px;"></div>';
echo '</div>';
echo '</div>';

// Footer Information
echo '<div style="margin-top: 30px; border-top: 1px solid #000; padding-top: 10px; font-size: 8px; color: #666;">';
echo '<p class="bold text-center">*** CONFIDENTIAL DOCUMENT - NARCOTIC DRUGS REGISTER ***</p>';
echo '<p class="text-center">This document is generated from computerized records. Original signatures are maintained in the physical register.</p>';
echo '<p class="text-center">Document ID: NDPS-' . date('Ymd-His') . ' | Total Pages: 1 | Total Records: ' . ($result ? $result->num_rows : 0) . '</p>';
echo '<p class="text-center"><strong>IMPORTANT:</strong> This register must be preserved for minimum 5 years as per NDPS Act regulations.</p>';
echo '<p class="text-center">Generated by: ' . ($_SESSION['user_name'] ?? 'System') . ' | IP: ' . $_SERVER['REMOTE_ADDR'] . ' | Browser: ' . $_SERVER['HTTP_USER_AGENT'] . '</p>';
echo '</div>';

echo '</body></html>';

// Close database connection
$conn->close();
exit();
?>

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: export-narcotic.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation