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

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

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
$buybilldetailsEx = new BuybilldetailMySqlExtDAO();
//Returnsellbilldetail
$returnbuybilldetailsEx = new ReturnbuybilldetailMySqlExtDAO();
//Sellandruternbilldetail
$buyandreturnbillEx = new BuyandruternbilldetailMySqlExtDAO();
//Product
$myProductRecord = new ProductMySqlDAO();
$myProductEx = new ProductMySqlExtDAO();
//Productcat
$myProductcatRecord = new ProductcatMySqlDAO();
$myProductcatEx = new ProductcatMySqlExtDAO();

//Productunit
$myProductunitEx = new ProductunitMySqlExtDAO();
$myProductunitRecord = new ProductunitMySqlDAO();
//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)) {

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

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

    $productCatData = loadProductCat();
    $smarty->assign("productCatData", $productCatData);
    $storesData = $storeDAO->queryByConditions(0);
    $smarty->assign("storesData", $storesData);

    $startDate = $_REQUEST['from'];
    $endDate = $_REQUEST['to'];
    $productCatId = $_REQUEST['productCatId'];
    $storeid = (int) $_REQUEST['storeid'];

    if (empty($startDate)) {
        $startDate = date('Y-m-d');
    }
    if (empty($endDate)) {
        $endDate = date('Y-m-d');
    }
    if (isset($startDate) && isset($endDate) && $startDate != "" && $endDate != "") {
        $startDate = $startDate . ' 00:00:00';
        $endDate = $endDate . ' 23:59:59';
    }

    $message = '';
    if (isset($productCatId) && $productCatId != "-1" && $productCatId != "all") {
        $mycatdata = $myProductcatRecord->load($productCatId);
        $message = "تقرير مشتريات منتج من التصنيف :" . $mycatdata->productCatName . "<br> من تاريخ: " . $startDate . " إلى تاريخ: " . $endDate;
        $smarty->assign('message', $message);
    } elseif (isset($productCatId) && $productCatId == "all") {
        $message = "تقرير مشتريات منتج في كل الأصناف<br> من تاريخ: " . $startDate . " إلى  تاريخ: " . $endDate;
        $smarty->assign('message', $message);
    }
    loadProductByAllCategoriesAndDate($startDate, $endDate, $productCatId, $storeid);
    //here the smarty templates
    $smarty->display("productbuyreportview/show.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 $buybilldetailsEx;

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

    return $sumQuantity;
}

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

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

function showReturnSellByProduct($productId) {
    global $returnbuybilldetailsEx;
    global $myProductunitEx;

    //$sumQuantity = $returnbuybilldetailsEx->queryQuantityWithProduct($productId);
    //return $sumQuantity;
    $sql = 'and returnbuybilldetail.returnbuybilldetailproductid = ' . $productId . '';
    $ruturndata = $returnbuybilldetailsEx->queryWithDateAndConditionsAndProductIdandstoreNew($sql);
    $ruturnquntity = 0;
    foreach ($ruturndata as $myruturndata) {
        $quantity = $myruturndata->returnbuybilldetailquantity;
        $productunitId = $myruturndata->productunitid;
        $productnumber = $myProductunitEx->getProductNumber($productunitId);
        $ruturnquntity += ($quantity * $productnumber);

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

function showAditionalReturnSellByProduct($productId) {
    global $buyandreturnbillEx;
    global $myProductunitEx;

//    $sumQuantity = $buyandreturnbillEx->queryQuantityWithProductAndSellType($productId, 1);
//    return $sumQuantity;
    $sql = 'and buyandruternbilldetail.buybilldetailproductid =' . $productId . '';
    $ruturndataformbyandreturn = $buyandreturnbillEx->queryWithDateAndConditionsAndTypeAndProductIdandstoreNew(1, $sql);
    $ruturnquntity = 0;
    foreach ($ruturndataformbyandreturn as $myruturndataformbyandreturn) {
        $quantity = $myruturndataformbyandreturn->buybilldetailquantity;
        $productunitId = $myruturndataformbyandreturn->productunitid;
        $productnumber = $myProductunitEx->getProductNumber($productunitId);
        $ruturnquntity += ($quantity * $productnumber);
        //print_r('ruturndata' . $ruturnquntity . '<br>');
    }
    return $ruturnquntity;
}

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

    $queryString = "";
    if ($storeid > 0) {
        $queryString = " and buybill.buybillstoreid = $storeid ";
    }
    $sumQuantity = $buybilldetailsEx->queryQuantityWithProductAndDate2($productId, $startDate, $endDate, $queryString);
    return $sumQuantity;
}

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

    $queryString = "";
    if ($storeid > 0) {
        $queryString = " and buyandruternbill.buybillstoreid = $storeid ";
    }
    $sumQuantity = $buyandreturnbillEx->queryQuantityWithProductAndSellTypeAndDate2($productId, 0, $startDate, $endDate, $queryString);
    return $sumQuantity;
}

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

    $queryString = "";
    if ($storeid > 0) {
        $queryString = " and returnbuybill.returnbuybillstoreid = $storeid ";
    }
    $sumQuantity = $returnbuybilldetailsEx->queryQuantityWithProductAndDate2($productId, $startDate, $endDate, $queryString);
    return $sumQuantity;
}

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

    $queryString = "";
    if ($storeid > 0) {
        $queryString = " and buyandruternbill.buybillstoreid = $storeid ";
    }
    $sumQuantity = $buyandreturnbillEx->queryQuantityWithProductAndSellTypeAndDate2($productId, 1, $startDate, $endDate, $queryString);
    return $sumQuantity;
}

