File: /home/cafsindia/.trash/application.1/controllers/Month_wise_consolidated_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Month_wise_consolidated_report extends Action_controller{
public function __construct(){
parent::__construct('month_wise_consolidated_report');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['company_info'] = $this->company_info();
$get_earning_qry = 'SELECT prime_form_id,prime_module_id,label_name,view_name FROM `cw_form_setting` WHERE prime_module_id = "employees" and transaction_type in (1,2) and field_type = 2 and taxable_check = 1 order by view_name asc';
$get_earning_info = $this->db->query("CALL sp_a_run ('SELECT','$get_earning_qry')");
$get_earning_rslt = $get_earning_info->result();
$get_earning_info->next_result();
// $earning_list[""] = "---- Select Column ----";
foreach($get_earning_rslt as $ded_column){
$prime_form_id = $ded_column->prime_form_id;
$column_value = $ded_column->label_name;
$view_name = $ded_column->view_name;
$earning_list[$this->xss_clean($column_value)] = $this->xss_clean($view_name);
}
$get_deduction_qry = 'SELECT prime_form_id,prime_module_id,label_name,view_name FROM `cw_form_setting` WHERE prime_module_id = "employees" and transaction_type = 3 and deduction_check = 1 order by view_name asc';
$get_deduction_info = $this->db->query("CALL sp_a_run ('SELECT','$get_deduction_qry')");
$get_deduction_rslt = $get_deduction_info->result();
$get_deduction_info->next_result();
// $deduction_list[""] = "---- Select Column ----";
foreach($get_deduction_rslt as $ded_column){
$prime_form_id = $ded_column->prime_form_id;
$column_value = $ded_column->label_name;
$view_name = $ded_column->view_name;
$deduction_list[$this->xss_clean($column_value)] = $this->xss_clean($view_name);
}
$get_finance_qry = 'SELECT prime_financial_setting_id,start_date,end_date FROM `cw_financial_setting` WHERE trans_status = 1';
$get_finance_info = $this->db->query("CALL sp_a_run ('SELECT','$get_finance_qry')");
$get_finance_rslt = $get_finance_info->result();
$get_finance_info->next_result();
$financial_list[""] = "---- Select Column ----";
foreach($get_finance_rslt as $finance_rslt){
$prime_financial_id = $finance_rslt->prime_financial_setting_id;
$start_date = date('d-m-Y',strtotime($finance_rslt->start_date));
$end_date = date('d-m-Y',strtotime($finance_rslt->end_date));
$financial_list[$prime_financial_id] = $start_date.' - '.$end_date;
}
$data['financial_list'] = $financial_list;
$data['earning_component'] = $earning_list;
$data['deduction_component'] = $deduction_list;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
// AUTOCOMPLETE FOR SERACH EMPLOYEE
public function emp_suggest(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$search_term = $this->input->post_get('term');
$role_condition = "";
if($this->role_condition){//role conditions is added
$role_condition = $this->role_condition;
}
$final_qry = 'select employee_code,emp_name from cw_employees where trans_status = 1 and employee_code like "'.$search_term.'%" or emp_name like "'.$search_term.'%" '.$role_condition.'';
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
foreach($final_result as $rslt){
$employee_code = $rslt->employee_code;
$emp_name = $rslt->emp_name;
$suggestions[] = array('value' => $employee_code, 'label' => "$employee_code - $emp_name");
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
public function get_table_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$employee_code = $this->input->post("employee_code");
$from_month = $this->input->post("from_month");
$to_month = $this->input->post("to_month");
$earning_component = implode(',cw_transactions.',$this->input->post("earning_component"));
$deduction_component= implode(',cw_transactions.',$this->input->post("deduction_component"));
$get_labels ="";
if($earning_component){
$earning_component = ",cw_transactions.".$earning_component.",cw_transactions.total_earnings,cw_transactions.net_pay";
}else{
$earning_component = ",cw_transactions.total_earnings,cw_transactions.net_pay";
}
// echo "$earning_component";die;
if($deduction_component){
$deduction_component = ",cw_transactions.".$deduction_component.",cw_transactions.total_deductions";
}else{
$deduction_component = ",cw_transactions.total_deductions";
}
$get_label = rtrim($get_labels,',');
$fromDate = '01-'.$from_month;
$fromDate = date('Y-m-d',strtotime($fromDate));
$toDate = '01-'.$to_month;
$toDate = date('Y-m-d',strtotime($toDate));
$emp_qry = 'select emp_name,cw_department.department from cw_employees inner join cw_department on cw_department.prime_department_id = cw_employees.department where employee_code ="'.$employee_code.'" and cw_employees.trans_status = 1';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_reslt = $emp_info->result();
$emp_info->next_result();
$emp_name = $emp_reslt[0]->emp_name;
$department = $emp_reslt[0]->department;
$label_qry = 'select label_name,view_name from cw_form_setting where prime_module_id ="employees" and trans_status = 1';
$label_info = $this->db->query("CALL sp_a_run ('SELECT','$label_qry')");
$label_rslt = $label_info->result_array();
$label_info->next_result();
$label_rslt = array_reduce($label_rslt, function ($result, $arr) {
$result[$arr['label_name']] = $arr['view_name'];
return $result;
}, array());
$earning_qry = 'select transactions_month'.$earning_component.' from cw_transactions where cw_transactions.employee_code = "'.$employee_code.'" and date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y") , "%Y-%m") BETWEEN date_format("'.$fromDate.'", "%Y-%m") and date_format("'.$toDate.'", "%Y-%m") and cw_transactions.trans_status = 1 group by cw_transactions.transactions_month order by date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y") , "%Y-%m") asc';
$earning_info = $this->db->query("CALL sp_a_run ('SELECT','$earning_qry')");
$earning_rslt = $earning_info->result_array();
$earning_info->next_result();
$earning_rslt = array_reduce($earning_rslt, function ($result, $arr) {
$result[$arr['transactions_month']] = $arr;
return $result;
}, array());
$deduction_qry = 'select transactions_month'.$deduction_component.' from cw_transactions where cw_transactions.employee_code = "'.$employee_code.'" and date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y") , "%Y-%m") BETWEEN date_format("'.$fromDate.'", "%Y-%m") and date_format("'.$toDate.'", "%Y-%m") and cw_transactions.trans_status = 1 group by cw_transactions.transactions_month order by date_format(str_to_date(CONCAT("01-", transactions_month), "%d-%m-%Y") , "%Y-%m") asc';
$deduction_info = $this->db->query("CALL sp_a_run ('SELECT','$deduction_qry')");
$deduction_rslt = $deduction_info->result_array();
$deduction_info->next_result();
$deduction_rslt = array_reduce($deduction_rslt, function ($result, $arr) {
$result[$arr['transactions_month']] = $arr;
return $result;
}, array());
$all_comp_arr = array_merge_recursive($earning_rslt,$deduction_rslt);
if(!$all_comp_arr){
echo json_encode(array('success'=>false, 'message' => 'No Data'));
exit(0);
}
$get_component .= "earning_val,".$earning_component.",";
$get_component .= "deduction_val,".$deduction_component.",";
// $all_compo = $ear_component.",".$ded_component;
$all_compo = rtrim($get_component,',');
// echo "$all_compo";die;
$all_component_arr = explode(',', $all_compo);
$tr_head = "<tr class='export_data'><th></th>";
$footer_val = array();
$footer_tot = "<tr class='export_data'><th style='font-weight: bold;color:red'>Total</th>";
foreach ($all_comp_arr as $key => $value) {
$tr_head .= "<th>$key</th>";
foreach ($all_component_arr as $get_label) {
$get_label = str_replace('cw_transactions.', '', $get_label);
$view_name = $label_rslt[$get_label];
$get_val = $value[$get_label];
if($get_label === "earning_val"){
$view_name = "Earnings";
}else
if($get_label === "deduction_val"){
$view_name = "Deductions";
}
if($view_name){
$label_wise_arr[$get_label]['label_name'] = $view_name;
$label_wise_arr[$get_label][$key] = $get_val;
$footer_val[$key][] = $get_val;
}
}
$get_footer_val = array_sum($footer_val[$key]);
$footer_tot .= "<th style='font-weight: bold;color:red'>$get_footer_val</th>";
}
$footer_tot .= "<th></th></tr>";
$tr_head .= "<th>Total</th></tr>";
$tr_line = "<tr class='export_data'>";
foreach ($label_wise_arr as $trans_key => $value) {
$tb_value_sum = 0;
foreach ($value as $key => $tb_value) {
if($trans_key === "earning_val" || $trans_key === "deduction_val"){
$tr_line .= "<td style ='font-weight: bold;color:green;'>$tb_value</td>";
}else
if($trans_key === "total_deductions" || $trans_key === "net_pay" || $trans_key === "total_earnings" ){
$tr_line .= "<td style ='color:#1b00ff;'>$tb_value</td>";
}else{
$tr_line .= "<td>$tb_value</td>";
}
(int)$tb_value_sum += (int)$tb_value;
}
if($trans_key === "earning_val" || $trans_key === "deduction_val" ){
$tr_line .= "<td style ='font-weight: bold; color:red;'></td></tr>";
}else{
$tr_line .= "<td style ='font-weight: bold; color:red;'>$tb_value_sum</td></tr>";
}
}
$company_info = $this->company_info();
$company_name = $company_info[0]->company_name;
$company_address = $company_info[0]->address;
$company_city = $company_info[0]->city;
$company_state = $company_info[0]->state;
$company_country = $company_info[0]->country;
$arr_count = count($all_comp_arr)+2;
$divid_span = $arr_count/2;
if($arr_count % 2 == 0){
$divid_span = $divid_span;
}else{
$divid_span = $divid_span+1;
}
$table_content = "<div style='page-break-after: always;width:100%; margin-left: auto; margin-right: auto; padding: 15px; background-color: #EEEEEE; border-radius: 3px; box-shadow: 0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2);'><button id='footables' class='excelexport dt-button buttons-collection btn btn-xs btn-edit'><div class='dt-buttons'>EXCEL</button><div class='dt-button-collection' style='top: 42.1632px; left: 1391.01px;'>
</div></div>
<table class='table table-striped table-bordered footable' id='footable' style='width:100%;border:1px solid #000;'>
<thead style='background-color:white;'>
<tr class='export_data add_style'>
<td colspan='$arr_count'>
<h3 style='text-align: center;color:#2b2b2b;'>Month Wise Consolidated Report </h3>
</td>
</tr>
<tr class='export_data add_style'>
<td colspan='$divid_span' style='color:black;text-align: right;'>Name and address of the employer</td>
<td colspan='$divid_span' style='color:black;text-align: center;'>Code,name and Department of the employee</td>
</tr>
<tr class='export_data add_style'>
<td colspan='$divid_span' style='color:black;font-weight:bold;text-align: center;''>$company_name<br/>$company_address<br/>$company_city<br/>$company_state<br/>$company_country</td>
<td colspan='$divid_span' style='color:black;text-align: center;'>$employee_code<br/>$emp_name<br/>$department<br/></td>
</tr>
</thead>
<thead>
$tr_head
</thead>
<tbody>
$tr_line
</tbody>
</table><div>"; //$footer_tot
echo json_encode(array('success'=>TRUE, 'table_content' => $table_content,'employee_code' => $employee_code,'emp_name' => $emp_name,'from_month' => $from_month,'to_month' =>$to_month,'department'=>$department, 'message'=>'Successfully Report Generated..!!'));
}
public function get_financial_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$finance_year = $this->input->post("finance_year");
$get_finance_qry = 'SELECT prime_financial_setting_id,start_date,end_date FROM `cw_financial_setting` WHERE prime_financial_setting_id = "'.$finance_year.'" and trans_status = 1';
$get_finance_info = $this->db->query("CALL sp_a_run ('SELECT','$get_finance_qry')");
$get_finance_rslt = $get_finance_info->result();
$get_finance_info->next_result();
$start_date = date('d-m-Y',strtotime($get_finance_rslt[0]->start_date));
$end_date = date('d-m-Y',strtotime($get_finance_rslt[0]->end_date));
// echo "end_date ::$start_date";die;
echo json_encode(array('success'=>TRUE, 'start_date' => $start_date,'end_date' => $end_date));
}
}
?>