<?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");
//Storereport
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');
//Storedetail
require_once('../models/dao/StoredetailDAO.class.php');
require_once('../models/dto/Storedetail.class.php');
require_once('../models/mysql/StoredetailMySqlDAO.class.php');
require_once('../models/mysql/ext/StoredetailMySqlExtDAO.class.php');
//Store
require_once('../models/dao/StoreDAO.class.php');
require_once('../models/dto/Store.class.php');
require_once('../models/mysql/StoreMySqlDAO.class.php');
require_once('../models/mysql/ext/StoreMySqlExtDAO.class.php');
//Product
require_once('../models/dao/ProductDAO.class.php');
require_once('../models/dto/Product.class.php');
require_once('../models/mysql/ProductMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductMySqlExtDAO.class.php');

//Programsetting
require_once('../models/dao/ProgramsettingsDAO.class.php');
require_once('../models/dto/Programsetting.class.php');
require_once('../models/mysql/ProgramsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/ProgramsettingsMySqlExtDAO.class.php');

//Client
require_once('../models/dao/ClientDAO.class.php');
require_once('../models/dto/Client.class.php');
require_once('../models/mysql/ClientMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientMySqlExtDAO.class.php');
//Supplier
require_once('../models/dao/SupplierDAO.class.php');
require_once('../models/dto/Supplier.class.php');
require_once('../models/mysql/SupplierMySqlDAO.class.php');
require_once('../models/mysql/ext/SupplierMySqlExtDAO.class.php');

//
require_once('../models/dao/SizecolorDAO.class.php');
require_once('../models/dto/Sizecolor.class.php');
require_once('../models/mysql/SizecolorMySqlDAO.class.php');
require_once('../models/mysql/ext/SizecolorMySqlExtDAO.class.php');
//
require_once('../models/dao/SizecolorstoredetailDAO.class.php');
require_once('../models/dto/Sizecolorstoredetail.class.php');
require_once('../models/mysql/SizecolorstoredetailMySqlDAO.class.php');
require_once('../models/mysql/ext/SizecolorstoredetailMySqlExtDAO.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');


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

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

//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');
//Usergroup
require_once('../models/dao/UsergroupDAO.class.php');
require_once('../models/dto/Usergroup.class.php');
require_once('../models/mysql/UsergroupMySqlDAO.class.php');
require_once('../models/mysql/ext/UsergroupMySqlExtDAO.class.php');

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

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

  Controller Name :- StorereportCTRL تقرير حركة المنتجات بالمخازن

  OPERTATION in Controller

  1-load storerawmaterials data
  2-load rawmaterials data
  3-display show form

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

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

//here goes the instances and general variables
$StoremovementDAO = new StoremovementMySqlDAO();
//Storereport
$myStorereportEx = new StorereportMySqlExtDAO();
//Storedetail
$myStoredetailEx = new StoredetailMySqlExtDAO();
$myStoredetailRecord = new StoredetailMySqlDAO();
//Store
$myStoreRecord = new StoreMySqlDAO();
$myStoreEx = new StoreMySqlExtDAO();

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

$ProgramsettingDAO = new ProgramsettingsMySqlDAO();

//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientExt = new ClientMySqlExtDAO();
//Supplier
$supplier = new Supplier();
$supplierDAO = new SupplierMySqlDAO();
$supplierExt = new SupplierMySqlExtDAO();
//
$sizeColor = new Sizecolor();
$sizeColorDAO = new SizecolorMySqlDAO();
$sizeColorEX = new SizecolorMySqlExtDAO();

$youtubeLink = new YoutubeLink();
$youtubeLinkDAO = new YoutubeLinkMySqlDAO();
$youtubeLinkEX = new YoutubeLinkMySqlExtDAO();
//
$sizeColorStoreDetail = new Sizecolorstoredetail();
$sizeColorStoreDetailDAO = new SizecolorstoredetailMySqlDAO();
$sizeColorStoreDetailEX = new SizecolorstoredetailMySqlExtDAO();

//Sellbilldetail
$mySellbilldetail = new Sellbilldetail();
$mySellbilldetailRecord = new SellbilldetailMySqlDAO();
$mySellbilldetailEx = new SellbilldetailMySqlExtDAO();

//Returnsellbilldetail
$myReturnsellbilldetail = new Returnsellbilldetail();
$myReturnsellbilldetailRecord = new ReturnsellbilldetailMySqlDAO();
$myReturnsellbilldetailEx = new ReturnsellbilldetailMySqlExtDAO();

//Buybilldetail
$buyBillDetail = new Buybilldetail();
$buyBillDetailDAO = new BuybilldetailMySqlDAO();
$buyBillDetailExt = new BuybilldetailMySqlExtDAO();


//Returnbuybilldetail
$returnBuyBillDetail = new Returnbuybilldetail();
$returnBuyBillDetailDAO = new ReturnbuybilldetailMySqlDAO();
$returnBuyBillDetailExt = new ReturnbuybilldetailMySqlExtDAO();

$mySellbillRecord = new SellbillMySqlDAO();
$myReturnsellbillRecord = new ReturnsellbillMySqlDAO();
$mySellbillandruternRecord = new SellbillandruternMySqlDAO();
$buyBillDAO = new BuybillMySqlDAO();
$returnBuyBillDAO = new ReturnbuybillMySqlDAO();
$buyAndReturnBillDAO = new BuyandruternbillMySqlDAO();
$billsDAO = new BillsMySqlDAO();
$billsReturnDAO = new BillsreturnMySqlDAO();
//Usergroup
$Usergroup = new Usergroup();
$UsergroupDAO = new UsergroupMySqlDAO();
$UsergroupEX = new UsergroupMySqlExtDAO();


