<?php
namespace App\Http\Controllers\hr;

use App\hr\EdpsReceive;
use App\hr\GratuityItem;
use App\hr\PfLoanReceive;
use App\hr\EmployeeGeneralInfo;


use App\hr\ProvidentFundReceive;
use App\hr\SecurityMoneyCollection;
use App\hr\VehicleLoanReceive;
use App\hr\WelfareFundReceive;

use App\Http\Controllers\Controller;

use App\Service\EasyCode;
use App\User;

use Illuminate\Http\Request;

use Illuminate\Support\Facades\Input;

class ReportController extends Controller {
	protected $easycode;
	public $viewFile, $pageSize, $printPage, $rowCount;

	public function __construct() {
		$this->easycode  = new EasyCode;
		$this->viewFile  = '';
		$this->pageSize  = env('pageSize');
		$this->printPage = false;
		$this->rowCount  = 0;
	}

	/* Edps receive report */
	public function edps(Request $request) {
		$data['easycode']         = $this->easycode;
		$data['edpsReceiveModel'] = new EdpsReceive;

		$data['reportName_label'] = 'EDPS Collection Report';
		$data['employeeColShow']  = true;
		$data['totalCol']         = 7;
		$data['tableView']        = 'table';

		$data['filter']             = array();
		$data['result']             = array();
		$data['totalPreAmount']     = 0;
		$data['totalCurrentAmount'] = 0;
		$data['totalAmount']        = 0;

		$this->viewFile = 'hr.report.edps.index';

		if (isset($_GET['print']) && $_GET['print'] == true) {
			$this->viewFile  = 'hr.report.edps.print';
			$this->printPage = true;
		}

		if (isset($_GET['filter'])) {
			$data['filter'] = $_GET['filter'];

			if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
				$data['branch_label'] = 'Branch: '.\App\gnr\GnrBranch::find($data['filter']['branch'])->name;
			}

			if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
				$data['project_label'] = 'Project: '.\App\gnr\GnrProject::find($data['filter']['project'])->name;
			}

