<?php  include_once('../../config/db.php');   // include_once('../config/check.php'); 


$startdate =           htmlspecialchars ($_POST['startdate']);
$startdate =            date("Y-m-d", strtotime($startdate));
 // $startdate =            '2025-01-01' ; date("Y-m-d", strtotime($startdate));

$enddate =            htmlspecialchars ($_POST['enddate']);
$enddate = date("Y-m-d", strtotime("+1 day , $enddate"));
 //$enddate =  '2025-02-09';  date("Y-m-d", strtotime("+1 day , $enddate"));

  
$query = "  SELECT *  FROM 
                (
                     SELECT a.mycoa id,mycoa,a.date,a.partner,a.branch,a.memo,a.description,a.currency,a.erate,a.mycoa coa,b.name bname,b.type,
                           b.cgroup,c.id cid, c.name cname ,c.accoption accoption ,c.f_option cfoption , d.name dname,debit,credit,
                          CASE  WHEN (`c`.`accoption` = 'dr') THEN a.debit- a.credit
                          WHEN (`c`.`accoption` = 'cr') THEN a.credit- a.debit END AS total 
                       FROM
			                      ( 
                                  -- All transaction start
                                   SELECT a.id,a.transid,a.coa mycoa,a.branch,a.partner, (a.total-a.itemtax) debit,'' credit,b.date,b.memo,b.transtype,a.currency,a.erate ,b.description description FROM acctransactionsdetails a 
                                                    JOIN acctransactions b ON a.transid=b.id WHERE transclass=1       
                                    UNION 
                                   
                                    SELECT a.id,a.transid,a.coa coa,a.branch,a.partner, '' debit,a.total credit,b.date,b.memo,b.transtype,a.currency,a.erate ,b.description description FROM acctransactionsdetails a 
                                                JOIN acctransactions b ON a.transid=b.id WHERE transclass=2 
                                               

                                    UNION  -- vat
                                    SELECT id,transid, 8 mycoa,branch,partner,debit,credit,date,memo,transtype,currency,erate,description FROM 
                                        (
                                           SELECT a.id,a.transid,a.coa coa,a.branch,a.partner, a.total debit,'' credit,a.transmove,a.total,b.date,b.memo,b.transtype,a.currency,a.erate ,b.description description FROM acctransactionsdetails a 
                                             JOIN acctransactions b ON a.transid=b.id WHERE  b.transtype=6 OR b.transtype=15 OR b.transtype=16
                                                 UNION 
                                             SELECT a.id,a.transid,a.coa coa,a.branch,a.partner, '' debit,a.total credit,a.transmove,a.total,b.date,b.memo,b.transtype,a.currency,a.erate ,b.description description FROM acctransactionsdetails a 
                                             JOIN acctransactions b ON a.transid=b.id WHERE   b.transtype=10 OR b.transtype=18 
                                        )v WHERE v.transmove=4 AND v.total>0

                                   -- All transaction ends
                          
                            )a  JOIN  coa b ON  a.mycoa=b.id 
                                JOIN coa_accounts c ON b.type= c.id
                                JOIN coa_group d ON b.cgroup=d.id
         
                ) z where z.date  >= '$startdate' and z.date < '$enddate' and z.total != 0   
                       ORDER BY z.mycoa ,z.date asc
                         

           " ;



$statement = $conn->prepare($query);

$statement->execute();

$number_filter_row = $statement->rowCount();

$statement = $conn->prepare($query );

$statement->execute();

$result = $statement->fetchAll();

$data = array();

// $mydr=0;
// $mycr=0;

foreach($result as $row)
{
  
    $total = ' ' ; 
    $edit = ' <form method="post" action="#" >  <button type="submit" style="background:none;border:none;padding:0px; outline:none; " name="edit" value="' . $row["cid"] . '"   "> <i class="fa fa-circle"></i>  </button></form> ' ; 
    $print = ' <form method="post" action="Report/expense.php" target="__blank">  <button type="submit" style="background:none;border:none;padding:0px; outline:none; " name="print" value="' . $row["id"] . '"   "> <i class="fa fa-print"></i>  </button></form> ' ; 
    
     $sub_array = array();
  
  
     $sub_array[] = $row['bname'];
     $sub_array[] = $row['dname'];
     $sub_array[] =  $row['cname'];
 
    //  $sub_array[] = $row['name'];
      $sub_array[] = $row['date'];
      $sub_array[] = $row['erate'];
      $sub_array[] = $row['memo'];
     $sub_array[] =  $row['debit'] ;
     $sub_array[] =  $row['credit'] ;
     $sub_array[] =  $total;
   
    
    
   
      $data[] = $sub_array; 

}

function count_all_data($conn,$query)
{
 $statement = $conn->prepare($query);
 $statement->execute();
 return $statement->rowCount();
}

$output = array(
                       "draw"       =>  $number_filter_row,
                        "recordsTotal"   =>  count_all_data($conn,$query),
                        "recordsFiltered"  =>  $number_filter_row,
                        "data"       =>  $data
);

echo json_encode($output);

?>