297 lines
9.8 KiB
PHP
297 lines
9.8 KiB
PHP
<?php
|
|
include('includes/config/config.php');
|
|
include('includes/functions.php');
|
|
|
|
|
|
$ohc = $_POST['iohcid'];
|
|
$program_name = $_POST['program_name'];
|
|
$activity_name = $_POST['activity_name'];
|
|
$village = $_POST['village'];
|
|
$agency = $_POST['agency'];
|
|
$startDate = date('Y-m-d', strtotime($_POST['startDate_i']));
|
|
$endDate = date('Y-m-d', strtotime($_POST['endDate_i']));
|
|
|
|
|
|
error_log("Ohc Name: " . $ohc);
|
|
error_log("Program Name: " . $program_name);
|
|
error_log("Activity Name: " . $activity_name);
|
|
error_log("Start date: " . $startDate);
|
|
error_log("End date: " . $endDate);
|
|
|
|
|
|
$type = isset($_GET['type']) ? $_GET['type'] : '';
|
|
error_log("Check_type : " . $type);
|
|
if ($type === 'excel') {
|
|
header("Content-Type: application/vnd.ms-excel");
|
|
header("Content-Disposition: attachment; filename=beneficiry_report_excel.xls");
|
|
header("Pragma: no-cache");
|
|
header("Expires: 0");
|
|
}
|
|
|
|
|
|
$sql = "SELECT si.*
|
|
FROM stock_issue si
|
|
JOIN patient_master pm ON si.beneficiary = pm.id
|
|
WHERE DATE(si.issue_date) BETWEEN ? AND ?
|
|
AND FIND_IN_SET($activity_name, REPLACE(si.activity_name, ' ', '')) > 0
|
|
-- AND si.activity_name = ?
|
|
AND si.distribution_vendor = ?
|
|
AND si.issue_ohc_type_id = ?";
|
|
|
|
if ($village !== "all") {
|
|
$sql .= " AND pm.village = ?";
|
|
}
|
|
|
|
|
|
$stmt = $conn->prepare($sql);
|
|
|
|
|
|
if (!$stmt) {
|
|
die("Error preparing query: " . $conn->error);
|
|
}
|
|
|
|
|
|
if ($village !== "all") {
|
|
|
|
$stmt->bind_param("ssiis", $startDate, $endDate, $agency, $ohc, $village);
|
|
} else {
|
|
|
|
$stmt->bind_param("ssii", $startDate, $endDate, $agency, $ohc);
|
|
}
|
|
|
|
if (!$stmt->execute()) {
|
|
die("Error executing query: " . $stmt->error);
|
|
}
|
|
|
|
$result = $stmt->get_result();
|
|
|
|
if (!$result) {
|
|
die("Error retrieving results: " . $stmt->error);
|
|
}
|
|
|
|
?>
|
|
|
|
<style>
|
|
@page {
|
|
margin: 15px;
|
|
}
|
|
|
|
.btn {
|
|
background-color: #4CAF50;
|
|
border-radius: 5%;
|
|
border: none;
|
|
color: white;
|
|
padding: 5px 8px;
|
|
text-align: center;
|
|
text-decoration: none;
|
|
display: inline-block;
|
|
font-size: 12px;
|
|
margin: 4px 2px;
|
|
cursor: pointer;
|
|
}
|
|
|
|
@media print {
|
|
#printPageButton {
|
|
display: none;
|
|
}
|
|
}
|
|
|
|
.table-fixed {
|
|
table-layout: fixed;
|
|
width: 100%;
|
|
}
|
|
|
|
.table-fixed td {
|
|
word-wrap: break-word;
|
|
}
|
|
</style>
|
|
|
|
<body>
|
|
<?php
|
|
if ($type === 'excel') {
|
|
} else {
|
|
include('pdf_current_ohc_header.php');
|
|
}
|
|
?>
|
|
<table width="100%">
|
|
<!-- 1st Row: Left Aligned -->
|
|
<tr align="left">
|
|
<td align="left" style="font-size: 12px">
|
|
<strong>Duration: <?php echo date("d-M-Y", strtotime($startDate)); ?> To <?php echo date("d-M-Y", strtotime($endDate)); ?></strong>
|
|
</td>
|
|
</tr>
|
|
|
|
<!-- 2nd Row: Center Aligned -->
|
|
<tr align="center">
|
|
<td style="font-size: 15px;">
|
|
<strong>
|
|
<?php
|
|
echo getFieldFromTable('program_name', 'csr_program', 'csr_id', $program_name) . '-' .
|
|
getFieldFromTable('program_name', 'program_master', 'program_id', $activity_name);
|
|
?>
|
|
</strong>
|
|
</td>
|
|
</tr>
|
|
<tr align="center">
|
|
<td style="font-size: 12px;">
|
|
<strong>
|
|
<?php
|
|
echo getFieldFromTable('agency_name', 'agency', 'id', $agency);
|
|
?>
|
|
</strong>
|
|
</td>
|
|
</tr>
|
|
|
|
<!-- 3rd Row: Right Aligned -->
|
|
<tr align="right">
|
|
|
|
<td align="right" style="font-size: 12px">
|
|
<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" class="table-fixed">
|
|
<tr>
|
|
<td style="width: 3%; font-size: 10px;">Sr.</td>
|
|
<td style="width: 10%; font-size: 10px;">Date</td>
|
|
<td style="width: 10%; font-size: 10px;">Rec. No.</td>
|
|
<td style="width: 15%; font-size: 10px;">Name of Beneficiaries</td>
|
|
<td style="width: 10%; font-size: 10px;">Mobile No.</td>
|
|
<td style="width: 10%; font-size: 10px;">Village</td>
|
|
<td style="width: 10%; font-size: 10px;">C/V O/V</td>
|
|
<td style="width: 10%; font-size: 10px;">Area (HA)</td>
|
|
<?php
|
|
|
|
|
|
$item_sql = "SELECT DISTINCT item_name FROM tbl_items WHERE activity = ? AND agency = ? AND ohc_type_id = ?";
|
|
$stmt = $conn->prepare($item_sql);
|
|
$stmt->bind_param("sss", $activity_name, $agency, $ohc);
|
|
$stmt->execute();
|
|
$item_result = $stmt->get_result();
|
|
|
|
$items = [];
|
|
while ($row = $item_result->fetch_assoc()) {
|
|
$items[] = $row['item_name'];
|
|
echo "<td style='width: 10%; font-size: 10px;'>{$row['item_name']}</td>";
|
|
}
|
|
?>
|
|
<td style="width: 10%; font-size: 10px;">Total</td>
|
|
<td style="width: 10%; font-size: 10px;">GHCL</td>
|
|
<td style="width: 10%; font-size: 10px;">Beneficiary Contribution</td>
|
|
</tr>
|
|
|
|
<?php
|
|
$count = 1;
|
|
$item_totals = array_fill(0, count($items), 0);
|
|
while ($row1 = $result->fetch_assoc()) {
|
|
$total_quantity = 0;
|
|
$total_amount = 0;
|
|
$total_subsidy = 0;
|
|
?>
|
|
<tr>
|
|
<td style="font-size: 10px;"><?php echo $count; ?></td>
|
|
<td style="font-size: 10px;"><?php echo date("d/m/Y", strtotime($row1['issue_date'])); ?></td>
|
|
<td style="font-size: 10px;"><?php echo $row1['issue_ref_no']; ?></td>
|
|
<td style="font-size: 10px;"><?php echo getFieldFromTable('patient_name', 'patient_master', 'id', $row1['beneficiary']); ?></td>
|
|
<td style="font-size: 10px;"><?php echo getFieldFromTable('aadhar_phone', 'patient_master', 'id', $row1['beneficiary']); ?></td>
|
|
<td style="font-size: 10px;"><?php echo getFieldFromTable('village', 'village', 'id', getFieldFromTable('village', 'patient_master', 'id', $row1['beneficiary'])); ?></td>
|
|
<td style="font-size: 10px;">Other</td>
|
|
<td style="font-size: 10px;"><?php echo getValue($row1['beneficiary'], 1); ?></td>
|
|
|
|
<?php
|
|
foreach ($items as $index => $item_name) {
|
|
$item_stock_sql = "SELECT issue_qty, total_rate, subsidy_percent FROM stock_issue_items
|
|
JOIN tbl_items ON stock_issue_items.item_id = tbl_items.item_id
|
|
WHERE stock_issue_id = ? AND item_name = ?";
|
|
$item_stmt = $conn->prepare($item_stock_sql);
|
|
$item_stmt->bind_param("is", $row1['stock_issue_id'], $item_name);
|
|
$item_stmt->execute();
|
|
$item_stock_result = $item_stmt->get_result();
|
|
|
|
$qty = 0;
|
|
$subsidy_percent = 0;
|
|
$total_rate = 0;
|
|
if ($item_stock_result) {
|
|
while ($row3 = $item_stock_result->fetch_assoc()) {
|
|
$qty += $row3['issue_qty'];
|
|
$subsidy_percent += $row3['subsidy_percent'];
|
|
$total_rate += $row3['total_rate'];
|
|
}
|
|
}
|
|
$total_quantity += $qty;
|
|
$total_amount += $total_rate;
|
|
$total_subsidy += $subsidy_percent;
|
|
|
|
|
|
$item_totals[$index] += $qty;
|
|
|
|
echo "<td style='font-size: 10px;'>$qty</td>";
|
|
}
|
|
?>
|
|
|
|
<td style="font-size: 10px;"><?php echo $total_amount; ?></td>
|
|
<td style="font-size: 10px;"><?php echo $total_subsidy; ?></td>
|
|
<td style="font-size: 10px;"><?php echo $total_amount - $total_subsidy; ?></td>
|
|
</tr>
|
|
<?php $count++;
|
|
$total_amount_sum += $total_amount;
|
|
$total_subsidy_sum += $total_subsidy;
|
|
$total_beneficiary_contribution_sum += ($total_amount - $total_subsidy);
|
|
} ?>
|
|
|
|
<!-- Row for Item Totals -->
|
|
<tr>
|
|
<td colspan="8" style="font-size: 10px; text-align: center;">Total</td>
|
|
<?php
|
|
// Display item totals
|
|
foreach ($item_totals as $item_total) {
|
|
echo "<td style='font-size: 10px;'>$item_total</td>";
|
|
}
|
|
?>
|
|
<td style="font-size: 10px;"><?php echo $total_amount_sum; ?></td>
|
|
<td style="font-size: 10px;"><?php echo $total_subsidy_sum; ?></td>
|
|
<td style="font-size: 10px;"><?php echo $total_beneficiary_contribution_sum; ?></td>
|
|
</tr>
|
|
</table>
|
|
|
|
|
|
<div style="text-align: center; margin-top: 20px;">
|
|
<button id="printPageButton" class="btn" onClick="window.print();">Print</button>
|
|
</div>
|
|
</body>
|
|
|
|
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
|
|
<script>
|
|
$(document).ready(function() {
|
|
|
|
});
|
|
</script>
|
|
<?php
|
|
function getValue($id, $key)
|
|
{
|
|
global $conn;
|
|
|
|
$id = mysqli_real_escape_string($conn, $id);
|
|
$key = mysqli_real_escape_string($conn, $key);
|
|
|
|
$sql = "SELECT checkup_form_value FROM beneficiary_form_key_value WHERE bene_detail_id = '$id' AND bene_form_key = '$key'";
|
|
|
|
error_log("Executing query: " . $sql);
|
|
|
|
$result = mysqli_query($conn, $sql);
|
|
|
|
if ($result) {
|
|
if ($row = mysqli_fetch_assoc($result)) {
|
|
error_log("Check_key+value : " . $row['checkup_form_value']);
|
|
return $row['checkup_form_value'];
|
|
} else {
|
|
return 'NA';
|
|
}
|
|
} else {
|
|
error_log("Error executing query: " . mysqli_error($conn));
|
|
return false;
|
|
}
|
|
}
|
|
?>
|