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

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

class Kepala extends CI_Controller {

	function __construct()
	{
		parent::__construct();
		date_default_timezone_set('Asia/Jakarta');
		$this->load->model('m_data');
		if($this->session->userdata('status')!="telah_login"){
			redirect(base_url().'welcome?alert=belum_login');
		}
	}

	public function index()
	{		
		$data['jumlah_sub'] = $this->m_data->get_data('sub_kategori')->num_rows();		
		$data['jumlah_kategori'] = $this->m_data->get_data('kategori')->num_rows();		
		$data['jumlah_user'] = $this->m_data->get_data('user')->num_rows();		
		$data['jumlah_project'] = $this->m_data->get_data('project')->num_rows();

		$data['project'] = $this->db->query("SELECT * FROM project  order by project_id desc limit 4")->result();

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

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

	public function ganti_password()
	{
		$this->load->view('kepala/v_header');
		$this->load->view('kepala/v_ganti_password');
		$this->load->view('kepala/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(
				'user_id' => $this->session->userdata('id'),
				'user_password' => md5($password_lama)
			);
			$cek = $this->m_data->cek_login('user', $where)->num_rows();
			if($cek > 0){				
				$w = array(
					'user_id' => $this->session->userdata('id')
				);
				$data = array(
					'user_password' => md5($password_baru)
				);
				$this->m_data->update_data($where, $data, 'user');

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

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

	}


	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('kepala/v_header');
		$this->load->view('kepala/v_profil',$data);
		$this->load->view('kepala/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().'kepala/profil/?alert=sukses');
			}else{
				unlink($path.$foto);
					// foto
				$config['upload_path']   = './gambar/user/';
				$config['allowed_types'] = 'jpg|png|jpeg';
				$config['max_size'] = '10000';
				$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().'kepala/profil/?alert=sukses');
				}else{
					$id_pengguna = $this->session->userdata('id');
					$where = array(
						'user_id' => $id_pengguna
					);

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

					$this->load->view('kepala/v_header');
					$this->load->view('kepala/v_profil',$data);
					$this->load->view('kepala/v_footer');
				}
			}
			
			
		}else{			
			$id_pengguna = $this->session->userdata('id');
			$where = array(
				'user_id' => $id_pengguna
			);

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

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

// CRUD PROJECT
	public function project()
	{
		$data['project'] = $this->db->query("SELECT * FROM project")->result();
		$this->load->view('kepala/v_header');
		$this->load->view('kepala/v_project',$data);
		$this->load->view('kepala/v_footer');
	}

	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();
		//$data['sub'] = $this->db->query("SELECT * FROM sub_kategori, pengeluaran where sub_id=pengeluaran_sub_kategori and pengeluaran_project='$id' group by pengeluaran_kategori")->result();		
		//$data['pengeluaran']= $this->db->query("SELECT * FROM pengeluaran where pengeluaran_project='$id'")->result();
		$this->load->view('kepala/v_header');
		$this->load->view('kepala/v_project_detail',$data);
		$this->load->view('kepala/v_footer');
	}

	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("kepala/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);		


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

		$numrow = 5;
		$numrow2 = 6;

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

			
		}


		$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');	

	}

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



}
