<?php

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

//global varable
global $showoutside;

//to check if the page from .htacess
//$showoutside = $_GET['sn'];
// get the config file
include_once("../public/config.php");

//here the db files that include in the file
include("../public/include_dao.php");
//Sellbill
require_once('../models/dao/SellbillDAO.class.php');
require_once('../models/dto/Sellbill.class.php');
require_once('../models/mysql/SellbillMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbillMySqlExtDAO.class.php');
//Returnsellbill
require_once('../models/dao/ReturnsellbillDAO.class.php');
require_once('../models/dto/Returnsellbill.class.php');
require_once('../models/mysql/ReturnsellbillMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnsellbillMySqlExtDAO.class.php');
//Sellbillandrutern
require_once('../models/dao/SellbillandruternDAO.class.php');
require_once('../models/dto/Sellbillandrutern.class.php');
require_once('../models/mysql/SellbillandruternMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbillandruternMySqlExtDAO.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');
//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');
//Productunit
require_once('../models/dao/ProductunitDAO.class.php');
require_once('../models/dto/Productunit.class.php');
require_once('../models/mysql/ProductunitMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductunitMySqlExtDAO.class.php');
//Buybill
require_once('../models/dao/BuybillDAO.class.php');
require_once('../models/dto/Buybill.class.php');
require_once('../models/mysql/BuybillMySqlDAO.class.php');
require_once('../models/mysql/ext/BuybillMySqlExtDAO.class.php');
//Buybilldetail
require_once('../models/dao/BuybilldetailDAO.class.php');
require_once('../models/dto/Buybilldetail.class.php');
require_once('../models/mysql/BuybilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/BuybilldetailMySqlExtDAO.class.php');
//Buyandruternbill
require_once('../models/dao/BuyandruternbillDAO.class.php');
require_once('../models/dto/Buyandruternbill.class.php');
require_once('../models/mysql/BuyandruternbillMySqlDAO.class.php');
require_once('../models/mysql/ext/BuyandruternbillMySqlExtDAO.class.php');
//Buyandruternbilldetail
require_once('../models/dao/BuyandruternbilldetailDAO.class.php');
require_once('../models/dto/Buyandruternbilldetail.class.php');
require_once('../models/mysql/BuyandruternbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/BuyandruternbilldetailMySqlExtDAO.class.php');
//Returnbuybill
require_once('../models/dao/ReturnbuybillDAO.class.php');
require_once('../models/dto/Returnbuybill.class.php');
require_once('../models/mysql/ReturnbuybillMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnbuybillMySqlExtDAO.class.php');
//Returnbuybilldetail
require_once('../models/dao/ReturnbuybilldetailDAO.class.php');
require_once('../models/dto/Returnbuybilldetail.class.php');
require_once('../models/mysql/ReturnbuybilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnbuybilldetailMySqlExtDAO.class.php');
//Productcat
require_once('../models/dao/ProductcatDAO.class.php');
require_once('../models/dto/Productcat.class.php');
require_once('../models/mysql/ProductcatMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductcatMySqlExtDAO.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');

require_once('../models/dao/StorereportDAO.class.php');
require_once('../models/dto/Storereport.class.php');
require_once('../models/mysql/StorereportMySqlDAO.class.php');
require_once('../models/mysql/ext/StorereportMySqlExtDAO.class.php');

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

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


//get the do the action
$do = $_GET['do'];

/* ======================
  Controller Name :- profitreportCTRL تقرير أرباح منتج

  OPERTATION in Controller

  1- search with product name or date
  ======================== */

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

//here goes the instances and general variables
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
//Sellbill
$mySellbillEx = new SellbillMySqlExtDAO();
$mySellbillRecord = new SellbillMySqlDAO();
//Returnsellbill
$myReturnsellbillEx = new ReturnsellbillMySqlExtDAO();
$myReturnsellbillRecord = new ReturnsellbillMySqlDAO();
//Sellbillandrutern
$mySellbillandruternRecord = new SellbillandruternMySqlDAO();
$mySellbillandruternEx = new SellbillandruternMySqlExtDAO();
//Sellbilldetail
$mySellbilldetailEx = new SellbilldetailMySqlExtDAO();
//Returnsellbilldetail
$myReturnsellbilldetailEx = new ReturnsellbilldetailMySqlExtDAO();
//Sellandruternbilldetail
$mySellandruternbilldetailEx = new SellandruternbilldetailMySqlExtDAO();
//Product
$myProductRecord = new ProductMySqlDAO();
$myProductEx = new ProductMySqlExtDAO();
//Productunit
$myProductunitEx = new ProductunitMySqlExtDAO();
$myProductunitRecord = new ProductunitMySqlDAO();
//Buybill
$myBuybillRecord = new BuybillMySqlDAO();
//Buybilldetail
$myBuybilldetailEx = new BuybilldetailMySqlExtDAO();
//Buyandruternbill
$myBuyandruternbillRecord = new BuyandruternbillMySqlDAO();
//Buyandruternbilldetail
$myBuyandruternbilldetailEx = new BuyandruternbilldetailMySqlExtDAO();
//Returnbuybill
$myReturnbuybillRecord = new ReturnbuybillMySqlDAO();
//Returnbuybilldetail
$myReturnbuybilldetailEx = new ReturnbuybilldetailMySqlExtDAO();
//Productcat
$myProductcatRecord = new ProductcatMySqlDAO();
$myProductcatEx = new ProductcatMySqlExtDAO();
//bills products
$mybillsproduct = new Billsproduct();
$mybillsproductDAO = new BillsproductsMySqlDAO();
$mybillsproductEx = new BillsproductsMySqlExtDAO();

