abel/shopee-apps
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
shopee-apps/application/models/ModelPenjualan.php
T
abel db327c0305 copy dari repo om Irfan
2026-06-13 16:02:07 +10:00

1892 lines
78 KiB
PHP
Raw Permalink Blame History

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class ModelPenjualan extends CI_Model {
public function create($table, $data, $batch = false)
{
if($batch === false){
$insert = $this->db->insert($table, $data);
}else{
$insert = $this->db->insert_batch($table, $data);
}
return $insert;
}
public function update($table, $data, $pk, $id = null, $batch = false)
{
if($batch === false){
$insert = $this->db->update($table, $data, array($pk => $id));
}else{
$insert = $this->db->update_batch($table, $data, $pk);
}
return $insert;
}
public function delete($table, $data, $pk)
{
$this->db->where_in($pk, $data);
return $this->db->delete($table);
}
public function getData_SalesOrder_Null_All($karyawan, $id = null, $level = null) { //Penjualan = [1]
if ($id == null) {
$this->db->select('*,FORMAT(a.FEE_SO,2) as FEE,DATE_FORMAT(a.TGL_SO,"%d/%m/%Y") as TANGGAL');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('jabatan d','d.ID_JABATAN=b.JABATAN_ID');
$this->db->join('corp e','e.ID_CORP=a.CORP_ID');
$this->db->join('kota f','f.ID_KOTA=c.KOTA_ID');
$this->db->where('a.STATUS_SO is NULL');
$this->db->where('a.PL_ID is NULL');
if ($level == 0) {
$this->db->where('a.CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('a.CORP_ID>=4');
}
}else{
$this->db->select('*');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID','left');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID','left');
$this->db->join('jabatan d','d.ID_JABATAN=b.JABATAN_ID','left');
$this->db->join('corp e','e.ID_CORP=a.CORP_ID','left');
$this->db->join('kota f','f.ID_KOTA=c.KOTA_ID','left');
$this->db->join('daftar_order g','g.SO_ID=a.ID_SO','left');
$this->db->group_by('a.ID_SO');
$this->db->where('a.STATUS_SO is NULL');
$this->db->where('a.PL_ID is NULL');
$this->db->where(['a.ID_SO' => $id]);
}
return $this->db->get()->result_array();
}
public function getData_SalesOrder_Null_SugengInclude($id) { //Penjualan = [1]
$this->db->select('*,DATE_FORMAT(a.TGL_SO,"%d/%m/%Y") as TANGGAL,FORMAT(a.FEE_SO,2) as FEE');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('jabatan d','d.ID_JABATAN=b.JABATAN_ID');
$this->db->join('corp e','e.ID_CORP=a.CORP_ID');
$this->db->join('kota f','f.ID_KOTA=c.KOTA_ID');
$this->db->where('a.STATUS_SO is NULL AND a.PL_ID is NULL AND a.SALES_ID=50');
$this->db->or_where('a.STATUS_SO is NULL AND a.PL_ID is NULL AND a.SALES_ID=51');
$this->db->or_where('a.STATUS_SO is NULL AND a.PL_ID is NULL AND a.SALES_ID=', $id);
return $this->db->get()->result_array();
}
public function getData_SalesOrder_Null($karyawan, $id = null, $level = null) { //Penjualan = [1, 5, 6, 11]
if ($id == null) {
$this->db->select('*,FORMAT(a.FEE_SO,2) as FEE,DATE_FORMAT(a.TGL_SO,"%d/%m/%Y") as TANGGAL');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('jabatan d','d.ID_JABATAN=b.JABATAN_ID');
$this->db->join('corp e','e.ID_CORP=a.CORP_ID');
$this->db->join('kota f','f.ID_KOTA=c.KOTA_ID');
$this->db->where(['a.SALES_ID' => $karyawan]);
$this->db->where('a.STATUS_SO is NULL');
$this->db->where('a.PL_ID is NULL');
if ($level == 0) {
$this->db->where('a.CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('a.CORP_ID>=4');
}
}else{
$this->db->select('*');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('jabatan d','d.ID_JABATAN=b.JABATAN_ID');
$this->db->join('corp e','e.ID_CORP=a.CORP_ID');
$this->db->join('kota f','f.ID_KOTA=c.KOTA_ID');
if ($karyawan!=1 AND $karyawan!=9 AND $karyawan!=21 AND $karyawan!=43) {
$this->db->where(['a.SALES_ID' => $karyawan]);
}
$this->db->where(['a.ID_SO' => $id]);
$this->db->where('a.STATUS_SO is NULL');
$this->db->where('a.PL_ID is NULL');
}
return $this->db->get()->result_array();
}
public function getData_Corp_So($id = null, $level = null){ //Penjualan = [1, 92]
if ($id == null) {
$this->db->select('*');
$this->db->from('corp');
if ($level == 0) {
$this->db->where('ID_CORP<=3');
} else if ($level == 1) {
$this->db->where('ID_CORP>=4');
}
} else {
$this->db->select('*');
$this->db->from('corp');
$this->db->where(['ID_CORP' => $id]);
}
return $this->db->get()->result_array();
}
public function getData_Peminjaman_byJENIS($jenis = null) { //Penjualan = [1]
$this->db->select('*,DATE_FORMAT(a.TGL_PEMINJAMAN,"%d/%m/%Y") as TANGGAL');
$this->db->from('peminjaman a');
$this->db->join('pelanggan b','b.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->where('JENIS_PEMINJAMAN=', $jenis);
$this->db->order_by('a.ID_PEMINJAMAN DESC');
return $this->db->get()->result_array();
}
public function getData_Karyawan_SalesPcm() { //Penjualan = [1, 85, 86]
$this->db->select('*');
$this->db->from('karyawan a');
$this->db->join('jabatan b','b.ID_JABATAN=a.JABATAN_ID');
$this->db->where('b.NAMA_JABATAN="SALES"');
$this->db->or_where('b.NAMA_JABATAN="ADMIN SALES"');
$this->db->or_where('a.USERNAME="sugeng"');
$this->db->order_by('a.NAMA_PANGGILAN_KARYAWAN ASC');
return $this->db->get()->result_array();
}
public function getData_Karyawan_SalesItp() { //Penjualan = [1, 85, 86]
$this->db->select('*');
$this->db->from('karyawan a');
$this->db->join('jabatan b','b.ID_JABATAN=a.JABATAN_ID');
$this->db->where('a.USERNAME="sugeng"');
$this->db->or_where('a.USERNAME="risti"');
$this->db->or_where('a.USERNAME="olla"');
$this->db->or_where('a.USERNAME="iwan"');
$this->db->order_by('a.NAMA_PANGGILAN_KARYAWAN ASC');
return $this->db->get()->result_array();
}
public function getData_Karyawan_SugengInclude($id) { //Penjualan = [1, 85, 86]
$this->db->select('*');
$this->db->from('karyawan a');
$this->db->join('jabatan b','b.ID_JABATAN=a.JABATAN_ID');
$this->db->where('a.ID_KARYAWAN=', $id);
$this->db->or_where('a.ID_KARYAWAN=50');
$this->db->or_where('a.ID_KARYAWAN=51');
return $this->db->get()->result_array();
}
public function getData_Pelanggan_byID($id=null) { //Penjualan = [1, 5, 11, 25, 55, 68, 92, 93, 95, 99]
if ($id==null) {
$this->db->select('*');
$this->db->from('pelanggan a');
$this->db->join('kota b','b.ID_KOTA=a.KOTA_ID');
} else {
$this->db->select('*');
$this->db->from('pelanggan a');
$this->db->join('kota b','b.ID_KOTA=a.KOTA_ID');
$this->db->where(['a.ID_PELANGGAN' => $id]);
}
return $this->db->get()->result_array();
}
public function getCount_SalesOrder_Null($id, $level=null){ //Penjualan = [1]
$this->db->select('ID_SO');
$this->db->from('sales_order');
$this->db->where('STATUS_SO is NULL');
if ($level == 0) {
$this->db->where('CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('CORP_ID>=4');
}
if ($level == null) {
$this->db->where(['SALES_ID' => $id]);
}
return $this->db->count_all_results();
}
public function getCount_SalesOrder_NotNull_Not1_Not5($id, $level=null){ //Penjualan = [1]
$this->db->select('ID_SO');
$this->db->from('sales_order');
if ($level == 0) {
$this->db->where('CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('CORP_ID>=4');
}
if ($level == null) {
$this->db->where('STATUS_SO is NOT NULL AND STATUS_SO!=1 AND STATUS_SO!=5 AND SALES_ID=', $id);
} else {
$this->db->where('STATUS_SO is NOT NULL AND STATUS_SO!=1 AND STATUS_SO!=5');
}
return $this->db->count_all_results();
}
public function getSum_DaftarOrder_bySO($id) { //Penjualan = [1]
$this->db->select('SUM(a.QTY_DO*a.HARGA_DO) as TOTAL');
$this->db->from('daftar_order a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->where(['a.SO_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_Karyawan_forLogin($username, $password) { //Penjualan = [2, 62, 83]
$this->db->select('*');
$this->db->from('karyawan a');
$this->db->join('jabatan b', 'b.ID_JABATAN=a.JABATAN_ID');
$this->db->where('a.USERNAME=', $username);
$this->db->where('a.PASSWORD=', $password);
return $this->db->get()->result_array();
}
public function getKode_SalesOrder() { //Penjualan = [3]
$this->db->select('MAX(KODE_SO) AS KODE');
$this->db->from('sales_order');
$this->db->like('KODE_SO','SO23', 'after');
return $this->db->get()->result_array();
}
public function getData_Peminjaman_byID($id) { //Penjualan = [4]
$this->db->select('*');
$this->db->from('peminjaman');
$this->db->where('ID_PEMINJAMAN=', $id);
return $this->db->get()->result_array();
}
public function getID_SalesOrderMax() { //Penjualan = [4]
$this->db->select_max('ID_SO');
$this->db->from('sales_order');
return $this->db->get()->result_array();
}
public function getData_Barang_byID($id) { //Penjualan = [4, 8, 25, 45, 96]
$this->db->select('*');
$this->db->from('barang a');
$this->db->join('jenis b','b.ID_JENIS=a.JENIS_ID');
$this->db->where(['a.ID_BARANG' => $id]);
return $this->db->get()->result_array();
}
public function getData_DaftarPinjam_byPEMINJAMAN($id) { //Penjualan = [4]
$this->db->select('*');
$this->db->from('daftar_pinjam a');
$this->db->join('peminjaman b','b.ID_PEMINJAMAN=a.PEMINJAMAN_ID');
$this->db->where('a.PEMINJAMAN_ID=', $id);
return $this->db->get()->result_array();
}
public function getData_PiutangDagang_byPELANGGAN($id) { //Penjualan = [5, 7, 11, 93, 95, 99]
$this->db->select('*');
$this->db->from('piutang_dagang');
$this->db->where(['PELANGGAN_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_DaftarOrder_byPELANGGAN_Null_Not1_Not5($id) { //Penjualan = [5, 7, 11, 93, 95, 99]
$this->db->select('*');
$this->db->from('daftar_order a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->where('b.STATUS_SO is NULL AND b.PELANGGAN_ID=', $id);
$this->db->or_where('b.STATUS_SO!=1 AND b.STATUS_SO!=5 AND b.PELANGGAN_ID=', $id);
return $this->db->get()->result_array();
}
public function getData_DaftarOrder_bySO($id) { //Penjualan = [6, 7, 11, 14, 19, 21, 25, 41, 54, 55, 56, 58, 60, 91, 94, 95, 99]
$this->db->select('*,FORMAT(a.HARGA_DO,2) as SATUAN, FORMAT((a.HARGA_DO*a.QTY_DO),2) as TOTAL');
$this->db->from('daftar_order a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->join('kuantiti d','d.ID_KUANTITI=b.ID_BARANG');
$this->db->join('sales_order e','e.ID_SO=a.SO_ID');
$this->db->join('pelanggan f','f.ID_PELANGGAN=e.PELANGGAN_ID');
$this->db->where(['a.SO_ID' => $id]);
$this->db->order_by('a.ID_DO ASC');
return $this->db->get()->result_array();
}
public function getData_SlipManual_bySO($id) { //Penjualan = [6, 94]
$this->db->select('*');
$this->db->from('slip_manual');
$this->db->where(['SO_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_SalesOrder_byID($id) { //Penjualan = [7, 8, 9, 10, 14, 23, 24, 41, 43, 55, 91, 95, 96, 100]
$this->db->select('*');
$this->db->from('sales_order a');
$this->db->join('pelanggan b','b.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('kota c','c.ID_KOTA=b.KOTA_ID');
$this->db->join('karyawan d','d.ID_KARYAWAN=a.SALES_ID');
$this->db->where(['a.ID_SO' => $id]);
return $this->db->get()->result_array();
}
public function getBarang_TypeAll_NonJasa($level){ //Penjualan = [7, 95]
if ($level == 0) {
$this->db->select('a.ID_BARANG,c.PCM_KUANTITI as QTY,a.KODE_BARANG,a.NAMA_BARANG,b.NAMA_JENIS');
} else if ($level == 1) {
$this->db->select('a.ID_BARANG,c.ITP_KUANTITI as QTY,a.KODE_BARANG,a.NAMA_BARANG,b.NAMA_JENIS');
}
$this->db->from('barang a');
$this->db->join('jenis b','b.ID_JENIS=a.JENIS_ID');
$this->db->join('kuantiti c','c.ID_KUANTITI=a.ID_BARANG');
$this->db->where('b.TYPE_JENIS!=2');
if ($level == 0) {
$this->db->order_by('c.PCM_KUANTITI DESC,b.NAMA_JENIS ASC,a.KODE_BARANG ASC');
} else if ($level == 1) {
$this->db->order_by('c.ITP_KUANTITI DESC,b.NAMA_JENIS ASC,a.KODE_BARANG ASC');
}
return $this->db->get()->result_array();
}
public function getBarang_TypeAll_NonQty(){ //Penjualan = [7, 95]
$this->db->select('a.ID_BARANG,a.KODE_BARANG,a.NAMA_BARANG,b.NAMA_JENIS');
$this->db->from('barang a');
$this->db->join('jenis b','b.ID_JENIS=a.JENIS_ID');
$this->db->where('b.TYPE_JENIS=2');
$this->db->order_by('b.TYPE_JENIS ASC,a.KODE_BARANG ASC');
return $this->db->get()->result_array();
}
public function getData_DaftarOrder_bySO_byBARANG($id1, $id2) { //Penjualan = [8, 45, 58, 96]
$this->db->select('*');
$this->db->from('daftar_order');
$this->db->where(['SO_ID' => $id1]);
$this->db->where(['BARANG_ID' => $id2]);
return $this->db->get()->result_array();
}
public function getData_DaftarOrder_byID($id=null) { //Penjualan = [9, 17, 26, 29, 30, 59, 97, 98, 100]
if ($id==null) {
$this->db->select('*');
$this->db->from('daftar_order');
} else {
$this->db->select('*');
$this->db->from('daftar_order a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->join('sales_order d','d.ID_SO=a.SO_ID');
$this->db->where(['ID_DO' => $id]);
}
return $this->db->get()->result_array();
}
public function getData_Pelanggan_byKELOMPOK($id) { //Penjualan = [11, 99]
$this->db->select('*');
$this->db->from('pelanggan a');
$this->db->join('kelompok b','b.ID_KELOMPOK=a.KELOMPOK_ID');
$this->db->where(['a.KELOMPOK_ID' => $id]);
return $this->db->get()->result_array();
}
public function addTempoYear($date,$year) { //Penjualan = [11, 93]
$sum = strtotime(date("Y-m-d", strtotime("$date")) . " +$year year");
$dateTo=date('Y-m-d',$sum);
return $dateTo;
}
public function addTempoMonth($date,$month) { //Penjualan = [11, 93]
$sum = strtotime(date("Y-m-d", strtotime("$date")) . " +$month month");
$dateTo=date('Y-m-d',$sum);
return $dateTo;
}
public function getID_NotifikasiMax() { //Penjualan = [11]
$this->db->select_max('ID_NOTIFIKASI');
$this->db->from('notifikasi');
return $this->db->get()->result_array();
}
public function getData_SalesOrder_NotNull_Not1_Not2_Not3_Not5($level, $jabatan=null) { //Penjualan = [12]
$this->db->select('*,DATE_FORMAT(a.TGL_SO,"%d/%m/%Y") as TANGGAL');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('jabatan d','d.ID_JABATAN=b.JABATAN_ID');
$this->db->join('corp e','e.ID_CORP=a.CORP_ID');
$this->db->join('kota f','f.ID_KOTA=c.KOTA_ID');
if ($level == 0) {
$this->db->where('a.CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('a.CORP_ID>=4');
}
if ($jabatan == "SUPPORT") {
$this->db->where('a.PL_ID is NULL AND a.STATUS_SO is NOT NULL AND a.STATUS_SO!=1 AND a.STATUS_SO!=2 AND a.STATUS_SO!=3 AND a.STATUS_SO!=4 AND a.STATUS_SO!=5 AND a.STATUS_SO!=6');
} else {
$this->db->where('a.PL_ID is NULL AND a.STATUS_SO is NOT NULL AND a.STATUS_SO!=1 AND a.STATUS_SO!=2 AND a.STATUS_SO!=3 AND a.STATUS_SO!=5');
}
return $this->db->get()->result_array();
}
public function getData_TransaksiStock_bySO_Null_Jenis1($id) { //Penjualan = [14, 15, 25, 43, 54, 91]
$this->db->select('*,FORMAT(d.HPP_HPAR,2) as HPP, FORMAT(a.HARGA_IO,2) as HARGA');
$this->db->from('transaksi_stock a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID','left');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID','left');
$this->db->join('harga_part d','d.ID_HPAR=a.HPAR_ID','left');
$this->db->join('jenis e','e.ID_JENIS=c.JENIS_ID','left');
$this->db->where(['a.SO_ID' => $id]);
$this->db->where('a.JENIS_IO=1');
$this->db->where('a.STATUS_IO is NULL');
return $this->db->get()->result_array();
}
public function getData_TransaksiStock_bySO_Null_Jenis0($id) { //Penjualan = [14, 15, 25, 43, 54, 91]
$this->db->select('*');
$this->db->from('transaksi_stock a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID','left');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID','left');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID','left');
$this->db->join('kuantiti e','e.ID_KUANTITI=c.ID_BARANG','left');
$this->db->where(['a.SO_ID' => $id]);
$this->db->where('a.JENIS_IO=0');
$this->db->where('a.STATUS_IO is NULL');
return $this->db->get()->result_array();
}
public function getBarang_TypeAll_jenisKeluar($level) { //Penjualan = [14, 43]
if ($level == 0) {
$this->db->select('*,b.PCM_KUANTITI as QTY');
} else if ($level == 1) {
$this->db->select('*,b.ITP_KUANTITI as QTY');
}
$this->db->from('barang a');
$this->db->join('kuantiti b','b.ID_KUANTITI=a.ID_BARANG');
$this->db->join('jenis c','c.ID_JENIS=a.JENIS_ID');
$this->db->where('c.TYPE_JENIS<2');
if ($level == 0) {
$this->db->where('b.PCM_KUANTITI>0');
} else if ($level == 1) {
$this->db->where('b.ITP_KUANTITI>0');
}
return $this->db->get()->result_array();
}
public function getBarang_TypeAll_jenisMasuk() { //Penjualan = [14, 43]
$this->db->select('*');
$this->db->from('barang a');
$this->db->join('kuantiti b','b.ID_KUANTITI=a.ID_BARANG');
$this->db->join('jenis c','c.ID_JENIS=a.JENIS_ID');
$this->db->where('c.TYPE_JENIS<2');
return $this->db->get()->result_array();
}
public function getData_HargaPart_byID($id=null) { //Penjualan = [14, 15, 43]
if ($id==null) {
$this->db->select('*,CONCAT("Rp ",FORMAT(a.HPP_HPAR,0)) as HPP');
$this->db->from('harga_part a');
$this->db->join('part b','b.ID_PART=a.PART_ID');
$this->db->order_by('b.NAMA_PART ASC, a.ID_HPAR DESC');
} else {
$this->db->select('*,CONCAT("Rp ",FORMAT(a.HPP_HPAR,0)) as HPP');
$this->db->from('harga_part a');
$this->db->join('part b','b.ID_PART=a.PART_ID');
$this->db->order_by('b.NAMA_PART ASC, a.ID_HPAR DESC');
$this->db->where(['a.ID_HPAR' => $id]);
}
return $this->db->get()->result_array();
}
public function getData_Karyawan_Qc() { //Penjualan = [14, 43]
$this->db->select('*');
$this->db->from('karyawan a');
$this->db->join('jabatan b','b.ID_JABATAN=a.JABATAN_ID');
$this->db->where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="SUPPORT"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="UMUM"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="GUDANG"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="ADMIN GUDANG"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="SERVICE"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND a.username="none"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND a.username="olla"');
$this->db->order_by('a.NAMA_PANGGILAN_KARYAWAN ASC');
return $this->db->get()->result_array();
}
public function getQtyReal_SnJual_byDO($id) { //Penjualan = [14]
$this->db->select('a.ID_SNJ');
$this->db->from('sn_jual a');
$this->db->join('daftar_order b','b.ID_DO=a.DO_ID');
$this->db->group_by('a.ID_SNJ');
$this->db->where(['a.DO_ID' => $id]);
return $this->db->count_all_results();
}
public function getQtyReal_TransaksiStock0($id) { //Penjualan = [14, 25, 31, 34, 35, 43]
$this->db->select('a.ID_SNIO');
$this->db->from('sn_io a');
$this->db->join('transaksi_stock b','b.ID_IO=a.IO_ID');
$this->db->group_by('a.ID_SNIO');
$this->db->where(['a.IO_ID' => $id]);
$this->db->where('a.JENIS_SNIO=0');
return $this->db->count_all_results();
}
public function getQtyReal_TransaksiStock1($id) { //Penjualan = [14, 25, 36, 39, 40, 43]
$this->db->select('a.ID_SNIO');
$this->db->from('sn_io a');
$this->db->join('transaksi_stock b','b.ID_IO=a.IO_ID');
$this->db->group_by('a.ID_SNIO');
$this->db->where(['a.IO_ID' => $id]);
$this->db->where('a.JENIS_SNIO=1');
return $this->db->count_all_results();
}
public function getData_SalesOrder_0_7_8($id) { //Penjualan = [15, 16]
$this->db->select('*');
$this->db->from('sales_order');
$this->db->where('PL_ID is NULL AND STATUS_SO=0 AND ID_SO=', $id);
$this->db->or_where('PL_ID is NULL AND STATUS_SO=7 AND ID_SO=', $id);
$this->db->or_where('PL_ID is NULL AND STATUS_SO=8 AND ID_SO=', $id);
return $this->db->get()->result_array();
}
public function getData_Kuantiti_byID($id) { //Penjualan = [15, 16, 20, 25, 45, 47, 48, 54, 60, 70, 81, 91]
$this->db->select('*');
$this->db->from('kuantiti');
$this->db->where(['ID_KUANTITI' => $id]);
return $this->db->get()->result_array();
}
public function getData_TransaksiStock_byID($id) { //Penjualan = [16, 31, 34, 35, 35, 39]
$this->db->select('*');
$this->db->from('transaksi_stock a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->where(['a.ID_IO' => $id]);
return $this->db->get()->result_array();
}
public function getData_SnIo_AllJenis($id) { //Penjualan = [16]
$this->db->select('*');
$this->db->from('sn_io');
$this->db->where(['IO_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_SalesOrder_0_7($id) { //Penjualan = [18, 19, 20, 21, 25]
$this->db->select('*');
$this->db->from('sales_order');
$this->db->where('PL_ID is NULL AND STATUS_SO=0 AND ID_SO=', $id);
$this->db->or_where('PL_ID is NULL AND STATUS_SO=7 AND ID_SO=', $id);
return $this->db->get()->result_array();
}
public function getData_SnJual_byDO($id) { //Penjualan = [19, 21, 25, 27, 28, 41, 56, 59, 60, 91, 97]
$this->db->select('*');
$this->db->from('sn_jual a');
$this->db->join('daftar_order b','b.ID_DO=a.DO_ID');
$this->db->join('barang c','c.ID_BARANG=b.BARANG_ID');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID');
$this->db->where(['a.DO_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_TransaksiStock_bySO_AllStatus($id) { //Penjualan = [19, 21, 44, 94]
$this->db->select('*');
$this->db->from('transaksi_stock a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->where(['a.SO_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_TransaksiStock_bySO_Jenis0_JoinProyek($id) { //Penjualan = [20]
$this->db->select('*');
$this->db->from('transaksi_stock a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID');
$this->db->join('proyek e','e.ID_PROYEK=a.PROYEK_ID');
$this->db->where(['a.SO_ID' => $id]);
$this->db->where('a.JENIS_IO=0');
$this->db->where('a.PROYEK_ID is NOT NULL');
return $this->db->get()->result_array();
}
public function getData_SnIo_0($id) { //Penjualan = [20]
$this->db->select('*');
$this->db->from('sn_io');
$this->db->where(['IO_ID' => $id]);
$this->db->where('JENIS_SNIO=0');
return $this->db->get()->result_array();
}
public function getData_DaftarKonsi_byPELANGGAN_groupBARANG_Status0($id) { //Penjualan = [21, 55]
$this->db->select('*,SUM(a.QTY_DFK) as QTYDFK');
$this->db->from('daftar_konsi a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->join('konsinyasi d','d.ID_KONSINYASI=a.KONSINYASI_ID');
$this->db->join('kuantiti e','e.ID_KUANTITI=a.BARANG_ID');
$this->db->where('d.STATUS_KONSINYASI=0');
$this->db->where('d.PELANGGAN_ID=', $id);
$this->db->group_by('a.BARANG_ID');
$this->db->order_by('c.NAMA_JENIS ASC, b.NAMA_BARANG ASC');
return $this->db->get()->result_array();
}
public function getKode_Penjualan() { //Penjualan = [22]
$this->db->select('MAX(KODE_PENJUALAN) AS KODE');
$this->db->from('penjualan');
return $this->db->get()->result_array();
}
public function getKode_SJPenjualanTMP() { //Penjualan = [23]
$this->db->select('MAX(SJ_PENJUALAN) AS KODE');
$this->db->from('penjualan');
$this->db->like('SJ_PENJUALAN','SJ/TMP', 'after');
return $this->db->get()->result_array();
}
public function getKode_SJPenjualanDMT() { //Penjualan = [23]
$this->db->select('MAX(SJ_PENJUALAN) AS KODE');
$this->db->from('penjualan');
$this->db->like('SJ_PENJUALAN','SJ/DMT', 'after');
return $this->db->get()->result_array();
}
public function getKode_SJPenjualanPCM() { //Penjualan = [23]
$this->db->select('MAX(SJ_PENJUALAN) AS KODE');
$this->db->from('penjualan');
$this->db->like('SJ_PENJUALAN','SJ/PCM', 'after');
return $this->db->get()->result_array();
}
public function getKode_SJPenjualanSFT() { //Penjualan = [23]
$this->db->select('MAX(SJ_PENJUALAN) AS KODE');
$this->db->from('penjualan');
$this->db->like('SJ_PENJUALAN','SJ/SFT', 'after');
return $this->db->get()->result_array();
}
public function getKode_SJPenjualanITP() { //Penjualan = [23]
$this->db->select('MAX(SJ_PENJUALAN) AS KODE');
$this->db->from('penjualan');
$this->db->like('SJ_PENJUALAN','SJ/ITP', 'after');
return $this->db->get()->result_array();
}
public function getKode_INVPenjualanTMP() { //Penjualan = [24]
$this->db->select('MAX(INV_PENJUALAN) AS KODE');
$this->db->from('penjualan');
$this->db->like('INV_PENJUALAN','INV/TMP', 'after');
return $this->db->get()->result_array();
}
public function getKode_INVPenjualanDMT() { //Penjualan = [24]
$this->db->select('MAX(INV_PENJUALAN) AS KODE');
$this->db->from('penjualan');
$this->db->like('INV_PENJUALAN','INV/DMT', 'after');
return $this->db->get()->result_array();
}
public function getKode_INVPenjualanPCM() { //Penjualan = [24]
$this->db->select('MAX(INV_PENJUALAN) AS KODE');
$this->db->from('penjualan');
$this->db->like('INV_PENJUALAN','INV/PCM', 'after');
return $this->db->get()->result_array();
}
public function getKode_INVPenjualanSFT() { //Penjualan = [24]
$this->db->select('MAX(INV_PENJUALAN) AS KODE');
$this->db->from('penjualan');
$this->db->like('INV_PENJUALAN','INV/SFT', 'after');
return $this->db->get()->result_array();
}
public function getKode_INVPenjualanITP() { //Penjualan = [24]
$this->db->select('MAX(INV_PENJUALAN) AS KODE');
$this->db->from('penjualan');
$this->db->like('INV_PENJUALAN','INV/ITP', 'after');
return $this->db->get()->result_array();
}
public function getQtyReal_DaftarOrder($id) { //Penjualan = [25, 26, 29, 30, 98]
$this->db->select('a.ID_SNJ');
$this->db->from('sn_jual a');
$this->db->join('daftar_order b','b.ID_DO=a.DO_ID');
$this->db->group_by('a.ID_SNJ');
$this->db->where(['a.DO_ID' => $id]);
return $this->db->count_all_results();
}
public function getData_SnStock_SnJual_byNAMA_AllStatus($id) { //Penjualan = [25, 29, 60, 91, 97]
$this->db->select('*');
$this->db->from('sn_stock a');
$this->db->join('sn_jual b','b.NAMA_SNJ=a.NAMA_SNSTOCK','left');
$this->db->where(['a.NAMA_SNSTOCK' => $id]);
return $this->db->get()->result_array();
}
public function addTempoDay($date,$day) { //Penjualan = [25, 54, 60, 87]
$sum = strtotime(date("Y-m-d", strtotime("$date")) . " +$day days");
$dateTo=date('Y-m-d',$sum);
return $dateTo;
}
public function getID_PenjualanMax() { //Penjualan = [25, 47, 60]
$this->db->select_max('ID_PENJUALAN');
$this->db->from('penjualan');
return $this->db->get()->result_array();
}
public function getData_Karyawan_byID($id) { //Penjualan = [25, 67, 92]
$this->db->select('*');
$this->db->from('karyawan a');
$this->db->join('jabatan b','b.ID_JABATAN=a.JABATAN_ID');
$this->db->where('a.ID_KARYAWAN=', $id);
return $this->db->get()->result_array();
}
public function getData_SnStock_byBARANG($id, $level) { //Penjualan = [26, 31, 36]
$this->db->select('*');
$this->db->from('sn_stock a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->where('a.STATUS_SNSTOCK is NULL');
$this->db->where(['a.BARANG_ID' => $id]);
if ($level == 0) {
$this->db->where('a.GUDANG_SNSTOCK=1');
} else if ($level == 1) {
$this->db->where('a.GUDANG_SNSTOCK=2');
}
return $this->db->get()->result_array();
}
public function getData_SnJual_SnStock_byNAMA_0($id) { //Penjualan = [29]
$this->db->select('*');
$this->db->from('sn_jual a');
$this->db->join('sn_stock b','b.NAMA_SNSTOCK=a.NAMA_SNJ','left');
$this->db->where(['a.NAMA_SNJ' => $id]);
$this->db->where('b.STATUS_SNSTOCK=0');
return $this->db->get()->result_array();
}
public function getData_SnJual_byID($id) { //Penjualan = [30, 70]
$this->db->select('*');
$this->db->from('sn_jual a');
$this->db->join('sn_stock b','b.NAMA_SNSTOCK=a.NAMA_SNJ');
$this->db->where(['a.ID_SNJ' => $id]);
return $this->db->get()->result_array();
}
public function getData_SnStock_byNAMA_AllStatus($id) { //Penjualan = [25, 30, 35, 40, 53, 54, 60]
$this->db->select('*');
$this->db->from('sn_stock');
$this->db->where(['NAMA_SNSTOCK' => $id]);
return $this->db->get()->result_array();
}
public function getData_SnIo_byIO_0($id) { //Penjualan = [25, 32, 33, 54, 91]
$this->db->select('*');
$this->db->from('sn_io a');
$this->db->join('transaksi_stock b','b.ID_IO=a.IO_ID');
$this->db->join('barang c','c.ID_BARANG=b.BARANG_ID');
$this->db->where(['a.IO_ID' => $id]);
$this->db->where('a.JENIS_SNIO=0');
return $this->db->get()->result_array();
}
public function getData_SnStock_SnIo_byNAMA_AllStatus($id) { //Penjualan = [34, 39, 91]
$this->db->select('*');
$this->db->from('sn_stock a');
$this->db->join('sn_io b','b.NAMA_SNIO=a.NAMA_SNSTOCK','left');
$this->db->where(['a.NAMA_SNSTOCK' => $id]);
return $this->db->get()->result_array();
}
public function getData_SnIo_SnStock_byNAMA_0($id) { //Penjualan = [34]
$this->db->select('*');
$this->db->from('sn_io a');
$this->db->join('sn_stock b','b.NAMA_SNSTOCK=a.NAMA_SNIO','left');
$this->db->where(['a.NAMA_SNIO' => $id]);
$this->db->where('b.STATUS_SNSTOCK=0');
return $this->db->get()->result_array();
}
public function getData_SnIo_byID($id) { //Penjualan = [35, 40]
$this->db->select('*');
$this->db->from('sn_io');
$this->db->where(['ID_SNIO' => $id]);
return $this->db->get()->result_array();
}
public function getData_SnIo_byIO_1($id) { //Penjualan = [25, 37, 38, 54, 91]
$this->db->select('*');
$this->db->from('sn_io a');
$this->db->join('transaksi_stock b','b.ID_IO=a.IO_ID');
$this->db->join('barang c','c.ID_BARANG=b.BARANG_ID');
$this->db->where(['a.IO_ID' => $id]);
$this->db->where('a.JENIS_SNIO=1');
return $this->db->get()->result_array();
}
public function getData_SalesOrder_4($id) { //Penjualan = [42]
$this->db->select('*');
$this->db->from('sales_order');
$this->db->where('STATUS_SO=4');
$this->db->where(['ID_SO' => $id]);
return $this->db->get()->result_array();
}
public function getData_DaftarOrder_bySO_forParsial($id) { //Penjualan = [43]
$this->db->select('*,a.BARANG_ID as DOBID,SUM(f.QTY_SJP) as SJP');
$this->db->from('daftar_order a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID');
$this->db->join('pelanggan d','d.ID_PELANGGAN=b.PELANGGAN_ID');
$this->db->join('jenis e','e.ID_JENIS=c.JENIS_ID');
$this->db->join('daftar_sjparsial f','f.DO_ID=a.ID_DO','left');
$this->db->where(['a.SO_ID' => $id]);
$this->db->group_by('a.ID_DO');
$this->db->order_by('a.ID_DO ASC');
return $this->db->get()->result_array();
}
public function getData_DaftarSjParsial_bySO_CDNull($id) { //Penjualan = [43, 47, 48, 54]
$this->db->select('*,a.BARANG_ID as BID');
$this->db->from('daftar_sjparsial a');
$this->db->join('daftar_order b','b.ID_DO=a.DO_ID');
$this->db->join('sales_order c','c.ID_SO=b.SO_ID');
$this->db->join('barang d','d.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis e','e.ID_JENIS=d.JENIS_ID');
$this->db->where(['b.SO_ID' => $id]);
$this->db->where('a.CD_ID is NULL');
return $this->db->get()->result_array();
}
public function getData_CetakDokumen_forParsial($id) { //Penjualan = [43, 44, 47, 48, 91]
$this->db->select('*,f.NAMA_PANGGILAN_KARYAWAN as NK,g.NAMA_PANGGILAN_KARYAWAN as NA,DATE_FORMAT(a.TGL_CD,"%d/%m/%Y") as TANGGAL');
$this->db->from('cetak_dokumen a');
$this->db->join('penjualan b','b.ID_PENJUALAN=a.PENJUALAN_ID');
$this->db->join('sales_order c','c.ID_SO=b.SO_ID');
$this->db->join('daftar_order d','d.SO_ID=c.ID_SO');
$this->db->join('daftar_sjparsial e','e.DO_ID=d.ID_DO');
$this->db->join('karyawan f','f.ID_KARYAWAN=a.KARYAWAN_ID','left');
$this->db->join('karyawan g','g.ID_KARYAWAN=a.ADMIN_ID','left');
$this->db->where(['b.SO_ID' => $id]);
$this->db->group_by('a.ID_CD');
return $this->db->get()->result_array();
}
public function getBarang_forParsialNonJasa($id, $level) { //Penjualan = [43]
if ($level ==0) {
$this->db->select('*,b.PCM_KUANTITI as QTY');
} else if ($level ==1) {
$this->db->select('*,b.ITP_KUANTITI as QTY');
}
$this->db->from('barang a');
$this->db->join('kuantiti b','b.ID_KUANTITI=a.ID_BARANG');
$this->db->join('jenis c','c.ID_JENIS=a.JENIS_ID');
$this->db->join('daftar_order d','d.BARANG_ID=a.ID_BARANG');
$this->db->where(['d.SO_ID' => $id]);
$this->db->where('c.TYPE_JENIS!=2');
$this->db->order_by('c.NAMA_JENIS ASC, a.NAMA_BARANG');
return $this->db->get()->result_array();
}
public function getData_Barang_forParsialJasaBiaya($id) { //Penjualan = [43]
$this->db->select('*');
$this->db->from('barang a');
$this->db->join('kuantiti b','b.ID_KUANTITI=a.ID_BARANG');
$this->db->join('jenis c','c.ID_JENIS=a.JENIS_ID');
$this->db->join('daftar_order d','d.BARANG_ID=a.ID_BARANG');
$this->db->where('c.TYPE_JENIS=2');
$this->db->where(['d.SO_ID' => $id]);
$this->db->order_by('a.NAMA_BARANG');
return $this->db->get()->result_array();
}
public function getData_SalesOrder_8($id) { //Penjualan = [44, 45, 46, 47, 48, 54]
$this->db->select('*');
$this->db->from('sales_order a');
$this->db->join('pelanggan b','b.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->where('a.STATUS_SO=8');
$this->db->where(['a.ID_SO' => $id]);
return $this->db->get()->result_array();
}
public function getData_DaftarSjParsial_bySO_allStatus($id) { //Penjualan = [44, 54, 91]
$this->db->select('*');
$this->db->from('daftar_sjparsial a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->where(['a.SO_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_DaftarSjParsial_bySO_byBARANG_CDNull($id1, $id2) { //Penjualan = [45]
$this->db->select('*');
$this->db->from('daftar_sjparsial a');
$this->db->join('daftar_order b','b.ID_DO=a.DO_ID');
$this->db->join('sales_order c','c.ID_SO=b.SO_ID');
$this->db->join('barang d','d.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis e','e.ID_JENIS=d.JENIS_ID');
$this->db->where(['b.SO_ID' => $id1]);
$this->db->where(['a.BARANG_ID' => $id2]);
$this->db->where('a.CD_ID is NULL');
return $this->db->get()->result_array();
}
public function getSum_DaftarSjParsial_byBARANG_bySO($id1, $id2) { //Penjualan = [45, 54]
$this->db->select('SUM(a.QTY_SJP) as SJP');
$this->db->from('daftar_sjparsial a');
$this->db->join('daftar_order b','b.ID_DO=a.DO_ID');
$this->db->where(['b.SO_ID' => $id1]);
$this->db->where(['a.BARANG_ID' => $id2]);
return $this->db->get()->result_array();
}
public function getData_SnSj_bySJP($id) { //Penjualan = [46, 48, 50, 51, 54, 91]
$this->db->select('*');
$this->db->from('sn_sjparsial a');
$this->db->join('daftar_sjparsial b','b.ID_SJP=a.SJP_ID');
$this->db->where(['a.SJP_ID' => $id]);
return $this->db->get()->result_array();
}
public function getQtyReal_DaftarSjParsial($id) { //Penjualan = [43, 47, 52, 53, 54]
$this->db->select('a.ID_SNSJ');
$this->db->from('sn_sjparsial a');
$this->db->join('daftar_sjparsial b','b.ID_SJP=a.SJP_ID');
$this->db->group_by('a.ID_SNSJ');
$this->db->where(['a.SJP_ID' => $id]);
return $this->db->count_all_results();
}
public function getData_Penjualan_bySO($id) { //Penjualan = [47, 54]
$this->db->select('*');
$this->db->from('penjualan a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID','left');
$this->db->where(['a.SO_ID' => $id]);
return $this->db->get()->result_array();
}
public function getID_CetakDokumen() { //Penjualan = [47]
$this->db->select_max('ID_CD');
$this->db->from('cetak_dokumen');
return $this->db->get()->result_array();
}
public function getData_DaftarSjParsial_byCD($id) { //Penjualan = [48]
$this->db->select('*');
$this->db->from('daftar_sjparsial a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->where(['a.CD_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_SnStock_SnSj_byNAMA_AllStatus($id) { //Penjualan = [48, 52, 54, 91]
$this->db->select('*');
$this->db->from('sn_stock a');
$this->db->join('sn_sjparsial b','b.NAMA_SNSJ=a.NAMA_SNSTOCK','left');
$this->db->where(['a.NAMA_SNSTOCK' => $id]);
return $this->db->get()->result_array();
}
public function getData_DaftarSjParsial_byID($id) { //Penjualan = [52, 53]
$this->db->select('*');
$this->db->from('daftar_sjparsial a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->where(['a.ID_SJP' => $id]);
return $this->db->get()->result_array();
}
public function getData_SnSj_SnStock_byNAMA_0($id) { //Penjualan = [52]
$this->db->select('*');
$this->db->from('sn_sjparsial a');
$this->db->join('sn_stock b','b.NAMA_SNSTOCK=a.NAMA_SNSJ','left');
$this->db->where(['a.NAMA_SNSJ' => $id]);
$this->db->where('b.STATUS_SNSTOCK=0');
return $this->db->get()->result_array();
}
public function getData_SnSj_byID($id) { //Penjualan = [53]
$this->db->select('*');
$this->db->from('sn_sjparsial a');
$this->db->join('sn_stock b','b.NAMA_SNSTOCK=a.NAMA_SNSJ');
$this->db->where(['a.ID_SNSJ' => $id]);
return $this->db->get()->result_array();
}
public function getData_CetakDokumen_byID($id) { //Penjualan = [54]
$this->db->select('*');
$this->db->from('cetak_dokumen');
$this->db->where(['ID_CD' => $id]);
return $this->db->get()->result_array();
}
public function getData_PenarikanLaku_byPELANGGAN($id) { //Penjualan = [55]
$this->db->select('*,SUM(a.QTY_DPL) as QTYDPL,DATE_FORMAT(d.TGL_PL,"%d/%m/%Y") as TANGGAL');
$this->db->from('daftar_penarikanlaku a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->join('penarikan_laku d','d.ID_PL=a.PL_ID');
$this->db->join('kuantiti e','e.ID_KUANTITI=a.BARANG_ID');
$this->db->where('d.STATUS_PL is NULL');
$this->db->where('d.PELANGGAN_ID=', $id);
$this->db->order_by('a.ID_DPL ASC');
$this->db->group_by('a.BARANG_ID');
return $this->db->get()->result_array();
}
public function getData_SnKonsi_byPELANGGAN_AllNull($id) { //Penjualan = [55]
$this->db->select('*');
$this->db->from('sn_konsi a');
$this->db->join('daftar_konsi b','b.ID_DFK=a.DFK_ID');
$this->db->join('konsinyasi c','c.ID_KONSINYASI=b.KONSINYASI_ID');
$this->db->join('barang d','d.ID_BARANG=b.BARANG_ID');
$this->db->where(['c.PELANGGAN_ID' => $id]);
$this->db->where('a.PK_ID is NULL');
$this->db->where('a.PL_ID is NULL');
$this->db->where('a.STATUS_SNK is NULL');
return $this->db->get()->result_array();
}
public function getData_SnJual_byDO_byBARANG($id1, $id2) { //Penjualan = [55]
$this->db->select('a.NAMA_SNJ');
$this->db->from('sn_jual a');
$this->db->join('daftar_order b','b.ID_DO=a.DO_ID');
$this->db->join('barang c','c.ID_BARANG=b.BARANG_ID');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID');
$this->db->where(['a.DO_ID' => $id1]);
$this->db->where(['b.BARANG_ID' => $id2]);
return $this->db->get()->result_array();
}
public function getData_SnKonsi_byBARANG_byPELANGGAN_forKonsinyasi($id1, $id2) { //Penjualan = [55]
$this->db->select('*');
$this->db->from('sn_konsi a');
$this->db->join('daftar_konsi b','b.ID_DFK=a.DFK_ID');
$this->db->join('konsinyasi c','c.ID_KONSINYASI=b.KONSINYASI_ID');
$this->db->where(['b.BARANG_ID' => $id1]);
$this->db->where(['c.PELANGGAN_ID' => $id2]);
$this->db->where('c.STATUS_KONSINYASI=0');
$this->db->where('a.STATUS_SNK is NULL');
$this->db->where('a.PL_ID is NULL');
$this->db->where('a.PK_ID is NULL');
return $this->db->get()->result_array();
}
public function getData_DaftarPenarikanKonsi_byBARANG_byPELANGGAN($id1, $id2) { //Penjualan = [55]
$this->db->select('SUM(a.QTY_DPK) as QTYDPK');
$this->db->from('daftar_penarikankonsi a');
$this->db->join('penarikan_konsi b','b.ID_PK=a.PK_ID');
$this->db->where(['a.BARANG_ID' => $id1]);
$this->db->where(['b.PELANGGAN_ID' => $id2]);
$this->db->group_by('a.BARANG_ID');
return $this->db->get()->result_array();
}
public function getData_DaftarPenarikanLaku_byBARANG_byPELANGGAN($id1, $id2) { //Penjualan = [55]
$this->db->select('SUM(a.QTY_DPL) as QTYDPL');
$this->db->from('daftar_penarikanlaku a');
$this->db->join('penarikan_laku b','b.ID_PL=a.PL_ID');
$this->db->where(['a.BARANG_ID' => $id1]);
$this->db->where(['b.PELANGGAN_ID' => $id2]);
$this->db->group_by('a.BARANG_ID');
return $this->db->get()->result_array();
}
public function getData_SalesOrder_9($id) { //Penjualan = [56, 60]
$this->db->select('*');
$this->db->from('sales_order a');
$this->db->join('pelanggan b','b.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->where('a.STATUS_SO=9 AND a.ID_SO=', $id);
return $this->db->get()->result_array();
}
public function getData_PenarikanLaku_byPELANGGAN_bySO_Null($id1, $id2) { //Penjualan = [56, 59]
$this->db->select('*');
$this->db->from('penarikan_laku');
$this->db->where(['PELANGGAN_ID' => $id1]);
$this->db->where(['SO_ID' => $id2]);
$this->db->where('STATUS_PL is NULL');
return $this->db->get()->result_array();
}
public function getData_DaftarPenarikanLaku_byPL($id) { //Penjualan = [56]
$this->db->select('*');
$this->db->from('daftar_penarikanlaku a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->join('penarikan_laku d','d.ID_PL=a.PL_ID');
$this->db->where(['a.PL_ID' => $id]);
return $this->db->get()->result_array();
}
public function getKode_PenarikanLaku() { //Penjualan = [57]
$this->db->select('MAX(KODE_PL) AS KODE');
$this->db->from('penarikan_laku');
return $this->db->get()->result_array();
}
public function getData_PenarikanLaku_byPELANGGAN_orderDESC($id) { //Penjualan = [58]
$this->db->select('*');
$this->db->from('penarikan_laku');
$this->db->where(['PELANGGAN_ID' => $id]);
$this->db->order_by('ID_PL DESC');
$this->db->limit('1');
return $this->db->get()->result_array();
}
public function getData_SnKonsi_byID($id) { //Penjualan = [58]
$this->db->select('*');
$this->db->from('sn_konsi a');
$this->db->join('sn_stock b','b.NAMA_SNSTOCK=a.NAMA_SNK');
$this->db->where(['a.ID_SNK' => $id]);
return $this->db->get()->result_array();
}
public function getData_PenarikanLaku_byPELANGGAN_Null($id) { //Penjualan = [58]
$this->db->select('*');
$this->db->from('penarikan_laku');
$this->db->where(['PELANGGAN_ID' => $id]);
$this->db->where('STATUS_PL is NULL');
return $this->db->get()->result_array();
}
public function getID_PLMax() { //Penjualan = [58]
$this->db->select_max('ID_PL');
$this->db->from('penarikan_laku');
return $this->db->get()->result_array();
}
public function getData_DaftarPenarikanLaku_byPL_byBARANG($id1, $id2) { //Penjualan = [58, 59, 91]
$this->db->select('*');
$this->db->from('daftar_penarikanlaku a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->join('penarikan_laku d','d.ID_PL=a.PL_ID');
$this->db->where(['a.PL_ID' => $id1]);
$this->db->where(['a.BARANG_ID' => $id2]);
return $this->db->get()->result_array();
}
public function getData_DaftarKonsi_byPELANGGAN_groupBARANG($id) { //Penjualan = [58]
$this->db->select('*,SUM(a.QTY_DFK) as QTY');
$this->db->from('daftar_konsi a');
$this->db->join('konsinyasi b','b.ID_KONSINYASI=a.KONSINYASI_ID');
$this->db->where(['b.PELANGGAN_ID' => $id]);
$this->db->group_by('a.BARANG_ID');
return $this->db->get()->result_array();
}
public function getData_SnKonsi_byPL_0($id) { //Penjualan = [58]
$this->db->select('*');
$this->db->from('sn_konsi a');
$this->db->join('daftar_konsi b','b.ID_DFK=a.DFK_ID');
$this->db->where(['a.PL_ID' => $id]);
$this->db->where('a.STATUS_SNK=0');
return $this->db->get()->result_array();
}
public function getData_SnKonsi_byPL_byNAMA_0($id1, $id2) { //Penjualan = [59]
$this->db->select('*');
$this->db->from('sn_konsi');
$this->db->where(['PL_ID' => $id1]);
$this->db->where(['NAMA_SNK' => $id2]);
$this->db->where('STATUS_SNK=0');
return $this->db->get()->result_array();
}
public function getData_SalesOrder_0_1_2_3_4_6_7_8($id = null) { //Penjualan = [61]
if ($id === null) {
$this->db->select('*,DATE_FORMAT(a.TGL_SO,"%d/%m/%Y") as TANGGAL,b.NAMA_PANGGILAN_KARYAWAN as NAMA_SALES');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('corp d','d.ID_CORP=a.CORP_ID');
$this->db->join('kota e','e.ID_KOTA=c.KOTA_ID');
$this->db->where('a.STATUS_SO is NOT NULL AND a.STATUS_SO!=5 AND a.STATUS_KIRIM is NULL');
$this->db->or_where('a.STATUS_SO is NOT NULL AND a.STATUS_SO!=5 AND a.STATUS_KIRIM=0');
$this->db->order_by('a.STATUS_KIRIM DESC, a.STATUS_SO ASC, a.TGL_SO ASC');
}else{
$this->db->select('*');
$this->db->from('sales_order');
$this->db->where('STATUS_SO is NOT NULL');
$this->db->where('STATUS_SO!=5');
$this->db->where('STATUS_KIRIM is NULL');
$this->db->where(['ID_SO' => $id]);
}
return $this->db->get()->result_array();
}
public function getData_SalesOrder_1_KirimNull($id=null) { //Penjualan = [61, 66]
if ($id==null) {
$this->db->select('*,DATE_FORMAT(a.TGL_SO,"%d/%m/%Y") as TANGGAL,b.NAMA_PANGGILAN_KARYAWAN as NAMA_SALES');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('corp d','d.ID_CORP=a.CORP_ID');
$this->db->join('kota e','e.ID_KOTA=c.KOTA_ID');
$this->db->where('a.STATUS_SO=1');
$this->db->where('a.STATUS_KIRIM is NULL');
$this->db->order_by('a.TGL_SO DESC');
$this->db->limit('500');
} else {
$this->db->select('*,DATE_FORMAT(a.TGL_SO,"%d/%m/%Y") as TANGGAL,b.NAMA_PANGGILAN_KARYAWAN as NAMA_SALES');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('corp d','d.ID_CORP=a.CORP_ID');
$this->db->join('kota e','e.ID_KOTA=c.KOTA_ID');
$this->db->where('a.STATUS_SO=1');
$this->db->where('a.STATUS_KIRIM is NULL');
$this->db->where('a.SALES_ID=', $id);
$this->db->order_by('a.TGL_SO DESC');
}
return $this->db->get()->result_array();
}
public function getData_Karyawan_Pengiriman() { //Penjualan = [61]
$this->db->select('*');
$this->db->from('karyawan a');
$this->db->join('jabatan b','b.ID_JABATAN=a.JABATAN_ID');
$this->db->where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="SUPPORT"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="UMUM"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="GUDANG"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="ADMIN GUDANG"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="ADMIN SALES"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="SALES"');
$this->db->or_where('a.STATUS_KARYAWAN=1 AND b.NAMA_JABATAN="SERVICE"');
$this->db->order_by('a.NAMA_PANGGILAN_KARYAWAN ASC');
return $this->db->get()->result_array();
}
public function getData_SalesOrder_forPenjadwalan() { //Penjualan = [61]
$tgl = date('Y-m-d');
$this->db->select('*,DATE_FORMAT(a.TGL_KIRIM,"%d/%m/%Y") as TANGGAL');
$this->db->from('sales_order a');
$this->db->join('pelanggan b','b.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('kota c','c.ID_KOTA=b.KOTA_ID');
$this->db->where('a.STATUS_SO=1');
$this->db->where('a.TGL_KIRIM is NOT NULL');
$this->db->where('a.TGL_SELESAI is NULL OR a.TGL_KIRIM>=', $tgl);
$this->db->order_by('a.TGL_KIRIM ASC, a.TGL_SELESAI ASC');
return $this->db->get()->result_array();
}
public function getData_JadwalPengiriman_bySO($id) { //Penjualan = [61, 64]
$this->db->select('b.NAMA_PANGGILAN_KARYAWAN');
$this->db->from('jadwal_pengiriman a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.KARYAWAN_ID');
$this->db->where('a.SO_ID=', $id);
return $this->db->get()->result_array();
}
public function getData_SalesOrder_1($id) { //Penjualan = [62, 65, 67]
$this->db->select('*');
$this->db->from('sales_order');
$this->db->where(['ID_SO' => $id]);
$this->db->where('STATUS_SO=1');
return $this->db->get()->result_array();
}
public function getData_SalesOrder_1_KirimNotNull() { //Penjualan = [64]
$this->db->select('*,b.NAMA_PANGGILAN_KARYAWAN as NAMA_PENGIRIM,
f.NAMA_PANGGILAN_KARYAWAN as NAMA_SALES');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.STATUS_KIRIM');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('corp d','d.ID_CORP=a.CORP_ID');
$this->db->join('kota e','e.ID_KOTA=c.KOTA_ID');
$this->db->join('karyawan f','f.ID_KARYAWAN=a.SALES_ID');
$this->db->where('a.STATUS_SO=1');
$this->db->where('a.STATUS_KIRIM is NOT NULL');
$this->db->where('a.TGL_SELESAI is NOT NULL');
$this->db->order_by('a.KODE_SO DESC, a.TGL_SELESAI DESC');
$this->db->limit('250');
return $this->db->get()->result_array();
}
public function getData_SalesOrder_1_KirimNull_forGudang($level) { //Penjualan = [66]
$this->db->select('*,DATE_FORMAT(a.TGL_SO,"%d/%m/%Y") as TANGGAL,b.NAMA_PANGGILAN_KARYAWAN as NAMA_SALES');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('corp d','d.ID_CORP=a.CORP_ID');
$this->db->join('kota e','e.ID_KOTA=c.KOTA_ID');
$this->db->join('jabatan f','f.ID_JABATAN=b.JABATAN_ID');
$this->db->where('a.STATUS_SO=1');
$this->db->where('a.STATUS_KIRIM is NULL');
$this->db->where('b.USERNAME!="service"');
if ($level == 0) {
$this->db->where('a.CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('a.CORP_ID>=4');
}
$this->db->order_by('a.TGL_SO DESC');
return $this->db->get()->result_array();
}
public function getData_SalesOrder_1_Kirim0($id) { //Penjualan = [66]
$this->db->select('*,DATE_FORMAT(a.TGL_SO,"%d/%m/%Y") as TANGGAL');
$this->db->from('sales_order a');
$this->db->join('pelanggan b','b.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('kota c','c.ID_KOTA=b.KOTA_ID');
$this->db->join('corp d','d.ID_CORP=a.CORP_ID');
$this->db->join('jadwal_pengiriman e','e.SO_ID=a.ID_SO');
$this->db->where('a.STATUS_SO=1');
$this->db->where('a.STATUS_KIRIM=0');
$this->db->where('a.TGL_SELESAI is NULL');
$this->db->where('e.KARYAWAN_ID=', $id);
$this->db->order_by('a.TGL_SO DESC');
$this->db->limit('500');
return $this->db->get()->result_array();
}
public function getData_Penjualan_1($id = null, $level = null, $tanggal_awal = null, $tanggal_akhir = null, $id_pelanggan = null){ //Penjualan = [68]
if ($id == null) {
$this->db->select('*,DATE_FORMAT(a.TGL_PENJUALAN,"%d/%m/%Y") as TGLJUAL');
$this->db->from('penjualan a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=b.PELANGGAN_ID');
$this->db->join('kota d','d.ID_KOTA=c.KOTA_ID');
$this->db->where('a.STATUS_PENJUALAN=1');
$this->db->where('a.TGL_PENJUALAN>=', $tanggal_awal);
$this->db->where('a.TGL_PENJUALAN<=', $tanggal_akhir);
if ($id_pelanggan!=null) {
$this->db->where('b.PELANGGAN_ID=', $id_pelanggan);
}
if ($level == 0) {
$this->db->where('b.CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('b.CORP_ID>=4');
}
$this->db->order_by('a.KODE_PENJUALAN ASC');
}else{
$this->db->select('*');
$this->db->from('penjualan');
$this->db->where('STATUS_PENJUALAN=1');
$this->db->where(['ID_PENJUALAN' => $id]);
}
return $this->db->get()->result_array();
}
public function getData_ReturPenjualan_byPENJUALAN_byKARYAWAN_Null($id_penjualan, $id_karyawan){ //Penjualan = [68]
$this->db->select('*');
$this->db->from('retur_penjualan');
$this->db->where(['PENJUALAN_ID' => $id_penjualan, 'ADMIN_ID' => $id_karyawan]);
$this->db->where('STATUS_RJ is NULL');
return $this->db->get()->result_array();
}
public function getSum_PiutangDagang_byPENJUALAN($id) { //Penjualan = [68, 81]
$this->db->select('SUM(DEBET_PDA) as DEBET,SUM(KREDIT_PDA) as KREDIT,SUM(DEBET_PDA-KREDIT_PDA) as TOTAL');
$this->db->from('piutang_dagang');
$this->db->where(['PENJUALAN_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_DaftarReturJual_byPENJUALAN_byKARYAWAN_byLEVEL_KODENull($id_penjualan, $id_karyawan, $level) { //Penjualan = [69, 73]
$this->db->select('*');
$this->db->from('daftar_returjual a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('retur_penjualan c','c.ID_RJ=a.RJ_ID');
$this->db->join('penjualan d','d.ID_PENJUALAN=c.PENJUALAN_ID');
$this->db->join('sales_order e','e.ID_SO=d.SO_ID');
$this->db->join('corp f','f.ID_CORP=e.CORP_ID');
$this->db->join('karyawan g','g.ID_KARYAWAN=c.ADMIN_ID');
$this->db->join('jabatan h','h.ID_JABATAN=g.JABATAN_ID');
$this->db->where(['c.PENJUALAN_ID' => $id_penjualan]);
$this->db->where(['c.ADMIN_ID' => $id_karyawan]);
$this->db->where(['h.LEVEL_JABATAN' => $level]);
$this->db->where('c.KODE_RJ is NULL');
return $this->db->get()->result_array();
}
public function getData_DaftarOrder_byPENJUALAN_JENIS0($id, $level) { //Penjualan = [69]
if ($level == 0) {
$this->db->select('a.QTY_DO,c.*,d.NAMA_JENIS,e.PCM_KUANTITI as REAL');
} else if ($level == 1) {
$this->db->select('a.QTY_DO,c.*,d.NAMA_JENIS,e.ITP_KUANTITI as REAL');
}
$this->db->from('daftar_order a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID');
$this->db->join('kuantiti e','e.ID_KUANTITI=c.ID_BARANG');
$this->db->join('penjualan f','f.SO_ID=a.SO_ID');
$this->db->where(['f.ID_PENJUALAN' => $id]);
$this->db->where('d.TYPE_JENIS="0"');
return $this->db->get()->result_array();
}
public function getData_DaftarOrder_byPENJUALAN_JENIS2($id) { //Penjualan = [69]
$this->db->select('*');
$this->db->from('daftar_order a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID');
$this->db->join('penjualan e','e.SO_ID=a.SO_ID');
$this->db->where(['e.ID_PENJUALAN' => $id]);
$this->db->where('d.TYPE_JENIS="2"');
return $this->db->get()->result_array();
}
public function getData_Penjualan_byID($id) { //Penjualan = [69, 85, 86, 87]
$this->db->select('*');
$this->db->from('penjualan a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID','left');
$this->db->join('pelanggan c','c.ID_PELANGGAN=b.PELANGGAN_ID','left');
$this->db->where(['a.ID_PENJUALAN' => $id]);
return $this->db->get()->result_array();
}
public function getData_SnJual_byPENJUALAN($id) { //Penjualan = [69]
$this->db->select('*');
$this->db->from('sn_jual a');
$this->db->join('sn_stock b','b.NAMA_SNSTOCK=a.NAMA_SNJ','left');
$this->db->join('barang c','c.ID_BARANG=b.BARANG_ID','left');
$this->db->join('daftar_order d','d.ID_DO=a.DO_ID','left');
$this->db->join('penjualan e','e.SO_ID=d.SO_ID','left');
$this->db->where(['e.ID_PENJUALAN' => $id]);
//$this->db->where('b.STATUS_SNSTOCK="1"');;
$this->db->order_by('c.KODE_BARANG ASC, a.NAMA_SNJ ASC');
return $this->db->get()->result_array();
}
public function getData_SnReturJual_byDRJ_RJNull($id_drj) { //Penjualan = [69, 71]
$this->db->select('*');
$this->db->from('sn_returjual a');
$this->db->join('retur_penjualan b','b.ID_RJ=a.RJ_ID');
$this->db->join('penjualan c','c.ID_PENJUALAN=b.PENJUALAN_ID');
$this->db->where(['a.DRJ_ID' => $id_drj]);
$this->db->where('b.STATUS_RJ is NULL');
$this->db->where('c.STATUS_PENJUALAN=1');
return $this->db->get()->result_array();
}
public function getData_DaftarOrder_byPENJUALAN($id) { //Penjualan = [70]
$this->db->select('*,FORMAT(a.HARGA_DO,2) as HARGA');
$this->db->from('daftar_order a');
$this->db->join('penjualan b','b.SO_ID=a.SO_ID','left');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID','left');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID','left');
$this->db->join('sales_order e','e.ID_SO=b.SO_ID','left');
$this->db->where(['b.ID_PENJUALAN' => $id]);
return $this->db->get()->result_array();
}
public function getSum_DaftarReturJual_byPENJUALAN_byBARANG($id1,$id2) { //Penjualan = [70]
$this->db->select('*,SUM(a.QTY_DRJ) as TOTAL_DRJ');
$this->db->from('daftar_returjual a');
$this->db->join('retur_penjualan b','b.ID_RJ=a.RJ_ID','left');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID','left');
$this->db->where(['b.PENJUALAN_ID' => $id1]);
$this->db->where(['a.BARANG_ID' => $id2]);
return $this->db->get()->result_array();
}
public function getData_ReturPenjualan_byPENJUALAN_byADMIN_Null($id1, $id2) { //Penjualan = [70]
$this->db->select('*');
$this->db->from('retur_penjualan');
$this->db->where(['PENJUALAN_ID' => $id1]);
$this->db->where(['ADMIN_ID' => $id2]);
$this->db->where('STATUS_RJ is NULL');
return $this->db->get()->result_array();
}
public function getID_RJMax() { //Penjualan = [70]
$this->db->select_max('ID_RJ');
$this->db->from('retur_penjualan');
return $this->db->get()->result_array();
}
public function getData_SnReturJual_byRJ_groupBARANG($id) { //Penjualan = [70]
$this->db->select('c.BARANG_ID,a.NAMA_SNRJ');
$this->db->from('sn_returjual a');
$this->db->join('sn_jual b','b.DO_ID=a.DO_ID','left');
$this->db->join('daftar_order c','c.ID_DO=a.DO_ID','left');
$this->db->where(['a.RJ_ID' => $id]);
$this->db->group_by('c.BARANG_ID');
return $this->db->get()->result_array();
}
public function getData_SnStock_bySNJ_4($id) { //Penjualan = [70]
$this->db->select('a.BARANG_ID');
$this->db->from('sn_stock a');
$this->db->join('sn_jual b','b.NAMA_SNJ=a.NAMA_SNSTOCK');
$this->db->where(['b.ID_SNJ' => $id]);
$this->db->where('a.STATUS_SNSTOCK=4');
$this->db->group_by('a.ID_SNSTOCK');
return $this->db->get()->result_array();
}
public function getData_DaftarReturJual_byRJ_byBARANG_byADMIN_Null($id_rj, $id_barang, $id_admin){ //Penjualan = [70]
$this->db->select('*');
$this->db->from('daftar_returjual a');
$this->db->join('retur_penjualan b','b.ID_RJ=a.RJ_ID');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID');
$this->db->where(['a.RJ_ID' => $id_rj]);
$this->db->where(['a.BARANG_ID' => $id_barang]);
$this->db->where(['b.ADMIN_ID' => $id_admin]);
$this->db->where('b.KODE_RJ is NULL');
$this->db->where('b.STATUS_RJ is NULL');
return $this->db->get()->result_array();
}
public function getData_DaftarReturJual_byRJ_groupID($id){ //Penjualan = [70]
$this->db->select('*,CONCAT("Rp ",FORMAT(a.HARGA_DRJ,2)) as HARGA,a.BARANG_ID as BID');
$this->db->from('daftar_returjual a');
$this->db->join('retur_penjualan b','b.ID_RJ=a.RJ_ID','left');
$this->db->join('penjualan c','c.ID_PENJUALAN=b.PENJUALAN_ID','left');
$this->db->join('daftar_order d','d.SO_ID=c.SO_ID','left');
$this->db->join('barang e','e.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis f','f.ID_JENIS=e.JENIS_ID');
$this->db->where(['a.RJ_ID' => $id]);
$this->db->group_by('a.ID_DRJ');
return $this->db->get()->result_array();
}
public function getData_SnReturJual_byRJ_groupID($id) { //Penjualan = [70, 74, 79]
$this->db->select('*');
$this->db->from('sn_returjual a');
$this->db->join('daftar_order b','b.ID_DO=a.DO_ID','left');
$this->db->group_by('a.ID_SNRJ');
$this->db->where(['a.RJ_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_DaftarReturJual_byID($id) { //Penjualan = [71, 77, 78]
$this->db->select('*');
$this->db->from('daftar_returjual a');
$this->db->join('retur_penjualan b','b.ID_RJ=a.RJ_ID','left');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID','left');
$this->db->where(['a.ID_DRJ' => $id]);
$this->db->group_by('a.ID_DRJ');
return $this->db->get()->result_array();
}
public function getData_SnStock_byNAMA_4($id) { //Penjualan = [71, 74, 79, 81]
$this->db->select('ID_SNSTOCK');
$this->db->from('sn_stock');
$this->db->where(['NAMA_SNSTOCK' => $id]);
$this->db->where('STATUS_SNSTOCK=4');
return $this->db->get()->result_array();
}
public function getData_DaftarReturJual_byRJ_Null($id){ //Penjualan = [71, 74]
$this->db->select('*');
$this->db->from('daftar_returjual a');
$this->db->join('retur_penjualan b','b.ID_RJ=a.RJ_ID');
$this->db->where('b.STATUS_RJ is NULL');
$this->db->where(['a.RJ_ID' => $id]);
return $this->db->get()->result_array();
}
public function getKode_ReturPenjualan() { //Penjualan = [72]
$this->db->select('MAX(KODE_RJ) AS KODE');
$this->db->from('retur_penjualan');
return $this->db->get()->result_array();
}
public function getData_ReturPenjualan_Null($id){ //Penjualan = [73, 74]
$this->db->select('*');
$this->db->from('retur_penjualan');
$this->db->where('STATUS_RJ is NULL');
$this->db->where(['ID_RJ' => $id]);
return $this->db->get()->result_array();
}
public function getData_ReturPenjualan_0($id = null){ //Penjualan = [75, 76, 79, 81]
if ($id === null) {
$this->db->select('*,DATE_FORMAT(a.TGL_RJ,"%d/%m/%Y") as TANGGAL');
$this->db->from('retur_penjualan a');
$this->db->join('daftar_returjual b','b.RJ_ID=a.ID_RJ','left');
$this->db->join('barang c','c.ID_BARANG=b.BARANG_ID','left');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID','left');
$this->db->join('penjualan e','e.ID_PENJUALAN=a.PENJUALAN_ID','left');
$this->db->join('sales_order f','f.ID_SO=e.SO_ID','left');
$this->db->join('pelanggan g','g.ID_PELANGGAN=f.PELANGGAN_ID','left');
$this->db->join('kota h','h.ID_KOTA=g.KOTA_ID','left');
$this->db->group_by('a.ID_RJ');
$this->db->where('a.STATUS_RJ=0');
}else{
$this->db->select('*');
$this->db->from('retur_penjualan a');
$this->db->join('daftar_returjual b','b.RJ_ID=a.ID_RJ','left');
$this->db->join('barang c','c.ID_BARANG=b.BARANG_ID','left');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID','left');
$this->db->join('penjualan e','e.ID_PENJUALAN=a.PENJUALAN_ID','left');
$this->db->join('sales_order f','f.ID_SO=e.SO_ID','left');
$this->db->join('pelanggan g','g.ID_PELANGGAN=f.PELANGGAN_ID','left');
$this->db->join('kota h','h.ID_KOTA=g.KOTA_ID','left');
$this->db->group_by('a.ID_RJ');
$this->db->where('a.STATUS_RJ=0');
$this->db->where(['a.ID_RJ' => $id]);
}
return $this->db->get()->result_array();
}
public function getData_DaftarReturJual_byRJ_byBARANG_0($id){ //Penjualan = [76]
$this->db->select('*,FORMAT(a.HARGA_DRJ,2) as HARGA,a.BARANG_ID as BID');
$this->db->from('daftar_returjual a');
$this->db->join('retur_penjualan b','b.ID_RJ=a.RJ_ID','left');
$this->db->join('penjualan c','c.ID_PENJUALAN=b.PENJUALAN_ID','left');
$this->db->join('daftar_order d','d.SO_ID=c.SO_ID','left');
$this->db->join('barang e','e.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis f','f.ID_JENIS=e.JENIS_ID');
$this->db->where('b.STATUS_RJ=0');
$this->db->where('a.BARANG_ID=d.BARANG_ID');
$this->db->where(['a.RJ_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_PiutangDagang_byPENJUALAN($id) { //Penjualan = [76, 91]
$this->db->select('*');
$this->db->from('piutang_dagang');
$this->db->where(['PENJUALAN_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_DaftarReturJual_byRJ_0($id){ //Penjualan = [79, 81]
$this->db->select('*');
$this->db->from('daftar_returjual a');
$this->db->join('retur_penjualan b','b.ID_RJ=a.RJ_ID');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID');
$this->db->where('b.STATUS_RJ=0');
$this->db->where(['a.RJ_ID' => $id]);
return $this->db->get()->result_array();
}
public function getKode_INVReturPenjualan() { //Penjualan = [80]
$this->db->select('MAX(INV_RJ) AS KODE');
$this->db->from('retur_penjualan');
return $this->db->get()->result_array();
}
public function getSum_DaftarReturJual_byRJ_groupDRJ_0($id){ //Penjualan = [81]
$this->db->select('SUM(a.HARGA_DRJ*a.QTY_DRJ) as TOTAL,f.PPN_SO');
$this->db->from('daftar_returjual a');
$this->db->join('retur_penjualan b','b.ID_RJ=a.RJ_ID');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID');
$this->db->join('penjualan e','e.ID_PENJUALAN=b.PENJUALAN_ID');
$this->db->join('sales_order f','f.ID_SO=e.SO_ID');
$this->db->where('b.STATUS_RJ=0');
$this->db->where(['a.RJ_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_SnReturJual_byRJ_byBARANG($id1,$id2) { //Penjualan = [81]
$this->db->select('a.NAMA_SNRJ');
$this->db->from('sn_returjual a');
$this->db->join('daftar_order b','b.ID_DO=a.DO_ID','left');
$this->db->where(['a.RJ_ID' => $id1]);
$this->db->where(['b.BARANG_ID' => $id2]);
$this->db->group_by('a.ID_SNRJ');
return $this->db->get()->result_array();
}
public function subTempoMonth($date,$month) { //Penjualan = [82]
$sum = strtotime(date("Y-m-d", strtotime("$date")) . " -$month month");
$dateTo=date('Y-m-d',$sum);
return $dateTo;
}
public function getData_Penjualan_forUbah($id=null, $level=null, $tanggal_awal=null, $tanggal_akhir=null) { //Penjualan = [82, 85, 86]
if ($id==NULL AND $tanggal_awal!=NULL AND $tanggal_akhir!=NULL) {
$this->db->select('*,DATE_FORMAT(a.TGL_PENJUALAN,"%d/%m/%Y") as TANGGAL,DATE_FORMAT(a.TEMPO_PENJUALAN,"%d/%m/%Y") as TEMPO,
,DATE_FORMAT(a.TT_PENJUALAN,"%d/%m/%Y") as TT');
$this->db->from('penjualan a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=b.PELANGGAN_ID');
$this->db->join('kota d','d.ID_KOTA=c.KOTA_ID');
$this->db->join('piutang_dagang e','e.PENJUALAN_ID=a.ID_PENJUALAN');
$this->db->join('corp f','f.ID_CORP=b.CORP_ID');
$this->db->where('a.TGL_PENJUALAN>=', $tanggal_awal);
$this->db->where('a.TGL_PENJUALAN<=', $tanggal_akhir);
$this->db->where('a.STATUS_PENJUALAN=1');
if ($level == 0) {
$this->db->where('b.CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('b.CORP_ID>=4');
}
$this->db->group_by('a.KODE_PENJUALAN');
$this->db->order_by('a.KODE_PENJUALAN ASC,a.TGL_PENJUALAN ASC');
} else {
$this->db->select('*');
$this->db->from('penjualan a');
$this->db->join('piutang_dagang b','b.PENJUALAN_ID=a.ID_PENJUALAN');
$this->db->join('sales_order c','c.ID_SO=a.SO_ID');
$this->db->join('pelanggan d','d.ID_PELANGGAN=c.PELANGGAN_ID');
$this->db->where(['a.ID_PENJUALAN' => $id]);
$this->db->where('a.STATUS_PENJUALAN=1');
if ($level == 0) {
$this->db->where('c.CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('c.CORP_ID>=4');
}
$this->db->group_by('a.ID_PENJUALAN');
}
return $this->db->get()->result_array();
}
public function getData_CetakDokumen_byPENJUALAN_forNota($id) { //Penjualan = [85, 86]
$this->db->select('*');
$this->db->from('cetak_dokumen');
$this->db->where('SJ_INV=1');
$this->db->where(['PENJUALAN_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_BiayaFee_byPENJUALAN($id) { //Penjualan = [87]
$this->db->select('*');
$this->db->from('biaya_fee a');
$this->db->join('penjualan b','b.ID_PENJUALAN=a.PENJUALAN_ID');
$this->db->join('sales_order c','c.ID_SO=b.SO_ID');
$this->db->where(['a.PENJUALAN_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_Penjualan_forKomisiUbah() { //Penjualan = [88]
$tgl_now = date('Y-m-01');
$tgl_now = $this->subTempoMonth($tgl_now,2);
$this->db->select('*,DATE_FORMAT(g.TGL_PENJUALAN,"%d/%m/%Y") as TANGGAL');
$this->db->from('daftar_order a');
$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis c','c.ID_JENIS=b.JENIS_ID');
$this->db->join('sales_order d','d.ID_SO=a.SO_ID');
$this->db->join('pelanggan e','e.ID_PELANGGAN=d.PELANGGAN_ID');
$this->db->join('kota f','f.ID_KOTA=e.KOTA_ID');
$this->db->join('penjualan g','g.SO_ID=d.ID_SO');
$this->db->where('g.STATUS_PENJUALAN=1');
$this->db->where('g.TGL_PENJUALAN>=', $tgl_now);
$this->db->order_by('g.TGL_PENJUALAN DESC, g.ID_PENJUALAN DESC,c.NAMA_JENIS ASC, b.KODE_BARANG');
return $this->db->get()->result_array();
}
public function getData_Penjualan_forBatal($id=null, $level=null) { //Penjualan = [90, 91]
$tgl_now = date('Y-m-01');
if ($id==null) {
$this->db->select('*,DATE_FORMAT(a.TGL_PENJUALAN,"%d/%m/%Y") as TANGGAL');
$this->db->from('penjualan a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=b.PELANGGAN_ID');
$this->db->join('kota d','d.ID_KOTA=c.KOTA_ID');
$this->db->join('piutang_dagang e','e.PENJUALAN_ID=a.ID_PENJUALAN');
$this->db->join('corp f','f.ID_CORP=b.CORP_ID');
$this->db->where('a.TGL_PENJUALAN>="'.$tgl_now.'"');
$this->db->where('a.STATUS_PENJUALAN=1');
if ($level == 0) {
$this->db->where('b.CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('b.CORP_ID>=4');
}
$this->db->order_by('a.KODE_PENJUALAN ASC');
$this->db->group_by('a.KODE_PENJUALAN');
} else {
$this->db->select('*');
$this->db->from('penjualan a');
$this->db->join('piutang_dagang b','b.PENJUALAN_ID=a.ID_PENJUALAN');
$this->db->join('sales_order c','c.ID_SO=a.SO_ID');
$this->db->where(['a.ID_PENJUALAN' => $id]);
$this->db->where('a.TGL_PENJUALAN>="'.$tgl_now.'"');
$this->db->where('a.STATUS_PENJUALAN=1');
if ($level == 0) {
$this->db->where('c.CORP_ID<=3');
} else if ($level == 1) {
$this->db->where('c.CORP_ID>=4');
}
$this->db->group_by('a.ID_PENJUALAN');
}
return $this->db->get()->result_array();
}
public function getData_ReturPenjualan_byPENJUALAN($id) { //Penjualan = [91]
$this->db->select('*');
$this->db->from('retur_penjualan a');
$this->db->join('penjualan b','b.ID_PENJUALAN=a.PENJUALAN_ID','left');
$this->db->where(['a.PENJUALAN_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_TransaksiStock_bySO_status1_Jenis0($id) { //Penjualan = [91]
$this->db->select('*');
$this->db->from('transaksi_stock a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID');
$this->db->where(['a.SO_ID' => $id]);
$this->db->where('a.JENIS_IO=0');
$this->db->where('a.STATUS_IO=1');
return $this->db->get()->result_array();
}
public function getData_TransaksiStock_bySO_status1_Jenis1($id) { //Penjualan = [91]
$this->db->select('*');
$this->db->from('transaksi_stock a');
$this->db->join('sales_order b','b.ID_SO=a.SO_ID');
$this->db->join('barang c','c.ID_BARANG=a.BARANG_ID');
$this->db->join('jenis d','d.ID_JENIS=c.JENIS_ID');
$this->db->where(['a.SO_ID' => $id]);
$this->db->where('a.JENIS_IO=1');
$this->db->where('a.STATUS_IO=1');
return $this->db->get()->result_array();
}
public function getData_PenarikanLaku_bySO_0($id) { //Penjualan = [91]
$this->db->select('*');
$this->db->from('penarikan_laku');
$this->db->where(['SO_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_LogNotifikasi_bySO($id) { //Penjualan = [91]
$this->db->select('*');
$this->db->from('log_notifikasi a');
$this->db->join('notifikasi b','b.ID_NOTIFIKASI=a.NOTIFIKASI_ID');
$this->db->where(['b.SO_ID' => $id]);
return $this->db->get()->result_array();
}
public function getData_SalesOrder_Not1_Not2_Not5_Not8_Not9($karyawan, $id = null) { //Penjualan = [92, 93, 97, 98]
if ($id === null) {
$this->db->select('*,DATE_FORMAT(a.TGL_SO,"%d/%m/%Y") as TANGGAL');
$this->db->from('sales_order a');
$this->db->join('karyawan b','b.ID_KARYAWAN=a.SALES_ID');
$this->db->join('pelanggan c','c.ID_PELANGGAN=a.PELANGGAN_ID');
$this->db->join('kota d','d.ID_KOTA=c.KOTA_ID');
$this->db->join('corp e','e.ID_CORP=a.CORP_ID');
if ($karyawan==1) {
$this->db->where('a.PL_ID is NULL AND a.STATUS_SO!=1 AND a.STATUS_SO!=2 AND a.STATUS_SO!=5 AND a.STATUS_SO!=8 AND a.STATUS_SO!=9');
} else if ($karyawan==21) {
$this->db->where('a.PL_ID is NULL AND a.STATUS_SO!=1 AND a.STATUS_SO!=2 AND a.STATUS_SO!=5 AND a.STATUS_SO!=8 AND a.STATUS_SO!=9 AND a.CORP_ID>=4');
} else if ($karyawan==9 OR $karyawan==43) {
$this->db->where('a.PL_ID is NULL AND a.STATUS_SO!=1 AND a.STATUS_SO!=2 AND a.STATUS_SO!=5 AND a.STATUS_SO!=8 AND a.STATUS_SO!=9 AND a.SALES_ID=50');
$this->db->or_where('a.PL_ID is NULL AND a.STATUS_SO!=1 AND a.STATUS_SO!=2 AND a.STATUS_SO!=5 AND a.STATUS_SO!=8 AND a.STATUS_SO!=9 AND a.SALES_ID=51');
$this->db->or_where('a.PL_ID is NULL AND a.STATUS_SO!=1 AND a.STATUS_SO!=2 AND a.STATUS_SO!=5 AND a.STATUS_SO!=8 AND a.STATUS_SO!=9 AND a.SALES_ID=', $karyawan);
} else {
$this->db->where('a.PL_ID is NULL AND a.STATUS_SO!=1 AND a.STATUS_SO!=2 AND a.STATUS_SO!=5 AND a.STATUS_SO!=8 AND a.STATUS_SO!=9 AND a.SALES_ID=', $karyawan);
}
}else{
$this->db->select('*');
$this->db->from('sales_order a');
$this->db->join('pelanggan b','b.ID_PELANGGAN=a.PELANGGAN_ID');
if ($karyawan!=1 AND $karyawan!=9 AND $karyawan!=21 AND $karyawan!=43) {
$this->db->where('a.SALES_ID=', $karyawan);
}
$this->db->where('a.ID_SO=', $id);
$this->db->where('a.STATUS_SO!=1');
$this->db->where('a.STATUS_SO!=2');
$this->db->where('a.STATUS_SO!=5');
$this->db->where('a.STATUS_SO!=8');
$this->db->where('a.STATUS_SO!=9');
$this->db->where('a.PL_ID is NULL');
}
return $this->db->get()->result_array();
}
public function getData_SalesOrder_6_7($id) { //Penjualan = [94]
$this->db->select('*');
$this->db->from('sales_order');
$this->db->where(['ID_SO' => $id]);
$this->db->where('STATUS_SO=6');
$this->db->or_where('STATUS_SO=7');
return $this->db->get()->result_array();
}
public function getData_SalesOrder_6($id) { //Penjualan = [99]
$this->db->select('*');
$this->db->from('sales_order');
$this->db->where(['ID_SO' => $id]);
$this->db->where('STATUS_SO=6');
return $this->db->get()->result_array();
}
}
?>
Reference in New Issue View Git Blame Copy Permalink