<?php

//the global file operation
include("../public/impOpreation.php");
include("../library/regenerateSession.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");
//Sellbill
require_once('../models/dao/SellbillDAO.class.php');
require_once('../models/dto/Sellbill.class.php');
require_once('../models/mysql/SellbillMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbillMySqlExtDAO.class.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');
//Returnsellbill
require_once('../models/dao/ReturnsellbillDAO.class.php');
require_once('../models/dto/Returnsellbill.class.php');
require_once('../models/mysql/ReturnsellbillMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnsellbillMySqlExtDAO.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');
//Sellbillandrutern
require_once('../models/dao/SellbillandruternDAO.class.php');
require_once('../models/dto/Sellbillandrutern.class.php');
require_once('../models/mysql/SellbillandruternMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbillandruternMySqlExtDAO.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');
//Storereport
require_once('../models/dao/StorereportDAO.class.php');
require_once('../models/dto/Storereport.class.php');
require_once('../models/mysql/StorereportMySqlDAO.class.php');
require_once('../models/mysql/ext/StorereportMySqlExtDAO.class.php');
//Storedetail
require_once('../models/dao/StoredetailDAO.class.php');
require_once('../models/dto/Storedetail.class.php');
require_once('../models/mysql/StoredetailMySqlDAO.class.php');
require_once('../models/mysql/ext/StoredetailMySqlExtDAO.class.php');
//Store
require_once('../models/dao/StoreDAO.class.php');
require_once('../models/dto/Store.class.php');
require_once('../models/mysql/StoreMySqlDAO.class.php');
require_once('../models/mysql/ext/StoreMySqlExtDAO.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');
//Save
require_once('../models/dao/SaveDAO.class.php');
require_once('../models/dto/Save.class.php');
require_once('../models/mysql/SaveMySqlDAO.class.php');
require_once('../models/mysql/ext/SaveMySqlExtDAO.class.php');
//Savedaily
require_once('../models/dao/SavedailyDAO.class.php');
require_once('../models/dto/Savedaily.class.php');
require_once('../models/mysql/SavedailyMySqlDAO.class.php');
require_once('../models/mysql/ext/SavedailyMySqlExtDAO.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');
//Clientdebtchange
require_once('../models/dao/ClientdebtchangeDAO.class.php');
require_once('../models/dto/Clientdebtchange.class.php');
require_once('../models/mysql/ClientdebtchangeMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientdebtchangeMySqlExtDAO.class.php');
//Billname
require_once('../models/dao/BillnameDAO.class.php');
require_once('../models/dto/Billname.class.php');
require_once('../models/mysql/BillnameMySqlDAO.class.php');
require_once('../models/mysql/ext/BillnameMySqlExtDAO.class.php');
//Billsetting
require_once('../models/dao/BillsettingsDAO.class.php');
require_once('../models/dto/Billsetting.class.php');
require_once('../models/mysql/BillsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsettingsMySqlExtDAO.class.php');
//User
require_once('../models/dao/UserDAO.class.php');
require_once('../models/dto/User.class.php');
require_once('../models/mysql/UserMySqlDAO.class.php');
require_once('../models/mysql/ext/UserMySqlExtDAO.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');
//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');
//Unit
require_once('../models/dao/UnitDAO.class.php');
require_once('../models/dto/Unit.class.php');
require_once('../models/mysql/UnitMySqlDAO.class.php');
require_once('../models/mysql/ext/UnitMySqlExtDAO.class.php');
//Productserial
require_once('../models/dao/ProductserialDAO.class.php');
require_once('../models/dto/Productserial.class.php');
require_once('../models/mysql/ProductserialMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductserialMySqlExtDAO.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');
//Soldserialproduct
require_once('../models/dao/SoldserialproductDAO.class.php');
require_once('../models/dto/Soldserialproduct.class.php');
require_once('../models/mysql/SoldserialproductMySqlDAO.class.php');
require_once('../models/mysql/ext/SoldserialproductMySqlExtDAO.class.php');


//######################### بداية المشتريات
//Buybill
require_once('../models/dao/BuybillDAO.class.php');
require_once('../models/dto/Buybill.class.php');
require_once('../models/mysql/BuybillMySqlDAO.class.php');
require_once('../models/mysql/ext/BuybillMySqlExtDAO.class.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');
//Returnbuybill
require_once('../models/dao/ReturnbuybillDAO.class.php');
require_once('../models/dto/Returnbuybill.class.php');
require_once('../models/mysql/ReturnbuybillMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnbuybillMySqlExtDAO.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');
//Buyandruternbill
require_once('../models/dao/BuyandruternbillDAO.class.php');
require_once('../models/dto/Buyandruternbill.class.php');
require_once('../models/mysql/BuyandruternbillMySqlDAO.class.php');
require_once('../models/mysql/ext/BuyandruternbillMySqlExtDAO.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');
//Supplierdebtchange
require_once('../models/dao/SupplierdebtchangeDAO.class.php');
require_once('../models/dto/Supplierdebtchange.class.php');
require_once('../models/mysql/SupplierdebtchangeMySqlDAO.class.php');
require_once('../models/mysql/ext/SupplierdebtchangeMySqlExtDAO.class.php');
//Store
require_once('../models/dao/StoreDAO.class.php');
require_once('../models/dto/Store.class.php');
require_once('../models/mysql/StoreMySqlDAO.class.php');
require_once('../models/mysql/ext/StoreMySqlExtDAO.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');
//Usergroup
require_once('../models/dao/UsergroupDAO.class.php');
require_once('../models/dto/Usergroup.class.php');
require_once('../models/mysql/UsergroupMySqlDAO.class.php');
require_once('../models/mysql/ext/UsergroupMySqlExtDAO.class.php');
//get the do the action
$do = $_GET['do'];

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

  Controller Name :- sellBillReportsController

  OPERTATION in Controller

  1- show sell bills only
  2- search

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

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

//here goes the instances and general variables
//Sellbill
$mySellbill = new Sellbill();
$mySellbillRecord = new SellbillMySqlDAO();
$mySellbillEx = new SellbillMySqlExtDAO();
//Sellbilldetail
$mySellbilldetail = new Sellbilldetail();
$mySellbilldetailRecord = new SellbilldetailMySqlDAO();
$mySellbilldetailEx = new SellbilldetailMySqlExtDAO();
//Returnsellbill
$myReturnsellbill = new Returnsellbill();
$myReturnsellbillRecord = new ReturnsellbillMySqlDAO();
$myReturnsellbillEx = new ReturnsellbillMySqlExtDAO();
//Returnsellbilldetail
$myReturnsellbilldetail = new Returnsellbilldetail();
$myReturnsellbilldetailRecord = new ReturnsellbilldetailMySqlDAO();
$myReturnsellbilldetailEX = new ReturnsellbilldetailMySqlExtDAO();

//Sellbillandrutern
$mySellbillandrutern = new Sellbillandrutern();
$mySellbillandruternRecord = new SellbillandruternMySqlDAO();
$mySellbillandruternEx = new SellbillandruternMySqlExtDAO();
//Sellandruternbilldetail
$mySellandruternbilldetail = new Sellandruternbilldetail();
$mySellandruternbilldetailRecord = new SellandruternbilldetailMySqlDAO();
$mySellandruternbilldetailEx = new SellandruternbilldetailMySqlExtDAO();
//Storereport
$myStorereport = new Storereport();
$myStorereportRecord = new StorereportMySqlDAO();
$myStorereportEx = new StorereportMySqlExtDAO();
//Storedetail
$myStoredetailEx = new StoredetailMySqlExtDAO();
$myStoredetailRecord = new StoredetailMySqlDAO();
//Store
$myStoreRecord = new StoreMySqlDAO();
//Product
$myProductRecord = new ProductMySqlDAO();
$myProductEx = new ProductMySqlExtDAO();
//save
$mySave = new Save();
$mySaveRecord = new SaveMySqlDAO();
$mySaveEx = new SaveMySqlExtDAO();
//savedaily
$mySavedaily = new Savedaily();
$mySavedailyRecord = new SavedailyMySqlDAO();
$mySavedailyEx = new SavedailyMySqlExtDAO();
//Client
$myClientRecord = new ClientMySqlDAO();
$myClient = new Client();
$myClientEx = new ClientMySqlExtDAO();
//Clientdebtchange
$myClientdebtchange = new Clientdebtchange();
$myClientdebtchangeRecord = new ClientdebtchangeMySqlDAO();
//Billname
$myBillnameRecord = new BillnameMySqlDAO();
$myBillnameEx = new BillnameMySqlExtDAO();
//Billsetting
$myBillsettingEx = new BillsettingsMySqlExtDAO();
//User
$myUserEx = new UserMySqlExtDAO();
//Productunit
$myProductunitEx = new ProductunitMySqlExtDAO();
//Productcat
$myProductcatRecord = new ProductcatMySqlDAO();
$myProductcatEx = new ProductcatMySqlExtDAO();
//Unit
$myUnitRecord = new UnitMySqlDAO();
//Productserial
$myProductserial = new Productserial();
$myProductserialEx = new ProductserialMySqlExtDAO();
//Programsetting
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$programSettingExt = new ProgramsettingsMySqlExtDAO();
//Soldserialproduct
$soldSerialProduct = new Soldserialproduct();
$soldSerialProductDAO = new SoldserialproductMySqlDAO();
$soldSerialProductExt = new SoldserialproductMySqlExtDAO();

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


//###################### بداية المشتريات
//Buybill
$buyBill = new Buybill();
$buyBillDAO = new BuybillMySqlDAO();
$buyBillExt = new BuybillMySqlExtDAO();
//Buybilldetail
$buyBillDetail = new Buybilldetail();
$buyBillDetailDAO = new BuybilldetailMySqlDAO();
$buyBillDetailExt = new BuybilldetailMySqlExtDAO();
//Returnbuybill
$returnBuyBill = new Returnbuybill();
$returnBuyBillDAO = new ReturnbuybillMySqlDAO();
$returnBuyBillExt = new ReturnbuybillMySqlExtDAO();
//Returnbuybilldetail
$returnBuyBillDetail = new Returnbuybilldetail();
$returnBuyBillDetailDAO = new ReturnbuybilldetailMySqlDAO();
$returnBuyBillDetailExt = new ReturnbuybilldetailMySqlExtDAO();
//Buyandruternbill
$buyAndReturnBill = new Buyandruternbill();
$buyAndReturnBillDAO = new BuyandruternbillMySqlDAO();
$buyAndReturnBillExt = new BuyandruternbillMySqlExtDAO();
//Buyandruternbilldetail
$buyAndReturnBillDetail = new Buyandruternbilldetail();
$buyAndReturnBillDetailDAO = new BuyandruternbilldetailMySqlDAO();
$buyAndReturnBillDetailExt = new BuyandruternbilldetailMySqlExtDAO();
//Product
$product = new Product();
$productDAO = new ProductMySqlDAO();
$productExt = new ProductMySqlExtDAO();
//Productcat
$productCatDAO = new ProductcatMySqlDAO();
$productCatExt = new ProductcatMySqlExtDAO();
//Supplier
$supplier = new Supplier();
$supplierDAO = new SupplierMySqlDAO();
$supplierExt = new SupplierMySqlExtDAO();
//Supplierdebtchange
$supplierDebtChange = new Supplierdebtchange();
$supplierDebtChangeDAO = new SupplierdebtchangeMySqlDAO();
$supplierDebtChangeExt = new SupplierdebtchangeMySqlExtDAO();
//Store
$storeDAO = new StoreMySqlDAO();
$storeExt = new StoreMySqlExtDAO();
//Unit
$unitDAO = new UnitMySqlDAO();
$unitExt = new UnitMySqlExtDAO();
//Billname
$billNameDAO = new BillnameMySqlDAO();
$billNameExt = new BillnameMySqlExtDAO();
//Billsetting
$billSettingExt = new BillsettingsMySqlExtDAO();
//Storedetail
$storeDetail = new Storedetail();
$storeDetailDAO = new StoredetailMySqlDAO();
$storeDetailExt = new StoredetailMySqlExtDAO();
//Storereport
$storeReport = new Storereport;
$storeReportDAO = new StorereportMySqlDAO();
$storeReportExt = new StorereportMySqlExtDAO();
//Save
$save = new Save();
$saveDAO = new SaveMySqlDAO();
$saveExt = new SaveMySqlExtDAO();
//Savedaily
$saveDaily = new Savedaily();
$saveDailyDAO = new SavedailyMySqlDAO();
$saveDailyExt = new SavedailyMySqlExtDAO();
//Productserial
$Productserial = new Productserial();
$ProductserialDAO = new ProductserialMySqlDAO();
$ProductserialEX = new ProductserialMySqlExtDAO();
//Usergroup
$Usergroup = new Usergroup();
$UsergroupDAO = new UsergroupMySqlDAO();
$UsergroupEX = new UsergroupMySqlExtDAO();