$mybillsReturnproduct = new Billsreturnproduct();
$mybillsReturnproductDAO = new BillsreturnproductsMySqlDAO();
$mybillsReturnproductEx = new BillsreturnproductsMySqlExtDAO();

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

$Storereport = new Storereport();
$StorereportDAO = new StorereportMySqlDAO();
$StorereportEX = new StorereportMySqlExtDAO();

$Store = new Store();
$StoreDAO = new StoreMySqlDAO();
$StoreEX = new StoreMySqlExtDAO();

//check and use the condetion that suite this action
if ($do == "show" || empty($do)) {
    include_once("../public/authentication.php");

    $Programsettingdata = $ProgramsettingDAO->load(1);
    $smarty->assign("Programsettingdata", $Programsettingdata);
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $productCatId = $_REQUEST['productCatId'];
    $storeId = $_REQUEST['storeId'];

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



    // $storeData = loadStore();
    // $smarty->assign("storeData", $storeData);

    $alldata = getProductCats();
    if (!empty($productCatId)) {
        $productCatData = $myProductcatRecord->load($productCatId);
        $message .= "تقرير  مبيعات تصنيف <h4>" . $productCatData->productCatName;
    }
    if (!empty($startDate)) {
        $message .= "</h4> والتاريخ من " . $startDate;
    }
    if (!empty($endDate)) {
        $message .= " إلى " . $endDate;
    }

    $query = '';
    if ($startDate != '' && $endDate != '') {
        $query .= ' and  storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '" ';
    }

    $storeQuery = '';
    if ($storeId != '' && $storeId != '-1' && $storeId != '0') {
        $query .= ' and  storereport.storeid = ' . $storeId . ' ';
        $storeQuery = ' and storedetail.storeid = ' . $storeId . ' ';
    } else if ($_SESSION['searchinonestore'] == 0) {
        if ($_SESSION['storeids'] != 0) {
            $query .= ' and  storereport.storeid in (' . $_SESSION['storeids'] . ') ';
            $storeQuery = ' and storedetail.storeid in (' . $_SESSION['storeids'] . ') ';
        }
    } else {
        $query .= ' and  storereport.storeid = ' . $_SESSION['storeid'] . ' ';
        $storeQuery = ' and storedetail.storeid = ' . $_SESSION['storeid'] . ' ';
    }

    $smarty->assign("message", $message);

    if (!empty($productCatId)) {

        // getProductInBillByDateAndProductId($startDate, $endDate, $productCatId);
        //showByStoreAndProductAndDate($productId, $storeId, $startDate, $endDate, $order, $bure);
        //$productData = $myProductRecord->queryByProductCatId($productCatId);
        $productData = $myProductEx->queryByProductCatIdEX2($productCatId, $storeQuery);
        //print_r('<pre>');
        //$productId = 1;
        //showByStoreAndProductAndDate($productId, $dateQuery);
        $productsArr = array();
        foreach ($productData as $val) {

            $productId = $val->productId;

            $arrContainer = showByStoreAndProductAndDate($productId, $query, $val->productquantity);
            if (!empty($arrContainer)) {
                array_push($productsArr, $arrContainer);
            }
        }
        //print_r($productsArr);
        //print_r('<pre>');
    }
    $smarty->assign('productsArr', $productsArr);


    $youtubes = $youtubeLinkDAO->queryAll();
    $smarty->assign("youtubes", $youtubes);
    //here the smarty templates
    $smarty->display("profitproductview/sallesProductCat.html");
    $smarty->assign("settlementstoreshow", 1);
}
//here the global templates
$smarty->display("footer.html");

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

function showByStoreAndProductAndDate($productid, $query, $productquantity)
{
    global $StorereportEX;
    global $smarty;

    $productidArr = array();

    $storemovementData = $StorereportEX->queryWithProductAndStoreAndDateandorderMo($productid, $query);
    $plusQuantity = $minQuantity = 0;

    foreach ($storemovementData as $val) {
        if ($val->tablename == "sellbillController.php") {
            $res = R::count("sellbill", "sellbillid =" . $val->storereportmodelid . " and conditions = 0");
        } elseif ($val->tablename == "buyBillController.php") {
            $res = R::count("buybill", "buybillid =" . $val->storereportmodelid . " and conditions = 0");
        }
        if ($res <= 0)
            $val->productquantity = 0;
        
        $resProductName = $val->productName;
        $resProductQuantity = $val->productquantity;
        $storereporttype = $val->storereporttype; //# 0 min , 1 plus .


        $resProductId = $val->productid;
        if (in_array($resProductId, $productidArr)) {

            if ($storereporttype == 1) {
                $minQuantity += $resProductQuantity;
            } else {
                $plusQuantity += $resProductQuantity;
            }
        } else {

            if ($storereporttype == 1) {
                $minQuantity = $resProductQuantity;
            } else {
                $plusQuantity = $resProductQuantity;
            }
            array_push($productidArr, $resProductId);
        }
    }

    if (!empty($resProductName)) {

        $arrContainer = array(
            'productName' => $resProductName,
            'plusQuantity' => $plusQuantity,
            'minQuantity' => $minQuantity,
            'diff' => $plusQuantity - $minQuantity,
            'productquantity' => $productquantity, //of search store,or all stores if no store choosen ,it is recieved not calculated in this function
        );
    }


    return $arrContainer;
    //    print_r('<pre>');
    //    print_r($arrContainer);
    //    print_r('<pre>');
    //    print_r('<pre>');
    //    print_r($storemovementData);
    //    print_r('<pre>');
    //print_r('<br>$productid = ' . $productid);
    //
    //$smarty->assign('storereportData', $storemovementData);
}

