<?php

//DELETED FROM MENUE IT HAS BEEN RE WRITTEN AS salesreport.php
//the global file operation
include("../public/impOpreation.php");
include("reportfunctions.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");
//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');
//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');


//# m7md
//Bill
require_once('../models/dao/BillsDAO.class.php');
require_once('../models/dto/Bill.class.php');
require_once('../models/mysql/BillsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsMySqlExtDAO.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');

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

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

$langs = $_SESSION['erp_lang'];
include_once("../views/languages/$langs/success.php");
include_once("../views/languages/$langs/error.php");

/* ======================

  Controller Name :- productsellsreportCtrl تقرير مبيعات منتج

  OPERTATION in Controller

  1-load save data
  2-display show form
  3-display details form for save
  4-display details for operation in savedaily

  ======================== */

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


//here goes the instances and general variables
//Sellbilldetail
$mySellbilldetailEx = new SellbilldetailMySqlExtDAO();
//Returnsellbilldetail
$myReturnsellbilldetailEx = new ReturnsellbilldetailMySqlExtDAO();
//Sellandruternbilldetail
$mySellandruternbilldetailEx = new SellandruternbilldetailMySqlExtDAO();

//Productunit
$myProductunitEx = new ProductunitMySqlExtDAO();
$myProductunitRecord = new ProductunitMySqlDAO();
//Product
$myProductRecord = new ProductMySqlDAO();
$myProductEx = new ProductMySqlExtDAO();
//Productcat
$myProductcatRecord = new ProductcatMySqlDAO();
$myProductcatEx = new ProductcatMySqlExtDAO();


//bill
$bills = new Bill();
$billsDAO = new BillsMySqlDAO();
$billsEX = new BillsMySqlExtDAO();

//billsproducts
$billsProducts = new Billsproduct();
$billsProductsDAO = new BillsproductsMySqlDAO();
$billsProductsEX = new BillsproductsMySqlExtDAO();

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

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

//Productcat
$productCatDAO = new ProductcatMySqlDAO();
$productCatExt = new ProductcatMySqlExtDAO();

//Programsetting
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();



//check and use the condetion that suite this action
if ($do == "show" || empty($do)) {

    $smarty->assign("hidecat", $_SESSION['hidecat']);
    //here the permssion check
    include_once("../public/authentication.php");

    $productCatData = loadProductCat();
    $smarty->assign("productCatData", $productCatData);


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


    $dateNow = date("Y-m-d");
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $productCatId = $_REQUEST['productCatId'];
    ########
    if (empty($startDate) && empty($endDate)) {
        $startDate = date('Y-m-d');
        $endDate = date('Y-m-d');
        if (empty($productCatId)) {
            $productCatId = "all";
        }
    }
    ########
    $Programsetting = $ProgramsettingDAO->load(1);
    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
        $endDate = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($endDate)));
        $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($startDate)));
    } else {
        $endDate = $endDate . ' 23:59:59';
        $startDate = $startDate . " 00:00:00";
    }
    #############
//    if (empty($productCatId)) {//dont search//must choose cat//too much products stop the report
//        $productCatId = 0;
//    }
    if (isset($productCatId) && $productCatId != "-1" && $productCatId != "all" && isset($startDate) && isset($endDate) && $startDate != "" && $endDate != "") {//بحث بإسم التصنيف والتاريخ
        $mycatdata = $myProductcatRecord->load($productCatId);
        $message = "تقرير مبيعات منتج من التصنيف :" . $mycatdata->productCatName . "<br> من تاريخ: " . $startDate . " إلى تاريخ: " . $endDate;
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 1);
        loadProductByProductcatIdAndDate($productCatId, $startDate, $endDate);
    } elseif (isset($productCatId) && $productCatId == "-1" && $startDate != "" && $endDate != "") {
        //بحث بكل الأصناف والتاريخ

        $message = "تقرير مبيعات منتج في كل الأصناف<br> من تاريخ: " . $startDate . " إلى  تاريخ: " . $endDate;
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 1);
        loadProductByAllCategoriesAndDate($startDate, $endDate);
    } elseif (isset($productCatId) && $productCatId == "-1" && $startDate != "" && $endDate == "") {
        //بحث بكل الأصناف والتاريخ

        $message = "تقرير مبيعات منتج في كل الأصناف " . $mycatdata->productCatName . "<br> من تاريخ: " . $startDate . " إلى  تاريخ: " . $dateNow;
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 1);
        loadProductByAllCategoriesAndDate($startDate, $dateNow);
    } elseif (isset($productCatId) && $productCatId != "-1" && $productCatId != "all" && $startDate != "" && $endDate == "") {
        $mycatdata = $myProductcatRecord->load($productCatId);

        //بحث بكل الأصناف والتاريخ
        $message = "تقرير مبيعات منتج من التصنيف :" . $mycatdata->productCatName . "<br> من تاريخ: " . $startDate . " إلى تاريخ: " . $dateNow;

        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 1);
        loadProductByProductcatIdAndDate($productCatId, $startDate, $dateNow);
    } elseif (isset($productCatId) && $productCatId == "-1" && $startDate == "" && $endDate != "") {
        //بحث بكل الأصناف والتاريخ
        $startDate = date("Y", strtotime("-1 year"));
        $message = "تقرير مبيعات منتج في كل الأصناف<br>   إلى  تاريخ: " . $endDate;
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 1);
        loadProductByAllCategoriesAndDate($startDate, $endDate);
    } elseif (isset($productCatId) && $productCatId != "-1" && $productCatId != "all" && $startDate == "" && $endDate != "") {
        //بحث بكل الأصناف والتاريخ
        $startDate = date("Y", strtotime("-1 year"));
        $message = "تقرير مبيعات منتج من التصنيف :" . $mycatdata->productCatName . "<br>إلى تاريخ: " . $endDate;
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 1);
        loadProductByProductcatIdAndDate($productCatId, $startDate, $endDate);
    } elseif (isset($productCatId) && $productCatId == "all" && $startDate != "" && $endDate != "") {
        //بحث بكل الأصناف والتاريخ
        $message = "تقرير مبيعات منتج في كل الأصناف<br> من تاريخ: " . $startDate . " إلى  تاريخ: " . $endDate;
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 1);
        loadProductByAllCategoriesAndDate($startDate, $endDate);
    } elseif (isset($productCatId) && $productCatId == "all" && $startDate != "" && $endDate == "") {

        //بحث بكل الأصناف والتاريخ
        $message = "تقرير مبيعات منتج في كل الأصناف<br> من تاريخ: " . $startDate . " إلى  تاريخ: " . $dateNow;
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 1);
        loadProductByAllCategoriesAndDate($startDate, $dateNow);
    } elseif (isset($productCatId) && $productCatId == "all" && $startDate == "" && $endDate != "") {

        $startDate = date("Y", strtotime("-1 year"));
        //بحث بكل الأصناف والتاريخ
        $message = "تقرير مبيعات منتج في كل الأصناف<br>  إلى  تاريخ: " . $endDate;
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 1);
        loadProductByAllCategoriesAndDate($startDate, $endDate);
    } elseif (isset($productCatId) && $productCatId != "-1" && $productCatId != "all" && $startDate == "" && $endDate == "") {
        //بحث بإسم التصنيف فقط
        $mycatdata = $myProductcatRecord->load($productCatId);
        $message = "تقرير مبيعات منتج من التصنيف : " . $mycatdata->productCatName;
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 0);
        loadProductByProductcatId($productCatId);
    } elseif (isset($productCatId) && $productCatId != "-1" && $startDate == "" && $endDate == "") {
        //بحث بكل الأصناف بدون تاريخ محدد
        $message = "تقرير مبيعات منتج في كل الأصناف";
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 0);
        loadProductByAllCategories();
    } else {

    }

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



