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


// new PDO("mysql:host=localhost;dbname=testing", "root", "");



$column = array('startdate','enddate', 'partner', 'memo', 'description','subtotal','tax','total' );

$query = "     ";

if(isset($_POST['type'])   && $_POST['type'] != '' )
{
    $gender= $_POST['type'] ;
    
    $query = " SELECT j.*
    FROM  (
    
        SELECT a.id,a.transactionid,a.myid,z.memo,z.debit,z.credit,z.coa,z.currency,z.erate,z.memo,z.creditgl,z.debitgl,z.date FROM bank_reconcile_item a 
LEFT JOIN (
Select * from (
               
                          SELECT      CASE WHEN h.id=h.tid  THEN  tid WHEN  h.id != h.tid THEN tid*-1 END AS myid, h.*,b.type btype FROM ( 
                -- multiple journal and banks transfer with different currency 
                        SELECT id id,transactionid tid,coa,date,TYPE transtype,memo,currency,erate,description,COALESCE(debit, 0)debitgl, COALESCE(credit , 0)creditgl ,COALESCE(debit, 0)debit, COALESCE(credit , 0)credit 
                        FROM  (							   
                                  SELECT a.id,a.transactionid,b.dr coa, b.amount debit,'' credit,b.date,b.memo,b.type,b.currency,b.erate ,b.description description FROM acctransaction_item a 
                                JOIN acctransaction b ON a.transactionid=b.id   WHERE  split=2 and b.type=5 AND a.glamount>0
                                UNION         
                                  SELECT a.id,a.transactionid,a.coa coa, '' debit,a.total credit,b.date,b.memo,b.type,b.currency,b.erate ,b.description description FROM acctransaction_item a 
                                JOIN acctransaction b ON a.transactionid=b.id WHERE  split=2 and b.type=5  AND a.glamount>0
                          )fB  
                          UNION 

                        SELECT id id,transactionid tid,coa,date,TYPE transtype,memo,currency,erate,description,COALESCE(debit, 0)debitgl, COALESCE(credit , 0)creditgl ,COALESCE(debit, 0)debit, COALESCE(credit , 0)credit 
                               FROM  (							   
                                         SELECT a.id,a.transactionid,a.sdebit coa, a.glamount debit,'' credit,b.date,b.memo,b.type,b.currency,b.erate ,b.description description FROM acctransaction_item a 
                                         JOIN acctransaction b ON a.transactionid=b.id   WHERE  split=2 and b.type=3 
                                       UNION         
                                         SELECT a.id,a.transactionid,a.scredit coa, '' debit,a.glamount credit,b.date,b.memo,b.type,b.currency,b.erate ,b.description description FROM acctransaction_item a 
                                         JOIN acctransaction b ON a.transactionid=b.id WHERE  split=2 and b.type=3   
                                   )m    	     
                       UNION  
                            SELECT  id,tid,coa,date,transtype,memo,currency,erate,description,debitgl,creditgl,debit,credit 
                          FROM (                
                                    SELECT id id,transactionid tid,date,TYPE transtype,memo,currency,erate,description,coa,COALESCE((jdebit* erate), 0)debitgl, COALESCE((jcredit * erate), 0)creditgl,
                                                                          COALESCE(jdebit, 0)debit, COALESCE(jcredit , 0)credit 
                                   FROM  (SELECT a.*,b.date,b.memo,b.type,b.currency,b.erate ,b.description description FROM acctransaction_item a 
                                             JOIN acctransaction b ON a.transactionid=b.id
                                             WHERE  split=2 and b.type=1 
                                                     )e     
                                           )n
                                           
                                UNION  
                                                 SELECT  tid,id,coa,date,transtype,memo,currency,erate,description,debitgl,creditgl ,debit,credit
                                    FROM (  SELECT id ,transactionid tid,date,TYPE transtype,memo,z.jcurrency currency,z.jerate erate,description,coa,COALESCE((jdebit*jerate), 0)debitgl, 
                                                COALESCE((jcredit*jerate), 0)creditgl ,COALESCE((jdebit), 0)debit, 
                                                COALESCE((jcredit), 0)credit
                                              FROM(
                                                    SELECT a.*,b.date,b.memo,b.type ,b.description description FROM acctransaction_item a 
                                                    JOIN acctransaction b ON a.transactionid=b.id
                                                     WHERE  split=2 and b.type=2
                                                                )z   
                                         ) o                                                      	
                                union							 
                                -- SINGLE JOURNALS ,PAYMENTS 
                           SELECT id,tid,coa,date,transtype ,memo,currency,erate,description,debitgl,creditgl ,debit ,credit
                           FROM
                                ( SELECT a.id id,a.id tid,a.date DATE,a.type transtype,a.memo memo,a.currency currency,a.erate erate,a.description description,a.dr coa ,a.glamount debitgl ,'' creditgl,a.amount debit ,'' credit  FROM acctransaction a where a.split= 0 
                                                   UNION 
                                                 SELECT a.id id,a.id tid,a.date date,a.type transtype,a.memo memo,a.currency currency,a.erate erate,a.description description,a.cr coa ,'' debitgl , a.glamount creditgl,'' debit , a.amount credit  FROM acctransaction a  WHERE a.split= 0 
                                )  p 
                                
                          UNION 	                           	                           
                         -- SALES EXPESNES BILLSFI ASSETS PURCHASES LANDING COST
                            SELECT id,tid,coa,date,transtype,memo,currency,erate,description,debitgl,creditgl ,debit,credit
                           FROM (
                                          SELECT id,tid,date,transtype,memo,currency,erate,description,coa,debitgl,creditgl ,debit,credit
                                         FROM
                                           (  select c.id id, c.id tid, c.date DATE,c.type transtype,  c.memo memo,c.currency currency,c.erate erate,   c.description description ,c.cr coa ,c.glamount creditgl,'' debitgl ,c.amount credit,'' debit  from `acctransaction` `c`   WHERE c.split = 1 and c.dr = 0
                                             UNION 
                                             select ce.id id,ce.transactionid tid ,cd.date date,cd.type transtype,cd.memo memo ,cd.currency currency,cd.erate erate, ce.name description,      ce.coa coa ,'' creditgl , ce.glamount debitgl,'' credit , ce.total debit FROM acctransaction cd  join acctransaction_item ce ON cd.id = ce.transactionid where cd.split = 1  and cd.dr = 0 
                                           )  b
                                         UNION  
                                         SELECT id,tid,DATE,transtype,memo,currency,erate,description,coa,debitgl,creditgl ,debit,credit
                                         FROM
                                             ( select c.id id, c.id tid ,c.date DATE,c.type transtype,  c.memo memo,c.currency currency,c.erate erate,   c.description description ,c.dr coa ,'' creditgl,c.glamount debitgl,'' credit,c.amount debit   from acctransaction c  WHERE c.split = 1 and c.cr = 0
                                               UNION 
                                               select ce.id id, ce.transactionid tid ,cd.date date,cd.type transtype,cd.memo memo ,cd.currency currency,cd.erate erate, ce.name description,      ce.coa coa ,ce.glamount  creditgl ,''  debitgl ,ce.total  credit ,''  debit FROM acctransaction cd  join acctransaction_item ce ON cd.id = ce.transactionid where cd.split = 1  and cd.cr = 0 
                                             )  c
                                )q                    
                          )h JOIN coa b ON h.coa=b.id 
                       
                          
                          ) y 
								  )z
								  ON a.myid=z.myid 


    
       ) j WHERE transactionid= '$gender'

      ";

}

if(isset($_POST['order']))
{
 $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY id,date asc ';
}



if($_POST["length"] != -1)
{
 $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

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

$statement->execute();

$number_filter_row = $statement->rowCount();

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

$statement->execute();

$result = $statement->fetchAll();



$data = array();

$unit = 0;
$ocost = 0;
$lcost = 0;
$fcost = 0;

foreach($result as $row)
{
 
  $actionButton = '<input type="radio" name="myradio" class="myradio" value="' . $row["id"] . '" /> </i></a>';
 
 // $edit = ' <form method="post" action="#" target="__blank">  <button type="submit" style="background:none;border:none;padding:0px; outline:none; " name="breakdown" value="' . $row["id"] . '"   "> <i class="fa fa-pencil"></i>  </button></form> ' ; 

    
 $sub_array = array();
  $sub_array[] =  $actionButton  ;
// $sub_array[] =  $print ;
  

 $sub_array[] = $row['date'];
 $sub_array[] = $row['memo'];
 $sub_array[] = $row['description'];
 
 $sub_array[] = number_format( $row['debit'] , 2)  ;
 $sub_array[] = number_format( $row['credit'] , 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);

?>