File: /home/cafsindia/cloud_cafsinfotech_in/application/controllers/Declartion_entry.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Declartion_entry extends Action_controller{
public function __construct(){
parent::__construct('declartion_entry');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$this->save_info();
$data['module_id'] = $this->control_name;
$category_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$category_result = $category_info->result();
$category_info->next_result();
$category_list[""] = "---- Category For ----";
foreach($category_result as $for){
$cat_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$cat_id] = $category_name;
}
$data['category_list'] = $category_list;
$temp_format_qry = 'select prime_inc_temp_setting_id,template_name from cw_inc_temp_setting where trans_status = 1 and module_id="'.$this->control_name.'"';
$temp_format = $this->db->query("CALL sp_a_run ('SELECT','$temp_format_qry')");
$temp_result = $temp_format->result();
$temp_format->next_result();
$temp_format_drop[""] = "---- Template Format ----";
foreach($temp_result as $template){
$temp_format_id = $template->prime_inc_temp_setting_id;
$template_name = $template->template_name;
$temp_format_drop[$temp_format_id] = $template_name;
}
$data['temp_format_drop'] = $temp_format_drop;
$data['financial_info'] = $this->financial_info;
$this->load->view("$this->control_name/manage",$data);
}
//IMPORT FILE VIEW INFORMATION
public function import(){
$data['module_id'] = $this->control_name;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "'.$this->control_name.'" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach($excel_result as $excel){
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
$temp_format_qry = 'select prime_inc_temp_setting_id,template_name from cw_inc_temp_setting where trans_status = 1 and module_id="declartion_entry"';
$temp_format = $this->db->query("CALL sp_a_run ('SELECT','$temp_format_qry')");
$temp_result = $temp_format->result();
$temp_format->next_result();
$temp_format_drop[""] = "---- Template Format ----";
foreach($temp_result as $template){
$temp_format_id = $template->prime_inc_temp_setting_id;
$template_name = $template->template_name;
$temp_format_drop[$temp_format_id] = $template_name;
}
$data['temp_format_drop'] = $temp_format_drop;
$this->load->view("$this->control_name/manage",$data);
}
public function excel($module_id,$template_name){
$tax_section_qry = 'select tax_subsection_column,tax_act_details from cw_tax_sub_section where trans_status = 1';
$tax_section_info = $this->db->query("CALL sp_a_run ('SELECT','$tax_section_qry')");
$tax_section_result = $tax_section_info->result_array();
$tax_section_info->next_result();
$tax_sub_section_arr= array_reduce($tax_section_result, function($result, $arr){
$result[$arr['tax_subsection_column']] = $arr['tax_act_details'];
return $result;
}, array());
$excel_format_qry = 'select DISTINCT employee_code,income_tax_type,column_name,column_map from cw_declaration_template where temp_name ="'.$template_name.'" and trans_status = 1 and column_map != ""';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
if(empty($excel_result)){
echo json_encode(array('success' => FALSE, 'output' => "Template settings not Mapped"));
exit(0);
}
require_once APPPATH."/controllers/php_excel/PHPExcel.php";
$obj = new PHPExcel();
//Set the first row as the header row
foreach($excel_result as $excel){
$excel_employee_code = $excel->employee_code;
$excel_income_tax_type = $excel->income_tax_type;
$excel_line_column_name = $tax_sub_section_arr[$excel->column_name];
if(!$excel_line_column_name){
$excel_line_column_name = ucwords(str_replace("_"," ",$excel->column_name));
}
$excel_line_value = $excel->column_map;
$obj->getActiveSheet()->setCellValue($excel_line_value."1", $excel_line_column_name);
}
$obj->getActiveSheet()->setCellValue($excel_employee_code."1", "Employee Code");
$obj->getActiveSheet()->setCellValue($excel_income_tax_type."1", "Incometax type");
// Rename worksheet name
$filename= $this->control_name."_".$template_name.".xls"; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($obj, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
echo json_encode(array('success' => TRUE, 'output' => $excelOutput));
}
//UPDATE STATUS TO DELETE FOR UPLOAD FILES or DOCUMENTS
public function remove_file(){
$prime_id_val = $this->input->post('prime_id_val');
$is_defult = (int)$this->input->post('is_defult');
$input_name = $this->input->post('input_name');
$table_name = '';
if($is_defult === 1){
$table_name = $this->prime_table;
}else
if($is_defult === 2){
$table_name = $this->cf_table;
}
if($table_name){
$created_on = date("Y-m-d h:i:s");
$set_query = $input_name .' = "" ,trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE '.$table_name .' SET '. $set_query .' WHERE '. $this->prime_id .' = "'. $prime_id_val .'"';
$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' => "Unable to process your request"));
}
}
//Sheet Name display in import page
public function sheet_name(){
$file_path = $this->input->post('file_path');
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
include($filename);
$excel_obj = PHPExcel_IOFactory::load($file_path);
$sheet_count = $excel_obj->getSheetCount();
$sheet_name = array();
for($i= 0; $i< $sheet_count; $i++){
$sheet = $excel_obj->getSheet($i);
$sheet_name[] = $sheet->getTitle();
}
echo json_encode(array('sheet_name' =>$sheet_name));
}
//category based employee list -- 27AUG2019
public function get_employee_list(){
$category = $this->input->post('category');
$emp_query = 'SELECT emp_name,employee_code FROM cw_employees WHERE role = "'.$category.'" and trans_status =1 and termination_status = 0';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_query')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$emp_list = "<option value=''>---- Select Employee ----</option>";
foreach($emp_result as $result){
$name = $result->emp_name;
$emp_code = $result->employee_code;
$emp_list .= "<option value='$emp_code'>$emp_code - $name</option>";
}
echo $emp_list;
}
public function get_income_tax_type(){
$this->save_info();
$emp_code = $this->input->post('emp_code');
$fin_set_id = $this->financial_info[0]->prime_financial_setting_id;
$start_date = $this->financial_info[0]->start_date;
$end_date = $this->financial_info[0]->end_date;
$process_month_dt = "01-".$process_month;
$section_dec_query = 'SELECT income_tax_type FROM cw_declaration_entry where emp_code = "'.$emp_code.'" and finacial_setting_id = "'.$fin_set_id.'" order by prime_declaration_entry_id DESC LIMIT 0,1';
$section_dec_data = $this->db->query("CALL sp_a_run ('SELECT','$section_dec_query')");
$section_dec_result = $section_dec_data->result();
$section_dec_data->next_result();
$income_tax_type = $section_dec_result[0]->income_tax_type;
if($section_dec_result){
echo json_encode(array('success' => TRUE, 'income_tax_type' => $income_tax_type));
}else{
echo json_encode(array('success' => FALSE, 'message' => "New Entry.."));
}
}
//section and subsection details
public function get_sec_sub_list(){
$this->save_info();
$today = date('d');
$emp_code = $this->input->post('emp_code');
$category = $this->input->post('category');
$tds_type = $this->input->post('tds_type');
$effective_month = $this->input->post('effective_month');
$tax_senior_citizen = $this->input->post('tax_senior_citizen');
$fin_set_id = $this->financial_info[0]->prime_financial_setting_id;
$start_date = $this->financial_info[0]->start_date;
$end_date = $this->financial_info[0]->end_date;
$process_month_dt = "01-".$process_month;
$dec_lock_date = $this->company_info[0]->dec_lock_date;
if($tax_senior_citizen === "on") {
$tax_senior_citizen = 1;
}else{
$tax_senior_citizen = 0;
}
//Check Previous Tax Type
$section_dec_query = 'SELECT a.income_tax_type FROM cw_declaration_entry as a INNER JOIN (SELECT emp_code, date_format(MAX(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_declaration_entry where emp_code = "'.$emp_code.'" and finacial_setting_id = "'.$fin_set_id.'" and date_format(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") GROUP BY emp_code) as groupedtt ON a.emp_code = groupedtt.emp_code AND a.effective_month = groupedtt.max_date';
$section_dec_data = $this->db->query("CALL sp_a_run ('SELECT','$section_dec_query')");
$section_dec_result = $section_dec_data->result_array();
$section_dec_data->next_result();
$income_tax_type = $section_dec_result[0]->income_tax_type;
//get employee lock or unlock based on emp_code
$emp_lock_query ='SELECT declaration_lock from cw_employees where employee_code='.$emp_code.'';
$query_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_lock_query')");
$dec_lock_result = $query_info->result();
$section_dec_data->next_result();
$dec_lock_data = $dec_lock_result[0]->declaration_lock;
if($section_dec_result){
if((int)$income_tax_type !== (int)$tds_type){
if((int)$income_tax_type === 1){
$type = "With Declaration (Old)";
}else{
$type = "Without Declaration (New)";
}
echo json_encode(array('success' => FALSE, 'message' => "Transaction Already Exist $type ..Income Tax Type Could not Change..!!!","income_tax_type"=>$income_tax_type));
exit(0);
}
}
//declaration lock employee
if((int)$dec_lock_data === 1){
echo json_encode(array('success' => false, 'message' => "Declaration is locked for this employee","income_tax_type"=>$income_tax_type));
exit(0);
}
$tax_process_exit_qry = 'select count(*) as rslt_count from cw_tax_calculation where emp_code="'.$emp_code.'" and process_month="'.$effective_month.'" and trans_status =1';
$tax_process_exit_info = $this->db->query("CALL sp_a_run ('SELECT','$tax_process_exit_qry')");
$tax_process_exit_result = $tax_process_exit_info->result();
$tax_process_exit_info->next_result();
$tax_exit_count = $tax_process_exit_result[0]->rslt_count;
if((int)$tax_exit_count === 1){
echo json_encode(array('success' => false, 'message' => "Already Tax Proceed, Please Delete the tax process?","income_tax_type"=>$income_tax_type));
exit(0);
}
//declaration_lock_date condition
if((int)$dec_lock_date >= (int)$today){
echo json_encode(array('success' => false, 'message' => "Declaration entry is locked in company information!.. Please contact HR!..","income_tax_type"=>$income_tax_type));
exit(0);
}
if((int)$tds_type === 1){
$tax_section_qry = 'select cw_tax_section.tax_section,tax_act_details,tax_subsection_column from cw_tax_section inner join cw_tax_sub_section on cw_tax_sub_section.tax_section =cw_tax_section.prime_tax_section_id where cw_tax_sub_section.trans_status = 1 and prime_tax_sub_section_id NOT IN (select tax_sub_section from cw_section_matching where cw_section_matching.trans_status = 1) and ((cw_tax_sub_section.tax_section = 1 AND bill_required = 1) OR (cw_tax_sub_section.tax_section != 1)) order by cw_tax_section.tax_order';
$tax_section_info = $this->db->query("CALL sp_a_run ('SELECT','$tax_section_qry')");
$tax_section_result = $tax_section_info->result();
$tax_section_info->next_result();
$process_month_dt = "01-".$effective_month;
$collect_dec_entry_qry = 'SELECT a.* FROM cw_declaration_entry as a INNER JOIN (SELECT emp_code, date_format(MAX(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_declaration_entry where emp_code = "'.$emp_code.'" and finacial_setting_id = "'.$fin_set_id.'" and date_format(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$process_month_dt.'", "%d-%m-%Y"), "%Y-%m-%d") GROUP BY emp_code) as groupedtt ON a.emp_code = groupedtt.emp_code AND a.effective_month = groupedtt.max_date';
$collect_dec_entry_info = $this->db->query("CALL sp_a_run ('SELECT','$collect_dec_entry_qry')");
$collect_dec_entry_result = $collect_dec_entry_info->result();
$collect_dec_entry_info->next_result();
$tr_line = "";
foreach($tax_section_result as $tax_rslt){
$subsec_column_name = $tax_rslt->tax_subsection_column;
$tax_section_head = $tax_rslt->tax_section;
$tax_subsection_head = $tax_rslt->tax_act_details;
if($collect_dec_entry_result){
$subsec_column_val = $collect_dec_entry_result[0]->$subsec_column_name;
$tax_house_rent = $collect_dec_entry_result[0]->tax_house_rent;
$childran_elig = $collect_dec_entry_result[0]->childran_elig;
$tax_senior_citizen = $collect_dec_entry_result[0]->tax_senior_citizen;
}else{
$subsec_column_val = 0;
$tax_house_rent = 0;
$childran_elig = 0;
$entry_id = 0;
$tax_senior_citizen = 0;
}
$tr_line .= "<tr><td>".$tax_section_head."</td><td>".$tax_subsection_head."</td><td><input type='text' id='".$subsec_column_name."' name='".$subsec_column_name."' value ='".$subsec_column_val."' class='form-control number'/></td></tr>";
}
$table_info = "<table class='table table-bordered'>
<thead>
<tr>
<th>Tax Section</th>
<th>Tax Subsection</th>
<th>Yearly Value</th>
</tr>
</thead>
<tbody>
<tr>
<td>House Rent Paid (Annual)</td>
<td></td>
<td><input type='text' id='tax_house_rent' name='tax_house_rent' value='$tax_house_rent' class='form-control number'/></td>
</tr>
<tr>
<td>No of Children Eligible for Education</td>
<td></td>
<td><input type='text' id='childran_elig' name='childran_elig' value='$childran_elig' class='form-control number'/></td>
</tr>
$tr_line
</tbody>
</table>
<div class='col-md-12' style='float: initial;text-align: end;'><button class='btn btn-primary btn-sm' id='save_declaration'><i class='fa fa-floppy-o' aria-hidden='true'></i> Save Declaration</button></div>";
echo json_encode(array('success' => true, 'table_info' => $table_info,'message'=>"Please Fill Your Declaration..","income_tax_type"=>$income_tax_type));
}else{
$exit_emp_qry = 'select count(*) as rslt_count from cw_declaration_entry where finacial_setting_id="'.$fin_set_id.'" and emp_code="'.$emp_code.'" and effective_month="'.$effective_month.'" and trans_status =1';
$exit_emp_info = $this->db->query("CALL sp_a_run ('SELECT','$exit_emp_qry')");
$exit_emp_result = $exit_emp_info->result();
$exit_emp_info->next_result();
$rslt_count = $exit_emp_result[0]->rslt_count;
if((int)$rslt_count === 0){
$prime_qry_key = "finacial_setting_id,category,emp_code,effective_month,income_tax_type,tax_senior_citizen,trans_created_by,trans_created_date";
$prime_qry_value = '"'.$fin_set_id.'","'.$category.'","'.$emp_code.'","'.$effective_month.'","'.$tds_type.'","'.$tax_senior_citizen.'","'.$this->logged_id.'",'.'"'.$created_on.'"';
$dec_entry_insert_qry = "insert into cw_declaration_entry ($prime_qry_key) values ($prime_qry_value)";
$dec_entry_insert_info = $this->db->query("CALL sp_a_run ('INSERT','$dec_entry_insert_qry')");
$dec_entry_insert_result = $dec_entry_insert_info->result();
$dec_entry_insert_info->next_result();
}else{
$update_upd_query = 'finacial_setting_id = "'. $fin_set_id .'",category = "'. $category .'",emp_code = "'. $emp_code .'",effective_month = "'. $effective_month .'",income_tax_type = "'. $tds_type .'",tax_senior_citizen = "'. $tax_senior_citizen .'",finacial_setting_id = "'. $fin_set_id .'",trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE cw_declaration_entry SET '. $update_upd_query .' WHERE finacial_setting_id = "'. $fin_set_id .'" and emp_code = "'. $emp_code .'" and effective_month = "'.$effective_month.'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
}
$table_info = "";
echo json_encode(array('success' => true, 'table_info' => $table_info,'message'=>"Declaration Successfully Saved..","income_tax_type"=>$income_tax_type));
}
}
public function save_section(){
$this->save_info();
$declaration_data = json_decode($this->input->post('declaration_data'));
$created_on = date("Y-m-d H:i:s");
$finacial_setting_id = $this->financial_info[0]->prime_financial_setting_id;
$prime_qry_key = '';
$prime_qry_value = '';
if(!empty($declaration_data)){
foreach($declaration_data as $entry_data){
$column_name = $entry_data->name;
$column_value = $entry_data->value;
if($column_name === "effective_month"){
$column_value = $entry_data->value;
$month_exit = $column_value;
}
if($column_name === "emp_code"){
$emp_code = $column_value;
}
$prime_qry_key .= $column_name.",";
$prime_qry_value .= '"'.$column_value.'",';
$update_qry_key .= $column_name.",";
$update_qry_value .= '"'.$column_value.'",';
$update_upd_query .= $column_name.' = "'.$column_value.'",';
}
$tax_process_exit_qry = 'select count(*) as rslt_count from cw_tax_calculation where emp_code="'.$emp_code.'" and process_month="'.$month_exit.'" and trans_status =1';
$tax_process_exit_info = $this->db->query("CALL sp_a_run ('SELECT','$tax_process_exit_qry')");
$tax_process_exit_result = $tax_process_exit_info->result();
$tax_process_exit_info->next_result();
$tax_exit_count = $tax_process_exit_result[0]->rslt_count;
if((int)$tax_exit_count === 1){
echo json_encode(array('success' => false, 'message' => "Already Tax Proceed, Please Delete the tax process?"));
exit(0);
}else{
$exit_emp_qry = 'select count(*) as rslt_count from cw_declaration_entry where finacial_setting_id="'.$finacial_setting_id.'" and emp_code="'.$emp_code.'" and effective_month="'.$month_exit.'" and trans_status =1';
$exit_emp_info = $this->db->query("CALL sp_a_run ('SELECT','$exit_emp_qry')");
$exit_emp_result = $exit_emp_info->result();
$exit_emp_info->next_result();
$rslt_count = $exit_emp_result[0]->rslt_count;
if((int)$rslt_count === 0){
$prime_qry_key .= "finacial_setting_id,trans_created_by,trans_created_date";
$prime_qry_value .= '"'.$finacial_setting_id.'","'.$this->logged_id.'",'.'"'.$created_on.'"';
$dec_entry_insert_qry = "insert into cw_declaration_entry ($prime_qry_key) values ($prime_qry_value)";
$dec_entry_insert_info = $this->db->query("CALL sp_a_run ('INSERT','$dec_entry_insert_qry')");
$dec_entry_insert_result = $dec_entry_insert_info->result();
$dec_entry_insert_info->next_result();
echo json_encode(array('success' => true, 'message' => "Successfully added your declaration entry"));
}else{
$update_upd_query .= 'trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE cw_declaration_entry SET '. $update_upd_query .' WHERE finacial_setting_id = "'. $finacial_setting_id .'" and emp_code = "'. $emp_code .'" and effective_month = "'. $month_exit .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array('success' => true, 'message' => "Successfully data is updated!!!"));
}
}
}
}
public function delete_section(){
$this->save_info();
$category = $this->input->post('category');
$emp_code = $this->input->post('emp_code');
$effective_month = $this->input->post('effective_month');
$tax_process_exit_qry = 'select count(*) as rslt_count from cw_tax_calculation where emp_code="'.$emp_code.'" and process_month="'.$effective_month.'" and trans_status =1';
$tax_process_exit_info = $this->db->query("CALL sp_a_run ('SELECT','$tax_process_exit_qry')");
$tax_process_exit_result = $tax_process_exit_info->result();
$tax_process_exit_info->next_result();
$tax_exit_count = $tax_process_exit_result[0]->rslt_count;
if((int)$tax_exit_count === 1){
echo json_encode(array('success' => false, 'message' => "Already Tax Proceed, Please Delete the tax process?"));
exit();
}else{
$created_on = date("Y-m-d H:i:s");
$finacial_setting_id = $this->financial_info[0]->prime_financial_setting_id;
$update_upd_query .= 'trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$created_on.'"';
if($effective_month){
$update_query = 'UPDATE cw_declaration_entry SET trans_status = 0, '. $update_upd_query .' WHERE finacial_setting_id = "'. $finacial_setting_id .'" and emp_code = "'. $emp_code .'" and effective_month = "'. $effective_month .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array('success' => true, 'message' => "Deleted your data successfully!!!"));
}
}
}
public function process_month_check(){
$process_month = $this->input->post('process_month');
$category = $this->input->post('category');
$check_tax_exit_qry = 'select count(emp_code) as process_count from cw_tax_calculation where trans_status = 1 and process_month="'.$process_month.'" and emp_role="'.$category.'"';
$check_tax_data = $this->db->query("CALL sp_a_run ('SELECT','$check_tax_exit_qry')");
$check_tax_result = $check_tax_data->result();
$check_tax_data->next_result();
$process_count = $check_tax_result[0]->process_count;
if((int)$process_count > 0){
echo json_encode(array('success' => false, 'message' => "Tax Already Processed for this Month..!!!"));
}
}
public function check_template_details(){
$category = $this->input->post('category_name');
$sel_format_qry = 'select prime_inc_temp_setting_id,template_name from cw_inc_temp_setting inner join cw_declaration_template on cw_declaration_template.temp_name=cw_inc_temp_setting.prime_inc_temp_setting_id where cw_declaration_template.trans_status= 1 group by template_name';
$sel_format = $this->db->query("CALL sp_a_run ('SELECT','$sel_format_qry')");
$sel_result = $sel_format->result();
$sel_format->next_result();
echo json_encode(array('success' => true, 'sel_result' => $sel_result));
}
//Import function start
public function save_dec_import(){
$category = $this->input->post('category_name');
$template_name = $this->input->post('template_name');
$apply_month = $this->input->post('imp_effective_month');
$excel_file_path = $this->input->post('excel_file_path');
$excel_sheet_name = $this->input->post('excel_sheet_name');
$excel_start_row = $this->input->post('excel_start_row');
$excel_end_row = $this->input->post('excel_end_row');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d H:i:s");
$import_query = 'insert into cw_inc_import (category,template_name,apply_month,excel_file_path,excel_sheet_name,excel_start_row,excel_end_row,trans_created_by,trans_created_date) value ("'.$category.'","'.$template_name.'","'.$apply_month.'","'.$excel_file_path.'","'.$excel_sheet_name.'","'.$excel_start_row.'","'.$excel_end_row.'","'.$logged_id.'","'.$today_date.'")';
$import_info = $this->db->query("CALL sp_a_run ('INSERT','$import_query')");
$import_result = $import_info->result();
$import_info->next_result();
$import_id = $import_result[0]->ins_id;
echo $this->do_excel_dec_import($import_id);
}
//import validation and checking updated
public function do_excel_dec_import($import_id){
$this->save_info();
$logged_id = $this->session->userdata('logged_id');
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
include($filename);
if($import_id < 0){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}
$excel_path_qry = 'select * from cw_inc_import where import_id = "'.$import_id.'"';
$excel_path_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_path_qry')");
$excel_path_result = $excel_path_info->result();
$excel_path_info->next_result();
if(!$excel_path_result){
return json_encode(array('success' => false, 'message' => "Invalid file upload"));
}else{
$category = $excel_path_result[0]->category;
$excel_format = $excel_path_result[0]->template_name;
$effective_month = $excel_path_result[0]->apply_month;
$excel_file_path = $excel_path_result[0]->excel_file_path;
$excel_sheet_name = (int)$excel_path_result[0]->excel_sheet_name;
$excel_row_start = (int)$excel_path_result[0]->excel_start_row;
$excel_row_end = (int)$excel_path_result[0]->excel_end_row;
$format_qry = 'select employee_code,income_tax_type,GROUP_CONCAT(column_name) as column_name, GROUP_CONCAT(column_map) as column_map from cw_declaration_template where temp_name = "'.$excel_format.'" and trans_status = 1';
$format_info = $this->db->query("CALL sp_a_run ('SELECT','$format_qry')");
$format_rslt = $format_info->result();
$format_info->next_result();
$employee_code = $format_rslt[0]->employee_code;
$income_tax_type = $format_rslt[0]->income_tax_type;
$column_name = explode(",",$format_rslt[0]->column_name);
$column_map = explode(",",$format_rslt[0]->column_map);
$column_value = array_combine($column_name,$column_map);
$emp_exist_query = 'select employee_code,role,termination_status from cw_employees where trans_status = 1 and role = "'.$category.'"';
$emp_exist_info = $this->db->query("CALL sp_a_run ('RUN','$emp_exist_query')");
$emp_exist_result = $emp_exist_info->result_array();
$emp_exist_info->next_result();
$emp_exist_result_arr = array_reduce($emp_exist_result, function($result, $arr){
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
$fin_id = $this->financial_info[0]->prime_financial_setting_id;
$section_dec_query = 'SELECT emp_code,income_tax_type FROM cw_declaration_entry where finacial_setting_id = "'.$fin_id.'" group by emp_code';
$section_dec_data = $this->db->query("CALL sp_a_run ('SELECT','$section_dec_query')");
$section_dec_result = $section_dec_data->result_array();
$section_dec_data->next_result();
$section_dec_arr = array_reduce($section_dec_result, function($result, $arr){
$result[$arr['emp_code']] = $arr['income_tax_type'];
return $result;
}, array());
if(!$format_rslt){
return json_encode(array('success' => false, 'message' => "Please add excel format before import"));
}else{
try{
$excel_obj = PHPExcel_IOFactory::load($excel_file_path);
}catch(Exception $e){
die('Error loading file "' . pathinfo($excel_file_path, PATHINFO_BASENAME). '": ' . $e->getMessage());
return json_encode(array('success' => false, 'message' => "Invalid file or path"));
}
$sheet = $excel_obj->getSheet($excel_sheet_name);
if($excel_row_end){
$total_rows = $excel_row_end;
}else{
$total_rows = $sheet->getHighestRow();
}
$created_on = date("Y-m-d H:i:s");
$primary_column_key = "";
$primary_column_val = "";
$status_array = array();
$error_array = array();
for($row = $excel_row_start; $row <= $total_rows; $row++){//validation purpose
$employee_code_val = trim($sheet->getCell("$employee_code$row")->getCalculatedValue());
$income_tax_type_val = trim($sheet->getCell("$income_tax_type$row")->getCalculatedValue());
if($income_tax_type_val === 'NEW'){
$income_tax_type_val = 2;
}else
if($income_tax_type_val === 'OLD'){
$income_tax_type_val = 1;
}
$termination_status = $emp_exist_result_arr[$employee_code_val]['termination_status'];
if((int)$termination_status === 1){
$error_array['error']["$employee_code$row"] = "Already Resigned Employee..";
}else
if(!$emp_exist_result_arr[$employee_code_val]['employee_code']){
$error_array['error']["$employee_code$row"] = "Employee code Not Present in this category..";
}else
if(!$income_tax_type_val){
$error_array['error']["$income_tax_type$row"] = "Please check your incometax type..";
}else
if($section_dec_arr[$employee_code_val]){
$prev = $section_dec_arr[$employee_code_val];
if((int)$prev !== (int)$income_tax_type_val){
$error_array['error']["$income_tax_type$row"] = "Income tax Type Should not Change for this Financial Year..";
}
}
}
$status_count_info = count($error_array);
if((int)$status_count_info > 0){
$table_info = $this->get_excel_error_ui($error_array);
return json_encode(array('success'=>False,'message'=>"Please check your excel file",'table_info'=>$table_info));
exit();
}else{
for($row = $excel_row_start; $row <= $total_rows; $row++){
$employee_code_val = trim($sheet->getCell("$employee_code$row")->getCalculatedValue());
$income_tax_type_val = trim($sheet->getCell("$income_tax_type$row")->getCalculatedValue());
if($income_tax_type_val === 'NEW'){
$income_tax_type_val = 2;
}else
if($income_tax_type_val === 'OLD'){
$income_tax_type_val = 1;
}
$primary_column_key = "category,emp_code,effective_month,finacial_setting_id,income_tax_type,trans_created_by,trans_created_date,";
$primary_column_val = '"'.$category.'","'.$employee_code_val.'","'.$effective_month.'","'.$fin_id.'","'.$income_tax_type_val.'","'.$this->logged_id.'",'.'"'.$created_on.'",';
$decl_update_qry = '';
foreach($column_value as $column_name_key => $column_name_val){
$column_val = $column_value[$column_name_key];
$column_name_value = trim($sheet->getCell("$column_val$row")->getCalculatedValue());
$primary_column_key .= $column_name_key.",";
$primary_column_val .= '"'.$column_name_value.'",';
$decl_update_qry .= $column_name_key.' = '.'"'.$column_name_value.'",';
}
$exist_query = 'select count(*) as exist_count from cw_declaration_entry where trans_status = 1 and emp_code ="'.$employee_code_val.'" and effective_month ="'.$effective_month.'"';
$exist_info = $this->db->query("CALL sp_a_run ('RUN','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
$exist_count = $exist_result[0]->exist_count;
if((int)$exist_count === 0){
$primary_column_key = rtrim($primary_column_key,",");
$primary_column_val = rtrim($primary_column_val,",");
$prime_dec_ent_qry = "insert into cw_declaration_entry ($primary_column_key) VALUES ($primary_column_val)";
$insert_info = $this->db->query("CALL sp_a_run ('RUN','$prime_dec_ent_qry')");
$sts = True;
$sts_message = "Successfully file imported";
$status_info["$row"] = "Inserted to DB";
}else{
$decl_update_qry .= 'trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$prime_update_query = 'UPDATE cw_declaration_entry SET '. $decl_update_qry .' WHERE finacial_setting_id = "'. $fin_id .'" and effective_month = "'. $effective_month .'" and emp_code = "'. $employee_code_val .'" and trans_status = 1';
$this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')");
$sts = False;
$sts_message = "Successfully file Updated";
$status_info["$row"] = "Updated to DB";
}
$status_array['success'] = $status_info;
}
}
}
}
$table_info = $this->get_excel_error_ui($status_array);
return json_encode(array('success'=>$sts,'message'=>$sts_message,'table_info'=>$table_info));
}
//import upload success table view
public function get_excel_import_sts($status_array){
$table_info = "";
$th_line = "";
$tr_line = "";
$count = 0;
foreach($status_array as $status){
$count++;
$status_array_count = count($status);
$status_count = 0;
foreach($status as $key => $value){
$status_count++;
if((int)$count === 1){
$th_line .= "<th style='text-align:center !important;'>Si.No</th><th style='text-align:center !important;'>Column Status</th>";
}
$td_line .= "<td>$count</td><td>$value</td>";
if((int)$status_count === (int)$status_array_count){
$color = "style='color:#15da15 !important;'";
if($value === "Already Exist in DB"){
$color = "style='color:#ff0303 !important;'";
}
$tr_line .= "<tr $color>$td_line</tr>";
$td_line = "";
}
}
}
if($th_line !== ""){
$table_info = "<table class='table table-bordered' style='text-align:center;'>
<thead>
<tr>
$th_line
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
}
return $table_info;
}
//import error table view
public function get_excel_import_error_sts($error_array){
$table_info = "";
$th_line = "";
$tr_line = "";
$count = 0;
foreach($error_array as $key => $value){
$th_line = "<th style='text-align:center !important;'>Column Name</th><th style='text-align:center !important;'>Column Status</th>";
$tr_line .= "<tr style='color:#ff0303 !important;'><td>$key</td><td>$value</td></tr>";
}
if($th_line !== ""){
$table_info = "<table class='table table-bordered' style='text-align:center;'>
<thead>
<tr>
$th_line
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
}
return $table_info;
}
}
?>