$Programsetting = $Programsettingdata = $ProgramsettingDAO->load(1);
$smarty->assign("Programsettingdata", $Programsettingdata);
$Usergroupdata = $UsergroupDAO->load($_SESSION['usergroupid']);
$smarty->assign("Usergroupdata", $Usergroupdata);

//check and use the condition that suites this action
if (empty($do) || $do == "show") {
    //# تقرير المبيعات
    include_once("../public/authentication.php");

    $storeData = $myStoreRecord->queryByConditions(0);
    $smarty->assign("storeData", $storeData);

    //    $clientData = loadAllClient();
    //    $smarty->assign("clientData", $clientData);

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

    $suppliersData = getSuppliers(); //undeleted suppliers
    $smarty->assign("suppliersData", $suppliersData);

    $billSerials = showBuyBills();
    $smarty->assign("billSerials", $billSerials);

    $smarty->display("allReportsOnOne/headReport.html");

    //# بيانات المبيعات
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid'];
    $clientId = (int) $_REQUEST['clientid'];

    //# بيانات المشتريات
    $supplierId = (int) $_REQUEST['supplierId'];
    $serial = $_REQUEST['sellbillserial'];
    $buybillid = $_REQUEST['sellbillid'];
    $from = $_REQUEST['from'];
    $to = $_REQUEST['to'];
    //#common
    $storeId = $_REQUEST['storeId'];

    $rep1 = $_REQUEST['rep1']; //# تقرير المبيعات
    $rep2 = $_REQUEST['rep2']; //# تقرير مردود المبيعات
    $rep3 = $_REQUEST['rep3']; //# تقرير المبيعات و مردود المبيعات
    $rep4 = $_REQUEST['rep4']; //# تقرير المشتريات
    $rep5 = $_REQUEST['rep5']; //# تقرير مردودات المشتريات
    $rep6 = $_REQUEST['rep6']; //# تقرير المشتريات ومردود المشتريات
    $smarty->assign("customCheck", 1);

    //# no client
    if ($clientId > 0) {

        if ($rep1 == '1') {
            //# تقرير المبيعات
            showAll();
            $smarty->display("allReportsOnOne/sellBillReport.html");
        }

        if ($rep2 == '1') {
            //# تقرير  مردودات المبيعات
            returnshowAll();
            $smarty->display("allReportsOnOne/sellReturnReport.html");
        }

        if ($rep3 == '1') {
            //# تقرير المبيعات و المردود
            showAll();
            returnshowAll();
            $smarty->display("allReportsOnOne/sellandreturnreport.html");
        }
    }



    //################## بداية المشتريات
    if ($supplierId > 0) {

        if ($rep4 == '1') {
            //# تقرير فواتير المشتريات
            showBuyBill();

            $smarty->display("allReportsOnOne/buyBill.html");
        }

        if ($rep5 == '1') {
            //# مردودات المشتريات
            showBuyReturn();
            $smarty->display("allReportsOnOne/buyReturn.html");
        }

        if ($rep6 == '1') {
            //# فواتير ومردودات المشتريات
            showBuyReturnAndBuy();
            $smarty->display("allReportsOnOne/buyReturnAndBills.html");
        }
    }

    if ($clientId < 1 && $supplierId < 1) {

        if ($_REQUEST['from'] == '' && $_REQUEST['to'] == '') {
            $startDate = date("Y-m-d");
            $endDate = date("Y-m-d");
            $from = date("Y-m-d");
            $to = date("Y-m-d");
        } else if ($_REQUEST['from'] != '' && $_REQUEST['to'] != '') {
            $startDate = $_REQUEST['from'];
            $endDate = $_REQUEST['to'];
            $from = $_REQUEST['from'];
            $to = $_REQUEST['to'];
        }


        if ($rep1 == '1') {
            //# تقرير المبيعات
            showAll();
            $smarty->display("allReportsOnOne/sellBillReport.html");
        }

        if ($rep2 == '1') {
            //# تقرير  مردودات المبيعات
            returnshowAll();
            $smarty->display("allReportsOnOne/sellReturnReport.html");
        }
        if ($rep3 == '1') {
            # تقرير المبيعات و المردود
            showAll();
            returnshowAll();
            $smarty->display("allReportsOnOne/sellandreturnreport.html");
        }

        if ($rep4 == '1') {
            # تقرير فواتير المشتريات
            showBuyBill();
            $smarty->display("allReportsOnOne/buyBill.html");
        }


        if ($rep5 == '1') {
            # مردودات المشتريات
            showBuyReturn();
            $smarty->display("allReportsOnOne/buyReturn.html");
        }

        if ($rep6 == '1') {
            //# فواتير ومردودات المشتريات
            showBuyReturnAndBuy();
            $smarty->display("allReportsOnOne/buyReturnAndBills.html");
        }
    }
} elseif ($do == "sellandreturnreport") {
    //# تقرير المبيعات و المردود
    $clientData = loadAllClient();
    $smarty->assign("clientData", $clientData);
    showAll();
    returnshowAll();
    $smarty->display("sellBillReportsview/sellandreturnreport.html");
} elseif ($do == "sellDetail") {
    include_once("../public/authentication.php");

    $sellbillId = $_GET['sellbillid'];

    $showDetailData = showDetail($sellbillId);

    $smarty->assign('sellbillData', $showDetailData[0]);
    $smarty->assign('sellbilldetailData', $showDetailData[1]);
    $smarty->assign("quantity", $showDetailData[2]);

    if ($showDetailData[0]->billnameid != 0 && isset($showDetailData[0]->billnameid) && $showDetailData[0]->billnameid != "" && !empty($showDetailData[0]->billnameid)) {
        $billsettingsData = loadBillProperty($showDetailData[0]->billnameid);
        $smarty->assign("billsettingsData", $billsettingsData);

        $billnameAddData = $myBillnameRecord->load($showDetailData[0]->billnameid);
        $smarty->assign("billnameAddData", $billnameAddData);
    }
    $Programsettingdata = $ProgramsettingDAO->load(1);
    $smarty->assign("Programsettingdata", $Programsettingdata);

    $smarty->display("sellBillReportsview/sellDetail.html");
} elseif ($do == "sellAndReturnDetail") {
    include_once("../public/authentication.php");

    $sellbillId = $_GET['sellbillid'];

    $showDetailData = showsellAndReturnDetail($sellbillId);
    $smarty->assign('sellbillandruternData', $showDetailData[0]);
    $smarty->assign('sellbilldetailData', $showDetailData[1]);
    $smarty->assign('ruternbilldetailData', $showDetailData[2]);
    $smarty->assign('sellQuantity', $showDetailData[3]);
    $smarty->assign('returnQuantity', $showDetailData[4]);

    $billsettingsData = loadBillProperty($showDetailData[0]->billnameid);
    $smarty->assign("billsettingsData", $billsettingsData);

    $billnameAddData = $myBillnameRecord->load($showDetailData[0]->billnameid);
    $smarty->assign("billnameAddData", $billnameAddData);

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

    //here the smarty templates
    $smarty->display("sellBillReportsview/sellAndReturnDetail.html");
}


$smarty->assign("customValidation", 1);

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

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

//select data from client tbl
function loadAllClient()
{
    global $myClientRecord;

    $clientData = $myClientRecord->queryAll();
    return $clientData;
}

function loadAllSellBill()
{
    global $mySellbillRecord;

    // select all data from sellbill tbl
    $sellbillSearchData = $mySellbillRecord->queryAll();
    return $sellbillSearchData;
}

// select from Billname tbl
function loadBillname()
{
    global $myBillnameEx;

    // select all data from Billname
    $billnameData = $myBillnameEx->queryByType(0);
    return $billnameData;
}

// select from Billname tbl
function loadBillProperty($billnameid)
{
    global $myBillsettingEx;

    // select all data from Billname
    $billsettingsData = $myBillsettingEx->queryWithBillnameId($billnameid);
    return $billsettingsData;
}

//load sellbill detail
function showDetail($sellbillid)
{
    //to use the variable out side the funcion
    global $mySellbillEx;
    global $mySellbilldetailEx;


    //load purchasebillreturn data by id
    $sellbillData = $mySellbillEx->loadSellBillById($sellbillid);

    $sellbilldetailData = $mySellbilldetailEx->queryWithSellBillId($sellbillid);
    $quantity = 0;
    foreach ($sellbilldetailData as $sellbilldetail) {
        $quantity = $quantity + ($sellbilldetail->sellbilldetailquantity);
    }
    return array($sellbillData, $sellbilldetailData, $quantity);
}

function showsellAndReturnDetail($sellbillid)
{
    global $mySellbillandruternEx;
    global $mySellandruternbilldetailEx;

    //load Sellbillandrutern data by id
    $sellbillandruternData = $mySellbillandruternEx->loadSellbillandruternById($sellbillid);

    $sellbilldetailData = $mySellandruternbilldetailEx->queryWithSellBillIdAndSellType($sellbillid, 0);

    $sellQuantity = 0;
    foreach ($sellbilldetailData as $sellbilldetail) {
        $sellQuantity = $sellQuantity + $sellbilldetail->sellbilldetailquantity;
    }

    $ruternbilldetailData = $mySellandruternbilldetailEx->queryWithSellBillIdAndSellType($sellbillid, 1);

    $returnQuantity = 0;
    foreach ($ruternbilldetailData as $ruternbilldetail) {
        $returnQuantity = $returnQuantity + $ruternbilldetail->sellbilldetailquantity;
    }

    return array($sellbillandruternData, $sellbilldetailData, $ruternbilldetailData, $sellQuantity, $returnQuantity);
}