if ($do == "showmain") {

    $smarty->assign("hidecat", $_SESSION['hidecat']);
    //here the permssion check
    include_once("../public/authentication.php");

//    $productCatData = loadProductCatmain();
//    $smarty->assign("productCatData", $productCatData);
    ##التصنيفات
    $catDataReport = getCategoryChilds();
    $smarty->assign("catDataReport", $catDataReport[1]);

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

    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $productCatId = $_REQUEST['productCatId'];
    if (empty($startDate) && empty($endDate)) {
        $startDate = date('Y-m-d') . ' 00:00:00';
        $endDate = date('Y-m-d') . ' 23:59:59';
    } else {
        $startDate = $startDate . ' 00:00:00';
        $endDate = $endDate . ' 23:59:59';
    }
    if (isset($productCatId) && $productCatId != "-1" && isset($startDate) && isset($endDate) && $startDate != "" && $endDate != "") {

        //print_r(11111111111111111111111111111111);
        //بحث بإسم التصنيف والتاريخ

        $mycatdata = $myProductcatRecord->load($productCatId);
        $message = "تقرير التصنيفات الرئيسية :" . $mycatdata->productCatName . "<br> من تاريخ: " . $startDate . " إلى تاريخ: " . $endDate;
        $smarty->assign('message', $message);
        $smarty->assign('nothavedate', 1);
        loadProductByProductcatIdAndDatemain($productCatId, $startDate, $endDate);
    }

    /* elseif (isset($productCatId) && $productCatId != "-1" && $startDate == "" && $endDate == "")
      {
      //print_r(22222222222222222222222222);
      //بحث بإسم التصنيف فقط
      $mycatdata = $myProductcatRecord->load($productCatId);
      $message = "تقرير مبيعات منتج من التصنيف : " . $mycatdata->productCatName;
      $smarty->assign('message', $message);
      $smarty->assign('nothavedate', 0);
      loadProductByProductcatIdmain($productCatId);
      }
     */



    //here the smarty templates
    $smarty->display("productsellsreportview/showmain.html");
} elseif ($do == "sucess") {
    //here the smarty templates
    $smarty->display("succes.html");
} elseif ($do == "error") {
    //here the smarty templates
    $smarty->display("error.html");
}

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

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

//select all data from sellbill datail by product id
function showSellByProduct($productId) {
    global $mySellbilldetailEx;

    $sumQuantity = $mySellbilldetailEx->queryQuantityWithProduct($productId);

    return $sumQuantity;
}

// show the form by date
function showAditionalSellByProduct($productId) {
    global $mySellandruternbilldetailEx;

    $sumQuantity = $mySellandruternbilldetailEx->queryQuantityWithProductAndSellType($productId, 0);
    return $sumQuantity;
}

function showReturnSellByProduct($productId) {
    global $myReturnsellbilldetailEx;

    $sumQuantity = $myReturnsellbilldetailEx->queryQuantityWithProduct($productId);
    return $sumQuantity;
}

function showAditionalReturnSellByProduct($productId) {
    global $mySellandruternbilldetailEx;

    $sumQuantity = $mySellandruternbilldetailEx->queryQuantityWithProductAndSellType($productId, 1);
    return $sumQuantity;
}

//select data from product by productcatid
function loadProductByProductcatId($productcatId) {
    global $myProductEx;
    global $myProductRecord;
    global $smarty;
    global $billsDAO;
    global $billsProductsDAO;
    global $billsProductsEX;
    global $billsReturnProductsEX;


    $totalRemainQuantity = 0;


    //$productData=$myProductEx->queryWithProductcatIdAndConditions($productcatId);
    $productData = $myProductEx->queryByProductCatIdExt($productcatId);
    $smarty->assign("productData", $productData);

    $h = 1;
    foreach ($productData as $product) {

        $remainQuantity = 0;
        $totalsell = $productproft = $total_profit = $totalsell_withoutdiscount = $bills_sells_quantity = 0;
        //# مردودات
        $get_discount_return = $product_total_return = $bills_return_quantity = 0;
        $productId = $product->productId;


        $sellsQuantity = showSellByProduct($productId);
        $aditionalSellsQuantity = showAditionalSellByProduct($productId);

        $totalSellsQuantity = $sellsQuantity + $aditionalSellsQuantity;

        $returnSellQuantity = showReturnSellByProduct($productId);
        $aditionalReturnSellsQuantity = showAditionalReturnSellByProduct($productId);

        $totalReturnSellsQuantity = $returnSellQuantity + $aditionalReturnSellsQuantity;

        $remainQuantity = $totalSellsQuantity - $totalReturnSellsQuantity;

        //إجمالي عدد القطع المباعة
        $totalRemainQuantity += $remainQuantity;


        //# start bills
        $where = " billsproducts.productid = " . $productId . "  ";
        $bills_details = $billsProductsEX->load_billproducts_and_bills_by_productid($where);

        if (!empty($bills_details)) {

            //# call function.
            $productCatName = '';
            $productCatId = $product->productCatId;
            $productCatName = get_parentCat_of_productCat($productCatId, $productCatName);
            $productCatName = get_childCat_of_productCat($productCatId, $productCatName);


            //# مبيعات بصاريات
            foreach ($bills_details as $detail_val) {
                //# الكمية المباعة
                $bills_sells_quantity += $detail_val->productno;

                //# get discount to calculate sell price .
                //$get_discount = (( $detail_val->productprice * $detail_val->productno ) / $detail_val->netbillvalue ) * $detail_val->discountvalue;
                $get_discount = ( $detail_val->productprice * $detail_val->productno ) - $detail_val->netbillvalue;

                //# مبلغ المبيعات
                $product_total_sell = ( $detail_val->productprice * $detail_val->productno ) - $get_discount;
                $totalsell += $product_total_sell;
                //# صافى الربح
                $total_profit += $product_total_sell - ( $detail_val->productBuyPrice * $detail_val->productno );
                print_r('<br> $total_profit = ' . $total_profit . ' $product_total_sell = ' . $product_total_sell . ' $detail_val->productBuyPrice ' . $detail_val->productBuyPrice . ' $detail_val->productno = ' . $detail_val->productno);

                //# المبيعات بدون خصومات
                $totalsell_withoutdiscount += ( $detail_val->netbillvalue + $get_discount );
            }

            //# مردود مبيعات بصاريات
            $where_return = " billsreturnproducts.productid = " . $productId . " ";
            $return_bill_detial = $billsReturnProductsEX->load_billproducts_and_bills_by_productid($where_return);

            foreach ($return_bill_detial as $detail_val) {
                //# الكمية المباعة
                $bills_return_quantity += $detail_val->productno;

                //# get discount to calculate sell price .
                //$get_discount_return = (( $detail_val->productprice * $detail_val->productno ) / $detail_val->netbillvalue ) * $detail_val->discountvalue;
                $get_discount_return = ( $detail_val->productprice * $detail_val->productno ) - $detail_val->netbillvalue;

                //# مبلغ مردود المبيعات
                $product_total_return = ( $detail_val->productprice * $detail_val->productno ) - $get_discount;
                $totalsell -= $product_total_return;

                //# المبيعات بدون خصومات
                $totalsell_withoutdiscount -= ( $detail_val->returnedprice + $get_discount );
            }
        }

        //# 	صافى الكميه المباعه
        $bills_remine_quantity = $bills_sells_quantity - $bills_return_quantity;
        $remainQuantity += $bills_remine_quantity;

        $totalSellsQuantity += $bills_sells_quantity;
        $totalsell = round($totalsell, 2);
        $totalRemainQuantity += $remainQuantity;
        $totalReturnSellsQuantity += $bills_return_quantity;

        //print_r('<br>before $productproft = '.$productproft .' $totalsell = '.$totalsell . ' $total_profit =  '.$total_profit);
        //$productproft += ($totalsell - $total_profit);
        $productproft += $total_profit;
        // print_r('<br>after $productproft = '.$productproft);

        $productproft = round($productproft, 2);

        //# end
        $smarty->assign("totalsell" . $h . "", round($totalsell, 2));
        $smarty->assign("productproft" . $h . "", round($productproft, 2));
        $smarty->assign("totalsell_withoutdiscount" . $h . "", $totalsell_withoutdiscount);
        $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
        $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
        $smarty->assign("remainQuantity" . $h . "", $remainQuantity);
        $smarty->assign("productCatName" . $h . "", $productCatName);

        $h++;
    }
    $smarty->assign("totalRemainQuantity", $totalRemainQuantity);
}

