<?php

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

// get the config file
include_once("../public/config.php");

//here the db files that include in the file
include("../public/include_dao.php");


//here the db files that include in the file
include("reportfunctions.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');


//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');

//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');
//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');
//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');

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');

require_once('../models/mysql/ext/ReturnsellbilldetailMySqlExtDAO.class.php');

$do = $_GET['do'];

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

  Controller Name :- storedetailCTRL تقرير مخزون اول مدة

  OPERTATION in Controller

  1-load save data
  2-display show form

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

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

//here goes the instances and general variables
//user
$user = new User();
$userDAO = new UserMySqlDAO();
$userEX = new UserMySqlExtDAO();

//Buybill
$buyBill = new Buybill();
$buyBillDAO = new BuybillMySqlDAO();
$buyBillEX = new BuybillMySqlExtDAO();
//Buybilldetail
$buyBillDetail = new Buybilldetail();
$buyBillDetailDAO = new BuybilldetailMySqlDAO();
$buyBillDetailEX = new BuybilldetailMySqlExtDAO();
//Returnbuybill
$returnBuyBill = new Returnbuybill();
$returnBuyBillDAO = new ReturnbuybillMySqlDAO();
$returnBuyBillEX = new ReturnbuybillMySqlExtDAO();
//Returnbuybilldetail
$returnBuyBillDetail = new Returnbuybilldetail();
$returnBuyBillDetailDAO = new ReturnbuybilldetailMySqlDAO();
$returnBuyBillDetailEX = new ReturnbuybilldetailMySqlExtDAO();
//Buyandruternbill
$buyAndReturnBill = new Buyandruternbill();
$buyAndReturnBillDAO = new BuyandruternbillMySqlDAO();
$buyAndReturnBillEX = new BuyandruternbillMySqlExtDAO();
//Buyandruternbilldetail
$buyAndReturnBillDetail = new Buyandruternbilldetail();
$buyAndReturnBillDetailDAO = new BuyandruternbilldetailMySqlDAO();
$buyAndReturnBillDetailEX = new BuyandruternbilldetailMySqlExtDAO();
##################################
//bill
$billsBuy = new Billsbuy();
$billsBuyDAO = new BillsbuyMySqlDAO();
$billsBuyEX = new BillsbuyMySqlExtDAO();

$billsProductsBuy = new Billsproductsbuy();
$billsProductsBuyDAO = new BillsproductsbuyMySqlDAO();
$billsProductsBuyEX = new BillsproductsbuyMySqlExtDAO();

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

///////////////product///////////////
$ProductDAO = new ProductMySqlDAO();
$Product = new Product();
$ProductEX = new ProductMySqlExtDAO();
//Productcat
$productCatDAO = new ProductcatMySqlDAO();
$productCatExt = new ProductcatMySqlExtDAO();

//supplier
$supplier = new Supplier();
$supplierDAO = new SupplierMySqlDAO();
$supplierEX = new SupplierMySqlExtDAO();
//Supplierdebtchange
$supplierDeptChange = new Supplierdebtchange();
$supplierDeptChangeDAO = new SupplierdebtchangeMySqlDAO();
$supplierDeptChangeExt = new SupplierdebtchangeMySqlExtDAO();

//Unit
$unitDAO = new UnitMySqlDAO();
$unitEX = new UnitMySqlExtDAO();
//Productunit
$productUnit = new Productunit();
$productUnitDAO = new ProductunitMySqlDAO();
$productUnitEX = new ProductunitMySqlExtDAO();
//Storedetail
$myStoredetail = new Storedetail();
$myStoredetailRecord = new StoredetailMySqlDAO();
$myStoredetailEx = new StoredetailMySqlExtDAO();

$clientDao = new ClientMySqlDAO();

$returnSellBillDetailEX = new ReturnsellbilldetailMySqlExtDAO();

//Programsetting
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();

$lastLevelCatIDS = array();
$catsIDS = "";

$myUserGroupRecord = new UsergroupMySqlDAO();
$userGroupId = $_SESSION['usergroupid'];
$loadUserGroupData = $myUserGroupRecord->load($userGroupId);
$smarty->assign("loadUserGroupData", $loadUserGroupData);

//check and use the condition that suite this action
if (empty($do)) {
    //here the permission check
    include_once("../public/authentication.php");
    $Programsetting = $ProgramsettingDAO->load(1);
    ##########################################################################
    //prepare search tools
    $suppliers = $supplierDAO->queryAll();
    $smarty->assign("suppliers", $suppliers);

    $clients = $clientDao->queryAll();
    $smarty->assign("clients", $clients);

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    ###########################################################################
    //search
    $datefrom = filter_input(INPUT_POST, 'datefrom');
    $dateto = filter_input(INPUT_POST, 'dateto');
    $supplierid = filter_input(INPUT_POST, 'supplierid');
    $smarty->assign('supplierid', $supplierid);
    $debt = getSupplierDept($supplierid);
    $smarty->assign('debt', $debt);

    $clientid = filter_input(INPUT_POST, 'clientid');
    $smarty->assign('clientid', $clientid);

    $order = filter_input(INPUT_POST, 'order');
    if ($order != "asc" && $order != "desc") {
        $order = "no";
    }

    $queryString = " where 1 "; //bills buy
    $queryString1 = " where 1 "; //buybill
    $queryString1R = " where 1 "; //buybillreturn
    $queryString1SR = " where 1 "; //buybillandreturn
    $message = '';
    if (isset($supplierid) && !empty($supplierid) && $supplierid != -1) {
        $queryString .= 'and billsbuy.clientid  = ' . $supplierid . ' ';
        $queryString1 .= 'and  buybill.buybillsupplierid  = ' . $supplierid . ' ';
        $queryString1R .= 'and  returnbuybill.returnbuybillsupplierid  = ' . $supplierid . ' ';
        $queryString1SR .= 'and  buyandruternbill.buybillsupplierid  = ' . $supplierid . ' ';
        $supplier = $supplierDAO->load($supplierid);
        $message .= " المورد: " . $supplier->suppliername . " ";
    }
    ##date
    if (isset($datefrom) && !empty($datefrom)) {
        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
            $datefrom = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($datefrom)));
        } else {
            $datefrom = $datefrom . " 00:00:00";
        }
        $queryString .= 'and  date(billsbuy.billdate)  >= "' . $datefrom . '" ';
        $queryString1 .= 'and  buybill.buybilldate  >= "' . $datefrom . '" ';
        $queryString1R .= 'and  returnbuybill.returnbuybilldate  >= "' . $datefrom . '" ';
        $queryString1SR .= 'and  buyandruternbill.buybilldate  >= "' . $datefrom . '" ';
        $message .= "من تاريخ: " . $datefrom;
    }
    if (isset($dateto) && !empty($dateto)) {
        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
            $dateto = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($dateto)));
        } else {
            $dateto = $dateto . ' 23:59:59';
        }
        $queryString .= 'and  date(billsbuy.billdate)  <= "' . $dateto . '" ';
        $queryString1 .= 'and  buybill.buybilldate  <= "' . $dateto . '" ';
        $queryString1R .= 'and  returnbuybill.returnbuybilldate  <= "' . $dateto . '" ';
        $queryString1SR .= 'and  buyandruternbill.buybilldate  <= "' . $dateto . '" ';
        $message .= " إلى تاريخ: " . $dateto;
    }

    if (isset($clientid) && !empty($clientid) && $clientid != -1) {
        $queryStringClient .= 'and sellbill.sellbillclientid  = ' . $clientid . ' ';
        $queryStringClient1R .= 'and  returnsellbill.returnsellbillclientid  = ' . $clientid . ' ';
        $queryStringClient1SR .= 'and  sellbillandrutern.sellbillclientid  = ' . $clientid . ' ';
        $client = $clientDao->load($clientid);
        $message .= " العميل: " . $client->clientname . " ";
        getDataNewWithClient($queryString, $queryString1, $queryString1R, $queryString1SR, $order, $queryStringClient, $queryStringClient1R, $queryStringClient1SR);
    } else {
        getDataNew($queryString, $queryString1, $queryString1R, $queryString1SR, $order);
    }
    $smarty->assign('message', $message);

    $payed = getCLientPayedDeptData($supplierid, $datefrom, $dateto);
    $smarty->assign('payed', $payed);
    //here the smarty templates
    $smarty->display("buyreportview/show.html");

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

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