function showAll()
{
    global $mySellbillEx;
    global $mySellbillandruternEx;
    global $myClientRecord;

    global $mySellandruternbilldetailEx;
    global $mySellbilldetailEx;

    global $myStoreRecord;

    global $smarty;
    global $Programsetting;


    //print('11xxxxxxxxxxxxxxxxxxx');
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid'];
    $clientId = $_REQUEST['clientid'];
    $storeId = $_REQUEST['storeId'];
    $obgyBillType = $_REQUEST['obgyBillType'];
    $proSellTrackingSerial = $_REQUEST['proSellTrackingSerial'];

    $message = '';
    $queryString = ' WHERE';
    $returnqueryString = ' WHERE';
    $queryStringDetails = '';

    if (isset($obgyBillType) && $obgyBillType > 0) {
        //$message .= $message . '<br>' . "إسم المخزن: " . $messageData->storeName;
        $queryString .= ' obgyBillType = ' . $obgyBillType . ' AND';

        $returnqueryString .= ' obgyBillType = ' . $obgyBillType . ' AND';
    }
    if (isset($storeId) && $storeId > 0) {
        $messageData = $myStoreRecord->load($storeId);
        $message .= $message . '<br>' . "إسم المخزن: " . $messageData->storeName;
        $queryString .= ' sellbillstoreid = ' . $storeId . ' AND';

        $returnqueryString .= ' returnsellbill.returnsellbillstoreid = ' . $storeId . ' AND';
    }

    if (isset($clientId) && $clientId > 0) {
        $messageData = $myClientRecord->load($clientId);
        $message .= $message . '<br>' . "إسم العميل: " . $messageData->clientname;

        $queryString .= ' sellbillclientid = ' . $clientId . ' AND';
        $returnqueryString .= ' returnsellbill.returnsellbillclientid = ' . $clientId . ' AND';
    }

    if (isset($sellbillSerial) && $sellbillSerial != '') {

        showBySriral($sellbillSerial);

        $message = $message . '<br>' . "رقم الفاتورة: " . $sellbillSerial;

        $queryString .= ' sellbillSerial = "' . $sellbillSerial . '" AND';
        $returnqueryString .= ' returnsellbill.returnsellbillserial = "' . $sellbillSerial . '" AND';
    }

    if (isset($sellbillId) && $sellbillId != '') {
        showBySellbillId($sellbillId);
        $message = $message . '<br>' . "الرقم المتسلسل : " . $sellbillId;

        $queryString .= ' sellbillid = "' . $sellbillId . '" AND';
    }

    if (!empty($startDate) && !empty($endDate)) {
        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 = $message . '<br>' . 'من تاريخ : ' . $startDate . 'إلى تاريخ : ' . $endDate . '';

        $queryString .= ' sellbilldate >= "' . $startDate . '" AND sellbilldate <= "' . $endDate . '" AND';
    } elseif (empty($startDate) && empty($endDate) && empty($sellbillSerial) && empty($sellbillid) && !isset($clientId)) {
        $startDate = $endDate = date("Y-m-d");
        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 = $message . '<br>' . "من تاريخ : " . $startDate . " إلى تاريخ : " . $endDate . "";

        $queryString .= ' sellbilldate >= "' . $startDate . '" AND sellbilldate <= "' . $endDate . '" AND';
    }
    $smarty->assign("message", $message);

    $arr = explode(' ', $queryString);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);
        if ($lastWord == 'AND') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        } else if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            $queryString = '';
        }
        //print("<br>queryString: ".$queryString."<br>");
    }



    if (!empty($proSellTrackingSerial)) {
        $message .= "<br> Serial : " . $proSellTrackingSerial;
        $queryStringDetails = ' and proSellTrackingSerial="' . $proSellTrackingSerial . '" ';
    }

    $queryStringDetailIds = '';
    $queryStringDetailIdsRet = '';
    if (!empty($queryStringDetails)) {
        $detailsData = R::getAll('SELECT distinct sellbillid FROM sellbilldetail where 1 ' . $queryStringDetails);
        $sellbillidS = '';
        foreach ($detailsData as $value) {
            $sellbillidS .= $value['sellbillid'] . ",";
        }
        $sellbillidS = trim($sellbillidS, ",");
        $sellbillidS = empty($sellbillidS) ? 0 : $sellbillidS;
        $queryStringDetailIds = " and sellbillid in (" . $sellbillidS . ")";
        #
        $detailsDataRet = R::getAll('SELECT distinct sellbillid FROM sellandruternbilldetail where 1 ' . $queryStringDetails);
        $sellbillidSRet = '';
        foreach ($detailsDataRet as $value) {
            $sellbillidSRet .= $value['sellbillid'] . ",";
        }
        $sellbillidSRet = trim($sellbillidSRet, ",");
        $sellbillidSRet = empty($sellbillidSRet) ? 0 : $sellbillidSRet;
        $queryStringDetailIdsRet = " and sellbillid in (" . $sellbillidSRet . ")";
    }

    if (empty($queryString)) {
        $queryString .= ' where 1 ';
    }
    $totalBills = 0;
    $totalQTY = 0;
    $totaltax = 0;
    $totaldiscount = 0;
    $totalpayed = 0;

    $sellbillData = R::getAll('SELECT sellbillid,sellbilldate,sellbilldiscount,sellbilldiscounttype,sellbilltotalbill,sellQuantity,pricetype,sellbilltotalpayed,sellbillfinalbill,sellbillaftertotalbill,sellbillserial,conditions,
                                   userid,sellerid,delbyuserid,sellbillclientid,sellbillstoreid,sellbillsaveid,deletedsellid
                                   FROM sellbill ' . $queryString . $queryStringDetailIds . ' AND sellbill.conditions=0 '); //. ' ORDER BY sellbilldate desc ,sellbillid desc '
    $sellbillandruternData = R::getAll('SELECT sellbillid,sellbilldate,sellbilldiscount,sellbilldiscounttype,sellbilltotalbill,sellQuantity,pricetype,sellbilltotalpayed,sellbillfinalbill,sellbillaftertotalbill,sellbillserial,conditions,
                                   userid,sellerid,delbyuserid,sellbillclientid,sellbillstoreid,sellbillsaveid,deletedsellid
                                   FROM sellbillandrutern ' . $queryString . $queryStringDetailIdsRet . ' AND sellbillandrutern.conditions=0 '); //. ' ORDER BY sellbilldate desc ,sellbillid desc '

    list($billIDs, $billIDsRet, $userIDs, $clientIDs) = [[], [], [], []];
    foreach ($sellbillData as $value) {
        $billIDs[] = $value['sellbillid'];
        $userIDs[] = $value['userid'];
        $userIDs[] = $value['sellerid'];
        //        $sellerAndDelByIDs[] = $value['sellerid'];
        //        $sellerAndDelByIDs[] = $value['delbyuserid'];
        $clientIDs[] = $value['sellbillclientid'];
    }
    foreach ($sellbillandruternData as $value) {
        $billIDsRet[] = $value['sellbillid'];
        $userIDs[] = $value['userid'];
        $userIDs[] = $value['sellerid'];
        //        $sellerAndDelByIDs[] = $value['sellerid'];
        //        $sellerAndDelByIDs[] = $value['delbyuserid'];
        $clientIDs[] = $value['sellbillclientid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $billIDsRet = !empty($billIDsRet) ? array_unique($billIDsRet) : [0];
    $userIDs = !empty($userIDs) ? array_unique($userIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];




    //////$sellbillData = $mySellbillEx->queryallWithOrder($queryString);
    $userDataArr = R::getAll('select userid,employeename from user where userid in(' . implode(',', $userIDs) . ') ');
    $userDataArr = customArrayIndexOne($userDataArr, 'userid');
    ##
    $clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ');
    $clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
    ##
    $discountDataArr = R::getAll('SELECT sellbillid,sum(discountvalue) as sumdiscountvalue FROM sellbilldetail where sellbillid in(' . implode(',', $billIDs) . ') group by sellbillid');
    $discountDataArr = customArrayIndexOne($discountDataArr, 'sellbillid');
    $discountDataArrRet = R::getAll('SELECT sellbillid,sum(discountvalue) as sumdiscountvalue FROM sellandruternbilldetail where sellbillid in(' . implode(',', $billIDsRet) . ') group by sellbillid');
    $discountDataArrRet = customArrayIndexOne($discountDataArrRet, 'sellbillid');


    $sellBillDataTemp = array();
    foreach ($sellbillData as $value) {
        if ($userDataArr[$value['userid']] !== null) {
            $value['username'] = $userDataArr[$value['userid']]['employeename'];
            $value['employeename'] = $userDataArr[$value['sellerid']]['employeename'];
            $value['clientname'] = $clientDataArr[$value['sellbillclientid']]['clientname'];
            $value['sellbilltotalpayed'] = (float) $value['sellbilltotalpayed'];
            $value['sumdiscountvalue'] = (float) $discountDataArr[$value['sellbillid']]['sumdiscountvalue'];

            /* $billTotalBeforeDiscount = $value['sellbilltotalbill'];
              $sellbillDiscountType = $value['sellbilldiscounttype'];
              $sellbillDiscount = $value['sellbilldiscount'];
              if ($sellbillDiscountType == "2") {
              $billDiscountVal = ($sellbillDiscount / 100) * $billTotalBeforeDiscount;
              $billDiscountVal = round($billDiscountVal, $noOfDecimalPlaces);
              $value['sellbilldiscount'] = $billDiscountVal;
              } */
            ##make it obj
            $obj = new stdClass();
            foreach ($value as $key1 => $value1) {
                $obj->$key1 = $value1;
            }
            $sellBillDataTemp[] = $obj;
            //$sellBillDataTemp[] = $value;
        }
    }
    $sellbillData = $sellBillDataTemp;

    $sellBillDataTemp = array();
    foreach ($sellbillandruternData as $value) {
        if ($userDataArr[$value['userid']] !== null) {
            $value['username'] = $userDataArr[$value['userid']]['employeename'];
            $value['employeename'] = $userDataArr[$value['sellerid']]['employeename'];
            $value['clientname'] = $clientDataArr[$value['sellbillclientid']]['clientname'];
            $value['sellbilltotalpayed'] = (float) $value['sellbilltotalpayed'];
            $value['sumdiscountvalue'] = (float) $discountDataArrRet[$value['sellbillid']]['sumdiscountvalue'];
            ##make it obj
            $obj = new stdClass();
            foreach ($value as $key1 => $value1) {
                $obj->$key1 = $value1;
            }
            $sellBillDataTemp[] = $obj;
            //$sellBillDataTemp[] = $value;
        }
    }
    $sellbillandruternData = $sellBillDataTemp;





    //$sellbillData = $mySellbillEx->querybyqueryString($queryString);
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $totalpayed += $sell->sellbilltotalpayed;
                $sell->type = "sell";


                //getalldiscount
                $sellbilldiscount = $sell->sellbilldiscount;
                $sellbilldiscounttype = $sell->sellbilldiscounttype; //1 مباشر
                $sellbilltotalbill = $sell->sellbilltotalbill;
                $sellbillaftertotalbill = $sell->sellbillaftertotalbill;
                $tax = $sell->tax;

                $detaildiscount = $sell->sumdiscountvalue; //$mySellbilldetailEx->getsumdiscount($sell->sellbillid);
                if ($sellbilldiscounttype == 1) {


                    //discount
                    $totaldiscount = $totaldiscount + $sellbilldiscount + $detaildiscount;
                    $sell->sellbilldiscount = ($sellbilldiscount + $detaildiscount);




                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($sellbilldiscount));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                } else {
                    //discount
                    $discountvalue = ($sellbilltotalbill / 100) * $sellbilldiscount;
                    $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
                    $sell->sellbilldiscount = ($discountvalue + $detaildiscount);

                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($discountvalue));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                }
            }
        }
    }

    //get sell bills from sellbillandrutern table
    //$sellbillandruternData = $mySellbillandruternEx->GetSellBillAndReturnUsingQueryString($queryString);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $totalpayed += $sell->sellbilltotalpayed;
                $sell->type = "sellAndReturn";




                //getalldiscount
                $sellbilldiscount = $sell->sellbilldiscount;
                $sellbilldiscounttype = $sell->sellbilldiscounttype; //1 مباشر
                $sellbilltotalbill = $sell->sellbilltotalbill;
                $sellbillaftertotalbill = $sell->sellbillaftertotalbill;
                $tax = $sell->tax;

                $detaildiscount = $sell->sumdiscountvalue; //$mySellandruternbilldetailEx->getsumdiscount($sell->sellbillid, 0);
                if ($sellbilldiscounttype == 1) {
                    //discount
                    $totaldiscount = $totaldiscount + $sellbilldiscount + $detaildiscount;
                    $sell->sellbilldiscount = ($sellbilldiscount + $detaildiscount);


                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($sellbilldiscount));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                } else {
                    //discount
                    $discountvalue = ($sellbilltotalbill / 100) * $sellbilldiscount;
                    $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
                    $sell->sellbilldiscount = ($discountvalue + $detaildiscount);
                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($discountvalue));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                }
            }
        }
    }

    $sellbillData = array_merge($sellbillData, $sellbillandruternData);

    $countSell = count($sellbillData);
    $smarty->assign('countSell', $countSell);

    // assign your db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalQTY', $totalQTY);
    $smarty->assign('totaldiscount', $totaldiscount);
    $smarty->assign('totaltax', $totaltax);
    $smarty->assign('totalpayedSell', $totalpayed);
}

