<?php

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

//global varable
global $showoutside;

//to check if the page from .htacess
//$showoutside = $_GET['sn'];
// get the config file
include_once("../public/config.php");

//here the db files that include in the file
include("../public/include_dao.php");
//Sellbilldetail
require_once('../models/dao/SellbilldetailDAO.class.php');
require_once('../models/dto/Sellbilldetail.class.php');
require_once('../models/mysql/SellbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbilldetailMySqlExtDAO.class.php');
//Returnsellbilldetail
require_once('../models/dao/ReturnsellbilldetailDAO.class.php');
require_once('../models/dto/Returnsellbilldetail.class.php');
require_once('../models/mysql/ReturnsellbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnsellbilldetailMySqlExtDAO.class.php');
//Sellandruternbilldetail
require_once('../models/dao/SellandruternbilldetailDAO.class.php');
require_once('../models/dto/Sellandruternbilldetail.class.php');
require_once('../models/mysql/SellandruternbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/SellandruternbilldetailMySqlExtDAO.class.php');
//Product
require_once('../models/dao/ProductDAO.class.php');
require_once('../models/dto/Product.class.php');
require_once('../models/mysql/ProductMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductMySqlExtDAO.class.php');
//Productcat
require_once('../models/dao/ProductcatDAO.class.php');
require_once('../models/dto/Productcat.class.php');
require_once('../models/mysql/ProductcatMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductcatMySqlExtDAO.class.php');
//Client
require_once('../models/dao/ClientDAO.class.php');
require_once('../models/dto/Client.class.php');
require_once('../models/mysql/ClientMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientMySqlExtDAO.class.php');
//Productunit
require_once('../models/dao/ProductunitDAO.class.php');
require_once('../models/dto/Productunit.class.php');
require_once('../models/mysql/ProductunitMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductunitMySqlExtDAO.class.php');

//Programsetting
require_once('../models/dao/ProgramsettingsDAO.class.php');
require_once('../models/dto/Programsetting.class.php');
require_once('../models/mysql/ProgramsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/ProgramsettingsMySqlExtDAO.class.php');

//Bill
require_once('../models/dao/BillsDAO.class.php');
require_once('../models/dto/Bill.class.php');
require_once('../models/mysql/BillsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsMySqlExtDAO.class.php');

//Billsproduct
require_once('../models/dao/BillsproductsDAO.class.php');
require_once('../models/dto/Billsproduct.class.php');
require_once('../models/mysql/BillsproductsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsproductsMySqlExtDAO.class.php');

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');
//get the do the action
$do = $_GET['do'];

/* ======================

  Controller Name :- clientsAndProductsReportController تقرير عملاء ومنتجات

  OPERTATION in Controller

  1- show report
  2- search with clients and products and date

  ======================== */

//here the global templates
$smarty->display("header.html");

//here goes the instances and general variables
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
//Sellbilldetail
$sellBillDetailExt = new SellbilldetailMySqlExtDAO();
//Returnsellbilldetail
$returnSellBillDetailExt = new ReturnsellbilldetailMySqlExtDAO();
//Sellandruternbilldetail
$sellAndRuternBillDetailExt = new SellandruternbilldetailMySqlExtDAO();
//Product
$productDAO = new ProductMySqlDAO();
$productExt = new ProductMySqlExtDAO();
//Productcat
$productCatDAO = new ProductcatMySqlDAO();
$productCatExt = new ProductcatMySqlExtDAO();
//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientExt = new ClientMySqlExtDAO();
//Productunit
$productUnitDAO = new ProductunitMySqlDAO();
$productUnitExt = new ProductunitMySqlExtDAO();

//bill
$billsReturn = new Billsreturn();
$billsReturnDAO = new BillsreturnMySqlDAO();
$billsReturnEX = new BillsreturnMySqlExtDAO();

//billsproducts
$billsReturnProducts = new Billsreturnproduct();
$billsReturnProductsDAO = new BillsreturnproductsMySqlDAO();
$billsReturnProductsEX = new BillsreturnproductsMySqlExtDAO();

