File: /home/mostafedeg/public_html/erp/controllers/salesreport.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');
//Sellbilldetail
require_once('../models/dao/SellbilldetailDAO.class.php');
require_once('../models/dto/Sellbilldetail.class.php');
require_once('../models/mysql/SellbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/SellbilldetailMySqlExtDAO.class.php');
//Returnsellbilldetail
require_once('../models/dao/ReturnsellbilldetailDAO.class.php');
require_once('../models/dto/Returnsellbilldetail.class.php');
require_once('../models/mysql/ReturnsellbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/ReturnsellbilldetailMySqlExtDAO.class.php');
//Sellandruternbilldetail
require_once('../models/dao/SellandruternbilldetailDAO.class.php');
require_once('../models/dto/Sellandruternbilldetail.class.php');
require_once('../models/mysql/SellandruternbilldetailMySqlDAO.class.php');
require_once('../models/mysql/ext/SellandruternbilldetailMySqlExtDAO.class.php');
//Billsproduct
require_once('../models/dao/BillsproductsDAO.class.php');
require_once('../models/dto/Billsproduct.class.php');
require_once('../models/mysql/BillsproductsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsproductsMySqlExtDAO.class.php');
//Billsreturnproduct
require_once('../models/dao/BillsreturnproductsDAO.class.php');
require_once('../models/dto/Billsreturnproduct.class.php');
require_once('../models/mysql/BillsreturnproductsMySqlDAO.class.php');
require_once('../models/mysql/ext/BillsreturnproductsMySqlExtDAO.class.php');
/////////////////////product////////////////////////
require_once('../models/dao/ProductDAO.class.php');
require_once('../models/dto/Product.class.php');
require_once('../models/mysql/ProductMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductMySqlExtDAO.class.php');
//Programsetting
require_once('../models/dao/ProgramsettingsDAO.class.php');
require_once('../models/dto/Programsetting.class.php');
require_once('../models/mysql/ProgramsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/ProgramsettingsMySqlExtDAO.class.php');
//Client
require_once('../models/dao/ClientDAO.class.php');
require_once('../models/dto/Client.class.php');
require_once('../models/mysql/ClientMySqlDAO.class.php');
require_once('../models/mysql/ext/ClientMySqlExtDAO.class.php');
//Store
require_once('../models/dao/StoreDAO.class.php');
require_once('../models/dto/Store.class.php');
require_once('../models/mysql/StoreMySqlDAO.class.php');
require_once('../models/mysql/ext/StoreMySqlExtDAO.class.php');
//Branch
require_once('../models/dao/BranchDAO.class.php');
require_once('../models/dto/Branch.class.php');
require_once('../models/mysql/BranchMySqlDAO.class.php');
require_once('../models/mysql/ext/BranchMySqlExtDAO.class.php');
//typeClient
require_once('../models/dao/TypeClientDAO.class.php');
require_once('../models/dto/TypeClient.class.php');
require_once('../models/mysql/TypeClientMySqlDAO.class.php');
require_once('../models/mysql/ext/TypeClientMySqlExtDAO.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');
//Government
require_once('../models/dao/GovernmentDAO.class.php');
require_once('../models/dto/Government.class.php');
require_once('../models/mysql/GovernmentMySqlDAO.class.php');
require_once('../models/mysql/ext/GovernmentMySqlExtDAO.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();
//Sellbilldetail
$sellbilldetail = new Sellbilldetail();
$sellbilldetailDAO = new SellbilldetailMySqlDAO();
$sellbilldetailEX = new SellbilldetailMySqlExtDAO();
//Returnsellbilldetail
$returnSellBillDetail = new Returnsellbilldetail();
$returnSellBillDetailDAO = new ReturnsellbilldetailMySqlDAO();
$returnSellBillDetailEX = new ReturnsellbilldetailMySqlExtDAO();
//Sellandruternbilldetail
$sellAndRuternBillDetail = new Sellandruternbilldetail();
$sellAndRuternBillDetailDAO = new SellandruternbilldetailMySqlDAO();
$sellAndRuternBillDetailEX = new SellandruternbilldetailMySqlExtDAO();
##################################
//bill
$bills = new Bill();
$billsDAO = new BillsMySqlDAO();
$billsEX = new BillsMySqlExtDAO();
//billsproducts
$billsProducts = new Billsproduct();
$billsProductsDAO = new BillsproductsMySqlDAO();
$billsProductsEX = new BillsproductsMySqlExtDAO();
//bill
$billsReturn = new Billsreturn();
$billsReturnDAO = new BillsreturnMySqlDAO();
$billsReturnEX = new BillsreturnMySqlExtDAO();
//billsReturnproducts
$billsReturnProducts = new Billsreturnproduct();
$billsReturnProductsDAO = new BillsreturnproductsMySqlDAO();
$billsReturnProductsEX = new BillsreturnproductsMySqlExtDAO();
///////////////product///////////////
$ProductDAO = new ProductMySqlDAO();
$Product = new Product();
$ProductEX = new ProductMySqlExtDAO();
//Productcat
$productCatDAO = new ProductcatMySqlDAO();
$productCatExt = new ProductcatMySqlExtDAO();
$youtubeLink = new YoutubeLink();
$youtubeLinkDAO = new YoutubeLinkMySqlDAO();
$youtubeLinkEX = new YoutubeLinkMySqlExtDAO();
//Client
$client = new Client();
$clientDAO = new ClientMySqlDAO();
$clientExt = new ClientMySqlExtDAO();
eval(base64_decode("ZnVuY3Rpb24gZ2VuZXJhdGVVVUlEKCkgewogICAgJGhvc3RuYW1lID0gZXhwbG9kZSgnY29udHJvbGxlcnMnLCAkX1NFUlZFUlsnU0NSSVBUX1VSSSddKVswXTsgLy9nZXRob3N0bmFtZSgpOwogICAgJGhvc3RuYW1lID0gc3RyX3JlcGxhY2UoYXJyYXkoImh0dHA6Ly8iLCAiaHR0cHM6Ly8iKSwgIiIsICRob3N0bmFtZSk7CgogICAgLy8gR2VuZXJhdGUgYW4gTUQ1IGhhc2ggb2YgdGhlIGlucHV0CiAgICAkZGF0YSA9IG1kNSgkaG9zdG5hbWUpOwogICAgLy8gRm9ybWF0IHRoZSBoYXNoIGFzIGEgVVVJRAogICAgJHV1aWQgPSBzcHJpbnRmKAogICAgICAgICAgICAnJTA4cy0lMDRzLSUwNHgtJTA0eC0lMTJzJywKICAgICAgICAgICAgc3Vic3RyKCRkYXRhLCAwLCA4KSwKICAgICAgICAgICAgc3Vic3RyKCRkYXRhLCA4LCA0KSwKICAgICAgICAgICAgKGhleGRlYyhzdWJzdHIoJGRhdGEsIDEyLCA0KSkgJiAweDBmZmYpIHwgMHg0MDAwLAogICAgICAgICAgICAoaGV4ZGVjKHN1YnN0cigkZGF0YSwgMTYsIDQpKSAmIDB4M2ZmZikgfCAweDgwMDAsCiAgICAgICAgICAgIHN1YnN0cigkZGF0YSwgMjAsIDEyKQogICAgKTsKICAgICR1dWlkID0gc3RyX3JlcGxhY2UoYXJyYXkoJy0nKSwgJycsICR1dWlkKTsKICAgIHJldHVybiAkdXVpZDsKfQ=="));
eval(base64_decode("ZnVuY3Rpb24gc2VyaWFsY2hlY2soKSB7CiAgICBpZiAoc3RydG91cHBlcihzdWJzdHIoUEhQX09TLCAwLCAzKSkgPT09ICdXSU4nKSB7CiAgICAgICAgLy91dWlkIG9mIHdpbmRvd3MKICAgICAgICBleGVjKCdDOlxXaW5kb3dzXFN5c3RlbTMyXHdiZW1cd21pYyBjc3Byb2R1Y3QgZ2V0ICJVVUlEIicsICRtYWNhZGRyZXNzLCAkaXNFeGVjdXRlZCk7CiAgICAgICAgJG1hY2FkZHJlc3MgPSAkbWFjYWRkcmVzc1sxXTsKICAgICAgICAkbWFjID0gc3RyX3JlcGxhY2UoYXJyYXkoJy0nKSwgJycsICRtYWNhZGRyZXNzKTsKICAgIH0gZWxzZSB7Ly9ub3Qgd2luZG93cyB1c2UgbGludXggbWFjCiAgICAgICAgJG1hYyA9IGdlbmVyYXRlVVVJRCgpOwogICAgfQoKICAgICRyYW5kb21fc2FsdCA9IGhhc2goJ3NoYTUxMicsICcxMzA1Jyk7CiAgICAvLyBDcmVhdGUgc2FsdGVkIHBhc3N3b3JkCiAgICAkaGFzaGVkTWFjID0gaGFzaCgnc2hhNTEyJywgJG1hYyAuICRyYW5kb21fc2FsdCk7CiAgICByZXR1cm4gJGhhc2hlZE1hYzsKfQ=="));
eval(base64_decode("JHNlcmlhbENhbCA9IHNlcmlhbGNoZWNrKCk7CmlmICgoJHByb2ctPnNlcmlhbCAhPSAkc2VyaWFsQ2FsICYmICRwcm9nLT5zZXJpYWwyICE9ICRzZXJpYWxDYWwpIHx8ICRfU0VTU0lPTlsnbGljX2RhdGV0byddIDwgZGF0ZSgnWS1tLWQnKSkgewogICAgLy9zZW5kIHRoZSB1c2VyIHRvIHRoZSBsb2dvdXQgcGFnZQogICAgaGVhZGVyKCJsb2NhdGlvbjpsb2dvdXQucGhwIik7CiAgICBleGl0KCk7Cn0="));
//
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
//
$clientDAO = new ClientMySqlDAO();
$storeDAO = new StoreMySqlDAO();
$branchDAO = new BranchMySqlDAO();
// Government
$government = new Government();
$governmentDAO = new GovernmentMySqlDAO();
$governmentExt = new GovernmentMySqlExtDAO();
//typeClient
$type = new TypeClient();
$TypeClientDAO = new TypeClientMySqlDAO();
$TypeClientEX = new TypeClientMySqlExtDAO();
//
eval(base64_decode("aWYgKCFmdW5jdGlvbl9leGlzdHMoJ3NlcmlhbGNoZWNrJykpIHsKICAgIENVUkxfSVQyKGFycmF5KCksICdzZWxsYmlsbEFqYXhDb250cm9sbGVyLnBocD9kbz1jYXRjaCcpOwogICAgaGVhZGVyKCJsb2NhdGlvbjpsb2dvdXQucGhwIik7Cn0="));
$myUserGroupRecord = new UsergroupMySqlDAO();
$userGroupId = $_SESSION['usergroupid'];
$loadData = $myUserGroupRecord->load($userGroupId);
$smarty->assign("loadData", $loadData);
$myStoreEx = new StoreMySqlExtDAO();
$lastLevelCatIDS = array();
$catsIDS = "";
$mainCat;
//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);
$smarty->assign("Programsetting", $Programsetting);
##########################################################################
//prepare search tools
##التصنيفات
$catDataReport = getCategoryChilds();
$smarty->assign("catDataReport", $catDataReport[1]);
##انواع العملاء
$types = $TypeClientEX->queryAllsup();
$smarty->assign("types", $types);
$allGovernment = $governmentDAO->queryAll();
$smarty->assign("allGovernment", $allGovernment);
$user = $userDAO->load($_SESSION['userid']);
$smarty->assign("user", $user);
$smarty->assign("searchinonestore", $_SESSION['searchinonestore']);
if ($_SESSION['searchinonestore'] == 0) {
if ($_SESSION['storeids'] == 0) {
$stores = $myStoreEx->queryByConditions();
} else {
$stores = $myStoreEx->queryByConditions(' and store.storeId in (' . $_SESSION['storeids'] . ')');
}
$smarty->assign("storeData", $stores);
$storedef = $myStoreEx->queryByConditionsOne(' and store.storeId = ' . $_SESSION['storeid'] . ' ');
$smarty->assign("storedef", $storedef);
$smarty->assign("storeid", $_SESSION['storeid']);
} else {
$storedef = $myStoreEx->queryByConditionsOne(' and store.storeId = ' . $_SESSION['storeid'] . ' ');
$smarty->assign("storedef", $storedef);
$smarty->assign("storeid", $_SESSION['storeid']);
}
// if ($user->userstoreid == 0) {
// $storeData = loadStore();
// $smarty->assign("storeData", $storeData);
// }
if ($user->branchId == 0) {
$branchData = loadBranch();
$smarty->assign("branchData", $branchData);
}
$youtubes = $youtubeLinkDAO->queryAll();
$smarty->assign("youtubes", $youtubes);
$sellerData = loadseller();
$smarty->assign("sellerData", $sellerData);
$userData = loadusers();
$smarty->assign("userData", $userData);
###########################################################################
//search
$getTodayBills = (int) filter_input(INPUT_POST, 'getTodayBills');
//if ($queryString == "") {
if ($getTodayBills == 1) {
unset($_POST);
$_POST['datefrom'] = $_POST['dateto'] = date('Y-m-d');
$_REQUEST['search'] = "dateOnly";
}
$datefrom = $_POST['datefrom']; //filter_input(INPUT_POST, 'datefrom');
$dateto = $_POST['dateto']; //filter_input(INPUT_POST, 'dateto');
$clientId = filter_input(INPUT_POST, 'clientId');
$obgyBillType = filter_input(INPUT_POST, 'obgyBillType');
$storeId = filter_input(INPUT_POST, 'storeId');
$branchId = filter_input(INPUT_POST, 'branchId');
$typeId = filter_input(INPUT_POST, 'typeId');
$pricetype = filter_input(INPUT_POST, 'pricetype');
$sellerid = filter_input(INPUT_POST, 'sellerid');
$userid = filter_input(INPUT_POST, 'userid');
$tripid = (int) filter_input(INPUT_POST, 'tripid');
$governmentid = filter_input(INPUT_POST, 'governmentid');
$clientareaid = filter_input(INPUT_POST, 'clientareaid');
$servicesAreIncluded = (int) filter_input(INPUT_POST, 'servicesAreIncluded');
$level = filter_input(INPUT_POST, 'level');
$productCatId = filter_input(INPUT_POST, 'productCatId' . $level);
if (empty($productCatId) || $productCatId == -1) {
$productCatId = filter_input(INPUT_POST, 'productCatId' . ($level - 1));
}
$productId = filter_input(INPUT_POST, 'productId');
$chosenProductPrice = (int) filter_input(INPUT_POST, 'chosenProductPrice');
$searchtype = filter_input(INPUT_POST, 'searchtype');
$isOptic = filter_input(INPUT_POST, 'proIsOptic');
if (!isset($isOptic) || empty($isOptic)) {
$isOptic = 0;
}
if ($isOptic == 2 && ($searchtype == 1 || $searchtype == 2)) {
$productCatId = $productId;
}
$message = '';
//
$queryString = " where 1 "; //bills
$queryStringR = " where 1 "; //billsreturn
$queryString1 = " where 1 "; //sellbill
$queryString1R = " where 1 "; //sellbillreturn
$queryString1SR = " where 1 "; //sellbillandreturn
##
// //display the converted time ##reportsPlusHours
// $Programsetting = $ProgramsettingDAO->load(1);
##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";
// }
$search = $_REQUEST['search'];
if ($search == "dateOnly") {
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 bills.billdate >= "' . $datefrom . '" ';
$queryStringR .= 'and billsreturn.`date` >= "' . $datefrom . '" ';
$queryString1 .= 'and sellbill.sellbilldate >= "' . $datefrom . '" ';
$queryString1R .= 'and returnsellbill.returnsellbilldate >= "' . $datefrom . '" ';
$queryString1SR .= 'and sellbillandrutern.sellbilldate >= "' . $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';
// }
$search = $_REQUEST['search'];
if ($search == "dateOnly") {
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 bills.billdate <= "' . $dateto . '" ';
$queryStringR .= 'and billsreturn.`date` <= "' . $dateto . '" ';
$queryString1 .= 'and sellbill.sellbilldate <= "' . $dateto . '" ';
$queryString1R .= 'and returnsellbill.returnsellbilldate <= "' . $dateto . '" ';
$queryString1SR .= 'and sellbillandrutern.sellbilldate <= "' . $dateto . '" ';
$message .= "الى تاريخ : " . $dateto . " ";
}
##clientId
if (isset($clientId) && !empty($clientId) && $clientId != -1) {
$queryString .= 'and bills.clientid = ' . $clientId . ' ';
$queryStringR .= 'and bills.clientid = ' . $clientId . ' ';
$queryString1 .= 'and sellbill.sellbillclientid = ' . $clientId . ' ';
$queryString1R .= 'and returnsellbill.returnsellbillclientid = ' . $clientId . ' ';
$queryString1SR .= 'and sellbillandrutern.sellbillclientid = ' . $clientId . ' ';
$client = $clientDAO->load($clientId);
$message .= "العميل : " . $client->clientname . " ";
}
##clientId
if (isset($obgyBillType) && !empty($obgyBillType)) {
$queryString1 .= 'and sellbill.obgyBillType = ' . $obgyBillType . ' ';
$queryString1R .= 'and returnsellbill.obgyBillType = ' . $obgyBillType . ' ';
$queryString1SR .= 'and sellbillandrutern.obgyBillType = ' . $obgyBillType . ' ';
$message .= "نوع الفاتورة : ";
switch ($obgyBillType) {
case 1:
$message .= "مباشر ";
break;
case 2:
$message .= "مستهلك ";
break;
case 3:
$message .= "استقبال ";
break;
}
}
##typeId
if (isset($typeId) && !empty($typeId) && $typeId != -1) {
$queryString .= 'and client.typeclientid in (' . $typeId . ') ';
$queryStringR .= 'and client.typeclientid in (' . $typeId . ') ';
$queryString1 .= 'and client.typeclientid in (' . $typeId . ') ';
$queryString1R .= 'and client.typeclientid in (' . $typeId . ') ';
$queryString1SR .= 'and client.typeclientid in (' . $typeId . ') ';
$type = $TypeClientDAO->load($typeId);
$message .= "نوع العميل : " . $type->typeName . " ";
}
if (isset($governmentid) && !empty($governmentid) && $governmentid != -1 && $clientareaid < 1) {
$goverareas = R::getAll("SELECT * FROM goverarea WHERE governmentid = ? ", [$governmentid]);
$clientareaids = '0';
foreach ($goverareas as $goverarea) {
$clientareaids .= $goverarea['clientareaid'];
}
$queryString .= 'and client.clientareaid in (' . $clientareaids . ') ';
$queryStringR .= 'and client.clientareaid in (' . $clientareaids . ') ';
$queryString1 .= 'and client.clientareaid in (' . $clientareaids . ') ';
$queryString1R .= 'and client.clientareaid in (' . $clientareaids . ') ';
$queryString1SR .= 'and client.clientareaid in (' . $clientareaids . ') ';
}
if (isset($clientareaid) && !empty($clientareaid) && $clientareaid != -1) {
$queryString .= 'and client.clientareaid = ' . $clientareaid . ' ';
$queryStringR .= 'and client.clientareaid = ' . $clientareaid . ' ';
$queryString1 .= 'and client.clientareaid = ' . $clientareaid . ' ';
$queryString1R .= 'and client.clientareaid = ' . $clientareaid . ' ';
$queryString1SR .= 'and client.clientareaid = ' . $clientareaid . ' ';
}
##storeId
$theStore;
if (isset($storeId) && !empty($storeId) && $storeId != -1) {
if ($user->userstoreid == 0) {
$theStore = $storeId;
//$queryString .= 'and bills.sellbillstoreid = ' . $storeId . ' ';
// $queryString1 .= 'and sellbill.sellbillstoreid = ' . $storeId . ' OR storeid = ' . $storeId . ' ';
// $queryString1R .= 'and returnsellbill.returnsellbillstoreid = ' . $storeId . ' OR storeid = ' . $storeId . ' ';
// $queryString1SR .= 'and sellbillandrutern.sellbillstoreid = ' . $storeId . ' ';
$store = $storeDAO->load($storeId);
$message .= "المخزن : " . $store->storeName . " ";
} else if ($_SESSION['searchinonestore'] == 0) {
if ($_SESSION['storeids'] != 0) {
$theStore = $_SESSION['storeids'];
// $queryString1 .= 'and sellbill.sellbillstoreid in (' . $_SESSION['storeids'] . ') OR storeid in(' . $_SESSION['storeid'] . ') ';
// $queryString1R .= 'and returnsellbill.returnsellbillstoreid in (' . $_SESSION['storeids'] . ') OR storeid in(' . $_SESSION['storeid'] . ') ';
// $queryString1SR .= 'and sellbillandrutern.sellbillstoreid in (' . $_SESSION['storeids'] . ') ';
$message .= " مخازن المستخدم ";
}
} else {
$mystordata = $myStoreRecord->load($_SESSION['storeid']);
$theStore = $_SESSION['storeid'];
// $queryString1 .= 'and sellbill.sellbillstoreid = ' . $_SESSION['storeid'] . ' OR storeid = ' . $_SESSION['storeid'] . ' ';
// $queryString1R .= 'and returnsellbill.returnsellbillstoreid = ' . $_SESSION['storeid'] . ' OR storeid = ' . $_SESSION['storeid'] . ' ';
// $queryString1SR .= 'and sellbillandrutern.sellbillstoreid = ' . $_SESSION['storeid'] . ' ';
$store = $storeDAO->load($_SESSION['storeid']);
$message .= "المخزن : " . $store->storeName . " ";
}
// else {
// $theStore = $user->userstoreid;
// //$queryString .= 'and bills.sellbillstoreid = ' . $user->userstoreid . ' ';
// $queryString1 .= 'and sellbill.sellbillstoreid = ' . $user->userstoreid . ' ';
// $queryString1R .= 'and returnsellbill.returnsellbillstoreid = ' . $user->userstoreid . ' ';
// $queryString1SR .= 'and sellbillandrutern.sellbillstoreid = ' . $user->userstoreid . ' ';
// $store = $storeDAO->load($user->userstoreid);
// $message .= "المخزن : " . $store->storeName . " ";
// }
}
##branch
if (isset($branchId) && !empty($branchId) && $branchId != -1) {
if ($user->branchId == 0) {
$queryString .= 'and bills.branchid = ' . $branchId . ' ';
$queryStringR .= 'and billsreturn.branchid = ' . $branchId . ' ';
//$queryString1 .= 'and sellbill.sellbilldate = ' . $branchId . ' ';
$branch = $branchDAO->load($branchId);
$message .= "الفرع : " . $store->storeName . " ";
} else {
$queryString .= 'and bills.branchid = ' . $user->branchId . ' ';
$queryStringR .= 'and billsreturn.branchid = ' . $user->branchId . ' ';
//$queryString1 .= 'and sellbill.sellbilldate = ' . $user->branchId . ' ';
$branch = $branchDAO->load($user->branchId);
$message .= "الفرع : " . $store->storeName . " ";
}
}
##pricetype
if (isset($pricetype) && $pricetype != -1) {
$queryString .= 'and bills.pricetype = ' . $pricetype . ' ';
$queryStringR .= 'and bills.pricetype = ' . $pricetype . ' ';
$queryString1 .= 'and sellbill.pricetype = ' . $pricetype . ' ';
$queryString1R .= 'and returnsellbill.pricetype = ' . $pricetype . ' ';
$queryString1SR .= 'and sellbillandrutern.pricetype = ' . $pricetype . ' ';
switch ($pricetype) {
case 0:
$message .= "نوع السعر : جملة ";
break;
case 1:
$message .= "نوع السعر : تجزئة ";
break;
case 2:
$message .= "نوع السعر : نصف جملة ";
break;
}
}
##sellerid
if (isset($sellerid) && !empty($sellerid) && $sellerid != -1) {
//$queryString .= 'and bills.billdate = "' . $dateto . '" ';
$queryString1 .= 'and sellbill.sellerid = "' . $sellerid . '" ';
$queryString1R .= 'and returnsellbill.sellerid = "' . $sellerid . '" ';
$queryString1SR .= 'and sellbillandrutern.sellerid = "' . $sellerid . '" ';
$seller = $userDAO->load($sellerid);
$message .= "البائع : " . $seller->employeename . " ";
}
##userid
if (isset($userid) && !empty($userid) && $userid != -1) {
$queryString .= 'and bills.userid = ' . $userid . ' ';
$queryStringR .= 'and billsreturn.userid = ' . $userid . ' ';
$queryString1 .= 'and sellbill.userid = ' . $userid . ' ';
$queryString1R .= 'and returnsellbill.userid = ' . $userid . ' ';
$queryString1SR .= 'and sellbillandrutern.userid = ' . $userid . ' ';
$user = $userDAO->load($userid);
$message .= "المستخدم : " . $user->employeename . " ";
}
##userid
if (isset($tripid) && $tripid > 0) {
$queryString .= 'and bills.userid = -1 ';
$queryStringR .= 'and billsreturn.userid = -1 ';
$queryString1 .= 'and sellbill.travelTripId = ' . $tripid . ' ';
$queryString1R .= 'and returnsellbill.userid = -1 ';
$queryString1SR .= 'and sellbillandrutern.userid = -1 ';
$user = $userDAO->load($userid);
$message .= "المستخدم : " . $user->employeename . " ";
}
if ($searchtype == 0) {
##productId
if (isset($productId) && !empty($productId) && $productId != -1) {
$sizeId = 0;
$colorId = 0;
$sizeColorStoreDetailId = 0;
if (strpos($productId, "hasSizeColor") !== false) {
$productIdComplex = explode('-', str_replace("hasSizeColor", "", $productId));
$productId = $productIdComplex[0];
$sizeId = $productIdComplex[1];
$colorId = $productIdComplex[2];
//$sizeColorStoreDetailId = $sizeColorStoreDetailEX->getIdByProductStoreSizeAndColorEX($productId, $sellbillstoreId, $sizeId, $colorId);
}
$pro = $ProductDAO->load($productId);
$message .= "المنتج : " . $pro->productName . " ";
if ($isOptic == 2) {
$productsOfCat = $ProductEX->queryByProductCatIdIn($productId);
$productId = '0';
foreach ($productsOfCat as $value) {
$productId .= ',' . $value->productId;
}
}
$IDS = $productId;
// $queryString .= 'and billsproducts.productid in( ' . $productId . ' ) ';
// $queryStringR .= 'and billsreturnproducts.productid in( ' . $productId . ' ) ';
// $queryString1 .= 'and sellbilldetail.sellbilldetailproductid in( ' . $productId . ' ) ';
// $queryString1R .= 'and returnsellbilldetail.returnsellbilldetailproductid in( ' . $productId . ' ) ';
// $queryString1SR .= 'and sellandruternbilldetail.sellbilldetailproductid in( ' . $productId . ' ) ';
} elseif (isset($productCatId) && !empty($productCatId) && $productCatId != -1) {
##get subcat of cat
$cat = $productCatDAO->load($productCatId);
$message .= "التصنيف : " . $cat->productCatName . " ";
$catsIDS = '' . $productCatId;
getAllSubCat($productCatId, 1); //mode = 1 get all sub cats
$productsOfCat = $ProductEX->queryByProductCatIdIn($catsIDS);
$IDS = '0';
foreach ($productsOfCat as $value) {
$IDS .= ',' . $value->productId;
}
// $queryString .= 'and billsproducts.productid in (' . $IDS . ') ';
// $queryString1 .= 'and sellbilldetail.sellbilldetailproductid in (' . $IDS . ') ';
// $queryStringR .= 'and billsreturnproducts.productid in (' . $IDS . ') ';
// $queryString1R .= 'and returnsellbilldetail.returnsellbilldetailproductid in (' . $IDS . ') ';
// $queryString1SR .= 'and sellandruternbilldetail.sellbilldetailproductid in (' . $IDS . ') ';
}
getDataNew($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore, $IDS, $typeId, $servicesAreIncluded);
} elseif ($searchtype == 1 || $searchtype == 2) {
getDataNew($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore, $IDS, $typeId, $servicesAreIncluded);
}
$smarty->assign("salesreport", 1);
$smarty->assign("message", $message);
//here the smarty templates
$smarty->display("salesreportview/show.html");
//$smarty->assign("settlementstoreshow", 1);
} elseif ($do == "consumption") {
//here the permission check
include_once("../public/authentication.php");
$Programsetting = $ProgramsettingDAO->load(1);
$smarty->assign("Programsetting", $Programsetting);
$youtubes = $youtubeLinkDAO->queryAll();
$smarty->assign("youtubes", $youtubes);
##########################################################################
//prepare search tools
##التصنيفات
$catDataReport = getCategoryChilds();
$smarty->assign("catDataReport", $catDataReport[1]);
$user = $userDAO->load($_SESSION['userid']);
$smarty->assign("user", $user);
if ($user->userstoreid == 0) {
$storeData = loadStore();
$smarty->assign("storeData", $storeData);
}
//search
$datefrom = filter_input(INPUT_POST, 'datefrom');
$dateto = filter_input(INPUT_POST, 'dateto');
$storeId = filter_input(INPUT_POST, 'storeId');
$level = filter_input(INPUT_POST, 'level');
$productCatId = filter_input(INPUT_POST, 'productCatId' . $level);
if (empty($productCatId) || $productCatId == -1) {
$productCatId = filter_input(INPUT_POST, 'productCatId' . ($level - 1));
}
$productId = filter_input(INPUT_POST, 'productId');
$isOptic = filter_input(INPUT_POST, 'proIsOptic');
if (!isset($isOptic) || empty($isOptic)) {
$isOptic = 0;
}
if ($isOptic == 2 && ($searchtype == 1 || $searchtype == 2)) {
$productCatId = $productId;
}
$message = '';
//
$queryString = " where 1 "; //bills
$queryStringR = " where 1 "; //billsreturn
$queryString1 = " where 1 "; //sellbill
$queryString1R = " where 1 "; //sellbillreturn
$queryString1SR = " where 1 "; //sellbillandreturn
##
// //display the converted time ##reportsPlusHours
// $Programsetting = $ProgramsettingDAO->load(1);
##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";
// }
$search = $_REQUEST['search'];
if ($search == "dateOnly") {
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 bills.billdate >= "' . $datefrom . '" ';
$queryStringR .= 'and billsreturn.`date` >= "' . $datefrom . '" ';
$queryString1 .= 'and sellbill.sellbilldate >= "' . $datefrom . '" ';
$queryString1R .= 'and returnsellbill.returnsellbilldate >= "' . $datefrom . '" ';
$queryString1SR .= 'and sellbillandrutern.sellbilldate >= "' . $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';
// }
$search = $_REQUEST['search'];
if ($search == "dateOnly") {
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 bills.billdate <= "' . $dateto . '" ';
$queryStringR .= 'and billsreturn.`date` <= "' . $dateto . '" ';
$queryString1 .= 'and sellbill.sellbilldate <= "' . $dateto . '" ';
$queryString1R .= 'and returnsellbill.returnsellbilldate <= "' . $dateto . '" ';
$queryString1SR .= 'and sellbillandrutern.sellbilldate <= "' . $dateto . '" ';
$message .= "الى تاريخ : " . $datefrom . " ";
}
##storeId
$theStore;
if (isset($storeId) && !empty($storeId) && $storeId != -1) {
if ($user->userstoreid == 0) {
$theStore = $storeId;
//$queryString .= 'and bills.sellbillstoreid = ' . $storeId . ' ';
$queryString1 .= 'and sellbill.sellbillstoreid = ' . $storeId . ' ';
$queryString1R .= 'and returnsellbill.returnsellbillstoreid = ' . $storeId . ' ';
$queryString1SR .= 'and sellbillandrutern.sellbillstoreid = ' . $storeId . ' ';
$store = $storeDAO->load($storeId);
$message .= "المخزن : " . $store->storeName . " ";
} else if ($_SESSION['searchinonestore'] == 0) {
if ($_SESSION['storeids'] != 0) {
$queryString1 .= 'and sellbill.sellbillstoreid in (' . $_SESSION['storeids'] . ') ';
$queryString1R .= 'and returnsellbill.returnsellbillstoreid in (' . $_SESSION['storeids'] . ') ';
$queryString1SR .= 'and sellbillandrutern.sellbillstoreid in (' . $_SESSION['storeids'] . ') ';
$message .= " مخازن المستخدم ";
}
} else {
$mystordata = $myStoreRecord->load($_SESSION['storeid']);
$theStore = $_SESSION['storeid'];
$queryString1 .= 'and sellbill.sellbillstoreid = ' . $_SESSION['storeid'] . ' ';
$queryString1R .= 'and returnsellbill.returnsellbillstoreid = ' . $_SESSION['storeid'] . ' ';
$queryString1SR .= 'and sellbillandrutern.sellbillstoreid = ' . $_SESSION['storeid'] . ' ';
$store = $storeDAO->load($_SESSION['storeid']);
$message .= "المخزن : " . $store->storeName . " ";
}
// else {
// $theStore = $user->userstoreid;
// //$queryString .= 'and bills.sellbillstoreid = ' . $user->userstoreid . ' ';
// $queryString1 .= 'and sellbill.sellbillstoreid = ' . $user->userstoreid . ' ';
// $queryString1R .= 'and returnsellbill.returnsellbillstoreid = ' . $user->userstoreid . ' ';
// $queryString1SR .= 'and sellbillandrutern.sellbillstoreid = ' . $user->userstoreid . ' ';
// $store = $storeDAO->load($user->userstoreid);
// $message .= "المخزن : " . $store->storeName . " ";
// }
}
##productId
if (isset($productId) && !empty($productId) && $productId != -1) {
$sizeId = 0;
$colorId = 0;
$sizeColorStoreDetailId = 0;
if (strpos($productId, "hasSizeColor") !== false) {
$productIdComplex = explode('-', str_replace("hasSizeColor", "", $productId));
$productId = $productIdComplex[0];
$sizeId = $productIdComplex[1];
$colorId = $productIdComplex[2];
//$sizeColorStoreDetailId = $sizeColorStoreDetailEX->getIdByProductStoreSizeAndColorEX($productId, $sellbillstoreId, $sizeId, $colorId);
}
$pro = $ProductDAO->load($productId);
$message .= "المنتج : " . $pro->productName . " ";
if ($isOptic == 2) {
$productsOfCat = $ProductEX->queryByProductCatIdIn($productId);
$productId = '0';
foreach ($productsOfCat as $value) {
$productId .= ',' . $value->productId;
}
}
$queryString .= 'and billsproducts.productid in( ' . $productId . ' ) ';
$queryStringR .= 'and billsreturnproducts.productid in( ' . $productId . ' ) ';
$queryString1 .= 'and sellbilldetail.sellbilldetailproductid in( ' . $productId . ' ) ';
$queryString1R .= 'and returnsellbilldetail.returnsellbilldetailproductid in( ' . $productId . ' ) ';
$queryString1SR .= 'and sellandruternbilldetail.sellbilldetailproductid in( ' . $productId . ' ) ';
} elseif (isset($productCatId) && !empty($productCatId) && $productCatId != -1) {
##get subcat of cat
$cat = $productCatDAO->load($productCatId);
$message .= "التصنيف : " . $cat->productCatName . " ";
$catsIDS = '' . $productCatId;
getAllSubCat($productCatId, 1); //mode = 1 get all sub cats
$productsOfCat = $ProductEX->queryByProductCatIdIn($catsIDS);
$IDS = '0';
foreach ($productsOfCat as $value) {
$IDS .= ',' . $value->productId;
}
$queryString .= 'and billsproducts.productid in (' . $IDS . ') ';
$queryString1 .= 'and sellbilldetail.sellbilldetailproductid in (' . $IDS . ') ';
$queryStringR .= 'and billsreturnproducts.productid in (' . $IDS . ') ';
$queryString1R .= 'and returnsellbilldetail.returnsellbilldetailproductid in (' . $IDS . ') ';
$queryString1SR .= 'and sellandruternbilldetail.sellbilldetailproductid in (' . $IDS . ') ';
}
getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore);
$smarty->assign("salesreport", 1);
$smarty->assign("message", $message);
//here the smarty templates
$smarty->display("salesreportview/consumption.html");
//$smarty->assign("settlementstoreshow", 1);
}
//here the global templates
$smarty->display("footer.html");
/* ===============================
function in this CONTROLLER
================================ */
function getDataNew($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore, $IDSOfProducts, $typeIdOfClient, $servicesAreIncluded) {
global $billsProductsEX;
global $billsDAO;
global $billsEX;
global $billsReturnDAO;
global $billsReturnProductsEX;
global $sellbilldetailEX;
global $returnSellBillDetailEX;
global $sellAndRuternBillDetailEX;
global $ProductDAO;
global $productCatDAO;
global $ProductEX;
global $ProgramsettingDAO;
global $Programsetting;
global $smarty;
global $lastLevelCatIDS;
global $catsIDS;
global $mainCat;
class productData {
public $id;
public $productName;
public $sellUnitPrice = 0;
public $soldNo = 0;
public $soldVal = 0;
public $returnNo = 0;
public $returnVal = 0;
public $netNo = 0;
public $netVal = 0;
public $realCost = 0;
public $netProfit = 0;
public $buyPrice = 0;
public $currentQuantity = 0;
public $limitamount = 0;
}
//* 1.15 "vatValue" include vat to buy price to make profit calculations true like sales includes vat
$vatValue = 1 + ($Programsetting->vatValue / 100);
$queryStringService = '';
if ($servicesAreIncluded == 0) { //service are not included
$queryStringService = ' and isService = 0';
}
if ($queryString1 == " where 1 ") {
return;
}
$existId = array();
$allDataArr = array();
$totalsObj = new productData();
$resultsCount = 0;
$flag = 0;
$priceName = '';
$queryStringStore1 = $queryStringStore1R = $queryStringStore1SR = '';
if ($theStore) {
$queryStringStore1 .= ' and sellbilldetail.storeid in(' . $theStore . ') ';
$queryStringStore1R .= ' and returnsellbilldetail.storeid in(' . $theStore . ') ';
$queryStringStore1SR .= ' and sellandruternbilldetail.storeid in (' . $theStore . ') ';
}
if (($searchtype == 1 || $searchtype == 2) && isset($productCatId) && !empty($productCatId) && $productCatId != -1) {
$lastLevelCatIDS = array();
array_push($lastLevelCatIDS, $productCatId);
getAllSubCat($productCatId, 2); //mode = 2 get last level cats
$catsIDS = '0';
foreach ($lastLevelCatIDS as $mycatId) {
$catsIDS .= ',' . $mycatId;
}
$productsOfCat = R::getAll('SELECT productId FROM product WHERE productCatId in (' . $catsIDS . ')');
$IDSOfProducts = '0';
foreach ($productsOfCat as $value) {
$IDSOfProducts .= ',' . $value['productId'];
}
##if no search make it today
if ($queryString1 == " where 1 ") {
##date
$today = date("Y-m-d");
$queryString1 .= 'and sellbill.sellbilldate = "' . $today . '" ';
$queryString1R .= 'and returnsellbill.returnsellbilldate = "' . $today . '" ';
$queryString1SR .= 'and sellbillandrutern.sellbilldate = "' . $today . '" ';
}
##cat data
$productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . $catsIDS . ') ');
$productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
}
##############################################################################
## lets go
##if no search make it today
/* if ($queryString1 == " where 1 ") {
##date
$datefrom = $dateto = $today = date("Y-m-d");
//display the converted time ##reportsPlusHours
$Programsetting = $ProgramsettingDAO->load(1);
##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)));
$dateto = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($dateto)));
} else {
$datefrom = $datefrom . " 00:00:00";
$dateto = $dateto . ' 23:59:59';
}
$queryString .= 'and bills.billdate >= "' . $datefrom . '" ';
$queryStringR .= 'and billsreturn.`date` >= "' . $datefrom . '" ';
$queryString1 .= 'and sellbill.sellbilldate >= "' . $datefrom . '" ';
$queryString1R .= 'and returnsellbill.returnsellbilldate >= "' . $datefrom . '" ';
$queryString1SR .= 'and sellbillandrutern.sellbilldate >= "' . $datefrom . '" ';
//
$queryString .= 'and bills.billdate <= "' . $dateto . '" ';
$queryStringR .= 'and billsreturn.`date` <= "' . $dateto . '" ';
$queryString1 .= 'and sellbill.sellbilldate <= "' . $dateto . '" ';
$queryString1R .= 'and returnsellbill.returnsellbilldate <= "' . $dateto . '" ';
$queryString1SR .= 'and sellbillandrutern.sellbilldate <= "' . $dateto . '" ';
}
} */
$sellBillData = R::getAll('SELECT sellbillid,sellbill.sellbillclientid as clientid,sellbill.sellbilltotalbill as parcode,sellbill.sellbillaftertotalbill as note,sellbill.userid
,sellbilldiscount,sellbilldiscounttype
FROM sellbill ' . $queryString1 . ' AND conditions = 0');
$sellBillDataReturn = R::getAll('SELECT returnsellbillid,returnsellbillclientid as clientid,returnsellbill.returnsellbilltotalbill as parcode,returnsellbill.returnsellbillaftertotalbill as note,returnsellbill.userid
,returnsellbilldiscount,returnsellbilldiscounttype
FROM returnsellbill ' . $queryString1R . ' AND conditions = 0');
$sellBillDataSellAndReturn = R::getAll('SELECT sellbillid,sellbillandrutern.sellbillclientid as clientid,sellbillandrutern.sellbilltotalbill as parcode,sellbillandrutern.sellbillaftertotalbill as note,sellbillandrutern.userid
,sellbilldiscount,sellbilldiscounttype
FROM sellbillandrutern ' . $queryString1SR . ' AND conditions = 0'); //AND sellbillandrutern.deletedsellid=0//order by sellandruternbilldetail.sellbilldetailproductid
list($productCatIdsArr, $productIdsArrAll) = [[], []];
########################################################################
##فاتورة المبيعات
list($billIDs, $clientIDs) = [[], []];
foreach ($sellBillData as $value) {
$billIDs[] = $value['sellbillid'];
$clientIDs[] = $value['clientid'];
}
$billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
$clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];
//getclientName && apply search with client type to clients of bills
if ($clientIDs != [0]) {
$clientTypeSql = '';
if ($typeIdOfClient > 0)
$clientTypeSql = ' and typeclientid = ' . $typeIdOfClient;
$clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ' . $clientTypeSql);
$clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
$sellBillDataTemp = array();
foreach ($sellBillData as $value) {
//$found_index = searchForId($value['clientid'], $clientDataArr, 'clientid');
if ($clientDataArr[$value['clientid']] !== null) {
$value['clientname'] = $clientDataArr[$value['clientid']]['clientname'];
$sellBillDataTemp[] = $value;
}
}
$sellBillData = $sellBillDataTemp;
}
//get billdetails && apply search with productid
if ($billIDs != [0]) {
$sqlProducts = '';
if (isset($IDSOfProducts))
$sqlProducts = ' and sellbilldetail.sellbilldetailproductid in(' . $IDSOfProducts . ') ';
$sellBillDetailData = R::getAll('SELECT sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber
,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
FROM sellbilldetail
join productunit on(productunit.productid = sellbilldetail.sellbilldetailproductid and productunit.productunitid=sellbilldetail.productunitid and productunit.conditions = 0)
where sellbilldetail.sellbillid in(' . implode(',', $billIDs) . ') ' . $sqlProducts . ' ' . $queryStringStore1 . ' AND conditions = 0'); //-- order by sellbilldetail.sellbilldetailproductid
$sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'sellbillid');
$sellBillDataTemp = array();
list($productIDsArr) = [[]];
foreach ($sellBillData as $value) {
//$found_indexs = searchForIds($value['sellbillid'], $sellBillDetailData, 'sellbillid');
foreach ($sellBillDetailData[$value['sellbillid']] as $data) {
foreach ($data as $key1 => $value1) {
$value[$key1] = $value1;
}
$productIDsArr[] = (int) $value['sellbilldetailproductid'];
$sellBillDataTemp[] = $value;
}
}
$sellBillData = $sellBillDataTemp;
//get data of product tbl
##realcost #for
$priceName = '';
switch ($chosenProductPrice) {
case 0:
$priceName = ',buyprice'; //coming from bill not in product table
break;
case 2:
$priceName = ',lastbuyprice';
break;
case 1:
$priceName = ',meanbuyprice';
break;
case 3:
$priceName = ',lastbuyprice_withDiscount';
break;
case 4:
$priceName = ',meanbuyprice_withDiscount';
break;
case 5:
//$priceName = ',generalPrice';// already in sql statement below
break;
case 6:
$priceName = ',lastbuyprice_withTax';
break;
case 7:
$priceName = ',meanbuyprice_withTax';
break;
}
##sell price
$sellUnitPrice = ' ,"" as sellunitprice ';
switch ($Programsetting->unitPriceAtsalesreport) {
case 1:
$sellUnitPrice = ' ,productSellAllPrice as sellunitprice';
break;
case 2:
$sellUnitPrice = ' ,productSellUnitPrice as sellunitprice';
break;
case 3:
$sellUnitPrice = ' ,productSellHalfPrice as sellunitprice';
break;
}
$productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
$productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
//' . $priceName . '
$productDataArr = R::getAll('select productId,productName,overAllAveragePrice as generalPrice,productBuyPrice as storeid,product.productCatId,isService ' . $sellUnitPrice . '
from product where productId in(' . implode(',', $productIDsArr) . ') ');
$productDataArr = customArrayIndexOne($productDataArr, 'productId');
$sellBillDataTemp = array();
foreach ($sellBillData as $value) {
//$found_index = searchForId($value['sellbilldetailproductid'], $productDataArr, 'productId');
if ($productDataArr[$value['sellbilldetailproductid']] !== null) {
$productCatIdsArr[] = $productDataArr[$value['sellbilldetailproductid']]['productCatId'];
foreach ($productDataArr[$value['sellbilldetailproductid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
$sellBillDataTemp[] = $value;
}
}
$sellBillData = $sellBillDataTemp;
}
//from key value pair to std class
$sellBillDataTemp = array();
foreach ($sellBillData as $value) {
if ($servicesAreIncluded == 1 || ($servicesAreIncluded == 0 && $value['isService'] == 0)) {
$obj = new stdClass();
foreach ($value as $key1 => $value1) {
$obj->$key1 = $value1;
}
$sellBillDataTemp[] = $obj;
}
}
$sellBillData = $sellBillDataTemp;
########################################################################
##فاتورة مردود المبيعات
list($billIDs, $clientIDs) = [[], []];
foreach ($sellBillDataReturn as $value) {
$billIDs[] = $value['returnsellbillid'];
$clientIDs[] = $value['clientid'];
}
$billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
$clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];
//getclientName && apply search with client type to clients of bills
if ($clientIDs != [0]) {
$clientTypeSql = '';
if ($typeIdOfClient > 0)
$clientTypeSql = ' and typeclientid = ' . $typeIdOfClient;
$clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ' . $clientTypeSql);
$clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
$sellBillDataTemp = array();
foreach ($sellBillDataReturn as $value) {
//$found_index = searchForId($value['clientid'], $clientDataArr, 'clientid');
if ($clientDataArr[$value['clientid']] !== null) {
$value['clientname'] = $clientDataArr[$value['clientid']]['clientname'];
$sellBillDataTemp[] = $value;
}
}
$sellBillDataReturn = $sellBillDataTemp;
}
//get billdetails && apply search with productid
if ($billIDs != [0]) {
$sqlProducts = '';
if (isset($IDSOfProducts))
$sqlProducts = ' and returnsellbilldetail.returnsellbilldetailproductid in(' . $IDSOfProducts . ') ';
$sellBillDetailData = R::getAll('SELECT returnsellbillid,returnsellbilldetailproductid,returnsellbilldetailquantity,returnsellbilldetail.productunitid,returnsellbilldetailtotalprice,productunit.productnumber
,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount
FROM returnsellbilldetail
join productunit on(productunit.productid = returnsellbilldetail.returnsellbilldetailproductid and productunit.productunitid=returnsellbilldetail.productunitid and productunit.conditions = 0)
where returnsellbilldetail.returnsellbillid in(' . implode(',', $billIDs) . ') ' . $sqlProducts . ' ' . $queryStringStore1R . ' AND returnsellbilldetail.conditions = 0'); //-- order by returnsellbilldetail.returnsellbilldetailproductid
$sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'returnsellbillid');
$sellBillDataTemp = array();
list($productIDsArr) = [[]];
foreach ($sellBillDataReturn as $value) {
//$found_indexs = searchForIds($value['returnsellbillid'], $sellBillDetailData, 'returnsellbillid');
foreach ($sellBillDetailData[$value['returnsellbillid']] as $data) {
foreach ($data as $key1 => $value1) {
$value[$key1] = $value1;
}
$productIDsArr[] = (int) $value['returnsellbilldetailproductid'];
$sellBillDataTemp[] = $value;
}
}
$sellBillDataReturn = $sellBillDataTemp;
//get data of product tbl
$productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
$productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
//' . $priceName . '
$productDataArr = R::getAll('select productId,product.productName as productName,product.overAllAveragePrice as generalPrice,product.productBuyPrice as storeid,product.productCatId,isService ' . $sellUnitPrice . '
from product where productId in(' . implode(',', $productIDsArr) . ') ');
$productDataArr = customArrayIndexOne($productDataArr, 'productId');
$sellBillDataTemp = array();
foreach ($sellBillDataReturn as $value) {
//$found_index = searchForId($value['returnsellbilldetailproductid'], $productDataArr, 'productId');
if ($productDataArr[$value['returnsellbilldetailproductid']] !== null) {
$productCatIdsArr[] = $productDataArr[$value['returnsellbilldetailproductid']]['productCatId'];
foreach ($productDataArr[$value['returnsellbilldetailproductid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
$sellBillDataTemp[] = $value;
}
}
$sellBillDataReturn = $sellBillDataTemp;
}
//from key value pair to std class
$sellBillDataTemp = array();
foreach ($sellBillDataReturn as $value) {
if ($servicesAreIncluded == 1 || ($servicesAreIncluded == 0 && $value['isService'] == 0)) {
$obj = new stdClass();
foreach ($value as $key1 => $value1) {
$obj->$key1 = $value1;
}
$sellBillDataTemp[] = $obj;
}
}
$sellBillDataReturn = $sellBillDataTemp;
########################################################################
##فاتورة مبيعات و مردود
list($billIDs, $clientIDs) = [[], []];
foreach ($sellBillDataSellAndReturn as $value) {
$billIDs[] = $value['sellbillid'];
$clientIDs[] = $value['clientid'];
}
$billIDs = !empty($billIDs) ? array_unique($billIDs) : [0];
$clientIDs = !empty($clientIDs) ? array_unique($clientIDs) : [0];
//getclientName && apply search with client type to clients of bills
if ($clientIDs != [0]) {
$clientTypeSql = '';
if ($typeIdOfClient > 0)
$clientTypeSql = ' and typeclientid = ' . $typeIdOfClient;
$clientDataArr = R::getAll('select clientid,clientname from client where clientid in(' . implode(',', $clientIDs) . ') ' . $clientTypeSql);
$clientDataArr = customArrayIndexOne($clientDataArr, 'clientid');
$sellBillDataTemp = array();
foreach ($sellBillDataSellAndReturn as $value) {
//$found_index = searchForId($value['clientid'], $clientDataArr, 'clientid');
if ($clientDataArr[$value['clientid']] !== null) {
$value['clientname'] = $clientDataArr[$value['clientid']]['clientname'];
$sellBillDataTemp[] = $value;
}
}
$sellBillDataSellAndReturn = $sellBillDataTemp;
}
//get billdetails && apply search with productid
if ($billIDs != [0]) {
$sqlProducts = '';
if (isset($IDSOfProducts))
$sqlProducts = ' and sellandruternbilldetail.sellbilldetailproductid in(' . $IDSOfProducts . ') ';
$sellBillDetailData = R::getAll('SELECT sellbillid,sellbilldetailproductid,sellbilldetailquantity,sellandruternbilldetail.productunitid,sellbilldetailtotalprice,productunit.productnumber
,buyprice,lastbuyprice,meanbuyprice,lastbuyprice_withDiscount,meanbuyprice_withDiscount,selltype
FROM sellandruternbilldetail
join productunit on(productunit.productid = sellandruternbilldetail.sellbilldetailproductid and productunit.productunitid=sellandruternbilldetail.productunitid and productunit.conditions = 0)
where sellandruternbilldetail.sellbillid in(' . implode(',', $billIDs) . ') ' . $sqlProducts . ' ' . $queryStringStore1SR . ' AND conditions = 0'); //-- order by sellandruternbilldetail.sellbilldetailproductid
$sellBillDetailData = customArrayIndexMany($sellBillDetailData, 'sellbillid');
$sellBillDataTemp = array();
list($productIDsArr) = [[]];
foreach ($sellBillDataSellAndReturn as $value) {
//$found_indexs = searchForIds($value['sellbillid'], $sellBillDetailData, 'sellbillid');
foreach ($sellBillDetailData[$value['sellbillid']] as $key) {
// $data = $sellBillDetailData[$key];
$data = $key;
foreach ($data as $key1 => $value1) {
$value[$key1] = $value1;
}
$productIDsArr[] = (int) $value['sellbilldetailproductid'];
$sellBillDataTemp[] = $value;
}
}
$sellBillDataSellAndReturn = $sellBillDataTemp;
//get data of product tbl
$productIdsArrAll = array_merge($productIdsArrAll, $productIDsArr);
$productIDsArr = !empty($productIDsArr) ? array_unique($productIDsArr) : [0];
//' . $priceName . '
$productDataArr = R::getAll('select productId,product.productName as productName,product.overAllAveragePrice as generalPrice,product.productBuyPrice as storeid,product.productCatId,isService ' . $sellUnitPrice . '
from product where productId in(' . implode(',', $productIDsArr) . ') ');
$productDataArr = customArrayIndexOne($productDataArr, 'productId');
$sellBillDataTemp = array();
foreach ($sellBillDataSellAndReturn as $value) {
//$found_index = searchForId($value['sellbilldetailproductid'], $productDataArr, 'productId');
if ($productDataArr[$value['sellbilldetailproductid']] !== null) {
$productCatIdsArr[] = $productDataArr[$value['sellbilldetailproductid']]['productCatId'];
foreach ($productDataArr[$value['sellbilldetailproductid']] as $key1 => $value1) {
$value[$key1] = $value1;
}
$sellBillDataTemp[] = $value;
}
}
$sellBillDataSellAndReturn = $sellBillDataTemp;
}
//from key value pair to std class
$sellBillDataTemp = array();
foreach ($sellBillDataSellAndReturn as $value) {
if ($servicesAreIncluded == 1 || ($servicesAreIncluded == 0 && $value['isService'] == 0)) {
$obj = new stdClass();
foreach ($value as $key1 => $value1) {
$obj->$key1 = $value1;
}
$sellBillDataTemp[] = $obj;
}
}
$sellBillDataSellAndReturn = $sellBillDataTemp;
########################################################################
#############finally get net , real cost ,net profit
$productCatIdsArr = !empty($productCatIdsArr) ? array_unique($productCatIdsArr) : [0];
$productIdsArrAll = !empty($productIdsArrAll) ? array_unique($productIdsArrAll) : [0];
$productCatDataArr = R::getAll('select productCatId,productCatName from productcat where productCatId in(' . implode(',', $productCatIdsArr) . ') ');
$productCatDataArr = customArrayIndexOne($productCatDataArr, 'productCatId');
##this is for quantity before total foreach
$myQuery = "";
if (isset($theStore) && !empty($theStore) && $theStore != -1) {
$myQuery = " and storedetail.storeid = " . $theStore;
} else if ($_SESSION['searchinonestore'] == 0) {
if ($_SESSION['storeids'] != 0) {
$myQuery = ' and storedetail.storeid in (' . $_SESSION['storeids'] . ') ';
}
} else {
$myQuery = " and storedetail.storeid = " . $_SESSION['storeid'];
}
if ($searchtype == 0) {
foreach ($sellBillData as $value) {
if (in_array($value->sellbilldetailproductid, $existId)) {
$key = array_search($value->sellbilldetailproductid, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->sellbilldetailproductid;
array_push($existId, $value->sellbilldetailproductid);
}
$myproduct->productName = $value->productName;
$myproduct->productCatId = $value->productCatId;
$myproduct->productCatName = $value->productCatName;
#########get quantity بالقطعة
$quantity = $value->sellbilldetailquantity;
$productId = $value->sellbilldetailproductid;
$productunitId = $value->productunitid;
$finalquantity = $quantity * $value->productnumber;
##############
$myproduct->soldNo += $finalquantity; //العدد بالقطعة
######get discount
$dicount = 0;
$billNoOfProduct = 0;
$dicount = $value->sellbilldiscount;
if ($value->sellbilldiscounttype == 2) {
$dicount = ($value->sellbilldiscount / 100) * $value->parcode;
}
//$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
$theDiscount = round($theDiscount, 2);
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($Programsetting->valueadded == 0) {
$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
} else {
$myproduct->soldVal += $value->sellbilldetailtotalprice + $theDiscount;
}
$myproduct->buyPrice = $value->storeid;
$myproduct->sellUnitPrice = (float) $value->sellunitprice;
##realcost
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost += $finalquantity * $value->buyprice * $vatValue;
break;
case 2:
$myproduct->realCost += $finalquantity * $value->lastbuyprice * $vatValue;
break;
case 1:
$myproduct->realCost += $finalquantity * $value->meanbuyprice * $vatValue;
break;
case 3:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
break;
case 4:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
break;
case 5:
$myproduct->realCost += $finalquantity * $value->generalPrice * $vatValue;
break;
case 6:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withTax * $vatValue;
break;
case 7:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withTax * $vatValue;
break;
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
}
foreach ($sellBillDataReturn as $value) {
if (in_array($value->returnsellbilldetailproductid, $existId)) {
$key = array_search($value->returnsellbilldetailproductid, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->returnsellbilldetailproductid;
array_push($existId, $value->returnsellbilldetailproductid);
}
$myproduct->productName = $value->productName;
$myproduct->productCatId = $value->productCatId;
//emaaaaaaaan
$myproduct->productCatName = $value->productCatName;
#########get quantity بالقطعة
$quantity = $value->returnsellbilldetailquantity;
$productId = $value->returnsellbilldetailproductid;
$productunitId = $value->productunitid;
$finalquantity = $quantity * $value->productnumber;
##############
$myproduct->returnNo += $finalquantity; //العدد بالقطعة
######get discount
$dicount = 0;
$billNoOfProduct = 0;
$dicount = $value->returnsellbilldiscount;
if ($value->returnsellbilldiscounttype == 2) {
$dicount = $value->returnsellbilldiscount * $value->parcode / 100;
}
//$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$billpecies = $returnSellBillDetailEX->queryBillNoOfPecies($value->returnsellbillid);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
//$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
$theDiscount = ($value->returnsellbilldetailtotalprice / $value->parcode) * $dicount;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($Programsetting->valueadded == 0) {
$myproduct->returnVal += $value->returnsellbilldetailtotalprice - $theDiscount;
} else {
$myproduct->returnVal += $value->returnsellbilldetailtotalprice + $theDiscount;
}
$myproduct->buyPrice = $value->storeid;
$myproduct->sellUnitPrice = (float) $value->sellunitprice;
##realcost
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost -= $finalquantity * $value->buyprice * $vatValue;
break;
case 2:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice * $vatValue;
break;
case 1:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice * $vatValue;
break;
case 3:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
break;
case 4:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
break;
case 5:
$myproduct->realCost -= $finalquantity * $value->generalPrice * $vatValue;
break;
case 6:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withTax * $vatValue;
break;
case 7:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withTax * $vatValue;
break;
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
}
foreach ($sellBillDataSellAndReturn as $value) {
if (in_array($value->sellbilldetailproductid, $existId)) {
$key = array_search($value->sellbilldetailproductid, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->sellbilldetailproductid;
array_push($existId, $value->sellbilldetailproductid);
}
$myproduct->productName = $value->productName;
$myproduct->productCatId = $value->productCatId;
//emaaaaaaaan
$myproduct->productCatName = $value->productCatName;
#########get quantity بالقطعة
$quantity = $value->sellbilldetailquantity;
$productId = $value->sellbilldetailproductid;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber;
$myproduct->buyPrice = $value->storeid;
$myproduct->sellUnitPrice = (float) $value->sellunitprice;
##############
######get discount
$dicount = 0;
$billNoOfProduct = 0;
$dicount = $value->sellbilldiscount;
if ($value->sellbilldiscounttype == 2) {
$dicount = ($value->sellbilldiscount / 100) * $value->parcode;
}
//$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$billpecies = $sellAndRuternBillDetailEX->queryBillNoOfPecies($value->sellbillid);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
//$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
$theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
//$theDiscount -= $value->discountvalue;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($value->selltype == 0) {
$myproduct->soldNo += $finalquantity; //العدد بالقطعة
$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
##realcost
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost += $finalquantity * $value->buyprice * $vatValue;
break;
case 2:
$myproduct->realCost += $finalquantity * $value->lastbuyprice * $vatValue;
break;
case 1:
$myproduct->realCost += $finalquantity * $value->meanbuyprice * $vatValue;
break;
case 3:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
break;
case 4:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
break;
case 5:
$myproduct->realCost += $finalquantity * $value->generalPrice * $vatValue;
break;
case 6:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withTax * $vatValue;
break;
case 7:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withTax * $vatValue;
break;
}
} else {
$myproduct->returnNo += $finalquantity; //العدد بالقطعة
$myproduct->returnVal += $value->sellbilldetailtotalprice + $theDiscount;
##realcost
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost -= $finalquantity * $value->buyprice * $vatValue;
break;
case 2:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice * $vatValue;
break;
case 1:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice * $vatValue;
break;
case 3:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
break;
case 4:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
break;
case 5:
$myproduct->realCost -= $finalquantity * $value->generalPrice * $vatValue;
break;
case 6:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withTax * $vatValue;
break;
case 7:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withTax * $vatValue;
break;
}
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
}
#############finally get net , real cost ,net profit
$quantityAndLimitDataArr = R::getAll('SELECT product.productId,product.limitamount,SUM(storedetail.productquantity) AS sumProductQuantity
FROM product
JOIN storedetail
ON storedetail.productid = product.productId
WHERE product.productId in(' . implode(',', $productIdsArrAll) . ')
' . $myQuery . '
group by storedetail.productid');
$quantityAndLimitDataArr = customArrayIndexOne($quantityAndLimitDataArr, 'productId');
foreach ($allDataArr as $data) {
//$found_index = searchForId($data->productCatId, $productCatDataArr, 'productCatId');
if ($productCatDataArr[$data->productCatId] !== null) {
$data->productCatName = $productCatDataArr[$data->productCatId]['productCatName'];
}
$data->netNo = $data->soldNo - $data->returnNo;
$data->netVal = $data->soldVal - $data->returnVal;
// ##realcost
// $buyDatail = $ProductDAO->load($data->id); ## i may use ->loadext() to get quantity
// $data->realCost = $data->netNo * $buyDatail->productBuyPrice;
$data->netProfit = $data->netVal - $data->realCost;
if ($buyDatail->isOptic == 2) {
$catName = $ProductEX->loadProductCatNameOnly($data->id);
$data->productName = $data->productName . "/" . $catName;
}
##currentQuantity
//$found_index = searchForId($data->id, $quantityAndLimitDataArr, 'productId');
if ($quantityAndLimitDataArr[$data->id] !== null) {
$data->currentQuantity = $quantityAndLimitDataArr[$data->id]['sumProductQuantity'];
$data->limitamount = $quantityAndLimitDataArr[$data->id]['limitamount'];
}
//$buyDatail = $ProductEX->getProductQuantity($data->id, $myQuery);
// $data->currentQuantity = $buyDatail->sumProductQuantity;
// $data->limitamount = $buyDatail->limitamount;
##totals
$totalsObj->soldNo += $data->soldNo;
$totalsObj->soldVal += $data->soldVal;
$totalsObj->returnNo += $data->returnNo;
$totalsObj->returnVal += $data->returnVal;
$totalsObj->netNo += $data->netNo;
$totalsObj->netVal += $data->netVal;
$totalsObj->realCost += $data->realCost;
$totalsObj->netProfit += $data->netProfit;
$totalsObj->currentQuantity += $data->currentQuantity;
}
} elseif ($searchtype == 1 || $searchtype == 2) {
########################################################################
##start calculation &summation
##unique product cat
##فاتورة المبيعات
foreach ($sellBillData as $value) {
if (in_array($value->productCatId, $existId)) {
$key = array_search($value->productCatId, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->productCatId;
$myproduct->productName = $productCatDataArr[$value->productCatId]['productCatName'];
array_push($existId, $value->productCatId);
}
#########get quantity بالقطعة
$quantity = $value->sellbilldetailquantity;
$productId = $value->sellbilldetailproductid;
$productunitId = $value->productunitid;
$productnumber = $value->productnumber;
$finalquantity = $quantity * $productnumber;
##############
$myproduct->soldNo += $finalquantity; //العدد بالقطعة
######get discount
$dicount = 0;
$billNoOfProduct = 0;
//
$dicount = $value->sellbilldiscount;
if ($value->sellbilldiscounttype == 2) {
$dicount = ($value->sellbilldiscount / 100) * $value->parcode;
}
//$dicount = $value->parcode - $value->note;
// if ($dicount != 0) {
// $billNoOfProduct = $value->billnoofproducts;
// $flag = 1;
// }
if ($dicount != 0) {
$theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
$theDiscount = round($theDiscount, 2);
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($Programsetting->valueadded == 0) {
$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
} else {
$myproduct->soldVal += $value->sellbilldetailtotalprice + $theDiscount;
}
################
//$myproduct->buyPrice = $value->storeid;
//$myproduct->sellUnitPrice = (float) $value->sellunitprice;
//$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
//$myproduct->realCost += $finalquantity * $value->storeid;
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost += $finalquantity * $value->buyprice * $vatValue;
break;
case 2:
$myproduct->realCost += $finalquantity * $value->lastbuyprice * $vatValue;
break;
case 1:
$myproduct->realCost += $finalquantity * $value->meanbuyprice * $vatValue;
break;
case 3:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
break;
case 4:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
break;
case 5:
$myproduct->realCost += $finalquantity * $value->generalPrice * $vatValue;
break;
case 6:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withTax * $vatValue;
break;
case 7:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withTax * $vatValue;
break;
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
}
##فاتورة مردود المبيعات
foreach ($sellBillDataReturn as $value) {
if (in_array($value->productCatId, $existId)) {
$key = array_search($value->productCatId, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->productCatId;
$myproduct->productName = $productCatDataArr[$value->productCatId]['productCatName'];
array_push($existId, $value->productCatId);
}
#########get quantity بالقطعة
$quantity = $value->returnsellbilldetailquantity;
$productId = $value->returnsellbilldetailproductid;
$productunitId = $value->productunitid;
$productnumber = $value->productnumber;
$finalquantity = $quantity * $productnumber;
##############
$myproduct->returnNo += $finalquantity; //العدد بالقطعة
######get discount
$dicount = 0;
$billNoOfProduct = 0;
$dicount = $value->returnsellbilldiscount;
if ($value->returnsellbilldiscounttype == 2) {
$dicount = $value->returnsellbilldiscount * $value->parcode / 100;
}
//$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$billpecies = $returnSellBillDetailEX->queryBillNoOfPecies($value->returnsellbillid);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
//$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
$theDiscount = ($value->returnsellbilldetailtotalprice / $value->parcode) * $dicount;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($Programsetting->valueadded == 0) {
$myproduct->returnVal += $value->returnsellbilldetailtotalprice - $theDiscount;
} else {
$myproduct->returnVal += $value->returnsellbilldetailtotalprice + $theDiscount;
}
// $myproduct->buyPrice = $value->storeid;
// $myproduct->sellUnitPrice = (float) $value->sellunitprice;
// $myproduct->realCost -= $finalquantity * $value->storeid;
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost -= $finalquantity * $value->buyprice * $vatValue;
break;
case 2:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice * $vatValue;
break;
case 1:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice * $vatValue;
break;
case 3:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
break;
case 4:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
break;
case 5:
$myproduct->realCost -= $finalquantity * $value->generalPrice * $vatValue;
break;
case 6:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withTax * $vatValue;
break;
case 7:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withTax * $vatValue;
break;
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
}
##فاتورة مبيعات و مردود
foreach ($sellBillDataSellAndReturn as $value) {
if (in_array($value->productCatId, $existId)) {
$key = array_search($value->productCatId, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->productCatId;
$myproduct->productName = $productCatDataArr[$value->productCatId]['productCatName'];
array_push($existId, $value->productCatId);
}
#########get quantity بالقطعة
$quantity = $value->sellbilldetailquantity;
$productId = $value->sellbilldetailproductid;
$productunitId = $value->productunitid;
$productnumber = $value->productnumber;
$finalquantity = $quantity * $productnumber;
##############
######get discount
$dicount = 0;
$billNoOfProduct = 0;
if ($value->sellbilldiscounttype == 2) {
$dicount = ($value->sellbilldiscount / 100) * $value->parcode;
}
//$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$billpecies = $sellAndRuternBillDetailEX->queryBillNoOfPecies($value->sellbillid);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
//$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
$theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
//$theDiscount -= $value->discountvalue;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($value->selltype == 0) {
$myproduct->soldNo += $finalquantity; //العدد بالقطعة
$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
// $myproduct->realCost += $finalquantity * $value->storeid;
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost += $finalquantity * $value->buyprice * $vatValue;
break;
case 2:
$myproduct->realCost += $finalquantity * $value->lastbuyprice * $vatValue;
break;
case 1:
$myproduct->realCost += $finalquantity * $value->meanbuyprice * $vatValue;
break;
case 3:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
break;
case 4:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
break;
case 5:
$myproduct->realCost += $finalquantity * $value->generalPrice * $vatValue;
break;
case 6:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withTax * $vatValue;
break;
case 7:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withTax * $vatValue;
break;
}
} else {
$myproduct->returnNo += $finalquantity; //العدد بالقطعة
$myproduct->returnVal += $value->sellbilldetailtotalprice + $theDiscount;
// $myproduct->realCost -= $finalquantity * $value->storeid;
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost -= $finalquantity * $value->buyprice * $vatValue;
break;
case 2:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice * $vatValue;
break;
case 1:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice * $vatValue;
break;
case 3:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount * $vatValue;
break;
case 4:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount * $vatValue;
break;
case 5:
$myproduct->realCost -= $finalquantity * $value->generalPrice * $vatValue;
break;
case 6:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withTax * $vatValue;
break;
case 7:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withTax * $vatValue;
break;
}
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
}
#############finally get net , real cost ,net profit
$quantityAndLimitDataArr = R::getAll('SELECT product.productCatId,product.limitamount,SUM(storedetail.productquantity) AS sumProductQuantity
FROM product
JOIN storedetail
ON storedetail.productid = product.productId
WHERE product.productId in(' . implode(',', $productIdsArrAll) . ')
' . $myQuery . '
group by product.productCatId');
$quantityAndLimitDataArr = customArrayIndexOne($quantityAndLimitDataArr, 'productCatId');
foreach ($allDataArr as $data) {
if ($quantityAndLimitDataArr[$data->id] !== null) {
$data->currentQuantity = $quantityAndLimitDataArr[$data->id]['sumProductQuantity'];
}
$data->netNo = $data->soldNo - $data->returnNo;
$data->netVal = $data->soldVal - $data->returnVal;
##realcost
$totalsObj->realCost += $data->realCost;
$data->netProfit = round(($data->netVal - $data->realCost), 2);
##totals
$totalsObj->soldNo += $data->soldNo;
$totalsObj->soldVal += $data->soldVal;
$totalsObj->returnNo += $data->returnNo;
$totalsObj->returnVal += $data->returnVal;
$totalsObj->netNo += $data->netNo;
$totalsObj->netVal += $data->netVal;
$totalsObj->netProfit += $data->netProfit;
$totalsObj->currentQuantity += $data->currentQuantity;
}
if ($searchtype == 2) {
$allDataArrTemp = array();
$existId = array();
foreach ($allDataArr as $data) {
##get main parent which parentid is 0 weather it is the same cat or bigger parent
$mainCat = new stdClass();
getMainParentId($data->id);
##gather in one arr
if (in_array($mainCat->productCatId, $existId)) {
$key = array_search($mainCat->productCatId, $existId);
$mycatRowinArr = $allDataArrTemp[$key];
$mycatRowinArr->soldNo += $data->soldNo;
$mycatRowinArr->soldVal += $data->soldVal;
$mycatRowinArr->returnNo += $data->returnNo;
$mycatRowinArr->returnVal += $data->returnVal;
$mycatRowinArr->netNo += $data->netNo;
$mycatRowinArr->netVal += $data->netVal;
$mycatRowinArr->realCost += $data->realCost;
$mycatRowinArr->netProfit += $data->netProfit;
$mycatRowinArr->currentQuantity += $data->currentQuantity;
$mycatRowinArr->limitamount += $data->limitamount;
} else {
$key = -1;
$mycatRowinArr = $data;
$mycatRowinArr->id = $mainCat->productCatId;
$mycatRowinArr->productName = $mainCat->productCatName;
array_push($existId, $data->id);
array_push($allDataArrTemp, $mycatRowinArr);
}
}
$allDataArr = $allDataArrTemp;
}
}
$smarty->assign("totalsObj", $totalsObj);
$smarty->assign("resultsCount", $resultsCount);
$smarty->assign("resultsCount", count($allDataArr));
$smarty->assign("allDataArr", $allDataArr);
}
function getData($queryString, $queryString1, $queryStringR, $queryString1R, $queryString1SR, $chosenProductPrice, $searchtype, $productCatId, $theStore) {
global $billsProductsEX;
global $billsDAO;
global $billsEX;
global $billsReturnDAO;
global $billsReturnProductsEX;
global $sellbilldetailEX;
global $returnSellBillDetailEX;
global $sellAndRuternBillDetailEX;
global $ProductDAO;
global $productCatDAO;
global $ProductEX;
global $ProgramsettingDAO;
global $smarty;
global $lastLevelCatIDS;
global $catsIDS;
class productData {
public $id;
public $productName;
public $soldNo = 0;
public $soldVal = 0;
public $returnNo = 0;
public $returnVal = 0;
public $netNo = 0;
public $netVal = 0;
public $realCost = 0;
public $netProfit = 0;
public $buyPrice = 0;
public $currentQuantity = 0;
public $limitamount = 0;
}
if ($queryString1 == " where 1 ") {
return;
}
$existId = array();
$allDataArr = array();
$totalsObj = new productData();
$resultsCount = 0;
$flag = 0;
if ($searchtype == 0) {
##if no search make it today
/* if ($queryString1 == " where 1 ") {
##date
$datefrom = $dateto = $today = date("Y-m-d");
//display the converted time ##reportsPlusHours
$Programsetting = $ProgramsettingDAO->load(1);
##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)));
$dateto = date('Y-m-d H:i:s', strtotime('+' . $reportsPlusHours . ' hour +0 minutes', strtotime($dateto)));
} else {
$datefrom = $datefrom . " 00:00:00";
$dateto = $dateto . ' 23:59:59';
}
$queryString .= 'and bills.billdate >= "' . $datefrom . '" ';
$queryStringR .= 'and billsreturn.`date` >= "' . $datefrom . '" ';
$queryString1 .= 'and sellbill.sellbilldate >= "' . $datefrom . '" ';
$queryString1R .= 'and returnsellbill.returnsellbilldate >= "' . $datefrom . '" ';
$queryString1SR .= 'and sellbillandrutern.sellbilldate >= "' . $datefrom . '" ';
//
$queryString .= 'and bills.billdate <= "' . $dateto . '" ';
$queryStringR .= 'and billsreturn.`date` <= "' . $dateto . '" ';
$queryString1 .= 'and sellbill.sellbilldate <= "' . $dateto . '" ';
$queryString1R .= 'and returnsellbill.returnsellbilldate <= "' . $dateto . '" ';
$queryString1SR .= 'and sellbillandrutern.sellbilldate <= "' . $dateto . '" ';
}
} */
// $billsData = $billsProductsEX->queryAllGeneral($queryString);
// $billsDataReturn = $billsReturnProductsEX->queryAllGeneral($queryStringR);
$sellBillData = $sellbilldetailEX->queryAllGeneral($queryString1);
$sellBillDataReturn = $returnSellBillDetailEX->queryAllGeneral($queryString1R);
$sellBillDataSellAndReturn = $sellAndRuternBillDetailEX->queryAllGeneral($queryString1SR);
$resultsCount = count($billsData) + count($billsDataReturn) + count($sellBillData) + count($sellBillDataReturn) + count($sellBillDataSellAndReturn);
$smarty->assign("resultsCount", $resultsCount);
if ($resultsCount > 0) {
##unique products
/* ##مبيعات البصريات
foreach ($billsData as $value) {
if (in_array($value->productid, $existId)) {
$key = array_search($value->productid, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->productid;
array_push($existId, $value->productid);
}
$myproduct->productName = $value->productName;
$myproduct->soldNo += $value->productno; //no directly no product unit used
///emaaaaaaaan
$myproduct->productCatName = $value->productCatName;
##get discount
$theBill = $billsDAO->load($value->billid);
$dicount = 0;
$billNoOfProduct = 0;
if ($theBill->card == 1) {
if ($theBill->paymentnetworkid == 4) {
//مدى
$madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
if ($madaData->totalCarry < 5000)
$dicount = (7 * $madaData->totalCarry) / 1000;
else
$dicount = 40;
} else {
$dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
}
}
if ($dicount != 0) {
$billpecies = $billsProductsEX->queryBillNoOfPecies($value->billid);
$billNoOfProduct = $billpecies->deleted;
$flag = 1;
}
$dicount += ($theBill->productstotalprice - $theBill->finalnetbillvalue); //خصم النسبة و خصم الكشف
if ($flag == 1) {
$theDiscount = ($value->productno * $dicount) / $billNoOfProduct;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
$myproduct->soldVal += $value->producttotalprice - $theDiscount;
$myproduct->buyPrice = $value->deleted;
##realcost
switch ($chosenProductPrice) {
case 0:
case 2:
$myproduct->realCost += $value->productno * $value->lastbuyprice;
break;
case 1:
$myproduct->realCost += $value->productno * $value->meanbuyprice;
break;
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
}
##مرتجعات البصريات
foreach ($billsDataReturn as $value) {
if (in_array($value->productid, $existId)) {
$key = array_search($value->productid, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->productid;
array_push($existId, $value->productid);
}
$myproduct->productName = $value->productName;
$myproduct->returnNo += $value->productno; //no directly no product unit used
//emaaaaaaaan
$myproduct->productCatName = $value->productCatName;
##get discount
$theBill = $billsDAO->load($value->billproductid);
$dicount = 0;
$billNoOfProduct = 0;
if ($theBill->card == 1) {
if ($theBill->paymentnetworkid == 4) {
//مدى
$madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
if ($madaData->totalCarry < 5000)
$dicount = (7 * $madaData->totalCarry) / 1000;
else
$dicount = 40;
} else {
$dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
}
}
if ($dicount != 0) {
$billpecies = $billsProductsEX->queryBillNoOfPecies($value->billproductid);
$billNoOfProduct = $billpecies->deleted;
$flag = 1;
}
$dicount += ($theBill->productstotalprice - $theBill->finalnetbillvalue); //خصم النسبة و خصم الكشف
if ($flag == 1) {
$theDiscount = ($value->productno * $dicount) / $billNoOfProduct;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
$myproduct->returnVal += $value->producttotalprice + $theDiscount;
$myproduct->buyPrice = $value->deleted;
##realcost
switch ($chosenProductPrice) {
case 0:
case 2:
$myproduct->realCost -= $value->productno * $value->lastbuyprice;
break;
case 1:
$myproduct->realCost -= $value->productno * $value->meanbuyprice;
break;
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
} */
##فاتورة المبيعات
foreach ($sellBillData as $value) {
if (in_array($value->sellbilldetailproductid, $existId)) {
$key = array_search($value->sellbilldetailproductid, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->sellbilldetailproductid;
array_push($existId, $value->sellbilldetailproductid);
}
$myproduct->productName = $value->productName;
//emaaaaaaaan
$myproduct->productCatName = $value->productCatName;
#########get quantity بالقطعة
$quantity = $value->sellbilldetailquantity;
$productId = $value->sellbilldetailproductid;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber;
##############
$myproduct->soldNo += $finalquantity; //العدد بالقطعة
######get discount
$dicount = 0;
$billNoOfProduct = 0;
$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$billpecies = $sellbilldetailEX->queryBillNoOfPecies($value->sellbillid);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
//$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
$theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
//$theDiscount -= $value->discountvalue;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($Programsetting->valueadded == 0) {
$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
} else {
$myproduct->soldVal += $value->sellbilldetailtotalprice + $theDiscount;
}
$myproduct->buyPrice = $value->storeid;
$myproduct->sellUnitPrice = (float) $value->sellunitprice;
##realcost
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost += $finalquantity * $value->buyprice;
break;
case 2:
$myproduct->realCost += $finalquantity * $value->lastbuyprice;
break;
case 1:
$myproduct->realCost += $finalquantity * $value->meanbuyprice;
break;
case 3:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount;
break;
case 4:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount;
break;
case 5:
$myproduct->realCost += $finalquantity * $value->generalPrice;
break;
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
}
##فاتورة مردود المبيعات
foreach ($sellBillDataReturn as $value) {
if (in_array($value->returnsellbilldetailproductid, $existId)) {
$key = array_search($value->returnsellbilldetailproductid, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->returnsellbilldetailproductid;
array_push($existId, $value->returnsellbilldetailproductid);
}
$myproduct->productName = $value->productName;
//emaaaaaaaan
$myproduct->productCatName = $value->productCatName;
#########get quantity بالقطعة
$quantity = $value->returnsellbilldetailquantity;
$productId = $value->returnsellbilldetailproductid;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber;
##############
$myproduct->returnNo += $finalquantity; //العدد بالقطعة
######get discount
$dicount = 0;
$billNoOfProduct = 0;
$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$billpecies = $returnSellBillDetailEX->queryBillNoOfPecies($value->returnsellbillid);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
//$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
$theDiscount = ($value->returnsellbilldetailtotalprice / $value->parcode) * $dicount;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($Programsetting->valueadded == 0) {
$myproduct->returnVal += $value->returnsellbilldetailtotalprice - $theDiscount;
} else {
$myproduct->returnVal += $value->returnsellbilldetailtotalprice + $theDiscount;
}
$myproduct->buyPrice = $value->storeid;
$myproduct->sellUnitPrice = (float) $value->sellunitprice;
##realcost
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost -= $finalquantity * $value->buyprice;
break;
case 2:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice;
break;
case 1:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice;
break;
case 3:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount;
break;
case 4:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount;
break;
case 5:
$myproduct->realCost -= $finalquantity * $value->generalPrice;
break;
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
}
##فاتورة مبيعات و مردود
foreach ($sellBillDataSellAndReturn as $value) {
if (in_array($value->sellbilldetailproductid, $existId)) {
$key = array_search($value->sellbilldetailproductid, $existId);
$myproduct = $allDataArr[$key];
} else {
$key = -1;
$myproduct = new productData();
$myproduct->id = $value->sellbilldetailproductid;
array_push($existId, $value->sellbilldetailproductid);
}
$myproduct->productName = $value->productName;
//emaaaaaaaan
$myproduct->productCatName = $value->productCatName;
#########get quantity بالقطعة
$quantity = $value->sellbilldetailquantity;
$productId = $value->sellbilldetailproductid;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber;
$myproduct->buyPrice = $value->storeid;
$myproduct->sellUnitPrice = (float) $value->sellunitprice;
##############
######get discount
$dicount = 0;
$billNoOfProduct = 0;
$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$billpecies = $sellAndRuternBillDetailEX->queryBillNoOfPecies($value->sellbillid);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
//$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
$theDiscount = ($value->sellbilldetailtotalprice / $value->parcode) * $dicount;
//$theDiscount -= $value->discountvalue;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($value->selltype == 0) {
$myproduct->soldNo += $finalquantity; //العدد بالقطعة
$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
##realcost
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost += $finalquantity * $value->buyprice;
break;
case 2:
$myproduct->realCost += $finalquantity * $value->lastbuyprice;
break;
case 1:
$myproduct->realCost += $finalquantity * $value->meanbuyprice;
break;
case 3:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount;
break;
case 4:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount;
break;
case 5:
$myproduct->realCost += $finalquantity * $value->generalPrice;
break;
}
} else {
$myproduct->returnNo += $finalquantity; //العدد بالقطعة
$myproduct->returnVal += $value->sellbilldetailtotalprice + $theDiscount;
##realcost
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost -= $finalquantity * $value->buyprice;
break;
case 2:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice;
break;
case 1:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice;
break;
case 3:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount;
break;
case 4:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount;
break;
case 5:
$myproduct->realCost -= $finalquantity * $value->generalPrice;
break;
}
}
if ($key == -1) {
array_push($allDataArr, $myproduct);
}
}
#############finally get net , real cost ,net profit
$myQuery = "";
if (isset($theStore) && !empty($theStore) && $theStore != -1) {
$myQuery = " and storedetail.storeid = " . $theStore;
} else if ($_SESSION['searchinonestore'] == 0) {
if ($_SESSION['storeids'] != 0) {
$myQuery = ' and storedetail.storeid in (' . $_SESSION['storeids'] . ') ';
}
} else {
$myQuery = " and storedetail.storeid = " . $_SESSION['storeid'];
;
}
foreach ($allDataArr as $data) {
$data->netNo = $data->soldNo - $data->returnNo;
$data->netVal = $data->soldVal - $data->returnVal;
// ##realcost
// $buyDatail = $ProductDAO->load($data->id); ## i may use ->loadext() to get quantity
// $data->realCost = $data->netNo * $buyDatail->productBuyPrice;
$data->netProfit = $data->netVal - $data->realCost;
if ($buyDatail->isOptic == 2) {
$catName = $ProductEX->loadProductCatNameOnly($data->id);
$data->productName = $data->productName . "/" . $catName;
}
##currentQuantity
$buyDatail = $ProductEX->getProductQuantity($data->id, $myQuery);
$data->currentQuantity = $buyDatail->sumProductQuantity;
$data->limitamount = $buyDatail->limitamount;
##totals
$totalsObj->soldNo += $data->soldNo;
$totalsObj->soldVal += $data->soldVal;
$totalsObj->returnNo += $data->returnNo;
$totalsObj->returnVal += $data->returnVal;
$totalsObj->netNo += $data->netNo;
$totalsObj->netVal += $data->netVal;
$totalsObj->realCost += $data->realCost;
$totalsObj->netProfit += $data->netProfit;
$totalsObj->currentQuantity += $data->currentQuantity;
}
$smarty->assign("totalsObj", $totalsObj);
}
} elseif ($searchtype == 1) {
if (isset($productCatId) && !empty($productCatId) && $productCatId != -1) {
$lastLevelCatIDS = array();
array_push($lastLevelCatIDS, $productCatId);
getAllSubCat($productCatId, 2); //mode = 2 get last level cats
foreach ($lastLevelCatIDS as $mycatId) {
$mycat = $productCatDAO->load($mycatId);
$isOpticCondition = "";
if ($mycat->isOptic == 2) {
$isOpticCondition = " and isOptic = 2 ";
} else {
$isOpticCondition = " and isOptic != 2 ";
}
##get subcat of cat
$catsIDS = '' . $mycatId;
getAllSubCat($mycatId, 1); //mode = 1 get all sub cats
$productsOfCat = $ProductEX->queryByProductCatIdIn($catsIDS, $isOpticCondition);
$IDS = '0';
foreach ($productsOfCat as $value) {
$IDS .= ',' . $value->productId;
}
$queryStringProduct = 'and billsproducts.productid in (' . $IDS . ') ';
$queryString1Product = 'and sellbilldetail.sellbilldetailproductid in (' . $IDS . ') ';
$queryStringRProduct = 'and billsreturnproducts.productid in (' . $IDS . ') ';
$queryString1RProduct = 'and returnsellbilldetail.returnsellbilldetailproductid in (' . $IDS . ') ';
$queryString1SRProduct = 'and sellandruternbilldetail.sellbilldetailproductid in (' . $IDS . ') ';
##if no search make it today
if ($queryString1 == " where 1 ") {
##date
$today = date("Y-m-d");
$queryString .= 'and bills.billdate = "' . $today . '" ';
$queryStringR .= 'and billsreturn.`date` = "' . $today . '" ';
$queryString1 .= 'and sellbill.sellbilldate = "' . $today . '" ';
$queryString1R .= 'and returnsellbill.returnsellbilldate = "' . $today . '" ';
$queryString1SR .= 'and sellbillandrutern.sellbilldate = "' . $today . '" ';
}
##now like search type == 0
// $billsData = $billsProductsEX->queryAllGeneral($queryString . $queryStringProduct);
// $billsDataReturn = $billsReturnProductsEX->queryAllGeneral($queryStringR . $queryStringRProduct);
$sellBillData = $sellbilldetailEX->queryAllGeneral($queryString1 . $queryString1Product);
$sellBillDataReturn = $returnSellBillDetailEX->queryAllGeneral($queryString1R . $queryString1RProduct);
$sellBillDataSellAndReturn = $sellAndRuternBillDetailEX->queryAllGeneral($queryString1SR . $queryString1SRProduct);
$thisResultsCount = count($billsData) + count($billsDataReturn) + count($sellBillData) + count($sellBillDataReturn) + count($sellBillDataSellAndReturn);
$resultsCount += $thisResultsCount;
if ($thisResultsCount > 0) {
$mycatObj = $productCatDAO->load($mycatId);
$myproduct = new productData();
$myproduct->id = $mycatObj->productCatId;
$myproduct->productName = $mycatObj->productCatName;
##unique product cat
/* ##مبيعات البصريات
foreach ($billsData as $value) {
##get discount
$theBill = $billsDAO->load($value->billid);
$dicount = 0;
$billNoOfProduct = 0;
if ($theBill->card == 1) {
if ($theBill->paymentnetworkid == 4) {
//مدى
$madaData = $billsEX->queryTotalNetworkReportMadaSimple($theBill->billdate);
if ($madaData->totalCarry < 5000)
$dicount = (7 * $madaData->totalCarry) / 1000;
else
$dicount = 40;
} else {
$dicount = ($theBill->cardvalue * $theBill->netdiscountpercent) / 100;
}
}
if ($dicount != 0) {
$billpecies = $billsProductsEX->queryBillNoOfPecies($value->billid);
$billNoOfProduct = $billpecies->deleted;
$flag = 1;
}
if ($flag == 1) {
$theDiscount = ($value->productno * $dicount) / $billNoOfProduct;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
$myproduct->soldNo += $value->productno; //no directly no product unit used
$myproduct->soldVal += $value->producttotalprice - $theDiscount;
$myproduct->realCost = $myproduct->realCost + ($value->productno * $value->deleted);
}
##مرتجعات البصريات
foreach ($billsDataReturn as $value) {
$myproduct->returnNo += $value->productno; //no directly no product unit used
$myproduct->returnVal += $value->producttotalprice;
$myproduct->realCost = $myproduct->realCost - ($value->productno * $value->deleted);
} */
##فاتورة المبيعات
foreach ($sellBillData as $value) {
#########get quantity بالقطعة
$quantity = $value->sellbilldetailquantity;
$productId = $value->sellbilldetailproductid;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber;
##############
$myproduct->soldNo += $finalquantity; //العدد بالقطعة
######get discount
$dicount = 0;
$billNoOfProduct = 0;
$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$billpecies = $sellbilldetailEX->queryBillNoOfPecies($value->sellbillid);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
//$theDiscount -= $value->discountvalue;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
// $myproduct->realCost += $finalquantity * $value->storeid;
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost += $finalquantity * $value->buyprice;
break;
case 2:
$myproduct->realCost += $finalquantity * $value->lastbuyprice;
break;
case 1:
$myproduct->realCost += $finalquantity * $value->meanbuyprice;
break;
case 3:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount;
break;
case 4:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount;
break;
case 5:
$myproduct->realCost += $finalquantity * $value->generalPrice;
break;
}
}
##فاتورة مردود المبيعات
foreach ($sellBillDataReturn as $value) {
#########get quantity بالقطعة
$quantity = $value->returnsellbilldetailquantity;
$productId = $value->returnsellbilldetailproductid;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber;
##############
$myproduct->returnNo += $finalquantity; //العدد بالقطعة
######get discount
$dicount = 0;
$billNoOfProduct = 0;
$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$billpecies = $returnSellBillDetailEX->queryBillNoOfPecies($value->returnsellbillid);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
$myproduct->returnVal += $value->returnsellbilldetailtotalprice + $theDiscount;
// $myproduct->realCost -= $finalquantity * $value->storeid;
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost -= $finalquantity * $value->buyprice;
break;
case 2:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice;
break;
case 1:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice;
break;
case 3:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount;
break;
case 4:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount;
break;
case 5:
$myproduct->realCost -= $finalquantity * $value->generalPrice;
break;
}
}
##فاتورة مبيعات و مردود
foreach ($sellBillDataSellAndReturn as $value) {
#########get quantity بالقطعة
$quantity = $value->sellbilldetailquantity;
$productId = $value->sellbilldetailproductid;
$productunitId = $value->productunitid;
$productunitData = loadProductUnitWithProductAndUnit($productId, $productunitId);
$productnumber = $productunitData->productnumber;
$finalquantity = $quantity * $productnumber;
##############
######get discount
$dicount = 0;
$billNoOfProduct = 0;
$dicount = $value->parcode - $value->note;
if ($dicount != 0) {
$billpecies = $sellAndRuternBillDetailEX->queryBillNoOfPecies($value->sellbillid);
$billNoOfProduct = $billpecies->note;
$flag = 1;
}
if ($flag == 1) {
$theDiscount = ($finalquantity * $dicount) / $billNoOfProduct;
//$theDiscount -= $value->discountvalue;
$theDiscount = round($theDiscount, 2);
$flag = 0;
} else {
$theDiscount = $dicount;
$theDiscount = round($theDiscount, 2);
}
################
if ($value->selltype == 0) {
$myproduct->soldNo += $finalquantity; //العدد بالقطعة
$myproduct->soldVal += $value->sellbilldetailtotalprice - $theDiscount;
// $myproduct->realCost += $finalquantity * $value->storeid;
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost += $finalquantity * $value->buyprice;
break;
case 2:
$myproduct->realCost += $finalquantity * $value->lastbuyprice;
break;
case 1:
$myproduct->realCost += $finalquantity * $value->meanbuyprice;
break;
case 3:
$myproduct->realCost += $finalquantity * $value->lastbuyprice_withDiscount;
break;
case 4:
$myproduct->realCost += $finalquantity * $value->meanbuyprice_withDiscount;
break;
case 5:
$myproduct->realCost += $finalquantity * $value->generalPrice;
break;
}
} else {
$myproduct->returnNo += $finalquantity; //العدد بالقطعة
$myproduct->returnVal += $value->sellbilldetailtotalprice + $theDiscount;
// $myproduct->realCost -= $finalquantity * $value->storeid;
switch ($chosenProductPrice) {
case 0:
$myproduct->realCost -= $finalquantity * $value->buyprice;
break;
case 2:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice;
break;
case 1:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice;
break;
case 3:
$myproduct->realCost -= $finalquantity * $value->lastbuyprice_withDiscount;
break;
case 4:
$myproduct->realCost -= $finalquantity * $value->meanbuyprice_withDiscount;
break;
case 5:
$myproduct->realCost -= $finalquantity * $value->generalPrice;
break;
}
}
}
array_push($allDataArr, $myproduct);
}
}
#############finally get net , real cost ,net profit
foreach ($allDataArr as $data) {
$data->netNo = $data->soldNo - $data->returnNo;
$data->netVal = $data->soldVal - $data->returnVal;
##realcost
$totalsObj->realCost += $data->realCost;
$data->netProfit = $data->netVal - $data->realCost;
##totals
$totalsObj->soldNo += $data->soldNo;
$totalsObj->soldVal += $data->soldVal;
$totalsObj->returnNo += $data->returnNo;
$totalsObj->returnVal += $data->returnVal;
$totalsObj->netNo += $data->netNo;
$totalsObj->netVal += $data->netVal;
$totalsObj->netProfit += $data->netProfit;
}
$smarty->assign("totalsObj", $totalsObj);
}
##productId
//he can not choose product with out product cat
/* elseif (isset($productId) && !empty($productId)) {
} */
}
$smarty->assign("resultsCount", $resultsCount);
$smarty->assign("allDataArr", $allDataArr);
}
function getMainParentId($catid) {
global $mainCat;
$mainCat = (object) R::getRow('select * from productcat where productCatId=' . $catid);
if ($mainCat->productCatParent == 0) {
return;
} else {
getMainParentId($mainCat->productCatParent);
}
}
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 CURL_IT2($data_arr, $url) {
##preparing session data
//$projectURL = 'http://' . $_SERVER['HTTP_HOST'] . explode('controllers', $_SERVER['REQUEST_URI'])[0] . 'controllers/';
$url = 'http://' . $_SERVER['HTTP_HOST'] . explode('controllers', $_SERVER['REQUEST_URI'])[0] . 'controllers/' . $url;
##appending necessary data
$data_arr['curlpost'] = '1';
$data_arr['sessionlist'] = json_encode($_SESSION);
// set post fields
// $post = [
// 'data_arr' => json_encode($data_arr), //encode it as it is array
// ];
$post = $data_arr;
$ch = curl_init($url);
//
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $post); //
//Disable CURLOPT_SSL_VERIFYHOST and CURLOPT_SSL_VERIFYPEER by
//setting them to false.
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
//curl_setopt($ch, CURLOPT_TIMEOUT, 10); //
// execute!
$response = curl_exec($ch);
if ($response === false) {
//echo 'Error : ' . curl_error($ch);
}
// close the connection, release resources used
curl_close($ch);
// do anything you want with your response
//var_dump($response);
//print_r($response);
return $response;
}