<?php
defined('BASEPATH') or exit('No direct script access allowed');
class ModelService 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_Service_byID_status0($id=null) { // Service [1]
		if ($id==null) {
			$this->db->select('*,DATE_FORMAT(a.TGL_SERVICE,"%d/%m/%Y") as TANGGAL');
			$this->db->from('service a');
			$this->db->join('karyawan b', 'b.ID_KARYAWAN=a.PEMBUAT_ID');
			$this->db->where('a.STATUS_SERVICE=0');
		} else {
			$this->db->select('*,DATE_FORMAT(a.TGL_SERVICE,"%d/%m/%Y") as TANGGAL');
			$this->db->from('service a');
			$this->db->join('karyawan b', 'b.ID_KARYAWAN=a.PEMBUAT_ID');
			$this->db->where('a.STATUS_SERVICE=0');
			$this->db->where('a.ID_SERVICE=', $id);
		}
		return $this->db->get()->result_array();
	}
	
	public function getData_DaftarService_bySERVICE($id) { // Service [1, 2, 3]
		$this->db->select('*');
		$this->db->from('daftar_service a');
		$this->db->join('service b','b.ID_SERVICE=a.SERVICE_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.SERVICE_ID=', $id);
		return $this->db->get()->result_array();
	}
	
	public function getData_SnService_byDFS($id) { // Service [1, 3]
		$this->db->select('*');
		$this->db->from('sn_service a');
		$this->db->join('daftar_service b','b.ID_DFS=a.DFS_ID');
		$this->db->where('a.DFS_ID=', $id);
		return $this->db->get()->result_array();
	}
	
	public function getData_Service_byID($id) { // Service [2, 3]
		$this->db->select('*');
		$this->db->from('service');
		$this->db->where('ID_SERVICE=', $id);
		return $this->db->get()->result_array();
	}

	public function getData_Kuantiti_byID($id) { // Service [2, 3]
		$this->db->select('*');
		$this->db->from('kuantiti');
		$this->db->where('ID_KUANTITI=', $id);
		return $this->db->get()->result_array();
	}
	
	public function getData_SnStock_byNAMA_status17($id) { // Service [3]
		$this->db->select('*');
		$this->db->from('sn_stock');
		$this->db->where('NAMA_SNSTOCK=', $id);
		$this->db->where('STATUS_SNSTOCK=17');
		return $this->db->get()->result_array();
	}
	
	public function getData_SnService_byKARYAWAN_statusNull($id) { // Service [4]
		$this->db->select('*');
		$this->db->from('sn_service a');
		$this->db->join('service b', 'b.ID_SERVICE=a.SERVICE_ID');
		$this->db->join('unitservice c', 'c.ID_UNITSERVICE=a.UNITSERVICE_ID');
		$this->db->join('pelanggan d', 'd.ID_PELANGGAN=b.PELANGGAN_ID');
		$this->db->join('kota e', 'e.ID_KOTA=d.KOTA_ID');
		$this->db->where('b.STATUS_SERVICE is NULL');
		$this->db->where('b.PEMBUAT_ID=', $id);
		return $this->db->get()->result_array();
	}

	public function getData_Pelanggan_byID($id = null) { // Service [4]
		if ($id == null) {
			$this->db->select('*');
			$this->db->from('pelanggan a');
			$this->db->join('kota b', 'b.ID_KOTA=a.KOTA_ID');
			$this->db->order_by('a.TYPE_PELANGGAN ASC');
		} 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 getData_UnitService_byID($id=null) { // Service [4, 8, 28]
		if ($id==null) {
			$this->db->select('*');
			$this->db->from('unitservice a');
			$this->db->join('jenis b','b.ID_JENIS=a.JENIS_ID');
			$this->db->order_by('a.NAMA_UNITSERVICE ASC');
		} else {
			$this->db->select('*');
			$this->db->from('unitservice a');
			$this->db->join('jenis b','b.ID_JENIS=a.JENIS_ID');
			$this->db->where('a.ID_UNITSERVICE=', $id);
		}
		return $this->db->get()->result_array();
	}
	
	public function getData_Kota_byID($id=null) { // Service [4]
		if ($id==null) {
			$this->db->select('*');
			$this->db->from('kota');
			$this->db->order_by('NAMA_KOTA ASC');
		} else {
			$this->db->select('*');
			$this->db->from('kota');
			$this->db->where('ID_KOTA=', $id);
		}
		return $this->db->get()->result_array();
	}
	
	public function getData_Jenis_BIAYANot0() { // Service [4]
        $this->db->select('*');
        $this->db->from('jenis');
		$this->db->where('BIAYA_SERVICE!="0.00"');
        return $this->db->get()->result_array();
    }
	
	public function getKode_Service() { // Service [5]
        $this->db->select('MAX(KODE_SERVICE) AS KODE');
        $this->db->from('service');
		$this->db->like('KODE_SERVICE','SRV', 'after');
        return $this->db->get()->result_array();
    }
	
	public function getData_Pelanggan_byNAMA_byKOTA($nama, $kota) { // Service [6]
        $this->db->select('*');
        $this->db->from('pelanggan');
		$this->db->where('NAMA_PELANGGAN=', $nama);
		$this->db->where('KOTA_ID=', $kota);
        return $this->db->get()->result_array();
    }
	
	public function getData_UnitService_byNAMA_byJENIS($nama, $jenis) { // Service [6]
        $this->db->select('*');
        $this->db->from('unitservice');
		$this->db->where('NAMA_UNITSERVICE=', $nama);
		$this->db->where('JENIS_ID=', $jenis);
        return $this->db->get()->result_array();
    }
	
	public function getID_PelangganMax() {  // Service [6]
        $this->db->select_max('ID_PELANGGAN');
        $this->db->from('pelanggan');
        return $this->db->get()->result_array();
    }
	
	public function getID_UnitServiceMax() {  // Service [6]
        $this->db->select_max('ID_UNITSERVICE');
        $this->db->from('unitservice');
        return $this->db->get()->result_array();
    }
	
	public function getID_ServiceMax() {  // Service [6]
        $this->db->select_max('ID_SERVICE');
        $this->db->from('service');
        return $this->db->get()->result_array();
    }
	
	public function getData_SnService_byID($id) { // Service [7, 8, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 24, 28]
		$this->db->select('*,DATE_FORMAT(b.TGL_SERVICE,"%d/%m/%Y") as TANGGAL,e.NAMA_PANGGILAN_KARYAWAN as PEMBUAT');
		$this->db->from('sn_service a');
		$this->db->join('service b', 'b.ID_SERVICE=a.SERVICE_ID');
		$this->db->join('pelanggan c', 'c.ID_PELANGGAN=b.PELANGGAN_ID','left');
		$this->db->join('unitservice d', 'd.ID_UNITSERVICE=a.UNITSERVICE_ID','left');
		$this->db->join('karyawan e', 'e.ID_KARYAWAN=b.PEMBUAT_ID','left');
		$this->db->where('a.ID_SNS=', $id);
		return $this->db->get()->result_array();
	}
	
	public function getData_SnService_statusNull() { // Service [11, 16, 35, 36]
		$this->db->select('*,DATE_FORMAT(b.TGL_SERVICE,"%d/%m/%Y") as TANGGAL');
		$this->db->from('sn_service a');
		$this->db->join('service b', 'b.ID_SERVICE=a.SERVICE_ID');
		$this->db->join('pelanggan c', 'c.ID_PELANGGAN=b.PELANGGAN_ID','left');
		$this->db->join('kota d', 'd.ID_KOTA=c.KOTA_ID','left');
		$this->db->join('unitservice e', 'e.ID_UNITSERVICE=a.UNITSERVICE_ID','left');
		$this->db->join('daftar_service g', 'g.ID_DFS=a.DFS_ID','left');
		$this->db->join('barang h', 'h.ID_BARANG=g.BARANG_ID','left');
		$this->db->where('a.STATUS_SNS is NULL');
		$this->db->where('b.STATUS_SERVICE is NOT NULL');
		return $this->db->get()->result_array();
	}
	
	public function getData_HistoriService_bySNS($id) { // Service [11, 12, 16, 21, 35, 36]
		$this->db->select('*');
		$this->db->from('histori_service a');
		$this->db->join('sn_service b', 'b.ID_SNS=a.SNS_ID');
		$this->db->join('karyawan c', 'c.ID_KARYAWAN=a.TEKNISI_ID');
		$this->db->where('a.SNS_ID=', $id);
		return $this->db->get()->result_array();
	}
	
	public function getData_SnService_statusNull_fromGudang() { // Service [16, 35, 36]
		$this->db->select('*,DATE_FORMAT(b.TGL_SERVICE,"%d/%m/%Y") as TANGGAL');
		$this->db->from('sn_service a');
		$this->db->join('service b', 'b.ID_SERVICE=a.SERVICE_ID');
		$this->db->where('a.STATUS_SNS is NULL');
		$this->db->where('b.PELANGGAN_ID is NULL');
		return $this->db->get()->result_array();
	}
	
	public function getData_SnService_statusNull_fromPelanggan() { // Service [16, 35, 36]
		$this->db->select('*,DATE_FORMAT(b.TGL_SERVICE,"%d/%m/%Y") as TANGGAL');
		$this->db->from('sn_service a');
		$this->db->join('service b', 'b.ID_SERVICE=a.SERVICE_ID');
		$this->db->join('pelanggan c', 'c.ID_PELANGGAN=b.PELANGGAN_ID');
		$this->db->where('a.STATUS_SNS is NULL');
		$this->db->where('b.PELANGGAN_ID is NOT NULL');
		return $this->db->get()->result_array();
	}
	
	public function getData_SnService_status1() { // Service [16, 35, 36]
		$this->db->select('*,DATE_FORMAT(b.TGL_SERVICE,"%d/%m/%Y") as TANGGAL');
		$this->db->from('sn_service a');
		$this->db->join('service b', 'b.ID_SERVICE=a.SERVICE_ID');
		$this->db->join('pelanggan c', 'c.ID_PELANGGAN=b.PELANGGAN_ID');
		$this->db->where('a.STATUS_SNS=1');
		return $this->db->get()->result_array();
	}
	
	public function subTempoMonth($date,$month) { // Service = [16] = Terpakai Untuk Fungsi Internal
		$sum = strtotime(date("Y-m-d", strtotime("$date")) . " -$month month");
		$dateTo=date('Y-m-d',$sum);
		return $dateTo;
	}
	
	public function getData_SalesOrder_byADMINSERVICE_status1() { // Service [16, 35, 36]
		$tgl = date('Y-m-d');
		$tgl_before = $this->subTempoMonth($tgl, 3);
		$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('sn_service c', 'c.SO_ID=a.ID_SO','left');
		$this->db->where('a.SALES_ID=53');
		$this->db->where('a.STATUS_SO=1');
		$this->db->where('a.TGL_SO>=', $tgl_before);
		$this->db->where('c.SO_ID is NULL');
		$this->db->order_by('a.TGL_SO DESC');
		return $this->db->get()->result_array();
	}

	public function getData_SalesOrder_byID($id) { // Service [17, 18]
		$this->db->select('*');
		$this->db->from('sales_order');
		$this->db->where('ID_SO=', $id);
		return $this->db->get()->result_array();
	}
	
	public function getData_SnService_allStatus_allSource() { // Service [22, 29]
		$this->db->select('*,DATE_FORMAT(b.TGL_SERVICE,"%d/%m/%Y") as TANGGAL,DATE_FORMAT(a.TGL_SNS,"%d/%m/%Y") as TANGGALAMBIL,
				   f.NAMA_PANGGILAN_KARYAWAN as PENERIMA,j.NAMA_PANGGILAN_KARYAWAN as PENGAMBIL');
		$this->db->from('sn_service a');
		$this->db->join('service b', 'b.ID_SERVICE=a.SERVICE_ID');
		$this->db->join('pelanggan c', 'c.ID_PELANGGAN=b.PELANGGAN_ID','left');
		$this->db->join('kota d', 'd.ID_KOTA=c.KOTA_ID','left');
		$this->db->join('unitservice e', 'e.ID_UNITSERVICE=a.UNITSERVICE_ID','left');
		$this->db->join('karyawan f', 'f.ID_KARYAWAN=b.PENERIMA_ID','left');
		$this->db->join('daftar_service g', 'g.ID_DFS=a.DFS_ID','left');
		$this->db->join('barang h', 'h.ID_BARANG=g.BARANG_ID','left');
		$this->db->join('penarikan_service i', 'i.ID_PSV=a.PSV_ID','left');
		$this->db->join('karyawan j', 'j.ID_KARYAWAN=i.PENGAMBIL_ID','left');
		return $this->db->get()->result_array();
	}
	
	public function getData_SnService_status2_PSVNull() { // Service [23]
		$this->db->select('*,DATE_FORMAT(a.TGL_SNS,"%d/%m/%Y") as TANGGAL');
		$this->db->from('sn_service a');
		$this->db->join('service b', 'b.ID_SERVICE=a.SERVICE_ID');
		$this->db->join('daftar_service c', 'c.ID_DFS=a.DFS_ID');
		$this->db->join('barang d', 'd.ID_BARANG=c.BARANG_ID');
		$this->db->where('a.STATUS_SNS=2');
		$this->db->where('a.PSV_ID is NULL');
		return $this->db->get()->result_array();
	}
	
	public function getData_NonSnService_groupBARANG() { // Service [25]
		$this->db->select('*,SUM(a.MASUK_NONSNS-a.KELUAR_NONSNS) as TOTAL');
		$this->db->from('nonsn_service a');
		$this->db->join('barang b','b.ID_BARANG=a.BARANG_ID');
		$this->db->group_by('a.BARANG_ID');
		$this->db->order_by('TOTAL DESC,b.KODE_BARANG ASC');
		return $this->db->get()->result_array();
	}

	public function getData_NonSnService_byBARANG($id) { // Service [26]
		$this->db->select('*,DATE_FORMAT(a.TGL_NONSNS,"%d/%m/%Y") as TANGGAL');
		$this->db->from('nonsn_service a');
		$this->db->join('karyawan b','b.ID_KARYAWAN=a.PENERIMA_ID');
		$this->db->where('a.BARANG_ID=', $id);
		return $this->db->get()->result_array();
	}

	public function getData_Barang_byID($id) { // Service [26]
		$this->db->select('*');
		$this->db->from('barang');
		$this->db->where('ID_BARANG=', $id);
		return $this->db->get()->result_array();
	}
	
	public function getData_SnService_PELANGGANNull_allStatus() { // Service [27]
		$this->db->select('*,DATE_FORMAT(b.TGL_SERVICE,"%d/%m/%Y") as TANGGAL');
		$this->db->from('sn_service a');
		$this->db->join('service b', 'b.ID_SERVICE=a.SERVICE_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('unitservice e', 'e.ID_UNITSERVICE=a.UNITSERVICE_ID');
		$this->db->join('karyawan f', 'f.ID_KARYAWAN=b.PEMBUAT_ID');
		$this->db->where('b.PELANGGAN_ID is NOT NULL');
		$this->db->order_by('b.KODE_SERVICE DESC');
		return $this->db->get()->result_array();
	}

	public function getData_KlaimService_Null() { // Service [29]
		$this->db->select('*,DATE_FORMAT(a.TGL_KSV,"%d/%m/%Y") as TANGGAL,h.NAMA_PANGGILAN_KARYAWAN as PENERIMA,n.NAMA_PANGGILAN_KARYAWAN as PEMBERI,d.PELANGGAN_ID as DPID,
				   g.NAMA_BARANG as NBG,o.NAMA_BARANG as NBO');
		$this->db->from('klaim_service a');
		$this->db->join('servicecenter b','b.ID_SERVICECENTER=a.SERVICECENTER_ID');
		$this->db->join('sn_service c','c.ID_SNS=a.SNS_ID','left');
		$this->db->join('service d','d.ID_SERVICE=c.SERVICE_ID','left');
		$this->db->join('unitservice e','e.ID_UNITSERVICE=c.UNITSERVICE_ID','left');
		$this->db->join('daftar_service f','f.ID_DFS=c.DFS_ID','left');
		$this->db->join('barang g','g.ID_BARANG=f.BARANG_ID','left');
		$this->db->join('karyawan h','h.ID_KARYAWAN=d.PENERIMA_ID','left');
		$this->db->join('pelanggan i','i.ID_PELANGGAN=d.PELANGGAN_ID','left');
		$this->db->join('kota j','j.ID_KOTA=i.KOTA_ID','left');
		$this->db->join('sn_pinjam k','k.ID_SNP=a.SNP_ID','left');
		$this->db->join('daftar_pinjam l','l.ID_DFP=k.DFP_ID','left');
		$this->db->join('peminjaman m','m.ID_PEMINJAMAN=l.PEMINJAMAN_ID','left');
		$this->db->join('karyawan n','n.ID_KARYAWAN=m.PEMBERI_ID','left');
		$this->db->join('barang o','o.ID_BARANG=l.BARANG_ID','left');
		$this->db->where('a.STATUS_KSV is NULL');
		$this->db->order_by('a.TGL_KSV ASC');
		return $this->db->get()->result_array();
	}

	public function getData_ServiceCenter_byID($id=null) { // Service [29]
		if ($id==null) {
			$this->db->select('*');
			$this->db->from('servicecenter');
			$this->db->order_by('NAMA_SERVICECENTER ASC');
		} else {
			$this->db->select('*');
			$this->db->from('servicecenter');
			$this->db->where('ID_SERVICECENTER=', $id);
		}
		return $this->db->get()->result_array();
	}
	
	public function getData_KlaimService_NotNull() { // Service [29]
		$this->db->select('*,DATE_FORMAT(a.TGL_KSV,"%d/%m/%Y") as TANGGAL');
		$this->db->from('klaim_service a');
		$this->db->join('sn_service b','b.ID_SNS=a.SNS_ID');
		$this->db->join('service c','c.ID_SERVICE=b.SERVICE_ID');
		$this->db->join('unitservice d','d.ID_UNITSERVICE=b.UNITSERVICE_ID','left');
		$this->db->join('daftar_service e','e.ID_DFS=b.DFS_ID','left');
		$this->db->join('barang f','f.ID_BARANG=e.BARANG_ID','left');
		$this->db->join('karyawan g','g.ID_KARYAWAN=c.PENERIMA_ID','left');
		$this->db->where('a.STATUS_KSV is NOT NULL');
		$this->db->order_by('a.TGL_KSV ASC');
		return $this->db->get()->result_array();
	}
	
	public function getData_SnPinjam_allStatus_allSource() { // Service [29]
		$this->db->select('*');
		$this->db->from('sn_pinjam a');
		$this->db->join('daftar_pinjam b', 'b.ID_DFP=a.DFP_ID');
		$this->db->join('peminjaman c', 'c.ID_PEMINJAMAN=b.PEMINJAMAN_ID');
		$this->db->join('barang d', 'd.ID_BARANG=b.BARANG_ID');
		return $this->db->get()->result_array();
	}
	
	public function getData_KlaimService_byID($id) { // Service [31, 32, 33]
		$this->db->select('*');
		$this->db->from('klaim_service');
		$this->db->where('ID_KSV=', $id);
		return $this->db->get()->result_array();
	}
	
	public function getData_KlaimService_AllStatus() { // Service [34]
		$this->db->select('*,DATE_FORMAT(a.TGL_KSV,"%d/%m/%Y") as TANGGAL,h.NAMA_PANGGILAN_KARYAWAN as PENERIMA,n.NAMA_PANGGILAN_KARYAWAN as PEMBERI,d.PELANGGAN_ID as DPID,
				   g.NAMA_BARANG as NBG,o.NAMA_BARANG as NBO');
		$this->db->from('klaim_service a');
		$this->db->join('servicecenter b','b.ID_SERVICECENTER=a.SERVICECENTER_ID');
		$this->db->join('sn_service c','c.ID_SNS=a.SNS_ID','left');
		$this->db->join('service d','d.ID_SERVICE=c.SERVICE_ID','left');
		$this->db->join('unitservice e','e.ID_UNITSERVICE=c.UNITSERVICE_ID','left');
		$this->db->join('daftar_service f','f.ID_DFS=c.DFS_ID','left');
		$this->db->join('barang g','g.ID_BARANG=f.BARANG_ID','left');
		$this->db->join('karyawan h','h.ID_KARYAWAN=d.PENERIMA_ID','left');
		$this->db->join('pelanggan i','i.ID_PELANGGAN=d.PELANGGAN_ID','left');
		$this->db->join('kota j','j.ID_KOTA=i.KOTA_ID','left');
		$this->db->join('sn_pinjam k','k.ID_SNP=a.SNP_ID','left');
		$this->db->join('daftar_pinjam l','l.ID_DFP=k.DFP_ID','left');
		$this->db->join('peminjaman m','m.ID_PEMINJAMAN=l.PEMINJAMAN_ID','left');
		$this->db->join('karyawan n','n.ID_KARYAWAN=m.PEMBERI_ID','left');
		$this->db->join('barang o','o.ID_BARANG=l.BARANG_ID','left');
		$this->db->order_by('a.TGL_KSV ASC');
		return $this->db->get()->result_array();
	}
	
}