//bill
$bills = new Bill();
$billsDAO = new BillsMySqlDAO();
$billsEX = new BillsMySqlExtDAO();

//billsproducts
$billsProducts = new Billsproduct();
$billsProductsDAO = new BillsproductsMySqlDAO();
$billsProductsEX = new BillsproductsMySqlExtDAO();

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

$smarty->assign("hidecat", $_SESSION['hidecat']);
//check and use the condetion that suite this action
if ($do == "show" || empty($do)) {
    //here the permission check
    include_once("../public/authentication.php");

    $Programsetting = $ProgramsettingDAO->load(1);
    $smarty->assign("Programsettingdata", $Programsetting);

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

    $clientsData = getClients();
    $smarty->assign("clientsData", $clientsData);

    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $productId = $_REQUEST['productId'];
    $clientId = (int) $_REQUEST['clientId'];

    if (empty($startDate) && empty($endDate)) {
        $startDate = date('Y-m-d');
        $endDate = date('Y-m-d');
    }

    //if (isset($productId)  && $productId != "" && $productId != "-1" && isset($clientId) && $clientId != "-1" && isset($startDate) && isset($endDate) && $startDate != "" && $endDate != "") {
    $message = '';
    if ($clientId > 1) {
        $clientData = $clientDAO->load($clientId);
        $clientName = $clientData->clientname;
        $message .= "العميل :" . $clientName;
    }

    if ($productId != '' && $productId != '-1') {
        $productData = $productDAO->load($productId);
        $productName = $productData->productName;
        $message .= "<br> المنتج: " . $productName;
    }


    if ($startDate != '' && $endDate != '') {
        ########
        $Programsetting = $ProgramsettingDAO->load(1);
        if (isset($Programsetting->reportsPlusHours) && !empty($Programsetting->reportsPlusHours)) {
            $reportsPlusHours = $Programsetting->reportsPlusHours + 24; //24 to get the end of the day and add search hours to it
            $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($endDate)));
            $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($startDate)));
        } else {
            $endDate = $endDate . ' 23:59:59';
            $startDate = $startDate . " 00:00:00";
        }
        #############
        $message .= "<br> من تاريخ: " . $startDate . " إلى تاريخ: " . $endDate;
    }
    //$message = "العميل :" . $clientName . "<br> المنتج: " . $productName . "<br> من تاريخ: " . $startDate . " إلى تاريخ: " . $endDate;
    $smarty->assign('message', $message);

    if (empty($clientId) && $productId == '' && $startDate == '' && $endDate == '') {

    } else {

        $whereSellBill = '';
        $whereSellAndReturn = '';
        $whereBillProduct = '';
        $whereReturnBill = '';
        $whereReturnSellBill = '';
        if ($productId != '' && $productId != '-1') {
            $whereSellBill .= ' and sellbilldetail.sellbilldetailproductid = ' . $productId . '';
            $whereSellAndReturn .= ' and sellandruternbilldetail.sellbilldetailproductid = ' . $productId . '';
            $whereBillProduct .= ' and billsproducts.productid = ' . $productId . '';
            $whereReturnBill .= ' and billsreturnproducts.productid = ' . $productId . '';
            $whereReturnSellBill .= ' and returnsellbilldetail.returnsellbilldetailproductid = ' . $productId . '';
        }

        //# ClientId .
        if ($clientId > 1) {
            $whereSellBill .= ' and sellbill.sellbillclientid = ' . $clientId . ' ';
            $whereSellAndReturn .= ' and  sellbillandrutern.sellbillclientid = ' . $clientId . ' ';
            $whereBillProduct .= ' and bills.clientid = ' . $clientId . ' ';
            $whereReturnBill .= ' and  bills.clientid = ' . $clientId . ' ';
            $whereReturnSellBill .= ' and returnsellbill.returnsellbillclientid = ' . $clientId . ' ';
        }



        //# productunitid .
        if ($startDate != '' && $endDate != '') {
            $whereSellBill .= ' and sellbill.sellbilldate >= "' . $startDate . '" AND sellbill.sellbilldate <= "' . $endDate . '"';
            $whereSellAndReturn .= '  and sellbillandrutern.sellbilldate >= "' . $startDate . '" AND sellbillandrutern.sellbilldate <= "' . $endDate . '"';
            $whereBillProduct .= ' and  bills.billdate >= "' . $startDate . '" AND bills.billdate <= "' . $endDate . '"';
            $whereReturnBill .= ' and  bills.billdate >= "' . $startDate . '" AND bills.billdate <= "' . $endDate . '"';
            $whereReturnSellBill .= '  and returnsellbill.returnsellbilldate >= "' . $startDate . '" AND returnsellbill.returnsellbilldate <= "' . $endDate . '"';
        }


        $alldata = R::getAll('( SELECT sellbilldetail.sellbilldetailprice as prices, sellbilldetail.pricetype as pricetypes, product.productName, unit.unitName ,productcat.productCatName, DATE_FORMAT(sellbilldate, "%d-%m-%Y") as dates, sellbill.sellbillid as ids
        ,0 as billtypes , client.clientname , sellbilldetail.sellbilldetailquantity as quantitys, sellbilldetail.discountvalue as discounts, productquantity FROM sellbilldetail
        JOIN sellbill ON sellbilldetail.sellbillid = sellbill.sellbillid
        JOIN product ON sellbilldetail.sellbilldetailproductid = product.productId
        left JOIN storedetail ON  (product.productId = storedetail.productid and sellbilldetail.storeid = storedetail.storeid)
        JOIN productunit  ON sellbilldetail.productunitid = productunit.productunitid
        JOIN unit ON unit.unitId = productunit.unitid
        JOIN productcat ON productcat.productCatId=product.productCatId
        join client on client.clientid = sellbill.sellbillclientid
        WHERE  sellbill.conditions = 0   ' . $whereSellBill . '  order by sellbilldetailid desc ) union all
        (SELECT sellandruternbilldetail.sellbilldetailprice as prices, sellandruternbilldetail.pricetype as pricetypes,  product.productName, unit.unitName ,productcat.productCatName,  DATE_FORMAT(sellbilldate, "%d-%m-%Y")  as dates, sellbillandrutern.sellbillid as ids ,1 as billtypes , client.clientname  , sellbillandrutern.sellQuantity  as quantitys, sellandruternbilldetail.discountvalue as discounts, productquantity FROM sellandruternbilldetail
        JOIN sellbillandrutern ON   sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
        JOIN product ON sellandruternbilldetail.sellbilldetailproductid = product.productId
        left JOIN storedetail ON  (product.productId = storedetail.productid  and sellandruternbilldetail.storeid = storedetail.storeid)
        JOIN productunit ON sellandruternbilldetail.productunitid = productunit.productunitid
        JOIN unit ON unit.unitId = productunit.unitid
        join productcat on productcat.productCatId=product.productCatId
        join client on client.clientid = sellbillandrutern.sellbillclientid
        WHERE sellandruternbilldetail.selltype = 0  ' . $whereSellAndReturn . '  order by sellbilldetailid desc) union all
         ( SELECT returnsellbilldetail.returnsellbilldetailprice as prices, returnsellbilldetail.pricetype as pricetypes, product.productName, unit.unitName ,productcat.productCatName, DATE_FORMAT(returnsellbilldate, "%d-%m-%Y") as dates, returnsellbill.returnsellbillid as ids
        ,4 as billtypes , client.clientname , returnsellbilldetail.returnsellbilldetailquantity as quantitys, returnsellbilldetail.discountvalue as discounts, productquantity FROM returnsellbilldetail
        JOIN returnsellbill ON returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
        JOIN product ON returnsellbilldetail.returnsellbilldetailproductid = product.productId
        left JOIN storedetail ON  (product.productId = storedetail.productid and returnsellbilldetail.storeid = storedetail.storeid)
        JOIN productunit  ON returnsellbilldetail.productunitid = productunit.productunitid
        JOIN unit ON unit.unitId = productunit.unitid
        JOIN productcat ON productcat.productCatId=product.productCatId
        join client on client.clientid = returnsellbill.returnsellbillclientid
        WHERE  returnsellbill.conditions = 0   ' . $whereReturnSellBill . '  order by returnsellbilldetailid desc )
        ');

        // $alldata = loadPricesForClient($productId, $clientId, $startDate, $endDate);
        $smarty->assign("alldata", json_decode(json_encode($alldata)));
    }

    //}
    //here the smarty templates
    $smarty->display("clientsAndProductsReportview/showNew.html");
    $smarty->assign("settlementstoreshow", 1); //for products (select2)
    $smarty->assign("clientAndProduct", 1); // check selection of clients and products and date
}
//here the global templates
$smarty->display("footer.html");