function returnshowAll()
{
    global $myReturnsellbillEx;
    global $myReturnsellbilldetailEX;
    global $myReturnsellbillEx;

    global $mySellbillandruternEx;
    global $mySellandruternbilldetailEx;
    global $mySellbilldetailEx;
    global $myClientRecord;
    global $myStoreRecord;

    global $smarty;
    global $Programsetting;


    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $sellbillSerial = $_REQUEST['sellbillserial'];
    $sellbillId = $_REQUEST['sellbillid'];
    $clientId = $_REQUEST['clientid'];
    $storeId = $_REQUEST['storeId'];
    $obgyBillType = $_REQUEST['obgyBillType'];
    $proSellTrackingSerial = $_REQUEST['proSellTrackingSerial'];


    $message = '';
    $queryString = ' WHERE';
    $returnqueryString = ' WHERE';
    $queryStringDetails = '';
    if (isset($obgyBillType) && $obgyBillType > 0) {
        //$message .= $message . '<br>' . "إسم المخزن: " . $messageData->storeName;
        //$queryString .= ' sellbillandrutern.obgyBillType = ' . $obgyBillType . ' AND';

        $returnqueryString .= ' returnsellbill.obgyBillType = ' . $obgyBillType . ' AND';
    }
    if (isset($storeId) && $storeId > 0) {
        $messageData = $myStoreRecord->load($storeId);
        $message .= $message . '<br>' . "إسم المخزن: " . $messageData->storeName;

        $queryString .= ' sellbillandrutern.sellbillstoreid = ' . $storeId . ' AND';
        $returnqueryString .= ' returnsellbill.returnsellbillstoreid = ' . $storeId . ' AND';
    }

    if (isset($clientId) && $clientId > 0) {
        $messageData = $myClientRecord->load($clientId);
        $message .= $message . '<br>' . "إسم العميل: " . $messageData->clientname;

        $queryString .= ' sellbillandrutern.sellbillclientid = ' . $clientId . ' AND';
        $returnqueryString .= ' returnsellbill.returnsellbillclientid = ' . $clientId . ' AND';
    }

    if (isset($sellbillSerial) && $sellbillSerial != '') {

        //showBySriral($sellbillSerial);

        $message = $message . '<br>' . "رقم الفاتورة: " . $sellbillSerial;

        $queryString .= ' sellbillandrutern.sellbillSerial = "' . $sellbillSerial . '" AND';
        $returnqueryString .= ' returnsellbill.returnsellbillserial = "' . $sellbillSerial . '" AND';
    }

    if (isset($sellbillId) && $sellbillId != '') {
        //	showBySellbillId($sellbillId);
        $message = $message . '<br>' . "الرقم المتسلسل : " . $sellbillId;

        $queryString .= ' sellbillandrutern.sellbillid = "' . $sellbillId . '" AND';
        $returnqueryString .= ' returnsellbill.returnsellbillid = ' . $sellbillId . ' AND';
    }

    if (!empty($startDate) && !empty($endDate)) {
        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 = $message . '<br>' . 'من تاريخ : ' . $startDate . 'إلى تاريخ : ' . $endDate . '';

        $queryString .= ' sellbillandrutern.sellbilldate >= "' . $startDate . '" AND sellbillandrutern.sellbilldate <= "' . $endDate . '" AND';
        $returnqueryString .= ' returnsellbill.returnsellbilldate >= "' . $startDate . '" AND returnsellbill.returnsellbilldate <= "' . $endDate . '" AND';
    } elseif (empty($startDate) && empty($endDate) && empty($sellbillSerial) && empty($sellbillid) && !isset($clientId)) {
        $startDate = $endDate = date("Y-m-d");
        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 = $message . '<br>' . "من تاريخ : " . $startDate . " إلى تاريخ : " . $endDate . "";

        $queryString .= ' sellbillandrutern.sellbilldate >= "' . $startDate . '" AND sellbillandrutern.sellbilldate <= "' . $endDate . '" AND';
        $returnqueryString .= ' returnsellbill.returnsellbilldate >= "' . $startDate . '" AND returnsellbill.returnsellbilldate <= "' . $endDate . '" AND';
        //	print_r($returnqueryString);
    }
    $smarty->assign("message", $message);


    $arr = explode(' ', $queryString);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);
        if ($lastWord == 'AND') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        } else if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            $queryString = '';
        }
        //print("<br>queryString: ".$queryString."<br>");
    }



    $arr = explode(' ', $returnqueryString);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);
        if ($lastWord == 'AND') { //remove it
            array_pop($arr);
            //form the string again
            $returnqueryString = implode(' ', $arr);
        } else if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            $returnqueryString = '';
        }
        //print("<br>queryString: ".$queryString."<br>");
    }


    if (!empty($proSellTrackingSerial)) {
        $message .= "<br> Serial : " . $proSellTrackingSerial;
        $queryStringDetails = ' and proSellTrackingSerial="' . $proSellTrackingSerial . '" ';
    }

    $queryStringDetailIds = '';
    $queryStringDetailIdsRet = '';
    if (!empty($queryStringDetails)) {
        $detailsData = R::getAll('SELECT distinct returnsellbillid FROM returnsellbilldetail where 1 ' . $queryStringDetails);
        $sellbillidS = '';
        foreach ($detailsData as $value) {
            $sellbillidS .= $value['returnsellbillid'] . ",";
        }
        $sellbillidS = trim($sellbillidS, ",");
        $sellbillidS = empty($sellbillidS) ? 0 : $sellbillidS;

        $queryStringDetailIds = " and returnsellbillid in (" . $sellbillidS . ")";
        #
        $detailsDataRet = R::getAll('SELECT distinct sellbillid FROM sellandruternbilldetail where 1 ' . $queryStringDetails);
        $sellbillidSRet = '';
        foreach ($detailsDataRet as $value) {
            $sellbillidSRet .= $value['sellbillid'] . ",";
        }
        $sellbillidSRet = trim($sellbillidSRet, ",");
        $sellbillidSRet = empty($sellbillidSRet) ? 0 : $sellbillidSRet;
        $queryStringDetailIdsRet = " and sellbillid in (" . $sellbillidSRet . ")";
    }
    if (empty($returnqueryString)) {
        $returnqueryString .= ' where 1 ';
    }
    if (empty($queryString)) {
        $queryString .= ' where 1 ';
    }

    $returnsellbillData = R::getAll('SELECT *
                                   FROM returnsellbill ' . $returnqueryString . $queryStringDetailIds . ' AND returnsellbill.conditions=0 '); //. ' ORDER BY sellbilldate desc ,sellbillid desc '
    $sellbillandruternData = R::getAll('SELECT sellbillid,sellbilldate,sellbilldiscount,sellbilldiscounttype,sellbilltotalbill,sellQuantity,pricetype,sellbilltotalpayed,sellbillfinalbill,sellbillaftertotalbill,sellbillserial,conditions,
                                   userid,sellerid,delbyuserid,sellbillclientid,sellbillstoreid,sellbillsaveid,deletedsellid,returnsellbillprice,returnsellQuantity
                                   FROM sellbillandrutern ' . $queryString . $queryStringDetailIdsRet . ' AND sellbillandrutern.conditions=0 '); //. ' ORDER BY sellbilldate desc ,sellbillid desc '

    list($billIDs, $billIDsRet, $userIDs, $clientIDs) = [[], [], [], []];
    foreach ($returnsellbillData as $value) {
        $billIDs[] = $value['returnsellbillid'];
        $userIDs[] = $value['userid'];
        $userIDs[] = $value['sellerid'];
        //        $sellerAndDelByIDs[] = $value['sellerid'];
        //        $sellerAndDelByIDs[] = $value['delbyuserid'];
        $clientIDs[] = $value['returnsellbillclientid'];
    }
    foreach ($sellbillandruternData as $value) {
        $billIDsRet[] = $value['sellbillid'];
        $userIDs[] = $value['userid'];
        $userIDs[] = $value['sellerid'];
        //        $sellerAndDelByIDs[] = $value['sellerid'];
        //        $sellerAndDelByIDs[] = $value['delbyuserid'];
        $clientIDs[] = $value['sellbillclientid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $billIDsRet = !empty($billIDsRet) ? array_unique($billIDsRet) : [0];
    $userIDs = !empty($userIDs) ? array_unique($userIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];


    //////$sellbillData = $mySellbillEx->queryallWithOrder($queryString);
    $userDataArr = R::getAll('select userid,employeename from user where userid in(' . implode(',', $userIDs) . ') ');
    $userDataArr = customArrayIndexOne($userDataArr, 'userid');
    ##
    $clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ');
    $clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
    ##
    $discountDataArr = R::getAll('SELECT returnsellbillid,sum(discountvalue) as sumdiscountvalue FROM returnsellbilldetail where returnsellbillid in(' . implode(',', $billIDs) . ') group by returnsellbillid');
    $discountDataArr = customArrayIndexOne($discountDataArr, 'returnsellbillid');
    $discountDataArrRet = R::getAll('SELECT sellbillid,sum(discountvalue) as sumdiscountvalue FROM sellandruternbilldetail where sellbillid in(' . implode(',', $billIDsRet) . ') group by sellbillid');
    $discountDataArrRet = customArrayIndexOne($discountDataArrRet, 'sellbillid');




    $sellBillDataTemp = array();
    foreach ($returnsellbillData as $value) {
        if ($userDataArr[$value['userid']] !== null) {
            $value['username'] = $userDataArr[$value['userid']]['employeename'];
            $value['employeename'] = $userDataArr[$value['sellerid']]['employeename'];
            $value['clientname'] = $clientDataArr[$value['returnsellbillclientid']]['clientname'];
            $value['returnsellbilltotalpayed'] = (float) $value['returnsellbilltotalpayed'];
            $value['sumdiscountvalue'] = (float) $discountDataArr[$value['returnsellbillid']]['sumdiscountvalue'];

            ##make it obj
            $obj = new stdClass();
            foreach ($value as $key1 => $value1) {
                $obj->$key1 = $value1;
            }
            $sellBillDataTemp[] = $obj;
            //$sellBillDataTemp[] = $value;
        }
    }
    $returnsellbillData = $sellBillDataTemp;

    $sellBillDataTemp = array();
    foreach ($sellbillandruternData as $value) {
        if ($userDataArr[$value['userid']] !== null) {
            $value['username'] = $userDataArr[$value['userid']]['employeename'];
            $value['employeename'] = $userDataArr[$value['sellerid']]['employeename'];
            $value['clientname'] = $clientDataArr[$value['sellbillclientid']]['clientname'];
            $value['sellbilltotalpayed'] = (float) $value['sellbilltotalpayed'];
            $value['sumdiscountvalue'] = (float) $discountDataArrRet[$value['sellbillid']]['sumdiscountvalue'];
            ##make it obj
            $obj = new stdClass();
            foreach ($value as $key1 => $value1) {
                $obj->$key1 = $value1;
            }
            $sellBillDataTemp[] = $obj;
            //$sellBillDataTemp[] = $value;
        }
    }
    $sellbillandruternData = $sellBillDataTemp;



    $totalBills = 0;
    $totalQTY = 0;
    $totaltax = 0;
    $totaldiscount = 0;
    $totalpayed = 0;
    //$returnsellbillData = $myReturnsellbillEx->queryallWithqueryString2($returnqueryString);
    if (count($returnsellbillData) > 0) {
        foreach ($returnsellbillData as $sell) {

            if ($sell->conditions == 0) {
                $totalBills += $sell->returnsellbillaftertotalbill;
                $totalQTY += $sell->returnsellQuantity;
                $sell->type = "returnSell";
                $totalpayed += $sell->returnsellbilltotalpayed;


                //getalldiscount
                $sellbilldiscount = $sell->returnsellbilldiscount;
                $sellbilldiscounttype = $sell->returnsellbilldiscounttype; //1 مباشر
                $sellbilltotalbill = $sell->returnsellbilltotalbill;
                $sellbillaftertotalbill = $sell->returnsellbillaftertotalbill;
                $tax = $sell->tax;

                $detaildiscount = $sell->sumdiscountvalue; //$myReturnsellbilldetailEX->getsumdiscount($sell->returnsellbillid);
                if ($sellbilldiscounttype == 1) {


                    //discount
                    $totaldiscount = $totaldiscount + $sellbilldiscount + $detaildiscount;
                    $sell->returnsellbilldiscount = ($sellbilldiscount + $detaildiscount);




                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($sellbilldiscount));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                } else {
                    //discount
                    $discountvalue = ($sellbilltotalbill / 100) * $sellbilldiscount;
                    $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
                    $sell->returnsellbilldiscount = ($discountvalue + $detaildiscount);

                    //tax
                    $taxvalue = $sellbillaftertotalbill - ($sellbilltotalbill - ($discountvalue));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                }
            }
        }
    }

    //get return sell bills from sellbillandrutern table
    //$sellbillandruternData = $mySellbillandruternEx->GetSellBillAndReturnUsingQueryString($queryString);
    //print_r($sellbillandruternData);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->returnsellbillprice; //sellbillaftertotalbill;
                $sell->returnsellbillid = $sell->sellbillid;
                $sell->returnsellbillaftertotalbill = $sell->sellbillaftertotalbill;
                $sell->returnsellbilldate = $sell->sellbilldate;
                $totalQTY += $sell->returnsellQuantity;
                $totalpayed += $sell->sellbilltotalpayed;
                $sell->type = "sellAndReturn";



                //getalldiscount
                $sellbilldiscount = $sell->sellbilldiscount;
                $sellbilldiscounttype = $sell->sellbilldiscounttype; //1 مباشر
                $sellbilltotalbill = $sell->sellbilltotalbill;
                $sellbillaftertotalbill = $sell->sellbillaftertotalbill;
                $tax = $sell->tax;

                $detaildiscount = $sell->sumdiscountvalue; //$mySellandruternbilldetailEx->getsumdiscount($sell->sellbillid, 1);
                if ($sellbilldiscounttype == 1) {
                    //discount
                    $totaldiscount = $totaldiscount + $detaildiscount;
                    $sell->sellbilldiscount = $detaildiscount;


                    //tax
                    $taxvalue = 0; //$sellbillaftertotalbill-($sellbilltotalbill-($sellbilldiscount));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                } else {
                    //discount
                    //$discountvalue=($sellbilltotalbill/100)*$sellbilldiscount;
                    $totaldiscount = $totaldiscount + $detaildiscount;
                    $sell->sellbilldiscount = $detaildiscount;
                    //tax
                    $taxvalue = 0; //$sellbillaftertotalbill-($sellbilltotalbill-($discountvalue));
                    $totaltax = $totaltax + $taxvalue;
                    $sell->tax = $taxvalue;
                }
            }
        }
    }

    $returnsellbillData = array_merge($returnsellbillData, $sellbillandruternData);


    $countRetunSell = count($returnsellbillData);
    $smarty->assign('countRetunSell', $countRetunSell);


    // assign your db results to the template
    $smarty->assign('returnsellbillData', $returnsellbillData);
    $smarty->assign('returntotalBills', $totalBills);
    $smarty->assign('returntotalQTY', $totalQTY);
    $smarty->assign('returntotaltax', $totaltax);
    $smarty->assign('returntotaldiscount', $totaldiscount);
    $smarty->assign('totalpayed', $totalpayed);
}

