<?php

class Dukut_model extends CI_Model
{


	 
	private $_table = 'transaksi';
	 
  
    public function proses_riwayat_penjualan() {
        // Kosongkan tabel
        $this->db->empty_table('tabel_riwayat_penjualan');

        // Query untuk mendapatkan semua ID produk
        $query = "
            SELECT DISTINCT transaksi.id_master AS id, COUNT(id) AS jml 
            FROM beta.transaksi
            WHERE transaksi.jenis IN ('5822spo', '5822so', '5822pkd', '5822spd')   
            GROUP BY transaksi.id_master
        ";
        //AND id>=300 AND id<=400  AND id>=1800 AND id<=2000
        $listIdProduk = $this->db->query($query);

        $records = array();
        foreach ($listIdProduk->result() as $row) {
            // Query backup untuk setiap produk
            $querybackup = "
                SELECT transaksi.id_master, transaksi.customers_nama, transaksi.id, transaksi.nomer, transaksi.jenis, transaksi.dtime
                FROM beta.transaksi
                WHERE transaksi.jenis IN ('5822spo', '5822so', '5822pkd', '5822spd')
                AND transaksi.id_master = '".$row->id."'
                AND DATE(transaksi.dtime) >= '2024-01-01'
                AND DATE(transaksi.dtime) <= '2024-12-31'
                ORDER BY transaksi.id ASC
            ";
            $records[] = $this->db->query($querybackup);
        }

        // Proses data dan masukkan ke tabel riwayat
        foreach ($records as $record) {
            foreach ($record->result() as $row) {
                $jenis = preg_replace('/[^a-zA-Z]/i', '', $row->jenis); // Mengambil huruf dari jenis
                $this->db->set('id_master', $row->id_master);
                $this->db->set('customers_nama', $row->customers_nama);
                $this->db->set('id_transaksi', $row->id);

                // Cek jenis dan masukkan data sesuai kolom
                if ($jenis === 'spo') {
                    $this->db->set('nomer_spo', $row->nomer);
                    $this->db->set('dtime_spo', $row->dtime);
                } elseif ($jenis === 'so') {
                    $this->db->set('nomer_so', $row->nomer);
                    $this->db->set('dtime_so', $row->dtime);
                } elseif ($jenis === 'pkd') {
                    $this->db->set('nomer_pkd', $row->nomer);
                    $this->db->set('dtime_pkd', $row->dtime);
                } elseif ($jenis === 'spd') {
                    $this->db->set('nomer_spd', $row->nomer);
                    $this->db->set('dtime_spd', $row->dtime);
                }

                            }
                            // Insert data ke tabel
                $this->db->insert('tabel_riwayat_penjualan');

        }
    }
 
    

	public function get_published_count()
	{
// cari semua produk id yang ingin ditampilkan list debet akhirnya
$query = "(SELECT  DISTINCT `transaksi`.id_master as id_master , COUNT(id) AS jml   ";
$query = $query . "FROM   `beta`.`transaksi` ";
$query = $query . "WHERE     transaksi.jenis IN ('5822spo' , '5822so' , '5822pkd', '5822spd')" ;
$query = $query . "GROUP BY `transaksi`.`id_master` ASC ";       
 $query = $query . " )";
 // jalankan query ambil semua produk idnya taruh ke var listIdProduk 
$listIdProduk   = $this->db->query($query);


return $listIdProduk->num_rows();
	}


	public function get_published($limit = null, $offset = null,$date1 = null , $date2 = null)
	{
		if (!$limit && !$offset) {

        // cari semua produk id yang ingin ditampilkan list debet akhirnya
        $query = "(SELECT  DISTINCT `transaksi`.id_master as id , COUNT(id) AS jml  ";
        $query = $query . " FROM   `beta`.`transaksi`  ";
        $query = $query . " WHERE      transaksi.jenis IN ('5822spo' , '5822so' , '5822pkd', '5822spd')  ";//AND id>=1800 AND id<=2000 " ;

        //    $query = $query . " WHERE id_master = '269563' " ;
        $query = $query . " GROUP BY `transaksi`.`id_master` ASC limit 0,20 ";       
         $query = $query . " )";

         // jalankan query ambil semua produk idnya taruh ke var listIdProduk 
        $listIdProduk   = $this->db->query($query);
        
        $records =  array();
		 
        foreach ($listIdProduk->result() as $row) {
           /* untuk mengambil data yang untuk ditampilkan */
			$query = "( select `transaksi`.id_master ,customers_nama,id, nomer,jenis, dtime";
            $query = $query . " from `beta`.`transaksi`";
            $query = $query . " WHERE jenis IN ('5822spo','5822so','5822pkd','5822spd') and id_master = '".$row->id."' and DATE(dtime) >= '".$date1."' and DATE(dtime) <= '".$date2."' ORDER BY `transaksi`.`id` ASC )" ;
                       
           $records[]  = $this->db->query($query)->result();
             
                       
		}


		} else {

	/* percobaan 1 */
// cari semua produk id yang ingin ditampilkan list debet akhirnya selanjutnya
$query = "(SELECT  DISTINCT `transaksi`.id_master as id , COUNT(id) AS jml  ";
$query = $query . " FROM   `beta`.`transaksi`  ";
$query = $query . "WHERE    transaksi.jenis IN ('5822spo' , '5822so' , '5822pkd', '5822spd')  "; // AND id>=1800 AND id<=2000 " ;

//$query = $query . " WHERE id_master = '269563'  " ;
$query = $query . " GROUP BY `transaksi`.`id_master` ASC limit ".$offset.",".$limit." ";       
 $query = $query . " )";
 // jalankan query ambil semua produk idnya taruh ke var listIdProduk 
$listIdProduk   = $this->db->query($query);
$records =  array();
$querybackup = "";
foreach ($listIdProduk->result() as $row) {
   /* untuk mengambil data yang untuk ditampilkan */
	$query = "(select `transaksi`.id_master ,customers_nama,id, nomer,jenis, dtime";
	$query = $query . " from `beta`.`transaksi`  ";
	$query = $query . " WHERE jenis IN ('5822spo','5822so','5822pkd','5822spd') and id_master = '".$row->id."' and DATE(dtime) >= '".$date1."' and DATE(dtime) <= '".$date2."' ORDER BY `transaksi`.`id` ASC )" ;
			   
   $records[]  = $this->db->query($query)->result();
	 
			   
}

 
 		}
 
	//return $this->db->query($query)->result();
    return $records;
}

	

}
 