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/ecommer.in/public_html/billing/medical

Free Space:

10.27 TB

🔧 Quick Actions

✏️ Editing: narcotic-register.php

<?php 
include('includes/head.php');

// Database connection
include('includes/db_connection.php');

// Start session for print parameters
session_start();

// Check if required tables exist
$tables_check = [
    'sales' => $conn->query("SHOW TABLES LIKE 'sales'")->num_rows > 0,
    'sale_items' => $conn->query("SHOW TABLES LIKE 'sale_items'")->num_rows > 0,
    'medicines' => $conn->query("SHOW TABLES LIKE 'medicines'")->num_rows > 0,
    'prescription_details' => $conn->query("SHOW TABLES LIKE 'prescription_details'")->num_rows > 0,
    'customers' => $conn->query("SHOW TABLES LIKE 'customers'")->num_rows > 0
];

$all_tables_exist = !in_array(false, $tables_check);

// Get filter parameters with validation
$start_date = isset($_GET['start_date']) && !empty($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-d', strtotime('-30 days'));
$end_date = isset($_GET['end_date']) && !empty($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
$doctor_name = isset($_GET['doctor_name']) ? trim($_GET['doctor_name']) : '';
$patient_name = isset($_GET['patient_name']) ? trim($_GET['patient_name']) : '';
$medicine_name = isset($_GET['medicine_name']) ? trim($_GET['medicine_name']) : '';
$bill_number = isset($_GET['bill_number']) ? trim($_GET['bill_number']) : '';

// Handle form submissions
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Handle signature update
    if (isset($_POST['update_signature'])) {
        $sale_id = (int)$_POST['sale_id'];
        $medicine_id = (int)$_POST['medicine_id'];
        $doctor_signature = isset($_POST['doctor_signature']) ? trim($_POST['doctor_signature']) : '';
        $pharmacist_signature = isset($_POST['pharmacist_signature']) ? trim($_POST['pharmacist_signature']) : '';
        $narcotic_verified = isset($_POST['narcotic_verified']) ? 1 : 0;
        $patient_id_type = isset($_POST['patient_id_type']) ? trim($_POST['patient_id_type']) : '';
        $patient_id_number = isset($_POST['patient_id_number']) ? trim($_POST['patient_id_number']) : '';
        $witness_name = isset($_POST['witness_name']) ? trim($_POST['witness_name']) : '';
        
        // Check if prescription details exist
        $check_query = "SELECT id FROM prescription_details WHERE sale_id = ? AND medicine_id = ?";
        $check_stmt = $conn->prepare($check_query);
        if ($check_stmt === false) {
            $error_message = "Prepare failed (check): " . $conn->error;
        } else {
            $check_stmt->bind_param("ii", $sale_id, $medicine_id);
            $check_stmt->execute();
            $check_result = $check_stmt->get_result();

if ($check_result && $check_result->num_rows > 0) {
                // Update existing record
                $update_query = "
                    UPDATE prescription_details 
                    SET doctor_signature = ?, 
                        pharmacist_signature = ?,
                        signature_date = NOW(),
                        narcotic_verified = ?,
                        patient_id_type = ?,
                        patient_id_number = ?,
                        witness_name = ?
                    WHERE sale_id = ? AND medicine_id = ?
                ";
                $update_stmt = $conn->prepare($update_query);
                if ($update_stmt === false) {
                    $error_message = "Prepare failed (update): " . $conn->error;
                } else {
                    // Corrected type string: 8 variables (s,s,i,s,s,s,i,i) -> "ssisssii"
                    $update_stmt->bind_param(
                        "ssisssii",
                        $doctor_signature,
                        $pharmacist_signature,
                        $narcotic_verified,
                        $patient_id_type,
                        $patient_id_number,
                        $witness_name,
                        $sale_id,
                        $medicine_id
                    );

if ($update_stmt->execute()) {
                        $success_message = "Narcotic record updated successfully!";
                        
                        // Log the audit trail
                        $audit_query = "
                            INSERT INTO audit_trail (user_id, user_name, action_type, table_name, record_id, description, ip_address, timestamp)
                            VALUES (?, ?, 'NARCOTIC_SIGNED', 'prescription_details', ?, ?, ?, NOW())
                        ";
                        $user_id = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : NULL;
                        $user_name = isset($_SESSION['username']) ? $_SESSION['username'] : 'System';
                        $ip_address = $_SERVER['REMOTE_ADDR'] ?? 'unknown';
                        
                        $audit_stmt = $conn->prepare($audit_query);
                        if ($audit_stmt === false) {
                            // Non-fatal: log error
                            error_log("Prepare failed (audit): " . $conn->error);
                        } else {
                            // Types: user_id (i or null -> treat as i with cast), user_name (s), record_id (i), description (s), ip_address (s)
                            // Our query placeholders: ?, ?, ?, ?, ?  -> but we actually used 4 placeholders earlier; to match new audit_query we have 5 placeholders.
                            // Bind accordingly:
                            $description = "Signature added for Narcotic drug sale #$sale_id";
                            // Ensure user_id is integer (0 if null)
                            $user_id_bind = is_null($user_id) ? 0 : (int)$user_id;
                            $audit_stmt->bind_param("isiss", $user_id_bind, $user_name, $sale_id, $description, $ip_address);
                            $audit_stmt->execute();
                        }
                    } else {
                        $error_message = "Error updating narcotic record: " . $update_stmt->error;
                    }
                }
            } else {
                // No existing record - optionally insert one
                // Insert new prescription_detail record if required
                $insert_query = "
                    INSERT INTO prescription_details 
                        (sale_id, medicine_id, doctor_signature, pharmacist_signature, signature_date, narcotic_verified, patient_id_type, patient_id_number, witness_name)
                    VALUES (?, ?, ?, ?, NOW(), ?, ?, ?, ?)
                ";
                $insert_stmt = $conn->prepare($insert_query);
                if ($insert_stmt === false) {
                    $error_message = "Prepare failed (insert): " . $conn->error;
                } else {
                    // types: sale_id (i), medicine_id (i), doctor_signature (s), pharmacist_signature (s), narcotic_verified (i), patient_id_type (s), patient_id_number (s), witness_name (s)
                    $insert_stmt->bind_param(
                        "iississs",
                        $sale_id,
                        $medicine_id,
                        $doctor_signature,
                        $pharmacist_signature,
                        $narcotic_verified,
                        $patient_id_type,
                        $patient_id_number,
                        $witness_name
                    );

if ($insert_stmt->execute()) {
                        $success_message = "Narcotic record inserted successfully!";

// Log the audit trail for insert
                        $audit_query = "
                            INSERT INTO audit_trail (user_id, user_name, action_type, table_name, record_id, description, ip_address, timestamp)
                            VALUES (?, ?, 'NARCOTIC_SIGNED', 'prescription_details', ?, ?, ?, NOW())
                        ";
                        $user_id = isset($_SESSION['user_id']) ? $_SESSION['user_id'] : NULL;
                        $user_name = isset($_SESSION['username']) ? $_SESSION['username'] : 'System';
                        $ip_address = $_SERVER['REMOTE_ADDR'] ?? 'unknown';
                        $audit_stmt = $conn->prepare($audit_query);
                        if ($audit_stmt !== false) {
                            $description = "Signature added for Narcotic drug sale #$sale_id (insert)";
                            $user_id_bind = is_null($user_id) ? 0 : (int)$user_id;
                            $audit_stmt->bind_param("isiss", $user_id_bind, $user_name, $sale_id, $description, $ip_address);
                            $audit_stmt->execute();
                        } else {
                            error_log("Prepare failed (audit insert): " . $conn->error);
                        }
                    } else {
                        $error_message = "Error inserting narcotic record: " . $insert_stmt->error;
                    }
                }
            }
        }
    }
    
    // Handle print action
    if (isset($_POST['print_register'])) {
        // Store print parameters in session
        $_SESSION['print_narcotic'] = [
            'start_date' => $start_date,
            'end_date' => $end_date,
            'doctor_name' => $doctor_name,
            'patient_name' => $patient_name,
            'medicine_name' => $medicine_name,
            'bill_number' => $bill_number
        ];
        header('Location: print-narcotic-register.php');
        exit();
    }
}

// Fetch Narcotic sales data
if ($all_tables_exist) {
    // Build WHERE clause for Narcotic drugs
    $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 ? OR m.generic_name LIKE ?)";
        $params[] = "%$medicine_name%";
        $params[] = "%$medicine_name%";
        $param_types .= 'ss';
    }
    
    // Bill number - extract numeric part if INV- prefix is used
    if (!empty($bill_number)) {
        $bill_number_clean = preg_replace('/[^0-9]/', '', $bill_number);
        if (!empty($bill_number_clean)) {
            $where_conditions[] = "s.id = ?";
            $params[] = $bill_number_clean;
            $param_types .= 'i';
        }
    }
    
    // Narcotic drugs filter - only narcotic drugs
    $where_conditions[] = "m.is_narcotic = 1";
    
    // Only show prescription sales for narcotic drugs
    $where_conditions[] = "pd.id IS NOT NULL";
    
    $where_clause = implode(" AND ", $where_conditions);
    
    // Debug: Check the where clause
    error_log("Where Clause: " . $where_clause);
    error_log("Params count: " . count($params));
    error_log("Param types: " . $param_types);
    
    // Main query - for narcotic drugs
    $query = "
        SELECT 
            s.id as sale_id,
            s.sale_date,
            s.total_amount,
            s.customer_name,
            s.customer_phone,
            m.id as medicine_id,
            m.medicine_name,
            m.generic_name,
            m.batch_number,
            m.expiry_date,
            m.manufacturer,
            m.schedule_type,
            m.is_narcotic,
            si.quantity,
            si.unit_price,
            si.subtotal,
            pd.doctor_name,
            pd.doctor_license,
            pd.patient_name,
            pd.prescription_date,
            pd.doctor_signature,
            pd.pharmacist_signature,
            pd.signature_date,
            pd.narcotic_verified,
            pd.patient_id_type,
            pd.patient_id_number,
            pd.witness_name,
            pd.max_quantity_allowed,
            pd.balance_stock_after,
            c.customer_address,
            c.customer_email,
            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);
        if ($stmt === false) {
            $error_message = "Prepare failed (main query): " . $conn->error;
        } else {
            // Debug the parameters
            error_log("Binding " . count($params) . " parameters: " . implode(", ", $params));
            // bind dynamically
            $types = $param_types;
            // create references for bind_param
            $stmt_params = [];
            $stmt_params[] = & $types;
            for ($i = 0; $i < count($params); $i++) {
                $stmt_params[] = & $params[$i];
            }
            call_user_func_array([$stmt, 'bind_param'], $stmt_params);
            $stmt->execute();
            $result = $stmt->get_result();
        }
    } else {
        $result = $conn->query($query);
    }
    
    $total_records = $result ? $result->num_rows : 0;
    
    // Calculate statistics - FIXED QUERY
    $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.subtotal) as total_value,
            MIN(s.sale_date) as first_sale,
            MAX(s.sale_date) as last_sale,
            COUNT(DISTINCT CASE WHEN pd.narcotic_verified = 1 THEN s.id END) as verified_count,
            COUNT(DISTINCT CASE WHEN pd.narcotic_verified = 0 OR pd.narcotic_verified IS NULL THEN s.id END) as pending_verification
        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);
        if ($stats_stmt === false) {
            $error_message = "Prepare failed (stats): " . $conn->error;
        } else {
            $types = $param_types;
            $stmt_params = [];
            $stmt_params[] = & $types;
            for ($i = 0; $i < count($params); $i++) {
                $stmt_params[] = & $params[$i];
            }
            call_user_func_array([$stats_stmt, 'bind_param'], $stmt_params);
            $stats_stmt->execute();
            $stats_result = $stats_stmt->get_result();
        }
    } else {
        $stats_result = $conn->query($stats_query);
    }
    
    $stats = $stats_result ? $stats_result->fetch_assoc() : [];
}

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

