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

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

//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientExt = new ClientMySqlExtDAO();
//
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
//
$clientDAO = new ClientMySqlDAO();
$storeDAO = new StoreMySqlDAO();
$branchDAO = new BranchMySqlDAO();


$youtubeLink = new YoutubeLink();
$youtubeLinkDAO = new YoutubeLinkMySqlDAO();
$youtubeLinkEX = new YoutubeLinkMySqlExtDAO();
//
$myUserGroupRecord = new UsergroupMySqlDAO();
$userGroupId = $_SESSION['usergroupid'];
$loadData = $myUserGroupRecord->load($userGroupId);
$smarty->assign("loadData", $loadData);



$lastLevelCatIDS = array();
$catsIDS = "";
//check and use the condition that suite this action
if (empty($do)) {
    //here the permission check
    include_once("../public/authentication.php");
    $Programsetting = $ProgramsettingDAO->load(1);
    $smarty->assign("Programsetting", $Programsetting);

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

    ##########################################################################
    //prepare search tools
    ##التصنيفات
    $catDataReport = getCategoryChilds();
    $smarty->assign("catDataReport", $catDataReport[1]);
    ##العملاء
    $clients = getClientData();
    $smarty->assign("clients", $clients);

    $user = $userDAO->load($_SESSION['userid']);
    $smarty->assign("user", $user);
    if ($user->userstoreid == 0) {
        $storeData = loadStore();
        $smarty->assign("storeData", $storeData);
    }
    if ($user->branchId == 0) {
        $branchData = loadBranch();
        $smarty->assign("branchData", $branchData);
    }
    $sellerData = loadseller();
    $smarty->assign("sellerData", $sellerData);
    $userData = loadusers();
    $smarty->assign("userData", $userData);
    ###########################################################################
    //search
    $datefrom = filter_input(INPUT_POST, 'datefrom');
    $dateto = filter_input(INPUT_POST, 'dateto');
    $clientId = filter_input(INPUT_POST, 'clientId');

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

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

    $isOptic = filter_input(INPUT_POST, 'proIsOptic');
    if (!isset($isOptic) || empty($isOptic)) {
        $isOptic = 0;
    }
    if ($isOptic == 2 && $searchtype == 1) {
        $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 .= "الى تاريخ : " . $datefrom . " ";
    }
    ##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 .= "العميل : " . $client->clientname . " ";
    }

    ##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 {
            $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 .= "الفرع : " . $store->storeName . " ";
        } 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 .= "الفرع : " . $store->storeName . " ";
        }
    }
    ##pricetype
    if (isset($pricetype) && $pricetype != -1) {
        $queryString .= 'and  bills.pricetype = ' . $pricetype . ' ';
        $queryStringR .= 'and  bills.pricetype = ' . $pricetype . ' ';
        $queryString1 .= 'and  sellbilldetail.pricetype  = ' . $pricetype . ' ';
        $queryString1R .= 'and  returnsellbilldetail.pricetype  = ' . $pricetype . ' ';
        $queryString1SR .= 'and  sellandruternbilldetail.pricetype  = ' . $pricetype . ' ';
        switch ($pricetype) {
            case 0:
                $message .= "نوع السعر : جملة ";
                break;
            case 1:
                $message .= "نوع السعر : تجزئة ";
                break;
            case 2:
                $message .= "نوع السعر : نصف جملة ";
                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 .= "البائع : " . $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 .= "المستخدم : " . $user->employeename . " ";
    }


    if ($searchtype == 0) {
        ##productId
        if (isset($productId) && !empty($productId) && $productId != -1) {
            $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);
    } elseif ($searchtype == 1) {

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



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

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

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

function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $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 clientData {

        public $id;
        public $clientName;
        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;

    }

    $existId = array();
    $allDataArr = array();
    $totalsObj = new clientData();
    $resultsCount = 0;
    $flag = 0;

    ##if no search make it today
    if ($queryString == " 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->clientid, $existId)) {
                $key = array_search($value->clientid, $existId);
                $myclient = $allDataArr[$key];
            } else {
                $key = -1;
                $myclient = new clientData();
                $myclient->id = $value->clientid;
                array_push($existId, $value->clientid);
            }

            $myclient->clientName = $value->clientname;
            $myclient->soldNo += $value->productno; //no directly no product unit used
            ##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);
            }
            ################


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

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

            $myclient->clientName = $value->clientname;
            $myclient->returnNo += $value->productno; //no directly no product unit used
            ##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);
            }
            ################
            $myclient->returnVal += $value->producttotalprice + $theDiscount;
            $myclient->buyPrice = $value->deleted;
            ##realcost
            switch ($chosenProductPrice) {
                case 0:
                case 2:
                    $myclient->realCost -= $value->productno * $value->lastbuyprice;
                    break;
                case 1:
                    $myclient->realCost -= $value->productno * $value->meanbuyprice;
                    break;
            }
            if ($key == -1) {
                array_push($allDataArr, $myclient);
            }
        }
        ##فاتورة المبيعات
        foreach ($sellBillData as $value) {
            if (in_array($value->clientid, $existId)) {
                $key = array_search($value->clientid, $existId);
                $myclient = $allDataArr[$key];
            } else {
                $key = -1;
                $myclient = new clientData();
                $myclient->id = $value->clientid;
                array_push($existId, $value->clientid);
            }

            $myclient->clientName = $value->clientname;
            #########get quantity بالقطعة
            $quantity = $value->sellbilldetailquantity;
            $productId = $value->sellbilldetailproductid;
            $productunitId = $value->productunitid;
            $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
            $productnumber = $productunitData->productnumber;
            $finalquantity = $quantity * $productnumber;
            ##############
            $myclient->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);
            }
            ################
            $myclient->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
            $myclient->buyPrice = $value->storeid;
            ##realcost
            switch ($chosenProductPrice) {
                case 0:
                    $myclient->realCost += $finalquantity * $value->buyprice;
                    break;
                case 2:
                    $myclient->realCost += $finalquantity * $value->lastbuyprice;
                    break;
                case 1:
                    $myclient->realCost += $finalquantity * $value->meanbuyprice;
                    break;
                case 3:
                    $myclient->realCost += $value->productno * $value->lastbuyprice_withDiscount;
                    break;
                case 4:
                    $myclient->realCost += $value->productno * $value->meanbuyprice_withDiscount;
                    break;
                case 5:
                    $myclient->realCost += $value->productno * $value->generalPrice;
                    break;
                case 6:
                    $myclient->realCost += $value->productno * $value->lastbuyprice_withTax;
                    break;
                case 7:
                    $myclient->realCost += $value->productno * $value->meanbuyprice_withTax;
                    break;
            }
            if ($key == -1) {
                array_push($allDataArr, $myclient);
            }
        }
        ##فاتورة مردود المبيعات
        foreach ($sellBillDataReturn as $value) {
            if (in_array($value->clientid, $existId)) {
                $key = array_search($value->clientid, $existId);
                $myclient = $allDataArr[$key];
            } else {
                $key = -1;
                $myclient = new clientData();
                $myclient->id = $value->clientid;
                array_push($existId, $value->clientid);
            }

            $myclient->clientName = $value->clientname;
            #########get quantity بالقطعة
            $quantity = $value->returnsellbilldetailquantity;
            $productId = $value->returnsellbilldetailproductid;
            $productunitId = $value->productunitid;
            $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
            $productnumber = $productunitData->productnumber;
            $finalquantity = $quantity * $productnumber;
            ##############
            $myclient->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);
            }
            ################
            $myclient->returnVal += $value->returnsellbilldetailtotalprice + $theDiscount;
            $myclient->buyPrice = $value->storeid;
            ##realcost
            switch ($chosenProductPrice) {
                case 0:
                    $myclient->realCost -= $finalquantity * $value->buyprice;
                    break;
                case 2:
                    $myclient->realCost -= $finalquantity * $value->lastbuyprice;
                    break;
                case 1:
                    $myclient->realCost -= $finalquantity * $value->meanbuyprice;
                    break;
                case 3:
                    $myclient->realCost -= $finalquantity * $value->lastbuyprice_withDiscount;
                    break;
                case 4:
                    $myclient->realCost -= $finalquantity * $value->meanbuyprice_withDiscount;
                    break;
                case 5:
                    $myclient->realCost -= $finalquantity * $value->generalPrice;
                    break;
                case 6:
                    $myclient->realCost += $finalquantity * $value->lastbuyprice_withTax;
                    break;
                case 7:
                    $myclient->realCost += $finalquantity * $value->meanbuyprice_withTax;
                    break;
            }
            if ($key == -1) {
                array_push($allDataArr, $myclient);
            }
        }
        ##فاتورة مبيعات و مردود
        foreach ($sellBillDataSellAndReturn as $value) {
            if (in_array($value->clientid, $existId)) {
                $key = array_search($value->clientid, $existId);
                $myclient = $allDataArr[$key];
            } else {
                $key = -1;
                $myclient = new clientData();
                $myclient->id = $value->clientid;
                array_push($existId, $value->clientid);
            }

            $myclient->clientName = $value->clientname;
            #########get quantity بالقطعة
            $quantity = $value->sellbilldetailquantity;
            $productId = $value->sellbilldetailproductid;
            $productunitId = $value->productunitid;
            $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
            $productnumber = $productunitData->productnumber;
            $finalquantity = $quantity * $productnumber;
            $myclient->buyPrice = $value->storeid;
            ##############
            ######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) {
                $myclient->soldNo += $finalquantity; //العدد بالقطعة
                $myclient->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
                ##realcost
                switch ($chosenProductPrice) {
                    case 0:
                        $myclient->realCost += $finalquantity * $value->buyprice;
                        break;
                    case 2:
                        $myclient->realCost += $finalquantity * $value->lastbuyprice;
                        break;
                    case 1:
                        $myclient->realCost += $finalquantity * $value->meanbuyprice;
                        break;
                    case 3:
                        $myclient->realCost += $finalquantity * $value->lastbuyprice_withDiscount;
                        break;
                    case 4:
                        $myclient->realCost += $finalquantity * $value->meanbuyprice_withDiscount;
                        break;
                    case 5:
                        $myclient->realCost += $finalquantity * $value->generalPrice;
                        break;
                    case 6:
                        $myclient->realCost += $finalquantity * $value->lastbuyprice_withTax;
                        break;
                    case 7:
                        $myclient->realCost += $finalquantity * $value->meanbuyprice_withTax;
                        break;
                }
            } else {
                $myclient->returnNo += $finalquantity; //العدد بالقطعة
                $myclient->returnVal += $value->sellbilldetailtotalprice + $theDiscount;
                ##realcost
                switch ($chosenProductPrice) {
                    case 0:
                        $myclient->realCost -= $finalquantity * $value->buyprice;
                        break;
                    case 2:
                        $myclient->realCost -= $finalquantity * $value->lastbuyprice;
                        break;
                    case 1:
                        $myclient->realCost -= $finalquantity * $value->meanbuyprice;
                        break;
                    case 3:
                        $myclient->realCost -= $finalquantity * $value->lastbuyprice_withDiscount;
                        break;
                    case 4:
                        $myclient->realCost -= $finalquantity * $value->meanbuyprice_withDiscount;
                        break;
                    case 5:
                        $myclient->realCost -= $finalquantity * $value->generalPrice;
                        break;
                    case 6:
                        $myclient->realCost += $finalquantity * $value->lastbuyprice_withTax;
                        break;
                    case 7:
                        $myclient->realCost += $finalquantity * $value->meanbuyprice_withTax;
                        break;
                }
            }
            if ($key == -1) {
                array_push($allDataArr, $myclient);
            }
        }
        #############finally get net , real cost ,net profit
        $myQuery = "";
        if (isset($theStore) && !empty($theStore) && $theStore != -1) {
            $myQuery = " and storedetail.storeid = " . $theStore;
        }
        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->clientName = $data->clientName . "/" . $catName;
            }

            ##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;
        }
        $smarty->assign("totalsObj", $totalsObj);
    }

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

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

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

?>