File: /home/cafsindia/hrms_patroniss_com/application/controllers/Detailed_reconciliation.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Detailed_reconciliation extends Action_controller{
public function __construct(){
parent::__construct('detailed_reconciliation');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->collect_base_info();
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$data['table_headers']= $this->xss_clean(get_detailed_reconciliation_headers());
$this->load->view("$this->control_name/manage",$data);
}
//LOAD MODEL PAGE VIEW WITH DATA
public function view($prime_reconciliation_id = -1){
$recon_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_reconciliation_id,setting_name from cw_detailed_reconciliation where cw_detailed_reconciliation.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_detailed_reconciliation.trans_status = 1')");
$reconciliation_info = $recon_query->row();
$recon_query->next_result();
$data['reconciliation_info'] = $reconciliation_info;
//get all the earning columns without tax column
$get_match_columns = '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 order by view_name asc';
$match_column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_match_columns')");
$match_column_result = $match_column_info->result();
$match_column_info->next_result();
$match_columns[""] = "---- Select Column ----";
foreach($match_column_result as $match_column){
$prime_form_id = $match_column->prime_form_id;
$column_value = $match_column->label_name;
$view_name = $match_column->view_name;
$match_columns[$this->xss_clean($column_value)] = $this->xss_clean($view_name);
}
$data['earning_columns'] = $match_columns;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
$category_list[1] = "All Category";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
//Get All Deduction Component
$get_ded_columns = 'SELECT prime_form_id,prime_module_id,label_name,view_name FROM `cw_form_setting` WHERE prime_module_id = "employees" and transaction_type = 3 order by view_name asc';
$ded_column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_ded_columns')");
$ded_column_result = $ded_column_info->result();
$ded_column_info->next_result();
$ded_columns[""] = "---- Select Column ----";
foreach($ded_column_result as $ded_column){
$prime_form_id = $ded_column->prime_form_id;
$column_value = $ded_column->label_name;
$view_name = $ded_column->view_name;
$ded_columns[$this->xss_clean($column_value)] = $this->xss_clean($view_name);
}
$data['ded_columns'] = $ded_columns;
$send_data = json_decode($this->get_earning_info($prime_reconciliation_id));
$data['earning_column_table'] = $send_data->earning_info;
$send_data = json_decode($this->get_deduct_info($prime_reconciliation_id));
$data['deduct_column_table'] = $send_data->deduct_info;
$this->load->view("$this->control_name/form",$data);
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search(){
$search = $this->input->get('search');
$limit = $this->input->get('limit');
$offset = $this->input->get('offset');
$sort = $this->input->get('sort');
$order = $this->input->get('order');
$start_date = str_replace("/","-",$this->input->get('start_date'));
$end_date = str_replace("/","-",$this->input->get('end_date'));
$from_date = date('Y-m-d',strtotime($start_date));
$to_date = date('Y-m-d',strtotime($end_date));
$search_query ='select cw_detailed_reconciliation.prime_reconciliation_id,cw_detailed_reconciliation.setting_name from cw_detailed_reconciliation';
$common_search = "";
if($search){
$common_search .= ' or prime_reconciliation_id like "'.$search.'%"';
$common_search .= ' or setting_name like "'.$search.'%"';
}
if($common_search){
$common_search = ltrim($common_search,' or ');
$common_search = " and ($common_search)";
}
if(!$sort){ $sort = "cw_detailed_reconciliation.prime_reconciliation_id"; }
if(!$order){ $order = "asc"; }
$search_query .= " where cw_detailed_reconciliation.trans_status = 1 $common_search";
$search_query .= " ORDER BY $sort $order";
//FETCH RECORDS DATA
$search_data = $this->db->query("CALL sp_a_run ('SELECT','$search_query')");
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
$search_data->next_result();
foreach($search_result as $receiving){
$datarows[] = get_detailed_reconciliation_data_row($receiving,$this);
}
echo json_encode(array('total'=>$num_rows,'rows'=>$datarows));
}
public function save_setting(){
$prime_reconciliation_id = $this->input->post('prime_reconciliation_id');
$setting_name = $this->input->post('setting_name');
$detailed_reconciliation_data = array(
'setting_name' => $setting_name
);
if((int)$prime_reconciliation_id === 0){
$created_on = date("Y-m-d H:i:s");
$insert_query = 'insert into cw_detailed_reconciliation (setting_name,trans_created_by,trans_created_date) values ("'.$setting_name.'","'.$this->session->userdata('logged_id').'","'.$created_on.'")';
$return = $this->db->query("CALL sp_a_run ('INSERT','$insert_query')");
$return->next_result();
if($return){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Inserted"));
}else{
echo json_encode(array('success' => FALSe, 'message' => "Insertion failed, Please Try Again"));
}
}else
if((int)$prime_reconciliation_id > 0 && $this->check_reconciliation_exists($prime_reconciliation_id,TRUE)){
$created_on = date("Y-m-d H:i:s");
$update_reconciliation = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",setting_name = "'.$setting_name.'"';
$update_query = 'UPDATE cw_detailed_reconciliation SET '. $update_reconciliation .' WHERE cw_detailed_reconciliation.prime_reconciliation_id = "'. $prime_reconciliation_id .'"';
if($this->db->query("CALL sp_a_run ('UPDATE','$update_query')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Updated"));
}else{
echo json_encode(array('success' => FALSe, 'message' => "Update failed, Please Try Again"));
}
}else{
echo json_encode(array('success' => False, 'message' => "Please contact Admin.!"));
}
}
public function save_earning_info(){
$prime_reconciliation_id = (int)$this->input->post('reconciliation_id');
$prime_earning_id = (int)$this->input->post('prime_earning_id');
$earning_column = $this->input->post('earning_column');
$display_name = $this->input->post('display_name');
$earning_order = $this->input->post('earning_order');
if(($this->check_reconciliation_exists($prime_reconciliation_id,TRUE)) && ($prime_earning_id === 0)){
if(!($this->check_earning_already_exists($earning_column,$prime_reconciliation_id))){
$earning_order = $this->change_earning_order($earning_order,$prime_reconciliation_id);
$created_on = date("Y-m-d H:i:s");
$insert_earning_query = 'insert into cw_reconciliation_earning_info (earning_column,display_name,prime_reconciliation_id,earning_order,trans_created_by,trans_created_date) values ("'.$earning_column.'","'.$display_name.'","'.$prime_reconciliation_id.'","'.$earning_order.'","'.$this->session->userdata('logged_id').'","'.$created_on.'")';
$earning_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_earning_query')");
$earning_info->next_result();
if($earning_info){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Inserted",'prime_reconciliation_id' => $prime_reconciliation_id));
}else{
echo json_encode(array('success' => FALSe, 'message' => "Insertion failed, Please Try Again"));
}
}else{
echo json_encode(array('success' => False, 'message' => "Earning Infomation Already exists.!"));
}
}else
if(($this->check_reconciliation_exists($prime_reconciliation_id,TRUE)) && ((int)$prime_earning_id > 0)){
$earning_order = $this->change_earning_order($earning_order,$prime_reconciliation_id,$prime_earning_id);
$created_on = date("Y-m-d H:i:s");
$update_earning_info = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",earning_column = "'.$earning_column.'",display_name = "'.$display_name.'",prime_reconciliation_id = "'.$prime_reconciliation_id.'",earning_order = "'.$earning_order.'"';
$update_query = 'UPDATE cw_reconciliation_earning_info SET '. $update_earning_info .' WHERE cw_reconciliation_earning_info.prime_earning_id = "'. $prime_earning_id .'"';
if($this->db->query("CALL sp_a_run ('UPDATE','$update_query')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Updated",'prime_reconciliation_id'=> $prime_reconciliation_id ));
}else{
echo json_encode(array('success' => FALSe, 'message' => "Update failed, Please Try Again"));
}
}else{
echo json_encode(array('success' => False, 'message' => "Please Fill Previous Tab Info.!"));
}
}
public function save_deduct_info(){
$prime_reconciliation_id = (int)$this->input->post('prime_deduct_reconciliation_id');
$prime_deduct_id = (int)$this->input->post('prime_deduct_id');
$deduct_column = $this->input->post('deduct_column');
$display_deduct_name = $this->input->post('display_deduct_name');
$deduct_order = (int)$this->input->post('deduct_order');
if(($this->check_reconciliation_exists($prime_reconciliation_id,TRUE)) && ($prime_deduct_id === 0)){
if(!($this->check_deduct_already_exists($deduct_column,$prime_reconciliation_id))){
$deduct_order = $this->change_deduct_order($deduct_order,$prime_reconciliation_id,$prime_deduct_id);
$created_on = date("Y-m-d H:i:s");
$insert_deduct_query = 'insert into cw_reconciliation_deduct_info (deduct_column,display_name,prime_reconciliation_id,deduct_order,trans_created_by,trans_created_date) values ("'.$deduct_column.'","'.$display_deduct_name.'","'.$prime_reconciliation_id.'","'.$deduct_order.'","'.$this->session->userdata('logged_id').'","'.$created_on.'")';
$deduct_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_deduct_query')");
$deduct_info->next_result();
if($deduct_info){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Inserted",'prime_reconciliation_id' => $prime_reconciliation_id));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Insertion failed, Please Try Again"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Deduction Information Already exists.!"));
}
}else
if(($this->check_reconciliation_exists($prime_reconciliation_id,TRUE)) && ((int)$prime_deduct_id > 0)){
$deduct_order = $this->change_deduct_order($deduct_order,$prime_reconciliation_id,$prime_deduct_id);
$created_on = date("Y-m-d H:i:s");
$update_deduct_info = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",deduct_column = "'.$deduct_column.'",display_name = "'.$display_deduct_name.'",prime_reconciliation_id = "'.$prime_reconciliation_id.'",deduct_order = "'.$deduct_order.'"';
$update_deduct_query = 'UPDATE cw_reconciliation_deduct_info SET '. $update_deduct_info .' WHERE cw_reconciliation_deduct_info.prime_deduct_id = "'. $prime_deduct_id .'"';
if($this->db->query("CALL sp_a_run ('UPDATE','$update_deduct_query')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Updated",'prime_reconciliation_id'=> $prime_reconciliation_id ));
}else{
echo json_encode(array('success' => FALSe, 'message' => "Update failed, Please Try Again"));
}
}else{
echo json_encode(array('success' => False, 'message' => "Please Fill Previous Tab Info.!"));
}
}
//LIST EARNING INFOMATION
public function get_earning_info($prime_reconciliation_id = -1){
if((int)$this->input->post('prime_reconciliation_id') > 0){
$prime_reconciliation_id = $this->input->post('prime_reconciliation_id');
}
$prime_reconciliation_id = (int)$prime_reconciliation_id;
$tble_line = '';
if($this->check_reconciliation_exists((int)$prime_reconciliation_id,TRUE)){
$earning_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_earning_id,prime_reconciliation_id,earning_column,display_name,earning_order,cw_form_setting.view_name as earning_column from cw_reconciliation_earning_info inner join cw_form_setting on cw_form_setting.label_name = cw_reconciliation_earning_info.earning_column where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.trans_status = 1 group by cw_reconciliation_earning_info.prime_earning_id order by cw_reconciliation_earning_info.earning_order')");
$earning_info = $earning_query->result();
$earning_query->next_result();
foreach($earning_info as $send_rlst){
$prime_earning_id = $send_rlst->prime_earning_id;
$prime_reconciliation_id = $send_rlst->prime_reconciliation_id;
$earning_column = $send_rlst->earning_column;
$display_name = $send_rlst->display_name;
$earning_order = $send_rlst->earning_order;
$tble_line .= " <tr>
<td>$earning_column</td>
<td>$display_name</td>
<td>$earning_order</td>
<td>
<a class='btn btn-edit btn-xs row_btn' onclick='edit_earning_info($prime_earning_id)'>Edit</a>
<a class='btn btn-danger btn-xs row_btn' onclick='delete_earning_info($prime_earning_id,$prime_reconciliation_id)'>Delete</a>
</td>
</tr>";
}
}
$table_data = " <h4 class='m-t-0 header-title'><b>Earning Information List</b></h4>
<table class='table table-striped table-bordered' id='deduction_info_table'>
<thead>
<tr>
<th>Column Name</th>
<th>Display Name</th>
<th>Order</th>
<th>Action</th>
</tr>
</thead>
<tbody>
$tble_line
</tbody>
</table>";
return json_encode(array("success" => TRUE,'earning_info' => $table_data));
}
//LIST MATERIAL REQUEST INFOMATION
public function get_deduct_info($prime_reconciliation_id = -1){
if((int)$this->input->post('prime_reconciliation_id') > 0){
$prime_reconciliation_id = $this->input->post('prime_reconciliation_id');
}
$prime_reconciliation_id = (int)$prime_reconciliation_id;
$tble_line = '';
if($this->check_reconciliation_exists((int)$prime_reconciliation_id,TRUE)){
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_deduct_id,prime_reconciliation_id,display_name,deduct_order,cw_form_setting.view_name as deduct_column from cw_reconciliation_deduct_info inner join cw_form_setting on cw_form_setting.label_name = cw_reconciliation_deduct_info.deduct_column where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_deduct_info.trans_status = 1 group by cw_reconciliation_deduct_info.prime_deduct_id order by cw_reconciliation_deduct_info.deduct_order')");
$deduct_info = $deduct_query->result();
$deduct_query->next_result();
foreach($deduct_info as $send_rlst){
$prime_deduct_id = $send_rlst->prime_deduct_id;
$prime_reconciliation_id = $send_rlst->prime_reconciliation_id;
$deduct_column = $send_rlst->deduct_column;
$display_name = $send_rlst->display_name;
$deduct_order = $send_rlst->deduct_order;
$tble_line .= " <tr>
<td>$deduct_column</td>
<td>$display_name</td>
<td>$deduct_order</td>
<td>
<a class='btn btn-edit btn-xs row_btn' onclick='edit_deduct_info($prime_deduct_id)'>Edit</a>
<a class='btn btn-danger btn-xs row_btn' onclick='delete_deduct_info($prime_deduct_id,$prime_reconciliation_id)'>Delete</a>
</td>
</tr>";
}
}
$table_data = " <h4 class='m-t-0 header-title'><b>Deduction Information List</b></h4>
<table class='table table-striped table-bordered' id='deduction_info_table'>
<thead>
<tr>
<th>Column Name</th>
<th>Display Name</th>
<th>Order</th>
<th>Action</th>
</tr>
</thead>
<tbody>
$tble_line
</tbody>
</table>";
return json_encode(array("success" => TRUE,'deduct_info' => $table_data));
}
//CHECK DETAILED RECONCILIATION
public function check_reconciliation_exists($prime_reconciliation_id){
$prime_reconciliation_id = (int)$prime_reconciliation_id;
$recon_query = $this->db->query("CALL sp_a_run ('SELECT','select * from cw_detailed_reconciliation where cw_detailed_reconciliation.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_detailed_reconciliation.trans_status = 1')");
$num = $recon_query->num_rows();
$recon_query->next_result();
return ((int)$num === 1);
}
//CHECK EARNING ALREADY EXISTS
public function check_earning_already_exists($earning_column,$prime_reconciliation_id){
$prime_reconciliation_id = (int)$prime_reconciliation_id;
$check_earning = $this->db->query("CALL sp_a_run ('SELECT','select * from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.earning_column = \"$earning_column\" and cw_reconciliation_earning_info.trans_status = 1')");
$num = $check_earning->num_rows();
$check_earning->next_result();
if((int)$num === 1){
return TRUE;
}else{
return FALSE;
}
}
//CHECK DEDUCTION ALREADY EXISTS
public function check_deduct_already_exists($deduct_column,$prime_reconciliation_id){
$prime_reconciliation_id = (int)$prime_reconciliation_id;
$check_deduct = $this->db->query("CALL sp_a_run ('SELECT','select * from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_deduct_info.deduct_column = \"$deduct_column\" and cw_reconciliation_deduct_info.trans_status = 1')");
$num = $check_deduct->num_rows();
$check_deduct->next_result();
if((int)$num === 1){
return TRUE;
}else{
return FALSE;
}
}
//GET TABLE VIEW FOR EARNING INFORMATION TABLE
public function get_earning_info_list_table(){
$prime_reconciliation_id = '';
if((int)$this->input->post('prime_reconciliation_id') > 0){
$prime_reconciliation_id = $this->input->post('prime_reconciliation_id');
}
$send_data = json_decode($this->get_earning_info($prime_reconciliation_id));
echo json_encode(array("success" => TRUE,'earning_table' => $send_data->earning_info));
}
//GET TABLE VIEW FOR DEDUCTION INFORMATION TABLE
public function get_deduct_info_list_table(){
$prime_reconciliation_id = '';
if((int)$this->input->post('prime_reconciliation_id') > 0){
$prime_reconciliation_id = $this->input->post('prime_reconciliation_id');
}
$send_data = json_decode($this->get_deduct_info($prime_reconciliation_id));
echo json_encode(array("success" => TRUE,'deduct_table' => $send_data->deduct_info));
}
//EDIT EARNING INFOMATION
public function edit_earning_info(){
$prime_earning_id = (int)$this->input->post('prime_earning_id');
$earing_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_earning_id,prime_reconciliation_id,earning_column,display_name,earning_order from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_earning_id = \"$prime_earning_id\" and cw_reconciliation_earning_info.trans_status = 1')");
$return_data = $earing_query->result();
$earing_query->next_result();
echo json_encode(array("success" => TRUE,'earning_edit_info' => $return_data));
}
//DELETE EARNING INFOMATION
public function delete_earning_info(){
$prime_earning_id = (int)$this->input->post('prime_earning_id');
$prime_reconciliation_id = (int)$this->input->post('prime_reconciliation_id');
$created_on = date("Y-m-d H:i:s");
$delete_earning = 'trans_deleted_by = "'. $this->session->userdata('logged_id') .'",trans_deleted_date = "'.$created_on.'",trans_status = 0';
$delete_earning_query = 'UPDATE cw_reconciliation_earning_info SET '. $delete_earning .' WHERE cw_reconciliation_earning_info.prime_earning_id = "'. $prime_earning_id .'"';
if($this->db->query("CALL sp_a_run ('UPDATE','$delete_earning_query')")){
$earing_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_earning_id,prime_reconciliation_id,earning_order from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.trans_status = 1')");
$earning_edit_info = $earing_query->result();
$earing_query->next_result();
$order = 1;
foreach($earning_edit_info as $result){
$prime_earning_id = $result->prime_earning_id;
$prime_reconciliation_id = $result->prime_reconciliation_id;
$created_on = date("Y-m-d H:i:s");
$update_earning = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",earning_order = "'.$order.'"';
$update_earning_query = 'UPDATE cw_reconciliation_earning_info SET '. $update_earning .' WHERE cw_reconciliation_earning_info.prime_earning_id = "'. $prime_earning_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
$order++;
}
echo json_encode(array("success" => TRUE,'earning_delete_info' => 'Deleted successfully'));
}else{
echo json_encode(array("success" => False,'earning_delete_info' => "Please Try Again"));
}
}
//EDIT EARNING INFOMATION
public function edit_deduct_info(){
$prime_deduct_id = (int)$this->input->post('prime_deduct_id');
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_deduct_id,prime_reconciliation_id,deduct_column,display_name,deduct_order from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_deduct_id = \"$prime_deduct_id\" and cw_reconciliation_deduct_info.trans_status = 1')");
$return_data = $deduct_query->result();
$deduct_query->next_result();
echo json_encode(array("success" => TRUE,'deduct_edit_info' => $return_data));
}
//DELETE DEDUCTION INFOMATION
public function delete_deduct_info(){
$prime_deduct_id = (int)$this->input->post('prime_deduct_id');
$prime_reconciliation_id = (int)$this->input->post('prime_reconciliation_id');
$created_on = date("Y-m-d H:i:s");
$delete_deduct = 'trans_deleted_by = "'. $this->session->userdata('logged_id') .'",trans_deleted_date = "'.$created_on.'",trans_status = 0';
$delete_deduct_query = 'UPDATE cw_reconciliation_deduct_info SET '. $delete_deduct .' WHERE cw_reconciliation_deduct_info.prime_deduct_id = "'. $prime_deduct_id .'"';
if($this->db->query("CALL sp_a_run ('UPDATE','$delete_deduct_query')")){
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_deduct_id,prime_reconciliation_id,deduct_order from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_deduct_info.trans_status = 1')");
$deduct_edit_info = $deduct_query->result();
$deduct_query->next_result();
$order = 1;
foreach($deduct_edit_info as $result){
$prime_deduct_id = $result->prime_deduct_id;
$prime_reconciliation_id = $result->prime_reconciliation_id;
$created_on = date("Y-m-d H:i:s");
$update_deduct = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",deduct_order = "'.$order.'"';
$update_deduct_query = 'UPDATE cw_reconciliation_deduct_info SET '. $update_deduct .' WHERE cw_reconciliation_deduct_info.prime_deduct_id = "'. $prime_deduct_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_deduct_query')");
$order++;
}
echo json_encode(array("success" => TRUE,'deduct_delete_info' => 'Deleted successfully'));
}else{
echo json_encode(array("success" => False,'deduct_delete_info' => "Please Try Again"));
}
}
//CHANGING DEDUCT ORDER
public function change_deduct_order($deduct_order,$prime_reconciliation_id,$prime_deduct_id = 0){
$deduct_order = (int)$deduct_order;
$prime_reconciliation_id = (int)$prime_reconciliation_id;
$prime_deduct_id = (int)$prime_deduct_id;
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_deduct_id,prime_reconciliation_id,deduct_order from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_deduct_info.trans_status = 1')");
$num = $deduct_query->num_rows();
$deduct_query->next_result();
$update_data = array();
if((int)$prime_deduct_id === 0){
if((int)$deduct_order < (int)$num){
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_deduct_id,prime_reconciliation_id,deduct_order from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_deduct_info.deduct_order >= \"$deduct_order\" and cw_reconciliation_deduct_info.trans_status = 1 order by cw_reconciliation_deduct_info.deduct_order')");
$editing_info = $deduct_query->result();
$deduct_query->next_result();
$order = $deduct_order;
$update_column = '';
$update_count = 0;
foreach($editing_info as $result){
$previous_order = $result->deduct_order;
if((int)$previous_order >= (int)$deduct_order){
$order++;
$prime_deduct_id = $result->prime_deduct_id;
$prime_reconciliation_id = $result->prime_reconciliation_id;
$created_on = date("Y-m-d H:i:s");
$update_column .= '("'.$prime_deduct_id.'","'.$this->session->userdata('logged_id').'","'.$created_on.'","'.$order.'"),';
$update_count++;
}
}
if($update_count > 0){
$update_column = rtrim($update_column,",");
$update_earning_query = "INSERT INTO cw_reconciliation_deduct_info (prime_deduct_id,trans_updated_by,trans_updated_date,deduct_order) VALUES $update_column ON DUPLICATE KEY UPDATE trans_updated_by=VALUES(trans_updated_by),trans_updated_date=VALUES(trans_updated_date),deduct_order=VALUES(deduct_order)";
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
}
if((int)$deduct_order != 0){
return $deduct_order;
}else{
return 1;
}
}else
if((int)$num === 0){
return 1;
}else
if((int)$deduct_order > (int)$num){
$num++;
return (int)$num;
}else{
$update_data = array();
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_deduct_id,prime_reconciliation_id,deduct_order from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_deduct_info.deduct_order = \"$deduct_order\" and cw_reconciliation_deduct_info.trans_status = 1')");
$rslt = $deduct_query->row();
$deduct_query->next_result();
$prime_deduct_id = $rslt->prime_deduct_id;
$data_order = $rslt->deduct_order + 1;
$created_on = date("Y-m-d H:i:s");
$update_deduct = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",deduct_order = "'.$data_order.'"';
$update_deduct_query = 'UPDATE cw_reconciliation_deduct_info SET '. $update_deduct .' WHERE cw_reconciliation_deduct_info.prime_deduct_id = "'. $prime_deduct_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_deduct_query')");
return $num;
}
}else{
$update_data = array();
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_deduct_id,prime_reconciliation_id,deduct_order from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_deduct_info.prime_deduct_id = \"$prime_deduct_id\" and cw_reconciliation_deduct_info.trans_status = 1')");
$rlst = $deduct_query->row();
$deduct_query->next_result();
$check_order = $rlst->deduct_order;
if((int)$deduct_order === 0){
$deduct_order = 1;
}
if((int)$check_order === (int)$deduct_order){
return $deduct_order;
}else
if((int)$deduct_order > (int)$num){
return $check_order;
}else
if((int)$deduct_order === 1){
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_deduct_id,prime_reconciliation_id,deduct_order from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_deduct_info.deduct_order >= \"$deduct_order\" and cw_reconciliation_deduct_info.prime_deduct_id != \"$prime_deduct_id\" and cw_reconciliation_deduct_info.trans_status = 1')");
$deduct_edit_info = $deduct_query->result();
$deduct_query->next_result();
$order = 1;
$update_column = '';
foreach($deduct_edit_info as $result){
$order++;
$prime_deduct_id = $result->prime_deduct_id;
$prime_reconciliation_id = $result->prime_reconciliation_id;
$created_on = date("Y-m-d H:i:s");
$update_column .= '("'.$prime_deduct_id.'","'.$this->session->userdata('logged_id').'","'.$created_on.'","'.$order.'"),';
}
$update_column = rtrim($update_column,",");
$update_earning_query = "INSERT INTO cw_reconciliation_deduct_info (prime_deduct_id,trans_updated_by,trans_updated_date,deduct_order) VALUES $update_column ON DUPLICATE KEY UPDATE trans_updated_by=VALUES(trans_updated_by),trans_updated_date=VALUES(trans_updated_date),deduct_order=VALUES(deduct_order)";
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
if((int)$deduct_order <= (int)$num){
return $deduct_order;
}else{
return $num;
}
}else
if(((int)$deduct_order === (int)$check_order+1) || ((int)$deduct_order === (int)$check_order-1)){
$created_on = date("Y-m-d H:i:s");
$update_deduct = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",deduct_order = "'.$check_order.'"';
$update_deduct_query = 'UPDATE cw_reconciliation_deduct_info SET '. $update_deduct .' WHERE cw_reconciliation_deduct_info.deduct_order = "'. $deduct_order .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_deduct_query')");
return (int)$deduct_order;
}else
if((int)$deduct_order < (int)$check_order){
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_deduct_id,prime_reconciliation_id,deduct_order from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_deduct_info.deduct_order < \"$check_order\" and cw_reconciliation_deduct_info.deduct_order >= \"$deduct_order\" and cw_reconciliation_deduct_info.trans_status = 1')");
$deduct_edit_info = $deduct_query->result();
$deduct_query->next_result();
$update_column = '';
foreach($deduct_edit_info as $result){
$order = $result->deduct_order+1;
$prime_deduct_id = $result->prime_deduct_id;
$created_on = date("Y-m-d H:i:s");
$update_column .= '("'.$prime_deduct_id.'","'.$this->session->userdata('logged_id').'","'.$created_on.'","'.$order.'"),';
}
$update_column = rtrim($update_column,",");
$update_earning_query = "INSERT INTO cw_reconciliation_deduct_info (prime_deduct_id,trans_updated_by,trans_updated_date,deduct_order) VALUES $update_column ON DUPLICATE KEY UPDATE trans_updated_by=VALUES(trans_updated_by),trans_updated_date=VALUES(trans_updated_date),deduct_order=VALUES(deduct_order)";
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
return $deduct_order;
}else
if((int)$deduct_order > (int)$check_order){
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_deduct_id,prime_reconciliation_id,deduct_order from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_deduct_info.deduct_order > \"$check_order\" and cw_reconciliation_deduct_info.deduct_order <= \"$deduct_order\" and cw_reconciliation_deduct_info.trans_status = 1')");
$deduct_edit_info = $deduct_query->result();
$deduct_query->next_result();
$previous_order = $check_order;
$update_column = '';
foreach($deduct_edit_info as $result){
$prime_deduct_id = $result->prime_deduct_id;
$prime_reconciliation_id = $result->prime_reconciliation_id;
$created_on = date("Y-m-d H:i:s");
$update_column .= '("'.$prime_deduct_id.'","'.$this->session->userdata('logged_id').'","'.$created_on.'","'.$previous_order.'"),';
$previous_order++;
}
$update_column = rtrim($update_column,",");
$update_earning_query = "INSERT INTO cw_reconciliation_deduct_info (prime_deduct_id,trans_updated_by,trans_updated_date,deduct_order) VALUES $update_column ON DUPLICATE KEY UPDATE trans_updated_by=VALUES(trans_updated_by),trans_updated_date=VALUES(trans_updated_date),deduct_order=VALUES(deduct_order)";
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
return (int)$deduct_order;
}
}
}
//CHANGING EARNING ORDER
public function change_earning_order($earning_order,$prime_reconciliation_id,$prime_earning_id = 0){
$earning_order = (int)$earning_order;
$prime_reconciliation_id = (int)$prime_reconciliation_id;
$prime_earning_id = (int)$prime_earning_id;
$earing_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_earning_id,prime_reconciliation_id,earning_order from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.trans_status = 1')");
$num = $earing_query->num_rows();
$earing_query->next_result();
if((int)$prime_earning_id === 0){
$update_data = array();
if((int)$earning_order < (int)$num){
$earing_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_earning_id,prime_reconciliation_id,earning_order from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.earning_order >= \"$earning_order\" and cw_reconciliation_earning_info.trans_status = 1')");
$earning_edit_info = $earing_query->result();
$earing_query->next_result();
$order = $earning_order;
$update_column = '';
$update_count = 0;
foreach($earning_edit_info as $result){
$previous_order = $result->earning_order;
if((int)$previous_order >= (int)$earning_order){
$order++;
$prime_earning_id = $result->prime_earning_id;
$prime_reconciliation_id = $result->prime_reconciliation_id;
$created_on = date("Y-m-d H:i:s");
$update_column .= '("'.$prime_earning_id.'","'.$this->session->userdata('logged_id').'","'.$created_on.'","'.$order.'"),';
$update_count++;
}
}
if($update_count > 0){
$update_column = rtrim($update_column,",");
$update_earning_query = "INSERT INTO cw_reconciliation_earning_info (prime_earning_id,trans_updated_by,trans_updated_date,earning_order) VALUES $update_column ON DUPLICATE KEY UPDATE trans_updated_by=VALUES(trans_updated_by),trans_updated_date=VALUES(trans_updated_date),earning_order=VALUES(earning_order)";
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
}
if((int)$earning_order != 0){
return $earning_order;
}else{
return 1;
}
}else
if((int)$num === 0){
return 1;
}else
if((int)$earning_order > (int)$num){
$num++;
return (int)$num;
}else{
$update_data = array();
$earing_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_earning_id,prime_reconciliation_id,earning_order from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.earning_order = \"$earning_order\" and cw_reconciliation_earning_info.trans_status = 1')");
$rslt = $earing_query->row();
$earing_query->next_result();
$prime_earning_id = $rslt->prime_earning_id;
$data_order = $rslt->earning_order + 1;
$created_on = date("Y-m-d H:i:s");
$update_earning = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",earning_order = "'.$data_order.'"';
$update_earning_query = 'UPDATE cw_reconciliation_earning_info SET '. $update_earning .' WHERE cw_reconciliation_earning_info.prime_earning_id = "'. $prime_earning_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
return $num;
}
}else{
$update_data = array();
$earing_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_earning_id,prime_reconciliation_id,earning_order from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.prime_earning_id = \"$prime_earning_id\" and cw_reconciliation_earning_info.trans_status = 1')");
$rlst = $earing_query->row();
$earing_query->next_result();
$check_order = $rlst->earning_order;
if((int)$earning_order === 0){
$earning_order = 1;
}
if((int)$check_order === (int)$earning_order){
return $earning_order;
}else
if((int)$earning_order > (int)$num){
return $check_order;
}else
if((int)$earning_order === 1){
$earing_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_earning_id,prime_reconciliation_id,earning_order from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.earning_order >= 1 and cw_reconciliation_earning_info.prime_earning_id != \"$prime_earning_id\" and cw_reconciliation_earning_info.trans_status = 1')");
$earning_edit_info = $earing_query->result();
$earing_query->next_result();
$order = 1;
$update_column = '';
foreach($earning_edit_info as $result){
$order++;
$prime_earning_id = $result->prime_earning_id;
$prime_reconciliation_id = $result->prime_reconciliation_id;
$created_on = date("Y-m-d H:i:s");
$update_column .= '("'.$prime_earning_id.'","'.$this->session->userdata('logged_id').'","'.$created_on.'","'.$order.'"),';
}
$update_column = rtrim($update_column,",");
$update_earning_query = "INSERT INTO cw_reconciliation_earning_info (prime_earning_id,trans_updated_by,trans_updated_date,earning_order) VALUES $update_column ON DUPLICATE KEY UPDATE trans_updated_by=VALUES(trans_updated_by),trans_updated_date=VALUES(trans_updated_date),earning_order=VALUES(earning_order)";
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
return (int)$earning_order;
}else
if(((int)$earning_order === (int)$check_order+1) || ((int)$earning_order === (int)$check_order-1)){
$created_on = date("Y-m-d H:i:s");
$update_earning = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",earning_order = "'.$check_order.'"';
$update_earning_query = 'UPDATE cw_reconciliation_earning_info SET '. $update_earning .' WHERE cw_reconciliation_earning_info.earning_order = "'. $earning_order .'" and cw_reconciliation_earning_info.prime_reconciliation_id = "'. $prime_reconciliation_id .'" ';
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
return (int)$earning_order;
}else
if((int)$earning_order < (int)$check_order){
$earing_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_earning_id,prime_reconciliation_id,earning_order from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.earning_order < \"$check_order\" and cw_reconciliation_earning_info.earning_order >= \"$earning_order\" and cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.trans_status = 1')");
$earning_edit_info = $earing_query->result();
$earing_query->next_result();
$update_column = '';
foreach($earning_edit_info as $result){
$order = $result->earning_order+1;
$prime_earning_id = $result->prime_earning_id;
$created_on = date("Y-m-d H:i:s");
$update_column .= '("'.$prime_earning_id.'","'.$this->session->userdata('logged_id').'","'.$created_on.'","'.$order.'"),';
}
$update_column = rtrim($update_column,",");
$update_earning_query = "INSERT INTO cw_reconciliation_earning_info (prime_earning_id,trans_updated_by,trans_updated_date,earning_order) VALUES $update_column ON DUPLICATE KEY UPDATE trans_updated_by=VALUES(trans_updated_by),trans_updated_date=VALUES(trans_updated_date),earning_order=VALUES(earning_order)";
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
return (int)$earning_order;
}else
if((int)$earning_order > (int)$check_order){
$earing_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_earning_id,prime_reconciliation_id,earning_order from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.earning_order > \"$check_order\" and cw_reconciliation_earning_info.earning_order <= \"$earning_order\" and cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_reconciliation_earning_info.trans_status = 1')");
$earning_edit_info = $earing_query->result();
$earing_query->next_result();
$previous_order = $check_order;
$update_column = '';
foreach($earning_edit_info as $result){
$prime_earning_id = $result->prime_earning_id;
$prime_reconciliation_id = $result->prime_reconciliation_id;
$created_on = date("Y-m-d H:i:s");
$update_column .= '("'.$prime_earning_id.'","'.$this->session->userdata('logged_id').'","'.$created_on.'","'.$previous_order.'"),';
$previous_order++;
}
$update_column = rtrim($update_column,",");
$update_earning_query = "INSERT INTO cw_reconciliation_earning_info (prime_earning_id,trans_updated_by,trans_updated_date,earning_order) VALUES $update_column ON DUPLICATE KEY UPDATE trans_updated_by=VALUES(trans_updated_by),trans_updated_date=VALUES(trans_updated_date),earning_order=VALUES(earning_order)";
$this->db->query("CALL sp_a_run ('UPDATE','$update_earning_query')");
return (int)$earning_order;
}
}
}
//CHECK DEDUCTION ORDER NUMBER
public function check_deduct_order_num(){
$prime_deduct_reconciliation_id = (int)$this->input->post('prime_deduct_reconciliation_id');
$deduct_order = (int)$this->input->post('deduct_order');
$deduct_query = $this->db->query("CALL sp_a_run ('SELECT','select deduct_order from cw_reconciliation_deduct_info where cw_reconciliation_deduct_info.prime_reconciliation_id = \"$prime_deduct_reconciliation_id\" and cw_reconciliation_deduct_info.trans_status = 1')");
$num_rows = $deduct_query->num_rows();
$deduct_query->next_result();
if(((int)$deduct_order <= $num_rows) && ($deduct_order != 0)){
echo json_encode(array("success" => True,'message' => "Process success",'deduct_order' => $deduct_order));
}else
if((int)$deduct_order === 0 || (int)$num_rows === 0){
echo json_encode(array("success" => True,'message' => "Process success",'deduct_order' => 1));
}else
if((int)$deduct_order > (int)$num_rows){
echo json_encode(array("success" => True,'message' => "Order should be less than or equal to No of rows",'deduct_order' => $num_rows+1));
}
}
//CHECK EARNING ORDER NUMBER
public function check_earning_order_num(){
$prime_deduct_reconciliation_id = (int)$this->input->post('prime_deduct_reconciliation_id');
$earning_order = (int)$this->input->post('earning_order');
$earing_query = $this->db->query("CALL sp_a_run ('SELECT','select earning_order from cw_reconciliation_earning_info where cw_reconciliation_earning_info.prime_reconciliation_id = \"$prime_deduct_reconciliation_id\" and cw_reconciliation_earning_info.trans_status = 1')");
$num = $earing_query->num_rows();
$earing_query->next_result();
if(((int)$earning_order <= $num) && ($earning_order != 0)){
echo json_encode(array("success" => True,'message' => "Process success",'earning_order' => $earning_order));
}else
if((int)$earning_order === 0 || (int)$num === 0){
echo json_encode(array("success" => True,'message' => "Process success",'earning_order' => 1));
}else
if((int)$earning_order > (int)$num){
echo json_encode(array("success" => True,'message' => "Process success",'earning_order' => $num+1));
}
}
//GENERATE EXCELS
public function generate_excel($prime_reconciliation_id = -1){
$filter_info = array();
$this->session->set_userdata('filter_info', $filter_info);
$prime_reconciliation_id = (int)$prime_reconciliation_id;
$reconciliation_query = $this->db->query("CALL sp_a_run ('SELECT','select prime_reconciliation_id,setting_name from cw_detailed_reconciliation where cw_detailed_reconciliation.prime_reconciliation_id = \"$prime_reconciliation_id\" and cw_detailed_reconciliation.trans_status = 1')");
$reconciliation_info = $reconciliation_query->row();
$reconciliation_query->next_result();
$data['reconciliation_info'] = $reconciliation_info;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
$category_list[1] = "All Category";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$from_query = 'select * from cw_form_setting where prime_module_id = "employees" and field_show = "1" and field_type in (1,4,5,7) ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
$fliter_list = $this->get_filter_data($form_result);
$data['fliter_list'] = $fliter_list;
$group_query = 'select label_name,view_name from cw_form_setting where prime_module_id = "employees" and field_show = "1" and input_view_type !=3 and field_type in (5,7) ORDER BY input_for,field_sort asc';
$group_info = $this->db->query("CALL sp_a_run ('SELECT','$group_query')");
$group_result = $group_info->result();
$group_info->next_result();
$group_by[""] = "---- Select Group ----";
foreach($group_result as $rlst){
$col_name = "cw_employees.".$rlst->label_name;
$view_name = $rlst->view_name;
$group_by[$col_name] = $view_name;
}
$data['group_by'] = $group_by;
$this->load->view("$this->control_name/generate_excel",$data);
}
//PROVIDE QUERY AND DROPDOWN VALUES
public function get_filter_data($form_result){
$filter = array();
foreach($form_result as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$field_isdefault = (int)$setting->field_isdefault;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_field;
$search_show = (int)$setting->search_show;
$array_list = array();
if($label_id != 'role' && $label_id != 'employee_code' && $label_id != 'emp_name'){
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($pick_table == "cw_category"){
$qry = " and prime_category_id != 1";
}else{
$qry = "";
}
if($pick_table == "cw_payroll_formula"){
$pick_query = "select $pick_list from $pick_table where trans_status = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = ucwords(str_replace("_"," ",$pick->$pick_list_val_2));
$array_list[$pick_key] = $pick_val;
}
}else{
if($label_id === "excemption_component"){
$pick_query = "select $pick_list from $pick_table where trans_status = 1 and tax_section = 1 $qry";
}else{
$pick_query = "select $pick_list from $pick_table where trans_status = 1 $qry";
}
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
}
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
}
}
if(($input_view_type === 1) || ($input_view_type === 2)){
$filter[] = array('label_id'=> $label_id, 'field_isdefault'=> $field_isdefault, 'array_list'=> $array_list, 'field_type'=> $field_type);
}
}
}
return $filter;
}
public function generate_excel_sheets($prime_reconciliation_id,$process_month,$category,$add_salary = -1){
$pre_month = "01-".$process_month;
$pre_month = date("m-Y", strtotime( date( "Y-m-d", strtotime( $pre_month ) ) . "-1 month" ) );
$caty = '';
if((int)$category === 1){
$caty = '';
}else{
$caty = "and cw_employees.role = $category";
}
if((int)$add_salary === 1){
$salary_info = ",IFNULL(cw_transactions.total_earnings,0) as gross_earn,IFNULL(cw_transactions.total_deductions,0) as gross_deduct,IFNULL(cw_transactions.net_pay,0) as net_pay";
$pre_salary_info = ",IFNULL(cw_transactions.total_earnings,0) as pre_gross_earn,IFNULL(cw_transactions.total_deductions,0) as pre_gross_deduct,IFNULL(cw_transactions.net_pay,0) as pre_net_pay";
$sum_salary = ",IFNULL(SUM(cw_transactions.total_earnings),0) as sum_gross_earn,IFNULL(SUM(cw_transactions.total_deductions),0) as sum_gross_deduct,IFNULL(SUM(cw_transactions.net_pay),0) as sum_net_pay";
$pre_sum_salary = ",IFNULL(SUM(cw_transactions.total_earnings),0) as pre_sum_gross_earn,IFNULL(SUM(cw_transactions.total_deductions),0) as pre_sum_gross_deduct,IFNULL(SUM(cw_transactions.net_pay),0) as pre_sum_net_pay";
}else{
$salary_info = "";
$pre_salary_info = "";
$sum_salary = "";
$pre_sum_salary = "";
}
$filter_info = $this->session->userdata('filter_info');
$this->session->unset_userdata('filter_info');
if($filter_info['filter_query']){
$filter_query = $filter_info['filter_query'];
}else{
$filter_query = '';
}
if($filter_info['group_by']){
$group_by = $filter_info['group_by'];
}else{
$group_by = 'group by cw_employees.employee_code';
}
//DEDUCTION INFORMATION
$deduction_details = $this->db->query("CALL sp_a_run ('SELECT','select cw_detailed_reconciliation.setting_name,IFNULL(GROUP_CONCAT(CONCAT(\"IFNULL(replace_info.\", cw_reconciliation_deduct_info.deduct_column,\",0) as \",cw_reconciliation_deduct_info.deduct_column) order by cw_reconciliation_deduct_info.deduct_order),0) as deduct_column,IFNULL(GROUP_CONCAT(CONCAT(\"IFNULL(replace_info.\", cw_reconciliation_deduct_info.deduct_column,\",0) as pre_\",cw_reconciliation_deduct_info.deduct_column) order by cw_reconciliation_deduct_info.deduct_order),0) as pre_deduct_column,IFNULL(GROUP_CONCAT(CONCAT(\"IFNULL(SUM(replace_info.\", cw_reconciliation_deduct_info.deduct_column,\"),0) as sum_\",cw_reconciliation_deduct_info.deduct_column) order by cw_reconciliation_deduct_info.deduct_order),0) as sum_deduct_column,IFNULL(GROUP_CONCAT(CONCAT(\"IFNULL(SUM(replace_info.\", cw_reconciliation_deduct_info.deduct_column,\"),0) as pre_sum_\",cw_reconciliation_deduct_info.deduct_column) order by cw_reconciliation_deduct_info.deduct_order),0) as pre_sum_deduct_column,IFNULL(GROUP_CONCAT(cw_reconciliation_deduct_info.deduct_order),0) as deduct_order,GROUP_CONCAT(cw_reconciliation_deduct_info.deduct_column order by cw_reconciliation_deduct_info.deduct_order) as get_deduct_column,GROUP_CONCAT(CONCAT(\"sum_\",cw_reconciliation_deduct_info.deduct_column) order by cw_reconciliation_deduct_info.deduct_order) as get_sum_deduct_column,GROUP_CONCAT(cw_reconciliation_deduct_info.display_name order by cw_reconciliation_deduct_info.deduct_order) as display_name from cw_detailed_reconciliation inner join cw_reconciliation_deduct_info on cw_reconciliation_deduct_info.prime_reconciliation_id = cw_detailed_reconciliation.prime_reconciliation_id where cw_detailed_reconciliation.prime_reconciliation_id = $prime_reconciliation_id and cw_detailed_reconciliation.trans_status = 1 and cw_reconciliation_deduct_info.trans_status = 1 ')");
$deduction_rlst = $deduction_details->row();
$deduction_details->next_result();
$deduct_column = str_replace("replace_info","cw_transactions",$deduction_rlst->deduct_column);
$pre_deduct_column = str_replace("replace_info","cw_transactions",$deduction_rlst->pre_deduct_column);
$sum_deduct_column = str_replace("replace_info","cw_transactions",$deduction_rlst->sum_deduct_column);
$pre_sum_deduct_column = str_replace("replace_info","cw_transactions",$deduction_rlst->pre_sum_deduct_column);
$get_deduct_column = explode(',',$deduction_rlst->get_deduct_column);
$get_sum_deduct_column = explode(',',$deduction_rlst->get_sum_deduct_column);
$deduct_order = $deduction_rlst->deduct_order;
$setting_name = $deduction_rlst->setting_name;
$deduct_display_name = explode(',',$deduction_rlst->display_name);
//EARNING INFORMATION
$earning_details = $this->db->query("CALL sp_a_run ('SELECT','select cw_detailed_reconciliation.setting_name,IFNULL(GROUP_CONCAT(CONCAT(\"replace_info.\",cw_reconciliation_earning_info.earning_column) order by cw_reconciliation_earning_info.earning_order),0) as earning_column,IFNULL(GROUP_CONCAT(CONCAT(\"replace_info.\",cw_reconciliation_earning_info.earning_column,\" as pre_\",cw_reconciliation_earning_info.earning_column) order by cw_reconciliation_earning_info.earning_order),0) as pre_earning_column,IFNULL(GROUP_CONCAT(CONCAT(\"IFNULL(SUM(replace_info.\",cw_reconciliation_earning_info.earning_column,\"),0) as sum_\",cw_reconciliation_earning_info.earning_column) order by cw_reconciliation_earning_info.earning_order),0) as sum_earning_column,IFNULL(GROUP_CONCAT(CONCAT(\"IFNULL(SUM(replace_info.\",cw_reconciliation_earning_info.earning_column,\"),0) as pre_sum_\",cw_reconciliation_earning_info.earning_column) order by cw_reconciliation_earning_info.earning_order),0) as pre_sum_earning_column,IFNULL(GROUP_CONCAT(cw_reconciliation_earning_info.earning_order order by cw_reconciliation_earning_info.earning_order),0) as earning_order ,GROUP_CONCAT(cw_reconciliation_earning_info.earning_column order by cw_reconciliation_earning_info.earning_order) as get_earning_column,GROUP_CONCAT(CONCAT(\"sum_\",cw_reconciliation_earning_info.earning_column) order by cw_reconciliation_earning_info.earning_order) as get_sum_earning_column,GROUP_CONCAT(cw_reconciliation_earning_info.display_name order by cw_reconciliation_earning_info.earning_order) as display_name from cw_detailed_reconciliation inner join cw_reconciliation_earning_info on cw_reconciliation_earning_info.prime_reconciliation_id = cw_detailed_reconciliation.prime_reconciliation_id where cw_detailed_reconciliation.prime_reconciliation_id = $prime_reconciliation_id and cw_detailed_reconciliation.trans_status = 1 and cw_reconciliation_earning_info.trans_status = 1')");
$earning_rlst = $earning_details->row();
$earning_details->next_result();
$earning_column = str_replace("replace_info","cw_transactions",$earning_rlst->earning_column);
$pre_earning_column = str_replace("replace_info","cw_transactions",$earning_rlst->pre_earning_column);
$sum_earning_column = str_replace("replace_info","cw_transactions",$earning_rlst->sum_earning_column);
$pre_sum_earning_column = str_replace("replace_info","cw_transactions",$earning_rlst->pre_sum_earning_column);
$get_earning_column = explode(',',$earning_rlst->get_earning_column);
$get_sum_earning_column = explode(',',$earning_rlst->get_sum_earning_column);
$earning_order = $earning_rlst->earning_order;
$earning_display_name = explode(',',$earning_rlst->display_name);
//COMMOM INFORMATION FROM EMPLOYEES AND TRANSCATION TABLES
$commom_details = $this->db->query("CALL sp_a_run ('SELECT','select cw_employees.employee_code,cw_employees.emp_name,$deduct_column,$earning_column $salary_info from cw_employees inner join cw_transactions on cw_transactions.employee_code = cw_employees.employee_code where cw_transactions.process_month = \"$process_month\" and cw_transactions.trans_status = 1 and cw_employees.trans_status = 1 and cw_employees.role != 1 $caty $filter_query $group_by order by cw_employees.employee_code ASC')");
$rlst_current_month = json_decode(json_encode($commom_details->result()),true);
$commom_details->next_result();
$commom_details = $this->db->query("CALL sp_a_run ('SELECT','select $sum_deduct_column,$sum_earning_column $sum_salary from cw_employees inner join cw_transactions on cw_transactions.employee_code = cw_employees.employee_code where cw_transactions.process_month = \"$process_month\" and cw_transactions.trans_status = 1 and cw_employees.trans_status = 1 and cw_employees.role != 1 $caty $filter_query')");
$sum_rlst_current_month = json_decode(json_encode($commom_details->result()),true);
$commom_details->next_result();
$commom_details = $this->db->query("CALL sp_a_run ('SELECT','select cw_employees.employee_code,cw_employees.emp_name,$pre_deduct_column,$pre_earning_column $pre_salary_info from cw_employees inner join cw_transactions on cw_transactions.employee_code = cw_employees.employee_code where cw_transactions.process_month = \"$pre_month\" and cw_transactions.trans_status = 1 and cw_employees.trans_status = 1 and cw_employees.role != 1 $caty $filter_query $group_by order by cw_employees.employee_code ASC')");
$rlst_pre_month = json_decode(json_encode($commom_details->result()),true);
$commom_details->next_result();
$commom_details = $this->db->query("CALL sp_a_run ('SELECT','select $pre_sum_deduct_column,$pre_sum_earning_column $pre_sum_salary from cw_employees inner join cw_transactions on cw_transactions.employee_code = cw_employees.employee_code where cw_transactions.process_month = \"$pre_month\" and cw_transactions.trans_status = 1 and cw_employees.trans_status = 1 and cw_employees.role != 1 $caty $filter_query')");
$sum_rlst_pre_month = json_decode(json_encode($commom_details->result()),true);
$commom_details->next_result();
if((int)count($rlst_pre_month) >= (int)count($rlst_current_month)){
$count_basic = count($rlst_pre_month);
}else{
$count_basic = count($rlst_current_month);
}
$return_array = array();
for($i=0;$i<$count_basic;$i++){
if(empty($rlst_current_month[$i])){
$rlst_current_month[$i] = array();
}
if(empty($rlst_pre_month[$i])){
$rlst_pre_month[$i] = array();
}
$result = array_merge($rlst_current_month[$i], $rlst_pre_month[$i]);
array_push($return_array,$result);
}
$basic = json_decode(json_encode($return_array));
if((int)count($sum_rlst_pre_month) >= (int)count($sum_rlst_current_month)){
$count_info = count($sum_rlst_pre_month);
}else{
$count_info = count($sum_rlst_current_month);
}
$return_array = array();
for($i=0;$i<$count_info;$i++){
if(empty($sum_rlst_current_month[$i])){
$sum_rlst_current_month[$i] = array();
}
if(empty($sum_rlst_pre_month[$i])){
$sum_rlst_pre_month[$i] = array();
}
$result = array_merge($sum_rlst_current_month[$i], $sum_rlst_pre_month[$i]);
array_push($return_array,$result);
}
$sum = json_decode(json_encode($return_array));
$deductions_variance = array();
$deduction_comparison = array();
$consolidated_deduction = array();
$earnings_variance = array();
$earnings_comparison = array();
$consolidated_earning = array();
$company_info = $this->db->query("CALL sp_a_run ('SELECT','select company_name,company_short_name,mobile_number from cw_company_information')");
$company_infomation = $company_info->row();
$company_info->next_result();
$company_name = ucwords($company_infomation->company_name);
$company_short_name = $company_infomation->company_short_name;
$mobile_number = $company_infomation->mobile_number;
$mon_info = date('F-y', strtotime( date( "Y-m-d", strtotime("01-".$process_month))));
$deductions_variance[0] = array("Deductions variance statement of for $mon_info");
$earnings_variance[0] = array("Earning variance statement of for $mon_info");
$consolidated_earning[0] = array("Consolidated Earning statement of for $mon_info");
$consolidated_deduction[0] = array("Consolidated Deduction statement of for $mon_info");
$earnings_comparison[0] = array("Earnings Comparison statement of $company_name for $mon_info");
$deduction_comparison[0] = array("Deduction Comparison statement of $company_name for $mon_info");
if((int)$add_salary === 1){
$salary[0] = array("Salary statement of for $mon_info");
}
$print_process_month = date('M-y', strtotime( date( "Y-m-d", strtotime("01-".$process_month))));
$pre_month = date('M-y',strtotime( date( "Y-m-d", strtotime("01-".$process_month)) . "-1 month" ));
$deductions_variance[1] = array('Employees Code','Name');
$earnings_variance[1] = array('Employees Code','Name');
$deduction_comparison[1] = array('Employees Code','Name');
$consolidated_deduction[1] = array('Loan',"$print_process_month Amount","$pre_month Amount","VARIANCE");
$consolidated_earning[1] = array('Consolidated Earning',"$print_process_month Amount","$pre_month Amount","VARIANCE");
$earnings_comparison[1] = array('Employees Code','Name');
if((int)$add_salary === 1){
$salary[1] = array('EMPLOYEES CODE','NAME',"$print_process_month GROSS EARNING","$pre_month GROSS EARNING","VARIANCE GROSS EARNING","$print_process_month GROSS DEDUCTION","$pre_month GROSS DEDUCTION","VARIANCE GROSS DEDUCTION","Amt Paid $print_process_month (NET PAY)","Amt Paid $pre_month (NET PAY)","Net Pay Variance");
}
foreach($deduct_display_name as $display_val){
$deductions_variance[1][] = $display_val;
$deduction_comparison[1][] = $print_process_month." - ".$display_val;
$deduction_comparison[1][] = $pre_month." - ".$display_val;
$deduction_comparison[1][] = "variance - ".$display_val;
}
foreach($earning_display_name as $display_earn_val){
$earnings_variance[1][] = $display_earn_val;
$earnings_comparison[1][] = $print_process_month." - ".$display_earn_val;
$earnings_comparison[1][] = $pre_month." - ".$display_earn_val;
$earnings_comparison[1][] = "variance - ".$display_earn_val;
}
$i = 2;
foreach($basic as $result_cur){
$j = 2;$a=2;$b=2;$c=2;
$employee_code = $result_cur->employee_code;
$emp_name = $result_cur->emp_name;
$deductions_variance[$i][] = " ".$employee_code;
$deductions_variance[$i][] = "$emp_name";
$earnings_variance[$i][] = " ".$employee_code;
$earnings_variance[$i][] = "$emp_name";
$deduction_comparison[$i][] = " ".$employee_code;
$deduction_comparison[$i][] = "$emp_name";
$earnings_comparison[$i][] = " ".$employee_code;
$earnings_comparison[$i][] = "$emp_name";
if((int)$add_salary === 1){
$gross_earn = (int)$result_cur->gross_earn;
$pre_gross_earn = (int)$result_cur->pre_gross_earn;
$var_gross_earn = (int)$gross_earn - $pre_gross_earn;
$gross_deduct = (int)$result_cur->gross_deduct;
$pre_gross_deduct = (int)$result_cur->pre_gross_deduct;
$var_gross_deduct = (int)$gross_deduct - $pre_gross_deduct;
$net_pay = (int)$result_cur->net_pay;
$pre_net_pay = (int)$result_cur->pre_net_pay;
$var_net_pay = (int)$net_pay - $pre_net_pay;
$salary[$i][] = " ".$employee_code;
$salary[$i][] = "$emp_name";
$salary[$i][] = "$gross_earn";
$salary[$i][] = "$pre_gross_earn";
$salary[$i][] = "$var_gross_earn";
$salary[$i][] = "$gross_deduct";
$salary[$i][] = "$pre_gross_deduct";
$salary[$i][] = "$var_gross_deduct";
$salary[$i][] = "$net_pay";
$salary[$i][] = "$pre_net_pay";
$salary[$i][] = "$var_net_pay";
}
foreach($get_deduct_column as $val){
$pre_val = "pre_".$val;
$val = $result_cur->$val;
$pre_val = $result_cur->$pre_val;
if(empty($val)){
$val = 0;
}
if(empty($pre_val)){
$pre_val = 0;
}
$variance = (int)$val - (int)$pre_val;
$deductions_variance[$i][$j] = $val;
$deduction_comparison[$i][] = "$val";
$deduction_comparison[$i][] = "$pre_val";
$deduction_comparison[$i][] = "$variance";
$j++;
}
foreach($get_earning_column as $earn_val){
$pre_earn_val = "pre_".$earn_val;
$earn_val = $result_cur->$earn_val;
$pre_earn_val = $result_cur->$pre_earn_val;
if(empty($earn_val)){
$earn_val = 0;
}
if(empty($pre_earn_val)){
$pre_earn_val = 0;
}
$variance = (int)$earn_val - (int)$pre_earn_val;
$earnings_variance[$i][$c] = $earn_val;
$earnings_comparison[$i][] = "$earn_val";
$earnings_comparison[$i][] = "$pre_earn_val";
$earnings_comparison[$i][] = "$variance";
$c++;
}
$i++;
}
$tot_prededuct_col = 0;
$tot_deduct_col = 0;
$tot_var_sum = 0;
$tot_preearn_col = 0;
$tot_earn_col = 0;
$tot_ear_var_sum = 0;
$deductions_variance[$i][0] = '';
$deductions_variance[$i][1] = 'Total';
$earnings_variance[$i][0] = '';
$earnings_variance[$i][1] = 'Total';
$deduction_comparison[$i][] = '';
$deduction_comparison[$i][] = 'Total';
$earnings_comparison[$i][] = '';
$earnings_comparison[$i][] = 'Total';
foreach($sum as $cur_sum){
$j = 2;
$c = 2;
$a = 2;
$b = 2;
foreach($get_sum_deduct_column as $deduct_col){
$prededuct_col = "pre_".$deduct_col;
$dis_col = strtoupper(str_replace('sum_','',$deduct_col));
$deduct_col = $cur_sum->$deduct_col;
$prededuct_col = $cur_sum->$prededuct_col;
$var_sum = (int)$deduct_col - (int)$prededuct_col;
$deductions_variance[$i][$j] = "$deduct_col";
$deduction_comparison[$i][] = "$deduct_col";
$deduction_comparison[$i][] = "$prededuct_col";
$deduction_comparison[$i][] = "$var_sum";
$tot_prededuct_col = (int)$tot_prededuct_col + (int)$prededuct_col;
$tot_deduct_col = (int)$tot_deduct_col + (int)$deduct_col;
$tot_var_sum = (int)$tot_var_sum + (int)$var_sum;
$consolidated_deduction[$a][] = "$dis_col";
$consolidated_deduction[$a][] = "$prededuct_col";
$consolidated_deduction[$a][] = "$deduct_col";
$consolidated_deduction[$a][] = "$var_sum";
$j++;
$a++;
}
foreach($get_sum_earning_column as $earn_col){
$preearn_col = "pre_".$earn_col;
$dis_earn__col = strtoupper(str_replace('sum_','',$earn_col));
$earn_col = $cur_sum->$earn_col;
$preearn_col = $cur_sum->$preearn_col;
$var_sum = (int)$earn_col - (int)$preearn_col;
$earnings_variance[$i][$c] = "$earn_col";
$earnings_comparison[$i][] = "$earn_col";
$earnings_comparison[$i][] = "$preearn_col";
$earnings_comparison[$i][] = "$var_sum";
$tot_preearn_col = (int)$tot_preearn_col + (int)$preearn_col;
$tot_earn_col = (int)$tot_earn_col + (int)$earn_col;
$tot_ear_var_sum = (int)$tot_ear_var_sum + (int)$var_sum;
$consolidated_earning[$b][] = "$dis_earn__col";
$consolidated_earning[$b][] = "$preearn_col";
$consolidated_earning[$b][] = "$earn_col";
$consolidated_earning[$b][] = "$var_sum";
$c++;
$b++;
}
if((int)$add_salary === 1){
$sum_gross_earn = $cur_sum->sum_gross_earn;
$pre_sum_gross_earn = $cur_sum->pre_sum_gross_earn;
$var_sum_gross_earn = $sum_gross_earn - $pre_sum_gross_earn;
$sum_gross_deduct = $cur_sum->sum_gross_deduct;
$pre_sum_gross_deduct = $cur_sum->pre_sum_gross_deduct;
$var_sum_gross_deduct = $sum_gross_deduct - $pre_sum_gross_deduct;
$sum_net_pay = $cur_sum->sum_net_pay;
$pre_sum_net_pay = $cur_sum->pre_sum_net_pay;
$var_sum_net_pay = $sum_net_pay - $pre_sum_net_pay;
$salary[$i][] = "";
$salary[$i][] = "Total";
$salary[$i][] = "$sum_gross_earn";
$salary[$i][] = "$pre_sum_gross_earn";
$salary[$i][] = "$var_sum_gross_earn";
$salary[$i][] = "$sum_gross_deduct";
$salary[$i][] = "$pre_sum_gross_deduct";
$salary[$i][] = "$var_sum_gross_deduct";
$salary[$i][] = "$sum_net_pay";
$salary[$i][] = "$pre_sum_net_pay";
$salary[$i][] = "$var_sum_net_pay";
}
$i++;
}
$consolidated_deduction[$a][] = "Total";
$consolidated_deduction[$a][] = "$tot_prededuct_col";
$consolidated_deduction[$a][] = "$tot_deduct_col";
$consolidated_deduction[$a][] = "$tot_var_sum";
$consolidated_earning[$b][] = "Total";
$consolidated_earning[$b][] = "$tot_preearn_col";
$consolidated_earning[$b][] = "$tot_earn_col";
$consolidated_earning[$b][] = "$tot_ear_var_sum";
$print_process_month = date('F-y', strtotime( date( "Y-m-d", strtotime("01-".$process_month))));
$earnings_variance = json_decode(json_encode($earnings_variance), True);
$deductions_variance = json_decode(json_encode($deductions_variance), True);
$consolidated_earning = json_decode(json_encode($consolidated_earning), True);
$consolidated_deduction = json_decode(json_encode($consolidated_deduction), True);
$earnings_comparison = json_decode(json_encode($earnings_comparison), True);
$deduction_comparison = json_decode(json_encode($deduction_comparison), True);
$print_data = array();
if((int)$add_salary === 1){
$print_data['Salary'] = $salary;
}
$print_data['Earning variance'] = $earnings_variance;
$print_data['Deductions variance'] = $deductions_variance;
$print_data['Earnings Comparison'] = $earnings_comparison;
$print_data['Deduction Comparison'] = $deduction_comparison;
$print_data['Consolidated Earning'] = $consolidated_earning;
$print_data['Consolidated Deduction'] = $consolidated_deduction;
require_once APPPATH."/third_party/PHPExcel.php";
$excel = new PHPExcel();
$i=0;
foreach($print_data as $key => $value){
if($i>0){
$excel->createSheet($i);
}
$excel->setActiveSheetIndex($i)->setTitle($key);
$total_row = count($value);
$total_colums = count($value[1]) -1;
$cell_text = $this->getNameFromNumber($total_colums);
$merge_to_cell = $cell_text."1";
$last_from_cell = "A".$total_row;
$last_to_cell = $cell_text.$total_row;
$styleArray = array(
'font' => array('bold' => true),
'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,)
);
$excel->getActiveSheet()->mergeCells("A1:$merge_to_cell");
$second_end = $cell_text."2";
$excel->getActiveSheet()->getStyle("A1:$second_end")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKGREEN);
$excel->getActiveSheet()->getStyle("A1:$second_end")->getFont()->getColor()->setARGB('FFFFFFFF');
$excel->getActiveSheet()->getStyle("A1:$second_end")->applyFromArray($styleArray);
$excel->getActiveSheet()->getStyle("$last_from_cell:$last_to_cell")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFFF0000');
$excel->getActiveSheet()->getStyle("$last_from_cell:$last_to_cell")->getFont()->getColor()->setARGB('FFFFFFFF');
$excel->getActiveSheet()->getStyle("$last_from_cell:$last_to_cell")->applyFromArray($styleArray);
$styleArray = array(
'borders' => array('allborders' => array( 'style' => PHPExcel_Style_Border::BORDER_THIN ))
);
$excel->getActiveSheet()->getStyle("A1:$last_to_cell")->applyFromArray($styleArray);
$excel->getActiveSheet()->fromArray($value, null, 'A1');
$i++;
}
$excel->setActiveSheetIndex(0);
$filename = $setting_name.".xls";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$objWriter->save('php://output');
echo json_encode(array('success' => TRUE, 'message' => "Excel Generated Successfully"));
}
public function getNameFromNumber($num){
$numeric = $num % 26;
$letter = chr(65 + $numeric);
$num2 = intval($num / 26);
if ($num2 > 0) {
return getNameFromNumber($num2 - 1) . $letter;
} else {
return $letter;
}
}
public function check_earning_and_deduct_exists(){
$prime_reconciliation_id = (int)$this->input->post('prime_reconciliation_id');
$this->db->from('cw_reconciliation_deduct_info');
$this->db->where('prime_reconciliation_id',(int)$prime_reconciliation_id);
$this->db->where('trans_status',1);
$deduct_num = $this->db->get()->num_rows();
$this->db->from('cw_reconciliation_earning_info');
$this->db->where('prime_reconciliation_id',(int)$prime_reconciliation_id);
$this->db->where('trans_status',1);
$earn_num = $this->db->get()->num_rows();
if((int)$deduct_num === 0 || (int)$earn_num === 0){
echo json_encode(array('success' => False, 'message' => "Add at least one Earning and Deduction and then Try Again ..!"));
}else{
$fliter_label = $this->input->post('fliter_label');
$fliter_type = $this->input->post('fliter_type');
$filter_cond = $this->input->post('filter_cond');
$fliter_val = $this->input->post('fliter_val');
$input_field_type = $this->input->post('input_field_type');
$group_by = ltrim(implode(",",$this->input->post('group_by[]')),",");
$filter_count = count($fliter_label);
$fliter_query = "";
$search_count = 0;
for($i=0;$i<=(int)$filter_count;$i++){
$db_name = $fliter_label[$i];
$table_name = $fliter_type[$i];
$db_cond = $filter_cond[$i];
$db_value = $fliter_val[$i];
$field_type = $field_type[$i];
if(($db_cond) && ($db_value)){
$search_count++;
if((int)$field_type === 4){
$search_val = date("Y-m-d",strtotime($db_value));
}else{
$search_val = $db_value;
}
if($db_cond === "LIKE"){ $search_val = "$db_value%"; }
if((int)$table_name === 1){
$this->prime_table = "cw_employees";
$filter_query .= ' and '. $this->prime_table .".". $db_name ." ". $db_cond .' "'.$search_val.'"';
}
if((int)$table_name === 2){
$this->cf_table = "cw_employees_cf";
$filter_query .= ' and '. $this->cf_table .".". $db_name ." ". $db_cond .' "'.$search_val.'"';
}
}
}
if($group_by){
$group_by_qry = "group by $group_by";
}else{
$group_by_qry = "";
}
$filter_info = array('filter_query' => $filter_query, 'group_by' => $group_by_qry);
$this->session->set_userdata('filter_info', $filter_info);
echo json_encode(array('success' => True, 'message' => "Processing"));
}
}
}
?>