<!doctype html>
<html lang="en">

<?php include('includes/pre-loader.php')?>

<div id="layout-wrapper">

<?php include('includes/topbar.php')?>

<div class="vertical-menu">

<?php include('includes/sidebar.php')?>
            
        </div>
    </div>

<div class="main-content">
        <div class="page-content">
           
            <div class="container-fluid">

<?php if (isset($success_message)): ?>
                    <div class="alert alert-success alert-dismissible fade show" role="alert">
                        <i class="mdi mdi-check-all me-2"></i>
                        <?php echo $success_message; ?>
                        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                    </div>
                    <script>
                        setTimeout(function() {
                            $('.alert-success').fadeOut();
                        }, 3000);
                    </script>
                <?php endif; ?>
                
                <?php if (isset($error_message)): ?>
                    <div class="alert alert-danger alert-dismissible fade show" role="alert">
                        <i class="mdi mdi-block-helper me-2"></i>
                        <?php echo $error_message; ?>
                        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                    </div>
                <?php endif; ?>

<div class="row">
                    <div class="col-xl-2 col-md-4 col-sm-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo $stats['total_transactions'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Total Transactions</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-file-document font-size-24 text-primary"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-2 col-md-4 col-sm-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo $stats['verified_count'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Verified Narcotics</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-shield-check font-size-24 text-success"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-2 col-md-4 col-sm-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo $stats['pending_verification'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Pending Verification</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-alert-circle font-size-24 text-warning"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-2 col-md-4 col-sm-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo $stats['total_patients'] ?? 0; ?></h4>
                                        <p class="text-muted mb-0">Unique Patients</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-account-multiple font-size-24 text-info"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-2 col-md-4 col-sm-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0">₹<?php echo number_format($stats['total_value'] ?? 0, 2); ?></h4>
                                        <p class="text-muted mb-0">Total Value</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-currency-inr font-size-24 text-success"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-xl-2 col-md-4 col-sm-6">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex align-items-center">
                                    <div class="flex-grow-1">
                                        <h4 class="mb-0"><?php echo $total_records; ?></h4>
                                        <p class="text-muted mb-0">Register Entries</p>
                                    </div>
                                    <div class="flex-shrink-0">
                                        <i class="mdi mdi-clipboard-text font-size-24 text-purple"></i>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

<div class="row">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <div class="d-flex justify-content-between align-items-center mb-4">
                                    <h4 class="card-title">Narcotic Drugs Register</h4>
                                    <div>
                                        <button type="button" class="btn btn-outline-info me-2" onclick="printNarcoticRegister()">
                                            <i class="mdi mdi-printer me-1"></i> Print Register
                                        </button>
                                        <button type="button" class="btn btn-outline-success me-2" onclick="exportNarcoticToExcel()">
                                            <i class="mdi mdi-file-excel me-1"></i> Export Excel
                                        </button>
                                        <a href="schedule-h1-register.php" class="btn btn-outline-primary">
                                            <i class="mdi mdi-pill me-1"></i> Schedule H1 Register
                                        </a>
                                    </div>
                                </div>

<form method="GET" action="" class="row g-2 mb-4" id="filterForm">
                                    <div class="col-md-2">
                                        <label class="form-label">Start Date</label>
                                        <input type="date" class="form-control" name="start_date" value="<?php echo htmlspecialchars($start_date); ?>">
                                    </div>
                                    <div class="col-md-2">
                                        <label class="form-label">End Date</label>
                                        <input type="date" class="form-control" name="end_date" value="<?php echo htmlspecialchars($end_date); ?>">
                                    </div>
                                    <div class="col-md-2">
                                        <label class="form-label">Doctor Name</label>
                                        <input type="text" class="form-control" name="doctor_name" value="<?php echo htmlspecialchars($doctor_name); ?>" placeholder="Doctor name...">
                                    </div>
                                    <div class="col-md-2">
                                        <label class="form-label">Patient Name</label>
                                        <input type="text" class="form-control" name="patient_name" value="<?php echo htmlspecialchars($patient_name); ?>" placeholder="Patient name...">
                                    </div>
                                    <div class="col-md-2">
                                        <label class="form-label">Medicine Name</label>
                                        <input type="text" class="form-control" name="medicine_name" value="<?php echo htmlspecialchars($medicine_name); ?>" placeholder="Medicine name...">
                                    </div>
                                    <div class="col-md-2">
                                        <label class="form-label">Bill Number</label>
                                        <input type="text" class="form-control" name="bill_number" value="<?php echo htmlspecialchars($bill_number); ?>" placeholder="e.g., 12345">
                                    </div>
                                    <div class="col-12">
                                        <div class="d-flex gap-2 mt-2">
                                            <button type="submit" class="btn btn-primary">
                                                <i class="mdi mdi-filter"></i> Apply Filters
                                            </button>
                                            <a href="narcotic-register.php" class="btn btn-secondary">
                                                <i class="mdi mdi-refresh"></i> Reset Filters
                                            </a>
                                            <button type="button" class="btn btn-danger" data-bs-toggle="modal" data-bs-target="#complianceModal">
                                                <i class="mdi mdi-shield-check"></i> Compliance Check
                                            </button>
                                            <button type="button" class="btn btn-info" data-bs-toggle="modal" data-bs-target="#helpModal">
                                                <i class="mdi mdi-help-circle"></i> Help
                                            </button>
                                        </div>
                                    </div>
                                </form>

<?php if (!$all_tables_exist): ?>
                                    <div class="alert alert-warning text-center">
                                        <h5>Required Tables Missing</h5>
                                        <p>Some required database tables are missing. Please check your database setup.</p>
                                        <div class="mt-2">
                                            <?php foreach ($tables_check as $table => $exists): ?>
                                                <span class="badge <?php echo $exists ? 'bg-success' : 'bg-danger'; ?> me-1">
                                                    <?php echo $table; ?>: <?php echo $exists ? '✓' : '✗'; ?>
                                                </span>
                                            <?php endforeach; ?>
                                        </div>
                                    </div>
                                <?php elseif ($total_records == 0): ?>
                                    <div class="alert alert-info text-center">
                                        <h5>No Narcotic Drug Records Found</h5>
                                        <p>No narcotic drug sales found for the selected period and filters.</p>
                                        <div class="mt-3">
                                            <a href="sales-history.php" class="btn btn-primary me-2">
                                                <i class="mdi mdi-history"></i> Check Sales History
                                            </a>
                                            <a href="add-medicine.php" class="btn btn-success">
                                                <i class="mdi mdi-plus"></i> Add Medicine
                                            </a>
                                        </div>
                                    </div>
                                <?php else: ?>
                                    
                                    <div class="table-responsive">
                                        <table class="table table-centered table-nowrap mb-0" id="narcoticTable">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>#</th>
                                                    <th>Date & Time</th>
                                                    <th>Bill No</th>
                                                    <th>Patient Details</th>
                                                    <th>Medicine Details</th>
                                                    <th>Doctor Details</th>
                                                    <th>Quantity</th>
                                                    <th>Amount</th>
                                                    <th>Verification Status</th>
                                                    <th>ID Proof</th>
                                                    <th>Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php $counter = 1; ?>
                                                <?php while ($row = $result->fetch_assoc()): ?>
                                                    <?php
                                                    // Determine verification status
                                                    $is_verified = !empty($row['narcotic_verified']) && $row['narcotic_verified'] == 1;
                                                    $has_signature = !empty($row['doctor_signature']) && !empty($row['pharmacist_signature']);
                                                    $row_class = $is_verified ? 'table-success' : ($has_signature ? 'table-warning' : 'table-danger');
                                                    $status_badge = $is_verified ? 'success' : ($has_signature ? 'warning' : 'danger');
                                                    $status_text = $is_verified ? 'Verified' : ($has_signature ? 'Signed' : 'Pending');
                                                    
                                                    // Format expiry date
                                                    $expiry_date = date('M Y', strtotime($row['expiry_date']));
                                                    $days_until_expiry = floor((strtotime($row['expiry_date']) - time()) / (60 * 60 * 24));
                                                    
                                                    // Determine expiry status
                                                    $expiry_class = '';
                                                    if ($days_until_expiry < 0) {
                                                        $expiry_class = 'danger';
                                                    } elseif ($days_until_expiry <= 30) {
                                                        $expiry_class = 'warning';
                                                    } else {
                                                        $expiry_class = 'success';
                                                    }
                                                    
                                                    // Check if max quantity exceeded
                                                    $max_qty = $row['max_quantity_allowed'];
                                                    $qty_sold = $row['quantity'];
                                                    $qty_warning = ($max_qty && $qty_sold > $max_qty) ? 'text-danger' : '';
                                                    
                                                    // ID Proof status
                                                    $has_id_proof = !empty($row['patient_id_type']) && !empty($row['patient_id_number']);
                                                    $id_proof_badge = $has_id_proof ? 'success' : 'danger';
                                                    $id_proof_text = $has_id_proof ? htmlspecialchars($row['patient_id_type']) : 'Not provided';
                                                    ?>
                                                    <tr class="<?php echo $row_class; ?>">
                                                        <td><?php echo $counter++; ?></td>
                                                        <td>
                                                            <?php echo date('d/m/Y', strtotime($row['sale_date'])); ?><br>
                                                            <small class="text-muted"><?php echo date('h:i A', strtotime($row['sale_date'])); ?></small>
                                                        </td>
                                                        <td>
                                                            <strong><?php echo $row['sale_id']; ?></strong>
                                                        </td>
                                                        <td>
                                                            <strong><?php echo htmlspecialchars($row['customer_name'] ?: $row['patient_name'] ?: 'N/A'); ?></strong><br>
                                                            <small><?php echo htmlspecialchars($row['customer_phone'] ?? 'N/A'); ?></small><br>
                                                            <?php if (!empty($row['customer_email'])): ?>
                                                                <small><?php echo htmlspecialchars($row['customer_email']); ?></small><br>
                                                            <?php endif; ?>
                                                            <small class="text-muted"><?php echo htmlspecialchars($row['customer_address'] ?? 'Address not provided'); ?></small>
                                                        </td>
                                                        <td>
                                                            <strong><?php echo htmlspecialchars($row['medicine_name']); ?></strong><br>
                                                            <?php if (!empty($row['generic_name'])): ?>
                                                                <small>Generic: <?php echo htmlspecialchars($row['generic_name']); ?></small><br>
                                                            <?php endif; ?>
                                                            <small>Batch: <?php echo htmlspecialchars($row['batch_number']); ?></small><br>
                                                            <small>Exp: <span class="badge bg-<?php echo $expiry_class; ?>"><?php echo $expiry_date; ?></span></small><br>
                                                            <span class="badge bg-danger">NARCOTIC</span>
                                                            <?php if ($row['schedule_type'] === 'X'): ?>
                                                                <span class="badge bg-warning">SCHEDULE X</span>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <strong>Dr. <?php echo htmlspecialchars($row['doctor_name'] ?? 'N/A'); ?></strong><br>
                                                            <small>License: <?php echo htmlspecialchars($row['doctor_license'] ?? 'Not provided'); ?></small><br>
                                                            <small>Date: <?php echo date('d/m/Y', strtotime($row['prescription_date'])); ?></small>
                                                        </td>
                                                        <td class="text-center">
                                                            <span class="badge bg-primary <?php echo $qty_warning; ?>">
                                                                <?php echo $row['quantity']; ?>
                                                            </span>
                                                            <?php if ($max_qty): ?>
                                                                <br><small class="text-muted">Max: <?php echo $max_qty; ?></small>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            ₹<?php echo number_format($row['subtotal'], 2); ?><br>
                                                            <small>@ ₹<?php echo number_format($row['unit_price'], 2); ?></small>
                                                        </td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $status_badge; ?>">
                                                                <?php echo $status_text; ?>
                                                            </span>
                                                            <?php if ($is_verified): ?>
                                                                <br>
                                                                <small class="text-muted">Verified on <?php echo date('d/m/Y', strtotime($row['signature_date'])); ?></small>
                                                            <?php endif; ?>
                                                            <?php if (!empty($row['witness_name'])): ?>
                                                                <br>
                                                                <small class="text-muted">Witness: <?php echo htmlspecialchars($row['witness_name']); ?></small>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <span class="badge bg-<?php echo $id_proof_badge; ?>">
                                                                <?php echo $id_proof_text; ?>
                                                            </span>
                                                            <?php if ($has_id_proof): ?>
                                                                <br>
                                                                <small class="text-muted"><?php echo htmlspecialchars($row['patient_id_number']); ?></small>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <div class="d-flex gap-1">
                                                                <button type="button" 
                                                                        class="btn btn-sm btn-outline-danger" 
                                                                        data-bs-toggle="modal" 
                                                                        data-bs-target="#signatureModal"
                                                                        onclick="setNarcoticData(
                                                                            <?php echo $row['sale_id']; ?>, 
                                                                            <?php echo $row['medicine_id']; ?>, 
                                                                            '<?php echo addslashes($row['medicine_name']); ?>', 
                                                                            '<?php echo addslashes($row['doctor_name'] ?? ''); ?>',
                                                                            '<?php echo addslashes($row['doctor_signature'] ?? ''); ?>',
                                                                            '<?php echo addslashes($row['pharmacist_signature'] ?? ''); ?>',
                                                                            <?php echo $row['narcotic_verified'] ? 'true' : 'false'; ?>,
                                                                            '<?php echo addslashes($row['patient_id_type'] ?? ''); ?>',
                                                                            '<?php echo addslashes($row['patient_id_number'] ?? ''); ?>',
                                                                            '<?php echo addslashes($row['witness_name'] ?? ''); ?>'
                                                                        )"
                                                                        title="Update Narcotic Details">
                                                                    <i class="mdi mdi-security"></i>
                                                                </button>
                                                                <a href="sale-details.php?id=<?php echo $row['sale_id']; ?>" 
                                                                   class="btn btn-sm btn-outline-info"
                                                                   target="_blank"
                                                                   title="View Sale Details">
                                                                    <i class="mdi mdi-eye"></i>
                                                                </a>
                                                                <?php if ($row['prescription_date']): ?>
                                                                    <a href="print-prescription.php?sale_id=<?php echo $row['sale_id']; ?>&medicine_id=<?php echo $row['medicine_id']; ?>" 
                                                                       class="btn btn-sm btn-outline-primary"
                                                                       target="_blank"
                                                                       title="Print Prescription">
                                                                        <i class="mdi mdi-printer"></i>
                                                                    </a>
                                                                <?php endif; ?>
                                                                <button type="button" 
                                                                        class="btn btn-sm btn-outline-warning"
                                                                        onclick="showNarcoticDetails(<?php echo $row['sale_id']; ?>)"
                                                                        title="View Full Details">
                                                                    <i class="mdi mdi-information"></i>
                                                                </button>
                                                            </div>
                                                        </td>
                                                    </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                            <tfoot>
                                                <tr class="table-light">
                                                    <td colspan="7" class="text-end"><strong>Total:</strong></td>
                                                    <td>
                                                        <strong>₹<?php echo number_format($stats['total_value'] ?? 0, 2); ?></strong>
                                                    </td>
                                                    <td colspan="3"></td>
                                                </tr>
                                            </tfoot>
                                        </table>
                                    </div>
                                    
                                    
                                    <div class="row mt-3">
                                        <div class="col-sm-12">
                                            <div class="d-flex justify-content-between align-items-center">
                                                <div class="text-muted">
                                                    Showing <strong><?php echo $total_records; ?></strong> narcotic drug sale(s)
                                                    from <strong><?php echo date('d M Y', strtotime($start_date)); ?></strong> 
                                                    to <strong><?php echo date('d M Y', strtotime($end_date)); ?></strong>
                                                    <?php if (!empty($doctor_name) || !empty($patient_name) || !empty($medicine_name) || !empty($bill_number)): ?>
                                                        <br><small>(with additional filters applied)</small>
                                                    <?php endif; ?>
                                                </div>
                                                <div>
                                                    <button type="button" class="btn btn-outline-info me-2" onclick="printNarcoticRegister()">
                                                        <i class="mdi mdi-printer me-1"></i> Print Register
                                                    </button>
                                                    <button type="button" class="btn btn-success" onclick="exportNarcoticToExcel()">
                                                        <i class="mdi mdi-file-excel me-1"></i> Export Excel
                                                    </button>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

