<?php
include("../public/impOpreation.php");
$do = $_GET['do'];
$today = date("Y-m-d H:i:s");
$date = date("Y-m-d");
if (empty($do)) {  
    $smarty->display("header.html");
    $smarty->display("clientsexpensereportview/show.html");
    $smarty->display("footer.html");
} else if ($do == "getdata") { 
    $start_date = filter_input(INPUT_POST, 'start_date');  
    $end_date = filter_input(INPUT_POST, 'end_date');  
    $clientid =  filter_input(INPUT_POST, 'clientid');  
     $clientQuery = '';
    $sellQuery = '';
    $returnQuery = '';
    $expensehQuery = '';
    $changeQuery = '';
    if($start_date != '' && $end_date != ''){
        $sellQuery .=' and  sellbill.sellbilldate  >= "' . $start_date . ' 00-00-00" and sellbill.sellbilldate <= "' . $end_date . ' 23-59-55" ';
        $returnQuery .=' and  returnsellbill.returnsellbilldate  >= "' . $start_date . ' 00-00-00" and returnsellbill.returnsellbilldate <= "' . $end_date . ' 23-59-55" ';
        $expensehQuery .=' and  expenses.expensesdate  >= "' . $start_date . '" and expenses.expensesdate <= "' . $end_date . '" ';
        $changeQuery .=' and  clientdebtchange.clientdebtchangedate  >= "' . $start_date . ' 00-00-00" and clientdebtchange.clientdebtchangedate <= "' . $end_date . ' 23-59-55" ';
    }

    
    
    if($clientid){
        // $client = R::getRow('SELECT * FROM client  where clientid =  ' . $clientid );
        // $sellbill = R::getAll('SELECT * FROM sellbill  where sellbill.conditions = 0 and sellbill.sellbillclientid =  ' . $clientid . $sellQuery);
        // $returnsellbill = R::getAll('SELECT * FROM returnsellbill  where returnsellbill.conditions = 0  and returnsellbill.returnsellbillclientid = ' . $clientid . $returnQuery);
        // $expenses = R::getAll('SELECT * FROM expenses  where expenses.conditions = 0  and expenses.clientid = ' . $clientid. $expensehQuery);
        // $clientdebtchange = R::getAll('SELECT * FROM clientdebtchange where  clientdebtchange.clientid = ' . $clientid . $changeQuery);
        // $smarty->assign('client', $client);
        // $smarty->assign('sellbill', $sellbill);
        // $smarty->assign('returnsellbill', $returnsellbill);
        // $smarty->assign('expenses', $expenses);
        // $smarty->assign('clientdebtchange', $clientdebtchange);
        // $smarty->assign('clients', $clients);
        // $smarty->display("clientsexpensereportview/getclient.html");  

              $client = R::getRow('SELECT * FROM client  where clientid =  ' . $clientid );
        $sellbill = R::getAll('SELECT * FROM sellbill  where sellbill.conditions = 0 and sellbill.sellbillclientid =  ' . $clientid . $sellQuery);
        $sellbilldata = []; 
        foreach($sellbill as $sell){
            $sellbilldetail = R::getAll('SELECT * FROM sellbilldetail JOIN product ON sellbilldetail.sellbilldetailproductid = product.productId
            JOIN productcat ON product.productCatId  = productcat.productCatId 
            where sellbilldetail.sellbillid = ?',[$sell['sellbillid']]);
            $sell['sellbilldetail'] = $sellbilldetail;
            $sellbilldata[] =[
                'sellbillid' => $sell['sellbillid'],
                'sellbilldate' => $sell['sellbilldate'],
                'sellbilltotalpayed' => $sell['sellbilltotalpayed'],
                'sellbilldetail' => $sellbilldetail,
            ];
        }
        $returnsellbill = R::getAll('SELECT * FROM returnsellbill  where returnsellbill.conditions = 0  and returnsellbill.returnsellbillclientid = ' . $clientid . $returnQuery);
        $returnsellbilldata = []; 
        foreach($returnsellbill as $return){
            $returnsellbilldetail = R::getAll('SELECT * FROM returnsellbilldetail JOIN product ON returnsellbilldetail.returnsellbilldetailproductid = product.productId
            JOIN productcat ON product.productCatId  = productcat.productCatId 
            where returnsellbilldetail.returnsellbillid = ?',[$return['returnsellbillid']]);
            $return['returnsellbilldetail'] = $sellbilldetail;
            $returnsellbilldata[] =[
                'returnsellbillid' => $return['returnsellbillid'],
                'returnsellbilldate' => $return['returnsellbilldate'],
                'returnsellbillaftertotalbill' => $return['returnsellbillaftertotalbill'],
                'returnsellbilldetail' => $returnsellbilldetail,
            ];
        }
        
        $expenses = R::getAll('SELECT * FROM expenses  where expenses.conditions = 0  and expenses.clientid = ' . $clientid. $expensehQuery);
        $clientdebtchange = R::getAll('SELECT * FROM clientdebtchange where  clientdebtchange.clientid = ' . $clientid . $changeQuery);
        $smarty->assign('client', $client);
        $smarty->assign('sellbill', $sellbilldata);
        $smarty->assign('returnsellbill', $returnsellbilldata);
        $smarty->assign('expenses', $expenses);
        $smarty->assign('clientdebtchange', $clientdebtchange);
        $smarty->assign('clients', $clients);
        $smarty->display("clientsexpensereportview/getclient.html");  
    }else{
        $clients = R::getAll('SELECT * FROM client JOIN clientdebtchange ON clientdebtchange.clientid = client.clientid where client.conditions = 0 ' . $clientQuery .' group by client.clientid');
        $clientsdata =[];
        foreach ($clients as $client) {
            $sellbill = R::getcell('SELECT sum(sellbilltotalpayed) FROM sellbill  where sellbill.conditions = 0 and sellbill.sellbillclientid =  ' . $client['clientid'] . $sellQuery);
            $returnsellbill = R::getcell('SELECT sum(returnsellbillaftertotalbill) FROM returnsellbill  where returnsellbill.conditions = 0  and returnsellbill.returnsellbillclientid = ' .  $client['clientid'] . $returnQuery);
            $expenses = R::getcell('SELECT sum(expensesValue) FROM expenses  where expenses.conditions = 0  and expenses.clientid = ' .  
            $client['clientid'] . $expensehQuery);
            $clientdebtchange = R::getcell('SELECT sum(clientdebtchangeamount) FROM clientdebtchange where  clientdebtchange.clientid = ' .  
            $client['clientid'] . $changeQuery);
            $client['sellbill'] = $sellbill;
            $client['returnsellbill'] = $returnsellbill;
            $client['expenses'] = $expenses;
            $client['clientdebtchange'] = $clientdebtchange;
            $clientsdata[] =[
                'clientname' => $client['clientname'],
                'clientdebt' => $client['clientdebt'],
                'sellbill' => $sellbill,
                'returnsellbill' => $returnsellbill,
                'expenses' => $expenses,
                'clientdebtchange' => $clientdebtchange
            ];
            
        }
        $smarty->assign('clientsdatas', $clientsdata);
        $smarty->display("clientsexpensereportview/getdata.html");  
        
            
      
       
        
    }

   
   
}
?>