/* ===============================
  function in this CONTROLLER
  ================================ */

function getClients() {
    global $clientDAO;
    $clientsData = $clientDAO->queryByCondition(0);
    return $clientsData;
}

function loadPricesForClient($productId, $clientId, $startDate, $endDate) {
    global $productExt;
    global $productDAO;
    global $productUnitExt;
    global $smarty;


//    $startDate1 =  $startDate . " 00:00:00" ;
//    $endDate1 =  $endDate . " 23:59:59";
    ##################فواتير مبيعات
    $returnedArray1 = getProductPriceFromSellBill($productId, $clientId, $startDate, $endDate);

    $sellbilldetailData = $returnedArray1[0];
    $sellandruternbilldetailData = $returnedArray1[1];

    $billdetailData = $returnedArray1[2];
    $ruternbilldetailData = $returnedArray1[3];


    $alldata = array_merge($sellbilldetailData, $sellandruternbilldetailData, $billdetailData, $ruternbilldetailData);

    return $alldata;
    /*
      //get product units' ids
      $unitsdata = $productUnitExt->queryWithProductIdAndConditionExt($productId);
      $array3 = array();
      if (count($unitsdata) > 0) {
      $i = 1;
      foreach ($unitsdata as $u) {


      ##################فواتير مبيعات
      $returnedArray1 = getProductPriceFromSellBill($productId, $clientId, $u->productunitid, $startDate1, $endDate1);

      $sellbilldetailData = $returnedArray1[0];
      $sellandruternbilldetailData = $returnedArray1[1];

      $billdetailData = $returnedArray1[2];
      $ruternbilldetailData = $returnedArray1[3];


      $alldata = array_merge($sellbilldetailData, $sellandruternbilldetailData,$billdetailData,$ruternbilldetailData);


      //  $smarty->assign("billtype" . $i, $billtype);
      $smarty->assign("alldata" . $i, $alldata);


      $i++;
      }
      }

     */
}