function getProductInBillByDateAndProductId($startDate, $endDate, $productCatId)
{
    global $mySellbilldetailEx;
    global $myProductRecord;
    global $StorereportEX;
    global $StoreEX;
    global $StoreDAO;


    global $smarty;
    $allstor = $StoreDAO->queryByConditions(0);
    $smarty->assign("allstor", $allstor);


    $productData = $myProductRecord->queryByProductCatId($productCatId);


    $all_data = array();
    $i = 1;
    foreach ($allstor as $myallstor) {

        foreach ($productData as $product) {
            $productId = $product->productId;
            $storeId = $myallstor->storeId;

            $queryString = '';
            if (!empty($productId)) {
                $queryString .= ' AND productid=' . $productId . '';
            }
            if (!empty($startDate)) {
                $queryString .= ' AND storereportdate >= "' . $startDate . '"';
            }
            if (!empty($storeId)) {
                $queryString .= ' AND storeid = ' . $storeId . '';
            }
            $storreportdata = $StorereportEX->queryAlllastdealNew($queryString);
            $productafter = $storreportdata->productafter + 0;
            $smarty->assign("productafter" . $i, $productafter);

            $smarty->assign("productData" . $i, $productData);

            //sellbill
            $sellPriceData = getTotalSellPriceByDateAndProductId($startDate, $endDate, $productId, $storeId);
            $sellPricePulsData = getTotalAditionalSellPriceByDateAndProductId($startDate, $endDate, $productId, $storeId);
            $billsData = getTotalbills($startDate, $endDate, $productId, $storeId);
            $totalsell = $sellPriceData[1] + $sellPricePulsData[1] + $billsData;
            $smarty->assign("totalsell" . $i, $totalsell);




            //returnsellbill
            $returnSellPriceData = getTotalReturnSellPriceByDateAndProductId($startDate, $endDate, $productId, $storeId);
            $returnSellPricePulsData = getTotalAditionalReturnSellPriceByDateAndProductId($startDate, $endDate, $productId, $storeId);
            $billsReturnData = getTotalbillsReturn($startDate, $endDate, $productId, $storeId);

            $totalreturnsell = $returnSellPricePulsData[1] + $returnSellPriceData[1] + $billsReturnData;
            $smarty->assign("totalreturnsell" . $i, $totalreturnsell);

            $selldif = $totalsell - $totalreturnsell;
            $smarty->assign("selldif" . $i, $selldif);


            //buybill
            $buybilldata = getTotalBuyPriceByDateAndProductId($startDate, $endDate, $productId, $storeId);


            $totalbuy = $buybilldata[0];
            $totalreturnbuy = $buybilldata[1];
            $buydif = $totalbuy - $totalreturnbuy;
            $smarty->assign("buydif" . $i, $buydif);
            $smarty->assign("totalbuy" . $i, $totalbuy);
            $smarty->assign("totalreturnbuy" . $i, $totalreturnbuy);


            $final = ($buydif + $productafter) - $selldif;
            $smarty->assign("final" . $i, $final);

            $arr = array(
                'storeId' => $myallstor->storeId,
                'productName' => $product->productName,
                'totalsell' => $totalsell,
                'totalreturnsell' => $totalreturnsell,
                'selldif' => $selldif
            );
            array_push($all_data, $arr);
        }

        $i++;
    }

    $smarty->assign("all_data", $all_data);
}

//select all store data
function loadStore()
{
    global $StoreDAO;

    //load all store data
    $storeData = $StoreDAO->queryByConditions(0);
    return $storeData;
}

function getTotalbills($startDate, $endDate, $ProductIdselected, $storid)
{

    global $Storereport;
    global $StorereportDAO;
    global $StorereportEX;
    global $mybillsproduct;
    global $mybillsproductDAO;
    global $mybillsproductEx;
    global $myProductunitEx;


    $quantity = 0;
    $queryString = '';
    if (!empty($ProductIdselected)) {
        $queryString .= ' AND  billsproducts.productid=' . $ProductIdselected . '';
    }
    if (!empty($startDate)) {
        $queryString .= ' AND storereport.storereportdate >= "' . $startDate . '"';
    }
    if (!empty($endDate)) {
        $queryString .= ' AND storereport.storereportdate <= "' . $endDate . '"';
    }
    if (!empty($storid)) {
        $queryString .= ' AND (storereport.storeid = ' . $storid . ')';
    }

    $billsData = $mybillsproductEx->queryStoreAndBillsProducts($queryString);

    foreach ($billsData as $billsDa) {
        $quantity = $billsDa->productno;
        $productunitId = $billsDa->productid;
        $productnumber = $myProductunitEx->getProductNumber($productunitId);

        $quantityUnit += $quantity;
    }

    return $quantityUnit;
}

