File: /home/mostafedeg/public_html/erp/controllers/chartserp.php
<?php
include_once("../public/con_reedbean.php");
$do = $_GET['do'];
if (empty($do)) {
$smarty->display("chartserp/index.html");
}else if ($do == 'categories') {
categories();
}else if ($do == 'products') {
products();
}else if ($do == 'select2products') {
select2products();
}else if ($do == 'select2clients') {
select2clients();
}else if ($do == 'select2categories') {
select2categories();
}else if ($do == 'clients') {
clients();
}else if ($do == 'sellbills') {
sellbills();
}else if ($do == 'chartssellbill') {
$smarty->display("chartserp/chartssellbill.html");
}else if ($do == 'chartsproducts') {
$smarty->display("chartserp/chartsproducts.html");
}else if ($do == 'chartsclients') {
$smarty->display("chartserp/chartsclients.html");
}else if ($do == 'chartscategories') {
$smarty->display("chartserp/chartscategories.html");
}else if ($do == 'getdata') {
getdata();
}
function select2categories() {
$row_array = array();
$return_arr = array();
$name = $_POST['searchTerm'];
$productsData = R::getAll("SELECT productCatId, productCatName as name
FROM productcat
WHERE productCatName LIKE '%" . $name . "%' limit 50");
foreach ($productsData as $pro) {
$row_array['id'] = $pro['productCatId'];
$row_array['text'] = $pro['name'];
array_push($return_arr, $row_array);
}
echo json_encode($return_arr);
}
function select2products() {
$row_array = array();
$return_arr = array();
$name = $_POST['searchTerm'];
$productsData = R::getAll("SELECT productId,CONCAT(productName,'/',productCatName) as name
FROM product left join productcat on product.productCatId = productcat.productCatId
WHERE CONCAT(productName,'/',productCatName) LIKE '%" . $name . "%' limit 50");
foreach ($productsData as $pro) {
$row_array['id'] = $pro['productId'];
$row_array['text'] = $pro['name'];
array_push($return_arr, $row_array);
}
echo json_encode($return_arr);
}
function select2clients() {
$row_array = array();
$return_arr = array();
$name = $_POST['searchTerm'];
$productsData = R::getAll("SELECT clientid, clientname as name
FROM client
WHERE clientname LIKE '%" . $name . "%' limit 50");
foreach ($productsData as $pro) {
$row_array['id'] = $pro['clientid'];
$row_array['text'] = $pro['name'];
array_push($return_arr, $row_array);
}
echo json_encode($return_arr);
}
function categories() {
$fromdate = $_POST['fromdate'];
$todate = $_POST['todate'];
$kindprice = $_POST['kindprice'];
$categorieid = $_POST['categorieid'];
if ($categorieid != '') {
if($fromdate != '' && $todate != ''){
$fromdate = strtotime("first day of this month",strtotime($fromdate));
$todate = strtotime("last day of this month",strtotime($todate));
}else{
$today = date('Y-m-d');
$fromdate = strtotime("first day of this month",strtotime($today));
$todate = strtotime("last day of this month",strtotime($today));
}
$new_allData = array();
while($fromdate <= $todate)
{
$start = date('Y-m-d', $fromdate);
$end = date("Y-m-t", strtotime($start));
$searchQuery = "";
$searchQuery .= 'and sellbilldetail.sellbilldetaildate >= "' . $start . '" and sellbilldetail.sellbilldetaildate <= "' . $end . '" and sellbilldetail.sellbilldetailcatid = " '.$categorieid.'"';
$mounthd = date('m', $fromdate);
$mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
$mounthds = $mounths[intval($mounthd)];
if($kindprice == ''){$kindprice = profitevaluation();}
$getdata = R::getrow("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, '$mounthds' as Namedata, sellbilldetaildate, productCatName, productName, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbill` left join sellbilldetail on sellbill.sellbillid = sellbilldetail.sellbillid left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery");
array_push($new_allData, $getdata);
$fromdate = strtotime("+1 month", $fromdate);
}
}else{
if($fromdate == '' || $todate == ''){
$fromdate = date('Y-m-d');
$todate = date('Y-m-d');
}
if($kindprice == ''){$kindprice = profitevaluation();}
$searchQuery .='and sellbilldetail.sellbilldetaildate >= "' . $fromdate . '" and sellbilldetail.sellbilldetaildate <= "' . $todate . '" ';
$new_allData = R::getAll("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals,sellbilldetailcatid, productCatName as Namedata, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbilldetail` left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery group by sellbilldetailcatid");
}
echo json_encode($new_allData);
}
function products() {
$fromdate = $_POST['fromdate'];
$todate = $_POST['todate'];
$kindprice = $_POST['kindprice'];
$productid = $_POST['productid'];
if ($productid != '') {
if($fromdate != '' && $todate != ''){
$fromdate = strtotime("first day of this month",strtotime($fromdate));
$todate = strtotime("last day of this month",strtotime($todate));
}else{
$today = date('Y-m-d');
$fromdate = strtotime("first day of this month",strtotime($today));
$todate = strtotime("last day of this month",strtotime($today));
}
$new_allData = array();
while($fromdate <= $todate)
{
$start = date('Y-m-d', $fromdate);
$end = date("Y-m-t", strtotime($start));
$searchQuery = "";
$searchQuery .= 'and sellbilldetail.sellbilldetaildate >= "' . $start . '" and sellbilldetail.sellbilldetaildate <= "' . $end . '" and sellbilldetail.sellbilldetailproductid = " '.$productid.'"';
$mounthd = date('m', $fromdate);
$mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
$mounthds = $mounths[intval($mounthd)];
if($kindprice == ''){$kindprice = profitevaluation();}
$getdata = R::getrow("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, '$mounthds' as Namedata, sellbilldetaildate, productCatName, productName, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbill` left join sellbilldetail on sellbill.sellbillid = sellbilldetail.sellbillid left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery");
array_push($new_allData, $getdata);
$fromdate = strtotime("+1 month", $fromdate);
}
}else{
if($fromdate == '' || $todate == ''){
$fromdate = date('Y-m-d');
$todate = date('Y-m-d');
}
if($kindprice == ''){$kindprice = profitevaluation();}
$searchQuery .='and sellbilldetail.sellbilldetaildate >= "' . $fromdate . '" and sellbilldetail.sellbilldetaildate <= "' . $todate . '" ';
$new_allData = R::getAll("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, productCatName, productName as Namedata, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbilldetail` left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery group by sellbilldetailproductid");
}
echo json_encode($new_allData);
}
function clients() {
$clientid = $_POST['clientid'];
$fromdate = $_POST['fromdate'];
$todate = $_POST['todate'];
$kindprice = $_POST['kindprice'];
if ($clientid != '') {
if($fromdate != '' && $todate != ''){
$fromdate = strtotime("first day of this month",strtotime($fromdate));
$todate = strtotime("last day of this month",strtotime($todate));
}else{
$today = date('Y-m-d');
$fromdate = strtotime("first day of this month",strtotime($today));
$todate = strtotime("last day of this month",strtotime($today));
}
$new_allData = array();
while($fromdate <= $todate)
{
$start = date('Y-m-d', $fromdate);
$end = date("Y-m-t", strtotime($start));
$searchQuery = "";
$searchQuery .= 'and sellbilldetail.sellbilldetaildate >= "' . $start . '" and sellbilldetail.sellbilldetaildate <= "' . $end . '" and sellbill.sellbillclientid = " '.$clientid.'"';
$mounthd = date('m', $fromdate);
$mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
$mounthds = $mounths[intval($mounthd)];
if($kindprice == ''){$kindprice = profitevaluation();}
$getdata = R::getrow("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, '$mounthds' as Namedata, sellbilldetaildate, productCatName, productName, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbill` left join sellbilldetail on sellbill.sellbillid = sellbilldetail.sellbillid left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery");
array_push($new_allData, $getdata);
$fromdate = strtotime("+1 month", $fromdate);
}
}else{
if($fromdate == '' || $todate == ''){
$fromdate = date('Y-m-d');
$todate = date('Y-m-d');
}
if($kindprice == ''){$kindprice = profitevaluation();}
$searchQuery = '';
$searchQuery .= " and sellbilldetail.sellbilldetaildate >= '$fromdate' and sellbilldetail.sellbilldetaildate <= '$todate' group by sellbillclientid";
$new_allData = R::getAll("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, clientname as Namedata, sellbilldetaildate, productCatName, productName, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbill` left join sellbilldetail on sellbill.sellbillid = sellbilldetail.sellbillid left join client on sellbill.sellbillclientid = client.clientid left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery");
}
echo json_encode($new_allData);
}
// function product() {
// $productid = $_POST['productid'];
// $fromdate = $_POST['fromdate'];
// $todate = $_POST['todate'];
// $kindprice = $_POST['kindprice'];
// if($fromdate != '' && $todate != ''){
// $fromdate = strtotime("first day of this month",strtotime($fromdate));
// $todate = strtotime("last day of this month",strtotime($todate));
// }else{
// $today = date('Y-m-d');
// $fromdate = strtotime("first day of this month",strtotime($today));
// $todate = strtotime("last day of this month",strtotime($today));
// }
// $new_allData = array();
// if($productid > 0) {
// while($fromdate <= $todate)
// {
// $start = date('Y-m-d', $fromdate);
// $end = date("Y-m-t", strtotime($start));
// $searchQuery = "";
// $searchQuery .= 'and sellbilldetail.sellbilldetaildate >= "' . $start . '" and sellbilldetail.sellbilldetaildate <= "' . $end . '"';
// if ($productid != '') {
// $searchQuery .= " and sellbilldetail.sellbilldetailproductid = ".$productid. " ";
// }
// $mounthd = date('m', $fromdate);
// $mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
// $mounthds = $mounths[intval($mounthd)];
// if($kindprice == ''){$kindprice = profitevaluation();}
// $getdata = R::getrow("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, '$mounthds' as Namedata, sellbilldetaildate, productCatName, productName, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbilldetail` left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery");
// array_push($new_allData, $getdata);
// $fromdate = strtotime("+1 month", $fromdate);
// }
// }
// echo json_encode($new_allData);
// }
function sellbills() {
$fromdate = $_POST['fromdate'];
$todate = $_POST['todate'];
$kindprice = $_POST['kindprice'];
if($fromdate != '' && $todate != ''){
$fromdate = strtotime("first day of this month",strtotime($fromdate));
$todate = strtotime("last day of this month",strtotime($todate));
}else{
$today = date('Y-m-d');
$fromdate = strtotime("first day of this month",strtotime($today));
$todate = strtotime("last day of this month",strtotime($today));
}
$new_allData = array();
while($fromdate <= $todate)
{
$start = date('Y-m-d', $fromdate);
$end = date("Y-m-t", strtotime($start));
$searchQuery = "";
$searchQuery .= 'and sellbilldetail.sellbilldetaildate >= "' . $start . '" and sellbilldetail.sellbilldetaildate <= "' . $end . '"';
$mounthd = date('m', $fromdate);
$mounths = array(' ','يناير','فبراير','مارس','أبريل','مايو','يونيو','يوليو','أغسطس','سبتمبر','أكتوبر','نوفمبر','ديسمبر');
$mounthds = $mounths[intval($mounthd)];
if($kindprice == ''){$kindprice = profitevaluation();}
$getdata = R::getrow("SELECT sum(sellbilldetailquantity) as quantitys, sum(sellbilldetailtotalprice) as totals, '$mounthds' as Namedata, sellbilldetaildate, SUM($kindprice * sellbilldetail.sellbilldetailquantity) AS totalpro FROM `sellbilldetail` left join productcat on sellbilldetail.sellbilldetailcatid = productcat.productCatId left join product on sellbilldetail.sellbilldetailproductid = product.productId WHERE 1 $searchQuery");
array_push($new_allData, $getdata);
$fromdate = strtotime("+1 month", $fromdate);
}
echo json_encode($new_allData);
}
function profitevaluation(){
$Profitevaluation = R::getCell("select Profitevaluation from programsettings where programsettingsid = 1");
$kindprice = '';
switch ($Profitevaluation) {
case 'first':
$kindprice .= 'sellbilldetail.buyprice';
break;
case 'last':
$kindprice .= 'sellbilldetail.lastbuyprice';
break;
case 'last_discount':
$kindprice .= 'sellbilldetail.lastbuyprice_withDiscount';
break;
case 'mean':
$kindprice .= 'sellbilldetail.meanbuyprice';
break;
case 'mean_discount':
$kindprice .= 'sellbilldetail.meanbuyprice_withDiscount';
break;
case 'generalPrice':
$kindprice .= 'product.productBuyPrice';
break;
default:
$kindprice .='sellbilldetail.buyprice';
break;
}
return $kindprice;
}
function getdata() {
// for ($i = 1; $i <= 200; $i++) {
// $tblmarks = R::xdispense('tbl_marks');
// $tblmarks->student_name = substr(str_shuffle('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'),1,10);
// $tblmarks->marks = rand(100000, 999999);
// R::store($tblmarks);
// }
$getdata = R::getAll("SELECT id,student_name,marks FROM tbl_marks ORDER BY id limit 81");
echo json_encode($getdata);
}
?>