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); }