<?php
// array for JSON response
$response = array();

// include db connect class
require_once __DIR__ . '/../DBConnect.php';

if (isset($_POST['Date']) && isset($_POST['CountryClientID']) && isset($_POST['CountryUserID'])) {

    $CountryClientID = $_POST['CountryClientID'];
    $CountryUserID = $_POST['CountryUserID'];
    $date = $_POST['Date'];
    
    $sql = "Select
    Account3Name.AcNameID As AccountID,
    AccountBalanct.Debit,
    AccountBalanct.Credit,
    AccountBalanct.Bal,
    AccountBalanct.DebitBal,
    AccountBalanct.CreditBal,
    Account3Name.AcName,
    Account3Name.UpdatedDate,
    Account3Name.AcGroupID,
    Account3Name.ClientID,
    Account3Name.AcContactNo,
    Account3Name.AcMobileNo,
    Account2Group.AcGruopName
From
    Account3Name Left Join
    (Select
         GroupTotal.AccountID,
         Sum(GroupTotal.Debit) As Debit,
         Sum(GroupTotal.Credit) As Credit,
         Sum(GroupTotal.Debit) - Sum(GroupTotal.Credit) As Bal,
         Case
             When (Sum(GroupTotal.Debit) - Sum(GroupTotal.Credit)) > 0
             Then (Sum(GroupTotal.Debit) - Sum(GroupTotal.Credit))
             Else 0
         End As DebitBal,
         Case
             When (Sum(GroupTotal.Debit) - Sum(GroupTotal.Credit)) < 0
             Then (Sum(GroupTotal.Debit) - Sum(GroupTotal.Credit))
             Else 0
         End As CreditBal,
         GroupTotal.ClientID
     From
         (Select
              CashBook.DebitAccount As AccountID,
              CashBook.Amount As Debit,
              0 As Credit,
              CashBook.ClientID
          From
              CashBook
          Where
              CashBook.ClientID = '".$CountryClientID."' And
              CashBook.DebitAccount = '".$CountryUserID."' And
              CashBook.CBDate <= '".$date." '
          Union All
          Select
              CashBook_1.CreditAccount As AccountID,
              0 As Debit,
              CashBook_1.Amount As Credit,
              CashBook_1.ClientID
          From
              CashBook As CashBook_1
          Where
              CashBook_1.ClientID = '".$CountryClientID."' And
              CashBook_1.CreditAccount = '".$CountryUserID."' And
              CashBook_1.CBDate <= '".$date."') As GroupTotal
     Group By
         GroupTotal.AccountID,
         GroupTotal.ClientID) As AccountBalanct On Account3Name.AcNameID = AccountBalanct.AccountID
            And AccountBalanct.ClientID = Account3Name.ClientID Left Join
    Account2Group On Account2Group.AcGroupID = Account3Name.AcGroupID
            And Account2Group.ClientID = Account3Name.ClientID
Where
    Account3Name.ClientID = '".$CountryClientID."' And
    AccountBalanct.AccountID = '".$CountryUserID."'";

    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if ($result != null &&  $result > 0) {
        foreach ($result as $row) {
            $response["Bal"] = $row["Bal"];
            $response["AccountID"] = $row["AccountID"];
        }
        // success
        $response["success"] = 1;
        // echoing JSON response
        echo json_encode($response);
    } else {
        $response["success"] = 0;
        $response["message"] = "No Balance";
        // echoing JSON response
        echo json_encode($response);
    }
} else {
    $response["success"] = 0;
    $response["message"] = "Set ClientID First ";
    // echoing JSON response
    echo json_encode($response);
}
