391 lines
16 KiB
PHP
391 lines
16 KiB
PHP
<?php
|
|
class Stock 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'] . '%';
|
|
}
|
|
$start_date = date("Y-m-d");
|
|
if (isset($prm['start_date'])) {
|
|
$start_date = $prm["start_date"];
|
|
}
|
|
$end_date = date("Y-m-d");
|
|
if (isset($prm['end_date'])) {
|
|
$end_date = $prm["end_date"];
|
|
}
|
|
$filter_warehouse = '';
|
|
if (isset($prm['warehouse_id']) && intval($prm['warehouse_id']) > 0) {
|
|
$filter_warehouse = ' AND WarehouseID = '.$prm['warehouse_id'];
|
|
}
|
|
|
|
$order_by = "StockID";
|
|
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 MutasiRequestReceiveID
|
|
FROM `mutasirequestreceive`
|
|
JOIN `companyaddress` ON MutasiRequestReceiveCompanyAddressID = CompanyAddressID
|
|
JOIN `company` ON CompanyAddressCompanyID = CompanyID
|
|
JOIN $this->db_onex.`m_branch` ON CompanyM_BranchID = M_BranchID $filter_branch
|
|
JOIN `item` ON MutasiRequestReceiveItemID = ItemID
|
|
JOIN `itemunit` ON MutasiRequestReceiveItemUnitID = ItemUnitID
|
|
WHERE
|
|
MutasiRequestReceiveIsActive = 'Y' AND (MutasiRequestReceiveDate BETWEEN ? AND ?) $filter_status
|
|
) x";
|
|
$qry = $this->db_inventory->query($sql,array($start_date, $end_date));
|
|
$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 MutasiRequestReceiveID as id,
|
|
MutasiRequestReceiveCompanyAddressID as company_address_id,
|
|
M_BranchID as branch_id,
|
|
M_BranchName as branch_name,
|
|
MutasiRequestReceiveEstimatedDelivery as estimated_delivery,
|
|
MutasiRequestReceiveDate as request_date,
|
|
MutasiRequestReceiveNote as request_note,
|
|
MutasiRequestReceiveSenderUsername as sender_name,
|
|
MutasiRequestReceiveSentAt as sent_at,
|
|
MutasiRequestReceiveStatus as request_status,
|
|
MutasiRequestReceiveCompleteNote as complete_note,
|
|
MutasiRequestReceiveRequestQty as request_qty,
|
|
MutasiRequestReceiveReceiveQty as receive_qty,
|
|
MutasiRequestReceiveItemID as item_id,
|
|
ItemName as item_name,
|
|
MutasiRequestReceiveItemUnitID as unit_id,
|
|
ItemUnitName as unit_name,
|
|
MutasiRequestReceiveStatus as request_status
|
|
FROM `mutasirequestreceive`
|
|
JOIN `companyaddress` ON MutasiRequestReceiveCompanyAddressID = CompanyAddressID
|
|
JOIN `company` ON CompanyAddressCompanyID = CompanyID
|
|
JOIN $this->db_onex.`m_branch` ON CompanyM_BranchID = M_BranchID $filter_branch
|
|
JOIN `item` ON MutasiRequestReceiveItemID = ItemID
|
|
JOIN `itemunit` ON MutasiRequestReceiveItemUnitID = ItemUnitID
|
|
WHERE
|
|
MutasiRequestReceiveIsActive = 'Y' AND (MutasiRequestReceiveDate BETWEEN ? AND ?) $filter_status
|
|
ORDER BY $order_by $order_type
|
|
LIMIT ? OFFSET ?";
|
|
$qry = $this->db_inventory->query($sql, array($start_date, $end_date, $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("request select error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_terms(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$prm = $this->sys_input;
|
|
$id = 0;
|
|
if(isset($prm['id']) && intval($prm) > 0){
|
|
$id = $prm['id'];
|
|
$sql = "SELECT SupplierPaymentTermsID as id,
|
|
SupplierPaymentTermsCode as code,
|
|
SupplierPaymentTermsName as name,
|
|
SupplierPaymentTermsDescription as description,
|
|
DATE_FORMAT(SupplierPaymentTermsStartDate,'%d-%m-%Y') as start_date,
|
|
DATE_FORMAT(SupplierPaymentTermsEndDate,'%d-%m-%Y') as end_date
|
|
FROM supplierpaymentterms
|
|
WHERE
|
|
SupplierPaymentTermsSupplierID = ? AND SupplierPaymentTermsIsActive = 'Y'";
|
|
$query = $this->db_inventory->query($sql,$id);
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("supplierpaymentterms rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
}
|
|
else{
|
|
$this->sys_error("Invalid Param ID");
|
|
exit;
|
|
}
|
|
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_stock(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$prm = $this->sys_input;
|
|
|
|
$sql = "SELECT StockID as idx,
|
|
ItemID as item_id,
|
|
ItemName as item_name,
|
|
ItemUnitID as unit_id,
|
|
ItemUnitName as unit_name,
|
|
WarehouseName as warehouse_name,
|
|
SUM(StockQty) as qty
|
|
FROM stock
|
|
JOIN item ON StockItemID = ItemID AND StockItemID = ItemID
|
|
JOIN itemunit ON StockItemUnitID = ItemUnitID
|
|
JOIN warehouse ON StockWarehouseID = WarehouseID
|
|
GROUP BY StockWarehouseID, StockItemID
|
|
";
|
|
$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("stock rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
|
|
|
|
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_stock_branch(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$prm = $this->sys_input;
|
|
|
|
$sql = "SELECT StockItemID as item_id, StockItemUnitID as unit_id
|
|
FROM stock
|
|
JOIN mutasirequestreceive ON MutasiRequestReceiveItemID = StockItemID AND
|
|
MutasiRequestReceiveStatus NOT IN('N','D','C') AND MutasiRequestReceiveIsActive = 'Y'
|
|
JOIN item ON MutasiRequestReceiveItemID = ItemID AND StockItemID = ItemID
|
|
JOIN itemunit ON StockItemUnitID = ItemUnitID
|
|
JOIN warehouse ON StockWarehouseID = WarehouseID AND WarehouseIsOffice = 'Y'
|
|
GROUP BY StockWarehouseID, StockItemID
|
|
";
|
|
$query = $this->db_inventory->query($sql);
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
$items = [];
|
|
$units = [];
|
|
foreach ($rows as $key => $value) {
|
|
array_push($items,$value['item_id']);
|
|
array_push($units,$value['unit_id']);
|
|
}
|
|
$string_items = join(",",array_unique($items));
|
|
$string_units = join(",",array_unique($units));
|
|
|
|
$config['hostname'] = $prm['branch_ip'];
|
|
$config['username'] = 'root';
|
|
$config['password'] = 'sasone102938';
|
|
$config['database'] = 'one_inventory';
|
|
$config['dbdriver'] = 'mysqli';
|
|
$config['autoinit'] = FALSE;
|
|
$config['dbprefix'] = '';
|
|
$config['pconnect'] = FALSE;
|
|
$config['db_debug'] = TRUE;
|
|
$config['cache_on'] = FALSE;
|
|
$config['cachedir'] = '';
|
|
$config['char_set'] = 'utf8';
|
|
$config['dbcollat'] = 'utf8_general_ci';
|
|
$db_to = $this->load->database($config,TRUE);
|
|
//if(!$db_to)
|
|
//echo "error connection ".$v['branch_ip'];
|
|
$connected = $db_to->initialize();
|
|
if (!$connected) {
|
|
$db_to = $this->load->database($config,TRUE);
|
|
}
|
|
|
|
//print_r($db_to);
|
|
$sql = "SELECT StockID as idx,ItemID as item_id, ItemName as item_name, ItemUnitID as unit_id, ItemUnitName as unit_name, WarehouseName as warehouse_name,
|
|
SUM(StockQty) as qty
|
|
FROM stock
|
|
JOIN item ON StockItemID = ItemID AND ItemID IN ({$string_items})
|
|
JOIN itemunit ON StockItemUnitID = ItemUnitID AND ItemUnitID IN ({$string_units})
|
|
JOIN warehouse ON StockWarehouseID = WarehouseID AND WarehouseIsOffice = 'Y'
|
|
WHERE
|
|
StockQty > 0
|
|
GROUP BY StockWarehouseID, StockItemID
|
|
";
|
|
//echo $sql;
|
|
$query = $db_to->query($sql);
|
|
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("stock rows", $db_to->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
else {
|
|
$this->sys_error_db("stock rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_term_detail(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$prm = $this->sys_input;
|
|
$id = 0;
|
|
if(isset($prm['id']) && intval($prm) > 0){
|
|
$id = $prm['id'];
|
|
$sql = "SELECT SupplierPaymentTermsDetailID as id,
|
|
SupplierPaymentTermsDetailItemID as item_id,
|
|
ItemName as item_name,
|
|
CONCAT(ItemName,' : ',ItemUnitName) as itemunit_name,
|
|
SupplierPaymentTermsDetailItemUnitID as unit_id,
|
|
ItemUnitName as unit_name,
|
|
0 as qty,
|
|
'N' as is_exist,
|
|
SupplierPaymentTermsDetailPrice as price,
|
|
SupplierPaymentTermsDetailDiscount as discount_percent,
|
|
SupplierPaymentTermsDetailDiscountRp as discount_rp,
|
|
SupplierPaymentTermsDetailTotal as total,
|
|
'N' as selected
|
|
FROM supplierpaymenttermsdetail
|
|
JOIN item ON SupplierPaymentTermsDetailItemID = ItemID AND ItemIsActive = 'Y'
|
|
JOIN itemunit ON SupplierPaymentTermsDetailItemUnitID = ItemUnitID AND ItemUnitIsActive = 'Y'
|
|
WHERE
|
|
SupplierPaymentTermsSupplierPaymentTermsID = ? AND
|
|
SupplierPaymentTermsDetailIsActive = 'Y'";
|
|
$query = $this->db_inventory->query($sql,$id);
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
if($rows && count($rows) > 0){
|
|
foreach ($rows as $key => $value) {
|
|
$rows[$key]['selected'] = false;
|
|
}
|
|
}
|
|
$result = array("records" => $rows);
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("supplierpaymenttermsdetail rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
}
|
|
else{
|
|
$this->sys_error("Invalid Param ID");
|
|
exit;
|
|
}
|
|
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function get_branches(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT CompanyID as id,
|
|
CompanyName as name,
|
|
M_BranchID as branch_id,
|
|
M_BranchName as branch_name,
|
|
M_BranchIPAddress as branch_ip
|
|
FROM company
|
|
JOIN $this->db_onex.m_branch ON CompanyM_BranchID = M_BranchID
|
|
WHERE
|
|
CompanyIsActive = 'Y' AND CompanyIsCenter = 'N'";
|
|
$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);
|
|
}
|
|
}
|
|
|
|
}
|