function getTotalbillsReturn($startDate, $endDate, $ProductIdselected, $storid)
{

    global $mybillsReturnproductEx;
    global $myProductunitEx;
    $quantity = 0;

    $queryString = '';
    if (!empty($ProductIdselected)) {
        $queryString .= ' AND  billsreturnproducts.productid=' . $ProductIdselected . '';
    }
    if (!empty($startDate)) {
        $queryString .= ' AND storereport.storereportdate >= "' . $startDate . '"';
    }
    if (!empty($endDate)) {
        $queryString .= ' AND storereport.storereportdate <= "' . $endDate . '"';
    }
    if (!empty($storid)) {
        $queryString .= ' AND (storereport.storeid = ' . $storid . ')';
    }

    $billsReturnData = $mybillsReturnproductEx->queryStoreAndBillsReturnProducts($queryString);

    foreach ($billsReturnData as $billRetDa) {
        $quantity = $billRetDa->productno;
        $productunitId = $billRetDa->productid;
        $productnumber = $myProductunitEx->getProductNumber($productunitId);

        $quantityUnit1 += ($quantity * $productnumber);
    }
    return $quantityUnit1;
}

// show the form by date
function getTotalSellPriceByDateAndProductId($startDate, $endDate, $ProductIdselected, $storid)
{
    global $mySellbilldetailEx;
    global $myProductunitRecord;
    global $mySellbillRecord;
    global $myProductRecord;
    global $myProductunitEx;

    $sellPrice = 0;
    $quantityUnit = 0;
    $finalallbuyprice = 0;




    $queryString = '';
    if (!empty($ProductIdselected)) {
        $queryString .= ' AND sellbilldetailproductid=' . $ProductIdselected . '';
    }
    if (!empty($startDate)) {
        $queryString .= ' AND sellbill.sellbilldate >= "' . $startDate . '"';
    }
    if (!empty($endDate)) {
        $queryString .= ' AND sellbill.sellbilldate <= "' . $endDate . '"';
    }
    if (!empty($storid)) {
        $queryString .= ' AND (sellbilldetail.storeid = ' . $storid . ' or sellbill.sellbillstoreid = ' . $storid . ')';
    }

    $sellbilldetailData = $mySellbilldetailEx->queryWithDateAndConditionsAndProductIdExtandstorNew($queryString);

    foreach ($sellbilldetailData as $sellbilldetail) {
        $quantity = $sellbilldetail->sellbilldetailquantity;
        $price = $sellbilldetail->sellbilldetailprice;
        $totalPrice = $sellbilldetail->sellbilldetailtotalprice;
        $discountValue = $sellbilldetail->discountvalue;
        $productunitId = $sellbilldetail->productunitid;
        $sellbillTotalBill = $sellbilldetail->sellbilltotalbill;
        $sellbillDiscount = $sellbilldetail->sellbilldiscount;
        $sellbillDiscountType = $sellbilldetail->sellbilldiscounttype;
        $buyprice = $sellbilldetail->buyprice;


        //خصم نسبه
        if ($sellbillDiscountType == 2) {
            $sellbillDiscount = ($sellbillDiscount / 100) * $sellbillTotalBill;
        }




        $totalPriceBeforeDiscount = $price * $quantity;

        $productnumber = $myProductunitEx->getProductNumber($productunitId);

        $quantityUnit += ($quantity * $productnumber);


        $allbuyprice = $quantity * $buyprice;

        //first Step قيمة خصم المنتج على الوحدة
        //$firstStep=$discountValue;
        //second Stepقيمة المنتج من الخصم على المنتج
        //$secondStep=$totalPrice / $sellbillTotalBill;
        //third Step قيمة الخصم للمنتجات
        //$thirdStep=$secondStep * $sellbillDiscount;
        $thirdStep = ($totalPrice / $sellbillTotalBill) * $sellbillDiscount;

        //print_r('totalPrice'.$totalPrice.'<br>');
        //print_r('discountValue'.$discountValue.'<br>');
        //print_r('quantity'.$quantity.'<br>');
        //print_r('totalPriceBeforeDiscount'.$totalPriceBeforeDiscount.'<br>');
        //fourth Step  قيمة خصم الفاتورة للوحدة
        //$fourthStep=$thirdStep;

        $sellPriceForOneProduct = $totalPriceBeforeDiscount - (($discountValue * $quantity) + $sellbillDiscount);
        $allbuyprice = $quantity * $buyprice;
        $finalallbuyprice += $allbuyprice;
        $sellPrice += $sellPriceForOneProduct;
        //	print_r('sellPriceForOneProduct'.$sellPriceForOneProduct.'<br>');
    }
    return array($sellPrice, $quantityUnit, $finalallbuyprice);
}

