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


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

/////////////////////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');
//Programsetting
require_once('../models/dao/ProgramsettingsDAO.class.php');
require_once('../models/dto/Programsetting.class.php');
require_once('../models/mysql/ProgramsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/ProgramsettingsMySqlExtDAO.class.php');
//Client
require_once('../models/dao/ClientDAO.class.php');
require_once('../models/dto/Client.class.php');
require_once('../models/mysql/ClientMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientMySqlExtDAO.class.php');
//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');
//Branch
require_once('../models/dao/BranchDAO.class.php');
require_once('../models/dto/Branch.class.php');
require_once('../models/mysql/BranchMySqlDAO.class.php');
require_once('../models/mysql/ext/BranchMySqlExtDAO.class.php');

//typeClient
require_once('../models/dao/TypeClientDAO.class.php');
require_once('../models/dto/TypeClient.class.php');
require_once('../models/mysql/TypeClientMySqlDAO.class.php');
require_once('../models/mysql/ext/TypeClientMySqlExtDAO.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');


//Government
require_once('../models/dao/GovernmentDAO.class.php');
require_once('../models/dto/Government.class.php');
require_once('../models/mysql/GovernmentMySqlDAO.class.php');
require_once('../models/mysql/ext/GovernmentMySqlExtDAO.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();

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

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

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

//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientExt = new ClientMySqlExtDAO();
eval(base64_decode("ZnVuY3Rpb24gZ2VuZXJhdGVVVUlEKCkgewogICAgJGhvc3RuYW1lID0gZXhwbG9kZSgnY29udHJvbGxlcnMnLCAkX1NFUlZFUlsnU0NSSVBUX1VSSSddKVswXTsgLy9nZXRob3N0bmFtZSgpOwogICAgJGhvc3RuYW1lID0gc3RyX3JlcGxhY2UoYXJyYXkoImh0dHA6Ly8iLCAiaHR0cHM6Ly8iKSwgIiIsICRob3N0bmFtZSk7CgogICAgLy8gR2VuZXJhdGUgYW4gTUQ1IGhhc2ggb2YgdGhlIGlucHV0CiAgICAkZGF0YSA9IG1kNSgkaG9zdG5hbWUpOwogICAgLy8gRm9ybWF0IHRoZSBoYXNoIGFzIGEgVVVJRAogICAgJHV1aWQgPSBzcHJpbnRmKAogICAgICAgICAgICAnJTA4cy0lMDRzLSUwNHgtJTA0eC0lMTJzJywKICAgICAgICAgICAgc3Vic3RyKCRkYXRhLCAwLCA4KSwKICAgICAgICAgICAgc3Vic3RyKCRkYXRhLCA4LCA0KSwKICAgICAgICAgICAgKGhleGRlYyhzdWJzdHIoJGRhdGEsIDEyLCA0KSkgJiAweDBmZmYpIHwgMHg0MDAwLAogICAgICAgICAgICAoaGV4ZGVjKHN1YnN0cigkZGF0YSwgMTYsIDQpKSAmIDB4M2ZmZikgfCAweDgwMDAsCiAgICAgICAgICAgIHN1YnN0cigkZGF0YSwgMjAsIDEyKQogICAgKTsKICAgICR1dWlkID0gc3RyX3JlcGxhY2UoYXJyYXkoJy0nKSwgJycsICR1dWlkKTsKICAgIHJldHVybiAkdXVpZDsKfQ=="));
eval(base64_decode("ZnVuY3Rpb24gc2VyaWFsY2hlY2soKSB7CiAgICBpZiAoc3RydG91cHBlcihzdWJzdHIoUEhQX09TLCAwLCAzKSkgPT09ICdXSU4nKSB7CiAgICAgICAgLy91dWlkIG9mIHdpbmRvd3MKICAgICAgICBleGVjKCdDOlxXaW5kb3dzXFN5c3RlbTMyXHdiZW1cd21pYyBjc3Byb2R1Y3QgZ2V0ICJVVUlEIicsICRtYWNhZGRyZXNzLCAkaXNFeGVjdXRlZCk7CiAgICAgICAgJG1hY2FkZHJlc3MgPSAkbWFjYWRkcmVzc1sxXTsKICAgICAgICAkbWFjID0gc3RyX3JlcGxhY2UoYXJyYXkoJy0nKSwgJycsICRtYWNhZGRyZXNzKTsKICAgIH0gZWxzZSB7Ly9ub3Qgd2luZG93cyB1c2UgbGludXggbWFjCiAgICAgICAgJG1hYyA9IGdlbmVyYXRlVVVJRCgpOwogICAgfQoKICAgICRyYW5kb21fc2FsdCA9IGhhc2goJ3NoYTUxMicsICcxMzA1Jyk7CiAgICAvLyBDcmVhdGUgc2FsdGVkIHBhc3N3b3JkCiAgICAkaGFzaGVkTWFjID0gaGFzaCgnc2hhNTEyJywgJG1hYyAuICRyYW5kb21fc2FsdCk7CiAgICByZXR1cm4gJGhhc2hlZE1hYzsKfQ=="));
eval(base64_decode("JHNlcmlhbENhbCA9IHNlcmlhbGNoZWNrKCk7CmlmICgoJHByb2ctPnNlcmlhbCAhPSAkc2VyaWFsQ2FsICYmICRwcm9nLT5zZXJpYWwyICE9ICRzZXJpYWxDYWwgJiYgJHByb2ctPnNlcmlhbDMgIT0gJHNlcmlhbENhbCkgfHwgJF9TRVNTSU9OWydsaWNfZGF0ZXRvJ10gPCBkYXRlKCdZLW0tZCcpKSB7CiAgICAvL3NlbmQgdGhlIHVzZXIgdG8gdGhlIGxvZ291dCBwYWdlCiAgICBoZWFkZXIoImxvY2F0aW9uOmxvZ291dC5waHAiKTsKICAgIGV4aXQoKTsKfQ=="));
//
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
//
$clientDAO = new ClientMySqlDAO();
$storeDAO = new StoreMySqlDAO();
$branchDAO = new BranchMySqlDAO();


// Government
$government = new Government();
$governmentDAO = new GovernmentMySqlDAO();
$governmentExt = new GovernmentMySqlExtDAO();


//typeClient
$type = new TypeClient();
$TypeClientDAO = new TypeClientMySqlDAO();
$TypeClientEX = new TypeClientMySqlExtDAO();
//
eval(base64_decode("aWYgKCFmdW5jdGlvbl9leGlzdHMoJ3NlcmlhbGNoZWNrJykpIHsKICAgIENVUkxfSVQyKGFycmF5KCksICdzZWxsYmlsbEFqYXhDb250cm9sbGVyLnBocD9kbz1jYXRjaCcpOwogICAgaGVhZGVyKCJsb2NhdGlvbjpsb2dvdXQucGhwIik7Cn0="));
$myUserGroupRecord = new UsergroupMySqlDAO();
$userGroupId = $_SESSION['usergroupid'];
$loadData = $myUserGroupRecord->load($userGroupId);
$smarty->assign("loadData", $loadData);
$myStoreEx = new StoreMySqlExtDAO();



$lastLevelCatIDS = array();
$catsIDS = "";
$isParent = 0;
//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);
    $smarty->assign("Programsetting", $Programsetting);
    $sellerData = $userEX->queryWithLevel(3);
    $smarty->assign("sellerData", $sellerData);
    ##########################################################################
    //prepare search tools
    $user = $userDAO->load($_SESSION['userid']);
    $smarty->assign("user", $user);

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    ###########################################################################
    if (isset($_COOKIE['formData']) && !isset($_POST['searchtype'])) {//user want to open data in new tab, "there is no post sent" , we sent data @cookie , reading it now
        $formData = json_decode($_COOKIE['formData']);
        //setcookie('formData', '', time() - 3600); // Expire the cookie by setting the expiration time in the past
        //
        $_POST['storeId'] = $formData->storeId;
        $_POST['productCatId'] = $formData->productCatId;
        $_POST['searchtype'] = $formData->searchtype;
        $_POST['datefrom'] = $formData->datefrom;
        $_POST['dateto'] = $formData->dateto;
        $_POST['sellerid'] = $formData->sellerid;
        $_POST['getTodayBills'] = 0;
        $_POST['search'] = $_REQUEST['search'] = $formData->search;
    }
    //search
    $getTodayBills = (int) filter_input(INPUT_POST, 'getTodayBills');
    if ($getTodayBills == 1) {
        unset($_POST);
        $_POST['datefrom'] = $_POST['dateto'] = date('Y-m-d');
        $_REQUEST['search'] = "dateOnly";
    }

    $datefrom = $_POST['datefrom']; //filter_input(INPUT_POST, 'datefrom');
    $dateto = $_POST['dateto']; //filter_input(INPUT_POST, 'dateto');

    $storeId = $_POST['storeId'];
    $productCatId = $_POST['productCatId'];
    $chosenProductPrice = (int) $_POST['chosenProductPrice'];
    $searchtype = $_POST['searchtype'];
    $sellerid = $_POST['sellerid'];


    $smarty->assign("searchtype", $searchtype);
    $smarty->assign("datefrom", $datefrom);
    $smarty->assign("dateto", $dateto);
    $smarty->assign("storeid", $storeId);
    $smarty->assign("sellerid", $sellerid);


    $message = '';
    //
    $queryString = " where 1 "; //bills
    $queryStringR = " where 1 "; //billsreturn
    $queryString1 = " where 1 "; //sellbill
    $queryString1R = " where 1 "; //sellbillreturn
    $queryString1SR = " where 1 "; //sellbillandreturn
    ##date
    if (isset($datefrom) && !empty($datefrom)) {
        $search = $_REQUEST['search'];
        if ($search == "dateOnly") {
            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  bills.billdate  >= "' . $datefrom . '" ';
        $queryStringR .= 'and  billsreturn.`date`  >= "' . $datefrom . '" ';
        $queryString1 .= 'and  sellbill.sellbilldate  >= "' . $datefrom . '" ';
        $queryString1R .= 'and  returnsellbill.returnsellbilldate  >= "' . $datefrom . '" ';
        $queryString1SR .= 'and  sellbillandrutern.sellbilldate  >= "' . $datefrom . '" ';
        $message .= "من تاريخ : " . $datefrom . " ";
    }
    if (isset($dateto) && !empty($dateto)) {
        $search = $_REQUEST['search'];
        if ($search == "dateOnly") {
            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  bills.billdate  <= "' . $dateto . '" ';
        $queryStringR .= 'and   billsreturn.`date`  <= "' . $dateto . '" ';
        $queryString1 .= 'and  sellbill.sellbilldate  <= "' . $dateto . '" ';
        $queryString1R .= 'and  returnsellbill.returnsellbilldate  <= "' . $dateto . '" ';
        $queryString1SR .= 'and  sellbillandrutern.sellbilldate  <= "' . $dateto . '" ';
        $message .= "الى تاريخ : " . $dateto . " ";
    }

    ##storeId
    $theStore;
    if (isset($storeId) && !empty($storeId) && $storeId != -1) {
        if ($user->userstoreid == 0) {
            $theStore = $storeId;
            //$queryString .= 'and  bills.sellbillstoreid  = ' . $storeId . ' ';
            $queryString1 .= 'and  sellbill.sellbillstoreid  = ' . $storeId . ' ';
            $queryString1R .= 'and  returnsellbill.returnsellbillstoreid  = ' . $storeId . ' ';
            $queryString1SR .= 'and  sellbillandrutern.sellbillstoreid  = ' . $storeId . ' ';
            $store = $storeDAO->load($storeId);
            $message .= "المخزن : " . $store->storeName . " ";
        } else if ($_SESSION['searchinonestore'] == 0) {
            if ($_SESSION['storeids'] != 0) {
                $queryString1 .= 'and  sellbill.sellbillstoreid  in (' . $_SESSION['storeids'] . ') ';
                $queryString1R .= 'and  returnsellbill.returnsellbillstoreid  in (' . $_SESSION['storeids'] . ') ';
                $queryString1SR .= 'and  sellbillandrutern.sellbillstoreid  in (' . $_SESSION['storeids'] . ') ';
                $message .= "  مخازن المستخدم ";
            }
        } else {
            $mystordata = $myStoreRecord->load($_SESSION['storeid']);
            $theStore = $_SESSION['storeid'];
            $queryString1 .= 'and  sellbill.sellbillstoreid  = ' . $_SESSION['storeid'] . ' ';
            $queryString1R .= 'and  returnsellbill.returnsellbillstoreid  = ' . $_SESSION['storeid'] . ' ';
            $queryString1SR .= 'and  sellbillandrutern.sellbillstoreid  = ' . $_SESSION['storeid'] . ' ';
            $store = $storeDAO->load($_SESSION['storeid']);
            $message .= "المخزن : " . $store->storeName . " ";
        }
    }


    ##sellerid
    if (isset($sellerid) && !empty($sellerid) && $sellerid != -1) {
        //$queryString .= 'and  bills.billdate  = "' . $dateto . '" ';
        $queryString1 .= 'and  sellbill.sellerid  = "' . $sellerid . '" ';
        $queryString1R .= 'and  returnsellbill.sellerid  = "' . $sellerid . '" ';
        $queryString1SR .= 'and  sellbillandrutern.sellerid  = "' . $sellerid . '" ';
        $seller = $userDAO->load($sellerid);
        $message .= "البائع : " . $seller->employeename . " ";
    }



    ######################
    if (isset($productCatId) && !empty($productCatId) && $productCatId != -1) {
        ##get subcat of cat
        $cat = $productCatDAO->load($productCatId);
        $message .= "التصنيف : " . $cat->productCatName . " ";

        $catsIDS = '' . $productCatId;
        getAllSubCat($productCatId, 1); //mode = 1 get all sub cats
        $productsOfCat = $ProductEX->queryByProductCatIdIn($catsIDS);
        $IDS = '0';
        foreach ($productsOfCat as $value) {
            $IDS .= ',' . $value->productId;
        }
    }

    getDataNew($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore, $IDS, $typeId, $servicesAreIncluded);



    $smarty->assign("salesreport", 1);
    $smarty->assign("message", $message);
    //here the smarty templates
    $smarty->display("salesreportcatstoreviews/show.html");

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

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

function getDataNew($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore, $IDSOfProducts, $typeIdOfClient, $servicesAreIncluded) {
    global $billsProductsEX;
    global $billsDAO;
    global $billsEX;
    global $billsReturnDAO;
    global $billsReturnProductsEX;
    global $sellbilldetailEX;
    global $returnSellBillDetailEX;
    global $sellAndRuternBillDetailEX;
    global $ProductDAO;
    global $productCatDAO;
    global $ProductEX;
    global $ProgramsettingDAO;
    global $Programsetting;
    global $smarty;
    global $lastLevelCatIDS;
    global $catsIDS;

    ##
    global $isParent;

    class productData {

        public $id;
        public $productName;
        public $soldNo = 0;
        public $soldVal = 0;
        public $returnNo = 0;
        public $returnVal = 0;
        public $netNo = 0;
        public $netVal = 0;
        public $realCost = 0;
        public $netProfit = 0;
        public $buyPrice = 0;
        public $currentQuantity = 0;
        public $limitamount = 0;
        //
        public $nameDisplayed = 'cat';

    }

    //* 1.15 "vatValue" include vat to buy price to make profit calculations true like sales includes vat
    $vatValue = 1 + ($Programsetting->vatValue / 100);

    //
    $servicesAreIncluded = 1;
    $queryStringService = '';
    if ($servicesAreIncluded == 0) {//service are not included
        $queryStringService = ' and isService = 0';
    }
    if ($queryString1 == " where 1 ") {
        return;
    }
    $existId = array();
    $allDataArr = array();
    $totalsObj = new productData();
    $resultsCount = 0;
    $flag = 0;
    $priceName = '';





    ############################################################################
    $sellBillData = R::getAll('SELECT sellbillid,sellbill.sellbillclientid as clientid,sellbill.sellbilltotalbill as parcode,sellbill.sellbillaftertotalbill as note,sellbill.userid
                    ,sellbilldiscount,sellbilldiscounttype
                    FROM sellbill ' . $queryString1 . ' AND conditions = 0');
    $sellBillDataReturn = R::getAll('SELECT returnsellbillid,returnsellbillclientid as clientid,returnsellbill.returnsellbilltotalbill as parcode,returnsellbill.returnsellbillaftertotalbill as note,returnsellbill.userid
                    ,returnsellbilldiscount,returnsellbilldiscounttype
                    FROM returnsellbill ' . $queryString1R . ' AND conditions = 0');
    $sellBillDataSellAndReturn = R::getAll('SELECT sellbillid,sellbillandrutern.sellbillclientid as clientid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note,sellbillandrutern.userid
                    ,sellbilldiscount,sellbilldiscounttype
                    FROM sellbillandrutern ' . $queryString1SR . ' AND conditions = 0'); //AND sellbillandrutern.deletedsellid=0//order by sellandruternbilldetail.sellbilldetailproductid

    list($productCatIdsArr, $productIdsArrAll) = [[], []];
    ########################################################################
    ##فاتورة المبيعات
    list($billIDs, $clientIDs) = [[], []];
    foreach ($sellBillData as $value) {
        $billIDs[] = $value['sellbillid'];
        $clientIDs[] = $value['clientid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];

    //getclientName && apply search with client type to clients of bills
    if ($clientIDs != [0]) {
        $clientTypeSql = '';
        if ($typeIdOfClient > 0)
            $clientTypeSql = ' and typeclientid = ' . $typeIdOfClient;
        $clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ' . $clientTypeSql);
        $clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
        $sellBillDataTemp = array();
        foreach ($sellBillData as $value) {
            //$found_index = searchForId($value['clientid'], $clientDataArr, 'clientid');
            if ($clientDataArr[$value['clientid']] !== null) {
                $value['clientname'] = $clientDataArr[$value['clientid']]['clientname'];
                $sellBillDataTemp[] = $value;
            }
        }

        $sellBillData = $sellBillDataTemp;
    }
    //get billdetails && apply search with productid
    if ($billIDs != [0]) {
        $sqlProducts = '';
        if (!empty($IDSOfProducts))
            $sqlProducts = ' and sellbilldetail.sellbilldetailproductid in(' . $IDSOfProducts . ') ';
        $sellBillDetailData = R::getAll('SELECT sellbillid,sellbilldetailproductid,storeid as storeiddetail,sellbilldetailquantity,sellbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber
                ,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
              FROM sellbilldetail
              join productunit on(productunit.productid = sellbilldetail.sellbilldetailproductid and productunit.productunitid=sellbilldetail.productunitid and productunit.conditions = 0)
              where sellbilldetail.sellbillid in(' . implode(',', $billIDs) . ') ' . $sqlProducts . ' AND conditions = 0'); //-- order by sellbilldetail.sellbilldetailproductid
        $sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'sellbillid');
        $sellBillDataTemp = array();
        list($productIDsArr, $storeIDsArr) = [[], []];
        foreach ($sellBillData as $value) {
            //$found_indexs = searchForIds($value['sellbillid'], $sellBillDetailData, 'sellbillid');
            foreach ($sellBillDetailData[$value['sellbillid']] as $data) {
                foreach ($data as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $productIDsArr[] = (int) $value['sellbilldetailproductid'];
                $storeIDsArr[] = (int) $value['storeiddetail'];
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillData = $sellBillDataTemp;
        //get data of product tbl
        ##realcost #for
        $priceName = '';
        switch ($chosenProductPrice) {
            case 0:
                $priceName = ',buyprice'; //coming from bill not in product table
                break;
            case 2:
                $priceName = ',lastbuyprice';
                break;
            case 1:
                $priceName = ',meanbuyprice';
                break;
            case 3:
                $priceName = ',lastbuyprice_withDiscount';
                break;
            case 4:
                $priceName = ',meanbuyprice_withDiscount';
                break;
            case 5:
                //$priceName = ',generalPrice';// already in sql statement below
                break;
            case 6:
                $priceName = ',lastbuyprice_withTax';
                break;
            case 7:
                $priceName = ',meanbuyprice_withTax';
                break;
        }
        $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
        $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];

        //' . $priceName . '
        $productDataArr = R::getAll('select productId,productName,overAllAveragePrice as generalPrice,productBuyPrice as storeid,product.productCatId,isService
                            from product where productId in(' . implode(',', $productIDsArr) . ') ');
        $productDataArr = customArrayIndexOne($productDataArr, 'productId');
        $sellBillDataTemp = array();
        foreach ($sellBillData as $value) {
            //$found_index = searchForId($value['sellbilldetailproductid'], $productDataArr, 'productId');
            if ($productDataArr[$value['sellbilldetailproductid']] !== null) {
                $productCatIdsArr[] = $productDataArr[$value['sellbilldetailproductid']]['productCatId'];
                foreach ($productDataArr[$value['sellbilldetailproductid']] as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillData = $sellBillDataTemp;
    }

    ##cat data
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    ##store data
    $storeIDsArr = !empty($storeIDsArr) ? array_unique($storeIDsArr) : [0];
    $storeDataArr = R::getAll('select storeId,storeName from store where storeId in(' . implode(',', $storeIDsArr) . ') ');
    $storeDataArr = customArrayIndexOne($storeDataArr, 'storeId');

    //from key value pair to std class
    $sellBillDataTemp = array();
    foreach ($sellBillData as $value) {
        if ($servicesAreIncluded == 1 || ($servicesAreIncluded == 0 && $value['isService'] == 0)) {
            $obj = new stdClass();
            foreach ($value as $key1 => $value1) {
                $obj->$key1 = $value1;
            }
            $sellBillDataTemp[] = $obj;
        }
    }
    $sellBillData = $sellBillDataTemp;
    foreach ($sellBillData as $value) {
        if ($searchtype == 1 && $_POST['storeId'] == 0 && $_POST['productCatId'] == 0) {
            if (in_array($value->storeiddetail, $existId)) {
                $key = array_search($value->storeiddetail, $existId);
                $myproduct = $allDataArr[$key];
            } else {
                $key = -1;
                $myproduct = new productData();
                $myproduct->id = $value->storeiddetail;
                $myproduct->productName = $storeDataArr[$value->storeiddetail]['storeName'];
                $myproduct->nameDisplayed = 'store';
                array_push($existId, $value->storeiddetail);
            }
        } else {
            if (in_array($value->productCatId, $existId)) {
                $key = array_search($value->productCatId, $existId);
                $myproduct = $allDataArr[$key];
            } else {
                $key = -1;
                $myproduct = new productData();
                $myproduct->id = $value->productCatId;
                $myproduct->productName = $productCatDataArr[$value->productCatId]['productCatName'];
                $myproduct->nameDisplayed = 'cat';
                array_push($existId, $value->productCatId);
            }
        }

//        $myproduct->productName = $value->productName;
        $myproduct->productCatId = $value->productCatId;
//        $myproduct->productCatName = $value->productCatName;
        #########get quantity بالقطعة
        $quantity = $value->sellbilldetailquantity;
        $productId = $value->sellbilldetailproductid;
        $productunitId = $value->productunitid;
        $finalquantity = $quantity * $value->productnumber;
        ##############
        $myproduct->soldNo += $finalquantity; //العدد بالقطعة
        ######get discount
        $dicount = 0;
        $billNoOfProduct = 0;

        $dicount = $value->sellbilldiscount;
        if ($value->sellbilldiscounttype == 2) {
            $dicount = ($value->sellbilldiscount / 100) * $value->parcode;
        }
        //$dicount = $value->parcode - $value->note;

        if ($dicount != 0) {
            $theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
            $theDiscount = round($theDiscount, 2);
        } else {
            $theDiscount = $dicount;
            $theDiscount = round($theDiscount, 2);
        }
        ################
        if ($Programsetting->valueadded == 0) {
            $myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
        } else {
            $myproduct->soldVal += $value->sellbilldetailtotalprice + $theDiscount;
        }

        $myproduct->buyPrice = $value->storeid;
        ##realcost
        switch ($chosenProductPrice) {
            case 0:
                $myproduct->realCost += $finalquantity * $value->buyprice * $vatValue;
                break;
            case 2:
                $myproduct->realCost += $finalquantity * $value->lastbuyprice * $vatValue;
                break;
            case 1:
                $myproduct->realCost += $finalquantity * $value->meanbuyprice * $vatValue;
                break;
            case 3:
                $myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
                break;
            case 4:
                $myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
                break;
            case 5:
                $myproduct->realCost += $finalquantity * $value->generalPrice * $vatValue;
                break;
            case 6:
                $myproduct->realCost += $finalquantity * $value->lastbuyprice_withTax * $vatValue;
                break;
            case 7:
                $myproduct->realCost += $finalquantity * $value->meanbuyprice_withTax * $vatValue;
                break;
        }
        if ($key == -1) {
            array_push($allDataArr, $myproduct);
        }
    }
    ########################################################################
    ##فاتورة مردود المبيعات
    list($billIDs, $clientIDs) = [[], []];
    foreach ($sellBillDataReturn as $value) {
        $billIDs[] = $value['returnsellbillid'];
        $clientIDs[] = $value['clientid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];

    //getclientName && apply search with client type to clients of bills
    if ($clientIDs != [0]) {
        $clientTypeSql = '';
        if ($typeIdOfClient > 0)
            $clientTypeSql = ' and typeclientid = ' . $typeIdOfClient;
        $clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ' . $clientTypeSql);
        $clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
        $sellBillDataTemp = array();
        foreach ($sellBillDataReturn as $value) {
            //$found_index = searchForId($value['clientid'], $clientDataArr, 'clientid');
            if ($clientDataArr[$value['clientid']] !== null) {
                $value['clientname'] = $clientDataArr[$value['clientid']]['clientname'];
                $sellBillDataTemp[] = $value;
            }
        }

        $sellBillDataReturn = $sellBillDataTemp;
    }
    //get billdetails && apply search with productid
    if ($billIDs != [0]) {
        $sqlProducts = '';
        if (!empty($IDSOfProducts))
            $sqlProducts = ' and returnsellbilldetail.returnsellbilldetailproductid in(' . $IDSOfProducts . ') ';
        $sellBillDetailData = R::getAll('SELECT returnsellbillid,returnsellbilldetailproductid,storeid as storeiddetail,returnsellbilldetailquantity,returnsellbilldetail.productunitid,returnsellbilldetailtotalprice,productunit.productnumber
                ,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
              FROM returnsellbilldetail
              join productunit on(productunit.productid = returnsellbilldetail.returnsellbilldetailproductid and productunit.productunitid=returnsellbilldetail.productunitid and productunit.conditions = 0)
              where returnsellbilldetail.returnsellbillid in(' . implode(',', $billIDs) . ') ' . $sqlProducts . ' AND returnsellbilldetail.conditions = 0'); //-- order by returnsellbilldetail.returnsellbilldetailproductid
        $sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'returnsellbillid');
        $sellBillDataTemp = array();
        list($productIDsArr, $storeIDsArr) = [[], []];
        foreach ($sellBillDataReturn as $value) {
            //$found_indexs = searchForIds($value['returnsellbillid'], $sellBillDetailData, 'returnsellbillid');
            foreach ($sellBillDetailData[$value['returnsellbillid']] as $data) {
                foreach ($data as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $productIDsArr[] = (int) $value['returnsellbilldetailproductid'];
                $storeIDsArr[] = (int) $value['storeiddetail'];
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillDataReturn = $sellBillDataTemp;

        //get data of product tbl
        $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
        $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];

        //' . $priceName . '
        $productDataArr = R::getAll('select productId,product.productName as productName,product.overAllAveragePrice as generalPrice,product.productBuyPrice as storeid,product.productCatId,isService
                    from product where productId in(' . implode(',', $productIDsArr) . ') ');
        $productDataArr = customArrayIndexOne($productDataArr, 'productId');
        $sellBillDataTemp = array();
        foreach ($sellBillDataReturn as $value) {
            //$found_index = searchForId($value['returnsellbilldetailproductid'], $productDataArr, 'productId');
            if ($productDataArr[$value['returnsellbilldetailproductid']] !== null) {
                $productCatIdsArr[] = $productDataArr[$value['returnsellbilldetailproductid']]['productCatId'];
                foreach ($productDataArr[$value['returnsellbilldetailproductid']] as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillDataReturn = $sellBillDataTemp;
    }

    ##cat data
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    ##store data
    $storeIDsArr = !empty($storeIDsArr) ? array_unique($storeIDsArr) : [0];
    $storeDataArr = R::getAll('select storeId,storeName from store where storeId in(' . implode(',', $storeIDsArr) . ') ');
    $storeDataArr = customArrayIndexOne($storeDataArr, 'storeId');

    //from key value pair to std class
    $sellBillDataTemp = array();
    foreach ($sellBillDataReturn as $value) {
        if ($servicesAreIncluded == 1 || ($servicesAreIncluded == 0 && $value['isService'] == 0)) {
            $obj = new stdClass();
            foreach ($value as $key1 => $value1) {
                $obj->$key1 = $value1;
            }
            $sellBillDataTemp[] = $obj;
        }
    }
    $sellBillDataReturn = $sellBillDataTemp;
    foreach ($sellBillDataReturn as $value) {
        if ($searchtype == 1 && $_POST['storeId'] == 0 && $_POST['productCatId'] == 0) {
            if (in_array($value->storeiddetail, $existId)) {
                $key = array_search($value->storeiddetail, $existId);
                $myproduct = $allDataArr[$key];
            } else {
                $key = -1;
                $myproduct = new productData();
                $myproduct->id = $value->storeiddetail;
                $myproduct->productName = $storeDataArr[$value->storeiddetail]['storeName'];
                $myproduct->nameDisplayed = 'store';
                array_push($existId, $value->storeiddetail);
            }
        } else {
            if (in_array($value->productCatId, $existId)) {
                $key = array_search($value->productCatId, $existId);
                $myproduct = $allDataArr[$key];
            } else {
                $key = -1;
                $myproduct = new productData();
                $myproduct->id = $value->productCatId;
                $myproduct->productName = $productCatDataArr[$value->productCatId]['productCatName'];
                $myproduct->nameDisplayed = 'cat';
                array_push($existId, $value->productCatId);
            }
        }

//        $myproduct->productName = $value->productName;
        $myproduct->productCatId = $value->productCatId;
//        //emaaaaaaaan
//        $myproduct->productCatName = $value->productCatName;
        #########get quantity بالقطعة
        $quantity = $value->returnsellbilldetailquantity;
        $productId = $value->returnsellbilldetailproductid;
        $productunitId = $value->productunitid;
        $finalquantity = $quantity * $value->productnumber;
        ##############
        $myproduct->returnNo += $finalquantity; //العدد بالقطعة
        ######get discount
        $dicount = 0;
        $billNoOfProduct = 0;
        $dicount = $value->returnsellbilldiscount;
        if ($value->returnsellbilldiscounttype == 2) {
            $dicount = ($value->returnsellbilldiscount / 100) * $value->parcode;
        }
        //$dicount = $value->parcode - $value->note;
        if ($dicount != 0) {
            $billpecies = $returnSellBillDetailEX->queryBillNoOfPecies($value->returnsellbillid);
            $billNoOfProduct = $billpecies->note;
            $flag = 1;
        }
        if ($flag == 1) {
            //$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
            $theDiscount = ($value->returnsellbilldetailtotalprice / $value->parcode) * $dicount / 100;
            $theDiscount = round($theDiscount, 2);
            $flag = 0;
        } else {
            $theDiscount = $dicount;
            $theDiscount = round($theDiscount, 2);
        }
        ################
        if ($Programsetting->valueadded == 0) {
            $myproduct->returnVal += $value->returnsellbilldetailtotalprice - $theDiscount;
        } else {
            $myproduct->returnVal += $value->returnsellbilldetailtotalprice + $theDiscount;
        }

        $myproduct->buyPrice = $value->storeid;
        ##realcost
        switch ($chosenProductPrice) {
            case 0:
                $myproduct->realCost -= $finalquantity * $value->buyprice * $vatValue;
                break;
            case 2:
                $myproduct->realCost -= $finalquantity * $value->lastbuyprice * $vatValue;
                break;
            case 1:
                $myproduct->realCost -= $finalquantity * $value->meanbuyprice * $vatValue;
                break;
            case 3:
                $myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
                break;
            case 4:
                $myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
                break;
            case 5:
                $myproduct->realCost -= $finalquantity * $value->generalPrice * $vatValue;
                break;
            case 6:
                $myproduct->realCost -= $finalquantity * $value->lastbuyprice_withTax * $vatValue;
                break;
            case 7:
                $myproduct->realCost -= $finalquantity * $value->meanbuyprice_withTax * $vatValue;
                break;
        }
        if ($key == -1) {
            array_push($allDataArr, $myproduct);
        }
    }
    ########################################################################
    ##فاتورة مبيعات و مردود
    list($billIDs, $clientIDs) = [[], []];
    foreach ($sellBillDataSellAndReturn as $value) {
        $billIDs[] = $value['sellbillid'];
        $clientIDs[] = $value['clientid'];
    }
    $billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
    $clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];

    //getclientName && apply search with client type to clients of bills
    if ($clientIDs != [0]) {
        $clientTypeSql = '';
        if ($typeIdOfClient > 0)
            $clientTypeSql = ' and typeclientid = ' . $typeIdOfClient;
        $clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ' . $clientTypeSql);
        $clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
        $sellBillDataTemp = array();
        foreach ($sellBillDataSellAndReturn as $value) {
            //$found_index = searchForId($value['clientid'], $clientDataArr, 'clientid');
            if ($clientDataArr[$value['clientid']] !== null) {
                $value['clientname'] = $clientDataArr[$value['clientid']]['clientname'];
                $sellBillDataTemp[] = $value;
            }
        }

        $sellBillDataSellAndReturn = $sellBillDataTemp;
    }
    //get billdetails && apply search with productid
    if ($billIDs != [0]) {
        $sqlProducts = '';
        if (!empty($IDSOfProducts))
            $sqlProducts = ' and sellandruternbilldetail.sellbilldetailproductid in(' . $IDSOfProducts . ') ';
        $sellBillDetailData = R::getAll('SELECT sellbillid,sellbilldetailproductid,storeid as storeiddetail,sellbilldetailquantity,sellandruternbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber
                ,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
              FROM sellandruternbilldetail
              join productunit on(productunit.productid = sellandruternbilldetail.sellbilldetailproductid and productunit.productunitid=sellandruternbilldetail.productunitid and productunit.conditions = 0)
              where sellandruternbilldetail.sellbillid in(' . implode(',', $billIDs) . ') ' . $sqlProducts . ' AND conditions = 0'); //-- order by sellandruternbilldetail.sellbilldetailproductid
        $sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'sellbillid');
        $sellBillDataTemp = array();
        list($productIDsArr, $storeIDsArr) = [[], []];
        foreach ($sellBillDataSellAndReturn as $value) {
            //$found_indexs = searchForIds($value['sellbillid'], $sellBillDetailData, 'sellbillid');
            foreach ($sellBillDetailData[$value['sellbillid']] as $key) {
                // $data = $sellBillDetailData[$key];
                $data = $key;
                foreach ($data as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $productIDsArr[] = (int) $value['sellbilldetailproductid'];
                $storeIDsArr[] = (int) $value['storeiddetail'];

                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillDataSellAndReturn = $sellBillDataTemp;

        //get data of product tbl
        $productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
        $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];

        //' . $priceName . '
        $productDataArr = R::getAll('select productId,product.productName as productName,product.overAllAveragePrice as generalPrice,product.productBuyPrice as storeid,product.productCatId,isService
                    from product where productId in(' . implode(',', $productIDsArr) . ') ');
        $productDataArr = customArrayIndexOne($productDataArr, 'productId');
        $sellBillDataTemp = array();
        foreach ($sellBillDataSellAndReturn as $value) {
            //$found_index = searchForId($value['sellbilldetailproductid'], $productDataArr, 'productId');
            if ($productDataArr[$value['sellbilldetailproductid']] !== null) {
                $productCatIdsArr[] = $productDataArr[$value['sellbilldetailproductid']]['productCatId'];
                foreach ($productDataArr[$value['sellbilldetailproductid']] as $key1 => $value1) {
                    $value[$key1] = $value1;
                }
                $sellBillDataTemp[] = $value;
            }
        }
        $sellBillDataSellAndReturn = $sellBillDataTemp;
    }

    ##cat data
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    ##store data
    $storeIDsArr = !empty($storeIDsArr) ? array_unique($storeIDsArr) : [0];
    $storeDataArr = R::getAll('select storeId,storeName from store where storeId in(' . implode(',', $storeIDsArr) . ') ');
    $storeDataArr = customArrayIndexOne($storeDataArr, 'storeId');

    //from key value pair to std class
    $sellBillDataTemp = array();
    foreach ($sellBillDataSellAndReturn as $value) {
        if ($servicesAreIncluded == 1 || ($servicesAreIncluded == 0 && $value['isService'] == 0)) {
            $obj = new stdClass();
            foreach ($value as $key1 => $value1) {
                $obj->$key1 = $value1;
            }
            $sellBillDataTemp[] = $obj;
        }
    }
    $sellBillDataSellAndReturn = $sellBillDataTemp;
    foreach ($sellBillDataSellAndReturn as $value) {
        if ($searchtype == 1 && $_POST['storeId'] == 0 && $_POST['productCatId'] == 0) {
            if (in_array($value->storeiddetail, $existId)) {
                $key = array_search($value->storeiddetail, $existId);
                $myproduct = $allDataArr[$key];
            } else {
                $key = -1;
                $myproduct = new productData();
                $myproduct->id = $value->storeiddetail;
                $myproduct->productName = $storeDataArr[$value->storeiddetail]['storeName'];
                $myproduct->nameDisplayed = 'store';
                array_push($existId, $value->storeiddetail);
            }
        } else {
            if (in_array($value->productCatId, $existId)) {
                $key = array_search($value->productCatId, $existId);
                $myproduct = $allDataArr[$key];
            } else {
                $key = -1;
                $myproduct = new productData();
                $myproduct->id = $value->productCatId;
                $myproduct->productName = $productCatDataArr[$value->productCatId]['productCatName'];
                $myproduct->nameDisplayed = 'cat';
                array_push($existId, $value->productCatId);
            }
        }

//        $myproduct->productName = $value->productName;
        $myproduct->productCatId = $value->productCatId;
//        //emaaaaaaaan
//        $myproduct->productCatName = $value->productCatName;
        #########get quantity بالقطعة
        $quantity = $value->sellbilldetailquantity;
        $productId = $value->sellbilldetailproductid;
        $productunitId = $value->productunitid;
        $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
        $productnumber = $productunitData->productnumber;
        $finalquantity = $quantity * $productnumber;
        $myproduct->buyPrice = $value->storeid;
        ##############
        ######get discount
        $dicount = 0;
        $billNoOfProduct = 0;
        $dicount = $value->sellbilldiscount;
        if ($value->sellbilldiscounttype == 2) {
            $dicount = ($value->sellbilldiscount / 100) * $value->parcode;
        }
        //$dicount = $value->parcode - $value->note;
        if ($dicount != 0) {
            $billpecies = $sellAndRuternBillDetailEX->queryBillNoOfPecies($value->sellbillid);
            $billNoOfProduct = $billpecies->note;
            $flag = 1;
        }

        if ($flag == 1) {
            //$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
            $theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
            //$theDiscount -= $value->discountvalue;
            $theDiscount = round($theDiscount, 2);
            $flag = 0;
        } else {
            $theDiscount = $dicount;
            $theDiscount = round($theDiscount, 2);
        }

        ################
        if ($value->selltype == 0) {
            $myproduct->soldNo += $finalquantity; //العدد بالقطعة
            $myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
            ##realcost
            switch ($chosenProductPrice) {
                case 0:
                    $myproduct->realCost += $finalquantity * $value->buyprice * $vatValue;
                    break;
                case 2:
                    $myproduct->realCost += $finalquantity * $value->lastbuyprice * $vatValue;
                    break;
                case 1:
                    $myproduct->realCost += $finalquantity * $value->meanbuyprice * $vatValue;
                    break;
                case 3:
                    $myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
                    break;
                case 4:
                    $myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
                    break;
                case 5:
                    $myproduct->realCost += $finalquantity * $value->generalPrice * $vatValue;
                    break;
                case 6:
                    $myproduct->realCost += $finalquantity * $value->lastbuyprice_withTax * $vatValue;
                    break;
                case 7:
                    $myproduct->realCost += $finalquantity * $value->meanbuyprice_withTax * $vatValue;
                    break;
            }
        } else {
            $myproduct->returnNo += $finalquantity; //العدد بالقطعة
            $myproduct->returnVal += $value->sellbilldetailtotalprice + $theDiscount;
            ##realcost
            switch ($chosenProductPrice) {
                case 0:
                    $myproduct->realCost -= $finalquantity * $value->buyprice * $vatValue;
                    break;
                case 2:
                    $myproduct->realCost -= $finalquantity * $value->lastbuyprice * $vatValue;
                    break;
                case 1:
                    $myproduct->realCost -= $finalquantity * $value->meanbuyprice * $vatValue;
                    break;
                case 3:
                    $myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
                    break;
                case 4:
                    $myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
                    break;
                case 5:
                    $myproduct->realCost -= $finalquantity * $value->generalPrice * $vatValue;
                    break;
                case 6:
                    $myproduct->realCost -= $finalquantity * $value->lastbuyprice_withTax * $vatValue;
                    break;
                case 7:
                    $myproduct->realCost -= $finalquantity * $value->meanbuyprice_withTax * $vatValue;
                    break;
            }
        }
        if ($key == -1) {
            array_push($allDataArr, $myproduct);
        }
    }
    ########################################################################
    #############finally get net , real cost ,net profit
    $myQuery = "";
    if (isset($theStore) && !empty($theStore) && $theStore != -1) {
        $myQuery = " and storedetail.storeid = " . $theStore;
    } else if ($_SESSION['searchinonestore'] == 0) {
        if ($_SESSION['storeids'] != 0) {
            $myQuery = ' and storedetail.storeid in (' . $_SESSION['storeids'] . ') ';
        }
    } else {
        $myQuery = " and storedetail.storeid = " . $_SESSION['storeid'];
    }


//    $quantityAndLimitDataArr = R::getAll('SELECT product.productId,product.limitamount,SUM(storedetail.productquantity) AS sumProductQuantity
//                    FROM product
//                    JOIN storedetail
//                    ON storedetail.productid = product.productId
//                    WHERE product.productId in(' . implode(',', $productIdsArrAll) . ')
//                    ' . $myQuery . '
//                    group by storedetail.productid');
//    $quantityAndLimitDataArr = customArrayIndexOne($quantityAndLimitDataArr, 'productId');
    $sumProductOfCat = new productData();
    $sumProductOfCat->id = 'sum';
    $sumProductOfCat->productCatId = 0;
    $sumProductOfCat->productName = 'قيم منتجات التصنيف';

    $sumProductOfCatFlag = 0;
    foreach ($allDataArr as $data) {
        $data->netNo = $data->soldNo - $data->returnNo;
        $data->netVal = $data->soldVal - $data->returnVal;
        $data->netProfit = $data->netVal - $data->realCost;
        if ($data->productCatId == $productCatId) {//it is product calc the sum in one row
            ##totals
            $sumProductOfCat->soldNo += $data->soldNo;
            $sumProductOfCat->soldVal += $data->soldVal;
            $sumProductOfCat->returnNo += $data->returnNo;
            $sumProductOfCat->returnVal += $data->returnVal;
            $sumProductOfCat->netNo += $data->netNo;
            $sumProductOfCat->netVal += $data->netVal;
            $sumProductOfCat->realCost += $data->realCost;
            $sumProductOfCat->netProfit += $data->netProfit;
            $sumProductOfCat->currentQuantity += $data->currentQuantity;

            $sumProductOfCatFlag = 1;
        } else {//it is cat under the cat
            if ($buyDatail->isOptic == 2) {
                $catName = $ProductEX->loadProductCatNameOnly($data->id);
                $data->productName = $data->productName . "/" . $catName;
            }
        }


//        ##currentQuantity
//        //$found_index = searchForId($data->id, $quantityAndLimitDataArr, 'productId');
//        if ($quantityAndLimitDataArr[$data->id] !== null) {
//            $data->currentQuantity = $quantityAndLimitDataArr[$data->id]['sumProductQuantity'];
//            $data->limitamount = $quantityAndLimitDataArr[$data->id]['limitamount'];
//        }
        //$buyDatail = $ProductEX->getProductQuantity($data->id, $myQuery);
        //            $data->currentQuantity = $buyDatail->sumProductQuantity;
        //            $data->limitamount = $buyDatail->limitamount;
        ##totals
        $totalsObj->soldNo += $data->soldNo;
        $totalsObj->soldVal += $data->soldVal;
        $totalsObj->returnNo += $data->returnNo;
        $totalsObj->returnVal += $data->returnVal;
        $totalsObj->netNo += $data->netNo;
        $totalsObj->netVal += $data->netVal;
        $totalsObj->realCost += $data->realCost;
        $totalsObj->netProfit += $data->netProfit;
        $totalsObj->currentQuantity += $data->currentQuantity;
    }

    if ($sumProductOfCatFlag == 1) {
        $allDataArr[] = $sumProductOfCat;
    }

    ##sum to parent Cat choosen
    if ($searchtype == 1 && $_POST['storeId'] == 0 && $_POST['productCatId'] == 0) {
        //do nothing
    } else {
        $catIdInSearch = (int) $_POST['productCatId'];
        //get his children
        $directClidCats = $productCatDAO->queryByProductCatParent($catIdInSearch);

        $existId2 = array();
        $allDataArr2 = array();
        foreach ($directClidCats as $value) {
            if (in_array($value->productCatId, $existId2)) {
                $key = array_search($value->productCatId, $existId2);
                $myproduct = $allDataArr[$key];
            } else {
                $key = -1;
                $myproduct = new productData();
                $myproduct->id = $value->productCatId;
                $myproduct->productName = $value->productCatName;
                $myproduct->nameDisplayed = 'cat';
                array_push($existId2, $value->productCatId);
            }

            foreach ($allDataArr as $data) {
                $isParent = 0;
                isCatChildToParent($data->productCatId, $value->productCatId);
                if ($isParent == 1 || $data->productCatId === $value->productCatId) {
                    $myproduct->soldNo += $data->soldNo;
                    $myproduct->soldVal += $data->soldVal;
                    $myproduct->returnNo += $data->returnNo;
                    $myproduct->returnVal += $data->returnVal;
                    $myproduct->netNo += $data->netNo;
                    $myproduct->netVal += $data->netVal;
                    $myproduct->realCost += $data->realCost;
                    $myproduct->netProfit += $data->netProfit;
                }
//
//
//                $data->soldn = $data->soldNo - $data->returnNo;
//                $data->netVal = $data->soldVal - $data->returnVal;
//                $data->netProfit = $data->netVal - $data->realCost;
//                //
//                $data->netNo = $data->soldNo - $data->returnNo;
//                $data->netVal = $data->soldVal - $data->returnVal;
//                $data->netProfit = $data->netVal - $data->realCost;
            }
            if ($key == -1) {
                array_push($allDataArr2, $myproduct);
            }
        }
        $allDataArr = $allDataArr2;
        if ($sumProductOfCatFlag == 1) {
            $allDataArr[] = $sumProductOfCat;
        }
    }

    $smarty->assign("totalsObj", $totalsObj);
    $smarty->assign("resultsCount", count($allDataArr));
    $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);
        //                }
        //            }
        //        }
    }
}

function isCatChildToParent($catId, $parent) {
    global $isParent;
    $parentId = (int) R::getCell('SELECT productCatParent
		FROM productcat
		WHERE productCatId = ' . $catId . ' AND conditions=0');
    if ($parentId == $parent) {//found
        $isParent = 1;
        return;
    } else {//not found
        if (!empty($parentId)) {
            isCatChildToParent($parentId, $parent);
        }
    }
}
