function index()
{
$show_data = array();
$show_data['error'] = '';
$show_data['success'] = $this->session->userdata('success');
$this->clearmessage();
$date_string = '';
/* Intermediary Commission Report */
if (isset($_POST['intermediary_commision_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'intermediary', 'label' => 'Intermediaries', 'rules' => 'required'), array('field' => 'intermediary_users', 'label' => 'Intermediary Users', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$intermediary_commision_report = array();
$intermediary_commision_report['branch'] = $this->input->post('branch');
$intermediary_commision_report['intermediary'] = $this->input->post('intermediary');
$intermediary_commision_report['intermediary_users'] = $this->input->post('intermediary_users');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$intermediary_commision_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$intermediary_commision_report['to_date'] = $WEndDtObj->format('Y-m-d');
$intermediary_commision_report['paid_status'] = $this->input->post('paid_status');
$withheld_col = $this->input->post('is_withheld');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('intermediary') != '') {
if ($this->input->post('intermediary') != 'All') {
array_push($condition_array, " pod.source_of_business =" . $this->input->post('intermediary'));
} else {
$conditions = '';
}
}
if ($this->input->post('intermediary_users') != '') {
if ($this->input->post('intermediary_users') != 'All') {
if ($this->input->post('intermediary') == 1 || $this->input->post('intermediary') == 2 || $this->input->post('intermediary') == 3) {
array_push($condition_array, " inter.user_type_id =" . $this->input->post('intermediary_users'));
} else {
if ($this->input->post('intermediary') == 4) {
array_push($condition_array, " tia_ud.user_details_id =" . $this->input->post('intermediary_users'));
}
}
} else {
if ($this->input->post('intermediary_users') == 'All') {
// $conditions = 'pod.source_of_business IN (1,2,3)';
array_push($condition_array, " pod.source_of_business IN (1,2,3)");
}
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Debit Cleared Date)";
array_push($condition_array, " paye.created_date BETWEEN '" . $intermediary_commision_report['from_date'] . "' AND '" . $intermediary_commision_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $intermediary_commision_report['from_date'] . "' AND '" . $intermediary_commision_report['to_date'] . "'");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('paid_status') != '') {
if ($this->input->post('paid_status') == 'P') {
array_push($condition_array, " pod.payment_status =2");
} else {
if ($this->input->post('paid_status') == 'N') {
array_push($condition_array, " pod.payment_status !=2");
} else {
$conditions = '';
}
}
}
$result = $this->report_model->get_intermediary_commision_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/intermediary_commission';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'Agency / Broker Commission Payable - ' . $branch . $date_string;
$name = 'Intermediary_Commission_Report';
$show_data['freedate_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_intermediary_commission($result, $folder, $name, $report_heading, $withheld_col);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/intermediary_commission/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* Premium Register General Report */
if (isset($_POST['premium_register_general_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'), array('field' => 'type', 'label' => 'Type'), array('field' => 'payment_status', 'label' => 'Payment status'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$premium_register_general_report = array();
$premium_register_general_report['branch'] = $this->input->post('branch');
$premium_register_general_report['businesslines'] = $this->input->post('businesslines');
$premium_register_general_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$premium_register_general_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$premium_register_general_report['to_date'] = $WEndDtObj->format('Y-m-d');
$premium_register_general_report['type'] = $this->input->post('type');
$withheld_col = $this->input->post('is_withheld');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
if ($this->input->post('intermediary') != '') {
if ($this->input->post('intermediary') != 'All' && $this->input->post('intermediary') != 0) {
array_push($condition_array, " pod.source_of_business =" . $this->input->post('intermediary'));
} else {
$conditions = '';
}
}
if ($this->input->post('intermediary_users') != '') {
if ($this->input->post('intermediary_users') != 'All') {
if ($this->input->post('intermediary') != 0 && $this->input->post('intermediary') != 4) {
array_push($condition_array, " inter.user_type_id =" . $this->input->post('intermediary_users'));
} else {
if ($this->input->post('intermediary') == 4) {
array_push($condition_array, " tia_ud.user_details_id =" . $this->input->post('intermediary_users'));
} else {
if ($this->input->post('intermediary') == 0) {
array_push($condition_array, " kyc.customer_id =" . $this->input->post('intermediary_users'));
}
}
}
}
// else if($this->input->post('intermediary_users')=='All')
// {
// // $conditions = 'pod.source_of_business IN (1,2,3)';
// array_push($condition_array," pod.source_of_business NOT IN (0,4)");
// }
}
if ($this->input->post('customer') != '') {
if ($this->input->post('customer') != 'All') {
array_push($condition_array, " kyc.customer_id =" . $this->input->post('customer'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $premium_register_general_report['from_date'] . "' AND '" . $premium_register_general_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $premium_register_general_report['from_date'] . "' AND '" . $premium_register_general_report['to_date'] . "'");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('type') != '') {
if ($this->input->post('type') != 1) {
array_push($condition_array, " pod.endorsement_type =" . $this->input->post('type'));
} else {
array_push($condition_array, " pod.endorsement_type NOT IN (0,9)");
}
}
if ($this->input->post('payment_status') != '') {
if ($this->input->post('payment_status') != 'All') {
array_push($condition_array, " pod.payment_status =" . $this->input->post('payment_status'));
} else {
$conditions = '';
}
}
$result = $this->report_model->get_premium_register_general_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/premium_register_general';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'Premium Register (General) - ' . $branch . $date_string;
$name = 'Premium_Register_(General)';
$show_data['premium_register_general_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_premium_register($result, $folder, $name, $report_heading, $withheld_col);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/premium_register_general/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* Premium Register DN Report */
if (isset($_POST['premium_register_DN_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'), array('field' => 'type', 'label' => 'Type'), array('field' => 'payment_status', 'label' => 'Payment Status'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$premium_register_general_report = array();
$premium_register_general_report['branch'] = $this->input->post('branch');
$premium_register_general_report['businesslines'] = $this->input->post('businesslines');
$premium_register_general_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$premium_register_general_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$premium_register_general_report['to_date'] = $WEndDtObj->format('Y-m-d');
$premium_register_general_report['type'] = $this->input->post('type');
$withheld_col = $this->input->post('is_withheld');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " pod.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $premium_register_general_report['from_date'] . "' AND '" . $premium_register_general_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $premium_register_general_report['from_date'] . "' AND '" . $premium_register_general_report['to_date'] . "'");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('type') != '') {
if ($this->input->post('type') != 1) {
array_push($condition_array, " pod.endorsement_type =" . $this->input->post('type'));
} else {
array_push($condition_array, " pod.endorsement_type NOT IN (0,9)");
}
}
if ($this->input->post('payment_status') != '') {
if ($this->input->post('payment_status') != 'All') {
array_push($condition_array, " pod.payment_status =" . $this->input->post('payment_status'));
} else {
$conditions = '';
}
}
$result = $this->report_model->get_premium_register_general_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/premium_register_DN';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'Premium Register With DN NO - ' . $branch . $date_string;
$name = 'Premium_Register_With_DN_No';
$show_data['premium_register_general_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_premium_register_DN($result, $folder, $name, $report_heading, $withheld_col);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/premium_register_DN/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* Renewal Notice Report */
if (isset($_POST['renewal_notice_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$renewal_notice_report = array();
$renewal_notice_report['branch'] = $this->input->post('branch');
$renewal_notice_report['businesslines'] = $this->input->post('businesslines');
$renewal_notice_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$renewal_notice_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$renewal_notice_report['to_date'] = $WEndDtObj->format('Y-m-d');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$result = $this->report_model->get_renewal_report_info($condition_array, $conditions);
$endorsement_sum = array();
foreach ($result as $username_policy) {
if (isset($username_policy['policy_id']) && sizeof($username_policy['policy_id']) > 0) {
$endorsement_sum[$username_policy['policy_id']] = $this->report_model->getcount_policy_id($username_policy['policy_id']);
}
}
$folder = 'timesheet/reports/production/renewal_notices_register';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'Production Wise Policies for Renewal - ' . $branch;
$name = 'Production_Wise_Policies_for_Renewal';
$show_data['renewal_notice_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_renewal_register($result, $folder, $name, $report_heading, $endorsement_sum);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/renewal_notices_register/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* GT Transaction Report */
if (isset($_POST['gt_transactions_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'type', 'label' => 'Type'), array('field' => 'payment_status', 'label' => 'Payment status', 'rules' => 'required'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$gt_transactions = array();
$gt_transactions['branch'] = $this->input->post('branch');
$gt_transactions['businesslines'] = $this->input->post('businesslines');
$gt_transactions['product_category'] = $this->input->post('product_category');
$gt_transactions['type'] = $this->input->post('type');
$gt_transactions['payment_status'] = $this->input->post('payment_status');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
if ($this->input->post('type') != '') {
array_push($condition_array, " pod.invoice_type =" . $this->input->post('type'));
}
if ($this->input->post('payment_status') != '') {
if ($this->input->post('payment_status') != 'All') {
array_push($condition_array, " pod.payment_status =" . $this->input->post('payment_status'));
} else {
$conditions = '';
}
}
$result = $this->report_model->get_gt_transaction_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/premium_register_general';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'GT Transactions - ' . $branch;
$name = 'GT_Transactions';
$show_data['gt_transactions'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_premium_register($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/premium_register_general/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* Certificate Utilization Report */
if (isset($_POST['certificate_utilization_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$certificate_utilization_report = array();
$certificate_utilization_report['branch'] = $this->input->post('branch');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$certificate_utilization_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$certificate_utilization_report['to_date'] = $WEndDtObj->format('Y-m-d');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " cm.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
$date_type = "(Policy Date)";
array_push($condition_array, " smi_cert.certficate_from BETWEEN '" . $certificate_utilization_report['from_date'] . "' AND '" . $certificate_utilization_report['to_date'] . "'");
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
$result = $this->report_model->get_certificate_utilization_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/certificate_utilization';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'Certificate Utilization Report - ' . $branch . $date_string;
$name = 'Certificate_Utilization_Report';
$show_data['freedate_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_certificate_utilization($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/certificate_utilization/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
if (isset($_POST['business_aggregate_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$business_aggregate_report = array();
$business_aggregate_report['branch'] = $this->input->post('branch');
$business_aggregate_report['intermediary'] = $this->input->post('intermediary');
$business_aggregate_report['customer_type'] = $this->input->post('customer_type');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$business_aggregate_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$business_aggregate_report['to_date'] = $WEndDtObj->format('Y-m-d');
$condition_array = array();
$conditions = '';
$sub_query_condition = array();
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " br.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('intermediary') != '') {
if ($this->input->post('intermediary') != 'All') {
array_push($condition_array, " pod.source_of_business =" . $this->input->post('intermediary'));
array_push($sub_query_condition, " s.source_of_business =" . $this->input->post('intermediary'));
} else {
$conditions = '';
}
}
if ($this->input->post('customer_type') != '') {
if ($this->input->post('customer_type') != 'All') {
array_push($condition_array, " kyc.customer_type =" . $this->input->post('customer_type'));
array_push($sub_query_condition, " kyc_d.customer_type =" . $this->input->post('customer_type'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $business_aggregate_report['from_date'] . "' AND '" . $business_aggregate_report['to_date'] . "'");
array_push($sub_query_condition, " s.approved_date BETWEEN '" . $business_aggregate_report['from_date'] . "' AND '" . $business_aggregate_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $business_aggregate_report['from_date'] . "' AND '" . $business_aggregate_report['to_date'] . "' ");
array_push($sub_query_condition, " s.from_date_details BETWEEN '" . $business_aggregate_report['from_date'] . "' AND '" . $business_aggregate_report['to_date'] . "'");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
$result = $this->report_model->get_business_aggregate_report_info($condition_array, $conditions, $sub_query_condition);
$folder = 'timesheet/reports/production/business_aggregate';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
$dates = $business_aggregate_report['from_date'] . '_' . $business_aggregate_report['to_date'];
} else {
$dates = 'ALL DATES';
}
if ($this->input->post('date_type') == 'AD') {
$type = 'AD';
} else {
$type = 'PD';
}
$for_premium_general = $dates . ',' . $type;
$report_heading = 'Business Aggregate Report - ' . $branch . $date_string;
$name = 'Business_Aggregate_Report';
$show_data['freedate_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_business_aggregate($result, $folder, $name, $report_heading, $for_premium_general);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/business_aggregate/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* Product Aggregate Report */
if (isset($_POST['product_business_aggregate_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$product_aggregate_report = array();
$product_aggregate_report['branch'] = $this->input->post('branch');
// $product_aggregate_report['businesslines'] = $this->input->post('businesslines');
$product_aggregate_report['product_category'] = $this->input->post('product_category');
$product_aggregate_report['intermediary'] = $this->input->post('intermediary');
$product_aggregate_report['customer_type'] = $this->input->post('customer_type');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$product_aggregate_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$product_aggregate_report['to_date'] = $WEndDtObj->format('Y-m-d');
$condition_array = array();
$conditions = '';
$sub_query_condition = array();
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " br.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
// if(($this->input->post('businesslines')!=''))
// {
// if($this->input->post('businesslines')!='All')
// {
// array_push($condition_array," c.business_line_id =".$this->input->post('businesslines'));
// }
// else
// {
// $conditions = '';
// }
// }
$product_categories = implode(",", $this->input->post('product_category'));
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " pol.product_category_id IN (" . $product_categories . ")");
array_push($sub_query_condition, " t.product_category_id IN (" . $product_categories . ")");
} else {
$conditions = '';
}
}
if ($this->input->post('intermediary') != '') {
if ($this->input->post('intermediary') != 'All') {
array_push($condition_array, " pod.source_of_business =" . $this->input->post('intermediary'));
array_push($sub_query_condition, " s.source_of_business =" . $this->input->post('intermediary'));
} else {
$conditions = '';
}
}
if ($this->input->post('customer_type') != '') {
if ($this->input->post('customer_type') != 'All') {
array_push($condition_array, " kyc.customer_type =" . $this->input->post('customer_type'));
array_push($sub_query_condition, " kyc_d.customer_type =" . $this->input->post('customer_type'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $product_aggregate_report['from_date'] . "' AND '" . $product_aggregate_report['to_date'] . "'");
array_push($sub_query_condition, " s.approved_date BETWEEN '" . $product_aggregate_report['from_date'] . "' AND '" . $product_aggregate_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $product_aggregate_report['from_date'] . "' AND '" . $product_aggregate_report['to_date'] . "' ");
array_push($sub_query_condition, " s.from_date_details BETWEEN '" . $product_aggregate_report['from_date'] . "' AND '" . $product_aggregate_report['to_date'] . "'");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
$result = $this->report_model->get_product_aggregate_report_info($condition_array, $conditions, $sub_query_condition);
if (!file_exists('timesheet/reports/production/product_aggregate')) {
mkdir('timesheet/reports/production/product_aggregate', 0777, true);
}
$folder = 'timesheet/reports/production/product_aggregate';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
$dates = $product_aggregate_report['from_date'] . '_' . $product_aggregate_report['to_date'];
} else {
$dates = 'ALL DATES';
}
if ($this->input->post('date_type') == 'AD') {
$type = 'AD';
} else {
$type = 'PD';
}
$for_premium_general = $dates . ',' . $type;
$report_heading = 'Product Aggregate Report - ' . $branch . $date_string;
$name = 'Product_Aggregate_Report';
$show_data['product_aggregate_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
// $timesheet_result=create_timesheet_product_aggregate($result,$folder,$name,$report_heading,$for_premium_general,$this->input->post('product_category'));
$timesheet_result = create_timesheet_product_aggregate($result, $folder, $name, $report_heading, $for_premium_general);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/product_aggregate/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* ends */
/* Credit Transaction Report */
if (isset($_POST['credit_transaction_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'), array('field' => 'type', 'label' => 'Type'), array('field' => 'payment_status', 'label' => 'Payment status'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$credit_transaction_report = array();
$credit_transaction_report['branch'] = $this->input->post('branch');
$credit_transaction_report['businesslines'] = $this->input->post('businesslines');
$credit_transaction_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$credit_transaction_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$credit_transaction_report['to_date'] = $WEndDtObj->format('Y-m-d');
$credit_transaction_report['type'] = $this->input->post('type');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $credit_transaction_report['from_date'] . "' AND '" . $credit_transaction_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $credit_transaction_report['from_date'] . "' AND '" . $credit_transaction_report['to_date'] . "'");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('type') != '') {
array_push($condition_array, " pod.invoice_type =" . $this->input->post('type'));
}
if ($this->input->post('payment_status') != '') {
if ($this->input->post('payment_status') != 'All') {
array_push($condition_array, " pod.payment_status =" . $this->input->post('payment_status'));
} else {
$conditions = '';
}
}
if (!file_exists('timesheet/reports/production/credit_transaction')) {
mkdir('timesheet/reports/production/credit_transaction', 0777, true);
}
$result = $this->report_model->get_credit_transaction_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/credit_transaction';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'Credit Transaction - ' . $branch . $date_string;
$name = 'Credit_Transaction';
$show_data['credit_transaction_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_premium_register($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/credit_transaction/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* GT User Activities Report */
if (isset($_POST['gt_user_activities_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'payment_status', 'label' => 'Payment status'), array('field' => 'user', 'label' => 'Users'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$gt_user_activities_report = array();
$gt_user_activities_report['branch'] = $this->input->post('branch');
$gt_user_activities_report['businesslines'] = $this->input->post('businesslines');
$gt_user_activities_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$gt_user_activities_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$gt_user_activities_report['to_date'] = $WEndDtObj->format('Y-m-d');
$gt_user_activities_report['payment_status'] = $this->input->post('payment_status');
$gt_user_activities_report['user'] = $this->input->post('user');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
//array_push($condition_array," pod.approved_date BETWEEN '".$gt_user_activities_report['from_date']."' AND '".$gt_user_activities_report['to_date']."'");
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $gt_user_activities_report['from_date'] . "' AND '" . $gt_user_activities_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $gt_user_activities_report['from_date'] . "' AND '" . $gt_user_activities_report['to_date'] . "' ");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('payment_status') != '') {
if ($this->input->post('payment_status') != 'All') {
array_push($condition_array, " pod.payment_status =" . $this->input->post('payment_status'));
} else {
$conditions = '';
}
}
if ($this->input->post('user') != '') {
if ($this->input->post('user') != 'All') {
array_push($condition_array, " pod.created_by =" . $this->input->post('user'));
} else {
$conditions = ' AND tia_ud.department_id=3';
}
}
if (!file_exists('timesheet/reports/production/gt_user_activities')) {
mkdir('timesheet/reports/production/gt_user_activities', 0777, true);
}
$result = $this->report_model->get_gt_user_activities_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/gt_user_activities';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'GT User Activities - ' . $branch . ' - ' . ucwords($result[0]['created_by']) . $date_string;
$name = 'GT_User_Activities';
$show_data['gt_user_activities_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_GT_user_activities($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/gt_user_activities/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* GT Summary User Activities Report */
if (isset($_POST['gt_summary_user_activities_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'user', 'label' => 'Users'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$gt_summary_user_activities_report = array();
$gt_summary_user_activities_report['branch'] = $this->input->post('branch');
$gt_summary_user_activities_report['businesslines'] = $this->input->post('businesslines');
$gt_summary_user_activities_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$gt_summary_user_activities_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$gt_summary_user_activities_report['to_date'] = $WEndDtObj->format('Y-m-d');
//$gt_summary_user_activities_report['payment_status'] = $this->input->post('payment_status');
$gt_summary_user_activities_report['user'] = $this->input->post('user');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
//array_push($condition_array," pod.approved_date BETWEEN '".$gt_summary_user_activities_report['from_date']."' AND '".$gt_summary_user_activities_report['to_date']."'");
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $gt_summary_user_activities_report['from_date'] . "' AND '" . $gt_summary_user_activities_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $gt_summary_user_activities_report['from_date'] . "' AND '" . $gt_summary_user_activities_report['to_date'] . "' ");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
// if(($this->input->post('payment_status')!=''))
// {
// if($this->input->post('payment_status')!='All')
// {
// array_push($condition_array," pod.payment_status =".$this->input->post('payment_status'));
// }
// else
// {
// $conditions = '';
// }
// }
if ($this->input->post('user') != '') {
if ($this->input->post('user') != 'All') {
array_push($condition_array, " pod.created_by =" . $this->input->post('user'));
} else {
$conditions = ' AND tia_ud.department_id=3';
}
}
if (!file_exists('timesheet/reports/production/gt_summary_user_activities')) {
mkdir('timesheet/reports/production/gt_summary_user_activities', 0777, true);
}
$result = $this->report_model->get_gt_user_activities_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/gt_summary_user_activities';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'GT User Activities - ' . $branch . ' - ' . ucwords($result[0]['created_by']) . $date_string;
$name = 'GT_Summary_User_Activities';
$show_data['gt_summary_user_activities_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_GT_summary_user_activities($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/gt_summary_user_activities/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* GT Finance Activities Report */
if (isset($_POST['gt_finance_activities_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'payment_status', 'label' => 'Payment status'), array('field' => 'user', 'label' => 'Users'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$gt_finance_activities_report = array();
$gt_finance_activities_report['branch'] = $this->input->post('branch');
$gt_finance_activities_report['businesslines'] = $this->input->post('businesslines');
$gt_finance_activities_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$gt_finance_activities_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$gt_finance_activities_report['to_date'] = $WEndDtObj->format('Y-m-d');
$gt_finance_activities_report['payment_status'] = $this->input->post('payment_status');
$gt_finance_activities_report['user'] = $this->input->post('user');
$condition_array = array();
$conditions = " AND pod.risk_approve_status=2";
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = " AND pod.risk_approve_status=2";
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = " AND pod.risk_approve_status=2";
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = " AND pod.risk_approve_status=2";
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
//array_push($condition_array," pod.approved_date BETWEEN '".$gt_finance_activities_report['from_date']."' AND '".$gt_finance_activities_report['to_date']."'");
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $gt_finance_activities_report['from_date'] . "' AND '" . $gt_finance_activities_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $gt_finance_activities_report['from_date'] . "' AND '" . $gt_finance_activities_report['to_date'] . "' ");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('payment_status') != '') {
if ($this->input->post('payment_status') != 'All') {
array_push($condition_array, " pod.payment_status =" . $this->input->post('payment_status'));
} else {
$conditions = " AND pod.risk_approve_status=2";
}
}
if ($this->input->post('user') != '') {
if ($this->input->post('user') != 'All') {
array_push($condition_array, " paye.payment_collected_by =" . $this->input->post('user'));
} else {
$conditions = " AND pod.risk_approve_status=2 AND ud.department_id=5";
}
}
if (!file_exists('timesheet/reports/production/gt_finance_activities')) {
mkdir('timesheet/reports/production/gt_finance_activities', 0777, true);
}
$result = $this->report_model->get_gt_user_activities_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/gt_finance_activities';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'GT Finance Activities - ' . $branch . ' - ' . ucwords($result[0]['payment_collected_by']) . $date_string;
$name = 'GT_Finance_Activities';
$show_data['gt_finance_activities_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_GT_finance_activities($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/gt_finance_activities/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* GT Claims Activities Report */
if (isset($_POST['gt_claims_activities_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'user', 'label' => 'Users'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$gt_claims_activities_report = array();
$gt_claims_activities_report['branch'] = $this->input->post('branch');
$gt_claims_activities_report['businesslines'] = $this->input->post('businesslines');
$gt_claims_activities_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$gt_claims_activities_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$gt_claims_activities_report['to_date'] = $WEndDtObj->format('Y-m-d');
//$gt_claims_activities_report['payment_status'] = $this->input->post('payment_status');
$gt_claims_activities_report['user'] = $this->input->post('user');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
//array_push($condition_array," pod.approved_date BETWEEN '".$gt_claims_activities_report['from_date']."' AND '".$gt_claims_activities_report['to_date']."'");
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $gt_claims_activities_report['from_date'] . "' AND '" . $gt_claims_activities_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $gt_claims_activities_report['from_date'] . "' AND '" . $gt_claims_activities_report['to_date'] . "' AND '" . $gt_claims_activities_report['to_date'] . "' ");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
// if(($this->input->post('payment_status')!=''))
// {
// if($this->input->post('payment_status')!='All')
// {
// array_push($condition_array," pod.payment_status =".$this->input->post('payment_status'));
// }
// else
// {
// $conditions = '';
// }
// }
if ($this->input->post('user') != '') {
if ($this->input->post('user') != 'All') {
array_push($condition_array, " tia_ci.intimator_id =" . $this->input->post('user'));
} else {
$conditions = ' AND tia_ud.department_id=4';
}
}
if (!file_exists('timesheet/reports/production/gt_claims_activities')) {
mkdir('timesheet/reports/production/gt_claims_activities', 0777, true);
}
$result = $this->report_model->get_gt_claims_activities_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/gt_claims_activities';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'GT Claims Activities - ' . $branch . ' - ' . ucwords($result[0]['intimated_by']) . $date_string;
$name = 'GT_Claims_Activities';
$show_data['gt_claims_activities_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_GT_claims_activities($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/gt_claims_activities/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* GT late claim report */
if (isset($_POST['gt_late_claims_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'from_date', 'label' => 'From Date', 'rules' => ''), array('field' => 'to_date', 'label' => 'To Date', 'rules' => ''));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$gt_claim_report_info = array();
$WStartDtObj = new DateTime($this->input->post('from_date'));
$gt_claim_report_info['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$gt_claim_report_info['to_date'] = $WEndDtObj->format('Y-m-d');
$condition_array = array();
$conditions = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
array_push($condition_array, " tia_ci.intimation_date BETWEEN '" . $gt_claim_report_info['from_date'] . "' AND '" . $gt_claim_report_info['to_date'] . "'");
}
if (!file_exists('timesheet/reports/production/gt_late_claim')) {
mkdir('timesheet/reports/production/gt_late_claim', 0777, true);
}
$result = $this->report_model->get_gt_claims_activities_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/gt_late_claim';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'GT Late Claims Report';
$name = 'GT_Late_Claim_Report';
$show_data['gt_late_claims_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_GT_claims_activities($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/gt_late_claim/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* GT Acturial Production Report */
if (isset($_POST['gt_acturial_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$gt_acturial_report = array();
$gt_acturial_report['branch'] = $this->input->post('branch');
$gt_acturial_report['businesslines'] = $this->input->post('businesslines');
$gt_acturial_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$gt_acturial_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$gt_acturial_report['to_date'] = $WEndDtObj->format('Y-m-d');
$gt_acturial_report['user'] = $this->input->post('user');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $gt_acturial_report['from_date'] . "' AND '" . $gt_acturial_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $gt_acturial_report['from_date'] . "' AND '" . $gt_acturial_report['to_date'] . "'");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if (!file_exists('timesheet/reports/production/gt_acturial_production')) {
mkdir('timesheet/reports/production/gt_acturial_production', 0777, true);
}
$result = $this->report_model->get_gt_acturial_production($condition_array, $conditions);
$folder = 'timesheet/reports/production/gt_acturial_production';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'GT Acturial - ' . $branch . ' - ' . $result[0]['business_line'] . $date_string;
$name = 'GT_Acturial_Production';
$show_data['gt_acturial_production_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_acturial_production($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/gt_acturial_production/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
if (isset($_POST['customer_production_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'), array('field' => 'type', 'label' => 'Type'), array('field' => 'payment_status', 'label' => 'Payment status'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$premium_register_general_report = array();
$premium_register_general_report['branch'] = $this->input->post('branch');
$premium_register_general_report['businesslines'] = $this->input->post('businesslines');
$premium_register_general_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$premium_register_general_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$premium_register_general_report['to_date'] = $WEndDtObj->format('Y-m-d');
$premium_register_general_report['type'] = $this->input->post('type');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
if ($this->input->post('intermediary') != '') {
if ($this->input->post('intermediary') != 'All' && $this->input->post('intermediary') != 0) {
array_push($condition_array, " pod.source_of_business =" . $this->input->post('intermediary'));
} else {
$conditions = '';
}
}
if ($this->input->post('intermediary_users') != '') {
if ($this->input->post('intermediary_users') != 'All') {
if ($this->input->post('intermediary') != 0 && $this->input->post('intermediary') != 4) {
array_push($condition_array, " inter.user_type_id =" . $this->input->post('intermediary_users'));
} else {
if ($this->input->post('intermediary') == 4) {
array_push($condition_array, " tia_ud.user_details_id =" . $this->input->post('intermediary_users'));
} else {
if ($this->input->post('intermediary') == 0) {
array_push($condition_array, " kyc.customer_id =" . $this->input->post('intermediary_users'));
}
}
}
}
// else if($this->input->post('intermediary_users')=='All')
// {
// // $conditions = 'pod.source_of_business IN (1,2,3)';
// array_push($condition_array," pod.source_of_business NOT IN (0,4)");
// }
}
if ($this->input->post('customer') != '') {
if ($this->input->post('customer') != 'All') {
array_push($condition_array, " kyc.customer_id =" . $this->input->post('customer'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $premium_register_general_report['from_date'] . "' AND '" . $premium_register_general_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $premium_register_general_report['from_date'] . "' AND '" . $premium_register_general_report['to_date'] . "'");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('type') != '') {
array_push($condition_array, " pod.invoice_type =" . $this->input->post('type'));
}
if ($this->input->post('payment_status') != '') {
if ($this->input->post('payment_status') != 'All') {
array_push($condition_array, " pod.payment_status =" . $this->input->post('payment_status'));
} else {
$conditions = '';
}
}
$result = $this->report_model->get_customer_production_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/premium_register_general';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'Customer Production Report - ' . $branch . $date_string;
$name = 'Customer_Production_Report';
$show_data['premium_register_general_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_premium_register($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/premium_register_general/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* customer aggregate report */
if (isset($_POST['customer_business_aggregate_report'])) {
if ($this->input->post('aggregate_report_type') == 'C') {
$customer_name_req = 'required';
$intermediary_req = '';
} else {
$customer_name_req = '';
$intermediary_req = 'required';
}
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business Line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'customer', 'label' => 'Customer', 'rules' => $customer_name_req), array('field' => 'intermediary', 'label' => 'Intermediary', 'rules' => $intermediary_req), array('field' => 'intermediary_users', 'label' => 'Intermediary User', 'rules' => $intermediary_req), array('field' => 'policy_id', 'label' => 'Policy Number', 'rules' => 'required'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
// $customer_business_aggregate_report = array();
// $customer_business_aggregate_report['branch'] = $this->input->post('branch');
// $customer_business_aggregate_report['businesslines'] = $this->input->post('businesslines');
// $customer_business_aggregate_report['product_category'] = $this->input->post('product_category');
// $customer_business_aggregate_report['customer'] = $this->input->post('customer');
// $customer_business_aggregate_report['policy_id'] = $this->input->post('policy_id');
$condition_array = array();
$conditions = '';
$sub_query_condition = array();
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " br.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " bl.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " pol.product_category_id =" . $this->input->post('product_category'));
array_push($sub_query_condition, " t.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
if ($this->input->post('aggregate_report_type') == 'C') {
if ($this->input->post('customer') != '') {
if ($this->input->post('customer') != 'All') {
array_push($condition_array, " pol.kyc_id =" . $this->input->post('customer'));
array_push($sub_query_condition, " t.kyc_id =" . $this->input->post('customer'));
} else {
$conditions = '';
}
}
} else {
if ($this->input->post('intermediary') != '') {
if ($this->input->post('intermediary') != 'All' && $this->input->post('intermediary') != 0) {
array_push($condition_array, " pod.source_of_business =" . $this->input->post('intermediary'));
array_push($sub_query_condition, " s.source_of_business =" . $this->input->post('intermediary'));
} else {
$conditions = '';
}
}
if ($this->input->post('intermediary_users') != '') {
if ($this->input->post('intermediary_users') != 'All') {
if ($this->input->post('intermediary') != 0 && $this->input->post('intermediary') != 4) {
array_push($condition_array, " inter.user_type_id =" . $this->input->post('intermediary_users'));
array_push($sub_query_condition, " interu.user_type_id =" . $this->input->post('intermediary_users'));
} else {
if ($this->input->post('intermediary') == 4) {
array_push($condition_array, " tia_ud.user_details_id =" . $this->input->post('intermediary_users'));
array_push($sub_query_condition, " tia_uds.user_details_id =" . $this->input->post('intermediary_users'));
} else {
if ($this->input->post('intermediary') == 0) {
array_push($condition_array, " kyc.customer_id =" . $this->input->post('intermediary_users'));
array_push($sub_query_condition, " kyc_d.customer_id =" . $this->input->post('intermediary_users'));
}
}
}
} else {
$conditions = '';
}
}
}
if ($this->input->post('policy_id') != '') {
if ($this->input->post('policy_id') != 'All') {
array_push($condition_array, " pod.policy_id =" . $this->input->post('policy_id'));
array_push($sub_query_condition, " s.policy_id =" . $this->input->post('policy_id'));
} else {
$conditions = '';
}
}
if (!file_exists('timesheet/reports/production/customer_business_aggregate_report')) {
mkdir('timesheet/reports/production/customer_business_aggregate_report', 0777, true);
}
$result = $this->report_model->get_customer_business_aggregate_report_info($condition_array, $conditions, $sub_query_condition, $this->input->post('aggregate_report_type'));
$folder = 'timesheet/reports/production/customer_business_aggregate_report';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
if ($this->input->post('aggregate_report_type') == 'C') {
$user_type = 'Customer';
$user = $result[0]['customer_name'];
} else {
if ($this->input->post('intermediary') != 0 && $this->input->post('intermediary') != 4) {
$user_type = 'Intermediary';
$user = $result[0]['agent_name'];
} else {
if ($this->input->post('intermediary') == 4) {
$user_type = 'Intermediary';
$user = $result[0]['employee_name'];
} else {
if ($this->input->post('intermediary') == 0) {
$user_type = 'Intermediary';
$user = $result[0]['customer_name'];
}
}
}
}
$report_heading = $result[0]['category'] . ' : ' . $user_type . ' Business Aggregate Report - ' . $branch . ' - ' . $user;
$name = 'Customer_Business_Aggregate_Report';
$show_data['customer_aggregate'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_business_aggregate($result, $folder, $name, $report_heading, $for_premium_general = '');
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/customer_business_aggregate_report/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* GT Re-insurance Report */
if (isset($_POST['gt_reinsurance_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'reinsurance_category', 'label' => 'Re-insurance Category', 'rules' => 'required'), array('field' => 'reinsurance_subcategory', 'label' => 'Re-insurance Sub Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$gt_reinsurance_report = array();
$gt_reinsurance_report['branch'] = $this->input->post('branch');
$gt_reinsurance_report['businesslines'] = $this->input->post('businesslines');
$gt_reinsurance_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$gt_reinsurance_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$gt_reinsurance_report['to_date'] = $WEndDtObj->format('Y-m-d');
$gt_reinsurance_report['reinsurance_category'] = $this->input->post('reinsurance_category');
$gt_reinsurance_report['reinsurance_subcategory'] = $this->input->post('reinsurance_subcategory');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
//array_push($condition_array," pod.approved_date BETWEEN '".$gt_reinsurance_report['from_date']."' AND '".$gt_reinsurance_report['to_date']."'");
if ($this->input->post('reinsurance_category') == 3) {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $gt_reinsurance_report['from_date'] . "' AND '" . $gt_reinsurance_report['to_date'] . "'");
} else {
if ($this->input->post('reinsurance_category') == 2) {
$date_type = "(Loss Date)";
array_push($condition_array, " tia_ci.incident_date BETWEEN '" . $gt_reinsurance_report['from_date'] . "' AND '" . $gt_reinsurance_report['to_date'] . "' ");
}
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('reinsurance_category') != '') {
if ($this->input->post('reinsurance_category') != 'All') {
array_push($condition_array, " pr.reinsurance_category =" . $this->input->post('reinsurance_category'));
} else {
$conditions = '';
}
}
if ($this->input->post('reinsurance_subcategory') != '') {
if ($this->input->post('reinsurance_subcategory') != 'All') {
array_push($condition_array, " pr.reinsurance_subcategory =" . $this->input->post('reinsurance_subcategory'));
} else {
$conditions = '';
}
}
if (!file_exists('timesheet/reports/production/gt_reinsurance_report')) {
mkdir('timesheet/reports/production/gt_reinsurance_report', 0777, true);
}
$result = $this->report_model->get_gt_reinsurance_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/gt_reinsurance_report';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$show_data['gt_reinsurance_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
if ($this->input->post('businesslines') == 1) {
$report_heading = 'GT Re-Insurance - ' . $branch . ' - Non-Propotional';
$name = 'GT_Reinsurance_Activities';
//$timesheet_result=create_timesheet_GT_reinsurance_motor($result,$folder,$name,$report_heading);
$timesheet_result = create_timesheet_GT_reinsurance_non_proportion($result, $folder, $name, $report_heading);
} else {
if ($this->input->post('reinsurance_category') == 2) {
$report_heading = 'GT Re-Insurance - ' . $branch . ' - Non-Propotional';
$name = 'GT_Reinsurance_Activities';
$timesheet_result = create_timesheet_GT_reinsurance_non_proportion($result, $folder, $name, $report_heading);
} else {
if ($this->input->post('reinsurance_category') == 3) {
$report_heading = 'GT Re-Insurance - ' . $branch . ' - Propotional';
$name = 'GT_Reinsurance_Activities';
$timesheet_result = create_timesheet_GT_reinsurance_proportion($result, $folder, $name, $report_heading);
}
}
}
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/gt_reinsurance_report/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/*pvt*/
if (isset($_POST['gt_pvt_reinsurance_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'reinsurance_category', 'label' => 'Re-insurance Category', 'rules' => 'required'), array('field' => 'reinsurance_subcategory', 'label' => 'Re-insurance Sub Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$gt_pvt_reinsurance_report = array();
$gt_pvt_reinsurance_report['branch'] = $this->input->post('branch');
$gt_pvt_reinsurance_report['businesslines'] = $this->input->post('businesslines');
$gt_pvt_reinsurance_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$gt_pvt_reinsurance_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$gt_pvt_reinsurance_report['to_date'] = $WEndDtObj->format('Y-m-d');
$gt_pvt_reinsurance_report['reinsurance_category'] = $this->input->post('reinsurance_category');
$gt_pvt_reinsurance_report['reinsurance_subcategory'] = $this->input->post('reinsurance_subcategory');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
//array_push($condition_array," pod.approved_date BETWEEN '".$gt_pvt_reinsurance_report['from_date']."' AND '".$gt_pvt_reinsurance_report['to_date']."'");
if ($this->input->post('date_type') == 'AD') {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $gt_pvt_reinsurance_report['from_date'] . "' AND '" . $gt_pvt_reinsurance_report['to_date'] . "'");
} else {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date BETWEEN '" . $gt_pvt_reinsurance_report['from_date'] . "' AND '" . $gt_pvt_reinsurance_report['to_date'] . "' ");
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('reinsurance_category') != '') {
if ($this->input->post('reinsurance_category') != 'All') {
array_push($condition_array, " pr.reinsurance_category =" . $this->input->post('reinsurance_category'));
} else {
$conditions = '';
}
}
if ($this->input->post('reinsurance_subcategory') != '') {
if ($this->input->post('reinsurance_subcategory') != 'All') {
array_push($condition_array, " pr.reinsurance_subcategory =" . $this->input->post('reinsurance_subcategory'));
} else {
$conditions = '';
}
}
if (!file_exists('timesheet/reports/production/gt_pvt_reinsurance_report')) {
mkdir('timesheet/reports/production/gt_pvt_reinsurance_report', 0777, true);
}
$result = $this->report_model->get_gt_pvt_reinsurance_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/gt_pvt_reinsurance_report';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$show_data['gt_pvt_reinsurance_report'] = $result;
$report_heading = 'PVT Re-Insurance - ' . $branch . ' ' . $date_string;
$name = 'PVT_Reinsurance_Activities';
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_pvt_reinsurance($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/gt_pvt_reinsurance_report/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* recoveries */
if (isset($_POST['gt_recovery_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'reinsurance_category', 'label' => 'Re-insurance Category', 'rules' => 'required'), array('field' => 'reinsurance_subcategory', 'label' => 'Re-insurance Sub Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$gt_recovery_report = array();
$gt_recovery_report['branch'] = $this->input->post('branch');
$gt_recovery_report['businesslines'] = $this->input->post('businesslines');
$gt_recovery_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$gt_recovery_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$gt_recovery_report['to_date'] = $WEndDtObj->format('Y-m-d');
$gt_recovery_report['reinsurance_category'] = $this->input->post('reinsurance_category');
$gt_recovery_report['reinsurance_subcategory'] = $this->input->post('reinsurance_subcategory');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
//array_push($condition_array," pod.approved_date BETWEEN '".$gt_recovery_report['from_date']."' AND '".$gt_recovery_report['to_date']."'");
if ($this->input->post('reinsurance_category') == 3) {
$date_type = "(Approval Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $gt_recovery_report['from_date'] . "' AND '" . $gt_recovery_report['to_date'] . "'");
} else {
if ($this->input->post('reinsurance_category') == 2) {
$date_type = "(Loss Date)";
array_push($condition_array, " tia_ci.incident_date BETWEEN '" . $gt_recovery_report['from_date'] . "' AND '" . $gt_recovery_report['to_date'] . "' ");
}
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('reinsurance_category') != '') {
if ($this->input->post('reinsurance_category') != 'All') {
array_push($condition_array, " pr.reinsurance_category =" . $this->input->post('reinsurance_category'));
} else {
$conditions = '';
}
}
if ($this->input->post('reinsurance_subcategory') != '') {
if ($this->input->post('reinsurance_subcategory') != 'All') {
array_push($condition_array, " pr.reinsurance_subcategory =" . $this->input->post('reinsurance_subcategory'));
} else {
$conditions = '';
}
}
if (!file_exists('timesheet/reports/production/gt_recovery_report')) {
mkdir('timesheet/reports/production/gt_recovery_report', 0777, true);
}
$result = $this->report_model->get_gt_recovery_report_info($condition_array, $conditions);
$folder = 'timesheet/reports/production/gt_recovery_report';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$show_data['gt_recovery_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
if ($this->input->post('businesslines') == 1) {
$report_heading = 'GT Re-Insurance - ' . $branch . ' - Non-Propotional';
$name = 'GT_Reinsurance_Activities';
//$timesheet_result=create_timesheet_GT_reinsurance_motor($result,$folder,$name,$report_heading);
$timesheet_result = create_timesheet_GT_reinsurance_recoveries_non_proportion($result, $folder, $name, $report_heading);
} else {
if ($this->input->post('reinsurance_category') == 2) {
$report_heading = 'GT Re-Insurance - ' . $branch . ' - Non-Propotional';
$name = 'GT_Reinsurance_Activities';
$timesheet_result = create_timesheet_GT_reinsurance_recoveries_non_proportion($result, $folder, $name, $report_heading);
} else {
if ($this->input->post('reinsurance_category') == 3) {
$report_heading = 'GT Re-Insurance - ' . $branch . ' - Propotional';
$name = 'GT_Reinsurance_Activities';
$timesheet_result = create_timesheet_GT_reinsurance_recoveries_proportion($result, $folder, $name, $report_heading);
}
}
}
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/gt_recovery_report/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/******** payment received report ********/
if (isset($_POST['payment_received_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date'), array('field' => 'to_date', 'label' => 'To Date'), array('field' => 'type', 'label' => 'Type'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$premium_register_general_report = array();
$premium_register_general_report['branch'] = $this->input->post('branch');
$premium_register_general_report['businesslines'] = $this->input->post('businesslines');
$premium_register_general_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$premium_register_general_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$premium_register_general_report['to_date'] = $WEndDtObj->format('Y-m-d');
$premium_register_general_report['type'] = $this->input->post('type');
$condition_array = array();
$conditions = '';
if ($this->input->post('branch') != '') {
if ($this->input->post('branch') != 'All') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
} else {
$conditions = '';
}
}
if ($this->input->post('businesslines') != '') {
if ($this->input->post('businesslines') != 'All') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
} else {
$conditions = '';
}
}
if ($this->input->post('product_category') != '') {
if ($this->input->post('product_category') != 'All') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
} else {
$conditions = '';
}
}
$date_string = '';
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
if ($this->input->post('date_type') == 'DN') {
$date_type = "(Debit Note Date)";
array_push($condition_array, " pod.approved_date BETWEEN '" . $premium_register_general_report['from_date'] . "' AND '" . $premium_register_general_report['to_date'] . "'");
} else {
if ($this->input->post('date_type') == 'PD') {
$date_type = "(Policy Date)";
array_push($condition_array, " pod.from_date_details BETWEEN '" . $premium_register_general_report['from_date'] . "' AND '" . $premium_register_general_report['to_date'] . "'");
} else {
$date_type = "(Receipting Date)";
array_push($condition_array, " paye.created_date BETWEEN '" . $premium_register_general_report['from_date'] . "' AND '" . $premium_register_general_report['to_date'] . "'");
}
}
$from_date = $this->input->post('from_date');
$to_date = $this->input->post('to_date');
$date_string = ' for ' . $from_date . ' - ' . $to_date . $date_type;
}
if ($this->input->post('type') != '') {
array_push($condition_array, " pod.invoice_type =" . $this->input->post('type'));
}
$result = $this->report_model->get_customer_production_report_info($condition_array, $conditions);
if (!file_exists('timesheet/reports/production/payment_received')) {
mkdir('timesheet/reports/production/payment_received', 0777, true);
}
$folder = 'timesheet/reports/production/payment_received';
if (sizeof($result) > 0) {
if ($this->input->post('branch') != 'All') {
$branch = $result[0]['branch'];
} else {
$branch = 'ALL BRANCHES';
}
$report_heading = 'Payment Received Report - ' . $branch . $date_string;
$name = 'Payment_Received_Report';
$show_data['payment_received_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_payment_received($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/payment_received/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
$this->load->view('reports/reports_view', $show_data);
}
function index()
{
$show_data = array();
$show_data['error'] = '';
$show_data['success'] = $this->session->userdata('success');
$this->clearmessage();
/* Intermediary Commission Report */
if (isset($_POST['intermediary_commision_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$intermediary_commision_report = array();
$intermediary_commision_report['branch'] = $this->input->post('branch');
$intermediary_commision_report['intermediary'] = $this->input->post('intermediary');
$intermediary_commision_report['intermediary_users'] = $this->input->post('intermediary_users');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$intermediary_commision_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$intermediary_commision_report['to_date'] = $WEndDtObj->format('Y-m-d');
$intermediary_commision_report['paid_status'] = $this->input->post('paid_status');
$condition_array = array();
if ($this->input->post('branch') != '') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
}
if ($this->input->post('intermediary') != '') {
array_push($condition_array, " pod.source_of_business =" . $this->input->post('intermediary'));
}
if ($this->input->post('intermediary_users') != '') {
if ($this->input->post('intermediary') == 1 || $this->input->post('intermediary') == 2 || $this->input->post('intermediary') == 3) {
array_push($condition_array, " inter.user_type_id =" . $this->input->post('intermediary_users'));
} else {
if ($this->input->post('intermediary') == 4) {
array_push($condition_array, " tia_ud.user_details_id =" . $this->input->post('intermediary_users'));
}
}
}
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
array_push($condition_array, " pod.created_date BETWEEN '" . $intermediary_commision_report['from_date'] . "' AND '" . $intermediary_commision_report['to_date'] . "'");
}
$result = $this->report_model->get_intermediary_commision_report_info($condition_array);
$folder = 'timesheet/reports/production/intermediary_commission';
if (sizeof($result) > 0) {
$report_heading = 'Agency / Broker Commission Payable';
$name_1 = $result[0]['created_date'];
$row = explode(" ", $name_1);
$name = $row[0];
$show_data['freedate_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_intermediary_commission($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/intermediary_commission/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
/* Premium Register General Report */
if (isset($_POST['premium_register_general_report'])) {
$this->load->library('form_validation');
$config = array(array('field' => 'branch', 'label' => 'Branch Name', 'rules' => 'required'), array('field' => 'businesslines', 'label' => 'Business line', 'rules' => 'required'), array('field' => 'product_category', 'label' => 'Product Category', 'rules' => 'required'), array('field' => 'from_date', 'label' => 'From Date', 'rules' => 'required'), array('field' => 'to_date', 'label' => 'To Date', 'rules' => 'required'), array('field' => 'type', 'label' => 'Type', 'rules' => 'required'));
$this->form_validation->set_rules($config);
if ($this->form_validation->run() != false) {
$premium_register_general_report = array();
$premium_register_general_report['branch'] = $this->input->post('branch');
$premium_register_general_report['businesslines'] = $this->input->post('businesslines');
$premium_register_general_report['product_category'] = $this->input->post('product_category');
$WStartDtObj = new DateTime($this->input->post('from_date'));
$premium_register_general_report['from_date'] = $WStartDtObj->format('Y-m-d');
$WEndDtObj = new DateTime($this->input->post('to_date'));
$premium_register_general_report['to_date'] = $WEndDtObj->format('Y-m-d');
$premium_register_general_report['type'] = $this->input->post('type');
$condition_array = array();
if ($this->input->post('branch') != '') {
array_push($condition_array, " mt.branch_id =" . $this->input->post('branch'));
}
if ($this->input->post('businesslines') != '') {
array_push($condition_array, " c.business_line_id =" . $this->input->post('businesslines'));
}
if ($this->input->post('product_category') != '') {
array_push($condition_array, " mt.product_category_id =" . $this->input->post('product_category'));
}
if ($this->input->post('from_date') != '' && $this->input->post('to_date') != '') {
array_push($condition_array, " pod.created_date BETWEEN '" . $premium_register_general_report['from_date'] . "' AND '" . $premium_register_general_report['to_date'] . "'");
}
if ($this->input->post('type') != '') {
array_push($condition_array, " pod.invoice_type =" . $this->input->post('type'));
}
$result = $this->report_model->get_premium_register_general_report_info($condition_array);
$folder = 'timesheet/reports/production/premium_register_general';
if (sizeof($result) > 0) {
$report_heading = 'Premium Register (General)';
// $name_1 = $result[0]['created_date'];
// $row = explode(" ",$name_1);
// $name = $row[0];
$name = 'Premium_Register_(General)';
$show_data['premium_register_general_report'] = $result;
$this->load->helper('amountinwords');
$this->load->helper(array('timesheet', 'file'));
$timesheet_result = create_timesheet_premium_register($result, $folder, $name, $report_heading);
if ($timesheet_result != '') {
redirect(base_url() . 'timesheet/reports/production/premium_register_general/' . $timesheet_result . '.xlsx');
exit;
}
} else {
$show_data['error'] = 'No Data Found';
}
}
}
$this->load->view('underwriter/reports_view', $show_data);
}