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

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

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

    $CountryClientID = $_POST['CountryClientID'];
    $CountryUserID = $_POST['CountryUserID'];
    $From = $_POST['From'];
    $To = $_POST['To'];
    
    $From = date("Y-m-d", strtotime($From)); 
    $To = date("Y-m-d", strtotime($To)); 
    
    $sql = "SELECT ClientID, 0 AS EntryNo, MAX(CBDate) AS Date, 0 AS SelectedAc, 0 AS AgainstAc, '' AS AccountName, 'B/F' AS Particulars, SUM(Debit) AS Debit, SUM(Credit) AS Credit, '' AS EntryOf, SUM(Debit) - SUM(Credit) AS Balance, '' As TableName, '' As TableID, '' As User   
                             FROM            (Select 
            CashBook.ClientID, 
            CashBook.CashBookID, 
            CashBook.CBDate, 
            CashBook.DebitAccount As SelectedAc, 
            CashBook.CreditAccount, 
            '' As AcName, 
            CashBook.CBRemarks, 
            CashBook.Amount As Debit, 
            0 As Credit, 
            'CB' As EntryOf, 
            '' As TableName, 
            '' As TableID, 
            '' As User 
        From 
            CashBook 
        Where 
            CashBook.ClientID = '$CountryClientID' And 
            CashBook.CBDate < '$To' And 
            CashBook.DebitAccount = '$CountryUserID' 
                                                       UNION ALL     
                                                      Select 
            CashBook_1.ClientID, 
            CashBook_1.CashBookID, 
            CashBook_1.CBDate, 
            CashBook_1.CreditAccount As SelectedAc, 
            CashBook_1.DebitAccount, 
            '' As AcName, 
            CashBook_1.CBRemarks, 
            0 As Debit, 
            CashBook_1.Amount As Credit, 
            'CB' As EntryOf, 
            '' As TableName, 
            CashBook_1.TableID As TableID, 
            '' As User 
        From 
            CashBook As CashBook_1 
        Where 
            CashBook_1.ClientID = '$CountryClientID' And 
            CashBook_1.CBDate < '$To' And 
            CashBook_1.CreditAccount = '$CountryUserID') AS OpQuery 
                             GROUP BY ClientID  
         Union ALL 
          
        Select 
            CashBook_2.ClientID, 
            CashBook_2.CashBookID, 
            CashBook_2.CBDate, 
            CashBook_2.DebitAccount As SelectedAc, 
            CashBook_2.CreditAccount As AgainsAc, 
            Account3Name.AcName, 
            CashBook_2.CBRemarks, 
            CashBook_2.Amount As Debit, 
            0 As Credit, 
            'CB' As EntryOf, 
            CashBook_2.Amount As Balance, 
            CashBook_2.TableName, 
            CashBook_2.TableID, 
            Account3Name_2.AcName As User 
        From 
            CashBook As CashBook_2 Left Join 
            Account3Name As Account3Name_2 On Account3Name_2.ClientID = CashBook_2.ClientID 
                    And Account3Name_2.AcNameID = CashBook_2.ClientUserID Left Join 
            Account3Name On Account3Name.ClientID = CashBook_2.ClientID 
                    And Account3Name.AcNameID = CashBook_2.CreditAccount 
        Where 
            CashBook_2.ClientID = '$CountryClientID' And 
            CashBook_2.CBDate >= '$From' And 
            CashBook_2.CBDate <= '$To' And 
            CashBook_2.DebitAccount = '$CountryUserID' 
             
            UNION ALL     
                            Select 
            CashBook_3.ClientID, 
            CashBook_3.CashBookID, 
            CashBook_3.CBDate, 
            CashBook_3.CreditAccount As SelectedAc, 
            CashBook_3.DebitAccount As AgainstAc, 
            Account3Name.AcName, 
            CashBook_3.CBRemarks, 
            0 As Debit, 
            CashBook_3.Amount As Credit, 
            'CB' As EntryOf, 
            -CashBook_3.Amount As Balance, 
            CashBook_3.TableName, 
            CashBook_3.TableID, 
            Account3Name_3.AcName As User 
        From 
            CashBook As CashBook_3 Left Join 
            Account3Name As Account3Name_3 On Account3Name_3.ClientUserID = CashBook_3.ClientID 
                    And Account3Name_3.AcNameID = CashBook_3.ClientUserID Left Join 
            Account3Name On Account3Name.ClientID = CashBook_3.ClientID 
                    And Account3Name.AcNameID = CashBook_3.DebitAccount 
        Where 
            CashBook_3.ClientID = '$CountryClientID' And 
            CashBook_3.CBDate >= '$From' And 
            CashBook_3.CBDate <= '$To' And 
            CashBook_3.CreditAccount = '$CountryUserID'";

    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if ($result != null &&  $result > 0) {
        $response["CashBook"] = array();
        foreach ($result as $row) {
          $CashBook["ClientID"] = $row["ClientID"];
          $CashBook["EntryNo"] = $row["EntryNo"];
          $CashBook["Date"] = $row["Date"];
          $CashBook["SelectedAc"] = $row["SelectedAc"];
          $CashBook["AgainstAc"] = $row["AgainstAc"];
          $CashBook["AccountName"] = $row["AccountName"];
          $CashBook["Particulars"] = $row["Particulars"];
          $CashBook["Debit"] = $row["Debit"];
          $CashBook["Credit"] = $row["Credit"];
		  $CashBook["EntryOf"] = $row["EntryOf"];
         $CashBook["Balance"] = $row["Balance"];
		  $CashBook["TableName"] = $row["TableName"];
		  $CashBook["TableID"] = $row["TableID"];
            
            // push single product into final response array
          array_push($response["CashBook"], $CashBook);
        }
        // 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);
}