function loadProductUnitWithProductAndUnit($productId, $unitId) {

    //to use the variable out side the funcion
    global $myProductunitEx;

    // select all data from productunit tbl
    $productunitData = $myProductunitEx->queryWithProductIdAndUnitId($productId, $unitId);

    return $productunitData;
}

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

    $totalRemainQuantity = 0;
    $remainQuantity = 0;


    $queryString = " and product.conditions = 0 ";
    if ($productCatId > 0) {
        $queryString .= " and product.productCatId = $productCatId ";
    }

    $productData = R::getAll('SELECT productId,productName,productCatId FROM product where 1 ' . $queryString);
    ##get catname
    list($productCatIdsArr, $productIdsArr) = [[], []];
    foreach ($productData as $value) {
        $productCatIdsArr[] = $value['productCatId'];
        $productIdsArr[] = $value['productId'];
    }
    $productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
    $productIdsArr = !empty($productIdsArr) ? array_unique($productIdsArr) : [0];
    $productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
    $productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');

    $tempArr = array();
    foreach ($productData as $value) {
        $value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
        //
        $obj = new stdClass();
        foreach ($value as $key1 => $value1) {
            $obj->$key1 = $value1;
        }
        $tempArr[] = $obj;
    }
    $productData = $tempArr;

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

    ######
    list($productunitIdsArr) = [[]];
    //# 1 $sellsQuantity
    $queryString = "";
    if ($storeid > 0) {
        $queryString = " and buybill.buybillstoreid = $storeid ";
    }
    $sellsQuantity = R::getAll('SELECT buybilldetailproductid,buybilldetailquantity as quantity, buybilldetail.productunitid as productunitid
		FROM buybilldetail
                JOIN buybill ON buybilldetail.buybillid = buybill.buybillid
		where  buybill.conditions=0 and buybilldetail.buybilldetailproductid in(' . implode(',', $productIdsArr) . ')
                and buybilldetail.buybilldetaildate >= "' . $startDate . '" and buybilldetail.buybilldetaildate <= "' . $endDate . '" ' . $queryString);
    foreach ($sellsQuantity as $value) {
        $productunitIdsArr[] = $value['productunitid'];
    }
    $sellsQuantity = customArrayIndexMany($sellsQuantity, 'buybilldetailproductid');
    //# 2 $aditionalSellsQuantity
    $queryString = "";
    if ($storeid > 0) {
        $queryString = " and buyandruternbill.buybillstoreid = $storeid ";
    }
    $aditionalSellsQuantity = R::getAll('SELECT buybilldetailproductid,buybilldetailquantity  as quantity, buyandruternbilldetail.productunitid as productunitid
		 FROM buyandruternbilldetail
                 JOIN buyandruternbill ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 where buyandruternbill.conditions = 0 and buyandruternbilldetail.billtype =0 and buyandruternbilldetail.buybilldetailproductid in(' . implode(',', $productIdsArr) . ')
		 and buyandruternbill.buybilldate >= "' . $startDate . '" and buyandruternbill.buybilldate <= "' . $endDate . '" ' . $queryString);
    foreach ($aditionalSellsQuantity as $value) {
        $productunitIdsArr[] = $value['productunitid'];
    }
    $aditionalSellsQuantity = customArrayIndexMany($aditionalSellsQuantity, 'buybilldetailproductid');
    //# 3 $returnSellQuantity
    $queryString = "";
    if ($storeid > 0) {
        $queryString = " and returnbuybill.returnbuybillstoreid = $storeid ";
    }
    $returnSellQuantity = R::getAll('SELECT returnbuybilldetailproductid,returnbuybilldetail.returnbuybilldetailquantity  as quantity,returnbuybilldetail.productunitid as productunitid
                FROM returnbuybilldetail
                JOIN returnbuybill ON returnbuybilldetail.returnbuybillid = returnbuybill.returnbuybillid
                where returnbuybill.conditions=0 and returnbuybilldetail.returnbuybilldetailproductid in(' . implode(',', $productIdsArr) . ')
                and returnbuybill.returnbuybilldate >= "' . $startDate . '" and returnbuybill.returnbuybilldate <= "' . $endDate . '" ' . $queryString);
    foreach ($returnSellQuantity as $value) {
        $productunitIdsArr[] = $value['productunitid'];
    }
    $returnSellQuantity = customArrayIndexMany($returnSellQuantity, 'returnbuybilldetailproductid');
    //# 4
    $queryString = "";
    if ($storeid > 0) {
        $queryString = " and buyandruternbill.buybillstoreid = $storeid ";
    }
    $aditionalReturnSellsQuantity = R::getAll('SELECT buybilldetailproductid,buybilldetailquantity  as quantity, buyandruternbilldetail.productunitid as productunitid
		 FROM buyandruternbilldetail
                 JOIN buyandruternbill ON buyandruternbilldetail.buybillid = buyandruternbill.buybillid
		 where buyandruternbill.conditions = 0 and buyandruternbilldetail.billtype =1 and buyandruternbilldetail.buybilldetailproductid in(' . implode(',', $productIdsArr) . ')
		 and buyandruternbill.buybilldate >= "' . $startDate . '" and buyandruternbill.buybilldate <= "' . $endDate . '" ' . $queryString);
    foreach ($aditionalReturnSellsQuantity as $value) {
        $productunitIdsArr[] = $value['productunitid'];
    }
    $aditionalReturnSellsQuantity = customArrayIndexMany($aditionalReturnSellsQuantity, 'returnbuybilldetailproductid');


    ##product unit data
    $productunitIdsArr = !empty($productunitIdsArr) ? array_unique($productunitIdsArr) : [0];
    $productUnitDataArr = R::getAll('SELECT productunitid,productnumber
		 FROM productunit
		 where productunitid in(' . implode(',', $productunitIdsArr) . ') ');
    $productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');

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

        //# 1
        $sellsfinalquantity = 0;
        foreach ($sellsQuantity[$productId] as $value) {
            $quantity = $value['quantity'];
            $productnumber = $productUnitDataArr[$value['productunitid']]['productnumber'];
            $sellsfinalquantity += $quantity * $productnumber;
        }

        //# 2
        $sellsaditionalSellsQuantity = 0;
        foreach ($aditionalSellsQuantity[$productId] as $value) {
            $quantity = $value['quantity'];
            $productnumber = $productUnitDataArr[$value['productunitid']]['productnumber'];
            $sellsaditionalSellsQuantity += $quantity * $productnumber;
        }

        $totalSellsQuantity = $sellsfinalquantity + $sellsaditionalSellsQuantity;


        //# 3
        $finalreturnSellQuantity = 0;
        foreach ($returnSellQuantity[$productId] as $value) {
            $quantity = $value['quantity'];
            $productnumber = $productUnitDataArr[$value['productunitid']]['productnumber'];
            $finalreturnSellQuantity += $quantity * $productnumber;
        }

        //# 4
        $finaladitionalReturnSellsQuantity = 0;
        foreach ($aditionalReturnSellsQuantity[$productId] as $value) {
            $quantity = $value['quantity'];
            $productnumber = $productUnitDataArr[$value['productunitid']]['productnumber'];
            $finaladitionalReturnSellsQuantity += $quantity * $productnumber;
        }

        $totalReturnSellsQuantity = $finalreturnSellQuantity + $finaladitionalReturnSellsQuantity;
        $remainQuantity = $totalSellsQuantity - $totalReturnSellsQuantity;


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

        // اجمالى مردود المشتريات
        $totalreturn += $totalReturnSellsQuantity;

        $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
        $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
        $smarty->assign("remainQuantity" . $h . "", $remainQuantity);
        $h++;
    }
    /* old way ,now we are making report faster
     * foreach ($productData as $product) {
      $productId = $product->productId;
      $productCatId = $product->productCatId;

      //# 1
      $sellsQuantity = showSellByProductAndDate($productId, $startDate, $endDate, $storeid);
      $quantity = $sellsQuantity[0]->buybilldetailquantity;
      $productunitId = $sellsQuantity[0]->productunitid;
      $productnumber = 0;
      if (!empty($productunitId)) {
      $productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
      $productnumber = $productunitData->productnumber;
      }

      $sellsfinalquantity = $quantity * $productnumber;


      //# 2
      $aditionalSellsQuantity = showAditionalSellByProductAndDate($productId, $startDate, $endDate, $storeid);

      $quantity_1 = $aditionalSellsQuantity[0]->buybilldetailquantity;
      $productunitId_1 = $aditionalSellsQuantity[0]->productunitid;
      $productnumber_1 = 0;
      if (!empty($productunitId_1)) {
      $productunitData_1 = loadProductUnitWithProductAndUnit($productId, $productunitId_1);
      $productnumber_1 = $productunitData_1->productnumber;
      }
      $sellsaditionalSellsQuantity = $quantity_1 * $productnumber_1;
      $totalSellsQuantity = $sellsfinalquantity + $sellsaditionalSellsQuantity;


      //# 3
      $returnSellQuantity = showReturnSellByProductAndDate($productId, $startDate, $endDate, $storeid);
      $quantity_2 = $returnSellQuantity[0]->returnbuybilldetailquantity;
      $productunitId_2 = $returnSellQuantity[0]->productunitid;
      $productnumber_2 = 0;
      if (!empty($productunitData_2)) {
      $productunitData_2 = loadProductUnitWithProductAndUnit($productId, $productunitId_2);
      $productnumber_2 = $productunitData_2->productnumber;
      }

      $finalreturnSellQuantity = $quantity_2 * $productnumber_2;


      //# 4
      $aditionalReturnSellsQuantity = showAditionalReturnSellByProductAndProduct($productId, $startDate, $endDate, $storeid);
      $quantity_3 = $aditionalReturnSellsQuantity[0]->buybilldetailquantity;
      $productunitId_3 = $aditionalReturnSellsQuantity[0]->productunitid;
      $productunitId_3 = 0;
      if (!empty($productunitId_3)) {
      $productunitData_3 = loadProductUnitWithProductAndUnit($productId, $productunitId_3);
      $productnumber_3 = $productunitData_3->productnumber;
      }


      $finaladitionalReturnSellsQuantity = $quantity_3 * $productnumber_3;
      $totalReturnSellsQuantity = $finalreturnSellQuantity + $finaladitionalReturnSellsQuantity;
      $remainQuantity = $totalSellsQuantity - $totalReturnSellsQuantity;


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

      // اجمالى مردود المشتريات
      $totalreturn += $totalReturnSellsQuantity;

      $smarty->assign("totalSellsQuantity" . $h . "", $totalSellsQuantity);
      $smarty->assign("totalReturnSellsQuantity" . $h . "", $totalReturnSellsQuantity);
      $smarty->assign("remainQuantity" . $h . "", $remainQuantity);
      $h++;
      } */

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

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

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

?>