//
$Usergroupdata = $UsergroupDAO->load($_SESSION['usergroupid']);
$smarty->assign("Usergroupdata", $Usergroupdata);
//check and use the condition that suite this action
if ($do == "show" || empty($do)) {
    //here the permission check
    include_once("../public/authentication.php");

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




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

    $productId = $_REQUEST['productId'];
    $storeId = $_REQUEST['storeId'];
    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $order = $_REQUEST['order'];
    $bure = $_REQUEST['bure'];

    if (isset($productId) && $productId != "-1" && $productId != "" && isset($storeId) && $storeId != "-1" && isset($startDate) && isset($endDate) && $startDate != "" && $endDate != "") {
        if (strpos($productId, "hasSizeColor") !== false) {
            $productIdComplex = explode('-', str_replace("hasSizeColor", "", $productId));
            $productidTemp = $productIdComplex[0];
            $sizeId = $productIdComplex[1];
            $colorId = $productIdComplex[2];
            $size = $sizeColorDAO->load($sizeId);
            $color = $sizeColorDAO->load($colorId);
            $myproductdata = $myProductRecord->load($productidTemp);
            if (!is_null($size->name) && !empty($size->name)) {
                $myproductdata->productName .= '/' . $size->name;
            }
            if (!is_null($color->name) && !empty($color->name)) {
                $myproductdata->productName .= '/' . $color->name;
            }
        } else {
            $myproductdata = $myProductRecord->load($productId);
        }

        $mystoredata = $myStoreRecord->load($storeId);

        $message = "تقرير حركه المخازن من المخزن : " . $mystoredata->storeName . "<br>  للمنتج :" . $myproductdata->productName . "<br>من تاريخ : " . $startDate . " إلى تاريخ : " . $endDate;
        $smarty->assign('message', $message);

        showByStoreAndProductAndDate($productId, $storeId, $startDate, $endDate, $order, $bure);
        //print_r($storereportData);
        $sumValue = sumCreditByProductIdAndStoreId($productId, $storeId);
    }

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

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

    $smarty->assign('sumValue', $sumValue);

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

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

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

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

function sumCredit()
{
    global $myStoredetailEx;
    global $ProgramsettingDAO;
    $Programsetting = $ProgramsettingDAO->load(1);

    $storedetailData = $myStoredetailEx->queryWithOrder();

    //get the sum of multiplying for productquantity and productunitprice
    $sumValue = 0;
    foreach ($storedetailData as $storedetail) {
        switch ($Programsetting->Inventoryevaluation) {
            case "first":
                $buyprice = (float) $storedetail->productBuyPrice;
                break;
            case "last":
                $buyprice = (float) $storedetail->lastbuyprice;
                break;
            case "mean":
                $buyprice = (float) $storedetail->meanbuyprice;
                break;
            case "last_discount":
                $buyprice = (float) $storedetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyprice = (float) $storedetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyprice = (float) $storedetail->overAllAveragePrice;
                break;
            case "tax":
                $buyprice = (float) $storedetail->lastbuyprice_withTax;
                break;
            case "mean_tax":
                $buyprice = (float) $storedetail->meanbuyprice_withTax;
                break;
        }
        //        $productBuyPrice = $storedetail->productBuyPrice;
        $productBuyPrice = $buyprice;
        $productQuantity = $storedetail->productquantity;
        $SumProductPrice = $productBuyPrice * $productQuantity;

        $sumValue = $SumProductPrice + $sumValue;
    }
    return $sumValue;
}

function sumCreditByStoreId($StoreId)
{
    global $myStoredetailEx;
    global $ProgramsettingDAO;

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

    $storedetailData = $myStoredetailEx->queryWithStoreId($StoreId);

    //get the sum of multiplying for productquantity and productunitprice
    $sumValue = 0;
    foreach ($storedetailData as $storedetail) {

        switch ($Programsetting->Inventoryevaluation) {
            case "first":
                $buyprice = (float) $storedetail->productBuyPrice;
                break;
            case "last":
                $buyprice = (float) $storedetail->lastbuyprice;
                break;
            case "mean":
                $buyprice = (float) $storedetail->meanbuyprice;
                break;
            case "last_discount":
                $buyprice = (float) $storedetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyprice = (float) $storedetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyprice = (float) $storedetail->overAllAveragePrice;
                break;
            case "tax":
                $buyprice = (float) $storedetail->lastbuyprice_withTax;
                break;
            case "mean_tax":
                $buyprice = (float) $storedetail->meanbuyprice_withTax;
                break;
        }

        //        $productBuyPrice = $storedetail->productBuyPrice;
        $productBuyPrice = $buyprice;
        $productQuantity = $storedetail->productquantity;
        $SumProductPrice = $productBuyPrice * $productQuantity;

        $sumValue = $SumProductPrice + $sumValue;
    }
    return $sumValue;
}

function sumCreditByProductId($ProductId)
{
    global $myStoredetailEx;
    global $ProgramsettingDAO;

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

    $storedetailData = $myStoredetailEx->queryWithProductId($ProductId);

    //get the sum of multiplying for productquantity and productunitprice
    $sumValue = 0;
    foreach ($storedetailData as $storedetail) {

        switch ($Programsetting->Inventoryevaluation) {
            case "first":
                $buyprice = (float) $storedetail->productBuyPrice;
                break;
            case "last":
                $buyprice = (float) $storedetail->lastbuyprice;
                break;
            case "mean":
                $buyprice = (float) $storedetail->meanbuyprice;
                break;
            case "last_discount":
                $buyprice = (float) $storedetail->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyprice = (float) $storedetail->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyprice = (float) $storedetail->overAllAveragePrice;
                break;
            case "tax":
                $buyprice = (float) $storedetail->lastbuyprice_withTax;
                break;
            case "mean_tax":
                $buyprice = (float) $storedetail->meanbuyprice_withTax;
                break;
        }

        //        $productBuyPrice = $storedetail->productBuyPrice;
        $productBuyPrice = $buyprice;
        $productQuantity = $storedetail->productquantity;
        $SumProductPrice = $productBuyPrice * $productQuantity;

        $sumValue = $SumProductPrice + $sumValue;
    }
    return $sumValue;
}

function sumCreditByProductIdAndStoreId($ProductId, $storeId)
{
    global $myStoredetailEx;
    global $sizeColorStoreDetailDAO;
    global $sizeColorStoreDetailEX;
    global $myProductRecord;
    global $ProgramsettingDAO;

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

    //get the sum of multiplying for productquantity and productunitprice
    $sumValue = 0;
    $productId = $ProductId;
    if (strpos($productId, "hasSizeColor") !== false) {
        $productIdComplex = explode('-', str_replace("hasSizeColor", "", $productId));
        $productId = $productIdComplex[0];
        $sizeId = $productIdComplex[1];
        $colorId = $productIdComplex[2];
        $sizeColor = $sizeColorStoreDetailEX->getParcodeByProductSizeAndColorAndStoreEX($productId, $sizeId, $colorId, $storeId);
        $myproductdata = $myProductRecord->load($productId);

        switch ($Programsetting->Inventoryevaluation) {
            case "first":
                $buyprice = (float) $myproductdata->productBuyPrice;
                break;
            case "last":
                $buyprice = (float) $myproductdata->lastbuyprice;
                break;
            case "mean":
                $buyprice = (float) $myproductdata->meanbuyprice;
                break;
            case "last_discount":
                $buyprice = (float) $myproductdata->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyprice = (float) $myproductdata->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyprice = (float) $myproductdata->overAllAveragePrice;
                break;
            case "tax":
                $buyprice = (float) $myproductdata->lastbuyprice_withTax;
                break;
            case "mean_tax":
                $buyprice = (float) $myproductdata->meanbuyprice_withTax;
                break;
        }

        //        $productBuyPrice = $myproductdata->productBuyPrice;
        $productBuyPrice = $buyprice;
        $productQuantity = $sizeColor->quantity;
        $SumProductPrice = $productBuyPrice * $productQuantity;
    } else {
        $storedetailData = $myStoredetailEx->queryWithStoreAndProduct($ProductId, $storeId);

        switch ($Programsetting->Inventoryevaluation) {
            case "first":
                $buyprice = (float) $storedetailData->productBuyPrice;
                break;
            case "last":
                $buyprice = (float) $storedetailData->lastbuyprice;
                break;
            case "mean":
                $buyprice = (float) $storedetailData->meanbuyprice;
                break;
            case "last_discount":
                $buyprice = (float) $storedetailData->lastbuyprice_withDiscount;
                break;
            case "mean_discount":
                $buyprice = (float) $storedetailData->meanbuyprice_withDiscount;
                break;
            case "generalPrice":
                $buyprice = (float) $storedetailData->overAllAveragePrice;
                break;
            case "tax":
                $buyprice = (float) $storedetailData->lastbuyprice_withTax;
                break;
            case "mean_tax":
                $buyprice = (float) $storedetailData->meanbuyprice_withTax;
                break;
        }

        //        $productBuyPrice = $storedetailData->productBuyPrice;
        $productBuyPrice = $buyprice;
        $productQuantity = $storedetailData->productquantity;
        $SumProductPrice = $productBuyPrice * $productQuantity;
    }
    $sumValue = $SumProductPrice + $sumValue;
    return $sumValue;
}

// show the form by product name
function showByProductName($productid)
{
    global $myStorereportEx;

    $storereportData = $myStorereportEx->queryWithProductId($productid);
    return $storereportData;
}

// show the form by store name
function showBystoreName($storeid)
{
    global $myStorereportEx;

    $storereportData = $myStorereportEx->queryWithStoreId($storeid);
    return $storereportData;
}

// show the form by date
function showByDate($startDate, $endDate)
{
    global $myStorereportEx;

    $storereportData = $myStorereportEx->queryWithDate($startDate, $endDate);
    return $storereportData;
}

// show the form
function showByStoreAndProductAndDate($productid, $storeid, $startDate, $endDate, $order, $bure)
{
    global $StoremovementDAO;
    global $myStorereportEx;
    global $clientDAO;
    global $supplierDAO;
    global $buyBillDAO;
    global $returnBuyBillDAO;
    global $buyAndReturnBillDAO;
    global $mySellbillRecord;
    global $myReturnsellbillRecord;
    global $mySellbillandruternRecord;
    global $billsDAO;
    global $billsReturnDAO;
    global $myStoreRecord;
    global $mySellbilldetailEx;
    global $myReturnsellbilldetailEx;
    global $buyBillDetailExt;
    global $returnBuyBillDetailExt;
    global $smarty;

    $queryString = '';
    if (strpos($productid, "hasSizeColor") !== false) {
        $productIdComplex = explode('-', str_replace("hasSizeColor", "", $productid));
        $productid = $productIdComplex[0];
        $sizeId = $productIdComplex[1];
        $colorId = $productIdComplex[2];
        $queryString = " and  storereport.sizeid = $sizeId  and  storereport.colorid = $colorId ";
    }

    $orderByStr = '';
    if (isset($bure) && $bure == 1) {
        $orderByStr = 'order by storereportdate ' . $order . ',  storereportid ' . $order . '';
        //$storemovementData = $myStorereportEx->queryWithProductAndStoreAndDateandorderbure($productid, $storeid, $startDate, $endDate, $order, $queryString);
    } else {
        $orderByStr = 'order by storereportid ' . $order . '';
        //$storemovementData = $myStorereportEx->queryWithProductAndStoreAndDateandorder($productid, $storeid, $startDate, $endDate, $order, $queryString);
    }

    ##get qyery result
    $storemovementData = R::getAll('SELECT  CONCAT(  `tablename` ,  `storereportmodelid` , productquantity,`storereporttype` ) AS finalstring,storereport.*
            FROM storereport
            where storereport.productid = ' . $productid . '
            ' . $queryString . '
            and  storereport.storeid = ' . $storeid . '
            and  storereport.storereportdate >= "' . $startDate . '" and storereport.storereportdate <= "' . $endDate . '"
            ' . $orderByStr . '');
            list($productIDsArr, $sizeColorIDsArr, $storeIDsArr, $userIDsArr) = [[], [], [], []];
    foreach ($storemovementData as $value) {
        $productIDsArr[] = $value['productid'];
        $sizeColorIDsArr[] = $value['sizeid'];
        $sizeColorIDsArr[] = $value['colorid'];
        $storeIDsArr[] = $value['storeid'];
        $userIDsArr[] = $value['userid'];
    }
    
    $productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
    $sizeColorIDsArr = !empty($sizeColorIDsArr) ? array_unique($sizeColorIDsArr) : [0];
    $storeIDsArr = !empty($storeIDsArr) ? array_unique($storeIDsArr) : [0];
    $userIDsArr = !empty($userIDsArr) ? array_unique($userIDsArr) : [0];

    ##get product,sizename,colorname, storename
    $productDataArr = R::getAll('select productId,productName,product.productCatId,productSellUnitPrice from product where productId in(' . implode(',', $productIDsArr) . ') ');
    $productDataArr = customArrayIndexOne($productDataArr, 'productId');
    $storeDataArr = R::getAll('select storeId,storeName as storename from store where storeId in(' . implode(',', $storeIDsArr) . ') ');
    $storeDataArr = customArrayIndexOne($storeDataArr, 'storeId');
    $sizeColorDataArr = R::getAll('select id,name from sizecolor where id in(' . implode(',', $sizeColorIDsArr) . ') ');
    $sizeColorDataArr = customArrayIndexOne($sizeColorDataArr, 'id');
    $userDataArr = R::getAll('select userid,employeename from user where userid in(' . implode(',', $userIDsArr) . ') ');
    $userDataArr = customArrayIndexOne($userDataArr, 'userid');

    ## get buyprice , catname ##get names of client,supp,stores doing operation
    $dataTemp = array();
    list($proIDsArrSell, $proIDsArrRetSell, $proIDsArrBuy, $proIDsArrRetBuy, $productCatIdsArr, $proIDsArrSellAndRetSell, $modelIDsArrSellAndRetSell) = [[], [], [], [], []];
    list($modelIDsArrSell, $modelIDsArrRetSell, $modelIDsArrBuy, $modelIDsArrRetBuy) = [[], [], [], []];
    list($sellIDsArr, $retSellIDsArr, $sellAndRetIDsArr, $buyIDsArr, $retbuyIDsArr, $buyAndRetIDsArr, $storeMoveIDsArr) = [[], [], [], [], [], [], []];
    foreach ($storemovementData as $value) {
        $value['storeName'] = $storeDataArr[$value['storeid']]['storename'];
        $value['sizeName'] = $sizeColorDataArr[$value['sizeid']]['name'];
        $value['colorName'] = $sizeColorDataArr[$value['colorid']]['name'];
        $value['employeename'] = $userDataArr[$value['userid']]['employeename'];
        foreach ($productDataArr[$value['productid']] as $key1 => $value1) {
            $value[$key1] = $value1;
        }

        $productCatIdsArr[] = $value['productCatId'];

        ##for buy prices
        switch ($value['tablename']) {
            case 'sellbillController.php':
                $proIDsArrSell[] = $value['productid'];
                $modelIDsArrSell[] = $value['storereportmodelid'];
                break;
            case 'returnsellbillController.php':
                $proIDsArrRetSell[] = $value['productid'];
                $modelIDsArrRetSell[] = $value['storereportmodelid'];
                break;
            case 'sellbillandruternController.php':
                $proIDsArrSellAndRetSell[] = $value['productid'];
                $modelIDsArrSellAndRetSell[] = $value['storereportmodelid'];
                break;
            case 'buyBillController.php':
                $proIDsArrBuy[] = $value['productid'];
                $modelIDsArrBuy[] = $value['storereportmodelid'];
                break;
            case 'returnBuyBillController.php':
                $proIDsArrRetBuy[] = $value['productid'];
                $modelIDsArrRetBuy[] = $value['storereportmodelid'];
                break;
        }
        ##for names of client,supp,stores doing operation
        switch ($value['tablename']) {
            case "buyBillController.php":
                $buyIDsArr[] = $value['storereportmodelid'];
                break;
            case "returnBuyBillController.php":
                $retbuyIDsArr[] = $value['storereportmodelid'];
                break;
            case "buyAndReturnBillController.php":
                $buyAndRetIDsArr[] = $value['storereportmodelid'];
                break;
            case "sellbillController.php":
                $sellIDsArr[] = $value['storereportmodelid'];
                break;
            case "sellbillandruternController.php":
                $sellAndRetIDsArr[] = $value['storereportmodelid'];
                break;
            case "returnsellbillController.php":
                $retSellIDsArr[] = $value['storereportmodelid'];
                break;
            case "storemovementController.php":
                $storeMoveIDsArr[] = $value['storereportmodelid'];
                break;
        }

        $dataTemp[] = $value;
    }
    $storemovementData = $dataTemp;
    unset($productDataArr, $storeDataArr, $sizeColorDataArr);


    $proIDsArrSell = !empty($proIDsArrSell) ? array_unique($proIDsArrSell) : [0];
    $proIDsArrRetSell = !empty($proIDsArrRetSell) ? array_unique($proIDsArrRetSell) : [0];
    $proIDsArrBuy = !empty($proIDsArrBuy) ? array_unique($proIDsArrBuy) : [0];
    $proIDsArrRetBuy = !empty($proIDsArrRetBuy) ? array_unique($proIDsArrRetBuy) : [0];
    $modelIDsArrSell = !empty($modelIDsArrSell) ? array_unique($modelIDsArrSell) : [0];
    $modelIDsArrRetSell = !empty($modelIDsArrRetSell) ? array_unique($modelIDsArrRetSell) : [0];
    $modelIDsArrBuy = !empty($modelIDsArrBuy) ? array_unique($modelIDsArrBuy) : [0];
    $modelIDsArrRetBuy = !empty($modelIDsArrRetBuy) ? array_unique($modelIDsArrRetBuy) : [0];
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $sellIDsArr = !empty($sellIDsArr) ? array_unique($sellIDsArr) : [0];
    $retSellIDsArr = !empty($retSellIDsArr) ? array_unique($retSellIDsArr) : [0];
    $sellAndRetIDsArr = !empty($sellAndRetIDsArr) ? array_unique($sellAndRetIDsArr) : [0];
    $buyIDsArr = !empty($buyIDsArr) ? array_unique($buyIDsArr) : [0];
    $retbuyIDsArr = !empty($retbuyIDsArr) ? array_unique($retbuyIDsArr) : [0];
    $buyAndRetIDsArr = !empty($buyAndRetIDsArr) ? array_unique($buyAndRetIDsArr) : [0];
    $storeMoveIDsArr = !empty($storeMoveIDsArr) ? array_unique($storeMoveIDsArr) : [0];
    $proIDsArrSellAndRetSell = !empty($proIDsArrSellAndRetSell) ? array_unique($proIDsArrSellAndRetSell) : [0];
    $modelIDsArrSellAndRetSell = !empty($modelIDsArrSellAndRetSell) ? array_unique($modelIDsArrSellAndRetSell) : [0];

    //
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
    $sellDetailDataArr = R::getAll('SELECT sellbillid,sellbilldetailproductid,sellbilldetailprice
		FROM sellbilldetail
		WHERE sellbilldetailproductid in(' . implode(',', $proIDsArrSell) . ') and sellbillid in(' . implode(',', $modelIDsArrSell) . ')');
    $sellDetailDataArr = customArrayManyIndexOne($sellDetailDataArr, array('sellbillid', 'sellbilldetailproductid'));
    $retSellDetailDataArr = R::getAll('SELECT returnsellbillid,returnsellbilldetailproductid,returnsellbilldetailprice
        FROM returnsellbilldetail
        WHERE returnsellbilldetailproductid in(' . implode(',', $proIDsArrRetSell) . ') and returnsellbillid in(' . implode(',', $modelIDsArrRetSell) . ')');
    $retSellDetailDataArr = customArrayManyIndexOne($retSellDetailDataArr, array('returnsellbillid', 'returnsellbilldetailproductid'));

    $sellAndRetDetailDataArr = R::getAll('SELECT sellbillid,sellbilldetailproductid,sellbilldetailprice
		FROM sellandruternbilldetail
		WHERE sellbilldetailproductid in(' . implode(',', $proIDsArrSellAndRetSell) . ') and sellbillid in(' . implode(',', $modelIDsArrSellAndRetSell) . ')');
    $sellAndRetDetailDataArr = customArrayManyIndexOne($sellAndRetDetailDataArr, array('sellbillid', 'sellbilldetailproductid'));


    $buyDetailDataArr = R::getAll('SELECT buybillid,buybilldetailproductid,buybilldetailprice
        FROM buybilldetail
        WHERE buybilldetailproductid in(' . implode(',', $proIDsArrBuy) . ') and buybillid in(' . implode(',', $modelIDsArrBuy) . ')');
    $buyDetailDataArr = customArrayManyIndexOne($buyDetailDataArr, array('buybillid', 'buybilldetailproductid'));
    $retBuyDetailDataArr = R::getAll('SELECT returnbuybillid,returnbuybilldetailproductid,returnbuybilldetailprice
        FROM returnbuybilldetail
        WHERE returnbuybilldetailproductid in(' . implode(',', $proIDsArrRetBuy) . ') and returnbuybillid in(' . implode(',', $modelIDsArrRetBuy) . ')');
    $retBuyDetailDataArr = customArrayManyIndexOne($retBuyDetailDataArr, array('returnbuybillid', 'returnbuybilldetailproductid'));
    //
    $sellBillData = R::getAll('SELECT sellbillid,sellbillclientid as clientid FROM sellbill where sellbillid in(' . implode(',', $sellIDsArr) . ')');
    $sellBillData = customArrayIndexOne($sellBillData, 'sellbillid');
    $sellBillDataReturn = R::getAll('SELECT returnsellbillid,returnsellbillclientid as clientid	FROM returnsellbill where returnsellbillid in(' . implode(',', $retSellIDsArr) . ')');
    $sellBillDataReturn = customArrayIndexOne($sellBillDataReturn, 'returnsellbillid');
    $sellBillDataSellAndReturn = R::getAll('SELECT sellbillid,sellbillandrutern.sellbillclientid as clientid FROM sellbillandrutern where sellbillid in(' . implode(',', $sellAndRetIDsArr) . ')');
    $sellBillDataSellAndReturn = customArrayIndexOne($sellBillDataSellAndReturn, 'sellbillid');
    $buyBillData = R::getAll('SELECT buybillid,buybillsupplierid as supplierid FROM buybill where buybillid in(' . implode(',', $buyIDsArr) . ')');
    $buyBillData = customArrayIndexOne($buyBillData, 'buybillid');
    $returnBuyBillData = R::getAll('SELECT returnbuybillid,returnbuybillsupplierid as supplierid FROM returnbuybill where returnbuybillid in(' . implode(',', $retbuyIDsArr) . ')');
    $returnBuyBillData = customArrayIndexOne($returnBuyBillData, 'returnbuybillid');
    $buyAndReturnBillData = R::getAll('SELECT buybillid,buybillsupplierid as supplierid FROM buyandruternbill where buybillid in(' . implode(',', $buyAndRetIDsArr) . ')');
    $buyAndReturnBillData = customArrayIndexOne($buyAndReturnBillData, 'buybillid');
    $transferProductData = R::getAll('SELECT transferproductid,storeidfrom,storeidto FROM storemovement WHERE transferproductid in(' . implode(',', $storeMoveIDsArr) . ')');
    $transferProductData = customArrayIndexOne($transferProductData, 'transferproductid');
    $dataTemp = array();
    list($clientIDsArr, $supplierIDsArr, $StoreIDsArr) = [[], [], []];
    foreach ($storemovementData as $value) {
        $index = $value['storereportmodelid'] . "," . $value['productid'];
        switch ($value['tablename']) {
            case 'sellbillController.php':
                $value['pricebuy'] = $sellDetailDataArr[$index]['sellbilldetailprice'];
                break;
            case 'returnsellbillController.php':
                $value['pricebuy'] = $retSellDetailDataArr[$index]['returnsellbilldetailprice'];
                break;
            case 'sellbillandruternController.php':
                $value['pricebuy'] = $sellAndRetDetailDataArr[$index]['sellbilldetailprice'];
                break;
            case 'buyBillController.php':
                $value['pricebuy'] = $buyDetailDataArr[$index]['buybilldetailprice'];
                break;
            case 'returnBuyBillController.php':
                $value['pricebuy'] = $retBuyDetailDataArr[$index]['returnbuybilldetailprice'];
                break;
            case 'projectoperationController.php':
                $value['pricebuy'] = R::getCell('SELECT probuyprice FROM projectexchmaterialdetail where projectexchid = ? and productid = ?  limit 1',[$value['storereportmodelid'], $value['productid']]);
            case 'projectoperationControllerreturn.php':
                $value['pricebuy'] = R::getCell('SELECT probuyprice FROM projectexchmaterialdetailreturn where projectexchid = ? and productid = ?  limit 1',[$value['storereportmodelid'], $value['productid']]);    
                break;     
            default:
                $value['pricebuy'] = 0;
                break;
        }
        $value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
        switch ($value['tablename']) {
            case "buyBillController.php":
                if ($buyBillData[$value['storereportmodelid']]['supplierid'] !== null)
                    $supplierIDsArr[] = $buyBillData[$value['storereportmodelid']]['supplierid'];
                break;
            case "returnBuyBillController.php":
                if ($returnBuyBillData[$value['storereportmodelid']]['supplierid'] !== null)
                    $supplierIDsArr[] = $returnBuyBillData[$value['storereportmodelid']]['supplierid'];
                break;
            case "buyAndReturnBillController.php":
                if ($buyAndReturnBillData[$value['storereportmodelid']]['supplierid'] !== null)
                    $supplierIDsArr[] = $buyAndReturnBillData[$value['storereportmodelid']]['supplierid'];
                break;
            case "sellbillController.php":
                if ($sellBillData[$value['storereportmodelid']]['clientid'] !== null)
                    $clientIDsArr[] = $sellBillData[$value['storereportmodelid']]['clientid'];
                break;
            case "sellbillandruternController.php":
                if ($sellBillDataSellAndReturn[$value['storereportmodelid']]['clientid'] !== null)
                    $clientIDsArr[] = $sellBillDataSellAndReturn[$value['storereportmodelid']]['clientid'];
                break;
            case "returnsellbillController.php":
                if ($sellBillDataReturn[$value['storereportmodelid']]['clientid'] !== null)
                    $clientIDsArr[] = $sellBillDataReturn[$value['storereportmodelid']]['clientid'];
                break;
            case "storemovementController.php":
                if ($transferProductData[$value['storereportmodelid']]['storeidfrom'] !== null && $transferProductData[$value['storereportmodelid']]['storeidto'] !== null) {
                    $StoreIDsArr[] = $transferProductData[$value['storereportmodelid']]['storeidfrom'];
                    $StoreIDsArr[] = $transferProductData[$value['storereportmodelid']]['storeidto'];
                }
                break;
        }

        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $dataTemp[] = $obj;
        //$dataTemp[] = $value;
    }
    $storemovementData = $dataTemp;
    unset($sellDetailDataArr, $retSellDetailDataArr, $buyDetailDataArr, $retBuyDetailDataArr);
    $supplierIDsArr = !empty($supplierIDsArr) ? array_unique($supplierIDsArr) : [0];
    $clientIDsArr = !empty($clientIDsArr) ? array_unique($clientIDsArr) : [0];
    $StoreIDsArr = !empty($StoreIDsArr) ? array_unique($StoreIDsArr) : [0];

    $clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDsArr) . ') ');
    $clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
    $supplierDataArr = R::getAll('select supplierid,suppliername from supplier where supplierid in(' . implode(',', $supplierIDsArr) . ') ');
    $supplierDataArr = customArrayIndexOne($supplierDataArr, 'supplierid');
    $storeDataArr = R::getAll('select storeId,storeName as storename from store where storeId in(' . implode(',', $StoreIDsArr) . ') ');
    $storeDataArr = customArrayIndexOne($storeDataArr, 'storeId');

    if (isset($bure) && $bure == 1) {
        $i = 0;
        foreach ($storemovementData as $mystoremovementData) {
            $finalstring = $mystoremovementData->finalstring;
            $type = $mystoremovementData->storereporttype;
            $storereportid = $mystoremovementData->storereportid;

            if ($type == 1) {
                $otherfinal = substr_replace($finalstring, '0', -1);
                foreach ($storemovementData as $xx) {
                    if ($xx->finalstring == $otherfinal) {
                        $xx->storereportid = -1;
                        $mystoremovementData->storereportid = -1;
                    }
                }
            }
        }
    }




    //make the pure report
    $ii = 0;
    //select all data from storemovement
    $startvalue = 0;
    $startbefore = 0;
    $startafter = 0;
    foreach ($storemovementData as $mov) {
        $mov->productquantity = (float) $mov->productquantity;
        $mov->productbefore = (float) $mov->productbefore;
        $mov->productafter = (float) $mov->productafter;

        if ($bure == "1" && $order == 'desc') {

            if ($ii == 0) {

                $startvalue = $mov->productquantity;
                $mov->productquantity = $startvalue;
                $startbefore = $mov->productbefore;
                $mov->productbefore = $startbefore;
                $startafter = $mov->productafter;
                $mov->productafter = $startafter;
            } else {

                if ($mov->storereportid > 0) {
                    $startvalue = $mov->productquantity;
                    $mov->productafter = $startbefore;

                    if ($mov->productbefore == $mov->productafter) { //no change
                        $mov->productafter = $mov->productbefore;
                        //$startafter = $startafter - $startvalue;
                    } else {

                        if ($mov->storereporttype == "1") {
                            $mov->productbefore = $startbefore + $startvalue;
                            $startbefore = $startbefore + $startvalue;
                            //$startafter = $startbefore + $startvalue;
                            //print_r($startafter.'<br>');
                        } else {
                            $mov->productbefore = $startbefore - $startvalue;
                            $startbefore = $startbefore - $startvalue;
                        }
                    }
                }
            }


            ///////////To show product category name (the product whole path)
            //            $parentId = $mov->productCatId;
            //            //names of product's parents
            //            $pathArr = getProductPath_recursive($parentId, $categories);
            //new product name with all path
            if ($_SESSION['hidecat'] == 1) {
                $mov->productName = $mov->productName . '/' . $mov->productCatName; //. $pathArr;
            } else {
                $mov->productName = $mov->productName;
            }

            //            if (!is_null($pro->sizecolorstoredetailid) && $pro->sizecolorstoredetailid > 0) {//has size and color
            //                $pro->productid = "hasSizeColor" . $pro->productid . "-" . $pro->sizeid . "-" . $pro->colorid;
            //            }
            //$row_array['sizecolorstoredetailid'] = $pro->sizecolorstoredetailid;
            if (!is_null($mov->sizeName) && !empty($mov->sizeName)) {
                $mov->productName .= '/' . $mov->sizeName;
            }
            if (!is_null($mov->colorName) && !empty($mov->colorName)) {
                $mov->productName .= '/' . $mov->colorName;
            }
        } elseif ($bure == "1" && $order == 'asc') {

            if ($mov->storereportid != -1) {
                if ($ii == 0) {

                    $startvalue = $mov->productquantity;
                    $mov->productquantity = $startvalue;
                    $startbefore = $mov->productbefore;
                    $mov->productbefore = $startbefore;
                    $startafter = $mov->productafter;
                    $mov->productafter = $startafter;
                } else {
                    $startvalue = $mov->productquantity;
                    $mov->productbefore = $startafter;
                    if ($mov->productbefore == $mov->productafter) { //no change
                        $mov->productafter = $mov->productbefore;
                        //$startafter = $startafter - $startvalue;
                    } else {
                        if ($mov->storereporttype == "1") {
                            $mov->productafter = $startafter - $startvalue;
                            $startafter = $startafter - $startvalue;
                        } else {
                            $mov->productafter = $startafter + $startvalue;
                            $startafter = $startafter + $startvalue;
                        }
                    }
                }
            }
            ///////////To show product category name (the product whole path)
            //            $parentId = $mov->productCatId;
            //            //names of product's parents
            //            $pathArr = getProductPath_recursive($parentId, $categories);
            //new product name with all path
            if ($_SESSION['hidecat'] == 1) {
                $mov->productName = $mov->productName . '/' . $mov->productCatName; //. $pathArr;
            } else {
                $mov->productName = $mov->productName;
            }

            //            if (!is_null($pro->sizecolorstoredetailid) && $pro->sizecolorstoredetailid > 0) {//has size and color
            //                $pro->productid = "hasSizeColor" . $pro->productid . "-" . $pro->sizeid . "-" . $pro->colorid;
            //            }
            //$row_array['sizecolorstoredetailid'] = $pro->sizecolorstoredetailid;
            if (!is_null($mov->sizeName) && !empty($mov->sizeName)) {
                $mov->productName .= '/' . $mov->sizeName;
            }
            if (!is_null($mov->colorName) && !empty($mov->colorName)) {
                $mov->productName .= '/' . $mov->colorName;
            }
        } else {
            ///////////To show product category name (the product whole path)
            //            $parentId = $mov->productCatId;
            //            //names of product's parents
            //            $pathArr = getProductPath_recursive($parentId, $categories);
            //new product name with all path
            if ($_SESSION['hidecat'] == 1) {
                $mov->productName = $mov->productName . '/' . $mov->productCatName; //. $pathArr;
            } else {
                $mov->productName = $mov->productName;
            }

            //            if (!is_null($pro->sizecolorstoredetailid) && $pro->sizecolorstoredetailid > 0) {//has size and color
            //                $pro->productid = "hasSizeColor" . $pro->productid . "-" . $pro->sizeid . "-" . $pro->colorid;
            //            }
            //$row_array['sizecolorstoredetailid'] = $pro->sizecolorstoredetailid;
            if (!is_null($mov->sizeName) && !empty($mov->sizeName)) {
                $mov->productName .= '/' . $mov->sizeName;
            }
            if (!is_null($mov->colorName) && !empty($mov->colorName)) {
                $mov->productName .= '/' . $mov->colorName;
            }
        }
        switch ($mov->tablename) {
            case "buyBillController.php":
                $supplierID = $buyBillData[$mov->storereportmodelid]['supplierid'];
                $mov->otherName = $supplierDataArr[$supplierID]['suppliername'];
                break;
            case "returnBuyBillController.php":
                $supplierID = $returnBuyBillData[$mov->storereportmodelid]['supplierid'];
                $mov->otherName = $supplierDataArr[$supplierID]['suppliername'];
                break;
            case "buyAndReturnBillController.php":
                $supplierID = $buyAndReturnBillData[$mov->storereportmodelid]['supplierid'];
                $mov->otherName = $supplierDataArr[$supplierID]['suppliername'];
                break;
            case "sellbillController.php":
                $clientID = $sellBillData[$mov->storereportmodelid]['clientid'];
                $mov->otherName = $clientDataArr[$clientID]['clientname'];
                break;
            case "sellbillandruternController.php":
                $clientID = $sellBillDataSellAndReturn[$mov->storereportmodelid]['clientid'];
                $mov->otherName = $clientDataArr[$clientID]['clientname'];
                break;
            case "returnsellbillController.php":
                $clientID = $sellBillDataReturn[$mov->storereportmodelid]['clientid'];
                $mov->otherName = $clientDataArr[$clientID]['clientname'];
                break;
            case "storemovementController.php":
                $storeidfrom = $transferProductData[$mov->storereportmodelid]['storeidfrom'];
                $storeidto = $transferProductData[$mov->storereportmodelid]['storeidto'];
                if ($storeidfrom == $mov->storeid) {
                    $mov->otherName = "الى المخزن " . $storeDataArr[$storeidto]['storename'];
                } elseif ($storeidto == $mov->storeid) {
                    $mov->otherName = "من المخزن " . $storeDataArr[$storeidfrom]['storename'];
                }
                break;
        }


        $ii++;
    }


    //return $storemovementData;
    $smarty->assign('storereportData', $storemovementData);
}

function getProductPath_recursive($parentid, $categories)
{
    global $productCatExt;

    $catData = $productCatExt->getCategoryAndParentByCatId($parentid);

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

        return getProductPath_recursive($newParentId, $categories);
    }
    $categories = substr($categories, 0, strlen($categories) - 1);
    return $categories;
}

// show the form
function showAll()
{
    global $myStorereportEx;

    //select all data from storemovement
    $storemovementData = $myStorereportEx->queryWithOrder();
    return $storemovementData;
}
