<?php
//the global file operation
include("../public/impOpreation.php");

//global varable
global $showoutside;
//require_once('../models/dao/YoutubeLinkDAO.class.php');
//require_once('../models/dto/YoutubeLink.class.php');
//require_once('../models/mysql/YoutubeLinkMySqlDAO.class.php');
//require_once('../models/mysql/ext/YoutubeLinkMySqlExtDAO.class.php');
//to check if the page from .htacess
//$showoutside = $_GET['sn'];
// get the config file
include_once("../public/config.php");

//$youtubeLink = new YoutubeLink();
//$youtubeLinkDAO = new YoutubeLinkMySqlDAO();
//$youtubeLinkEX = new YoutubeLinkMySqlExtDAO();

//here the db files that include in the file
// GOES HERE ....................
include("../public/include_dao.php");
$do = $_GET['do'];

if (empty($do)) {

    //$youtubes = $youtubeLinkDAO->queryAll();
    //$smarty->assign("youtubes", $youtubes);

    $smarty->display("header.html");
    $smarty->display("autoSalesReportview/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() {
    global $allcutsup;
    $columns = array('', 'serialnumber', 'buybilldate', 'buybillfinalbill', 'suppliername', 'sellbilldate', 'sellbillfinalbill', 'clientname', '', 'buybillid', 'sellbillidend');

    $fromdate = $_POST['fromdate'];
    $todate = $_POST['todate'];
    $supplierid = $_POST['data1'];
    $clientid = $_POST['data2'];
     $code = $_POST['data3'];



    $searchQuery = " ";
    if ($clientid != '') {
        $searchQuery .= " and client.clientid = " . $clientid . " ";
    }
    if ($supplierid != '') {
        $searchQuery .= " and supplier.supplierid = " . $supplierid . " ";
    }
    if ($code != '') {
        $searchQuery .= " and sellbilldetail.parcode = " . $code . " ";
    }

    if ($fromdate != '' && $todate != '') {
        // $searchQuery .= " and buybilldate  >= '$fromdate' and buybilldate <= '$todate' ";
        $searchQuery .= " and sellbilldate  >= '$fromdate' and sellbilldate <= '$todate' ";

    }else{
        $today = date("Y-m-d");
        $searchQuery .='and  sellbilldate  >= "' . $today . ' 00-00-00" and sellbilldate <= "' . $today . ' 23-59-55" ';
    }
    if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
        $searchQuery .= "and ( serialnumber LIKE " % '.$_POST["search"]["value"].' % "
                    OR buybilldate LIKE " % '.$_POST["search"]["value"].' % "
                    OR buybillfinalbill LIKE " % '.$_POST["search"]["value"].' % "
                    OR suppliername LIKE " % '.$_POST["search"]["value"].' % "
                    OR sellbilldate LIKE " % '.$_POST["search"]["value"].' % "
                    OR sellbillfinalbill LIKE " % '.$_POST["search"]["value"].' % "
                    OR clientname LIKE " % '.$_POST["search"]["value"].' % "
                    OR buybillid LIKE " % '.$_POST["search"]["value"].' % "
                    OR sellbillidend LIKE " % '.$_POST["search"]["value"].' % "
        )
     ";
    }


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


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