function getTotalReturnSellPriceByDateAndProductId($startDate, $endDate, $ProductIdselected, $storeId)
{
    global $myReturnsellbilldetailEx;
    global $myReturnsellbillRecord;
    global $myProductunitRecord;
    global $myProductRecord;
    global $myProductunitEx;

    $ReturnSellPrice = 0;
    $quantityUnit = 0;
    $finalallbuyprice = 0;



    $queryString = '';
    if (!empty($ProductIdselected)) {
        $queryString .= ' AND returnsellbilldetailproductid=' . $ProductIdselected . '';
    }
    if (!empty($startDate)) {
        $queryString .= ' AND returnsellbill.returnsellbilldate >= "' . $startDate . '"';
    }
    if (!empty($endDate)) {
        $queryString .= ' AND returnsellbill.returnsellbilldate <= "' . $endDate . '"';
    }
    if (!empty($storeId)) {
        $queryString .= ' and (returnsellbilldetail.storeid = ' . $storeId . ' or returnsellbill.returnsellbillstoreid = ' . $storeId . ') ';
    }
    $returnsellbilldetailData = $myReturnsellbilldetailEx->queryWithDateAndConditionsAndProductIdExtandstoridNew($queryString);

    foreach ($returnsellbilldetailData as $returnsellbilldetail) {


        $quantity = $returnsellbilldetail->returnsellbilldetailquantity;
        $price = $returnsellbilldetail->returnsellbilldetailprice;
        $totalPrice = $returnsellbilldetail->returnsellbilldetailtotalprice;
        $discountValue = $returnsellbilldetail->discountvalue;
        $productunitId = $returnsellbilldetail->productunitid;
        $sellbillTotalBill = $returnsellbilldetail->returnsellbilltotalbill;
        $sellbillDiscount = $returnsellbilldetail->returnsellbilldiscount;
        $sellbillDiscountType = $returnsellbilldetail->returnsellbilldiscounttype;
        $buyprice = $returnsellbilldetail->buyprice;

        if ($sellbillDiscountType == 2) {
            $sellbillDiscount = ($sellbillDiscount / 100) * $sellbillTotalBill;
        }

        $totalPriceBeforeDiscount = $price * $quantity;

        $productnumber = $myProductunitEx->getProductNumber($productunitId);

        $quantityUnit += ($quantity * $productnumber);

        //third Step قيمة الخصم للمنتجات
        $thirdStep = ($totalPrice / $sellbillTotalBill) * $sellbillDiscount;


        $ReturnSellPriceForOneProduct = $totalPriceBeforeDiscount - (($discountValue * $quantity) + $sellbillDiscount);

        //print_r($ReturnSellPriceForOneProduct.'xxx');
        $ReturnSellPrice += $ReturnSellPriceForOneProduct;
        $allbuyprice = $quantity * $buyprice;
        $finalallbuyprice += $allbuyprice;
    }
    return array($ReturnSellPrice, $quantityUnit, $finalallbuyprice);
}

// show the form by date
function getTotalAditionalSellPriceByDateAndProductId($startDate, $endDate, $ProductIdselected, $storid)
{
    global $mySellandruternbilldetailEx;
    global $mySellbillandruternRecord;
    global $myProductunitRecord;
    global $myProductRecord;
    global $myProductunitEx;

    $sellPrice = 0;
    $quantityUnit = 0;
    $finalallbuyprice = 0;

    $queryString = '';
    if (!empty($ProductIdselected)) {
        $queryString .= ' AND sellandruternbilldetail.sellbilldetailproductid=' . $ProductIdselected . '';
    }
    if (!empty($startDate)) {
        $queryString .= ' AND sellbillandrutern.sellbilldate >= "' . $startDate . '"';
    }
    if (!empty($endDate)) {
        $queryString .= ' AND sellbillandrutern.sellbilldate <= "' . $endDate . '"';
    }
    if (!empty($storid)) {
        $queryString .= ' and (sellandruternbilldetail.storeid = ' . $storid . ' or sellbillandrutern.sellbillstoreid = ' . $storid . ') ';
    }


    $sellandreturndetailData = $mySellandruternbilldetailEx->queryWithDateAndConditionsTypeAndProductIdExtandstorNew(0, $queryString);

    foreach ($sellandreturndetailData as $sellandreturndetail) {
        $quantity = $sellandreturndetail->sellbilldetailquantity;
        $price = $sellandreturndetail->sellbilldetailprice;
        $totalPrice = $sellandreturndetail->sellbilldetailtotalprice;
        $discountValue = $sellandreturndetail->discountvalue;
        $productunitId = $sellandreturndetail->productunitid;
        $sellbillTotalBill = $sellandreturndetail->sellbillprice;
        $sellbillDiscount = $sellandreturndetail->sellbilldiscount; //محتاجة تتغير
        $sellbillDiscountType = $sellandreturndetail->sellbilldiscounttype;
        $buyprice = $sellandreturndetail->buyprice;

        if ($sellbillDiscountType == 2) {
            $sellbillDiscount = ($sellbillDiscount / 100) * $sellbillTotalBill;
        }

        $totalPriceBeforeDiscount = $price * $quantity;

        $productnumber = $myProductunitEx->getProductNumber($productunitId);

        $quantityUnit += ($quantity * $productnumber);

        //third Step قيمة الخصم للمنتجات
        $thirdStep = ($totalPrice / $sellbillTotalBill) * $sellbillDiscount;

        $sellPriceForOneProduct = $totalPriceBeforeDiscount - (($discountValue * $quantity) + $sellbillDiscount);
        $sellPrice += $sellPriceForOneProduct;

        $allbuyprice = $quantity * $buyprice;
        $finalallbuyprice += $allbuyprice;
    }
    return array($sellPrice, $quantityUnit, $finalallbuyprice);
}

