<?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 = "";
$mainCat;
//check and use the condition that suite this action
if (empty($do) || $do == "approvedsales") {
    //here the permission check
    include_once("../public/authentication.php");
    $Programsetting = $ProgramsettingDAO->load(1);
    $smarty->assign("Programsetting", $Programsetting);
    $smarty->assign("do", $do);
    ##########################################################################
    //prepare search tools
    ##التصنيفات
    $catDataReport = getCategoryChilds();
    $smarty->assign("catDataReport", $catDataReport[1]);
    ##انواع العملاء
    $types = $TypeClientEX->queryAllsup();
    $smarty->assign("types", $types);


    $allGovernment = $governmentDAO->queryAll();
    $smarty->assign("allGovernment", $allGovernment);

    $user = $userDAO->load($_SESSION['userid']);
    $smarty->assign("user", $user);

    $smarty->assign("searchinonestore", $_SESSION['searchinonestore']);
    if ($_SESSION['searchinonestore'] == 0) {
        if ($_SESSION['storeids'] == 0) {
            $stores = $myStoreEx->queryByConditions();
        } else {
            $stores = $myStoreEx->queryByConditions(' and store.storeId in (' . $_SESSION['storeids'] . ')');
        }
        $smarty->assign("storeData", $stores);
        $storedef = $myStoreEx->queryByConditionsOne(' and store.storeId = ' . $_SESSION['storeid'] . ' ');
        $smarty->assign("storedef", $storedef);
        $smarty->assign("storeid", $_SESSION['storeid']);
    } else {
        $storedef = $myStoreEx->queryByConditionsOne(' and store.storeId = ' . $_SESSION['storeid'] . ' ');
        $smarty->assign("storedef", $storedef);
        $smarty->assign("storeid", $_SESSION['storeid']);
    }


    // if ($user->userstoreid == 0) {
    //     $storeData = loadStore();
    //     $smarty->assign("storeData", $storeData);
    // }
    if ($user->branchId == 0) {
        $branchData = loadBranch();
        $smarty->assign("branchData", $branchData);
    }

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    $sellerData = loadseller();
    $smarty->assign("sellerData", $sellerData);
    $userData = loadusers();
    $smarty->assign("userData", $userData);
    ###########################################################################
    //search
    $getTodayBills = (int) filter_input(INPUT_POST, 'getTodayBills');
    //if ($queryString == "") {
    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');
    $clientId = filter_input(INPUT_POST, 'clientId');
    $obgyBillType = filter_input(INPUT_POST, 'obgyBillType');

    $storeId = filter_input(INPUT_POST, 'storeId');
    $branchId = filter_input(INPUT_POST, 'branchId');
    $typeId = filter_input(INPUT_POST, 'typeId');
    $pricetype = filter_input(INPUT_POST, 'pricetype');
    $sellerid = filter_input(INPUT_POST, 'sellerid');
    $userid = filter_input(INPUT_POST, 'userid');
    $tripid = (int) filter_input(INPUT_POST, 'tripid');

    $governmentid = filter_input(INPUT_POST, 'governmentid');
    $clientareaid = filter_input(INPUT_POST, 'clientareaid');
    $servicesAreIncluded = (int) filter_input(INPUT_POST, 'servicesAreIncluded');

    $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');
    $chosenProductPrice = (int) filter_input(INPUT_POST, 'chosenProductPrice');
    $searchtype = filter_input(INPUT_POST, 'searchtype');

    $days = $_POST['days'];
    $daysNo = implode(",", $days);

    $isOptic = filter_input(INPUT_POST, 'proIsOptic');
    if (!isset($isOptic) || empty($isOptic)) {
        $isOptic = 0;
    }
    if ($isOptic == 2 && ($searchtype == 1 || $searchtype == 2)) {
        $productCatId = $productId;
    }
    $message = '<strong>نوع السعر : </strong>';
    switch($chosenProductPrice){
        case 0:
            $message .= "سعر الشراء";
            break;
        case 1:
            $message .= "متوسط سعر الشراء";
            break;
        case 2:
            $message .= "اخر سعر شراء";
            break;
        case 3:
            $message .= "اخر سعر شراء بعد الخصم";
            break;
        case 4:
            $message .= "متوسط سعر الشراء بعد الخصم";
            break;
        case 5:
            $message .= "المتوسط العام";
            break;
        case 6:
            $message .= "اخر سعر شراء شامل القيمة المضافة";
            break;
        case 7:
            $message .= "متوسط سعر الشراء شامل القيمة المضافة";
            break;
    }
    //
    $queryString = " where 1 "; //bills
    $queryStringR = " where 1 "; //billsreturn
    $queryString1 = " where 1 "; //sellbill
    $queryString1R = " where 1 "; //sellbillreturn
    $queryString1SR = " where 1 "; //sellbillandreturn
    ##
    //    //display the converted time ##reportsPlusHours
    //    $Programsetting = $ProgramsettingDAO->load(1);
    if($daysNo)
    {
        $queryString .= ' and DAYOFWEEK(bills.billdate) IN (' . $daysNo . ') ';
        $queryStringR .= ' and DAYOFWEEK(billsreturn.`date`) IN (' . $daysNo . ') ';
        $queryString1 .= ' and DAYOFWEEK(sellbill.sellbilldate) IN (' . $daysNo . ') ';
        $queryString1R .= ' and DAYOFWEEK(returnsellbill.returnsellbilldate) IN (' . $daysNo . ') ';
        $queryString1SR .= ' and DAYOFWEEK(sellbillandrutern.sellbilldate) IN (' . $daysNo . ') ';
        $dayNames = array(
            1 => 'الأحد',
            2 => 'الإثنين',
            3 => 'الثلاثاء',
            4 => 'الأربعاء',
            5 => 'الخميس',
            6 => 'الجمعة',
            7 => 'السبت'
        );
        $daysMsg = '';
        foreach($days as $day){
            $daysMsg .= $dayNames[$day] . ' ';
        }

        $message .= "<strong> ايام الاسبوع : </strong> " . $daysMsg . " ";
    }
    ##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";
        //        }
        $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 .= "<strong>من تاريخ : </strong> " . date('Y-m-d', strtotime($datefrom)) . " ";
        $smarty->assign("datefrom", date('Y-m-d', strtotime($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';
        //        }
        $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 .= "<strong>الى تاريخ : </strong> " . date('Y-m-d', strtotime($dateto)) . " ";
        $smarty->assign("dateto", date('Y-m-d', strtotime($dateto)));
    }
    ##clientId
    if (isset($clientId) && !empty($clientId) && $clientId != -1) {
        $queryString .= 'and  bills.clientid  = ' . $clientId . ' ';
        $queryStringR .= 'and  bills.clientid  = ' . $clientId . ' ';
        $queryString1 .= 'and  sellbill.sellbillclientid  = ' . $clientId . ' ';
        $queryString1R .= 'and  returnsellbill.returnsellbillclientid  = ' . $clientId . ' ';
        $queryString1SR .= 'and  sellbillandrutern.sellbillclientid  = ' . $clientId . ' ';
        $client = $clientDAO->load($clientId);
        $message .= "<strong>العميل : </strong> " . $client->clientname . " ";
        $smarty->assign('clientName', $client->clientname);
    }



    ##clientId
    if (isset($obgyBillType) && !empty($obgyBillType)) {
        $queryString1 .= 'and  sellbill.obgyBillType  = ' . $obgyBillType . ' ';
        $queryString1R .= 'and  returnsellbill.obgyBillType  = ' . $obgyBillType . ' ';
        $queryString1SR .= 'and  sellbillandrutern.obgyBillType  = ' . $obgyBillType . ' ';
        $message .= "<strong>نوع الفاتورة : </strong> ";
        switch ($obgyBillType) {
            case 1:
                $message .= "مباشر ";
                break;
            case 2:
                $message .= "مستهلك ";
                break;
            case 3:
                $message .= "استقبال ";
                break;
        }
    }

    ##typeId
    if (isset($typeId) && !empty($typeId) && $typeId != -1) {

        $queryString .= 'and  client.typeclientid in (' . $typeId . ') ';
        $queryStringR .= 'and  client.typeclientid in (' . $typeId . ') ';
        $queryString1 .= 'and  client.typeclientid in (' . $typeId . ') ';
        $queryString1R .= 'and  client.typeclientid in (' . $typeId . ') ';
        $queryString1SR .= 'and  client.typeclientid in (' . $typeId . ') ';
        $type = $TypeClientDAO->load($typeId);
        $message .= "<strong>نوع العميل : </strong> " . $type->typeName . " ";
    }

    if (isset($governmentid) && !empty($governmentid) && $governmentid != -1 && $clientareaid < 1) {
        $goverareas = R::getAll("SELECT * FROM goverarea WHERE  governmentid = ? ", [$governmentid]);
        $clientareaids = '0';
        foreach ($goverareas as $goverarea) {
            $clientareaids .= $goverarea['clientareaid'];
        }

        $queryString .= 'and  client.clientareaid  in (' . $clientareaids . ') ';
        $queryStringR .= 'and  client.clientareaid in (' . $clientareaids . ') ';
        $queryString1 .= 'and  client.clientareaid  in (' . $clientareaids . ') ';
        $queryString1R .= 'and  client.clientareaid  in (' . $clientareaids . ') ';
        $queryString1SR .= 'and  client.clientareaid  in (' . $clientareaids . ') ';
    }


    if (isset($clientareaid) && !empty($clientareaid) && $clientareaid != -1) {

        $queryString .= 'and  client.clientareaid  = ' . $clientareaid . ' ';
        $queryStringR .= 'and  client.clientareaid  = ' . $clientareaid . ' ';
        $queryString1 .= 'and  client.clientareaid  = ' . $clientareaid . ' ';
        $queryString1R .= 'and  client.clientareaid  = ' . $clientareaid . ' ';
        $queryString1SR .= 'and  client.clientareaid  = ' . $clientareaid . ' ';
    }

    ##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 . ' OR storeid = ' . $storeId . ' ';
            // $queryString1R .= 'and  returnsellbill.returnsellbillstoreid  = ' . $storeId . ' OR storeid = ' . $storeId . ' ';
            // $queryString1SR .= 'and  sellbillandrutern.sellbillstoreid  = ' . $storeId . ' ';
            $store = $storeDAO->load($storeId);
            $message .= "<strong>المخزن : </strong> " . $store->storeName . " ";
        } else if ($_SESSION['searchinonestore'] == 0) {
            if ($_SESSION['storeids'] != 0) {
                $theStore = $_SESSION['storeids'];
                // $queryString1 .= 'and  sellbill.sellbillstoreid  in (' . $_SESSION['storeids'] . ') OR storeid in(' . $_SESSION['storeid'] . ') ';
                // $queryString1R .= 'and  returnsellbill.returnsellbillstoreid  in (' . $_SESSION['storeids'] . ') OR storeid in(' . $_SESSION['storeid'] . ') ';
                // $queryString1SR .= 'and  sellbillandrutern.sellbillstoreid  in (' . $_SESSION['storeids'] . ') ';
                $message .= "  مخازن المستخدم ";
            }
        } else {
            $mystordata = $myStoreRecord->load($_SESSION['storeid']);
            $theStore = $_SESSION['storeid'];
            // $queryString1 .= 'and  sellbill.sellbillstoreid  = ' . $_SESSION['storeid'] . ' OR storeid = ' . $_SESSION['storeid'] . ' ';
            // $queryString1R .= 'and  returnsellbill.returnsellbillstoreid  = ' . $_SESSION['storeid'] . ' OR storeid = ' . $_SESSION['storeid'] . ' ';
            // $queryString1SR .= 'and  sellbillandrutern.sellbillstoreid  = ' . $_SESSION['storeid'] . ' ';
            $store = $storeDAO->load($_SESSION['storeid']);
            $message .= "<strong>المخزن : </strong> " . $store->storeName . " ";
        }





        //  else {
        //     $theStore = $user->userstoreid;
        //     //$queryString .= 'and  bills.sellbillstoreid  = ' . $user->userstoreid . ' ';
        //     $queryString1 .= 'and  sellbill.sellbillstoreid  = ' . $user->userstoreid . ' ';
        //     $queryString1R .= 'and  returnsellbill.returnsellbillstoreid  = ' . $user->userstoreid . ' ';
        //     $queryString1SR .= 'and  sellbillandrutern.sellbillstoreid  = ' . $user->userstoreid . ' ';
        //     $store = $storeDAO->load($user->userstoreid);
        //     $message .= "المخزن : " . $store->storeName . " ";
        // }
    }


    ##branch
    if (isset($branchId) && !empty($branchId) && $branchId != -1) {
        if ($user->branchId == 0) {
            $queryString .= 'and  bills.branchid  = ' . $branchId . ' ';
            $queryStringR .= 'and  billsreturn.branchid  = ' . $branchId . ' ';
            //$queryString1 .= 'and  sellbill.sellbilldate  = ' . $branchId . ' ';
            $branch = $branchDAO->load($branchId);
            $message .= "<strong>الفرع : </strong> " . $branch->branchName . " ";
        } else {
            $queryString .= 'and  bills.branchid  = ' . $user->branchId . ' ';
            $queryStringR .= 'and  billsreturn.branchid  = ' . $user->branchId . ' ';
            //$queryString1 .= 'and  sellbill.sellbilldate  = ' . $user->branchId . ' ';
            $branch = $branchDAO->load($user->branchId);
            $message .= "<strong>الفرع : </strong> " . $branch->branchName . " ";
        }
    }
    ##pricetype
    if (isset($pricetype) && $pricetype != -1) {
        $queryString .= 'and  bills.pricetype = ' . $pricetype . ' ';
        $queryStringR .= 'and  bills.pricetype = ' . $pricetype . ' ';
        $queryString1 .= 'and  sellbill.pricetype  = ' . $pricetype . ' ';
        $queryString1R .= 'and  returnsellbill.pricetype  = ' . $pricetype . ' ';
        $queryString1SR .= 'and  sellbillandrutern.pricetype  = ' . $pricetype . ' ';
        switch ($pricetype) {
            case 0:
                $message .= " <strong>نوع السعر : </strong> جملة ";
                break;
            case 1:
                $message .= " <strong>نوع السعر : </strong> تجزئة ";
                break;
            case 2:
                $message .= " <strong>نوع السعر : </strong> نصف جملة ";
                break;
        }
    }

    ##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 .= " <strong>البائع : </strong> " . $seller->employeename . " ";
    }
    ##userid
    if (isset($userid) && !empty($userid) && $userid != -1) {
        $queryString .= 'and  bills.userid  = ' . $userid . ' ';
        $queryStringR .= 'and  billsreturn.userid  = ' . $userid . ' ';
        $queryString1 .= 'and  sellbill.userid  = ' . $userid . ' ';
        $queryString1R .= 'and  returnsellbill.userid  = ' . $userid . ' ';
        $queryString1SR .= 'and  sellbillandrutern.userid  = ' . $userid . ' ';
        $user = $userDAO->load($userid);
        $message .= " <strong>المستخدم : </strong> " . $user->employeename . " ";
    }

    ##userid
    if (isset($tripid) && $tripid > 0) {
        $queryString .= 'and  bills.userid  = -1 ';
        $queryStringR .= 'and  billsreturn.userid  = -1 ';
        $queryString1 .= 'and  sellbill.travelTripId  = ' . $tripid . ' ';
        $queryString1R .= 'and  returnsellbill.userid  = -1 ';
        $queryString1SR .= 'and  sellbillandrutern.userid  = -1 ';
        $user = $userDAO->load($userid);
        $message .= "المستخدم : " . $user->employeename . " ";
    }


    if ($searchtype == 0) {
        ##productId
        if (isset($productId) && !empty($productId) && $productId != -1) {
            $sizeId = 0;
            $colorId = 0;
            $sizeColorStoreDetailId = 0;
            if (strpos($productId, "hasSizeColor") !== false) {
                $productIdComplex = explode('-', str_replace("hasSizeColor", "", $productId));
                $productId = $productIdComplex[0];
                $sizeId = $productIdComplex[1];
                $colorId = $productIdComplex[2];
                //$sizeColorStoreDetailId = $sizeColorStoreDetailEX->getIdByProductStoreSizeAndColorEX($productId, $sellbillstoreId, $sizeId, $colorId);
            }

            $pro = $ProductDAO->load($productId);
            $message .= " <strong>المنتج : </strong> " . $pro->productName . " ";

            if ($isOptic == 2) {
                $productsOfCat = $ProductEX->queryByProductCatIdIn($productId);
                $productId = '0';
                foreach ($productsOfCat as $value) {
                    $productId .= ',' . $value->productId;
                }
            }
            $IDS = $productId;
            //            $queryString .= 'and  billsproducts.productid  in( ' . $productId . ' ) ';
            //            $queryStringR .= 'and  billsreturnproducts.productid  in( ' . $productId . ' ) ';
            //            $queryString1 .= 'and  sellbilldetail.sellbilldetailproductid  in( ' . $productId . ' ) ';
            //            $queryString1R .= 'and  returnsellbilldetail.returnsellbilldetailproductid  in( ' . $productId . ' ) ';
            //            $queryString1SR .= 'and  sellandruternbilldetail.sellbilldetailproductid  in( ' . $productId . ' ) ';
        } elseif (isset($productCatId) && !empty($productCatId) && $productCatId != -1) {
            ##get subcat of cat
            $cat = $productCatDAO->load($productCatId);
            $message .= " <strong>التصنيف : </strong> " . $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;
            }

            //            $queryString .= 'and  billsproducts.productid  in (' . $IDS . ') ';
            //            $queryString1 .= 'and  sellbilldetail.sellbilldetailproductid  in (' . $IDS . ') ';
            //            $queryStringR .= 'and  billsreturnproducts.productid   in (' . $IDS . ') ';
            //            $queryString1R .= 'and  returnsellbilldetail.returnsellbilldetailproductid  in (' . $IDS . ') ';
            //            $queryString1SR .= 'and  sellandruternbilldetail.sellbilldetailproductid  in (' . $IDS . ') ';
        }

        getDataNew($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore, $IDS, $typeId, $servicesAreIncluded);
    } elseif ($searchtype == 1 || $searchtype == 2) {

        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("salesreportview/show.html");

    //$smarty->assign("settlementstoreshow", 1);
} elseif ($do == "consumption") {
    //here the permission check
    include_once("../public/authentication.php");
    $Programsetting = $ProgramsettingDAO->load(1);
    $smarty->assign("Programsetting", $Programsetting);

    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    ##########################################################################
    //prepare search tools
    ##التصنيفات
    $catDataReport = getCategoryChilds();
    $smarty->assign("catDataReport", $catDataReport[1]);

    $user = $userDAO->load($_SESSION['userid']);
    $smarty->assign("user", $user);
    if ($user->userstoreid == 0) {
        $storeData = loadStore();
        $smarty->assign("storeData", $storeData);
    }


    //search
    $datefrom = filter_input(INPUT_POST, 'datefrom');
    $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');
    $isOptic = filter_input(INPUT_POST, 'proIsOptic');
    if (!isset($isOptic) || empty($isOptic)) {
        $isOptic = 0;
    }
    if ($isOptic == 2 && ($searchtype == 1 || $searchtype == 2)) {
        $productCatId = $productId;
    }

    $message = '';
    //
    $queryString = " where 1 "; //bills
    $queryStringR = " where 1 "; //billsreturn
    $queryString1 = " where 1 "; //sellbill
    $queryString1R = " where 1 "; //sellbillreturn
    $queryString1SR = " where 1 "; //sellbillandreturn
    ##
    //    //display the converted time ##reportsPlusHours
    //    $Programsetting = $ProgramsettingDAO->load(1);
    ##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";
        //        }
        $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)) {
        //        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';
        //        }
        $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 . " ";
        }

        //  else {
        //     $theStore = $user->userstoreid;
        //     //$queryString .= 'and  bills.sellbillstoreid  = ' . $user->userstoreid . ' ';
        //     $queryString1 .= 'and  sellbill.sellbillstoreid  = ' . $user->userstoreid . ' ';
        //     $queryString1R .= 'and  returnsellbill.returnsellbillstoreid  = ' . $user->userstoreid . ' ';
        //     $queryString1SR .= 'and  sellbillandrutern.sellbillstoreid  = ' . $user->userstoreid . ' ';
        //     $store = $storeDAO->load($user->userstoreid);
        //     $message .= "المخزن : " . $store->storeName . " ";
        // }
    }
    ##productId
    if (isset($productId) && !empty($productId) && $productId != -1) {
        $sizeId = 0;
        $colorId = 0;
        $sizeColorStoreDetailId = 0;
        if (strpos($productId, "hasSizeColor") !== false) {
            $productIdComplex = explode('-', str_replace("hasSizeColor", "", $productId));
            $productId = $productIdComplex[0];
            $sizeId = $productIdComplex[1];
            $colorId = $productIdComplex[2];
            //$sizeColorStoreDetailId = $sizeColorStoreDetailEX->getIdByProductStoreSizeAndColorEX($productId, $sellbillstoreId, $sizeId, $colorId);
        }

        $pro = $ProductDAO->load($productId);
        $message .= "المنتج : " . $pro->productName . " ";

        if ($isOptic == 2) {
            $productsOfCat = $ProductEX->queryByProductCatIdIn($productId);
            $productId = '0';
            foreach ($productsOfCat as $value) {
                $productId .= ',' . $value->productId;
            }
        }
        $queryString .= 'and  billsproducts.productid  in( ' . $productId . ' ) ';
        $queryStringR .= 'and  billsreturnproducts.productid  in( ' . $productId . ' ) ';
        $queryString1 .= 'and  sellbilldetail.sellbilldetailproductid  in( ' . $productId . ' ) ';
        $queryString1R .= 'and  returnsellbilldetail.returnsellbilldetailproductid  in( ' . $productId . ' ) ';
        $queryString1SR .= 'and  sellandruternbilldetail.sellbilldetailproductid  in( ' . $productId . ' ) ';
    } elseif (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;
        }

        $queryString .= 'and  billsproducts.productid  in (' . $IDS . ') ';
        $queryString1 .= 'and  sellbilldetail.sellbilldetailproductid  in (' . $IDS . ') ';
        $queryStringR .= 'and  billsreturnproducts.productid   in (' . $IDS . ') ';
        $queryString1R .= 'and  returnsellbilldetail.returnsellbilldetailproductid  in (' . $IDS . ') ';
        $queryString1SR .= 'and  sellandruternbilldetail.sellbilldetailproductid  in (' . $IDS . ') ';
    }

    getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore);

    $smarty->assign("salesreport", 1);
    $smarty->assign("message", $message);
    //here the smarty templates
    $smarty->display("salesreportview/consumption.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 $mainCat;
    global $do;

    class productData {

        public $id;
        public $productName;
        public $sellUnitPrice = 0;
        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;

    }

    //* 1.15 "vatValue" include vat to buy price to make profit calculations true like sales includes vat
    //$vatValue = 1 + ($Programsetting->vatValue / 100);
    //vat not applied to all products , get vat from meanbuyprice_withTax from product table
    $vatValue = 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 = '';

    $queryStringStore1 = $queryStringStore1R = $queryStringStore1SR = '';
    if ($theStore) {
        $queryStringStore1 .= ' and sellbilldetail.storeid in(' . $theStore . ') ';
        $queryStringStore1R .= ' and  returnsellbilldetail.storeid in(' . $theStore . ') ';
        $queryStringStore1SR .= ' and  sellandruternbilldetail.storeid   in (' . $theStore . ') ';
    }




    if (($searchtype == 1 || $searchtype == 2) && isset($productCatId) && !empty($productCatId) && $productCatId != -1) {
        $lastLevelCatIDS = array();
        array_push($lastLevelCatIDS, $productCatId);
        getAllSubCat($productCatId, 2); //mode = 2 get last level cats
        $catsIDS = '0';
        foreach ($lastLevelCatIDS as $mycatId) {
            $catsIDS .= ',' . $mycatId;
        }

        $productsOfCat = R::getAll('SELECT productId FROM product WHERE productCatId in (' . $catsIDS . ')');
        $IDSOfProducts = '0';
        foreach ($productsOfCat as $value) {
            $IDSOfProducts .= ',' . $value['productId'];
        }
        ##if no search make it today
        if ($queryString1 == " where 1 ") {
            ##date
            $today = date("Y-m-d");
            $queryString1 .= 'and  sellbill.sellbilldate  = "' . $today . '" ';
            $queryString1R .= 'and  returnsellbill.returnsellbilldate  = "' . $today . '" ';
            $queryString1SR .= 'and  sellbillandrutern.sellbilldate  = "' . $today . '" ';
        }


        ##cat data
        $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . $catsIDS . ') ');
        $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    }


    ##############################################################################
    ## lets go
    ##if no search make it today
    /* if ($queryString1 == " where 1 ") {
      ##date
      $datefrom = $dateto = $today = date("Y-m-d");
      //display the converted time ##reportsPlusHours
      $Programsetting = $ProgramsettingDAO->load(1);
      ##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)));
      $dateto = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($dateto)));
      } else {
      $datefrom = $datefrom . " 00:00:00";
      $dateto = $dateto . ' 23:59:59';
      }

      $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 . '" ';
      //
      $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 . '" ';
      }
      } */

    if($do == "approvedsales"){
        $queryString1 .= ' and sellbill.taxBillNumber > 0 ';
    }

    $sellBillData = R::getAll('SELECT sellbillid,sellbill.sellbillclientid as clientid,sellbill.sellbilltotalbill as parcode,sellbill.sellbillaftertotalbill as note,sellbill.userid
                    ,sellbilldiscount,sellbilldiscounttype,tax
                    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,tax
                    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,tax
                    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 (isset($IDSOfProducts))
            $sqlProducts = ' and sellbilldetail.sellbilldetailproductid in(' . $IDSOfProducts . ') ';
        $sellBillDetailData = R::getAll('SELECT sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber
                ,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,lastbuyprice_withTax,meanbuyprice_withTax,lastbuyprice_withDiscountAndTax
              FROM sellbilldetail
              join productunit on(productunit.productid = sellbilldetail.sellbilldetailproductid and productunit.productunitid=sellbilldetail.productunitid)
              where sellbilldetail.sellbillid in(' . implode(',', $billIDs) . ') ' . $sqlProducts . ' ' . $queryStringStore1 . ''); //-- AND conditions = 0 order by sellbilldetail.sellbilldetailproductid
        $sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'sellbillid');
        $sellBillDataTemp = array();
        list($productIDsArr) = [[]];
        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'];
                $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;
        }
        ##sell price
        $sellUnitPrice = ' ,"" as sellunitprice ';
        switch ($Programsetting->unitPriceAtsalesreport) {
            case 1:
                $sellUnitPrice = ' ,productSellAllPrice as sellunitprice';
                break;
            case 2:
                $sellUnitPrice = ' ,productSellUnitPrice as sellunitprice';
                break;
            case 3:
                $sellUnitPrice = ' ,productSellHalfPrice as sellunitprice';
                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 ' . $sellUnitPrice . '
                            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;
    }

    //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;
    ########################################################################
    ##فاتورة مردود المبيعات
    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 (isset($IDSOfProducts))
            $sqlProducts = ' and returnsellbilldetail.returnsellbilldetailproductid in(' . $IDSOfProducts . ') ';
        $sellBillDetailData = R::getAll('SELECT returnsellbillid,returnsellbilldetailproductid,returnsellbilldetailquantity,returnsellbilldetail.productunitid,returnsellbilldetailtotalprice,productunit.productnumber
                ,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,lastbuyprice_withTax,meanbuyprice_withTax,lastbuyprice_withDiscountAndTax
              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 . ' ' . $queryStringStore1R . ' AND returnsellbilldetail.conditions = 0'); //-- order by returnsellbilldetail.returnsellbilldetailproductid
        $sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'returnsellbillid');
        $sellBillDataTemp = array();
        list($productIDsArr) = [[]];
        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'];
                $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  ' . $sellUnitPrice . '
                    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;
    }

    //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;
    ########################################################################
    ##فاتورة مبيعات و مردود
    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 (isset($IDSOfProducts))
            $sqlProducts = ' and sellandruternbilldetail.sellbilldetailproductid in(' . $IDSOfProducts . ') ';
        $sellBillDetailData = R::getAll('SELECT sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellandruternbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber
                ,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,selltype,lastbuyprice_withTax,meanbuyprice_withTax,lastbuyprice_withDiscountAndTax
              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 . ' ' . $queryStringStore1SR . ' AND conditions = 0'); //-- order by sellandruternbilldetail.sellbilldetailproductid
        $sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'sellbillid');
        $sellBillDataTemp = array();
        list($productIDsArr) = [[]];
        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'];
                $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  ' . $sellUnitPrice . '
                    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;
    }

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



    ########################################################################
    #############finally get net , real cost ,net profit
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $productIdsArrAll = !empty($productIdsArrAll) ? array_unique($productIdsArrAll) : [0];
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');

    ##this is for quantity before total foreach
    $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'];
    }



    if ($searchtype == 0) {
        foreach ($sellBillData as $value) {
            if (in_array($value->sellbilldetailproductid, $existId)) {
                $key = array_search($value->sellbilldetailproductid, $existId);
                $myproduct = $allDataArr[$key];
            } else {
                $key = -1;
                $myproduct = new productData();
                $myproduct->id = $value->sellbilldetailproductid;
                array_push($existId, $value->sellbilldetailproductid);
            }

            $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 ($theDiscount > 0) {
                $myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
            } else {
                $myproduct->soldVal += $value->sellbilldetailtotalprice;// + $theDiscount;
            }
            $theTaxVal = 0;
            if ($value->tax != 0) {
                $theTaxVal = $value->sellbilldetailtotalprice * ($value->tax/100);
                $myproduct->soldVal += round($theTaxVal, 2);
            } 

            $myproduct->buyPrice = $value->storeid;
            $myproduct->sellUnitPrice = (float) $value->sellunitprice;
            ##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);
            }
        }
        foreach ($sellBillDataReturn as $value) {
            if (in_array($value->returnsellbilldetailproductid, $existId)) {
                $key = array_search($value->returnsellbilldetailproductid, $existId);
                $myproduct = $allDataArr[$key];
            } else {
                $key = -1;
                $myproduct = new productData();
                $myproduct->id = $value->returnsellbilldetailproductid;
                array_push($existId, $value->returnsellbilldetailproductid);
            }

            $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 * $value->parcode / 100;
            }
            //$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;
                $theDiscount = round($theDiscount, 2);
                $flag = 0;
            } else {
                $theDiscount = $dicount;
                $theDiscount = round($theDiscount, 2);
            }
            ################
            if ($theDiscount > 0) {
                $myproduct->returnVal += $value->returnsellbilldetailtotalprice - $theDiscount;
            } else {
                $myproduct->returnVal += $value->returnsellbilldetailtotalprice;// + $theDiscount;
            }
            $theTaxVal = 0;
            if ($value->tax != 0) {
                $theTaxVal = $value->returnsellbilldetailtotalprice * ($value->tax/100);
                $myproduct->returnVal += round($theTaxVal, 2);
            } 

            $myproduct->buyPrice = $value->storeid;
            $myproduct->sellUnitPrice = (float) $value->sellunitprice;
            ##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);
            }
        }
        foreach ($sellBillDataSellAndReturn as $value) {
            if (in_array($value->sellbilldetailproductid, $existId)) {
                $key = array_search($value->sellbilldetailproductid, $existId);
                $myproduct = $allDataArr[$key];
            } else {
                $key = -1;
                $myproduct = new productData();
                $myproduct->id = $value->sellbilldetailproductid;
                array_push($existId, $value->sellbilldetailproductid);
            }

            $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;
            $myproduct->sellUnitPrice = (float) $value->sellunitprice;
            ##############
            ######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);
            }
            $theTaxVal = 0;
            if ($value->tax != 0) {
                $theTaxVal = $value->sellbilldetailtotalprice * ($value->tax/100);
                $myproduct->soldVal += round($theTaxVal, 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
        $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');
        foreach ($allDataArr as $data) {
            //$found_index = searchForId($data->productCatId, $productCatDataArr, 'productCatId');
            if ($productCatDataArr[$data->productCatId] !== null) {
                $data->productCatName = $productCatDataArr[$data->productCatId]['productCatName'];
            }
            $data->netNo = $data->soldNo - $data->returnNo;
            $data->netVal = $data->soldVal - $data->returnVal;
            //                ##realcost
            //                $buyDatail = $ProductDAO->load($data->id); ## i may use ->loadext() to get quantity
            //                $data->realCost = $data->netNo * $buyDatail->productBuyPrice;
            $data->netProfit = $data->netVal - $data->realCost;
            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;
        }
    } elseif ($searchtype == 1 || $searchtype == 2) {
        ########################################################################
        ##start calculation &summation
        ##unique product cat
        ##فاتورة المبيعات
        foreach ($sellBillData as $value) {
            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'];
                array_push($existId, $value->productCatId);
            }
            #########get quantity بالقطعة
            $quantity = $value->sellbilldetailquantity;
            $productId = $value->sellbilldetailproductid;
            $productunitId = $value->productunitid;
            $productnumber = $value->productnumber;
            $finalquantity = $quantity * $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) {
            //                $billNoOfProduct = $value->billnoofproducts;
            //                $flag = 1;
            //            }

            if ($dicount != 0) {
                $theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
                $theDiscount = round($theDiscount, 2);
            } else {
                $theDiscount = $dicount;
                $theDiscount = round($theDiscount, 2);
            }
            ################
            if ($theDiscount > 0) {
                $myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
            } else {
                $myproduct->soldVal += $value->sellbilldetailtotalprice;// + $theDiscount;
            }
            $theTaxVal = 0;
            if ($value->tax != 0) {
                $theTaxVal = $value->sellbilldetailtotalprice * ($value->tax/100);
                $myproduct->soldVal += round($theTaxVal, 2);
            } 
            ################
            //$myproduct->buyPrice = $value->storeid;
            //$myproduct->sellUnitPrice = (float) $value->sellunitprice;
            //$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
            //$myproduct->realCost += $finalquantity * $value->storeid;
            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);
            }
        }
        ##فاتورة مردود المبيعات
        foreach ($sellBillDataReturn as $value) {
            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'];
                array_push($existId, $value->productCatId);
            }
            #########get quantity بالقطعة
            $quantity = $value->returnsellbilldetailquantity;
            $productId = $value->returnsellbilldetailproductid;
            $productunitId = $value->productunitid;
            $productnumber = $value->productnumber;
            $finalquantity = $quantity * $productnumber;
            ##############
            $myproduct->returnNo += $finalquantity; //العدد بالقطعة
            ######get discount
            $dicount = 0;
            $billNoOfProduct = 0;
            $dicount = $value->returnsellbilldiscount;
            if ($value->returnsellbilldiscounttype == 2) {
                $dicount = $value->returnsellbilldiscount * $value->parcode / 100;
            }
            //$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;
                $theDiscount = round($theDiscount, 2);
                $flag = 0;
            } else {
                $theDiscount = $dicount;
                $theDiscount = round($theDiscount, 2);
            }
            ################
            if ($theDiscount > 0) {
                $myproduct->returnVal += $value->returnsellbilldetailtotalprice - $theDiscount;
            } else {
                $myproduct->returnVal += $value->returnsellbilldetailtotalprice;// + $theDiscount;
            }
            $theTaxVal = 0;
            if ($value->tax != 0) {
                $theTaxVal = $value->returnsellbilldetailtotalprice * ($value->tax/100);
                $myproduct->soldVal += round($theTaxVal, 2);
            } 