			if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
				$data['employee_label']  = 'Employee: '.\App\User::find($data['filter']['user'])->employee->emp_name_english.' - '.\App\User::find($data['filter']['user'])->employee->emp_id;
				$data['employeeColShow'] = false;
				$data['totalCol'] -= 2;
			}

			if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
				$data['duration_label'] = 'Month: '.date('F, Y', strtotime('01-'.$data['filter']['start_month'])).' to '.date('F, Y', strtotime('01-'.$data['filter']['end_month']));
			} else {
				$data['duration_label'] = 'Month: '.date('F, Y').' to '.date('F, Y');
			}

			$data['q'] = $data['edpsReceiveModel']->leftJoin('users', 'hr_edps_receive.users_id_fk', '=', 'users.id')->leftJoin('hr_emp_general_info', 'users.emp_id_fk', '=', 'hr_emp_general_info.id')->where(function ($q) use ($request, $data) {

					$q->where('hr_edps_receive.payment_status', 'Paid');

					if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
						$q->where('users.branchId', $data['filter']['branch']);
					}

					if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
						$q->where('users.project_id_fk', $data['filter']['project']);
					}

					if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
						$q->where('hr_edps_receive.users_id_fk', $data['filter']['user']);
					}

					if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
						$smonth = date('Y-m-01 00:00:00', strtotime('01-'.$data['filter']['start_month']));
						$emonth = date('Y-m-t 23:59:59', strtotime('01-'.$data['filter']['end_month']));

						$data['filter']['start_month'] = date('m-Y', strtotime($smonth));
						$data['filter']['end_month'] = date('m-Y', strtotime($emonth));
						$q->whereRaW('(`hr_edps_receive`.`created_at` BETWEEN ? and ?)', [$smonth, $emonth]);
					}/*else{
				$smonth = date('Y-m-01 00:00:00');
				$emonth = date('Y-m-t 23:59:59');

				$data['filter']['start_month'] = date('m-Y');
				$data['filter']['end_month'] = date('m-Y');
				}*/

				});

			if (isset($data['filter']['branch']) && intval($data['filter']['branch']) == 0) {
				$data['q']->orderby('hr_edps_receive.received_branch_id', 'asc');
			} else if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
				$data['q']->orderby('hr_edps_receive.for_month', 'desc');
			} else {
				$data['q']->orderby('hr_edps_receive.for_month', 'desc');
			}
			$data['q']->orderby('hr_emp_general_info.emp_id', 'asc');

			if ($data['filter']['report_type'] == 'Preodic') {
				$data['q']->select(
					'hr_edps_receive.*',
					'hr_emp_general_info.emp_id as emp_id'
				);
			} else {
				$data['tableView'] = 'table2';
				$data['totalCol']  = 9;
				if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
					$data['totalCol'] -= 2;
				}

				//$data['q']->select('hr_edps_receive.*', 'hr_emp_general_info.emp_id as emp_id');
				$data['q']->selectRaw('hr_edps_receive.*, hr_emp_general_info.emp_id as emp_id, SUM(amount) as total_amount');

				$data['q']->groupby('hr_edps_receive.users_id_fk', 'hr_edps_receive.transaction_date');
			}

			//echo $data['q']->toSql();

			$data['list'] = $data['q']->get();

			if (count($data['list']) > 0):
			$i = 0;

			foreach ($data['list'] as $row) {
				$i++;
				if ($data['filter']['report_type'] == 'Preodic') {
					$data['totalAmount'] += floatval($row->amount);
					$data['result'][] = [
						'serial'          => $i,
						'collection_date' => date('d-m-Y', strtotime($row->created_at)),
						'received_branch' => @$row->receivedBranch->name,
						'month'           => date('F, Y', strtotime($row->for_month)),
						'employee'        => $row->user->employee->emp_name_english,
						'employee_id'     => $row->emp_id,
						'amount'          => $data['easycode']->currencyFormat($row->amount)
					];
				} else {

					$preAmount = $this->preEdpsAmount($row->users_id_fk, $data['filter']);
					$data['totalPreAmount'] += floatval($preAmount);
					$data['totalCurrentAmount'] += floatval($row->total_amount);
					$data['totalAmount'] += floatval($preAmount)+floatval($row->total_amount);
					$data['result'][] = [
						'id'              => $row->id,
						'serial'          => $i,
						'collection_date' => date('d-m-Y', strtotime($row->created_at)),
						'received_branch' => @$row->receivedBranch->name,
						'month'           => date('F, Y', strtotime($row->for_month)),
						'employee'        => $row->user->employee->emp_name_english,
						'employee_id'     => $row->emp_id,
						'pre_amount'      => $data['easycode']->currencyFormat($preAmount),
						'total_amount'    => $data['easycode']->currencyFormat(floatval(@$row->total_amount)),
						'sum_amount'      => $data['easycode']->currencyFormat(floatval(@$preAmount)+floatval(@$row->total_amount)),
					];
				}
			}
			endif;

			$data['totalPreAmount']     = $data['easycode']->currencyFormat($data['totalPreAmount']);
			$data['totalCurrentAmount'] = $data['easycode']->currencyFormat($data['totalCurrentAmount']);
			$data['totalAmount']        = $data['easycode']->currencyFormat($data['totalAmount']);

			//$data['pagination'] = $data['list']->appends(Input::except('page'))->links();
		}
		return view($this->viewFile, ['data' => $data]);
	}

	private function preEdpsAmount($user_id, $filter) {
		$data['edpsReceiveModel'] = new EdpsReceive;

		$data['q'] = $data['edpsReceiveModel']
		->select(\DB::raw('SUM(amount) as total_amount'))
		->where(function ($q) use ($user_id, $filter) {

				$q->where('hr_edps_receive.users_id_fk', $user_id);

				if (@$filter['start_month'] != '' && @$filter['start_month'] != '') {
					$smonth = date('Y-m-01 00:00:00', strtotime('01-'.$filter['start_month']));
				} else {
					$smonth = date('Y-m-01 00:00:00');
				}

				$q->whereRaW('(`hr_edps_receive`.`created_at` < ? )', [$smonth]);

			});

		return $data['q']->first()->total_amount;
	}

	/* PF receive report */
	public function pf(Request $request) {
		$data['easycode']       = $this->easycode;
		$data['pfReceiveModel'] = new ProvidentFundReceive;

		$data['reportName_label'] = 'PF Collection Report';
		$data['employeeColShow']  = true;
		$data['totalCol']         = 9;

		$data['filter']      = array();
		$data['result']      = array();
		$data['orgAmount']   = 0;
		$data['empAmount']   = 0;
		$data['totalAmount'] = 0;

		$this->viewFile = 'hr.report.pf.index';

		if (isset($_GET['print']) && $_GET['print'] == true) {
			$this->viewFile  = 'hr.report.pf.print';
			$this->printPage = true;
		}

		if (isset($_GET['filter'])) {
			$data['filter'] = $_GET['filter'];

			if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
				$data['branch_label'] = 'Branch: '.\App\gnr\GnrBranch::find($data['filter']['branch'])->name;
			}

			if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
				$data['project_label'] = 'Project: '.\App\gnr\GnrProject::find($data['filter']['project'])->name;
			}

			if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
				$data['employee_label']  = 'Employee: '.\App\User::find($data['filter']['user'])->employee->emp_name_english.' - '.\App\User::find($data['filter']['user'])->employee->emp_id;
				$data['employeeColShow'] = false;
				$data['totalCol'] -= 2;
			}

			if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
				$data['duration_label'] = 'Month: '.date('F, Y', strtotime('01-'.$data['filter']['start_month'])).' to '.date('F, Y', strtotime('01-'.$data['filter']['end_month']));
			}

			$data['q'] = $data['pfReceiveModel']->select('hr_pf_receive.*', 'hr_emp_general_info.emp_id as emp_id')->leftJoin('users', 'hr_pf_receive.users_id_fk', '=', 'users.id')->leftJoin('hr_emp_general_info', 'users.emp_id_fk', '=', 'hr_emp_general_info.id')->where(function ($q) use ($request, $data) {

					$q->where('hr_pf_receive.payment_status', 'Paid');

					if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
						$q->where('users.branchId', $data['filter']['branch']);
					}

					if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
						$q->where('users.project_id_fk', $data['filter']['project']);
					}

					if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
						$q->where('hr_pf_receive.users_id_fk', $data['filter']['user']);
					}

					if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
						$smonth = date('Y-m-01 00:00:00', strtotime('01-'.$data['filter']['start_month']));
						$emonth = date('Y-m-t 23:59:59', strtotime('01-'.$data['filter']['end_month']));
						$q->whereRaW('`hr_pf_receive`.`created_at` BETWEEN ? and ?', [$smonth, $emonth]);

						$data['filter']['start_month'] = date('m-Y', strtotime($smonth));
						$data['filter']['end_month'] = date('m-Y', strtotime($emonth));
					}

				})
			->orderby('hr_pf_receive.for_month', 'desc')
			->orderby('hr_emp_general_info.emp_id', 'asc');

			if ($this->printPage) {
				$this->rowCount = $data['q']->count();
				$this->pageSize = $this->rowCount;
			}

			$data['list'] = $data['q']->paginate($this->pageSize);

			if (count($data['list']) > 0):
			$i = 0;

			foreach ($data['list'] as $row) {
				$i++;
				$data['orgAmount'] += floatval($row->org_amount);
				$data['empAmount'] += floatval($row->emp_amount);
				$data['totalAmount'] += floatval($row->org_amount)+floatval($row->emp_amount);
				$data['result'][] = [
					'serial'          => $i,
					'collection_date' => date('d-m-Y', strtotime($row->created_at)),
					'received_branch' => @$row->receivedBranch->name,
					'month'           => date('F, Y', strtotime($row->for_month)),
					'employee'        => $row->user->employee->emp_name_english,
					'employee_id'     => $row->emp_id,
					'org_amount'      => $row->org_amount,
					'emp_amount'      => $row->emp_amount,
					'total_amount'    => $data['easycode']->currencyFormat($row->org_amount+$row->emp_amount)
				];
			}
			endif;

			$data['empAmount']   = $data['easycode']->currencyFormat($data['empAmount']);
			$data['orgAmount']   = $data['easycode']->currencyFormat($data['orgAmount']);
			$data['totalAmount'] = $data['easycode']->currencyFormat($data['totalAmount']);

			$data['pagination'] = $data['list']->appends(Input::except('page'))->links();
		}
		return view($this->viewFile, ['data' => $data]);
	}

	/* PF Loan receive report */
	public function pfloan(Request $request) {
		$data['easycode']       = $this->easycode;
		$data['pfReceiveModel'] = new PfLoanReceive;

		$data['reportName_label'] = 'PF Loan Collection Report';
		$data['employeeColShow']  = true;
		$data['tableView']        = 'table';
		$data['totalCol']         = 9;

		$data['filter']           = array();
		$data['result']           = array();
		$data['principalAmount']  = 0;
		$data['interestAmount']   = 0;
		$data['settlementAmount'] = 0;
		$data['totalAmount']      = 0;

		$this->viewFile = 'hr.report.pfloan.index';

		if (isset($_GET['print']) && $_GET['print'] == true) {
			$this->viewFile  = 'hr.report.pfloan.print';
			$this->printPage = true;
		}

		if (isset($_GET['filter'])) {
			$data['filter'] = $_GET['filter'];

			if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
				$data['branch_label'] = 'Branch: '.\App\gnr\GnrBranch::find($data['filter']['branch'])->name;
			}

			if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
				$data['project_label'] = 'Project: '.\App\gnr\GnrProject::find($data['filter']['project'])->name;
			}

			if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
				$data['employee_label']  = 'Employee: '.\App\User::find($data['filter']['user'])->employee->emp_name_english.' - '.\App\User::find($data['filter']['user'])->employee->emp_id;
				$data['employeeColShow'] = false;
				$data['totalCol'] -= 2;
			}

			if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
				$data['duration_label'] = 'Month: '.date('F, Y', strtotime('01-'.$data['filter']['start_month'])).' to '.date('F, Y', strtotime('01-'.$data['filter']['end_month']));
			}

			if ($data['filter']['report_type'] == 'Preodic') {
				$data['q'] = $data['pfReceiveModel']->select('hr_pf_loan_receive.*', 'hr_emp_general_info.emp_id as emp_id')->leftJoin('users', 'hr_pf_loan_receive.users_id_fk', '=', 'users.id')->leftJoin('hr_emp_general_info', 'users.emp_id_fk', '=', 'hr_emp_general_info.id')->where(function ($q) use ($request, $data) {

						$q->where('hr_pf_loan_receive.payment_status', 'Paid');

						if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
							$q->where('users.branchId', $data['filter']['branch']);
						}

						if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
							$q->where('users.project_id_fk', $data['filter']['project']);
						}

						if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
							$q->where('hr_pf_loan_receive.users_id_fk', $data['filter']['user']);
						}

						if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
							$smonth = date('Y-m-01 00:00:00', strtotime('01-'.$data['filter']['start_month']));
							$emonth = date('Y-m-t 23:59:59', strtotime('01-'.$data['filter']['end_month']));
							$q->whereRaW('`hr_pf_loan_receive`.`created_at` BETWEEN ? and ?', [$smonth, $emonth]);

							$data['filter']['start_month'] = date('m-Y', strtotime($smonth));
							$data['filter']['end_month'] = date('m-Y', strtotime($emonth));
						}

					})
				->orderby('hr_pf_loan_receive.for_month', 'desc')
				->orderby('hr_emp_general_info.emp_id', 'asc');
			} else {

				$data['tableView'] = 'table2';
				$data['totalCol']  = 10;

				$data['q'] = $data['pfReceiveModel']
				->select(
					'hr_pf_loan_receive.*',
					'hr_emp_general_info.emp_id as emp_id',
					\DB::raw('SUM(amount) as total_amount')
				)
				->leftJoin('users', 'hr_pf_loan_receive.users_id_fk', '=', 'users.id')
				->leftJoin('hr_emp_general_info', 'users.emp_id_fk', '=', 'hr_emp_general_info.id')
				->where(function ($q) use ($request, $data) {

						if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
							$q->where('users.branchId', $data['filter']['branch']);
						}

						if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
							$q->where('users.project_id_fk', $data['filter']['project']);
						}

						if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
							$q->where('hr_pf_loan_receive.users_id_fk', $data['filter']['user']);
						}

						if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
							$smonth = date('Y-m-01 00:00:00', strtotime('01-'.$data['filter']['start_month']));
							$emonth = date('Y-m-t 23:59:59', strtotime('01-'.$data['filter']['end_month']));
							$q->whereRaW('`hr_pf_loan_receive`.`created_at` BETWEEN ? and ?', [$smonth, $emonth]);

							$data['filter']['start_month'] = date('m-Y', strtotime($smonth));
							$data['filter']['end_month'] = date('m-Y', strtotime($emonth));
						}

					})
				->groupby('hr_pf_loan_receive.transaction_date', 'hr_pf_loan_receive.users_id_fk')
				->orderby('hr_pf_loan_receive.for_month', 'desc')
				->orderby('hr_emp_general_info.emp_id', 'asc');
			}

			if ($this->printPage) {
				$this->rowCount = $data['q']->count();
				$this->pageSize = $this->rowCount;
			}

			$data['list'] = $data['q']->paginate($this->pageSize);

			if (count($data['list']) > 0):
			$i = 0;

			foreach ($data['list'] as $row) {
				$i++;
				$data['principalAmount'] += floatval($row->principal_amount);
				$data['interestAmount'] += floatval($row->interest_amount);
				$data['settlementAmount'] += floatval($row->settlement_fee);
				$data['totalAmount'] += floatval($row->principal_amount)+floatval($row->interest_amount);
				if ($data['filter']['report_type'] == 'Preodic') {
					$data['result'][] = [
						'serial'           => $i,
						'collection_date'  => date('d-m-Y', strtotime($row->created_at)),
						'received_branch'  => @$row->receivedBranch->name,
						'month'            => date('F, Y', strtotime($row->for_month)),
						'employee'         => $row->user->employee->emp_name_english,
						'employee_id'      => $row->emp_id,
						'principal_amount' => $row->principal_amount,
						'interest_amount'  => $row->interest_amount,
						'settlement_fee'   => $row->settlement_fee,
						'total_amount'     => $data['easycode']->currencyFormat($row->principal_amount+$row->interest_amount+$row->settlement_fee)
					];
				} else {
					$data['result'][] = [
						'serial'           => $i,
						'collection_date'  => date('d-m-Y', strtotime($row->created_at)),
						'received_branch'  => @$row->receivedBranch->name,
						'month'            => date('F, Y', strtotime($row->for_month)),
						'employee'         => $row->user->employee->emp_name_english,
						'employee_id'      => $row->emp_id,
						'principal_amount' => $row->principal_amount,
						'interest_amount'  => $row->interest_amount,
						'settlement_fee'   => $row->settlement_fee,
						'total_amount'     => $data['easycode']->currencyFormat($row->principal_amount+$row->interest_amount+$row->settlement_fee),
						'sum_amount'       => $row->total_amount
					];
				}
			}
			endif;

			$data['principalAmount']  = $data['easycode']->currencyFormat($data['principalAmount']);
			$data['interestAmount']   = $data['easycode']->currencyFormat($data['interestAmount']);
			$data['settlementAmount'] = $data['easycode']->currencyFormat($data['settlementAmount']);
			$data['totalAmount']      = $data['easycode']->currencyFormat($data['totalAmount']);

			$data['pagination'] = $data['list']->appends(Input::except('page'))->links();
		}
		return view($this->viewFile, ['data' => $data]);
	}

	/* WF receive report */
	public function wf(Request $request) {
		$data['easycode']       = $this->easycode;
		$data['wfReceiveModel'] = new WelfareFundReceive;

		$data['reportName_label'] = 'WF Collection Report';
		$data['employeeColShow']  = true;
		$data['totalCol']         = 10;

		$data['filter']          = array();
		$data['result']          = array();
		$data['orgAmount']       = 0;
		$data['empContriAmount'] = 0;
		$data['empAmount']       = 0;
		$data['totalAmount']     = 0;

		$this->viewFile = 'hr.report.wf.index';

		if (isset($_GET['print']) && $_GET['print'] == true) {
			$this->viewFile  = 'hr.report.wf.print';
			$this->printPage = true;
		}

		if (isset($_GET['filter'])) {
			$data['filter'] = $_GET['filter'];

			if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
				$data['branch_label'] = 'Branch: '.\App\gnr\GnrBranch::find($data['filter']['branch'])->name;
			}

			if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
				$data['project_label'] = 'Project: '.\App\gnr\GnrProject::find($data['filter']['project'])->name;
			}

			if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
				$data['employee_label']  = 'Employee: '.\App\User::find($data['filter']['user'])->employee->emp_name_english.' - '.\App\User::find($data['filter']['user'])->employee->emp_id;
				$data['employeeColShow'] = false;
				$data['totalCol'] -= 2;
			}

			if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
				$data['duration_label'] = 'Month: '.date('F, Y', strtotime('01-'.$data['filter']['start_month'])).' to '.date('F, Y', strtotime('01-'.$data['filter']['end_month']));
			}

			$data['q'] = $data['wfReceiveModel']->select('hr_wf_receive.*', 'hr_emp_general_info.emp_id as emp_id')->leftJoin('users', 'hr_wf_receive.users_id_fk', '=', 'users.id')->leftJoin('hr_emp_general_info', 'users.emp_id_fk', '=', 'hr_emp_general_info.id')->where(function ($q) use ($request, $data) {

					$q->where('hr_wf_receive.payment_status', 'Paid');

					if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
						$q->where('users.branchId', $data['filter']['branch']);
					}

					if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
						$q->where('users.project_id_fk', $data['filter']['project']);
					}

					if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
						$q->where('hr_wf_receive.users_id_fk', $data['filter']['user']);
					}

					if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
						$smonth = date('Y-m-01 00:00:00', strtotime('01-'.$data['filter']['start_month']));
						$emonth = date('Y-m-t 23:59:59', strtotime('01-'.$data['filter']['end_month']));
						$q->whereRaW('`hr_wf_receive`.`created_at` BETWEEN ? and ?', [$smonth, $emonth]);

						$data['filter']['start_month'] = date('m-Y', strtotime($smonth));
						$data['filter']['end_month'] = date('m-Y', strtotime($emonth));
					}

				})
			->orderby('hr_wf_receive.for_month', 'desc')
			->orderby('hr_emp_general_info.emp_id', 'asc');

			if ($this->printPage) {
				$this->rowCount = $data['q']->count();
				$this->pageSize = $this->rowCount;
			}

			$data['list'] = $data['q']->paginate($this->pageSize);

			if (count($data['list']) > 0):
			$i = 0;

			foreach ($data['list'] as $row) {
				$i++;
				$data['orgAmount'] += floatval($row->org_contri_amount);
				$data['empContriAmount'] += floatval($row->emp_contri_amount);
				$data['empAmount'] += floatval($row->emp_amount);
				$data['totalAmount'] += floatval($row->org_contri_amount)+floatval($row->emp_contri_amount)+floatval($row->emp_amount);
				$data['result'][] = [
					'serial'            => $i,
					'collection_date'   => date('d-m-Y', strtotime($row->created_at)),
					'received_branch'   => @$row->receivedBranch->name,
					'month'             => date('F, Y', strtotime($row->for_month)),
					'employee'          => $row->user->employee->emp_name_english,
					'employee_id'       => $row->emp_id,
					'org_amount'        => $row->org_contri_amount,
					'emp_contri_amount' => $row->emp_contri_amount,
					'emp_amount'        => $row->emp_amount,
					'total_amount'      => $data['easycode']->currencyFormat($row->org_contri_amount+$row->emp_contri_amount+$row->emp_amount)
				];
			}
			endif;

			$data['empAmount']       = $data['easycode']->currencyFormat($data['empAmount']);
			$data['empContriAmount'] = $data['easycode']->currencyFormat($data['empContriAmount']);
			$data['orgAmount']       = $data['easycode']->currencyFormat($data['orgAmount']);
			$data['totalAmount']     = $data['easycode']->currencyFormat($data['totalAmount']);

			$data['pagination'] = $data['list']->appends(Input::except('page'))->links();
		}
		return view($this->viewFile, ['data' => $data]);
	}

	/* Vehicle Loan receive report */
	public function vehicleloan(Request $request) {
		$data['easycode']       = $this->easycode;
		$data['pfReceiveModel'] = new VehicleLoanReceive;

		$data['reportName_label'] = 'Vechicle Loan Collection Report';
		$data['employeeColShow']  = true;
		$data['totalCol']         = 10;

		$data['filter']           = array();
		$data['result']           = array();
		$data['principalAmount']  = 0;
		$data['interestAmount']   = 0;
		$data['settlementAmount'] = 0;
		$data['totalAmount']      = 0;

		$this->viewFile = 'hr.report.vehicleloan.index';

		if (isset($_GET['print']) && $_GET['print'] == true) {
			$this->viewFile  = 'hr.report.vehicleloan.print';
			$this->printPage = true;
		}

		if (isset($_GET['filter'])) {
			$data['filter'] = $_GET['filter'];

			if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
				$data['branch_label'] = 'Branch: '.\App\gnr\GnrBranch::find($data['filter']['branch'])->name;
			}

			if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
				$data['project_label'] = 'Project: '.\App\gnr\GnrProject::find($data['filter']['project'])->name;
			}

			if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
				$data['employee_label']  = 'Employee: '.\App\User::find($data['filter']['user'])->employee->emp_name_english.' - '.\App\User::find($data['filter']['user'])->employee->emp_id;
				$data['employeeColShow'] = false;
				$data['totalCol'] -= 2;
			}

			if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
				$data['duration_label'] = 'Month: '.date('F, Y', strtotime('01-'.$data['filter']['start_month'])).' to '.date('F, Y', strtotime('01-'.$data['filter']['end_month']));
			}

			$data['q'] = $data['pfReceiveModel']->select('hr_vehicle_loan_receive.*', 'hr_emp_general_info.emp_id as emp_id')->leftJoin('users', 'hr_vehicle_loan_receive.users_id_fk', '=', 'users.id')->leftJoin('hr_emp_general_info', 'users.emp_id_fk', '=', 'hr_emp_general_info.id')->where(function ($q) use ($request, $data) {

					$q->where('hr_vehicle_loan_receive.payment_status', 'Paid');

					if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
						$q->where('users.branchId', $data['filter']['branch']);
					}

					if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
						$q->where('users.project_id_fk', $data['filter']['project']);
					}

					if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
						$q->where('hr_vehicle_loan_receive.users_id_fk', $data['filter']['user']);
					}

					if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
						$smonth = date('Y-m-01 00:00:00', strtotime('01-'.$data['filter']['start_month']));
						$emonth = date('Y-m-t 23:59:59', strtotime('01-'.$data['filter']['end_month']));
						$q->whereRaW('`hr_vehicle_loan_receive`.`created_at` BETWEEN ? and ?', [$smonth, $emonth]);

						$data['filter']['start_month'] = date('m-Y', strtotime($smonth));
						$data['filter']['end_month'] = date('m-Y', strtotime($emonth));
					}

				})
			->orderby('hr_vehicle_loan_receive.for_month', 'desc')
			->orderby('hr_emp_general_info.emp_id', 'asc');

			if ($this->printPage) {
				$this->rowCount = $data['q']->count();
				$this->pageSize = $this->rowCount;
			}

			$data['list'] = $data['q']->paginate($this->pageSize);

			if (count($data['list']) > 0):
			$i = 0;

			foreach ($data['list'] as $row) {
				$i++;
				$data['principalAmount'] += floatval($row->principal_amount);
				$data['interestAmount'] += floatval($row->interest_amount);
				$data['settlementAmount'] += floatval($row->settlement_fee);
				$data['totalAmount'] += floatval($row->principal_amount)+floatval($row->interest_amount)+floatval($row->settlement_fee);
				$data['result'][] = [
					'serial'            => $i,
					'collection_date'   => date('d-m-Y', strtotime($row->created_at)),
					'received_branch'   => @$row->receivedBranch->name,
					'month'             => date('F, Y', strtotime($row->for_month)),
					'employee'          => $row->user->employee->emp_name_english,
					'employee_id'       => $row->emp_id,
					'principal_amount'  => $row->principal_amount,
					'interest_amount'   => $row->interest_amount,
					'settlement_amount' => $row->settlement_fee,
					'total_amount'      => $data['easycode']->currencyFormat($row->principal_amount+$row->interest_amount+$row->settlement_fee)
				];
			}
			endif;

			$data['principalAmount']  = $data['easycode']->currencyFormat($data['principalAmount']);
			$data['interestAmount']   = $data['easycode']->currencyFormat($data['interestAmount']);
			$data['settlementAmount'] = $data['easycode']->currencyFormat($data['settlementAmount']);
			$data['totalAmount']      = $data['easycode']->currencyFormat($data['totalAmount']);

			$data['pagination'] = $data['list']->appends(Input::except('page'))->links();
		}
		return view($this->viewFile, ['data' => $data]);
	}

	/* Security money receive report */
	public function securitymoney(Request $request) {
		$data['easycode']       = $this->easycode;
		$data['pfReceiveModel'] = new SecurityMoneyCollection;

		$data['reportName_label'] = 'Security Money Collection Report';
		$data['employeeColShow']  = true;
		$data['totalCol']         = 8;

		$data['filter']      = array();
		$data['result']      = array();
		$data['totalAmount'] = 0;

		$this->viewFile = 'hr.report.securitymoney.index';

		if (isset($_GET['print']) && $_GET['print'] == true) {
			$this->viewFile  = 'hr.report.securitymoney.print';
			$this->printPage = true;
		}

		if (isset($_GET['filter'])) {
			$data['filter'] = $_GET['filter'];

			if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
				$data['branch_label'] = 'Branch: '.\App\gnr\GnrBranch::find($data['filter']['branch'])->name;
			}

			if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
				$data['project_label'] = 'Project: '.\App\gnr\GnrProject::find($data['filter']['project'])->name;
			}

			if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
				$data['employee_label']  = 'Employee: '.\App\User::find($data['filter']['user'])->employee->emp_name_english.' - '.\App\User::find($data['filter']['user'])->employee->emp_id;
				$data['employeeColShow'] = false;
				$data['totalCol'] -= 2;
			}

			if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
				$data['duration_label'] = 'Month: '.date('F, Y', strtotime('01-'.$data['filter']['start_month'])).' to '.date('F, Y', strtotime('01-'.$data['filter']['end_month']));
			}

			$data['q'] = $data['pfReceiveModel']->select('hr_security_money_collection.*', 'hr_emp_general_info.emp_id as emp_id')->leftJoin('users', 'hr_security_money_collection.emp_id_fk', '=', 'users.emp_id_fk')->leftJoin('hr_emp_general_info', 'users.emp_id_fk', '=', 'hr_emp_general_info.id')->where(function ($q) use ($request, $data) {

					$q->where('hr_security_money_collection.payment_status', 'Paid');

					if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
						$q->where('users.branchId', $data['filter']['branch']);
					}

					if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
						$q->where('users.project_id_fk', $data['filter']['project']);
					}

					if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
						$q->where('hr_security_money_collection.emp_id_fk', $data['filter']['user']);
					}

					if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
						$smonth = date('Y-m-01 00:00:00', strtotime('01-'.$data['filter']['start_month']));
						$emonth = date('Y-m-t 23:59:59', strtotime('01-'.$data['filter']['end_month']));
						$q->whereRaW('`hr_security_money_collection`.`created_at` BETWEEN ? and ?', [$smonth, $emonth]);

						$data['filter']['start_month'] = date('m-Y', strtotime($smonth));
						$data['filter']['end_month'] = date('m-Y', strtotime($emonth));
					}

				})
			->orderby('hr_security_money_collection.for_month', 'desc')
			->orderby('hr_emp_general_info.emp_id', 'asc');

			if ($this->printPage) {
				$this->rowCount = $data['q']->count();
				$this->pageSize = $this->rowCount;
			}

			$data['list'] = $data['q']->paginate($this->pageSize);

			if (count($data['list']) > 0):
			$i = 0;

			foreach ($data['list'] as $row) {
				$i++;
				$data['totalAmount'] += floatval($row->amount);
				$data['result'][] = [
					'serial'          => $i,
					'collection_date' => date('d-m-Y', strtotime($row->created_at)),
					'received_branch' => @$row->receivedBranch->name,
					'month'           => date('F, Y', strtotime($row->for_month)),
					'employee'        => $row->employee->emp_name_english,
					'employee_id'     => $row->employee->emp_id,
					'type'            => $row->type,
					'total_amount'    => $data['easycode']->currencyFormat($row->amount)
				];
			}
			endif;

			$data['totalAmount'] = $data['easycode']->currencyFormat($data['totalAmount']);

			$data['pagination'] = $data['list']->appends(Input::except('page'))->links();
		}
		return view($this->viewFile, ['data' => $data]);
	}

	/* Gratuity receive report */
	public function gratuity(Request $request) {
		$data['easycode']      = $this->easycode;
		$data['gratuityModel'] = new GratuityItem;

		$data['reportName_label'] = 'Gratuity Report';
		$data['employeeColShow']  = true;
		$data['totalCol']         = 9;

		$data['filter']      = array();
		$data['result']      = array();
		$data['totalAmount'] = 0;
		$data['netAmount']   = 0;

		$this->viewFile = 'hr.report.gratuity.index';

		if (isset($_GET['print']) && $_GET['print'] == true) {
			$this->viewFile  = 'hr.report.gratuity.print';
			$this->printPage = true;
		}

		if (isset($_GET['filter'])) {
			$data['filter'] = $_GET['filter'];

			if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
				$data['branch_label'] = 'Branch: '.\App\gnr\GnrBranch::find($data['filter']['branch'])->name;
			}

			if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
				$data['project_label'] = 'Project: '.\App\gnr\GnrProject::find($data['filter']['project'])->name;
			}

			if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
				$data['employee_label']  = 'Employee: '.\App\User::find($data['filter']['user'])->employee->emp_name_english.' - '.\App\User::find($data['filter']['user'])->employee->emp_id;
				$data['employeeColShow'] = false;
				$data['totalCol'] -= 2;
			}

			if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
				$data['duration_label'] = 'Month: '.date('F, Y', strtotime('01-'.$data['filter']['start_month'])).' to '.date('F, Y', strtotime('01-'.$data['filter']['end_month']));
			}

			$data['q'] = $data['gratuityModel']->select('hr_gratuity_item.*')
			                                   ->leftJoin('hr_gratuity', 'hr_gratuity_item.gratuity_id_fk', '=', 'hr_gratuity.id')
			                                   ->leftJoin('users', 'users.id', '=', 'hr_gratuity_item.users_id_fk')
			                                   ->where(function ($q) use ($request, $data) {

					if (isset($data['filter']['branch']) && intval($data['filter']['branch']) > 0) {
						$q->where('users.branchId', $data['filter']['branch']);
					}

					if (isset($data['filter']['project']) && intval($data['filter']['project']) > 0) {
						$q->where('users.project_id_fk', $data['filter']['project']);
					}

					if (isset($data['filter']['user']) && intval($data['filter']['user']) > 0) {
						$q->where('hr_gratuity_item.users_id_fk', $data['filter']['user']);
					}

					if (@$data['filter']['start_month'] != '' && @$data['filter']['start_month'] != '') {
						$smonth = date('Y-m-01', strtotime('01-'.$data['filter']['start_month']));
						$emonth = date('Y-m-t', strtotime('01-'.$data['filter']['end_month']));
						$q->whereRaW('`hr_gratuity`.`target_month` BETWEEN ? and ?', [$smonth, $emonth]);

						$data['filter']['start_month'] = date('m-Y', strtotime($smonth));
						$data['filter']['end_month'] = date('m-Y', strtotime($emonth));
					}

				})
			->orderby('hr_gratuity.target_month', 'desc')
			->orderby('hr_gratuity_item.users_id_fk', 'asc');

			if ($this->printPage) {
				$this->rowCount = $data['q']->count();
				$this->pageSize = $this->rowCount;
			}

			$data['list'] = $data['q']->paginate($this->pageSize);

			if (count($data['list']) > 0):
			$i = 0;

			foreach ($data['list'] as $row) {
				$i++;
				$data['netAmount'] += floatval($row->net_amount);
				$data['totalAmount'] += floatval($row->total_amount);
				$data['result'][] = [
					'serial'        => $i,
					'employee_name' => $row->user->employee->emp_name_english,
					'employee_id'   => $row->user->employee->emp_id,
					'branch'        => @$row->branch->name,
					'joining_date'  => date('d-m-Y', strtotime(@$row->user->employee->organization->joining_date)),
					'basic'         => $row->basic_salary,
					'job_duration'  => $row->job_duration_month.' Month(s)',
					'total_amount'  => $data['easycode']->currencyFormat($row->total_amount),
					'net_amount'    => $data['easycode']->currencyFormat($row->net_amount)
				];
			}
			endif;

			$data['totalAmount'] = $data['easycode']->currencyFormat($data['totalAmount']);
			$data['netAmount']   = $data['easycode']->currencyFormat($data['netAmount']);

			$data['pagination'] = $data['list']->appends(Input::except('page'))->links();
		}
		return view($this->viewFile, ['data' => $data]);
	}
}