File: /home/cafsindia/cloud_cafsinfotech_in/application/controllers/Salary_summary.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Salary_summary extends Action_controller{
public function __construct(){
parent::__construct('salary_summary');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data = "";
$this->load->view("$this->control_name/manage",$data);
}
public function summary_details(){
$process_month = $this->input->post('process_month');
$month_info = date('F-Y', strtotime( date( "Y-m-d", strtotime("01-".$process_month))));
$month_info = strtoupper($month_info);
//check payroll exit or not;
$istrans_exist_qry = 'select count(*) as result_count from cw_transactions where trans_status = 1 and termination_status = 0 and transactions_month = "'.$process_month.'"';
$istrans_data = $this->db->query("CALL sp_a_run ('SELECT','$istrans_exist_qry')");
$istrans_result = $istrans_data->result();
$istrans_data->next_result();
$istrans_count = (int)$istrans_result[0]->result_count;
if((int)$istrans_count === 0){
echo json_encode(array('success' => false, 'message' => "Payroll is not proceed this month?"));
exit(0);
}else{
//earned gross_check columns//
$earned_column_qry = 'SELECT GROUP_CONCAT(label_name ORDER BY field_sort) as earned_columns, GROUP_CONCAT(view_name ORDER BY field_sort) as earned_columns_view FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) and gross_check = 1';
$earned_column_data = $this->db->query("CALL sp_a_run ('SELECT','$earned_column_qry')");
$earned_column_rslt = $earned_column_data->result();
$earned_column_data->next_result();
$earned_columns = explode(",",$earned_column_rslt[0]->earned_columns);
$earned_column_view = explode(",",$earned_column_rslt[0]->earned_columns_view);
$view_names = array_combine($earned_columns,$earned_column_view);
//deduction_check columns//
$deduct_column_qry = 'SELECT GROUP_CONCAT(label_name ORDER BY field_sort) as deduct_columns, GROUP_CONCAT(view_name ORDER BY field_sort) as ded_columns_view FROM cw_form_setting WHERE prime_module_id = "employees" AND trans_status = 1 AND input_view_type IN (1,2) and deduction_check = 1';
$deduct_column_data = $this->db->query("CALL sp_a_run ('SELECT','$deduct_column_qry')");
$deduct_column_rslt = $deduct_column_data->result();
$deduct_column_data->next_result();
$deduct_columns = explode(",",$deduct_column_rslt[0]->deduct_columns);
$deduct_column_view = explode(",",$deduct_column_rslt[0]->ded_columns_view);
$deduct_view_names = array_combine($deduct_columns,$deduct_column_view);
$sum_column = "";
foreach($earned_columns as $earned_col){
$sum_column .= "sum($earned_col) as $earned_col,";
}
$sum_column = rtrim($sum_column,',');
$ded_column = "";
foreach($deduct_columns as $ded_col){
$ded_column .= "sum($ded_col) as $ded_col,";
}
$ded_column = rtrim($ded_column,',');
//earning details
$earned_sum_query ="select category_name, count(employee_code) as emp_count, $sum_column from cw_transactions inner join cw_category on cw_category.prime_category_id = role where cw_transactions.trans_status =1 and transactions_month = \"$process_month\" group by role";
$earned_sum_data = $this->db->query("CALL sp_a_run ('SELECT','$earned_sum_query')");
$earned_sum_result = $earned_sum_data->result_array();
$earned_sum_data->next_result();
$earned_sum_result = array_reduce($earned_sum_result, function ($result, $arr) {
$result[$arr['category_name']] = $arr;
return $result;
}, array());
$summary_list = array();
foreach($earned_sum_result as $earned_key=>$earned_val){
foreach($earned_val as $key =>$val){
$summary_list[$key][$earned_key] = $val;
}
}
$tr_line = "";
$cls = "";
foreach($summary_list as $sum_key=>$sum_val){
$td_line = "";
$i = 2;
$td_tot = 0;
foreach($sum_val as $sumkey =>$sumval){
$td_line .="<td>".$sumval."</td>";
$td_tot = $td_tot + $sumval;
$td_tot_line = "<td>$td_tot</td>";
$i++;
}
if($sum_key === "total_earnings"){
$cls = "style='font-weight:bold;'";
}else{
$cls = "";
}
if(array_key_exists($sum_key,$view_names)){
if($sum_key === "net_pay"){
$cls = "style='font-weight:bold;'";
$tr_lastline ="<tr $cls><td>".strtoupper($view_names[$sum_key])."</td>$td_line$td_tot_line</tr>";
}else{
$tr_line .="<tr $cls><td>".strtoupper($view_names[$sum_key])."</td>$td_line$td_tot_line</tr>";
}
}else{
if($sum_key === "category_name"){
$sum_name = "DESCRIPTION";
$cls = "style='font-weight:bold;'";
$tr_first_line ="<tr $cls><td>$sum_name</td>$td_line<td>Total</td></tr>";
}else
if($sum_key === "emp_count"){
$sum_name = "EMP STRENGTH";
$tr_line .="<tr><td>$sum_name</td>$td_line$td_tot_line</tr>";
}
}
}
//deductions details
$deduct_sum_query ="select category_name, $ded_column from cw_transactions inner join cw_category on cw_category.prime_category_id = role where cw_transactions.trans_status =1 and transactions_month = \"$process_month\" group by role";
$deduct_sum_data = $this->db->query("CALL sp_a_run ('SELECT','$deduct_sum_query')");
$deduct_sum_result = $deduct_sum_data->result_array();
$deduct_sum_data->next_result();
$deduct_sum_result = array_reduce($deduct_sum_result, function ($result, $arr) {
$result[$arr['category_name']] = $arr;
return $result;
}, array());
$ded_summary_list = array();
foreach($deduct_sum_result as $deduct_key=>$deduct_val){
foreach($deduct_val as $dedkey =>$dedval){
if($dedkey !="category_name"){
$ded_summary_list[$dedkey][$deduct_key] = $dedval;
}
}
}
$ded_tr_line = "";
foreach($ded_summary_list as $ded_sum_key=>$ded_sum_val){
$ded_td_line = "";
$ded_td_tot = 0;
foreach($ded_sum_val as $dedsumkey =>$dedsumval){
$ded_td_line .="<td>".$dedsumval."</td>";
$ded_td_tot = $ded_td_tot + $dedsumval;
$ded_td_tot_line = "<td>$ded_td_tot</td>";
}
if($ded_sum_key === "total_deductions"){
$cls = "style='font-weight:bold;'";
}else{
$cls ="";
}
if(array_key_exists($ded_sum_key,$deduct_view_names)){
$ded_tr_line .="<tr $cls><td>".strtoupper($deduct_view_names[$ded_sum_key])."</td>$ded_td_line$ded_td_tot_line</tr>";
}
}
//bank details
$bankwise_net_query ="select sum(cw_transactions.net_pay) as net_pay,cw_bank_name.bank_name as bank_name from cw_transactions inner join cw_employees on cw_employees.employee_code = cw_transactions.employee_code inner join cw_bank_name on cw_bank_name.prime_bank_name_id = cw_employees.bank_name where cw_transactions.trans_status = 1 and transactions_month =\"$process_month\"group by cw_employees.bank_name";
$bankwise_net_data = $this->db->query("CALL sp_a_run ('SELECT','$bankwise_net_query')");
$bankwise_net_result = $bankwise_net_data->result_array();
$bankwise_net_data->next_result();
$bankwise_net_result = array_reduce($bankwise_net_result, function ($result, $arr) {
$result[$arr['bank_name']] = $arr['net_pay'];
return $result;
}, array());
//empty td for total
for($j = 2;$j<$i;$j++){
$td_emp .= "<td/>";
}
$bank_tr_line = "";
foreach($bankwise_net_result as $bank_key=>$bak_val){
$bank_case = strtoupper($bank_key);
$bank_tr_line .= "<tr><td>$bank_case</td>$td_emp<td>$bak_val</td></tr>";
}
$ded_head_tr_line = "<tr style='font-weight:bold;'><td>DEDUCTIONS</td>$td_emp<td></td></tr>";
$bank_head_tr_line = "<tr style='font-weight:bold;'><td>BANK DETAILS</td>$td_emp<td></td></tr>";
$tot_tr_line .= "<tr style='font-weight:bold;'><td>TOTAL SALARY</td>$td_emp$td_tot_line</tr>";
$table_data = "<div style='margin:20px;'><table class='table table-bordered col-style' id='detail_list' style='box-shadow:none;'>
<thead>
<tr style='font-weight:bold;text-align:center;'><td colspan='$i'>SUMMARY $month_info</td></tr>
$tr_first_line
</thead>
<tbody>
$tr_line
$ded_head_tr_line
$ded_tr_line
$tr_lastline
$bank_head_tr_line
$bank_tr_line
$tot_tr_line
</tbody>
</table></div>";
echo json_encode(array('success' => true,'msg'=>'Ok', 'table_data' => $table_data));
}
}
}
?>