208 lines
8.8 KiB
PHP
208 lines
8.8 KiB
PHP
<?php include('includes/config/config.php');
|
|
include('includes/functions.php');
|
|
$ohc = isset($_POST['ohctype']) ? (int) $_POST['ohctype'] : 0;
|
|
$agency = isset($_POST['agency']) ? $_POST['agency'] : 0;
|
|
$date = isset($_POST['startDate_sales']) ? mysqli_real_escape_string($conn, $_POST['startDate_sales']) : '';
|
|
$type = isset($_GET['type']) ? mysqli_real_escape_string($conn, $_GET['type']) : '';
|
|
if ($type === 'excel') {
|
|
header("Content-Type: application/vnd.ms-excel");
|
|
header("Content-Disposition: attachment; filename=annual_report.xls");
|
|
header("Pragma: no-cache");
|
|
header("Expires: 0");
|
|
} ?>
|
|
|
|
<?php
|
|
function getHistoricalRate($item_id, $for_date) {
|
|
global $conn;
|
|
|
|
|
|
$for_date = date('Y-m-d', strtotime($for_date));
|
|
|
|
|
|
$sql = "SELECT * FROM item_rate_change_history
|
|
WHERE item_id = '$item_id'
|
|
AND DATE(last_modified) >= '$for_date'
|
|
ORDER BY last_modified DESC
|
|
LIMIT 1";
|
|
error_log($sql."history item rate");
|
|
|
|
$result = mysqli_query($conn, $sql);
|
|
|
|
if ($result && mysqli_num_rows($result) > 0) {
|
|
|
|
$row = mysqli_fetch_assoc($result);
|
|
|
|
|
|
$new_rate = $row['old_rate'];
|
|
$subsidy = $row['old_subsidy_percent'];
|
|
|
|
|
|
$final_rate = $new_rate - $subsidy;
|
|
|
|
return $final_rate;
|
|
}
|
|
|
|
|
|
$sql_current = "SELECT mrp, subsidy_percent FROM item_rate
|
|
WHERE item_id = '$item_id'";
|
|
$result_current = mysqli_query($conn, $sql_current);
|
|
|
|
if ($result_current && mysqli_num_rows($result_current) > 0) {
|
|
$row = mysqli_fetch_assoc($result_current);
|
|
$mrp = $row['mrp'];
|
|
$subsidy = (float)$row['subsidy_percent'];
|
|
|
|
return $mrp - $subsidy;
|
|
}
|
|
|
|
|
|
return 0;
|
|
}
|
|
?>
|
|
<link href="includes/css-js/admin.css" rel="stylesheet" type="text/css" />
|
|
<style>
|
|
@media print {
|
|
#printPageButton {
|
|
display: none;
|
|
}
|
|
}
|
|
</style>
|
|
|
|
<body style="padding: 10px;">
|
|
<?php if ($type !== 'excel') include('pdf_ohc_header.php'); ?>
|
|
<div style="text-align: center; margin-bottom: 20px;">
|
|
<h2>Daily Sales Report</h2>
|
|
</div>
|
|
<table width="100%">
|
|
<tr>
|
|
<td><strong>Date: <?= $date ?></strong></td>
|
|
<td align="right"><strong>Selected Location: <?= htmlspecialchars(getFieldFromTable('ohc_type_name', 'ohc_type', 'ohc_type_id', $ohc)) ?></strong></td>
|
|
</tr>
|
|
</table>
|
|
<br>
|
|
|
|
<table border="1" width="100%" cellspacing="0" style="border-collapse: collapse;">
|
|
<thead>
|
|
<tr bgcolor="#eeeeee">
|
|
<th rowspan="2">Sr.No</th>
|
|
<th rowspan="2">Product Name</th>
|
|
<th colspan="3">Opening Stock</th>
|
|
<th colspan="3">Inward</th>
|
|
<th colspan="3">Sales</th>
|
|
<th colspan="3">Closing Stock</th>
|
|
</tr>
|
|
<tr>
|
|
<th>QTY</th>
|
|
<th>Rate</th>
|
|
<th>Amount</th>
|
|
<th>QTY</th>
|
|
<th>Rate</th>
|
|
<th>Amount</th>
|
|
<th>QTY</th>
|
|
<th>Rate</th>
|
|
<th>Amount</th>
|
|
<th>QTY</th>
|
|
<th>Rate</th>
|
|
<th>Amount</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php
|
|
$agency = implode(",", $agency);
|
|
$sql1 = "SELECT * FROM agency WHERE id IN ($agency)";
|
|
$result1 = mysqli_query($conn, $sql1);
|
|
$count = 1;
|
|
$gt1 = $gt2 = $gt3 = $gt4 = 0;
|
|
$g_qty_opening = $g_qty_inward = $g_qty_sales = $g_qty_closing = 0;
|
|
|
|
while ($row1 = mysqli_fetch_assoc($result1)) {
|
|
$sql2 = "SELECT * FROM tbl_items WHERE RoleCode = 'CSR' AND agency = '" . $row1['id'] . "' AND ohc_type_id = $ohc";
|
|
$result2 = mysqli_query($conn, $sql2);
|
|
echo "<tr style='background-color:#66e7e9;'><th colspan='3'>{$row1['agency_name']}</th><td colspan='11'></td></tr>";
|
|
$total1 = $total2 = $total3 = $total4 = 0;
|
|
$qty_opening = $qty_inward = $qty_sales = $qty_closing = 0;
|
|
|
|
while ($row2 = mysqli_fetch_assoc($result2)) {
|
|
// CORRECT RATE CALCULATION WITH HISTORY
|
|
$date_fmt = (new DateTime($date))->format('Y-m-d');
|
|
$current_rate = getHistoricalRate($row2['item_id'], $date_fmt);
|
|
|
|
$rate1 = $current_rate;
|
|
$rate2 = $current_rate;
|
|
$rate3 = $current_rate;
|
|
$rate4 = $current_rate;
|
|
|
|
// Opening Stock
|
|
$op_sql = "SELECT a.stock_qty as qty FROM item_stock_daily_balance a LEFT JOIN item_stock b ON a.item_id = b.item_id AND a.item_batch_no = b.item_batch_no WHERE a.item_id = '{$row2['item_id']}' AND a.record_date = '$date_fmt' AND b.ohc_type_id = '$ohc'";
|
|
$op_res = mysqli_query($conn, $op_sql);
|
|
$op_qty = 0;
|
|
while ($r = mysqli_fetch_assoc($op_res)) $op_qty += $r['qty'];
|
|
$cost1 = $op_qty * $rate1;
|
|
$qty_opening += $op_qty;
|
|
$total1 += $cost1;
|
|
|
|
// Inward
|
|
$inw_sql = "SELECT * FROM procurement p LEFT JOIN procurement_items pi ON p.procurement_id = pi.procurement_id WHERE pi.item_id = '{$row2['item_id']}' AND p.procurement_date = '$date_fmt' AND p.ohc_type_id = '$ohc'";
|
|
$inw_res = mysqli_query($conn, $inw_sql);
|
|
$inw_qty = 0;
|
|
while ($r = mysqli_fetch_assoc($inw_res)) $inw_qty += $r['qty'];
|
|
$cost2 = $inw_qty * $rate2;
|
|
$qty_inward += $inw_qty;
|
|
$total2 += $cost2;
|
|
|
|
// Sales
|
|
$sale_sql = "SELECT * FROM stock_issue si LEFT JOIN stock_issue_items sii ON si.stock_issue_id = sii.stock_issue_id WHERE sii.item_id = '{$row2['item_id']}' AND si.issue_date = '$date_fmt' AND si.issue_ohc_type_id = '$ohc'";
|
|
$sale_res = mysqli_query($conn, $sale_sql);
|
|
$sale_qty = 0;
|
|
while ($r = mysqli_fetch_assoc($sale_res)) $sale_qty += $r['issue_qty'];
|
|
$cost3 = $sale_qty * $rate3;
|
|
$qty_sales += $sale_qty;
|
|
$total3 += $cost3;
|
|
|
|
// Closing Stock
|
|
$cls_sql = "SELECT a.stock_qty as qty FROM item_stock_daily_balance a LEFT JOIN item_stock b ON a.item_id = b.item_id AND a.item_batch_no = b.item_batch_no WHERE a.item_id = '{$row2['item_id']}' AND a.record_date = '$date_fmt' AND b.ohc_type_id = '$ohc'";
|
|
$cls_res = mysqli_query($conn, $cls_sql);
|
|
$cls_qty = 0;
|
|
while ($r = mysqli_fetch_assoc($cls_res)) $cls_qty += $r['qty'];
|
|
$cost4 = $cls_qty * $rate4;
|
|
$qty_closing += $cls_qty;
|
|
$total4 += $cost4;
|
|
|
|
echo "<tr>
|
|
<td align='center'>{$count}</td>
|
|
<td>{$row2['item_name']}/{$row2['item_id']}</td>
|
|
<td>{$op_qty}</td><td>{$rate1}</td><td>{$cost1}</td>
|
|
<td>{$inw_qty}</td><td>{$rate2}</td><td>{$cost2}</td>
|
|
<td>{$sale_qty}</td><td>{$rate3}</td><td>{$cost3}</td>
|
|
<td>{$cls_qty}</td><td>{$rate4}</td><td>{$cost4}</td>
|
|
</tr>";
|
|
$count++;
|
|
}
|
|
echo "<tr style='background-color:#ffff99; font-weight:bold;'>
|
|
<td colspan='2'>Qty Total:</td>
|
|
<td><b>{$qty_opening}</b></td><td></td><td></td>
|
|
<td><b>{$qty_inward}</b></td><td></td><td></td>
|
|
<td><b>{$qty_sales}</b></td><td></td><td></td>
|
|
<td><b>{$qty_closing}</b></td><td></td><td></td>
|
|
</tr>";
|
|
echo "<tr style='background-color:#cbf4cc; font-weight:bold;'>
|
|
<td colspan='2'>Amount Total:</td>
|
|
<td colspan='1'></td><td></td><td><b>{$total1}</b></td>
|
|
<td colspan='1'></td><td></td><td><b>{$total2}</b></td>
|
|
<td colspan='1'></td><td></td><td><b>{$total3}</b></td>
|
|
<td colspan='1'></td><td></td><td><b>{$total4}</b></td>
|
|
</tr>";
|
|
|
|
$gt1 += $total1;
|
|
$gt2 += $total2;
|
|
$gt3 += $total3;
|
|
$gt4 += $total4;
|
|
$g_qty_opening += $qty_opening;
|
|
$g_qty_inward += $qty_inward;
|
|
$g_qty_sales += $qty_sales;
|
|
$g_qty_closing += $qty_closing;
|
|
}
|
|
?>
|
|
</tbody>
|
|
</table>
|
|
</body>
|