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


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

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

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

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

    $productSearchId = $_REQUEST['productId'];
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];

    ##
    $sizeId = 0;
    $colorId = 0;
    $sizeColorStoreDetailId = 0;
    if (strpos($productSearchId, "hasSizeColor") !== false) {
        $productIdComplex = explode('-', str_replace("hasSizeColor", "", $productSearchId));
        $productSearchId = $productIdComplex[0];
        $sizeId = $productIdComplex[1];
        $colorId = $productIdComplex[2];
    }

    $Programsettingdata = $ProgramsettingDAO->load(1);
    $smarty->assign("Programsettingdata", $Programsettingdata);

    if (isset($productSearchId) && $productSearchId != "-1" && $productSearchId != "") {

        if (!empty($productSearchId)) {
            $productData = $myProductEx->loadProduct($productSearchId);
            $message .= "تقرير حركة منتج <h4>" . $productData->productName . "/ " . $productData->productCatName;
        }
        $Programsetting = $ProgramsettingDAO->load(1);
        if (!empty($startDate)) {
            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
                $startDate = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($startDate)));
            } else {
                $startDate = $startDate . " 00:00:00";
            }
            $message .= "</h4> والتاريخ من " . $startDate;
        }
        if (!empty($endDate)) {
            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)));
            } else {
                $endDate = $endDate . ' 23:59:59';
            }
            $message .= " إلى " . $endDate;
        }



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

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

        getProductInBillByDateAndProductId($startDate, $endDate, $productSearchId);
    }
    //here the smarty templates
    $smarty->display("profitproductview/productmovment.html");
    $smarty->assign("settlementstoreshow", 1);
}
//here the global templates
$smarty->display("footer.html");

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

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


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

    $productData = $myProductRecord->load($productId);
    $smarty->assign("productData", $productData);


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

        $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 = 0;
        if ($storreportdata->tablename != "buyBillController.php")
            $productafter = (int) $storreportdata->productafter;
        $smarty->assign("productafter" . $i, $productafter);

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

        if (!empty($endDate)) {
            $queryString .= ' AND storereportdate <= "' . $endDate . '"';
        }
        $settlementAdd = $settlementSub = $settleFinal = 0;
        
        $settlementAdd = R::getCell('select if(sum(productquantity) > 0,sum(productquantity),0) from storereport where storereporttype = 0 AND (tablename ="settlementstoreController.php" OR tablename ="inventoryController.php")' . $queryString);
        $settlementSub = R::getCell('select if(sum(productquantity) > 0,sum(productquantity),0) from storereport where storereporttype = 1 AND (tablename ="settlementstoreController.php" OR tablename ="inventoryController.php")' . $queryString);
        $smarty->assign("settlementAdd" . $i, $settlementAdd);
        $smarty->assign("settlementSub" . $i, $settlementSub);

        $settleFinal = $settlementAdd - $settlementSub;
        $smarty->assign("settleFinal" . $i, $settleFinal);


        $final = 0;
        // $lastStoreReport = R::getRow('SELECT * FROM storereport where 1
        //                             ' . $queryString . '
        //                             order by storereportid desc');
        // $final += $lastStoreReport['productafter']; 
        $final = ($buydif + $productafter + $settleFinal) - $selldif;
        $smarty->assign("final" . $i, $final);
        $i++;
    }
}

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 . ')';
        $queryString .= ' AND sellbilldetail.storeid = ' . $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;
}