</div>
            
        </div>

<?php include('includes/footer.php')?>
    </div>

</div>

<?php include('includes/rightbar.php')?>

<div class="modal fade" id="signatureModal" tabindex="-1" aria-labelledby="signatureModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="signatureModalLabel">Narcotic Drug Details</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <form method="POST" action="">
                <div class="modal-body">
                    <input type="hidden" id="modal_sale_id" name="sale_id">
                    <input type="hidden" id="modal_medicine_id" name="medicine_id">
                    
                    <div class="mb-3">
                        <label for="modal_medicine_name" class="form-label">Narcotic Medicine</label>
                        <input type="text" class="form-control" id="modal_medicine_name" readonly>
                    </div>
                    
                    <div class="mb-3">
                        <label for="modal_doctor_name" class="form-label">Prescribing Doctor</label>
                        <input type="text" class="form-control" id="modal_doctor_name" readonly>
                    </div>
                    
                    <div class="mb-3">
                        <label for="patient_id_type" class="form-label">Patient ID Type</label>
                        <select class="form-select" id="patient_id_type" name="patient_id_type" required>
                            <option value="">Select ID Type</option>
                            <option value="Aadhar">Aadhar Card</option>
                            <option value="PAN">PAN Card</option>
                            <option value="Passport">Passport</option>
                            <option value="Driving License">Driving License</option>
                            <option value="Voter ID">Voter ID</option>
                            <option value="Other">Other</option>
                        </select>
                        <small class="text-muted">ID proof is mandatory for narcotic drugs</small>
                    </div>
                    
                    <div class="mb-3">
                        <label for="patient_id_number" class="form-label">ID Number</label>
                        <input type="text" class="form-control" id="patient_id_number" name="patient_id_number" 
                               placeholder="Enter ID number" required>
                    </div>
                    
                    <div class="mb-3">
                        <label for="doctor_signature" class="form-label">Doctor's Prescription Details</label>
                        <textarea class="form-control" id="doctor_signature" name="doctor_signature" rows="2" 
                                  placeholder="Enter doctor's prescription details..."></textarea>
                    </div>
                    
                    <div class="mb-3">
                        <label for="pharmacist_signature" class="form-label">Dispensing Pharmacist</label>
                        <input type="text" class="form-control" id="pharmacist_signature" name="pharmacist_signature" 
                               placeholder="Enter pharmacist's name" required>
                    </div>
                    
                    <div class="mb-3">
                        <label for="witness_name" class="form-label">Witness Name (Optional)</label>
                        <input type="text" class="form-control" id="witness_name" name="witness_name" 
                               placeholder="Enter witness name if any">
                    </div>
                    
                    <div class="mb-3 form-check">
                        <input type="checkbox" class="form-check-input" id="narcotic_verified" name="narcotic_verified" value="1">
                        <label class="form-check-label" for="narcotic_verified">
                            <strong>Narcotic Verification Complete</strong>
                        </label>
                        <small class="text-muted d-block">Check this after verifying all details as per narcotic drug regulations</small>
                    </div>
                    
                    <div class="alert alert-danger">
                        <i class="mdi mdi-alert-circle"></i>
                        <small>Narcotic drugs are strictly controlled substances. Ensure all regulations are followed.</small>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                    <button type="submit" name="update_signature" class="btn btn-primary">Save Narcotic Record</button>
                </div>
            </form>
        </div>
    </div>