function showByClient($clientId)
{
    global $mySellbillEx;
    global $mySellbillandruternEx;
    global $smarty;

    $totalBills = 0;
    $totalQTY = 0;


    //get sell bills from sellbill table
    $sellbillData = $mySellbillEx->queryWithClientId($clientId);
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sell";
            }
        }
    }

    //get sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithClientId($clientId);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sellAndReturn";
            }
        }
    }
    $sellbillData = array_merge($sellbillData, $sellbillandruternData);

    // assign your db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalQTY', $totalQTY);
}

function showBySriral($serial)
{
    global $mySellbillEx;
    global $mySellbillandruternEx;
    global $smarty;

    $totalBills = 0;
    $totalQTY = 0;

    //get sell bills from sellbill table
    $sellbillData = $mySellbillEx->queryWithSerail($serial);
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sell";
            }
        }
    }

    //get sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithSerail($serial);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sellAndReturn";
            }
        }
    }
    $sellbillData = array_merge($sellbillData, $sellbillandruternData);

    // assign your db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalQTY', $totalQTY);
}

function showBySellbillId($sellbillId)
{
    global $mySellbillEx;
    global $mySellbillandruternEx;
    global $smarty;

    $totalBills = 0;
    $totalQTY = 0;

    $sellbillData = $mySellbillEx->queryWithSellbillId($sellbillId);
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sell";
            }
        }
    }

    //get sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithSellbillid($sellbillId);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sellAndReturn";
            }
        }
    }
    $sellbillData = array_merge($sellbillData, $sellbillandruternData);
    // assign your db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalQTY', $totalQTY);
}

function showByDate($startDate, $endDate)
{


    global $mySellbillEx;
    global $mySellbillandruternEx;
    global $smarty;

    $totalBills = 0;
    $totalQTY = 0;

    $sellbillData = $mySellbillEx->queryWithDate($startDate, $endDate);
    if (count($sellbillData) > 0) {
        foreach ($sellbillData as $sell) {
            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sell";
            }
        }
    }

    //get sell bills from sellbillandrutern table
    $sellbillandruternData = $mySellbillandruternEx->querySellBillsOnlyWithDate($startDate, $endDate);

    if (count($sellbillandruternData) > 0) {
        foreach ($sellbillandruternData as $sell) {

            if ($sell->conditions == 0) {
                $totalBills += $sell->sellbillaftertotalbill;
                $totalQTY += $sell->sellQuantity;
                $sell->type = "sellAndReturn";
            }
        }
    }
    $sellbillData = array_merge($sellbillData, $sellbillandruternData);

    // assign your db results to the template
    $smarty->assign('sellbillData', $sellbillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalQTY', $totalQTY);
}

