<?php 
	defined('BASEPATH') or exit('No direct script access allowed');
	
	use \VisualAppeal\AutoUpdate;
	use Curl\Curl;
	
	class Model_penjualan extends CI_model{
		
		
		function __construct()
		{
			parent::__construct();
			$this->table = 'tb_users';
			
		}
		//CEK PEMBAYARAN
		public function cek_total_bayar($id=0){
			$this->db->select('SUM(jml_bayar) AS `total`');
			$this->db->where('id_invoice',$id);
			$query = $this->db->get('bayar_invoice_detail');
			if($query->num_rows() > 0){
				return $query->row()->total;
				}else{
				return 0;
			}
		}
		//CEK TOTAL BAYAR DI INVOICE
		public function cek_total_invoice($id=0){
			$this->db->select('total_bayar');
			$this->db->where('id_invoice',$id);
			return $this->db->get('invoice')->row()->total_bayar;
		}
		//CEK TOTAL DISKON DI INVOICE
		public function cek_total_diskon_invoice($id=0){
			$this->db->select('potongan_harga');
			$this->db->where('id_invoice',$id);
			return $this->db->get('invoice')->row()->potongan_harga;
		}
		//CEK SUM DETAIL INVOICE
		public function cek_total_detail($id=0){
			$this->db->select('SUM(jumlah * harga) AS `total`');
			$this->db->where('id_invoice',$id);
			return $this->db->get('invoice_detail')->row()->total;
		}
		
		public function get_harga_satuan($id_member=0, $id_bahan=0,$id_satuan=0,$type_harga=0,$jml=0,$harga_awal=0)
		{
			if ((int) $type_harga === 6) {
				$type_harga = resolve_semua_metode_harga($id_bahan, $id_member, $id_satuan, $jml);
			}
			$sql = $this->db->query("select * from harga_satuan where id_bahan=$id_bahan AND  id_satuan=$id_satuan");
			if ($sql->num_rows() > 0 and $type_harga == 2) 
			{
				$data = ['status' => true, 'harga' => $this->harga_response($sql->row()->harga_jual), 'satuan' => $id_satuan];
				} else {
				if ($type_harga == 1) 
				{
					$sql1 = $this->db->query("SELECT 
					`satuan`.`id` AS idsatuan,
					`satuan`.`satuan`,
					`satu_harga`.`id_satuan`,
					`satu_harga`.`harga_jual`,
					`bahan`.`title`
					FROM
					`bahan`
					INNER JOIN `satu_harga` ON (`bahan`.`id` = `satu_harga`.`id_bahan`)
					INNER JOIN `satuan` ON (`satu_harga`.`id_satuan` = `satuan`.`id`)
					WHERE
					`bahan`.`id` = $id_bahan");
					if ($sql1->num_rows() > 0) 
					{
						$harga_jual = $sql1->row()->harga_jual;
						$satuan = $sql1->row()->id_satuan;
						} else {
						$result = $this->harga_awal($id_bahan);
						$harga_jual = $result->harga_jual;
						$satuan = $result->id_satuan;
					}
					
					$data = [
					'status' => true, 
					'harga' => $this->harga_response($harga_jual), 
					'satuan' => $satuan, 
					'type_harga' => $type_harga,
					'log'=>'Satu Harga'
					];
					
				} elseif ($type_harga == 2) 
				{
					$sql2 = $this->db->query("SELECT 
					`satuan`.`id` AS idsatuan,
					`satuan`.`satuan`,
					`harga_satuan`.`id_satuan`,
					`bahan`.`title`,
					`harga_satuan`.`harga_jual`,
					`harga_satuan`.`id_bahan`
					FROM
					`satuan`
					INNER JOIN `harga_satuan` ON (`satuan`.`id` = `harga_satuan`.`id_satuan`)
					INNER JOIN `bahan` ON (`harga_satuan`.`id_bahan` = `bahan`.`id`)
					WHERE
					`harga_satuan`.`id_satuan` = $id_satuan AND `harga_satuan`.`id_bahan` = $id_bahan");
					
					if ($sql2->num_rows() > 0) 
					{
						$harga_jual = $sql2->row()->harga_jual;
						$satuan = $sql2->row()->id_satuan;
						} else {
						$result = $this->harga_awal($id_bahan);
						$harga_jual = $result->harga_jual;
						$satuan = $result->id_satuan;
					}
					
					$data = [
					'status' => true, 
					'harga' => $this->harga_response($harga_jual), 
					'satuan' => $satuan, 
					'type_harga' => $type_harga,
					'log'=>'Harga satuan'
					];
					
				} elseif ($type_harga == 3) 
				{
					
					$sql3 = $this->db->query("SELECT 
					`harga_member`.`id_bahan`,
					`harga_member`.`id_satuan`,
					`harga_member`.`id_member`,
					`harga_member`.`harga_jual`
					FROM
					`satuan`
					INNER JOIN `harga_member` ON (`satuan`.`id` = `harga_member`.`id_satuan`)
					INNER JOIN `bahan` ON (`harga_member`.`id_bahan` = `bahan`.`id`)
					INNER JOIN `konsumen` ON (`harga_member`.`id_member` = `konsumen`.`jenis_member`)
					WHERE
					`harga_member`.`id_member` = $id_member AND 
					`harga_member`.`id_bahan` = $id_bahan
					GROUP BY
					`harga_member`.`id_bahan`,
					`harga_member`.`id_satuan`,
					`harga_member`.`id_member`,
					`harga_member`.`harga_jual`");
					
					if ($sql3->num_rows() > 0) 
					{
						$harga_jual = $sql3->row()->harga_jual;
						$satuan = $sql3->row()->id_satuan;
						} else {
						$result = $this->harga_awal($id_bahan);
						$harga_jual = $result->harga_jual;
						$satuan = $result->id_satuan;
					}
					
					$data = [
					'status' => true, 
					'harga' => $this->harga_response($harga_jual), 
					'satuan' => $satuan, 
					'type_harga' =>$type_harga,
					'log'=>'Berdasarkan Level'
					];
					
				} elseif ($type_harga == 4) 
				{
					
					$sql = $this->db->query("select id_satuan,harga_jual from range_harga where id_bahan=$id_bahan AND $jml between jumlah_minimal and jumlah_maksimal");
					if ($sql->num_rows() > 0) 
					{
						$harga_jual = $sql->row()->harga_jual;
						$satuan = $sql->row()->id_satuan;
						} else {
						$result = $this->harga_awal($id_bahan);
						$harga_jual = $result->harga_jual;
						$satuan = $result->id_satuan;
					}
					
					$data = [
					'status' => true, 
					'harga' => $this->harga_response($harga_jual), 
					'satuan' => $satuan, 
					'type_harga' => $type_harga,
					'log'=>'Berdasarkan Jumlah'
					];
					
				} elseif ($type_harga == 5) 
				{
					
					$sql4 = $this->db->query("select id_satuan,harga_jual from harga_range_member where id_bahan=$id_bahan AND id_member='$id_member' AND $jml between jumlah_minimal and jumlah_maksimal");
					if ($sql4->num_rows() > 0) 
					{
						$harga_jual = $sql4->row()->harga_jual;
						$satuan = $sql4->row()->id_satuan;
						} else {
						$result = $this->harga_awal($id_bahan);
						$harga_jual = $result->harga_jual;
						$satuan = $result->id_satuan;
					}
					
					$data = [
					'status' => true, 
					'harga' => $this->harga_response($harga_jual), 
					'satuan' => $satuan,
					'type_harga' => $type_harga,
					'log'=>'Berdasarkan Jumlah & Level'
					];
					
					} else {
					$result = $this->harga_awal($id_bahan);
					
					$data = [
					'status' => true, 
					'harga' => $this->harga_response($result->harga_jual), 
					'satuan' => $result->id_satuan,
					'type_harga' => $type_harga,
					'log'=>'Default'
					];
				}
			}
			return $data;
		}

		private function harga_response($value)
		{
			$value = (float) $value;
			return rtrim(rtrim(number_format($value, 4, '.', ''), '0'), '.');
		}
		
		public function get_harga_range($id_member=0, $id_bahan=0,$id_satuan=0,$type_harga=0,$jml=0,$harga_awal=0,$status_hitung=0,$ukuran_hitung=0){
			if ((int) $type_harga === 6) {
				$type_harga = resolve_semua_metode_harga($id_bahan, $id_member, $id_satuan, $jml);
			}
			
			if ($status_hitung > 0 AND $ukuran_hitung!='NaN') {
				$tot_ukuran =  $ukuran_hitung;
				} else {
				$tot_ukuran = 1;
			}
			
			if ($type_harga == 1) {
				$sql1 = $this->db->query("SELECT 
				`satuan`.`id` AS idsatuan,
				`satuan`.`satuan`,
				`satu_harga`.`id_satuan`,
				`satu_harga`.`harga_jual`,
				`bahan`.`title`
				FROM
				`bahan`
				INNER JOIN `satu_harga` ON (`bahan`.`id` = `satu_harga`.`id_bahan`)
				INNER JOIN `satuan` ON (`satu_harga`.`id_satuan` = `satuan`.`id`)
				WHERE
				`bahan`.`id` = $id_bahan");
				if ($sql1->num_rows() > 0) {
					$harga_jual = $sql1->row()->harga_jual * $tot_ukuran;
					$satuan = $sql1->row()->id_satuan;
					} else {
					$sql = $this->db->query("select id,id_satuan,harga_jual from bahan where id=$id_bahan");
					$harga_jual = $sql->row()->harga_jual * $tot_ukuran;
					$satuan = $sql->row()->id_satuan;
				}
				
				$data = ['status' => true, 'harga' => $harga_jual, 'satuan' => $satuan, 'type_harga' => $type_harga,'log'=>'Satu Harga'];
			}elseif ($type_harga == 2) 
			{
				$sql2 = $this->db->query("SELECT 
				`satuan`.`id` AS idsatuan,
				`satuan`.`satuan`,
				`harga_satuan`.`id_satuan`,
				`bahan`.`title`,
				`harga_satuan`.`harga_jual`,
				`harga_satuan`.`id_bahan`
				FROM
				`satuan`
				INNER JOIN `harga_satuan` ON (`satuan`.`id` = `harga_satuan`.`id_satuan`)
				INNER JOIN `bahan` ON (`harga_satuan`.`id_bahan` = `bahan`.`id`)
				WHERE
				`harga_satuan`.`id_satuan` = $id_satuan AND `harga_satuan`.`id_bahan` = $id_bahan");
				if ($sql2->num_rows() > 0) {
					$harga_jual = $sql2->row()->harga_jual * $tot_ukuran;
					$satuan = $sql2->row()->id_satuan;
					} else {
					$sql = $this->db->query("select id,id_satuan,harga_jual from bahan where id=$id_bahan");
					$harga_jual = $sql->row()->harga_jual * $tot_ukuran;
					$satuan = $sql->row()->id_satuan;
				}
				$data = ['status' => true, 'harga' => $harga_jual, 'satuan' => $satuan, 'type_harga' => $type_harga,'log'=>'Harga satuan'];
			}elseif ($type_harga == 3) 
			{
				$sql3 = $this->db->query("SELECT 
				`harga_member`.`id_bahan`,
				`harga_member`.`id_satuan`,
				`harga_member`.`id_member`,
				`harga_member`.`harga_jual`
				FROM
				`satuan`
				INNER JOIN `harga_member` ON (`satuan`.`id` = `harga_member`.`id_satuan`)
				INNER JOIN `bahan` ON (`harga_member`.`id_bahan` = `bahan`.`id`)
				INNER JOIN `konsumen` ON (`harga_member`.`id_member` = `konsumen`.`jenis_member`)
				WHERE
				`harga_member`.`id_member` = $id_member AND 
				`harga_member`.`id_bahan` = $id_bahan
				GROUP BY
				`harga_member`.`id_bahan`,
				`harga_member`.`id_satuan`,
				`harga_member`.`id_member`,
				`harga_member`.`harga_jual`");
				if ($sql3->num_rows() > 0) {
					$harga_jual = $sql3->row()->harga_jual * $tot_ukuran;
					$satuan = $sql3->row()->id_satuan;
					} else {
					$result = $this->harga_awal($id_bahan);
					$harga_jual = $result->harga_jual * $tot_ukuran;
					$satuan = $result->id_satuan;
				}
				$data = ['status' => true, 'harga' => $harga_jual, 'satuan' => $satuan, 'type_harga' => $type_harga,'log'=>'Harga level'];
			}elseif ($type_harga == 4) 
			{
				$sql = $this->db->query("select id_satuan,harga_jual from range_harga where id_bahan=$id_bahan AND $jml between jumlah_minimal and jumlah_maksimal");
				if ($sql->num_rows() > 0) 
				{
					$harga_jual = $sql->row()->harga_jual * $tot_ukuran;
					$satuan = $sql->row()->id_satuan;
					$arr = 'A';
					}else{
					$result = $this->harga_awal($id_bahan);
					$harga_jual = $result->harga_jual * $tot_ukuran;
					$satuan = $result->id_satuan;
					$arr = 'B';
				}
				
				$data = [
				'status' => true, 
				'harga' => $harga_jual, 
				'satuan' => $satuan, 
				'type_harga' => $type_harga,
				'log'=>'Harga jumlah '.$arr
				];
				
			}elseif ($type_harga == 5) 
			{
				$sql = $this->db->query("select id_satuan,harga_jual from harga_range_member where id_bahan=$id_bahan AND id_member='$id_member' AND $jml between jumlah_minimal and jumlah_maksimal");
				
				if ($sql->num_rows() > 0) 
				{
					$harga_jual = $sql->row()->harga_jual * $tot_ukuran;
					$satuan =$sql->row()->id_satuan;
					}else{
					$result = $this->harga_awal($id_bahan);
					$harga_jual = $result->harga_jual * $tot_ukuran;
					$satuan = $result->id_satuan;
				}
				
				$data = [
				'status' => true, 
				'harga' => $harga_jual, 
				'satuan' => $satuan, 
				'type_harga' => $type_harga,
				'log'=>'Harga jumlah & level'
				];
				
				}else{
				$result = $this->harga_awal($id_bahan);
				$harga_jual = $result->harga_jual * $tot_ukuran;
				$satuan = $result->id_satuan;
				
				$data = [
				'status' => true, 
				'harga' => $harga_jual, 
				'satuan' => $satuan, 
				'id_member' => $id_member, 
				'type_harga' =>$type_harga,
				'log'=>'Default'
				];
				
			} 
			return $data;
		}
		
		private function harga_awal($id=0)
		{
			
			$this->db->select('id,id_satuan,harga_jual');
			$this->db->where('id',$id);
			$this->db->from('bahan');
			$query = $this->db->get();
			$row = $query->num_rows() > 0 ? $query->row() : false;
			$response = (object)['harga_jual'=>$row->harga_jual,'id_satuan'=>$row->id_satuan];
			
			return $response;
		}
		public function cek_status_invoice($id=0)
		{
			
			$this->db->select('status');
			$this->db->where('id_invoice',$id);
			$this->db->from('invoice');
			$query = $this->db->get();
			$row = $query->num_rows() > 0 ? $query->row() : false;
			$response = (object)['status'=>$row->status];
			
			return $response;
		}
		// Fungsi untuk mendapatkan total order, total bayar, total diskon, total pajak, total piutang
		public function get_summary_data($params = []) {
			
			$this->db->select('
            SUM(id.jumlah * id.harga) AS total_order,
            SUM(i.total_bayar) AS total_bayar,
            SUM(id.diskon) AS total_diskon,
            SUM(i.pajak) AS total_pajak,
            SUM(i.total_bayar - COALESCE(bjd.jml_bayar, 0)) AS total_piutang
			');
			$this->db->from('invoice i');
			$this->db->join('invoice_detail id', 'i.id_invoice = id.id_invoice');
			$this->db->join('bayar_invoice_detail bjd', 'i.id_invoice = bjd.id_invoice', 'left');
			$this->db->where('i.status', 'simpan');  // Anda bisa menyesuaikan status sesuai kebutuhan
			$this->db->group_by('i.status');  // Mengelompokkan berdasarkan status transaksi
			
			if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
				}
			}
			
			if (array_key_exists('search', $params)) {
				if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
                    $this->db->where('i.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
				}
			}
			// Menjalankan query
			$query = $this->db->get();
			
			// Mengembalikan hasil dalam bentuk objek
			return $query->row();
		}
		
		// Get clients with server-side processing
		public function get_clients_server_side($limit, $start, $search = '', $order_column = 'id_invoice', $order_dir = 'desc',$start_date = null,$status_trx="") {
			// Apply search
			if ($search) {
				$this->db->like('k.nama', $search);
				$this->db->or_like('k.no_hp', $search);
				$this->db->or_like('i.id_transaksi', $search);
				$this->db->or_like('u.nama_lengkap', $search);
			}
			if ($status_trx) {
				if ($status_trx == 1) {
					$this->db->where('`i`.`status`', 'simpan');
					$this->db->like('`i`.`lunas`', $status_trx);
					} elseif ($status_trx == 2) {
					$this->db->like('`i`.`lunas`', 0);
					} else {
					$this->db->like('`i`.`status`', $status_trx);
				}
			}
			if ($start_date) {
				$date = date_ranges($start_date);
				$dari = date_replace_slash($date['dari']);
				$sampai = date_replace_slash($date['sampai']);
				$this->db->where('i.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($dari)).'" and "'.date('Y-m-d', strtotime($sampai)).'"');
			}
			// Apply ordering
			$this->db->order_by($order_column, $order_dir);
			
			// Apply pagination
			$this->db->limit($limit, $start);
			$this->db->select('
            `i`.`tgl_trx`,
			`i`.`id_transaksi`,
			`i`.`id_konsumen`,
			`i`.`id_dropshiper`,
			`i`.`total_bayar` AS total_order,
			`i`.`jumlah_bayar` AS total_bayar,
			`i`.`potongan_harga`,
			`i`.`cashback`,
			`i`.`pajak`,
			`i`.`status`,
			`i`.`oto`,
			`i`.`lunas`,
			`i`.`cetak`,
			`k`.`id`,
			`k`.`nama`,
			`k`.`no_hp`,
			`k`.`tampil`,
			`k`.`perusahaan`,
            i.id_invoice,
            k.nama AS konsumen_nama,
            u.nama_lengkap AS user_nama
            ');
            
            // From invoice table and joining with konsumen and tb_users
            $this->db->from('invoice i');
            $this->db->join('konsumen k', 'k.id = i.id_konsumen', 'left');
            $this->db->join('tb_users u', 'u.id_user = i.id_marketing', 'left'); // Adjust this join condition as needed
            // $this->db->join('invoice_detail id', 'id.id_invoice = i.id_invoice', 'left');
            
            // Group by invoice ID
            $this->db->group_by('i.id_invoice');
			// Get the clients
			$query = $this->db->get();
			return $query->result_array();
		}
		// Get clients with server-side processing
		public function get_clients_server_sides($limit, $start, $search = '', $order_column = 'id_invoice', $order_dir = 'desc',$start_date = null,$status_trx="") {
			// Apply search
			if ($search) {
				$this->db->like('k.nama', $search);
				$this->db->or_like('k.no_hp', $search);
				$this->db->or_like('i.id_transaksi', $search);
				$this->db->or_like('u.nama_lengkap', $search);
			}
			if ($status_trx) {
				if ($status_trx == 1) {
					$this->db->where('`i`.`status`', 'simpan');
					$this->db->like('`i`.`lunas`', $status_trx);
					} elseif ($status_trx == 2) {
					$this->db->like('`i`.`lunas`', 0);
					} else {
					$this->db->like('`i`.`status`', $status_trx);
				}
			}
			if ($start_date) {
				$date = date_ranges($start_date);
				$dari = date_replace_slash($date['dari']);
				$sampai = date_replace_slash($date['sampai']);
				$this->db->where('i.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($dari)).'" and "'.date('Y-m-d', strtotime($sampai)).'"');
			}
			// Apply ordering
			$this->db->order_by($order_column, $order_dir);
			
			// Apply pagination
			$this->db->limit($limit, $start);
			$this->db->select('
            `i`.`tgl_trx`,
			`i`.`id_transaksi`,
			`i`.`id_konsumen`,
			`i`.`id_dropshiper`,
			`i`.`potongan_harga`,
			`i`.`cashback`,
			`i`.`pajak`,
			`i`.`status`,
			`i`.`oto`,
			`i`.`lunas`,
			`i`.`cetak`,
			`k`.`id`,
			`k`.`nama`,
			`k`.`no_hp`,
			`k`.`tampil`,
			`k`.`perusahaan`,
            i.id_invoice,
            k.nama AS konsumen_nama,
            u.nama_lengkap AS user_nama,
            SUM(id.jumlah * id.harga) AS total_order,
            i.potongan_harga AS total_potongan_harga,
            (SELECT IFNULL(SUM(bd.jml_bayar), 0) FROM bayar_invoice_detail bd 
            WHERE bd.id_invoice = i.id_invoice) AS total_bayar,
            (SUM(id.jumlah * id.harga) - 
            (SELECT IFNULL(SUM(bd.jml_bayar), 0) FROM bayar_invoice_detail bd 
            WHERE bd.id_invoice = i.id_invoice)) AS sisa_bayar
            ');
            
            // From invoice table and joining with konsumen and tb_users
            $this->db->from('invoice i');
            $this->db->join('konsumen k', 'k.id = i.id_konsumen', 'left');
            $this->db->join('tb_users u', 'u.id_user = i.id_marketing', 'left'); // Adjust this join condition as needed
            $this->db->join('invoice_detail id', 'id.id_invoice = i.id_invoice', 'left');
            
            // Group by invoice ID
            $this->db->group_by('i.id_invoice');
			// Get the clients
			$query = $this->db->get();
			return $query->result_array();
		}
		
		// Get the total number of records (for pagination)
		public function get_total_clients($search = '',$start_date = null,$status_trx="") {
			if ($search) {
				$this->db->like('k.nama', $search);
				$this->db->or_like('k.no_hp', $search);
				$this->db->or_like('i.id_transaksi', $search);
				$this->db->or_like('u.nama_lengkap', $search);
			}
			if ($status_trx) {
				if ($status_trx == 1) {
					$this->db->where('`i`.`status`', 'simpan');
					$this->db->like('`i`.`lunas`', $status_trx);
					} elseif ($status_trx == 2) {
					$this->db->like('`i`.`lunas`', 0);
					} else {
					$this->db->like('`i`.`status`', $status_trx);
				}
			}
			if ($start_date) {
				$date = date_ranges($start_date);
				$dari = date_replace_slash($date['dari']);
				$sampai = date_replace_slash($date['sampai']);
				$this->db->where('i.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($dari)).'" and "'.date('Y-m-d', strtotime($sampai)).'"');
			}
			$this->db->select('
            `i`.`tgl_trx`,
			`i`.`id_transaksi`,
			`i`.`id_konsumen`,
			`i`.`id_dropshiper`,
			`i`.`potongan_harga`,
			`i`.`cashback`,
			`i`.`pajak`,
			`i`.`status`,
			`i`.`oto`,
			`i`.`lunas`,
			`i`.`cetak`,
			`k`.`id`,
			`k`.`nama`,
			`k`.`no_hp`,
			`k`.`tampil`,
			`k`.`perusahaan`,
            i.id_invoice,
            k.nama AS konsumen_nama,
            u.nama_lengkap AS user_nama,
            SUM(id.jumlah * id.harga) AS total_order,
            i.potongan_harga AS total_potongan_harga,
            (SELECT IFNULL(SUM(bd.jml_bayar), 0) FROM bayar_invoice_detail bd 
            WHERE bd.id_invoice = i.id_invoice) AS total_bayar,
            (SUM(id.jumlah * id.harga) - 
            (SELECT IFNULL(SUM(bd.jml_bayar), 0) FROM bayar_invoice_detail bd 
            WHERE bd.id_invoice = i.id_invoice)) AS sisa_bayar
            ');
            
            // From invoice table and joining with konsumen and tb_users
            $this->db->from('invoice i');
            $this->db->join('konsumen k', 'k.id = i.id_konsumen', 'left');
            $this->db->join('tb_users u', 'u.id_user = i.id_marketing', 'left'); // Adjust this join condition as needed
            $this->db->join('invoice_detail id', 'id.id_invoice = i.id_invoice', 'left');
            
            // Group by invoice ID
            $this->db->group_by('i.id_invoice');
			return $this->db->count_all_results();
		}
		
		public function getRows($params = [])
        {
            // print_r($params);
            $this->db->select('`invoice`.`tgl_trx`,
			`invoice`.`tgl_ambil`,
			`invoice`.`id_transaksi`,
			`invoice`.`id_konsumen`,
			`invoice`.`id_invoice`,
			`invoice`.`id_dropshiper`,
			`invoice`.`total_bayar` as total_order,
			`invoice`.`potongan_harga`,
			`invoice`.`cashback`,
			`invoice`.`pajak`,
			`invoice`.`total_pajak`,
			`invoice`.`status`,
			`invoice`.`oto`,
			`invoice`.`lunas`,
			`invoice`.`history`,
			`invoice`.`cetak`,
			`konsumen`.`id`,
			`konsumen`.`nama`,
			`konsumen`.`no_hp`,
			`konsumen`.`tampil`,
			`konsumen`.`perusahaan`,
			`tb_users`.`nama_lengkap`');
            $this->db->from($this->table);
            $this->db->join('invoice', '`tb_users`.`id_user` = `invoice`.`id_marketing`');
            $this->db->join('konsumen', '`invoice`.`id_konsumen` = `konsumen`.`id`');
            $this->db->not_like('`invoice`.`id_transaksi`', 'CMP-', 'after');
            
            if (array_key_exists('where', $params)) {
                foreach ($params['where'] as $key => $val) {
                    $this->db->where($key, $val);
				}
			}
            if (array_key_exists('search', $params)) {
                if (!empty($params['search']['keywords'])) {
                    $this->db->group_start();
                    $this->db->like('`invoice`.`id_invoice`', $params['search']['keywords']);
                    $this->db->or_like('`konsumen`.`nama`', $params['search']['keywords']);
                    $this->db->or_like('`konsumen`.`no_hp`', $params['search']['keywords']);
                    $this->db->or_like('`tb_users`.`nama_lengkap`', $params['search']['keywords']);
                    $this->db->or_like('`invoice`.`id_transaksi`', $params['search']['keywords']);
                    $this->db->or_like('`invoice`.`total_bayar`', $params['search']['keywords']);
                    $this->db->group_end();
				}
                if (!empty($params['search']['dari']) and !empty($params['search']['sampai'])) {
                    $this->db->where('invoice.tgl_trx BETWEEN "'.date('Y-m-d', strtotime($params['search']['dari'])).'" and "'.date('Y-m-d', strtotime($params['search']['sampai'])).'"');
				}
                if (!empty($params['search']['trx'])) {
                    $trx = $params['search']['trx'];
                    $sumBayarExpr = '(SELECT COALESCE(SUM(bid.jml_bayar),0) FROM bayar_invoice_detail bid WHERE bid.id_invoice = invoice.id_invoice)';

                    if ($trx == 1) {
                        $this->db->where('`invoice`.`status`', 'simpan');
                        $this->db->like('`invoice`.`lunas`', $trx);
                        } elseif ($trx == 2) {
                        $this->db->like('`invoice`.`lunas`', 0);
                        } elseif ($trx == 'belum_dibayar') {
                        $this->db->where('`invoice`.`lunas`', 0);
                        $this->db->where('`invoice`.`status` !=', 'batal');
                        $this->db->where($sumBayarExpr.' <= 0', null, false);
                        $this->db->where('(invoice.history IS NULL OR invoice.history NOT LIKE "%#DITAGIH#%")', null, false);
                        } elseif ($trx == 'dibayar_sebagian') {
                        $this->db->where('`invoice`.`lunas`', 0);
                        $this->db->where('`invoice`.`status` !=', 'batal');
                        $this->db->where($sumBayarExpr.' > 0', null, false);
                        $this->db->where('(invoice.history IS NULL OR invoice.history NOT LIKE "%#DITAGIH#%")', null, false);
                        } elseif ($trx == 'ditagih') {
                        $this->db->where('`invoice`.`lunas`', 0);
                        $this->db->where('`invoice`.`status` !=', 'batal');
                        $this->db->where('invoice.history LIKE "%#DITAGIH#%"', null, false);
                        } else {
                        $this->db->like('`invoice`.`status`', $trx);
					}
				}
			}
            if (!empty($params['search']['sortBy'])) {
                if ($params['search']['sortBy'] == 'min_order') {
                    $this->db->order_by('`invoice`.`total_bayar`', 'ASC');
                    } elseif ($params['search']['sortBy'] == 'max_order') {
                    $this->db->order_by('`invoice`.`total_bayar`', 'DESC');
                    } else {
                    $this->db->order_by('`invoice`.`id_invoice`', $params['search']['sortBy']);
				}
			}
            if (array_key_exists('returnType', $params) && $params['returnType'] == 'count') {
                $result = $this->db->count_all_results();
                } else {
                if (array_key_exists('id', $params) || (array_key_exists('returnType', $params) && $params['returnType'] == 'single')) {
                    if (!empty($params['id'])) {
                        $this->db->where('invoice.id_invoice', $params['id']);
					}
                    $query = $this->db->get();
                    $result = $query->row_array();
                    } else {
                    $this->db->order_by('invoice.id_invoice', 'desc');
                    if (array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit'], $params['start']);
                        } elseif (!array_key_exists('start', $params) && array_key_exists('limit', $params)) {
                        $this->db->limit($params['limit']);
					}
                    
                    $query = $this->db->get();
                    $result = ($query->num_rows() > 0) ? $query->result_array() : false;
				}
			}
            
            // Return fetched data
            return $result;
		}
	}																													