</div>

<div class="modal fade" id="complianceModal" tabindex="-1" aria-labelledby="complianceModalLabel" aria-hidden="true">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="complianceModalLabel"><i class="mdi mdi-shield-check"></i> Narcotic Compliance Check</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <div class="modal-body">
                <div class="alert alert-danger">
                    <h6><i class="mdi mdi-alert"></i> Strict Legal Requirements</h6>
                    <p class="mb-0">Narcotic drugs are governed by the Narcotic Drugs and Psychotropic Substances (NDPS) Act, 1985.</p>
                </div>
                
                <h6>Compliance Checklist:</h6>
                <div class="table-responsive">
                    <table class="table table-bordered">
                        <thead>
                            <tr>
                                <th width="5%">#</th>
                                <th width="50%">Requirement</th>
                                <th width="15%">Status</th>
                                <th width="30%">Remarks</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>1</td>
                                <td>Valid prescription with doctor's registration number</td>
                                <td><span class="badge bg-<?php echo ($stats['total_transactions'] ?? 0) > 0 ? 'success' : 'secondary'; ?>">Check</span></td>
                                <td><?php echo $stats['total_doctors'] ?? 0; ?> doctors recorded</td>
                            </tr>
                            <tr>
                                <td>2</td>
                                <td>Patient identity proof verification</td>
                                <td><span class="badge bg-<?php echo ($stats['total_patients'] ?? 0) > 0 ? 'success' : 'secondary'; ?>">Check</span></td>
                                <td><?php echo $stats['total_patients'] ?? 0; ?> patients recorded</td>
                            </tr>
                            <tr>
                                <td>3</td>
                                <td>Dual signatures (Doctor & Pharmacist)</td>
                                <td><span class="badge bg-<?php echo ($stats['verified_count'] ?? 0) > 0 ? 'success' : 'secondary'; ?>">Check</span></td>
                                <td><?php echo $stats['verified_count'] ?? 0; ?> verified records</td>
                            </tr>
                            <tr>
                                <td>4</td>
                                <td>Maintain separate narcotic register</td>
                                <td><span class="badge bg-success">✓</span></td>
                                <td>This register is maintained</td>
                            </tr>
                            <tr>
                                <td>5</td>
                                <td>Limit quantity as per prescription</td>
                                <td><span class="badge bg-<?php echo ($total_records ?? 0) > 0 ? 'success' : 'secondary'; ?>">Check</span></td>
                                <td><?php echo $total_records ?? 0; ?> entries checked</td>
                            </tr>
                            <tr>
                                <td>6</td>
                                <td>Monthly stock verification</td>
                                <td><span class="badge bg-warning">Due</span></td>
                                <td>Last checked: <?php echo date('d/m/Y'); ?></td>
                            </tr>
                        </tbody>
                    </table>
                </div>
                
                <h6>Regulatory Requirements:</h6>
                <ol>
                    <li>Maintain separate register for each narcotic drug</li>
                    <li>Prescription must be retained for 2 years</li>
                    <li>Register must be preserved for 5 years</li>
                    <li>Immediate reporting of any discrepancies</li>
                    <li>Regular inspection by drug control authorities</li>
                </ol>
                
                <div class="alert alert-info">
                    <h6><i class="mdi mdi-information"></i> Important Notes:</h6>
                    <ul class="mb-0">
                        <li>Violation of NDPS Act can lead to imprisonment</li>
                        <li>Always verify doctor's registration with medical council</li>
                        <li>Keep digital and physical copies of all records</li>
                        <li>Conduct regular staff training on narcotic handling</li>
                        <li>Install CCTV as per regulatory requirements</li>
                    </ul>
                </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                <button type="button" class="btn btn-success" onclick="runComplianceCheck()">
                    <i class="mdi mdi-shield-sync"></i> Run Full Check
                </button>
                <button type="button" class="btn btn-primary" onclick="printComplianceReport()">
                    <i class="mdi mdi-printer"></i> Print Report
                </button>
            </div>
        </div>
    </div>
