<?php
defined('BASEPATH') OR exit('No direct script access allowed');

require FCPATH . '/vendor/autoload.php';
use Dompdf\Dompdf;

// Include librari PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Dashboard extends CI_Controller {

	function __construct()
	{
		parent::__construct();

		date_default_timezone_set('Asia/Jakarta');

		$this->load->model('m_data');
		$this->load->library('pdf');

		// cek session yang login, 
		// jika session status tidak sama dengan session telah_login, berarti pengguna belum login
		// maka halaman akan di alihkan kembali ke halaman login.
		if($this->session->userdata('status')!="telah_login"){
			redirect(base_url().'welcome?alert=belum_login');
		}
	}

	public function index()
	{
		// hitung jumlah entitas

		$data['jumlah_project'] = $this->m_data->get_data('project')->num_rows();
		$data['jumlah_kategori'] = $this->m_data->get_data('kategori')->num_rows();
		$data['jumlah_sub'] = $this->m_data->get_data('sub_kategori')->num_rows();
		// hitung jumlah superadmin
		$data['jumlah_user'] = $this->m_data->get_data('user')->num_rows();
		$data['project'] = $this->db->query("SELECT * FROM project  order by project_id desc limit 4")->result();

		$this->load->view('dashboard/v_header');
		$this->load->view('dashboard/v_index',$data);
		$this->load->view('dashboard/v_footer');
	}

	public function dashboard_perolehan()
	{
		$data['provinsis'] = $this->db->select('*')
							 ->from('perolehan as tb1')
							 ->join('master_provinsi as tb2', 'tb1.provinsi_id = tb2.provinsi_id', 'LEFT')
							 ->join('master_kota as tb3', 'tb1.kota_id = tb3.kota_id', 'LEFT')
							 ->group_by('tb1.provinsi_id')
							 ->get()
							 ->result();
		$data['jumlah_provinsi'] = count($data['provinsis']);

		$data['kotas'] = $this->db->select('*')
							 ->from('perolehan as tb1')
							 ->join('master_provinsi as tb2', 'tb1.provinsi_id = tb2.provinsi_id', 'LEFT')
							 ->join('master_kota as tb3', 'tb1.kota_id = tb3.kota_id', 'LEFT')
							 ->group_by('tb1.kota_id')
							 ->get()
							 ->result();
		$data['jumlah_kota'] = count($data['kotas']);
		
		$data['perolehans'] = $this->db->select('*')
							  ->from('perolehan as t1')
							  ->join('master_provinsi as t2', 't1.provinsi_id = t2.provinsi_id', 'LEFT')
							  ->join('master_kota as t3', 't1.kota_id = t3.kota_id', 'LEFT')
							  ->join('master_kelurahan as t4', 't1.kelurahan_id = t4.kelurahan_id', 'LEFT')
							  ->get()
							  ->result();
		$data['jumlah_perolehan'] = count($data['perolehans']);

		$data['jumlah_luas'] = $this->db->query("SELECT SUM(perolehan_luas) AS luas FROM perolehan")->row()->luas;

		$data['progres'] = $this->db->select('*')
							->from('perolehan_progres as t1')
							->join('master_perolehan_status as t6', 't1.perolehan_status_id = t6.perolehan_status_id', 'LEFT')
							->join('master_perolehan_kegiatan as t7', 't1.perolehan_kegiatan_id = t7.perolehan_kegiatan_id', 'LEFT')
							->order_by("tgl_awal", "desc")
							->get()
							->result();

		$this->load->view('template/v_header');
		$this->load->view('perolehan/v_dashboard',$data);
		$this->load->view('template/v_footer');
	}

	public function keluar()
	{
		$this->session->sess_destroy();
		redirect('welcome/?alert=logout');
	}

	public function ganti_password()
	{
		$this->load->view('dashboard/v_header');
		$this->load->view('dashboard/v_ganti_password');
		$this->load->view('dashboard/v_footer');
	}

	public function ganti_password_aksi()
	{

		// form validasi
		$this->form_validation->set_rules('password_lama','Password Lama','required');
		$this->form_validation->set_rules('password_baru','Password Baru','required|min_length[8]');
		$this->form_validation->set_rules('konfirmasi_password','Konfirmasi Password Baru','required|matches[password_baru]');

		// cek validasi
		if($this->form_validation->run() != false){

			// menangkap data dari form
			$password_lama = $this->input->post('password_lama');
			$password_baru = $this->input->post('password_baru');
			$konfirmasi_password = $this->input->post('konfirmasi_password');

			// cek kesesuaian password lama dengan id pengguna yang sedang login dan password lama
			$where = array(
				'superadmin_id' => $this->session->userdata('id'),
				'superadmin_password' => md5($password_lama)
			);
			$cek = $this->m_data->cek_login('superadmin', $where)->num_rows();

			// cek kesesuaikan password lama
			if($cek > 0){

				// update data password superadmin
				$w = array(
					'superadmin_id' => $this->session->userdata('id')
				);
				$data = array(
					'superadmin_password' => md5($password_baru)
				);
				$this->m_data->update_data($where, $data, 'superadmin');

				// alihkan halaman kembali ke halaman ganti password
				redirect('dashboard/ganti_password?alert=sukses');
			}else{
				// alihkan halaman kembali ke halaman ganti password
				redirect('dashboard/ganti_password?alert=gagal');
			}

		}else{
			$this->load->view('dashboard/v_header');
			$this->load->view('dashboard/v_ganti_password');
			$this->load->view('dashboard/v_footer');
		}

	}

	// CRUD KATEGORI
	public function kategori()
	{
		$data['kategori'] = $this->m_data->get_data('kategori')->result();
		$this->load->view('dashboard/v_header');
		$this->load->view('dashboard/v_kategori',$data);
		$this->load->view('dashboard/v_footer');
	}	
	public function kategori_act()
	{				
		$nama = $this->input->post('nama');
		$data = array(			
			'kategori_nama' => $nama
		);

		$this->m_data->insert_data($data,'kategori');
		redirect(base_url().'dashboard/kategori');
	}
	public function kategori_update()
	{
		$id = $this->input->post('id');
		$nama = $this->input->post('nama');
		$where = array(
			'kategori_id' => $id
		);

		$data = array(
			'kategori_nama' => $nama
		);

		$this->m_data->update_data($where, $data,'kategori');
		redirect(base_url().'dashboard/kategori');
	}
	public function kategori_hapus($id)
	{
		$where = array(
			'kategori_id' => $id
		);

		$this->m_data->delete_data($where,'kategori');
		$this->db->query("delete from sub_kategori where kategori='$id'");
		$this->db->query("delete from transaksi where transaksi_kategori='$id'");	
		$this->db->query("delete from realisasi where realisasi_kategori='$id'");		
		redirect(base_url().'dashboard/kategori');
	}
	// END CRUD KATEGORI


	// CRUD SUB KATEGORI
	public function sub()
	{
		$data['sub'] = $this->db->query('select * from sub_kategori, kategori where sub_kategori.kategori=kategori.kategori_id order by kategori asc')->result();
		$this->load->view('dashboard/v_header');
		$this->load->view('dashboard/v_sub_kategori',$data);
		$this->load->view('dashboard/v_footer');
	}
	public function sub_act()
	{				
		$kategori = $this->input->post('kategori');
		$nama = $this->input->post('nama');
		$satuan = $this->input->post('satuan');

		$data = array(			
			'kategori' => $kategori,
			'nama' => $nama,
			'satuan'=>$satuan
		);

		$this->m_data->insert_data($data,'sub_kategori');
		redirect(base_url().'dashboard/sub');
	}
	public function sub_update()
	{
		$id = $this->input->post('id');
		$kategori = $this->input->post('kategori');
		$nama = $this->input->post('nama');
		$satuan = $this->input->post('satuan');

		$where = array(
			'sub_id' => $id
		);

		$data = array(
			'kategori' => $kategori,
			'nama' => $nama,			
			'satuan'=>$satuan
		);

		$this->m_data->update_data($where, $data,'sub_kategori');
		redirect(base_url().'dashboard/sub');
	}
	public function sub_hapus($id)
	{
		$where = array(
			'sub_id' => $id
		);

		$this->m_data->delete_data($where,'sub_kategori');
		$this->db->query("delete from transaksi where transaksi_sub_kategori='$id'");		
		$this->db->query("delete from realisasi where realisasi_sub_kategori='$id'");	
		redirect(base_url().'dashboard/sub');
	}
	// END CRUD KATEGORI

	//AJAX SUB KATEGORI PADA PROSES ADD TRANSAKSI PROJECT
	function get_sub_category(){
		$id = $this->input->post('id',TRUE);
		$data = $this->db->query("SELECT * from sub_kategori WHERE kategori='$id'")->result();
		foreach ($data as $d) {
			?>
			<option value="<?php echo $d->sub_id; ?>"><?php echo $d->nama ?></option>
			<?php
		}
	}

	


	public function profil()
	{
		// id pengguna yang sedang login
		$id_user = $this->session->userdata('id');
		$where = array(
			'user_id' => $id_user
		);

		$data['profil'] = $this->m_data->edit_data($where,'user')->result();

		$this->load->view('dashboard/v_header');
		$this->load->view('dashboard/v_profil',$data);
		$this->load->view('dashboard/v_footer');
	}

	public function profil_update()
	{
		// Wajib isi nama dan email
		$this->form_validation->set_rules('nama','Nama','required');
		$this->form_validation->set_rules('username','Username','required');

		
		
		if($this->form_validation->run() != false){

			$id = $this->session->userdata('id');
			$nama = $this->input->post('nama');
			$username = $this->input->post('username');			

			//cek foto sebelumnya
			$cek = $this->db->query("SELECT * from user where user_id='$id'")->row();
			$foto = $cek->user_foto;
			$path = './gambar/user/';		
			$nama_foto = $_FILES['foto']['name'];

			if($nama_foto==""){
				$where = array(
					'user_id' => $id
				);
				$data = array(
					'user_nama' => $nama,
					'user_username' => $username
				);
				$this->m_data->update_data($where,$data,'user');
				redirect(base_url().'dashboard/profil/?alert=sukses');
			}else{
				unlink($path.$foto);
					// foto
				$config['upload_path']   = './gambar/user/';
				$config['allowed_types'] = 'jpg|png|jpeg';
				$config['max_size'] = '1000';
				$config['remove_spaces'] = TRUE;
				$config['encrypt_name'] = TRUE;
				$this->load->library('upload', $config);

				if($this->upload->do_upload('foto')) {
					$data_upload = $this->upload->data();
					$data_file = $data_upload['file_name'];
					$where = array(
						'user_id' => $id
					);
					$data = array(
						'user_nama' => $nama,
						'user_username' => $email,
						'user_foto' => $data_file
					);

					$this->m_data->update_data($where,$data,'user');

					redirect(base_url().'dashboard/profil/?alert=sukses');
				}else{					
					$id_user = $this->session->userdata('id');
					$where = array(
						'user_id' => $id_user
					);

					$data['profil'] = $this->m_data->edit_data($where,'user')->result();

					$this->load->view('dashboard/v_header');
					$this->load->view('dashboard/v_profil',$data);
					$this->load->view('dashboard/v_footer');
				}

			}

			
			
		}else{
			// id superadmin yang sedang login
			$id_superadmin = $this->session->userdata('id');

			$where = array(
				'superadmin_id' => $id_superadmin
			);

			$data['profil'] = $this->m_data->edit_data($where,'superadmin')->result();

			$this->load->view('dashboard/v_header');
			$this->load->view('dashboard/v_profil',$data);
			$this->load->view('dashboard/v_footer');
		}
	}


	public function pengaturan()
	{
		$data['pengaturan'] = $this->m_data->get_data('pengaturan')->result();

		$this->load->view('dashboard/v_header');
		$this->load->view('dashboard/v_pengaturan',$data);
		$this->load->view('dashboard/v_footer');
	}


	public function pengaturan_update()
	{
		// Wajib isi nama dan deskripsi website
		$this->form_validation->set_rules('nama','Nama Website','required');
		$this->form_validation->set_rules('deskripsi','Deskripsi Website','required');
		
		if($this->form_validation->run() != false){

			$nama = $this->input->post('nama');
			$deskripsi = $this->input->post('deskripsi');
			$link_facebook = $this->input->post('link_facebook');
			$link_twitter = $this->input->post('link_twitter');
			$link_instagram = $this->input->post('link_instagram');
			$link_github = $this->input->post('link_github');

			$where = array(

			);

			$data = array(
				'nama' => $nama,
				'deskripsi' => $deskripsi,
				'link_facebook' => $link_facebook,
				'link_twitter' => $link_twitter,
				'link_instagram' => $link_instagram,
				'link_github' => $link_github
			);

			// update pengaturan
			$this->m_data->update_data($where,$data,'pengaturan');

			// Periksa apakah ada gambar logo yang diupload
			if (!empty($_FILES['logo']['name'])){
				
				$config['upload_path']   = './gambar/website/';
				$config['allowed_types'] = 'jpg|png';

				$this->load->library('upload', $config);

				if ($this->upload->do_upload('logo')) {
					// mengambil data tentang gambar logo yang diupload
					$gambar = $this->upload->data();

					$logo = $gambar['file_name'];
					
					$this->db->query("UPDATE pengaturan SET logo='$logo'");
				}
			}

			redirect(base_url().'dashboard/pengaturan/?alert=sukses');

		}else{
			$data['pengaturan'] = $this->m_data->get_data('pengaturan')->result();

			$this->load->view('dashboard/v_header');
			$this->load->view('dashboard/v_pengaturan',$data);
			$this->load->view('dashboard/v_footer');
		}
	}



	// CRUD user
	public function user()
	{
		$data['user'] = $this->db->query("SELECT * FROM user")->result();
		$this->load->view('template/v_header');
		$this->load->view('dashboard/v_user',$data);
		$this->load->view('template/v_footer');
	}
	public function user_tambah()
	{		
		$this->load->view('template/v_header');
		$this->load->view('dashboard/v_user_tambah');
		$this->load->view('template/v_footer');
	}
	public function user_act()
	{
		$nama = $this->input->post('nama');
		$username = $this->input->post('username');
		$level = $this->input->post('level');			
		$password = md5($this->input->post('password'));

		$config['upload_path']   = './gambar/user/';
		$config['allowed_types'] = 'jpg|png|jpeg';
		$config['max_size'] = '1000';
		$config['remove_spaces'] = TRUE;
		$config['encrypt_name'] = TRUE;

		$this->load->library('upload', $config);
		$nama_foto = $_FILES['foto']['name'];

		if($nama_foto==""){
			$data = array(
				'user_nama' => $nama,
				'user_username' => $username,
				'user_password' => $password,
				'user_level' => $level					
			);
			$this->m_data->insert_data($data,'user');
			redirect(base_url().'dashboard/user');
		}else{
			if($this->upload->do_upload('foto')) {
				$data_upload = $this->upload->data();
				$data_file = $data_upload['file_name'];


				$data = array(
					'user_nama' => $nama,
					'user_username' => $username,
					'user_password' => $password,
					'user_level' => $level,
					'user_foto'=>$data_file
				);
				$this->m_data->insert_data($data,'user');
				redirect(base_url().'dashboard/user');

			}else{			
				$this->load->view('template/v_header');
				$this->load->view('dashboard/v_user');
				$this->load->view('template/v_footer');
			}
		}
	}
	public function user_update()
	{		
		$id = $this->input->post('id');
		$nama = $this->input->post('nama');
		$username = $this->input->post('username');
		$level = $this->input->post('level');		
		$password = md5($this->input->post('password'));

		if($this->input->post('password') == ""){
			$data = array(
				'user_nama' => $nama,
				'user_username' => $username,
				'user_level' => $level
			);

			$where = array(
				'user_id' => $id
			);
			$this->m_data->update_data($where,$data,'user');
			redirect(base_url().'dashboard/user');
		}else{
			$data = array(
				'user_nama' => $nama,
				'user_username' => $username,
				'user_password' => $password,
				'user_level' => $level
			);

			$where = array(
				'user_id' => $id
			);
			$this->m_data->update_data($where,$data,'user');
			redirect(base_url().'dashboard/user');
		}
	}
	public function user_hapus($id)
	{
		$where = array(
			'user_id' => $id
		);
		$cek = $this->db->query("SELECT * from user where user_id='$id'")->row();
		$foto = $cek->user_foto;
		$path = './gambar/user/';
		unlink($path.$foto);
		$this->m_data->delete_data($where,'user');
		redirect(base_url().'dashboard/user');
	}
	// END CRUD user





	// CRUD PROJECT
	public function project()
	{
		$data['project'] = $this->db->query("SELECT * FROM project order by project_id desc")->result();
		$this->load->view('dashboard/v_header');
		$this->load->view('dashboard/v_project',$data);
		$this->load->view('dashboard/v_footer');
	}
	public function project_act()
	{
		$nama = $this->input->post('nama');
		$lokasi = $this->input->post('lokasi');
		$periode = $this->input->post('periode');		

		$data = array(			
			'project_nama' => $nama,
			'project_lokasi' => $lokasi,
			'project_periode' => $periode			
		);

		$this->m_data->insert_data($data,'project');
		redirect(base_url().'dashboard/project');
	}
	public function project_update()
	{		
		$id = $this->input->post('id');
		$nama = $this->input->post('nama');
		$lokasi = $this->input->post('lokasi');
		$periode = $this->input->post('periode');		

		$data = array(
			'project_nama' => $nama,
			'project_lokasi' => $lokasi,
			'project_periode' => $periode			
		);

		$where = array(
			'project_id' => $id
		);
		$this->m_data->update_data($where,$data,'project');
		redirect(base_url().'dashboard/project');
	}
	public function project_hapus($id)
	{
		$where = array(
			'project_id' => $id
		);
		$this->m_data->delete_data($where,'project');
		$this->db->query("delete from transaksi where transaksi_project='$id'");
		$this->db->query("delete from realisasi where realisasi_project='$id'");
		redirect(base_url().'dashboard/project');
	}

	public function project_detail($id)
	{
		$data['project'] = $this->db->query("SELECT * FROM project where project_id='$id'")->result();
		
		$data['data'] = $this->db->query("SELECT transaksi_kategori, kategori_nama from transaksi, kategori where transaksi_project='$id' and transaksi_kategori=kategori_id group by transaksi_kategori")->result();		
		$this->load->view('dashboard/v_header');
		$this->load->view('dashboard/v_project_detail',$data);
		$this->load->view('dashboard/v_footer');
	}


	
	// END CRUD PROJECT


	// TRANSAKSI PENGELUARAN
	public function transaksi_act()
	{
		$id = $this->input->post('id');		
		$kategori = $this->input->post('kategori');
		$sub_kategori = $this->input->post('sub_kategori');
		$rencana_volume = $this->input->post('rencana_volume');
		$rencana_harga = $this->input->post('rencana_harga');
		$rencana_jumlah = $this->input->post('rencana_jumlah');
		$keterangan = $this->input->post('keterangan');


		$data = array(			
			'transaksi_project' => $id,			
			'transaksi_kategori' => $kategori,
			'transaksi_sub_kategori' => $sub_kategori,
			'transaksi_rencana_volume' => $rencana_volume,
			'transaksi_rencana_harga' => $rencana_harga,
			'transaksi_rencana_jumlah' => $rencana_jumlah,
			'transaksi_keterangan' => $keterangan
		);

		$this->m_data->insert_data($data,'transaksi');
		redirect(base_url().'dashboard/project_detail/'.$id);
	}

	public function transaksi_update()
	{		
		$id_project = $this->input->post('id_project');
		$id_transaksi = $this->input->post('id_transaksi');

		$kategori = $this->input->post('kategori');
		$sub_kategori = $this->input->post('sub_kategori');
		$rencana_volume = $this->input->post('rencana_volume');
		$rencana_harga = $this->input->post('rencana_harga');
		$rencana_jumlah = $this->input->post('rencana_jumlah');
		$keterangan = $this->input->post('keterangan');

		$data = array(
			'transaksi_kategori' => $kategori,
			'transaksi_sub_kategori' => $sub_kategori,
			'transaksi_rencana_volume' => $rencana_volume,
			'transaksi_rencana_harga' => $rencana_harga,
			'transaksi_rencana_jumlah' => $rencana_jumlah,
			'transaksi_keterangan' => $keterangan
		);

		$where = array(
			'transaksi_id' => $id_transaksi
		);
		$this->m_data->update_data($where,$data,'transaksi');
		redirect(base_url().'dashboard/project_detail/'.$id_project);
	}

	public function transaksi_hapus()
	{
		$id_project = $this->input->post('id_project');
		$id_transaksi = $this->input->post('id_transaksi');

		$this->db->query("delete from transaksi where transaksi_id='$id_transaksi'");
		redirect(base_url().'dashboard/project_detail/'.$id_project);
	}

	public function transaksi_realisasi()
	{
		$id_project = $this->input->post('id_project');
		$id_kategori = $this->input->post('id_kategori');
		$id_sub = $this->input->post('id_sub');
		$id_transaksi = $this->input->post('id_transaksi');		
		$realisasi_volume = $this->input->post('realisasi_volume');
		$realisasi_harga = $this->input->post('realisasi_harga');
		$realisasi_jumlah = $this->input->post('realisasi_jumlah');


		$data = array(			
			'realisasi_project' => $id_project,
			'realisasi_kategori' => $id_kategori,
			'realisasi_sub_kategori' => $id_sub,			
			'realisasi_transaksi' => $id_transaksi,						
			'realisasi_volume' => $realisasi_volume,
			'realisasi_harga_satuan' => $realisasi_harga,
			'realisasi_jumlah_harga' => $realisasi_jumlah
		);

		$this->m_data->insert_data($data,'realisasi');
		redirect(base_url().'dashboard/project_detail/'.$id_project);
	}

	function transaksi_cetak($id)
	{
		$data['project'] = $this->db->query("SELECT * FROM project where project_id='$id'")->result();
		$data['data'] = $this->db->query("SELECT transaksi_kategori, kategori_nama from transaksi, kategori where transaksi_project='$id' and transaksi_kategori=kategori_id group by transaksi_kategori")->result();	
		$pdf = $this->load->view("dashboard/v_transaksi_cetak",$data,true);
		//$this->load->view("dashboard/v_transaksi_cetak", $data);

		$dompdf = new Dompdf();
		$dompdf->set_option('isRemoteEnabled', TRUE);
		$dompdf->loadHtml($pdf);
       // (Optional) Setup the paper size and orientation
		$dompdf->setPaper('A4', 'landscape');
       // Render the HTML as PDF
		$dompdf->render();
       // Output the generated PDF to Browser
       //$dompdf->stream();
		$dompdf->stream('my.pdf',array('Attachment'=>0));
	}

	function transaksi_excel($id)
	{
		$spreadsheet = new Spreadsheet();
		$sheet = $spreadsheet->getActiveSheet();

		$style_col = [
			'font' => ['bold' => true],
			'alignment' => [
				'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 
				'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
			],
			'borders' => [
				'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
				'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
				'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
				'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]
			]
		];

		$style_row = [
			'alignment' => [
				'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
			],
			'borders' => [
				'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
				'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
				'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
				'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]
			]
		];


		$sheet->setCellValue('A1', "LAPORAN RENCANA DAN REALISASI BADAN BANK TANAH"); 
		$sheet->mergeCells('A1:G1'); 		
		$sheet->getStyle('A1')->getFont()->setBold(true);     


		$sheet->setCellValue('A3', "URAIAN PEKERJAAN"); 
		$sheet->mergeCells('A3:A4');
		$sheet->setCellValue('B3', "SATUAN");
		$sheet->mergeCells('B3:B4');

		$sheet->setCellValue('C3', "RENCANA BDP");
		$sheet->mergeCells('C3:F3');	
		$sheet->setCellValue('C4', "VOLUME");
		$sheet->setCellValue('D4', "HARGA SATUAN");
		$sheet->setCellValue('E4', "JUMLAH HARGA");
		$sheet->setCellValue('F4', "TOTAL");

		$sheet->setCellValue('G3', "REALISASI BDP");
		$sheet->mergeCells('G3:J3');   
		$sheet->setCellValue('G4', "VOLUME");
		$sheet->setCellValue('H4', "HARGA SATUAN");
		$sheet->setCellValue('I4', "JUMLAH HARGA");
		$sheet->setCellValue('J4', "TOTAL");		

		$sheet->setCellValue('K3', "SISA");   
		$sheet->mergeCells('K3:K4'); 
		$sheet->setCellValue('L3', "KETERANGAN"); 
		$sheet->mergeCells('L3:L4');  


		$sheet->getStyle('A3:A4')->applyFromArray($style_col);
		$sheet->getStyle('B3:B4')->applyFromArray($style_col);
		$sheet->getStyle('C3:C4')->applyFromArray($style_col);
		$sheet->getStyle('D3')->applyFromArray($style_col);	
		$sheet->getStyle('D4')->applyFromArray($style_col);		 		 
		$sheet->getStyle('E3')->applyFromArray($style_col);
		$sheet->getStyle('E4')->applyFromArray($style_col);	
		$sheet->getStyle('F3')->applyFromArray($style_col);
		$sheet->getStyle('F4')->applyFromArray($style_col);		 		 
		$sheet->getStyle('G3')->applyFromArray($style_col);
		$sheet->getStyle('G4')->applyFromArray($style_col);

		$sheet->getStyle('H3')->applyFromArray($style_col);	
		$sheet->getStyle('H4')->applyFromArray($style_col);	 		 
		$sheet->getStyle('I3')->applyFromArray($style_col);
		$sheet->getStyle('I4')->applyFromArray($style_col);
		$sheet->getStyle('J3')->applyFromArray($style_col);
		$sheet->getStyle('J4')->applyFromArray($style_col);		 		 
		$sheet->getStyle('K3:K4')->applyFromArray($style_col);		
		$sheet->getStyle('L3:L4')->applyFromArray($style_col);		


		$idProject=$id;
            // foreach ($project as $p) {

            //     $idProject = $p->project_id;
            // }

		$data = $this->db->query("SELECT transaksi_kategori, kategori_nama from transaksi,kategori where transaksi_project='$id' and transaksi_kategori=kategori_id group by transaksi_kategori")->result();	

		$numrow = 5;
		$numrow2 = 6;

		foreach ($data as $d) {
			// if($numrow == 5){
			// code...
			$idKat = $d->transaksi_kategori;
			$sheet->setCellValue('A'.$numrow, $d->kategori_nama);

			$totx = $this->db->query("select sum(transaksi_rencana_jumlah) as total from transaksi where transaksi_project='$id' and transaksi_kategori='$idKat'")->row();
			$toty = $this->db->query("select sum(realisasi_jumlah_harga) as total from realisasi where realisasi_project='$id' and realisasi_kategori='$idKat'")->row();

			$sheet->setCellValue('F'.$numrow, $totx->total);
			$sheet->setCellValue('J'.$numrow, $toty->total);

			$x = $totx->total;
			$y = $toty->total;
			$hasil = abs($x-$y); 

			$sheet->setCellValue('K'.$numrow, $hasil);




			$numrow++;

			$sub = $this->db->query("select * from transaksi, sub_kategori where transaksi_project='$idProject' and transaksi_kategori='$idKat' and transaksi_sub_kategori=sub_id order by transaksi_kategori")->result();
			foreach ($sub as $s) {
	                // code...
				$idSub = $s->sub_id;
				$id_kategori = $s->transaksi_kategori;
				$id_transaksi = $s->transaksi_id;



				$sheet->setCellValue('A'.$numrow, $s->nama);

				$sheet->setCellValue('B'.$numrow, $s->satuan);
				$sheet->setCellValue('C'.$numrow, $s->transaksi_rencana_volume);
				$sheet->setCellValue('D'.$numrow, $s->transaksi_rencana_harga);
				$sheet->setCellValue('E'.$numrow, $s->transaksi_rencana_jumlah);


				$sheet->setCellValue('L'.$numrow, $s->transaksi_keterangan);


				$real = $this->db->query("select * from realisasi where realisasi_transaksi='$id_transaksi'")->result();
				foreach ($real as $r) {

                          	$sheet->setCellValue('G'.$numrow, $r->realisasi_volume);                      // code...

                          	$sheet->setCellValue('H'.$numrow, $r->realisasi_harga_satuan);
                          	$sheet->setCellValue('I'.$numrow, $r->realisasi_jumlah_harga);
                          	$numrow++;
                          }



                          $numrow++;

                      }





			  // Apply style row yang telah kita buat tadi ke masing-masing baris (isi tabel)
			// $sheet->getStyle('A'.$numrow)->applyFromArray($style_row);
			// $sheet->getStyle('B'.$numrow)->applyFromArray($style_row);
			// $sheet->getStyle('C'.$numrow)->applyFromArray($style_row);
			// $sheet->getStyle('D'.$numrow)->applyFromArray($style_row);
			// $sheet->getStyle('E'.$numrow)->applyFromArray($style_row);

			//$numrow++;


		// }s
                  }



                  $numrow = $numrow-1;
                  $sheet->setCellValue('A'.$numrow, "TOTAL");

                  $cek = $this->db->query("select sum(transaksi_rencana_jumlah) as total_rencana from transaksi where transaksi_project='$idProject'")->row();
                  
                  $sheet->setCellValue('F'.$numrow, $cek->total_rencana);

                  $cek = $this->db->query("select sum(realisasi_jumlah_harga) as total_realisasi from realisasi where realisasi_project='$idProject'")->row();
                  

                  $sheet->setCellValue('J'.$numrow, $cek->total_realisasi);



                  $sheet->getStyle('A3:L'.$numrow)->applyFromArray($style_row);
			// $sheet->getStyle('B'.$numrow)->applyFromArray($style_row);
			// $sheet->getStyle('C'.$numrow)->applyFromArray($style_row);
			// $sheet->getStyle('D'.$numrow)->applyFromArray($style_row);
			// $sheet->getStyle('E'.$numrow)->applyFromArray($style_row);

                  $sheet->getColumnDimension('A')->setWidth(20);
                  $sheet->getColumnDimension('B')->setWidth(13);
                  $sheet->getColumnDimension('C')->setWidth(13);
                  $sheet->getColumnDimension('D')->setWidth(20);
                  $sheet->getColumnDimension('E')->setWidth(20);
                  $sheet->getColumnDimension('F')->setWidth(20);
                  $sheet->getColumnDimension('G')->setWidth(30);

                  $sheet->getColumnDimension('H')->setWidth(20);
                  $sheet->getColumnDimension('I')->setWidth(20);
                  $sheet->getColumnDimension('J')->setWidth(20);
                  $sheet->getColumnDimension('K')->setWidth(20);

                  $sheet->getColumnDimension('L')->setWidth(20);
                  $sheet->getColumnDimension('M')->setWidth(20);

                  $sheet->getDefaultRowDimension()->setRowHeight(-1);    
                  $sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);    
                  $sheet->setTitle("Rencana dan Realisasi");
                  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                  header('Content-Disposition: attachment; filename="LAPORAN.xlsx"'); 
                  header('Cache-Control: max-age=0');
                  $writer = new Xlsx($spreadsheet);
                  $writer->save('php://output');	

              }

              public function transaksi_detail($project, $transaksi)
              {
              	$data['project'] = $this->db->query("select * from project where project_id='$project'")->result();
              	$data['realisasi'] = $this->db->query("SELECT * FROM realisasi where realisasi_transaksi='$transaksi'")->result();
              	$data['data'] = $this->db->query("SELECT * from realisasi, kategori where realisasi_project='$project' and realisasi_transaksi='$transaksi' and realisasi_kategori=kategori_id group by realisasi_kategori")->result();		

              	$this->load->view('dashboard/v_header');
              	$this->load->view('dashboard/v_transaksi_detail',$data);
              	$this->load->view('dashboard/v_footer');
              }

              public function realisasi_hapus()
              {
              	$id_project = $this->input->post('id_project');
              	$id_realisasi = $this->input->post('id_realisasi');
              	$id_transaksi = $this->input->post('id_transaksi');

              	$this->db->query("delete from realisasi where realisasi_id='$id_transaksi'");
              	redirect(base_url().'dashboard/transaksi_detail/'.$id_project."/".$id_transaksi);
              }

              public function realisasi_update()
              {		
              	$id_project = $this->input->post('id_project');
              	$id_realisasi = $this->input->post('id_realisasi');
              	$id_transaksi = $this->input->post('id_transaksi');
              
              	$realisasi_volume = $this->input->post('realisasi_volume');
              	$realisasi_harga = $this->input->post('realisasi_harga');
              	$realisasi_jumlah = $this->input->post('realisasi_jumlah');

              	$data = array(
              		'realisasi_volume' => $realisasi_volume,
              		'realisasi_harga_satuan' => $realisasi_harga,
              		'realisasi_jumlah_harga' => $realisasi_jumlah              		
              	);

              	$where = array(
              		'realisasi_id' => $id_realisasi
              	);
              	$this->m_data->update_data($where,$data,'realisasi');
              	redirect(base_url().'dashboard/transaksi_detail/'.$id_project."/".$id_transaksi);
              }


	// grafik
              public function grafik()
              {		
              	$data['project'] = $this->db->query("SELECT * from project")->result();
              	$this->load->view('dashboard/v_header');
              	$this->load->view('dashboard/v_grafik', $data);
              	$this->load->view('dashboard/v_footer');
              }



          }