//            $myproduct->buyPrice = $value->storeid;
//            $myproduct->sellUnitPrice = (float) $value->sellunitprice;
            //                        $myproduct->realCost -= $finalquantity * $value->storeid;
            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);
            }
        }
        ##فاتورة مبيعات و مردود
        foreach ($sellBillDataSellAndReturn as $value) {
            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'];
                array_push($existId, $value->productCatId);
            }
            #########get quantity بالقطعة
            $quantity = $value->sellbilldetailquantity;
            $productId = $value->sellbilldetailproductid;
            $productunitId = $value->productunitid;
            $productnumber = $value->productnumber;
            $finalquantity = $quantity * $productnumber;
            ##############
            ######get discount
            $dicount = 0;
            $billNoOfProduct = 0;
            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);
            }
            $theTaxVal = 0;
            if ($value->tax != 0) {
                $theTaxVal = $value->sellbilldetailtotalprice * ($value->tax/100);
                $myproduct->soldVal += round($theTaxVal, 2);
            } 
            ################
            if ($value->selltype == 0) {
                $myproduct->soldNo += $finalquantity; //العدد بالقطعة
                $myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
                //                            $myproduct->realCost += $finalquantity * $value->storeid;
                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;
                //                            $myproduct->realCost -= $finalquantity * $value->storeid;
                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
        $quantityAndLimitDataArr = R::getAll('SELECT product.productCatId,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 product.productCatId');
        $quantityAndLimitDataArr = customArrayIndexOne($quantityAndLimitDataArr, 'productCatId');
        foreach ($allDataArr as $data) {
            if ($quantityAndLimitDataArr[$data->id] !== null) {
                $data->currentQuantity = $quantityAndLimitDataArr[$data->id]['sumProductQuantity'];
            }
            $data->netNo = $data->soldNo - $data->returnNo;
            $data->netVal = $data->soldVal - $data->returnVal;
            ##realcost
            $totalsObj->realCost += $data->realCost;

            $data->netProfit = round(($data->netVal - $data->realCost), 2);
            ##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->netProfit += $data->netProfit;
            $totalsObj->currentQuantity += $data->currentQuantity;
        }

        if ($searchtype == 2) {
            $allDataArrTemp = array();
            $existId = array();
            foreach ($allDataArr as $data) {
                ##get main parent which parentid is 0   weather it is the same cat or bigger parent
                $mainCat = new stdClass();
                getMainParentId($data->id);
                ##gather in one arr
                if (in_array($mainCat->productCatId, $existId)) {
                    $key = array_search($mainCat->productCatId, $existId);
                    $mycatRowinArr = $allDataArrTemp[$key];
                    $mycatRowinArr->soldNo += $data->soldNo;
                    $mycatRowinArr->soldVal += $data->soldVal;
                    $mycatRowinArr->returnNo += $data->returnNo;
                    $mycatRowinArr->returnVal += $data->returnVal;
                    $mycatRowinArr->netNo += $data->netNo;
                    $mycatRowinArr->netVal += $data->netVal;
                    $mycatRowinArr->realCost += $data->realCost;
                    $mycatRowinArr->netProfit += $data->netProfit;
                    $mycatRowinArr->currentQuantity += $data->currentQuantity;
                    $mycatRowinArr->limitamount += $data->limitamount;
                } else {
                    $key = -1;
                    $mycatRowinArr = $data;
                    $mycatRowinArr->id = $mainCat->productCatId;
                    $mycatRowinArr->productName = $mainCat->productCatName;


                    array_push($existId, $data->id);
                    array_push($allDataArrTemp, $mycatRowinArr);
                }
            }

            $allDataArr = $allDataArrTemp;
        }
    }

    $smarty->assign("totalsObj", $totalsObj);
    $smarty->assign("resultsCount", $resultsCount);


    $smarty->assign("resultsCount", count($allDataArr));
    $smarty->assign("allDataArr", $allDataArr);

    $clientId = $_POST['clientId'];
    $datefrom = $_POST['datefrom'];
    $dateto = $_POST['dateto'];
    if (isset($clientId) && !empty($clientId) && $clientId != -1) {
        $datefrom = date('Y-m-d', strtotime($datefrom)) . " 00:00:00";
        $dateto = date('Y-m-d', strtotime($dateto)) . " 23:59:59";
        $clientDebtChangeData = R::getAll('SELECT clientdebtchange.* FROM clientdebtchange
                                        WHERE clientid = ' . $clientId . ' and (tablename="clientPayedDeptController.php" OR tablename = "clientPayedDeptSellBillsController.php") and clientdebtchangedate >= "' . $datefrom . '" and clientdebtchangedate <= "' . $dateto . '"');
    }
    $smarty->assign('clientDebtChangeData',$clientDebtChangeData);
}