// show the form by date
function getTotalAditionalReturnSellPriceByDateAndProductId($startDate, $endDate, $ProductIdselected, $storid)
{
    global $mySellandruternbilldetailEx;
    global $mySellbillandruternRecord;
    global $myProductunitRecord;
    global $myProductRecord;
    global $myProductunitEx;

    $ReturnSellPrice = 0;
    $quantityUnit = 0;
    $finalallbuyprice = 0;

    $queryString = '';
    if (!empty($ProductIdselected)) {
        $queryString .= ' AND sellandruternbilldetail.sellbilldetailproductid=' . $ProductIdselected . '';
    }
    if (!empty($startDate)) {
        $queryString .= ' AND sellbillandrutern.sellbilldate >= "' . $startDate . '"';
    }
    if (!empty($endDate)) {
        $queryString .= ' AND sellbillandrutern.sellbilldate <= "' . $endDate . '"';
    }
    if (!empty($storid)) {
        $queryString .= ' and (sellandruternbilldetail.storeid = ' . $storid . ' or sellbillandrutern.sellbillstoreid = ' . $storid . ') ';
    }

    $sellandreturndetailData = $mySellandruternbilldetailEx->queryWithDateAndConditionsTypeAndProductIdExtandstorNew(1, $queryString);

    foreach ($sellandreturndetailData as $sellandreturndetail) {
        $quantity = $sellandreturndetail->sellbilldetailquantity;
        $price = $sellandreturndetail->sellbilldetailprice;
        $totalPrice = $sellandreturndetail->sellbilldetailtotalprice;
        $discountValue = $sellandreturndetail->discountvalue;
        $productunitId = $sellandreturndetail->productunitid;
        $sellbillTotalBill = $sellandreturndetail->sellbillprice;
        $sellbillDiscount = $sellandreturndetail->sellbilldiscount; //محتاجة تتغير
        $sellbillDiscountType = $sellandreturndetail->sellbilldiscounttype;
        $buyprice = $sellandreturndetail->buyprice;

        if ($sellbillDiscountType == 2) {
            $sellbillDiscount = ($sellbillDiscount / 100) * $sellbillTotalBill;
        }

        $totalPriceBeforeDiscount = $price * $quantity;

        $productnumber = $myProductunitEx->getProductNumber($productunitId);

        $quantityUnit += ($quantity * $productnumber);

        //third Step قيمة الخصم للمنتجات
        $thirdStep = ($totalPrice / $sellbillTotalBill);

        $ReturnSellPriceForOneProduct = $totalPriceBeforeDiscount - (($discountValue * $quantity) + $sellbillDiscount);
        $ReturnSellPrice += $ReturnSellPriceForOneProduct;

        $allbuyprice = $quantity * $buyprice;
        $finalallbuyprice += $allbuyprice;
    }
    return array($ReturnSellPrice, $quantityUnit, $finalallbuyprice);
}

