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

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

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

//Sellbilldetail
require_once('../models/dao/SellbilldetailDAO.class.php');
require_once('../models/dto/Sellbilldetail.class.php');
require_once('../models/mysql/SellbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbilldetailMySqlExtDAO.class.php');
//Returnsellbilldetail
require_once('../models/dao/ReturnsellbilldetailDAO.class.php');
require_once('../models/dto/Returnsellbilldetail.class.php');
require_once('../models/mysql/ReturnsellbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnsellbilldetailMySqlExtDAO.class.php');
//Sellandruternbilldetail
require_once('../models/dao/SellandruternbilldetailDAO.class.php');
require_once('../models/dto/Sellandruternbilldetail.class.php');
require_once('../models/mysql/SellandruternbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/SellandruternbilldetailMySqlExtDAO.class.php');
//Billsproduct
require_once('../models/dao/BillsproductsDAO.class.php');
require_once('../models/dto/Billsproduct.class.php');
require_once('../models/mysql/BillsproductsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsproductsMySqlExtDAO.class.php');
//Billsreturnproduct
require_once('../models/dao/BillsreturnproductsDAO.class.php');
require_once('../models/dto/Billsreturnproduct.class.php');
require_once('../models/mysql/BillsreturnproductsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsreturnproductsMySqlExtDAO.class.php');
//Store
require_once('../models/dao/StoreDAO.class.php');
require_once('../models/dto/Store.class.php');
require_once('../models/mysql/StoreMySqlDAO.class.php');
require_once('../models/mysql/ext/StoreMySqlExtDAO.class.php');

$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();

//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();
//Sellbilldetail
$sellbilldetail = new Sellbilldetail();
$sellbilldetailDAO = new SellbilldetailMySqlDAO();
$sellbilldetailEX = new SellbilldetailMySqlExtDAO();
//Returnsellbilldetail
$returnSellBillDetail = new Returnsellbilldetail();
$returnSellBillDetailDAO = new ReturnsellbilldetailMySqlDAO();
$returnSellBillDetailEX = new ReturnsellbilldetailMySqlExtDAO();
//Sellandruternbilldetail
$sellAndRuternBillDetail = new Sellandruternbilldetail();
$sellAndRuternBillDetailDAO = new SellandruternbilldetailMySqlDAO();
$sellAndRuternBillDetailEX = new SellandruternbilldetailMySqlExtDAO();
##################################
//bill
$bills = new Bill();
$billsDAO = new BillsMySqlDAO();
$billsEX = new BillsMySqlExtDAO();
//billsproducts
$billsProducts = new Billsproduct();
$billsProductsDAO = new BillsproductsMySqlDAO();
$billsProductsEX = new BillsproductsMySqlExtDAO();

//bill
$billsReturn = new Billsreturn();
$billsReturnDAO = new BillsreturnMySqlDAO();
$billsReturnEX = new BillsreturnMySqlExtDAO();
//billsReturnproducts
$billsReturnProducts = new Billsreturnproduct();
$billsReturnProductsDAO = new BillsreturnproductsMySqlDAO();
$billsReturnProductsEX = new BillsreturnproductsMySqlExtDAO();
//Store
$storeDAO = new StoreMySqlDAO();
//Programsetting
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();

