<?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");
//Buybilldetail
require_once('../models/dao/BuybilldetailDAO.class.php');
require_once('../models/dto/Buybilldetail.class.php');
require_once('../models/mysql/BuybilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/BuybilldetailMySqlExtDAO.class.php');
//Returnbuybilldetail
require_once('../models/dao/ReturnbuybilldetailDAO.class.php');
require_once('../models/dto/Returnbuybilldetail.class.php');
require_once('../models/mysql/ReturnbuybilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnbuybilldetailMySqlExtDAO.class.php');
//Buyandruternbilldetail
require_once('../models/dao/BuyandruternbilldetailDAO.class.php');
require_once('../models/dto/Buyandruternbilldetail.class.php');
require_once('../models/mysql/BuyandruternbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/BuyandruternbilldetailMySqlExtDAO.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');
//Supplier
require_once('../models/dao/SupplierDAO.class.php');
require_once('../models/dto/Supplier.class.php');
require_once('../models/mysql/SupplierMySqlDAO.class.php');
require_once('../models/mysql/ext/SupplierMySqlExtDAO.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');

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
$buyBillDetailEX = new BuybilldetailMySqlExtDAO();
//Returnsellbilldetail
$returnBuyBillDetailEX = new ReturnbuybilldetailMySqlExtDAO();
//Sellandruternbilldetail
$buyAndRuternBillDetailEX = new BuyandruternbilldetailMySqlExtDAO();
//Product
$productDAO = new ProductMySqlDAO();
$productExt = new ProductMySqlExtDAO();
//Productcat
$productCatDAO = new ProductcatMySqlDAO();
$productCatExt = new ProductcatMySqlExtDAO();
//Client
$supplier = new Supplier();
$supplierDAO = new SupplierMySqlDAO();
$supplierEX = new SupplierMySqlExtDAO();
//Productunit
$productUnitDAO = new ProductunitMySqlDAO();
$productUnitExt = new ProductunitMySqlExtDAO();

$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" || $do == "all" || empty($do)) {
    //here the permission check
    include_once("../public/authentication.php");

    $suppliersData = $supplierDAO->queryByCondition(0);
    $smarty->assign("suppliersData", $suppliersData);

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

    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];

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

    if (empty($startDate) && empty($endDate)) {
        $startDate = $endDate = date('Y-m-d');
    }
    //display the converted time ##reportsPlusHours
    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";
    }

    $productId = $_REQUEST['productId'];
    $supplierId = $_REQUEST['supplierId'];

    if (isset($productId) && !empty($productId) && $productId != "-1" && isset($supplierId) && !empty($supplierId) && $supplierId != "-1" && isset($startDate) && isset($endDate) && $startDate != "" && $endDate != "") {
        $productData = $productDAO->load($productId);
        $supplierData = $supplierDAO->load($supplierId);
        $message = " المورد : " . $supplierData->suppliername . "<br> المنتج: " . $productData->productName . "<br> من تاريخ: " . $startDate . " إلى تاريخ: " . $endDate;
        $smarty->assign('message', $message);
        $productIDS = array();
        array_push($productIDS, $productId);
        $smarty->assign('productIDS', $productIDS);

        loadPricesForSupplier($productId, $supplierId, $startDate, $endDate);
    } elseif ((!isset($productId) || !empty($productId) || $productId != "-1" ) && isset($supplierId) && !empty($supplierId) && $supplierId != "-1" && isset($startDate) && isset($endDate) && $startDate != "" && $endDate != "") {
        $allproduct = $productDAO->queryAll();
        $productIDS = array();
        foreach ($allproduct as $value) {
            $productId = $value->productId;
            array_push($productIDS, $productId);
            loadPricesForSupplier($productId, $supplierId, $startDate, $endDate);
            $count++;
        }

        $smarty->assign('productIDS', $productIDS);
    }
    elseif (isset($productId) && !empty($productId) && $productId != "-1" && isset($startDate) && isset($endDate) && $startDate != "" && $endDate != "") {
        $productData = $productDAO->load($productId);
        $message = "المنتج: " . $productData->productName . "<br> من تاريخ: " . $startDate . " إلى تاريخ: " . $endDate;
        $smarty->assign('message', $message);
        $productIDS = array();
        array_push($productIDS, $productId);
        $smarty->assign('productIDS', $productIDS);

        loadPricesForAllSuppliers($productId, $startDate, $endDate);
    } 

    //here the smarty templates
    $smarty->display("suppliersAndProductsReportview/show.html");
    $smarty->assign("settlementstoreshow", 1); //for products (select2)
}
//here the global templates
$smarty->display("footer.html");

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

