COUNTRY INN & SUITES · PLUS
| DATE: DAY: |
BISMILAH
DAILY CASH SHEET
Daily_Sheet-2
connect_error) {
die(“❌ Connection failed: ” . $conn->connect_error);
}else
{
// echo(“Database Connected”);
}
$showAll = isset($_GET[‘show_all’]) ? 1 : 0;
if (isset($_GET[‘ajax’]) && $_GET[‘ajax’] === ‘customer_details’) {
$room = $conn->real_escape_string($_GET[‘room’]);
$sql = ”
SELECT
n.num_name AS room_no,
CONCAT(
JSON_UNQUOTE(JSON_EXTRACT(c.info,’$.first_name’)),
‘ ‘,
JSON_UNQUOTE(JSON_EXTRACT(c.info,’$.last_name’))
) AS name,
r.check_in,
r.check_out,
r.accom_price,
r.paid,
r.cash,
r.credit_card,
r.status
FROM wpoz_hb_resa r
JOIN wpoz_hb_accom_num_name n ON n.accom_num = r.accom_num
JOIN wpoz_hb_customers c ON c.id = r.customer_id
WHERE n.num_name = ‘$room’
ORDER BY r.check_in DESC
LIMIT 1
“;
$res = $conn->query($sql);
echo json_encode($res->fetch_assoc());
exit;
}
// Get total cash
// $totalCash = 0;
// $sumSql = “SELECT SUM(cash) AS total_cash FROM wpoz_hb_resa”;
// $sumResult = $conn->query($sumSql);
// if ($sumResult && $sumResult->num_rows > 0) {
// $row = $sumResult->fetch_assoc();
// $totalCash = $row[‘total_cash’] ?? 0;
// }
if ($_SERVER[‘REQUEST_METHOD’] === ‘POST’) {
$input = json_decode(file_get_contents(‘php://input’), true);
if (isset($input[‘ajax’]) && $input[‘ajax’] === ‘update_multiple’) {
foreach ($input[‘data’] as $room => $fields) {
foreach ($fields as $field => $value) {
$room = $conn->real_escape_string($room);
$field = $conn->real_escape_string($field);
$value = $conn->real_escape_string($value);
$allowed = [‘cash’, ‘credit_card’, ‘misc’, ‘tax’, ‘paid’];
if (!in_array($field, $allowed)) continue;
$update = ”
UPDATE wpoz_hb_resa r
JOIN wpoz_hb_accom_num_name n
ON n.accom_num = r.accom_num
SET r.$field = ‘$value’
WHERE n.num_name = ‘$room’
“;
$conn->query($update);
}
}
echo “success”;
exit;
}
}
$selectedDate = $_GET[‘selected_date’] ?? ”;
$safeDate = $selectedDate ? $conn->real_escape_string($selectedDate) : ”;
// $dateFilterSQL = ”;
if (!empty($selectedDate)) {
$safeDate = $conn->real_escape_string($selectedDate);
// Apply date filter
$dateFilterSQL = “AND r.check_in <= '$safeDate'
AND (r.check_out IS NULL OR r.check_out = '' OR r.check_out >= ‘$safeDate’)”;
} else {
// NO DATE → SHOW ALL DATA
$dateFilterSQL = “”;
}
// if (isset($_GET[‘show_all’]) && $_GET[‘show_all’] == 1) {
// $sql = “SELECT * FROM bookings ORDER BY room_no ASC”;
// } else {
// $sql = “SELECT * FROM bookings
// WHERE date = ‘$selected_date’
// ORDER BY room_no ASC”;
// }
// if (isset($_GET[‘show_all’])) {
// $safeDate = ”;
// $selectedDate = ”;
// }
// if (isset($_GET[‘update_data’])) {
// // Example update – change this to what you need
// $sql = “UPDATE wpoz_hb_accom_blocked SET updated_at = NOW()”;
// $conn->query($sql);
// echo ““;
// exit;
// }
$sql = ”
SELECT
n.num_name AS room_no,
CONCAT(
COALESCE(JSON_UNQUOTE(JSON_EXTRACT(c.info,’$.first_name’)),”),
CASE
WHEN JSON_UNQUOTE(JSON_EXTRACT(c.info,’$.last_name’)) IS NULL
THEN ”
ELSE CONCAT(‘ ‘,JSON_UNQUOTE(JSON_EXTRACT(c.info,’$.last_name’)))
END
) AS name,
COALESCE(r.accom_price,0) AS rent,
COALESCE(o.amount,0) AS misc_charges,
r.cash,
r.credit_card,
r.check_in,
r.check_out,
COALESCE(r.paid,0) AS paid,
(
COALESCE(r.accom_price,0)
+ (0.15 * COALESCE(r.accom_price,0))
+ COALESCE(o.amount,0)
– COALESCE(r.paid,0)
) AS balance_due,
r.status AS booking_status,
CASE
WHEN b.accom_num IS NOT NULL THEN 1
ELSE 0
END AS is_blocked,
b.id AS block_id
FROM wpoz_hb_accom_num_name n
/* —- RESERVATION JOIN (DATE SENSITIVE) —- */
LEFT JOIN wpoz_hb_resa r
ON r.accom_num = n.accom_num
AND (
‘$safeDate’ = ”
OR (
r.check_in <= '$safeDate'
AND (r.check_out IS NULL OR r.check_out > ‘$safeDate’)
)
)
LEFT JOIN wpoz_hb_customers c ON c.id = r.customer_id
LEFT JOIN wpoz_hb_options o ON o.id = r.id
/* —- BLOCKED JOIN (ALWAYS SHOW IF BLOCKED) —- */
/* —- BLOCKED ROOMS (Correct logic) —- */
LEFT JOIN wpoz_hb_accom_blocked b
ON b.accom_num = n.accom_num
AND (
(‘$safeDate’ <> ” AND ‘$safeDate’ BETWEEN b.from_date AND b.to_date)
OR
(‘$showAll’ = 1)
)
ORDER BY CAST(n.num_name AS UNSIGNED)”;
$result = $conn->query($sql);
// Build map (room_no => row data). Note: room numbers like “RV 1” may not match DB num_name; we’ll handle if DB has matching strings.
$dataMap = [];
if ($result && $result->num_rows > 0) {
while ($r = $result->fetch_assoc()) {
// extract only numbers from room_no so “D 105” → “105”
$key = preg_replace(‘/\D/’, ”, $r[‘room_no’]);
$dataMap[$key] = $r;
}
}
// Totals queries (for the selected date if present)
$totalsWhere = $selectedDate ? “WHERE DATE(check_in) = ‘” . $conn->real_escape_string($selectedDate) . “‘” : “”;
$totPaidSql = ”
SELECT SUM(COALESCE(r.paid, 0)) AS total_paid
FROM wpoz_hb_resa r
LEFT JOIN wpoz_hb_accom_num_name n ON n.accom_num = r.accom_num
WHERE 1 = 1
$dateFilterSQL
“;
$totMiscSql = “SELECT SUM(COALESCE(o.amount,0)) AS total_misc_all FROM wpoz_hb_options o JOIN wpoz_hb_resa r ON r.id = o.id ” . ($selectedDate ? “WHERE DATE(r.check_in) = ‘” . $conn->real_escape_string($selectedDate) . “‘” : “”);
$totCashSql = ”
SELECT SUM(COALESCE(r.paid,0)) AS total_cash_paid
FROM wpoz_hb_resa r
WHERE r.payment_type = ‘cash’
” . ($selectedDate ? “AND DATE(r.check_in) = ‘” . $conn->real_escape_string($selectedDate) . “‘” : “”);$totCreditSql = “SELECT SUM(CASE WHEN payment_type=’credit’ THEN COALESCE(paid,0) ELSE 0 END) AS total_credit_paid FROM wpoz_hb_resa ” . $totalsWhere;
$totPaid = $conn->query($totPaidSql)->fetch_assoc()[‘total_paid’] ?? 0;
// $totMisc = $conn->query($totMiscSql)->fetch_assoc()[‘total_misc_all’] ?? 0;
$totCash = $conn->query($totCashSql)->fetch_assoc()[‘total_cash_paid’] ?? 0;
$totCredit = $conn->query($totCreditSql)->fetch_assoc()[‘total_credit_paid’] ?? 0;
// $totSales = (float)$totPaid + (float)$totMisc;
// optional: PIA (paid in advance) calculation — simplistic: sum of (paid – amount_to_pay) positives
// /$piaSql = “SELECT SUM(CASE WHEN COALESCE(paid,0) > COALESCE(amount_to_pay,0) THEN (COALESCE(paid,0) – COALESCE(amount_to_pay,0)) ELSE 0 END) AS pia FROM wpoz_hb_resa ” . $totalsWhere;
// /$piaVal = $conn->query($piaSql)->fetch_assoc()[‘pia’] ?? 0;
// Open Acct (example placeholder — you can customize logic)
// $openAcctSql = “SELECT SUM(CASE WHEN COALESCE(paid,0) < COALESCE(amount_to_pay,0) THEN (COALESCE(amount_to_pay,0) - COALESCE(paid,0)) ELSE 0 END) AS open_acct FROM wpoz_hb_resa " . $totalsWhere;
// $openAcct = $conn->query($openAcctSql)->fetch_assoc()[‘open_acct’] ?? 0;
function fmt($val) {
return number_format((float)$val, 2);
}
?>
Daily Cash Sheet – PDF Accurate Layout