function getDataNew($queryString, $queryString1, $queryString1R, $queryString1SR, $order)
{
    global $billsProductsBuyEX;
    global $billsBuyDAO;
    global $billsBuyEX;
    global $buyBillDetailEX;
    global $returnBuyBillDetailEX;
    global $buyAndReturnBillDetailEX;
    global $ProductDAO;
    global $productCatDAO;
    global $ProductEX;
    global $smarty;
    global $lastLevelCatIDS;
    global $catsIDS;
    global $productUnitEX;
    global $unitEX;
    global $myStoredetailEx;
    global $ProgramsettingDAO;
    global $returnSellBillDetailEX;

    class productUnitData
    {

        public $catId;
        public $catName;
        public $productId;
        public $productName;
        public $logo;
        public $unitId;
        public $unitName;
        public $amount = 0;
        public $price = 0;
        public $amount_ret = 0;
        public $price_ret = 0;
        public $productQuantity = 0;
        public $productLastPrice = 0;
        public $noOfBills;
        public $priceWithTax;
        public $priceWithTax_ret;
        //prices
        public $productBuyPrice = 0;
        public $lastbuyprice = 0;
        public $meanbuyprice = 0;
        public $lastbuyprice_withDiscount = 0;
        public $meanbuyprice_withDiscount = 0;
        public $overAllAveragePrice = 0;
        //for total
        public $storeQuantity = 0;
        public $storeQuantityPrice = 0;
    }

    $AllDataIndexArr = array();
    $unitTotal = array();
    $allDataArr = array();
    global $allDataArr;
    ##if no search make it today
    if ($queryString == " where 1 ") {
        ##date
        $today = date("Y-m-d");
        $queryString .= 'and  date(billsbuy.billdate)  = "' . $today . '" ';
        $queryString1 .= 'and  buybill.buybilldate  = "' . $today . '" ';
        $queryString1R .= 'and  returnbuybill.returnbuybilldate  = "' . $today . '" ';
        $queryString1SR .= 'and  buyandruternbill.buybilldate  = "' . $today . '" ';
    }
    $Programsettingdata = $ProgramsettingDAO->load(1);

    $buyBillData = R::getAll('SELECT buybillid,payedtax
		FROM buybill ' . $queryString1 . ' AND conditions = 0');
    $returnBuyBillData = R::getAll('SELECT returnbuybillid,payedtax
		FROM returnbuybill ' . $queryString1R . ' AND conditions = 0');
    $buyAndReturnBillData = R::getAll('SELECT buybillid,payedtax
		FROM buyandruternbill ' . $queryString1SR . ' AND conditions = 0'); //AND sellbillandrutern.deletedsellid=0//order by sellandruternbilldetail.sellbilldetailproductid
    //    $buyBillData = $buyBillDetailEX->queryAllGeneral($queryString1);
    //    $returnBuyBillData = $returnBuyBillDetailEX->queryAllGeneral($queryString1R);
    //    $buyAndReturnBillData = $buyAndReturnBillDetailEX->queryAllGeneral($queryString1SR);
    $smarty->assign('buyBillNo', count($buyBillData));
    $smarty->assign('returnBuyBillNo', count($returnBuyBillData));
    list($productIdsArrAll) = [[]];
    ############################################################################
    ##فاتورة المشتريات
    list($billIDs) = [[]];
    foreach ($buyBillData as $value) {
        $billIDs[] = $value['buybillid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    //1- bill details
    $buyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,productunitid,buybilldetailquantity,buybilldetailtotalprice
          FROM buybilldetail
          where buybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
    $buyBillDetail = customArrayIndexMany($buyBillDetail, 'buybillid');
    $buyBillDataTemp = array();
    list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
    foreach ($buyBillData as $value) {
        foreach ($buyBillDetail[$value['buybillid']] as $data) {
            foreach ($data as $key1 => $value1) {
                $value[$key1] = $value1;
            }
            $productIDsArr[] = (int) $value['buybilldetailproductid'];
            $productUnitIDsArr[] = (int) $value['productunitid'];
            $buyBillDataTemp[] = $value;
        }
    }
    $buyBillData = $buyBillDataTemp;
    $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
    $productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
    $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
    //2- products
    $productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
            from product where productId in(' . implode(',', $productIDsArr) . ') ');
    $productDataArr = customArrayIndexOne($productDataArr, 'productId');
    $buyBillDataTemp = array();
    foreach ($buyBillData as $value) {
        $cat = $productDataArr[$value['buybilldetailproductid']]['productCatId'];
        if ($cat !== null)
            $productCatIdsArr[] = $cat;
        foreach ($productDataArr[$value['buybilldetailproductid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }
        $buyBillDataTemp[] = $value;
    }
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $buyBillData = $buyBillDataTemp;
    //3- cat,units
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    $productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
            from productunit
            join unit on unit.unitId = productunit.unitid
            where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
    $productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
    $buyBillDataTemp = array();
    foreach ($buyBillData as $value) {
        $value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
        foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }

        //from key value pair to std class
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $buyBillDataTemp[] = $obj;
    }
    $buyBillData = $buyBillDataTemp;
    foreach ($buyBillData as $value) {
        $productid = $value->buybilldetailproductid;
        $unitid = $value->unitid;
        $unitname = $value->unitName;
        $quantity = $value->buybilldetailquantity;
        $value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
        $taxes = $value->buybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
        ##############
        ##check if product is here
        if (isset($allDataArr[$productid])) {
            ##product exists
            ##check if unit is here
            if (in_array($unitid, $AllDataIndexArr[$productid])) {
                ##unit exists
                $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
                $myproduct = $allDataArr[$productid][$key2];
                ## common data ##
                $myproduct->price += $value->buybilldetailtotalprice;
                $myproduct->priceWithTax += $value->buybilldetailtotalprice + $taxes;
                $myproduct->amount += $quantity;
                $myproduct->noOfBills = $myproduct->noOfBills + 1;
                #################
            } else {
                ##new unit
                $myproduct = new productUnitData();
                $myproduct->unitId = $unitid;
                $myproduct->unitName = $unitname;
                ## common data ##
                //                    $myproduct->productId = '';
                //                    $myproduct->productName = '';
                $myproduct->productId = $productid;
                $myproduct->productName = $value->productName . " / " . $value->productCatName;
                $myproduct->catId = $value->productCatId;
                $myproduct->catName = $value->productCatName;
                $myproduct->logo = $value->logo;
                ##prices
                $myproduct->productBuyPrice = $value->productBuyPrice;
                $myproduct->lastbuyprice = $value->lastbuyprice;
                $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
                $myproduct->meanbuyprice = $value->meanbuyprice;
                $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
                $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

                $myproduct->price += $value->buybilldetailtotalprice;
                $myproduct->priceWithTax += $value->buybilldetailtotalprice + $taxes;
                $myproduct->amount += $quantity;
                $myproduct->noOfBills = 1;
                #################
                array_push($allDataArr[$productid], $myproduct);
                array_push($AllDataIndexArr[$productid], $unitid);
            }
        } else {
            ##new product&& new unit
            $myproduct = new productUnitData();
            $myproduct->unitId = $unitid;
            $myproduct->unitName = $unitname;
            ## common data ##
            $myproduct->productId = $productid;
            $myproduct->productName = $value->productName . " / " . $value->productCatName;
            $myproduct->catId = $value->productCatId;
            $myproduct->catName = $value->productCatName;
            $myproduct->logo = $value->logo;
            ##prices
            $myproduct->productBuyPrice = $value->productBuyPrice;
            $myproduct->lastbuyprice = $value->lastbuyprice;
            $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
            $myproduct->meanbuyprice = $value->meanbuyprice;
            $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
            $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

            $myproduct->price = $value->buybilldetailtotalprice;
            $myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
            $myproduct->amount = $quantity;
            $myproduct->noOfBills = 1;
            #################
            //handle indexes
            $allDataArr[$productid] = array();
            array_push($allDataArr[$productid], $myproduct);
            $AllDataIndexArr[$productid] = array();
            array_push($AllDataIndexArr[$productid], $unitid);
        }
        if (!isset($unitTotal[$myproduct->unitName])) {
            $unitTotal[$myproduct->unitName] = 0;
        }
        $unitTotal[$myproduct->unitName] += $quantity;
    }
    ############################################################################
    ##فاتورة مردود المشتريات
    list($billIDs) = [[]];
    foreach ($returnBuyBillData as $value) {
        $billIDs[] = $value['returnbuybillid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    //1- bill details
    $buyBillDetail = R::getAll('SELECT returnbuybillid,returnbuybilldetailproductid,productunitid,returnbuybilldetailquantity,returnbuybilldetailtotalprice
          FROM returnbuybilldetail
          where returnbuybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
    $buyBillDetail = customArrayIndexMany($buyBillDetail, 'returnbuybillid');
    $buyBillDataTemp = array();
    list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
    foreach ($returnBuyBillData as $value) {
        foreach ($buyBillDetail[$value['returnbuybillid']] as $data) {
            foreach ($data as $key1 => $value1) {
                $value[$key1] = $value1;
            }
            $productIDsArr[] = (int) $data['returnbuybilldetailproductid'];
            $productUnitIDsArr[] = (int) $data['productunitid'];
            $buyBillDataTemp[] = $value;
        }
    }
    $returnBuyBillData = $buyBillDataTemp;
    $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
    $productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
    $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
    //2- products
    $productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
            from product where productId in(' . implode(',', $productIDsArr) . ') ');
    $productDataArr = customArrayIndexOne($productDataArr, 'productId');
    $buyBillDataTemp = array();
    foreach ($returnBuyBillData as $value) {
        $cat = $productDataArr[$value['returnbuybilldetailproductid']]['productCatId'];
        if ($cat !== null)
            $productCatIdsArr[] = $cat;
        foreach ($productDataArr[$value['returnbuybilldetailproductid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }
        $buyBillDataTemp[] = $value;
    }
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $returnBuyBillData = $buyBillDataTemp;
    //3- cat,units
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    $productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
            from productunit
            join unit on unit.unitId = productunit.unitid
            where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
    $productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
    $buyBillDataTemp = array();
    foreach ($returnBuyBillData as $value) {
        $value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
        foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }

        //from key value pair to std class
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $buyBillDataTemp[] = $obj;
    }
    $returnBuyBillData = $buyBillDataTemp;
    foreach ($returnBuyBillData as $value) {
        $productid = $value->returnbuybilldetailproductid;
        $unitid = $value->unitid;
        $unitname = $value->unitName;
        $quantity = $value->returnbuybilldetailquantity;
        $value->returnbuybilldetailtotalprice = round($value->returnbuybilldetailtotalprice, 2);
        $taxes = $value->returnbuybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
        ##############
        ##check if product is here
        if (isset($allDataArr[$productid])) {
            ##product exists
            ##check if unit is here
            if (in_array($unitid, $AllDataIndexArr[$productid])) {
                ##unit exists
                $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
                $myproduct = $allDataArr[$productid][$key2];
                ## common data ##
                $myproduct->price_ret += $value->returnbuybilldetailtotalprice;
                $myproduct->priceWithTax_ret += $value->returnbuybilldetailtotalprice + $taxes;
                $myproduct->amount_ret += $quantity;
                // $myproduct->noOfBills = $myproduct->noOfBills + 1;
                #################
            } else {
                ##new unit
                $myproduct = new productUnitData();
                $myproduct->unitId = $unitid;
                $myproduct->unitName = $unitname;
                ## common data ##
                //                    $myproduct->productId = '';
                //                    $myproduct->productName = '';
                $myproduct->productId = $productid;
                $myproduct->productName = $value->productName . " / " . $value->productCatName;
                $myproduct->catId = $value->productCatId;
                $myproduct->catName = $value->productCatName;
                $myproduct->logo = $value->logo;
                ##prices
                $myproduct->productBuyPrice = $value->productBuyPrice;
                $myproduct->lastbuyprice = $value->lastbuyprice;
                $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
                $myproduct->meanbuyprice = $value->meanbuyprice;
                $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
                $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

                $myproduct->price_ret += $value->returnbuybilldetailtotalprice;
                $myproduct->priceWithTax_ret += $value->returnbuybilldetailtotalprice + $taxes;
                $myproduct->amount_ret += $quantity;
                // $myproduct->noOfBills = 1;
                #################
                array_push($allDataArr[$productid], $myproduct);
                array_push($AllDataIndexArr[$productid], $unitid);
            }
        } else {
            ##new product&& new unit
            $myproduct = new productUnitData();
            $myproduct->unitId = $unitid;
            $myproduct->unitName = $unitname;
            ## common data ##
            $myproduct->productId = $productid;
            $myproduct->productName = $value->productName . " / " . $value->productCatName;
            $myproduct->catId = $value->productCatId;
            $myproduct->catName = $value->productCatName;
            $myproduct->logo = $value->logo;
            ##prices
            $myproduct->productBuyPrice = $value->productBuyPrice;
            $myproduct->lastbuyprice = $value->lastbuyprice;
            $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
            $myproduct->meanbuyprice = $value->meanbuyprice;
            $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
            $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

            $myproduct->price_ret = $value->returnbuybilldetailtotalprice;
            $myproduct->priceWithTax_ret = $value->returnbuybilldetailtotalprice + $taxes;
            $myproduct->amount_ret = $quantity;
            // $myproduct->noOfBills = 1;
            #################
            //handle indexes
            $allDataArr[$productid] = array();
            array_push($allDataArr[$productid], $myproduct);
            $AllDataIndexArr[$productid] = array();
            array_push($AllDataIndexArr[$productid], $unitid);
        }
        if (!isset($unitTotal[$myproduct->unitName])) {
            $unitTotal[$myproduct->unitName] = 0;
        }
        $unitTotal[$myproduct->unitName] -= $quantity;
    }
    ############################################################################
    ##فاتورة المشتريات والمردود
    list($billIDs) = [[]];
    foreach ($buyAndReturnBillData as $value) {
        $billIDs[] = $value['buybillid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    //1- bill details
    $buyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,productunitid,buybilldetailquantity,buybilldetailtotalprice
          FROM buyandruternbilldetail
          where buybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
    $buyBillDetail = customArrayIndexMany($buyBillDetail, 'buybillid');
    $buyBillDataTemp = array();
    list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
    foreach ($buyAndReturnBillData as $value) {
        foreach ($buyBillDetail[$value['buybillid']] as $data) {
            foreach ($data as $key1 => $value1) {
                $value[$key1] = $value1;
            }
            $productIDsArr[] = (int) $value['buybilldetailproductid'];
            $productUnitIDsArr[] = (int) $value['productunitid'];
            $buyBillDataTemp[] = $value;
        }
    }
    $buyAndReturnBillData = $buyBillDataTemp;
    $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
    $productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
    $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
    //2- products
    $productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
            from product where productId in(' . implode(',', $productIDsArr) . ') ');
    $productDataArr = customArrayIndexOne($productDataArr, 'productId');
    $buyBillDataTemp = array();
    foreach ($buyAndReturnBillData as $value) {
        $cat = $productDataArr[$value['buybilldetailproductid']]['productCatId'];
        if ($cat !== null)
            $productCatIdsArr[] = $cat;
        foreach ($productDataArr[$value['buybilldetailproductid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }
        $buyBillDataTemp[] = $value;
    }
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $buyAndReturnBillData = $buyBillDataTemp;
    //3- cat,units
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    $productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
            from productunit
            join unit on unit.unitId = productunit.unitid
            where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
    $productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
    $buyBillDataTemp = array();
    foreach ($buyAndReturnBillData as $value) {
        $value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
        foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }

        //from key value pair to std class
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $buyBillDataTemp[] = $obj;
    }
    $buyAndReturnBillData = $buyBillDataTemp;
    foreach ($buyAndReturnBillData as $value) {
        $productid = $value->buybilldetailproductid;
        $unitid = $value->unitid;
        $unitname = $value->unitName;
        $quantity = $value->buybilldetailquantity;
        $billType = $value->billtype;
        $value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
        $taxes = $value->buybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
        ##############
        ##check if product is here
        if (isset($allDataArr[$productid])) {
            ##product exists
            ##check if unit is here
            if (in_array($unitid, $AllDataIndexArr[$productid])) {
                ##unit exists
                $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
                $myproduct = $allDataArr[$productid][$key2];
                ## common data ##
                if ($billType == 0) {
                    $myproduct->price += $value->buybilldetailtotalprice;
                    $myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
                    $myproduct->amount += $quantity;
                } else {
                    $myproduct->price_ret += $value->buybilldetailtotalprice;
                    $myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
                    $myproduct->amount_ret += $quantity;
                }
                $myproduct->noOfBills = $myproduct->noOfBills + 1;
                #################
            } else {
                ##new unit
                $myproduct = new productUnitData();
                $myproduct->unitId = $unitid;
                $myproduct->unitName = $unitname;
                ## common data ##
                //                    $myproduct->productId = '';
                //                    $myproduct->productName = '';
                $myproduct->productId = $productid;
                $myproduct->productName = $value->productName . " / " . $value->productCatName;
                $myproduct->catId = $value->productCatId;
                $myproduct->catName = $value->productCatName;
                $myproduct->logo = $value->logo;
                ##prices
                $myproduct->productBuyPrice = $value->productBuyPrice;
                $myproduct->lastbuyprice = $value->lastbuyprice;
                $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
                $myproduct->meanbuyprice = $value->meanbuyprice;
                $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
                $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

                if ($billType == 0) {
                    $myproduct->price += $value->buybilldetailtotalprice;
                    $myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
                    $myproduct->amount += $quantity;
                } else {
                    $myproduct->price_ret += $value->buybilldetailtotalprice;
                    $myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
                    $myproduct->amount_ret += $quantity;
                }
                $myproduct->noOfBills = 1;
                #################
                array_push($allDataArr[$productid], $myproduct);
                array_push($AllDataIndexArr[$productid], $unitid);
            }
        } else {
            ##new product&& new unit
            $myproduct = new productUnitData();
            $myproduct->unitId = $unitid;
            $myproduct->unitName = $unitname;
            ## common data ##
            $myproduct->productId = $productid;
            $myproduct->productName = $value->productName . " / " . $value->productCatName;
            $myproduct->catId = $value->productCatId;
            $myproduct->catName = $value->productCatName;
            $myproduct->logo = $value->logo;
            ##prices
            $myproduct->productBuyPrice = $value->productBuyPrice;
            $myproduct->lastbuyprice = $value->lastbuyprice;
            $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
            $myproduct->meanbuyprice = $value->meanbuyprice;
            $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
            $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

            if ($billType == 0) {
                $myproduct->price += $value->buybilldetailtotalprice;
                $myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
                $myproduct->amount += $quantity;
            } else {
                $myproduct->price_ret += $value->buybilldetailtotalprice;
                $myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
                $myproduct->amount_ret += $quantity;
            }
            $myproduct->noOfBills = 1;
            #################
            //handle indexes
            $allDataArr[$productid] = array();
            array_push($allDataArr[$productid], $myproduct);
            $AllDataIndexArr[$productid] = array();
            array_push($AllDataIndexArr[$productid], $unitid);
        }
        if (!isset($unitTotal[$myproduct->unitName])) {
            $unitTotal[$myproduct->unitName] = 0;
        }
        if ($billType == 0) {
            $unitTotal[$myproduct->unitName] += $quantity;
        } else {
            $unitTotal[$myproduct->unitName] -= $quantity;
        }
    }
    ############################################################################
    ##get store quantity
    $quantityDataArr = R::getAll('SELECT productid,SUM(storedetail.productquantity) AS sumProductQuantity, GROUP_CONCAT(storeName SEPARATOR " - ") as storeName
                FROM storedetail
                join store on store.storeId = storedetail.storeid
		WHERE productid in(' . implode(',', $productIdsArrAll) . ') and productquantity > 0
                group by storedetail.productid');
    $quantityDataArr = customArrayIndexOne($quantityDataArr, 'productid');
    $totalStoreQuantity = 0;
    $totalStoreQuantityPrice = 0;
    foreach ($allDataArr as $value11) {
        foreach ($value11 as $value) {
            $productQuantity = (float) $quantityDataArr[$value->productId]['sumProductQuantity'];

            switch ($Programsettingdata->Inventoryevaluation) {
                case "first":
                    $pro_price = (float) $value->productBuyPrice;
                    break;
                case "last":
                    $pro_price = (float) $value->lastbuyprice;
                    break;
                case "mean":
                    $pro_price = (float) $value->meanbuyprice;
                    break;
                case "last_discount":
                    $pro_price = (float) $value->lastbuyprice_withDiscount;
                    break;
                case "mean_discount":
                    $pro_price = (float) $value->meanbuyprice_withDiscount;
                    break;
                case "generalPrice":
                    $pro_price = (float) $value->overAllAveragePrice;
                    break;
                case "tax":
                    $pro_price = (float) $value->lastbuyprice_withTax;
                    break;
                case "mean_tax":
                    $pro_price = (float) $value->meanbuyprice_withTax;
                    break;
            }
            $value->productQuantity = $productQuantity;
            $value->storeName = rtrim($quantityDataArr[$value->productId]['storeName'], '- ');;
            $value->productLastPrice = $pro_price;
            //
            $value->storeQuantity += $value->productQuantity;
            $value->storeQuantityPrice = $value->productQuantity * $value->productLastPrice;
            //
            $totalStoreQuantity += $value->productQuantity;
            $totalStoreQuantityPrice += $value->productQuantity * $value->productLastPrice;
        }
    }

    //get sellbill & retturnsell bill data for each product
    $vatValue = 1 + ($Programsettingdata->vatValue / 100);
    foreach ($allDataArr as $data) {
        $productQuantity = 0;
        $productPrice = 0;
        $realCost = 0;
        foreach ($data as $item) {
            $sellBillDetailData = R::getAll('SELECT sellbilldetail.sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber, buyprice,
                                            sellbilldiscount,sellbilldiscounttype,sellbilltotalbill
                                            FROM sellbilldetail
                                            join sellbill on(sellbilldetail.sellbillid = sellbill.sellbillid) 
                                            join productunit on(productunit.productid = sellbilldetail.sellbilldetailproductid and productunit.productunitid=sellbilldetail.productunitid and productunit.conditions = 0)
                                            where sellbill.conditions = 0 AND sellbilldetailproductid = ' . $item->productId);

            foreach ($sellBillDetailData as $data) {
                $finalquantity = $data['sellbilldetailquantity'] * $data['productnumber'];
                //sum realCost 
                // $realCost +=  $finalquantity * $data['buyprice'] * $vatValue;
                $realCost +=  $finalquantity * $item->productBuyPrice * $vatValue;

                //sum product quantity
                $productQuantity += $finalquantity;
                //sum product price
                $dicount = $data['sellbilldiscount'];
                if ($data['sellbilldiscounttype'] == 2) {
                    $dicount = ($data['sellbilldiscount'] / 100) * $data['sellbilltotalbill'];
                }

                if ($dicount != 0) {
                    $theDiscount = ($data['sellbilldetailtotalprice'] / $data['sellbilltotalbill']) * $dicount;
                    $theDiscount = round($theDiscount, 2);
                } else {
                    $theDiscount = $dicount;
                    $theDiscount = round($theDiscount, 2);
                }
                ################
                if ($Programsettingdata->valueadded == 0) {
                    $productPrice += $data['sellbilldetailtotalprice'] - $theDiscount;
                } else {
                    $productPrice += $data['sellbilldetailtotalprice'] + $theDiscount;
                }
            }

            $returnSellBillDetailData = R::getAll('SELECT returnsellbilldetail.returnsellbillid,returnsellbilldetailproductid,returnsellbilldetailquantity,returnsellbilldetail.productunitid,returnsellbilldetailtotalprice,productunit.productnumber, buyprice,
                                            returnsellbilldiscount,returnsellbilldiscounttype,returnsellbilltotalbill
                                            FROM returnsellbilldetail
                                            join returnsellbill on (returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid)
                                            join productunit on(productunit.productid = returnsellbilldetail.returnsellbilldetailproductid and productunit.productunitid=returnsellbilldetail.productunitid and productunit.conditions = 0)
                                            where returnsellbill.conditions = 0 AND returnsellbilldetailproductid = ' . $item->productId);
            foreach ($returnSellBillDetailData as $data) {
                $finalquantity = $data['returnsellbilldetailquantity'] * $data['productnumber'];
                //subtract realCost 
                $realCost -=  $finalquantity * $item->productBuyPrice * $vatValue;

                //subtract product quantity
                $productQuantity -= $data['returnsellbilldetailquantity'] * $data['productnumber'];

                //subtract product price
                $dicount = $data['returnsellbilldiscount'];
                if ($data['returnsellbilldiscounttype'] == 2) {
                    $dicount = ($data['returnsellbilldiscount']/100)  * $data['returnsellbilltotalbill'];
                }
                if ($dicount != 0) {
                    $billpecies = $returnSellBillDetailEX->queryBillNoOfPecies($data['returnsellbillid']);
                    $billNoOfProduct = $billpecies->note;
                    $flag = 1;
                }
                if ($flag == 1) {
                    $theDiscount = ($data['returnsellbilldetailtotalprice'] / $data['returnsellbilltotalbill']) * $dicount;
                    $theDiscount = round($theDiscount, 2);
                    $flag = 0;
                } else {
                    $theDiscount = $dicount;
                    $theDiscount = round($theDiscount, 2);
                }
                ################
                if ($Programsettingdata->valueadded == 0) {
                    $productPrice -= $data['returnsellbilldetailtotalprice'] - $theDiscount;
                } else {
                    $productPrice -= $data['returnsellbilldetailtotalprice'] + $theDiscount;
                }
            }

            $sellBillAndRetDetailData = R::getAll('SELECT sellandruternbilldetail.sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellandruternbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber
                                            ,buyprice,sellbilldiscount,sellbilldiscounttype,sellbilltotalbill,sellbillprice,returnsellbillprice,sellandruternbilldetail.selltype
                                            FROM sellandruternbilldetail
                                            join sellbillandrutern on(sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid) 
                                            join productunit on(productunit.productid = sellandruternbilldetail.sellbilldetailproductid and productunit.productunitid=sellandruternbilldetail.productunitid and productunit.conditions = 0)
                                            where sellbillandrutern.conditions = 0 AND sellbilldetailproductid = ' . $item->productId);
            foreach ($sellBillAndRetDetailData as $data) {
                $finalquantity = $data['sellbilldetailquantity'] * $data['productnumber'];
                $dicount = $data['sellbilldiscount'];
                if ($data['sellbilldiscounttype'] == 2) {
                    $dicount = ($data['sellbilldiscount'] / 100) * $data['sellbilltotalbill'];
                }

                if ($dicount != 0) {
                    $theDiscount = ($data['sellbilldetailtotalprice'] / $data['sellbilltotalbill']) * $dicount;
                    $theDiscount = round($theDiscount, 2);
                } else {
                    $theDiscount = $dicount;
                    $theDiscount = round($theDiscount, 2);
                }


                if ($data['selltype'] == 0) {
                    //sum realCost 
                    $realCost +=  $finalquantity * $item->productBuyPrice * $vatValue;
                    //sum product quantity
                    $productQuantity += $finalquantity;
                    //sum product price
                    ################
                    if ($Programsettingdata->valueadded == 0) {
                        $productPrice += $data['sellbilldetailtotalprice'] - $theDiscount;
                    } else {
                        $productPrice += $data['sellbilldetailtotalprice'] + $theDiscount;
                    }
                } else {
                    //subtract realCost 
                    $realCost -=  $finalquantity * $item->productBuyPrice * $vatValue;
                    //subtract product quantity
                    $productQuantity -= $finalquantity;

                    //subtract product price

                    ################
                    if ($Programsettingdata->valueadded == 0) {
                        $productPrice -= $data['sellbilldetailtotalprice'] - $theDiscount;
                    } else {
                        $productPrice -= $data['sellbilldetailtotalprice'] + $theDiscount;
                    }
                }
            }
            $item->netQuantity = $productQuantity;
            $item->netPrice = $productPrice;
            $item->realCost = $realCost;
        }
    }

    $allCatIndexArr = array();
    $allCatArr = array();
    ##total of cats
    foreach ($allDataArr as $value11) {
        foreach ($value11 as $value) {
            $catId = $value->catId;
            $unitid = $value->unitId;
            ##############
            ##check if cat is here
            if (isset($allCatArr[$catId])) {
                ##cat exists
                ##check if unit is here
                if (in_array($unitid, $allCatIndexArr[$catId])) {
                    ##unit exists
                    $key2 = array_search($unitid, $allCatIndexArr[$catId]);
                    $mycat = $allCatArr[$catId][$key2];
                    ## common data ##
                    $mycat->price += $value->price;
                    $mycat->priceWithTax += $value->priceWithTax;
                    $mycat->amount += $value->amount;
                    $mycat->price_ret += $value->price_ret;
                    $mycat->priceWithTax_ret += $value->priceWithTax_ret;
                    $mycat->amount_ret += $value->amount_ret;
                    $mycat->storeQuantity += $value->productQuantity;
                    $mycat->storeQuantityPrice += $value->storeQuantityPrice;

                    $mycat->netQuantity += $value->netQuantity;
                    $mycat->netPrice += $value->netPrice;
                    $mycat->realCost += $value->realCost;
                    #################
                } else {
                    ##new unit
                    $mycat = new productUnitData();
                    $mycat->unitId = $unitid;
                    $mycat->unitName = $value->unitName;
                    $mycat->productId = '';
                    $mycat->productName = '';
                    $mycat->catId = $value->catId;
                    $mycat->catName = $value->catName;

                    $mycat->price += $value->price;
                    $mycat->priceWithTax += $value->priceWithTax;
                    $mycat->amount += $value->amount;
                    $mycat->price_ret += $value->price_ret;
                    $mycat->priceWithTax_ret += $value->priceWithTax_ret;
                    $mycat->amount_ret += $value->amount_ret;
                    $mycat->storeQuantity += $value->productQuantity;
                    $mycat->storeQuantityPrice += $value->storeQuantityPrice;

                    $mycat->netQuantity += $value->netQuantity;
                    $mycat->netPrice += $value->netPrice;
                    $mycat->realCost += $value->realCost;
                    #################
                    array_push($allCatArr[$catId], $mycat);
                    array_push($allCatIndexArr[$catId], $unitid);
                }
            } else {
                ##new cat&& new unit
                $mycat = new productUnitData();
                $mycat->unitId = $value->unitId;
                $mycat->unitName = $value->unitName;
                $mycat->productId = '';
                $mycat->productName = '';
                $mycat->catId = $value->catId;
                $mycat->catName = $value->catName;
                $mycat->price = $value->price;
                $mycat->priceWithTax += $value->priceWithTax;
                $mycat->amount = $value->amount;
                $mycat->price_ret = $value->price_ret;
                $mycat->priceWithTax_ret += $value->priceWithTax_ret;
                $mycat->amount_ret = $value->amount_ret;

                $mycat->storeQuantity += $value->productQuantity;
                $mycat->storeQuantityPrice += $value->storeQuantityPrice;

                $mycat->netQuantity += $value->netQuantity;
                $mycat->netPrice += $value->netPrice;
                $mycat->realCost += $value->realCost;
                #################

                //handle indexes
                $allCatArr[$catId] = array();
                array_push($allCatArr[$catId], $mycat);
                $allCatIndexArr[$catId] = array();
                array_push($allCatIndexArr[$catId], $unitid);
            }
        }
    }

    //    $smarty->assign("unitTotal", $unitTotal);
    //    $smarty->assign("totalOfTotals", $totalOfTotals);
    $smarty->assign("totalStoreQuantity", $totalStoreQuantity);
    $smarty->assign("totalStoreQuantityPrice", $totalStoreQuantityPrice);
    $smarty->assign("allCatArr", $allCatArr);
    $smarty->assign("productTotalArr", $productTotalArr);
    $smarty->assign("allDataArr", $allDataArr);
}

function getData($queryString, $queryString1, $queryString1R, $queryString1SR, $order)
{
    global $billsProductsBuyEX;
    global $billsBuyDAO;
    global $billsBuyEX;
    global $buyBillDetailEX;
    global $returnBuyBillDetailEX;
    global $buyAndReturnBillDetailEX;
    global $ProductDAO;
    global $productCatDAO;
    global $ProductEX;
    global $smarty;
    global $lastLevelCatIDS;
    global $catsIDS;
    global $productUnitEX;
    global $unitEX;
    global $myStoredetailEx;
    global $ProgramsettingDAO;

    class productUnitData
    {

        public $catId;
        public $catName;
        public $productId;
        public $productName;
        public $logo;
        public $unitId;
        public $unitName;
        public $amount = 0;
        public $price = 0;
        public $amount_ret = 0;
        public $price_ret = 0;
        public $productQuantity = 0;
        public $productLastPrice = 0;
        public $noOfBills;
        //for total
        public $storeQuantity = 0;
        public $storeQuantityPrice = 0;
    }

    $AllDataIndexArr = array();
    $unitTotal = array();
    $allDataArr = array();
    global $allDataArr;
    ##if no search make it today
    if ($queryString == " where 1 ") {
        ##date
        $today = date("Y-m-d");
        $queryString .= 'and  date(billsbuy.billdate)  = "' . $today . '" ';
        $queryString1 .= 'and  buybill.buybilldate  = "' . $today . '" ';
        $queryString1R .= 'and  returnbuybill.returnbuybilldate  = "' . $today . '" ';
        $queryString1SR .= 'and  buyandruternbill.buybilldate  = "' . $today . '" ';
    }
    $Programsettingdata = $ProgramsettingDAO->load(1);
    $billsData = $billsProductsBuyEX->queryAllGeneral($queryString);
    $buyBillData = $buyBillDetailEX->queryAllGeneral($queryString1);
    $returnBuyBillData = $returnBuyBillDetailEX->queryAllGeneral($queryString1R);
    $buyAndReturnBillData = $buyAndReturnBillDetailEX->queryAllGeneral($queryString1SR);
    $resultsCount = count($billsData) + count($buyBillData) + count($returnBuyBillData) + count($buyAndReturnBillData);
    $smarty->assign("resultsCount", $resultsCount);
    if ($resultsCount > 0) {
        ##unique products
        ##فاتورة البصريات
        foreach ($billsData as $value) {
            $productid = $value->productid;
            $value->producttotalprice = round($value->producttotalprice, 2);
            ##all products come in unit of one piece  ##get this unit
            $unitData = $productUnitEX->getUnitDataOfUnityWithProductId($productid);
            $unitid = 0;
            $unitname = 'وحدة';
            if (count($unitData) > 0) {
                $unitid = $unitData->unitid;
                $unitname = $unitData->conditions;
            }

            ##check if product is here
            if (isset($allDataArr[$productid])) {
                ##product exists
                ##check if unit is here
                if (in_array($unitid, $AllDataIndexArr[$productid])) {
                    ##unit exists
                    $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
                    $myproduct = $allDataArr[$productid][$key2];
                    ## common data ##
                    $myproduct->price += $value->producttotalprice;
                    $myproduct->amount += $value->productno;
                    $myproduct->noOfBills = $myproduct->noOfBills + 1;
                    #################
                } else {
                    ##new unit
                    $myproduct = new productUnitData();
                    $myproduct->unitId = $unitid;
                    $myproduct->unitName = $unitname;
                    ## common data ##
                    //                    $myproduct->productId = '';
                    //                    $myproduct->productName = '';
                    $myproduct->productId = $value->productid;
                    $myproduct->productName = $value->productName . " / " . $value->productCatName;
                    $myproduct->catId = $value->productCatId;
                    $myproduct->catName = $value->productCatName;
                    $myproduct->logo = $value->logo;


                    $myproduct->price += $value->producttotalprice;
                    $myproduct->amount += $value->productno;
                    $myproduct->noOfBills = 1;
                    #################
                    array_push($allDataArr[$productid], $myproduct);
                    array_push($AllDataIndexArr[$productid], $unitid);
                }
            } else {
                ##new product&& new unit
                $myproduct = new productUnitData();
                $myproduct->unitId = $unitid;
                $myproduct->unitName = $unitname;
                ## common data ##
                $myproduct->productId = $value->productid;
                $myproduct->productName = $value->productName . " / " . $value->productCatName;
                $myproduct->catId = $value->productCatId;
                $myproduct->catName = $value->productCatName;
                $myproduct->logo = $value->logo;

                $myproduct->price = $value->producttotalprice;
                $myproduct->amount = $value->productno;
                $myproduct->noOfBills = 1;
                #################
                //handle indexes
                $allDataArr[$productid] = array();
                array_push($allDataArr[$productid], $myproduct);
                $AllDataIndexArr[$productid] = array();
                array_push($AllDataIndexArr[$productid], $unitid);
            }

            if (!isset($unitTotal[$myproduct->unitName])) {
                $unitTotal[$myproduct->unitName] = 0;
            }
            $unitTotal[$myproduct->unitName] += $value->productno;
        }

        ##فاتورة المشتريات
        foreach ($buyBillData as $value) {
            $productid = $value->buybilldetailproductid;
            $unitid = $value->unitid;
            $unitname = $value->unitName;
            $quantity = $value->buybilldetailquantity;
            $value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
            ##############
            ##check if product is here
            if (isset($allDataArr[$productid])) {
                ##product exists
                ##check if unit is here
                if (in_array($unitid, $AllDataIndexArr[$productid])) {
                    ##unit exists
                    $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
                    $myproduct = $allDataArr[$productid][$key2];
                    ## common data ##
                    $myproduct->price += $value->buybilldetailtotalprice;
                    $myproduct->amount += $quantity;
                    $myproduct->noOfBills = $myproduct->noOfBills + 1;
                    #################
                } else {
                    ##new unit
                    $myproduct = new productUnitData();
                    $myproduct->unitId = $unitid;
                    $myproduct->unitName = $unitname;
                    ## common data ##
                    //                    $myproduct->productId = '';
                    //                    $myproduct->productName = '';
                    $myproduct->productId = $productid;
                    $myproduct->productName = $value->productName . " / " . $value->productCatName;
                    $myproduct->catId = $value->productCatId;
                    $myproduct->catName = $value->productCatName;
                    $myproduct->logo = $value->logo;

                    $myproduct->price += $value->buybilldetailtotalprice;
                    $myproduct->amount += $quantity;
                    $myproduct->noOfBills = 1;
                    #################
                    array_push($allDataArr[$productid], $myproduct);
                    array_push($AllDataIndexArr[$productid], $unitid);
                }
            } else {
                ##new product&& new unit
                $myproduct = new productUnitData();
                $myproduct->unitId = $unitid;
                $myproduct->unitName = $unitname;
                ## common data ##
                $myproduct->productId = $productid;
                $myproduct->productName = $value->productName . " / " . $value->productCatName;
                $myproduct->catId = $value->productCatId;
                $myproduct->catName = $value->productCatName;
                $myproduct->logo = $value->logo;

                $myproduct->price = $value->buybilldetailtotalprice;
                $myproduct->amount = $quantity;
                $myproduct->noOfBills = 1;
                #################
                //handle indexes
                $allDataArr[$productid] = array();
                array_push($allDataArr[$productid], $myproduct);
                $AllDataIndexArr[$productid] = array();
                array_push($AllDataIndexArr[$productid], $unitid);
            }
            if (!isset($unitTotal[$myproduct->unitName])) {
                $unitTotal[$myproduct->unitName] = 0;
            }
            $unitTotal[$myproduct->unitName] += $quantity;
        }

        ##فاتورة مردود المشتريات
        foreach ($returnBuyBillData as $value) {
            $productid = $value->returnbuybilldetailproductid;
            $unitid = $value->unitid;
            $unitname = $value->unitName;
            $quantity = $value->returnbuybilldetailquantity;
            $value->returnbuybilldetailtotalprice = round($value->returnbuybilldetailtotalprice, 2);
            ##############
            ##check if product is here
            if (isset($allDataArr[$productid])) {
                ##product exists
                ##check if unit is here
                if (in_array($unitid, $AllDataIndexArr[$productid])) {
                    ##unit exists
                    $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
                    $myproduct = $allDataArr[$productid][$key2];
                    ## common data ##
                    $myproduct->price_ret += $value->returnbuybilldetailtotalprice;
                    $myproduct->amount_ret += $quantity;
                    $myproduct->noOfBills = $myproduct->noOfBills + 1;
                    #################
                } else {
                    ##new unit
                    $myproduct = new productUnitData();
                    $myproduct->unitId = $unitid;
                    $myproduct->unitName = $unitname;
                    ## common data ##
                    //                    $myproduct->productId = '';
                    //                    $myproduct->productName = '';
                    $myproduct->productId = $productid;
                    $myproduct->productName = $value->productName . " / " . $value->productCatName;
                    $myproduct->catId = $value->productCatId;
                    $myproduct->catName = $value->productCatName;
                    $myproduct->logo = $value->logo;

                    $myproduct->price_ret += $value->returnbuybilldetailtotalprice;
                    $myproduct->amount_ret += $quantity;
                    $myproduct->noOfBills = 1;
                    #################
                    array_push($allDataArr[$productid], $myproduct);
                    array_push($AllDataIndexArr[$productid], $unitid);
                }
            } else {
                ##new product&& new unit
                $myproduct = new productUnitData();
                $myproduct->unitId = $unitid;
                $myproduct->unitName = $unitname;
                ## common data ##
                $myproduct->productId = $productid;
                $myproduct->productName = $value->productName . " / " . $value->productCatName;
                $myproduct->catId = $value->productCatId;
                $myproduct->catName = $value->productCatName;
                $myproduct->logo = $value->logo;

                $myproduct->price_ret = $value->returnbuybilldetailtotalprice;
                $myproduct->amount_ret = $quantity;
                $myproduct->noOfBills = 1;
                #################
                //handle indexes
                $allDataArr[$productid] = array();
                array_push($allDataArr[$productid], $myproduct);
                $AllDataIndexArr[$productid] = array();
                array_push($AllDataIndexArr[$productid], $unitid);
            }
            if (!isset($unitTotal[$myproduct->unitName])) {
                $unitTotal[$myproduct->unitName] = 0;
            }
            $unitTotal[$myproduct->unitName] -= $quantity;
        }

        ##فاتورة المشتريات والمردود
        foreach ($buyAndReturnBillData as $value) {
            $productid = $value->buybilldetailproductid;
            $unitid = $value->unitid;
            $unitname = $value->unitName;
            $quantity = $value->buybilldetailquantity;
            $billType = $value->billtype;
            $value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
            ##############
            ##check if product is here
            if (isset($allDataArr[$productid])) {
                ##product exists
                ##check if unit is here
                if (in_array($unitid, $AllDataIndexArr[$productid])) {
                    ##unit exists
                    $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
                    $myproduct = $allDataArr[$productid][$key2];
                    ## common data ##
                    if ($billType == 0) {
                        $myproduct->price += $value->buybilldetailtotalprice;
                        $myproduct->amount += $quantity;
                    } else {
                        $myproduct->price_ret += $value->buybilldetailtotalprice;
                        $myproduct->amount_ret += $quantity;
                    }
                    $myproduct->noOfBills = $myproduct->noOfBills + 1;
                    #################
                } else {
                    ##new unit
                    $myproduct = new productUnitData();
                    $myproduct->unitId = $unitid;
                    $myproduct->unitName = $unitname;
                    ## common data ##
                    //                    $myproduct->productId = '';
                    //                    $myproduct->productName = '';
                    $myproduct->productId = $productid;
                    $myproduct->productName = $value->productName . " / " . $value->productCatName;
                    $myproduct->catId = $value->productCatId;
                    $myproduct->catName = $value->productCatName;
                    $myproduct->logo = $value->logo;

                    if ($billType == 0) {
                        $myproduct->price += $value->buybilldetailtotalprice;
                        $myproduct->amount += $quantity;
                    } else {
                        $myproduct->price_ret += $value->buybilldetailtotalprice;
                        $myproduct->amount_ret += $quantity;
                    }
                    $myproduct->noOfBills = 1;
                    #################
                    array_push($allDataArr[$productid], $myproduct);
                    array_push($AllDataIndexArr[$productid], $unitid);
                }
            } else {
                ##new product&& new unit
                $myproduct = new productUnitData();
                $myproduct->unitId = $unitid;
                $myproduct->unitName = $unitname;
                ## common data ##
                $myproduct->productId = $productid;
                $myproduct->productName = $value->productName . " / " . $value->productCatName;
                $myproduct->catId = $value->productCatId;
                $myproduct->catName = $value->productCatName;
                $myproduct->logo = $value->logo;

                if ($billType == 0) {
                    $myproduct->price += $value->buybilldetailtotalprice;
                    $myproduct->amount += $quantity;
                } else {
                    $myproduct->price_ret += $value->buybilldetailtotalprice;
                    $myproduct->amount_ret += $quantity;
                }
                $myproduct->noOfBills = 1;
                #################
                //handle indexes
                $allDataArr[$productid] = array();
                array_push($allDataArr[$productid], $myproduct);
                $AllDataIndexArr[$productid] = array();
                array_push($AllDataIndexArr[$productid], $unitid);
            }
            if (!isset($unitTotal[$myproduct->unitName])) {
                $unitTotal[$myproduct->unitName] = 0;
            }
            if ($billType == 0) {
                $unitTotal[$myproduct->unitName] += $quantity;
            } else {
                $unitTotal[$myproduct->unitName] -= $quantity;
            }
        }


        ##get store quantity
        $totalStoreQuantity = 0;
        $totalStoreQuantityPrice = 0;
        foreach ($allDataArr as $value11) {
            foreach ($value11 as $value) {
                $productQuantity = $myStoredetailEx->getProductQuantityInAllStores($value->productId);
                if (!isset($productQuantity) || empty($productQuantity)) {
                    $productQuantity = 0;
                }
                $pro = $ProductDAO->load($value->productId);

                switch ($Programsettingdata->Inventoryevaluation) {
                    case "first":
                        $pro_price = (float) $pro->productBuyPrice;
                        break;
                    case "last":
                        $pro_price = (float) $pro->lastbuyprice;
                        break;
                    case "mean":
                        $pro_price = (float) $pro->meanbuyprice;
                        break;
                    case "last_discount":
                        $pro_price = (float) $pro->lastbuyprice_withDiscount;
                        break;
                    case "mean_discount":
                        $pro_price = (float) $pro->meanbuyprice_withDiscount;
                        break;
                    case "tax":
                        $pro_price = (float) $pro->lastbuyprice_withTax;
                        break;
                    case "mean_tax":
                        $pro_price = (float) $pro->meanbuyprice_withTax;
                        break;
                    default:
                        $pro_price = (float) $pro->overAllAveragePrice;
                        break;
                }
                $value->productQuantity = $productQuantity;
                $value->productLastPrice = $pro_price;
                //
                $value->storeQuantity += $value->productQuantity;
                $value->storeQuantityPrice = $value->productQuantity * $value->productLastPrice;
                //
                $totalStoreQuantity += $value->productQuantity;
                $totalStoreQuantityPrice += $value->productQuantity * $value->productLastPrice;
            }
        }
        $allCatIndexArr = array();
        $allCatArr = array();
        ##total of cats
        foreach ($allDataArr as $value11) {
            foreach ($value11 as $value) {
                $catId = $value->catId;
                $unitid = $value->unitId;
                ##############
                ##check if cat is here
                if (isset($allCatArr[$catId])) {
                    ##cat exists
                    ##check if unit is here
                    if (in_array($unitid, $allCatIndexArr[$catId])) {
                        ##unit exists
                        $key2 = array_search($unitid, $allCatIndexArr[$catId]);
                        $mycat = $allCatArr[$catId][$key2];
                        ## common data ##
                        $mycat->price += $value->price;
                        $mycat->amount += $value->amount;
                        $mycat->price_ret += $value->price_ret;
                        $mycat->amount_ret += $value->amount_ret;
                        $mycat->storeQuantity += $value->productQuantity;
                        $mycat->storeQuantityPrice += $value->storeQuantityPrice;
                        #################
                    } else {
                        ##new unit
                        $mycat = new productUnitData();
                        $mycat->unitId = $unitid;
                        $mycat->unitName = $value->unitName;
                        $mycat->productId = '';
                        $mycat->productName = '';
                        $mycat->catId = $value->catId;
                        $mycat->catName = $value->catName;

                        $mycat->price += $value->price;
                        $mycat->amount += $value->amount;
                        $mycat->price_ret += $value->price_ret;
                        $mycat->amount_ret += $value->amount_ret;
                        $mycat->storeQuantity += $value->productQuantity;
                        $mycat->storeQuantityPrice += $value->storeQuantityPrice;
                        #################
                        array_push($allCatArr[$catId], $mycat);
                        array_push($allCatIndexArr[$catId], $unitid);
                    }
                } else {
                    ##new cat&& new unit
                    $mycat = new productUnitData();
                    $mycat->unitId = $value->unitId;
                    $mycat->unitName = $value->unitName;
                    $mycat->productId = '';
                    $mycat->productName = '';
                    $mycat->catId = $value->catId;
                    $mycat->catName = $value->catName;
                    $mycat->price = $value->price;
                    $mycat->amount = $value->amount;
                    $mycat->price_ret = $value->price_ret;
                    $mycat->amount_ret = $value->amount_ret;

                    $mycat->storeQuantity += $value->productQuantity;
                    $mycat->storeQuantityPrice += $value->storeQuantityPrice;
                    #################
                    //handle indexes
                    $allCatArr[$catId] = array();
                    array_push($allCatArr[$catId], $mycat);
                    $allCatIndexArr[$catId] = array();
                    array_push($allCatIndexArr[$catId], $unitid);
                }
            }
        }

        //        ##finally get total foreach project
        //        $productTotalArr = array();
        //        global $productTotalArr;
        //        $totalOfTotals = 0;
        //        foreach ($allDataArr as $data) {
        //            $prototal = 0;
        //            $proid = 0;
        //            $i = 0;
        //            foreach ($data as $product) {
        //                $prototal+=$product->price;
        //                if ($i == 0) {
        //
        //                    $proid = $product->productId;
        //                }
        //                $i++;
        //            }
        //
        //            $productTotalArr[$proid] = $prototal;
        //            $totalOfTotals += $prototal;
        //        }
        //        if ($order == "asc" || $order == "desc") {
        //            $allDataArr = sortByTotal($order, $allDataArr, $productTotalArr);
        //        }
    }

    //    $smarty->assign("unitTotal", $unitTotal);
    //    $smarty->assign("totalOfTotals", $totalOfTotals);
    $smarty->assign("totalStoreQuantity", $totalStoreQuantity);
    $smarty->assign("totalStoreQuantityPrice", $totalStoreQuantityPrice);
    $smarty->assign("allCatArr", $allCatArr);
    $smarty->assign("productTotalArr", $productTotalArr);
    $smarty->assign("allDataArr", $allDataArr);
}

//sort key value pair
function sortByTotal($type, $allDataArrTemp, $productTotalArrTemp)
{
    $allDataArr = array();
    //1-sort by date
    foreach ($productTotalArrTemp as $key => $value) {
        if ($type == "asc") {
            $minTotalIndex = array_keys($productTotalArrTemp, min($productTotalArrTemp));
            $minTotalIndex = $minTotalIndex[0];
            unset($productTotalArrTemp[$minTotalIndex]);
            $allDataArr[$minTotalIndex] = $allDataArrTemp[$minTotalIndex];
        } elseif ($type == "desc") {
            $maxTotalIndex = array_keys($productTotalArrTemp, max($productTotalArrTemp));
            $maxTotalIndex = $maxTotalIndex[0];
            unset($productTotalArrTemp[$maxTotalIndex]);
            $allDataArr[$maxTotalIndex] = $allDataArrTemp[$maxTotalIndex];
        }
    }

    return $allDataArr;
}

function getAllSubCat($catid, $mode)
{
    global $productCatExt;
    global $productCatDAO;
    global $catsIDS;
    global $lastLevelCatIDS;
    //mode = 1 get all sub cats
    //mode = 2 get last level cats

    $result = $productCatExt->queryByParentExt($catid);
    if (count($result) > 0) {
        foreach ($result as $data) {
            if ($mode == 1) {
                $catsIDS .= ", " . $data->productCatId;
                getAllSubCat($data->productCatId, $mode);
            } elseif ($mode == 2) {
                $childData = $productCatExt->queryByParentExt($data->productCatId);
                if (count($childData) > 0) {
                    getAllSubCat($data->productCatId, $mode);
                } else {
                    if (!empty($data->productCatId)) {
                        array_push($lastLevelCatIDS, $data->productCatId);
                    }
                }
            }
        }
    } else {
        //        if ($mode == 2) {
        //            $childData = $productCatExt->queryByParentExt($data->productCatId);
        //            if (count($childData) > 0) {
        //                getAllSubCat($data->productCatId, $mode);
        //            } else {
        //                if (!empty($data->productCatId)) {
        //                    array_push($lastLevelCatIDS, $data->productCatId);
        //                }
        //            }
        //        }
    }
}

function getSupplierDept($supplierId)
{
    global $supplierEX;
    $dept = " - ";
    if ($supplierId > 0) {
        $getsuppliercurrentDebt = $supplierEX->querysuppliercurrentDebt($supplierId);
        //echo $getsuppliercurrentDebt;

        $dept = $getsuppliercurrentDebt[0]->suppliercurrentDebt;
    }
    return $dept;
}

function getCLientPayedDeptData($supplierid, $datefrom, $dateto)
{
    global $supplierDeptChangeExt;
    global $buyBillDAO;
    global $returnBuyBillDAO;

    $payed = " - ";
    if ($supplierid > 0) {
        $queryString = ' and supplierid = ' . $supplierid;
        if (isset($datefrom) && !empty($datefrom)) {
            $queryString .= " AND date( supplierdebtchange.supplierdebtchangedate ) >= '" . $datefrom . "' ";
        }
        if (isset($dateto) && !empty($dateto)) {
            $queryString .= "AND date( supplierdebtchange.supplierdebtchangedate ) <= '" . $dateto . "' ";
        }
        // $payedDeptAndPayedDeptInBills = $supplierDeptChangeExt->queryPayedDeptAndPayedDeptInBills($queryString);
        $payedDeptAndPayedDeptInBills = $supplierDeptChangeExt->queryPayedDeptAndPayedDeptInBillsWithoutABS($queryString);
        $payed = $payedDeptAndPayedDeptInBills;

        $qString = ' AND supplierdebtchange.tablename NOT IN("supplierPayedDeptController.php","supplierController.php")';
        if (isset($datefrom) && !empty($datefrom)) {
            $qString .= " AND supplierdebtchange.supplierdebtchangedate >= '" . $datefrom . "' ";
        }
        if (isset($dateto) && !empty($dateto)) {
            $qString .= "AND supplierdebtchange.supplierdebtchangedate <= '" . $dateto . "' ";
        }
        if (!$payed || $payed == null) {
            $payed = 0;
        }

        $supDeptChanges = $supplierDeptChangeExt->queryBySupplierIdNotDeleted($supplierid, $qString);
        if ($supDeptChanges) {
            foreach ($supDeptChanges as $change) {
                if (in_array($change->tablename, ["datedCheckedController.php", "checkwithdrawalController.php"])) {
                    if ($change->supplierdebtchangetype == 0)
                        $payed -= $change->supplierdebtchangeamount;
                    else
                        $payed += $change->supplierdebtchangeamount;
                } elseif ($change->tablename == "buyBillController.php") {
                    $buybillid = $buyBillDAO->load($change->supplierdebtchangemodelid);
                    if ($buybillid->conditions == 0)
                        $payed += $buybillid->buybilltotalpayed;
                } elseif ($change->tablename == "returnBuyBillController.php") {
                    $buyreturnbillid = $returnBuyBillDAO->load($change->supplierdebtchangemodelid);
                    if ($buyreturnbillid->conditions == 0)
                        $payed += $buyreturnbillid->returnbuybilltotalpayed;
                } elseif ($change->tablename == "dailyentry.php") {
                    if ($change->supplierdebtchangetype == 0)
                        $payed -= $change->supplierdebtchangeamount;
                    else
                        $payed += $change->supplierdebtchangeamount;
                }
            }
        }
    }
    return $payed;
}


function getDataNewWithClient($queryString, $queryString1, $queryString1R, $queryString1SR, $order, $queryStringClient, $queryStringClient1R, $queryStringClient1SR)
{
    global $billsProductsBuyEX;
    global $billsBuyDAO;
    global $billsBuyEX;
    global $buyBillDetailEX;
    global $returnBuyBillDetailEX;
    global $buyAndReturnBillDetailEX;
    global $ProductDAO;
    global $productCatDAO;
    global $ProductEX;
    global $smarty;
    global $lastLevelCatIDS;
    global $catsIDS;
    global $productUnitEX;
    global $unitEX;
    global $myStoredetailEx;
    global $ProgramsettingDAO;
    global $returnSellBillDetailEX;

    class productUnitData
    {

        public $catId;
        public $catName;
        public $productId;
        public $productName;
        public $logo;
        public $unitId;
        public $unitName;
        public $amount = 0;
        public $price = 0;
        public $amount_ret = 0;
        public $price_ret = 0;
        public $productQuantity = 0;
        public $productLastPrice = 0;
        public $noOfBills;
        public $priceWithTax;
        public $priceWithTax_ret;
        //prices
        public $productBuyPrice = 0;
        public $lastbuyprice = 0;
        public $meanbuyprice = 0;
        public $lastbuyprice_withDiscount = 0;
        public $meanbuyprice_withDiscount = 0;
        public $overAllAveragePrice = 0;
        //for total
        public $storeQuantity = 0;
        public $storeQuantityPrice = 0;
    }

    $AllDataIndexArr = array();
    $unitTotal = array();
    $allDataArr = array();
    global $allDataArr;
    ##if no search make it today
    if ($queryString == " where 1 ") {
        ##date
        $today = date("Y-m-d");
        $queryString .= 'and  date(billsbuy.billdate)  = "' . $today . '" ';
        $queryString1 .= 'and  buybill.buybilldate  = "' . $today . '" ';
        $queryString1R .= 'and  returnbuybill.returnbuybilldate  = "' . $today . '" ';
        $queryString1SR .= 'and  buyandruternbill.buybilldate  = "' . $today . '" ';
    }
    $Programsettingdata = $ProgramsettingDAO->load(1);

    $buyBillData = R::getAll('SELECT buybillid,payedtax
		FROM buybill ' . $queryString1 . ' AND conditions = 0');
    $returnBuyBillData = R::getAll('SELECT returnbuybillid,payedtax
		FROM returnbuybill ' . $queryString1R . ' AND conditions = 0');
    $buyAndReturnBillData = R::getAll('SELECT buybillid,payedtax
		FROM buyandruternbill ' . $queryString1SR . ' AND conditions = 0'); //AND sellbillandrutern.deletedsellid=0//order by sellandruternbilldetail.sellbilldetailproductid
    //    $buyBillData = $buyBillDetailEX->queryAllGeneral($queryString1);
    //    $returnBuyBillData = $returnBuyBillDetailEX->queryAllGeneral($queryString1R);
    //    $buyAndReturnBillData = $buyAndReturnBillDetailEX->queryAllGeneral($queryString1SR);
    list($productIdsArrAll) = [[]];
    ############################################################################
    ##فاتورة المشتريات
    list($billIDs) = [[]];
    foreach ($buyBillData as $value) {
        $billIDs[] = $value['buybillid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    //1- bill details
    $buyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,productunitid,buybilldetailquantity,buybilldetailtotalprice
          FROM buybilldetail
          where buybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
    $buyBillDetail = customArrayIndexMany($buyBillDetail, 'buybillid');
    $buyBillDataTemp = array();
    list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
    foreach ($buyBillData as $value) {
        foreach ($buyBillDetail[$value['buybillid']] as $data) {
            foreach ($data as $key1 => $value1) {
                $value[$key1] = $value1;
            }
            $productIDsArr[] = (int) $value['buybilldetailproductid'];
            $productUnitIDsArr[] = (int) $value['productunitid'];
            $buyBillDataTemp[] = $value;
        }
    }
    $buyBillData = $buyBillDataTemp;
    $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
    $productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
    $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
    //2- products
    $productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
            from product where productId in(' . implode(',', $productIDsArr) . ') ');
    $productDataArr = customArrayIndexOne($productDataArr, 'productId');
    $buyBillDataTemp = array();
    foreach ($buyBillData as $value) {
        $cat = $productDataArr[$value['buybilldetailproductid']]['productCatId'];
        if ($cat !== null)
            $productCatIdsArr[] = $cat;
        foreach ($productDataArr[$value['buybilldetailproductid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }
        $buyBillDataTemp[] = $value;
    }
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $buyBillData = $buyBillDataTemp;
    //3- cat,units
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    $productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
            from productunit
            join unit on unit.unitId = productunit.unitid
            where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
    $productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
    $buyBillDataTemp = array();
    foreach ($buyBillData as $value) {
        $value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
        foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }

        //from key value pair to std class
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $buyBillDataTemp[] = $obj;
    }
    $buyBillData = $buyBillDataTemp;
    foreach ($buyBillData as $value) {
        $productid = $value->buybilldetailproductid;
        $unitid = $value->unitid;
        $unitname = $value->unitName;
        $quantity = $value->buybilldetailquantity;
        $value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
        $taxes = $value->buybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
        ##############
        ##check if product is here
        if (isset($allDataArr[$productid])) {
            ##product exists
            ##check if unit is here
            if (in_array($unitid, $AllDataIndexArr[$productid])) {
                ##unit exists
                $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
                $myproduct = $allDataArr[$productid][$key2];
                ## common data ##
                $myproduct->price += $value->buybilldetailtotalprice;
                $myproduct->priceWithTax += $value->buybilldetailtotalprice + $taxes;
                $myproduct->amount += $quantity;
                $myproduct->noOfBills = $myproduct->noOfBills + 1;
                #################
            } else {
                ##new unit
                $myproduct = new productUnitData();
                $myproduct->unitId = $unitid;
                $myproduct->unitName = $unitname;
                ## common data ##
                //                    $myproduct->productId = '';
                //                    $myproduct->productName = '';
                $myproduct->productId = $productid;
                $myproduct->productName = $value->productName . " / " . $value->productCatName;
                $myproduct->catId = $value->productCatId;
                $myproduct->catName = $value->productCatName;
                $myproduct->logo = $value->logo;
                ##prices
                $myproduct->productBuyPrice = $value->productBuyPrice;
                $myproduct->lastbuyprice = $value->lastbuyprice;
                $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
                $myproduct->meanbuyprice = $value->meanbuyprice;
                $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
                $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

                $myproduct->price += $value->buybilldetailtotalprice;
                $myproduct->priceWithTax += $value->buybilldetailtotalprice + $taxes;
                $myproduct->amount += $quantity;
                $myproduct->noOfBills = 1;
                #################
                array_push($allDataArr[$productid], $myproduct);
                array_push($AllDataIndexArr[$productid], $unitid);
            }
        } else {
            ##new product&& new unit
            $myproduct = new productUnitData();
            $myproduct->unitId = $unitid;
            $myproduct->unitName = $unitname;
            ## common data ##
            $myproduct->productId = $productid;
            $myproduct->productName = $value->productName . " / " . $value->productCatName;
            $myproduct->catId = $value->productCatId;
            $myproduct->catName = $value->productCatName;
            $myproduct->logo = $value->logo;
            ##prices
            $myproduct->productBuyPrice = $value->productBuyPrice;
            $myproduct->lastbuyprice = $value->lastbuyprice;
            $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
            $myproduct->meanbuyprice = $value->meanbuyprice;
            $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
            $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

            $myproduct->price = $value->buybilldetailtotalprice;
            $myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
            $myproduct->amount = $quantity;
            $myproduct->noOfBills = 1;
            #################
            //handle indexes
            $allDataArr[$productid] = array();
            array_push($allDataArr[$productid], $myproduct);
            $AllDataIndexArr[$productid] = array();
            array_push($AllDataIndexArr[$productid], $unitid);
        }
        if (!isset($unitTotal[$myproduct->unitName])) {
            $unitTotal[$myproduct->unitName] = 0;
        }
        $unitTotal[$myproduct->unitName] += $quantity;
    }
    ############################################################################
    ##فاتورة مردود المشتريات
    list($billIDs) = [[]];
    foreach ($returnBuyBillData as $value) {
        $billIDs[] = $value['returnbuybillid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    //1- bill details
    $buyBillDetail = R::getAll('SELECT returnbuybillid,returnbuybilldetailproductid,productunitid,returnbuybilldetailquantity,returnbuybilldetailtotalprice
          FROM returnbuybilldetail
          where returnbuybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
    $buyBillDetail = customArrayIndexMany($buyBillDetail, 'returnbuybillid');
    $buyBillDataTemp = array();
    list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
    foreach ($returnBuyBillData as $value) {
        foreach ($buyBillDetail[$value['returnbuybillid']] as $data) {
            foreach ($data as $key1 => $value1) {
                $value[$key1] = $value1;
            }
            $productIDsArr[] = (int) $data['returnbuybilldetailproductid'];
            $productUnitIDsArr[] = (int) $data['productunitid'];
            $buyBillDataTemp[] = $value;
        }
    }
    $returnBuyBillData = $buyBillDataTemp;
    $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
    $productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
    $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
    //2- products
    $productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
            from product where productId in(' . implode(',', $productIDsArr) . ') ');
    $productDataArr = customArrayIndexOne($productDataArr, 'productId');
    $buyBillDataTemp = array();
    foreach ($returnBuyBillData as $value) {
        $cat = $productDataArr[$value['returnbuybilldetailproductid']]['productCatId'];
        if ($cat !== null)
            $productCatIdsArr[] = $cat;
        foreach ($productDataArr[$value['returnbuybilldetailproductid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }
        $buyBillDataTemp[] = $value;
    }
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $returnBuyBillData = $buyBillDataTemp;
    //3- cat,units
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    $productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
            from productunit
            join unit on unit.unitId = productunit.unitid
            where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
    $productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
    $buyBillDataTemp = array();
    foreach ($returnBuyBillData as $value) {
        $value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
        foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }

        //from key value pair to std class
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $buyBillDataTemp[] = $obj;
    }
    $returnBuyBillData = $buyBillDataTemp;
    foreach ($returnBuyBillData as $value) {
        $productid = $value->returnbuybilldetailproductid;
        $unitid = $value->unitid;
        $unitname = $value->unitName;
        $quantity = $value->returnbuybilldetailquantity;
        $value->returnbuybilldetailtotalprice = round($value->returnbuybilldetailtotalprice, 2);
        $taxes = $value->returnbuybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
        ##############
        ##check if product is here
        if (isset($allDataArr[$productid])) {
            ##product exists
            ##check if unit is here
            if (in_array($unitid, $AllDataIndexArr[$productid])) {
                ##unit exists
                $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
                $myproduct = $allDataArr[$productid][$key2];
                ## common data ##
                $myproduct->price_ret += $value->returnbuybilldetailtotalprice;
                $myproduct->priceWithTax_ret += $value->returnbuybilldetailtotalprice + $taxes;
                $myproduct->amount_ret += $quantity;
                $myproduct->noOfBills = $myproduct->noOfBills + 1;
                #################
            } else {
                ##new unit
                $myproduct = new productUnitData();
                $myproduct->unitId = $unitid;
                $myproduct->unitName = $unitname;
                ## common data ##
                //                    $myproduct->productId = '';
                //                    $myproduct->productName = '';
                $myproduct->productId = $productid;
                $myproduct->productName = $value->productName . " / " . $value->productCatName;
                $myproduct->catId = $value->productCatId;
                $myproduct->catName = $value->productCatName;
                $myproduct->logo = $value->logo;
                ##prices
                $myproduct->productBuyPrice = $value->productBuyPrice;
                $myproduct->lastbuyprice = $value->lastbuyprice;
                $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
                $myproduct->meanbuyprice = $value->meanbuyprice;
                $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
                $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

                $myproduct->price_ret += $value->returnbuybilldetailtotalprice;
                $myproduct->priceWithTax_ret += $value->returnbuybilldetailtotalprice + $taxes;
                $myproduct->amount_ret += $quantity;
                $myproduct->noOfBills = 1;
                #################
                array_push($allDataArr[$productid], $myproduct);
                array_push($AllDataIndexArr[$productid], $unitid);
            }
        } else {
            ##new product&& new unit
            $myproduct = new productUnitData();
            $myproduct->unitId = $unitid;
            $myproduct->unitName = $unitname;
            ## common data ##
            $myproduct->productId = $productid;
            $myproduct->productName = $value->productName . " / " . $value->productCatName;
            $myproduct->catId = $value->productCatId;
            $myproduct->catName = $value->productCatName;
            $myproduct->logo = $value->logo;
            ##prices
            $myproduct->productBuyPrice = $value->productBuyPrice;
            $myproduct->lastbuyprice = $value->lastbuyprice;
            $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
            $myproduct->meanbuyprice = $value->meanbuyprice;
            $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
            $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

            $myproduct->price_ret = $value->returnbuybilldetailtotalprice;
            $myproduct->priceWithTax_ret = $value->returnbuybilldetailtotalprice + $taxes;
            $myproduct->amount_ret = $quantity;
            $myproduct->noOfBills = 1;
            #################
            //handle indexes
            $allDataArr[$productid] = array();
            array_push($allDataArr[$productid], $myproduct);
            $AllDataIndexArr[$productid] = array();
            array_push($AllDataIndexArr[$productid], $unitid);
        }
        if (!isset($unitTotal[$myproduct->unitName])) {
            $unitTotal[$myproduct->unitName] = 0;
        }
        $unitTotal[$myproduct->unitName] -= $quantity;
    }
    ############################################################################
    ##فاتورة المشتريات والمردود
    list($billIDs) = [[]];
    foreach ($buyAndReturnBillData as $value) {
        $billIDs[] = $value['buybillid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    //1- bill details
    $buyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,productunitid,buybilldetailquantity,buybilldetailtotalprice
          FROM buyandruternbilldetail
          where buybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
    $buyBillDetail = customArrayIndexMany($buyBillDetail, 'buybillid');
    $buyBillDataTemp = array();
    list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
    foreach ($buyAndReturnBillData as $value) {
        foreach ($buyBillDetail[$value['buybillid']] as $data) {
            foreach ($data as $key1 => $value1) {
                $value[$key1] = $value1;
            }
            $productIDsArr[] = (int) $value['buybilldetailproductid'];
            $productUnitIDsArr[] = (int) $value['productunitid'];
            $buyBillDataTemp[] = $value;
        }
    }
    $buyAndReturnBillData = $buyBillDataTemp;
    $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
    $productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
    $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
    //2- products
    $productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
            from product where productId in(' . implode(',', $productIDsArr) . ') ');
    $productDataArr = customArrayIndexOne($productDataArr, 'productId');
    $buyBillDataTemp = array();
    foreach ($buyAndReturnBillData as $value) {
        $cat = $productDataArr[$value['buybilldetailproductid']]['productCatId'];
        if ($cat !== null)
            $productCatIdsArr[] = $cat;
        foreach ($productDataArr[$value['buybilldetailproductid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }
        $buyBillDataTemp[] = $value;
    }
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $buyAndReturnBillData = $buyBillDataTemp;
    //3- cat,units
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    $productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
            from productunit
            join unit on unit.unitId = productunit.unitid
            where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
    $productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
    $buyBillDataTemp = array();
    foreach ($buyAndReturnBillData as $value) {
        $value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
        foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }

        //from key value pair to std class
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $buyBillDataTemp[] = $obj;
    }
    $buyAndReturnBillData = $buyBillDataTemp;
    foreach ($buyAndReturnBillData as $value) {
        $productid = $value->buybilldetailproductid;
        $unitid = $value->unitid;
        $unitname = $value->unitName;
        $quantity = $value->buybilldetailquantity;
        $billType = $value->billtype;
        $value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
        $taxes = $value->buybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
        ##############
        ##check if product is here
        if (isset($allDataArr[$productid])) {
            ##product exists
            ##check if unit is here
            if (in_array($unitid, $AllDataIndexArr[$productid])) {
                ##unit exists
                $key2 = array_search($unitid, $AllDataIndexArr[$productid]);
                $myproduct = $allDataArr[$productid][$key2];
                ## common data ##
                if ($billType == 0) {
                    $myproduct->price += $value->buybilldetailtotalprice;
                    $myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
                    $myproduct->amount += $quantity;
                } else {
                    $myproduct->price_ret += $value->buybilldetailtotalprice;
                    $myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
                    $myproduct->amount_ret += $quantity;
                }
                $myproduct->noOfBills = $myproduct->noOfBills + 1;
                #################
            } else {
                ##new unit
                $myproduct = new productUnitData();
                $myproduct->unitId = $unitid;
                $myproduct->unitName = $unitname;
                ## common data ##
                //                    $myproduct->productId = '';
                //                    $myproduct->productName = '';
                $myproduct->productId = $productid;
                $myproduct->productName = $value->productName . " / " . $value->productCatName;
                $myproduct->catId = $value->productCatId;
                $myproduct->catName = $value->productCatName;
                $myproduct->logo = $value->logo;
                ##prices
                $myproduct->productBuyPrice = $value->productBuyPrice;
                $myproduct->lastbuyprice = $value->lastbuyprice;
                $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
                $myproduct->meanbuyprice = $value->meanbuyprice;
                $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
                $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

                if ($billType == 0) {
                    $myproduct->price += $value->buybilldetailtotalprice;
                    $myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
                    $myproduct->amount += $quantity;
                } else {
                    $myproduct->price_ret += $value->buybilldetailtotalprice;
                    $myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
                    $myproduct->amount_ret += $quantity;
                }
                $myproduct->noOfBills = 1;
                #################
                array_push($allDataArr[$productid], $myproduct);
                array_push($AllDataIndexArr[$productid], $unitid);
            }
        } else {
            ##new product&& new unit
            $myproduct = new productUnitData();
            $myproduct->unitId = $unitid;
            $myproduct->unitName = $unitname;
            ## common data ##
            $myproduct->productId = $productid;
            $myproduct->productName = $value->productName . " / " . $value->productCatName;
            $myproduct->catId = $value->productCatId;
            $myproduct->catName = $value->productCatName;
            $myproduct->logo = $value->logo;
            ##prices
            $myproduct->productBuyPrice = $value->productBuyPrice;
            $myproduct->lastbuyprice = $value->lastbuyprice;
            $myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
            $myproduct->meanbuyprice = $value->meanbuyprice;
            $myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
            $myproduct->overAllAveragePrice = $value->overAllAveragePrice;

            if ($billType == 0) {
                $myproduct->price += $value->buybilldetailtotalprice;
                $myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
                $myproduct->amount += $quantity;
            } else {
                $myproduct->price_ret += $value->buybilldetailtotalprice;
                $myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
                $myproduct->amount_ret += $quantity;
            }
            $myproduct->noOfBills = 1;
            #################
            //handle indexes
            $allDataArr[$productid] = array();
            array_push($allDataArr[$productid], $myproduct);
            $AllDataIndexArr[$productid] = array();
            array_push($AllDataIndexArr[$productid], $unitid);
        }
        if (!isset($unitTotal[$myproduct->unitName])) {
            $unitTotal[$myproduct->unitName] = 0;
        }
        if ($billType == 0) {
            $unitTotal[$myproduct->unitName] += $quantity;
        } else {
            $unitTotal[$myproduct->unitName] -= $quantity;
        }
    }
    ############################################################################
    ##get store quantity
    $quantityDataArr = R::getAll('SELECT productid,SUM(storedetail.productquantity) AS sumProductQuantity
                FROM storedetail
		WHERE productid in(' . implode(',', $productIdsArrAll) . ')
                group by storedetail.productid');
    $quantityDataArr = customArrayIndexOne($quantityDataArr, 'productid');
    $totalStoreQuantity = 0;
    $totalStoreQuantityPrice = 0;
    foreach ($allDataArr as $value11) {
        foreach ($value11 as $value) {
            $productQuantity = (float) $quantityDataArr[$value->productId]['sumProductQuantity'];

            switch ($Programsettingdata->Inventoryevaluation) {
                case "first":
                    $pro_price = (float) $value->productBuyPrice;
                    break;
                case "last":
                    $pro_price = (float) $value->lastbuyprice;
                    break;
                case "mean":
                    $pro_price = (float) $value->meanbuyprice;
                    break;
                case "last_discount":
                    $pro_price = (float) $value->lastbuyprice_withDiscount;
                    break;
                case "mean_discount":
                    $pro_price = (float) $value->meanbuyprice_withDiscount;
                    break;
                case "generalPrice":
                    $pro_price = (float) $value->overAllAveragePrice;
                    break;
                case "tax":
                    $pro_price = (float) $value->lastbuyprice_withTax;
                    break;
                case "mean_tax":
                    $pro_price = (float) $value->meanbuyprice_withTax;
                    break;
            }
            $value->productQuantity = $productQuantity;
            $value->productLastPrice = $pro_price;
            //
            $value->storeQuantity += $value->productQuantity;
            $value->storeQuantityPrice = $value->productQuantity * $value->productLastPrice;
            //
            $totalStoreQuantity += $value->productQuantity;
            $totalStoreQuantityPrice += $value->productQuantity * $value->productLastPrice;
        }
    }

    //get sellbill & retturnsell bill data for each product
    $vatValue = 1 + ($Programsettingdata->vatValue / 100);
    foreach ($allDataArr as $data) {
        $productQuantity = 0;
        $productPrice = 0;
        $realCost = 0;
        foreach ($data as $item) {
            $sellBillDetailData = R::getAll('SELECT sellbilldetail.sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber, buyprice,
                                            sellbilldiscount,sellbilldiscounttype,sellbilltotalbill
                                            FROM sellbilldetail
                                            join sellbill on(sellbilldetail.sellbillid = sellbill.sellbillid) 
                                            join productunit on(productunit.productid = sellbilldetail.sellbilldetailproductid and productunit.productunitid=sellbilldetail.productunitid and productunit.conditions = 0)
                                            where sellbill.conditions = 0 AND sellbilldetailproductid = ' . $item->productId . ' ' . $queryStringClient);

            foreach ($sellBillDetailData as $data) {
                $finalquantity = $data['sellbilldetailquantity'] * $data['productnumber'];
                //sum realCost 
                $realCost +=  $finalquantity * $item->productBuyPrice * $vatValue;

                //sum product quantity
                $productQuantity += $finalquantity;
                //sum product price
                $dicount = $data['sellbilldiscount'];
                if ($data['sellbilldiscounttype'] == 2) {
                    $dicount = ($data['sellbilldiscount'] / 100) * $data['sellbilltotalbill'];
                }

                if ($dicount != 0) {
                    $theDiscount = ($data['sellbilldetailtotalprice'] / $data['sellbilltotalbill']) * $dicount;
                    $theDiscount = round($theDiscount, 2);
                } else {
                    $theDiscount = $dicount;
                    $theDiscount = round($theDiscount, 2);
                }
                ################
                if ($Programsettingdata->valueadded == 0) {
                    $productPrice += $data['sellbilldetailtotalprice'] - $theDiscount;
                } else {
                    $productPrice += $data['sellbilldetailtotalprice'] + $theDiscount;
                }
            }

            $returnSellBillDetailData = R::getAll('SELECT returnsellbilldetail.returnsellbillid,returnsellbilldetailproductid,returnsellbilldetailquantity,returnsellbilldetail.productunitid,returnsellbilldetailtotalprice,productunit.productnumber, buyprice,
                                            returnsellbilldiscount,returnsellbilldiscounttype,returnsellbilltotalbill
                                            FROM returnsellbilldetail
                                            join returnsellbill on (returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid)
                                            join productunit on(productunit.productid = returnsellbilldetail.returnsellbilldetailproductid and productunit.productunitid=returnsellbilldetail.productunitid and productunit.conditions = 0)
                                            where returnsellbilldetail.conditions = 0 AND returnsellbilldetailproductid = ' . $item->productId . ' ' . $queryStringClient1R);
            foreach ($returnSellBillDetailData as $data) {
                $finalquantity = $data['returnsellbilldetailquantity'] * $data['productnumber'];
                //sum realCost 
                $realCost -=  $finalquantity * $item->productBuyPrice * $vatValue;

                //subtract product quantity
                $productQuantity -= $data['returnsellbilldetailquantity'] * $data['productnumber'];

                //subtract product price
                $dicount = $data['returnsellbilldiscount'];
                if ($data['returnsellbilldiscounttype'] == 2) {
                    $dicount = $data['returnsellbilldiscount'] * $data['returnsellbilltotalbill'];
                }
                if ($dicount != 0) {
                    $billpecies = $returnSellBillDetailEX->queryBillNoOfPecies($data['returnsellbillid']);
                    $billNoOfProduct = $billpecies->note;
                    $flag = 1;
                }
                if ($flag == 1) {
                    $theDiscount = ($data['returnsellbilldetailtotalprice'] / $data['returnsellbilltotalbill']) * $dicount;
                    $theDiscount = round($theDiscount, 2);
                    $flag = 0;
                } else {
                    $theDiscount = $dicount;
                    $theDiscount = round($theDiscount, 2);
                }
                ################
                if ($Programsettingdata->valueadded == 0) {
                    $productPrice -= $data['returnsellbilldetailtotalprice'] - $theDiscount;
                } else {
                    $productPrice -= $data['returnsellbilldetailtotalprice'] + $theDiscount;
                }
            }
            $item->netQuantity = $productQuantity;
            $item->netPrice = $productPrice;
            $item->realCost = $realCost;
        }
    }

    $allCatIndexArr = array();
    $allCatArr = array();
    ##total of cats
    foreach ($allDataArr as $value11) {
        foreach ($value11 as $value) {
            $catId = $value->catId;
            $unitid = $value->unitId;
            ##############
            ##check if cat is here
            if (isset($allCatArr[$catId])) {
                ##cat exists
                ##check if unit is here
                if (in_array($unitid, $allCatIndexArr[$catId])) {
                    ##unit exists
                    $key2 = array_search($unitid, $allCatIndexArr[$catId]);
                    $mycat = $allCatArr[$catId][$key2];
                    ## common data ##
                    $mycat->price += $value->price;
                    $mycat->priceWithTax += $value->priceWithTax;
                    $mycat->amount += $value->amount;
                    $mycat->price_ret += $value->price_ret;
                    $mycat->priceWithTax_ret += $value->priceWithTax_ret;
                    $mycat->amount_ret += $value->amount_ret;
                    $mycat->storeQuantity += $value->productQuantity;
                    $mycat->storeQuantityPrice += $value->storeQuantityPrice;

                    $mycat->netQuantity += $value->netQuantity;
                    $mycat->netPrice += $value->netPrice;
                    $mycat->realCost += $value->realCost;
                    #################
                } else {
                    ##new unit
                    $mycat = new productUnitData();
                    $mycat->unitId = $unitid;
                    $mycat->unitName = $value->unitName;
                    $mycat->productId = '';
                    $mycat->productName = '';
                    $mycat->catId = $value->catId;
                    $mycat->catName = $value->catName;

                    $mycat->price += $value->price;
                    $mycat->priceWithTax += $value->priceWithTax;
                    $mycat->amount += $value->amount;
                    $mycat->price_ret += $value->price_ret;
                    $mycat->priceWithTax_ret += $value->priceWithTax_ret;
                    $mycat->amount_ret += $value->amount_ret;
                    $mycat->storeQuantity += $value->productQuantity;
                    $mycat->storeQuantityPrice += $value->storeQuantityPrice;

                    $mycat->netQuantity += $value->netQuantity;
                    $mycat->netPrice += $value->netPrice;
                    $mycat->realCost += $value->realCost;
                    #################
                    array_push($allCatArr[$catId], $mycat);
                    array_push($allCatIndexArr[$catId], $unitid);
                }
            } else {
                ##new cat&& new unit
                $mycat = new productUnitData();
                $mycat->unitId = $value->unitId;
                $mycat->unitName = $value->unitName;
                $mycat->productId = '';
                $mycat->productName = '';
                $mycat->catId = $value->catId;
                $mycat->catName = $value->catName;
                $mycat->price = $value->price;
                $mycat->priceWithTax += $value->priceWithTax;
                $mycat->amount = $value->amount;
                $mycat->price_ret = $value->price_ret;
                $mycat->priceWithTax_ret += $value->priceWithTax_ret;
                $mycat->amount_ret = $value->amount_ret;

                $mycat->storeQuantity += $value->productQuantity;
                $mycat->storeQuantityPrice += $value->storeQuantityPrice;

                $mycat->netQuantity += $value->netQuantity;
                $mycat->netPrice += $value->netPrice;
                $mycat->realCost += $value->realCost;
                #################

                //handle indexes
                $allCatArr[$catId] = array();
                array_push($allCatArr[$catId], $mycat);
                $allCatIndexArr[$catId] = array();
                array_push($allCatIndexArr[$catId], $unitid);
            }
        }
    }

    //    $smarty->assign("unitTotal", $unitTotal);
    //    $smarty->assign("totalOfTotals", $totalOfTotals);
    $smarty->assign("totalStoreQuantity", $totalStoreQuantity);
    $smarty->assign("totalStoreQuantityPrice", $totalStoreQuantityPrice);
    $smarty->assign("allCatArr", $allCatArr);
    $smarty->assign("productTotalArr", $productTotalArr);
    $smarty->assign("allDataArr", $allDataArr);
}
