1820 lines
75 KiB
PHP
1820 lines
75 KiB
PHP
<?php
|
|
class Mutation extends MY_Controller
|
|
{
|
|
function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->db_inventory = $this->load->database("inventory", true);
|
|
$this->db_inventory_log = $this->load->database('inventory_log', true);
|
|
$this->db_onex = 'one_aditya';
|
|
}
|
|
|
|
function search()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$prm = $this->sys_input;
|
|
|
|
$search = "%%";
|
|
if (isset($prm['search']) && trim($prm["search"]) != '') {
|
|
$search = trim($prm["search"]);
|
|
$search = '%' . $prm['search'] . '%';
|
|
}
|
|
|
|
$filter_type = '';
|
|
if ($prm['type'] != 'ALL') {
|
|
$filter_type = ' AND StockInOutType = '.$prm['type'];
|
|
}
|
|
|
|
$order_by = "StockInOutNumber";
|
|
if (isset($prm['order_by'])) {
|
|
$order_by = trim($prm["order_by"]);
|
|
}
|
|
$order_type = "asc";
|
|
if (isset($prm['order_type'])) {
|
|
$order_type = trim($prm["order_type"]);
|
|
}
|
|
$order = $order_by.' '.$order_type;
|
|
$perpage = 10;
|
|
$offset = ($prm['current_page'] - 1) * $perpage ;
|
|
|
|
$sql = "SELECT COUNT(*) as total FROM
|
|
(
|
|
SELECT StockInOutID as id
|
|
FROM `stockinout`
|
|
WHERE
|
|
StockInOutIsActive = 'Y' AND (DATE(StockInOutDate) BETWEEN ? AND ?) AND
|
|
( StockInOutNumber like ? ) $filter_type
|
|
) x";
|
|
$qry = $this->db_inventory->query($sql,array($prm['start_date'],$prm['end_date'],$search));
|
|
$tot_count = 0;
|
|
$tot_page = 0;
|
|
if ($qry) {
|
|
$tot_count = $qry->row()->total;
|
|
$tot_page = ceil($tot_count/$perpage);
|
|
} else {
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("purchase order count error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$rows = array();
|
|
$sql = "SELECT StockInOutID as id,
|
|
StockInOutNumber as code,
|
|
WarehouseName as warehouse_name,
|
|
StockInOutType as tx_type,
|
|
StockInOutWarehouseID as warehouse_id,
|
|
StockInOutDate as trx_date,
|
|
StockInOutNote as note,
|
|
StockInOutStockInOutReasonID as reason_id,
|
|
StockInOutReasonName as reason_name,
|
|
StockInOutIsConfirm as is_confirm,
|
|
IFNULL(M_UserUsername,'') as confirm_username,
|
|
DATE_FORMAT(StockInOutConfirmAt,'%d-%m-%Y %H:%i') as confirm_at,
|
|
'' as details
|
|
FROM `stockinout`
|
|
JOIN `warehouse` ON StockInOutWarehouseID = WarehouseID
|
|
JOIN `stockinoutreason` ON StockInOutStockInOutReasonID = StockInOutReasonID
|
|
LEFT JOIN $this->db_onex.`m_user` ON StockInOutConfirmBy = M_UserID
|
|
WHERE
|
|
StockInOutIsActive = 'Y' AND (DATE(StockInOutDate) BETWEEN ? AND ?) AND
|
|
( StockInOutNumber like ? ) $filter_type
|
|
ORDER BY $order_by $order_type
|
|
LIMIT ? OFFSET ?";
|
|
$qry = $this->db_inventory->query($sql, array($prm['start_date'],$prm['end_date'],$search, $perpage, $offset));
|
|
//echo $this->db_inventory->last_query();
|
|
if($qry){
|
|
$rows = $qry->result_array();
|
|
|
|
$result = array("total_page" => $tot_page, "records" => $rows);
|
|
$this->sys_ok($result);
|
|
exit;
|
|
}
|
|
else{
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("stockinout order select error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_details(){
|
|
try {
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$prm = $this->sys_input;
|
|
|
|
|
|
if($prm['tx_type'] == 'IN'){
|
|
$sql = "SELECT StockInDetailStockID as id,
|
|
StockInDetailStockItemID as item_id,
|
|
ItemName as item_name,
|
|
StockInDetailStockItemUnitID as unit_id,
|
|
ItemUnitName as unit_name,
|
|
StockInDetailStockStockNumber as stock_number,
|
|
IFNULL(StockInDetailStockBatchNo,'-') as batch_no,
|
|
IF(StockInDetailStockED IS NOT NULL, DATE_FORMAT(StockInDetailStockED,'%d-%m-%Y'),'-') as ed,
|
|
StockInDetailStockQty as qty
|
|
FROM stockindetailstock
|
|
JOIN item ON StockInDetailStockItemID = ItemID
|
|
JOIN itemunit ON StockInDetailStockItemUnitID = ItemUnitID
|
|
WHERE
|
|
StockInDetailStockStockInOutID = ? AND StockInDetailStockIsActive = 'Y'
|
|
";
|
|
}
|
|
|
|
if($prm['tx_type'] == 'OUT'){
|
|
$sql = "SELECT StockOutDetailID as id,
|
|
StockOutDetailItemID as item_id,
|
|
ItemName as item_name,
|
|
StockOutDetailItemUnitID as unit_id,
|
|
ItemUnitName as unit_name,
|
|
StockOutDetailQty as qty
|
|
FROM stockoutdetail
|
|
JOIN item ON StockOutDetailItemID = ItemID
|
|
JOIN itemunit ON StockOutDetailItemUnitID = ItemUnitID
|
|
WHERE
|
|
StockOutDetailStockInOutID = ? AND StockOutDetailIsActive = 'Y'
|
|
";
|
|
}
|
|
|
|
$query = $this->db_inventory->query($sql,array($prm['id']));
|
|
//echo $this->db_onedev->last_query();
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
//echo $this->db_onedev->last_query();
|
|
$result = array( "records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("mutasiinternalreceivedetail rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function search_to_company_address(){
|
|
try {
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$prm = $this->sys_input;
|
|
|
|
$max_rst = 12;
|
|
$tot_count =0;
|
|
|
|
$q = [
|
|
'search' => '%'
|
|
];
|
|
|
|
if ($prm['search'] != '')
|
|
{
|
|
$q['search'] = "%{$prm['search']}%";
|
|
}
|
|
|
|
$sql = "
|
|
SELECT CompanyAddressID as id, CompanyAddressLabel as name, CompanyAddressDescription as description,
|
|
M_BranchID as branch_id, M_BranchName as branch_name
|
|
FROM companyaddress
|
|
JOIN company ON CompanyAddressCompanyID = CompanyID
|
|
JOIN $this->db_onex.m_branch ON CompanyM_BranchID = M_BranchID
|
|
WHERE
|
|
CompanyName like ?
|
|
AND CompanyAddressIsActive = 'Y'
|
|
ORDER BY CompanyName ASC
|
|
";
|
|
$query = $this->db_inventory->query($sql, array($q['search']));
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
array_push($rows,array('id'=>0,'name'=>'Semua'));
|
|
//echo $this->db_onedev->last_query();
|
|
$result = array( "records" => $rows, "total_display" => sizeof($rows));
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("companyaddress rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_from_company_address(){
|
|
try {
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT CompanyAddressID as id, CompanyAddressLabel as name, CompanyAddressDescription as description,
|
|
M_BranchID as branch_id, M_BranchName as branch_name
|
|
FROM companyaddress
|
|
JOIN company ON CompanyAddressCompanyID = CompanyID
|
|
JOIN $this->db_onex.m_branch ON CompanyM_BranchID = M_BranchID
|
|
WHERE
|
|
CompanyAddressIsActive = 'Y'
|
|
ORDER BY CompanyName ASC
|
|
";
|
|
$query = $this->db_inventory->query($sql);
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
array_push($rows,array('id'=>0,'name'=>'Semua'));
|
|
//echo $this->db_onedev->last_query();
|
|
$result = array( "records" => $rows, "total_display" => sizeof($rows));
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("companyaddress rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
|
|
function get_from_warehouse(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT WarehouseID as id,
|
|
WarehouseName as name
|
|
FROM warehouse
|
|
JOIN warehousealmari ON WarehouseAlmariWarehouseID = WarehouseID AND WarehouseAlmariIsActive = 'Y'
|
|
JOIN warehouserack ON WarehouseRackWarehouseAlmariID = WarehouseAlmariID AND WarehouseRackIsActive = 'Y' AND WarehouseIsOffice = 'Y'
|
|
WHERE
|
|
WarehouseIsOffice = 'Y' AND WarehouseIsActive = 'Y'
|
|
GROUP BY WarehouseID";
|
|
$query = $this->db_inventory->query($sql);
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
$rows[] = array('id'=>0,'name'=>'Semua');
|
|
|
|
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("warehouse rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_from_warehouse_form(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT WarehouseID as id,
|
|
WarehouseName as name
|
|
FROM warehouse
|
|
JOIN mutasiinternal ON MutasiInternalFromWarehouseID = WarehouseID AND MutasiInternalStatus = 'S'
|
|
JOIN mutasiinternaldetail ON MutasiInternalDetailMutasiInternalID = MutasiInternalID AND MutasiInternalDetailIsActive = 'Y'
|
|
WHERE
|
|
WarehouseIsActive = 'Y'
|
|
GROUP BY WarehouseID";
|
|
$query = $this->db_inventory->query($sql);
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("warehouse rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
|
|
function save(){
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$userid = $this->sys_user['M_UserID'];
|
|
$prm = $this->sys_input;
|
|
|
|
$note = "";
|
|
if (isset($prm['note'])) {
|
|
$note = trim($prm['note']);
|
|
}
|
|
|
|
|
|
$this->db_inventory->trans_start();
|
|
$this->db_inventory->trans_strict(FALSE);
|
|
|
|
$numbering = '';
|
|
$sql = "SELECT `fn_numbering`('SO') as numbering";
|
|
$qry = $this->db_inventory->query($sql);
|
|
if($qry){
|
|
$numbering = $qry->row()->numbering;
|
|
}
|
|
else{
|
|
$this->sys_error_db("get numbering error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$last_id = 0;
|
|
$now = new DateTime();
|
|
|
|
$sql = "INSERT INTO stockinout(
|
|
StockInOutNumber,
|
|
StockInOutDate,
|
|
StockInOutType,
|
|
StockInOutWarehouseID,
|
|
StockInOutStockInOutReasonID,
|
|
StockInOutNote,
|
|
StockInOutUserID,
|
|
StockInOutCreated,
|
|
StockInOutLastUpdated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,?,?,NOW(),NOW()
|
|
)";
|
|
$param_insert = array(
|
|
$numbering,
|
|
$prm['date_trx'],
|
|
$prm['selected_type_form']['id'],
|
|
$prm['warehouse']['id'],
|
|
$prm['reason']['id'],
|
|
$note,
|
|
$userid
|
|
);
|
|
$qry = $this->db_inventory->query($sql, $param_insert);
|
|
$last_id = 0;
|
|
if($qry){
|
|
$last_id = $this->db_inventory->insert_id();
|
|
}
|
|
else{
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("save stockinout error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
if(count($prm['details'])>0){
|
|
if($prm['selected_type_form']['id'] == 'IN'){
|
|
foreach ($prm['details'] as $key => $value) {
|
|
if(intval($value['qty']) > 0){
|
|
|
|
$sql = "SELECT fn_numbering_stock_not_purchase(ItemSKU) as numbering FROM item WHERE ItemID = ?";
|
|
$qry = $this->db_inventory->query($sql,array($value['item_id']));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("fn_numbering_stock_not_purchase error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$stock_numbering = $qry->row()->numbering;
|
|
$arr_insert_detail_stock = array(
|
|
'StockInDetailStockStockInOutID' => $last_id,
|
|
'StockInDetailStockItemID' => $value['item_id'],
|
|
'StockInDetailStockItemUnitID' => $value['unit_id'],
|
|
'StockInDetailStockStockNumber' => $stock_numbering,
|
|
'StockInDetailStockBatchNo' => $value['batch_no'],
|
|
'StockInDetailStockQty' => $value['qty'],
|
|
'StockInDetailStockUserID' => $userid,
|
|
'StockInDetailStockCreated' => date("Y-m-d H:i:s"),
|
|
'StockInDetailStockLastUpdated' => date("Y-m-d H:i:s")
|
|
);
|
|
$ed = date("Y-m-d", strtotime($value['ed']) );
|
|
$valid_ed = $this->validateDate($ed)?$ed:NULL;
|
|
if($valid_ed && $ed != '0000-00-00')
|
|
$arr_insert_detail_stock['StockInDetailStockED'] = $ed;
|
|
|
|
$qry = $this->db_inventory->insert('stockindetailstock', $arr_insert_detail_stock);
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("save stockindetailstock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|
|
if($prm['selected_type_form']['id'] == 'OUT'){
|
|
foreach ($prm['details'] as $key => $value) {
|
|
$arr_insert_detail = array(
|
|
'StockOutDetailStockInOutID' => $last_id,
|
|
'StockOutDetailItemID' => $value['item_id'],
|
|
'StockOutDetailItemUnitID' => $value['unit_id'],
|
|
'StockOutDetailQty' => $value['qty'],
|
|
'StockOutDetailUserID' => $userid,
|
|
'StockOutDetailCreated' => date("Y-m-d H:i:s"),
|
|
'StockOutDetailLastUpdated' => date("Y-m-d H:i:s")
|
|
);
|
|
|
|
|
|
$qry = $this->db_inventory->insert('stockoutdetail', $arr_insert_detail);
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("save stockoutdetail error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
$id = $last_id;
|
|
$sql = "SELECT stockinout.*, '' as details
|
|
FROM stockinout
|
|
WHERE StockInOutID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select mutasiinternalreceive error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$row_after = $qry->row_array();
|
|
if($prm['selected_type_form']['id'] == 'IN'){
|
|
$sql = "SELECT *
|
|
FROM `stockindetailstock`
|
|
WHERE
|
|
StockInDetailStockStockInOutID = ? AND
|
|
StockInDetailStockIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if(!$qry){
|
|
$this->sys_error_db("select stockindetailstock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
if($prm['selected_type_form']['id'] == 'OUT'){
|
|
$sql = "SELECT *
|
|
FROM `stockoutdetail`
|
|
WHERE
|
|
StockOutDetailStockInOutID = ? AND
|
|
StockOutDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if(!$qry){
|
|
$this->sys_error_db("select stockoutdetail error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$row_after['details'] = $qry->result_array();
|
|
$data_log_after = $row_after;
|
|
|
|
$sql = "INSERT INTO stockinout_log (
|
|
StockInOutLogStockInOutID,
|
|
StockInOutLogStatus,
|
|
StockInOutLogJSONBefore,
|
|
StockInOutLogJSONAfter,
|
|
StockInOutLogUserID,
|
|
StockInOutLogCreated
|
|
)
|
|
VALUES(
|
|
?,?,NULL,?,?,NOW()
|
|
)";
|
|
$qry = $this->db_inventory_log->query($sql, array($id,'ADD',json_encode($data_log_after),$userid));
|
|
if(!$qry){
|
|
$this->sys_error_db("insert log error", $this->db_inventory_log->last_query());
|
|
exit;
|
|
}
|
|
|
|
|
|
$this->db_inventory->trans_complete();
|
|
|
|
$result = array(
|
|
"message" => ''
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
|
|
function validateDate($date, $format = 'Y-m-d')
|
|
{
|
|
$d = DateTime::createFromFormat($format, $date);
|
|
// The Y ( 4 digits year ) returns TRUE for any integer with any number of digits so changing the comparison from == to === fixes the issue.
|
|
return $d && $d->format($format) === $date;
|
|
}
|
|
|
|
|
|
|
|
function get_data_company_default(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT CompanyID as id,
|
|
CompanyName as name,
|
|
'' as address
|
|
FROM company
|
|
JOIN one_aditya.m_branch ON CompanyM_BranchID = M_BranchID AND M_BranchIsDefault = 'Y'
|
|
WHERE
|
|
CompanyIsActive = 'Y' LIMIT 1";
|
|
$query = $this->db_inventory->query($sql);
|
|
|
|
if ($query) {
|
|
$row = $query->row_array();
|
|
|
|
$sql = "SELECT CompanyAddressID as id,
|
|
CompanyAddressLabel as name,
|
|
CompanyAddressDescription as description
|
|
FROM companyaddress
|
|
WHERE
|
|
CompanyAddressCompanyID = ? AND CompanyAddressIsActive = 'Y'";
|
|
$query = $this->db_inventory->query($sql,array($row['id']));
|
|
$data_address = $query->result_array();
|
|
$row['address'] = $data_address;
|
|
|
|
|
|
|
|
$result = array("records" => $row);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("expedition rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
|
|
function get_warehouse(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$prm = $this->sys_input;
|
|
|
|
$sql = "SELECT WarehouseID as id,
|
|
WarehouseName as name,
|
|
'' as almaries
|
|
FROM warehouse
|
|
JOIN warehousealmari ON WarehouseAlmariWarehouseID = WarehouseID AND WarehouseAlmariIsActive = 'Y'
|
|
JOIN warehouserack ON WarehouseRackWarehouseAlmariID = WarehouseAlmariID AND WarehouseRackIsActive = 'Y'
|
|
WHERE
|
|
WarehouseIsActive = 'Y' AND WarehouseIsOffice = 'N'
|
|
GROUP BY WarehouseID
|
|
LIMIT 20";
|
|
$query = $this->db_inventory->query($sql);
|
|
//echo $this->db_inventory->last_query();
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
if($rows){
|
|
foreach ($rows as $key => $value) {
|
|
$almaries = [];
|
|
$sql = "SELECT WarehouseAlmariID as id,
|
|
WarehouseAlmariCode as code,
|
|
WarehouseAlmariName as name,
|
|
'' as racks
|
|
FROM warehousealmari
|
|
WHERE
|
|
WarehouseAlmariWarehouseID = ? AND WarehouseAlmariIsActive = 'Y'
|
|
ORDER BY WarehouseAlmariCode";
|
|
$query = $this->db_inventory->query($sql,array($value['id']));
|
|
if(!$query){
|
|
$this->sys_error_db("warehousealmari rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
$almaries = $query->result_array();
|
|
if($almaries){
|
|
foreach ($almaries as $k => $v) {
|
|
$sql = "SELECT WarehouseRackID as id,
|
|
WarehouseRackCode as code,
|
|
WarehouseRackName as name,
|
|
'' as racks
|
|
FROM warehouserack
|
|
WHERE
|
|
WarehouseRackWarehouseAlmariID = ? AND WarehouseRackIsActive = 'Y'
|
|
ORDER BY WarehouseRackCode";
|
|
$query = $this->db_inventory->query($sql,array($v['id']));
|
|
if(!$query){
|
|
$this->sys_error_db("warehouserack rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
$almaries[$k]['racks'] = $query->result_array();
|
|
}
|
|
}
|
|
|
|
$rows[$key]['almaries'] = $almaries;
|
|
}
|
|
}
|
|
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("warehouse rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_reason(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$prm = $this->sys_input;
|
|
$type = isset($prm['id'])?$prm['id']:'';
|
|
if($type != ''){
|
|
$sql = "SELECT StockInOutReasonID as id,
|
|
StockInOutReasonName as name,
|
|
StockInOutReasonCode as code
|
|
FROM stockinoutreason
|
|
WHERE
|
|
StockInOutReasonIsActive = 'Y' AND StockInOutReasonType = ?
|
|
";
|
|
$query = $this->db_inventory->query($sql,array($type));
|
|
//echo $this->db_inventory->last_query();
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
}else{
|
|
$this->sys_error("Invalid Param Type ID");
|
|
exit;
|
|
}
|
|
|
|
|
|
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
function get_expedition_staff(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT M_StaffID as id,
|
|
M_StaffName as name
|
|
FROM expeditionstaff
|
|
JOIN $this->db_onex.m_staff ON ExpeditionStaffM_StaffID = M_StaffID AND M_StaffIsActive = 'Y'
|
|
WHERE
|
|
ExpeditionStaffIsActive = 'Y'";
|
|
$query = $this->db_inventory->query($sql);
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
|
|
|
|
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("expedition rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
|
|
function get_from_company_address_form(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT CompanyAddressID as id,
|
|
CompanyAddressLabel as name,
|
|
CompanyAddressDescription as description,
|
|
CompanyName as company_name
|
|
FROM companyaddress
|
|
JOIN mutasitransitdetail ON MutasiTransitDetailFromCompanyAddressID = CompanyAddressID AND
|
|
MutasiTransitDetailIsReceive = 'N'
|
|
JOIN company ON CompanyAddressCompanyID = CompanyID
|
|
WHERE
|
|
CompanyAddressIsActive = 'Y'
|
|
GROUP BY CompanyAddressID";
|
|
$query = $this->db_inventory->query($sql);
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("companyaddress rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
|
|
function get_data_transit(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$userid = $this->sys_user['M_UserID'];
|
|
$prm = $this->sys_input;
|
|
$id_from = $prm['from_warehouse_id'];
|
|
$id_to = isset($prm['to_warehouse_id']) && intval($prm['to_warehouse_id']) > 0 ? $prm['to_warehouse_id'] : 0;
|
|
$sql = "SELECT MutasiInternalDetailStockID as transit_detail_id,
|
|
MutasiInternalDetailStockID as detail_stock_id,
|
|
MutasiInternalNumber as mutasi_number,
|
|
MutasiInternalID as mutasi_id,
|
|
ItemID as item_id,
|
|
ItemUnitID as unit_id,
|
|
MutasiInternalDetailQty as qty_sent,
|
|
ItemName as item_name,
|
|
ItemUnitName as unit_name,
|
|
CONCAT(ItemName,' : ',ItemUnitName) as itemunit_name,
|
|
MutasiInternalDetailStockQty as qty,
|
|
MutasiInternalDetailStockBatchNo as batch_no,
|
|
MutasiInternalDetailStockStockNumber as stock_number,
|
|
MutasiInternalDetailStockED as ed,
|
|
MutasiInternalFromWarehouseID as from_warehouse_id,
|
|
MutasiInternalToWarehouseID as to_warehouse_id,
|
|
M_StaffName as expedition_staff,
|
|
MutasiInternalDetailID as mutasi_detail_id,
|
|
MutasiInternalDetailMutasiRequestInternalDetailID as mutasi_request_receive_id,
|
|
MutasiInternalDetailMutasiRequestInternalDetailID as mutasi_request_detail_id,
|
|
MutasiInternalNote as note,
|
|
'' as almaries,
|
|
'' as racks,
|
|
'' as almari_id,
|
|
'' as rack_id
|
|
FROM mutasiinternaldetailstock
|
|
JOIN mutasiinternaldetail ON MutasiInternalDetailStockMutasiInternalDetailID = MutasiInternalDetailID
|
|
JOIN mutasiinternal ON MutasiInternalDetailMutasiInternalID = MutasiInternalID
|
|
JOIN $this->db_onex.m_staff ON MutasiInternalM_StaffID = M_StaffID
|
|
JOIN item ON MutasiInternalDetailStockItemID = ItemID
|
|
JOIN itemunit ON MutasiInternalDetailStockItemUnitID = ItemUnitID
|
|
WHERE
|
|
MutasiInternalDetailStockIsReceive = 'N' AND MutasiInternalDetailStockIsActive = 'Y' AND
|
|
MutasiInternalToWarehouseID = ? AND MutasiInternalFromWarehouseID = ?";
|
|
$query = $this->db_inventory->query($sql,array($id_to,$id_from));
|
|
//echo $this->db_inventory->last_query();
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
if(intval($id_to) > 0){
|
|
$sql = "SELECT WarehouseAlmariID as id,
|
|
WarehouseAlmariName as name,
|
|
WarehouseAlmariCode as code,
|
|
'' as racks
|
|
FROM warehousealmari
|
|
JOIN warehouserack ON WarehouseRackWarehouseAlmariID = WarehouseAlmariID AND WarehouseRackIsActive = 'Y'
|
|
WHERE
|
|
WarehouseAlmariWarehouseID = ? AND WarehouseAlmariIsActive = 'Y'
|
|
GROUP BY WarehouseAlmariID";
|
|
$query = $this->db_inventory->query($sql,array($id_to));
|
|
if(!$query){
|
|
$this->sys_error_db("get data almari error",$this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$rows_almaries = $query->result_array();
|
|
foreach ($rows_almaries as $key => $value) {
|
|
$sql = "SELECT WarehouseRackID as id,
|
|
WarehouseRackCode as code,
|
|
WarehouseRackCode as name
|
|
FROM warehouserack
|
|
WHERE
|
|
WarehouseRackWarehouseAlmariID = ? AND WarehouseRackIsActive = 'Y'
|
|
GROUP BY WarehouseRackID
|
|
ORDER BY WarehouseRackCode ASC";
|
|
$query = $this->db_inventory->query($sql,array($value['id']));
|
|
if(!$query){
|
|
$this->sys_error_db("get data rack error",$this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$rows_racks = $query->result_array();
|
|
$rows_almaries[$key]['racks'] = $rows_racks;
|
|
}
|
|
|
|
if(count($rows_almaries) > 0){
|
|
foreach ($rows as $k => $v) {
|
|
$rows[$k]['almaries'] = $rows_almaries;
|
|
$rows[$k]['almari_id'] = $rows_almaries[0]['id'];
|
|
$rows[$k]['racks'] = $rows_almaries[0]['racks'];
|
|
$rows[$k]['rack_id'] = $rows[$k]['racks'][0]['id'];
|
|
}
|
|
}
|
|
|
|
}
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("get data transit rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_printout(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT *
|
|
FROM r_report
|
|
WHERE
|
|
R_ReportCode = 'MPB' AND R_ReportIsActive = 'Y'";
|
|
$query = $this->db_inventory->query($sql);
|
|
|
|
if ($query) {
|
|
$rows = $query->row_array();
|
|
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("r_report rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function search_item(){
|
|
try {
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$prm = $this->sys_input;
|
|
|
|
$max_rst = 12;
|
|
$tot_count =0;
|
|
|
|
$q = [
|
|
'search' => '%'
|
|
];
|
|
|
|
if ($prm['search'] != '')
|
|
{
|
|
$q['search'] = "%{$prm['search']}%";
|
|
}
|
|
if($prm['type'] == 'IN'){
|
|
$sql = "SELECT ItemID as id, ItemName as name
|
|
FROM item
|
|
JOIN itemunitmap ON ItemUnitMapItemID = ItemID
|
|
WHERE
|
|
ItemName like ?
|
|
AND ItemIsActive = 'Y'
|
|
ORDER BY ItemName ASC
|
|
";
|
|
$query = $this->db_inventory->query($sql, array($q['search']));
|
|
}
|
|
if($prm['type'] == 'OUT'){
|
|
$sql = "SELECT ItemID as id, ItemName as name,
|
|
SUM(StockQty) as qty_stock
|
|
FROM item
|
|
JOIN itemunitmap ON ItemUnitMapItemID = ItemID
|
|
JOIN stock ON StockItemID = ItemID AND StockItemUnitID = ItemUnitMapItemUnitID AND StockQty > 0
|
|
WHERE
|
|
ItemName like ?
|
|
AND ItemIsActive = 'Y'
|
|
GROUP BY ItemUnitMapID
|
|
ORDER BY ItemName ASC
|
|
";
|
|
$query = $this->db_inventory->query($sql, array($q['search']));
|
|
}
|
|
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
//echo $this->db_onedev->last_query();
|
|
$result = array( "records" => $rows, "total_display" => sizeof($rows));
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("item rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_unit_by_item(){
|
|
try {
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$prm = $this->sys_input;
|
|
|
|
$sql = "SELECT ItemUnitID as id, ItemUnitName as name, ItemUnitMapMin as min_stock
|
|
FROM itemunitmap
|
|
JOIN itemunit ON ItemUnitMapItemUnitID = ItemUnitID
|
|
WHERE
|
|
ItemUnitMapItemID = ? AND ItemUnitMapIsActive = 'Y'
|
|
ORDER BY ItemUnitName ASC
|
|
";
|
|
$query = $this->db_inventory->query($sql,[$prm['id']]);
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
//echo $this->db_onedev->last_query();
|
|
$result = array( "records" => $rows, "total_display" => sizeof($rows));
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("unit rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function delete()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$userid = $this->sys_user['M_UserID'];
|
|
$param = $this->sys_input;
|
|
$id = "";
|
|
if (isset($param['id'])) {
|
|
$id = intval($param["id"]);
|
|
}
|
|
if ($id == "" || !$id) {
|
|
$error = array(
|
|
"message" => "id is mandatory",
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
|
|
$this->db_inventory->trans_start();
|
|
$this->db_inventory->trans_strict(FALSE);
|
|
|
|
$sql = "UPDATE stockinout
|
|
SET
|
|
StockInOutIsActive = 'N',
|
|
StockInOutLastUpdated = now(),
|
|
StockInOutUserID = ?
|
|
WHERE StockInOutID = ?
|
|
";
|
|
$qry = $this->db_inventory->query($sql, [$userid,$id]);
|
|
if(!$qry){
|
|
$this->sys_error_db("delete stockinout error", $this->db_inventory->last_query());
|
|
exit;
|
|
}else{
|
|
$sql = "UPDATE stockoutdetail
|
|
SET
|
|
StockOutDetailIsActive = 'N',
|
|
StockOutDetailLastUpdated = now(),
|
|
StockOutDetailUserID = ?
|
|
WHERE
|
|
StockOutDetailStockInOutID = ?
|
|
";
|
|
$qry = $this->db_inventory->query($sql, [$userid,$id]);
|
|
if(!$qry){
|
|
$this->sys_error_db("delete stockoutdetail error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$sql = "UPDATE stockindetailstock
|
|
SET
|
|
StockInDetailStockIsActive = 'N',
|
|
StockInDetailStockLastUpdated = now(),
|
|
StockInDetailStockUserID = ?
|
|
WHERE
|
|
StockInDetailStockStockInOutID = ?
|
|
";
|
|
$qry = $this->db_inventory->query($sql, [$userid,$id]);
|
|
if(!$qry){
|
|
$this->sys_error_db("delete stockindetailstock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$sql = "SELECT stockinout.*, '' as details
|
|
FROM stockinout
|
|
WHERE StockInOutID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select stockinout error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$row_after = $qry->row_array();
|
|
$data_log_after = $row_after;
|
|
|
|
$sql = "INSERT INTO stockinout_log (
|
|
StockInOutLogStockInOutID,
|
|
StockInOutLogStatus,
|
|
StockInOutLogJSONBefore,
|
|
StockInOutLogJSONAfter,
|
|
StockInOutLogUserID,
|
|
StockInOutLogCreated
|
|
)
|
|
VALUES(
|
|
?,?,NULL,?,?,NOW()
|
|
)";
|
|
$qry = $this->db_inventory_log->query($sql, array($id,'DELETE',json_encode($data_log_after),$userid));
|
|
if(!$qry){
|
|
$this->sys_error_db("insert log error", $this->db_inventory_log->last_query());
|
|
exit;
|
|
}
|
|
|
|
$this->db_inventory->trans_complete();
|
|
|
|
$result = array(
|
|
"message" => ''
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function update($prm,$userid){
|
|
$note = "";
|
|
if (isset($prm['note'])) {
|
|
$note = trim($prm["note"]);
|
|
}
|
|
|
|
$id = $prm['id'];
|
|
|
|
$sql = "SELECT stockinout.*, '' as details
|
|
FROM stockinout
|
|
WHERE StockInOutID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select request mutasi error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$row_before = $qry->row_array();
|
|
if($prm['selected_type_form'] == 'IN'){
|
|
$sql = "SELECT *, '' as details
|
|
FROM `stockindetailstock`
|
|
WHERE
|
|
StockInDetailStockStockInOutID = ? AND
|
|
StockInDetailStockIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
}else{
|
|
$sql = "SELECT *, '' as details
|
|
FROM `stockoutdetail`
|
|
WHERE
|
|
StockOutDetailStockInOutID = ? AND
|
|
StockOutDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
}
|
|
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select detail error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$row_before['details'] = $qry->result_array();
|
|
$data_log_before = $row_before;
|
|
|
|
$this->db_inventory->trans_start();
|
|
$this->db_inventory->trans_strict(FALSE);
|
|
|
|
$last_id = $id;
|
|
$sql = "UPDATE stockinout SET
|
|
StockInOutDate = ?,
|
|
StockInOutType = ?,
|
|
StockInOutWarehouseID = ?,
|
|
StockInOutStockInOutReasonID = ?,
|
|
StockInOutNote = ?,
|
|
StockInOutLastUpdated = NOW(),
|
|
StockInOutUserID = ?
|
|
WHERE
|
|
StockInOutID = ?";
|
|
$param_update_header = array(
|
|
$prm['date_trx'],
|
|
$prm['selected_type_form']['id'],
|
|
isset($prm['warehouse'])?$prm['warehouse']['id']:0,
|
|
isset($prm['reason'])?$prm['reason']['id']:0,
|
|
$note,
|
|
$userid,
|
|
$id
|
|
);
|
|
|
|
$qry = $this->db_inventory->query($sql, $param_update_header);
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("edit stockinout error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
if($prm['selected_type_form']['id'] == 'IN'){
|
|
$sql = "UPDATE mutasirequestdetail SET MutasiRequestDetailIsActive = 'N' WHERE MutasiRequestDetailMutasiRequestID = ?";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("update N detail error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
if(count($prm['details'])>0){
|
|
foreach ($prm['details'] as $key => $value) {
|
|
if(intval($value['qty']) > 0){
|
|
if(intval($value['id']) > 0){
|
|
$sql = "UPDATE stockindetailstock SET
|
|
StockInDetailStockUserID = ?,
|
|
StockInDetailStockIsActive = 'Y',
|
|
StockInDetailStockLastUpdated = NOW()
|
|
WHERE
|
|
StockInDetailStockID = ?";
|
|
$param_edit_detail = array(
|
|
$userid,
|
|
$value['id']
|
|
);
|
|
$qry = $this->db_inventory->query($sql, $param_edit_detail);
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("edit stockindetailstock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}else{
|
|
$sql = "SELECT fn_numbering_stock_not_purchase(ItemSKU) as numbering FROM item WHERE ItemID = ?";
|
|
$qry = $this->db_inventory->query($sql,array($value['item_id']));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("fn_numbering_stock_not_purchase error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$stock_numbering = $qry->row()->numbering;
|
|
|
|
$arr_insert_detail_stock = array(
|
|
'StockInDetailStockStockInOutID' => $last_id,
|
|
'StockInDetailStockItemID' => $value['item_id'],
|
|
'StockInDetailStockItemUnitID' => $value['unit_id'],
|
|
'StockInDetailStockStockNumber' => $stock_numbering,
|
|
'StockInDetailStockBatchNo' => $value['batch_no'],
|
|
'StockInDetailStockQty' => $value['qty'],
|
|
'StockInDetailStockUserID' => $userid,
|
|
'StockInDetailStockCreated' => date("Y-m-d H:i:s"),
|
|
'StockInDetailStockLastUpdated' => date("Y-m-d H:i:s")
|
|
);
|
|
$ed = date("Y-m-d", strtotime($value['ed']) );
|
|
$valid_ed = $this->validateDate($ed)?$ed:NULL;
|
|
if($valid_ed && $ed != '0000-00-00')
|
|
$arr_insert_detail_stock['StockInDetailStockED'] = $ed;
|
|
|
|
$qry = $this->db_inventory->insert('stockindetailstock', $arr_insert_detail_stock);
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("save stockindetailstock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
$sql = "SELECT stockinout.*, '' as details
|
|
FROM stockinout
|
|
WHERE StockInOutID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select mutasiinternalreceive error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$row_after = $qry->row_array();
|
|
if($param['selected_type_form']['id'] == 'IN'){
|
|
$sql = "SELECT *
|
|
FROM `stockindetailstock`
|
|
WHERE
|
|
StockInDetailStockStockInOutID = ? AND
|
|
StockInDetailStockIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if(!$qry){
|
|
$this->sys_error_db("select stockindetailstock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
if($param['selected_type_form']['id'] == 'OUT'){
|
|
$sql = "SELECT *
|
|
FROM `stockoutdetail`
|
|
WHERE
|
|
StockOutDetailStockInOutID = ? AND
|
|
StockOutDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if(!$qry){
|
|
$this->sys_error_db("select stockoutdetail error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$row_after['details'] = $qry->result_array();
|
|
$data_log_after = $row_after;
|
|
|
|
$sql = "INSERT INTO stockinout_log (
|
|
StockInOutLogStockInOutID,
|
|
StockInOutLogStatus,
|
|
StockInOutLogJSONBefore,
|
|
StockInOutLogJSONAfter,
|
|
StockInOutLogUserID,
|
|
StockInOutLogCreated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,NOW()
|
|
)";
|
|
$qry = $this->db_inventory_log->query($sql, array($id,'EDIT',json_encode($data_log_before),json_encode($data_log_after),$userid));
|
|
if(!$qry){
|
|
$this->sys_error_db("insert log error", $this->db_inventory_log->last_query());
|
|
exit;
|
|
}
|
|
|
|
$this->db_inventory->trans_complete();
|
|
|
|
return true;
|
|
}
|
|
|
|
function confirm()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$userid = $this->sys_user['M_UserID'];
|
|
$param = $this->sys_input;
|
|
|
|
$this->update($param,$userid);
|
|
$id = "";
|
|
|
|
if (isset($param['id'])) {
|
|
$id = intval($param["id"]);
|
|
}
|
|
|
|
if ($id == "" || !$id) {
|
|
$error = array(
|
|
"message" => "id is mandatory",
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
|
|
|
|
$this->db_inventory->trans_start();
|
|
$this->db_inventory->trans_strict(FALSE);
|
|
|
|
$sql = "UPDATE stockinout
|
|
SET StockInOutIsConfirm = 'Y',
|
|
StockInOutStatus = 'C',
|
|
StockInOutLastUpdated = NOW(),
|
|
StockInOutConfirmAt = NOW(),
|
|
StockInOutConfirmBy = ?,
|
|
StockInOutUserID = ?
|
|
WHERE StockInOutID = ?
|
|
";
|
|
$qry = $this->db_inventory->query($sql, [$userid,$userid,$id]);
|
|
if(!$qry){
|
|
|
|
$this->sys_error_db("confirm stockinout error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
//echo $prm['selected_type_form']['id'];
|
|
|
|
if($param['selected_type_form']['id'] == 'IN'){
|
|
|
|
|
|
foreach ($param['details'] as $key => $value) {
|
|
//start insert stock
|
|
|
|
$select_ed = "";
|
|
if($valid_ed && $value['ed'] != '0000-00-00')
|
|
$select_ed = " AND StockED = '{$value['ed']}'";
|
|
|
|
$sql = "SELECT *
|
|
FROM stock
|
|
WHERE
|
|
StockWarehouseID = ? AND
|
|
StockWarehouseAlmariID = ? AND
|
|
StockWarehouseRackID = ? AND
|
|
StockStockNumber = ? AND
|
|
StockBatchNo = ? AND
|
|
StockItemID = ? AND
|
|
StockItemUnitID = ?
|
|
$select_ed
|
|
LIMIT 1";
|
|
$qry = $this->db_inventory->query($sql, array(
|
|
$param['warehouse']['id'],
|
|
$value['almari_id'],
|
|
$value['rack_id'],
|
|
$value['stock_number'],
|
|
$value['batch_no'],
|
|
$value['item_id'],
|
|
$value['unit_id']
|
|
));
|
|
//echo $this->db_inventory->last_query();
|
|
|
|
if($qry->row_array()){
|
|
$data_stock_exist = $qry->row_array();
|
|
$sql = "UPDATE stock SET StockQty = ?
|
|
WHERE
|
|
StockID = ?";
|
|
$qry = $this->db_inventory->query($sql, array(
|
|
$data_stock_exist['StockID'],
|
|
intval($data_stock_exist['StockQty']) + intval($value['qty'])
|
|
));
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("update stock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}else{
|
|
$arr_insert_stock = array(
|
|
'StockWarehouseID' => $param['warehouse']['id'],
|
|
'StockWarehouseAlmariID' => $value['almari_id'],
|
|
'StockWarehouseRackID' => $value['rack_id'],
|
|
'StockStockNumber' => $value['stock_number'],
|
|
'StockItemID' => $value['item_id'],
|
|
'StockItemUnitID' => $value['unit_id'],
|
|
'StockBatchNo' => $value['batch_no'],
|
|
'StockQty' => $value['qty'],
|
|
'StockUserID' => $userid
|
|
);
|
|
|
|
if($valid_ed && $value['ed'] != '0000-00-00')
|
|
$arr_insert_stock['StockED'] = $value['ed'];
|
|
|
|
$qry = $this->db_inventory->insert('stock', $arr_insert_stock);
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("insert stock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
//end insert stock
|
|
|
|
//start insert stocklog
|
|
$arr_insert_stock = array(
|
|
'StockLogWarehouseID' => $param['warehouse']['id'],
|
|
'StockLogWarehouseAlmariID' => $value['almari_id'],
|
|
'StockLogWarehouseRackID' => $value['rack_id'],
|
|
'StockLogStockNumber' => $value['stock_number'],
|
|
'StockLogItemID' => $value['item_id'],
|
|
'StockLogItemUnitID' => $value['unit_id'],
|
|
'StockLogBatchNo' => $value['batch_no'],
|
|
'StockLogQty' => $value['qty'],
|
|
'StockLogDatetime' => date("Y-m-d H:i:s"),
|
|
'StockLogUserID' => $userid,
|
|
'StockLogReffID' => $detail_id,
|
|
'StockLogStatus' => 'SIN'
|
|
);
|
|
|
|
|
|
if($valid_ed && $value['ed'] != '0000-00-00')
|
|
$arr_insert_stock['StockLogED'] = $value['ed'];
|
|
|
|
$qry = $this->db_inventory->insert('stocklog', $arr_insert_stock);
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("save stocklog error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
//end insert stocklog
|
|
|
|
//start insert stockcard
|
|
$select_ed_card = "";
|
|
if($valid_ed && $value['ed'] != '0000-00-00')
|
|
$select_ed_card = " AND StockCardED = '{$value['ed']}'";
|
|
$sql = "SELECT *
|
|
FROM stockcard
|
|
WHERE
|
|
StockCardWarehouseID = ? AND
|
|
StockCardItemID = ? AND
|
|
StockCardItemUnitID = ? AND
|
|
StockCardBatchNo = ? $select_ed_card
|
|
ORDER BY StockCardDatetime DESC
|
|
LIMIT 1";
|
|
$qry = $this->db_inventory->query($sql,array(
|
|
$param['warehouse']['id'],
|
|
$value['item_id'],
|
|
$value['unit_id'],
|
|
$value['batch_no']
|
|
));
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("save stockcard error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$last_qty = 0;
|
|
if($qry->row_array()){
|
|
$last_qty = $qry->row_array()['StockCardAfter'];
|
|
}
|
|
|
|
$arr_insert_stockcard = array(
|
|
'StockCardWarehouseID' => $param['warehouse']['id'],
|
|
'StockCardItemID' => $value['item_id'],
|
|
'StockCardItemUnitID' => $value['unit_id'],
|
|
'StockCardBatchNo' => $value['batch_no'],
|
|
'StockCardBefore' => $last_qty,
|
|
'StockCardIn' => $value['qty'],
|
|
'StockCardOut' => 0,
|
|
'StockCardAfter' => $last_qty+intval($value['qty']),
|
|
'StockCardStatus' => 'SIN',
|
|
'StockCardDatetime' => date("Y-m-d H:i:s"),
|
|
'StockCardUserID' => $userid
|
|
);
|
|
|
|
|
|
if($valid_ed && $value['ed'] != '0000-00-00')
|
|
$arr_insert_stockcard['StockCardED'] = $value['ed'];
|
|
|
|
$qry = $this->db_inventory->insert('stockcard', $arr_insert_stockcard);
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("insert stockcard error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
//end insert stockcard
|
|
}
|
|
}
|
|
|
|
//print_r($param['selected_type_form']);
|
|
//start out
|
|
if($param['selected_type_form']['id'] == 'OUT'){
|
|
//echo "proses keluar";
|
|
$sql = "SELECT stockoutdetail.*,'' as details
|
|
FROM stockoutdetail
|
|
WHERE
|
|
StockOutDetailStockInOutID = ? AND StockOutDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, [$id]);
|
|
if(!$qry){
|
|
|
|
$this->sys_error_db("select stockoutdetail before get stock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$data_details = $qry->result_array();
|
|
//print_r($data_details);
|
|
|
|
//start mengeluarkan data dari stock
|
|
foreach ($data_details as $kdetail => $vdetail) {
|
|
$sql = "UPDATE stockoutdetail SET StockOutDetailQty = 0
|
|
WHERE
|
|
StockOutDetailID = ?";
|
|
$qry = $this->db_inventory->query($sql, [$vdetail['StockOutDetailID']]);
|
|
if(!$qry){
|
|
$this->sys_error_db("update QTY = 0 stockoutdetail", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
//print_r($vdetail);
|
|
$qty = intval($vdetail['StockOutDetailQty']);
|
|
//echo $qty;
|
|
if($qty > 0){
|
|
|
|
$qtyLoop = 0;
|
|
//start looping stock out sesuai qty yg dikirim
|
|
while($qtyLoop < $qty) {
|
|
$sql = "SELECT *
|
|
FROM stock
|
|
JOIN warehouse ON StockWarehouseID = WarehouseID AND
|
|
WarehouseID = ?
|
|
WHERE
|
|
StockItemID = ? AND
|
|
StockItemUnitID = ? AND
|
|
StockQty > 0
|
|
ORDER BY StockED ASC, StockStockNumber ASC
|
|
LIMIT 1";
|
|
$qry = $this->db_inventory->query($sql, [$param['warehouse']['id'],$vdetail['StockOutDetailItemID'],$vdetail['StockOutDetailItemUnitID']]);
|
|
if(!$qry){
|
|
$qtyLoop = $qty;
|
|
$this->sys_error_db("select stock error", $this->db_inventory->last_query());
|
|
exit;
|
|
|
|
}
|
|
|
|
if($qry->row_array()){
|
|
$data_stock = $qry->row_array();
|
|
$qtyOut = 0;
|
|
// echo $qty;
|
|
// echo $qtyLoop;
|
|
// echo $qty - $qtyLoop;
|
|
if(intval($data_stock['StockQty']) >= ($qty - $qtyLoop)){
|
|
$qtyOut = $qty - $qtyLoop;
|
|
|
|
}else{
|
|
$qtyOut = intval($data_stock['StockQty']);
|
|
}
|
|
$sql = "UPDATE stock SET StockQty = ?
|
|
WHERE
|
|
StockID = ? ";
|
|
$qry = $this->db_inventory->query($sql, [intval($data_stock['StockQty'])-$qtyOut,$data_stock['StockID']]);
|
|
if(!$qry){
|
|
$this->sys_error_db("stock out error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$arr_insert = array(
|
|
'StockOutDetailStockStockOutDetailID' => $vdetail['StockOutDetailID'],
|
|
'StockOutDetailStockItemID' => $vdetail['StockOutDetailItemID'],
|
|
'StockOutDetailStockItemUnitID' => $vdetail['StockOutDetailItemUnitID'],
|
|
'StockOutDetailStockBatchNo' => $data_stock['StockBatchNo'],
|
|
'StockOutDetailStockStockNumber' => $data_stock['StockStockNumber'],
|
|
'StockOutDetailStockQty' => $qtyOut,
|
|
'StockOutDetailStockUserID' => $userid,
|
|
'StockOutDetailStockCreated' => date("Y-m-d H:i:s"),
|
|
'StockOutDetailStockLastUpdated' => date("Y-m-d H:i:s")
|
|
);
|
|
$arr_insert_ed = $this->validateDate($data_stock['StockED'])?$data_stock['StockED']:NULL;
|
|
if($arr_insert_ed)
|
|
$arr_insert['StockOutDetailStockED'] = $data_stock['StockED'];
|
|
|
|
$qry = $this->db_inventory->insert('stockoutdetailstock', $arr_insert);
|
|
if(!$qry){
|
|
$this->sys_error_db("stockoutdetailstock insert error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$detailstock_id = $this->db_inventory->insert_id();
|
|
|
|
$sql = "UPDATE stockoutdetail SET StockOutDetailQty = StockOutDetailQty + ?
|
|
WHERE
|
|
StockOutDetailID = ?";
|
|
$qry = $this->db_inventory->query($sql, [$qtyOut,$vdetail['StockOutDetailID']]);
|
|
if(!$qry){
|
|
$this->sys_error_db("update QTY detail berdasarkan stock keluar error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$sql = "INSERT INTO stocklog (
|
|
StockLogWarehouseID,
|
|
StockLogWarehouseAlmariID,
|
|
StockLogWarehouseRackID,
|
|
StockLogDatetime,
|
|
StockLogItemID,
|
|
StockLogItemUnitID,
|
|
StockLogStockNumber,
|
|
StockLogBatchNo,
|
|
StockLogED,
|
|
StockLogReffID,
|
|
StockLogQty,
|
|
StockLogUserID,
|
|
StockLogStatus
|
|
)
|
|
VALUES(
|
|
?,?,?,NOW(),?,?,?,?,?,?,?,?,'SOT'
|
|
)";
|
|
$param_insert_stocklog = array(
|
|
$data_stock['StockWarehouseID'],
|
|
$data_stock['StockWarehouseAlmariID'],
|
|
$data_stock['StockWarehouseRackID'],
|
|
$data_stock['StockItemID'],
|
|
$data_stock['StockItemUnitID'],
|
|
$data_stock['StockStockNumber'],
|
|
$data_stock['StockBatchNo'],
|
|
$data_stock['StockLogED'],
|
|
$detailstock_id,
|
|
$qtyOut,
|
|
$userid
|
|
);
|
|
$qry = $this->db_inventory->query($sql, $param_insert_stocklog);
|
|
if(!$qry){
|
|
$this->sys_error_db("stocklog insert error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$batch_no = $data_stock['StockBatchNo'];
|
|
$ed = $this->validateDate($data_stock['StockLogED'])?$data_stock['StockLogED']:NULL;
|
|
$data_insert_stocklog = array(
|
|
'StockCardStatus' => 'SOT',
|
|
'StockCardDatetime' => date('Y-m-d H:i:s'),
|
|
'StockCardWarehouseID' => $data_stock['StockWarehouseID'],
|
|
'StockCardItemID' => $data_stock['StockItemID'],
|
|
'StockCardItemUnitID' => $data_stock['StockItemUnitID'],
|
|
'StockCardBatchNo' => $batch_no,
|
|
'StockCardReffID' => $detailstock_id,
|
|
'StockCardBefore' => $data_stock['StockQty'],
|
|
'StockCardIn' => 0,
|
|
'StockCardOut' => $qtyOut,
|
|
'StockCardAfter' => $data_stock['StockQty'] - $qtyOut,
|
|
'StockCardUserID' => $userid
|
|
);
|
|
if($ed)
|
|
$data_insert_stocklog['StockCardED'] = $ed;
|
|
|
|
$qry = $this->db_inventory->insert('stockcard',$data_insert_stocklog);
|
|
if(!$qry){
|
|
$this->sys_error_db("insert stockcard error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
//$qty = $qty - $qtyOut;
|
|
$qtyLoop = $qtyLoop + $qtyOut;
|
|
}
|
|
else{
|
|
$qtyLoop = $qty;
|
|
}
|
|
|
|
}
|
|
//end looping stock out sesuai qty yg dikeluarkan
|
|
}
|
|
}
|
|
//end mengeluarkan data dari stock
|
|
}
|
|
//end out
|
|
|
|
|
|
|
|
$sql = "SELECT stockinout.*, '' as details
|
|
FROM stockinout
|
|
WHERE StockInOutID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select stockinout error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$row_after = $qry->row_array();
|
|
|
|
if($param['selected_type_form']['id'] == 'IN'){
|
|
$sql = "SELECT *
|
|
FROM `stockindetailstock`
|
|
WHERE
|
|
StockInDetailStockStockInOutID = ? AND
|
|
StockInDetailStockIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if(!$qry){
|
|
$this->sys_error_db("select stockindetailstock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
if($param['selected_type_form']['id'] == 'OUT'){
|
|
$sql = "SELECT *
|
|
FROM `stockoutdetail`
|
|
WHERE
|
|
StockOutDetailStockInOutID = ? AND
|
|
StockOutDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if(!$qry){
|
|
$this->sys_error_db("select stockoutdetail error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$row_after['details'] = $qry->result_array();
|
|
$data_log_after = $row_after;
|
|
|
|
$sql = "INSERT INTO stockinout_log (
|
|
StockInOutLogStockInOutID,
|
|
StockInOutLogStatus,
|
|
StockInOutLogJSONBefore,
|
|
StockInOutLogJSONAfter,
|
|
StockInOutLogUserID,
|
|
StockInOutLogCreated
|
|
)
|
|
VALUES(
|
|
?,?,NULL,?,?,NOW()
|
|
)";
|
|
$qry = $this->db_inventory_log->query($sql, array($id,'CONFIRM',json_encode($data_log_after),$userid));
|
|
if(!$qry){
|
|
$this->sys_error_db("insert log error", $this->db_inventory_log->last_query());
|
|
exit;
|
|
}
|
|
|
|
$this->db_inventory->trans_complete();
|
|
|
|
|
|
$result = array(
|
|
"message" => 'confirm success'
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_data_stock(){
|
|
try {
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$prm = $this->sys_input;
|
|
|
|
$sql = "SELECT 'N' as selected,ItemID as item_id, ItemName as item_name,
|
|
ItemUnitID as unit_id, ItemUnitName as unit_name,
|
|
SUM(StockQty) as qty_stock, 0 as qty, 'N' is_exist
|
|
FROM stock
|
|
JOIN item ON StockItemID = ItemID
|
|
JOIN itemunit ON StockItemUnitID = ItemUnitID
|
|
WHERE
|
|
StockWarehouseID = ? AND StockQty > 0
|
|
GROUP BY ItemID, ItemUnitID
|
|
|
|
";
|
|
$query = $this->db_inventory->query($sql,[$prm['id']]);
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
foreach ($rows as $key => $value) {
|
|
$rows[$key]['selected'] = false;
|
|
}
|
|
//echo $this->db_onedev->last_query();
|
|
$result = array( "records" => $rows, "total_display" => sizeof($rows));
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("unit rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
}
|