<?php 

class M_transaksi extends CI_Model{
	public function __construct()
	{
		$this->load->database();
	}
	 
	function data_transaksi($id_admin,$level,$id_kantor){
		date_default_timezone_set('Asia/Jakarta');
        $tanggal = date("Y-m-d");
        $tgl_awal = $this->input->post('tgl_awal');
        $tgl_akhir = $this->input->post('tgl_akhir');
         
        $metode_bayar=$this->input->post('metode_bayar');
        if($level =='manager'){
        	if(empty($tgl_awal)){
		                $query = $this->db->query("select  * from tb_transaksi where date(tb_transaksi.date) = CURDATE() order by tb_transaksi.date desc ");
		        }else{

		           
		        	if( $id_kantor == 'semua' &&   $metode_bayar =='semua' ){
		        		 $cari="where  tb_transaksi.date BETWEEN '$tgl_awal' AND '$tgl_akhir'  ";

		        	}else if(  $id_kantor == 'semua' &&   $metode_bayar !='semua' ){
		        		 $cari="where  
		                           		 tb_transaksi.metode_bayar ='$metode_bayar' and
		                 				tb_transaksi.date BETWEEN '$tgl_awal' AND '$tgl_akhir'  ";

		        	}else if(  $id_kantor != 'semua' &&   $metode_bayar =='semua' ){
		        		 $cari="where tb_transaksi.id_kantor ='$id_kantor'   and
		                 				tb_transaksi.date BETWEEN '$tgl_awal' AND '$tgl_akhir'  ";

		        	}else{
		        		 $cari="where tb_transaksi.id_kantor ='$id_kantor' and 
		                           		 tb_transaksi.metode_bayar ='$metode_bayar' and
		                 				tb_transaksi.date BETWEEN '$tgl_awal' AND '$tgl_akhir'  ";

		        	}	



					$query = $this->db->query("select  * from tb_transaksi $cari  order by tb_transaksi.date desc ");


		        }


        	

        }else{


        	if(empty($tgl_awal)){
		                $query = $this->db->query("select  * from tb_transaksi where date(tb_transaksi.date) = CURDATE() AND tb_transaksi.id_admin= '$id_admin' order by tb_transaksi.date desc ");
		        }else{

		           
		        	if( $id_kantor == 'semua' &&   $metode_bayar =='semua' ){
		        		 $cari="where  tb_transaksi.date BETWEEN '$tgl_awal' AND '$tgl_akhir' AND tb_transaksi.id_admin= '$id_admin'  ";

		        	}else if(  $id_kantor == 'semua' &&   $metode_bayar !='semua' ){
		        		 $cari="where  
		                           		 tb_transaksi.metode_bayar ='$metode_bayar' and
		                 				tb_transaksi.date BETWEEN '$tgl_awal' AND '$tgl_akhir'  AND tb_transaksi.id_admin= '$id_admin' ";

		        	}else if(  $id_kantor != 'semua' &&   $metode_bayar =='semua' ){
		        		 $cari="where tb_transaksi.id_kantor ='$id_kantor'   and
		                 				tb_transaksi.date BETWEEN '$tgl_awal' AND '$tgl_akhir' AND tb_transaksi.id_admin= '$id_admin' ";

		        	}else{
		        		 $cari="where tb_transaksi.id_kantor ='$id_kantor' and 
		                           		 tb_transaksi.metode_bayar ='$metode_bayar' and
		                 				tb_transaksi.date BETWEEN '$tgl_awal' AND '$tgl_akhir' AND tb_transaksi.id_admin= '$id_admin' ";

		        	}	



					$query = $this->db->query("select  * from tb_transaksi $cari  order by tb_transaksi.date desc ");


		        }

        } 
		        
       
        return $query->result();
    }
    function data_transaksi_terakhir($id_admin)
	{
		 
	 	$query = $this->db->query("select  *  from tb_transaksi where id_admin='$id_admin' and  DATE(tb_transaksi.date) =  CURDATE()   order by tb_transaksi.id_transaksi desc limit 1  ");
		return $query->result();
	} 
    public function detail_transaksi($id_transaksi)
	{
		 
		 
	 	$this->db->where('id_transaksi',$id_transaksi);
		 
		$query = $this->db->get('tb_jual_detail');
		return $query->result();
	} 

	function struk($id_transaksi)
	{
		 
		 
	 	$this->db->where('id_transaksi',$id_transaksi);
		 
		$query = $this->db->get('tb_transaksi');
		return $query->result();
	} 

	function jumlah_item($id_transaksi)
	{
		 
		$query = $this->db->query("select  sum(jumlah) as total_belanja from tb_jual_detail where id_transaksi = '$id_transaksi'    ");
		return $query->result();
	} 