$lastLevelCatIDS = array();
$catsIDS = "";
//check and use the condition that suite this action
$Programsetting = $ProgramsettingDAO->load(1);
if (empty($do)) {
    //here the permission check
    include_once("../public/authentication.php");
    ##########################################################################
    //prepare search tools
    $stores = $storeDAO->queryAll();
    $smarty->assign("stores", $stores);
    ##التصنيفات
    $catDataReport = getCategoryChilds();
    $smarty->assign("catDataReport", $catDataReport[1]);

    //here the smarty templates
    $smarty->display("fullCategoryReport/show.html");

    //$smarty->assign("settlementstoreshow", 1);
} elseif ($do == "show") {
    //here the permission check
    include_once("../public/authentication.php");
    ##########################################################################
    //prepare search tools
    $stores = $storeDAO->queryAll();
    $smarty->assign("stores", $stores);

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    ##التصنيفات
    $catDataReport = getCategoryChilds();
    $smarty->assign("catDataReport", $catDataReport[1]);
    ###########################################################################
    //search
    $datefrom = filter_input(INPUT_POST, 'datefrom');
    $pricetype = filter_input(INPUT_POST, 'pricetype');
    $dateto = filter_input(INPUT_POST, 'dateto');
    $storeId = filter_input(INPUT_POST, 'storeId');
    $level = filter_input(INPUT_POST, 'level');
    $productCatId = filter_input(INPUT_POST, 'productCatId' . $level);
    if (empty($productCatId) || $productCatId == -1) {
        $productCatId = filter_input(INPUT_POST, 'productCatId' . ($level - 1));
    }
    $productId = filter_input(INPUT_POST, 'productId');
    $searchtype = filter_input(INPUT_POST, 'searchtype');

    $isOptic = filter_input(INPUT_POST, 'proIsOptic');
    if (!isset($isOptic) || empty($isOptic)) {
        $isOptic = 0;
    }
    if ($isOptic == 2 && $searchtype == 1) {
        $productCatId = $productId;
    }

    $queryString = " where 1 "; //bills buy
    $queryString1 = " where 1 "; //buybill
    $queryString1R = " where 1 "; //buybillreturn
    $queryString1SR = " where 1 "; //buybillandreturn
    //
    $queryString_sell = " where 1 "; //bills
    $queryStringR_sell = " where 1 "; //billsreturn
    $queryString1_sell = " where 1 "; //sellbill
    $queryString1R_sell = " where 1 "; //sellbillreturn
    $queryString1SR_sell = " where 1 "; //sellbillandreturn

    $message = '';
    ##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  billsbuy.billdate  >= "' . $datefrom . '" ';
        $queryString1 .= 'and  buybill.buybilldate  >= "' . $datefrom . '" ';
        $queryString1R .= 'and  returnbuybill.returnbuybilldate  >= "' . $datefrom . '" ';
        $queryString1SR .= 'and  buyandruternbill.buybilldate  >= "' . $datefrom . '" ';
        //
        $queryString_sell .= 'and  bills.billdate  >= "' . $datefrom . '" ';
        $queryStringR_sell .= 'and  billsreturn.`date`  >= "' . $datefrom . '" ';
        $queryString1_sell .= 'and  sellbill.sellbilldate  >= "' . $datefrom . '" ';
        $queryString1R_sell .= 'and  returnsellbill.returnsellbilldate  >= "' . $datefrom . '" ';
        $queryString1SR_sell .= 'and  sellbillandrutern.sellbilldate  >= "' . $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  billsbuy.billdate  <= "' . $dateto . '" ';
        $queryString1 .= 'and  buybill.buybilldate  <= "' . $dateto . '" ';
        $queryString1R .= 'and  returnbuybill.returnbuybilldate  <= "' . $dateto . '" ';
        $queryString1SR .= 'and  buyandruternbill.buybilldate  <= "' . $dateto . '" ';
        //
        $queryString_sell .= 'and  bills.billdate  <= "' . $dateto . '" ';
        $queryStringR_sell .= 'and   billsreturn.`date`  <= "' . $dateto . '" ';
        $queryString1_sell .= 'and  sellbill.sellbilldate  <= "' . $dateto . '" ';
        $queryString1R_sell .= 'and  returnsellbill.returnsellbilldate  <= "' . $dateto . '" ';
        $queryString1SR_sell .= 'and  sellbillandrutern.sellbilldate  <= "' . $dateto . '" ';
        $message .= ' التاريخ الى"' . $dateto . '"';
    }
    if (isset($storeId) && !empty($storeId) && $storeId != -1) {
        $queryString1 .= 'and  buybill.buybillstoreid  = ' . $storeId . ' ';
        $queryString1R .= 'and  returnbuybill.returnbuybillstoreid  = ' . $storeId . ' ';
        $queryString1SR .= 'and  buyandruternbill.buybillstoreid  = ' . $storeId . ' ';
        //
        $queryString1_sell .= 'and  sellbill.sellbillstoreid  = ' . $storeId . ' ';
        $queryString1R_sell .= 'and  returnsellbill.returnsellbillstoreid  = ' . $storeId . ' ';
        $queryString1SR_sell .= 'and  sellbillandrutern.sellbillstoreid  = ' . $storeId . ' ';

        $store = $storeDAO->load($storeId);
        $message .= " المخزن : $store->storeName";
    }
    ##pricetype
    if (isset($pricetype) && $pricetype != -1) {
        $queryString .= 'and  billsbuy.pricetype  = ' . $pricetype . ' ';
        //
        $queryString_sell .= 'and  bills.pricetype = ' . $pricetype . ' ';
        $queryStringR_sell .= 'and  bills.pricetype = ' . $pricetype . ' ';
        $queryString1_sell .= 'and  sellbilldetail.pricetype  = ' . $pricetype . ' ';
        $queryString1R_sell .= 'and  returnsellbilldetail.pricetype  = ' . $pricetype . ' ';
        $queryString1SR_sell .= 'and  sellandruternbilldetail.pricetype  = ' . $pricetype . ' ';
        switch ($pricetype) {
            case 0:
                $message .= "نوع السعر : جملة ";
                break;
            case 1:
                $message .= "نوع السعر : تجزئة ";
                break;
            case 2:
                $message .= "نوع السعر : نصف جملة ";
                break;
        }
    }
    if (isset($productCatId) && !empty($productCatId) && $productCatId != -1) {
        $cat = $productCatDAO->load($productCatId);
        $message .= " التصنيف : $cat->productCatName";

        ##get subcat of cat
        $catsIDS = '' . $productCatId;
        getAllSubCat($productCatId, 1); //mode = 1 get all sub cats
        $productsOfCat = $ProductEX->queryByProductCatIdIn($catsIDS);
        $IDS = '0';
        foreach ($productsOfCat as $value) {
            $IDS .= ',' . $value->productId;
        }
        //
        $queryString .= 'and  billsproductsbuy.productid  in (' . $IDS . ') ';
        $queryString1 .= 'and  buybilldetail.buybilldetailproductid  in (' . $IDS . ') ';
        $queryString1R .= 'and  returnbuybilldetail.returnbuybilldetailproductid  in (' . $IDS . ') ';
        $queryString1SR .= 'and  buyandruternbilldetail.buybilldetailproductid  in (' . $IDS . ') ';
        //
        $queryString_sell .= 'and  billsproducts.productid  in (' . $IDS . ') ';
        $queryString1_sell .= 'and  sellbilldetail.sellbilldetailproductid  in (' . $IDS . ') ';
        $queryStringR_sell .= 'and  billsreturnproducts.productid   in (' . $IDS . ') ';
        $queryString1R_sell .= 'and  returnsellbilldetail.returnsellbilldetailproductid  in (' . $IDS . ') ';
        $queryString1SR_sell .= 'and  sellandruternbilldetail.sellbilldetailproductid  in (' . $IDS . ') ';
    }
    getData($productCatId, $queryString, $queryString1, $queryString1R, $queryString1SR, $queryString_sell, $queryString1_sell, $queryStringR_sell, $queryString1R_sell, $queryString1SR_sell);


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

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

