File: /home/mostafedeg/public_html/erp/controllers/buyreport.php
<?php
//the global file operation
include("../public/impOpreation.php");
// get the config file
include_once("../public/config.php");
//here the db files that include in the file
include("../public/include_dao.php");
//here the db files that include in the file
include("reportfunctions.php");
//user
require_once('../models/dao/UserDAO.class.php');
require_once('../models/dto/User.class.php');
require_once('../models/mysql/UserMySqlDAO.class.php');
require_once('../models/mysql/ext/UserMySqlExtDAO.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');
//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');
//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');
/////////////////////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');
//Unit
require_once('../models/dao/UnitDAO.class.php');
require_once('../models/dto/Unit.class.php');
require_once('../models/mysql/UnitMySqlDAO.class.php');
require_once('../models/mysql/ext/UnitMySqlExtDAO.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');
//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');
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');
require_once('../models/mysql/ext/ReturnsellbilldetailMySqlExtDAO.class.php');
$do = $_GET['do'];
/* ======================
Controller Name :- storedetailCTRL تقرير مخزون اول مدة
OPERTATION in Controller
1-load save data
2-display show form
======================== */
//here the global templates
$smarty->display("header.html");
//here goes the instances and general variables
//user
$user = new User();
$userDAO = new UserMySqlDAO();
$userEX = new UserMySqlExtDAO();
//Buybill
$buyBill = new Buybill();
$buyBillDAO = new BuybillMySqlDAO();
$buyBillEX = new BuybillMySqlExtDAO();
//Buybilldetail
$buyBillDetail = new Buybilldetail();
$buyBillDetailDAO = new BuybilldetailMySqlDAO();
$buyBillDetailEX = new BuybilldetailMySqlExtDAO();
//Returnbuybill
$returnBuyBill = new Returnbuybill();
$returnBuyBillDAO = new ReturnbuybillMySqlDAO();
$returnBuyBillEX = new ReturnbuybillMySqlExtDAO();
//Returnbuybilldetail
$returnBuyBillDetail = new Returnbuybilldetail();
$returnBuyBillDetailDAO = new ReturnbuybilldetailMySqlDAO();
$returnBuyBillDetailEX = new ReturnbuybilldetailMySqlExtDAO();
//Buyandruternbill
$buyAndReturnBill = new Buyandruternbill();
$buyAndReturnBillDAO = new BuyandruternbillMySqlDAO();
$buyAndReturnBillEX = new BuyandruternbillMySqlExtDAO();
//Buyandruternbilldetail
$buyAndReturnBillDetail = new Buyandruternbilldetail();
$buyAndReturnBillDetailDAO = new BuyandruternbilldetailMySqlDAO();
$buyAndReturnBillDetailEX = new BuyandruternbilldetailMySqlExtDAO();
##################################
//bill
$billsBuy = new Billsbuy();
$billsBuyDAO = new BillsbuyMySqlDAO();
$billsBuyEX = new BillsbuyMySqlExtDAO();
$billsProductsBuy = new Billsproductsbuy();
$billsProductsBuyDAO = new BillsproductsbuyMySqlDAO();
$billsProductsBuyEX = new BillsproductsbuyMySqlExtDAO();
$youtubeLink = new YoutubeLink();
$youtubeLinkDAO = new YoutubeLinkMySqlDAO();
$youtubeLinkEX = new YoutubeLinkMySqlExtDAO();
///////////////product///////////////
$ProductDAO = new ProductMySqlDAO();
$Product = new Product();
$ProductEX = new ProductMySqlExtDAO();
//Productcat
$productCatDAO = new ProductcatMySqlDAO();
$productCatExt = new ProductcatMySqlExtDAO();
//supplier
$supplier = new Supplier();
$supplierDAO = new SupplierMySqlDAO();
$supplierEX = new SupplierMySqlExtDAO();
//Supplierdebtchange
$supplierDeptChange = new Supplierdebtchange();
$supplierDeptChangeDAO = new SupplierdebtchangeMySqlDAO();
$supplierDeptChangeExt = new SupplierdebtchangeMySqlExtDAO();
//Unit
$unitDAO = new UnitMySqlDAO();
$unitEX = new UnitMySqlExtDAO();
//Productunit
$productUnit = new Productunit();
$productUnitDAO = new ProductunitMySqlDAO();
$productUnitEX = new ProductunitMySqlExtDAO();
//Storedetail
$myStoredetail = new Storedetail();
$myStoredetailRecord = new StoredetailMySqlDAO();
$myStoredetailEx = new StoredetailMySqlExtDAO();
$clientDao = new ClientMySqlDAO();
$returnSellBillDetailEX = new ReturnsellbilldetailMySqlExtDAO();
//Programsetting
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$lastLevelCatIDS = array();
$catsIDS = "";
$myUserGroupRecord = new UsergroupMySqlDAO();
$userGroupId = $_SESSION['usergroupid'];
$loadUserGroupData = $myUserGroupRecord->load($userGroupId);
$smarty->assign("loadUserGroupData", $loadUserGroupData);
//check and use the condition that suite this action
if (empty($do)) {
//here the permission check
include_once("../public/authentication.php");
$Programsetting = $ProgramsettingDAO->load(1);
##########################################################################
//prepare search tools
$suppliers = $supplierDAO->queryAll();
$smarty->assign("suppliers", $suppliers);
$clients = $clientDao->queryAll();
$smarty->assign("clients", $clients);
$youtubes = $youtubeLinkDAO->queryAll();
$smarty->assign("youtubes", $youtubes);
###########################################################################
//search
$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');
$supplierid = filter_input(INPUT_POST, 'supplierid');
$smarty->assign('supplierid', $supplierid);
$debt = getSupplierDept($supplierid);
$smarty->assign('debt', $debt);
$clientid = filter_input(INPUT_POST, 'clientid');
$smarty->assign('clientid', $clientid);
$order = filter_input(INPUT_POST, 'order');
if ($order != "asc" && $order != "desc") {
$order = "no";
}
$queryString = " where 1 "; //bills buy
$queryString1 = " where 1 "; //buybill
$queryString1R = " where 1 "; //buybillreturn
$queryString1SR = " where 1 "; //buybillandreturn
$message = '';
if (isset($supplierid) && !empty($supplierid) && $supplierid != -1) {
$queryString .= 'and billsbuy.clientid = ' . $supplierid . ' ';
$queryString1 .= 'and buybill.buybillsupplierid = ' . $supplierid . ' ';
$queryString1R .= 'and returnbuybill.returnbuybillsupplierid = ' . $supplierid . ' ';
$queryString1SR .= 'and buyandruternbill.buybillsupplierid = ' . $supplierid . ' ';
$supplier = $supplierDAO->load($supplierid);
$message .= " المورد: " . $supplier->suppliername . " ";
}
##date
if (isset($datefrom) && !empty($datefrom)) {
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
$datefrom = date('Y-m-d H:i:s', strtotime('+' . $Programsetting->reportsPlusHours . ' hour +0 minutes', strtotime($datefrom)));
} else {
$datefrom = $datefrom . " 00:00:00";
}
$queryString .= 'and date(billsbuy.billdate) >= "' . $datefrom . '" ';
$queryString1 .= 'and buybill.buybilldate >= "' . $datefrom . '" ';
$queryString1R .= 'and returnbuybill.returnbuybilldate >= "' . $datefrom . '" ';
$queryString1SR .= 'and buyandruternbill.buybilldate >= "' . $datefrom . '" ';
$message .= "من تاريخ: " . $datefrom;
}
if (isset($dateto) && !empty($dateto)) {
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
$dateto = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($dateto)));
} else {
$dateto = $dateto . ' 23:59:59';
}
$queryString .= 'and date(billsbuy.billdate) <= "' . $dateto . '" ';
$queryString1 .= 'and buybill.buybilldate <= "' . $dateto . '" ';
$queryString1R .= 'and returnbuybill.returnbuybilldate <= "' . $dateto . '" ';
$queryString1SR .= 'and buyandruternbill.buybilldate <= "' . $dateto . '" ';
$message .= " إلى تاريخ: " . $dateto;
}
if (isset($clientid) && !empty($clientid) && $clientid != -1) {
$queryStringClient .= 'and sellbill.sellbillclientid = ' . $clientid . ' ';
$queryStringClient1R .= 'and returnsellbill.returnsellbillclientid = ' . $clientid . ' ';
$queryStringClient1SR .= 'and sellbillandrutern.sellbillclientid = ' . $clientid . ' ';
$client = $clientDao->load($clientid);
$message .= " العميل: " . $client->clientname . " ";
getDataNewWithClient($queryString, $queryString1, $queryString1R, $queryString1SR, $order, $queryStringClient, $queryStringClient1R, $queryStringClient1SR);
} else {
getDataNew($queryString, $queryString1, $queryString1R, $queryString1SR, $order);
}
$smarty->assign('message', $message);
$payed = getCLientPayedDeptData($supplierid, $datefrom, $dateto);
$smarty->assign('payed', $payed);
//here the smarty templates
$smarty->display("buyreportview/show.html");
//$smarty->assign("settlementstoreshow", 1);
}
//here the global templates
$smarty->display("footer.html");
/* ===============================
function in this CONTROLLER
================================ */
function getDataNew($queryString, $queryString1, $queryString1R, $queryString1SR, $order)
{
global $billsProductsBuyEX;
global $billsBuyDAO;
global $billsBuyEX;
global $buyBillDetailEX;
global $returnBuyBillDetailEX;
global $buyAndReturnBillDetailEX;
global $ProductDAO;
global $productCatDAO;
global $ProductEX;
global $smarty;
global $lastLevelCatIDS;
global $catsIDS;
global $productUnitEX;
global $unitEX;
global $myStoredetailEx;
global $ProgramsettingDAO;
global $returnSellBillDetailEX;
class productUnitData
{
public $catId;
public $catName;
public $productId;
public $productName;
public $logo;
public $unitId;
public $unitName;
public $amount = 0;
public $price = 0;
public $amount_ret = 0;
public $price_ret = 0;
public $productQuantity = 0;
public $productLastPrice = 0;
public $noOfBills;
public $priceWithTax;
public $priceWithTax_ret;
//prices
public $productBuyPrice = 0;
public $lastbuyprice = 0;
public $meanbuyprice = 0;
public $lastbuyprice_withDiscount = 0;
public $meanbuyprice_withDiscount = 0;
public $overAllAveragePrice = 0;
//for total
public $storeQuantity = 0;
public $storeQuantityPrice = 0;
}
$AllDataIndexArr = array();
$unitTotal = array();
$allDataArr = array();
global $allDataArr;
##if no search make it today
if ($queryString == " where 1 ") {
##date
$today = date("Y-m-d");
$queryString .= 'and date(billsbuy.billdate) = "' . $today . '" ';
$queryString1 .= 'and buybill.buybilldate = "' . $today . '" ';
$queryString1R .= 'and returnbuybill.returnbuybilldate = "' . $today . '" ';
$queryString1SR .= 'and buyandruternbill.buybilldate = "' . $today . '" ';
}
$Programsettingdata = $ProgramsettingDAO->load(1);
$buyBillData = R::getAll('SELECT buybillid,payedtax
FROM buybill ' . $queryString1 . ' AND conditions = 0');
$returnBuyBillData = R::getAll('SELECT returnbuybillid,payedtax
FROM returnbuybill ' . $queryString1R . ' AND conditions = 0');
$buyAndReturnBillData = R::getAll('SELECT buybillid,payedtax
FROM buyandruternbill ' . $queryString1SR . ' AND conditions = 0'); //AND sellbillandrutern.deletedsellid=0//order by sellandruternbilldetail.sellbilldetailproductid
// $buyBillData = $buyBillDetailEX->queryAllGeneral($queryString1);
// $returnBuyBillData = $returnBuyBillDetailEX->queryAllGeneral($queryString1R);
// $buyAndReturnBillData = $buyAndReturnBillDetailEX->queryAllGeneral($queryString1SR);
$smarty->assign('buyBillNo', count($buyBillData));
$smarty->assign('returnBuyBillNo', count($returnBuyBillData));
list($productIdsArrAll) = [[]];
############################################################################
##فاتورة المشتريات
list($billIDs) = [[]];
foreach ($buyBillData as $value) {
$billIDs[] = $value['buybillid'];
}
$billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
//1- bill details
$buyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,productunitid,buybilldetailquantity,buybilldetailtotalprice
FROM buybilldetail
where buybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
$buyBillDetail = customArrayIndexMany($buyBillDetail, 'buybillid');
$buyBillDataTemp = array();
list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
foreach ($buyBillData as $value) {
foreach ($buyBillDetail[$value['buybillid']] as $data) {
foreach ($data as $key1 => $value1) {
$value[$key1] = $value1;
}
$productIDsArr[] = (int) $value['buybilldetailproductid'];
$productUnitIDsArr[] = (int) $value['productunitid'];
$buyBillDataTemp[] = $value;
}
}
$buyBillData = $buyBillDataTemp;
$productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
$productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
$productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
//2- products
$productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
from product where productId in(' . implode(',', $productIDsArr) . ') ');
$productDataArr = customArrayIndexOne($productDataArr, 'productId');
$buyBillDataTemp = array();
foreach ($buyBillData as $value) {
$cat = $productDataArr[$value['buybilldetailproductid']]['productCatId'];
if ($cat !== null)
$productCatIdsArr[] = $cat;
foreach ($productDataArr[$value['buybilldetailproductid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
$buyBillDataTemp[] = $value;
}
$productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
$buyBillData = $buyBillDataTemp;
//3- cat,units
$productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
$productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
$productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
from productunit
join unit on unit.unitId = productunit.unitid
where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
$productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
$buyBillDataTemp = array();
foreach ($buyBillData as $value) {
$value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
//from key value pair to std class
$obj = new stdClass();
foreach ($value as $key1 => $value1) {
$obj->$key1 = $value1;
}
$buyBillDataTemp[] = $obj;
}
$buyBillData = $buyBillDataTemp;
foreach ($buyBillData as $value) {
$productid = $value->buybilldetailproductid;
$unitid = $value->unitid;
$unitname = $value->unitName;
$quantity = $value->buybilldetailquantity;
$value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
$taxes = $value->buybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
##############
##check if product is here
if (isset($allDataArr[$productid])) {
##product exists
##check if unit is here
if (in_array($unitid, $AllDataIndexArr[$productid])) {
##unit exists
$key2 = array_search($unitid, $AllDataIndexArr[$productid]);
$myproduct = $allDataArr[$productid][$key2];
## common data ##
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->priceWithTax += $value->buybilldetailtotalprice + $taxes;
$myproduct->amount += $quantity;
$myproduct->noOfBills = $myproduct->noOfBills + 1;
#################
} else {
##new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
// $myproduct->productId = '';
// $myproduct->productName = '';
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->priceWithTax += $value->buybilldetailtotalprice + $taxes;
$myproduct->amount += $quantity;
$myproduct->noOfBills = 1;
#################
array_push($allDataArr[$productid], $myproduct);
array_push($AllDataIndexArr[$productid], $unitid);
}
} else {
##new product&& new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
$myproduct->price = $value->buybilldetailtotalprice;
$myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount = $quantity;
$myproduct->noOfBills = 1;
#################
//handle indexes
$allDataArr[$productid] = array();
array_push($allDataArr[$productid], $myproduct);
$AllDataIndexArr[$productid] = array();
array_push($AllDataIndexArr[$productid], $unitid);
}
if (!isset($unitTotal[$myproduct->unitName])) {
$unitTotal[$myproduct->unitName] = 0;
}
$unitTotal[$myproduct->unitName] += $quantity;
}
############################################################################
##فاتورة مردود المشتريات
list($billIDs) = [[]];
foreach ($returnBuyBillData as $value) {
$billIDs[] = $value['returnbuybillid'];
}
$billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
//1- bill details
$buyBillDetail = R::getAll('SELECT returnbuybillid,returnbuybilldetailproductid,productunitid,returnbuybilldetailquantity,returnbuybilldetailtotalprice
FROM returnbuybilldetail
where returnbuybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
$buyBillDetail = customArrayIndexMany($buyBillDetail, 'returnbuybillid');
$buyBillDataTemp = array();
list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
foreach ($returnBuyBillData as $value) {
foreach ($buyBillDetail[$value['returnbuybillid']] as $data) {
foreach ($data as $key1 => $value1) {
$value[$key1] = $value1;
}
$productIDsArr[] = (int) $data['returnbuybilldetailproductid'];
$productUnitIDsArr[] = (int) $data['productunitid'];
$buyBillDataTemp[] = $value;
}
}
$returnBuyBillData = $buyBillDataTemp;
$productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
$productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
$productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
//2- products
$productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
from product where productId in(' . implode(',', $productIDsArr) . ') ');
$productDataArr = customArrayIndexOne($productDataArr, 'productId');
$buyBillDataTemp = array();
foreach ($returnBuyBillData as $value) {
$cat = $productDataArr[$value['returnbuybilldetailproductid']]['productCatId'];
if ($cat !== null)
$productCatIdsArr[] = $cat;
foreach ($productDataArr[$value['returnbuybilldetailproductid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
$buyBillDataTemp[] = $value;
}
$productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
$returnBuyBillData = $buyBillDataTemp;
//3- cat,units
$productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
$productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
$productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
from productunit
join unit on unit.unitId = productunit.unitid
where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
$productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
$buyBillDataTemp = array();
foreach ($returnBuyBillData as $value) {
$value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
//from key value pair to std class
$obj = new stdClass();
foreach ($value as $key1 => $value1) {
$obj->$key1 = $value1;
}
$buyBillDataTemp[] = $obj;
}
$returnBuyBillData = $buyBillDataTemp;
foreach ($returnBuyBillData as $value) {
$productid = $value->returnbuybilldetailproductid;
$unitid = $value->unitid;
$unitname = $value->unitName;
$quantity = $value->returnbuybilldetailquantity;
$value->returnbuybilldetailtotalprice = round($value->returnbuybilldetailtotalprice, 2);
$taxes = $value->returnbuybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
##############
##check if product is here
if (isset($allDataArr[$productid])) {
##product exists
##check if unit is here
if (in_array($unitid, $AllDataIndexArr[$productid])) {
##unit exists
$key2 = array_search($unitid, $AllDataIndexArr[$productid]);
$myproduct = $allDataArr[$productid][$key2];
## common data ##
$myproduct->price_ret += $value->returnbuybilldetailtotalprice;
$myproduct->priceWithTax_ret += $value->returnbuybilldetailtotalprice + $taxes;
$myproduct->amount_ret += $quantity;
// $myproduct->noOfBills = $myproduct->noOfBills + 1;
#################
} else {
##new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
// $myproduct->productId = '';
// $myproduct->productName = '';
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
$myproduct->price_ret += $value->returnbuybilldetailtotalprice;
$myproduct->priceWithTax_ret += $value->returnbuybilldetailtotalprice + $taxes;
$myproduct->amount_ret += $quantity;
// $myproduct->noOfBills = 1;
#################
array_push($allDataArr[$productid], $myproduct);
array_push($AllDataIndexArr[$productid], $unitid);
}
} else {
##new product&& new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
$myproduct->price_ret = $value->returnbuybilldetailtotalprice;
$myproduct->priceWithTax_ret = $value->returnbuybilldetailtotalprice + $taxes;
$myproduct->amount_ret = $quantity;
// $myproduct->noOfBills = 1;
#################
//handle indexes
$allDataArr[$productid] = array();
array_push($allDataArr[$productid], $myproduct);
$AllDataIndexArr[$productid] = array();
array_push($AllDataIndexArr[$productid], $unitid);
}
if (!isset($unitTotal[$myproduct->unitName])) {
$unitTotal[$myproduct->unitName] = 0;
}
$unitTotal[$myproduct->unitName] -= $quantity;
}
############################################################################
##فاتورة المشتريات والمردود
list($billIDs) = [[]];
foreach ($buyAndReturnBillData as $value) {
$billIDs[] = $value['buybillid'];
}
$billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
//1- bill details
$buyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,productunitid,buybilldetailquantity,buybilldetailtotalprice
FROM buyandruternbilldetail
where buybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
$buyBillDetail = customArrayIndexMany($buyBillDetail, 'buybillid');
$buyBillDataTemp = array();
list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
foreach ($buyAndReturnBillData as $value) {
foreach ($buyBillDetail[$value['buybillid']] as $data) {
foreach ($data as $key1 => $value1) {
$value[$key1] = $value1;
}
$productIDsArr[] = (int) $value['buybilldetailproductid'];
$productUnitIDsArr[] = (int) $value['productunitid'];
$buyBillDataTemp[] = $value;
}
}
$buyAndReturnBillData = $buyBillDataTemp;
$productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
$productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
$productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
//2- products
$productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
from product where productId in(' . implode(',', $productIDsArr) . ') ');
$productDataArr = customArrayIndexOne($productDataArr, 'productId');
$buyBillDataTemp = array();
foreach ($buyAndReturnBillData as $value) {
$cat = $productDataArr[$value['buybilldetailproductid']]['productCatId'];
if ($cat !== null)
$productCatIdsArr[] = $cat;
foreach ($productDataArr[$value['buybilldetailproductid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
$buyBillDataTemp[] = $value;
}
$productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
$buyAndReturnBillData = $buyBillDataTemp;
//3- cat,units
$productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
$productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
$productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
from productunit
join unit on unit.unitId = productunit.unitid
where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
$productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
$buyBillDataTemp = array();
foreach ($buyAndReturnBillData as $value) {
$value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
//from key value pair to std class
$obj = new stdClass();
foreach ($value as $key1 => $value1) {
$obj->$key1 = $value1;
}
$buyBillDataTemp[] = $obj;
}
$buyAndReturnBillData = $buyBillDataTemp;
foreach ($buyAndReturnBillData as $value) {
$productid = $value->buybilldetailproductid;
$unitid = $value->unitid;
$unitname = $value->unitName;
$quantity = $value->buybilldetailquantity;
$billType = $value->billtype;
$value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
$taxes = $value->buybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
##############
##check if product is here
if (isset($allDataArr[$productid])) {
##product exists
##check if unit is here
if (in_array($unitid, $AllDataIndexArr[$productid])) {
##unit exists
$key2 = array_search($unitid, $AllDataIndexArr[$productid]);
$myproduct = $allDataArr[$productid][$key2];
## common data ##
if ($billType == 0) {
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount += $quantity;
} else {
$myproduct->price_ret += $value->buybilldetailtotalprice;
$myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount_ret += $quantity;
}
$myproduct->noOfBills = $myproduct->noOfBills + 1;
#################
} else {
##new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
// $myproduct->productId = '';
// $myproduct->productName = '';
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
if ($billType == 0) {
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount += $quantity;
} else {
$myproduct->price_ret += $value->buybilldetailtotalprice;
$myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount_ret += $quantity;
}
$myproduct->noOfBills = 1;
#################
array_push($allDataArr[$productid], $myproduct);
array_push($AllDataIndexArr[$productid], $unitid);
}
} else {
##new product&& new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
if ($billType == 0) {
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount += $quantity;
} else {
$myproduct->price_ret += $value->buybilldetailtotalprice;
$myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount_ret += $quantity;
}
$myproduct->noOfBills = 1;
#################
//handle indexes
$allDataArr[$productid] = array();
array_push($allDataArr[$productid], $myproduct);
$AllDataIndexArr[$productid] = array();
array_push($AllDataIndexArr[$productid], $unitid);
}
if (!isset($unitTotal[$myproduct->unitName])) {
$unitTotal[$myproduct->unitName] = 0;
}
if ($billType == 0) {
$unitTotal[$myproduct->unitName] += $quantity;
} else {
$unitTotal[$myproduct->unitName] -= $quantity;
}
}
############################################################################
##get store quantity
$quantityDataArr = R::getAll('SELECT productid,SUM(storedetail.productquantity) AS sumProductQuantity, GROUP_CONCAT(storeName SEPARATOR " - ") as storeName
FROM storedetail
join store on store.storeId = storedetail.storeid
WHERE productid in(' . implode(',', $productIdsArrAll) . ') and productquantity > 0
group by storedetail.productid');
$quantityDataArr = customArrayIndexOne($quantityDataArr, 'productid');
$totalStoreQuantity = 0;
$totalStoreQuantityPrice = 0;
foreach ($allDataArr as $value11) {
foreach ($value11 as $value) {
$productQuantity = (float) $quantityDataArr[$value->productId]['sumProductQuantity'];
switch ($Programsettingdata->Inventoryevaluation) {
case "first":
$pro_price = (float) $value->productBuyPrice;
break;
case "last":
$pro_price = (float) $value->lastbuyprice;
break;
case "mean":
$pro_price = (float) $value->meanbuyprice;
break;
case "last_discount":
$pro_price = (float) $value->lastbuyprice_withDiscount;
break;
case "mean_discount":
$pro_price = (float) $value->meanbuyprice_withDiscount;
break;
case "generalPrice":
$pro_price = (float) $value->overAllAveragePrice;
break;
case "tax":
$pro_price = (float) $value->lastbuyprice_withTax;
break;
case "mean_tax":
$pro_price = (float) $value->meanbuyprice_withTax;
break;
}
$value->productQuantity = $productQuantity;
$value->storeName = rtrim($quantityDataArr[$value->productId]['storeName'], '- ');;
$value->productLastPrice = $pro_price;
//
$value->storeQuantity += $value->productQuantity;
$value->storeQuantityPrice = $value->productQuantity * $value->productLastPrice;
//
$totalStoreQuantity += $value->productQuantity;
$totalStoreQuantityPrice += $value->productQuantity * $value->productLastPrice;
}
}
//get sellbill & retturnsell bill data for each product
$vatValue = 1 + ($Programsettingdata->vatValue / 100);
foreach ($allDataArr as $data) {
$productQuantity = 0;
$productPrice = 0;
$realCost = 0;
foreach ($data as $item) {
$sellBillDetailData = R::getAll('SELECT sellbilldetail.sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber, buyprice,
sellbilldiscount,sellbilldiscounttype,sellbilltotalbill
FROM sellbilldetail
join sellbill on(sellbilldetail.sellbillid = sellbill.sellbillid)
join productunit on(productunit.productid = sellbilldetail.sellbilldetailproductid and productunit.productunitid=sellbilldetail.productunitid and productunit.conditions = 0)
where sellbill.conditions = 0 AND sellbilldetailproductid = ' . $item->productId);
foreach ($sellBillDetailData as $data) {
$finalquantity = $data['sellbilldetailquantity'] * $data['productnumber'];
//sum realCost
// $realCost += $finalquantity * $data['buyprice'] * $vatValue;
$realCost += $finalquantity * $item->productBuyPrice * $vatValue;
//sum product quantity
$productQuantity += $finalquantity;
//sum product price
$dicount = $data['sellbilldiscount'];
if ($data['sellbilldiscounttype'] == 2) {
$dicount = ($data['sellbilldiscount'] / 100) * $data['sellbilltotalbill'];
}
if ($dicount != 0) {
$theDiscount = ($data['sellbilldetailtotalprice'] / $data['sellbilltotalbill']) * $dicount;
$theDiscount = round($theDiscount, 2);
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($Programsettingdata->valueadded == 0) {
$productPrice += $data['sellbilldetailtotalprice'] - $theDiscount;
} else {
$productPrice += $data['sellbilldetailtotalprice'] + $theDiscount;
}
}
$returnSellBillDetailData = R::getAll('SELECT returnsellbilldetail.returnsellbillid,returnsellbilldetailproductid,returnsellbilldetailquantity,returnsellbilldetail.productunitid,returnsellbilldetailtotalprice,productunit.productnumber, buyprice,
returnsellbilldiscount,returnsellbilldiscounttype,returnsellbilltotalbill
FROM returnsellbilldetail
join returnsellbill on (returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid)
join productunit on(productunit.productid = returnsellbilldetail.returnsellbilldetailproductid and productunit.productunitid=returnsellbilldetail.productunitid and productunit.conditions = 0)
where returnsellbill.conditions = 0 AND returnsellbilldetailproductid = ' . $item->productId);
foreach ($returnSellBillDetailData as $data) {
$finalquantity = $data['returnsellbilldetailquantity'] * $data['productnumber'];
//subtract realCost
$realCost -= $finalquantity * $item->productBuyPrice * $vatValue;
//subtract product quantity
$productQuantity -= $data['returnsellbilldetailquantity'] * $data['productnumber'];
//subtract product price
$dicount = $data['returnsellbilldiscount'];
if ($data['returnsellbilldiscounttype'] == 2) {
$dicount = $data['returnsellbilldiscount'] * $data['returnsellbilltotalbill'];
}
if ($dicount != 0) {
$billpecies = $returnSellBillDetailEX->queryBillNoOfPecies($data['returnsellbillid']);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
$theDiscount = ($data['returnsellbilldetailtotalprice'] / $data['returnsellbilltotalbill']) * $dicount;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($Programsettingdata->valueadded == 0) {
$productPrice -= $data['returnsellbilldetailtotalprice'] - $theDiscount;
} else {
$productPrice -= $data['returnsellbilldetailtotalprice'] + $theDiscount;
}
}
$sellBillAndRetDetailData = R::getAll('SELECT sellandruternbilldetail.sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellandruternbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber
,buyprice,sellbilldiscount,sellbilldiscounttype,sellbilltotalbill,sellbillprice,returnsellbillprice,sellandruternbilldetail.selltype
FROM sellandruternbilldetail
join sellbillandrutern on(sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid)
join productunit on(productunit.productid = sellandruternbilldetail.sellbilldetailproductid and productunit.productunitid=sellandruternbilldetail.productunitid and productunit.conditions = 0)
where sellbillandrutern.conditions = 0 AND sellbilldetailproductid = ' . $item->productId);
foreach ($sellBillAndRetDetailData as $data) {
$finalquantity = $data['sellbilldetailquantity'] * $data['productnumber'];
$dicount = $data['sellbilldiscount'];
if ($data['sellbilldiscounttype'] == 2) {
$dicount = ($data['sellbilldiscount'] / 100) * $data['sellbilltotalbill'];
}
if ($dicount != 0) {
$theDiscount = ($data['sellbilldetailtotalprice'] / $data['sellbilltotalbill']) * $dicount;
$theDiscount = round($theDiscount, 2);
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
if ($data['selltype'] == 0) {
//sum realCost
$realCost += $finalquantity * $item->productBuyPrice * $vatValue;
//sum product quantity
$productQuantity += $finalquantity;
//sum product price
################
if ($Programsettingdata->valueadded == 0) {
$productPrice += $data['sellbilldetailtotalprice'] - $theDiscount;
} else {
$productPrice += $data['sellbilldetailtotalprice'] + $theDiscount;
}
} else {
//subtract realCost
$realCost -= $finalquantity * $item->productBuyPrice * $vatValue;
//subtract product quantity
$productQuantity -= $finalquantity;
//subtract product price
################
if ($Programsettingdata->valueadded == 0) {
$productPrice -= $data['sellbilldetailtotalprice'] - $theDiscount;
} else {
$productPrice -= $data['sellbilldetailtotalprice'] + $theDiscount;
}
}
}
$item->netQuantity = $productQuantity;
$item->netPrice = $productPrice;
$item->realCost = $realCost;
}
}
$allCatIndexArr = array();
$allCatArr = array();
##total of cats
foreach ($allDataArr as $value11) {
foreach ($value11 as $value) {
$catId = $value->catId;
$unitid = $value->unitId;
##############
##check if cat is here
if (isset($allCatArr[$catId])) {
##cat exists
##check if unit is here
if (in_array($unitid, $allCatIndexArr[$catId])) {
##unit exists
$key2 = array_search($unitid, $allCatIndexArr[$catId]);
$mycat = $allCatArr[$catId][$key2];
## common data ##
$mycat->price += $value->price;
$mycat->priceWithTax += $value->priceWithTax;
$mycat->amount += $value->amount;
$mycat->price_ret += $value->price_ret;
$mycat->priceWithTax_ret += $value->priceWithTax_ret;
$mycat->amount_ret += $value->amount_ret;
$mycat->storeQuantity += $value->productQuantity;
$mycat->storeQuantityPrice += $value->storeQuantityPrice;
$mycat->netQuantity += $value->netQuantity;
$mycat->netPrice += $value->netPrice;
$mycat->realCost += $value->realCost;
#################
} else {
##new unit
$mycat = new productUnitData();
$mycat->unitId = $unitid;
$mycat->unitName = $value->unitName;
$mycat->productId = '';
$mycat->productName = '';
$mycat->catId = $value->catId;
$mycat->catName = $value->catName;
$mycat->price += $value->price;
$mycat->priceWithTax += $value->priceWithTax;
$mycat->amount += $value->amount;
$mycat->price_ret += $value->price_ret;
$mycat->priceWithTax_ret += $value->priceWithTax_ret;
$mycat->amount_ret += $value->amount_ret;
$mycat->storeQuantity += $value->productQuantity;
$mycat->storeQuantityPrice += $value->storeQuantityPrice;
$mycat->netQuantity += $value->netQuantity;
$mycat->netPrice += $value->netPrice;
$mycat->realCost += $value->realCost;
#################
array_push($allCatArr[$catId], $mycat);
array_push($allCatIndexArr[$catId], $unitid);
}
} else {
##new cat&& new unit
$mycat = new productUnitData();
$mycat->unitId = $value->unitId;
$mycat->unitName = $value->unitName;
$mycat->productId = '';
$mycat->productName = '';
$mycat->catId = $value->catId;
$mycat->catName = $value->catName;
$mycat->price = $value->price;
$mycat->priceWithTax += $value->priceWithTax;
$mycat->amount = $value->amount;
$mycat->price_ret = $value->price_ret;
$mycat->priceWithTax_ret += $value->priceWithTax_ret;
$mycat->amount_ret = $value->amount_ret;
$mycat->storeQuantity += $value->productQuantity;
$mycat->storeQuantityPrice += $value->storeQuantityPrice;
$mycat->netQuantity += $value->netQuantity;
$mycat->netPrice += $value->netPrice;
$mycat->realCost += $value->realCost;
#################
//handle indexes
$allCatArr[$catId] = array();
array_push($allCatArr[$catId], $mycat);
$allCatIndexArr[$catId] = array();
array_push($allCatIndexArr[$catId], $unitid);
}
}
}
// $smarty->assign("unitTotal", $unitTotal);
// $smarty->assign("totalOfTotals", $totalOfTotals);
$smarty->assign("totalStoreQuantity", $totalStoreQuantity);
$smarty->assign("totalStoreQuantityPrice", $totalStoreQuantityPrice);
$smarty->assign("allCatArr", $allCatArr);
$smarty->assign("productTotalArr", $productTotalArr);
$smarty->assign("allDataArr", $allDataArr);
}
function getData($queryString, $queryString1, $queryString1R, $queryString1SR, $order)
{
global $billsProductsBuyEX;
global $billsBuyDAO;
global $billsBuyEX;
global $buyBillDetailEX;
global $returnBuyBillDetailEX;
global $buyAndReturnBillDetailEX;
global $ProductDAO;
global $productCatDAO;
global $ProductEX;
global $smarty;
global $lastLevelCatIDS;
global $catsIDS;
global $productUnitEX;
global $unitEX;
global $myStoredetailEx;
global $ProgramsettingDAO;
class productUnitData
{
public $catId;
public $catName;
public $productId;
public $productName;
public $logo;
public $unitId;
public $unitName;
public $amount = 0;
public $price = 0;
public $amount_ret = 0;
public $price_ret = 0;
public $productQuantity = 0;
public $productLastPrice = 0;
public $noOfBills;
//for total
public $storeQuantity = 0;
public $storeQuantityPrice = 0;
}
$AllDataIndexArr = array();
$unitTotal = array();
$allDataArr = array();
global $allDataArr;
##if no search make it today
if ($queryString == " where 1 ") {
##date
$today = date("Y-m-d");
$queryString .= 'and date(billsbuy.billdate) = "' . $today . '" ';
$queryString1 .= 'and buybill.buybilldate = "' . $today . '" ';
$queryString1R .= 'and returnbuybill.returnbuybilldate = "' . $today . '" ';
$queryString1SR .= 'and buyandruternbill.buybilldate = "' . $today . '" ';
}
$Programsettingdata = $ProgramsettingDAO->load(1);
$billsData = $billsProductsBuyEX->queryAllGeneral($queryString);
$buyBillData = $buyBillDetailEX->queryAllGeneral($queryString1);
$returnBuyBillData = $returnBuyBillDetailEX->queryAllGeneral($queryString1R);
$buyAndReturnBillData = $buyAndReturnBillDetailEX->queryAllGeneral($queryString1SR);
$resultsCount = count($billsData) + count($buyBillData) + count($returnBuyBillData) + count($buyAndReturnBillData);
$smarty->assign("resultsCount", $resultsCount);
if ($resultsCount > 0) {
##unique products
##فاتورة البصريات
foreach ($billsData as $value) {
$productid = $value->productid;
$value->producttotalprice = round($value->producttotalprice, 2);
##all products come in unit of one piece ##get this unit
$unitData = $productUnitEX->getUnitDataOfUnityWithProductId($productid);
$unitid = 0;
$unitname = 'وحدة';
if (count($unitData) > 0) {
$unitid = $unitData->unitid;
$unitname = $unitData->conditions;
}
##check if product is here
if (isset($allDataArr[$productid])) {
##product exists
##check if unit is here
if (in_array($unitid, $AllDataIndexArr[$productid])) {
##unit exists
$key2 = array_search($unitid, $AllDataIndexArr[$productid]);
$myproduct = $allDataArr[$productid][$key2];
## common data ##
$myproduct->price += $value->producttotalprice;
$myproduct->amount += $value->productno;
$myproduct->noOfBills = $myproduct->noOfBills + 1;
#################
} else {
##new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
// $myproduct->productId = '';
// $myproduct->productName = '';
$myproduct->productId = $value->productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
$myproduct->price += $value->producttotalprice;
$myproduct->amount += $value->productno;
$myproduct->noOfBills = 1;
#################
array_push($allDataArr[$productid], $myproduct);
array_push($AllDataIndexArr[$productid], $unitid);
}
} else {
##new product&& new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
$myproduct->productId = $value->productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
$myproduct->price = $value->producttotalprice;
$myproduct->amount = $value->productno;
$myproduct->noOfBills = 1;
#################
//handle indexes
$allDataArr[$productid] = array();
array_push($allDataArr[$productid], $myproduct);
$AllDataIndexArr[$productid] = array();
array_push($AllDataIndexArr[$productid], $unitid);
}
if (!isset($unitTotal[$myproduct->unitName])) {
$unitTotal[$myproduct->unitName] = 0;
}
$unitTotal[$myproduct->unitName] += $value->productno;
}
##فاتورة المشتريات
foreach ($buyBillData as $value) {
$productid = $value->buybilldetailproductid;
$unitid = $value->unitid;
$unitname = $value->unitName;
$quantity = $value->buybilldetailquantity;
$value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
##############
##check if product is here
if (isset($allDataArr[$productid])) {
##product exists
##check if unit is here
if (in_array($unitid, $AllDataIndexArr[$productid])) {
##unit exists
$key2 = array_search($unitid, $AllDataIndexArr[$productid]);
$myproduct = $allDataArr[$productid][$key2];
## common data ##
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->amount += $quantity;
$myproduct->noOfBills = $myproduct->noOfBills + 1;
#################
} else {
##new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
// $myproduct->productId = '';
// $myproduct->productName = '';
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->amount += $quantity;
$myproduct->noOfBills = 1;
#################
array_push($allDataArr[$productid], $myproduct);
array_push($AllDataIndexArr[$productid], $unitid);
}
} else {
##new product&& new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
$myproduct->price = $value->buybilldetailtotalprice;
$myproduct->amount = $quantity;
$myproduct->noOfBills = 1;
#################
//handle indexes
$allDataArr[$productid] = array();
array_push($allDataArr[$productid], $myproduct);
$AllDataIndexArr[$productid] = array();
array_push($AllDataIndexArr[$productid], $unitid);
}
if (!isset($unitTotal[$myproduct->unitName])) {
$unitTotal[$myproduct->unitName] = 0;
}
$unitTotal[$myproduct->unitName] += $quantity;
}
##فاتورة مردود المشتريات
foreach ($returnBuyBillData as $value) {
$productid = $value->returnbuybilldetailproductid;
$unitid = $value->unitid;
$unitname = $value->unitName;
$quantity = $value->returnbuybilldetailquantity;
$value->returnbuybilldetailtotalprice = round($value->returnbuybilldetailtotalprice, 2);
##############
##check if product is here
if (isset($allDataArr[$productid])) {
##product exists
##check if unit is here
if (in_array($unitid, $AllDataIndexArr[$productid])) {
##unit exists
$key2 = array_search($unitid, $AllDataIndexArr[$productid]);
$myproduct = $allDataArr[$productid][$key2];
## common data ##
$myproduct->price_ret += $value->returnbuybilldetailtotalprice;
$myproduct->amount_ret += $quantity;
$myproduct->noOfBills = $myproduct->noOfBills + 1;
#################
} else {
##new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
// $myproduct->productId = '';
// $myproduct->productName = '';
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
$myproduct->price_ret += $value->returnbuybilldetailtotalprice;
$myproduct->amount_ret += $quantity;
$myproduct->noOfBills = 1;
#################
array_push($allDataArr[$productid], $myproduct);
array_push($AllDataIndexArr[$productid], $unitid);
}
} else {
##new product&& new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
$myproduct->price_ret = $value->returnbuybilldetailtotalprice;
$myproduct->amount_ret = $quantity;
$myproduct->noOfBills = 1;
#################
//handle indexes
$allDataArr[$productid] = array();
array_push($allDataArr[$productid], $myproduct);
$AllDataIndexArr[$productid] = array();
array_push($AllDataIndexArr[$productid], $unitid);
}
if (!isset($unitTotal[$myproduct->unitName])) {
$unitTotal[$myproduct->unitName] = 0;
}
$unitTotal[$myproduct->unitName] -= $quantity;
}
##فاتورة المشتريات والمردود
foreach ($buyAndReturnBillData as $value) {
$productid = $value->buybilldetailproductid;
$unitid = $value->unitid;
$unitname = $value->unitName;
$quantity = $value->buybilldetailquantity;
$billType = $value->billtype;
$value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
##############
##check if product is here
if (isset($allDataArr[$productid])) {
##product exists
##check if unit is here
if (in_array($unitid, $AllDataIndexArr[$productid])) {
##unit exists
$key2 = array_search($unitid, $AllDataIndexArr[$productid]);
$myproduct = $allDataArr[$productid][$key2];
## common data ##
if ($billType == 0) {
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->amount += $quantity;
} else {
$myproduct->price_ret += $value->buybilldetailtotalprice;
$myproduct->amount_ret += $quantity;
}
$myproduct->noOfBills = $myproduct->noOfBills + 1;
#################
} else {
##new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
// $myproduct->productId = '';
// $myproduct->productName = '';
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
if ($billType == 0) {
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->amount += $quantity;
} else {
$myproduct->price_ret += $value->buybilldetailtotalprice;
$myproduct->amount_ret += $quantity;
}
$myproduct->noOfBills = 1;
#################
array_push($allDataArr[$productid], $myproduct);
array_push($AllDataIndexArr[$productid], $unitid);
}
} else {
##new product&& new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
if ($billType == 0) {
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->amount += $quantity;
} else {
$myproduct->price_ret += $value->buybilldetailtotalprice;
$myproduct->amount_ret += $quantity;
}
$myproduct->noOfBills = 1;
#################
//handle indexes
$allDataArr[$productid] = array();
array_push($allDataArr[$productid], $myproduct);
$AllDataIndexArr[$productid] = array();
array_push($AllDataIndexArr[$productid], $unitid);
}
if (!isset($unitTotal[$myproduct->unitName])) {
$unitTotal[$myproduct->unitName] = 0;
}
if ($billType == 0) {
$unitTotal[$myproduct->unitName] += $quantity;
} else {
$unitTotal[$myproduct->unitName] -= $quantity;
}
}
##get store quantity
$totalStoreQuantity = 0;
$totalStoreQuantityPrice = 0;
foreach ($allDataArr as $value11) {
foreach ($value11 as $value) {
$productQuantity = $myStoredetailEx->getProductQuantityInAllStores($value->productId);
if (!isset($productQuantity) || empty($productQuantity)) {
$productQuantity = 0;
}
$pro = $ProductDAO->load($value->productId);
switch ($Programsettingdata->Inventoryevaluation) {
case "first":
$pro_price = (float) $pro->productBuyPrice;
break;
case "last":
$pro_price = (float) $pro->lastbuyprice;
break;
case "mean":
$pro_price = (float) $pro->meanbuyprice;
break;
case "last_discount":
$pro_price = (float) $pro->lastbuyprice_withDiscount;
break;
case "mean_discount":
$pro_price = (float) $pro->meanbuyprice_withDiscount;
break;
case "tax":
$pro_price = (float) $pro->lastbuyprice_withTax;
break;
case "mean_tax":
$pro_price = (float) $pro->meanbuyprice_withTax;
break;
default:
$pro_price = (float) $pro->overAllAveragePrice;
break;
}
$value->productQuantity = $productQuantity;
$value->productLastPrice = $pro_price;
//
$value->storeQuantity += $value->productQuantity;
$value->storeQuantityPrice = $value->productQuantity * $value->productLastPrice;
//
$totalStoreQuantity += $value->productQuantity;
$totalStoreQuantityPrice += $value->productQuantity * $value->productLastPrice;
}
}
$allCatIndexArr = array();
$allCatArr = array();
##total of cats
foreach ($allDataArr as $value11) {
foreach ($value11 as $value) {
$catId = $value->catId;
$unitid = $value->unitId;
##############
##check if cat is here
if (isset($allCatArr[$catId])) {
##cat exists
##check if unit is here
if (in_array($unitid, $allCatIndexArr[$catId])) {
##unit exists
$key2 = array_search($unitid, $allCatIndexArr[$catId]);
$mycat = $allCatArr[$catId][$key2];
## common data ##
$mycat->price += $value->price;
$mycat->amount += $value->amount;
$mycat->price_ret += $value->price_ret;
$mycat->amount_ret += $value->amount_ret;
$mycat->storeQuantity += $value->productQuantity;
$mycat->storeQuantityPrice += $value->storeQuantityPrice;
#################
} else {
##new unit
$mycat = new productUnitData();
$mycat->unitId = $unitid;
$mycat->unitName = $value->unitName;
$mycat->productId = '';
$mycat->productName = '';
$mycat->catId = $value->catId;
$mycat->catName = $value->catName;
$mycat->price += $value->price;
$mycat->amount += $value->amount;
$mycat->price_ret += $value->price_ret;
$mycat->amount_ret += $value->amount_ret;
$mycat->storeQuantity += $value->productQuantity;
$mycat->storeQuantityPrice += $value->storeQuantityPrice;
#################
array_push($allCatArr[$catId], $mycat);
array_push($allCatIndexArr[$catId], $unitid);
}
} else {
##new cat&& new unit
$mycat = new productUnitData();
$mycat->unitId = $value->unitId;
$mycat->unitName = $value->unitName;
$mycat->productId = '';
$mycat->productName = '';
$mycat->catId = $value->catId;
$mycat->catName = $value->catName;
$mycat->price = $value->price;
$mycat->amount = $value->amount;
$mycat->price_ret = $value->price_ret;
$mycat->amount_ret = $value->amount_ret;
$mycat->storeQuantity += $value->productQuantity;
$mycat->storeQuantityPrice += $value->storeQuantityPrice;
#################
//handle indexes
$allCatArr[$catId] = array();
array_push($allCatArr[$catId], $mycat);
$allCatIndexArr[$catId] = array();
array_push($allCatIndexArr[$catId], $unitid);
}
}
}
// ##finally get total foreach project
// $productTotalArr = array();
// global $productTotalArr;
// $totalOfTotals = 0;
// foreach ($allDataArr as $data) {
// $prototal = 0;
// $proid = 0;
// $i = 0;
// foreach ($data as $product) {
// $prototal+=$product->price;
// if ($i == 0) {
//
// $proid = $product->productId;
// }
// $i++;
// }
//
// $productTotalArr[$proid] = $prototal;
// $totalOfTotals += $prototal;
// }
// if ($order == "asc" || $order == "desc") {
// $allDataArr = sortByTotal($order, $allDataArr, $productTotalArr);
// }
}
// $smarty->assign("unitTotal", $unitTotal);
// $smarty->assign("totalOfTotals", $totalOfTotals);
$smarty->assign("totalStoreQuantity", $totalStoreQuantity);
$smarty->assign("totalStoreQuantityPrice", $totalStoreQuantityPrice);
$smarty->assign("allCatArr", $allCatArr);
$smarty->assign("productTotalArr", $productTotalArr);
$smarty->assign("allDataArr", $allDataArr);
}
//sort key value pair
function sortByTotal($type, $allDataArrTemp, $productTotalArrTemp)
{
$allDataArr = array();
//1-sort by date
foreach ($productTotalArrTemp as $key => $value) {
if ($type == "asc") {
$minTotalIndex = array_keys($productTotalArrTemp, min($productTotalArrTemp));
$minTotalIndex = $minTotalIndex[0];
unset($productTotalArrTemp[$minTotalIndex]);
$allDataArr[$minTotalIndex] = $allDataArrTemp[$minTotalIndex];
} elseif ($type == "desc") {
$maxTotalIndex = array_keys($productTotalArrTemp, max($productTotalArrTemp));
$maxTotalIndex = $maxTotalIndex[0];
unset($productTotalArrTemp[$maxTotalIndex]);
$allDataArr[$maxTotalIndex] = $allDataArrTemp[$maxTotalIndex];
}
}
return $allDataArr;
}
function getAllSubCat($catid, $mode)
{
global $productCatExt;
global $productCatDAO;
global $catsIDS;
global $lastLevelCatIDS;
//mode = 1 get all sub cats
//mode = 2 get last level cats
$result = $productCatExt->queryByParentExt($catid);
if (count($result) > 0) {
foreach ($result as $data) {
if ($mode == 1) {
$catsIDS .= ", " . $data->productCatId;
getAllSubCat($data->productCatId, $mode);
} elseif ($mode == 2) {
$childData = $productCatExt->queryByParentExt($data->productCatId);
if (count($childData) > 0) {
getAllSubCat($data->productCatId, $mode);
} else {
if (!empty($data->productCatId)) {
array_push($lastLevelCatIDS, $data->productCatId);
}
}
}
}
} else {
// if ($mode == 2) {
// $childData = $productCatExt->queryByParentExt($data->productCatId);
// if (count($childData) > 0) {
// getAllSubCat($data->productCatId, $mode);
// } else {
// if (!empty($data->productCatId)) {
// array_push($lastLevelCatIDS, $data->productCatId);
// }
// }
// }
}
}
function getSupplierDept($supplierId)
{
global $supplierEX;
$dept = " - ";
if ($supplierId > 0) {
$getsuppliercurrentDebt = $supplierEX->querysuppliercurrentDebt($supplierId);
//echo $getsuppliercurrentDebt;
$dept = $getsuppliercurrentDebt[0]->suppliercurrentDebt;
}
return $dept;
}
function getCLientPayedDeptData($supplierid, $datefrom, $dateto)
{
global $supplierDeptChangeExt;
global $buyBillDAO;
global $returnBuyBillDAO;
$payed = " - ";
if ($supplierid > 0) {
$queryString = ' and supplierid = ' . $supplierid;
if (isset($datefrom) && !empty($datefrom)) {
$queryString .= " AND date( supplierdebtchange.supplierdebtchangedate ) >= '" . $datefrom . "' ";
}
if (isset($dateto) && !empty($dateto)) {
$queryString .= "AND date( supplierdebtchange.supplierdebtchangedate ) <= '" . $dateto . "' ";
}
// $payedDeptAndPayedDeptInBills = $supplierDeptChangeExt->queryPayedDeptAndPayedDeptInBills($queryString);
$payedDeptAndPayedDeptInBills = $supplierDeptChangeExt->queryPayedDeptAndPayedDeptInBillsWithoutABS($queryString);
$payed = $payedDeptAndPayedDeptInBills;
$qString = ' AND supplierdebtchange.tablename NOT IN("supplierPayedDeptController.php","supplierController.php")';
if (isset($datefrom) && !empty($datefrom)) {
$qString .= " AND supplierdebtchange.supplierdebtchangedate >= '" . $datefrom . "' ";
}
if (isset($dateto) && !empty($dateto)) {
$qString .= "AND supplierdebtchange.supplierdebtchangedate <= '" . $dateto . "' ";
}
if (!$payed || $payed == null) {
$payed = 0;
}
$supDeptChanges = $supplierDeptChangeExt->queryBySupplierIdNotDeleted($supplierid, $qString);
if ($supDeptChanges) {
foreach ($supDeptChanges as $change) {
if (in_array($change->tablename, ["datedCheckedController.php", "checkwithdrawalController.php"])) {
if ($change->supplierdebtchangetype == 0)
$payed -= $change->supplierdebtchangeamount;
else
$payed += $change->supplierdebtchangeamount;
} elseif ($change->tablename == "buyBillController.php") {
$buybillid = $buyBillDAO->load($change->supplierdebtchangemodelid);
if ($buybillid->conditions == 0)
$payed += $buybillid->buybilltotalpayed;
} elseif ($change->tablename == "returnBuyBillController.php") {
$buyreturnbillid = $returnBuyBillDAO->load($change->supplierdebtchangemodelid);
if ($buyreturnbillid->conditions == 0)
$payed += $buyreturnbillid->returnbuybilltotalpayed;
} elseif ($change->tablename == "dailyentry.php") {
if ($change->supplierdebtchangetype == 0)
$payed -= $change->supplierdebtchangeamount;
else
$payed += $change->supplierdebtchangeamount;
}
}
}
}
return $payed;
}
function getDataNewWithClient($queryString, $queryString1, $queryString1R, $queryString1SR, $order, $queryStringClient, $queryStringClient1R, $queryStringClient1SR)
{
global $billsProductsBuyEX;
global $billsBuyDAO;
global $billsBuyEX;
global $buyBillDetailEX;
global $returnBuyBillDetailEX;
global $buyAndReturnBillDetailEX;
global $ProductDAO;
global $productCatDAO;
global $ProductEX;
global $smarty;
global $lastLevelCatIDS;
global $catsIDS;
global $productUnitEX;
global $unitEX;
global $myStoredetailEx;
global $ProgramsettingDAO;
global $returnSellBillDetailEX;
class productUnitData
{
public $catId;
public $catName;
public $productId;
public $productName;
public $logo;
public $unitId;
public $unitName;
public $amount = 0;
public $price = 0;
public $amount_ret = 0;
public $price_ret = 0;
public $productQuantity = 0;
public $productLastPrice = 0;
public $noOfBills;
public $priceWithTax;
public $priceWithTax_ret;
//prices
public $productBuyPrice = 0;
public $lastbuyprice = 0;
public $meanbuyprice = 0;
public $lastbuyprice_withDiscount = 0;
public $meanbuyprice_withDiscount = 0;
public $overAllAveragePrice = 0;
//for total
public $storeQuantity = 0;
public $storeQuantityPrice = 0;
}
$AllDataIndexArr = array();
$unitTotal = array();
$allDataArr = array();
global $allDataArr;
##if no search make it today
if ($queryString == " where 1 ") {
##date
$today = date("Y-m-d");
$queryString .= 'and date(billsbuy.billdate) = "' . $today . '" ';
$queryString1 .= 'and buybill.buybilldate = "' . $today . '" ';
$queryString1R .= 'and returnbuybill.returnbuybilldate = "' . $today . '" ';
$queryString1SR .= 'and buyandruternbill.buybilldate = "' . $today . '" ';
}
$Programsettingdata = $ProgramsettingDAO->load(1);
$buyBillData = R::getAll('SELECT buybillid,payedtax
FROM buybill ' . $queryString1 . ' AND conditions = 0');
$returnBuyBillData = R::getAll('SELECT returnbuybillid,payedtax
FROM returnbuybill ' . $queryString1R . ' AND conditions = 0');
$buyAndReturnBillData = R::getAll('SELECT buybillid,payedtax
FROM buyandruternbill ' . $queryString1SR . ' AND conditions = 0'); //AND sellbillandrutern.deletedsellid=0//order by sellandruternbilldetail.sellbilldetailproductid
// $buyBillData = $buyBillDetailEX->queryAllGeneral($queryString1);
// $returnBuyBillData = $returnBuyBillDetailEX->queryAllGeneral($queryString1R);
// $buyAndReturnBillData = $buyAndReturnBillDetailEX->queryAllGeneral($queryString1SR);
list($productIdsArrAll) = [[]];
############################################################################
##فاتورة المشتريات
list($billIDs) = [[]];
foreach ($buyBillData as $value) {
$billIDs[] = $value['buybillid'];
}
$billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
//1- bill details
$buyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,productunitid,buybilldetailquantity,buybilldetailtotalprice
FROM buybilldetail
where buybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
$buyBillDetail = customArrayIndexMany($buyBillDetail, 'buybillid');
$buyBillDataTemp = array();
list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
foreach ($buyBillData as $value) {
foreach ($buyBillDetail[$value['buybillid']] as $data) {
foreach ($data as $key1 => $value1) {
$value[$key1] = $value1;
}
$productIDsArr[] = (int) $value['buybilldetailproductid'];
$productUnitIDsArr[] = (int) $value['productunitid'];
$buyBillDataTemp[] = $value;
}
}
$buyBillData = $buyBillDataTemp;
$productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
$productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
$productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
//2- products
$productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
from product where productId in(' . implode(',', $productIDsArr) . ') ');
$productDataArr = customArrayIndexOne($productDataArr, 'productId');
$buyBillDataTemp = array();
foreach ($buyBillData as $value) {
$cat = $productDataArr[$value['buybilldetailproductid']]['productCatId'];
if ($cat !== null)
$productCatIdsArr[] = $cat;
foreach ($productDataArr[$value['buybilldetailproductid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
$buyBillDataTemp[] = $value;
}
$productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
$buyBillData = $buyBillDataTemp;
//3- cat,units
$productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
$productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
$productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
from productunit
join unit on unit.unitId = productunit.unitid
where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
$productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
$buyBillDataTemp = array();
foreach ($buyBillData as $value) {
$value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
//from key value pair to std class
$obj = new stdClass();
foreach ($value as $key1 => $value1) {
$obj->$key1 = $value1;
}
$buyBillDataTemp[] = $obj;
}
$buyBillData = $buyBillDataTemp;
foreach ($buyBillData as $value) {
$productid = $value->buybilldetailproductid;
$unitid = $value->unitid;
$unitname = $value->unitName;
$quantity = $value->buybilldetailquantity;
$value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
$taxes = $value->buybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
##############
##check if product is here
if (isset($allDataArr[$productid])) {
##product exists
##check if unit is here
if (in_array($unitid, $AllDataIndexArr[$productid])) {
##unit exists
$key2 = array_search($unitid, $AllDataIndexArr[$productid]);
$myproduct = $allDataArr[$productid][$key2];
## common data ##
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->priceWithTax += $value->buybilldetailtotalprice + $taxes;
$myproduct->amount += $quantity;
$myproduct->noOfBills = $myproduct->noOfBills + 1;
#################
} else {
##new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
// $myproduct->productId = '';
// $myproduct->productName = '';
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->priceWithTax += $value->buybilldetailtotalprice + $taxes;
$myproduct->amount += $quantity;
$myproduct->noOfBills = 1;
#################
array_push($allDataArr[$productid], $myproduct);
array_push($AllDataIndexArr[$productid], $unitid);
}
} else {
##new product&& new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
$myproduct->price = $value->buybilldetailtotalprice;
$myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount = $quantity;
$myproduct->noOfBills = 1;
#################
//handle indexes
$allDataArr[$productid] = array();
array_push($allDataArr[$productid], $myproduct);
$AllDataIndexArr[$productid] = array();
array_push($AllDataIndexArr[$productid], $unitid);
}
if (!isset($unitTotal[$myproduct->unitName])) {
$unitTotal[$myproduct->unitName] = 0;
}
$unitTotal[$myproduct->unitName] += $quantity;
}
############################################################################
##فاتورة مردود المشتريات
list($billIDs) = [[]];
foreach ($returnBuyBillData as $value) {
$billIDs[] = $value['returnbuybillid'];
}
$billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
//1- bill details
$buyBillDetail = R::getAll('SELECT returnbuybillid,returnbuybilldetailproductid,productunitid,returnbuybilldetailquantity,returnbuybilldetailtotalprice
FROM returnbuybilldetail
where returnbuybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
$buyBillDetail = customArrayIndexMany($buyBillDetail, 'returnbuybillid');
$buyBillDataTemp = array();
list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
foreach ($returnBuyBillData as $value) {
foreach ($buyBillDetail[$value['returnbuybillid']] as $data) {
foreach ($data as $key1 => $value1) {
$value[$key1] = $value1;
}
$productIDsArr[] = (int) $data['returnbuybilldetailproductid'];
$productUnitIDsArr[] = (int) $data['productunitid'];
$buyBillDataTemp[] = $value;
}
}
$returnBuyBillData = $buyBillDataTemp;
$productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
$productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
$productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
//2- products
$productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
from product where productId in(' . implode(',', $productIDsArr) . ') ');
$productDataArr = customArrayIndexOne($productDataArr, 'productId');
$buyBillDataTemp = array();
foreach ($returnBuyBillData as $value) {
$cat = $productDataArr[$value['returnbuybilldetailproductid']]['productCatId'];
if ($cat !== null)
$productCatIdsArr[] = $cat;
foreach ($productDataArr[$value['returnbuybilldetailproductid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
$buyBillDataTemp[] = $value;
}
$productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
$returnBuyBillData = $buyBillDataTemp;
//3- cat,units
$productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
$productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
$productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
from productunit
join unit on unit.unitId = productunit.unitid
where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
$productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
$buyBillDataTemp = array();
foreach ($returnBuyBillData as $value) {
$value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
//from key value pair to std class
$obj = new stdClass();
foreach ($value as $key1 => $value1) {
$obj->$key1 = $value1;
}
$buyBillDataTemp[] = $obj;
}
$returnBuyBillData = $buyBillDataTemp;
foreach ($returnBuyBillData as $value) {
$productid = $value->returnbuybilldetailproductid;
$unitid = $value->unitid;
$unitname = $value->unitName;
$quantity = $value->returnbuybilldetailquantity;
$value->returnbuybilldetailtotalprice = round($value->returnbuybilldetailtotalprice, 2);
$taxes = $value->returnbuybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
##############
##check if product is here
if (isset($allDataArr[$productid])) {
##product exists
##check if unit is here
if (in_array($unitid, $AllDataIndexArr[$productid])) {
##unit exists
$key2 = array_search($unitid, $AllDataIndexArr[$productid]);
$myproduct = $allDataArr[$productid][$key2];
## common data ##
$myproduct->price_ret += $value->returnbuybilldetailtotalprice;
$myproduct->priceWithTax_ret += $value->returnbuybilldetailtotalprice + $taxes;
$myproduct->amount_ret += $quantity;
$myproduct->noOfBills = $myproduct->noOfBills + 1;
#################
} else {
##new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
// $myproduct->productId = '';
// $myproduct->productName = '';
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
$myproduct->price_ret += $value->returnbuybilldetailtotalprice;
$myproduct->priceWithTax_ret += $value->returnbuybilldetailtotalprice + $taxes;
$myproduct->amount_ret += $quantity;
$myproduct->noOfBills = 1;
#################
array_push($allDataArr[$productid], $myproduct);
array_push($AllDataIndexArr[$productid], $unitid);
}
} else {
##new product&& new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
$myproduct->price_ret = $value->returnbuybilldetailtotalprice;
$myproduct->priceWithTax_ret = $value->returnbuybilldetailtotalprice + $taxes;
$myproduct->amount_ret = $quantity;
$myproduct->noOfBills = 1;
#################
//handle indexes
$allDataArr[$productid] = array();
array_push($allDataArr[$productid], $myproduct);
$AllDataIndexArr[$productid] = array();
array_push($AllDataIndexArr[$productid], $unitid);
}
if (!isset($unitTotal[$myproduct->unitName])) {
$unitTotal[$myproduct->unitName] = 0;
}
$unitTotal[$myproduct->unitName] -= $quantity;
}
############################################################################
##فاتورة المشتريات والمردود
list($billIDs) = [[]];
foreach ($buyAndReturnBillData as $value) {
$billIDs[] = $value['buybillid'];
}
$billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
//1- bill details
$buyBillDetail = R::getAll('SELECT buybillid,buybilldetailproductid,productunitid,buybilldetailquantity,buybilldetailtotalprice
FROM buyandruternbilldetail
where buybillid in(' . implode(',', $billIDs) . ')'); //-- order by buybilldetail.buybilldetailproductid
$buyBillDetail = customArrayIndexMany($buyBillDetail, 'buybillid');
$buyBillDataTemp = array();
list($productIDsArr, $productUnitIDsArr, $productCatIdsArr) = [[], [], []];
foreach ($buyAndReturnBillData as $value) {
foreach ($buyBillDetail[$value['buybillid']] as $data) {
foreach ($data as $key1 => $value1) {
$value[$key1] = $value1;
}
$productIDsArr[] = (int) $value['buybilldetailproductid'];
$productUnitIDsArr[] = (int) $value['productunitid'];
$buyBillDataTemp[] = $value;
}
}
$buyAndReturnBillData = $buyBillDataTemp;
$productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
$productUnitIDsArr = !empty($productUnitIDsArr) ? array_unique($productUnitIDsArr) : [0];
$productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
//2- products
$productDataArr = R::getAll('select productId,product.productName as productName,product.productCatId,logo,productBuyPrice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,overAllAveragePrice,lastbuyprice_withTax,meanbuyprice_withTax
from product where productId in(' . implode(',', $productIDsArr) . ') ');
$productDataArr = customArrayIndexOne($productDataArr, 'productId');
$buyBillDataTemp = array();
foreach ($buyAndReturnBillData as $value) {
$cat = $productDataArr[$value['buybilldetailproductid']]['productCatId'];
if ($cat !== null)
$productCatIdsArr[] = $cat;
foreach ($productDataArr[$value['buybilldetailproductid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
$buyBillDataTemp[] = $value;
}
$productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
$buyAndReturnBillData = $buyBillDataTemp;
//3- cat,units
$productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
$productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
$productUnitDataArr = R::getAll('select productunitid,unit.unitId as unitid,unit.unitName as unitName
from productunit
join unit on unit.unitId = productunit.unitid
where productunitid in(' . implode(',', $productUnitIDsArr) . ') ');
$productUnitDataArr = customArrayIndexOne($productUnitDataArr, 'productunitid');
$buyBillDataTemp = array();
foreach ($buyAndReturnBillData as $value) {
$value['productCatName'] = $productCatDataArr[$value['productCatId']]['productCatName'];
foreach ($productUnitDataArr[$value['productunitid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
//from key value pair to std class
$obj = new stdClass();
foreach ($value as $key1 => $value1) {
$obj->$key1 = $value1;
}
$buyBillDataTemp[] = $obj;
}
$buyAndReturnBillData = $buyBillDataTemp;
foreach ($buyAndReturnBillData as $value) {
$productid = $value->buybilldetailproductid;
$unitid = $value->unitid;
$unitname = $value->unitName;
$quantity = $value->buybilldetailquantity;
$billType = $value->billtype;
$value->buybilldetailtotalprice = round($value->buybilldetailtotalprice, 2);
$taxes = $value->buybilldetailtotalprice * ($value->payedtax / 100); //buybilldetailtotalprice include the row tax, now calc its share of billtax
##############
##check if product is here
if (isset($allDataArr[$productid])) {
##product exists
##check if unit is here
if (in_array($unitid, $AllDataIndexArr[$productid])) {
##unit exists
$key2 = array_search($unitid, $AllDataIndexArr[$productid]);
$myproduct = $allDataArr[$productid][$key2];
## common data ##
if ($billType == 0) {
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount += $quantity;
} else {
$myproduct->price_ret += $value->buybilldetailtotalprice;
$myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount_ret += $quantity;
}
$myproduct->noOfBills = $myproduct->noOfBills + 1;
#################
} else {
##new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
// $myproduct->productId = '';
// $myproduct->productName = '';
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
if ($billType == 0) {
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount += $quantity;
} else {
$myproduct->price_ret += $value->buybilldetailtotalprice;
$myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount_ret += $quantity;
}
$myproduct->noOfBills = 1;
#################
array_push($allDataArr[$productid], $myproduct);
array_push($AllDataIndexArr[$productid], $unitid);
}
} else {
##new product&& new unit
$myproduct = new productUnitData();
$myproduct->unitId = $unitid;
$myproduct->unitName = $unitname;
## common data ##
$myproduct->productId = $productid;
$myproduct->productName = $value->productName . " / " . $value->productCatName;
$myproduct->catId = $value->productCatId;
$myproduct->catName = $value->productCatName;
$myproduct->logo = $value->logo;
##prices
$myproduct->productBuyPrice = $value->productBuyPrice;
$myproduct->lastbuyprice = $value->lastbuyprice;
$myproduct->lastbuyprice_withDiscount = $value->lastbuyprice_withDiscount;
$myproduct->meanbuyprice = $value->meanbuyprice;
$myproduct->meanbuyprice_withDiscount = $value->meanbuyprice_withDiscount;
$myproduct->overAllAveragePrice = $value->overAllAveragePrice;
if ($billType == 0) {
$myproduct->price += $value->buybilldetailtotalprice;
$myproduct->priceWithTax = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount += $quantity;
} else {
$myproduct->price_ret += $value->buybilldetailtotalprice;
$myproduct->priceWithTax_ret = $value->buybilldetailtotalprice + $taxes;
$myproduct->amount_ret += $quantity;
}
$myproduct->noOfBills = 1;
#################
//handle indexes
$allDataArr[$productid] = array();
array_push($allDataArr[$productid], $myproduct);
$AllDataIndexArr[$productid] = array();
array_push($AllDataIndexArr[$productid], $unitid);
}
if (!isset($unitTotal[$myproduct->unitName])) {
$unitTotal[$myproduct->unitName] = 0;
}
if ($billType == 0) {
$unitTotal[$myproduct->unitName] += $quantity;
} else {
$unitTotal[$myproduct->unitName] -= $quantity;
}
}
############################################################################
##get store quantity
$quantityDataArr = R::getAll('SELECT productid,SUM(storedetail.productquantity) AS sumProductQuantity
FROM storedetail
WHERE productid in(' . implode(',', $productIdsArrAll) . ')
group by storedetail.productid');
$quantityDataArr = customArrayIndexOne($quantityDataArr, 'productid');
$totalStoreQuantity = 0;
$totalStoreQuantityPrice = 0;
foreach ($allDataArr as $value11) {
foreach ($value11 as $value) {
$productQuantity = (float) $quantityDataArr[$value->productId]['sumProductQuantity'];
switch ($Programsettingdata->Inventoryevaluation) {
case "first":
$pro_price = (float) $value->productBuyPrice;
break;
case "last":
$pro_price = (float) $value->lastbuyprice;
break;
case "mean":
$pro_price = (float) $value->meanbuyprice;
break;
case "last_discount":
$pro_price = (float) $value->lastbuyprice_withDiscount;
break;
case "mean_discount":
$pro_price = (float) $value->meanbuyprice_withDiscount;
break;
case "generalPrice":
$pro_price = (float) $value->overAllAveragePrice;
break;
case "tax":
$pro_price = (float) $value->lastbuyprice_withTax;
break;
case "mean_tax":
$pro_price = (float) $value->meanbuyprice_withTax;
break;
}
$value->productQuantity = $productQuantity;
$value->productLastPrice = $pro_price;
//
$value->storeQuantity += $value->productQuantity;
$value->storeQuantityPrice = $value->productQuantity * $value->productLastPrice;
//
$totalStoreQuantity += $value->productQuantity;
$totalStoreQuantityPrice += $value->productQuantity * $value->productLastPrice;
}
}
//get sellbill & retturnsell bill data for each product
$vatValue = 1 + ($Programsettingdata->vatValue / 100);
foreach ($allDataArr as $data) {
$productQuantity = 0;
$productPrice = 0;
$realCost = 0;
foreach ($data as $item) {
$sellBillDetailData = R::getAll('SELECT sellbilldetail.sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber, buyprice,
sellbilldiscount,sellbilldiscounttype,sellbilltotalbill
FROM sellbilldetail
join sellbill on(sellbilldetail.sellbillid = sellbill.sellbillid)
join productunit on(productunit.productid = sellbilldetail.sellbilldetailproductid and productunit.productunitid=sellbilldetail.productunitid and productunit.conditions = 0)
where sellbill.conditions = 0 AND sellbilldetailproductid = ' . $item->productId . ' ' . $queryStringClient);
foreach ($sellBillDetailData as $data) {
$finalquantity = $data['sellbilldetailquantity'] * $data['productnumber'];
//sum realCost
$realCost += $finalquantity * $item->productBuyPrice * $vatValue;
//sum product quantity
$productQuantity += $finalquantity;
//sum product price
$dicount = $data['sellbilldiscount'];
if ($data['sellbilldiscounttype'] == 2) {
$dicount = ($data['sellbilldiscount'] / 100) * $data['sellbilltotalbill'];
}
if ($dicount != 0) {
$theDiscount = ($data['sellbilldetailtotalprice'] / $data['sellbilltotalbill']) * $dicount;
$theDiscount = round($theDiscount, 2);
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($Programsettingdata->valueadded == 0) {
$productPrice += $data['sellbilldetailtotalprice'] - $theDiscount;
} else {
$productPrice += $data['sellbilldetailtotalprice'] + $theDiscount;
}
}
$returnSellBillDetailData = R::getAll('SELECT returnsellbilldetail.returnsellbillid,returnsellbilldetailproductid,returnsellbilldetailquantity,returnsellbilldetail.productunitid,returnsellbilldetailtotalprice,productunit.productnumber, buyprice,
returnsellbilldiscount,returnsellbilldiscounttype,returnsellbilltotalbill
FROM returnsellbilldetail
join returnsellbill on (returnsellbill.returnsellbillid = returnsellbilldetail.returnsellbillid)
join productunit on(productunit.productid = returnsellbilldetail.returnsellbilldetailproductid and productunit.productunitid=returnsellbilldetail.productunitid and productunit.conditions = 0)
where returnsellbilldetail.conditions = 0 AND returnsellbilldetailproductid = ' . $item->productId . ' ' . $queryStringClient1R);
foreach ($returnSellBillDetailData as $data) {
$finalquantity = $data['returnsellbilldetailquantity'] * $data['productnumber'];
//sum realCost
$realCost -= $finalquantity * $item->productBuyPrice * $vatValue;
//subtract product quantity
$productQuantity -= $data['returnsellbilldetailquantity'] * $data['productnumber'];
//subtract product price
$dicount = $data['returnsellbilldiscount'];
if ($data['returnsellbilldiscounttype'] == 2) {
$dicount = $data['returnsellbilldiscount'] * $data['returnsellbilltotalbill'];
}
if ($dicount != 0) {
$billpecies = $returnSellBillDetailEX->queryBillNoOfPecies($data['returnsellbillid']);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
$theDiscount = ($data['returnsellbilldetailtotalprice'] / $data['returnsellbilltotalbill']) * $dicount;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($Programsettingdata->valueadded == 0) {
$productPrice -= $data['returnsellbilldetailtotalprice'] - $theDiscount;
} else {
$productPrice -= $data['returnsellbilldetailtotalprice'] + $theDiscount;
}
}
$item->netQuantity = $productQuantity;
$item->netPrice = $productPrice;
$item->realCost = $realCost;
}
}
$allCatIndexArr = array();
$allCatArr = array();
##total of cats
foreach ($allDataArr as $value11) {
foreach ($value11 as $value) {
$catId = $value->catId;
$unitid = $value->unitId;
##############
##check if cat is here
if (isset($allCatArr[$catId])) {
##cat exists
##check if unit is here
if (in_array($unitid, $allCatIndexArr[$catId])) {
##unit exists
$key2 = array_search($unitid, $allCatIndexArr[$catId]);
$mycat = $allCatArr[$catId][$key2];
## common data ##
$mycat->price += $value->price;
$mycat->priceWithTax += $value->priceWithTax;
$mycat->amount += $value->amount;
$mycat->price_ret += $value->price_ret;
$mycat->priceWithTax_ret += $value->priceWithTax_ret;
$mycat->amount_ret += $value->amount_ret;
$mycat->storeQuantity += $value->productQuantity;
$mycat->storeQuantityPrice += $value->storeQuantityPrice;
$mycat->netQuantity += $value->netQuantity;
$mycat->netPrice += $value->netPrice;
$mycat->realCost += $value->realCost;
#################
} else {
##new unit
$mycat = new productUnitData();
$mycat->unitId = $unitid;
$mycat->unitName = $value->unitName;
$mycat->productId = '';
$mycat->productName = '';
$mycat->catId = $value->catId;
$mycat->catName = $value->catName;
$mycat->price += $value->price;
$mycat->priceWithTax += $value->priceWithTax;
$mycat->amount += $value->amount;
$mycat->price_ret += $value->price_ret;
$mycat->priceWithTax_ret += $value->priceWithTax_ret;
$mycat->amount_ret += $value->amount_ret;
$mycat->storeQuantity += $value->productQuantity;
$mycat->storeQuantityPrice += $value->storeQuantityPrice;
$mycat->netQuantity += $value->netQuantity;
$mycat->netPrice += $value->netPrice;
$mycat->realCost += $value->realCost;
#################
array_push($allCatArr[$catId], $mycat);
array_push($allCatIndexArr[$catId], $unitid);
}
} else {
##new cat&& new unit
$mycat = new productUnitData();
$mycat->unitId = $value->unitId;
$mycat->unitName = $value->unitName;
$mycat->productId = '';
$mycat->productName = '';
$mycat->catId = $value->catId;
$mycat->catName = $value->catName;
$mycat->price = $value->price;
$mycat->priceWithTax += $value->priceWithTax;
$mycat->amount = $value->amount;
$mycat->price_ret = $value->price_ret;
$mycat->priceWithTax_ret += $value->priceWithTax_ret;
$mycat->amount_ret = $value->amount_ret;
$mycat->storeQuantity += $value->productQuantity;
$mycat->storeQuantityPrice += $value->storeQuantityPrice;
$mycat->netQuantity += $value->netQuantity;
$mycat->netPrice += $value->netPrice;
$mycat->realCost += $value->realCost;
#################
//handle indexes
$allCatArr[$catId] = array();
array_push($allCatArr[$catId], $mycat);
$allCatIndexArr[$catId] = array();
array_push($allCatIndexArr[$catId], $unitid);
}
}
}
// $smarty->assign("unitTotal", $unitTotal);
// $smarty->assign("totalOfTotals", $totalOfTotals);
$smarty->assign("totalStoreQuantity", $totalStoreQuantity);
$smarty->assign("totalStoreQuantityPrice", $totalStoreQuantityPrice);
$smarty->assign("allCatArr", $allCatArr);
$smarty->assign("productTotalArr", $productTotalArr);
$smarty->assign("allDataArr", $allDataArr);
}