//     $rResult = R::getAll("SELECT *,buybill.buybillid as buybillidend,sellbill.sellbillid as sellbillidend FROM `productserial`
//  LEFT JOIN sellbilldetail ON productserial.serialnumber = sellbilldetail.parcode
//  LEFT JOIN sellbill ON sellbilldetail.sellbillid = sellbill.sellbillid
//  LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
//  LEFT JOIN buybill ON productserial.billid = buybill.buybillid
//  LEFT JOIN buybilldetail ON buybill.buybillid = buybilldetail.buybillid
//  LEFT JOIN supplier ON buybill.buybillsupplierid = supplier.supplierid
// WHERE productserial.del = 0 group by productserial.serialnumber,buybill.buybillid, sellbill.sellbillid  $searchQuery ");


        $rResult = R::getAll("SELECT *,sellbilldetail.sellbillid as sellbillidend, sellbilldetail.parcode as parcodes, sellbill.sellbilldate as sellbilldates FROM `sellbilldetail`
            LEFT JOIN sellbill ON sellbilldetail.sellbillid = sellbill.sellbillid and sellbill.conditions = 0 
         
            LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
            LEFT JOIN productserial ON sellbilldetail.parcode = productserial.serialnumber
            LEFT JOIN buybill ON productserial.billid = buybill.buybillid
            LEFT JOIN buybilldetail ON buybill.buybillid = buybilldetail.buybillid
            LEFT JOIN supplier ON buybill.buybillsupplierid = supplier.supplierid
            WHERE productserial.del = 0  $searchQuery ");


  
    $apps = count($rResult);

    $output = array(
        "recordsTotal" => intval($apps),
        "recordsFiltered" => intval($apps),
        "data" => array()
    );
    $buybillfinalbilltotal = 0;
    $sellbillfinalbilltotal = 0;
    $finaltotals = 0;
    $i = 1;
    foreach ($rResult as $row) {
        $countreturnsellbill = R::count("returnsellbill"," LEFT JOIN returnsellbilldetail ON returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid  
        where returnsellbilldetail.parcode = ? and returnsellbill.returnsellbilldate >= ? and returnsellbill.conditions = 0 ", [$row["parcodes"], $row["sellbilldates"]]);
        if ($countreturnsellbill == 0) {
       // $returnsellbilldetail = R::getRow("SELECT * FROM returnsellbilldetail where parcode = " .$row["parcodes"] ." order by returnsellbilldetailid desc ");
//if ($returnsellbilldetail['returnsellbilldetailid'] > 0) {
  //         $returnsellbill = R::getRow("SELECT * FROM returnsellbill where returnsellbillid = ? order by returnsellbillid desc",[$returnsellbilldetail["returnsellbillid"]]);
    //       $row["buybillaftertotalbill"] =  $returnsellbill['returnsellbillaftertotalbill'];
      //  }
        $buybillfinalbilltotal += $row["buybillaftertotalbill"];
        $sellbillfinalbilltotal += $row["sellbillaftertotalbill"];
        $finaltotals += round($row["sellbillaftertotalbill"] - $row["buybillaftertotalbill"]);
        $sub_array = array();
        $sub_array[] =  $i;
        $sub_array[] = $row["parcodes"];
        $sub_array[] = $row["buybilldate"];
        $sub_array[] = round($row["buybillaftertotalbill"]);
        $sub_array[] = $row["suppliername"];
        $sub_array[] = $row["sellbilldate"];
        $sub_array[] = round($row["sellbillaftertotalbill"]);
        $sub_array[] = $row["clientname"];
        $sub_array[] = round($row["sellbillaftertotalbill"] - $row["buybillaftertotalbill"]);
        $sub_array[] = '<a href="buyBillController.php?do=editbill&amp;id='.$row["buybillid"].'" type="button" class="btn btn-default btn-lg notesicon" target="_blank"></a>';
        $sub_array[] = '<a href="sellbillController.php?do=showDetail&amp;sellbillid='.$row["sellbillidend"].'" type="button" class="btn btn-default btn-lg notesicon" target="_blank"></a>';
         
        $output['data'][] = $sub_array;
        $i++;
        }
    }

     // $returnsellbillaftertotalbill = R::getCell("SELECT sum(returnsellbillaftertotalbill) FROM returnsellbill where returnsellbill.conditions = 0 and returnsellbill.returnsellbillsysdate  >= '$fromdate 00-00-00' and returnsellbill.returnsellbillsysdate <= '$todate 23-59-55' ");
    $sub_array2 = array();
    $sub_array2[] = "";
    $sub_array2[] = "";
    $sub_array2[] = "";
    $sub_array2[] = (int)$buybillfinalbilltotal;
    $sub_array2[] = "";
    $sub_array2[] = date("Y-m-d H:i:s");
    $sub_array2[] = (int) $sellbillfinalbilltotal;
    $sub_array2[] = "";
    $sub_array2[] = (int) $finaltotals;
    $sub_array2[] = "";
    $sub_array2[] = "";
    $output['data'][] = $sub_array2;

    echo json_encode($output, JSON_UNESCAPED_SLASHES);
}

?>