function loadPricesForSupplier($productId, $supplierId, $startDate, $endDate) {
    global $productExt;
    global $productDAO;
    global $productUnitExt;
    global $smarty;

    $dataArray;

    //get product units' ids
    $unitsdata = $productUnitExt->queryWithProductIdAndConditionExt($productId);

    if (count($unitsdata) > 0) {
        $i = 1;
        foreach ($unitsdata as $u) {
            $returnedArray1 = getProductPriceFromBuyBill($productId, $supplierId, $u->productunitid, $startDate, $endDate);

            // $buybilldetailData = $returnedArray1[0];
            // $buyandruternbilldetailData = $returnedArray1[1];

            // $alldata = array_merge($buybilldetailData, $buyandruternbilldetailData);

            //$smarty->assign("alldata".$i, $alldata);
            $dataArray[$i] = $returnedArray1;

            //$returnedArray2 = getProductPriceFromReturnSellBill($productId, $clientId, $u->productunitid, $startDate, $endDate);
//			$returnsellbilldetailData = $returnedArray2[0];
//			$sellandruternbilldetailData = $returnedArray2[1];
//			$return_alldata = array_merge($returnsellbilldetailData, $sellandruternbilldetailData);
//			$smarty->assign("return_alldata".$i, $return_alldata);

            $i++;
        }

        $smarty->assign("dataArray_" . $productId, $dataArray);
    }
}

function getProductPriceFromBuyBill($ProductId, $supplierId, $productunitid, $startDate, $endDate) {
    global $buyBillDetailEX;
    global $buyAndRuternBillDetailEX;

    $alldata = R::getAll('(SELECT buyandruternbilldetail.buybilldetailprice, buyandruternbilldetail.buybilldetaildate, product.productName, unit.unitName ,productcat.productCatName, buybilldate, buyandruternbill.buybillid, 0 as billtypes, productquantity, buybilldiscount as discounts, suppliername, buybilldetailquantity as quantitys FROM buyandruternbilldetail 
         JOIN buyandruternbill ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
          JOIN supplier ON buyandruternbill.buybillsupplierid = supplier.supplierid
         JOIN product ON buyandruternbilldetail.buybilldetailproductid = product.productId  
         JOIN storedetail ON  (product.productId = storedetail.productid and buyandruternbill.buybillstoreid = storedetail.storeid)
         JOIN productunit ON buyandruternbilldetail.productunitid = productunit.productunitid 
         JOIN unit ON unit.unitId = productunit.unitid 
         join productcat on productcat.productCatId=product.productCatId
          WHERE buyandruternbilldetail.buybilldetailproductid = ' . $ProductId . ' AND buyandruternbilldetail.productunitid = ' . $productunitid . ' AND buyandruternbill.buybillsupplierid = ' . $supplierId . ' AND buyandruternbill.conditions = 0 AND buyandruternbill.buybilldate >= "' . $startDate . '" AND buyandruternbill.buybilldate <= "' . $endDate . '" order by buybilldetailid desc ) union all
        (SELECT buybilldetail.buybilldetailprice, buybilldetail.buybilldetaildate, product.productName, unit.unitName ,productcat.productCatName, buybilldate, buybill.buybillid, 1 as billtypes, productquantity, buybilldiscount as discounts, suppliername, buybilldetailquantity as quantitys FROM buybilldetail JOIN buybill
         ON buybilldetail.buybillid = buybill.buybillid 
         JOIN supplier ON buybill.buybillsupplierid = supplier.supplierid
         JOIN product ON buybilldetail.buybilldetailproductid = product.productId  
         JOIN storedetail ON  (product.productId = storedetail.productid and buybill.buybillstoreid = storedetail.storeid)
         JOIN productunit ON buybilldetail.productunitid = productunit.productunitid JOIN unit
         ON unit.unitId = productunit.unitid JOIN productcat ON productcat.productCatId=product.productCatId WHERE buybilldetail.buybilldetailproductid = ' . $ProductId . ' AND buybilldetail.productunitid = ' . $productunitid . ' AND buybill.buybillsupplierid = ' . $supplierId . ' AND buybill.buybilldate >= "' . $startDate . '" AND buybill.buybilldate <= "' . $endDate . '" AND buybill.conditions = 0 order by buybilldetailid desc) union all
         (SELECT returnbuybilldetail.returnbuybilldetailprice as buybilldetailprice, DATE_FORMAT(returnbuybilldate, "%d-%m-%Y")  as buybilldetaildate, product.productName, unit.unitName ,productcat.productCatName, returnbuybilldate as buybilldate, returnbuybill.returnbuybillid as buybillid, 2 as billtypes, productquantity, returnbuybilldiscount as discounts, suppliername, returnbuybilldetailquantity as quantitys FROM returnbuybilldetail JOIN returnbuybill ON returnbuybilldetail.returnbuybillid = returnbuybill.returnbuybillid 
         JOIN supplier ON returnbuybill.returnbuybillsupplierid = supplier.supplierid
         JOIN product ON returnbuybilldetail.returnbuybilldetailproductid = product.productId 
         JOIN storedetail ON  (product.productId = storedetail.productid and returnbuybill.returnbuybillstoreid= storedetail.storeid)
         JOIN productunit ON returnbuybilldetail.productunitid = productunit.productunitid JOIN unit ON unit.unitId = productunit.unitid join productcat on productcat.productCatId=product.productCatId WHERE returnbuybilldetail.returnbuybilldetailproductid = ' . $ProductId . ' AND returnbuybilldetail.productunitid = ' . $productunitid . ' AND returnbuybill.returnbuybillsupplierid = ' . $supplierId . ' AND returnbuybill.conditions = 0 AND returnbuybill.returnbuybilldate >= "' . $startDate . '" AND returnbuybill.returnbuybilldate <= "' . $endDate . '" order by returnbuybilldetailid desc ) 
        ');

     return json_decode(json_encode($alldata));


    // $buybilldetailData = $buyBillDetailEX->queryWithProductIdAndSupplierIdAndUnitExt($ProductId, $supplierId, $productunitid, $startDate, $endDate);

    // /* print_r('<pre>');
    //   print_r($buybilldetailData);
    //   print_r('</pre>'); */

    // $buyandruternbilldetailData = $buyAndRuternBillDetailEX->queryWithProductIdAndSupplierIdAndUnitExt($ProductId, $supplierId, $productunitid, $startDate, $endDate);

    // /* print_r('<pre>');
    //   print_r($buyandruternbilldetailData);
    //   print_r('</pre>'); */

    // return array($buybilldetailData, $buyandruternbilldetailData);
}