function getTotalBuyPriceByDateAndProductId($startDate, $endDate, $ProductIdselected, $storeId)
{
    global $myBuybilldetailEx;
    global $myReturnbuybilldetailEx;
    global $myBuyandruternbilldetailEx;
    global $myProductunitRecord;

    global $myProductRecord;
    global $myProductunitEx;

    $sellPrice = 0;
    $quantityUnit = 0;
    $ruturnquntity = 0;


    $queryString = '';
    $queryString2 = '';
    $queryString3 = '';

    if (!empty($ProductIdselected)) {
        $queryString .= ' AND buybilldetailproductid =' . $ProductIdselected . '';
        $queryString2 .= ' AND buybilldetailproductid =' . $ProductIdselected . '';
        $queryString3 .= ' AND returnbuybilldetailproductid =' . $ProductIdselected . '';
    }
    if (!empty($startDate)) {
        $queryString .= ' AND buybill.buybilldate >= "' . $startDate . '"';
        $queryString2 .= ' AND buyandruternbill.buybilldate >= "' . $startDate . '"';
        $queryString3 .= ' AND returnbuybill.returnbuybilldate >= "' . $startDate . '"';
    }
    if (!empty($endDate)) {
        $queryString .= ' AND buybill.buybilldate <= "' . $endDate . '"';
        $queryString2 .= ' AND buyandruternbill.buybilldate <= "' . $endDate . '"';
        $queryString3 .= ' AND returnbuybill.returnbuybilldate <= "' . $endDate . '"';
    }
    if (!empty($storeId)) {
        $queryString .= ' and buybill.buybillstoreid = ' . $storeId;
        $queryString2 .= ' and  buyandruternbill.buybillstoreid = ' . $storeId . ' ';
        $queryString3 .= ' and returnbuybill.returnbuybillstoreid = ' . $storeId . ' ';
    }

    $buybilldataData = $myBuybilldetailEx->queryWithDateAndConditionsAndProductIdandstorNew($queryString);
    foreach ($buybilldataData as $mybuybilldataData) {
        $quantity = $mybuybilldataData->buybilldetailquantity;
        $productunitId = $mybuybilldataData->productunitid;
        $productnumber = $myProductunitEx->getProductNumber($productunitId);
        $quantityUnit += ($quantity * $productnumber);
    }



    $buyandruturn = $myBuyandruternbilldetailEx->queryWithDateAndConditionsAndTypeAndProductIdandstoreNew(0, $queryString2);
    foreach ($buyandruturn as $mybuyandruturn) {
        $quantity = $mybuyandruturn->buybilldetailquantity;
        $productunitId = $mybuyandruturn->productunitid;
        $productnumber = $myProductunitEx->getProductNumber($productunitId);
        $quantityUnit += ($quantity * $productnumber);
    }


    $ruturndata = $myReturnbuybilldetailEx->queryWithDateAndConditionsAndProductIdandstoreNew($queryString3);
    foreach ($ruturndata as $myruturndata) {
        $quantity = $myruturndata->returnbuybilldetailquantity;
        $productunitId = $myruturndata->productunitid;
        $productnumber = $myProductunitEx->getProductNumber($productunitId);
        $ruturnquntity += ($quantity * $productnumber);

        //print_r('ruturndata'.$quantity.'<br>');
    }

    $ruturndataformbyandreturn = $myBuyandruternbilldetailEx->queryWithDateAndConditionsAndTypeAndProductIdandstoreNew(1, $queryString2);
    foreach ($ruturndataformbyandreturn as $myruturndataformbyandreturn) {
        $quantity = $myruturndataformbyandreturn->buybilldetailquantity;
        $productunitId = $myruturndataformbyandreturn->productunitid;
        $productnumber = $myProductunitEx->getProductNumber($productunitId);
        $ruturnquntity += ($quantity * $productnumber);
        //print_r('ruturndata'.$quantity.'<br>');
    }

    //print_r($quantityUnit.'<br>');

    return array($quantityUnit, $ruturnquntity);
}

function getTotalAditionalBuyPriceByDateAndProductId($startDate, $endDate, $ProductIdselected)
{
    global $mySellandruternbilldetailEx;
    global $mySellbillandruternRecord;
    global $myProductunitRecord;
    global $myProductRecord;
    global $myProductunitEx;

    $sellPrice = 0;
    $quantityUnit = 0;

    $sellandreturndetailData = $mySellandruternbilldetailEx->queryWithDateAndConditionsTypeAndProductIdExt($startDate, $endDate, 0, $ProductIdselected);

    foreach ($sellandreturndetailData as $sellandreturndetail) {
        $quantity = $sellandreturndetail->sellbilldetailquantity;
        $price = $sellandreturndetail->sellbilldetailprice;
        $totalPrice = $sellandreturndetail->sellbilldetailtotalprice;
        $discountValue = $sellandreturndetail->discountvalue;
        $productunitId = $sellandreturndetail->productunitid;
        $buyPrice = $sellandreturndetail->buyprice;
        $buyDiscount = $sellandreturndetail->buydiscount;
        $buyDiscountType = $sellandreturndetail->buydiscounttype;

        $productnumber = $myProductunitEx->getProductNumber($productunitId);

        $productBuyPrice = $buyPrice;
        /* if($buyDiscount > 0 && $buyDiscountType == 0)
          {
          $productBuyPrice = $buyPrice - $buyDiscount;
          } */
        /* elseif($buyDiscount > 0 && $buyDiscountType == 1)
          {
          $productBuyPrice = $buyPrice - (($buyDiscount/100) * $buyPrice);
          } */

        //$productBuyPrice = $productBuyPrice * $productnumber * $quantity;

        $quantityUnit += ($quantity * $productnumber);

        $sellPrice += $productBuyPrice;
    }

    print_r('---------------------------' . $quantityUnit . '---------------------------<br>');
    return array($sellPrice, $quantityUnit);
}

function getTotalAditionalReturnBuyPriceByDateAndProductId($startDate, $endDate, $ProductIdselected)
{
    global $mySellandruternbilldetailEx;
    global $mySellbillandruternRecord;
    global $myProductunitRecord;
    global $myProductRecord;
    global $myProductunitEx;

    $sellPrice = 0;
    $quantityUnit = 0;

    $sellandreturndetailData = $mySellandruternbilldetailEx->queryWithDateAndConditionsTypeAndProductIdExt($startDate, $endDate, 1, $ProductIdselected);

    foreach ($sellandreturndetailData as $sellandreturndetail) {
        $quantity = $sellandreturndetail->sellbilldetailquantity;
        $price = $sellandreturndetail->sellbilldetailprice;
        $totalPrice = $sellandreturndetail->sellbilldetailtotalprice;
        $discountValue = $sellandreturndetail->discountvalue;
        $productunitId = $sellandreturndetail->productunitid;
        $buyPrice = $sellandreturndetail->buyprice;
        $buyDiscount = $sellandreturndetail->buydiscount;
        $buyDiscountType = $sellandreturndetail->buydiscounttype;

        $productnumber = $myProductunitEx->getProductNumber($productunitId);

        $productBuyPrice = $buyPrice;
        if ($buyDiscount > 0 && $buyDiscountType == 0) {
            $productBuyPrice = $buyPrice - $buyDiscount;
        } elseif ($buyDiscount > 0 && $buyDiscountType == 1) {
            $productBuyPrice = $buyPrice - (($buyDiscount / 100) * $buyPrice);
        }

        $productBuyPrice = $productBuyPrice * $productnumber * $quantity;

        $quantityUnit += ($quantity * $productnumber);

        $sellPrice += $productBuyPrice;
    }
    return array($sellPrice, $quantityUnit);
}

