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



    $startdate =           htmlspecialchars (strtoupper($_POST['startdate']));
    $startdate =            date("Y-m-d", strtotime($startdate));
    $enddate =            htmlspecialchars (strtoupper($_POST['enddate']));
    $enddate = date("Y-m-d", strtotime("+1 day , $enddate"));
    
    $query = " SELECT  * from 
                (
                        SELECT a.mycoa id,mycoa,b.name bname,b.type,b.cgroup, c.id cid, c.name cname ,c.accoption accoption ,c.f_option cfoption , d.name dname ,
                        (case when (`c`.`accoption` = 'dr') then (sum(debit) - sum(`a`.`credit`)) else 0 end) AS mydebit,
                        (case when (`c`.`accoption` = 'cr') then (sum(`a`.`credit`) - sum(`a`.`debit`)) else 0 end) AS mycredit 
                        FROM ( 
                                      -- ALL transactions 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
                                -- where a.date  >= '$startdate' and a.date < '$enddate'  
                                       GROUP BY a.mycoa 
                            
                                
                )za     order by cid,cgroup  asc           


      " ;



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

$statement->execute();

$number_filter_row = $statement->rowCount();

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

$statement->execute();
$result = $statement->fetchAll();
$data = array();



foreach($result as $row)
{
  
    $edit = ' <form method="post" action="#" >  <button type="submit" style="background:none;border:none;padding:0px; outline:none; " name="edit" value="' . $row["id"] . '"   "> <i class="fa fa-edit" style="color:green;" ></i>  </button></form> ' ; 
    $delete = ' <form method="post" action="#" >  <button type="submit" style="background:none;border:none;padding:0px; font-color:red; outline:none; " name="delete" value="' . $row["id"] . '"   "> <i class="fa fa-trash" style="color:red;" ></i>  </button></form> ' ; 
    
    
     $sub_array = array();
     $sub_array[] = $row['cname'];
     $sub_array[] = $row['dname'];
     $sub_array[] = $row['bname'];
     $sub_array[] =  number_format(( $row['mydebit'] ), 2)    ;
     $sub_array[] =  number_format(( $row['mycredit'] ), 2)    ;
     
     $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);

?>