//function getProductPriceFromSellBill($ProductId, $ClientId, $productunitid, $startDate, $endDate) {
function getProductPriceFromSellBill($ProductId, $ClientId, $startDate, $endDate) {

    global $sellBillDetailExt;
    global $sellAndRuternBillDetailExt;
    global $billsDAO;
    global $billsProductsEX;
    global $billsReturnProductsEX;

    $whereSellBill = $whereSellAndReturn = $whereBillProduct = $whereReturnBill = '';
    //# m7md 21/11/2017
    //# productId .
    if ($ProductId != '' && $ProductId != '-1') {

        $whereSellBill .= ' sellbilldetail.sellbilldetailproductid = ' . $ProductId . '';
        $whereSellAndReturn .= ' sellandruternbilldetail.sellbilldetailproductid = ' . $ProductId . '';
        $whereBillProduct .= ' billsproducts.productid = ' . $ProductId . '';
        $whereReturnBill .= ' billsreturnproducts.productid = ' . $ProductId . '';
    }

    //# ClientId .
    if ($ClientId != '' && $ClientId != '-1') {

        if ($whereSellBill != '') {
            $andSell = ' and ';
        }
        if ($whereSellAndReturn != '') {
            $andSellRetun = ' and ';
        }
        if ($whereBillProduct != '') {
            $andProduct = ' and ';
        }
        if ($whereReturnBill != '') {
            $andReturn = ' and ';
        }

        $whereSellBill .= $andSell . '  sellbill.sellbillclientid = ' . $ClientId . '';
        $whereSellAndReturn .= $andSellRetun . '  sellbillandrutern.sellbillclientid = ' . $ClientId . '';
        $whereBillProduct .= $andProduct . '  bills.clientid = ' . $ClientId . '';
        $whereReturnBill .= $andReturn . '  bills.clientid = ' . $ClientId . '';
    }



    //# productunitid .
    if ($startDate != '' && $endDate != '') {

        if ($whereSellBill != '') {
            $andSell = ' and ';
        }
        if ($whereSellAndReturn != '') {
            $andSellRetun = ' and ';
        }
        if ($whereBillProduct != '') {
            $andProduct = ' and ';
        }
        if ($whereReturnBill != '') {
            $andReturn = ' and ';
        }

        $whereSellBill .= $andSell . '  sellbill.sellbilldate >= "' . $startDate . '" AND sellbill.sellbilldate <= "' . $endDate . '"';
        $whereSellAndReturn .= $andSellRetun . '  sellbillandrutern.sellbilldate >= "' . $startDate . '" AND sellbillandrutern.sellbilldate <= "' . $endDate . '"';
        $whereBillProduct .= $andProduct . '  bills.billdate >= "' . $startDate . '" AND bills.billdate <= "' . $endDate . '"';
        $whereReturnBill .= $andReturn . '  bills.billdate >= "' . $startDate . '" AND bills.billdate <= "' . $endDate . '"';
    }

//    $sellbilldetailData = $sellBillDetailExt->queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $productunitid, $startDate, $endDate);
//    $sellandruternbilldetailData = $sellAndRuternBillDetailExt->queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $productunitid, 0, $startDate, $endDate);
//    $BillProductData = $billsProductsEX->queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $startDate, $endDate);
//    $ruternBillProductData = $billsReturnProductsEX->queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $startDate, $endDate);

    if ($whereSellBill != '') {
        $whereSellBill .= ' and ';
    }
    $sellbilldetailData = $sellBillDetailExt->queryWithProductIdAndClientIdAndUnitExtNew($whereSellBill);

    if ($whereSellAndReturn != '') {
        $whereSellAndReturn .= ' and ';
    }
    $sellandruternbilldetailData = $sellAndRuternBillDetailExt->queryWithProductIdAndClientIdAndUnitExtNew($whereSellAndReturn);

    if ($whereBillProduct != '') {
        $whereBillProduct .= ' and ';
    }
    $BillProductData = $billsProductsEX->queryWithProductIdAndClientIdAndUnitExtNew($whereBillProduct);

    if ($whereReturnBill != '') {
        $whereReturnBill .= ' and ';
    }
    $ruternBillProductData = $billsReturnProductsEX->queryWithProductIdAndClientIdAndUnitExtNew($whereReturnBill);


    return array($sellbilldetailData, $sellandruternbilldetailData, $BillProductData, $ruternBillProductData);
}

/* ================================================ فواتير البصريات =============================================== */

function getProductPriceFromBills($ProductId, $ClientId, $startDate, $endDate) {
    global $billsDAO;
    global $billsProductsEX;
    global $billsReturnProductsEX;

    $BillProductData = $billsProductsEX->queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $startDate, $endDate);

    $ruternBillProductData = $billsReturnProductsEX->queryWithProductIdAndClientIdAndUnitExt($ProductId, $ClientId, $startDate, $endDate);

    return array($BillProductData, $ruternBillProductData);
}

?>