Sybase - Unifood
unknown
php
6 months ago
93 kB
3
Indexable
<?php
defined('BASEPATH') or exit('No direct script access allowed');
date_default_timezone_set('Asia/Jakarta');
ini_set('memory_limit', '5G');
ini_set('max_execution_time', 1000);
// ini_set('dispay_errors', 1);
// error_reporting(E_ALL);
class Sybase extends CI_Controller
{
public function __construct()
{
parent::__construct();
$this->load->database();
$this->load->library('pagination');
$this->load->helper('url');
}
public function index()
{
$this->load->view('welcome_message');
}
public function statuskirim()
{
$date = date('Y-m-d H:i:s', strtotime('-20 minutes'));
$statuskirim = $this->db->query("
SELECT
intranshdr.Sys,
intranshdr.TrNo,
intranshdr.Last_Modified,intranshdr.CustBillTo,intranshdr.CustSellTo, intranshdr.CustShipTo,intranshdr.CustShipTo,intranshdr.CustTaxTo
FROM intranshdr
where intranshdr.transcode = 3
Order By Last_Modified DESC;
-- where intranshdr.transcode = 3 and intranshdr.Last_Modified ='{$date}'
")->result();
$this->db->query("COMMIT");
if ($statuskirim) {
$dbsales = $this->load->database('dbsales', TRUE);
foreach ($statuskirim as $sk) {
$db_data = array(
'sys' => $sk->Sys,
'TrNo' => $sk->TrNo,
'last_modified' => $sk->Last_Modified,
'CustBillTo' => $sk->CustBillTo,
'CustSellTo' => $sk->CustSellTo,
'CustShipTo' => $sk->CustShipTo,
'CustTaxTo' => $sk->CustTaxTo
);
$dbsales->replace('statuskirim', $db_data);
}
}
}
public function item()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('item');
$sql = "select itemid,description,unitsetid,Inventory_Unit,ItemGroupID,ItemCategoryID,itembrandid,ItemModelID,ClassID,GL_GroupID,Tax_GroupID from initem where Blocked = 0";
$item = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('item', $item);
}
public function itemunit($itemid)
{
$itemid = htmlspecialchars($itemid);
$sql = "select INUnitSetDtl.UnitID from initem join INUnitSetDtl on INItem.UnitSetID = INUnitSetDtl.unitsetid
where initem.itemid ='{$itemid}'";
$result = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode((array) $result, true);
}
public function itembalance()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('itembalance');
$sql = "select initem.itemid,initembalance.locationid,initembalance.periodid,initembalance.beginqty,initembalance.inqty,initembalance.outqty from initembalance join initem on initem.itemid = initembalance.itemid where initem.itemgroupid = 105 or initem.itemgroupid = 2007 or initem.itemgroupid = 2002 or initem.itemgroupid = 2011";
$itembalance = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('itembalance', $itembalance);
}
public function period()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('period');
$sql = "select periodid,description,startdate,enddate from pxperiod";
$period = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('period', $period);
}
public function location()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('location');
$sql = "select locationid,WarehouseID,Description,capacitym3 from inlocation";
$location = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('location', $location);
}
public function employee()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('employee');
$sql = "select employeeid,empname,RegisterDate from PXEmployee";
$employee = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('employee', $employee);
}
public function customer($save_to_mysql = 'y')
{
if ($save_to_mysql == 'y') {
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('customer');
$sql = 'select CustID, custname, PersonalID,last_modified,address,parentID,blockShip_,blockAR_,blockSale_,dispatchID,forwarderID,areaid,contact_sales,contact_acc,our_id,currencyid,salesrepid,GroupA_ID,GroupB_ID,PriceID,DeliveryMtdID,CustStatus,CreditStatus,CollectorID,CreditLimit,RegisterDate,LastTransDate,ConsiderOverdueDays_,CreditDays,TaxGroupID,InvTaxCode,PromoID,Owner_Name,PersonalName,PersonalAddress,MaxUnpaidSO,phones from arcustomer';
$customer = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('customer', $customer);
} elseif ($save_to_mysql == 's') {
$sql = 'select arcustomer.CustID, arcustomer.custname, ARCustomerGroupA.Description, arcustomer.taxid, ARCustomerGroupA.groupa_id from arcustomer join ARCustomerGroupA on ARCustomerGroupA.groupa_id = arcustomer.GroupA_ID';
$result = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode((array) $result, true);
}
}
public function belum_invoice($x = 0, $y = 0)
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('beluminvoice');
// for($i=((int) $x);$i<=((int) $y);$i++){
$sql = "select
INTranshdr.entityid,
INTransDtl.OrderNo,
intransdtl.ROTNo,
INTransHdr.InvoiceNo,
intranshdr.TrNo,
intranshdr.TrDate,
intranshdr.TrTime,
intranshdr.trtype,
intranshdr.TransCode,
intranshdr.string1,
intranshdr.CustBillTo,
intranshdr.CustShipTo,
intranshdr.CustTaxTo,
intranshdr.TOP_ID,
intranshdr.TOP_days,
intranshdr.TaxCalc,
intranshdr.TaxInvoiceDate,
intranshdr.TaxInvoiceNo,
intranshdr.SalesRepID,
intranshdr.FooterNote,
intransdtl.ItemID,
intransdtl.Description,
INTransDtl.LocationID,
intransdtl.QT,
intransdtl.UnitID,
intransdtl.UnitPrice,
INTransDtl.DiscPct_,
intransdtl.GrossAmt,
intransdtl.Taxable,
intransdtl.TaxAmt,
intransdtl.Rounding,
INTransDtl.NetAmt
from intranshdr inner join INTransDtl where INTransHdr.TransCode like '3' and intranshdr.Void_ like '0' and INTransHdr.InvoiceNo is null";
$beluminvoice = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
if ($beluminvoice)
$dbsales->insert_batch('beluminvoice', $beluminvoice);
// }
}
public function sudah_invoice()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('sudahinvoice');
$sql = "SELECT
entityid,
entitydesc,
salesrepid,
salesrepname,
periodID,
custID,
custname,
trtype,
trno,
trdate,
duedate,
age,
noinv,
currencyID,
amount,
amountpaid,
discrepancy,
outstandingcheck
FROM AR_SP_UnpaidInvoices_asof_check(getdate(),'0','0','z','0','z') where trx like 'sales' or trx like 'ar debit note'";
$sudahinvoice = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('sudahinvoice', $sudahinvoice);
}
// public function loadsheet()
// {
// $dbsales = $this->load->database('dbsales', TRUE);
// $dbsales->truncate('loadsheet');
// $sql = "select prtranshdr.trno,prtranshdr.TrManualRef,PXDispatch.Description,inloadsheet.VehicleID,prtransdtl.ShipmentTRNo,intranshdr.CustShipTo,arcustomer.CustName,arcustomer.address, intransdtl.ItemID,intransdtl.Description,intransdtl.qty,intransdtl.qt,intransdtl.UnitID from prtransdtl
// join prtranshdr on prtransdtl.sys = prtranshdr.sys
// join intranshdr on intranshdr.trno = prtransdtl.ShipmentTRNo
// join intransdtl on intranshdr.sys =intransdtl.sys
// join arcustomer on intranshdr.CustShipTo = arcustomer.custid
// join inloadsheet on inloadsheet.sheetno = prtranshdr.TrManualRef
// join PXDispatch on PXDispatch.DispatchID = INLoadSheet.DispatchID
// where prtranshdr.trtype like '%ppoa%'
// order by prtranshdr.trno desc";
// $loadsheet = $this->db->query($sql)->result_array();
// if($loadsheet)
// $dbsales->insert_batch('loadsheet', $loadsheet);
// }
public function monitoringpermintaanarmada($date1, $date2)
{
$sql = "select
prtransHDR.TRDATE,
prtransHDR.TRNO,
prtransDTL.ITEMID,
prtransDTL.QTY,
prtransDTL.UNITID,
PRTRANSDTL.LoadSheetTRNo,
PRTRANSDTL.Remarks
from prtransdtl
join prtranshdr on prtranshdr.sys = prtransdtl.sys
where prtransHDR.TRDATE >='{$date1}' and prtransHDR.TRDATE <='{$date2}' and prtransdtl.LoadSheetTRNo ='' and (prtranshdr.trtype ='PRE1' OR prtranshdr.trtype ='PRE2' OR prtranshdr.trtype ='PRE3' OR prtranshdr.trtype ='PRE4' OR prtranshdr.trtype ='PPOA8' )
order by prtransHDR.TRDATE desc";
$result = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode((array) $result, true);
}
public function detail_surat_jalan($sjno)
{
$sql = "select
intransdtl.ItemID,
intransdtl.Description,
intransdtl.qt,
intransdtl.unitid,
intransdtl.qty,
intransdtl.unitprice,
INTransDtl.DiscAmt,
intransdtl.Taxable,
intransdtl.TaxAmt,
INTransDtl.NetAmt
from intranshdr
inner join intransdtl on intranshdr.sys = intransdtl.sys
where intranshdr.transcode = 3 and (intranshdr.periodid >= 10 or intranshdr.periodid >=31) and (intranshdr.periodid <= 30 or intranshdr.periodid <= 60) and intranshdr.trno = '{$sjno}'";
$result = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode((array) $result, true);
}
public function detail_sudah_invoice($invno)
{
$sql = "select
intranshdr.trno as SJNO,
intranshdr.trdate as SJDate,
intranshdr.custshipto,
SUM(intransdtl.DiscAmt) DiscAmt,
SUM(intransdtl.Taxable) Taxable,
SUM(intransdtl.TaxAmt) TaxAmt,
SUM(intransdtl.NetAmt) NetAmt
from intranshdr
inner join intransdtl on intranshdr.sys = intransdtl.sys
inner join arcustomer on intranshdr.CustShipTo = arcustomer.custid
where intranshdr.transcode = 3 and (intranshdr.periodid >= 10 or intranshdr.periodid >=31) and (intranshdr.periodid <= 30 or intranshdr.periodid <= 60) and intranshdr.InvoiceNo = '{$invno}' group by intranshdr.trno, intranshdr.trdate, intranshdr.custshipto";
$result = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode((array) $result, true);
}
public function detail_paid_amount($invno)
{
$sql = "select catrhdr.TrNo as nobayar, catrhdr.trdate as tanggalbayar, catrdtl.trno as noalokasi, vCADetailAllocation.invoiceno as noinvoice, catrdtl.amount as nilaibayar, catrdtl.CustomerID, catrdtl.Remarks
from catrhdr join catrdtl on catrdtl.catrsys = catrhdr.CATRsys join RPPmtAllocation on RPPmtAllocation.trno = catrdtl.trno join vCADetailAllocation on vCADetailAllocation.paymentno = RPPmtAllocation.trno
where vCADetailAllocation.invoiceno = '{$invno}'";
$result = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode((array) $result, true);
}
public function get_ar_aging_data($date)
{
$sql = "select
AR_SP_UnpaidInvoices_asof_check.SalesRepID as Kode_Sales,
AR_SP_UnpaidInvoices_asof_check.SalesrepName as Nama_Sales,
AR_SP_UnpaidInvoices_asof_check.CustID as Kode_Customer,
AR_SP_UnpaidInvoices_asof_check.CustName as Nama_Customer,
AR_SP_UnpaidInvoices_asof_check.trtype as Tipe_Transaksi,
AR_SP_UnpaidInvoices_asof_check.trno as Nomor_Invoice,
rptranshdr.taxinvoiceno as Nomor_Faktur,
AR_SP_UnpaidInvoices_asof_check.trdate as Tanggal_Invoice,
AR_SP_UnpaidInvoices_asof_check.Duedate as Jatuh_Tempo,
AR_SP_UnpaidInvoices_asof_check.age as Umur_Piutang,
AR_SP_UnpaidInvoices_asof_check.Amount as Nilai_Invoice,
rptranshdr.tax as pajak,
AR_SP_UnpaidInvoices_asof_check.AmountPaid as Pembayaran,
AR_SP_UnpaidInvoices_asof_check.Discrepancy as Sisa_piutang
from AR_SP_UnpaidInvoices_asof_check('{$date}','0','0','z','0','z')
full outer join RPTransHdr on rptranshdr.trno = AR_SP_UnpaidInvoices_asof_check.trno
where AR_SP_UnpaidInvoices_asof_check.SalesRepID <>''
order by AR_SP_UnpaidInvoices_asof_check.age desc";
$result = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode((array) $result, true);
}
public function get_work_order_data()
{
$date = (isset($_GET['date']) && $_GET['date']) ? " AND PSTRANSHDR.TRDATE = '" . (htmlspecialchars($_GET['date'])) . "'" : '';
$date1 = (isset($_GET['date1']) && $_GET['date1']) ? " AND PSTRANSHDR.TRDATE >= '" . (htmlspecialchars($_GET['date1'])) . "'" : '';
$date2 = (isset($_GET['date2']) && $_GET['date2']) ? " AND PSTRANSHDR.TRDATE <= '" . (htmlspecialchars($_GET['date2'])) . "'" : '';
$item = (isset($_GET['item']) && $_GET['item']) ? " AND PSTRANSDTL.ITEMID = '" . (htmlspecialchars($_GET['item'])) . "'" : '';
$limit = (isset($_GET['limit']) && $_GET['limit']) ? " TOP " . ((int) $_GET['limit']) : '';
$sql = "SELECT {$limit}
PSTRANSDTL.ITEMID,
PSTRANSDTL.Description,
SUM(PSTRANSDTL.QT) Total_Quotation,
SUM(PSTRANSDTL.QTY) Total_Quotationinkg,
initem.Inventory_Unit, initem.Weight, initem.Weight_Unit
FROM PSTRANSDTL
INNER JOIN PSTRANSHDR ON PSTRANSHDR.SYS = PSTRANSDTL.SYS
INNER JOIN initem ON PSTRANSDTL.ITEMID = INITEM.ITEMID
WHERE PSTRANSHDR.APPROVED = '1' and pstranshdr.transcode = '53' {$item} {$date} {$date1} {$date2}
GROUP BY PSTRANSDTL.ITEMID, PSTRANSDTL.Description, INITEM.Inventory_Unit,initem.Weight,initem.Weight_Unit";
$result = $this->db->query($sql);
$this->db->query("COMMIT");
echo json_encode((array) ($limit ? $result->row_array() : $result->result_array()), true);
}
public function expected_arv_work_order_data()
{
$date = (isset($_GET['date']) && $_GET['date']) ? " AND PSTRANSHDR.TRDATE = '" . (htmlspecialchars($_GET['date'])) . "'" : '';
$date = (isset($_GET['date1']) && $_GET['date1']) ? " AND PSTRANSHDR.TRDATE >= '" . (htmlspecialchars($_GET['date1'])) . "'" : '';
$date = (isset($_GET['date2']) && $_GET['date2']) ? " AND PSTRANSHDR.TRDATE <= '" . (htmlspecialchars($_GET['date2'])) . "'" : '';
$item = (isset($_GET['item']) && $_GET['item']) ? " AND PSTRANSDTL.ITEMID = '" . (htmlspecialchars($_GET['item'])) . "'" : '';
$sql = "SELECT PSTRANSHDR.TRNO,PSDTLSCHEDULE.Expected_Arv, pstransdtl.ItemID,pstransdtl.Description,PSTRANSDTL.QT,PSTRANSDTL.UNITID,PSTRANSDTL.QTY FROM PSTRANSHDR
JOIN PSTRANSDTL ON PSTRANSDTL.SYS = PSTRANSHDR.SYS
join PSDTLSCHEDULE on PSDTLSCHEDULE.lineno = PSTRANSDTL.lineno and PSDTLSCHEDULE.sys = PSTRANSDTL.sys
WHERE PSTRANSHDR.APPROVED = 1 and PSTRANSHDR.transcode = 53 {$item} {$date}
group by PSTRANSHDR.TRNO,pstransdtl.ItemID ,pstransdtl.Description,PSTRANSDTL.QT,PSTRANSDTL.UNITID,PSTRANSDTL.QTY ,pSDTLSCHEDULE.Expected_Arv order by PSDTLSCHEDULE.Expected_Arv";
$result = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode((array) $result, true);
}
public function PSTRANSHDR()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('PSTRANSHDR');
// $sql = "select itemid,description, unitsetid,Inventory_Unit,itemgroupid,ItemCategoryID,ItemBrandID,ItemModelID,ClassID,GL_GroupID,Tax_GroupID from initem where itemgroupid = 105 or itemgroupid = 2007 or itemgroupid = 2002 or itemgroupid = 2011";
$sql = "SELECT SYS,TRNO,TRDATE,TRTYPE,CUSTBILLTO,CustSellTo,CUSTSHIPTO,CUSTTAXTO,OrderNo FROM PSTRANSHDR WHERE APPROVED =1 and (TRTYPE ='SQ004' or TRTYPE ='SQ003' or TRTYPE ='SQ002' or TRTYPE ='SQ001') ";
$PSTRANSHDR = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
if ($PSTRANSHDR)
$dbsales->insert_batch('PSTRANSHDR', $PSTRANSHDR);
}
public function PSTRANSDTL()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('PSTRANSDTL');
// $sql = "select itemid,description, unitsetid,Inventory_Unit,itemgroupid,ItemCategoryID,ItemBrandID,ItemModelID,ClassID,GL_GroupID,Tax_GroupID from initem where itemgroupid = 105 or itemgroupid = 2007 or itemgroupid = 2002 or itemgroupid = 2011";
$sql = "SELECT
PSTRANSDTL.SYS,PSTRANSDTL.ITEMID,PSTRANSDTL.QT,PSTRANSDTL.UNITID,PSTRANSDTL.QTY,PSTRANSDTL.Description,PSTRANSDTL.LineNo
FROM PSTRANSDTL
JOIN PSTRANSHDR ON PSTRANSHDR.SYS = PSTRANSDTL.SYS
WHERE PSTRANSHDR.APPROVED =1 and (TRTYPE ='SQ004' or TRTYPE ='SQ003' or TRTYPE ='SQ002' or TRTYPE ='SQ001')";
$PSTRANSDTL = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
if ($PSTRANSDTL)
$dbsales->insert_batch('PSTRANSDTL', $PSTRANSDTL);
}
public function PSDTLSCHEDULE()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('PSDTLSCHEDULE');
$sql = "select PSDtlSchedule.sys, PSDtlSchedule.lineno , PSDtlSchedule.Expected_Arv,PSDtlSchedule.QT,PSDtlSchedule.Remarks from PSDtlSchedule
join pstranshdr on pstranshdr.sys = PSDtlSchedule.sys
where pstranshdr.transcode= 53";
$PSDTLSCHEDULE = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
if ($PSDTLSCHEDULE)
$dbsales->insert_batch('PSDTLSCHEDULE', $PSDTLSCHEDULE);
}
public function get_conversion_data()
{
$item = (isset($_GET['item']) && $_GET['item']) ? (htmlspecialchars($_GET['item'])) : '';
$unitid = (isset($_GET['unitid']) && $_GET['unitid']) ? (htmlspecialchars($_GET['unitid'])) : '';
$sql = "select INUnitSetDtl.unitid,INUnitSetDtl.Conversion from initem
join INUnitSetDtl on INUnitSetDtl.unitsetid = initem.unitsetid
where initem.itemid ='{$item}' and INUnitSetDtl.unitid = '{$unitid}'";
$result = $this->db->query($sql)->row_array();
$this->db->query("COMMIT");
echo json_encode((array) $result, true);
}
public function get_project_list()
{
$sql = "select projectid from PXProjectCC";
$result = $this->db->query($sql)->result_array();
echo json_encode((array) $result, true);
}
public function VmonitoringSalesRetur()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('VmonitoringSalesRetur');
$sql = "select distinct
PSH.EntityID,
PSH.TrDate AS TANGGAL_SRO,
PSH.TrNo AS SRO,
inh.trdate AS TANGGAL_SR,
inh.trno AS SR,
rph.trdate AS TANGGAL_SRI,
rph.trno AS SRI,
PSH.ORDERNO AS NOMOR_SO,
psh.TRMANUALREF,
psh.CUSTBILLTO AS KODE_CUST,
ARC.CUSTNAME AS NAMA_CUST,
psh.FOOTERNOTE AS NOTE_FOOTER,
psh.ENTRYTIME AS WAKTU_INPUT_SRO,
PSH.Added_by AS USER_ENTRY_SRO,
PSD.ITEMID AS KODE_ITEM,
PSD.Description AS DESKRIPSI,
PSD.LOCATIONID,
PSd.QT AS QTY_SRO,
PSD.QTY AS TONASE_SRO
from pstranshdr as PSH
join pstransdtl AS PSD ON PSH.SYS = PSD.SYS
left join intransdtl as IND ON IND.ORDERNO = PSH.TrNo
left Join intranshdr as INH ON INH.sys = ind.sys
LEFT join arcustomer as arc on arc.custid = psh.custbillto
LEFT join initem as ITM on ITM.Itemid = IND.itemid
LEFT join rptranshdr as RPH on RPH.Trno = INH.Invoiceno
where PSD.transcode like '56' and psh.void_ like '0'
order by PSH.ENTITYID DESC, psh.trdate desc";
$VmonitoringSalesRetur = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('VmonitoringSalesRetur', $VmonitoringSalesRetur);
}
// public function Vsales()
// {
// $dbsales = $this->load->database('dbsales', TRUE);
// $dbsales->truncate('Vsales');
// $sql = "select
// intranshdr.trno as SJNO,
// intranshdr.trdate as SJDate,
// intranshdr.custbillto,
// intranshdr.custsellto,
// intranshdr.custshipto,
// intranshdr.custtaxto,
// intranshdr.FooterNote,
// intranshdr.InvoiceNo,
// intranshdr.OrderNo,
// intransdtl.ItemID,
// intransdtl.Description,
// intransdtl.qt,
// intransdtl.unitid,
// intransdtl.qty,
// intransdtl.unitprice,
// INTransDtl.DiscAmt,
// intransdtl.Taxable,
// intransdtl.TaxAmt,
// INTransDtl.NetAmt
// from intranshdr
// inner join intransdtl on intranshdr.sys = intransdtl.sys
// where intranshdr.transcode =3 and intranshdr.periodid >=10 and intranshdr.periodid <=30";
// $Vsales = $this->db->query($sql)->result_array();
// $dbsales->insert_batch('Vsales', $Vsales);
// }
// public function Vsales2()
// {
// $dbsales = $this->load->database('dbsales', TRUE);
// $sql = "select
// intranshdr.trno as SJNO,
// intranshdr.trdate as SJDate,
// intranshdr.custbillto,
// intranshdr.custsellto,
// intranshdr.custshipto,
// intranshdr.custtaxto,
// intranshdr.FooterNote,
// intranshdr.InvoiceNo,
// intranshdr.OrderNo,
// intransdtl.ItemID,
// intransdtl.Description,
// intransdtl.qt,
// intransdtl.unitid,
// intransdtl.qty,
// intransdtl.unitprice,
// INTransDtl.DiscAmt,
// intransdtl.Taxable,
// intransdtl.TaxAmt,
// INTransDtl.NetAmt
// from intranshdr
// inner join intransdtl on intranshdr.sys = intransdtl.sys
// where intranshdr.transcode =3 and intranshdr.periodid >=31 and intranshdr.periodid <=60";
// $Vsales = $this->db->query($sql)->result_array();
// if($Vsales)
// $dbsales->insert_batch('Vsales', $Vsales);
// }
// public function Vpembayaran()
// {
// $dbsales = $this->load->database('dbsales', TRUE);
// $dbsales->truncate('Vpembayaran');
// $sql = "select
// catrhdr.TrNo as nobayar,
// catrhdr.trdate as tanggalbayar,
// catrdtl.trno as noalokasi,
// vCADetailAllocation.invoiceno as noinvoice,
// catrdtl.amount as nilaibayar,
// catrdtl.CustomerID,
// catrdtl.Remarks
// from catrhdr
// join catrdtl on catrdtl.catrsys = catrhdr.CATRsys
// join RPPmtAllocation on RPPmtAllocation.trno = catrdtl.trno
// join vCADetailAllocation on vCADetailAllocation.paymentno = RPPmtAllocation.trno
// where catrdtl.CustomerID <> '' and CATRHdr.EntityID in (01,02,03,04,05,06,07,09,10)";
// $Vpembayaran = $this->db->query($sql)->result_array();
// if($Vpembayaran)
// $dbsales->insert_batch('Vpembayaran', $Vpembayaran);
// }
// public function Vpembayaran2()
// {
// $dbsales = $this->load->database('dbsales', TRUE);
// $sql = "select
// catrhdr.TrNo as nobayar,
// catrhdr.trdate as tanggalbayar,
// catrdtl.trno as noalokasi,
// vCADetailAllocation.invoiceno as noinvoice,
// catrdtl.amount as nilaibayar,
// catrdtl.CustomerID,
// catrdtl.Remarks
// from catrhdr
// join catrdtl on catrdtl.catrsys = catrhdr.CATRsys
// join RPPmtAllocation on RPPmtAllocation.trno = catrdtl.trno
// join vCADetailAllocation on vCADetailAllocation.paymentno = RPPmtAllocation.trno
// where catrdtl.CustomerID <> '' and CATRHdr.EntityID in (08) and CATRHdr.periodid >= 0 and CATRHdr.PeriodID <=40";
// $Vpembayaran = $this->db->query($sql)->result_array();
// if($Vpembayaran)
// $dbsales->insert_batch('Vpembayaran', $Vpembayaran);
// }
// public function Vpembayaran3()
// {
// $dbsales = $this->load->database('dbsales', TRUE);
// $sql = "select
// catrhdr.TrNo as nobayar,
// catrhdr.trdate as tanggalbayar,
// catrdtl.trno as noalokasi,
// vCADetailAllocation.invoiceno as noinvoice,
// catrdtl.amount as nilaibayar,
// catrdtl.CustomerID,
// catrdtl.Remarks
// from catrhdr
// join catrdtl on catrdtl.catrsys = catrhdr.CATRsys
// join RPPmtAllocation on RPPmtAllocation.trno = catrdtl.trno
// join vCADetailAllocation on vCADetailAllocation.paymentno = RPPmtAllocation.trno
// where catrdtl.CustomerID <> '' and CATRHdr.EntityID in (08) and CATRHdr.periodid >= 41 and CATRHdr.PeriodID <=80";
// $Vpembayaran = $this->db->query($sql)->result_array();
// if($Vpembayaran)
// $dbsales->insert_batch('Vpembayaran', $Vpembayaran);
// }
public function customerbank()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('customerbank');
$sql = "select custid,no,CurrencyID,bankname,bankaddress,accountno,AccountHolder,remarks from arcustomerbank";
$customerbank = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('customerbank', $customerbank);
}
public function Vcreditnote()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('Vcreditnote');
$sql = "select rptranshdr1.trno as nobayar, rptranshdr1.TrDate,'' as noalokasi, rptranshdr2.trno as noinvoice,
rptranshdr1.netamt as nilaibayar, rptranshdr1.custid, '' as remark
from rptranshdr as rptranshdr1
join RPPmtAllocation on RPPmtAllocation.trno = rptranshdr1.trno
join RPPmtSch on RPPmtSch.siteid = RPPmtAllocation.schsiteid and RPPmtSch.Sys = RPPmtAllocation.schsys and RPPmtSch.no_ = RPPmtAllocation.schno_
join rptranshdr as rptranshdr2 on RPPmtSch.siteid = rptranshdr2.siteid and RPPmtSch.Sys = rptranshdr2.sys
join arcustomer on arcustomer.custid = rptranshdr1.custid";
$Vcreditnote = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('Vcreditnote', $Vcreditnote);
}
public function vsalesrep()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('vsalesrep');
$sql = "select
ARSalesDirector.salesdirid as salesdirid,
ARSalesDirector.Name_ as namesalesdir,
ARSalesCountryMgr.CountryMgrID as salescountrymanagerid,
ARSalesCountryMgr.Name_ as namesalescountrymanager,
arsalesmgr.mgrID as salesmanagerid,
arsalesmgr.Name_ as namesalesmgr,
ARSalesSpv.SpvID as salesspvid,
ARSalesSpv.name_ as namesalesspv,
ARSalesrep.SalesRepID as salesrepid,
arsalesrep.name_ as namesalesrep
from ARSalesRep
join ARSalesSpv on ARSalesSpv.SpvID = ARSalesRep.SpvID
join arsalesmgr on arsalesmgr.MgrID = ARSalesSpv.MgrID
join ARSalesCountryMgr on ARSalesCountryMgr.CountryMgrID = arsalesmgr.CountryMgrID
join ARSalesDirector on ARSalesDirector.salesdirid = ARSalesCountryMgr.SalesDirID";
$vsalesrep = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('vsalesrep', $vsalesrep);
}
public function RPTRANSHDR()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('RPTRANSHDR');
$sql = "select
RPTransHdr.TRNO, RPTransHdr.TrManualRef,RPTransHdr.TrManualRef2,RPTransHdr.TRDATE,RPTransHdr.SuppID,RPTransHdr.ORDERNO,rptranshdr.sys
from RPTransHdr
where RPTransHdr.trtype = 'AP101'";
$RPTRANSHDR = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('RPTRANSHDR', $RPTRANSHDR);
}
public function RPTRANSDTL()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('RPTRANSDTL');
$sql = "select
RPTRANSDTL.Sys,
RPTRANSDTL.ItemID,
RPTRANSDTL.Description,
RPTransDtl.QT,
RPTransDtl.UNITID,
RPTRANSDTL.UnitPrice,
RPTRANSDTL.DiscAmt,
RPTRANSDTL.GrossAmt,
RPTRANSDTL.TaxAmt,
RPTRANSDTL.NetAmt
FROM RPTransHdr
JOIN RPTransDtl ON RPTransDtl.SYS = RPTransHdr.SYS
where RPTransHdr.trtype = 'AP101'";
$RPTRANSDTL = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('RPTRANSDTL', $RPTRANSDTL);
}
public function APSUPPLIER()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('APSUPPLIER');
$sql = "select suppid,suppname,address,phones,taxid,city,gl_groupid,TOP_ID from APSupplier where gl_groupid ='APGLG001'";
$APSUPPLIER = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('APSUPPLIER', $APSUPPLIER);
}
public function no_wo($trtype, $tanggal_produksi, $return = false)
{
$date = date('ym', strtotime($tanggal_produksi));
$no = $this->db->query("select NEXTNO next from PXNumbering where trtype ='{$trtype}' AND SEQSET = '{$date}'")->row();
$this->db->query("COMMIT");
$no = ($no ? $no->next : '1');
if ($return)
return $no;
echo $no;
die;
}
public function sys_wo($return = false)
{
$sys = $this->db->query("select max(sys) sys from intranshdr")->row();
$this->db->query("COMMIT");
$no = (int) $sys->sys + 1;
if ($return)
return $no;
echo $no;
die;
}
public function periodID()
{
switch (date('n')) {
case '1':
$code = 51;
break;
case '2':
$code = 52;
break;
case '3':
$code = 53;
break;
case '4':
$code = 54;
break;
case '5':
$code = 55;
break;
case '56':
$code = 56;
break;
case '57':
$code = 57;
break;
case '58':
$code = 58;
break;
case '59':
$code = 59;
break;
case '60':
$code = 60;
break;
case '61':
$code = 61;
break;
case '62':
$code = 62;
break;
default:
//case '3':
$code = 53;
break;
}
return $code;
}
public function orlansoft()
{
if (isset($_POST['data'])) {
$dbsales = $this->load->database('dbsales', TRUE);
foreach ((array) $_POST['data'] as $no) {
$wo = $dbsales->get_where('work_order', ['trno' => $no])->row();
$ps = $dbsales->query("SELECT PSDTLSCHEDULE.Expected_Arv FROM PSTRANSDTL
JOIN PSTRANSHDR ON PSTRANSHDR.SYS = PSTRANSDTL.SYS
join PSDTLSCHEDULE on PSDTLSCHEDULE.lineno = PSTRANSDTL.lineno")->row();
$sys_wo = $this->sys_wo(true);
$period = $this->periodID();
$project = $wo->project;
$trtype = sprintf('WO%s8', $wo->area_produksi);
$tanggal_produksi = $wo->tanggal_produksi;
$seqset = date('ym', strtotime($tanggal_produksi));
$item = $dbsales->get_where('item', ['itemid' => $wo->item])->row_array();
$data = [
"SiteID" => '001',
"Sys" => $sys_wo,
"PeriodID" => $period,
"EntityID" => '08',
"XRPeriod" => '1131',
"TrNo" => $no,
"TrManualRef" => $item['description'],
"TrManualRef2" => $wo->shift_produksi,
"TrDate" => $tanggal_produksi,
"TrTime" => date('H:i:s'),
"TrType" => $trtype,
"TransCode" => 7,
"Expected_Dlv" => $ps ? $ps->Expected_Arv : date('Y-m-d'),
"Expected_Arv" => $ps ? $ps->Expected_Arv : date('Y-m-d'),
"SuppID" => NULL,
"CustBillTo" => NULL,
"CustSellTo" => NULL,
"CustShipTo" => NULL,
"CustTaxTo" => NULL,
"TOP_ID" => NULL,
"TOP_days" => NULL,
"TaxCalc" => NULL,
"TaxInvoiceNo" => NULL,
"TaxInvoiceDate" => date('Y-m-d'),
"TaxSubmitDate" => date('Y-m-d'),
"SalesRepID" => NULL,
"DlvMtdID" => NULL,
"ShipToNote" => NULL,
"HeaderNote" => NULL,
"FooterNote" => NULL,
"EntryTime" => date('Y-m-d H:i:s'),
"Added_by" => 'SA',
"Changed_by" => 'SA',
"Approved" => 0,
"XRPeriodTax" => NULL,
"CurrencyID" => 'IDR',
"Posted" => 0,
"PriceID" => NULL,
"DiscPct_" => 0,
"DiscPct" => NULL,
"DiscPct2" => NULL,
"DiscPct3" => NULL,
"DiscPct4" => NULL,
"DiscPct5" => NULL,
"DiscAmt" => 0,
"CounterCOAID" => 0,
"LocationID" => NULL,
"LocationID2" => NULL,
"ManualCalc_" => NULL,
"InvoiceNo" => NULL,
"OrderNo" => NULL,
"ProjectID" => $project,
"Printed" => 0,
"Void_" => NULL,
"String1" => 0,
"String2" => NULL,
"String3" => NULL,
"String4" => NULL,
"Numeric1" => NULL,
"InvDiscChanged" => NULL,
"NetAmt" => 0,
"ControlTotal" => 0,
"UsePLComp" => 0,
"UsePLReceipt" => 0,
"PriceIDReceipt" => 0,
"TOP_ID2" => 0,
"ListID" => 0,
"Mf" => NULL,
"MfRet" => NULL,
"POS_" => NULL,
"POS_StationID" => NULL,
"POS_ShiftID" => 0,
"POS_CashierID" => 0,
"RO_" => NULL,
"UniqueTaxNo" => NULL,
"SuppShipTo" => NULL,
"Synchronized" => NULL,
"DateTime1" => date('Y-m-d'),
"DateTime2" => date('Y-m-d'),
"QtyException" => NULL,
"QtyExceptReason" => NULL,
"Last_Modified" => date('Y-m-d H:i:s'),
"Saved_" => NULL,
"DiscAmt2" => NULL,
"Date1" => date('Y-m-d H:i:s'),
"InvDiscAmt1" => 1,
"InvDiscAmt2" => 0,
"InvDiscAmt3" => 0,
"InvDiscAmt4" => NULL,
"InvDiscAmt5" => NULL,
"LocationTrNo" => NULL,
"CalcMixItemBonus" => NULL,
"LotID" => NULL,
"ShipmentNo" => NULL,
"RejectionNo" => NULL,
"WarehouseID" => NULL,
"AreaID" => 0,
"POS_ExtraDiscReason" => NULL,
"POS_ExtraDiscAmt" => NULL,
"ShipToVirtualLocation" => NULL,
"SuppPurchFrom" => NULL,
"ForwarderID" => 0
];
$columns = implode('`,`', array_keys($data));
$values = implode("','", array_values($data));
// echo "INSERT INTO `DBA`.`INTransHdr` (`{$columns}`) VALUES ('{$values}')";
$this->db->query("INSERT INTO `DBA`.`INTransHdr` (`{$columns}`) VALUES ('{$values}')");
$this->db->query("COMMIT");
//data work_order
$num = 1;
$data = [
"SiteID" => '001',
"Sys" => $sys_wo,
"LineNo" => $num++,
"TransCode" => 7,
"ItemID" => $wo->item,
"PeriodID" => $period,
"LocationID" => 'GGR-BJ',
"LocationID2" => NULL,
"ProjectID" => $project,
"QT" => (strtoupper(trim($item['Inventory_Unit'])) == 'KG') ? $wo->total_kg : $wo->total_unit,
"UnitID" => $item['Inventory_Unit'],
"Qty" => $wo->total_kg,
"Qty2" => 0,
"Description" => $item['description'],
"Remarks" => NULL,
"Length_" => NULL,
"Width_" => NULL,
"Height" => NULL,
"Diameter" => NULL,
"UnitPrice" => NULL,
"DiscPct" => NULL,
"GrossAmt" => NULL,
"DiscAmt" => NULL,
"Taxable" => NULL,
"TaxAmt" => NULL,
"Rounding" => NULL,
"NetAmt" => NULL,
"Qty_Inv" => NULL,
"Qty2_Inv" => NULL,
"OrderNo" => NULL,
"OrderLineNo" => NULL,
"InvoiceNo" => NULL,
"DiscPct2" => NULL,
"DiscPct3" => NULL,
"DiscPct4" => NULL,
"DiscPct5" => NULL,
"DiscPct_" => NULL,
"InvDiscAmt" => NULL,
"COGSUnit" => NULL,
"COGSUnit2" => NULL,
"NewLot_" => NULL,
"LineType" => NULL,
"Minus_" => 0,
"String1" => NULL,
"Numeric1" => NULL,
"Barcode" => NULL,
"ShippingUnit" => NULL,
"ShippingQty" => 0,
"QtyUsed" => NULL,
"QtySetup" => NULL,
"TrailerType" => NULL,
"ReserveID" => NULL,
"ReasonID" => NULL,
"ROTNo" => NULL,
"ROTLineNo" => NULL,
"GiftLineNo" => NULL,
"Bonus_" => NULL,
"GiftID" => NULL,
"FA_No" => NULL,
"PriceID" => NULL,
"String2" => NULL,
"String3" => NULL,
"String4" => NULL,
"UseItemPlann" => NULL,
"Changed_by" => NULL,
"Last_Modified" => date('Y-m-d H:i:s'),
"EditStock_" => 1,
"GiftID2" => NULL,
"InvDiscAmt2" => NULL,
"PromoClaimPrice" => NULL,
"ReturnType" => NULL,
"Tax_GroupID" => NULL,
"GiftIDApplyTo" => NULL,
"Last_Scan" => date('Y-m-d H:i:s'),
"WO_" => 0,
"ExtraDiscAmt" => NULL,
"Added_by" => NULL,
"EntryTime" => date('Y-m-d H:i:s'),
"CountLotID" => NULL,
"Multiplier" => 1
];
$columns = implode('`,`', array_keys($data));
$values = implode("','", array_values($data));
$this->db->query("INSERT INTO `DBA`.`INTransDtl` (`{$columns}`) VALUES ('{$values}')");
$this->db->query("COMMIT");
$issued = $dbsales->get_where('issuecomponent', ['Trno' => $no])->result_array();
foreach ($issued as $is) {
if (empty($is['UnitID'])) {
$item = $dbsales->get_where('item', ['itemid' => $is['ItemID']])->row_array();
if ($item)
$is['UnitID'] = $item['Inventory_Unit'];
}
$data = [
"SiteID" => '001',
"Sys" => $sys_wo,
"LineNo" => $num++,
"TransCode" => 8,
"ItemID" => $is['ItemID'],
"PeriodID" => $period,
"LocationID" => 'GGR',
"LocationID2" => NULL,
"ProjectID" => $project,
"QT" => $is['QT'],
"UnitID" => $is['UnitID'],
"Qty" => $is['QTY'],
"Qty2" => 0,
"Description" => $is['Description'],
"Remarks" => NULL,
"Length_" => NULL,
"Width_" => NULL,
"Height" => NULL,
"Diameter" => NULL,
"UnitPrice" => NULL,
"DiscPct" => NULL,
"GrossAmt" => NULL,
"DiscAmt" => NULL,
"Taxable" => NULL,
"TaxAmt" => NULL,
"Rounding" => NULL,
"NetAmt" => NULL,
"Qty_Inv" => NULL,
"Qty2_Inv" => NULL,
"OrderNo" => NULL,
"OrderLineNo" => NULL,
"InvoiceNo" => NULL,
"DiscPct2" => NULL,
"DiscPct3" => NULL,
"DiscPct4" => NULL,
"DiscPct5" => NULL,
"DiscPct_" => NULL,
"InvDiscAmt" => NULL,
"COGSUnit" => NULL,
"COGSUnit2" => NULL,
"NewLot_" => NULL,
"LineType" => NULL,
"Minus_" => 0,
"String1" => NULL,
"Numeric1" => NULL,
"Barcode" => NULL,
"ShippingUnit" => NULL,
"ShippingQty" => 0,
"QtyUsed" => NULL,
"QtySetup" => NULL,
"TrailerType" => NULL,
"ReserveID" => NULL,
"ReasonID" => NULL,
"ROTNo" => NULL,
"ROTLineNo" => NULL,
"GiftLineNo" => NULL,
"Bonus_" => NULL,
"GiftID" => NULL,
"FA_No" => NULL,
"PriceID" => NULL,
"String2" => NULL,
"String3" => NULL,
"String4" => NULL,
"UseItemPlann" => NULL,
"Changed_by" => NULL,
"Last_Modified" => date('Y-m-d H:i:s'),
"EditStock_" => 1,
"GiftID2" => NULL,
"InvDiscAmt2" => NULL,
"PromoClaimPrice" => NULL,
"ReturnType" => NULL,
"Tax_GroupID" => NULL,
"GiftIDApplyTo" => NULL,
"Last_Scan" => date('Y-m-d H:i:s'),
"WO_" => 0,
"ExtraDiscAmt" => NULL,
"Added_by" => NULL,
"EntryTime" => date('Y-m-d H:i:s'),
"CountLotID" => NULL,
"Multiplier" => 1
];
$columns = implode('`,`', array_keys($data));
$values = implode("','", array_values($data));
$this->db->query("INSERT INTO `DBA`.`INTransDtl` (`{$columns}`) VALUES ('{$values}')");
$this->db->query("COMMIT");
}
//update nextno PXNumbering
$nextno = (int) $this->no_wo($trtype, $tanggal_produksi, true) + 1;
$this->db->query("update PXNumbering set nextno = {$nextno} where trtype ='{$trtype}' AND SEQSET = '{$seqset}'");
$this->db->query("COMMIT");
}
echo 'OK';
} else {
echo 'FAILED';
}
die;
}
public function orlansoftsql()
{
if (isset($_POST['data'])) {
$dbsales = $this->load->database('dbsales', TRUE);
foreach ((array) $_POST['data'] as $no) {
$wo = $dbsales->get_where('work_order', ['trno' => $no])->row();
$ps = $dbsales->query("SELECT PSDTLSCHEDULE.Expected_Arv FROM PSTRANSDTL
JOIN PSTRANSHDR ON PSTRANSHDR.SYS = PSTRANSDTL.SYS
join PSDTLSCHEDULE on PSDTLSCHEDULE.lineno = PSTRANSDTL.lineno")->row();
$sys_wo = $this->sys_wo(true);
$period = $this->periodID();
$project = $wo->project;
$trtype = sprintf('WO%s8', $wo->area_produksi);
$tanggal_produksi = $wo->tanggal_produksi;
$seqset = date('ym', strtotime($tanggal_produksi));
$item = $dbsales->get_where('item', ['itemid' => $wo->item])->row_array();
$data = [
"SiteID" => '001',
"Sys" => $sys_wo,
"PeriodID" => $period,
"EntityID" => '08',
"XRPeriod" => '1131',
"TrNo" => $no,
"TrManualRef" => $item['description'],
"TrManualRef2" => $wo->shift_produksi,
"TrDate" => $tanggal_produksi,
"TrTime" => date('H:i:s'),
"TrType" => $trtype,
"TransCode" => 7,
"Expected_Dlv" => $ps ? $ps->Expected_Arv : date('Y-m-d'),
"Expected_Arv" => $ps ? $ps->Expected_Arv : date('Y-m-d'),
"SuppID" => NULL,
"CustBillTo" => NULL,
"CustSellTo" => NULL,
"CustShipTo" => NULL,
"CustTaxTo" => NULL,
"TOP_ID" => NULL,
"TOP_days" => NULL,
"TaxCalc" => NULL,
"TaxInvoiceNo" => NULL,
"TaxInvoiceDate" => date('Y-m-d'),
"TaxSubmitDate" => date('Y-m-d'),
"SalesRepID" => NULL,
"DlvMtdID" => NULL,
"ShipToNote" => NULL,
"HeaderNote" => NULL,
"FooterNote" => NULL,
"EntryTime" => date('Y-m-d H:i:s'),
"Added_by" => 'SA',
"Changed_by" => 'SA',
"Approved" => 0,
"XRPeriodTax" => NULL,
"CurrencyID" => 'IDR',
"Posted" => 0,
"PriceID" => NULL,
"DiscPct_" => 0,
"DiscPct" => NULL,
"DiscPct2" => NULL,
"DiscPct3" => NULL,
"DiscPct4" => NULL,
"DiscPct5" => NULL,
"DiscAmt" => 0,
"CounterCOAID" => 0,
"LocationID" => NULL,
"LocationID2" => NULL,
"ManualCalc_" => NULL,
"InvoiceNo" => NULL,
"OrderNo" => NULL,
"ProjectID" => $project,
"Printed" => 0,
"Void_" => NULL,
"String1" => 0,
"String2" => NULL,
"String3" => NULL,
"String4" => NULL,
"Numeric1" => NULL,
"InvDiscChanged" => NULL,
"NetAmt" => 0,
"ControlTotal" => 0,
"UsePLComp" => 0,
"UsePLReceipt" => 0,
"PriceIDReceipt" => 0,
"TOP_ID2" => 0,
"ListID" => 0,
"Mf" => NULL,
"MfRet" => NULL,
"POS_" => NULL,
"POS_StationID" => NULL,
"POS_ShiftID" => 0,
"POS_CashierID" => 0,
"RO_" => NULL,
"UniqueTaxNo" => NULL,
"SuppShipTo" => NULL,
"Synchronized" => NULL,
"DateTime1" => date('Y-m-d'),
"DateTime2" => date('Y-m-d'),
"QtyException" => NULL,
"QtyExceptReason" => NULL,
"Last_Modified" => date('Y-m-d H:i:s'),
"Saved_" => NULL,
"DiscAmt2" => NULL,
"Date1" => date('Y-m-d H:i:s'),
"InvDiscAmt1" => 1,
"InvDiscAmt2" => 0,
"InvDiscAmt3" => 0,
"InvDiscAmt4" => NULL,
"InvDiscAmt5" => NULL,
"LocationTrNo" => NULL,
"CalcMixItemBonus" => NULL,
"LotID" => NULL,
"ShipmentNo" => NULL,
"RejectionNo" => NULL,
"WarehouseID" => NULL,
"AreaID" => 0,
"POS_ExtraDiscReason" => NULL,
"POS_ExtraDiscAmt" => NULL,
"ShipToVirtualLocation" => NULL,
"SuppPurchFrom" => NULL,
"ForwarderID" => 0
];
$columns = implode('`,`', array_keys($data));
$values = implode("','", array_values($data));
echo "INSERT INTO `DBA`.`INTransHdr` (`{$columns}`) VALUES ('{$values}')\n\n";
//data work_order
$num = 1;
$data = [
"SiteID" => '001',
"Sys" => $sys_wo,
"LineNo" => $num++,
"TransCode" => 7,
"ItemID" => $wo->item,
"PeriodID" => $period,
"LocationID" => 'GGR-BJ',
"LocationID2" => NULL,
"ProjectID" => $project,
"QT" => (strtoupper(trim($item['Inventory_Unit'])) == 'KG') ? $wo->total_kg : $wo->total_unit,
"UnitID" => $item['Inventory_Unit'],
"Qty" => $wo->total_kg,
"Qty2" => 0,
"Description" => $item['description'],
"Remarks" => NULL,
"Length_" => NULL,
"Width_" => NULL,
"Height" => NULL,
"Diameter" => NULL,
"UnitPrice" => NULL,
"DiscPct" => NULL,
"GrossAmt" => NULL,
"DiscAmt" => NULL,
"Taxable" => NULL,
"TaxAmt" => NULL,
"Rounding" => NULL,
"NetAmt" => NULL,
"Qty_Inv" => NULL,
"Qty2_Inv" => NULL,
"OrderNo" => NULL,
"OrderLineNo" => NULL,
"InvoiceNo" => NULL,
"DiscPct2" => NULL,
"DiscPct3" => NULL,
"DiscPct4" => NULL,
"DiscPct5" => NULL,
"DiscPct_" => NULL,
"InvDiscAmt" => NULL,
"COGSUnit" => NULL,
"COGSUnit2" => NULL,
"NewLot_" => NULL,
"LineType" => NULL,
"Minus_" => 0,
"String1" => NULL,
"Numeric1" => NULL,
"Barcode" => NULL,
"ShippingUnit" => NULL,
"ShippingQty" => 0,
"QtyUsed" => NULL,
"QtySetup" => NULL,
"TrailerType" => NULL,
"ReserveID" => NULL,
"ReasonID" => NULL,
"ROTNo" => NULL,
"ROTLineNo" => NULL,
"GiftLineNo" => NULL,
"Bonus_" => NULL,
"GiftID" => NULL,
"FA_No" => NULL,
"PriceID" => NULL,
"String2" => NULL,
"String3" => NULL,
"String4" => NULL,
"UseItemPlann" => NULL,
"Changed_by" => NULL,
"Last_Modified" => date('Y-m-d H:i:s'),
"EditStock_" => 1,
"GiftID2" => NULL,
"InvDiscAmt2" => NULL,
"PromoClaimPrice" => NULL,
"ReturnType" => NULL,
"Tax_GroupID" => NULL,
"GiftIDApplyTo" => NULL,
"Last_Scan" => date('Y-m-d H:i:s'),
"WO_" => 0,
"ExtraDiscAmt" => NULL,
"Added_by" => NULL,
"EntryTime" => date('Y-m-d H:i:s'),
"CountLotID" => NULL,
"Multiplier" => 1
];
$columns = implode('`,`', array_keys($data));
$values = implode("','", array_values($data));
echo "INSERT INTO `DBA`.`INTransDtl` (`{$columns}`) VALUES ('{$values}')\n\n";
$issued = $dbsales->get_where('issuecomponent', ['Trno' => $no])->result_array();
foreach ($issued as $is) {
if (empty($is['UnitID'])) {
$item = $dbsales->get_where('item', ['itemid' => $is['ItemID']])->row_array();
if ($item)
$is['UnitID'] = $item['Inventory_Unit'];
}
$data = [
"SiteID" => '001',
"Sys" => $sys_wo,
"LineNo" => $num++,
"TransCode" => 8,
"ItemID" => $is['ItemID'],
"PeriodID" => $period,
"LocationID" => 'GGR',
"LocationID2" => NULL,
"ProjectID" => $project,
"QT" => $is['QT'],
"UnitID" => $is['UnitID'],
"Qty" => $is['QTY'],
"Qty2" => 0,
"Description" => $is['Description'],
"Remarks" => NULL,
"Length_" => NULL,
"Width_" => NULL,
"Height" => NULL,
"Diameter" => NULL,
"UnitPrice" => NULL,
"DiscPct" => NULL,
"GrossAmt" => NULL,
"DiscAmt" => NULL,
"Taxable" => NULL,
"TaxAmt" => NULL,
"Rounding" => NULL,
"NetAmt" => NULL,
"Qty_Inv" => NULL,
"Qty2_Inv" => NULL,
"OrderNo" => NULL,
"OrderLineNo" => NULL,
"InvoiceNo" => NULL,
"DiscPct2" => NULL,
"DiscPct3" => NULL,
"DiscPct4" => NULL,
"DiscPct5" => NULL,
"DiscPct_" => NULL,
"InvDiscAmt" => NULL,
"COGSUnit" => NULL,
"COGSUnit2" => NULL,
"NewLot_" => NULL,
"LineType" => NULL,
"Minus_" => 0,
"String1" => NULL,
"Numeric1" => NULL,
"Barcode" => NULL,
"ShippingUnit" => NULL,
"ShippingQty" => 0,
"QtyUsed" => NULL,
"QtySetup" => NULL,
"TrailerType" => NULL,
"ReserveID" => NULL,
"ReasonID" => NULL,
"ROTNo" => NULL,
"ROTLineNo" => NULL,
"GiftLineNo" => NULL,
"Bonus_" => NULL,
"GiftID" => NULL,
"FA_No" => NULL,
"PriceID" => NULL,
"String2" => NULL,
"String3" => NULL,
"String4" => NULL,
"UseItemPlann" => NULL,
"Changed_by" => NULL,
"Last_Modified" => date('Y-m-d H:i:s'),
"EditStock_" => 1,
"GiftID2" => NULL,
"InvDiscAmt2" => NULL,
"PromoClaimPrice" => NULL,
"ReturnType" => NULL,
"Tax_GroupID" => NULL,
"GiftIDApplyTo" => NULL,
"Last_Scan" => date('Y-m-d H:i:s'),
"WO_" => 0,
"ExtraDiscAmt" => NULL,
"Added_by" => NULL,
"EntryTime" => date('Y-m-d H:i:s'),
"CountLotID" => NULL,
"Multiplier" => 1
];
$columns = implode('`,`', array_keys($data));
$values = implode("','", array_values($data));
echo "INSERT INTO `DBA`.`INTransDtl` (`{$columns}`) VALUES ('{$values}')";
}
}
echo 'OK';
} else {
echo 'FAILED';
}
die;
}
public function cek_balance()
{
if (isset($_POST['ds'], $_POST['de'], $_POST['location'], $_POST['gl_group'])) {
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('cek_balance');
$location = ($_POST['location'] == 'all') ? '' : "AND a.locationid ='" . $_POST['location'] . "'";
$sql = "select
b.blocked, b.gl_groupid,c.description,a.locationid,a.desclocation,a.itemid,
a.descitem, a.inventory_unit,a.beginqty_,a.inqty_,a.outqty_,a.endqty_
from IN_LocationStockDaily(('" . $_POST['ds'] . "'),('" . $_POST['de'] . "'),'0','z','0','z') as a
full outer join initem as b on a.itemid = b.itemid
full outer join INGLGroup as c on b.gl_groupid = c.ID
where b.gl_groupid ='" . $_POST['gl_group'] . "' " . $location . " and not a.locationid = 'null' order by a.locationid asc";
$cek_balance = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
if ($cek_balance) {
$dbsales->insert_batch('cek_balance', $cek_balance);
echo 'OK';
die;
}
}
}
public function gl_group_balance()
{
if (isset($_POST['ds'], $_POST['de'], $_POST['location'])) {
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('gl_group_balance');
$location = ($_POST['location'] == 'all') ? '' : "AND a.locationid ='" . $_POST['location'] . "'";
$sql = "select distinct
b.gl_groupid, c.Description
from IN_LocationStockDaily(('" . $_POST['ds'] . "'),('" . $_POST['de'] . "'),'0','z','0','z') as a
full outer join initem as b on a.itemid = b.itemid
full outer join INGLGroup as c on b.gl_groupid = c.ID
where b.GL_GroupID like 'IN%' and not a.locationid = 'null' " . $location;
$gl_group_balance = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
if ($gl_group_balance) {
$dbsales->insert_batch('gl_group_balance', $gl_group_balance);
echo 'OK';
die;
}
}
}
public function get_purchase_request_outstanding_data()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('purchase_request_outstanding');
$sql = "SELECT COUNT(*) TOTAL from PR_Outstanding(0,'z',0,'z',0,'z',0,'z',getdate()) as a
WHERE qtyPRoutstanding <>0 AND wo_ = 0
GROUP BY a.itemid, a.itemdesc, a.unitid,a.locationid";
$total = $this->db->query($sql)->num_rows();
$limit = 1000;
for ($offset = 1; $offset < $total; $offset += $limit) {
$sql = "SELECT TOP {$limit} START AT {$offset} a.entityid,a.trtype,a.requestorid,a.requestorname, a.trdate,a.trno, a.itemid, a.itemdesc, a.qt as qt, a.qtorder as qtorder, a.qtproutstanding as qtproutstanding, a.unitid, a.locationid from PR_Outstanding(0,'z',0,'z',0,'z',0,'z',getdate()) as a
WHERE qtyPRoutstanding <>0 AND wo_ = 0";
$purchase_request_outstanding = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('purchase_request_outstanding', $purchase_request_outstanding);
}
}
public function get_purchase_order_outstanding_data()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('purchase_order_outstanding');
$sql = "SELECT COUNT(*) total FROM PO_Outstanding ('0', 'z', '0', 'z', '0', 'z', '0', 'z', getdate()) AS a
JOIN prpo AS b ON a.sys = b.sys AND a.lineno = b.lineno";
$total = $this->db->query($sql)->row()->total;
$limit = 1000;
for ($offset = 1; $offset < $total; $offset += $limit) {
// $sql = "SELECT TOP {$limit} START AT {$offset}
// a.entityid,a.trtype,a.trtypedesc,a.trdate,a.trno,a.suppid,a.suppname,a.itemid, a.itemdesc,a.qt,a.qt,a.qtreturn,a.qtreceived,a.qtoutstanding,a.unitid,a.locationid,b.PRNo
// FROM PO_Outstanding ('0', 'z', '0', 'z', '0', 'z', '0', 'z', getdate()) AS a
// JOIN prpo AS b ON a.sys = b.sys AND a.lineno = b.lineno";
$sql = "SELECT TOP {$limit} START AT {$offset} a.trdate,a.trno,a.suppid,a.suppname,a.itemid, a.itemdesc,a.qt,a.qtreturn,a.qtreceived,a.qtoutstanding,a.unitid,a.locationid,b.PRNo from PO_Outstanding ('0', 'z', '0', 'z', '0', 'z', '0', 'z', getdate()) as a
join prpo as b on a.sys = b.sys and a.lineno = b.lineno
where a.suppid='idsupplier' AND a.qtoutstanding <> 0";
$purchase_order_outstanding = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$purchase_order_outstanding_supplier = array_map(function ($arr) {
return [
'suppid' => $arr['suppid'],
'suppname' => $arr['suppname']
];
}, $purchase_order_outstanding);
$dbsales->insert_batch('purchase_order_outstanding', $purchase_order_outstanding);
$dbsales->insert_batch('purchase_order_outstanding_supplier', $purchase_order_outstanding_supplier);
}
$this->create_appoinment_with_po();
}
public function create_appoinment_with_po()
{
$dbsales = $this->load->database('dbsales', TRUE);
$tanggaljamdaftar = '0000-00-00 00:00:00';
$tanggaljammasuk = '0000-00-00 00:00:00';
$tanggaljamkeluar = '0000-00-00 00:00:00';
$userid = 1306;
$date = date('Y-m-d');
$pos = $dbsales->limit(1000)
->group_by('trno')
->get_where('purchase_order_outstanding', 'trno not in(select pono from appointment_with_po)')->result();
foreach ($pos as $po) {
$res = $dbsales->query("
SELECT
MAX(CAST(nomortamu AS UNSIGNED)) AS nomortamu
FROM
Tamu
WHERE
nomortamu LIKE CONCAT(DATE_FORMAT(CURRENT_DATE(), '%m'), '%')
AND YEAR(created_date) = YEAR(CURRENT_DATE())
AND LENGTH(nomortamu) >= 5")->row();
$nomortamu = sprintf('%s%03s', date('m'), substr($res->nomortamu, 1) + 1);
$sql = sprintf("INSERT INTO appointment_with_po VALUES(NULL, '%s', '%s', '%s', '%s', '%s')", $nomortamu, $po->suppid, $po->suppname, $po->trno, $date);
$dbsales->query($sql);
$sql = sprintf("INSERT INTO Tamu VALUES (NULL, '%s', NULL, NULL, '%s', NULL, NULL, '%s', NULL, NULL, NULL, 0, '%s', '%s', '%s', NULL, NULL, '%s', NULL, NULL, '%s', '%s')", $nomortamu, $po->suppid . ' - ' . $po->suppname, $po->trno, $tanggaljamdaftar, $tanggaljammasuk, $tanggaljamkeluar, $dbsales->insert_id(), $userid, $date);
$dbsales->query($sql);
}
}
public function detailitembalance()
{
$item = htmlspecialchars(@$_GET['item']);
$page = (int) @$_GET['page'];
$limit = (int) @$_GET['limit'];
$offset = (($page - 1) * $limit) + 1;
$startDate = htmlspecialchars(@$_GET['startdate']);
$endDate = htmlspecialchars(@$_GET['enddate']);
$locationId = htmlspecialchars(@$_GET['locationid']);
$all = $results = [];
if ($item) {
$baseSql = "SELECT
INLocation.Description,
INItem.Description,
INTransHdr.TrDate,
INTransHdr.TrNo,
INTransHdr.TrTime,
PXTransCode.Desc_Eng,
INItemBalance.BeginQty,
INItem.Inventory_Unit,
INTransDtl.Qty,
(INItemBalance.BeginQty + INItemBalance.inQty- INItemBalance.OutQty) as endqty
FROM (((((((((((((DBA.INTransHdr INTransHdr
INNER JOIN DBA.INTransDtl INTransDtl ON (INTransHdr.SiteID=INTransDtl.SiteID)
AND (INTransHdr.Sys=INTransDtl.Sys))
INNER JOIN DBA.PXTRType PXTRType ON INTransHdr.TrType=PXTRType.TrType)
LEFT OUTER JOIN DBA.APSupplier APSupplier ON INTransHdr.SuppID=APSupplier.SuppID)
LEFT OUTER JOIN DBA.ARCustomer ARCustomer ON INTransHdr.CustBillTo=ARCustomer.CustID)
INNER JOIN DBA.PXSetup PXSetup ON INTransHdr.TransCode>=PXSetup.ID)
INNER JOIN DBA.PXPeriod PXPeriod ON INTransHdr.PeriodID=PXPeriod.PeriodID)
INNER JOIN DBA.PXTransCode PXTransCode ON INTransDtl.TransCode=PXTransCode.Code)
INNER JOIN DBA.INLocation INLocation ON INTransDtl.LocationID=INLocation.LocationID)
LEFT OUTER JOIN DBA.INLocation INLocation_Trf ON INTransDtl.LocationID2=INLocation_Trf.LocationID)
LEFT OUTER JOIN DBA.INItemBalance INItemBalance ON ((INTransDtl.ItemID=INItemBalance.ItemID)
AND (INTransDtl.LocationID=INItemBalance.LocationID)) AND (INTransDtl.PeriodID=INItemBalance.PeriodID))
INNER JOIN DBA.INItem INItem ON INTransDtl.ItemID=INItem.ItemID)
INNER JOIN DBA.INWarehouse INWarehouse ON INLocation.WarehouseID=INWarehouse.ID)
INNER JOIN DBA.PXEntity PXEntity ON INWarehouse.EntityID=PXEntity.EntityID)
INNER JOIN DBA.PXSite PXSite ON INWarehouse.SiteID=PXSite.SiteID
WHERE INTransHdr.Void_=0
AND PXTRType.Budget_=0
AND (INTransDtl.LocationID>='0' AND INTransDtl.LocationID<='Z')
AND INTransDtl.ItemID='$item'
AND (INTransDtl.PeriodID>=0 AND INTransDtl.PeriodID<=999999)";
$conditions = [];
if (!empty($startDate)) {
$conditions[] = "INTransHdr.TrDate >= '$startDate'";
}
if (!empty($endDate)) {
$conditions[] = "INTransHdr.TrDate <= '$endDate'";
}
if (!empty($locationId)) {
$conditions[] = "INTransDtl.LocationID = '$locationId'";
}
if (!empty($conditions)) {
$baseSql .= " AND " . implode(" AND ", $conditions);
}
$baseSql .= " ORDER BY INTransDtl.LocationID, INTransDtl.PeriodID, INTransHdr.TrDate, INTransHdr.TrTime, INTransHdr.TrNo, INTransDtl.TransCode";
$allSql = $baseSql;
$limitSql = "SELECT TOP {$limit} START AT {$offset} " . substr($baseSql, 6); // Removing "SELECT" from baseSql to prepend "SELECT TOP ... START AT ..."
$imageSql = "SELECT Path_ FROM INItemExtFiles WHERE itemid = '$item'";
$all = $this->db->query($allSql)->result_array();
$result = $this->db->query($limitSql)->result_array();
$images = $this->db->query($imageSql)->result_array();
$this->db->query("COMMIT");
$results = [
'total' => count($all),
'data' => $result,
'images' => $images
];
echo json_encode((array) $results, true);
}
}
// public function detailitembalance()
// {
// $item = htmlspecialchars(@$_GET['item']);
// $page = (int) @$_GET['page'];
// $limit = (int) @$_GET['limit'];
// $offset = (($page - 1) * $limit) + 1;
// $all = $results = [];
// if($item) {
// $sql = "SELECT
// INLocation.Description,
// INItem.Description,
// INTransHdr.TrDate,
// INTransHdr.TrNo,
// INTransHdr.TrTime,
// PXTransCode.Desc_Eng,
// INItemBalance.BeginQty,
// INItem.Inventory_Unit,
// INTransDtl.Qty
// FROM (((((((((((((DBA.INTransHdr INTransHdr
// INNER JOIN DBA.INTransDtl INTransDtl ON (INTransHdr.SiteID=INTransDtl.SiteID)
// AND (INTransHdr.Sys=INTransDtl.Sys))
// INNER JOIN DBA.PXTRType PXTRType ON INTransHdr.TrType=PXTRType.TrType)
// LEFT OUTER JOIN DBA.APSupplier APSupplier ON INTransHdr.SuppID=APSupplier.SuppID)
// LEFT OUTER JOIN DBA.ARCustomer ARCustomer ON INTransHdr.CustBillTo=ARCustomer.CustID)
// INNER JOIN DBA.PXSetup PXSetup ON INTransHdr.TransCode>=PXSetup.ID)
// INNER JOIN DBA.PXPeriod PXPeriod ON INTransHdr.PeriodID=PXPeriod.PeriodID)
// INNER JOIN DBA.PXTransCode PXTransCode ON INTransDtl.TransCode=PXTransCode.Code)
// INNER JOIN DBA.INLocation INLocation ON INTransDtl.LocationID=INLocation.LocationID)
// LEFT OUTER JOIN DBA.INLocation INLocation_Trf ON INTransDtl.LocationID2=INLocation_Trf.LocationID)
// LEFT OUTER JOIN DBA.INItemBalance INItemBalance ON ((INTransDtl.ItemID=INItemBalance.ItemID)
// AND (INTransDtl.LocationID=INItemBalance.LocationID)) AND (INTransDtl.PeriodID=INItemBalance.PeriodID))
// INNER JOIN DBA.INItem INItem ON INTransDtl.ItemID=INItem.ItemID)
// INNER JOIN DBA.INWarehouse INWarehouse ON INLocation.WarehouseID=INWarehouse.ID)
// INNER JOIN DBA.PXEntity PXEntity ON INWarehouse.EntityID=PXEntity.EntityID)
// INNER JOIN DBA.PXSite PXSite ON INWarehouse.SiteID=PXSite.SiteID
// WHERE INTransHdr.Void_=0
// AND PXTRType.Budget_=0
// AND (INTransDtl.LocationID>='0'
// AND INTransDtl.LocationID<='Z')
// AND INTransDtl.ItemID='$item'
// AND (INTransDtl.PeriodID>=0 AND INTransDtl.PeriodID<=999999)
// ORDER BY INTransDtl.LocationID,
// INTransDtl.PeriodID,
// INTransHdr.TrDate, INTransHdr.
// TrTime,
// INTransHdr.TrNo,
// INTransDtl.TransCode";
// $all = $this->db->query($sql)->result_array();
// $sql = "SELECT TOP {$limit} START AT {$offset}
// INLocation.Description,
// INItem.Description,
// INTransHdr.TrDate,
// INTransHdr.TrNo,
// INTransHdr.TrTime,
// PXTransCode.Desc_Eng,
// INItemBalance.BeginQty,
// INItem.Inventory_Unit,
// INTransDtl.Qty
// FROM (((((((((((((DBA.INTransHdr INTransHdr
// INNER JOIN DBA.INTransDtl INTransDtl ON (INTransHdr.SiteID=INTransDtl.SiteID)
// AND (INTransHdr.Sys=INTransDtl.Sys))
// INNER JOIN DBA.PXTRType PXTRType ON INTransHdr.TrType=PXTRType.TrType)
// LEFT OUTER JOIN DBA.APSupplier APSupplier ON INTransHdr.SuppID=APSupplier.SuppID)
// LEFT OUTER JOIN DBA.ARCustomer ARCustomer ON INTransHdr.CustBillTo=ARCustomer.CustID)
// INNER JOIN DBA.PXSetup PXSetup ON INTransHdr.TransCode>=PXSetup.ID)
// INNER JOIN DBA.PXPeriod PXPeriod ON INTransHdr.PeriodID=PXPeriod.PeriodID)
// INNER JOIN DBA.PXTransCode PXTransCode ON INTransDtl.TransCode=PXTransCode.Code)
// INNER JOIN DBA.INLocation INLocation ON INTransDtl.LocationID=INLocation.LocationID)
// LEFT OUTER JOIN DBA.INLocation INLocation_Trf ON INTransDtl.LocationID2=INLocation_Trf.LocationID)
// LEFT OUTER JOIN DBA.INItemBalance INItemBalance ON ((INTransDtl.ItemID=INItemBalance.ItemID)
// AND (INTransDtl.LocationID=INItemBalance.LocationID)) AND (INTransDtl.PeriodID=INItemBalance.PeriodID))
// INNER JOIN DBA.INItem INItem ON INTransDtl.ItemID=INItem.ItemID)
// INNER JOIN DBA.INWarehouse INWarehouse ON INLocation.WarehouseID=INWarehouse.ID)
// INNER JOIN DBA.PXEntity PXEntity ON INWarehouse.EntityID=PXEntity.EntityID)
// INNER JOIN DBA.PXSite PXSite ON INWarehouse.SiteID=PXSite.SiteID
// WHERE INTransHdr.Void_=0
// AND PXTRType.Budget_=0
// AND (INTransDtl.LocationID>='0'
// AND INTransDtl.LocationID<='Z')
// AND INTransDtl.ItemID='$item'
// AND (INTransDtl.PeriodID>=0 AND INTransDtl.PeriodID<=999999)
// ORDER BY INTransDtl.LocationID,
// INTransDtl.PeriodID,
// INTransHdr.TrDate, INTransHdr.
// TrTime,
// INTransHdr.TrNo,
// INTransDtl.TransCode";
// $result = $this->db->query($sql)->result_array();
// $this->db->query("COMMIT");
// }
// $results = [
// 'total' => count($all),
// 'data' => $result
// ];
// echo json_encode((array) $results, true);
// }
// public function sync_marketing()
// {
// $dbsql = $this->load->database('dbsales', TRUE);
// $tables = [
// 'mastercustomer' => 'ARCustomer',
// 'CustTopId' => 'ARCustTopID',
// 'SalesRoute' => 'ARSalesRepRoute',
// 'routecalendar' => 'ARSalesRepRouteCalendar',
// 'routecalendardtl' => 'ARSalesRepRouteCalendarDtl',
// 'routepattern' => 'ARSalesRoutePattern',
// 'routepatterndtl' => 'ARSalesRoutePatternDtl',
// 'transcode' => 'PXTransCode',
// 'masteritem' => 'INItem',
// 'arsalesrepcallhdr' => 'ARSalesRepCallHdr',
// 'arsalesrepcalldtl' => 'ARSalesRepCallDtl',
// 'PxTopId' => 'PXTOPID',
// 'PSTransHdr' => 'PSTransHdr',
// 'PSTransDtl' => 'PSTransDtl',
// ];
// foreach ($tables as $table => $table2) {
// $sql = "SELECT * FROM {$table2}";
// if ($table == 'PSTransHdr' || $table == 'PSTransDtl') {
// $sql = "SELECT * FROM {$table2} WHERE TransCode = 52";
// }
// $data = $this->db->query($sql)->result_array();
// $this->db->query("COMMIT");
// if (!empty($data)) {
// if (!$dbsql->truncate($table)) {
// continue;
// }
// if (!$dbsql->insert_batch($table, $data)) {
// continue;
// }
// }
// }
// }
public function sync_marketing()
{
$dbsales = $this->load->database('dbsales', TRUE);
$tables = [
'mastercustomer' => 'ARCustomer',
'CustTopId' => 'ARCustTopID',
'SalesRoute' => 'ARSalesRepRoute',
'routecalendar' => 'ARSalesRepRouteCalendar',
'routecalendardtl' => 'ARSalesRepRouteCalendarDtl',
'routepattern' => 'ARSalesRoutePattern',
'routepatterndtl' => 'ARSalesRoutePatternDtl',
'transcode' => 'PXTransCode',
'masteritem' => 'INItem',
'arsalesrepcallhdr' => 'ARSalesRepCallHdr',
'arsalesrepcalldtl' => 'ARSalesRepCallDtl',
'PxTopId' => 'PXTOPID',
// 'PSTransHdr' => 'PSTransHdr',
// 'PSTransDtl' => 'PSTransDtl',
];
foreach ($tables as $localTable => $serverTable) {
$sql = "SELECT * FROM {$serverTable}";
// if ($localTable == 'PSTransHdr' || $localTable == 'PSTransDtl') {
// $sql = "SELECT * FROM {$serverTable} WHERE TransCode = 52";
// }
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
if (!empty($data)) {
$dbsales->trans_begin();
try {
if (!$dbsales->truncate($localTable)) {
throw new Exception('Failed to truncate table ' . $localTable);
}
if (!$dbsales->insert_batch($localTable, $data)) {
throw new Exception('Failed to insert data into table ' . $localTable);
}
$dbsales->trans_commit();
} catch (Exception $e) {
$dbsales->trans_rollback();
}
}
}
}
public function sys_pstrans($return = false)
{
$sys = $this->db->query("SELECT MAX(Sys) AS Sys FROM PSTransHdr")->row();
$this->db->query("COMMIT");
$no = (int) $sys->Sys + 1;
if ($return)
return $no;
echo $no;
die;
}
public function get_period_pstrans()
{
date_default_timezone_set('Asia/Jakarta');
$today = date('Y-m-d');
$sql = "SELECT PeriodID FROM pxperiod WHERE '$today' >= StartDate AND '$today' <= EndDate";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
return $data[0]['PeriodID'];
}
public function get_xrperiod_pstrans()
{
date_default_timezone_set('Asia/Jakarta');
$today = date('Y-m-d');
$sql = "SELECT XRPeriod FROM PXXRPeriod WHERE '$today' >= Date1 AND '$today' <= Date2";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
return $data[0]['XRPeriod'];
}
public function get_topid_pstrans($custId)
{
$sql = "SELECT TOP_ID FROM ARCustTopID WHERE CustID = '$custId'";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
return $data[0]['TOP_ID'];
}
public function getsiteid()
{
$sql = "SELECT SiteID, Description FROM pxsite";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode($data);
}
public function gettrtype()
{
$sql = "SELECT TrType, Description FROM PXTRType";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode($data);
}
public function getdeliverymtd()
{
$sql = "SELECT DeliveryMtdID, Description FROM PXDeliveryMtd";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
echo json_encode($data);
}
public function getcurrency($xrperiod)
{
$sql = "SELECT Ccy1 FROM pxxrate WHERE XRPeriod = '$xrperiod'";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
return $data[0]['Ccy1'];
}
public function getTaxRate($taxgroup)
{
date_default_timezone_set('Asia/Jakarta');
$today = date('Y-m-d');
$sql = "SELECT Rate FROM INTaxDetail WHERE ID = '$taxgroup' AND FromDate <= '$today' AND ToDate >= '$today'";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
return $data[0]['Rate'];
}
public function getNextNo($trtype)
{
date_default_timezone_set('Asia/Jakarta');
$today = date('ym');
$sql = "SELECT NextNo FROM PXNumbering WHERE TrType = '$trtype' AND SeqSet = '$today'";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$no = $data[0]['NextNo'] ? (int) $data[0]['NextNo'] : 0;
$no++;
return $no;
}
public function convert_unit($qty, $unitid, $unitsetid)
{
$sql = "SELECT * FROM INUnitSetDtl WHERE UnitSetID = '$unitsetid' AND Conversion = 1";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$data = $data[0];
if ($data['UnitID'] == $unitid) {
return $qty;
} else {
$sql = "SELECT * FROM INUnitSetDtl WHERE UnitSetID = '$unitsetid' AND UnitID = '$unitid'";
$data = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$data = $data[0];
$qty = $qty / $data['Conversion'];
return $qty;
}
}
function incrementSequence($sequence)
{
$letters = str_split($sequence);
for ($i = count($letters) - 1; $i >= 0; $i--) {
if ($letters[$i] == 'Z') {
$letters[$i] = 'A';
} else {
$letters[$i] = chr(ord($letters[$i]) + 1);
break;
}
}
return implode('', $letters);
}
function generateNextCustID($lastCustID)
{
preg_match('/([A-Z]+)-([A-Z]{2})(\d{3})/', $lastCustID, $matches);
$prefix = $matches[1]; // CMS
$sequence = $matches[2]; // AA
$number = (int) $matches[3]; // 001
$number++;
if ($number > 999) {
$number = 1;
$sequence = $this->incrementSequence($sequence);
}
$formattedNumber = sprintf('%03d', $number);
return $prefix . '-' . $sequence . $formattedNumber;
}
function get_customer_id()
{
$sql = "SELECT TOP(1) CustID FROM ARCustomer
WHERE CustID LIKE 'CMS-%'
ORDER BY RegisterDate DESC";
$data = $this->db->query($sql)->result_array();
if ($data) {
$lastCustID = $data[0]['CustID'];
return $this->generateNextCustID($lastCustID);
} else {
return 'CMS-AA001';
}
}
function generate_address($address_array)
{
$address_array = json_decode(json_encode($address_array), true);
foreach ($address_array as $key => $value) {
if ($value == 'null') {
$address_array[$key] = null;
}
}
unset($address_array['id'], $address_array['id_noo']);
$address_array = array_filter($address_array, function ($value) {
return !is_null($value) && $value !== '';
});
return implode(", ", $address_array);
}
public function post_noo_to_arcustomer()
{
$dbsales = $this->load->database('dbsales', TRUE);
$sql = "SELECT * FROM masternoo WHERE id = '" . $_POST['id'] . "'";
$noo = $dbsales->query($sql)->row();
$sql = "SELECT * FROM nooaddress WHERE id = '{$noo->alamat_kantor}'";
$alamat_kantor = $dbsales->query($sql)->row();
$kantor = $this->generate_address($alamat_kantor);
$sql = "SELECT * FROM nooaddress WHERE id = '{$noo->alamat_owner}'";
$alamat_owner = $dbsales->query($sql)->row();
$owner = $this->generate_address($alamat_owner);
$sql = "SELECT * FROM nooaddress WHERE id = '{$noo->alamat_npwp}'";
$alamat_npwp = $dbsales->query($sql)->row();
$npwp = $this->generate_address($alamat_npwp);
$sql = "SELECT lat_ci, lon_ci FROM marketing_activity WHERE cust_id = '{$noo->id}'";
$gps = $dbsales->query($sql)->row();
$orlanid = 'CMSOPS01';
$custid = $this->get_customer_id();
$data = [
'CustID' => $custid,
'CustName' => $noo->nama_perusahaan,
'Address' => $kantor,
'PostCode' => $alamat_kantor->kode_pos,
'Phones' => $noo->nohp_owner,
'Telex' => NULL,
'Fax' => NULL,
'email' => $noo->email_owner,
'TaxID' => $noo->no_npwp,
'TaxDescription1' => '',
'TaxDescription2' => '',
'TaxName' => $noo->nama_npwp,
'TaxAddress' => $npwp,
'City' => $alamat_kantor->kabupaten_kota,
'State' => $alamat_kantor->provinsi,
'CountryID' => 'ID',
'Affiliate_' => 0,
'GL_GroupID' => "ARGLG01",
'ParentID' => $custid,
'BlockShip_' => 1,
'BlockAR_' => 1,
'BlockSale_' => 0,
'DispatchID' => 'UNFDEF',
'ForwarderID' => 'UNFDEF',
'AreaID' => $_POST['areaid'],
'Contact_Sales' => $noo->nama_jabatan_penjualan . ' - ' . $noo->nohp_penjualan,
'Contact_Acc' => $noo->nama_jabatan_keuangan . ' - ' . $noo->nohp_keuangan,
'Our_ID' => $_POST['ourid'],
'CurrencyID' => 'IDR',
'SalesRepID' => null,
'GroupA_ID' => 0,
'GroupB_ID' => 0,
'GroupC_ID' => 0,
'GroupD_ID' => 0,
'PriceID' => $_POST['priceid'],
'DeliveryMtdID' => 'FOBS',
'FormSetID' => '',
'CustStatus' => 1,
'CreditStatus' => 1,
'CollectorID' => $_POST['collector'],
'CreditLimit' => 0,
'RegisterDate' => date('Y-m-d H:i:s'),
'LastTransDate' => NULL,
'DayToSubmit' => 0,
'DayToCollect' => 0,
'IgnoreCL_' => 0,
'Remarks' => '',
'ConsiderOverdueDays_' => 0,
'CreditDays' => 0,
'TaxGroupID' => $_POST['taxgroup'],
'InvTaxCode' => '',
'PKP_' => 0,
'POSMember_' => 0,
'POSMemberNo' => '',
'Added_by' => $orlanid,
'Changed_by' => $orlanid,
'InvoiceOption' => NULL,
'TOPType' => NULL,
'TOPDays' => NULL,
'TOPFromDate' => NULL,
'DateOfBirth' => $noo->tgl_mulai_usaha,
'Gender' => 1,
'PersonalID' => $noo->id_owner,
'PromoID' => '',
'LatestUpdate' => date('Y-m-d H:i:s'),
'ExtraField1' => NULL,
'ExtraField2' => NULL,
'ExtraField3' => NULL,
'ExtraField4' => NULL,
'Photo' => NULL,
'GpsLatitude' => $gps->lat_ci,
'GpsLongitude' => $gps->lon_ci,
'TaxOfficeID' => '',
'GpsPostalCode' => NULL,
'Owner_Name' => $noo->nama_owner,
'Barcode' => '',
'Last_Modified' => date('Y-m-d H:i:s'),
'DayToSubmitPattern' => NULL,
'DayToCollectPattern' => NULL,
'Numeric1' => 0,
'Numeric2' => 0,
'DateTime_' => NULL,
'UseMaxReturnDate' => 0,
'MaxReturnDate' => 0,
'ConsignmentLocID' => '',
'AllowMultipleOrder' => 0,
'POSMemberExpired' => NULL,
'PersonalName' => $noo->nama_owner,
'PersonalAddress' => $owner,
'MaxUnpaidSO' => 0,
'UseCollSubmission' => 0,
'ItemTaxGroupID' => NULL,
'SubdistrictID' => '',
'POSMemberType' => NULL,
'VehicleGroupID' => '',
'UseStrataDiscount' => 0,
'StrataDiscountID' => '',
'UseDispatchPlaceID' => 0,
'DispatchPlaceID' => ''
];
$columns = implode('`,`', array_keys($data));
$values = implode(',', array_map(function ($value) {
if (is_null($value) || $value == 'null') {
return 'NULL';
} elseif (is_numeric($value) && !is_string($value)) {
return $value;
} else {
return "'" . addslashes($value) . "'";
}
}, array_values($data)));
$sql = "INSERT INTO `DBA`.`ARCustomer` (`{$columns}`) VALUES ({$values})";
$this->db->query($sql);
$this->db->query("COMMIT");
echo "OK";
// echo $sql;
}
public function post_pstranshdr()
{
date_default_timezone_set('Asia/Jakarta');
$dbsales = $this->load->database('dbsales', TRUE);
$manualref = "From Call Management/" . $_POST['salesrepid'] . "/" . date('d-m-Y');
$xrperiod = $this->get_xrperiod_pstrans();
$currency = $this->getcurrency($xrperiod);
$seqset = date('ym');
$nextNo = $this->getNextNo($_POST['trtype']);
$trno = $_POST['trtype'] . $seqset . sprintf('%05d', $nextNo);
$trtype = 'TRIAL';
$sys = $this->sys_pstrans(true);
$periodid = $this->get_period_pstrans();
$siteid = strval($_POST['siteid']);
$orlanid = 'CMSOPS01';
$data = [
"SiteID" => $siteid,
"Sys" => $sys,
"PeriodID" => $periodid,
"EntityID" => $_POST['entityid'],
"XRPeriod" => $xrperiod,
"TrNo" => $trno,
"TrManualRef" => $manualref,
"TrManualRef2" => isset($_POST['notes']) ? $_POST['notes'] : '',
"TrDate" => date('Y-m-d'),
"TrTime" => date('H:i:s'),
"TrType" => $trtype,
"TransCode" => 52,
"Expected_Dlv" => $_POST['expecteddlv'],
"Expected_Arv" => $_POST['expectedarv'],
"CustBillTo" => $_POST['custid'],
"CustSellTo" => $_POST['custid'],
"CustShipTo" => $_POST['custid'],
"CustTaxTo" => $_POST['custid'],
"TOP_ID" => $this->get_topid_pstrans($_POST['custid']),
"TOP_days" => NULL,
"TaxCalc" => "E",
"SalesRepID" => $_POST['salesrepid'],
"DlvMtdID" => $_POST['dlvmtdid'],
"HeaderNote" => '',
"FooterNote" => '',
"EntryTime" => date('Y-m-d H:i:s'),
"Added_by" => $orlanid,
"Changed_by" => $orlanid,
"Approved" => 0,
"CurrencyID" => $currency,
"SuppID" => NULL,
"PS_" => "SO",
"PriceID" => NULL,
"DiscPct_" => 0,
"DiscPct" => 0.0000,
"DiscPct2" => 0.0000,
"DiscPct3" => 0.0000,
"DiscPct4" => 0.0000,
"DiscPct5" => 0.0000,
"DiscAmt" => 0.0000,
"ProjectID" => NULL,
"Printed" => 0,
"Void_" => 0,
"String1" => NULL,
"String2" => NULL,
"String3" => NULL,
"String4" => NULL,
"Numeric1" => NULL,
"InvDiscChanged" => 0,
"NetAmt" => NULL,
"ControlTotal" => NULL,
"OrderToSiteID" => NULL,
"IntransitLocID" => NULL,
"DestinationLocID" => NULL,
"OrderNo" => NULL,
"SuppShipTo" => NULL,
"CloseSQ" => 0,
"QtyException" => 0,
"QtyExceptReason" => NULL,
"Barcode" => NULL,
"BudgetException" => 0,
"SendEmail" => NULL,
"Last_Modified" => date('Y-m-d H:i:s'),
"DiscAmt2" => 0.0000,
"Date1" => NULL,
"InvDiscAmt1" => NULL,
"InvDiscAmt2" => NULL,
"InvDiscAmt3" => NULL,
"InvDiscAmt4" => NULL,
"InvDiscAmt5" => NULL,
"CalcMixItemBonus" => 0,
"DownShip_" => NULL,
"LotID" => NULL,
"AreaID" => NULL,
"DisDiscRecalc" => 0,
"ReasonID" => 0,
"FollowupId" => NULL,
"Expired" => NULL,
"SuppPurchFrom" => NULL,
"OrderNo2" => NULL,
"Odometer" => NULL,
"DateTime1" => NULL,
"DateTime2" => NULL,
"Process_" => 0,
"ForwarderID" => NULL,
"ClosedDate" => NULL
];
$this->db->trans_start();
$columns = implode('`,`', array_keys($data));
$values = implode(',', array_map(function ($value) {
if (is_null($value)) {
return 'NULL';
} elseif (is_numeric($value) && !is_string($value)) {
return $value;
} else {
return "'" . addslashes($value) . "'";
}
}, array_values($data)));
$sql = "INSERT INTO `DBA`.`PSTransHdr` (`{$columns}`) VALUES ({$values})";
$this->db->query($sql);
$sql = "SELECT mrt.quantity, mrt.description, mrt.itemid, mi.Height, mi.Width_, mi.Length_, mi.Tax_GroupID, mrt.unitid, mrt.unit, mi.UnitSetID
FROM marketing_report_to mrt
JOIN masteritem mi ON mi.ItemID = mrt.itemid
WHERE mrt.idMA = '{$_POST['idma']}'";
$items = $dbsales->query($sql)->result_array();
foreach ($items as $index => $item) {
$unit_price = $item['unit'];
$qty = $item['quantity'];
$taxRate = $this->getTaxRate($item['Tax_GroupID']);
$disc = 0 / 100;
$tax = $taxRate / 100;
$gross = $unit_price * $qty;
$disc_amt = $gross * $disc;
$taxable = $gross - $disc_amt;
$tax_amt = $taxable * $tax;
$net_amt = $taxable + $tax_amt;
$qtyConverted = $this->convert_unit($item['quantity'], $item['unitid'], $item['UnitSetID']);
$data = [
"SiteID" => $_POST['siteid'],
"Sys" => $sys,
"LineNo" => $index + 1,
"TransCode" => 52,
"ItemID" => $item['itemid'],
"PeriodID" => $periodid,
"LocationID" => $_POST['locationid'],
"LocationID2" => $currency,
"ProjectID" => null,
"QT" => $item['quantity'],
"UnitID" => $item['unitid'],
"Qty" => $qtyConverted,
"Qty2" => null,
"Description" => $item['description'],
"Remarks" => '',
"Length_" => $item['Length_'],
"Width_" => $item['Width_'],
"Height" => $item['Height'],
"Diameter" => null,
"UnitPrice" => $unit_price,
"DiscPct" => $disc,
"GrossAmt" => $gross,
"DiscAmt" => $disc_amt,
"Taxable" => $taxable,
"TaxAmt" => $tax_amt,
"Rounding" => null,
"NetAmt" => $net_amt,
"LinkNo" => null,
"LinkLineNo" => 0,
"InvoiceNo" => null,
"DiscPct2" => 0.0000,
"DiscPct3" => 0.0000,
"DiscPct4" => 0.0000,
"DiscPct5" => 0.0000,
"DiscPct_" => 0,
"WO_" => 0,
"InvDiscAmt" => null,
"LineType" => null,
"SalesRepID" => null,
"String1" => null,
"Numeric1" => null,
"Barcode" => null,
"PRUnitPrice" => null,
"OrderNo" => null,
"OrderLineNo" => $index + 1,
"PO_" => 0,
"GiftLineNo" => 0,
"Bonus_" => 0,
"GiftID" => null,
"String2" => null,
"String3" => null,
"String4" => null,
"PriceID" => null,
"Changed_By" => $orlanid,
"Last_Modified" => date('Y-m-d H:i:s'),
"GiftID2" => null,
"InvDiscAmt2" => 0.0000,
"ProcessCalcDtl" => 0,
"ReturnType" => 0,
"GiftIDApplyTo" => null,
"Added_by" => $orlanid,
"EntryTime" => date('Y-m-d H:i:s'),
"Multiplier" => 0.0000,
"CalcDiscPct" => 0.0000,
"CalcPctDiscAmt" => 0.0000,
"CalcDiscPct2" => 0.0000,
"CalcPctDiscAmt2" => 0.0000,
"CalcDiscPct3" => 0.0000,
"CalcPctDiscAmt3" => 0.0000,
"CalcDiscPct4" => 0.0000,
"CalcPctDiscAmt4" => 0.0000,
"CalcDiscPct5" => 0.0000,
"CalcPctDiscAmt5" => 0.0000,
"CalcDiscAmt" => 0.0000,
"CalcDiscAmtCombined" => 0.0000
];
$columns = implode('`,`', array_keys($data));
// $values = implode("','", array_values($data));
$values = implode(',', array_map(function ($value) {
if (is_null($value)) {
return 'NULL';
} elseif (is_int($value) || is_float($value)) {
return $value;
} else {
return "'" . addslashes($value) . "'";
}
}, array_values($data)));
$sql = "INSERT INTO `DBA`.`PSTransDtl` (`{$columns}`) VALUES ({$values})";
$this->db->query($sql);
}
$sql = "SELECT SUM(NetAmt) AS NetAmt FROM PSTransDtl WHERE Sys = $sys";
$netAmt = $this->db->query($sql)->row()->NetAmt;
$sql = "UPDATE PSTransHdr SET NetAmt = $netAmt WHERE Sys = $sys";
$this->db->query($sql);
$sql = "UPDATE PXNumbering SET NextNo = $nextNo WHERE TrType = '{$_POST['trtype']}' AND SeqSet = '$seqset'";
$this->db->query($sql);
$this->db->trans_complete();
if ($this->db->trans_status() === FALSE) {
echo "FAILED";
} else {
echo $sys;
}
}
public function getPStrans()
{
// $sql = "SELECT TOP 1 * FROM PSTransHdr WHERE TrType = 'TRIAL' ORDER BY Sys DESC";
// $sql = "SELECT * FROM PSTransHdr WHERE Sys = 43591";
// $today = date('ym');
$sql = "SELECT * FROM PSTransHdr WHERE TrType = 'TRIAL' ORDER BY Sys DESC";
// $sql = "SELECT * FROM PSTransDtl WHERE Sys = 43649";
// $sql = "SELECT fk.foreign_table_id AS FKTableID,
// fk.primary_table_id AS PKTableID,
// fk.foreign_key_id AS FKID,
// ft.table_name AS ForeignTable,
// pt.table_name AS PrimaryTable,
// fc.column_name AS ForeignColumn,
// pc.column_name AS PrimaryColumn
// FROM SYSFOREIGNKEY fk
// JOIN SYSTABLE ft ON fk.foreign_table_id = ft.table_id
// JOIN SYSTABLE pt ON fk.primary_table_id = pt.table_id
// JOIN SYSCOLUMN fc ON fk.foreign_table_id = fc.table_id AND fk.foreign_key_id = fc.column_id
// JOIN SYSCOLUMN pc ON fk.primary_table_id = pc.table_id AND fk.primary_table_id = pc.table_id
// WHERE ft.table_name = 'PSTransHdr' AND pt.table_name = 'PXSite';";
$data = $this->db->query($sql)->result_array();
echo "<pre>" . print_r($data, true) . "</pre>";
}
public function get_province_list()
{
$sql = "SELECT ID, Decription FROM ARAreab WHERE ParentID = 'IDN'";
$data = $this->db->query($sql)->result_array();
echo json_encode($data);
}
public function get_city_list()
{
$province = $_GET['province'];
$sql = "SELECT ID, Decription FROM ARAreac WHERE ParentID = '$province'";
$data = $this->db->query($sql)->result_array();
echo json_encode($data);
}
public function get_district_list()
{
$city = $_GET['city'];
$sql = "SELECT ID, Description FROM ARAread WHERE ParentID = '$city'";
$data = $this->db->query($sql)->result_array();
echo json_encode($data);
}
public function get_village_list()
{
$district = $_GET['district'];
$sql = "SELECT AreaID, Description FROM ARAreae WHERE ParentID = '$district'";
$data = $this->db->query($sql)->result_array();
echo json_encode($data);
}
public function get_price_list()
{
$today = date('Y-m-d');
$sql = "SELECT PriceID, Description FROM inpricemaster WHERE FromDate <= '$today' AND ToDate >= '$today'";
$data = $this->db->query($sql)->result_array();
echo json_encode($data);
}
public function get_tax_group()
{
$sql = "SELECT ID, Description FROM intaxgroup";
$data = $this->db->query($sql)->result_array();
echo json_encode($data);
}
public function get_collector()
{
$sql = "SELECT CollectorID, Name_ FROM ARCollector";
$data = $this->db->query($sql)->result_array();
echo json_encode($data);
}
public function get_unbilled_invoice()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('unpaid_invoice');
$sql = "select
AR_SP_UnpaidInvoices_asof_check.Trx as Transaksi,
left (AR_SP_UnpaidInvoices_asof_check.CustID,'3') as Grp_Customer,
arsalesrep.string1 as Group_,
AR_SP_UnpaidInvoices_asof_check.SalesRepID as Kode_Sales,
AR_SP_UnpaidInvoices_asof_check.SalesrepName as Nama_Sales,
AR_SP_UnpaidInvoices_asof_check.CustID as Kode_Customer,
AR_SP_UnpaidInvoices_asof_check.CustName as Nama_Customer,
AR_SP_UnpaidInvoices_asof_check.trtype as Tipe_Transaksi,
AR_SP_UnpaidInvoices_asof_check.trno as Nomor_Invoice,
rptranshdr.taxinvoiceno as Nomor_Faktur,
AR_SP_UnpaidInvoices_asof_check.trdate as Tanggal_Invoice,
AR_SP_UnpaidInvoices_asof_check.Duedate as Jatuh_Tempo,
AR_SP_UnpaidInvoices_asof_check.age as Umur_Piutang,
AR_SP_UnpaidInvoices_asof_check.Amount as Nilai_Invoice,
rptranshdr.tax as pajak,
AR_SP_UnpaidInvoices_asof_check.AmountPaid as Pembayaran,
AR_SP_UnpaidInvoices_asof_check.Discrepancy as Sisa_piutang
from AR_SP_UnpaidInvoices_asof_check(GETDATE(),'0','0','z','0','z') inner join arsalesrep on arsalesrep.salesrepid=AR_SP_UnpaidInvoices_asof_check.SalesRepID
join rptranshdr on AR_SP_UnpaidInvoices_asof_check.trno = rptranshdr.trno";
$unpaid_invoice = $this->db->query($sql)->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('unpaid_invoice', $unpaid_invoice);
}
public function save_employee($query = null)
{
if (isset($_POST['data'])) {
$res = false;
foreach ($_POST['data'] as $data) {
extract($data);
$tanggal = date('Y-m-d H:i:s');
$sql = "INSERT INTO DBA.PXEmployee ('EmployeeID','EmpName','RegisterDate','Added_by','Changed_by','Last_Modified','Block_','GpsPostalCode','GpsLatitude','GpsLongitude','SuppID','ListID_CashAdv','ListID_CashAdvTemp') VALUES('{$nik}','{$nama}','{$tanggal}','$added_by','-','{$tanggal}','0','-','-','-','-','-','-')";
if ($query) {
echo ($sql . "\n\n");
} else {
$res = $this->db->query($sql);
$this->db->query("COMMIT");
}
}
if ($res)
die("OK");
}
}
public function data_supplier()
{
$dbsales = $this->load->database('dbsales', TRUE);
$dbsales->truncate('datasupplier');
$datas = $this->db->query("SELECT suppid,suppname,address,phones,city,state,GL_GroupID,blockreceive_,BlockAP_,BlockPurchase_,Contact_Sales FROM apsupplier")->result_array();
$this->db->query("COMMIT");
$dbsales->insert_batch('datasupplier', $datas);
}
public function read($table, $single = null)
{
$res = $this->db->query("select * from $table");
echo "<pre>" . print_r($single ? $res->row_array() : $res->result_array(), true) . "</pre>";
}
/*****************
unifood dokumen end
******************/
public function sync_db1()
{
$this->belum_invoice(1, 7);
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db2()
{
$this->belum_invoice(8, 10);
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db3()
{
$this->sudah_invoice();
$this->item();
$this->statuskirim();
$this->itembalance();
$this->period();
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db4()
{
$this->employee();
$this->customer();
$this->loadsheet();
// $this->transaction_type();
// $this->user_sync();
// $this->customer();
// $this->item();
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db5()
{
// $this->document_shipment();
// $this->document_invoice();
$this->VmonitoringSalesRetur();
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db6()
{
// $this->document_shipment();
// $this->document_invoice();
// $this->Vsales();
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db7()
{
// $this->Vsales2();
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db8()
{
// $this->Vpembayaran();
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db9()
{
// $this->Vpembayaran2();
$this->PSTRANSHDR();
$this->PSTRANSDTL();
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db10()
{
// $this->Vpembayaran3();
$this->PSDTLSCHEDULE();
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db11()
{
$this->customerbank();
$this->Vcreditnote();
$this->vsalesrep();
file_put_contents('cron_log.txt', 'Sync Customers, StatusKirim, Sales Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db12()
{
$this->RPTRANSHDR();
file_put_contents('cron_log.txt', 'RPTRANSHDR' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db13()
{
$this->RPTRANSDTL();
file_put_contents('cron_log.txt', 'RPTRANSDTL' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db14()
{
$this->APSUPPLIER();
$this->location();
file_put_contents('cron_log.txt', 'RPTRANSDTL' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db15()
{
$this->sync_marketing();
file_put_contents('cron_log.txt', 'Sync Marketing Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db16()
{
$this->get_unbilled_invoice();
file_put_contents('cron_log.txt', 'Sync Unbilled Invoice Run At ' . date('Y-m-d H:i:s') . "\r\n", FILE_APPEND);
}
public function sync_db17()
{
$this->getPStrans();
// file_put_contents('cron_log.txt',' Sync PStrans Run At '.date('Y-m-d H:i:s')."\r\n",FILE_APPEND);
}
}Editor is loading...
Leave a Comment