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

class Report_cash extends CI_Controller {
	protected $table;

	public function __construct() {
        parent::__construct();
        $this->load->helper("array");
        $this->load->helper("az_core");
        az_check_login();
		$this->load->helper('az_headscript_helper');
    }

	public function index($type = ""){
		$this->load->library('AZApp');
		$azapp = $this->azapp;

		$this->load->helper("az_azkasir");

		$data_header['title'] = azlang('REPORT');
		$data_header['subtitle'] = 'LAPORAN KAS';
		$azapp->set_data_header($data_header);

		$datetime1 = $azapp->add_datetime();
		$datetime1->set_id("date_1");
		$datetime1->set_name("date_1");
		$datetime1->set_value(Date("d-m-Y"));
		$datetime1->set_format("DD-MM-YYYY");
		$data['datetime1'] = $datetime1->render();

		$datetime2 = $azapp->add_datetime();
		$datetime2->set_id("date_2");
		$datetime2->set_name("date_2");
		$datetime2->set_value(Date("d-m-Y"));
		$datetime2->set_format("DD-MM-YYYY");
		$data['datetime2'] = $datetime2->render();

		$view = $this->load->view('report_cash/v_report_cash', $data, true);
		$azapp->add_content($view);

		$this->load->helper('az_core');
		$js = az_add_js('report_cash/vjs_report_cash');
		$azapp->add_js($js);


		echo $azapp->render();
	}

	function get_data($return = false) {
		$this->load->helper('az_core');
		$date_1 = $this->input->post('date_1');
		$date_2 = $this->input->post('date_2');

		$date_1 = Date('Y-m-d', strtotime($date_1));
		$date_2 = Date('Y-m-d', strtotime($date_2));

		//trx 
		$this->db->select('code, transaction_date as date, "TRANSAKSI" as description, total_final_price as debet, "-" as credit');
		$this->db->where('date(transaction_date) >=', $date_1);
		$this->db->where('date(transaction_date) <=', $date_2);
		$this->db->where('payment_type', 'cash');
		$this->db->get('transaction_group');
		$q_trx = $this->db->last_query();

		$this->db->select('code, installment_payment_date as date, "TRANSAKSI (PIUTANG)" as description, installment_payment as debet, "-" as credit');
		$this->db->where('date(installment_payment_date) >=', $date_1);
		$this->db->where('date(installment_payment_date) <=', $date_2);
		$this->db->where('payment_type', 'credit');
		$this->db->join('transaction_group', 'credit_payment.idtransaction_group = transaction_group.idtransaction_group');
		$this->db->get('credit_payment');
		$q_trx_credit = $this->db->last_query();

		$this->db->select('"" as code, income_date as date, concat("PEMASUKAN - ", description) as description, total as debet, "-" as credit');
		$this->db->where('date(income_date) >=', $date_1);
		$this->db->where('date(income_date) <=', $date_2);
		$this->db->get('income');
		$q_in = $this->db->last_query();

		$this->db->select('"" as code, expense_date as date, concat("PENGELUARAN - ", description) as description, "-" as debet, total as credit');
		$this->db->where('date(expense_date) >=', $date_1);
		$this->db->where('date(expense_date) <=', $date_2);
		$this->db->get('expense');
		$q_out = $this->db->last_query();

		$this->db->query("
			SELECT code, date, description, debet, credit FROM (".$q_trx.") as t1
			UNION 
			SELECT code, date, description, debet, credit FROM (".$q_trx_credit.") as t1
			UNION 
			SELECT code, date, description, debet, credit FROM (".$q_in.") as t5
			UNION 
			SELECT code, date, description, debet, credit FROM (".$q_out.") as t6
		");

		$query = $this->db->last_query();

		$res = $this->db->query("SELECT * FROM (".$query.") as tx order by date desc");
		$res_balance = $this->db->query("SELECT * FROM (".$query.") as tx order by date");

		$arr_data = array();
		$balance = 0;
		$arr_balance = array();
		foreach ($res_balance->result() as $key => $value) {
			if ($value->debet != '-') {
				$balance  += $value->debet;
			}
			else {
				$balance -= $value->credit;
			}
			$arr_balance[] = $balance;
		}

		$arr_balance = array_reverse($arr_balance);

		foreach ($res->result() as $key => $value) {
			$balance = $arr_balance[$key];

			$the_debet = $value->debet;
			$the_credit = $value->credit;
			if ($the_debet != '-') {
				$the_debet = az_thousand_separator_decimal($the_debet);
			}
			if ($the_credit != '-') {
				$the_credit = az_thousand_separator_decimal($the_credit);
			}

			$arr_data[] = array(
				'code' => $value->code,
				'date' => $value->date,
				'description' => $value->description,
				'debet' => $the_debet,
				'credit' => $the_credit,
				'balance' => az_thousand_separator_decimal($balance)
			);
		}

		$result = array(
			'data' => $arr_data
		);

		if ($return) {
			return $result;
		}
		else {
			echo json_encode($result);
		}

	}

	function export_excel() {
		$data = $this->get_data(true);

		$this->load->library('AZApp');
		$azapp = $this->azapp;
		$azapp->add_phpexcel();
		$phpexcel = PHPExcel_IOFactory::load(APPPATH . "assets/excel/report_cash.xlsx");
		$sheet0 = $phpexcel->setActiveSheetIndex(0);

		$i = 0;
		$start_row = 5;

		$styleArray11 = array(
		  'borders' => array(
			'allborders' => array(
			'style' => PHPExcel_Style_Border::BORDER_THIN
			)
		  )
		);
		
		$sheet0->setCellValue("A2", 'Tanggal Export: '. Date('d-m-Y H:i:s'));
		foreach ($data['data'] as $key => $value) {
			$sheet0->setCellValue("A".($start_row + $i), ($i+1));
			$sheet0->setCellValue("B".($start_row + $i), $value['code']);
			$sheet0->setCellValue("C".($start_row + $i), $value['date']);
			$sheet0->setCellValue("D".($start_row + $i), $value['description']);
			$sheet0->setCellValueExplicit("E".($start_row + $i), $value['debet'], PHPExcel_Cell_DataType::TYPE_STRING);
			$sheet0->setCellValueExplicit("F".($start_row + $i), $value['credit'], PHPExcel_Cell_DataType::TYPE_STRING);
			$sheet0->setCellValueExplicit("G".($start_row + $i), $value['balance'], PHPExcel_Cell_DataType::TYPE_STRING);
			$i++;
		}

		$sheet0->getStyle("A".$start_row.":G".($start_row + $i - 1))->applyFromArray($styleArray11);
		//write file and download
		$filename='Laporan Kas.xls'; 
		header('Content-Type: application/vnd.ms-excel'); 
		header('Content-Disposition: attachment;filename="'.$filename.'"'); 
		header('Cache-Control: max-age=0'); 
		$objWriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');  
		$objWriter->save('php://output');
	}
}