<?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->assign('date', $date);
    $smarty->display("header.html");
    $smarty->display("orderperiodreportview/index.html");
    $smarty->display("footer.html");
} else if ($do == 'select2client') {
    select2client();
} else if ($do == 'select2supplier') {
    select2supplier();
} else if ($do == 'showajax') {
    showajax();
} else if ($do == 'select2code') {
    select2code();
}



// function select2supplier() {

//     $row_array = array();
//     $return_arr = array();
//     $name = $_POST['searchTerm'];

//     $productsData = R::getAll("SELECT supplierid, suppliername as name
//     FROM  supplier
//     WHERE suppliername LIKE '%" . $name . "%' limit 50");
//     foreach ($productsData as $pro) {
//         $row_array['id'] = $pro['supplierid'];
//         $row_array['text'] = $pro['name'];
//         array_push($return_arr, $row_array);
//     }

//     echo json_encode($return_arr);
// }

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

//     $productsData = R::getAll("SELECT clientid, clientname as name
//     FROM client 
//     WHERE 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 select2code() {
//     $row_array = array();
//     $return_arr = array();
//     $name = $_POST['searchTerm'];
    

//     $productsData = R::getAll("SELECT parcode
//     FROM sellbilldetail 
//     WHERE parcode LIKE '%" . $name . "%'  limit 50");
//     foreach ($productsData as $pro) {
//         $row_array['id'] = $pro['parcode'];
//         $row_array['text'] = $pro['parcode'];
//         array_push($return_arr, $row_array);
//     }

//     echo json_encode($return_arr);
// }

function showajax() {
        $columns = array('productId', 'productName', 'sellbilldetailquantity', 'productName', 'sellbilldetailquantity', 'sellbilldetailquantity');

        $fromdate = $_POST['fromdate'];
        $todate = $_POST['todate'];
        $searchQuery = " ";
        if ($todate != '') {
           $preorderperiod = R::getCell("SELECT preorderperiod FROM `programsettings` WHERE  programsettingsid = 1 ");
           $fromdate = date('Y-m-d', strtotime('-'.$preorderperiod.' days', strtotime($fromdate))); 
        }
    
        $searchQuery ='and  sellbilldate  >= "' . $fromdate . ' 00-00-00" and sellbilldate <= "' . $todate . ' 23-59-55" ';
        $returnsellbilldate ='and  returnsellbilldate  >= "' . $fromdate . ' 00-00-00" and returnsellbilldate <= "' . $todate . ' 23-59-55" ';
        $storedetail ='and  storedetaildate  >= "' . $fromdate . ' 00-00-00" and storedetaildate <= "' . $todate . ' 23-59-55" ';
        

        if (isset($_POST["order"])) {
            $searchQuery .= 'ORDER BY ' . $columns[$_POST['order']['0']['column']] . ' ' . $_POST['order']['0']['dir'] . '  ';
        }

         $apps = count('sellbill'," LEFT JOIN sellbilldetail ON sellbill.sellbillid = sellbilldetail.sellbillid  
             LEFT JOIN product ON sellbilldetail.sellbilldetailproductid = product.productId and product.conditions = 0 
            WHERE sellbill.conditions = 0  $searchQuery group by sellbilldetailproductid ");


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



        $rResult = R::getAll("SELECT  sum(sellbilldetailquantity) as tsellbilldetailquantity, productName, productId FROM `sellbill`  
             LEFT JOIN sellbilldetail ON sellbill.sellbillid = sellbilldetail.sellbillid  
             LEFT JOIN product ON sellbilldetail.sellbilldetailproductid = product.productId and product.conditions = 0 
            WHERE sellbill.conditions = 0  $searchQuery group by sellbilldetailproductid ");



        $output = array(
            "recordsTotal" => $apps,
            "recordsFiltered" => count($rResult),
            "data" => array()
        );
      
        foreach ($rResult as $row) {
            $sumProductQuantity = R::getCell("SELECT  SUM(storedetail.productquantity) FROM `storedetail` WHERE  productid = ".$row["productId"]."  $storedetail group by storedetail.productid ");
            $sumProductQuantity1 = R::getCell("SELECT  SUM(returnsellbilldetail.returnsellbilldetailquantity) FROM `returnsellbill` 
                LEFT JOIN returnsellbilldetail ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid  
                WHERE  returnsellbilldetailproductid = ".$row["productId"]."  $returnsellbilldate  group by returnsellbilldetail.returnsellbilldetailproductid ");
            $sub_array = array();
            $sub_array[] = $row["productId"];
            $sub_array[] = $row["productName"];
            $sub_array[] = (int)$row["tsellbilldetailquantity"];
            $sub_array[] = (int)$sumProductQuantity1;
            $sub_array[] = (int)$sumProductQuantity;
            $Quantity = ((int)$row["tsellbilldetailquantity"] - (int) $sumProductQuantity1 ) - (int) $sumProductQuantity;
            $sub_array[] = $Quantity;
            if ($Quantity > 1) {
                $output['data'][] = $sub_array;
            }
           
        }

        echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

?>