function getData($productCatId, $queryString, $queryString1, $queryString1R, $queryString1SR, $queryString_sell, $queryString1_sell, $queryStringR_sell, $queryString1R_sell, $queryString1SR_sell) {
    global $billsProductsBuyEX;
    global $billsBuyDAO;
    global $billsBuyEX;
    global $buyBillDetailEX;
    global $returnBuyBillDetailEX;
    global $buyAndReturnBillDetailEX;
    //
    global $billsProductsEX;
    global $billsDAO;
    global $billsEX;
    global $billsReturnDAO;
    global $billsReturnProductsEX;
    global $sellbilldetailEX;
    global $returnSellBillDetailEX;
    global $sellAndRuternBillDetailEX;
    //
    global $ProductDAO;
    global $productCatDAO;
    global $productCatExt;
    global $ProductEX;
    global $smarty;
    global $lastLevelCatIDS;
    global $catsIDS;
    global $productUnitEX;
    global $unitEX;
    global $myStoredetailEx;
    global $ProgramsettingDAO;

    global $message;

    class productUnitData {

        public $catId;
        public $catName;
        public $productId;
        public $productName;
        public $unitId;
        public $unitName;
        public $amount = 0;
        public $price = 0;
        public $amount_ret = 0;
        public $price_ret = 0;
        public $amount_sell = 0;
        public $price_sell = 0;
        public $amount_ret_sell = 0;
        public $price_ret_sell = 0;
        public $productQuantity = 0;
        public $productLastPrice = 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 ") {

        $datefrom = $dateto = date("Y-m-d");
        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  billsbuy.billdate  >= "' . $datefrom . '" ';
            $queryString1 .= 'and  buybill.buybilldate  >= "' . $datefrom . '" ';
            $queryString1R .= 'and  returnbuybill.returnbuybilldate  >= "' . $datefrom . '" ';
            $queryString1SR .= 'and  buyandruternbill.buybilldate  >= "' . $datefrom . '" ';
            //
            $queryString_sell .= 'and  bills.billdate  >= "' . $datefrom . '" ';
            $queryStringR_sell .= 'and  billsreturn.`date`  >= "' . $datefrom . '" ';
            $queryString1_sell .= 'and  sellbill.sellbilldate  >= "' . $datefrom . '" ';
            $queryString1R_sell .= 'and  returnsellbill.returnsellbilldate  >= "' . $datefrom . '" ';
            $queryString1SR_sell .= 'and  sellbillandrutern.sellbilldate  >= "' . $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  billsbuy.billdate  <= "' . $dateto . '" ';
            $queryString1 .= 'and  buybill.buybilldate  <= "' . $dateto . '" ';
            $queryString1R .= 'and  returnbuybill.returnbuybilldate  <= "' . $dateto . '" ';
            $queryString1SR .= 'and  buyandruternbill.buybilldate  <= "' . $dateto . '" ';
            //
            $queryString_sell .= 'and  bills.billdate  <= "' . $dateto . '" ';
            $queryStringR_sell .= 'and   billsreturn.`date`  <= "' . $dateto . '" ';
            $queryString1_sell .= 'and  sellbill.sellbilldate  <= "' . $dateto . '" ';
            $queryString1R_sell .= 'and  returnsellbill.returnsellbilldate  <= "' . $dateto . '" ';
            $queryString1SR_sell .= 'and  sellbillandrutern.sellbilldate  <= "' . $dateto . '" ';
            $message .= ' التاريخ الى"' . $dateto . '"';
        }
    }
    $billsData = $billsProductsBuyEX->queryAllGeneral($queryString);
    $buyBillData = $buyBillDetailEX->queryAllGeneral($queryString1);
    $returnBuyBillData = $returnBuyBillDetailEX->queryAllGeneral($queryString1R);
    $buyAndReturnBillData = $buyAndReturnBillDetailEX->queryAllGeneral($queryString1SR);
    //
    $billsData_sell = $billsProductsEX->queryAllGeneral($queryString_sell);
    $billsDataReturn_sell = $billsReturnProductsEX->queryAllGeneral($queryStringR_sell);
    $sellBillData = $sellbilldetailEX->queryAllGeneral($queryString1_sell);
    $sellBillDataReturn = $returnSellBillDetailEX->queryAllGeneral($queryString1R_sell);
    $sellBillDataSellAndReturn = $sellAndRuternBillDetailEX->queryAllGeneral($queryString1SR_sell);

    $resultsCount = count($billsData) + count($buyBillData) + count($returnBuyBillData) + count($buyAndReturnBillData) + count($billsData_sell) + count($billsDataReturn_sell) + count($sellBillData) + count($sellBillDataReturn) + count($sellBillDataSellAndReturn);
    $smarty->assign("resultsCount", $resultsCount);
    if ($resultsCount > 0) {
        ########################################################
        ## buy bills
        ########################################################
        ##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;
                    #################
                } 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->price += $value->producttotalprice;
                    $myproduct->amount += $value->productno;
                    #################
                    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->price = $value->producttotalprice;
                $myproduct->amount = $value->productno;
                #################
                //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;
                    #################
                } 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->price += $value->buybilldetailtotalprice;
                    $myproduct->amount += $quantity;
                    #################
                    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->price = $value->buybilldetailtotalprice;
                $myproduct->amount = $quantity;
                #################
                //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;
                    #################
                } 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->price_ret += $value->returnbuybilldetailtotalprice;
                    $myproduct->amount_ret += $quantity;
                    #################
                    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->price_ret = $value->returnbuybilldetailtotalprice;
                $myproduct->amount_ret = $quantity;
                #################
                //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;
                    }
                    #################
                } 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;

                    if ($billType == 0) {
                        $myproduct->price += $value->buybilldetailtotalprice;
                        $myproduct->amount += $quantity;
                    } else {
                        $myproduct->price_ret += $value->buybilldetailtotalprice;
                        $myproduct->amount_ret += $quantity;
                    }
                    #################
                    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;

                if ($billType == 0) {
                    $myproduct->price += $value->buybilldetailtotalprice;
                    $myproduct->amount += $quantity;
                } else {
                    $myproduct->price_ret += $value->buybilldetailtotalprice;
                    $myproduct->amount_ret += $quantity;
                }
                #################
                //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;
            }
        }

        ########################################################
        ## sell bills
        ########################################################
        ##مبيعات البصريات
        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_sell += $value->producttotalprice;
                    $myproduct->amount_sell += $value->productno;
                    #################
                } 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->price_sell += $value->producttotalprice;
                    $myproduct->amount_sell += $value->productno;
                    #################
                    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->price_sell = $value->producttotalprice;
                $myproduct->amount_sell = $value->productno;
                #################
                //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 ($billsDataReturn_sell 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_ret_sell += $value->producttotalprice;
                    $myproduct->amount_ret_sell += $value->productno;
                    #################
                } 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->price_ret_sell += $value->producttotalprice;
                    $myproduct->amount_ret_sell += $value->productno;
                    #################
                    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->price_ret_sell = $value->producttotalprice;
                $myproduct->amount_ret_sell = $value->productno;
                #################
                //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 ($sellBillData as $value) {
            $productid = $productId = $value->sellbilldetailproductid;
            ##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;
            }
            $value->sellbilldetailtotalprice = round($value->sellbilldetailtotalprice, 2);
            #########get quantity بالقطعة
            $quantity = $value->sellbilldetailquantity;
            $productunitId = $value->productunitid;
            $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
            $productnumber = $productunitData->productnumber;
            $quantity = $quantity * $productnumber;
            ##############
            ##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_sell += $value->sellbilldetailtotalprice;
                    $myproduct->amount_sell += $quantity;
                    #################
                } 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->price_sell += $value->sellbilldetailtotalprice;
                    $myproduct->amount_sell += $quantity;
                    #################
                    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->price_sell = $value->sellbilldetailtotalprice;
                $myproduct->amount_sell = $quantity;
                #################
                //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 ($sellBillDataReturn as $value) {
            $productid = $productId = $value->returnsellbilldetailproductid;
            ##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;
            }
            $value->sellbilldetailtotalprice = round($value->sellbilldetailtotalprice, 2);
            #########get quantity بالقطعة
            $quantity = $value->returnsellbilldetailquantity;
            $productunitId = $value->productunitid;
            $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
            $productnumber = $productunitData->productnumber;
            $quantity = $quantity * $productnumber;
            ##############
            ##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_sell += $value->returnsellbilldetailtotalprice;
                    $myproduct->amount_ret_sell += $quantity;
                    #################
                } 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->price_ret_sell += $value->returnsellbilldetailtotalprice;
                    $myproduct->amount_ret_sell += $quantity;
                    #################
                    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->price_ret_sell = $value->returnsellbilldetailtotalprice;
                $myproduct->amount_ret_sell = $quantity;
                #################
                //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 ($sellBillDataSellAndReturn as $value) {
            $productid = $productId = $value->sellbilldetailproductid;
            ##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;
            }
            $billType = $value->selltype;
            $value->sellbilldetailtotalprice = round($value->sellbilldetailtotalprice, 2);
            #########get quantity بالقطعة
            $quantity = $value->sellbilldetailquantity;
            $productunitId = $value->productunitid;
            $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
            $productnumber = $productunitData->productnumber;
            $quantity = $quantity * $productnumber;
            ##############
            ##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_sell += $value->sellbilldetailtotalprice;
                        $myproduct->amount_sell += $quantity;
                    } else {
                        $myproduct->price_ret_sell += $value->sellbilldetailtotalprice;
                        $myproduct->amount_ret_sell += $quantity;
                    }
                    #################
                } 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;

                    if ($billType == 0) {
                        $myproduct->price_sell += $value->sellbilldetailtotalprice;
                        $myproduct->amount_sell += $quantity;
                    } else {
                        $myproduct->price_ret_sell += $value->sellbilldetailtotalprice;
                        $myproduct->amount_ret_sell += $quantity;
                    }
                    #################
                    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;

                if ($billType == 0) {
                    $myproduct->price_sell += $value->sellbilldetailtotalprice;
                    $myproduct->amount_sell += $quantity;
                } else {
                    $myproduct->price_ret_sell += $value->sellbilldetailtotalprice;
                    $myproduct->amount_ret_sell += $quantity;
                }
                #################
                //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;
            }
        }
        $x = 0;
        ########################################################
        ## get store quantity
        ########################################################
        $Programsettingdata = $ProgramsettingDAO->load(1);
        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 "generalPrice":
                        $pro_price = (float) $pro->generalPrice;
                        break;
                    case "tax":
                        $pro_price = (float) $pro->lastbuyprice_withTax;
                        break;
                    case "mean_tax":
                        $pro_price = (float) $pro->meanbuyprice_withTax;
                        break;
                }
                $value->productQuantity = $productQuantity;
                $value->productLastPrice = $pro_price;
                //
                $value->storeQuantity += $value->productQuantity;
                $value->storeQuantityPrice = $value->productQuantity * $pro_price;
            }
        }
        ########################################################
        ## total of cats
        ########################################################
        $allCatIndexArr = array();
        $allCatArr = array();
        $pro = $ProductEX->queryByProductCatIdLimited($productCatId);
        $isLastLevelCatFlag = 1;
        if ($pro->productId > 0 && $productCatId > 0) {
            //last level of cats show products only
            $isLastLevelCatFlag = 1;
        } else {
            //not last level of cats show total of cats only
            $isLastLevelCatFlag = 0;
            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->price_sell += $value->price_sell;
                            $mycat->amount_sell += $value->amount_sell;
                            $mycat->price_ret_sell += $value->price_ret_sell;
                            $mycat->amount_ret_sell += $value->amount_ret_sell;
                            //
                            $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->price_sell += $value->price_sell;
                            $mycat->amount_sell += $value->amount_sell;
                            $mycat->price_ret_sell += $value->price_ret_sell;
                            $mycat->amount_ret_sell += $value->amount_ret_sell;
                            //
                            $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->price_sell += $value->price_sell;
                        $mycat->amount_sell += $value->amount_sell;
                        $mycat->price_ret_sell += $value->price_ret_sell;
                        $mycat->amount_ret_sell += $value->amount_ret_sell;
                        //
                        $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);
                    }
                }
            }
        }
    }

//    $smarty->assign("unitTotal", $unitTotal);
//    $smarty->assign("totalOfTotals", $totalOfTotals);
    $smarty->assign("isLastLevelCatFlag", $isLastLevelCatFlag);
    $smarty->assign("allCatArr", $allCatArr);
    $smarty->assign("productTotalArr", $productTotalArr);
    $smarty->assign("allDataArr", $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);
//                }
//            }
//        }
    }
}

?>