File: /home/mostafedeg/public_html/erp/controllers/productstore.php
<?php
//the global file operation
include("../public/impOpreation.php");
//global varable
global $showoutside;
include_once("../public/config.php");
include("../public/include_dao.php");
$do = $_GET['do'];
addAllOrMissingRows();
if (empty($do)) {
require_once('../models/mysql/ext/StoreMySqlExtDAO.class.php');
$myStoreEx = new StoreMySqlExtDAO();
$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("storesData", $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']);
}
$smarty->display("header.html");
$smarty->display("productstoreview/show.html");
$smarty->display("footer.html");
} else if ($do == 'allproduct') {
###no need addAllOrMissingRows(); will do it
// try {
// $products = R::getAll("SELECT product.* FROM `product` WHERE product.conditions = 0 ");
// foreach ($products as $product) {
// $stores = R::getAll("SELECT * FROM `store` WHERE conditions = 0");
// foreach ($stores as $store) {
// $productstorecount = R::count("productstore", 'productid = ? and storeid = ? and productstore.conditions = 0', [$product['productId'], $store['storeId']]);
// if ($productstorecount == 0) {
// $productstore = R::dispense('productstore');
// $productstore->productid = $product['productId'];
// $productstore->storeid = $store['storeId'];
// $productstore->productcatid = $product['productCatId'];
// $productstore->limitamount = $product['limitamount'];
// $productstore->prostorelimitamount = $product['limitamount'];
// $productstore->createddate = date("Y-m-d H:i:s");
// $productstore->conditions = 0;
// R::store($productstore);
// }
// }
// }
//
// header("location:productstore.php");
// } catch (Exception $e) {
// header("location:productstore.php");
// // header("location:?do=error");
// }
} else if ($do == 'add') {
$smarty->display("header.html");
$smarty->display("productstoreview/add.html");
$smarty->display("footer.html");
} else if ($do == 'save') {
try {
$allproducts = filter_input(INPUT_POST, 'allproducts');
for ($i = 1; $i <= $allproducts; $i++) {
$productid = filter_input(INPUT_POST, 'productid' . $i);
$limitamount = filter_input(INPUT_POST, 'limitamount' . $i);
$productcatid = filter_input(INPUT_POST, 'productcatid' . $i);
$allstores = filter_input(INPUT_POST, 'allstores' . $i);
for ($i2 = 1; $i2 <= $allstores; $i2++) {
$storeid = filter_input(INPUT_POST, 'storeid' . $i . $i2);
$prostorelimitamount = (float) filter_input(INPUT_POST, 'prostorelimitamount' . $i . $i2);
if ($prostorelimitamount < 1) {
continue;
}
$productstore = R::dispense('productstore');
$productstore->productid = $productid;
$productstore->storeid = $storeid;
$productstore->productcatid = $productcatid;
$productstore->limitamount = $limitamount;
$productstore->prostorelimitamount = $prostorelimitamount;
$productstore->createddate = date("Y-m-d H:i:s");
$productstore->conditions = 0;
R::store($productstore);
}
}
header("location:productstore.php");
} catch (Exception $e) {
header("location:productstore.php?do=add");
}
} else if ($do == 'allproducts') {
$id = filter_input(INPUT_POST, 'id');
$productcatchild = productcatchild($id);
$products = R::getAll("SELECT product.* FROM `product` WHERE product.conditions = 0 and product.productCatId in ($productcatchild) ");
$allproducts = array();
foreach ($products as $product) {
$stores = R::getAll("SELECT * FROM `store` WHERE conditions = 0");
$productone = array('productid' => $product['productId'], 'productname' => $product['productName'], 'limitamount' => $product['limitamount'], 'productcatid' => $product['productCatId']);
$productone['stores'] = array();
foreach ($stores as $store) {
$productstoreone = R::findOne("productstore", 'productid = ? and storeid = ? and productstore.conditions = 0', [$product['productId'], $store['storeId']]);
if ($productstoreone) {
$store['productstoreid'] = $productstoreone->id;
$store['prostorelimitamount'] = $productstoreone->prostorelimitamount;
} else {
$productstore = R::dispense('productstore');
$productstore->productid = $product['productId'];
$productstore->storeid = $store['storeId'];
$productstore->productcatid = $product['productCatId'];
$productstore->limitamount = $product['limitamount'];
$productstore->prostorelimitamount = $product['limitamount'];
$productstore->createddate = date("Y-m-d H:i:s");
$productstore->conditions = 0;
$productstoreid = R::store($productstore);
$store['productstoreid'] = $productstoreid;
$store['prostorelimitamount'] = $product['limitamount'];
}
array_push($productone['stores'], $store);
}
if ($productone['stores']) {
array_push($allproducts, $productone);
}
}
$smarty->assign('allproducts', $allproducts);
$allstores = R::count("store", ' store.conditions = 0');
$smarty->assign('allstores', $allstores);
$smarty->display("productstoreview/allproducts.html");
} else if ($do == 'edit') {
$id = filter_input(INPUT_GET, 'id');
$productstore = R::getRow("SELECT productstore.*, storeName, productCatName, productName FROM `productstore`
LEFT JOIN product ON productstore.productid = product.productId
LEFT JOIN productcat ON productstore.productcatid = productcat.productCatId
LEFT JOIN store ON productstore.storeid = store.storeId WHERE productstore.id = ?", [$id]);
$smarty->assign('productstore', $productstore);
$smarty->display("header.html");
$smarty->display("productstoreview/edit.html");
$smarty->display("footer.html");
} else if ($do == 'update') {
$id = filter_input(INPUT_POST, 'id');
$prostorelimitamount = filter_input(INPUT_POST, 'prostorelimitamount');
try {
$productstore = R::load('productstore', $id);
$productstore->prostorelimitamount = $prostorelimitamount;
R::store($productstore);
header("location:productstore.php");
} catch (Exception $e) {
header("location:productstore.php?do=edit&id=" . $id);
}
} else if ($do == 'updateAjax') {
$id = (int) filter_input(INPUT_POST, 'id');
$prostorelimitamount = (float) filter_input(INPUT_POST, 'val');
try {
$productstore = R::load('productstore', $id);
$productstore->prostorelimitamount = $prostorelimitamount;
R::store($productstore);
echo 1;
} catch (Exception $e) {
echo -1;
}
} else if ($do == 'prostorelimitamounts') {
$id = filter_input(INPUT_POST, 'id');
$prostorelimitamount = filter_input(INPUT_POST, 'prostorelimitamount');
R::exec("UPDATE productstore SET prostorelimitamount = $prostorelimitamount WHERE id = $id ");
} else if ($do == 'selectproductcat') {
selectproductcat();
} else if ($do == 'selectproduct') {
selectproduct();
} else if ($do == 'selectstore') {
selectstore();
} else if ($do == 'showajax') {
showajax();
} else if ($do == 'removeproductstore') {
removeproductstore();
} elseif ($do == "productBelowLimitInStoreAjax") {
//edited copy of showajax() in php,js,html
productBelowLimitInStoreAjax();
}
function selectproductcat() {
$row_array = array();
$return_arr = array();
$searchTerm = $_POST['searchTerm'];
$productsData = R::getAll("SELECT productCatId, productCatName
FROM productcat
WHERE productcat.conditions = 0 and productCatName LIKE '%" . $searchTerm . "%' limit 50");
foreach ($productsData as $pro) {
$row_array['id'] = $pro['productCatId'];
$row_array['text'] = $pro['productCatName'];
array_push($return_arr, $row_array);
}
echo json_encode($return_arr);
}
function selectproduct() {
$row_array = array();
$return_arr = array();
$searchTerm = $_POST['searchTerm'];
$productcatid = $_POST['productcat'];
$productcatchild = productcatchild($productcatid);
$searchs = '';
if ($productcatchild) {
$searchs .= " and product.productCatId in ($productcatchild)";
}
$productsData = R::getAll("SELECT productId, productName
FROM product
WHERE product.conditions = 0 and productName LIKE '%" . $searchTerm . "%' $searchs limit 50");
foreach ($productsData as $pro) {
$row_array['id'] = $pro['productId'];
$row_array['text'] = $pro['productName'];
array_push($return_arr, $row_array);
}
echo json_encode($return_arr);
}
function productcatchild($productcatid) {
$allproductcatid = $productcatid;
$productscatData = R::getAll("SELECT productCatId FROM productcat WHERE productcat.conditions = 0 and productCatParent = $productcatid");
foreach ($productscatData as $cat) {
$allproductcatid .= ',' . productcatchild($cat['productCatId']);
}
return $allproductcatid;
}
function selectstore() {
$row_array = array();
$return_arr = array();
$searchTerm = $_POST['searchTerm'];
$productsData = R::getAll("SELECT storeId, storeName
FROM store
WHERE store.conditions = 0 and storeName LIKE '%" . $searchTerm . "%' limit 50");
foreach ($productsData as $pro) {
$row_array['id'] = $pro['storeId'];
$row_array['text'] = $pro['storeName'];
array_push($return_arr, $row_array);
}
echo json_encode($return_arr);
}
function showajax() {
$columns = array('productstore.id', 'productName', 'productCatName', 'storeName', 'productstore.limitamount', 'prostorelimitamount', 'createddate', 'productstore.id', 'productstore.conditions');
$start_date = filter_input(INPUT_POST, 'start_date');
$end_date = filter_input(INPUT_POST, 'end_date');
$data1 = filter_input(INPUT_POST, 'data1');
$data2 = filter_input(INPUT_POST, 'data2');
$data3 = filter_input(INPUT_POST, 'data3');
$searchQuery = " ";
if ($data1 != '') {
$searchQuery .= " and productstore.productcatid = " . $data1 . " ";
}
if ($data2 != '') {
$searchQuery .= " and productstore.productid = " . $data2 . " ";
}
if ($data3 != '') {
$searchQuery .= " and productstore.storeid = " . $data3 . " ";
}else if ($_SESSION['searchinonestore'] == 0) {
if ($_SESSION['storeids'] != 0) {
$searchQuery .= 'and productstore.storeid in (' . $_SESSION['storeids'] . ') ';
}
}else{
$searchQuery .= " and productstore.storeid = " . $_SESSION['storeid'] . " ";
}
if ($start_date != '' && $end_date != '') {
$searchQuery .= ' and productstore.createddate >= "' . $start_date . ' 00-00-00" and productstore.createddate <= "' . $end_date . ' 23-59-55" ';
}
if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
$searchQuery .= " and ( ";
for ($i = 1; $i < count($columns); $i++) {
$searchQuery .= $columns[$i] . " LIKE '%" . trim($_POST['search']['value']) . "%' OR ";
}
$searchQuery = substr_replace($searchQuery, "", -3);
$searchQuery .= ") ";
$searchQuery .= " productstore.id LIKE " % '.$_POST["search"]["value"].' % " )";
}
if (isset($_POST["order"])) {
$searchQuery .= 'ORDER BY ' . $columns[$_POST['order']['0']['column']] . ' ' . $_POST['order']['0']['dir'] . ' ';
} else {
$searchQuery .= "ORDER BY productstore.id DESC ";
}
$totals = R::count('productstore', 'LEFT JOIN product ON productstore.productid = product.productId
LEFT JOIN productcat ON productstore.productcatid = productcat.productCatId
LEFT JOIN store ON productstore.storeid = store.storeId
WHERE productstore.conditions = 0 ' . $searchQuery . ' ');
if (isset($_POST['start']) && $_POST['length'] != '-1') {
$searchQuery .= "LIMIT " . intval($_POST['start']) . ", " .
intval($_POST['length']);
}
$rResult = R::getAll("SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $columns)) . "
FROM productstore
LEFT JOIN product ON productstore.productid = product.productId
LEFT JOIN productcat ON productstore.productcatid = productcat.productCatId
LEFT JOIN store ON productstore.storeid = store.storeId
WHERE productstore.conditions = 0 $searchQuery");
$i = 0;
$output = array(
"draw" => intval(filter_input(INPUT_POST, 'draw')),
"recordsTotal" => $i,
"recordsFiltered" => intval($totals),
"data" => array()
);
foreach ($rResult as $row) {
$sub_array = array();
$sub_array[] = $row["id"];
$sub_array[] = $row["productName"];
$sub_array[] = $row["productCatName"];
$sub_array[] = $row["storeName"];
$sub_array[] = $row["limitamount"];
$sub_array[] = '<input type="text" class="form-control saveThis positivefloat" name="prostorelimitamount' . $i . '" id="prostorelimitamount' . $i . '" data-id="' . $row["id"] . '" value="' . $row["prostorelimitamount"] . '" tabindex="10">';
$sub_array[] = $row["createddate"];
if ($row["conditions"] == 0) {
$sub_array[] = '<a href="productstore.php?do=edit&id=' . $row["id"] . '" type="button" class="btn btn-default btn-lg editicon"></a>';
$sub_array[] = '<a href="javascript:;" data-id="' . $row["id"] . '" type="button" class="btn btn-default btn-lg removeicon removeproductstore" ></a>';
} else {
$sub_array[] = 'محذوف ';
$sub_array[] = 'محذوف ';
}
$i++;
$output['data'][] = $sub_array;
}
// $sub_array2 = array();
// $sub_array2[] = "";
// $sub_array2[] = "";
// $sub_array2[] = "";
// $sub_array2[] = (int)$buybillfinalbilltotal;
// $sub_array2[] = "";
// $sub_array2[] = date("Y-m-d H:i:s");
// $sub_array2[] = (int) $sellbillfinalbilltotal;
// $sub_array2[] = "";
// $sub_array2[] = (int) $finaltotals;
// $sub_array2[] = "";
// $sub_array2[] = "";
// $output['data'][] = $sub_array2;
echo json_encode($output, JSON_UNESCAPED_SLASHES);
}
function productBelowLimitInStoreAjax() {
$columns = array('productstore.id', 'productName', 'productCatName', 'storeName', 'productstore.limitamount', 'prostorelimitamount', 'productquantity');
$userStore = (int) $_SESSION['userstore'];
$searchQuery = " and productstore.storeid = $userStore and storedetail.productquantity < productstore.prostorelimitamount ";
if (isset($_POST['search']['value']) && $_POST['search']['value'] != "") {
$searchQuery .= " and ( ";
for ($i = 1; $i < count($columns); $i++) {
$searchQuery .= $columns[$i] . " LIKE '%" . trim($_POST['search']['value']) . "%' OR ";
}
$searchQuery = substr_replace($searchQuery, "", -3);
$searchQuery .= ") ";
$searchQuery .= " productstore.id LIKE " % '.$_POST["search"]["value"].' % " )";
}
if (isset($_POST["order"])) {
$searchQuery .= 'ORDER BY ' . $columns[$_POST['order']['0']['column']] . ' ' . $_POST['order']['0']['dir'] . ' ';
} else {
$searchQuery .= "ORDER BY productstore.id DESC ";
}
$totals = R::count('productstore', 'JOIN product ON productstore.productid = product.productId
JOIN productcat ON productstore.productcatid = productcat.productCatId
JOIN storedetail ON (storedetail.storeid = productstore.storeid and storedetail.productid=productstore.productid)
JOIN store ON productstore.storeid = store.storeId
WHERE productstore.conditions = 0 and product.conditions=0 and product.isService=0 ' . $searchQuery . ' ');
if (isset($_POST['start']) && $_POST['length'] != '-1') {
$searchQuery .= "LIMIT " . intval($_POST['start']) . ", " .
intval($_POST['length']);
}
$rResult = R::getAll("SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $columns)) . "
FROM productstore
JOIN product ON productstore.productid = product.productId
JOIN productcat ON productstore.productcatid = productcat.productCatId
JOIN storedetail ON (storedetail.storeid = productstore.storeid and storedetail.productid=productstore.productid)
JOIN store ON productstore.storeid = store.storeId
WHERE productstore.conditions = 0 and product.conditions=0 and product.isService=0 $searchQuery");
$i = 1;
$output = array(
"draw" => intval(filter_input(INPUT_POST, 'draw')),
"recordsTotal" => $i,
"recordsFiltered" => intval($totals),
"data" => array()
);
foreach ($rResult as $row) {
$sub_array = array();
$sub_array[] = $i; //$row["id"];
$sub_array[] = $row["productName"];
$sub_array[] = $row["productCatName"];
$sub_array[] = $row["storeName"];
//$sub_array[] = $row["limitamount"];
$sub_array[] = $row["prostorelimitamount"];
$sub_array[] = (float) $row["productquantity"];
$i++;
$output['data'][] = $sub_array;
}
echo json_encode($output, JSON_UNESCAPED_SLASHES);
}
function removeproductstore() {
$id = filter_input(INPUT_POST, 'id');
$productstore = R::load('productstore', $id);
$productstore->conditions = 1;
try {
R::store($productstore);
echo 1;
} catch (Exception $e) {
echo 0;
}
}
function addAllOrMissingRows() {
$dbName = R::getCell("SELECT DATABASE()");
$rowsNo = R::getCell("SELECT table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" . $dbName . "' and TABLE_NAME = 'productstore'");
if ($rowsNo > 0) {
//1- make sure unique key exist drop ,recreate it
try {
$res = R::exec("ALTER TABLE productstore DROP INDEX `productstoreunique`");
} catch (Exception $exc) {
}
}
try {
$res = R::exec("ALTER TABLE productstore ADD UNIQUE `productstoreunique` ( `productid`, `storeid`)");
} catch (Exception $exc) {
}
//2- insert what is missing
$res = R::exec("INSERT IGNORE INTO productstore (productid, storeid, productcatid,limitamount,prostorelimitamount,createddate,conditions)
SELECT product.productId, storedetail.storeid, product.productCatId, product.limitamount, product.limitamount,'" . date('Y-m-d H:i:s') . "',0
from storedetail
join product on (product.productId = storedetail.productid and product.conditions=0)");
}
?>