API tools faq
paste
fiqriachmada

Mtpb_barang.php

fiqriachmada
Mar 4th, 2021
69
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 21.11 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. defined('BASEPATH') OR exit('No direct script access allowed');
  3.  
  4. Class Mtpb_barang extends CI_Model
  5. {
  6.     var $nowdt, $nowd, $nowt, $basesql, $table, $table_id;
  7.  
  8.     function __construct()
  9.     {
  10.         parent::__construct();
  11.         $this->nowdt = date('Y-m-d H:i:s');
  12.         $this->nowd = date('Y-m-d');
  13.         $this->nowt = date('H:i:s');
  14.         $this->table = getdbtpb($this).'.tpb_barang';
  15.         $this->table_id = 'ID';
  16.         $this->basesql = "select * from $this->table where ID is not null";
  17.     }
  18.  
  19.     function view()
  20.     {
  21.         $sql = $this->basesql;
  22.         $res = $this->db->query($sql);
  23.  
  24.         $data = array();
  25.         foreach ($res->result() as $r){
  26.             $data[] = $r;
  27.         }
  28.  
  29.         return $data;
  30.     }
  31.  
  32.     function viewDT($in, $opt = true)
  33.     {
  34.         $start = $in->start;
  35.         $sqlmain = $this->basesql;
  36.         $sql = "select * from ($sqlmain) pa";
  37.         $res = $this->db->query($sql);
  38.         $recordsTotal = $res->num_rows();
  39.  
  40.         $sql .= dtSearch($this, $in);
  41.         $res = $this->db->query($sql);
  42.         $recordsFiltered = $res->num_rows();
  43.  
  44.         $sql .= dtSort($in);
  45.         $sql .= dtLimit($in);
  46.         $res = $this->db->query($sql);
  47.         $num = $res->num_rows();
  48.  
  49.         $data = array();
  50.         if($num>0){
  51.             $i=$start+1;
  52.             foreach ($res->result() as $r){
  53.                 $r->no = $i;
  54.  
  55.                 if($opt){
  56.                     $r->option = '<a href="'.base_url($this->d->_controller.'/'.$this->d->_method.'/edit/'.$r->{$this->table_id}).'" class="btn btn-xs btn-success"><i class="fal fa fa-edit"></i></a> ';
  57.                     $r->option .= '<a href="javascript://" onclick="confirmDialog(this)" data-header="Confirm Delete" data-body="Do you want to delete this data?" data-url="'.base_url($this->d->_controller.'/'.$this->d->_method.'/delete/'.$r->{$this->table_id}).'" class="btn btn-xs btn-danger"><i class="fal fa fa-trash"></i></a>';
  58.                 }
  59.                 $data[] = $r;
  60.                 $i++;
  61.             }
  62.         }
  63.         $k = new stdClass();
  64.         $k->draw = $in->draw;
  65.         $k->recordsTotal = $recordsTotal;
  66.         $k->recordsFiltered = $recordsFiltered;
  67.         $k->data = $data;
  68.  
  69.         return $k;
  70.     }
  71.  
  72.     function viewReportCustomsDT($in, $opt = true)
  73.     {
  74.         $start = $in->start;
  75.         $sqlmain = "SELECT ta.KODE_BARANG, ta.URAIAN, ta.KODE_SATUAN, ta.HARGA_INVOICE, ta.CIF_RUPIAH, ta.ID_HEADER, tb.KODE_DOKUMEN_PABEAN, td.URAIAN_DOKUMEN_PABEAN, td.STATUS AS STATUS_DOKUMEN_PABEAN, tb.NOMOR_AJU, tb.TANGGAL_AJU, tb.NOMOR_DAFTAR, tb.TANGGAL_DAFTAR, tb.NAMA_PEMASOK, tb.KODE_NEGARA_PEMASOK, tc.URAIAN_NEGARA AS URAIAN_NEGARA_PEMASOK, tb.KODE_VALUTA, te.URAIAN_VALUTA FROM ".getdbtpb($this).".tpb_barang ta INNER JOIN ".getdbtpb($this).".tpb_header tb ON tb.ID = ta.ID_HEADER LEFT JOIN ".getdbtpb($this).".referensi_negara tc ON tc.KODE_NEGARA = tb.KODE_NEGARA_PEMASOK LEFT JOIN ".getdbtpb($this).".referensi_dokumen_pabean td ON td.KODE_DOKUMEN_PABEAN = tb.KODE_DOKUMEN_PABEAN LEFT JOIN ".getdbtpb($this).".referensi_valuta te ON te.KODE_VALUTA = tb.KODE_VALUTA";
  76.         if (isset($in->STATUS_DOKUMEN_PABEAN)) {
  77.             $sqlmain .= " AND td.STATUS = '".$in->STATUS_DOKUMEN_PABEAN."' ";
  78.         }
  79.         if (isset($in->KODE_DOKUMEN_PABEAN)) {
  80.             $sqlmain .= " AND tb.KODE_DOKUMEN_PABEAN = '".$in->KODE_DOKUMEN_PABEAN."' ";
  81.         }
  82.         if (isset($in->TANGGAL_AJU_START)) {
  83.             $sqlmain .= " AND tb.TANGGAL_AJU >= '".$in->TANGGAL_AJU_START."' ";
  84.         }
  85.         if (isset($in->TANGGAL_AJU_END)) {
  86.             $sqlmain .= " AND tb.TANGGAL_AJU <= '".$in->TANGGAL_AJU_END."' ";
  87.         }
  88.         if (isset($in->TANGGAL_DAFTAR_START)) {
  89.             $sqlmain .= " AND tb.TANGGAL_DAFTAR >= '".$in->TTANGGAL_DAFTAR_START."' ";
  90.         }
  91.         if (isset($in->TANGGAL_DAFTAR_END)) {
  92.             $sqlmain .= " AND tb.TANGGAL_DAFTAR <= '".$in->TANGGAL_DAFTAR_END."' ";
  93.         }
  94.         $sql = "select * from ($sqlmain) pa";
  95.         $res = $this->db->query($sql);
  96.         $recordsTotal = $res->num_rows();
  97.  
  98.         $sql .= dtSearch($this, $in);
  99.         $res = $this->db->query($sql);
  100.         $recordsFiltered = $res->num_rows();
  101.  
  102.         $sql .= dtSort($in);
  103.         $sql .= dtLimit($in);
  104.         $res = $this->db->query($sql);
  105.         $num = $res->num_rows();
  106.  
  107.         $data = array();
  108.         if($num>0){
  109.             $i=$start+1;
  110.             foreach ($res->result() as $r){
  111.                 $r->no = $i;
  112.                 $data[] = $r;
  113.                 $i++;
  114.             }
  115.         }
  116.         $k = new stdClass();
  117.         $k->draw = $in->draw;
  118.         $k->recordsTotal = $recordsTotal;
  119.         $k->recordsFiltered = $recordsFiltered;
  120.         $k->data = $data;
  121.  
  122.         return $k;
  123.     }
  124.  
  125.     function viewCustomsIn($in, $opt = true)
  126.     {
  127.         $start = $in->start;
  128.         $sqlmain = "select td.kode_mutasi kode_receive, td.tanggal_terima, ta.*, tb.detail, tc.URAIAN_DOKUMEN_PABEAN from (select *, (case when NAMA_PEMASOK is null then NAMA_PENGIRIM else NAMA_PEMASOK end) as supplier from ".getdbtpb($this).".tpb_header where (KODE_DOKUMEN_PABEAN = '23' or KODE_DOKUMEN_PABEAN = '262' or KODE_DOKUMEN_PABEAN = '40' or KODE_DOKUMEN_PABEAN = '27IN') and NOMOR_DAFTAR is not null) ta LEFT JOIN (select json_arrayagg(json_object('SERI_BARANG',SERI_BARANG,'KODE_BARANG',KODE_BARANG,'URAIAN',URAIAN,'KODE_SATUAN',KODE_SATUAN,'JUMLAH_SATUAN',JUMLAH_SATUAN,'hargarp',(case when CIF is null then HARGA_PENYERAHAN else CIF_RUPIAH end),'hargaasli',(case when CIF is null then '' else CIF end),'ID_HEADER',ID_HEADER)) as detail, ID_HEADER from ".getdbtpb($this).".tpb_barang group by ID_HEADER) tb on ta.ID = tb.ID_HEADER LEFT JOIN ".getdbtpb($this).".referensi_dokumen_pabean tc on ta.KODE_DOKUMEN_PABEAN = tc.KODE_DOKUMEN_PABEAN LEFT JOIN (select b.ID_HEADER, a.* from t_wh a LEFT JOIN (select * from t_dn where deleted_at is null) b on a.id_dn = b.id_dn where a.deleted_at is null and a.id_jenis_mutasi = '9') td on ta.ID = td.ID_HEADER LEFT JOIN tpb_approval tx on ta.ID = tx.ID_HEADER";
  129.         $sqlmain = "select * from ($sqlmain) pa where ID is not null";
  130.         if (isset($in->dokumenbc)) {
  131.             $i =1;
  132.             $sqlmain .= ' and (';
  133.             foreach ($in->dokumenbc as $row) {
  134.                 if ($i==1) {
  135.                     $sqlmain .= " KODE_DOKUMEN_PABEAN = '".$row."' ";
  136.                 } else {
  137.                     $sqlmain .= " OR KODE_DOKUMEN_PABEAN = '".$row."' ";
  138.                 }
  139.                 $i++;
  140.             }
  141.             $sqlmain .= ' ) ';
  142.         }
  143.  
  144.         $sqlmain = "select * from ($sqlmain) pa where ID is not null";
  145.  
  146.         if (isset($in->tglawal)) {
  147.             $tglawal = reverseDate($in->tglawal);
  148.             $sqlmain .= " and $in->typeFilter >= '$tglawal'";
  149.         }
  150.         if (isset($in->tglakhir)) {
  151.             $tglakhir = reverseDate($in->tglakhir);
  152.             $sqlmain .= " and $in->typeFilter <= '$tglakhir'";
  153.         }
  154.        
  155.        
  156.         $sql = "select * from ($sqlmain) pa";
  157.         // printJSON($sql);
  158.         $res = $this->db->query($sql);
  159.         $recordsTotal = $res->num_rows();
  160.  
  161.         $sql .= dtSearch($this, $in);
  162.         $res = $this->db->query($sql);
  163.         $recordsFiltered = $res->num_rows();
  164.  
  165.         $sql .= dtSort($in);
  166.         $sql .= dtLimit($in);
  167.         $res = $this->db->query($sql);
  168.         $num = $res->num_rows();
  169.  
  170.         $data = array();
  171.         if($num>0){
  172.             $i=$start+1;
  173.             foreach ($res->result() as $r){
  174.                 $r->no = $i;
  175.  
  176.                 $r->detail = a2o(json_decode(sterilizeJSON($r->detail)));
  177.                 $item = '';
  178.                 $qty = '';
  179.                 $hargarp = '';
  180.                 $hargaasli = '';
  181.                 foreach ($r->detail as $row) {
  182.                     $item .= "<p style='margin:0;padding:0;'>".$row->URAIAN."</p><small style='margin:0;padding:0;'>".$row->KODE_BARANG."</small><br><br>";
  183.                     $qty .= "<p style='margin:0;padding:0;'>".number_format($row->JUMLAH_SATUAN,3)."</p><small style='margin:0;padding:0;'>".$row->KODE_SATUAN."</small><br><br>";
  184.                     $hargarp .= "<p style='margin:0;padding:0;'>".number_format(floatval($row->hargarp),2)."</p><small style='margin:0;padding:0;'>&nbsp;</small><br><br>";
  185.                     $hargaasli .= "<p style='margin:0;padding:0;'>".number_format(floatval($row->hargaasli),2)."</p><small style='margin:0;padding:0;'>&nbsp;</small><br><br>";
  186.                 }
  187.                 $r->URAIAN = $item;
  188.                 $r->JUMLAH_SATUAN = $qty;
  189.                 $r->hargarp = $hargarp;
  190.                 $r->hargaasli = $hargaasli;
  191.  
  192.                 $data[] = $r;
  193.                 $i++;
  194.             }
  195.         }
  196.         $k = new stdClass();
  197.         $k->draw = $in->draw;
  198.         $k->recordsTotal = $recordsTotal;
  199.         $k->recordsFiltered = $recordsFiltered;
  200.         $k->data = $data;
  201.  
  202.         return $k;
  203.     }
  204.  
  205.      function toExcel($in) {
  206.         //  printJSON($in);
  207.  
  208.         $originalDate1 = $in->tglAwal;
  209.         $tglAwal = date("Y-m-d", strtotime($originalDate1));
  210.         $originalDate2 = $in->tglAkhir;
  211.         $tglAkhir = date("Y-m-d", strtotime($originalDate2));
  212.         $doc = $in->dokumenbc;
  213.         // $kode = array('25','261','41','27');
  214.        
  215.         // $sql = $this->basesql;
  216.         $sql = "select td.NOMOR_DOKUMEN as kode_dokumen, td.TANGGAL_DOKUMEN as tgl_dokumen, ta.NOMOR_AJU, ta.TANGGAL_AJU, ta.NOMOR_DAFTAR, ta.TANGGAL_DAFTAR, ta.NAMA_PENERIMA_BARANG, 'ID' KODE_BENDERA, ta.ID, ta.KODE_DOKUMEN_PABEAN, tb.detail, tc.URAIAN_DOKUMEN_PABEAN from (select * from ".getdbtpb($this).".tpb_header where (KODE_DOKUMEN_PABEAN = '25' or KODE_DOKUMEN_PABEAN = '261' or KODE_DOKUMEN_PABEAN = '41' or KODE_DOKUMEN_PABEAN = '27') and NOMOR_DAFTAR is not null and TANGGAL_AJU >='$tglAwal' and TANGGAL_AJU <='$tglAkhir' and KODE_DOKUMEN_PABEAN IN ('25','261','41','27') ) ta left join (select json_arrayagg(json_object('SERI_BARANG',SERI_BARANG,'KODE_BARANG',KODE_BARANG,'URAIAN',URAIAN,'KODE_SATUAN',KODE_SATUAN,'JUMLAH_SATUAN',JUMLAH_SATUAN,'hargarp',(case when CIF is null then HARGA_PENYERAHAN else CIF_RUPIAH end),'hargaasli',(case when CIF is null then '' else CIF end),'ID_HEADER',ID_HEADER)) as detail, ID_HEADER from ".getdbtpb($this).".tpb_barang group by ID_HEADER) tb on ta.ID = tb.ID_HEADER left join ".getdbtpb($this).".referensi_dokumen_pabean tc on ta.KODE_DOKUMEN_PABEAN = tc.KODE_DOKUMEN_PABEAN left join (select * from ".getdbtpb($this).".tpb_dokumen where KODE_JENIS_DOKUMEN = '999' group by ID_HEADER) td on ta.ID = td.ID_HEADER left join tpb_approval tx on ta.ID = tx.ID_HEADER UNION SELECT tb.NoDok NOMOR_DOKUMEN, tb.TgDok TANGGAL_DOKUMEN, ta.CAR NOMOR_AJU, ta.TGEKS TANGGAL_AJU, ta.NODAFT NOMOR_DAFTAR, ta.TGDAFT TANGGAL_DAFTAR, ta.NAMABELI2 NAMA_PENERIMA_BARANG, ta.NEGBELI2 KODE_BENDERA, ta.CAR ID, '30' as KODE_DOKUMEN_PABEAN, tc.detail,'BC 3.0' as URAIAN_DOKUMEN_PABEAN FROM ".getdbpeb($this).".tblpebhdr ta left join (select * from ".getdbpeb($this).".tblpebdok where KdDok = '217') tb on ta.CAR = tb.CAR left join (SELECT CAR, JSON_ARRAYAGG(JSON_OBJECT('SERI_BARANG',SERIBRG,'KODE_BARANG',KDBRG,'URAIAN',CONCAT(URBRG1,' - ',URBRG2,' - ',URBRG3,' - ',URBRG4),'KODE_SATUAN',JNSATUAN,'JUMLAH_SATUAN',JMSATUAN,'hargarp',DNilInv,'hargaasli',FOBPERBRG)) AS detail FROM ".getdbpeb($this).".tblpebdtl GROUP BY CAR) tc on ta.CAR = tc.CAR " ;
  217.         $res = $this->db->query($sql);
  218.         // printJSON($res);
  219.  
  220.         $data = array();
  221.         foreach ($res->result() as $r) {
  222.             $r->tgl_awal =  $in->tglAwal;
  223.             $r->tgl_akhir =  $in->tglAkhir;
  224.             // $r->kode =  $in->kode;
  225.             $data[] = $r;
  226.            
  227.         }
  228.        
  229.         printJSON($data);
  230.         return $data;
  231.     }
  232.  
  233.     function viewCustomsOut($in, $opt = true)
  234.     {
  235.         $start = $in->start;
  236.         if(getAppSetting($this)->kode_sbu == "OMI"){
  237.             $sqlmain = "SELECT
  238.         tdd.xxx AS kode_dokumen,
  239.         td.TANGGAL_DOKUMEN AS tgl_dokumen,
  240.         ta.NOMOR_AJU,
  241.         ta.TANGGAL_AJU,
  242.         ta.NOMOR_DAFTAR,
  243.         ta.TANGGAL_DAFTAR,
  244.         ta.NAMA_PENERIMA_BARANG,
  245.         'ID' KODE_BENDERA,
  246.         ta.ID,
  247.         ta.KODE_DOKUMEN_PABEAN,
  248.         tb.detail,
  249.         tc.URAIAN_DOKUMEN_PABEAN
  250.     FROM
  251.         (
  252.         SELECT
  253.             *
  254.         FROM
  255.             ".getdbtpb($this).".tpb_header
  256.         WHERE
  257.             ( KODE_DOKUMEN_PABEAN = '25' OR KODE_DOKUMEN_PABEAN = '261' OR KODE_DOKUMEN_PABEAN = '41' OR KODE_DOKUMEN_PABEAN = '27' )
  258.             AND NOMOR_DAFTAR IS NOT NULL
  259.         ) ta
  260.         LEFT JOIN (
  261.         SELECT
  262.             json_arrayagg(
  263.                 json_object(
  264.                     'SERI_BARANG',
  265.                     SERI_BARANG,
  266.                     'KODE_BARANG',
  267.                     KODE_BARANG,
  268.                     'URAIAN',
  269.                     URAIAN,
  270.                     'KODE_SATUAN',
  271.                     KODE_SATUAN,
  272.                     'JUMLAH_SATUAN',
  273.                     JUMLAH_SATUAN,
  274.                     'hargarp',(
  275.                     CASE
  276.                            
  277.                             WHEN CIF IS NULL THEN
  278.                             HARGA_PENYERAHAN ELSE CIF_RUPIAH
  279.                         END
  280.                         ),
  281.                         'hargaasli',(
  282.                         CASE
  283.                                
  284.                                 WHEN CIF IS NULL THEN
  285.                                 '' ELSE CIF
  286.                             END
  287.                             ),
  288.                             'ID_HEADER',
  289.                             ID_HEADER
  290.                         )) AS detail,
  291.                     ID_HEADER
  292.                 FROM
  293.                     ".getdbtpb($this).".tpb_barang
  294.                 GROUP BY
  295.                     ID_HEADER
  296.                 ) tb ON ta.ID = tb.ID_HEADER
  297.                 LEFT JOIN ".getdbtpb($this).".referensi_dokumen_pabean tc ON ta.KODE_DOKUMEN_PABEAN = tc.KODE_DOKUMEN_PABEAN
  298.                 LEFT JOIN ( SELECT * FROM ".getdbtpb($this).".tpb_dokumen WHERE KODE_JENIS_DOKUMEN = '999' GROUP BY ID_HEADER) td ON ta.ID = td.ID_HEADER
  299.                 LEFT JOIN (
  300.                 SELECT
  301.                     json_arrayagg(
  302.                         json_object( 'NOMOR_DOKUMEN', NOMOR_DOKUMEN, 'TANGGAL', TANGGAL_DOKUMEN )) AS xxx,ID_HEADER FROM ".getdbtpb($this).".tpb_dokumen WHERE KODE_JENIS_DOKUMEN = '999' GROUP BY ID_HEADER ) tdd ON ta.ID = tdd.ID_HEADER
  303.                         LEFT JOIN tpb_approval tx ON ta.ID = tx.ID_HEADER UNION
  304.                     SELECT
  305.                         tb.NoDok NOMOR_DOKUMEN,
  306.                         tb.TgDok TANGGAL_DOKUMEN,
  307.                         ta.CAR NOMOR_AJU,
  308.                         ta.TGEKS TANGGAL_AJU,
  309.                         ta.NODAFT NOMOR_DAFTAR,
  310.                         ta.TGDAFT TANGGAL_DAFTAR,
  311.                         ta.NAMABELI2 NAMA_PENERIMA_BARANG,
  312.                         ta.NEGBELI2 KODE_BENDERA,
  313.                         ta.CAR ID,
  314.                         '30' AS KODE_DOKUMEN_PABEAN,
  315.                         tc.detail,
  316.                         'BC 3.0' AS URAIAN_DOKUMEN_PABEAN
  317.                     FROM
  318.                         ".getdbpeb($this).".tblpebhdr ta
  319.                         LEFT JOIN ( SELECT * FROM ".getdbpeb($this).".tblpebdok WHERE KdDok = '217' ) tb ON ta.CAR = tb.CAR
  320.                         LEFT JOIN (
  321.                         SELECT
  322.                             CAR,
  323.                             JSON_ARRAYAGG(
  324.                                 JSON_OBJECT(
  325.                                     'SERI_BARANG',
  326.                                     SERIBRG,
  327.                                     'KODE_BARANG',
  328.                                     KDBRG,
  329.                                     'URAIAN',
  330.                                     CONCAT( URBRG1, ' - ', URBRG2, ' - ', URBRG3, ' - ', URBRG4 ),
  331.                                     'KODE_SATUAN',
  332.                                     JNSATUAN,
  333.                                     'JUMLAH_SATUAN',
  334.                                     JMSATUAN,
  335.                                     'hargarp',
  336.                                     DNilInv,
  337.                                     'hargaasli',
  338.                                     FOBPERBRG
  339.                                 )) AS detail
  340.                         FROM
  341.                             ".getdbpeb($this).".tblpebdtl
  342.                         GROUP BY
  343.                             CAR
  344.                         ) tc ON ta.CAR = tc.CAR";
  345.            
  346.         }else{
  347.             $sqlmain = "select td.NOMOR_DOKUMEN as kode_dokumen, td.TANGGAL_DOKUMEN as tgl_dokumen, ta.NOMOR_AJU, ta.TANGGAL_AJU, ta.NOMOR_DAFTAR, ta.TANGGAL_DAFTAR, ta.NAMA_PENERIMA_BARANG, 'ID' KODE_BENDERA, ta.ID, ta.KODE_DOKUMEN_PABEAN, tb.detail, tc.URAIAN_DOKUMEN_PABEAN from (select * from ".getdbtpb($this).".tpb_header where (KODE_DOKUMEN_PABEAN = '25' or KODE_DOKUMEN_PABEAN = '261' or KODE_DOKUMEN_PABEAN = '41' or KODE_DOKUMEN_PABEAN = '27') and NOMOR_DAFTAR is not null) ta left join (select json_arrayagg(json_object('SERI_BARANG',SERI_BARANG,'KODE_BARANG',KODE_BARANG,'URAIAN',URAIAN,'KODE_SATUAN',KODE_SATUAN,'JUMLAH_SATUAN',JUMLAH_SATUAN,'hargarp',(case when CIF is null then HARGA_PENYERAHAN else CIF_RUPIAH end),'hargaasli',(case when CIF is null then '' else CIF end),'ID_HEADER',ID_HEADER)) as detail, ID_HEADER from ".getdbtpb($this).".tpb_barang group by ID_HEADER) tb on ta.ID = tb.ID_HEADER left join ".getdbtpb($this).".referensi_dokumen_pabean tc on ta.KODE_DOKUMEN_PABEAN = tc.KODE_DOKUMEN_PABEAN left join (select * from ".getdbtpb($this).".tpb_dokumen group by ID_HEADER) td on ta.ID = td.ID_HEADER left join tpb_approval tx on ta.ID = tx.ID_HEADER UNION SELECT tb.NoDok NOMOR_DOKUMEN, tb.TgDok TANGGAL_DOKUMEN, ta.CAR NOMOR_AJU, ta.TGEKS TANGGAL_AJU, ta.NODAFT NOMOR_DAFTAR, ta.TGDAFT TANGGAL_DAFTAR, ta.NAMABELI2 NAMA_PENERIMA_BARANG, ta.NEGBELI2 KODE_BENDERA, ta.CAR ID, '30' as KODE_DOKUMEN_PABEAN, tc.detail,'BC 3.0' as URAIAN_DOKUMEN_PABEAN FROM ".getdbpeb($this).".tblpebhdr ta left join (select * from ".getdbpeb($this).".tblpebdok where KdDok = '217') tb on ta.CAR = tb.CAR left join (SELECT CAR, JSON_ARRAYAGG(JSON_OBJECT('SERI_BARANG',SERIBRG,'KODE_BARANG',KDBRG,'URAIAN',CONCAT(URBRG1,' - ',URBRG2,' - ',URBRG3,' - ',URBRG4),'KODE_SATUAN',JNSATUAN,'JUMLAH_SATUAN',JMSATUAN,'hargarp',DNilInv,'hargaasli',FOBPERBRG)) AS detail FROM ".getdbpeb($this).".tblpebdtl GROUP BY CAR) tc on ta.CAR = tc.CAR";
  348.            
  349.         }
  350. //      $sqlmain = "select td.NOMOR_DOKUMEN as kode_dokumen, td.TANGGAL_DOKUMEN as tgl_dokumen, ta.NOMOR_AJU, ta.TANGGAL_AJU, ta.NOMOR_DAFTAR, ta.TANGGAL_DAFTAR, ta.NAMA_PENERIMA_BARANG, 'ID' KODE_BENDERA, ta.ID, ta.KODE_DOKUMEN_PABEAN, tb.detail, tc.URAIAN_DOKUMEN_PABEAN from (select * from ".getdbtpb($this).".tpb_header where KODE_DOKUMEN_PABEAN = '25' or KODE_DOKUMEN_PABEAN = '261' or KODE_DOKUMEN_PABEAN = '41' or KODE_DOKUMEN_PABEAN = '27') ta inner join (select json_arrayagg(json_object('SERI_BARANG',SERI_BARANG,'KODE_BARANG',KODE_BARANG,'URAIAN',URAIAN,'KODE_SATUAN',KODE_SATUAN,'JUMLAH_SATUAN',JUMLAH_SATUAN,'hargarp',(case when CIF is null then HARGA_PENYERAHAN else CIF_RUPIAH end),'hargaasli',(case when CIF is null then '' else CIF end),'ID_HEADER',ID_HEADER)) as detail, ID_HEADER from ".getdbtpb($this).".tpb_barang group by ID_HEADER) tb on ta.ID = tb.ID_HEADER inner join ".getdbtpb($this).".referensi_dokumen_pabean tc on ta.KODE_DOKUMEN_PABEAN = tc.KODE_DOKUMEN_PABEAN left join (select * from ".getdbtpb($this).".tpb_dokumen where KODE_JENIS_DOKUMEN = '999' and NOMOR_DOKUMEN like '%SID%' group by ID_HEADER) td on ta.ID = td.ID_HEADER inner join tpb_approval tx on ta.ID = tx.ID_HEADER";
  351.         $sqlmain = "select * from ($sqlmain) pa where ID is not null";
  352.         if (isset($in->dokumenbc)) {
  353.             $i =1;
  354.             $sqlmain .= ' and (';
  355.             foreach ($in->dokumenbc as $row) {
  356.                 if ($i==1) {
  357.                     $sqlmain .= " KODE_DOKUMEN_PABEAN = '".$row."' ";
  358.                 } else {
  359.                     $sqlmain .= " OR KODE_DOKUMEN_PABEAN = '".$row."' ";
  360.                 }
  361.                 $i++;
  362.             }
  363.             $sqlmain .= ' ) ';
  364.         }
  365.  
  366.         $sqlmain = "select * from ($sqlmain) pa where ID is not null";
  367.  
  368.         if (isset($in->tglajuawal)) {
  369.             $tglajuawal = reverseDate($in->tglajuawal);
  370.             $sqlmain .= " and TANGGAL_AJU >= '$tglajuawal'";
  371.         }
  372.         if (isset($in->tglajuakhir)) {
  373.             $tglajuakhir = reverseDate($in->tglajuakhir);
  374.             $sqlmain .= " and TANGGAL_AJU <= '$tglajuakhir'";
  375.         }
  376.         if (isset($in->tglstuffingawal)) {
  377.             $tgl = reverseDate($in->tglstuffingawal);
  378.             $sqlmain .= " and tgl_dokumen >= '".$tgl."' ";
  379.         }
  380.         if (isset($in->tglstuffingakhir)) {
  381.             $tgl = reverseDate($in->tglstuffingakhir);
  382.             $sqlmain .= " and tgl_dokumen <= '".$tgl."' ";
  383.         }
  384.        
  385.         $sql = "select * from ($sqlmain) pa";
  386.         // printJSON($sql);
  387.         $res = $this->db->query($sql);
  388.         $recordsTotal = $res->num_rows();
  389.  
  390.         $sql .= dtSearch($this, $in);
  391.         $res = $this->db->query($sql);
  392.         $recordsFiltered = $res->num_rows();
  393.  
  394.         $sql .= dtSort($in);
  395.         $sql .= dtLimit($in);
  396.         $res = $this->db->query($sql);
  397.         $num = $res->num_rows();
  398.  
  399.         $data = array();
  400.         if($num>0){
  401.             $i=$start+1;
  402.             foreach ($res->result() as $r){
  403.                 $r->no = $i;
  404.                 $r->kode_dokumen = a2o(json_decode(sterilizeJSON($r->kode_dokumen)));
  405.                 $r->detail = a2o(json_decode(sterilizeJSON($r->detail)));
  406.                 $nomor_dokumen = '';
  407.                 $tanggal='';
  408.                 foreach ($r->kode_dokumen as $row) {
  409.                     $nomor_dokumen .= "<p style='margin:0;padding:0;'>".$row->NOMOR_DOKUMEN."</p><small style='margin:0;padding:0;'>".$row->NOMOR_DOKUMEN."</small><br><br>";
  410.                     $tanggal .= "<p style='margin:0;padding:0;'>".date("d-m-Y", strtotime($row->TANGGAL))."</p><br><br>";
  411.                
  412.                 }
  413.                 $r->nomor_dokumen = $nomor_dokumen;
  414.                 $r->tgl_dokumen = $tanggal;
  415.  
  416.                
  417.                 $item = '';
  418.                 $qty = '';
  419.                 $hargarp = '';
  420.                 $hargaasli = '';
  421.                 foreach ($r->detail as $row) {
  422.                     $item .= "<p style='margin:0;padding:0;'>".$row->URAIAN."</p><small style='margin:0;padding:0;'>".$row->KODE_BARANG."</small><br><br>";
  423.                     $qty .= "<p style='margin:0;padding:0;'>".number_format($row->JUMLAH_SATUAN,3)."</p><small style='margin:0;padding:0;'>".$row->KODE_SATUAN."</small><br><br>";
  424.                     $hargarp .= "<p style='margin:0;padding:0;'>".number_format(floatval($row->hargarp),2)."</p><small style='margin:0;padding:0;'>&nbsp;</small><br><br>";
  425.                     $hargaasli .= "<p style='margin:0;padding:0;'>".number_format(floatval($row->hargaasli),2)."</p><small style='margin:0;padding:0;'>&nbsp;</small><br><br>";
  426.                 }
  427.                 $r->URAIAN = $item;
  428.                 $r->JUMLAH_SATUAN = $qty;
  429.                 $r->hargarp = $hargarp;
  430.                 $r->hargaasli = $hargaasli;
  431.  
  432.                 $data[] = $r;
  433.                 $i++;
  434.             }
  435.         }
  436.  
  437.         $k = new stdClass();
  438.         $k->draw = $in->draw;
  439.         $k->recordsTotal = $recordsTotal;
  440.         $k->recordsFiltered = $recordsFiltered;
  441.         $k->data = $data;
  442.  
  443.         return $k;
  444.     }
  445.  
  446.     function get($id)
  447.     {
  448.         $sql = $this->basesql." and $this->table_id = '$id'";
  449.         $res = $this->db->query($sql);
  450.         $row = $res->row();
  451.         return $row;
  452.     }
  453.  
  454.     function getIN($id)
  455.     {
  456.         $sql = $this->basesql." and $this->table_id IN ($id)";
  457.         $res = $this->db->query($sql);
  458.         $data = array();
  459.         foreach ($res->result() as $r){
  460.             $data[] = $r;
  461.         }
  462.  
  463.         return $data;
  464.     }
  465.  
  466.     function getTotalVolume($id)
  467.     {
  468.         $sql =  "select sum(VOLUME) as vol from " .$this->table." where ID_HEADER = '$id'";
  469.         $res = $this->db->query($sql);
  470.         $row = $res->row();
  471.         return $row;
  472.        
  473.     }
  474.     function getNetto($id)
  475.     {
  476.         $sql =  "select sum(NETTO) as nett from  " .$this->table." where ID_HEADER = '$id'";
  477.         $res = $this->db->query($sql);
  478.         $row = $res->row();
  479.         return $row;
  480.        
  481.     }
  482.     function getTotalHargaPenyerahan($id)
  483.     {
  484.         $sql =  "select sum(HARGA_PENYERAHAN) as haper from  " .$this->table." where ID_HEADER = '$id'";
  485.         $res = $this->db->query($sql);
  486.         $row = $res->row();
  487.         return $row;
  488.        
  489.     }
  490.  
  491.     function getByIDHeader($id)
  492.     {
  493.         $sql = $this->basesql." where ID_HEADER = '$id'";
  494.         $res = $this->db->query($sql);
  495.  
  496.         $data = array();
  497.         foreach ($res->result() as $r){
  498.             $data[] = $r;
  499.         }
  500.  
  501.         return $data;
  502.     }
  503.  
  504.     function create($in)
  505.     {
  506.         $this->db->insert($this->table, $in);
  507.         $id = $this->db->insert_id();
  508.         return $id;
  509.     }
  510.  
  511.     function update($in)
  512.     {
  513.         $this->db->where($this->table_id, $in->{$this->table_id});
  514.         $this->db->update($this->table, $in);
  515.     }
  516.  
  517.     function delete($id)
  518.     {
  519.         $b = new stdClass();
  520.         $b->deleted_at = $this->nowdt;
  521.         $this->db->where($this->table_id, $id);
  522.         $this->db->update($this->table, $b);
  523.     }
  524. }
  525.  
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!