//# m7md
function get_parentCat_of_productCat($productcatId, $productCatName) {
    global $productCatDAO;

    $productCat_detail = $productCatDAO->load($productcatId);

    $productCatParent = $productCat_detail->productCatParent;
    $productCatName = $productCat_detail->productCatName . '/' . $productCatName;
    if ($productCatParent != 0) {
        $productCatName = get_parentCat_of_productCat($productCatParent, $productCatName);
    }

    return $productCatName;
}

//#m7md
function get_childCat_of_productCat($productcatId, $productCatName) {
    global $productCatDAO;

    $productCat_detail = $productCatDAO->queryByProductCatParent($productcatId);

    $productCatId = $productCat_detail[0]->productCatId;
    $productCatParent = $productCat_detail->productCatParent;

    if ($productCatParent != 0) {
        $productCatName = $productCatName . '/' . $productCat_detail[0]->productCatName;

        $productCat_detail_2 = $productCatDAO->queryByProductCatParent($productCatId);
        $productCatParent_2 = $productCat_detail_2[0]->productCatParent;

        if (!empty($productCat_detail_2[0])) {
            $productCatId_2 = $productCat_detail_2[0]->productCatId;

            $productCatName = $productCatName . '/' . $productCat_detail_2[0]->productCatName;
            $productCatName = get_childCat_of_productCat($productCatId_2, $productCatName);
        }
    }

    return $productCatName;
}

function getTotalSellPriceByDateAndProductId($startDate, $endDate, $ProductIdselected) {
    global $mySellbilldetailEx;
    global $myProductunitRecord;
    global $mySellbillRecord;
    global $myProductRecord;
    global $myProductEx;
    global $myProductunitEx;
    global $ProgramsettingDAO;

    $Programsetting = $ProgramsettingDAO->load(1);

    $totalsell_withoutdiscount = 0;
    $sellPrice = 0;
    $quantityUnit = 0;
    $finalbuypric = 0;
    $sellbilldetailData = $mySellbilldetailEx->queryWithDateAndConditionsAndProductIdExt($startDate, $endDate, $ProductIdselected);

    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;
        switch ($Programsetting->Profitevaluation) {
            case "first":
                $buyprice = (float) $sellbilldetail->buyprice;
                break;
            case "last":
                $buyprice = (float) $sellbilldetail->lastbuyprice;
                break;
            case "mean":
                $buyprice = (float) $sellbilldetail->meanbuyprice;
                break;
            case "last_discount":
                $buyprice = (float) $sellbilldetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyprice = (float) $sellbilldetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyprice = (float) $sellbilldetail->generalPrice;
                break;
        }

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

        $totalPriceBeforeDiscount = $price * $quantity; //product price before discount

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

        $quantityUnit += ($quantity * $productnumber); //الكمية * عدد القطع في وحدة القياس
        //first Step قيمة خصم المنتج على الوحدة
        //$firstStep = $discountValue;
        //second Stepقيمة المنتج من الخصم على المنتج
        //$secondStep = $totalPrice / $sellbillTotalBill;

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

        //fourth Step  قيمة خصم الفاتورة للوحدة
        //$fourthStep = $thirdStep;
        //$sellPriceForOneProduct= $totalPriceBeforeDiscount - ($firstStep + $fourthStep);
        $allbuyprice = $buyprice * $quantity;
        $finalbuypric += $allbuyprice;
        $sellPriceForOneProduct = $totalPriceBeforeDiscount - ($discountValue + $thirdStep);
        $sellPrice += $sellPriceForOneProduct;
        $totalsell_withoutdiscount += $totalPriceBeforeDiscount;
    }


    return array($sellPrice, $quantityUnit, $finalbuypric, $totalsell_withoutdiscount);
}

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

    $Programsetting = $ProgramsettingDAO->load(1);

    $sellPrice = 0;
    $quantityUnit = 0;
    $fianlbuyprice = 0;
    $sellbillTotalBillnor = 0;
    $totalsell_withoutdiscount = 0;

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


    foreach ($sellandreturndetailData as $sellandreturndetail) {

        //print_r($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;
        $returnsellbillprice = $sellandreturndetail->returnsellbillprice;
//        $buyprice = $sellandreturndetail->buyprice;

        switch ($Programsetting->Profitevaluation) {
            case "first":
                $buyprice = (float) $sellandreturndetail->buyprice;
                break;
            case "last":
                $buyprice = (float) $sellandreturndetail->lastbuyprice;
                break;
            case "mean":
                $buyprice = (float) $sellandreturndetail->meanbuyprice;
                break;
            case "last_discount":
                $buyprice = (float) $sellandreturndetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyprice = (float) $sellandreturndetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyprice = (float) $overAllAveragePrice;
                break;
        }


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

        $sellbillTotalBillnor = $sellbillTotalBillnor + $totalPrice;
//
//			//load Product Data
//			$productData=$myProductRecord->load($productId);
//
//			$productSellPrice;
//			//تجزئة
//			if($priceType == 1)
//			{
//				$productSellPrice=$productData->productSellUnitPrice;
//			}
//			//جملة
//			elseif($priceType == 0)
//			{
//				$productSellPrice=$productData->productSellAllPrice;
//			}
//			//نصف جملة
//			elseif($priceType == 2)
//			{
//				$productSellPrice=$productData->productSellHalfPrice;
//			}
//

        $totalPriceBeforeDiscount = $price * $quantity;

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

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

        if ($sellbillTotalBill == 0) {
            $sellbillTotalBill = 1;
        }
        //third Step قيمة الخصم للمنتجات
        //$thirdStep  = ($totalPrice / $sellbillTotalBill)* $sellbillDiscount;
        $allbuyprice = $quantity * $buyprice;
        $fianlbuyprice += $allbuyprice;

        $sellPriceForOneProduct = $totalPriceBeforeDiscount - ($discountValue + $thirdStep);
        $sellPrice += $sellPriceForOneProduct;
        $totalsell_withoutdiscount += $totalPriceBeforeDiscount;
    }
    $sellbillTotalBillnor = $sellbillTotalBillnor - $returnsellbillprice;
    ;
    /* print_r('<br>sellbillTotalBillnor'.$sellbillTotalBillnor);
      print_r('<br>returnsellbillprice'.$returnsellbillprice);
      print_r('<br>sellbillDiscount'.$sellbillDiscount); */
    if ($sellbillDiscountType == 2) {
        $sellbillDiscountxx = ($sellbillDiscount / 100) * $sellbillTotalBillnor;
        //print_r('<br>sellbillDiscountxx'.$sellbillDiscountxx);
    } else {
        $sellbillDiscountxx = ($sellbillDiscount);
    }

    $sellPrice = $sellPrice - $sellbillDiscountxx;

    @$thirdStep = ($totalPrice / $sellbillTotalBill) * $sellbillDiscount;

    return array($sellPrice, $quantityUnit, $fianlbuyprice, $totalsell_withoutdiscount);
}

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

    $Programsetting = $ProgramsettingDAO->load(1);

    $ReturnSellPrice = 0;
    $quantityUnit = 0;
    $finalbuyprice = 0;
    $totalsell_withoutdiscount = 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;
        $sellbillTotalBill = $returnsellbilldetail->returnsellbilltotalbill;
        $sellbillDiscount = $returnsellbilldetail->returnsellbilldiscount;
        $sellbillDiscountType = $returnsellbilldetail->returnsellbilldiscounttype;
