280 lines
14 KiB
PHP
280 lines
14 KiB
PHP
<?php
|
|
require_once __DIR__ . '/db/config.php';
|
|
require_once __DIR__ . '/helpers.php';
|
|
|
|
require_once __DIR__ . '/includes/auth.php';
|
|
check_auth();
|
|
|
|
$pdo = db();
|
|
$lang = $_SESSION['lang'] ?? 'en';
|
|
$type = $_GET['type'] ?? 'inventory_valuation';
|
|
$startDate = $_GET['start_date'] ?? date('Y-m-01');
|
|
$endDate = $_GET['end_date'] ?? date('Y-m-d');
|
|
|
|
// Fetch company settings
|
|
$stmt = $pdo->query("SELECT setting_key, setting_value FROM settings WHERE setting_key IN ('company_name', 'company_logo')");
|
|
$site_settings = [];
|
|
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
|
|
$site_settings[$row['setting_key']] = $row['setting_value'];
|
|
}
|
|
$companyName = $site_settings['company_name'] ?? __('hospital_management');
|
|
|
|
// Helper to translate report titles
|
|
function getReportTitle($type) {
|
|
switch ($type) {
|
|
case 'inventory_valuation': return __('inventory_valuation');
|
|
case 'sales': return __('sales_report');
|
|
case 'expiry': return __('expiry_report');
|
|
case 'low_stock': return __('low_stock_report');
|
|
case 'purchase_report': return __('purchase_report');
|
|
default: return __('report');
|
|
}
|
|
}
|
|
|
|
// Data Fetching Logic (No pagination for print, or very high limit)
|
|
$limit = 1000;
|
|
$data = [];
|
|
$grandTotals = [];
|
|
|
|
if ($type === 'inventory_valuation') {
|
|
$sql = "SELECT d.name_en as drug_name, d.name_ar as drug_name_ar,
|
|
g.name_en as category_name,
|
|
SUM(b.quantity) as stock_quantity,
|
|
SUM(b.quantity * b.cost_price) / SUM(b.quantity) as avg_cost,
|
|
SUM(b.quantity * b.sale_price) / SUM(b.quantity) as selling_price,
|
|
SUM(b.quantity * b.cost_price) as total_cost_value,
|
|
SUM(b.quantity * b.sale_price) as total_sales_value
|
|
FROM drugs d
|
|
JOIN pharmacy_batches b ON d.id = b.drug_id
|
|
LEFT JOIN drugs_groups g ON d.group_id = g.id
|
|
WHERE b.quantity > 0
|
|
GROUP BY d.id
|
|
ORDER BY d.name_en ASC
|
|
LIMIT $limit";
|
|
$stmt = $pdo->query($sql);
|
|
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$grandTotalSql = "SELECT SUM(b.quantity * b.cost_price) as total_cost,
|
|
SUM(b.quantity * b.sale_price) as total_sales
|
|
FROM pharmacy_batches b
|
|
WHERE b.quantity > 0";
|
|
$grandTotals = $pdo->query($grandTotalSql)->fetch(PDO::FETCH_ASSOC);
|
|
|
|
} elseif ($type === 'sales') {
|
|
$sql = "SELECT s.id, s.created_at, s.total_amount, s.payment_method,
|
|
p.name as patient_name,
|
|
(SELECT COUNT(*) FROM pharmacy_sale_items i WHERE i.sale_id = s.id) as item_count
|
|
FROM pharmacy_sales s
|
|
LEFT JOIN patients p ON s.patient_id = p.id
|
|
WHERE s.created_at BETWEEN ? AND ? + INTERVAL 1 DAY
|
|
ORDER BY s.created_at DESC
|
|
LIMIT $limit";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([$startDate, $endDate]);
|
|
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$grandTotalSql = "SELECT SUM(total_amount) as total FROM pharmacy_sales WHERE created_at BETWEEN ? AND ? + INTERVAL 1 DAY";
|
|
$grandTotalStmt = $pdo->prepare($grandTotalSql);
|
|
$grandTotalStmt->execute([$startDate, $endDate]);
|
|
$grandTotals['total_sales'] = $grandTotalStmt->fetchColumn();
|
|
|
|
} elseif ($type === 'expiry') {
|
|
$sql = "SELECT b.id, b.batch_number, b.expiry_date, b.quantity,
|
|
d.name_en as drug_name, d.name_ar as drug_name_ar,
|
|
s.name_en as supplier_name,
|
|
DATEDIFF(b.expiry_date, CURDATE()) as days_remaining
|
|
FROM pharmacy_batches b
|
|
JOIN drugs d ON b.drug_id = d.id
|
|
LEFT JOIN suppliers s ON b.supplier_id = s.id
|
|
WHERE b.expiry_date BETWEEN ? AND ? AND b.quantity > 0
|
|
ORDER BY b.expiry_date ASC
|
|
LIMIT $limit";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([$startDate, $endDate]);
|
|
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
} elseif ($type === 'low_stock') {
|
|
$sql = "SELECT d.id, d.name_en, d.name_ar, d.min_stock_level, d.reorder_level, d.unit,
|
|
COALESCE(SUM(b.quantity), 0) as total_stock
|
|
FROM drugs d
|
|
LEFT JOIN pharmacy_batches b ON d.id = b.drug_id AND b.quantity > 0 AND b.expiry_date >= CURDATE()
|
|
GROUP BY d.id
|
|
HAVING total_stock <= MAX(d.reorder_level)
|
|
ORDER BY total_stock ASC
|
|
LIMIT $limit";
|
|
$stmt = $pdo->query($sql);
|
|
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
} elseif ($type === 'purchase_report') {
|
|
$sql = "SELECT l.id, l.lpo_date, l.status, l.total_amount, s.name_en as supplier_name
|
|
FROM pharmacy_lpos l
|
|
LEFT JOIN suppliers s ON l.supplier_id = s.id
|
|
WHERE l.lpo_date BETWEEN ? AND ?
|
|
ORDER BY l.lpo_date DESC
|
|
LIMIT $limit";
|
|
$stmt = $pdo->prepare($sql);
|
|
$stmt->execute([$startDate, $endDate]);
|
|
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$grandTotalSql = "SELECT SUM(total_amount) as total FROM pharmacy_lpos WHERE lpo_date BETWEEN ? AND ?";
|
|
$grandTotalStmt = $pdo->prepare($grandTotalSql);
|
|
$grandTotalStmt->execute([$startDate, $endDate]);
|
|
$grandTotals['total_purchases'] = $grandTotalStmt->fetchColumn();
|
|
}
|
|
|
|
?>
|
|
<!DOCTYPE html>
|
|
<html lang="<?php echo $lang; ?>" dir="<?php echo get_dir(); ?>">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<title><?php echo getReportTitle($type); ?></title>
|
|
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
|
|
<?php if (is_rtl()): ?>
|
|
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.rtl.min.css">
|
|
<?php endif; ?>
|
|
<style>
|
|
body { font-size: 14px; background: white; }
|
|
.table thead th { background-color: #f8f9fa !important; border-bottom: 2px solid #dee2e6; -webkit-print-color-adjust: exact; }
|
|
@media print {
|
|
.no-print { display: none !important; }
|
|
.card { border: none !important; shadow: none !important; }
|
|
body { padding: 0; }
|
|
}
|
|
</style>
|
|
</head>
|
|
<body onload="window.print()">
|
|
<div class="container-fluid py-4">
|
|
<div class="d-flex justify-content-between align-items-center mb-4">
|
|
<div>
|
|
<h3 class="fw-bold mb-1"><?php echo htmlspecialchars($companyName); ?></h3>
|
|
<h5 class="text-muted"><?php echo getReportTitle($type); ?></h5>
|
|
</div>
|
|
<div class="text-end">
|
|
<p class="mb-0 text-muted"><?php echo __('printed_on'); ?>: <?php echo date('Y-m-d H:i'); ?></p>
|
|
<?php if (in_array($type, ['sales', 'expiry', 'purchase_report'])): ?>
|
|
<p class="mb-0 text-muted"><?php echo __('date'); ?>: <?php echo $startDate; ?> - <?php echo $endDate; ?></p>
|
|
<?php endif; ?>
|
|
</div>
|
|
</div>
|
|
|
|
<table class="table table-bordered table-striped">
|
|
<thead>
|
|
<?php if ($type === 'inventory_valuation'): ?>
|
|
<tr>
|
|
<th><?php echo __('drug_name'); ?></th>
|
|
<th><?php echo __('category'); ?></th>
|
|
<th><?php echo __('current_stock'); ?></th>
|
|
<th><?php echo __('avg_cost'); ?></th>
|
|
<th><?php echo __('selling_price'); ?></th>
|
|
<th><?php echo __('total_cost_value'); ?></th>
|
|
<th><?php echo __('total_sales_value'); ?></th>
|
|
</tr>
|
|
<?php elseif ($type === 'sales'): ?>
|
|
<tr>
|
|
<th><?php echo __('date'); ?></th>
|
|
<th><?php echo __('receipt_no'); ?></th>
|
|
<th><?php echo __('patient'); ?></th>
|
|
<th><?php echo __('items'); ?></th>
|
|
<th><?php echo __('total_amount'); ?></th>
|
|
<th><?php echo __('payment_method'); ?></th>
|
|
</tr>
|
|
<?php elseif ($type === 'expiry'): ?>
|
|
<tr>
|
|
<th><?php echo __('drug_name'); ?></th>
|
|
<th><?php echo __('batch_number'); ?></th>
|
|
<th><?php echo __('expiry_date'); ?></th>
|
|
<th><?php echo __('quantity'); ?></th>
|
|
<th><?php echo __('supplier'); ?></th>
|
|
<th><?php echo __('days_remaining'); ?></th>
|
|
</tr>
|
|
<?php elseif ($type === 'low_stock'): ?>
|
|
<tr>
|
|
<th><?php echo __('drug_name'); ?></th>
|
|
<th><?php echo __('current_stock'); ?></th>
|
|
<th><?php echo __('min_stock_level'); ?></th>
|
|
<th><?php echo __('reorder_level'); ?></th>
|
|
<th><?php echo __('unit'); ?></th>
|
|
</tr>
|
|
<?php elseif ($type === 'purchase_report'): ?>
|
|
<tr>
|
|
<th><?php echo __('date'); ?></th>
|
|
<th><?php echo __('lpo'); ?> #</th>
|
|
<th><?php echo __('supplier'); ?></th>
|
|
<th><?php echo __('status'); ?></th>
|
|
<th><?php echo __('total_amount'); ?></th>
|
|
</tr>
|
|
<?php endif; ?>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach ($data as $row): ?>
|
|
<tr>
|
|
<?php if ($type === 'inventory_valuation'): ?>
|
|
<td><?php echo htmlspecialchars($row['drug_name']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['category_name'] ?? '-'); ?></td>
|
|
<td><?php echo $row['stock_quantity']; ?></td>
|
|
<td><?php echo format_currency($row['avg_cost']); ?></td>
|
|
<td><?php echo format_currency($row['selling_price']); ?></td>
|
|
<td><?php echo format_currency($row['total_cost_value']); ?></td>
|
|
<td><?php echo format_currency($row['total_sales_value']); ?></td>
|
|
|
|
<?php elseif ($type === 'sales'): ?>
|
|
<td><?php echo $row['created_at']; ?></td>
|
|
<td>#<?php echo $row['id']; ?></td>
|
|
<td><?php echo htmlspecialchars($row['patient_name'] ?? 'Guest'); ?></td>
|
|
<td><?php echo $row['item_count']; ?></td>
|
|
<td><?php echo format_currency($row['total_amount']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['payment_method']); ?></td>
|
|
|
|
<?php elseif ($type === 'expiry'): ?>
|
|
<td><?php echo htmlspecialchars($row['drug_name']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['batch_number']); ?></td>
|
|
<td><?php echo $row['expiry_date']; ?></td>
|
|
<td><?php echo $row['quantity']; ?></td>
|
|
<td><?php echo htmlspecialchars($row['supplier_name'] ?? '-'); ?></td>
|
|
<td><?php echo $row['days_remaining']; ?></td>
|
|
|
|
<?php elseif ($type === 'low_stock'): ?>
|
|
<td><?php echo htmlspecialchars($row['name_en']); ?></td>
|
|
<td><?php echo $row['total_stock']; ?></td>
|
|
<td><?php echo $row['min_stock_level']; ?></td>
|
|
<td><?php echo $row['reorder_level']; ?></td>
|
|
<td><?php echo $row['unit'] ?? '-'; ?></td>
|
|
|
|
<?php elseif ($type === 'purchase_report'): ?>
|
|
<td><?php echo $row['lpo_date']; ?></td>
|
|
<td>#<?php echo $row['id']; ?></td>
|
|
<td><?php echo htmlspecialchars($row['supplier_name'] ?? '-'); ?></td>
|
|
<td><?php echo htmlspecialchars($row['status']); ?></td>
|
|
<td><?php echo format_currency($row['total_amount']); ?></td>
|
|
<?php endif; ?>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
<?php if (empty($data)): ?>
|
|
<tr><td colspan="7" class="text-center py-3"><?php echo __('no_records_found'); ?></td></tr>
|
|
<?php endif; ?>
|
|
</tbody>
|
|
<?php if (!empty($grandTotals) || isset($grandTotals['total_sales']) || isset($grandTotals['total_purchases'])): ?>
|
|
<tfoot>
|
|
<?php if ($type === 'inventory_valuation'): ?>
|
|
<tr class="fw-bold">
|
|
<td colspan="5" class="text-end"><?php echo __('grand_total'); ?>:</td>
|
|
<td><?php echo format_currency($grandTotals['total_cost'] ?? 0); ?></td>
|
|
<td><?php echo format_currency($grandTotals['total_sales'] ?? 0); ?></td>
|
|
</tr>
|
|
<?php elseif ($type === 'sales'): ?>
|
|
<tr class="fw-bold">
|
|
<td colspan="4" class="text-end"><?php echo __('grand_total'); ?>:</td>
|
|
<td colspan="2"><?php echo format_currency($grandTotals['total_sales'] ?? 0); ?></td>
|
|
</tr>
|
|
<?php elseif ($type === 'purchase_report'): ?>
|
|
<tr class="fw-bold">
|
|
<td colspan="4" class="text-end"><?php echo __('grand_total'); ?>:</td>
|
|
<td><?php echo format_currency($grandTotals['total_purchases'] ?? 0); ?></td>
|
|
</tr>
|
|
<?php endif; ?>
|
|
</tfoot>
|
|
<?php endif; ?>
|
|
</table>
|
|
</div>
|
|
</body>
|
|
</html>
|