function getTotalReturnBuyPriceByDateAndProductId($startDate, $endDate, $ProductIdselected)
{
    global $myReturnsellbilldetailEx;
    global $myReturnsellbillRecord;
    global $myProductunitRecord;
    global $myProductRecord;
    global $myProductunitEx;

    $sellPrice = 0;
    $quantityUnit = 0;

    $returnsellbilldetailData = $myReturnsellbilldetailEx->queryWithDateAndConditionsAndProductIdExt($startDate, $endDate, $ProductIdselected);

    foreach ($returnsellbilldetailData as $returnsellbilldetail) {
        $quantity = $returnsellbilldetail->returnsellbilldetailquantity;
        $price = $returnsellbilldetail->returnsellbilldetailprice;
        $totalPrice = $returnsellbilldetail->returnsellbilldetailtotalprice;
        $discountValue = $returnsellbilldetail->discountvalue;
        $productunitId = $returnsellbilldetail->productunitid;
        $buyPrice = $returnsellbilldetail->buyprice;
        $buyDiscount = $returnsellbilldetail->buydiscount;
        $buyDiscountType = $returnsellbilldetail->buydiscounttype;

        $productnumber = $myProductunitEx->getProductNumber($productunitId);

        $productBuyPrice = $buyPrice;
        if ($buyDiscount > 0 && $buyDiscountType == 0) {
            $productBuyPrice = $buyPrice - $buyDiscount;
        } elseif ($buyDiscount > 0 && $buyDiscountType == 1) {
            $productBuyPrice = $buyPrice - (($buyDiscount / 100) * $buyPrice);
        }

        $productBuyPrice = $productBuyPrice * $productnumber * $quantity;

        $quantityUnitForOneProduct = $quantity * $productnumber;
        $quantityUnit += ($quantity * $productnumber);

        $sellPrice += $productBuyPrice;
    }
    return array($sellPrice, $quantityUnit);
}

// select from Product tbl
function loadProduct()
{
    global $myProductEx;

    // select all data from producttbl
    $productData = $myProductEx->queryWithCoditionsAndCatConditions();

    foreach ($productData as $product) {
        $productcatName;
        $product->productName = loadProductCatNameById($product->productCatId, $productcatName, 1) . "/" . $product->productName;
    }
    return $productData;
}

// select from Product tbl
function loadProductCatNameById($productCatId, $productcatName, $itr)
{
    global $myProductcatRecord;

    $productcatNamex = $productcatName;
    // select all data from producttbl
    $productcatData = $myProductcatRecord->load($productCatId);

    if (count($productcatData) > 0) {
        if ($itr == 1) {
            $productcatNamex = $productcatData->productCatName;
        } elseif ($itr == 2) {
            $productcatNamex = $productcatData->productCatName . "/" . $productcatNamex;
        }

        if ($productcatData->productCatParent != 0) {
            return loadProductCatNameById($productcatData->productCatParent, $productcatNamex, 2);
        }
    }
    return $productcatNamex;
}

function loadProductCatById($productCatId)
{
    global $myProductcatRecord;
    $productCatSearchData = $myProductcatRecord->load($productCatId);
    return $productCatSearchData;
}

function getProductCats()
{
    global $myProductEx;
    global $productCatDAO;
    global $smarty;
    $categories;
    $productsData = $myProductEx->queryAllProducts();
    if (count($productsData) > 0) {
        $i = 0;
        foreach ($productsData as $pro) {
            $parentId = $pro->productCatId;
            $pathArr = fetch_recursive($parentId, $categories);
            $smarty->assign("names" . $i, $pathArr);
            $smarty->assign("parentId" . $i, $parentId);
            $i++;
        }
        $itr = $i - 1;
        $smarty->assign("itr", $itr);
    }
    return $productsData;
}

function fetch_recursive($parentid, $categories)
{
    global $myProductcatEx;
    $catData = $myProductcatEx->getCategoryAndParentByCatId($parentid);

    if (count($catData) > 0) {
        $categories .= $catData->productCatName . '/';
        $newParentId = $catData->productCatParent;

        if ($newParentId != 0) {
            $newParentName = $catData->parentName;
            $categories .= $newParentName . '/';
            fetch_recursive($newParentId, $categories);
        }
    }
    $categories = substr($categories, 0, strlen($categories) - 1);
    return $categories;
}
