query($sql);
$vendors = [];
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$vendors[$row['id']] = $row['party_name'];
}
}
// Query for Transactions (Sales, Purchases, Expenses)
$query = "SELECT * FROM tbl_ah_items WHERE 1";
if (!empty($type)) {
$query .= " AND type = '$type'";
}
if (!empty($from_date) && !empty($to_date)) {
$query .= " AND DATE(created_date) BETWEEN '$from_date' AND '$to_date'";
} elseif (!empty($from_date)) {
$query .= " AND DATE(created_date) >= '$from_date'";
} elseif (!empty($to_date)) {
$query .= " AND DATE(created_date) <= '$to_date'";
}
$query .= " ORDER BY created_date DESC";
$result = mysqli_query($conn, $query);
$output = '';
$i = 1;
// Fetch Transaction Data
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$party_name = $vendors[$row['customer_id']] ?? 'Unknown';
$date = date("d-m-Y", strtotime($row['created_date']));
$output .= "
| {$i}. |
{$date} |
{$row['reference_no']} |
{$party_name} |
{$row['exp_cat_id']} |
{$row['type']} |
₹ {$row['total_amt']} |
₹ {$row['received_amount']} |
₹ {$row['balance']} |
|
";
$i++;
}
}
// 🏦 Fetch Payment Out (Purchase Payments)
$query_out = "SELECT * FROM ah_purchase_payment_out WHERE 1";
if (!empty($from_date) && !empty($to_date)) {
$query_out .= " AND DATE(created_date) BETWEEN '$from_date' AND '$to_date'";
}
$query_out .= " ORDER BY created_date DESC";
$result_out = mysqli_query($conn, $query_out);
if (mysqli_num_rows($result_out) > 0) {
while ($row = mysqli_fetch_assoc($result_out)) {
$party_name = $vendors[$row['customer_id']] ?? 'Unknown';
$date = date("d-m-Y", strtotime($row['created_date']));
$output .= "
| {$i}. |
{$date} |
{$row['receipt_no']} |
{$party_name} |
Payment-Out |
{$row['payment_type']} |
₹ {$row['total_amount']} |
₹ {$row['paid_amount']} |
₹ {$row['balance']} |
|
";
$i++;
}
}
// 💰 Fetch Payment In (Sales Payments)
$query_in = "SELECT * FROM ah_sales_payment_in WHERE 1";
if (!empty($from_date) && !empty($to_date)) {
$query_in .= " AND DATE(created_date) BETWEEN '$from_date' AND '$to_date'";
}
$query_in .= " ORDER BY created_date DESC";
$result_in = mysqli_query($conn, $query_in);
if (mysqli_num_rows($result_in) > 0) {
while ($row = mysqli_fetch_assoc($result_in)) {
$party_name = $vendors[$row['customer_id']] ?? 'Unknown';
$date = date("d-m-Y", strtotime($row['created_date']));
$output .= "
| {$i}. |
{$date} |
{$row['receipt_no']} |
{$party_name} |
Payment-In |
{$row['payment_type']} |
₹ {$row['total_amount']} |
₹ {$row['received_amonut']} |
₹ {$row['balance']} |
|
";
$i++;
}
}
// If No Records Found
if ($output == '') {
$output = "| No transactions found. |
";
}
echo json_encode(['orders' => $output]);
?>