//        $buyprice = $returnsellbilldetail->buyprice;

        switch ($Programsetting->Profitevaluation) {
            case "first":
                $buyprice = (float) $returnsellbilldetail->buyprice;
                break;
            case "last":
                $buyprice = (float) $returnsellbilldetail->lastbuyprice;
                break;
            case "mean":
                $buyprice = (float) $returnsellbilldetail->meanbuyprice;
                break;
            case "last_discount":
                $buyprice = (float) $returnsellbilldetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyprice = (float) $returnsellbilldetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyprice = (float) $overAllAveragePrice;
                break;
        }
        if ($sellbillDiscountType == 2) {
            $sellbillDiscount = ($sellbillDiscount / 100) * $sellbillTotalBill;
        }

        $totalPriceBeforeDiscount = $price * $quantity;

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

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

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


        $allbuyprice = $quantity * $buyprice;

        //print_r("<br>buyprice".$buyprice);
        //print_r("<br>quantity".$quantity);
        $finalbuyprice += $allbuyprice;
        $ReturnSellPriceForOneProduct = $totalPriceBeforeDiscount - ($discountValue + $thirdStep);
        $ReturnSellPrice += $ReturnSellPriceForOneProduct;
        $totalsell_withoutdiscount += $totalPriceBeforeDiscount;
    }

//print($ReturnSellPrice."ReturnSellPrice<br>");

    return array($ReturnSellPrice, $quantityUnit, $finalbuyprice, $totalsell_withoutdiscount);
}

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

    $Programsetting = $ProgramsettingDAO->load(1);

    $ReturnSellPrice = 0;
    $quantityUnit = 0;
    $finalbuyprice = 0;

    $totalsell_withoutdiscount = 0;

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


    //print_r($sellandreturndetailData);
    foreach ($sellandreturndetailData as $sellandreturndetail) {
        $quantity = $sellandreturndetail->sellbilldetailquantity;
        $price = $sellandreturndetail->sellbilldetailprice;
        $totalPrice = $sellandreturndetail->sellbilldetailtotalprice;
        $discountValue = $sellandreturndetail->discountvalue;
        $productunitId = $sellandreturndetail->productunitid;
        //$sellbillTotalBill=$sellbillData->sellbillprice;
        $sellbillDiscount = $sellandreturndetail->sellbilldiscount; //محتاجة تتغير
        $totalPrice = $sellandreturndetail->sellbilldetailtotalprice;
        $discountvalue = $sellandreturndetail->discountvalue;
        //print_r("<br>sellbillDiscount".$sellbillDiscount."<br>");
        //print_r("<br>price".$price."<br>");
        //print_r("<br>discountvalue".$discountvalue."<br>");
        //خصم نسبه
        /* if($sellbillDiscountType == 2)
          {
          $sellbillDiscount = ($sellbillDiscount / 100) * $sellbillTotalBill;
          }
         */
//        $buyprice = $sellandreturndetail->buyprice;

        switch ($Programsetting->Profitevaluation) {
            case "first":
                $buyprice = (float) $sellandreturndetail->buyprice;
                break;
            case "last":
                $buyprice = (float) $sellandreturndetail->lastbuyprice;
                break;
            case "mean":
                $buyprice = (float) $sellandreturndetail->meanbuyprice;
                break;
            case "last_discount":
                $buyprice = (float) $sellandreturndetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyprice = (float) $sellandreturndetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyprice = (float) $overAllAveragePrice;
                break;
        }
        $totalPriceBeforeDiscount = $price * $quantity;

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

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

        if ($sellbillTotalBill == 0) {
            $sellbillTotalBill = 1;
        }
        //third Step قيمة الخصم للمنتجات
        $allbuyprice = $quantity * $buyprice;
        $finalbuyprice += $allbuyprice;
        $thirdStep = ($totalPrice / $sellbillTotalBill);
        //print_r("<br>totalPriceBeforeDiscount".$totalPriceBeforeDiscount."<br>");
        $ReturnSellPriceForOneProduct = $totalPriceBeforeDiscount - ($discountValue * $quantity);
        //$ReturnSellPriceForOneProduct = $totalPriceBeforeDiscount - ($discountValue + $thirdStep);
        //print_r("--------------<br>ReturnSellPriceForOneProduct".$ReturnSellPriceForOneProduct."<br>-------");
        $ReturnSellPrice += $ReturnSellPriceForOneProduct;
        $totalsell_withoutdiscount += $totalPriceBeforeDiscount;
    }
    //print_r("<br>".$ReturnSellPrice."<br>");

    return array($ReturnSellPrice, $quantityUnit, $finalbuyprice, $totalsell_withoutdiscount);
}