function getProductPriceFromReturnSellBill($ProductId, $ClientId, $productunitid, $startDate, $endDate) {
    /* global $returnSellBillDetailExt;
      global $sellAndRuternBillDetailExt;

      $returnsellbilldetailData = $returnSellBillDetailExt->queryWithProductIdAndClientIdAndUnit($ProductId, $ClientId, $productunitid);

      $sellandruternbilldetailData = $sellAndRuternBillDetailExt->queryWithProductIdAndClientIdAndUnit($ProductId, $ClientId, $productunitid, 1);

      return array($returnsellbilldetailData, $sellandruternbilldetailData); */
}

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

    $dataArray;

    //get product units' ids
    $unitsdata = $productUnitExt->queryWithProductIdAndConditionExt($productId);

    if (count($unitsdata) > 0) {
        $i = 1;
        foreach ($unitsdata as $u) {
            $returnedArray1 = getProductPriceFromBuyBillForAllSuppliers($productId, $u->productunitid, $startDate, $endDate);

            // $buybilldetailData = $returnedArray1[0];
            // $buyandruternbilldetailData = $returnedArray1[1];

            // $alldata = array_merge($buybilldetailData, $buyandruternbilldetailData);

            //$smarty->assign("alldata".$i, $alldata);
            $dataArray[$i] = $returnedArray1;

            //$returnedArray2 = getProductPriceFromReturnSellBill($productId, $clientId, $u->productunitid, $startDate, $endDate);
//			$returnsellbilldetailData = $returnedArray2[0];
//			$sellandruternbilldetailData = $returnedArray2[1];
//			$return_alldata = array_merge($returnsellbilldetailData, $sellandruternbilldetailData);
//			$smarty->assign("return_alldata".$i, $return_alldata);

            $i++;
        }

        $smarty->assign("dataArray_" . $productId, $dataArray);
    }
}