</div>

<div class="modal fade" id="helpModal" tabindex="-1" aria-labelledby="helpModalLabel" aria-hidden="true">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="helpModalLabel"><i class="mdi mdi-help-circle-outline"></i> Narcotic Drugs Register Help</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <div class="modal-body">
                <div class="alert alert-danger">
                    <h6><i class="mdi mdi-alert"></i> Legal Warning</h6>
                    <p class="mb-0">Narcotic drugs handling is strictly regulated under NDPS Act 1985. Non-compliance can result in severe penalties including imprisonment.</p>
                </div>
                
                <h6>Common Narcotic Drugs:</h6>
                <div class="row">
                    <div class="col-md-3">
                        <div class="card border-danger">
                            <div class="card-body text-center">
                                <h6 class="text-danger">Morphine</h6>
                                <small>Schedule I NDPS</small>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="card border-danger">
                            <div class="card-body text-center">
                                <h6 class="text-danger">Fentanyl</h6>
                                <small>Schedule I NDPS</small>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="card border-danger">
                            <div class="card-body text-center">
                                <h6 class="text-danger">Pethidine</h6>
                                <small>Schedule II NDPS</small>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="card border-danger">
                            <div class="card-body text-center">
                                <h6 class="text-danger">Methadone</h6>
                                <small>Schedule II NDPS</small>
                            </div>
                        </div>
                    </div>
                </div>
                
                <h6>Mandatory Documentation:</h6>
                <ol>
                    <li><strong>Valid prescription</strong> from registered medical practitioner</li>
                    <li><strong>Patient identity proof</strong> with photo and address</li>
                    <li><strong>Doctor's registration number</strong> clearly mentioned</li>
                    <li><strong>Dual signatures</strong> (Doctor + Pharmacist)</li>
                    <li><strong>Witness signature</strong> for high-quantity narcotics</li>
                    <li><strong>Stock balance record</strong> after each transaction</li>
                </ol>
                
                <h6>Storage Requirements:</h6>
                <ul>
                    <li>Store in double-locked cupboard/almirah</li>
                    <li>Maintain separate stock register for each narcotic</li>
                    <li>Regular stock verification (weekly minimum)</li>
                    <li>CCTV surveillance in storage area</li>
                    <li>Access limited to authorized personnel only</li>
                </ul>
                
                <h6>Printing Instructions:</h6>
                <p>Click "Print Register" to generate official narcotic register with:</p>
                <ul>
                    <li>Complete transaction details with signatures</li>
                    <li>Patient identification information</li>
                    <li>Doctor's prescription details</li>
                    <li>Stock balance records</li>
                    <li>Compliance declaration</li>
                </ul>
                
                <div class="alert alert-warning">
                    <h6><i class="mdi mdi-lightbulb-on"></i> Best Practices:</h6>
                    <ul class="mb-0">
                        <li>Train all staff on NDPS Act requirements</li>
                        <li>Conduct surprise audits regularly</li>
                        <li>Maintain digital backup of all records</li>
                        <li>Immediately report any stock discrepancy</li>
                        <li>Keep emergency contact numbers accessible</li>
                        <li>Cooperate fully with drug control inspections</li>
                    </ul>
                </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
            </div>
        </div>
    </div>
</div>

<?php include('includes/scripts.php')?>

<link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/dataTables.bootstrap5.min.css">
<script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.4/js/dataTables.bootstrap5.min.js"></script>

</body>

</html>
<?php
// Close database connection
if (isset($conn)) {
    $conn->close();
}

❌ Cancel

🗂️ Enhanced PHP File Manager

📊 Server Information

🔧 Quick Actions

✏️ Editing: narcotic-register.php

📤 Upload File

📁 Create Folder

📄 Create File

✏️ Rename Item

🗑️ Delete Confirmation