function getTotalBuyPriceByDateAndProductId($startDate, $endDate, $ProductIdselected, $overAllAveragePrice) {
    global $mySellbilldetailEx;
    global $myProductunitRecord;
    global $mySellbillRecord;
    global $myProductRecord;
    global $myProductunitEx;
    global $ProgramsettingDAO;

    $Programsetting = $ProgramsettingDAO->load(1);

    $sellPrice = 0;
    $quantityUnit = 0;

    $sellbilldetailData = $mySellbilldetailEx->queryWithDateAndConditionsAndProductIdExt($startDate, $endDate, $ProductIdselected);



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

        switch ($Programsetting->Profitevaluation) {
            case "first":
                $buyPrice = (float) $sellbilldetail->buyprice;
                break;
            case "last":
                $buyPrice = (float) $sellbilldetail->lastbuyprice;
                break;
            case "mean":
                $buyPrice = (float) $sellbilldetail->meanbuyprice;
                break;
            case "last_discount":
                $buyPrice = (float) $sellbilldetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyPrice = (float) $sellbilldetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyPrice = (float) $overAllAveragePrice;
                break;
        }
        $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 getTotalAditionalBuyPriceByDateAndProductId($startDate, $endDate, $ProductIdselected, $overAllAveragePrice) {
    global $mySellandruternbilldetailEx;
    global $mySellbillandruternRecord;
    global $myProductunitRecord;
    global $myProductRecord;
    global $myProductunitEx;
    global $ProgramsettingDAO;

    $Programsetting = $ProgramsettingDAO->load(1);

    $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;
        $buyDiscount = $sellandreturndetail->buydiscount;
        $buyDiscountType = $sellandreturndetail->buydiscounttype;
//        $buyPrice = $sellandreturndetail->buyprice;
        switch ($Programsetting->Profitevaluation) {
            case "first":
                $buyPrice = (float) $sellandreturndetail->buyprice;
                break;
            case "last":
                $buyPrice = (float) $sellandreturndetail->lastbuyprice;
                break;
            case "mean":
                $buyPrice = (float) $sellandreturndetail->meanbuyprice;
                break;
            case "last_discount":
                $buyPrice = (float) $sellandreturndetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyPrice = (float) $sellandreturndetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyPrice = (float) $overAllAveragePrice;
                break;
        }

        $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 getTotalAditionalReturnBuyPriceByDateAndProductId($startDate, $endDate, $ProductIdselected, $overAllAveragePrice) {
    global $mySellandruternbilldetailEx;
    global $mySellbillandruternRecord;
    global $myProductunitRecord;
    global $myProductRecord;
    global $myProductunitEx;
    global $ProgramsettingDAO;

    $Programsetting = $ProgramsettingDAO->load(1);

    $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;
        $buyDiscount = $sellandreturndetail->buydiscount;
        $buyDiscountType = $sellandreturndetail->buydiscounttype;
//        $buyPrice = $sellandreturndetail->buyprice;

        switch ($Programsetting->Profitevaluation) {
            case "first":
                $buyPrice = (float) $sellandreturndetail->buyprice;
                break;
            case "last":
                $buyPrice = (float) $sellandreturndetail->lastbuyprice;
                break;
            case "mean":
                $buyPrice = (float) $sellandreturndetail->meanbuyprice;
                break;
            case "last_discount":
                $buyPrice = (float) $sellandreturndetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyPrice = (float) $sellandreturndetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyPrice = (float) $overAllAveragePrice;
                break;
        }
        $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, $overAllAveragePrice) {
    global $myReturnsellbilldetailEx;
    global $myReturnsellbillRecord;
    global $myProductunitRecord;
    global $myProductRecord;
    global $myProductunitEx;
    global $ProgramsettingDAO;

    $Programsetting = $ProgramsettingDAO->load(1);

    $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;
        $buyDiscount = $returnsellbilldetail->buydiscount;
        $buyDiscountType = $returnsellbilldetail->buydiscounttype;
//        $buyPrice = $returnsellbilldetail->buyprice;
        switch ($Programsetting->Profitevaluation) {
            case "first":
                $buyPrice = (float) $returnsellbilldetail->buyprice;
                break;
            case "last":
                $buyPrice = (float) $returnsellbilldetail->lastbuyprice;
                break;
            case "mean":
                $buyPrice = (float) $returnsellbilldetail->meanbuyprice;
                break;
            case "last_discount":
                $buyPrice = (float) $returnsellbilldetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyPrice = (float) $returnsellbilldetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyPrice = (float) $overAllAveragePrice;
                break;
        }
        $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 getProductInBillByDateAndProductId($startDate, $endDate, $productId) {
    global $mySellbilldetailEx;
    global $myProductRecord;
    global $Programsetting;

    global $smarty;

    $productData = $myProductRecord->load($productId);
    $overAllAveragePrice = $productData->overAllAveragePrice;
    //$smarty->assign("productData", $productData);
    //sellbill
    $sellPriceData = getTotalSellPriceByDateAndProductId($startDate, $endDate, $productId, $overAllAveragePrice);

    $sellPricePulsData = getTotalAditionalSellPriceByDateAndProductId($startDate, $endDate, $productId, $overAllAveragePrice);

    $sellPrice = $sellPriceData[0] + $sellPricePulsData[0];
    $quantityUnit = $sellPriceData[1] + $sellPricePulsData[1];

    $totalsell_withoutdiscount = $sellPriceData[3] + $sellPricePulsData[3]; //# اجمالى المبيعات قبل الخصم فى فاتورة المبيعات



    $totalSellPrice = $totalSellPrice + $sellPrice;
    $totalQuantityUnit = $totalQuantityUnit + $quantityUnit;

    //return sellbill
    $returnSellPriceData = getTotalReturnSellPriceByDateAndProductId($startDate, $endDate, $productId, $overAllAveragePrice);

    $returnSellPricePulsData = getTotalAditionalReturnSellPriceByDateAndProductId($startDate, $endDate, $productId, $overAllAveragePrice);



    $returnSellPrice = $returnSellPriceData[0] + $returnSellPricePulsData[0];
    $returnQuantityUnit = $returnSellPriceData[1] + $returnSellPricePulsData[1];

    $totalReturnSellPrice = $totalReturnSellPrice + $returnSellPrice;
    $totalreturnQuantityUnit = $totalreturnQuantityUnit + $returnQuantityUnit;

    $totalPriceBeforeDiscount = $returnSellPriceData[3] + $returnSellPricePulsData[3]; //# اجمالى المبيعات قبل الخصم فى فاتورة المرتجعات
    //buybill
    $buybillPriceData = getTotalBuyPriceByDateAndProductId($startDate, $endDate, $productId, $overAllAveragePrice);

    $buybillPricePulsData = getTotalAditionalBuyPriceByDateAndProductId($startDate, $endDate, $productId, $overAllAveragePrice);

    $buybillPrice = $buybillPriceData[0] + $buybillPricePulsData[0];
    $buyQuantityUnit = $buybillPriceData[1] + $buybillPricePulsData[1];


    $totalBuybillPrice = $totalBuybillPrice + $buybillPrice;
    $totalBuyQuantityUnit = $totalBuyQuantityUnit + $buyQuantityUnit;

    //return buybill
    $returnBuybillPriceData = getTotalReturnBuyPriceByDateAndProductId($startDate, $endDate, $productId, $overAllAveragePrice);

    $returnBuybillPricePulsData = getTotalAditionalBuyPriceByDateAndProductId($startDate, $endDate, $productId, $overAllAveragePrice);

    $returnBuybillPrice = $returnBuybillPriceData[0] + $returnBuybillPricePulsData[0];
    $returnBuyQuantityUnit = $returnBuybillPriceData[1] + $returnBuybillPricePulsData[1];

    $totalReturnBuybillPrice = $totalReturnBuybillPrice + $returnBuybillPrice;
    $totalReturnBuyQuantityUnit = $totalReturnBuyQuantityUnit + $returnBuyQuantityUnit;

    $yy = $sellPrice - $returnSellPrice;

    $quantityForOneProduct = $quantityUnit - $returnQuantityUnit;

    //سعر الوحده
    if ($buybillPrice == 0 && $returnBuybillPrice != 0)
        @$xx = ($returnBuybillPrice) / $returnBuyQuantityUnit;
    else
        @$xx = ($buybillPrice) / $quantityUnit;




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

    $final_totalsellwithout_discount = $totalsell_withoutdiscount - $totalPriceBeforeDiscount;

    return array($profitForOneProduct, $yy, ($quantityForOneProduct * $xx), $final_totalsellwithout_discount);
}

//select data from product
function loadProductByAllCategories() {
    global $myProductEx;
    global $myProductRecord;
    global $smarty;
    global $billsProductsEX;
    global $billsReturnProductsEX;

    $totalRemainQuantity = 0;
    $remainQuantity = 0;

    $productData = $myProductEx->queryByAllCategories();
    $smarty->assign("productData", $productData);

    $h = 1;
    foreach ($productData as $product) {
        $totalsell = $productproft = $total_profit = $totalsell_withoutdiscount = $bills_sells_quantity = 0;
        //# مردودات
        $get_discount_return = $product_total_return = $bills_return_quantity = 0;


        $productId = $product->productId;
        $sellsQuantity = showSellByProduct($productId);
        $aditionalSellsQuantity = showAditionalSellByProduct($productId);

        $totalSellsQuantity = $sellsQuantity + $aditionalSellsQuantity;

        $returnSellQuantity = showReturnSellByProduct($productId);
        $aditionalReturnSellsQuantity = showAditionalReturnSellByProduct($productId);

        $totalReturnSellsQuantity = $returnSellQuantity + $aditionalReturnSellsQuantity;

        $remainQuantity = $totalSellsQuantity - $totalReturnSellsQuantity;

        //إجمالي عدد القطع المباعة
        $totalRemainQuantity += $remainQuantity;

        $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
        $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
        $smarty->assign("remainQuantity" . $h . "", $remainQuantity);


        //# start bills
        $where = " billsproducts.productid = " . $productId . " ";
        $bills_details = $billsProductsEX->load_billproducts_and_bills_by_productid($where);


        if (!empty($bills_details)) {

            //# call function.
            $productCatName = '';
            $productCatId = $product->productCatId;
            $productCatName = get_parentCat_of_productCat($productCatId, $productCatName);
            $productCatName = get_childCat_of_productCat($productCatId, $productCatName);



            //# مبيعات بصاريات
            foreach ($bills_details as $detail_val) {
                //# الكمية المباعة
                $bills_sells_quantity += $detail_val->productno;

                //# get discount to calculate sell price .
                //$get_discount = (( $detail_val->productprice * $detail_val->productno ) / $detail_val->netbillvalue ) * $detail_val->discountvalue;
                $get_discount = ( $detail_val->productprice * $detail_val->productno ) - $detail_val->netbillvalue;

                //# مبلغ المبيعات
                $product_total_sell = ( $detail_val->productprice * $detail_val->productno ) - $get_discount;
                $totalsell += $product_total_sell;

                //# صافى الربح
                $total_profit += $product_total_sell - ( $detail_val->productBuyPrice * $detail_val->productno );

                //# المبيعات بدون خصومات
                $totalsell_withoutdiscount += ( $detail_val->netbillvalue + $get_discount );
            }

            //# مردود مبيعات بصاريات
            $where_return = " billsreturnproducts.productid = " . $productId . "  ";
            $return_bill_detial = $billsReturnProductsEX->load_billproducts_and_bills_by_productid($where_return);

            foreach ($return_bill_detial as $detail_val) {
                //# الكمية المباعة
                $bills_return_quantity += $detail_val->productno;

                //# get discount to calculate sell price .
                //$get_discount_return = (( $detail_val->productprice * $detail_val->productno ) / $detail_val->netbillvalue ) * $detail_val->discountvalue;
                $get_discount_return = ( $detail_val->productprice * $detail_val->productno ) - $detail_val->netbillvalue;

                //# مبلغ مردود المبيعات
                $product_total_return = ( $detail_val->productprice * $detail_val->productno ) - $get_discount;
                $totalsell -= $product_total_return;

                //# المبيعات بدون خصومات
                $totalsell_withoutdiscount -= ( $detail_val->returnedprice + $get_discount );
            }

            //# 	صافى الكميه المباعه
            $bills_remine_quantity = $bills_sells_quantity - $bills_return_quantity;
            $remainQuantity += $bills_remine_quantity;

            $totalSellsQuantity += $bills_sells_quantity;
            $totalsell = round($totalsell, 2);
            $totalRemainQuantity += $remainQuantity;
            $totalReturnSellsQuantity += $bills_return_quantity;

            //$productproft += ($totalsell - $total_profit);
            $productproft += $total_profit;

            $productproft = round($productproft, 2);



            //# end

            $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
            $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
            $smarty->assign("remainQuantity" . $h . "", $remainQuantity);
            $smarty->assign("totalsellwithoutproft" . $h . "", $totalsellwithoutproft);
            $smarty->assign("totalsell" . $h . "", round($totalsell, 2));
            $smarty->assign("productproft" . $h . "", round($productproft, 2));
            $smarty->assign("totalsell_withoutdiscount" . $h . "", $totalsell_withoutdiscount);
            $smarty->assign("productCatName" . $h . "", $productCatName);


            $totalproductproft += $productproft;
            $totaltotalsell += $totalsell;
            $total_sell_without_discount += $totalsell_withoutdiscount;
        }



        $h++;
    }



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

//select all data from sellbill datail by product id
function showSellByProductAndDate($productId, $startDate, $endDate) {
    global $mySellbilldetailEx;

    $sumQuantity = $mySellbilldetailEx->queryQuantityWithProductAndDate($productId, $startDate, $endDate);
    return $sumQuantity;
}

// show the form by date
function showAditionalSellByProductAndDate($productId, $startDate, $endDate) {
    global $mySellandruternbilldetailEx;

    $sumQuantity = $mySellandruternbilldetailEx->queryQuantityWithProductAndSellTypeAndDate($productId, 0, $startDate, $endDate);
    return $sumQuantity;
}

function showReturnSellByProductAndDate($productId, $startDate, $endDate) {
    global $myReturnsellbilldetailEx;

    $sumQuantity = $myReturnsellbilldetailEx->queryQuantityWithProductAndDate($productId, $startDate, $endDate);
    return $sumQuantity;
}

function showAditionalReturnSellByProductAndProduct($productId, $startDate, $endDate) {
    global $mySellandruternbilldetailEx;

    $sumQuantity = $mySellandruternbilldetailEx->queryQuantityWithProductAndSellTypeAndDate($productId, 1, $startDate, $endDate);
    return $sumQuantity;
}

//select data from product by productcatid
function loadProductByProductcatIdAndDate($productcatId, $startDate, $endDate) {


    global $myProductEx;
    global $myProductRecord;
    global $smarty;
    global $billsProductsEX;
    global $billsReturnProductsEX;

    $totalRemainQuantity = 0;
    $remainQuantity = 0;

    //$productData=$myProductEx->queryWithProductcatIdAndConditions($productcatId);
    $productData = $myProductEx->queryByProductCatIdEX($productcatId);
    $smarty->assign("productData", $productData);


    $h = 1;
    $totaltotalsell = 0;
    $totalproductproft = 0;
    $total_sell_without_discount = 0;
    foreach ($productData as $product) {

        $productId = $product->productId;


        $totalsell = $productproft = $total_profit = $totalsell_withoutdiscount = $bills_sells_quantity = 0;
        //# مردودات
        $get_discount_return = $product_total_return = $bills_return_quantity = 0;

        //
        $sellsQuantity = showSellByProductAndDate($productId, $startDate, $endDate);
        $aditionalSellsQuantity = showAditionalSellByProductAndDate($productId, $startDate, $endDate);

        $totalSellsQuantity = $sellsQuantity + $aditionalSellsQuantity;

        $returnSellQuantity = showReturnSellByProductAndDate($productId, $startDate, $endDate);
        $aditionalReturnSellsQuantity = showAditionalReturnSellByProductAndProduct($productId, $startDate, $endDate);

        $totalReturnSellsQuantity = $returnSellQuantity + $aditionalReturnSellsQuantity;

        $remainQuantity = $totalSellsQuantity - $totalReturnSellsQuantity;

        //إجمالي عدد القطع المباعة
        $totalRemainQuantity += $remainQuantity;

        $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
        $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
        $smarty->assign("remainQuantity" . $h . "", $remainQuantity);

        if ($totalSellsQuantity > 0 || $totalReturnSellsQuantity > 0) {
            $allreturndata = getProductInBillByDateAndProductId($startDate, $endDate, $productId);
            $productproft = $allreturndata[0];
            $totalsell = $allreturndata[1];
            $totalsell_withoutdiscount = $allreturndata[3];

            $totalsellwithoutproft = $allreturndata[2];
            $smarty->assign("totalsellwithoutproft" . $h . "", $totalsellwithoutproft);
            $smarty->assign("totalsell" . $h . "", round($totalsell, 2));
            $smarty->assign("productproft" . $h . "", round($productproft, 2));
            $smarty->assign("totalsell_withoutdiscount" . $h . "", $totalsell_withoutdiscount);

            $totaltotalsell = $totaltotalsell + $totalsell;
            $totalproductproft = $totalproductproft + round($productproft, 2);
            $total_sell_without_discount = $total_sell_without_discount + $totalsell_withoutdiscount;
        }


        //# start bills
        $where = " billsproducts.productid = " . $productId . " and date(bills.billdate) >= '" . $startDate . "' and date(bills.billdate) <= '" . $endDate . "' ";
        $bills_details = $billsProductsEX->load_billproducts_and_bills_by_productid($where);

        if (!empty($bills_details)) {


            //# call function.
            $productCatName = '';
            $productCatId = $product->productCatId;
            $productCatName = get_parentCat_of_productCat($productCatId, $productCatName);
            $productCatName = get_childCat_of_productCat($productCatId, $productCatName);



            //# مبيعات بصاريات
            foreach ($bills_details as $detail_val) {
                //# الكمية المباعة
                $bills_sells_quantity += $detail_val->productno;

                //# get discount to calculate sell price .
                //$get_discount = (( $detail_val->productprice * $detail_val->productno ) / $detail_val->netbillvalue ) * $detail_val->discountvalue;
                $get_discount = ( $detail_val->productprice * $detail_val->productno ) - $detail_val->netbillvalue;

                //# مبلغ المبيعات
                $product_total_sell = ( $detail_val->productprice * $detail_val->productno ) - $get_discount;
                $totalsell += $product_total_sell;

                //# صافى الربح
                $total_profit += $product_total_sell - ( $detail_val->productBuyPrice * $detail_val->productno );

                //# المبيعات بدون خصومات
                $totalsell_withoutdiscount += ( $detail_val->netbillvalue + $get_discount );
            }

            //# مردود مبيعات بصاريات
            $where_return = " billsreturnproducts.productid = " . $productId . " and date(billsreturn.date) >= '" . $startDate . "' and date(billsreturn.date) <= '" . $endDate . "' ";
            $return_bill_detial = $billsReturnProductsEX->load_billproducts_and_bills_by_productid($where_return);

            foreach ($return_bill_detial as $detail_val) {
                //# الكمية المباعة
                $bills_return_quantity += $detail_val->productno;

                //# get discount to calculate sell price .
                //$get_discount_return = (( $detail_val->productprice * $detail_val->productno ) / $detail_val->netbillvalue ) * $detail_val->discountvalue;
                $get_discount_return = ( $detail_val->productprice * $detail_val->productno ) - $detail_val->netbillvalue;

                //# مبلغ مردود المبيعات
                $product_total_return = ( $detail_val->productprice * $detail_val->productno ) - $get_discount;
                $totalsell -= $product_total_return;

                //# المبيعات بدون خصومات
                $totalsell_withoutdiscount -= ( $detail_val->returnedprice + $get_discount );
            }

            //# 	صافى الكميه المباعه
            $bills_remine_quantity = $bills_sells_quantity - $bills_return_quantity;
            $remainQuantity += $bills_remine_quantity;

            $totalSellsQuantity += $bills_sells_quantity;
            $totalsell = round($totalsell, 2);
            $totalRemainQuantity += $remainQuantity;
            $totalReturnSellsQuantity += $bills_return_quantity;

            //$productproft += ($totalsell - $total_profit);
            $productproft += $total_profit;
            $productproft = round($productproft, 2);

            //# end

            $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
            $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
            $smarty->assign("remainQuantity" . $h . "", $remainQuantity);
            $smarty->assign("totalsellwithoutproft" . $h . "", $totalsellwithoutproft);
            $smarty->assign("totalsell" . $h . "", round($totalsell, 2));
            $smarty->assign("productproft" . $h . "", round($productproft, 2));
            $smarty->assign("totalsell_withoutdiscount" . $h . "", $totalsell_withoutdiscount);
            $smarty->assign("productCatName" . $h . "", $productCatName);


            $totalproductproft += $productproft;
            $totaltotalsell += $totalsell;
            $total_sell_without_discount += $totalsell_withoutdiscount;
        }


        $h++;
    }
    $smarty->assign("totalproductproft", $totalproductproft);
    $smarty->assign("totalRemainQuantity", $totalRemainQuantity);
    $smarty->assign("totaltotalsell", $totaltotalsell);
    $smarty->assign("total_sell_without_discount", $total_sell_without_discount);
}

function loadProductByProductcatIdAndDatemain($maincatId, $startDate, $endDate) {


    global $myProductEx;
    global $myProductRecord;
    global $myProductcatEx;


    $totaltotalsellmain = 0;
    $totalproductproftmain = 0;
    $totalRemainQuantitytmain = 0;


    global $smarty;
    $allsubcat = $myProductcatEx->queryAllChildrenwithparentExt($maincatId);

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

    $i = 1;


    foreach ($allsubcat as $myallsubcat) {


        $productcatId = $myallsubcat->productCatId;

        $productData = $myProductEx->queryByProductCatIdEX($productcatId);
        //$smarty->assign("productData", $productData);

        $h = 1;
        $totaltotalsell = 0;
        $totalproductproft = 0;
        $totalRemainQuantity = 0;
        $remainQuantity = 0;
        $totaltotalReturnSellsQuantity = 0;
        $totaltotalSellsQuantity = 0;

        $totalsell = 0;
        $productproft = 0;
        $totalsellwithoutproft = 0;
        $totalSellsQuantity = 0;
        $totalReturnSellsQuantity = 0;
        $totalsell_withoutdiscount = 0;



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

            //
            $sellsQuantity = showSellByProductAndDate($productId, $startDate, $endDate);
            $aditionalSellsQuantity = showAditionalSellByProductAndDate($productId, $startDate, $endDate);

            $totalSellsQuantity = $sellsQuantity + $aditionalSellsQuantity;

            $returnSellQuantity = showReturnSellByProductAndDate($productId, $startDate, $endDate);
            $aditionalReturnSellsQuantity = showAditionalReturnSellByProductAndProduct($productId, $startDate, $endDate);

            $totalReturnSellsQuantity = $returnSellQuantity + $aditionalReturnSellsQuantity;
            $totaltotalReturnSellsQuantity = $totaltotalReturnSellsQuantity + $totalReturnSellsQuantity;

            $remainQuantity = $totalSellsQuantity - $totalReturnSellsQuantity;
            $totaltotalSellsQuantity = $totaltotalSellsQuantity + $totalSellsQuantity;
            //إجمالي عدد القطع المباعة
            $totalRemainQuantity += $remainQuantity;


            if ($totalSellsQuantity > 0 || $totalReturnSellsQuantity > 0) {
                $allreturndata = getProductInBillByDateAndProductId($startDate, $endDate, $productId);
                $productproft = $allreturndata[0];
                $totalsell = $allreturndata[1];
                $totalsellwithoutproft = $allreturndata[2];
                $totalsell_withoutdiscount = $allreturndata[3];





                $totaltotalsell = $totaltotalsell + $totalsell;
                $totalproductproft = $totalproductproft + $productproft;
            }


            $h++;
        }



        $totalproductproftmain = $totalproductproftmain + $totalproductproft;
        $totalRemainQuantitytmain = $totalRemainQuantitytmain + $totalRemainQuantity;
        $totaltotalsellmain = $totaltotalsellmain + $totaltotalsell;
        $total_sell_without_discount = $total_sell_without_discount + $totalsell_withoutdiscount;

        $smarty->assign("totalproductproft" . $i, round($totalproductproft), 2);
        $smarty->assign("totalRemainQuantity" . $i, round($totalRemainQuantity, 2));
        $smarty->assign("totaltotalsell" . $i, round($totaltotalsell, 2));
        $smarty->assign("totaltotalReturnSellsQuantity" . $i . "", round($totaltotalReturnSellsQuantity, 2));
        $smarty->assign("totaltotalSellsQuantity" . $i . "", round($totaltotalSellsQuantity, 2));
        $smarty->assign("totalsell_withoutdiscount" . $h . "", $totalsell_withoutdiscount);


        $i++;
    }





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

    $smarty->assign("totalproductproftmain", round($totalproductproftmain, 2));
    $smarty->assign("totalRemainQuantitytmain", round($totalRemainQuantitytmain, 2));
    $smarty->assign("totaltotalsellmain", round($totaltotalsellmain, 2));
}

function loadProductByProductcatIdmain($productcatId) {
    global $myProductEx;
    global $myProductRecord;
    global $smarty;

    $totalRemainQuantity = 0;
    $remainQuantity = 0;

    //$productData=$myProductEx->queryWithProductcatIdAndConditions($productcatId);
    $productData = $myProductEx->queryByProductparentCatIdExt($productcatId);

    //print_r($queryByProductCatIdExt);
    $smarty->assign("productData", $productData);

    $h = 1;
    foreach ($productData as $product) {
        $productId = $product->productId;


        $sellsQuantity = showSellByProduct($productId);
        $aditionalSellsQuantity = showAditionalSellByProduct($productId);

        $totalSellsQuantity = $sellsQuantity + $aditionalSellsQuantity;

        $returnSellQuantity = showReturnSellByProduct($productId);
        $aditionalReturnSellsQuantity = showAditionalReturnSellByProduct($productId);

        $totalReturnSellsQuantity = $returnSellQuantity + $aditionalReturnSellsQuantity;

        $remainQuantity = $totalSellsQuantity - $totalReturnSellsQuantity;

        //إجمالي عدد القطع المباعة
        $totalRemainQuantity += $remainQuantity;

        $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
        $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
        $smarty->assign("remainQuantity" . $h . "", $remainQuantity);

        $h++;
    }
    $smarty->assign("totalRemainQuantity", $totalRemainQuantity);
}

//select data from product
function loadProductByAllCategoriesAndDate($startDate, $endDate) {
    global $myProductEx;
    global $myProductRecord;
    global $smarty;
    global $billsProductsEX;
    global $billsReturnProductsEX;

    $totalRemainQuantity = 0;
    $remainQuantity = 0;

    $productData = $myProductEx->queryByAllCategories();
    $smarty->assign("productData", $productData);

    //# call function.
    //$productCatName = get_childCat_of_productCat($productcatId, $productCatName);

    $h = 1;
    $totaltotalsell = 0;
    $totalproductproft = 0;
    foreach ($productData as $product) {



        $totalsell = $productproft = $total_profit = $totalsell_withoutdiscount = $bills_sells_quantity = 0;
        //# مردودات
        $get_discount_return = $product_total_return = $bills_return_quantity = 0;

        $productId = $product->productId;

        $sellsQuantity = showSellByProductAndDate($productId, $startDate, $endDate);
        $aditionalSellsQuantity = showAditionalSellByProductAndDate($productId, $startDate, $endDate);

        $totalSellsQuantity = $sellsQuantity + $aditionalSellsQuantity;

        $returnSellQuantity = showReturnSellByProductAndDate($productId, $startDate, $endDate);
        $aditionalReturnSellsQuantity = showAditionalReturnSellByProductAndProduct($productId, $startDate, $endDate);

        $totalReturnSellsQuantity = $returnSellQuantity + $aditionalReturnSellsQuantity;

        $remainQuantity = $totalSellsQuantity - $totalReturnSellsQuantity;

        //إجمالي عدد القطع المباعة
        $totalRemainQuantity += $remainQuantity;

        $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
        $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
        $smarty->assign("remainQuantity" . $h . "", $remainQuantity);
        if ($totalSellsQuantity > 0 || $totalReturnSellsQuantity > 0) {
            $allreturndata = getProductInBillByDateAndProductId($startDate, $endDate, $productId);
            $productproft = $allreturndata[0];
            $totalsell = $allreturndata[1];
            $totalsellwithoutproft = $allreturndata[2];
            $totalsell_withoutdiscount = $allreturndata[3];

            $smarty->assign("totalsellwithoutproft" . $h . "", $totalsellwithoutproft);
            $smarty->assign("totalsell" . $h . "", round($totalsell, 2));
            $smarty->assign("productproft" . $h . "", round($productproft, 2));
            $smarty->assign("totalsell_withoutdiscount" . $h . "", $totalsell_withoutdiscount);

            $totaltotalsell = $totaltotalsell + $totalsell;
            $totalproductproft = $totalproductproft + $productproft;
            $total_sell_without_discount = $total_sell_without_discount + $totalsell_withoutdiscount;
        }


        //# start bills
        $where = " billsproducts.productid = " . $productId . " and date(bills.billdate) >= '" . $startDate . "' and date(bills.billdate) <= '" . $endDate . "' ";
        $bills_details = $billsProductsEX->load_billproducts_and_bills_by_productid($where);



        if (!empty($bills_details)) {

            $productCatName = '';
            $productCatId = $product->productCatId;
            $productCatName = get_parentCat_of_productCat($productCatId, $productCatName);

            //# مبيعات بصاريات
            foreach ($bills_details as $detail_val) {
                //# الكمية المباعة
                $bills_sells_quantity += $detail_val->productno;

                //# get discount to calculate sell price .
                //$get_discount = (( $detail_val->productprice * $detail_val->productno ) / $detail_val->netbillvalue ) * $detail_val->discountvalue;
                $get_discount = ( $detail_val->productprice * $detail_val->productno ) - $detail_val->netbillvalue;

                //# مبلغ المبيعات
                $product_total_sell = ( $detail_val->productprice * $detail_val->productno ) - $get_discount;
                $totalsell += $product_total_sell;

                //# صافى الربح
                $total_profit += $product_total_sell - ( $detail_val->productBuyPrice * $detail_val->productno );

                //# المبيعات بدون خصومات
                $totalsell_withoutdiscount += ( $detail_val->netbillvalue + $get_discount );
            }

            //# مردود مبيعات بصاريات
            $where_return = " billsreturnproducts.productid = " . $productId . " and date(billsreturn.date) >= '" . $startDate . "' and date(billsreturn.date) <= '" . $endDate . "' ";
            $return_bill_detial = $billsReturnProductsEX->load_billproducts_and_bills_by_productid($where_return);

            foreach ($return_bill_detial as $detail_val) {
                //# الكمية المباعة
                $bills_return_quantity += $detail_val->productno;

                //# get discount to calculate sell price .
                //$get_discount_return = (( $detail_val->productprice * $detail_val->productno ) / $detail_val->netbillvalue ) * $detail_val->discountvalue;
                $get_discount_return = ( $detail_val->productprice * $detail_val->productno ) - $detail_val->netbillvalue;

                //# مبلغ مردود المبيعات
                $product_total_return = ( $detail_val->productprice * $detail_val->productno ) - $get_discount;
                $totalsell -= $product_total_return;

                //# المبيعات بدون خصومات
                $totalsell_withoutdiscount -= ( $detail_val->returnedprice + $get_discount );
            }

            //# 	صافى الكميه المباعه
            $bills_remine_quantity = $bills_sells_quantity - $bills_return_quantity;
            $remainQuantity += $bills_remine_quantity;

            $totalSellsQuantity += $bills_sells_quantity;
            $totalsell = round($totalsell, 2);
            $totalRemainQuantity += $remainQuantity;
            $totalReturnSellsQuantity += $bills_return_quantity;

            //$productproft += ($totalsell - $total_profit);
            $productproft += $total_profit;

            $productproft = round($productproft, 2);

            //# end

            $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
            $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
            $smarty->assign("remainQuantity" . $h . "", $remainQuantity);
            $smarty->assign("totalsellwithoutproft" . $h . "", $totalsellwithoutproft);
            $smarty->assign("totalsell" . $h . "", round($totalsell, 2));
            $smarty->assign("productproft" . $h . "", round($productproft, 2));
            $smarty->assign("totalsell_withoutdiscount" . $h . "", $totalsell_withoutdiscount);
            $smarty->assign("productCatName" . $h . "", $productCatName);

            $totalproductproft += $productproft;
            $totaltotalsell += $totalsell;
            $total_sell_without_discount += $totalsell_withoutdiscount;
        }


        $h++;
    }
    $smarty->assign("totalproductproft", $totalproductproft);
    $smarty->assign("totalRemainQuantity", $totalRemainQuantity);
    $smarty->assign("totaltotalsell", $totaltotalsell);
    $smarty->assign("total_sell_without_discount", $total_sell_without_discount);
}

//function loadProductCat()
//	{
//		global $myProductcatEx;
//
//		$productCatParentData=$myProductcatEx->queryProductcatParentWithCondition();
//
//
//
//		foreach($productCatParentData as $productCatParent)
//		{
//			print("parentname".$productCatParent->productCatName."<br/>");
//			print("parentid".$productCatParent->productCatId."<br/>");
//
//			$productcatData=loadProductCatNameById($productCatParent->productCatId,$productCatParent->productCatName);
//
//			$productCatParent->productCatName=$productCatParent->productCatName.'/'.$productcatData[0];
//
//			$productCatParent->productCatName=$productcatData[1];
//
//			print("endddddddddddddddddddddddddddd<br/>");
//			print(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><br/><br/>");
//		}
//
//		return $productCatParentData;
//	}
//
//
//	// select from Product tbl
//	function loadProductCatNameById($productCatId,$productcatName)
//	{
//		//to use the variable out side the funcion
//		global $myProductcatEx;
//
//		$productcatNamex=$productcatName;
//		$productCatIdx=$productCatId;
//
//		// select all data from producttbl
//		$productcatData=$myProductcatEx->queryWithParentAndCondition($productCatId);
//		print("<pre/>");
//		print_r($productcatData);
//		print("<pre/>");
//
//		if(count($productcatData) > 0)
//		{
//			print("yesssssssssssssssssss"."<br/>");
//			foreach($productcatData as $productcat)
//			{
//				print("oldname : ".$productcatNamex."<br/>");
//				print("oldid : ".$productCatIdx."<br/>");
//
//				$productcatNamex=$productcatNamex."/".$productcat->productCatName;
//				$productCatIdx=$productcat->productCatId;
//
//				print("newname : ".$productcatNamex."<br/>");
//				print("newId : ".$productCatIdx."<br/><br/>");
//				print("-------------------------------------<br/>");
//
//return loadProductCatNameById($productCatIdx,$productcatNamex,2);
//
//			}
//
//
//		}
//
//
//		return array($productCatIdx,$productcatNamex);
//	}
// select from productcatdata tbl
function loadProductCat() {
    global $myProductcatEx;

    $productcatData = $myProductcatEx->queryProductcatInProduct();

    foreach ($productcatData as $productcat) {
        $productcatName;
        $productcat->productCatName = loadProductCatNameById($productcat->productCatId, $productcatName, 1);
    }
    return $productcatData;
}

function loadProductCatmain() {
    global $myProductcatEx;

    $productcatData = $myProductcatEx->queryProductcatInProductmain();

    foreach ($productcatData as $productcat) {
        $productcatName;
        $productcat->productCatName = loadProductCatNameById($productcat->productCatId, $productcatName, 1);
    }
    return $productcatData;
}

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

?>