	function struk_detail($id_transaksi)
	{
		 
		 
	 	$this->db->where('id_transaksi',$id_transaksi);
		 
		$query = $this->db->get('tb_jual_detail');
		return $query->result();
	} 
	function struk_metode($id_metode)
	{
		 
		 
	 	$this->db->where('id_metode',$id_metode);
		 
		$query = $this->db->get('tb_metode');
		return $query->result();
	} 
	function laporan_kulakan_manual($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin){

		
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select  *  from tb_stok_masuk_manual where date(tb_stok_masuk_manual.tgl_kulakan) = '$tanggal'   order by tb_stok_masuk_manual.tgl_kulakan desc ");
			}else{
				$query = $this->db->query("select  * from tb_stok_masuk_manual where date(tb_stok_masuk_manual.tgl_kulakan) = '$tanggal' and tb_stok_masuk_manual.id_kantor ='$id_kantor_cari' order by tb_stok_masuk_manual.tgl_kulakan desc ");
			}

	        return $query->result();
    } 
    function laporan_barang_laris($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin,$jumlah_cek){

		if($jenis_laporan =='pilihan'){
			if($id_kantor_cari == 'all'){
				 	$this->db->select(' tb_barang.id, tb_barang.nama_barang , SUM(tb_jual_detail.jumlah) as total_terjual');
			        $this->db->order_by('total_terjual', 'desc');
			        $this->db->GROUP_BY('tb_jual_detail.id_barang');
			        $this->db->limit($jumlah_cek);
			        $this->db->where('tb_barang.id = tb_jual_detail.id_barang ');
			        $this->db->where('tb_jual_detail.date >=',$tanggal_awal);
					$this->db->where('tb_jual_detail.date <=',$tanggal_akhir);
			        $query = $this->db->get('tb_barang, tb_jual_detail');
			}else{
				 	 $this->db->select(' tb_barang.id, tb_barang.nama_barang , SUM(tb_jual_detail.jumlah) as total_terjual');
			        $this->db->order_by('total_terjual', 'desc');
			        $this->db->GROUP_BY('tb_jual_detail.id_barang');
			         $this->db->limit($jumlah_cek);
			        $this->db->where('id_admin',$id_admin);
			        $this->db->where('id_kantor',$id_kantor);
			        $this->db->where('tb_barang.id = tb_jual_detail.id_barang ');
			        $this->db->where('tb_jual_detail.date >=',$tanggal_awal);
					$this->db->where('tb_jual_detail.date <=',$tanggal_akhir);
			        
			        $query = $this->db->get('tb_barang, tb_jual_detail');
			}
		
		}

        return $query->result();
    } 
	function laporan($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin){

		if($jenis_laporan =='harian'){
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select  *  from tb_transaksi where date(tb_transaksi.date) = '$tanggal'   order by tb_transaksi.date desc ");
			}else{
				$query = $this->db->query("select  * from tb_transaksi where date(tb_transaksi.date) = '$tanggal' and tb_transaksi.id_kantor ='$id_kantor_cari' order by tb_transaksi.date desc ");
			}

		}else  if($jenis_laporan =='bulanan'){
			if($id_kantor_cari == 'all'){
				 $query = $this->db->query("select  * from tb_transaksi where month(tb_transaksi.date)='$bulan' and year(tb_transaksi.date)='$tahun'  order by tb_transaksi.date desc ");
			}else{
				 $query = $this->db->query("select  * from tb_transaksi where month(tb_transaksi.date)='$bulan' and year(tb_transaksi.date)='$tahun' and tb_transaksi.id_kantor ='$id_kantor_cari' order by tb_transaksi.date desc ");
			}
				
		}else  if($jenis_laporan =='pilihan'){
			if($id_kantor_cari == 'all'){
				 	$query = $this->db->query("select  * from tb_transaksi where  tb_transaksi.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'   order by tb_transaksi.date desc ");
			}else{
				 	$query = $this->db->query("select  * from tb_transaksi  where tb_transaksi.id_kantor ='$id_kantor_cari'and tb_transaksi.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'   order by tb_transaksi.date desc ");
			}
		
		}else  if($jenis_laporan =='kasir'){
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select  *  from tb_transaksi where date(tb_transaksi.date) = '$tanggal' and tb_transaksi.id_admin ='$id_admin'   order by tb_transaksi.date desc ");
			}else{
				$query = $this->db->query("select  * from tb_transaksi where date(tb_transaksi.date) = '$tanggal' and tb_transaksi.id_kantor ='$id_kantor_cari' and tb_transaksi.id_admin ='$id_admin' order by tb_transaksi.date desc ");
			}
		
		}

        return $query->result();
    } 

    function laporan_rincian($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin){

		if($jenis_laporan =='kasir'){
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select  *  from tb_jual_detail where date(tb_jual_detail.date) = '$tanggal' and tb_jual_detail.id_admin ='$id_admin'   order by tb_jual_detail.id_transaksi asc ");
			}else{
				$query = $this->db->query("select  * from tb_jual_detail where date(tb_jual_detail.date) = '$tanggal' and tb_jual_detail.id_kantor ='$id_kantor_cari' and tb_jual_detail.id_admin ='$id_admin' order by tb_jual_detail.id_transaksi asc ");
			}
		}else{
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select  *  from tb_jual_detail where date(tb_jual_detail.date) = '$tanggal'  order by tb_jual_detail.id_transaksi asc ");
			}else{
				$query = $this->db->query("select  * from tb_jual_detail where date(tb_jual_detail.date) = '$tanggal' and tb_jual_detail.id_kantor ='$id_kantor_cari' order by tb_jual_detail.id_transaksi asc ");
			}
		}
        return $query->result();
    }
    function laporan_custom($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin,$grup,$urutan,$pilihan){

    	

    	if($id_kantor_cari =='all'){
    		$kantor = "";
    	}else  {
    		$kantor = "AND tb_jual_detail.id_kantor = '$id_kantor_cari'";
    	}
 
		
		if($pilihan =='semua'){

			if($urutan =='jumlah'){
	    		$sort = "ORDER BY jumlah_transaksi desc";
	    	}else if($urutan =='tanggal'){
	    		$sort = "ORDER BY tanggal_transaksi asc";
	    	}
    	
    		$query = $this->db->query("SELECT * , tb_jual_detail.date AS tanggal_transaksi , tb_jual_detail.jumlah AS jumlah_transaksi,
    			(tb_jual_detail.jumlah * tb_jual_detail.harga_jual) AS total_jual
			,  (tb_jual_detail.jumlah * (tb_jual_detail.harga_jual-tb_jual_detail.harga_beli)) AS 
			total_komisi
					from tb_jual_detail  
					INNER JOIN tb_kantor 
					ON tb_jual_detail.id_kantor = tb_kantor.id_kantor

					INNER JOIN tb_barang 
					ON tb_jual_detail.id_barang = tb_barang.id 

					INNER JOIN tb_katagori
					ON tb_katagori.id_katagori = tb_barang.id_katagori

					WHERE   

					  
					DATE(tb_jual_detail.date) >= '$tanggal_awal' AND DATE(tb_jual_detail.date) <= '$tanggal_akhir' 
		 	
					 
					$kantor 
					$sort
		 	  

			"); 
    	}else if ($pilihan =='grub') {

    		if($urutan =='komisi'){
	    		$sort = "ORDER BY total_komisi desc";
	    	}else if($urutan =='jml'){
	    		$sort = "ORDER BY jumlah_transaksi desc";
	    	}

    		$query = $this->db->query("
			SELECT *, SUM(tb_jual_detail.jumlah * tb_jual_detail.harga_jual) AS total_jual
			,  SUM(tb_jual_detail.jumlah * (tb_jual_detail.harga_jual-tb_jual_detail.harga_beli)) AS 
			total_komisi,  tb_jual_detail.jumlah AS jumlah_transaksi

			from tb_jual_detail  
			INNER JOIN tb_kantor 
			ON tb_jual_detail.id_kantor = tb_kantor.id_kantor

			INNER JOIN tb_barang 
			ON tb_jual_detail.id_barang = tb_barang.id 

			INNER JOIN tb_katagori
			ON tb_katagori.id_katagori = tb_barang.id_katagori

			WHERE  
			DATE(tb_jual_detail.date) >= '$tanggal_awal' AND DATE(tb_jual_detail.date) <= '$tanggal_akhir' 
		 	
		  
		 	$kantor 
		 	
			group by tb_barang.id 

			$sort ");
			
    	}
		
		

        return $query->result();
    } 
 	function laporan_transaksi_jual_katagori($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin,$grup,$urutan,$pilihan,$id_katagori){

 		//// pilih kantor /////
 		if($id_kantor_cari =='all'){
    		$kantor = "";
    	}else  {
    		$kantor = "AND tb_jual_detail.id_kantor = '$id_kantor_cari'";
    	}
    	/// query ///
    		$query = $this->db->query("SELECT * , tb_jual_detail.date AS tanggal_transaksi , tb_jual_detail.jumlah AS jumlah_transaksi,
    			(tb_jual_detail.jumlah * tb_jual_detail.harga_jual) AS total_jual,(tb_jual_detail.jumlah * tb_jual_detail.harga_beli) AS total_beli
			,  (tb_jual_detail.jumlah * (tb_jual_detail.harga_jual-tb_jual_detail.harga_beli)) AS 
			total_komisi
					from tb_jual_detail  
					INNER JOIN tb_kantor 
					ON tb_jual_detail.id_kantor = tb_kantor.id_kantor

					INNER JOIN tb_barang 
					ON tb_jual_detail.id_barang = tb_barang.id 

					INNER JOIN tb_katagori
					ON tb_katagori.id_katagori = tb_barang.id_katagori

					WHERE   
				 
					tb_barang.id_katagori = '$id_katagori' and
					
					 
					 
					DATE(tb_jual_detail.date) >= '$tanggal_awal' AND DATE(tb_jual_detail.date) <= '$tanggal_akhir' 
				 
		 	  		$kantor 

			"); 
    	 
		

        return $query->result();
    } 
 	function laporan_transaksi_jual_katagori_total($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin,$grup,$urutan,$pilihan,$id_katagori){

 		//// pilih kantor /////
 		if($id_kantor_cari =='all'){
    		$kantor = "";
    	}else  {
    		$kantor = "AND tb_jual_detail.id_kantor = '$id_kantor_cari'";
    	}
    	/// query ///
    		$query = $this->db->query("SELECT (SUM( tb_jual_detail.jumlah * tb_jual_detail.harga_jual)) AS totaljum_jual,
(SUM( tb_jual_detail.jumlah * tb_jual_detail.harga_beli)) AS totaljum_beli 
					from tb_jual_detail  
					INNER JOIN tb_kantor 
					ON tb_jual_detail.id_kantor = tb_kantor.id_kantor

					INNER JOIN tb_barang 
					ON tb_jual_detail.id_barang = tb_barang.id 

					INNER JOIN tb_katagori
					ON tb_katagori.id_katagori = tb_barang.id_katagori

					WHERE   
				 
					tb_barang.id_katagori = '$id_katagori' and
					 
					DATE(tb_jual_detail.date) >= '$tanggal_awal' AND DATE(tb_jual_detail.date) <= '$tanggal_akhir' 
				 
		 	  		$kantor 

			"); 
    	 
		

        return $query->result();
    } 
 	function laporan_tanggal($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin){

	 
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select  * , (select count(tb_jual_detail.id) AS 
			jumlah FROM tb_jual_detail WHERE 
			tb_jual_detail.id_transaksi = tb_transaksi.id_transaksi) as jumlah_barang from tb_transaksi where date(tb_transaksi.date) = '$tanggal'   order by tb_transaksi.date desc ");
						}else{
							$query = $this->db->query("select  * , (select count(tb_jual_detail.id) AS 
			jumlah FROM tb_jual_detail WHERE 
			tb_jual_detail.id_transaksi = tb_transaksi.id_transaksi) as jumlah_barang from tb_transaksi where date(tb_transaksi.date) = '$tanggal' and tb_transaksi.id_kantor ='$id_kantor_cari' order by tb_transaksi.date desc ");
						}

	 

        return $query->result();
    } 
 	
 	function laporan_retur($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin){

		if($jenis_laporan =='harian'){
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select  *  from tb_transaksi_retur where date(tb_transaksi_retur.date) = '$tanggal'   order by tb_transaksi_retur.date desc ");
			}else{
				$query = $this->db->query("select  * from tb_transaksi_retur where date(tb_transaksi_retur.date) = '$tanggal' and tb_transaksi_retur.id_kantor ='$id_kantor_cari' order by tb_transaksi_retur.date desc ");
			}

		}else  if($jenis_laporan =='bulanan'){
			if($id_kantor_cari == 'all'){
				 $query = $this->db->query("select  * from tb_transaksi_retur where month(tb_transaksi_retur.date)='$bulan' and year(tb_transaksi_retur.date)='$tahun'  order by tb_transaksi_retur.date desc ");
			}else{
				 $query = $this->db->query("select  * from tb_transaksi_retur where month(tb_transaksi_retur.date)='$bulan' and year(tb_transaksi_retur.date)='$tahun' and tb_transaksi_retur.id_kantor ='$id_kantor_cari' order by tb_transaksi_retur.date desc ");
			}
				
		}else  if($jenis_laporan =='pilihan'){
			if($id_kantor_cari == 'all'){
				 	$query = $this->db->query("select  * from tb_transaksi_retur where  tb_transaksi_retur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'   order by tb_transaksi_retur.date desc ");
			}else{
				 	$query = $this->db->query("select  * from tb_transaksi_retur  where tb_transaksi_retur.id_kantor ='$id_kantor_cari'and tb_transaksi_retur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'   order by tb_transaksi_retur.date desc ");
			}
		
		}else  if($jenis_laporan =='kasir'){
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select  *  from tb_transaksi_retur where date(tb_transaksi_retur.date) = '$tanggal' and tb_transaksi_retur.id_admin ='$id_admin'   order by tb_transaksi_retur.date desc ");
			}else{
				$query = $this->db->query("select  * from tb_transaksi_retur where date(tb_transaksi_retur.date) = '$tanggal' and tb_transaksi_retur.id_kantor ='$id_kantor_cari' and tb_transaksi_retur.id_admin ='$id_admin' order by tb_transaksi_retur.date desc ");
			}
		
		}

        return $query->result();
    } 
 
    function jumlah_total($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin){

		if($jenis_laporan =='harian'){
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select sum(tb_transaksi.sub_total) as sub_total, sum(tb_transaksi.total) as total,sum(tb_transaksi.potongan) as potongan, sum(tb_transaksi.total) as total  from tb_transaksi where date(tb_transaksi.date) = '$tanggal'   order by tb_transaksi.date desc ");
			}else{
				$query = $this->db->query("select sum(tb_transaksi.sub_total) as sub_total, sum(tb_transaksi.potongan) as potongan,  sum(tb_transaksi.total) as total ,sum(tb_transaksi.total) as total from tb_transaksi where date(tb_transaksi.date) = '$tanggal' and tb_transaksi.id_kantor ='$id_kantor_cari' order by tb_transaksi.date desc ");
			}

		}else  if($jenis_laporan =='bulanan'){
			if($id_kantor_cari == 'all'){
				 $query = $this->db->query("select sum(tb_transaksi.sub_total) as sub_total, sum(tb_transaksi.total) as total, sum(tb_transaksi.potongan) as potongan, sum(tb_transaksi.total) as total from tb_transaksi where month(tb_transaksi.date)='$bulan' and year(tb_transaksi.date)='$tahun'  order by tb_transaksi.date desc ");
			}else{
				 $query = $this->db->query("select sum(tb_transaksi.sub_total) as sub_total,  sum(tb_transaksi.total) as total ,sum(tb_transaksi.potongan) as potongan,sum(tb_transaksi.total) as total from tb_transaksi where month(tb_transaksi.date)='$bulan' and year(tb_transaksi.date)='$tahun' and tb_transaksi.id_kantor ='$id_kantor_cari' order by tb_transaksi.date desc ");
			}
				
		}else  if($jenis_laporan =='pilihan'){
			if($id_kantor_cari == 'all'){
				 	$query = $this->db->query("select sum(tb_transaksi.sub_total) as sub_total,sum(tb_transaksi.total) as total,  sum(tb_transaksi.total) as total ,sum(tb_transaksi.potongan) as potongan   from tb_transaksi where   tb_transaksi.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'  ");
			}else{
				 	$query = $this->db->query("select sum(tb_transaksi.sub_total) as sub_total, sum(tb_transaksi.total) as total,  sum(tb_transaksi.total) as total ,sum(tb_transaksi.potongan) as potongan   from tb_transaksi where tb_transaksi.id_kantor ='$id_kantor_cari' and tb_transaksi.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir' ");
			}
		
		
		}else  if($jenis_laporan =='kasir'){
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select sum(tb_transaksi.sub_total) as sub_total, sum(tb_transaksi.total) as total,sum(tb_transaksi.potongan) as potongan, sum(tb_transaksi.total) as total  from tb_transaksi where date(tb_transaksi.date) = '$tanggal' and tb_transaksi.id_admin ='$id_admin'   order by tb_transaksi.date desc ");
			}else{
				$query = $this->db->query("select sum(tb_transaksi.sub_total) as sub_total, sum(tb_transaksi.potongan) as potongan,  sum(tb_transaksi.total) as total ,sum(tb_transaksi.total) as total from tb_transaksi where date(tb_transaksi.date) = '$tanggal' and tb_transaksi.id_kantor ='$id_kantor_cari' and tb_transaksi.id_admin ='$id_admin' order by tb_transaksi.date desc ");
			}
		
		}

        return $query->result();
    } 
    function jumlah_total_retur($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,	$tanggal_akhir,$id_admin){

		if($jenis_laporan =='harian'){
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select sum(tb_transaksi_retur.total) as total,sum(tb_transaksi_retur.potongan) as potongan, sum(tb_transaksi_retur.total) as total  from tb_transaksi_retur where date(tb_transaksi_retur.date) = '$tanggal'   order by tb_transaksi_retur.date desc ");
			}else{
				$query = $this->db->query("select sum(tb_transaksi_retur.potongan) as potongan,  sum(tb_transaksi_retur.total) as total ,sum(tb_transaksi_retur.total) as total from tb_transaksi_retur where date(tb_transaksi_retur.date) = '$tanggal' and tb_transaksi_retur.id_kantor ='$id_kantor_cari' order by tb_transaksi_retur.date desc ");
			}

		}else  if($jenis_laporan =='bulanan'){
			if($id_kantor_cari == 'all'){
				 $query = $this->db->query("select sum(tb_transaksi_retur.total) as total, sum(tb_transaksi_retur.potongan) as potongan, sum(tb_transaksi_retur.total) as total from tb_transaksi_retur where month(tb_transaksi_retur.date)='$bulan' and year(tb_transaksi_retur.date)='$tahun'  order by tb_transaksi_retur.date desc ");
			}else{
				 $query = $this->db->query("select   sum(tb_transaksi_retur.total) as total ,sum(tb_transaksi_retur.potongan) as potongan,sum(tb_transaksi_retur.total) as total from tb_transaksi_retur where month(tb_transaksi_retur.date)='$bulan' and year(tb_transaksi_retur.date)='$tahun' and tb_transaksi_retur.id_kantor ='$id_kantor_cari' order by tb_transaksi_retur.date desc ");
			}
				
		}else  if($jenis_laporan =='pilihan'){
			if($id_kantor_cari == 'all'){
				 	$query = $this->db->query("select sum(tb_transaksi_retur.total) as total,  sum(tb_transaksi_retur.total) as total ,sum(tb_transaksi_retur.potongan) as potongan   from tb_transaksi_retur where   tb_transaksi_retur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'  ");
			}else{
				 	$query = $this->db->query("select sum(tb_transaksi_retur.total) as total,  sum(tb_transaksi_retur.total) as total ,sum(tb_transaksi_retur.potongan) as potongan   from tb_transaksi_retur where tb_transaksi_retur.id_kantor ='$id_kantor_cari' and tb_transaksi_retur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir' ");
			}
		
		
		}else  if($jenis_laporan =='kasir'){
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select sum(tb_transaksi_retur.total) as total,sum(tb_transaksi_retur.potongan) as potongan, sum(tb_transaksi_retur.total) as total  from tb_transaksi_retur where date(tb_transaksi_retur.date) = '$tanggal' and tb_transaksi_retur.id_admin ='$id_admin'   order by tb_transaksi_retur.date desc ");
			}else{
				$query = $this->db->query("select sum(tb_transaksi_retur.potongan) as potongan,  sum(tb_transaksi_retur.total) as total ,sum(tb_transaksi_retur.total) as total from tb_transaksi_retur where date(tb_transaksi_retur.date) = '$tanggal' and tb_transaksi_retur.id_kantor ='$id_kantor_cari' and tb_transaksi_retur.id_admin ='$id_admin' order by tb_transaksi_retur.date desc ");
			}
		
		}

        return $query->result();
    }
    
    function dropout($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,$tanggal_akhir){

		if($jenis_laporan =='harian'){
			if($id_kantor_cari == 'all'){
				$query = $this->db->query("select  *  from tb_dropout where date(tb_dropout.date) = '$tanggal'   order by tb_dropout.date desc ");
			}else{
				$query = $this->db->query("select  * from tb_dropout where date(tb_dropout.date) = '$tanggal' and tb_dropout.id_kantor ='$id_kantor_cari' order by tb_dropout.date desc ");
			}

		}else  if($jenis_laporan =='bulanan'){
			if($id_kantor_cari == 'all'){
				 $query = $this->db->query("select  * from tb_dropout where month(tb_dropout.date)='$bulan' and year(tb_dropout.date)='$tahun'  order by tb_dropout.date desc ");
			}else{
				 $query = $this->db->query("select  * from tb_dropout where month(tb_dropout.date)='$bulan' and year(tb_dropout.date)='$tahun' and tb_dropout.id_kantor ='$id_kantor_cari' order by tb_dropout.date desc ");
			}
				
		}else  if($jenis_laporan =='pilihan'){
			if($id_kantor_cari == 'all'){
				 	$query = $this->db->query("select  * from tb_dropout where  tb_dropout.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'   order by tb_dropout.date desc ");
			}else{
				 	$query = $this->db->query("select  * from tb_dropout  where tb_dropout.id_kantor ='$id_kantor_cari' and tb_dropout.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'   order by tb_dropout.date desc ");
			}
		
		}

        return $query->result();
    }
    function faktur($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,	$tanggal_awal,$tanggal_akhir, $status){

		if($jenis_laporan =='harian'){
			if($id_kantor_cari == 'all'){
				if($status =='SEMUA'){
					$query = $this->db->query("select  *  from tb_faktur where date(tb_faktur.date) = '$tanggal'   order by tb_faktur.date desc ");
				}else{
					$query = $this->db->query("select  *  from tb_faktur where date(tb_faktur.date) = '$tanggal'   and tb_faktur.status='$status' order by tb_faktur.date desc ");
				}

				
			}else{
				if($status =='SEMUA'){
					$query = $this->db->query("select  * from tb_faktur where date(tb_faktur.date) = '$tanggal' and tb_faktur.id_kantor ='$id_kantor_cari' order by tb_faktur.date desc ");
				}else{
					$query = $this->db->query("select  * from tb_faktur where date(tb_faktur.date) = '$tanggal' and tb_faktur.id_kantor ='$id_kantor_cari' and tb_faktur.status='$status' order by tb_faktur.date desc ");
				}
				
			}

		}else  if($jenis_laporan =='bulanan'){
			if($id_kantor_cari == 'all'){
				if($status =='SEMUA'){
					$query = $this->db->query("select  * from tb_faktur where month(tb_faktur.date)='$bulan' and year(tb_faktur.date)='$tahun'  order by tb_faktur.date desc ");
				}else{
					$query = $this->db->query("select  * from tb_faktur where month(tb_faktur.date)='$bulan' and year(tb_faktur.date)='$tahun' and tb_faktur.status='$status'  order by tb_faktur.date desc ");
				}
				 
			}else{
				if($status =='SEMUA'){
					$query = $this->db->query("select  * from tb_faktur where month(tb_faktur.date)='$bulan' and year(tb_faktur.date)='$tahun' and tb_faktur.id_kantor ='$id_kantor_cari' order by tb_faktur.date desc ");
				}else{
					$query = $this->db->query("select  * from tb_faktur where month(tb_faktur.date)='$bulan' and year(tb_faktur.date)='$tahun' and tb_faktur.id_kantor ='$id_kantor_cari' and tb_faktur.status='$status'  order by tb_faktur.date desc ");
				}
				 
			}
				
		}else  if($jenis_laporan =='pilihan'){
			if($id_kantor_cari == 'all'){
				if($status =='SEMUA'){
					$query = $this->db->query("select  * from tb_faktur where  tb_faktur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'   order by tb_faktur.date desc ");
				}else{
				 	$query = $this->db->query("select  * from tb_faktur where  tb_faktur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir' and tb_faktur.status='$status'   order by tb_faktur.date desc ");
				}
				 	
			}else{
				if($status =='SEMUA'){
					$query = $this->db->query("select  * from tb_faktur  where tb_faktur.id_kantor ='$id_kantor_cari' and tb_faktur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'   order by tb_faktur.date desc ");
				}else{
					$query = $this->db->query("select  * from tb_faktur  where tb_faktur.id_kantor ='$id_kantor_cari' and tb_faktur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir' and tb_faktur.status='$status'   order by tb_faktur.date desc ");
				}
				 	
			}
		
		}

        return $query->result();
    }
	function jumlah_faktur($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun,$tanggal_awal,	$tanggal_akhir, $status){

		if($jenis_laporan =='harian'){
			if($id_kantor_cari == 'all'){
				if($status =='SEMUA'){
					$query = $this->db->query("select sum(tb_faktur.nominal) as nominal  from tb_faktur where date(tb_faktur.date) = '$tanggal'   order by tb_faktur.date desc ");
				}else{
					$query = $this->db->query("select sum(tb_faktur.nominal) as nominal  from tb_faktur where date(tb_faktur.date) = '$tanggal' and tb_faktur.status='$status'   order by tb_faktur.date desc ");
				}
				
			}else{
				if($status =='SEMUA'){
					$query = $this->db->query("select  sum(tb_faktur.nominal) as nominal from tb_faktur where date(tb_faktur.date) = '$tanggal' and tb_faktur.id_kantor ='$id_kantor_cari' order by tb_faktur.date desc ");
				}else{
					$query = $this->db->query("select  sum(tb_faktur.nominal) as nominal from tb_faktur where date(tb_faktur.date) = '$tanggal' and tb_faktur.id_kantor ='$id_kantor_cari' and tb_faktur.status='$status'  order by tb_faktur.date desc ");
				}
				
			}

		}else  if($jenis_laporan =='bulanan'){
			if($id_kantor_cari == 'all'){
				if($status =='SEMUA'){
					 $query = $this->db->query("select  sum(tb_faktur.nominal) as nominal from tb_faktur where month(tb_faktur.date)='$bulan' and year(tb_faktur.date)='$tahun'  order by tb_faktur.date desc ");
				}else{
					 $query = $this->db->query("select  sum(tb_faktur.nominal) as nominal from tb_faktur where month(tb_faktur.date)='$bulan' and year(tb_faktur.date)='$tahun' and tb_faktur.status='$status'  order by tb_faktur.date desc ");
				}
				
			}else{

				if($status =='SEMUA'){
					$query = $this->db->query("select   sum(tb_faktur.nominal) as nominal from tb_faktur where month(tb_faktur.date)='$bulan' and year(tb_faktur.date)='$tahun' and tb_faktur.id_kantor ='$id_kantor_cari' order by tb_faktur.date desc ");
				}else{
					 $query = $this->db->query("select   sum(tb_faktur.nominal) as nominal from tb_faktur where month(tb_faktur.date)='$bulan' and year(tb_faktur.date)='$tahun' and tb_faktur.id_kantor ='$id_kantor_cari' and tb_faktur.status='$status' order by tb_faktur.date desc ");
				}
				 
			}
				
		}else  if($jenis_laporan =='pilihan'){
			if($id_kantor_cari == 'all'){

				if($status =='SEMUA'){
					$query = $this->db->query(" select sum(tb_faktur.nominal) as nominal   from tb_faktur where   tb_faktur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'  ");
				}else{
					 $query = $this->db->query(" select sum(tb_faktur.nominal) as nominal   from tb_faktur where   tb_faktur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir' and tb_faktur.status='$status' ");
				}


				 	
			}else{

				if($status =='SEMUA'){
					$query = $this->db->query("select  sum(tb_faktur.nominal) as nominal  from tb_faktur where tb_faktur.id_kantor ='$id_kantor_cari' and tb_faktur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir' ");
				}else{
					$query = $this->db->query("select  sum(tb_faktur.nominal) as nominal  from tb_faktur where tb_faktur.id_kantor ='$id_kantor_cari' and tb_faktur.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir' and tb_faktur.status='$status' ");
				}
				 	
			}
		
		}

        return $query->result();
    }  
    function jumlah_total_stokopname($jenis_laporan,$tanggal, $id_kantor_cari,$bulan,$tahun, $tanggal_awal,  $tanggal_akhir,$id_admin){

    if($jenis_laporan =='harian'){
      if($id_kantor_cari == 'all'){
        $query = $this->db->query("select sum(tb_stok_opname_detail.total) as total  from tb_stok_opname_detail where date(tb_stok_opname_detail.date) = '$tanggal'   order by tb_stok_opname_detail.date desc ");
      }else{
        $query = $this->db->query("select sum(tb_stok_opname_detail.total) as total  from tb_stok_opname_detail where date(tb_stok_opname_detail.date) = '$tanggal' and tb_stok_opname_detail.id_kantor ='$id_kantor_cari' order by tb_stok_opname_detail.date desc ");
      }

    }else  if($jenis_laporan =='bulanan'){
      if($id_kantor_cari == 'all'){
         $query = $this->db->query("select sum(tb_stok_opname_detail.total) as total from tb_stok_opname_detail where month(tb_stok_opname_detail.date)='$bulan' and year(tb_stok_opname_detail.date)='$tahun'  order by tb_stok_opname_detail.date desc ");
      }else{
         $query = $this->db->query("select   sum(tb_stok_opname_detail.total) as total  from tb_stok_opname_detail where month(tb_stok_opname_detail.date)='$bulan' and year(tb_stok_opname_detail.date)='$tahun' and tb_stok_opname_detail.id_kantor ='$id_kantor_cari' order by tb_stok_opname_detail.date desc ");
      }
        
    }else  if($jenis_laporan =='pilihan'){
      if($id_kantor_cari == 'all'){
          $query = $this->db->query("select sum(tb_stok_opname_detail.total) as total,     from tb_stok_opname_detail where   tb_stok_opname_detail.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir'  ");
      }else{
          $query = $this->db->query("select sum(tb_stok_opname_detail.total) as total   from tb_stok_opname_detail where tb_stok_opname_detail.id_kantor ='$id_kantor_cari' and tb_stok_opname_detail.date BETWEEN '$tanggal_awal' AND '$tanggal_akhir' ");
      }
    
    
    } 

        return $query->result();
    }
    public function sub_total_pembelian_hemat($id_transaksi)
	{
		$this->db->select_sum('total_hemat');
		
		$this->db->where('id_transaksi',$id_transaksi);
		
		$query = $this->db->get('tb_jual_detail');
		if($query->num_rows()>0)
		{
			return $query->row()->total_hemat;
		}
		else
		{
			return 0;
		}
	}
	  
}