992 lines
41 KiB
PHP
992 lines
41 KiB
PHP
<?php
|
|
class Item 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 get_data_x(){
|
|
echo 'ready';
|
|
}
|
|
|
|
function search()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$prm = $this->sys_input;
|
|
|
|
$search = "%%";
|
|
if (isset($prm['search'])) {
|
|
$search = trim($prm["search"]);
|
|
$search = '%' . $prm['search'] . '%';
|
|
}
|
|
$order_by = "ItemName";
|
|
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"]);
|
|
}
|
|
|
|
$filter_warehouse = "";
|
|
if (isset($prm['warehouse']['id'])) {
|
|
$filter_warehouse = " AND WarehouseID = {$prm['warehouse']['id']}";
|
|
}
|
|
$order = $order_by.' '.$order_type;
|
|
$perpage = 10;
|
|
$offset = ($prm['current_page'] - 1) * $perpage ;
|
|
|
|
$sql = "SELECT COUNT(*) as total FROM
|
|
(
|
|
SELECT
|
|
StockID as stock_id
|
|
FROM `stock`
|
|
JOIN `item` ON StockItemID = ItemID AND ( ItemName like ? OR ItemSKU like ?)
|
|
JOIN `itemunit` ON StockItemUnitID = ItemUnitID
|
|
JOIN `warehousealmari` ON StockWarehouseAlmariID = WarehouseAlmariID
|
|
JOIN `warehouserack` ON StockWarehouseRackID = WarehouseRackID
|
|
JOIN `warehouse` ON StockWarehouseID = WarehouseID AND WarehouseID = ?
|
|
WHERE
|
|
StockQty > 0
|
|
) x";
|
|
$qry = $this->db_inventory->query($sql,array($search, $search, $prm['warehouse']['id']));
|
|
$tot_count = 0;
|
|
$tot_page = 0;
|
|
if ($qry) {
|
|
$tot_count = $qry->row()->total;
|
|
$tot_page = ceil($tot_count/$perpage);
|
|
} else {
|
|
$this->sys_error_db("supplier count error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$rows = array();
|
|
$sql = "SELECT ItemID as item_id,
|
|
ItemName as item_name,
|
|
ItemUnitID as unit_id,
|
|
ItemUnitName as unit_name,
|
|
StockID as stock_id,
|
|
StockStockNumber as stock_number,
|
|
StockBatchNo as batch_no,
|
|
IF(StockED IS NULL,'',DATE_FORMAT(StockED, '%d-%m-%Y') ) as ed,
|
|
StockED as stock_ed,
|
|
StockQty as qty_stock,
|
|
0 as qty,
|
|
WarehouseAlmariName as almari_name,
|
|
StockWarehouseAlmariID as almari_id,
|
|
WarehouseRackName as rack_name,
|
|
WarehouseRackID as rack_id,
|
|
WarehouseName as warehouse_name
|
|
FROM `stock`
|
|
JOIN `item` ON StockItemID = ItemID AND ( ItemName like ? OR ItemSKU like ?)
|
|
JOIN `itemunit` ON StockItemUnitID = ItemUnitID
|
|
JOIN `warehousealmari` ON StockWarehouseAlmariID = WarehouseAlmariID
|
|
JOIN `warehouserack` ON StockWarehouseRackID = WarehouseRackID
|
|
JOIN `warehouse` ON StockWarehouseID = WarehouseID AND WarehouseID = ?
|
|
WHERE
|
|
StockQty > 0
|
|
ORDER BY ?
|
|
LIMIT ? OFFSET ?";
|
|
$qry = $this->db_inventory->query($sql, array($search, $search, $prm['warehouse']['id'],$order, $perpage, $offset));
|
|
//echo $this->db_inventory->last_query();
|
|
if($qry){
|
|
$rows = $qry->result_array();
|
|
|
|
|
|
}else {
|
|
$this->sys_error_db("supplier data error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
|
|
$result = array("total_page" => $tot_page, "records" => $rows);
|
|
$this->sys_ok($result);
|
|
exit;
|
|
|
|
} 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 save(){
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$userid = $this->sys_user['M_UserID'];
|
|
$prm = $this->sys_input;
|
|
|
|
$this->db_inventory->trans_start();
|
|
$this->db_inventory->trans_strict(FALSE);
|
|
|
|
$numbering = '';
|
|
$sql = "SELECT `fn_numbering`('CU') 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;
|
|
}
|
|
|
|
$arr_insert_detail = array(
|
|
'StockConversionNumber' => $numbering,
|
|
'StockConversionWarehouseID' => $prm['warehouse']['id'],
|
|
'StockConversionWarehouseAlmariiD' => $prm['selected_item']['almari_id'],
|
|
'StockConversionWarehouseRackID' => $prm['selected_item']['rack_id'],
|
|
'StockConversionItemID' => $prm['selected_item']['item_id'],
|
|
'StockConversionItemUnitID' => $prm['selected_item']['unit_id'],
|
|
'StockConversionStockNumber' => $prm['selected_item']['stock_number'],
|
|
'StockConversionBatchNo' => $prm['selected_item']['batch_no'],
|
|
'StockConversionToItemUnitID' => $prm['selected_unit_conversion']['to_unit_id'],
|
|
'StockConversionAmount' => $prm['selected_unit_conversion']['amount'],
|
|
'StockConversionQty' => $prm['qty_conversi'],
|
|
'StockConversionQtyStock' => $prm['selected_item']['qty_stock'],
|
|
'StockConversionUserID' => $userid,
|
|
'StockConversionCreated' => date("Y-m-d H:i:s"),
|
|
'StockConversionLastUpdated' => date("Y-m-d H:i:s")
|
|
);
|
|
$valid_ed = $this->validateDate($prm['selected_item']['stock_ed'])?$prm['selected_item']['stock_ed']:NULL;
|
|
if($valid_ed && $prm['selected_item']['stock_ed'] != '0000-00-00')
|
|
$arr_insert_detail['StockConversionED'] = $prm['selected_item']['stock_ed'];
|
|
|
|
$qry = $this->db_inventory->insert('stockconversion', $arr_insert_detail);
|
|
if($qry){
|
|
$last_id = $this->db_inventory->insert_id();
|
|
}
|
|
else{
|
|
$this->sys_error_db("save stockconversion error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
//start stockcard out
|
|
$sql = "SELECT *
|
|
FROM stock
|
|
WHERE
|
|
StockID = ?";
|
|
$qry = $this->db_inventory->query($sql, array(
|
|
$prm['selected_item']['stock_id']
|
|
));
|
|
if(!$qry){
|
|
$this->sys_error_db("select stock error", $this->db_inventory->last_query());
|
|
exit;
|
|
|
|
}
|
|
|
|
$data_last_stock = $qry->row_array();
|
|
$last_stock = $data_last_stock['StockQty'];
|
|
|
|
$value = $prm['selected_item'];
|
|
$warehouse_id = $prm['warehouse']['id'];
|
|
$arr_insert_stockcard = array(
|
|
'StockCardWarehouseID' => $warehouse_id,
|
|
'StockCardItemID' => $value['item_id'],
|
|
'StockCardItemUnitID' => $value['unit_id'],
|
|
'StockCardBatchNo' => $value['batch_no'],
|
|
'StockCardBefore' => $last_stock,
|
|
'StockCardIn' => 0,
|
|
'StockCardOut' => $prm['qty_conversi'],
|
|
'StockCardAfter' => $last_stock-intval($prm['qty_conversi']),
|
|
'StockCardDatetime' => date("Y-m-d H:i:s"),
|
|
'StockCardUserID' => $userid,
|
|
'StockCardStatus' => 'CUO'
|
|
);
|
|
|
|
$valid_ed = $this->validateDate($data_last_stock['StockED'])?$data_last_stock['StockED']:NULL;
|
|
if($valid_ed && $data_last_stock['StockED'] != '0000-00-00')
|
|
$arr_insert_stockcard['StockCardED'] = $data_last_stock['StockED'];
|
|
|
|
$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 stockcard out
|
|
|
|
// start update stock out
|
|
$sql = "UPDATE stock SET StockQty = StockQty - {$prm['qty_conversi']}
|
|
WHERE
|
|
StockID = ?";
|
|
$qry = $this->db_inventory->query($sql, array(
|
|
$prm['selected_item']['stock_id']
|
|
));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("update stock error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
// end update stock out
|
|
|
|
//start stockcard in
|
|
$sql = "SELECT *
|
|
FROM stock
|
|
WHERE
|
|
StockWarehouseID = ? AND
|
|
StockWarehouseAlmariID = ? AND
|
|
StockWarehouseRackID = ? AND
|
|
StockStockNumber = ? AND
|
|
StockItemID = ? AND
|
|
StockItemUnitID = ? AND
|
|
StockBatchNo = ? AND
|
|
StockED = ?
|
|
";
|
|
$qry = $this->db_inventory->query($sql, array(
|
|
$prm['warehouse']['id'],
|
|
$value['almari_id'],
|
|
$value['rack_id'],
|
|
$value['stock_number'],
|
|
$value['item_id'],
|
|
$prm['selected_unit_conversion']['to_unit_id'],
|
|
$value['batch_no'],
|
|
$value['stock_ed']
|
|
));
|
|
if(!$qry){
|
|
$this->sys_error_db("select stock error", $this->db_inventory->last_query());
|
|
exit;
|
|
|
|
}
|
|
//echo $this->db_inventory->last_query();
|
|
|
|
$data_last_stock = $qry->row_array();
|
|
//print_r($data_last_stock);
|
|
if($data_last_stock )
|
|
$last_stock = $data_last_stock['StockQty'];
|
|
else
|
|
$last_stock = 0;
|
|
|
|
$qty_in = intval($prm['qty_conversi']) * intval($prm['selected_unit_conversion']['amount']);
|
|
$qty_after = $last_stock + $qty_in ;
|
|
|
|
$arr_insert_stockcard = array(
|
|
'StockCardWarehouseID' => $warehouse_id,
|
|
'StockCardItemID' => $value['item_id'],
|
|
'StockCardItemUnitID' => $prm['selected_unit_conversion']['to_unit_id'],
|
|
'StockCardBatchNo' => $value['batch_no'],
|
|
'StockCardBefore' => $last_stock,
|
|
'StockCardIn' => $qty_in,
|
|
'StockCardOut' => 0,
|
|
'StockCardAfter' => $qty_after,
|
|
'StockCardDatetime' => date("Y-m-d H:i:s"),
|
|
'StockCardUserID' => $userid,
|
|
'StockCardStatus' => 'CUI'
|
|
);
|
|
|
|
$valid_ed = $this->validateDate($value['stock_ed'])?$value['stock_ed']:NULL;
|
|
if($valid_ed && $value['stock_ed'] != '0000-00-00')
|
|
$arr_insert_stockcard['StockCardED'] = $value['stock_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 stockcard in
|
|
|
|
// start update stock in
|
|
if($data_last_stock){
|
|
$sql = "UPDATE stock SET StockQty = StockQty + {$qty_in}
|
|
WHERE
|
|
StockID = ?";
|
|
$qry = $this->db_inventory->query($sql, array(
|
|
$data_last_stock['StockID']
|
|
));
|
|
//echo $this->db_inventory->last_query();
|
|
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' => $warehouse_id,
|
|
'StockWarehouseAlmariID' => $value['almari_id'],
|
|
'StockWarehouseRackID' => $value['rack_id'],
|
|
'StockItemID' => $value['item_id'],
|
|
'StockItemUnitID' => $prm['selected_unit_conversion']['to_unit_id'],
|
|
'StockBatchNo' => $value['batch_no'],
|
|
'StockQty' => $qty_in,
|
|
'StockLastUpdated' => date("Y-m-d H:i:s"),
|
|
'StockUserID' => $userid,
|
|
'StockStockNumber' => $value['stock_number']
|
|
);
|
|
|
|
$valid_ed = $this->validateDate($value['stock_ed'])?$value['stock_ed']:NULL;
|
|
if($valid_ed && $value['stock_ed'] != '0000-00-00')
|
|
$arr_insert_stock['StockED'] = $value['stock_ed'];
|
|
|
|
$qry = $this->db_inventory->insert('stock', $arr_insert_stock);
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("insert stockcard error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
// end update stock in
|
|
|
|
//start insert stocklog
|
|
$arr_insert_stock = array(
|
|
'StockLogWarehouseID' => $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' => $prm['qty_conversi'],
|
|
'StockLogDatetime' => date("Y-m-d H:i:s"),
|
|
'StockLogUserID' => $userid,
|
|
'StockLogReffID' => $last_id,
|
|
'StockLogStatus' => 'CUO'
|
|
);
|
|
|
|
|
|
if($valid_ed && $value['stock_ed'] != '0000-00-00')
|
|
$arr_insert_stock['StockLogED'] = $value['stock_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;
|
|
}
|
|
|
|
$arr_insert_stock = array(
|
|
'StockLogWarehouseID' => $warehouse_id,
|
|
'StockLogWarehouseAlmariID' => $value['almari_id'],
|
|
'StockLogWarehouseRackID' => $value['rack_id'],
|
|
'StockLogStockNumber' => $value['stock_number'],
|
|
'StockLogItemID' => $value['item_id'],
|
|
'StockLogItemUnitID' => $prm['selected_unit_conversion']['to_unit_id'],
|
|
'StockLogBatchNo' => $value['batch_no'],
|
|
'StockLogQty' => $qty_in,
|
|
'StockLogDatetime' => date("Y-m-d H:i:s"),
|
|
'StockLogUserID' => $userid,
|
|
'StockLogReffID' => $last_id,
|
|
'StockLogStatus' => 'CUI'
|
|
);
|
|
|
|
|
|
if($valid_ed && $value['stock_ed'] != '0000-00-00')
|
|
$arr_insert_stock['StockLogED'] = $value['stock_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
|
|
|
|
$this->db_inventory->trans_complete();
|
|
|
|
$result = array(
|
|
"message" => ''
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function edit(){
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$userid = $this->sys_user['M_UserID'];
|
|
$prm = $this->sys_input;
|
|
|
|
$name = "";
|
|
if (isset($prm['name'])) {
|
|
$name = trim($prm["name"]);
|
|
}
|
|
$phone = "";
|
|
if (isset($prm['phone'])) {
|
|
$phone = trim($prm["phone"]);
|
|
}
|
|
$email = "";
|
|
if (isset($prm['email'])) {
|
|
$email = trim($prm["email"]);
|
|
}
|
|
$website = "";
|
|
if (isset($prm['website'])) {
|
|
$website = trim($prm["website"]);
|
|
}
|
|
|
|
$id = $prm['id'];
|
|
|
|
$sql = "SELECT supplier.*, '' as address, '' as contact
|
|
FROM supplier
|
|
WHERE SupplierID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select supplier error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$row = $qry->row_array();
|
|
$sql = "SELECT *
|
|
FROM `supplieraddress`
|
|
WHERE
|
|
SupplierAddressSupplierID = ? AND
|
|
SupplierAddressIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row['address'] = $qry->result_array();
|
|
}else{
|
|
$this->sys_error_db("select supplier address error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT *
|
|
FROM `suppliercontactperson`
|
|
WHERE
|
|
SupplierContactPersonSupplierID = ? AND
|
|
SupplierContactPersonIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row['contact'] = $qry->result_array();
|
|
}
|
|
else{
|
|
$this->sys_error_db("select supplier contact error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$data_log_before = $row;
|
|
|
|
$this->db_inventory->trans_start();
|
|
$this->db_inventory->trans_strict(FALSE);
|
|
|
|
$last_id = 0;
|
|
$sql = "UPDATE supplier SET
|
|
SupplierName = ?,
|
|
SupplierPhone = ?,
|
|
SupplierEmail = ?,
|
|
SupplierWebsite = ?,
|
|
SupplierLastUpdated = NOW(),
|
|
SupplierUserID = ?
|
|
WHERE
|
|
SupplierID = ?";
|
|
$qry = $this->db_inventory->query($sql, array($name,$phone,$email,$website,$userid,$prm['id']));
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("edit supplier error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$sql = "UPDATE supplieraddress SET SupplierAddressIsActive = 'N' WHERE SupplierAddressSupplierID = ?";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("update N address error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
if(count($prm['address'])>0){
|
|
foreach ($prm['address'] as $key => $value) {
|
|
$type = trim($value['type']);
|
|
$postcode = trim($value['postcode']);
|
|
$description = trim($value['description']);
|
|
$note = trim($value['note']);
|
|
if(intval($value['id']) > 0){
|
|
$sql = "UPDATE supplieraddress SET
|
|
SupplierAddressType = ?,
|
|
SupplierAddressPostCode = ?,
|
|
SupplierAddressDescription = ?,
|
|
SupplierAddressNote = ?,
|
|
SupplierAddressUserID = ?,
|
|
SupplierAddressIsActive = 'Y',
|
|
SupplierAddressLastUpdated = NOW()
|
|
WHERE
|
|
SupplierAddressID = ?";
|
|
$qry = $this->db_inventory->query($sql, array( $type, $postcode, $description, $note, $userid, $value['id']));
|
|
if(!$qry){
|
|
echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("edit supplier address error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}else{
|
|
$sql = "INSERT INTO supplieraddress(
|
|
SupplierAddressSupplierID,
|
|
SupplierAddressType,
|
|
SupplierAddressPostCode,
|
|
SupplierAddressDescription,
|
|
SupplierAddressNote,
|
|
SupplierAddressUserID,
|
|
SupplierAddressCreated,
|
|
SupplierAddressLastUpdated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,?,NOW(),NOW()
|
|
)";
|
|
$qry = $this->db_inventory->query($sql, array($prm['id'], $type, $postcode, $description, $note, $userid));
|
|
if(!$qry){
|
|
$this->sys_error_db("save supplier address error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
$sql = "UPDATE suppliercontactperson SET SupplierContactPersonIsActive = 'N' WHERE SupplierContactPersonSupplierID = ?";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("update N contact error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
if(count($prm['contact'])>0){
|
|
foreach ($prm['contact'] as $key => $value) {
|
|
$name = trim($value['name']);
|
|
$email = trim($value['email']);
|
|
$mobile = trim($value['mobile']);
|
|
|
|
if(intval($value['id']) > 0){
|
|
$sql = "UPDATE suppliercontactperson SET
|
|
SupplierContactPersonName = ?,
|
|
SupplierContactPersonEmail = ?,
|
|
SupplierContactPersonMobile = ?,
|
|
SupplierContactPersonLastUpdated = NOW(),
|
|
SupplierContactPersonIsActive = 'Y',
|
|
SupplierContactPersonUserID = ?
|
|
WHERE
|
|
SupplierContactPersonID = ?";
|
|
$qry = $this->db_inventory->query($sql, array($name, $email, $mobile, $userid, $value['id']));
|
|
if(!$qry){
|
|
$this->sys_error_db("save supplier contact error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}else{
|
|
$sql = "INSERT INTO suppliercontactperson(
|
|
SupplierContactPersonSupplierID,
|
|
SupplierContactPersonName,
|
|
SupplierContactPersonEmail,
|
|
SupplierContactPersonMobile,
|
|
SupplierContactPersonUserID,
|
|
SupplierContactPersonLastUpdated,
|
|
SupplierContactPersonCreated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,NOW(),NOW()
|
|
)";
|
|
$qry = $this->db_inventory->query($sql, array($prm['id'], $name, $email, $mobile, $userid));
|
|
if(!$qry){
|
|
echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("save supplier contact error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
$this->db_inventory->trans_complete();
|
|
|
|
$sql = "SELECT supplier.*, '' as address, '' as contact
|
|
FROM supplier
|
|
WHERE SupplierID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select supplier error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$row_after = $qry->row_array();
|
|
$sql = "SELECT *
|
|
FROM `supplieraddress`
|
|
WHERE
|
|
SupplierAddressSupplierID = ? AND
|
|
SupplierAddressIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row_after['address'] = $qry->result_array();
|
|
}else{
|
|
$this->sys_error_db("select supplier address error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT *
|
|
FROM `suppliercontactperson`
|
|
WHERE
|
|
SupplierContactPersonSupplierID = ? AND
|
|
SupplierContactPersonIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row_after['contact'] = $qry->result_array();
|
|
}
|
|
else{
|
|
$this->sys_error_db("select supplier contact error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$data_log_after = $row_after;
|
|
|
|
|
|
$sql = "INSERT INTO supplier_log (
|
|
SupplierLogSupplierID,
|
|
SupplierLogStatus,
|
|
SupplierLogJSONBefore,
|
|
SupplierLogJSONAfter,
|
|
SupplierLogUserID,
|
|
SupplierLogCreated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,NOW()
|
|
)";
|
|
$qry = $this->db_inventory_log->query($sql, array($prm['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;
|
|
}
|
|
|
|
$result = array(
|
|
"message" => ''
|
|
);
|
|
$this->sys_ok($result);
|
|
} 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 supplier
|
|
SET SupplierIsActive = 'N',
|
|
SupplierLastUpdated = now(),
|
|
SupplierUserID = ?
|
|
WHERE SupplierID = ?
|
|
";
|
|
$qry = $this->db_inventory->query($sql, [$userid,$id]);
|
|
if(!$qry){
|
|
|
|
$this->sys_error_db("delete supplier error", $this->db_inventory->last_query());
|
|
exit;
|
|
}else{
|
|
$sql = "UPDATE supplieraddress
|
|
SET SupplierAddressIsActive = 'N',
|
|
SupplierAddressLastUpdated = now(),
|
|
SupplierAddressUserID = ?
|
|
WHERE
|
|
SupplierAddressSupplierID = ?
|
|
";
|
|
$qry = $this->db_inventory->query($sql, [$userid,$id]);
|
|
if(!$qry){
|
|
$this->sys_error_db("delete supplier address error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$sql = "UPDATE suppliercontactperson
|
|
SET SupplierContactPersonIsActive = 'N',
|
|
SupplierContactPersonLastUpdated = now(),
|
|
SupplierContactPersonUserID = ?
|
|
WHERE
|
|
SupplierContactPersonSupplierID = ?
|
|
";
|
|
$qry = $this->db_inventory->query($sql, [$userid,$id]);
|
|
if(!$qry){
|
|
$this->sys_error_db("delete supplier contact error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
$this->db_inventory->trans_complete();
|
|
|
|
$sql = "SELECT supplier.*, '' as address, '' as contact
|
|
FROM supplier
|
|
WHERE SupplierID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select supplier error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$row_after = $qry->row_array();
|
|
$sql = "SELECT *
|
|
FROM `supplieraddress`
|
|
WHERE
|
|
SupplierAddressSupplierID = ? AND
|
|
SupplierAddressIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row_after['address'] = $qry->result_array();
|
|
}else{
|
|
$this->sys_error_db("select supplier address error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT *
|
|
FROM `suppliercontactperson`
|
|
WHERE
|
|
SupplierContactPersonSupplierID = ? AND
|
|
SupplierContactPersonIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row_after['contact'] = $qry->result_array();
|
|
}
|
|
else{
|
|
$this->sys_error_db("select supplier contact error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$data_log_after = $row_after;
|
|
|
|
|
|
$sql = "INSERT INTO supplier_log (
|
|
SupplierLogSupplierID,
|
|
SupplierLogStatus,
|
|
SupplierLogJSONBefore,
|
|
SupplierLogJSONAfter,
|
|
SupplierLogUserID,
|
|
SupplierLogCreated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,NOW()
|
|
)";
|
|
$qry = $this->db_inventory_log->query($sql, array($id,'DELETE',NULL,json_encode($data_log_after),$userid));
|
|
if(!$qry){
|
|
$this->sys_error_db("insert log error", $this->db_inventory_log->last_query());
|
|
exit;
|
|
}
|
|
$result = array(
|
|
"message" => ''
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_companies(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT CompanyID as id,
|
|
CompanyName as name,
|
|
M_BranchName as description
|
|
FROM company
|
|
JOIN $this->db_onex.m_branch ON CompanyM_BranchID = M_BranchID
|
|
WHERE
|
|
CompanyIsActive = 'Y'";
|
|
$query = $this->db_inventory->query($sql);
|
|
|
|
if ($query) {
|
|
$row = $query->result_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_warehouses(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT WarehouseID as id,
|
|
warehouseName as name
|
|
FROM warehouse
|
|
WHERE
|
|
WarehouseIsActive = 'Y'";
|
|
$query = $this->db_inventory->query($sql);
|
|
|
|
if ($query) {
|
|
$row = $query->result_array();
|
|
|
|
$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 select_item()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$userid = $this->sys_user['M_UserID'];
|
|
$prm = $this->sys_input;
|
|
$sql = "UPDATE itembranch SET ItemBranchIsActive = 'N', ItemBranchUserID = ?,ItemBranchLastUpdated = NOW()
|
|
WHERE
|
|
ItemBranchItemID = ? AND ItemBranchM_CompanyID = ? AND ItemBranchIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql,array($userid,$prm['item_id'],$prm['company']['id']));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("update is active exist error", $this->db_inventory_log->last_query());
|
|
exit;
|
|
}
|
|
$data = array(
|
|
'ItemBranchM_CompanyID' => $prm['company']['id'] ,
|
|
'ItemBranchItemID' =>$prm['item_id'],
|
|
'ItemBranchCreated' =>date("Y-m-d H:i:s"),
|
|
'ItemBranchLastUpdated' =>date("Y-m-d H:i:s"),
|
|
'ItemBranchUserID' => $userid
|
|
);
|
|
|
|
$save = $this->db_inventory->insert('itembranch', $data);
|
|
if(!$save){
|
|
$this->sys_error_db("save failed", $this->db_inventory_log->last_query());
|
|
exit;
|
|
}
|
|
$result = array(
|
|
"message" => 'save success'
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function unselected_item()
|
|
{
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$userid = $this->sys_user['M_UserID'];
|
|
$prm = $this->sys_input;
|
|
$sql = "UPDATE itembranch SET ItemBranchIsActive = 'N', ItemBranchUserID = ?,ItemBranchLastUpdated = NOW()
|
|
WHERE
|
|
ItemBranchItemID = ? AND ItemBranchM_CompanyID = ? AND ItemBranchIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql,array($userid,$prm['item_id'],$prm['company']['id']));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("update is active exist error", $this->db_inventory_log->last_query());
|
|
exit;
|
|
}
|
|
|
|
$result = array(
|
|
"message" => 'save success'
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_data_conversion(){
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$userid = $this->sys_user['M_UserID'];
|
|
$prm = $this->sys_input;
|
|
$sql = "SELECT ItemUnitConvertID as id,
|
|
fromunit.ItemUnitID as from_unit_id,
|
|
fromunit.ItemUnitName as from_unit_name,
|
|
tounit.ItemUnitID as to_unit_id,
|
|
tounit.ItemUnitName as to_unit_name,
|
|
ItemUnitConvertAmount as amount
|
|
FROM `itemunitconvert`
|
|
JOIN itemunit fromunit ON ItemUnitConvertFromItemUnitID = fromunit.ItemUnitID
|
|
JOIN itemunit tounit ON ItemUnitConvertToItemUnitID = tounit.ItemUnitID
|
|
WHERE
|
|
ItemUnitConvertIsActive = 'Y' AND
|
|
ItemUnitConvertItemID = ? AND
|
|
ItemUnitConvertFromItemUnitID = ?";
|
|
$qry = $this->db_inventory->query($sql,array($prm['item_id'],$prm['unit_id']));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("update is active exist error", $this->db_inventory_log->last_query());
|
|
exit;
|
|
}
|
|
$rows = $qry->result_array();
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
}
|