File: D:/HostingSpaces/SBogers10/deensekroon.komma-mediadesign.nl/wwwroot/admin/php/stock.php
<?php
include_once($_SERVER['DOCUMENT_ROOT'] . '/admin/php/Stock/StockController.php');
/*
stock.php
Mike Ontwerpt 2012
www.mikeontwerpt.nl
*/
function initStock(){
$controller = new Admin\Stock\StockController();
$output = '';
if(isset($_POST['editSelectedStock'])){
updateStock();
}
else if(isset($_POST['sbm_sortfilter'])){
sortFilter();
}
else if(isset($_POST['downloadExcel'])){
//createExcelFile();
//createSimpleExcelStock();
// header('location: ./');
$controller->downloadCsv();
}
else{
$output .= showDashboard();
}
return $output;
}
function showDashboard(){
global $mysqli;
/* language */
$siteLabels = getLanguage();
$output = '';
/* title */
$output .= '<h1>'.$siteLabels['stock-title'].'</h1>';
$output .= '<div class="submenu">';
$output .= '<form action="" method="post">';
$output .= '<input type="submit" name="downloadExcel" value="Download Excel Bestand" class="btn176 btn176b proceed"/>';
$output .= '</form>';
$output .= '</div>';
//start with form for checkboxes
$output .= '<form action="" name="chkForm" method="post">';
/* buttons */
/*$output .= '<div class="submenu">';
//buttons
$output .= '<a href="/admin/products/add-product/"><span class="btn176 btn176a proceed">'.$siteLabels['products-addproduct'].'</span></a>';
$output .= '</div>';*/
/* content */
/* SORT & FILTER */
# sort & filter
$i = 0;
$sortarr[$i]['val'] = 'stockCode';
$sortarr[$i]['label'] = 'Stock code';
$i++;
$sortarr[$i]['val'] = 'titleNL';
$sortarr[$i]['label'] = 'Title';
$i++;
$sortby = '';
$sortdir = '';
$filterquery = '';
if(defined('URL_SUB') && substr(URL_SUB,0,6) == 'sortby'){
# check sort
$sortby = substr(URL_SUB,9);
$sortdir = substr(URL_SUB, 7,1);
$sortdir == 'a' ? $sortdir = 'ASC' : $sortdir = 'DESC';
#check filter
$filterby = substr(URL_SUB2,9);
if(!empty($filterby)){
$filterstr = str_replace('-',' ',$filterby);
$filterquery = ' AND (';
$filterquery .= 'p.stockCode LIKE "%'.$filterstr.'%" OR ';
$filterquery .= 'p.titleNL LIKE "%'.$filterstr.'%"';
$filterquery .= ')';
}
}
## output form
$output .= '<div class="single-column sortfilter">';
# Filter input
$output .= '<strong>Filter</strong><br />';
if(!isset($filterstr)) $filterstr = '';
$output .= '<input type="text" name="filter-by" value="'.$filterstr.'" class="txt" />';
$output .= '</div>';
$output .= '<div class="single-column sortfilter">';
# Sort input
$output .= '<strong>Sort by</strong><br />';
$output .= '<select name="sort-by">';
foreach($sortarr as $key => $value){
$output .= '<option value="'.$value['val'].'" ';
if(isset($sortby) && $value['val'] == $sortby) $output .= 'selected';
$output .= '>'.$value['label'].'</option>';
}
$output .= '</select>';
$output .= '</div>';
$output .= '<div class="single-column sortfilter">';
$output .= '<br /><input type="radio" name="orderdir" value="A" ';
if (empty($sortdir) || $sortdir == 'ASC') $output .= 'checked';
$output .= '> Ascending ';
$output .= '<input type="radio" name="orderdir" value="D" ';
if(isset($sortdir) && $sortdir == 'DESC') $output .= 'checked';
$output .= '> Descending';
$output .= '</div>';
$output .= '<div class="single-column sortfilter">';
# submit button
$output .= '<br /><input type="submit" name="sbm_sortfilter" value="go" class="btn176 proceed no-margin"/>';
$output .= '</div>';
$output .= '<div class="clear"></div><br /><br />';
//EDIT
$output .= '<div class="itemlist-row itemlist-heading">';
$output .= '<div class="itemlist-triple-column"> </div>';
$output .= '<div class="itemlist-doublehalf-column"> </div>';
$output .= '<div class="itemlist-single-column">in stock</div>';
$output .= '<div class="itemlist-single-column"> </div>';
$output .= '<div class="itemlist-single-column"> </div>';
$output .= '</div>';
$output .= '<div class="itemlist-row">';
$output .= '<div class="itemlist-triple-column"><h2>edit stock</h2></div>';
$output .= '<div class="itemlist-doublehalf-column"> </div>';
$output .= '<div class="itemlist-single-column"><input type="text" name="instock" value="" class="itemlist-txt" /></div>';
$output .= '<div class="itemlist-single-column"><input type="submit" name="editSelectedStock" value="opslaan" class="btn80 btn80"/></div>';
$output .= '<div class="itemlist-single-column"> </div>';
$output .= '</div>';
/* top row */
$output .= '<div class="itemlist-row itemlist-heading">';
$output .= '<div class="itemlist-triple-column">stockcode</div>';
$output .= '<div class="itemlist-doublehalf-column"><input type="checkbox" name="checkCtrl" value="checkAll" onClick="check_all(this);" class="itemlist-cbox">title</div>';
$output .= '<div class="itemlist-single-column">in stock</div>';
$output .= '<div class="itemlist-single-column">available</div>';
$output .= '<div class="itemlist-single-column">in order</div>';
$output .= '</div>';
/* get info */
$query = 'SELECT DISTINCT p.id, p.stockCode, p.titleNL, p.catId, p.published
FROM shop_stock as st, product_products AS p, content_status AS s
WHERE s.itemId = p.id
AND s.active = 1
AND st.productId = p.id
AND NOT p.stockCode = "null"
AND s.linkname = "product_products"';
#sort & filter
if(!empty($filterquery)) $query .= $filterquery;
if(!empty($sortby) && $sortby == 'titleNL'){ $query .= ' ORDER BY p.'.$sortby.' '.$sortdir; }
else if(!empty($sortby)){ $query .= ' ORDER BY p.'.$sortby.' '.$sortdir; }
else{ $query .= ' ORDER BY p.stockCode ASC'; }
if($result = $mysqli->query($query)){
while($record = $result->fetch_assoc()){
//create productid
$stockCode = $record['stockCode'];
$productId = $record['id'];
$category = $record['catId'];
$published = $record['published'];
// Get Stock
$stockQuery = 'SELECT inStock, size FROM shop_stock WHERE productId = '.$productId . ' ORDER BY id ASC';
$stockResult = $mysqli->query($stockQuery);
//if($stockResult->num_rows > 0){
while($stockRecord = $stockResult->fetch_assoc())
{
//redefine variables
$inStock = $stockRecord['inStock'];
$size = $stockRecord['size'];
$displayStockCode = $stockCode;
if( categoryHasSize($category) )
{
// Add size to stockCode
$displayStockCode .= '-' . $size;
}
//get IN ORDER
$inOrderQuery = 'SELECT op.numProducts
FROM shop_order_products AS op, shop_order_info AS oi
WHERE op.orderId = oi.id
AND op.productId = '.$productId;
if( ! empty($size)) $inOrderQuery .= ' AND op.size = "' . $size . '"';
$inOrderQuery .= ' AND (oi.orderStatus = 0
OR oi.orderStatus = 2)';
$numInOrder = 0;
if($inOrderResult = $mysqli->query($inOrderQuery)){
while($inOrderRecord = $inOrderResult->fetch_assoc()){
$numInOrder += $inOrderRecord['numProducts'];
}
}
else{
$output .= $mysqli->error;
}
// get available
$available = $inStock - $numInOrder;
//title
$titleNL = fromDatabase($record['titleNL']);
$output .= '<div class="itemlist-row">';
/* stock code */
$output .= '<div class="itemlist-triple-column">';
$published == 1 ? $output .= $displayStockCode : $output .= '<span class="grey">'. $displayStockCode .'</span>';
$output .= '</div>';
/* title */
$output .= '<div class="itemlist-doublehalf-column">
<input type="checkbox" name="option[]" id="option'.$productId.'" value="'.$productId;
// Size in option?
if(categoryHasSize($category)) $output .= '_' . $size;
$output .= '" class="itemlist-cbox" />
<label for="option'.$productId.'">';
$published == 1 ? $output .= $titleNL : $output .= '<span class="grey">'. $titleNL .'</span>';
$output .= '</label>
</div>';
/* instock */
$output .= '<div class="itemlist-single-column">'.$inStock.'</div>';
/* available */
$output .= '<div class="itemlist-single-column">'.$available.'</div>';
/* inorder*/
$output .= '<div class="itemlist-single-column">'.$numInOrder.'</div>';
$output .= '<div class="clear"></div>';
$output .= '</div>';
}
}
}
else{
$output .= $mysqli->error;
}
$output .= '</form>';
return $output;
}
function updateStock(){
/* access the global sql object */
global $mysqli;
/* language */
$siteLabels = getLanguage();
if(isset($_POST['option'])){
$queries = array();
//validate fields
if(!empty($_POST['instock']) || $_POST['instock'] == 0){
//prepare queries in stock
$inStock = $_POST['instock'];
if(is_numeric($inStock)){
//get available
foreach($_POST['option'] as $productId){
// Check for sizes
$temp = explode('_',$productId);
if(count($temp) > 1)
{
$productId = $temp[0];
$size = $temp[1];
}
$query1 = 'SELECT inStock FROM shop_stock WHERE productId = '.$productId;
if(isset($size)) $query1 .= ' AND size = "'. $size .'"';
$query1 .= ' LIMIT 1';
if($result = $mysqli->query($query1)){
if($result->num_rows > 0){
$query2 = 'UPDATE shop_stock SET inStock = "'.$inStock.'" WHERE productId ='.$productId;
if(isset($size)) $query2 .= ' AND size = "'. $size .'"';
$query2 .= ' LIMIT 1';
$queries[] = $query2;
}
else{
$queries[] = 'INSERT INTO shop_stock (productId, inStock) VALUES("'.$productId.'","'.$inStock.'")';
}
}
$queries[] = ('UPDATE product_products SET updated_at = "' . \Carbon\Carbon::now() . '" WHERE id = ' . $productId);
}
}
}
// Set autocommit to FALSE
$mysqli->autocommit(FALSE);
foreach($queries as $query)
{
if(!$mysqli->query($query))
{
$errors[] = $siteLabels['fm-somethingwentwrong'].$mysqli->error;
}
}
// ROLLBACK of COMMIT
if(isset($errors))
{
$mysqli->rollback();
displayErrors($errors);
}
else
{
$mysqli->commit();
$_SESSION['feed-msg'] = '<span class="fm-suc"><span class="fm-suc-v">√</span>stock updated!</span>';
}
}
header('location: ./');
}
function sortFilter(){
//get sort & filter
$sort = $_POST['sort-by'];
$filter = linkname($_POST['filter-by']);
$direction = linkname($_POST['orderdir']);
header('location: /admin/stock/sortby-'.$direction.'-'.$sort.'/filterby-'.$filter.'/');
}
?>