1238 lines
47 KiB
PHP
1238 lines
47 KiB
PHP
<?php
|
|
class Request 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"];
|
|
}
|
|
|
|
$order_by = "MutasiRequestNumber";
|
|
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 PurchaseOrderID
|
|
FROM `purchaseorder`
|
|
JOIN `supplier` ON PurchaseOrderSupplierID = SupplierID $filter_supplier
|
|
JOIN `supplierpaymentterms` ON SupplierPaymentTermsID = PurchaseOrderSupplierPaymentTermsID AND
|
|
SupplierPaymentTermsSupplierID = PurchaseOrderSupplierID
|
|
WHERE
|
|
PurchaseOrderIsActive = 'Y' AND (PurchaseOrderDate BETWEEN ? AND ?) AND
|
|
( PurchaseOrderNumber like ? )
|
|
) x";
|
|
$qry = $this->db_inventory->query($sql,array($start_date, $end_date, $search));
|
|
$tot_count = 0;
|
|
$tot_page = 0;
|
|
if ($qry) {
|
|
$tot_count = $qry->row()->total;
|
|
$tot_page = ceil($tot_count/$perpage);
|
|
} else {
|
|
$this->sys_error_db("purchase order count error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$rows = array();
|
|
$sql = "SELECT MutasiRequestID as id,
|
|
CompanyAddressID as company_address_id,
|
|
CompanyAddressLabel as company_address_label,
|
|
CompanyAddressDescription as company_address_description,
|
|
MutasiRequestNumber as code,
|
|
PurchaseOrderStatus as status_pengiriman,
|
|
PurchaseOrderDate as purchase_date,
|
|
PurchaseOrderDeliveryDate as expectation_delivery_date,
|
|
IFNULL(PurchaseOrderExpeditionID,0) as expedition_id,
|
|
IFNULL(ExpeditionName,'') as expedition_name,
|
|
PurchaseOrderTaxInclusive as tax_inclusive,
|
|
PurchaseOrderTaxPercentase as tax_percent,
|
|
PurchaseOrderTaxTotal as tax_total,
|
|
PurchaseOrderMateraiInclusive as materai_inclusive,
|
|
PurchaseOrderMateraiPrice as materai_price,
|
|
PurchaseOrderMateraiAmount as materai_amount,
|
|
PurchaseOrderMateraiTotal as materai_total,
|
|
PurchaseOrderSubtotal as subtotal,
|
|
PurchaseOrderDiscount as discount_percent,
|
|
PurchaseOrderDiscountRp as discount_rp,
|
|
PurchaseOrderTotal as total,
|
|
PurchaseOrderNote as note,
|
|
PurchaseOrderSupplierPaymentTermsID as terms_id,
|
|
SupplierPaymentTermsName as terms_name,
|
|
SupplierPaymentTermsDescription as terms_description,
|
|
PurchaseOrderIsConfirm as is_confirm,
|
|
IFNULL(M_UserUsername,'') as user_confirm,
|
|
IF(PurchaseOrderConfirmDatetime = NULL ,'-',DATE_FORMAT(PurchaseOrderConfirmDatetime,'%d-%m-%Y %H:%i')) as date_confirm,
|
|
'' as details
|
|
FROM `purchaseorder`
|
|
JOIN `supplier` ON PurchaseOrderSupplierID = SupplierID $filter_supplier
|
|
JOIN `supplierpaymentterms` ON PurchaseOrderSupplierPaymentTermsID = SupplierPaymentTermsID AND
|
|
PurchaseOrderSupplierID = SupplierPaymentTermsSupplierID
|
|
JOIN `companyaddress` ON PurchaseOrderCompanyAddressID = CompanyAddressID
|
|
LEFT JOIN `expedition` ON PurchaseOrderExpeditionID = ExpeditionID
|
|
LEFT JOIN $this->db_onex.m_user ON PurchaseOrderConfirmBy = M_UserID
|
|
WHERE
|
|
PurchaseOrderIsActive = 'Y' AND (PurchaseOrderDate BETWEEN ? AND ?) AND
|
|
( PurchaseOrderNumber like ? )
|
|
ORDER BY ?
|
|
LIMIT ? OFFSET ?";
|
|
$qry = $this->db_inventory->query($sql, array($start_date, $end_date, $search, $order, $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("purchase order select error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function getdetails(){
|
|
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$prm = $this->sys_input;
|
|
$sql = "SELECT PurchaseOrderDetailID as id,
|
|
PurchaseOrderDetailItemID as item_id,
|
|
ItemName as item_name,
|
|
ItemUnitID as unit_id,
|
|
ItemUnitName as unit_name,
|
|
PurchaseOrderDetailStatus as status,
|
|
PurchaseOrderDetailQty as qty,
|
|
PurchaseOrderDetailAmount as price,
|
|
PurchaseOrderDetailDiscount as discount_percent,
|
|
PurchaseOrderDetailDiscountRp as discount_rp,
|
|
PurchaseOrderDetailTotal as total
|
|
FROM `purchaseorderdetail`
|
|
JOIN `item` ON PurchaseOrderDetailItemID = ItemID AND ItemIsActive = 'Y'
|
|
JOIN `itemunit` ON PurchaseOrderDetailItemUnitID = ItemUnitID AND ItemUnitIsActive = 'Y'
|
|
WHERE
|
|
PurchaseOrderDetailPurchaseOrderID = ? AND
|
|
PurchaseOrderDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($prm['id']));
|
|
if($qry){
|
|
$rows = $qry->result_array();
|
|
}else {
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("get purchase order detail error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$result = array( "records" => $rows);
|
|
$this->sys_ok($result);
|
|
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;
|
|
|
|
$company = "";
|
|
if (isset($prm['company'])) {
|
|
$company = trim($prm["company"]);
|
|
}
|
|
$company_address = "";
|
|
if (isset($prm['company_address'])) {
|
|
$company_address = trim($prm["company_address"]);
|
|
}
|
|
$delivery_date = "";
|
|
if (isset($prm['delivery_date'])) {
|
|
$delivery_date = trim($prm["delivery_date"]);
|
|
}
|
|
$expedition = "";
|
|
if (isset($prm['expedition'])) {
|
|
$expedition = trim($prm["expedition"]);
|
|
}
|
|
$purchase_date = "";
|
|
if (isset($prm['purchase_date'])) {
|
|
$purchase_date = trim($prm["purchase_date"]);
|
|
}
|
|
$supplier = "";
|
|
if (isset($prm['supplier'])) {
|
|
$supplier = trim($prm["supplier"]);
|
|
}
|
|
$terms = "";
|
|
if (isset($prm['terms'])) {
|
|
$terms = trim($prm["terms"]);
|
|
}
|
|
$note = "";
|
|
if (isset($prm['note'])) {
|
|
$note = trim($prm["note"]);
|
|
}
|
|
$tax_percent = 0;
|
|
$tax_total = 0;
|
|
$tax_include = $prm["tax"]['include']?'Y':'N';
|
|
if($tax_include == 'Y'){
|
|
$tax_percent = intval($prm["tax"]['percent']);
|
|
$tax_total = intval($prm["tax"]['total']);
|
|
}
|
|
|
|
$materai_total = 0;
|
|
$materai_price = 0;
|
|
$materai_amount = 0;
|
|
$materai_include = $prm["materai"]['include']?'Y':'N';
|
|
if($materai_include == 'Y'){
|
|
$materai_amount = intval($prm["materai"]['amount']);
|
|
$materai_price = intval($prm["materai"]['price']);
|
|
$materai_total = intval($prm["materai"]['total']);
|
|
}
|
|
|
|
$subtotal = 0;
|
|
if (isset($prm['subtotal'])) {
|
|
$subtotal = intval($prm["subtotal"]);
|
|
}
|
|
|
|
$total = 0;
|
|
if (isset($prm['total'])) {
|
|
$total = intval($prm["total"]);
|
|
}
|
|
|
|
$discount = 0;
|
|
$discount_rp = 0;
|
|
if (isset($prm['discount'])) {
|
|
if($prm['discount']['flag']){
|
|
if($prm['discount']['type'] == 'p')
|
|
$discount = $prm['discount']['amount'];
|
|
|
|
if($prm['discount']['type'] == 'r')
|
|
$discount_rp = $prm['discount']['amount'];
|
|
}
|
|
}
|
|
|
|
$this->db_inventory->trans_start();
|
|
$this->db_inventory->trans_strict(FALSE);
|
|
|
|
$numbering = '';
|
|
$sql = "SELECT `fn_numbering`('PO') 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;
|
|
$sql = "INSERT INTO purchaseorder(
|
|
PurchaseOrderNumber,
|
|
PurchaseOrderSupplierID,
|
|
PurchaseOrderCompanyAddressID,
|
|
PurchaseOrderDate,
|
|
PurchaseOrderDeliveryDate,
|
|
PurchaseOrderExpeditionID,
|
|
PurchaseOrderTaxInclusive,
|
|
PurchaseOrderMateraiInclusive,
|
|
PurchaseOrderTaxPercentase,
|
|
PurchaseOrderTaxTotal,
|
|
PurchaseOrderMateraiPrice,
|
|
PurchaseOrderMateraiAmount,
|
|
PurchaseOrderMateraiTotal,
|
|
PurchaseOrderSubtotal,
|
|
PurchaseOrderDiscount,
|
|
PurchaseOrderDiscountRp,
|
|
PurchaseOrderTotal,
|
|
PurchaseOrderNote,
|
|
PurchaseOrderSupplierPaymentTermsID,
|
|
PurchaseOrderUserID,
|
|
PurchaseOrderCreated,
|
|
PurchaseOrderLastUpdated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW()
|
|
)";
|
|
$param_insert = array(
|
|
$numbering,
|
|
$supplier,
|
|
$company_address,
|
|
$purchase_date,
|
|
$delivery_date,
|
|
$expedition,
|
|
$tax_include,
|
|
$materai_include,
|
|
$tax_percent,
|
|
$tax_total,
|
|
$materai_price,
|
|
$materai_amount,
|
|
$materai_total,
|
|
$subtotal,
|
|
$discount,
|
|
$discount_rp,
|
|
$total,
|
|
$note,
|
|
$terms,
|
|
$userid
|
|
);
|
|
$qry = $this->db_inventory->query($sql, $param_insert);
|
|
$last_id = 0;
|
|
if($qry){
|
|
$last_id = $this->db_inventory->insert_id();
|
|
}
|
|
else{
|
|
$this->sys_error_db("save supplier error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
if(count($prm['details'])>0){
|
|
foreach ($prm['details'] as $key => $value) {
|
|
$sql = "INSERT INTO purchaseorderdetail(
|
|
PurchaseOrderDetailPurchaseOrderID,
|
|
PurchaseOrderDetailItemID,
|
|
PurchaseOrderDetailItemUnitID,
|
|
PurchaseOrderDetailQty,
|
|
PurchaseOrderDetailAmount,
|
|
PurchaseOrderDetailDiscount,
|
|
PurchaseOrderDetailDiscountRp,
|
|
PurchaseOrderDetailTotal,
|
|
PurchaseOrderDetailUserID,
|
|
PurchaseOrderDetailCreated,
|
|
PurchaseOrderDetailLastUpdated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,?,?,?,?,NOW(),NOW()
|
|
)";
|
|
$param_insert_detail = array(
|
|
$last_id,
|
|
$value['item_id'],
|
|
$value['unit_id'],
|
|
$value['qty'],
|
|
$value['price'],
|
|
$value['discount_percent'],
|
|
$value['discount_rp'],
|
|
$value['total'],
|
|
$userid
|
|
);
|
|
$qry = $this->db_inventory->query($sql,$param_insert_detail);
|
|
if(!$qry){
|
|
//echo $this->db_inventory->last_query();
|
|
$this->sys_error_db("save supplier address error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
$id = $last_id;
|
|
$sql = "SELECT purchaseorder.*, '' as details
|
|
FROM purchaseorder
|
|
WHERE PurchaseOrderID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select purchaseorder error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$row_after = $qry->row_array();
|
|
$sql = "SELECT *
|
|
FROM `purchaseorderdetail`
|
|
WHERE
|
|
PurchaseOrderDetailPurchaseOrderID = ? AND
|
|
PurchaseOrderDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row_after['details'] = $qry->result_array();
|
|
}else{
|
|
$this->sys_error_db("select purchaseorder details error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$data_log_after = $row_after;
|
|
|
|
$sql = "INSERT INTO purchareorder_log (
|
|
PurchaseOrderLogPurchaseOrderID,
|
|
PurchaseOrderLogStatus,
|
|
PurchaseOrderLogJSONBefore,
|
|
PurchaseOrderLogJSONAfter,
|
|
PurchaseOrderLogUserID,
|
|
PurchaseOrderLogCreated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,NOW()
|
|
)";
|
|
$qry = $this->db_inventory_log->query($sql, array($id,'ADD',NULL,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 edit(){
|
|
try {
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$prm = $this->sys_input;
|
|
$userid = $this->sys_user['M_UserID'];
|
|
|
|
$this->update($prm,$userid);
|
|
|
|
|
|
$result = array(
|
|
"message" => ''
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function update($prm,$userid){
|
|
$company = "";
|
|
if (isset($prm['company'])) {
|
|
$company = trim($prm["company"]);
|
|
}
|
|
$company_address = "";
|
|
if (isset($prm['company_address'])) {
|
|
$company_address = trim($prm["company_address"]);
|
|
}
|
|
$delivery_date = "";
|
|
if (isset($prm['delivery_date'])) {
|
|
$delivery_date = trim($prm["delivery_date"]);
|
|
}
|
|
$expedition = "";
|
|
if (isset($prm['expedition'])) {
|
|
$expedition = trim($prm["expedition"]);
|
|
}
|
|
$purchase_date = "";
|
|
if (isset($prm['purchase_date'])) {
|
|
$purchase_date = trim($prm["purchase_date"]);
|
|
}
|
|
$supplier = "";
|
|
if (isset($prm['supplier'])) {
|
|
$supplier = trim($prm["supplier"]);
|
|
}
|
|
$terms = "";
|
|
if (isset($prm['terms'])) {
|
|
$terms = trim($prm["terms"]);
|
|
}
|
|
$note = "";
|
|
if (isset($prm['note'])) {
|
|
$note = trim($prm["note"]);
|
|
}
|
|
$tax_percent = 0;
|
|
$tax_total = 0;
|
|
$tax_include = $prm["tax"]['include']?'Y':'N';
|
|
if($tax_include == 'Y'){
|
|
$tax_percent = intval($prm["tax"]['percent']);
|
|
$tax_total = intval($prm["tax"]['total']);
|
|
}
|
|
|
|
$materai_total = 0;
|
|
$materai_price = 0;
|
|
$materai_amount = 0;
|
|
$materai_include = $prm["materai"]['include']?'Y':'N';
|
|
if($materai_include == 'Y'){
|
|
$materai_amount = intval($prm["materai"]['amount']);
|
|
$materai_price = intval($prm["materai"]['price']);
|
|
$materai_total = intval($prm["materai"]['total']);
|
|
}
|
|
|
|
$subtotal = 0;
|
|
if (isset($prm['subtotal'])) {
|
|
$subtotal = intval($prm["subtotal"]);
|
|
}
|
|
|
|
$total = 0;
|
|
if (isset($prm['total'])) {
|
|
$total = intval($prm["total"]);
|
|
}
|
|
|
|
$discount = 0;
|
|
$discount_rp = 0;
|
|
if (isset($prm['discount'])) {
|
|
if($prm['discount']['flag']){
|
|
if($prm['discount']['type'] == 'p')
|
|
$discount = $prm['discount']['amount'];
|
|
|
|
if($prm['discount']['type'] == 'r')
|
|
$discount_rp = $prm['discount']['amount'];
|
|
}
|
|
}
|
|
|
|
$id = $prm['id'];
|
|
|
|
$sql = "SELECT purchaseorder.*, '' as details
|
|
FROM purchaseorder
|
|
WHERE PurchaseOrderID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select purchaseorder error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$row_before = $qry->row_array();
|
|
$sql = "SELECT *
|
|
FROM `purchaseorderdetail`
|
|
WHERE
|
|
PurchaseOrderDetailPurchaseOrderID = ? AND
|
|
PurchaseOrderDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row_before['details'] = $qry->result_array();
|
|
}else{
|
|
$this->sys_error_db("select purchaseorder details error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$data_log_before = $row_before;
|
|
|
|
$this->db_inventory->trans_start();
|
|
$this->db_inventory->trans_strict(FALSE);
|
|
|
|
$last_id = $id;
|
|
$sql = "UPDATE purchaseorder SET
|
|
PurchaseOrderSupplierID = ?,
|
|
PurchaseOrderCompanyAddressID = ?,
|
|
PurchaseOrderDate = ?,
|
|
PurchaseOrderDeliveryDate = ?,
|
|
PurchaseOrderExpeditionID = ?,
|
|
PurchaseOrderTaxInclusive = ?,
|
|
PurchaseOrderMateraiInclusive = ?,
|
|
PurchaseOrderTaxPercentase = ?,
|
|
PurchaseOrderTaxTotal = ?,
|
|
PurchaseOrderMateraiPrice = ?,
|
|
PurchaseOrderMateraiAmount = ?,
|
|
PurchaseOrderMateraiTotal = ?,
|
|
PurchaseOrderSubtotal = ?,
|
|
PurchaseOrderDiscount = ?,
|
|
PurchaseOrderDiscountRp = ?,
|
|
PurchaseOrderTotal = ?,
|
|
PurchaseOrderNote = ?,
|
|
PurchaseOrderSupplierPaymentTermsID = ?,
|
|
PurchaseOrderLastUpdated = NOW(),
|
|
PurchaseOrderUserID = ?
|
|
WHERE
|
|
PurchaseOrderID = ?";
|
|
$param_update_header = array(
|
|
$supplier,
|
|
$company_address,
|
|
$purchase_date,
|
|
$delivery_date,
|
|
$expedition,
|
|
$tax_include,
|
|
$materai_include,
|
|
$tax_percent,
|
|
$tax_total,
|
|
$materai_price,
|
|
$materai_amount,
|
|
$materai_total,
|
|
$subtotal,
|
|
$discount,
|
|
$discount_rp,
|
|
$total,
|
|
$note,
|
|
$terms,
|
|
$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 purchaseorder error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$sql = "UPDATE purchaseorderdetail SET PurchaseOrderDetailIsActive = 'N' WHERE PurchaseOrderDetailPurchaseOrderID = ?";
|
|
$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['details'])>0){
|
|
foreach ($prm['details'] as $key => $value) {
|
|
if(intval($value['id']) > 0){
|
|
$sql = "UPDATE purchaseorderdetail SET
|
|
PurchaseOrderDetailItemID = ?,
|
|
PurchaseOrderDetailItemUnitID = ?,
|
|
PurchaseOrderDetailQty = ?,
|
|
PurchaseOrderDetailAmount = ?,
|
|
PurchaseOrderDetailDiscount = ?,
|
|
PurchaseOrderDetailDiscountRp = ?,
|
|
PurchaseOrderDetailTotal = ?,
|
|
PurchaseOrderDetailUserID = ?,
|
|
PurchaseOrderDetailIsActive = 'Y',
|
|
PurchaseOrderDetailLastUpdated = NOW()
|
|
WHERE
|
|
PurchaseOrderDetailID = ?";
|
|
$param_edit_detail = array(
|
|
$value['item_id'],
|
|
$value['unit_id'],
|
|
$value['qty'],
|
|
$value['price'],
|
|
$value['discount_rp'],
|
|
$value['discount_percent'],
|
|
$value['total'],
|
|
$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 purchaseorderdetail error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}else{
|
|
$sql = "INSERT INTO purchaseorderdetail(
|
|
PurchaseOrderDetailPurchaseOrderID,
|
|
PurchaseOrderDetailItemID,
|
|
PurchaseOrderDetailItemUnitID,
|
|
PurchaseOrderDetailQty,
|
|
PurchaseOrderDetailAmount,
|
|
PurchaseOrderDetailDiscount,
|
|
PurchaseOrderDetailDiscountRp,
|
|
PurchaseOrderDetailTotal,
|
|
PurchaseOrderDetailUserID,
|
|
PurchaseOrderDetailCreated,
|
|
PurchaseOrderDetailLastUpdated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,?,?,?,?,NOW(),NOW()
|
|
)";
|
|
$param_add_detail = array(
|
|
$id,
|
|
$value['item_id'],
|
|
$value['unit_id'],
|
|
$value['qty'],
|
|
$value['price'],
|
|
$value['discount_rp'],
|
|
$value['discount_percent'],
|
|
$value['total'],
|
|
$userid
|
|
);
|
|
//echo $this->db_inventory->last_query();
|
|
$qry = $this->db_inventory->query($sql, $param_add_detail);
|
|
if(!$qry){
|
|
$this->sys_error_db("save purchaseorderdetail error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
|
|
$sql = "SELECT purchaseorder.*, '' as details
|
|
FROM purchaseorder
|
|
WHERE PurchaseOrderID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select purchaseorder error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$row_after = $qry->row_array();
|
|
$sql = "SELECT *
|
|
FROM `purchaseorderdetail`
|
|
WHERE
|
|
PurchaseOrderDetailPurchaseOrderID = ? AND
|
|
PurchaseOrderDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row_after['details'] = $qry->result_array();
|
|
}else{
|
|
$this->sys_error_db("select purchaseorder details error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$data_log_after = $row_after;
|
|
|
|
$sql = "INSERT INTO purchareorder_log (
|
|
PurchaseOrderLogPurchaseOrderID,
|
|
PurchaseOrderLogStatus,
|
|
PurchaseOrderLogJSONBefore,
|
|
PurchaseOrderLogJSONAfter,
|
|
PurchaseOrderLogUserID,
|
|
PurchaseOrderLogCreated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,NOW()
|
|
)";
|
|
//print_r($data_log_before);
|
|
$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("update 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;
|
|
}
|
|
|
|
$sql = "SELECT purchaseorder.*, '' as details
|
|
FROM purchaseorder
|
|
WHERE PurchaseOrderID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select purchaseorder error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
$row = $qry->row_array();
|
|
$sql = "SELECT *
|
|
FROM `purchaseorderdetail`
|
|
WHERE
|
|
PurchaseOrderDetailPurchaseOrderID = ? AND
|
|
PurchaseOrderDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row['details'] = $qry->result_array();
|
|
}else{
|
|
$this->sys_error_db("select purchaseorder details error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$data_log_before = $row;
|
|
|
|
$this->db_inventory->trans_start();
|
|
$this->db_inventory->trans_strict(FALSE);
|
|
|
|
$sql = "UPDATE purchaseorder
|
|
SET PurchaseOrderIsConfirm = 'Y',
|
|
PurchaseOrderLastUpdated = NOW(),
|
|
PurchaseOrderConfirmDatetime = NOW(),
|
|
PurchaseOrderConfirmBy = ?,
|
|
PurchaseOrderUserID = ?
|
|
WHERE PurchaseOrderID = ?
|
|
";
|
|
$qry = $this->db_inventory->query($sql, [$userid,$userid,$id]);
|
|
if(!$qry){
|
|
|
|
$this->sys_error_db("confirm purchaseorder error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT purchaseorder.*, '' as details
|
|
FROM purchaseorder
|
|
WHERE PurchaseOrderID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select purchaseorder error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$row_after = $qry->row_array();
|
|
$sql = "SELECT *
|
|
FROM `purchaseorderdetail`
|
|
WHERE
|
|
PurchaseOrderDetailPurchaseOrderID = ? AND
|
|
PurchaseOrderDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row_after['details'] = $qry->result_array();
|
|
}else{
|
|
$this->sys_error_db("select purchaseorder details error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$data_log_after = $row_after;
|
|
|
|
$sql = "INSERT INTO purchareorder_log (
|
|
PurchaseOrderLogPurchaseOrderID,
|
|
PurchaseOrderLogStatus,
|
|
PurchaseOrderLogJSONBefore,
|
|
PurchaseOrderLogJSONAfter,
|
|
PurchaseOrderLogUserID,
|
|
PurchaseOrderLogCreated
|
|
)
|
|
VALUES(
|
|
?,?,?,?,?,NOW()
|
|
)";
|
|
$qry = $this->db_inventory_log->query($sql, array($id,'CONFIRM',json_decode($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();
|
|
|
|
|
|
$result = array(
|
|
"message" => 'confirm success'
|
|
);
|
|
$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 purchaseorder
|
|
SET
|
|
PurchaseOrderIsActive = 'N',
|
|
PurchaseOrderLastUpdated = now(),
|
|
PurchaseOrderUserID = ?
|
|
WHERE PurchaseOrderID = ?
|
|
";
|
|
$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 purchaseorderdetail
|
|
SET
|
|
PurchaseOrderDetailIsActive = 'N',
|
|
PurchaseOrderDetailLastUpdated = now(),
|
|
PurchaseOrderDetailUserID = ?
|
|
WHERE
|
|
PurchaseOrderDetailPurchaseOrderID = ?
|
|
";
|
|
$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 = "SELECT purchaseorder.*, '' as details
|
|
FROM purchaseorder
|
|
WHERE PurchaseOrderID = ? ";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
//echo $this->db_inventory->last_query();
|
|
if(!$qry){
|
|
$this->sys_error_db("select purchaseorder error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$row_after = $qry->row_array();
|
|
$sql = "SELECT *
|
|
FROM `purchaseorderdetail`
|
|
WHERE
|
|
PurchaseOrderDetailPurchaseOrderID = ? AND
|
|
PurchaseOrderDetailIsActive = 'Y'";
|
|
$qry = $this->db_inventory->query($sql, array($id));
|
|
if($qry){
|
|
$row_after['details'] = $qry->result_array();
|
|
}else{
|
|
$this->sys_error_db("select purchaseorder details error", $this->db_inventory->last_query());
|
|
exit;
|
|
}
|
|
|
|
$data_log_after = $row_after;
|
|
|
|
$sql = "INSERT INTO purchareorder_log (
|
|
PurchaseOrderLogPurchaseOrderID,
|
|
PurchaseOrderLogStatus,
|
|
PurchaseOrderLogJSONBefore,
|
|
PurchaseOrderLogJSONAfter,
|
|
PurchaseOrderLogUserID,
|
|
PurchaseOrderLogCreated
|
|
)
|
|
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;
|
|
}
|
|
|
|
$this->db_inventory->trans_complete();
|
|
|
|
$result = array(
|
|
"message" => ''
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
|
|
function search_supplier(){
|
|
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']}%";
|
|
}
|
|
|
|
// QUERY TOTAL
|
|
$sql = "SELECT count(*) as total
|
|
FROM supplier
|
|
WHERE
|
|
SupplierName like ?
|
|
AND SupplierIsActive = 'Y'";
|
|
$query = $this->db_inventory->query($sql,$q['search']);
|
|
//echo $query;
|
|
if ($query) {
|
|
$tot_count = $query->result_array()[0]["total"];
|
|
}
|
|
else {
|
|
$this->sys_error_db("m_city count",$this->db_inventory);
|
|
exit;
|
|
}
|
|
$rows = array('id'=>0,'name'=>'Semua');
|
|
$sql = "
|
|
SELECT SupplierID as id, SupplierName as name
|
|
FROM supplier
|
|
WHERE
|
|
SupplierName like ?
|
|
AND SupplierIsActive = 'Y'
|
|
ORDER BY SupplierName DESC
|
|
";
|
|
$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("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("supplier rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function search_supplier_form(){
|
|
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']}%";
|
|
}
|
|
|
|
// QUERY TOTAL
|
|
$sql = "SELECT count(*) as total
|
|
FROM supplier
|
|
WHERE
|
|
SupplierName like ?
|
|
AND SupplierIsActive = 'Y'";
|
|
$query = $this->db_inventory->query($sql,$q['search']);
|
|
//echo $query;
|
|
if ($query) {
|
|
$tot_count = $query->result_array()[0]["total"];
|
|
}
|
|
else {
|
|
$this->sys_error_db("m_city count",$this->db_inventory);
|
|
exit;
|
|
}
|
|
$rows = array('id'=>0,'name'=>'Semua');
|
|
$sql = "
|
|
SELECT SupplierID as id, SupplierName as name
|
|
FROM supplier
|
|
WHERE
|
|
SupplierName like ?
|
|
AND SupplierIsActive = 'Y'
|
|
ORDER BY SupplierName DESC
|
|
";
|
|
$query = $this->db_inventory->query($sql, array($q['search']));
|
|
|
|
if ($query) {
|
|
$rows = $query->result_array();
|
|
//array_push($rows,array('id'=>0,'name'=>'Pilih Supplier'));
|
|
//echo $this->db_onedev->last_query();
|
|
$result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
|
|
$this->sys_ok($result);
|
|
}
|
|
else {
|
|
$this->sys_error_db("supplier rows",$this->db_inventory);
|
|
exit;
|
|
}
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
|
|
function search_expedition(){
|
|
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']}%";
|
|
}
|
|
|
|
// QUERY TOTAL
|
|
$sql = "SELECT count(*) as total
|
|
FROM expedition
|
|
WHERE
|
|
ExpeditionName like ?
|
|
AND ExpeditionIsActive = 'Y'";
|
|
$query = $this->db_inventory->query($sql,$q['search']);
|
|
//echo $query;
|
|
if ($query) {
|
|
$tot_count = $query->result_array()[0]["total"];
|
|
}
|
|
else {
|
|
$this->sys_error_db("m_city count",$this->db_inventory);
|
|
exit;
|
|
}
|
|
$rows = array('id'=>0,'name'=>'Semua');
|
|
$sql = "
|
|
SELECT ExpeditionID as id, ExpeditionName as name
|
|
FROM expedition
|
|
WHERE
|
|
ExpeditionName like ?
|
|
AND ExpeditionIsActive = 'Y'
|
|
AND ExpeditionIsInternal = 'N'
|
|
ORDER BY ExpeditionName DESC
|
|
";
|
|
$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("total" => $tot_count, "records" => $rows, "total_display" => sizeof($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_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_expedition(){
|
|
try{
|
|
if (! $this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
|
|
$sql = "SELECT ExpeditionID as id,
|
|
ExpeditionName as name
|
|
FROM expedition
|
|
WHERE
|
|
ExpeditionIsInternal = 'N' AND ExpeditionIsActive = '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);
|
|
}
|
|
}
|
|
|
|
}
|