<?php
include("../public/impOpreation.php");

$do = $_GET['do'];
$today = date("Y-m-d H:i:s");
$date = date("Y-m-d");
$userid = $_SESSION['userid'];
if (empty($do)) {  
    $smarty->display("header.html");
    $smarty->display("executorsuserreport/show.html");
    $smarty->display("footer.html");
}else if ($do == 'select2client') {
  select2client();
}else if ($do == 'select2sellbill') {
  select2sellbill();  
}else if ($do == 'showajax') {
  showajax();      
}


function select2client() {
    $row_array = array();
    $return_arr = array();
    $name = $_POST['searchTerm'];
    

    $productsData = R::getAll("SELECT clientid, clientname as name
    FROM client 
    WHERE conditions = 0 and clientname LIKE '%" . $name . "%'  limit 50");
    foreach ($productsData as $pro) {
        $row_array['id'] = $pro['clientid'];
        $row_array['text'] = $pro['name'];
        array_push($return_arr, $row_array);
    }

    echo json_encode($return_arr);
}



function select2sellbill() {
    $row_array = array();
    $return_arr = array();
    $name = $_POST['searchTerm'];
    $clientid = $_POST['clientid'];
    
    $productsData = R::getAll("SELECT sellbillid, CONCAT(sellbillid,'/',datestarting) as texts
    FROM sellbill 
    WHERE conditions = 0 and CONCAT(sellbillid,'/',datestarting) LIKE '%" . $name . "%' and  sellbill.sellbillclientid = $clientid limit 50");
    foreach ($productsData as $pro) {
        $row_array['id'] = $pro['sellbillid'];
        $row_array['text'] = $pro['texts'];
        array_push($return_arr, $row_array);
    }

    echo json_encode($return_arr);
}



function showajax() {
    $columns = array('executors.id', 'clientname', 'executorssellid', 'sellbillaftertotalbill', 'sellbilldate', '', '', '', '');
    $start_date = $_POST['fromdate'];
    $end_date   = $_POST['todate'];
    $data1      = $_POST['data1'];
    $data2      = $_POST['data2'];
    $searchQuery = " ";
    if($data1 != ''){
      $searchQuery .=  " and executors.executorsclientid = ".$data1. " ";
    }

    if($data2 != ''){
       $searchQuery .= " and executors.executorssellid = ".$data2. " ";
    }
  
    
    if($start_date != '' && $end_date != ''){
       $searchQuery .='and  sellbill.datestarting  >= "' . $start_date . '" and sellbill.datestarting <= "' . $end_date . '" ';
    }

    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
    $searchQuery .= "and ( executors.id LIKE "%'.$_POST["search"]["value"].'%" 
                    OR clientname LIKE "%'.$_POST["search"]["value"].'%"
                    OR expensesValue LIKE "%'.$_POST["search"]["value"].'%"
                    OR sellbilldate LIKE "%'.$_POST["search"]["value"].'%"
                    OR datestartingLIKE "%'.$_POST["search"]["value"].'%"
        )
     ";
    }


    if(isset($_POST["order"]))
    {
    $searchQuery .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].'  ';
    }
    else
    {
     $searchQuery .= "ORDER BY  executors.id DESC ";
    }
    
     $totals = R::count('executors','
          LEFT JOIN sellbill ON executors.executorssellid = sellbill.sellbillid 
        LEFT JOIN client ON executors.executorsclientid = client.clientid   
        LEFT JOIN expenses ON expenses.sellbillid = sellbill.sellbillid 
         '.$searchjoin.' 
          WHERE 1 '.$searchQuery.' ');
    
    if (isset($_POST['start']) && $_POST['length'] != '-1') {
       $searchQuery .= "LIMIT " . intval($_POST['start']) . ", " .
                intval($_POST['length']);
    }

    $rResult = R::getAll('SELECT executors.* ,clientname, expensesValue, sellbillaftertotalbill, sellbilldate, datestarting FROM `executors`
        LEFT JOIN sellbill ON executors.executorssellid = sellbill.sellbillid 
        LEFT JOIN client ON executors.executorsclientid = client.clientid   
        LEFT JOIN expenses ON expenses.sellbillid = sellbill.sellbillid 
         '.$searchjoin.' 
         WHERE 1 '.$searchQuery.' '); 
           
    
    
     
    $output = array(  
        "draw" => intval($_POST["draw"]),
        "recordsTotal" =>  $totals,
        "recordsFiltered" => count($rResult),
        "data" => array()
    );
  
       foreach ($rResult as $row) {
        $clientdebtchangeamount = R::getCell('SELECT sum(clientdebtchangeamount) as clientdebtchangeamount FROM clientdebtchange WHERE 
            billid = '. $row["executorssellid"]. ' and tablename = "clientPayedDeptSellBillsController.php" '); 
       
         // $users = R::getAll('select * from user where userid in  ('.$row["executorsuserids"] .')');
         // $usertext = '';
         // foreach($users as $user){
         //    $usertext .= $user['employeename'] . '/';

         // }
        $sub_array = array();
        $sub_array[] = $row["id"];
        $sub_array[] = $row["clientname"];
        $sub_array[] = $row["executorssellid"];
        $sub_array[] = $row["sellbilldate"];
        $sub_array[] = $row["sellbillaftertotalbill"];
        $sub_array[] = $clientdebtchangeamount;
        $sub_array[] = $row["expensesValue"];
        $sub_array[] = $row["sellbillaftertotalbill"] - $clientdebtchangeamount;
        $sub_array[] = $row["sellbillaftertotalbill"] - $row["expensesValue"];
        $sub_array[] = $clientdebtchangeamount - $row["expensesValue"];
       
        $output['data'][] = $sub_array;
       }
    
       echo json_encode($output, JSON_UNESCAPED_SLASHES);

}






?>