function showBuyReturn()
{

    global $buyAndReturnBillExt;
    global $buyAndReturnBillDetailExt;
    global $returnBuyBill;
    global $returnBuyBillDAO;
    global $returnBuyBillExt;
    global $returnBuyBillDetailExt;
    //global $myStoreRecord;


    global $smarty;
    global $Programsetting;

    $queryString = ' WHERE';
    $queryString1 = ' WHERE';

    $serial = $_REQUEST["serial"];
    $supplierId = $_REQUEST["supplierId"];
    $buybillid = $_REQUEST["buybillid"];
    $from = $_REQUEST["from"];
    $to = $_REQUEST["to"];

    $storeId = $_REQUEST["storeId"];

    if (isset($storeId) && $storeId > 0) {
        //        $messageData = $myStoreRecord->load($storeId);
        //        $message .= $message . '<br>' . "إسم المخزن: " . $messageData->storeName;

        $queryString .= ' returnbuybill.returnbuybillstoreid = ' . $storeId . ' AND';
        $queryString1 .= '  buybillstoreid = ' . $supplierId . ' AND';
    }
    if (!empty($supplierId) && $supplierId != "-1") {
        $queryString .= '  returnbuybill.returnbuybillsupplierid = ' . $supplierId . ' AND';
        $queryString1 .= '  buybillsupplierid = ' . $supplierId . ' AND';
    }

    if (!empty($serial) && $serial != "-1") {
        $queryString .= '  returnbuybill.returnbuybillSerial = "' . $serial . '" AND';
        $queryString1 .= '  buybillSerial = "' . $serial . '" AND';
    }

    if (!empty($buybillid) && $buybillid != "-1") {
        $queryString .= '  returnbuybill.returnbuybillid = "' . $buybillid . '" AND';
        $queryString1 .= '  buybillid = "' . $buybillid . '" AND';
    }


    if (!empty($from) && $from != "" && !empty($to) && $to != "") {
        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
            $to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($to)));
            $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
        } else {
            $to = $to . ' 23:59:59';
            $from = $from . " 00:00:00";
        }
        $queryString .= '  returnbuybill.returnbuybilldate >= "' . $from . '" AND returnbuybill.returnbuybilldate <= "' . $to . '"  AND';
        $queryString1 .= '  buybilldate >= "' . $from . '" AND buybilldate <= "' . $to . '"  AND';
    }


    if (empty($from) && empty($to) && empty($buybillid) && empty($serial) && !isset($supplierId)) {
        $to = $from = date("Y-m-d");
        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
            $to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($to)));
            $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
        } else {
            $to = $to . ' 23:59:59';
            $from = $from . " 00:00:00";
        }
        $queryString1 .= '  buybilldate >= "' . $from . '" AND buybilldate <= "' . $to . '"  AND';
        $queryString .= '  returnbuybill.returnbuybilldate >= "' . $from . '" AND returnbuybill.returnbuybilldate <= "' . $to . '"  AND';
    }
    $arr = explode(' ', $queryString);

    $arr = explode(' ', $queryString);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);

        if ($lastWord == 'AND') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        } else if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            $queryString = ' ';
        }
    }


    $arr = explode(' ', $queryString1);
    $arr = explode(' ', $queryString1);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);
        // print_r('lastWord'.$lastWord.'<br>');
        if ($lastWord == 'AND') { //remove it
            array_pop($arr);
            //form the string again
            $queryString1 = implode(' ', $arr);
        } else if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            $queryString1 = ' ';
        }
        //print("<br>queryString: ".$queryString."<br>");
    }

    if (empty($queryString)) {
        $queryString .= ' where 1 ';
    }

    $returnBuyBillData1 = R::getAll('SELECT returnbuybillid,returnbuybilldate,returnbuybilltotalbill,returnbuybillaftertotalbill,returnbuybilltotalpayed,returnbuybilldiscount,returnbuybilldiscountrype,userid,returnbuybillsupplierid,conditions
                                   FROM returnbuybill ' . $queryString . ' AND returnbuybill.conditions=0 ');

    list($billIDs, $billIDsRet, $userIDs, $clientIDs) = [[], [], [], []];
    foreach ($returnBuyBillData1 as $value) {
        $billIDsRet[] = $value['returnbuybillid'];
        $userIDs[] = $value['userid'];
        $clientIDs[] = $value['returnbuybillsupplierid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $billIDsRet = !empty($billIDsRet) ? array_unique($billIDsRet) : [0];
    $userIDs = !empty($userIDs) ? array_unique($userIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];



    //////$sellbillData = $mySellbillEx->queryallWithOrder($queryString);
    $userDataArr = R::getAll('select userid,employeename from user where userid in(' . implode(',', $userIDs) . ') ');
    $userDataArr = customArrayIndexOne($userDataArr, 'userid');
    ##
    $clientDataArr = R::getAll('select supplierid,suppliername from supplier where supplierid in(' . implode(',', $clientIDs) . ') ');
    $clientDataArr = customArrayIndexOne($clientDataArr, 'supplierid');
    ##
    $discountDataArrRet = R::getAll('SELECT returnbuybillid,sum(discountvalue) as sumdiscountvalue from returnbuybilldetail where returnbuybillid in(' . implode(',', $billIDsRet) . ') group by returnbuybillid');
    $discountDataArrRet = customArrayIndexOne($discountDataArrRet, 'returnbuybillid');

    //1- bill details
    $returnbuyBillDetail = R::getAll('SELECT returnbuybillid,returnbuybilldetailproductid,returnbuybilldetailquantity,returnbuybilldetailtotalprice,productunit.productunitid,productnumber
          FROM returnbuybilldetail
          JOIN productunit ON returnbuybilldetail.productunitid = productunit.productunitid
          where returnbuybillid in(' . implode(',', $billIDsRet) . ')');
    $returnbuyBillDetail = customArrayIndexMany($returnbuyBillDetail, 'returnbuybillid');


    $sellBillDataTemp = array();
    foreach ($returnBuyBillData1 as $value) {
        $value['username'] = $userDataArr[$value['userid']]['employeename'];
        $value['suppliername'] = $clientDataArr[$value['returnbuybillsupplierid']]['suppliername'];
        $value['returnbuybilltotalpayed'] = (float) $value['returnbuybilltotalpayed'];
        //        $value['sumdiscountvalue'] = (float) $discountDataArrRet[$value['buybillid']]['sumdiscountvalue'];
        ##make it obj
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $sellBillDataTemp[] = $obj;
        //$sellBillDataTemp[] = $value;
    }
    $returnBuyBillData1 = $sellBillDataTemp;


    $totalBills = 0;
    $totalqty = 0;
    $totaltax = 0;
    $totaldiscount = 0;
    $totalpayed = 0;

    //$returnBuyBillData1 = $returnBuyBillExt->queryBystring($queryString);

    foreach ($returnBuyBillData1 as $myreturnData) {

        $returnbuybillid = $myreturnData->returnbuybillid;
        $totalBills = $totalBills + $myreturnData->returnbuybillaftertotalbill;
        $totalpayed += $myreturnData->buybilltotalpayed;
        $returnbuybilldetailes = $returnbuyBillDetail[$returnbuybillid]; //$returnBuyBillDetailExt->queryByBuybillidExt($returnbuybillid);
        if ($myreturnData->conditions == 0) {

            //getalldiscount
            $buybilldiscount = $myreturnData->returnbuybilldiscount;
            $buybilldiscountrype = $myreturnData->returnbuybilldiscountrype; //1 مباشر
            $buybilltotalbill = $myreturnData->returnbuybilltotalbill;
            $buybillaftertotalbill = $myreturnData->returnbuybillaftertotalbill;
            $tax = $myBillData->tax;

            $detaildiscount = $discountDataArrRet[$myreturnData->returnbuybillid]['sumdiscountvalue']; //$returnBuyBillDetailExt->getsumdiscount($myreturnData->returnbuybillid);
            if ($buybilldiscountrype == 0) {
                //discount
                $totaldiscount = $totaldiscount + $buybilldiscount + $detaildiscount;
                $myreturnData->returnbuybilldiscount = ($buybilldiscount + $detaildiscount);

                //tax
                $taxvalue = (-1 * $buybillaftertotalbill) - (number_format(($buybilltotalbill * -1) - $buybilldiscount));

                /* print_r('<br>sssssssssssssssssssssss'.$buybillaftertotalbill);
                  print_r('<br>sssssssssssssssssssssss'.number_format(($buybilltotalbill*-1)-$buybilldiscount));
                  print_r('<br>sssssssssssssssssssssss'.$buybilldiscount);
                 */
                $totaltax = $totaltax + $taxvalue;
                $myreturnData->tax = $taxvalue;
            } else {

                //discount
                $discountvalue = ($buybilltotalbill / 100) * $buybilldiscount;


                $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
                $myreturnData->returnbuybilldiscount = ($discountvalue + $detaildiscount);
                //tax
                $taxvalue = $buybillaftertotalbill - ($buybilltotalbill - ($discountvalue));
                $totaltax = $totaltax + $taxvalue;
                $myreturnData->tax = $taxvalue;
            }



            $myreturnData->controlname = "returnbuybill";
            $myreturnData->totalqty = 0;
            foreach ($returnbuybilldetailes as $myreturnbuybilldetailes) {
                $productnumber = $myreturnbuybilldetailes['productnumber'];
                $returnbuybilldetailquantity = $myreturnbuybilldetailes['returnbuybilldetailquantity'];
                $myreturnData->totalqty += $returnbuybilldetailquantity;
                $totalqty = $totalqty + ($productnumber * $returnbuybilldetailquantity);
            }
        }
    }














    //    $returnbuyBillData = $buyAndReturnBillExt->queryBystring($queryString1);
    //
    //    foreach ($returnbuyBillData as $remyBillData) {
    //
    //        $buybillid = $remyBillData->buybillid;
    //        $totalBills = $totalBills + $remyBillData->buybillaftertotalbill;
    //        $buybilldetailes = $buyAndReturnBillDetailExt->queryByBuybillidExt($buybillid);
    //
    //        if ($remyBillData->conditions == 0) {
    //
    //
    //            //getalldiscount
    //            $buybilldiscount = 0; //$remyBillData->buybilldiscount;
    //            $buybilldiscountrype = $remyBillData->buybilldiscountrype; //1 مباشر
    //            $buybilltotalbill = $remyBillData->buybilltotalbill;
    //            $buybillaftertotalbill = $remyBillData->buybillaftertotalbill;
    //            $tax = $remyBillData->tax;
    //
    //            $detaildiscount = $buyAndReturnBillDetailExt->getsumdiscount($remyBillData->buybillid, 1);
    //
    //            if ($buybilldiscountrype == 0) {
    //                //discount
    //                $totaldiscount = $totaldiscount + $buybilldiscount + $detaildiscount;
    //                $remyBillData->buybilldiscount = ($buybilldiscount + $detaildiscount);
    //
    //                //tax
    //                $taxvalue = 0; //$buybillaftertotalbill-($buybilltotalbill-($buybilldiscount));
    //                $totaltax = $totaltax + $taxvalue;
    //                $remyBillData->tax = $taxvalue;
    //            } else {
    //
    //                //discount
    //                $discountvalue = 0; //($buybilltotalbill/100)*$buybilldiscount;
    //
    //
    //                $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
    //                $remyBillData->buybilldiscount = ($discountvalue + $detaildiscount);
    //                //tax
    //                $taxvalue = 0; //$buybillaftertotalbill-($buybilltotalbill-($discountvalue));
    //                $totaltax = $totaltax + $taxvalue;
    //                $remyBillData->tax = $taxvalue;
    //            }
    //
    //
    //            $remyBillData->controlname = "returnbuybill";
    //            foreach ($buybilldetailes as $mybuybilldetailes) {
    //                if ($mybuybilldetailes->billtype == 1) {
    //                    $productnumber = $mybuybilldetailes->productnumber;
    //                    $buybilldetailquantity = $mybuybilldetailes->buybilldetailquantity;
    //
    //                    $totalqty = $totalqty + ($productnumber * $buybilldetailquantity);
    //                }
    //            }
    //        }
    //    }

    $countreturnBuyBillData1 = count($returnBuyBillData1);
    $smarty->assign('countreturnBuyBillData1', $countreturnBuyBillData1);
    $smarty->assign('returnBuyBillData1', $returnBuyBillData1);
    $smarty->assign('returnbuyBillData', $returnbuyBillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalqty', $totalqty);
    $smarty->assign('totaldiscount', $totaldiscount);
    $smarty->assign('totaltax', $totaltax);
    $smarty->assign('totalpayed', $totalpayed);
}

function getSuppliers()
{
    global $supplierDAO;
    $suppliersData = $supplierDAO->queryByCondition(0);
    return $suppliersData;
}

function showBuyBills()
{
    global $buyBillExt;
    $buyBills = $buyBillExt->queryAllSerialsNotDeleted();
    return $buyBills;
}

function showBuyReturnAndBuy()
{

    global $buyAndReturnBillExt;
    global $buyAndReturnBillDetailExt;
    global $returnBuyBill;
    global $returnBuyBillDAO;
    global $returnBuyBillExt;
    global $returnBuyBillDetailExt;
    //global $myStoreRecord;


    global $smarty;
    global $Programsetting;

    $queryString = ' WHERE';
    $queryString1 = ' WHERE';

    $serial = $_REQUEST["serial"];
    $supplierId = $_REQUEST["supplierId"];
    $buybillid = $_REQUEST["buybillid"];
    $from = $_REQUEST["from"];
    $to = $_REQUEST["to"];
    $storeId = $_REQUEST["storeId"];

    if (isset($storeId) && $storeId > 0) {
        //        $messageData = $myStoreRecord->load($storeId);
        //        $message .= $message . '<br>' . "إسم المخزن: " . $messageData->storeName;

        $queryString .= ' returnbuybill.returnbuybillstoreid = ' . $storeId . ' AND';
        $queryString1 .= ' buybillstoreid = ' . $storeId . ' AND';
    }

    if (!empty($supplierId) && $supplierId != "-1") {
        $queryString .= '  returnbuybill.returnbuybillsupplierid = ' . $supplierId . ' AND';
        $queryString1 .= '  buybillsupplierid = ' . $supplierId . ' AND';
    }

    if (!empty($serial) && $serial != "-1") {
        $queryString .= '  returnbuybill.returnbuybillSerial = "' . $serial . '" AND';
        $queryString1 .= '  buybillSerial = "' . $serial . '" AND';
    }

    if (!empty($buybillid) && $buybillid != "-1") {
        $queryString .= '  returnbuybill.returnbuybillid = "' . $buybillid . '" AND';
        $queryString1 .= '  buybillid = "' . $buybillid . '" AND';
    }


    if (!empty($from) && $from != "" && !empty($to) && $to != "") {
        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
            $to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($to)));
            $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
        } else {
            $to = $to . ' 23:59:59';
            $from = $from . " 00:00:00";
        }
        $queryString .= '  returnbuybill.returnbuybilldate >= "' . $from . '" AND returnbuybill.returnbuybilldate <= "' . $to . '"  AND';
        $queryString1 .= '  buybilldate >= "' . $from . '" AND buybilldate <= "' . $to . '"  AND';
    }


    if (empty($from) && empty($to) && empty($buybillid) && empty($serial) && !isset($supplierId)) {
        $to = $from = date("Y-m-d");
        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
            $to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($to)));
            $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
        } else {
            $to = $to . ' 23:59:59';
            $from = $from . " 00:00:00";
        }
        $queryString1 .= '  buybilldate >= "' . $from . '" AND buybilldate <= "' . $to . '"  AND';
        $queryString .= '  returnbuybill.returnbuybilldate >= "' . $from . '" AND returnbuybill.returnbuybilldate <= "' . $to . '"  AND';
    }
    $arr = explode(' ', $queryString);

    $arr = explode(' ', $queryString);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);

        if ($lastWord == 'AND') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        } else if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            $queryString = ' ';
        }
    }


    $arr = explode(' ', $queryString1);
    $arr = explode(' ', $queryString1);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);
        // print_r('lastWord'.$lastWord.'<br>');
        if ($lastWord == 'AND') { //remove it
            array_pop($arr);
            //form the string again
            $queryString1 = implode(' ', $arr);
        } else if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            $queryString1 = ' ';
        }
        //print("<br>queryString: ".$queryString."<br>");
    }


    $totalBills = 0;
    $totalqty = 0;
    $totaltax = 0;
    $totaldiscount = 0;
    $totalpayed = 0;
    //    $returnBuyBillData1 = $returnBuyBillExt->queryBystring($queryString);
    //
    //    foreach ($returnBuyBillData1 as $myreturnData) {
    //
    //        $returnbuybillid = $myreturnData->returnbuybillid;
    //        $totalBills = $totalBills + $myreturnData->returnbuybillaftertotalbill;
    //        $returnbuybilldetailes = $returnBuyBillDetailExt->queryByBuybillidExt($returnbuybillid);
    //
    //        if ($myreturnData->conditions == 0) {
    //
    //            //getalldiscount
    //            $buybilldiscount = $myreturnData->returnbuybilldiscount;
    //            $buybilldiscountrype = $myreturnData->returnbuybilldiscountrype; //1 مباشر
    //            $buybilltotalbill = $myreturnData->returnbuybilltotalbill;
    //            $buybillaftertotalbill = $myreturnData->returnbuybillaftertotalbill;
    //            $tax = $myBillData->tax;
    //
    //            $detaildiscount = $returnBuyBillDetailExt->getsumdiscount($myreturnData->returnbuybillid);
    //            if ($buybilldiscountrype == 0) {
    //                //discount
    //                $totaldiscount = $totaldiscount + $buybilldiscount + $detaildiscount;
    //                $myreturnData->returnbuybilldiscount = ($buybilldiscount + $detaildiscount);
    //
    //                //tax
    //                $taxvalue = (-1 * $buybillaftertotalbill) - (number_format(($buybilltotalbill * -1) - $buybilldiscount));
    //
    //                /* print_r('<br>sssssssssssssssssssssss'.$buybillaftertotalbill);
    //                  print_r('<br>sssssssssssssssssssssss'.number_format(($buybilltotalbill*-1)-$buybilldiscount));
    //                  print_r('<br>sssssssssssssssssssssss'.$buybilldiscount);
    //                 */
    //                $totaltax = $totaltax + $taxvalue;
    //                $myreturnData->tax = $taxvalue;
    //            } else {
    //
    //                //discount
    //                $discountvalue = ($buybilltotalbill / 100) * $buybilldiscount;
    //
    //
    //                $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
    //                $myreturnData->returnbuybilldiscount = ($discountvalue + $detaildiscount);
    //                //tax
    //                $taxvalue = $buybillaftertotalbill - ($buybilltotalbill - ($discountvalue));
    //                $totaltax = $totaltax + $taxvalue;
    //                $myreturnData->tax = $taxvalue;
    //            }
    //
    //
    //
    //            $myreturnData->controlname = "returnbuybill";
    //            foreach ($returnbuybilldetailes as $myreturnbuybilldetailes) {
    //                $productnumber = $myreturnbuybilldetailes->productnumber;
    //                $returnbuybilldetailquantity = $myreturnbuybilldetailes->returnbuybilldetailquantity;
    //
    //                $totalqty = $totalqty + ($productnumber * $returnbuybilldetailquantity);
    //            }
    //        }
    //    }
    //
    //
    //
    //


    if (empty($queryString1)) {
        $queryString1 .= ' where 1 ';
    }


    $returnbuyBillData = R::getAll('SELECT buybillid,buybilldate,buybilltotalbill,buybillaftertotalbill,buybilltotalpayed,buybilldiscount,buybilldiscountrype,userid,buybillsupplierid,conditions
                                   FROM buyandruternbill ' . $queryString1 . ' AND buyandruternbill.conditions=0 ');

    list($billIDs, $billIDsRet, $userIDs, $clientIDs) = [[], [], [], []];
    foreach ($returnbuyBillData as $value) {
        $billIDsRet[] = $value['buybillid'];
        $userIDs[] = $value['userid'];
        $clientIDs[] = $value['buybillsupplierid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $billIDsRet = !empty($billIDsRet) ? array_unique($billIDsRet) : [0];
    $userIDs = !empty($userIDs) ? array_unique($userIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];



    //////$sellbillData = $mySellbillEx->queryallWithOrder($queryString);
    $userDataArr = R::getAll('select userid,employeename from user where userid in(' . implode(',', $userIDs) . ') ');
    $userDataArr = customArrayIndexOne($userDataArr, 'userid');
    ##
    $clientDataArr = R::getAll('select supplierid,suppliername from supplier where supplierid in(' . implode(',', $clientIDs) . ') ');
    $clientDataArr = customArrayIndexOne($clientDataArr, 'supplierid');
    ##
    $discountDataArrRet = R::getAll('SELECT buybillid,sum(discountvalue) as sumdiscountvalue FROM buyandruternbill where buybillid in(' . implode(',', $billIDsRet) . ') and billtype =1 group by buybillid');
    $discountDataArrRet = customArrayIndexOne($discountDataArrRet, 'sellbillid');

    //1- bill details
    $returnbuyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,buybilldetailquantity,buybilldetailtotalprice,productunit.productunitid,productnumber
          FROM buyandruternbilldetail
          JOIN productunit ON buyandruternbilldetail.productunitid = productunit.productunitid
          where buybillid in(' . implode(',', $billIDsRet) . ')'); //-- order by buybilldetail.buybilldetailproductid
    $returnbuyBillDetail = customArrayIndexMany($returnbuyBillDetail, 'buybillid');

    $sellBillDataTemp = array();
    foreach ($returnbuyBillData as $value) {
        $value['username'] = $userDataArr[$value['userid']]['employeename'];
        $value['suppliername'] = $clientDataArr[$value['buybillsupplierid']]['suppliername'];
        $value['buybilltotalpayed'] = (float) $value['buybilltotalpayed'];
        //        $value['sumdiscountvalue'] = (float) $discountDataArrRet[$value['buybillid']]['sumdiscountvalue'];
        ##make it obj
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $sellBillDataTemp[] = $obj;
        //$sellBillDataTemp[] = $value;
    }
    $returnbuyBillData = $sellBillDataTemp;


    //$returnbuyBillData = $buyAndReturnBillExt->queryBystring($queryString1);

    foreach ($returnbuyBillData as $remyBillData) {

        $buybillid = $remyBillData->buybillid;
        $totalBills = $totalBills + $remyBillData->buybillaftertotalbill;
        $totalpayed += $remyBillData->buybilltotalpayed;

        if ($remyBillData->conditions == 0) {


            //getalldiscount
            $buybilldiscount = 0; //$remyBillData->buybilldiscount;
            $buybilldiscountrype = $remyBillData->buybilldiscountrype; //1 مباشر
            $buybilltotalbill = $remyBillData->buybilltotalbill;
            $buybillaftertotalbill = $remyBillData->buybillaftertotalbill;
            $tax = $remyBillData->tax;

            $detaildiscount = $discountDataArrRet[$remyBillData->buybillid]['sumdiscountvalue']; //$buyAndReturnBillDetailExt->getsumdiscount($remyBillData->buybillid, 1);
            if ($buybilldiscountrype == 0) {
                //discount
                $totaldiscount = $totaldiscount + $buybilldiscount + $detaildiscount;
                $remyBillData->buybilldiscount = ($buybilldiscount + $detaildiscount);

                //tax
                $taxvalue = 0; //$buybillaftertotalbill-($buybilltotalbill-($buybilldiscount));
                $totaltax = $totaltax + $taxvalue;
                $remyBillData->tax = $taxvalue;
            } else {

                //discount
                $discountvalue = 0; //($buybilltotalbill/100)*$buybilldiscount;


                $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
                $remyBillData->buybilldiscount = ($discountvalue + $detaildiscount);
                //tax
                $taxvalue = 0; //$buybillaftertotalbill-($buybilltotalbill-($discountvalue));
                $totaltax = $totaltax + $taxvalue;
                $remyBillData->tax = $taxvalue;
            }


            $buybilldetailes = $returnbuyBillDetail[$buybillid]; //$buyAndReturnBillDetailExt->queryByBuybillidExt($buybillid);
            $remyBillData->controlname = "returnbuybill";
            $remyBillData->totalqty = 0;
            foreach ($buybilldetailes as $mybuybilldetailes) {
                if ($mybuybilldetailes->billtype == 1) {
                    $productnumber = $mybuybilldetailes['productnumber'];
                    $buybilldetailquantity = $mybuybilldetailes['buybilldetailquantity'];
                    $remyBillData->totalqty += $buybilldetailquantity;
                    $totalqty = $totalqty + ($productnumber * $buybilldetailquantity);
                }
            }
        }
    }

    $smarty->assign('returnBuyBillData1', $returnBuyBillData1);
    $countreturnbuyBillData = count($returnbuyBillData);
    $smarty->assign('countreturnbuyBillData', $countreturnbuyBillData);
    $smarty->assign('returnbuyBillData', $returnbuyBillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalqty', $totalqty);
    $smarty->assign('totaldiscount', $totaldiscount);
    $smarty->assign('totaltax', $totaltax);
    $smarty->assign('totalpayed', $totalpayed);
}

function showBuyBill()
{
    global $buyBillExt;
    global $buyAndReturnBillExt;
    global $buyAndReturnBillDetailExt;
    global $buyBillDetailExt;
    //global $myStoreRecord;


    global $smarty;
    global $Programsetting;

    $queryString = ' WHERE';

    $serial = $_REQUEST["serial"];
    $supplierId = $_REQUEST["supplierId"];
    $buybillid = $_REQUEST["buybillid"];
    $from = $_REQUEST["from"];
    $to = $_REQUEST["to"];
    $storeId = $_REQUEST["storeId"];

    if (isset($storeId) && $storeId > 0) {
        //        $messageData = $myStoreRecord->load($storeId);
        //        $message .= $message . '<br>' . "إسم المخزن: " . $messageData->storeName;

        $queryString .= ' buybillstoreid = ' . $storeId . ' AND';
    }

    if (!empty($supplierId) && $supplierId != "-1") {
        $queryString .= '  buybillsupplierid = "' . $supplierId . '" AND';
    }

    if (!empty($serial) && $serial != "-1") {
        $queryString .= '  buybillSerial = "' . $serial . '" AND';
    }

    if (!empty($buybillid) && $buybillid != "-1") {
        $queryString .= '  buybillid = "' . $buybillid . '" AND';
    }


    if (!empty($from) && $from != "" && !empty($to) && $to != "") {
        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
            $to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($to)));
            $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
        } else {
            $to = $to . ' 23:59:59';
            $from = $from . " 00:00:00";
        }
        $queryString .= '  buybilldate >= "' . $from . '" AND buybilldate <= "' . $to . '"  AND';
    }

    if (empty($from) && empty($to) && empty($buybillid) && empty($serial) && !isset($supplierId)) {
        $to = $from = date("Y-m-d");
        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
            $to = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($to)));
            $from = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($from)));
        } else {
            $to = $to . ' 23:59:59';
            $from = $from . " 00:00:00";
        }
        $queryString .= '  buybilldate >= "' . $from . '" AND buybilldate <= "' . $to . '"  AND';
    }

    $arr = explode(' ', $queryString);

    $arr = explode(' ', $queryString);
    if (isset($arr) && count($arr) > 0) {
        $lastWord = end($arr);
        // print_r('lastWord'.$lastWord.'<br>');
        if ($lastWord == 'AND') { //remove it
            array_pop($arr);
            //form the string again
            $queryString = implode(' ', $arr);
        } else if ($lastWord == 'WHERE') { //remove it
            array_pop($arr);
            $queryString = ' ';
        }
        //print("<br>queryString: ".$queryString."<br>");
    }

    if (empty($queryString)) {
        $queryString .= ' where 1 ';
    }

    $buyBillData1 = R::getAll('SELECT buybillid,buybilldate,buybilltotalbill,buybillaftertotalbill,buybilltotalpayed,buybilldiscount,buybilldiscountrype,userid,buybillsupplierid,conditions
                                   FROM buybill ' . $queryString . ' AND buybill.conditions=0 ');
    $returnbuyBillData = R::getAll('SELECT buybillid,buybilldate,buybilltotalbill,buybillaftertotalbill,buybilltotalpayed,buybilldiscount,buybilldiscountrype,userid,buybillsupplierid,conditions
                                   FROM buyandruternbill ' . $queryString . ' AND buyandruternbill.conditions=0 ');

    list($billIDs, $billIDsRet, $userIDs, $clientIDs) = [[], [], [], []];
    foreach ($buyBillData1 as $value) {
        $billIDs[] = $value['buybillid'];
        $userIDs[] = $value['userid'];
        $clientIDs[] = $value['buybillsupplierid'];
    }
    foreach ($returnbuyBillData as $value) {
        $billIDsRet[] = $value['buybillid'];
        $userIDs[] = $value['userid'];
        $clientIDs[] = $value['buybillsupplierid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $billIDsRet = !empty($billIDsRet) ? array_unique($billIDsRet) : [0];
    $userIDs = !empty($userIDs) ? array_unique($userIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];



    //////$sellbillData = $mySellbillEx->queryallWithOrder($queryString);
    $userDataArr = R::getAll('select userid,employeename from user where userid in(' . implode(',', $userIDs) . ') ');
    $userDataArr = customArrayIndexOne($userDataArr, 'userid');
    ##
    $clientDataArr = R::getAll('select supplierid,suppliername from supplier where supplierid in(' . implode(',', $clientIDs) . ') ');
    $clientDataArr = customArrayIndexOne($clientDataArr, 'supplierid');
    ##
    $discountDataArr = R::getAll('SELECT buybillid,sum(discountvalue) as sumdiscountvalue FROM buybilldetail where buybillid in(' . implode(',', $billIDs) . ') group by buybillid');
    $discountDataArr = customArrayIndexOne($discountDataArr, 'buybillid');
    $discountDataArrRet = R::getAll('SELECT buybillid,sum(discountvalue) as sumdiscountvalue FROM buyandruternbill where buybillid in(' . implode(',', $billIDsRet) . ') and billtype =0 group by buybillid');
    $discountDataArrRet = customArrayIndexOne($discountDataArrRet, 'sellbillid');

    //1- bill details
    $buyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,buybilldetailquantity,buybilldetailtotalprice,productunit.productunitid,productnumber
          FROM buybilldetail
          JOIN productunit ON buybilldetail.productunitid = productunit.productunitid
          where buybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
    $buyBillDetail = customArrayIndexMany($buyBillDetail, 'buybillid');
    $returnbuyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,buybilldetailquantity,buybilldetailtotalprice,productunit.productunitid,productnumber
          FROM buyandruternbilldetail
          JOIN productunit ON buyandruternbilldetail.productunitid = productunit.productunitid
          where buybillid in(' . implode(',', $billIDsRet) . ')'); //-- order by buybilldetail.buybilldetailproductid
    $returnbuyBillDetail = customArrayIndexMany($returnbuyBillDetail, 'buybillid');

    $sellBillDataTemp = array();
    foreach ($buyBillData1 as $value) {
        $value['username'] = $userDataArr[$value['userid']]['employeename'];
        $value['suppliername'] = $clientDataArr[$value['buybillsupplierid']]['suppliername'];
        $value['buybilltotalpayed'] = (float) $value['buybilltotalpayed'];
        //        $value['sumdiscountvalue'] = (float) $discountDataArrRet[$value['buybillid']]['sumdiscountvalue'];
        ##make it obj
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $sellBillDataTemp[] = $obj;
        //$sellBillDataTemp[] = $value;
    }
    $buyBillData1 = $sellBillDataTemp;
    $sellBillDataTemp = array();
    foreach ($returnbuyBillData as $value) {
        $value['username'] = $userDataArr[$value['userid']]['employeename'];
        $value['suppliername'] = $clientDataArr[$value['buybillsupplierid']]['suppliername'];
        $value['buybilltotalpayed'] = (float) $value['buybilltotalpayed'];
        //        $value['sumdiscountvalue'] = (float) $discountDataArrRet[$value['buybillid']]['sumdiscountvalue'];
        ##make it obj
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $sellBillDataTemp[] = $obj;
        //$sellBillDataTemp[] = $value;
    }
    $returnbuyBillData = $sellBillDataTemp;

    $totalBills = 0;
    $totalqty = 0;
    $totaltax = 0;
    $totaldiscount = 0;
    $totalpayed = 0;
    //$buyBillData1 = $buyBillExt->queryBystring($queryString);


    foreach ($buyBillData1 as $myBillData) {

        $buybillid = $myBillData->buybillid;
        $totalBills = $totalBills + $myBillData->buybillaftertotalbill;
        $totalpayed += $myBillData->buybilltotalpayed;


        //getalldiscount
        $buybilldiscount = $myBillData->buybilldiscount;
        $buybilldiscountrype = $myBillData->buybilldiscountrype; //1 مباشر
        $buybilltotalbill = $myBillData->buybilltotalbill;
        $buybillaftertotalbill = $myBillData->buybillaftertotalbill;
        $tax = $myBillData->tax;

        $detaildiscount = $discountDataArr[$myBillData->buybillid]['sumdiscountvalue']; //$buyBillDetailExt->getsumdiscount($myBillData->buybillid);
        if ($buybilldiscountrype == 0) {
            //discount
            $totaldiscount = $totaldiscount + $buybilldiscount + $detaildiscount;
            $myBillData->buybilldiscount = ($buybilldiscount + $detaildiscount);

            //tax
            $taxvalue = $buybillaftertotalbill - ($buybilltotalbill - ($buybilldiscount));
            $totaltax = $totaltax + $taxvalue;
            $myBillData->tax = $taxvalue;
        } else {

            //discount
            $discountvalue = ($buybilltotalbill / 100) * $buybilldiscount;


            $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
            $myBillData->buybilldiscount = ($discountvalue + $detaildiscount);
            //tax
            $taxvalue = $buybillaftertotalbill - ($buybilltotalbill - ($discountvalue));
            $totaltax = $totaltax + $taxvalue;
            $myBillData->tax = $taxvalue;
        }

        $myBillData->totalqty = 0;
        if ($myBillData->conditions == 0) {
            $buybilldetailes = $buyBillDetail[$buybillid]; //$buyBillDetailExt->queryByBuybillidExt($buybillid);
            $myBillData->controlname = "buybill";
            foreach ($buybilldetailes as $mybuybilldetailes) {
                $productnumber = $mybuybilldetailes['productnumber'];
                $buybilldetailquantity = $mybuybilldetailes['buybilldetailquantity'];
                $myBillData->totalqty += $buybilldetailquantity;
                $totalqty = $totalqty + ($productnumber * $buybilldetailquantity);
            }
        }
    }


    //$returnbuyBillData = $buyAndReturnBillExt->queryBystring($queryString);

    foreach ($returnbuyBillData as $remyBillData) {


        //getalldiscount
        $buybilldiscount = $remyBillData->buybilldiscount;
        $buybilldiscountrype = $remyBillData->buybilldiscountrype; //1 مباشر
        $buybilltotalbill = $remyBillData->buybilltotalbill;
        $buybillaftertotalbill = $remyBillData->buybillaftertotalbill;
        $tax = $remyBillData->tax;


        $buybillid = $remyBillData->buybillid;
        $totalBills = $totalBills + $remyBillData->buybillaftertotalbill;
        $totalpayed += $remyBillData->buybilltotalpayed;

        $detaildiscount = $discountDataArrRet[$remyBillData->buybillid]['sumdiscountvalue']; //$buyAndReturnBillDetailExt->getsumdiscount($remyBillData->buybillid, 0);

        if ($buybilldiscountrype == 0) {
            //discount
            $totaldiscount = $totaldiscount + $buybilldiscount + $detaildiscount;
            $remyBillData->buybilldiscount = ($buybilldiscount + $detaildiscount);

            //tax
            $taxvalue = $buybillaftertotalbill - ($buybilltotalbill - ($buybilldiscount));
            $totaltax = $totaltax + $taxvalue;
            $remyBillData->tax = $taxvalue;
        } else {

            //discount
            $discountvalue = ($buybilltotalbill / 100) * $buybilldiscount;


            $totaldiscount = $totaldiscount + $discountvalue + $detaildiscount;
            $remyBillData->buybilldiscount = ($discountvalue + $detaildiscount);
            //tax
            $taxvalue = $buybillaftertotalbill - ($buybilltotalbill - ($discountvalue));
            $totaltax = $totaltax + $taxvalue;
            $remyBillData->tax = $taxvalue;
        }

        //print_r($buybilldetailes);
        //print_r('<br><br>');
        $remyBillData->totalqty = 0;
        if ($myBillData->conditions == 0) {
            $buybilldetailes = $returnbuyBillDetail[$buybillid]; //$buyAndReturnBillDetailExt->queryByBuybillidExt($buybillid);
            $myBillData->controlname = "returnbuybill";
            foreach ($buybilldetailes as $mybuybilldetailes) {
                if ($mybuybilldetailes->billtype == 0) {
                    $productnumber = $mybuybilldetailes['productnumber'];
                    $buybilldetailquantity = $mybuybilldetailes['buybilldetailquantity'];
                    $remyBillData->totalqty += $buybilldetailquantity;
                    $totalqty = $totalqty + ($productnumber * $buybilldetailquantity);
                }
            }
        }
    }

    $buyBillData = array_merge($buyBillData1, $returnbuyBillData);


    $countbuyBillData = count($buyBillData);
    $smarty->assign('countbuyBillData', $countbuyBillData);

    $smarty->assign('buyBillData', $buyBillData);
    $smarty->assign('totalBills', $totalBills);
    $smarty->assign('totalqty', $totalqty);
    $smarty->assign('totaldiscount', $totaldiscount);
    $smarty->assign('totaltax', $totaltax);
    $smarty->assign('totalpayed', $totalpayed);
}