function getProductPriceFromBuyBillForAllSuppliers($ProductId, $productunitid, $startDate, $endDate) {
    global $buyBillDetailEX;
    global $buyAndRuternBillDetailEX;

    $alldata = R::getAll('(SELECT buyandruternbilldetail.buybilldetailprice, buyandruternbilldetail.buybilldetaildate, product.productName, unit.unitName ,productcat.productCatName, buybilldate, buyandruternbill.buybillid, 0 as billtypes, productquantity, buybilldiscount as discounts, suppliername, buybilldetailquantity as quantitys FROM buyandruternbilldetail 
         JOIN buyandruternbill ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
          JOIN supplier ON buyandruternbill.buybillsupplierid = supplier.supplierid
         JOIN product ON buyandruternbilldetail.buybilldetailproductid = product.productId  
         JOIN storedetail ON  (product.productId = storedetail.productid  and buyandruternbill.buybillstoreid = storedetail.storeid)
         JOIN productunit ON buyandruternbilldetail.productunitid = productunit.productunitid 
         JOIN unit ON unit.unitId = productunit.unitid 
         join productcat on productcat.productCatId=product.productCatId
          WHERE buyandruternbilldetail.buybilldetailproductid = ' . $ProductId . ' AND buyandruternbilldetail.productunitid = ' . $productunitid . ' AND buyandruternbill.conditions = 0 AND buyandruternbill.buybilldate >= "' . $startDate . '" AND buyandruternbill.buybilldate <= "' . $endDate . '" order by buybilldetailid desc ) union all
        (SELECT buybilldetail.buybilldetailprice, buybilldetail.buybilldetaildate, product.productName, unit.unitName ,productcat.productCatName, buybilldate, buybill.buybillid, 1 as billtypes, productquantity, buybilldiscount as discounts, suppliername, buybilldetailquantity as quantitys FROM buybilldetail JOIN buybill
         ON buybilldetail.buybillid = buybill.buybillid 
         JOIN supplier ON buybill.buybillsupplierid = supplier.supplierid
         JOIN product ON buybilldetail.buybilldetailproductid = product.productId  
         JOIN storedetail ON  (product.productId = storedetail.productid and buybill.buybillstoreid = storedetail.storeid)
         JOIN productunit ON buybilldetail.productunitid = productunit.productunitid JOIN unit
         ON unit.unitId = productunit.unitid JOIN productcat ON productcat.productCatId=product.productCatId WHERE buybilldetail.buybilldetailproductid = ' . $ProductId . ' AND buybilldetail.productunitid = ' . $productunitid . ' AND buybill.buybilldate >= "' . $startDate . '" AND buybill.buybilldate <= "' . $endDate . '" AND buybill.conditions = 0 order by buybilldetailid desc) union all
         (SELECT returnbuybilldetail.returnbuybilldetailprice as buybilldetailprice, DATE_FORMAT(returnbuybilldate, "%d-%m-%Y")  as buybilldetaildate, product.productName, unit.unitName ,productcat.productCatName, returnbuybilldate as buybilldate, returnbuybill.returnbuybillid as buybillid, 2 as billtypes, productquantity, returnbuybilldiscount as discounts, suppliername, returnbuybilldetailquantity as quantitys FROM returnbuybilldetail JOIN returnbuybill ON returnbuybilldetail.returnbuybillid = returnbuybill.returnbuybillid 
         JOIN supplier ON returnbuybill.returnbuybillsupplierid = supplier.supplierid
         JOIN product ON returnbuybilldetail.returnbuybilldetailproductid = product.productId  
         JOIN storedetail ON  (product.productId = storedetail.productid and returnbuybill.returnbuybillstoreid= storedetail.storeid)
         JOIN productunit ON returnbuybilldetail.productunitid = productunit.productunitid JOIN unit ON unit.unitId = productunit.unitid join productcat on productcat.productCatId=product.productCatId WHERE returnbuybilldetail.returnbuybilldetailproductid = ' . $ProductId . ' AND returnbuybilldetail.productunitid = ' . $productunitid . ' AND returnbuybill.conditions = 0 AND returnbuybill.returnbuybilldate >= "' . $startDate . '" AND returnbuybill.returnbuybilldate <= "' . $endDate . '" order by returnbuybilldetailid desc ) 
        ');

     return json_decode(json_encode($alldata));


    // $buybilldetailData = $buyBillDetailEX->queryWithProductIdAndSupplierIdAndUnitExt($ProductId, $supplierId, $productunitid, $startDate, $endDate);

    // /* print_r('<pre>');
    //   print_r($buybilldetailData);
    //   print_r('</pre>'); */

    // $buyandruternbilldetailData = $buyAndRuternBillDetailEX->queryWithProductIdAndSupplierIdAndUnitExt($ProductId, $supplierId, $productunitid, $startDate, $endDate);

    // /* print_r('<pre>');
    //   print_r($buyandruternbilldetailData);
    //   print_r('</pre>'); */

    // return array($buybilldetailData, $buyandruternbilldetailData);
}