function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore) {
    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 $smarty;
    global $lastLevelCatIDS;
    global $catsIDS;

    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;

    }

    if ($queryString1 == " where 1 ") {
        return;
    }
    $existId = array();
    $allDataArr = array();
    $totalsObj = new productData();
    $resultsCount = 0;
    $flag = 0;
    if ($searchtype == 0) {
        ##if no search make it today
        /* if ($queryString1 == " where 1 ") {
          ##date
          $datefrom = $dateto = $today = date("Y-m-d");
          //display the converted time ##reportsPlusHours
          $Programsetting = $ProgramsettingDAO->load(1);
          ##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)));
          $dateto = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($dateto)));
          } else {
          $datefrom = $datefrom . " 00:00:00";
          $dateto = $dateto . ' 23:59:59';
          }

          $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 . '" ';
          //
          $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 . '" ';
          }
          } */
        //        $billsData = $billsProductsEX->queryAllGeneral($queryString);
        //        $billsDataReturn = $billsReturnProductsEX->queryAllGeneral($queryStringR);
        $sellBillData = $sellbilldetailEX->queryAllGeneral($queryString1);
        $sellBillDataReturn = $returnSellBillDetailEX->queryAllGeneral($queryString1R);
        $sellBillDataSellAndReturn = $sellAndRuternBillDetailEX->queryAllGeneral($queryString1SR);
        $resultsCount = count($billsData) + count($billsDataReturn) + count($sellBillData) + count($sellBillDataReturn) + count($sellBillDataSellAndReturn);
        $smarty->assign("resultsCount", $resultsCount);
        if ($resultsCount > 0) {
            ##unique products
            /* ##مبيعات البصريات
              foreach ($billsData as $value) {
              if (in_array($value->productid, $existId)) {
              $key = array_search($value->productid, $existId);
              $myproduct = $allDataArr[$key];
              } else {
              $key = -1;
              $myproduct = new productData();
              $myproduct->id = $value->productid;
              array_push($existId, $value->productid);
              }

              $myproduct->productName = $value->productName;
              $myproduct->soldNo += $value->productno; //no directly no product unit used
              ///emaaaaaaaan
              $myproduct->productCatName = $value->productCatName;

              ##get discount
              $theBill = $billsDAO->load($value->billid);

              $dicount = 0;
              $billNoOfProduct = 0;
              if ($theBill->card == 1) {
              if ($theBill->paymentnetworkid == 4) {
              //مدى
              $madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
              if ($madaData->totalCarry < 5000)
              $dicount = (7 * $madaData->totalCarry) / 1000;
              else
              $dicount = 40;
              } else {
              $dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
              }
              }
              if ($dicount != 0) {
              $billpecies = $billsProductsEX->queryBillNoOfPecies($value->billid);
              $billNoOfProduct = $billpecies->deleted;
              $flag = 1;
              }
              $dicount += ($theBill->productstotalprice - $theBill->finalnetbillvalue); //خصم النسبة و خصم الكشف
              if ($flag == 1) {
              $theDiscount = ($value->productno * $dicount) / $billNoOfProduct;
              $theDiscount = round($theDiscount, 2);
              $flag = 0;
              } else {
              $theDiscount = $dicount;
              $theDiscount = round($theDiscount, 2);
              }
              ################


              $myproduct->soldVal += $value->producttotalprice - $theDiscount;
              $myproduct->buyPrice = $value->deleted;
              ##realcost
              switch ($chosenProductPrice) {
              case 0:
              case 2:
              $myproduct->realCost += $value->productno * $value->lastbuyprice;
              break;
              case 1:
              $myproduct->realCost += $value->productno * $value->meanbuyprice;
              break;
              }

              if ($key == -1) {
              array_push($allDataArr, $myproduct);
              }
              }
              ##مرتجعات البصريات
              foreach ($billsDataReturn as $value) {
              if (in_array($value->productid, $existId)) {
              $key = array_search($value->productid, $existId);
              $myproduct = $allDataArr[$key];
              } else {
              $key = -1;
              $myproduct = new productData();
              $myproduct->id = $value->productid;
              array_push($existId, $value->productid);
              }

              $myproduct->productName = $value->productName;
              $myproduct->returnNo += $value->productno; //no directly no product unit used
              //emaaaaaaaan
              $myproduct->productCatName = $value->productCatName;
              ##get discount
              $theBill = $billsDAO->load($value->billproductid);

              $dicount = 0;
              $billNoOfProduct = 0;
              if ($theBill->card == 1) {
              if ($theBill->paymentnetworkid == 4) {
              //مدى
              $madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
              if ($madaData->totalCarry < 5000)
              $dicount = (7 * $madaData->totalCarry) / 1000;
              else
              $dicount = 40;
              } else {
              $dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
              }
              }
              if ($dicount != 0) {
              $billpecies = $billsProductsEX->queryBillNoOfPecies($value->billproductid);
              $billNoOfProduct = $billpecies->deleted;
              $flag = 1;
              }
              $dicount += ($theBill->productstotalprice - $theBill->finalnetbillvalue); //خصم النسبة و خصم الكشف
              if ($flag == 1) {
              $theDiscount = ($value->productno * $dicount) / $billNoOfProduct;
              $theDiscount = round($theDiscount, 2);
              $flag = 0;
              } else {
              $theDiscount = $dicount;
              $theDiscount = round($theDiscount, 2);
              }
              ################
              $myproduct->returnVal += $value->producttotalprice + $theDiscount;
              $myproduct->buyPrice = $value->deleted;
              ##realcost
              switch ($chosenProductPrice) {
              case 0:
              case 2:
              $myproduct->realCost -= $value->productno * $value->lastbuyprice;
              break;
              case 1:
              $myproduct->realCost -= $value->productno * $value->meanbuyprice;
              break;
              }
              if ($key == -1) {
              array_push($allDataArr, $myproduct);
              }
              } */
            ##فاتورة المبيعات
            foreach ($sellBillData as $value) {
                if (in_array($value->sellbilldetailproductid, $existId)) {
                    $key = array_search($value->sellbilldetailproductid, $existId);
                    $myproduct = $allDataArr[$key];
                } else {
                    $key = -1;
                    $myproduct = new productData();
                    $myproduct->id = $value->sellbilldetailproductid;
                    array_push($existId, $value->sellbilldetailproductid);
                }

                $myproduct->productName = $value->productName;
                //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->soldNo += $finalquantity; //العدد بالقطعة
                ######get discount
                $dicount = 0;
                $billNoOfProduct = 0;
                $dicount = $value->parcode - $value->note;

                if ($dicount != 0) {
                    $billpecies = $sellbilldetailEX->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 ($theDiscount > 0) {
                    $myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
                } else {
                    $myproduct->soldVal += $value->sellbilldetailtotalprice;// + $theDiscount;
                }

                $myproduct->buyPrice = $value->storeid;
                $myproduct->sellUnitPrice = (float) $value->sellunitprice;
                ##realcost
                switch ($chosenProductPrice) {
                    case 0:
                        $myproduct->realCost += $finalquantity * $value->buyprice;
                        break;
                    case 2:
                        $myproduct->realCost += $finalquantity * $value->lastbuyprice;
                        break;
                    case 1:
                        $myproduct->realCost += $finalquantity * $value->meanbuyprice;
                        break;
                    case 3:
                        $myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount;
                        break;
                    case 4:
                        $myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount;
                        break;
                    case 5:
                        $myproduct->realCost += $finalquantity * $value->generalPrice;
                        break;
                }
                if ($key == -1) {
                    array_push($allDataArr, $myproduct);
                }
            }
            ##فاتورة مردود المبيعات
            foreach ($sellBillDataReturn as $value) {
                if (in_array($value->returnsellbilldetailproductid, $existId)) {
                    $key = array_search($value->returnsellbilldetailproductid, $existId);
                    $myproduct = $allDataArr[$key];
                } else {
                    $key = -1;
                    $myproduct = new productData();
                    $myproduct->id = $value->returnsellbilldetailproductid;
                    array_push($existId, $value->returnsellbilldetailproductid);
                }

                $myproduct->productName = $value->productName;
                //emaaaaaaaan
                $myproduct->productCatName = $value->productCatName;
                #########get quantity بالقطعة
                $quantity = $value->returnsellbilldetailquantity;
                $productId = $value->returnsellbilldetailproductid;
                $productunitId = $value->productunitid;
                $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
                $productnumber = $productunitData->productnumber;
                $finalquantity = $quantity * $productnumber;
                ##############
                $myproduct->returnNo += $finalquantity; //العدد بالقطعة
                ######get discount
                $dicount = 0;
                $billNoOfProduct = 0;
                $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;
                    $theDiscount = round($theDiscount, 2);
                    $flag = 0;
                } else {
                    $theDiscount = $dicount;
                    $theDiscount = round($theDiscount, 2);
                }
                ################
                if ($theDiscount > 0) {
                    $myproduct->returnVal += $value->returnsellbilldetailtotalprice - $theDiscount;
                } else {
                    $myproduct->returnVal += $value->returnsellbilldetailtotalprice;// + $theDiscount;
                }

                $myproduct->buyPrice = $value->storeid;
                $myproduct->sellUnitPrice = (float) $value->sellunitprice;
                ##realcost
                switch ($chosenProductPrice) {
                    case 0:
                        $myproduct->realCost -= $finalquantity * $value->buyprice;
                        break;
                    case 2:
                        $myproduct->realCost -= $finalquantity * $value->lastbuyprice;
                        break;
                    case 1:
                        $myproduct->realCost -= $finalquantity * $value->meanbuyprice;
                        break;
                    case 3:
                        $myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount;
                        break;
                    case 4:
                        $myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount;
                        break;
                    case 5:
                        $myproduct->realCost -= $finalquantity * $value->generalPrice;
                        break;
                }
                if ($key == -1) {
                    array_push($allDataArr, $myproduct);
                }
            }
            ##فاتورة مبيعات و مردود
            foreach ($sellBillDataSellAndReturn as $value) {
                if (in_array($value->sellbilldetailproductid, $existId)) {
                    $key = array_search($value->sellbilldetailproductid, $existId);
                    $myproduct = $allDataArr[$key];
                } else {
                    $key = -1;
                    $myproduct = new productData();
                    $myproduct->id = $value->sellbilldetailproductid;
                    array_push($existId, $value->sellbilldetailproductid);
                }

                $myproduct->productName = $value->productName;
                //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;
                $myproduct->sellUnitPrice = (float) $value->sellunitprice;
                ##############
                ######get discount
                $dicount = 0;
                $billNoOfProduct = 0;
                $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;
                            break;
                        case 2:
                            $myproduct->realCost += $finalquantity * $value->lastbuyprice;
                            break;
                        case 1:
                            $myproduct->realCost += $finalquantity * $value->meanbuyprice;
                            break;
                        case 3:
                            $myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount;
                            break;
                        case 4:
                            $myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount;
                            break;
                        case 5:
                            $myproduct->realCost += $finalquantity * $value->generalPrice;
                            break;
                    }
                } else {
                    $myproduct->returnNo += $finalquantity; //العدد بالقطعة
                    $myproduct->returnVal += $value->sellbilldetailtotalprice + $theDiscount;
                    ##realcost
                    switch ($chosenProductPrice) {
                        case 0:
                            $myproduct->realCost -= $finalquantity * $value->buyprice;
                            break;
                        case 2:
                            $myproduct->realCost -= $finalquantity * $value->lastbuyprice;
                            break;
                        case 1:
                            $myproduct->realCost -= $finalquantity * $value->meanbuyprice;
                            break;
                        case 3:
                            $myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount;
                            break;
                        case 4:
                            $myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount;
                            break;
                        case 5:
                            $myproduct->realCost -= $finalquantity * $value->generalPrice;
                            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'];
                ;
            }
            foreach ($allDataArr as $data) {
                $data->netNo = $data->soldNo - $data->returnNo;
                $data->netVal = $data->soldVal - $data->returnVal;
                //                ##realcost
                //                $buyDatail = $ProductDAO->load($data->id); ## i may use ->loadext() to get quantity
                //                $data->realCost = $data->netNo * $buyDatail->productBuyPrice;
                $data->netProfit = $data->netVal - $data->realCost;
                if ($buyDatail->isOptic == 2) {
                    $catName = $ProductEX->loadProductCatNameOnly($data->id);
                    $data->productName = $data->productName . "/" . $catName;
                }

                ##currentQuantity
                $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;
            }
            $smarty->assign("totalsObj", $totalsObj);
        }
    } elseif ($searchtype == 1) {
        if (isset($productCatId) && !empty($productCatId) && $productCatId != -1) {
            $lastLevelCatIDS = array();
            array_push($lastLevelCatIDS, $productCatId);
            getAllSubCat($productCatId, 2); //mode = 2 get last level cats
            foreach ($lastLevelCatIDS as $mycatId) {
                $mycat = $productCatDAO->load($mycatId);
                $isOpticCondition = "";
                if ($mycat->isOptic == 2) {
                    $isOpticCondition = " and isOptic = 2 ";
                } else {
                    $isOpticCondition = " and isOptic != 2 ";
                }
                ##get subcat of cat
                $catsIDS = '' . $mycatId;
                getAllSubCat($mycatId, 1); //mode = 1 get all sub cats
                $productsOfCat = $ProductEX->queryByProductCatIdIn($catsIDS, $isOpticCondition);
                $IDS = '0';
                foreach ($productsOfCat as $value) {
                    $IDS .= ',' . $value->productId;
                }
                $queryStringProduct = 'and  billsproducts.productid  in (' . $IDS . ') ';
                $queryString1Product = 'and  sellbilldetail.sellbilldetailproductid  in (' . $IDS . ') ';
                $queryStringRProduct = 'and  billsreturnproducts.productid   in (' . $IDS . ') ';
                $queryString1RProduct = 'and  returnsellbilldetail.returnsellbilldetailproductid  in (' . $IDS . ') ';
                $queryString1SRProduct = 'and  sellandruternbilldetail.sellbilldetailproductid  in (' . $IDS . ') ';


                ##if no search make it today
                if ($queryString1 == " where 1 ") {
                    ##date
                    $today = date("Y-m-d");
                    $queryString .= 'and  bills.billdate  = "' . $today . '" ';
                    $queryStringR .= 'and  billsreturn.`date`  = "' . $today . '" ';
                    $queryString1 .= 'and  sellbill.sellbilldate  = "' . $today . '" ';
                    $queryString1R .= 'and  returnsellbill.returnsellbilldate  = "' . $today . '" ';
                    $queryString1SR .= 'and  sellbillandrutern.sellbilldate  = "' . $today . '" ';
                }
                ##now like search type == 0
                //                $billsData = $billsProductsEX->queryAllGeneral($queryString . $queryStringProduct);
                //                $billsDataReturn = $billsReturnProductsEX->queryAllGeneral($queryStringR . $queryStringRProduct);
                $sellBillData = $sellbilldetailEX->queryAllGeneral($queryString1 . $queryString1Product);
                $sellBillDataReturn = $returnSellBillDetailEX->queryAllGeneral($queryString1R . $queryString1RProduct);
                $sellBillDataSellAndReturn = $sellAndRuternBillDetailEX->queryAllGeneral($queryString1SR . $queryString1SRProduct);
                $thisResultsCount = count($billsData) + count($billsDataReturn) + count($sellBillData) + count($sellBillDataReturn) + count($sellBillDataSellAndReturn);
                $resultsCount += $thisResultsCount;

                if ($thisResultsCount > 0) {

                    $mycatObj = $productCatDAO->load($mycatId);
                    $myproduct = new productData();
                    $myproduct->id = $mycatObj->productCatId;
                    $myproduct->productName = $mycatObj->productCatName;

                    ##unique product cat
                    /* ##مبيعات البصريات
                      foreach ($billsData as $value) {
                      ##get discount
                      $theBill = $billsDAO->load($value->billid);
                      $dicount = 0;
                      $billNoOfProduct = 0;
                      if ($theBill->card == 1) {
                      if ($theBill->paymentnetworkid == 4) {
                      //مدى
                      $madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
                      if ($madaData->totalCarry < 5000)
                      $dicount = (7 * $madaData->totalCarry) / 1000;
                      else
                      $dicount = 40;
                      } else {
                      $dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
                      }
                      }
                      if ($dicount != 0) {
                      $billpecies = $billsProductsEX->queryBillNoOfPecies($value->billid);
                      $billNoOfProduct = $billpecies->deleted;
                      $flag = 1;
                      }
                      if ($flag == 1) {
                      $theDiscount = ($value->productno * $dicount) / $billNoOfProduct;
                      $theDiscount = round($theDiscount, 2);
                      $flag = 0;
                      } else {
                      $theDiscount = $dicount;
                      $theDiscount = round($theDiscount, 2);
                      }
                      ################

                      $myproduct->soldNo += $value->productno; //no directly no product unit used
                      $myproduct->soldVal += $value->producttotalprice - $theDiscount;

                      $myproduct->realCost = $myproduct->realCost + ($value->productno * $value->deleted);
                      }
                      ##مرتجعات البصريات
                      foreach ($billsDataReturn as $value) {
                      $myproduct->returnNo += $value->productno; //no directly no product unit used
                      $myproduct->returnVal += $value->producttotalprice;

                      $myproduct->realCost = $myproduct->realCost - ($value->productno * $value->deleted);
                      } */
                    ##فاتورة المبيعات
                    foreach ($sellBillData as $value) {
                        #########get quantity بالقطعة
                        $quantity = $value->sellbilldetailquantity;
                        $productId = $value->sellbilldetailproductid;
                        $productunitId = $value->productunitid;
                        $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
                        $productnumber = $productunitData->productnumber;
                        $finalquantity = $quantity * $productnumber;
                        ##############
                        $myproduct->soldNo += $finalquantity; //العدد بالقطعة
                        ######get discount
                        $dicount = 0;
                        $billNoOfProduct = 0;
                        $dicount = $value->parcode - $value->note;

                        if ($dicount != 0) {
                            $billpecies = $sellbilldetailEX->queryBillNoOfPecies($value->sellbillid);
                            $billNoOfProduct = $billpecies->note;
                            $flag = 1;
                        }

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

                        //                        $myproduct->realCost += $finalquantity * $value->storeid;
                        switch ($chosenProductPrice) {
                            case 0:
                                $myproduct->realCost += $finalquantity * $value->buyprice;
                                break;
                            case 2:
                                $myproduct->realCost += $finalquantity * $value->lastbuyprice;
                                break;
                            case 1:
                                $myproduct->realCost += $finalquantity * $value->meanbuyprice;
                                break;
                            case 3:
                                $myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount;
                                break;
                            case 4:
                                $myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount;
                                break;
                            case 5:
                                $myproduct->realCost += $finalquantity * $value->generalPrice;
                                break;
                        }
                    }
                    ##فاتورة مردود المبيعات
                    foreach ($sellBillDataReturn as $value) {
                        #########get quantity بالقطعة
                        $quantity = $value->returnsellbilldetailquantity;
                        $productId = $value->returnsellbilldetailproductid;
                        $productunitId = $value->productunitid;
                        $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
                        $productnumber = $productunitData->productnumber;
                        $finalquantity = $quantity * $productnumber;
                        ##############
                        $myproduct->returnNo += $finalquantity; //العدد بالقطعة
                        ######get discount
                        $dicount = 0;
                        $billNoOfProduct = 0;
                        $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 = round($theDiscount, 2);
                            $flag = 0;
                        } else {
                            $theDiscount = $dicount;
                            $theDiscount = round($theDiscount, 2);
                        }

                        ################
                        $myproduct->returnVal += $value->returnsellbilldetailtotalprice + $theDiscount;

                        //                        $myproduct->realCost -= $finalquantity * $value->storeid;
                        switch ($chosenProductPrice) {
                            case 0:
                                $myproduct->realCost -= $finalquantity * $value->buyprice;
                                break;
                            case 2:
                                $myproduct->realCost -= $finalquantity * $value->lastbuyprice;
                                break;
                            case 1:
                                $myproduct->realCost -= $finalquantity * $value->meanbuyprice;
                                break;
                            case 3:
                                $myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount;
                                break;
                            case 4:
                                $myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount;
                                break;
                            case 5:
                                $myproduct->realCost -= $finalquantity * $value->generalPrice;
                                break;
                        }
                    }
                    ##فاتورة مبيعات و مردود
                    foreach ($sellBillDataSellAndReturn as $value) {
                        #########get quantity بالقطعة
                        $quantity = $value->sellbilldetailquantity;
                        $productId = $value->sellbilldetailproductid;
                        $productunitId = $value->productunitid;
                        $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
                        $productnumber = $productunitData->productnumber;
                        $finalquantity = $quantity * $productnumber;
                        ##############
                        ######get discount
                        $dicount = 0;
                        $billNoOfProduct = 0;
                        $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->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;
                            //                            $myproduct->realCost += $finalquantity * $value->storeid;
                            switch ($chosenProductPrice) {
                                case 0:
                                    $myproduct->realCost += $finalquantity * $value->buyprice;
                                    break;
                                case 2:
                                    $myproduct->realCost += $finalquantity * $value->lastbuyprice;
                                    break;
                                case 1:
                                    $myproduct->realCost += $finalquantity * $value->meanbuyprice;
                                    break;
                                case 3:
                                    $myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount;
                                    break;
                                case 4:
                                    $myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount;
                                    break;
                                case 5:
                                    $myproduct->realCost += $finalquantity * $value->generalPrice;
                                    break;
                            }
                        } else {
                            $myproduct->returnNo += $finalquantity; //العدد بالقطعة
                            $myproduct->returnVal += $value->sellbilldetailtotalprice + $theDiscount;
                            //                            $myproduct->realCost -= $finalquantity * $value->storeid;
                            switch ($chosenProductPrice) {
                                case 0:
                                    $myproduct->realCost -= $finalquantity * $value->buyprice;
                                    break;
                                case 2:
                                    $myproduct->realCost -= $finalquantity * $value->lastbuyprice;
                                    break;
                                case 1:
                                    $myproduct->realCost -= $finalquantity * $value->meanbuyprice;
                                    break;
                                case 3:
                                    $myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount;
                                    break;
                                case 4:
                                    $myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount;
                                    break;
                                case 5:
                                    $myproduct->realCost -= $finalquantity * $value->generalPrice;
                                    break;
                            }
                        }
                    }
                    array_push($allDataArr, $myproduct);
                }
            }
            #############finally get net , real cost ,net profit
            foreach ($allDataArr as $data) {

                $data->netNo = $data->soldNo - $data->returnNo;
                $data->netVal = $data->soldVal - $data->returnVal;
                ##realcost
                $totalsObj->realCost += $data->realCost;

                $data->netProfit = $data->netVal - $data->realCost;

                ##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->netProfit += $data->netProfit;
            }

            $smarty->assign("totalsObj", $totalsObj);
        }
        ##productId
        //he can not choose product with out product cat
        /* elseif (isset($productId) && !empty($productId)) {

          } */
    }

    $smarty->assign("resultsCount", $resultsCount);
    $smarty->assign("allDataArr", $allDataArr);
}

