File: /var/www/html/inventory.breadsecret.com/class/Controller/stock.php
<?php
namespace Controller;
use Responses\Message, Responses\Action, Responses\Data;
use Database\Sql, Database\Listable;
use Pages\Page, Pages\ListPage, Pages\FormPage;
use Routing\Route;
use Utility\WebSystem, Utility\Excel, Utility\Email;
use Controller\documentHelper, Controller\formLayout;
class stock implements Listable {
private $stmStatus = null;
public static function find($id, $fetchMode=\PDO::FETCH_OBJ) {
$sql = Sql::select("item_stock")->where(['id', '=', $id]);
$stm = $sql->prepare();
$stm->execute();
$obj = $stm->fetch($fetchMode);
if ($obj === false) return null;
return $obj;
}
public static function findDetail($id, $fetchMode=\PDO::FETCH_OBJ) {
$sql = Sql::select("item_stock_detail")->where(['id', '=', $id]);
$stm = $sql->prepare();
$stm->execute();
$obj = $stm->fetch($fetchMode);
if ($obj === false) return null;
return $obj;
}
public static function findDetailList($id, $fetchMode=\PDO::FETCH_OBJ) {
$sql = Sql::select("item_stock_detail")->where(['itemStockID', '=', $id])->where(['qty', '>', 0]);
$stm = $sql->prepare();
$stm->execute();
return $stm;
}
public static function findBatchInArea($request, $fetchMode=\PDO::FETCH_OBJ) {
$batchList = self::findDetailList($request->get->id);
$data = array();
foreach($batchList as $batch) {
$data[]=["id"=>$batch['id'], "batch"=>$batch['batchNo'], "qty"=>$batch['qty']];
}
return new Data(['success'=>true, 'message'=>json_encode($data)]);
}
public function extraProcess($listObj) {
if (is_null($this->stmStatus))
$this->stmStatus = Sql::select('status')->where(['id', '=', "?"])->prepare();
$this->stmStatus->execute([$listObj->status]);
$objStatus = $this->stmStatus->fetch();
$listObj->statusName = $objStatus['name'];
return $listObj;
}
public function list($request) {
if (!user::checklogin()) return new Action('redirect', WebSystem::path(Route::getRouteByName('page.login')->path(), false, false));
$obj = null;
return new FormPage('stock/list', $obj);
}
public function delete($request) {
if (!user::checklogin()) return new Data(['success'=>false, 'message'=>L('login.signInMessage'), 'note'=>'signIn']);
if (!isset($request->get->id) || empty($request->get->id))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyID')]);
$sql = Sql::delete('stock')->where(['id', '=', $request->get->id]);
if ($sql->prepare()->execute()) {
return new Data(['success'=>true, 'message'=>L('info.stockDeleted')]);
} else {
return new Data(['success'=>false, 'message'=>L('error.stockDeleteFailed')]);
}
}
public function stockInForm($request) {
if (!user::checklogin()) return new Data(['success'=>false, 'message'=>L('login.signInMessage'), 'note'=>'signIn']);
$formName = "form-addStockIn";
$content = "<form id='".$formName."' class='' autocomplete='off'>";
$content .= "<div class='row'><p class='col-md-12 col-lg-12 text-primary' id='notice'>".L('info.stockAddHelperMessage')."</p></div>";
$content .= "<div class='row'>";
$content .= formLayout::rowInputNew(L('stock.date'),'transactionDate', 'transactionDate', 'datetime-local', 6, [], ['required'], date("Y-m-d H:i:s", time()+8*3600));
$content .= formLayout::rowInputNew(L('stock.batchNo'),'batchNo', 'batchNo', 'text', 6, [], ['required']);
$option = [""=>""];
$stm = Sql::select('item')->where(['status', '=', 1])->prepare();
$stm->execute();
foreach ($stm as $opt) {
$option[$opt['id']] = $opt['name'];
}
$content .= formLayout::rowSelectNew(L('stock.item'), 'itemID', 'itemID', $option, 6, [], ['required']);
$content .= formLayout::rowInputNew(L('stock.itemType'),'itemType', 'itemType', 'text', 6, [], ['readonly']);
$content .= formLayout::rowInputNew(L('stock.supplier'),'supplier', 'supplier', 'text', 6, [], ['readonly']);
$content .= formLayout::rowInputNew(L('stock.unit'),'unit', 'unit', 'text', 6, [], ['readonly']);
$option = [""=>""];
$stm = Sql::select('storage_location')->where(['status', '=', 1])->prepare();
$stm->execute();
foreach ($stm as $opt) {
$option[$opt['id']] = $opt['name'];
}
$content .= formLayout::rowSelectNew(L('stock.storageLocation'), 'storageLocationID', 'storageLocationID', $option, 6, [], ['required']);
$content .= formLayout::rowSelectNew(L('stock.storageLocationArea'), 'storageLocationAreaID', 'storageLocationAreaID', [], 6, [], ['required']);
$content .= formLayout::rowInputNew(L('stock.inQty'),'qty', 'qty', 'text', 6, [], ['required']);
$content .= formLayout::rowInputNew(L('stock.purchaseCost'),'purchaseCost', 'purchaseCost', 'text', 6, [], ['required']);
$content .= formLayout::rowTextAreaNew(L('stock.remark'), 'remark', 'remark', 12, [], []);
$content .= "</div>";
$content .= "</form>";
return new Data(['success'=>true, 'message'=>$content]);
}
public function stockOutForm($request) {
if (!user::checklogin()) return new Data(['success'=>false, 'message'=>L('login.signInMessage'), 'note'=>'signIn']);
$formName = "form-addStockOut";
$content = "<form id='".$formName."' class='' autocomplete='off'>";
$content .= "<div class='row'><p class='col-md-12 col-lg-12 text-primary' id='notice'>".L('info.stockAddHelperMessage')."</p></div>";
$content .= "<div class='row'>";
$content .= formLayout::rowInputNew(L('stock.date'),'transactionDate', 'transactionDate', 'datetime-local', 6, [], ['required'], date("Y-m-d H:i:s", time()+8*3600));
$option = [""=>""];
$stm = Sql::select('item')->where(['status', '=', 1])->where(['qty', '>', 0])->prepare();
$stm->execute();
foreach ($stm as $opt) {
$option[$opt['id']] = $opt['name'];
}
$content .= formLayout::rowSelectNew(L('stock.item'), 'itemID', 'itemID', $option, 6, [], ['required']);
$content .= formLayout::rowInputNew(L('stock.itemType'),'itemType', 'itemType', 'text', 6, [], ['readonly']);
$content .= formLayout::rowInputNew(L('stock.supplier'),'supplier', 'supplier', 'text', 6, [], ['readonly']);
$content .= formLayout::rowInputNew(L('stock.unit'),'unit', 'unit', 'text', 6, [], ['readonly']);
$option = [""=>""];
$stm = Sql::select('storage_location')->where(['status', '=', 1])->prepare();
$stm->execute();
foreach ($stm as $opt) {
$option[$opt['id']] = $opt['name'];
}
$content .= formLayout::rowSelectNew(L('stock.storageLocationArea'), 'storageLocationAreaID', 'storageLocationAreaID', [], 6, [], ['required']);
$content .= formLayout::rowSelectNew(L('stock.batchNo'), 'itemStockDetailID', 'itemStockDetailID', [], 6, [], ['required']);
$content .= formLayout::rowInputNew(L('stock.outQty'),'qty', 'qty', 'text', 6, [], ['required']);
$content .= formLayout::rowTextAreaNew(L('stock.remark'), 'remark', 'remark', 12, [], []);
$content .= "</div>";
return new Data(['success'=>true, 'message'=>$content]);
}
public function stockTransferForm($request) {
if (!user::checklogin()) return new Data(['success'=>false, 'message'=>L('login.signInMessage'), 'note'=>'signIn']);
$formName = "form-addStockTransfer";
$content = "<form id='".$formName."' class='' autocomplete='off'>";
$content .= "<div class='row'><p class='col-md-12 col-lg-12 text-primary' id='notice'>".L('info.stockAddHelperMessage')."</p></div>";
$content .= "<div class='row'>";
$content .= formLayout::rowInputNew(L('stock.date'),'transactionDate', 'transactionDate', 'datetime-local', 6, [], ['required'], date("Y-m-d H:i:s", time()+8*3600));
$option = [""=>""];
$stm = Sql::select('item')->where(['status', '=', 1])->where(['qty', '>', 0])->prepare();
$stm->execute();
foreach ($stm as $opt) {
$option[$opt['id']] = $opt['name'];
}
$content .= formLayout::rowSelectNew(L('stock.item'), 'itemID', 'itemID', $option, 6, [], ['required']);
$content .= formLayout::rowInputNew(L('stock.itemType'),'itemType', 'itemType', 'text', 6, [], ['readonly']);
$content .= formLayout::rowInputNew(L('stock.supplier'),'supplier', 'supplier', 'text', 6, [], ['readonly']);
$content .= formLayout::rowInputNew(L('stock.unit'),'unit', 'unit', 'text', 6, [], ['readonly']);
$option = [""=>""];
$stm = Sql::select('storage_location')->where(['status', '=', 1])->prepare();
$stm->execute();
foreach ($stm as $opt) {
$option[$opt['id']] = $opt['name'];
}
$content .= formLayout::rowSelectNew(L('stock.fromArea'), 'storageLocationAreaID', 'storageLocationAreaID', [], 6, [], ['required']);
$content .= formLayout::rowSelectNew(L('stock.batchNo'), 'itemStockDetailID', 'itemStockDetailID', [], 6, [], ['required']);
$content .= formLayout::rowInputNew(L('stock.transferQty'),'qty', 'qty', 'text', 6, [], ['required']);
$option = [""=>""];
$stm = Sql::select('storage_location')->where(['status', '=', 1])->prepare();
$stm->execute();
foreach ($stm as $opt) {
$option[$opt['id']] = $opt['name'];
}
$content .= formLayout::rowSelectNew(L('stock.toLocation'), 'newStorageLocationID', 'newStorageLocationID', $option, 6, [], ['required']);
$content .= formLayout::rowSelectNew(L('stock.toArea'), 'newStorageLocationAreaID', 'newStorageLocationAreaID', [], 6, [], ['required']);
$content .= formLayout::rowTextAreaNew(L('stock.remark'), 'remark', 'remark', 12, [], []);
$content .= "</div>";
return new Data(['success'=>true, 'message'=>$content]);
}
public static function detail($request) {
if (!user::checklogin()) return new Data(['success'=>false, 'message'=>L('login.signInMessage'), 'note'=>'signIn']);
if (!isset($request->get->id) || empty($request->get->id))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyID')]);
$stockObj = self::find($request->get->id);
if(is_null($stockObj)) return new Data(['success'=>false, 'message'=>L('error.stockNotFound')]);
$itemObj = item::find($stockObj->itemID);
$itemTypeObj = itemType::find($itemObj->itemTypeID);
$supplierObj = supplier::find($itemObj->supplierID);
$areaObj = storageLocationArea::find($stockObj->storageLocationAreaID);
$locationObj = storageLocation::find($areaObj->storageLocationID);
$unitObj = unit::find($itemObj->unitID);
$content = "<div class='row'>";
/*
$content .= formLayout::rowDisplayLineNew(L('stock.item'),$itemObj->name);
$content .= formLayout::rowDisplayLineNew(L('stock.itemType'),$itemTypeObj->name);
$content .= formLayout::rowDisplayLineNew(L('stock.supplier'),$supplierObj->name);
$content .= formLayout::rowDisplayLineNew(L('stock.storageLocation'),$areaObj->name);
$content .= formLayout::rowDisplayLineNew(L('stock.storageLocationArea'),$locationObj->name);
$content .= formLayout::rowDisplayLineNew(L('stock.qty'),$stockObj->qty);
$content .= formLayout::rowDisplayLineNew(L('stock.unit'),$unitObj->name);
$content .= formLayout::rowDisplayLineNew(L('stock.value'),$stockObj->totalValue);
*/
$content .= formLayout::rowInputNew(L('stock.item'),'', '', 'text', 4, [], ['disabled'], $itemObj->name);
$content .= formLayout::rowInputNew(L('stock.itemType'),'', '', 'text', 4, [], ['disabled'], $itemTypeObj->name);
$content .= formLayout::rowInputNew(L('stock.supplier'),'', '', 'text', 4, [], ['disabled'], $supplierObj->name);
$content .= formLayout::rowInputNew(L('stock.storageLocation'),'', '', 'text', 4, [], ['disabled'], $areaObj->name);
$content .= formLayout::rowInputNew(L('stock.storageLocationArea'),'', '', 'text', 4, [], ['disabled'], $locationObj->name);
$content .= formLayout::rowInputNew(L('stock.qty'),'', '', 'text', 4, [], ['disabled'], $stockObj->qty);
$content .= formLayout::rowInputNew(L('stock.unit'),'', '', 'text', 4, [], ['disabled'], $unitObj->name);
$content .= formLayout::rowInputNew(L('stock.value'),'', '', 'text', 4, [], ['disabled'], $stockObj->totalValue);
$content .= "</div>";
$content .= formLayout::rowSeparatorLineNew(12);
$detailObj = self::findDetailList($request->get->id);
$content .= "<div class='table-responsive'><table class='table table-bordered mt-3'>";
$content .= "<thead class='thead-dark'>";
$content .= "<tr>";
$content .= "<th>".L('stock.batchNo')."</th>";
$content .= "<th>".L('stock.qty')."</th>";
$content .= "<th>".L('stock.purchaseUnitCost')."</th>";
$content .= "<th>".L('item.cost')."</th>";
$content .= "</tr>";
$content .= "</thead>";
$content .= "</tbody>";
foreach($detailObj as $details){
$content .= "<tr>";
$content .= "<td>".$details['batchNo']."</td>";
$content .= "<td>".$details['qty']."</td>";
$content .= "<td>".$details['purchaseUnitCost']."</td>";
$content .= "<td>".$details['qty']*$details['purchaseUnitCost']."</td>";
$content .= "</tr>";
}
$content .= "</tbody>";
$content .= "</table></div>";
return new Data(['success'=>true, 'message'=>$content]);
}
public function stockIn($request) {
if (!user::checklogin())
return new Data(['success'=>false, 'message'=>L('login.signInMessage'), 'field'=>'notice']);
$currentUserObj = unserialize($_SESSION['user']);
// form check
if (!isset($request->post->transactionDate) || empty($request->post->transactionDate))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyTransactionDate'), 'field'=>'transactionDate']);
if (!isset($request->post->batchNo) || empty($request->post->batchNo))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyBatchNo'), 'field'=>'batchNo']);
if (!isset($request->post->itemID) || empty($request->post->itemID))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyItem'), 'field'=>'itemID']);
if (!isset($request->post->storageLocationAreaID) || empty($request->post->storageLocationAreaID))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyStorageLocationArea'), 'field'=>'storageLocationAreaID']);
if (!isset($request->post->qty) || empty($request->post->qty))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyQty'), 'field'=>'qty']);
if (!isset($request->post->purchaseCost) || empty($request->post->purchaseCost))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyPurchaseCost'), 'field'=>'purchaseCost']);
$purchaseUnitCost = $request->post->purchaseCost/$request->post->qty;
$sql = Sql::insert('transaction')->setFieldValue([
'itemID' => "?",
'action' => "?",
'qty' => "?",
'purchaseUnitCost' => "?",
'batchNo' => "?",
'remark' => "?",
'fromStorageAreaID' => "?",
'toStorageAreaID' => "?",
'transactionDate' => "?",
'createBy' => $currentUserObj->id
]);
if ($sql->prepare()->execute([
strip_tags($request->post->itemID),
strip_tags("Stock-In"),
strip_tags($request->post->qty),
strip_tags($purchaseUnitCost),
strip_tags($request->post->batchNo),
strip_tags($request->post->remark),
strip_tags("0"),
strip_tags($request->post->storageLocationAreaID),
strip_tags($request->post->transactionDate)
])) {
$transactionID = db()->lastInsertId();
$stock_id = self::checkStockExist($request->post->itemID, $request->post->storageLocationAreaID);
if($stock_id==0){
$sql = Sql::insert('item_stock')->setFieldValue([
'itemID' => "?",
'storageLocationAreaID' => "?",
'qty' => "?",
'totalValue' => "?",
'modifyBy' => $currentUserObj->id
]);
if ($sql->prepare()->execute([
strip_tags($request->post->itemID),
strip_tags($request->post->storageLocationAreaID),
strip_tags("0"),
strip_tags("0")
])) {
$stock_id = db()->lastInsertId();
}
}
$stock_detail_id = self::checkStockDetailExist($stock_id, $request->post->batchNo);
if($stock_detail_id==0){
$sql = Sql::insert('item_stock_detail')->setFieldValue([
'itemStockID' => "?",
'batchNo' => "?",
'qty' => "?",
'purchaseUnitCost' => "?",
'transactionID' => "?"
]);
if ($sql->prepare()->execute([
strip_tags($stock_id),
strip_tags($request->post->batchNo),
strip_tags($request->post->qty),
strip_tags($purchaseUnitCost),
strip_tags($transactionID),
])) { $stock_detail_id = db()->lastInsertId(); }
} else {
$editFields = [];
$editValues = [];
$stockDetailObj = self::findDetail($stock_detail_id);
$editFields['qty'] = "?";
$editValues[] = $stockDetailObj->qty + $request->post->qty;
$editFields['transactionID'] = "?";
$editValues[] = $transactionID;
$sql = Sql::update('item_stock_detail')->setFieldValue($editFields)->where(['id', '=', $stock_detail_id]);
$sql->prepare()->execute($editValues);
}
self::updateItemStock($stock_id);
self::updateItem($request->post->itemID);
return new Data(['success'=>true, 'message'=>L('info.saved')]);
} else {
return new Data(['success'=>false, 'message'=>L('error.unableInsert'), 'field'=>'notice']);
}
}
public function stockOut($request) {
if (!user::checklogin())
return new Data(['success'=>false, 'message'=>L('login.signInMessage'), 'field'=>'notice']);
$currentUserObj = unserialize($_SESSION['user']);
// form check
if (!isset($request->post->transactionDate) || empty($request->post->transactionDate))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyTransactionDate'), 'field'=>'transactionDate']);
if (!isset($request->post->itemID) || empty($request->post->itemID))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyItem'), 'field'=>'itemID']);
if (!isset($request->post->storageLocationAreaID) || empty($request->post->storageLocationAreaID))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyStorageLocationArea'), 'field'=>'storageLocationAreaID']);
if (!isset($request->post->itemStockDetailID) || empty($request->post->itemStockDetailID))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyBatchNo'), 'field'=>'itemStockDetailID']);
if (!isset($request->post->qty) || empty($request->post->qty))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyQty'), 'field'=>'qty']);
$itemStockDetailObj = self::findDetail($request->post->itemStockDetailID);
if ($request->post->qty > $itemStockDetailObj->qty)
return new Data(['success'=>false, 'message'=>L('error.stockNotEnoughQty'), 'field'=>'qty']);
$sql = Sql::insert('transaction')->setFieldValue([
'itemID' => "?",
'action' => "?",
'qty' => "?",
'purchaseUnitCost' => "?",
'batchNo' => "?",
'remark' => "?",
'fromStorageAreaID' => "?",
'toStorageAreaID' => "?",
'transactionDate' => "?",
'createBy' => $currentUserObj->id
]);
if ($sql->prepare()->execute([
strip_tags($request->post->itemID),
strip_tags("Stock-Out"),
strip_tags($request->post->qty),
strip_tags($itemStockDetailObj->purchaseUnitCost),
strip_tags($itemStockDetailObj->batchNo),
strip_tags($request->post->remark),
strip_tags($request->post->storageLocationAreaID),
strip_tags("0"),
strip_tags($request->post->transactionDate)
])) {
$transactionID = db()->lastInsertId();
$stock_id = $itemStockDetailObj->itemStockID;
$editFields = [];
$editValues = [];
$editFields['qty'] = "?";
$editValues[] = $itemStockDetailObj->qty - $request->post->qty;
$editFields['transactionID'] = "?";
$editValues[] = $transactionID;
$sql = Sql::update('item_stock_detail')->setFieldValue($editFields)->where(['id', '=', $itemStockDetailObj->id]);
$sql->prepare()->execute($editValues);
self::updateItemStock($stock_id);
self::updateItem($request->post->itemID);
return new Data(['success'=>true, 'message'=>L('info.saved')]);
} else {
return new Data(['success'=>false, 'message'=>L('error.unableInsert'), 'field'=>'notice']);
}
}
public function stockTransfer($request) {
if (!user::checklogin())
return new Data(['success'=>false, 'message'=>L('login.signInMessage'), 'field'=>'notice']);
$currentUserObj = unserialize($_SESSION['user']);
if (!isset($request->post->transactionDate) || empty($request->post->transactionDate))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyTransactionDate'), 'field'=>'transactionDate']);
if (!isset($request->post->itemID) || empty($request->post->itemID))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyItem'), 'field'=>'itemID']);
if (!isset($request->post->storageLocationAreaID) || empty($request->post->storageLocationAreaID))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyStorageLocationArea'), 'field'=>'storageLocationAreaID']);
if (!isset($request->post->itemStockDetailID) || empty($request->post->itemStockDetailID))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyBatchNo'), 'field'=>'itemStockDetailID']);
if (!isset($request->post->qty) || empty($request->post->qty))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyQty'), 'field'=>'qty']);
if (!isset($request->post->newStorageLocationAreaID) || empty($request->post->newStorageLocationAreaID))
return new Data(['success'=>false, 'message'=>L('error.stockEmptyStorageLocationArea'), 'field'=>'newStorageLocationAreaID']);
$itemStockDetailObj = self::findDetail($request->post->itemStockDetailID);
$sql = Sql::insert('transaction')->setFieldValue([
'itemID' => "?",
'action' => "?",
'qty' => "?",
'purchaseUnitCost' => "?",
'batchNo' => "?",
'remark' => "?",
'fromStorageAreaID' => "?",
'toStorageAreaID' => "?",
'transactionDate' => "?",
'createBy' => $currentUserObj->id
]);
if ($sql->prepare()->execute([
strip_tags($request->post->itemID),
strip_tags("Stock-Transfer"),
strip_tags($request->post->qty),
strip_tags($itemStockDetailObj->purchaseUnitCost),
strip_tags($itemStockDetailObj->batchNo),
strip_tags($request->post->remark),
strip_tags($request->post->storageLocationAreaID),
strip_tags($request->post->newStorageLocationAreaID),
strip_tags($request->post->transactionDate)
])) {
// stock out action
$transactionID = db()->lastInsertId();
$stock_id = $itemStockDetailObj->itemStockID;
$editFields = [];
$editValues = [];
$editFields['qty'] = "?";
$editValues[] = $itemStockDetailObj->qty - $request->post->qty;
$editFields['transactionID'] = "?";
$editValues[] = $transactionID;
$sql = Sql::update('item_stock_detail')->setFieldValue($editFields)->where(['id', '=', $itemStockDetailObj->id]);
$sql->prepare()->execute($editValues);
self::updateItemStock($stock_id);
self::updateItem($request->post->itemID);
// stock in action
$new_stock_id = self::checkStockExist($request->post->itemID, $request->post->newStorageLocationAreaID);
if($new_stock_id==0){
$sql = Sql::insert('item_stock')->setFieldValue([
'itemID' => "?",
'storageLocationAreaID' => "?",
'qty' => "?",
'totalValue' => "?",
'modifyBy' => $currentUserObj->id
]);
if ($sql->prepare()->execute([
strip_tags($request->post->itemID),
strip_tags($request->post->newStorageLocationAreaID),
strip_tags("0"),
strip_tags("0")
])) {
$new_stock_id = db()->lastInsertId();
}
}
$new_stock_detail_id = self::checkStockDetailExist($new_stock_id, $itemStockDetailObj->batchNo);
if($new_stock_detail_id==0){
$sql = Sql::insert('item_stock_detail')->setFieldValue([
'itemStockID' => "?",
'batchNo' => "?",
'qty' => "?",
'purchaseUnitCost' => "?",
'transactionID' => "?"
]);
if ($sql->prepare()->execute([
strip_tags($new_stock_id),
strip_tags($itemStockDetailObj->batchNo),
strip_tags($request->post->qty),
strip_tags($itemStockDetailObj->purchaseUnitCost),
strip_tags($transactionID),
])) { $new_stock_detail_id = db()->lastInsertId(); }
} else {
$editFields = [];
$editValues = [];
$newStockDetailObj = self::findDetail($new_stock_detail_id);
$editFields['qty'] = "?";
$editValues[] = $newStockDetailObj->qty + $request->post->qty;
$editFields['transactionID'] = "?";
$editValues[] = $transactionID;
$sql = Sql::update('item_stock_detail')->setFieldValue($editFields)->where(['id', '=', $new_stock_detail_id]);
$sql->prepare()->execute($editValues);
}
self::updateItemStock($new_stock_id);
self::updateItem($request->post->itemID);
return new Data(['success'=>true, 'message'=>L('info.saved')]);
} else {
return new Data(['success'=>false, 'message'=>L('error.unableInsert'), 'field'=>'notice']);
}
}
public static function checkStockExist($itemID, $storageLocationAreaID){
$sql = Sql::select("item_stock")->where(['itemID', '=', $itemID])->where(['storageLocationAreaID', '=', $storageLocationAreaID]);
$stm = $sql->prepare();
$stm->execute();
$obj = $stm->fetch($fetchMode=\PDO::FETCH_OBJ);
if ($obj === false) return "0";
return $obj->id;
}
public static function checkStockDetailExist($itemStockID, $batchNo){
$sql = Sql::select("item_stock_detail")->where(['itemStockID', '=', $itemStockID])->where(['batchNo', '=', "?"]);
$stm = $sql->prepare();
$stm->execute([$batchNo]);
$obj = $stm->fetch($fetchMode=\PDO::FETCH_OBJ);
if ($obj === false) return "0";
return $obj->id;
}
public static function updateItemStock($stockID){
$qty = 0;
$values = 0;
$sql = Sql::select("item_stock_detail")->where(['itemStockID', '=', $stockID]);
$stm = $sql->prepare();
$stm->execute();
$obj = $stm->fetchAll($fetchMode=\PDO::FETCH_OBJ);
foreach($obj as $stockDetail) {
$qty += $stockDetail->qty;
$values += $stockDetail->qty * $stockDetail->purchaseUnitCost;
}
$sql = Sql::update('item_stock')->setFieldValue(["qty" => $qty, "totalValue" => $values])->where(['id', '=', $stockID]);
$sql->prepare()->execute();
}
public static function updateItem($itemID){
$qty = 0;
$values = 0;
$sql = Sql::select("item_stock")->where(['itemID', '=', $itemID]);
$stm = $sql->prepare();
$stm->execute();
$obj = $stm->fetchAll($fetchMode=\PDO::FETCH_OBJ);
foreach($obj as $itemStock) {
$sqld = Sql::select("item_stock_detail")->where(['itemStockID', '=', $itemStock->id]);
$stmd = $sqld->prepare();
$stmd->execute();
$objd = $stmd->fetchAll($fetchMode=\PDO::FETCH_OBJ);
foreach($objd as $stockDetail) {
$qty += $stockDetail->qty;
$values += $stockDetail->qty * $stockDetail->purchaseUnitCost;
}
}
$sql = Sql::update('item')->setFieldValue(["qty" => $qty, "totalValue" => $values])->where(['id', '=', $itemID]);
$sql->prepare()->execute();
}
public static function genTableHeader() {
$htmlContent = "";
$htmlContent .= "<thead class='thead-dark'>";
$htmlContent .= "<tr>";
$htmlContent .= "<th>".L('ID')."</th>";
$htmlContent .= "<th>".L('stock.item')."</th>";
$htmlContent .= "<th>".L('stock.itemType')."</th>";
$htmlContent .= "<th>".L('stock.supplier')."</th>";
$htmlContent .= "<th>".L('stock.storageLocation')."</th>";
$htmlContent .= "<th>".L('stock.storageLocationArea')."</th>";
$htmlContent .= "<th>".L('stock.qty')."</th>";
$htmlContent .= "<th>".L('stock.unit')."</th>";
$htmlContent .= "<th>".L('stock.value')."</th>";
$htmlContent .= "<th>".L('Actions')."</th>";
$htmlContent .= "</tr>";
$htmlContent .= "</thead>";
return $htmlContent;
}
public static function genTableFooter() {
$htmlContent = "";
$htmlContent .= "<tfoot>";
$htmlContent .= "<tr>";
$htmlContent .= "<th>".L('ID')."</th>";
$htmlContent .= "<th>".L('stock.item')."</th>";
$htmlContent .= "<th>".L('stock.itemType')."</th>";
$htmlContent .= "<th>".L('stock.supplier')."</th>";
$htmlContent .= "<th>".L('stock.storageLocation')."</th>";
$htmlContent .= "<th>".L('stock.storageLocationArea')."</th>";
$htmlContent .= "<th>".L('stock.qty')."</th>";
$htmlContent .= "<th>".L('stock.unit')."</th>";
$htmlContent .= "<th>".L('stock.value')."</th>";
$htmlContent .= "<th></th>";
$htmlContent .= "</tr>";
$htmlContent .= "</tfoot>";
return $htmlContent;
}
public static function genTableContentData() {
$sql = Sql::select('item_stock')->where(['qty', '>', 0]);
$stm = $sql->prepare();
$stm->execute();
return $stm;
}
public static function genTableBodyRow($listObj) {
$htmlContent = "";
$htmlContent .= "<tr>";
$htmlContent .= "<td>".$listObj['id']."</td>";
$htmlContent .= "<td>".item::find($listObj['itemID'])->name."</td>";
$htmlContent .= "<td>".itemType::find(item::find($listObj['itemID'])->itemTypeID)->name."</td>";
$htmlContent .= "<td>".supplier::find(item::find($listObj['itemID'])->supplierID)->name."</td>";
$htmlContent .= "<td>".storageLocation::find(storageLocationArea::find($listObj['storageLocationAreaID'])->storageLocationID)->name."</td>";
$htmlContent .= "<td>".storageLocationArea::find($listObj['storageLocationAreaID'])->name."</td>";
$htmlContent .= "<td>".$listObj['qty']."</td>";
$htmlContent .= "<td>".unit::find(item::find($listObj['itemID'])->unitID)->name."</td>";
$htmlContent .= "<td>".number_format($listObj['totalValue'],2)."</td>";
$htmlContent .= "<td>";
$htmlContent .= "<div class='btn-group' role='group' aria-label=''>";
$htmlContent .= "<button class='btn btn-sm btn-dark btnView' type='button' data-bs-toggle='tooltip' data-bs-placement='top' title='".L('menu.inventoryMain')."' data-id='".$listObj['id']."'><i class='fas fa-sm fa-th'></i></button>";
//$htmlContent .= "<button class='btn btn-sm btn-success btnEdit' type='button' data-bs-toggle='tooltip' data-bs-placement='top' title='".L('Edit')."' data-id='".$listObj['id']."'><i class='fas fa-sm fa-edit'></i></button>";
//$htmlContent .= "<button class='btn btn-sm btn-danger btnDel' type='button' data-bs-toggle='tooltip' data-bs-placement='top' title='".L('Delete')."' data-id='".$listObj['id']."'><i class='fas fa-sm fa-trash-alt'></i></button>";
$htmlContent .= "</div>";
$htmlContent .= "</td>";
$htmlContent .= "</tr>";
return $htmlContent;
}
}