function getMainParentId($catid) {
    global $mainCat;
    $mainCat = (object) R::getRow('select * from productcat where productCatId=' . $catid);
    if ($mainCat->productCatParent == 0) {
        return;
    } else {
        getMainParentId($mainCat->productCatParent);
    }
}

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 CURL_IT2($data_arr, $url) {
    ##preparing session data
    //$projectURL = 'http://' . $_SERVER['HTTP_HOST'] . explode('controllers', $_SERVER['REQUEST_URI'])[0] . 'controllers/';
    $url = 'http://' . $_SERVER['HTTP_HOST'] . explode('controllers', $_SERVER['REQUEST_URI'])[0] . 'controllers/' . $url;

    ##appending necessary data
    $data_arr['curlpost'] = '1';
    $data_arr['sessionlist'] = json_encode($_SESSION);


    // set post fields
    //    $post = [
    //        'data_arr' => json_encode($data_arr), //encode it as it is array
    //    ];
    $post = $data_arr;
    $ch = curl_init($url);
    //
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $post); //
    //Disable CURLOPT_SSL_VERIFYHOST and CURLOPT_SSL_VERIFYPEER by
    //setting them to false.
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    //curl_setopt($ch, CURLOPT_TIMEOUT, 10); //
    // execute!
    $response = curl_exec($ch);
    if ($response === false) {
        //echo 'Error : ' . curl_error($ch);
    }

    // close the connection, release resources used
    curl_close($ch);
    // do anything you want with your response
    //var_dump($response);
    //print_r($response);
    return $response;
}
