File: /home/cafsindia/.trash/application.1/controllers/Tax_proof_entry.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Tax_proof_entry extends Action_controller{
public function __construct(){
parent::__construct('tax_proof_entry');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$this->save_info();
$data['encKey'] = $this->generateKey();
$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;
$data['financial_info'] = $this->financial_info;
$this->load->view("$this->control_name/manage",$data);
}
//category based employee list -- 03SEP2019
public function get_employee_list(){
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$category = $this->input->post('category');
$emp_query = 'select emp_name,employee_code from cw_employees where role = "'.$category.'" and trans_status =1';
$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;
}
//section list in 04sep2019
public function get_sec_sub_list(){
$this->save_info();
//Encryption
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$emp_code = $this->input->post('emp_code');
$finacial_setting_id = $this->financial_info[0]->prime_financial_setting_id;
$end_date = $this->financial_info[0]->end_date;
//For income tax type.
$emp_query = 'SELECT income_tax_type FROM cw_employees WHERE employee_code = "'.$emp_code.'" AND trans_status = 1';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_query')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$income_tax = $emp_result[0]->income_tax_type;
//tax section from declaration entry only
// $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)) AND cw_tax_sub_section.financial_setting_id = "'.$finacial_setting_id.'" AND cw_tax_section.income_tax_type IN ("'.$income_tax.'") order by cw_tax_section.tax_order';
//echo $tax_section_qry; die;
$tax_section_qry = 'select cw_tax_section.tax_section,tax_act_details,tax_subsection_column,cw_tax_sub_section.income_tax_type as income_tax_type 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)) and FIND_IN_SET("'.$income_tax.'",cw_tax_section.income_tax_type) and FIND_IN_SET("'.$income_tax.'",cw_tax_sub_section.income_tax_type) and cw_tax_section.prime_tax_section_id != 2 and cw_tax_sub_section.financial_setting_id = "'.$finacial_setting_id.'" 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();
$dec_tr_line = "";
$section_dec_query = '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 income_tax_type = "'.$income_tax.'" and date_format(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y"), "%Y-%m-%d") <= date_format(str_to_date("'.$end_date.'", "%Y-%m-%d"), "%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();
$section_dec_array = array_reduce($section_dec_result, function($result, $arr){
$result[$arr['emp_code']] = $arr;
return $result;
}, array());
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($section_dec_array){
$subsec_column_val = $section_dec_array[$emp_code][$subsec_column_name];
$tax_house_rent = $section_dec_array[$emp_code]['tax_house_rent'];
$childran_elig = $section_dec_array[$emp_code]['childran_elig'];
}else{
$subsec_column_val = 0;
$tax_house_rent = 0;
$childran_elig = 0;
}
$subsec_column_name = $subsec_column_name;
$proof_entry_qry = 'SELECT '.$subsec_column_name.',tax_house_rent,childran_elig FROM cw_tax_proof_entry WHERE finacial_setting_id = "'.$finacial_setting_id.'" AND emp_code = "'.$emp_code.'" AND trans_status = 1';
$proof_entry_info = $this->db->query("CALL sp_a_run ('SELECT','$proof_entry_qry')");
$proof_entry_result = $proof_entry_info->result();
$proof_entry_info->next_result();
if($proof_entry_result){
$proof_subsec_column_name_val = $proof_entry_result[0]->$subsec_column_name;
$proof_tax_house_rent = $proof_entry_result[0]->tax_house_rent;
$proof_childran_elig = $proof_entry_result[0]->childran_elig;
$proof_val = $proof_subsec_column_name_val;
}else{
$proof_tax_house_rent = 0;
$proof_childran_elig = 0;
$proof_val = 0;
}
$dec_tr_line .= "<tr><td>".$tax_section_head."</td><td>".$tax_subsection_head."</td><td>".$subsec_column_val."</td><td><input type='text' id='".$subsec_column_name."' name='".$subsec_column_name."' value ='$proof_val' class='form-control number'/></td><td><button type='button' class='btn btn-primary btn-sm' style='display: inline-block;' onclick=load_modal('".$subsec_column_name."','".$emp_code."') ><span class='fa fa-upload'> </span> Upload</button></td><td><button type='button' class='btn btn-info btn-sm' onclick=load_list_modal('".$subsec_column_name."','".$emp_code."','".$finacial_setting_id."') ><span class='fa fa-file'> </span> View</button></td></tr>";
}
$max_date_query = 'SELECT date_format(MAX(str_to_date(CONCAT("01-", effective_month), "%d-%m-%Y")) , "%m-%Y") AS max_date FROM cw_other_income_entry where financial_setting_id ="'.$finacial_setting_id.'" and emp_code="'.$emp_code.'" and trans_status = 1';
$max_date_data = $this->db->query("CALL sp_a_run ('SELECT','$max_date_query')");
$max_date_result = $max_date_data->result();
$max_date_data->next_result();
$max_date = $max_date_result[0]->max_date;
//other income entry
$other_income_qry = 'SELECT cw_income_type.income_type,income_description,other_income_column FROM cw_other_income INNER JOIN cw_income_type on cw_income_type.prime_income_type_id = cw_other_income.income_type WHERE cw_other_income.trans_status = 1 order by cw_other_income.prime_other_income_id';
$other_income_info = $this->db->query("CALL sp_a_run ('SELECT','$other_income_qry')");
$other_income_result = $other_income_info->result();
$other_income_info->next_result();
$other_tr_line = "";
foreach($other_income_result as $other_rslt){
$income_description = $other_rslt->income_description;
$income_type = $other_rslt->income_type;
$other_income_column = $other_rslt->other_income_column;
$collect_other_entry_qry = 'SELECT '.$other_income_column.' FROM cw_other_income_entry WHERE financial_setting_id = "'.$finacial_setting_id.'" and emp_code="'.$emp_code.'" and effective_month="'.$max_date.'" and trans_status = 1';
$collect_other_entry_info = $this->db->query("CALL sp_a_run ('SELECT','$collect_other_entry_qry')");
$collect_other_entry_result = $collect_other_entry_info->result();
$collect_other_entry_info->next_result();
if($collect_other_entry_result){
$other_income_column_val = $collect_other_entry_result[0]->$other_income_column;
}else{
$other_income_column_val = 0.00;
}
$other_income_column = $other_income_column;
$oth_entry_qry = 'SELECT '.$other_income_column.' FROM cw_tax_proof_entry WHERE finacial_setting_id="'.$finacial_setting_id.'" and emp_code="'.$emp_code.'" and trans_status =1';
$oth_entry_info = $this->db->query("CALL sp_a_run ('SELECT','$oth_entry_qry')");
$oth_entry_result = $oth_entry_info->result();
$oth_entry_info->next_result();
if($oth_entry_result){
$oth_val = $oth_entry_result[0]->$other_income_column;
}else{
$oth_val = 0;
}
$other_tr_line .= "<tr><td>".$income_type."</td><td>".$income_description."</td><td>$other_income_column_val</td><td><input type='text' id='".$other_income_column."' name='".$other_income_column."' value ='$oth_val' class='form-control number'/></td><td><button type='button' class='btn btn-primary btn-sm' style='display: inline-block;' onclick=load_modal('".$other_income_column."','".$emp_code."') ><span class='fa fa-upload'> </span> Upload</button></td><td><button type='button' class='btn btn-info btn-sm' onclick=load_list_modal('".$other_income_column."','".$emp_code."','".$finacial_setting_id."') ><span class='fa fa-file'> </span> View</button></td></tr>";
}
$table_info = " <table class='table table-bordered'>
<thead>
<tr>
<th>Tax Section</th>
<th>Tax Subsection</th>
<th>Declare Value</th>
<th>Proof Value</th>
<th>Document Upload</th>
<th>Upload List</th>
</tr>
</thead>
<tbody>
<tr>
<td>House Rent Paid (Annual)</td>
<td></td>
<td>$tax_house_rent</td>
<td><input type='text' id='tax_house_rent' name='tax_house_rent' value='$proof_tax_house_rent' class='form-control number'/></td>
<td><button type='button' class='btn btn-primary btn-sm' style='display: inline-block;' onclick=load_modal('tax_house_rent','".$emp_code."') ><span class='fa fa-upload'> </span> Upload</button></td>
<td><button type='button' class='btn btn-info btn-sm ' style='display: inline-block;' onclick=load_list_modal('tax_house_rent','".$emp_code."','".$finacial_setting_id."') ><span class='fa fa-file'> </span> View</button></td>
</tr>
<tr>
<td>No of Children Eligible for Education</td>
<td></td>
<td>$childran_elig</td>
<td><input type='text' id='childran_elig' name='childran_elig' value='$proof_childran_elig' class='form-control number'/></td>
<td><button type='button' class='btn btn-primary btn-sm' style='display: inline-block;' onclick=load_modal('childran_elig','".$emp_code."')><span class='fa fa-upload'> </span> Upload</button></td>
<td><button type='button' class='btn btn-info btn-sm' style='display: inline-block;' onclick=load_list_modal('childran_elig','".$emp_code."','".$finacial_setting_id."') ><span class='fa fa-file'> </span> View</button></td>
</tr>
$dec_tr_line
$other_tr_line
</tbody>
</table>";
echo $table_info;
}
//update and insert the values.
public function save_section(){
//Encryption
$encString = file_get_contents('php://input');
$proof_entry_data = $this->cryptoDecrypt($encString);
if(!$proof_entry_data){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$this->save_info();
$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 = '';
$update_qry_key = '';
$update_qry_value = '';
$update_upd_query = '';
if(!empty($proof_entry_data)){
foreach($proof_entry_data as $column_name => $column_value){
if($column_name === "emp_code"){
$emp_code = $column_value;
}
//insert qry values collection
$prime_qry_key .= $column_name.",";
$prime_qry_value .= '"'.$column_value.'",';
//updates qry values collection
$update_qry_key .= $column_name.",";
$update_qry_value .= '"'.$column_value.'",';
$update_upd_query .= $column_name.' = "'.$column_value.'",';
}
$user_role = $this->session->userdata('logged_user_role');
if((int)$user_role === 1 || (int)$user_role === 4 || (int)$user_role === 12){
$emp_code = $emp_code;
}else{
$emp_code = $this->session->userdata('logged_emp_code');
}
$exit_proof_qry = 'select count(*) as rslt_count from cw_tax_proof_entry where finacial_setting_id="'.$finacial_setting_id.'" and emp_code="'.$emp_code.'" and trans_status =1';
$exit_proof_info = $this->db->query("CALL sp_a_run ('SELECT','$exit_proof_qry')");
$exit_proof_result = $exit_proof_info->result();
$exit_proof_info->next_result();
$rslt_count = $exit_proof_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.'"';
$proof_entry_insert_qry = "insert into cw_tax_proof_entry ($prime_qry_key) values ($prime_qry_value)";
$proof_entry_insert_info = $this->db->query("CALL sp_a_run ('INSERT','$proof_entry_insert_qry')");
$proof_entry_insert_result = $proof_entry_insert_info->result();
$proof_entry_insert_info->next_result();
echo json_encode(array('success' => TRUE, 'message' => "Successfully added your proof entry value"));
}else{
$update_upd_query .= 'trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE cw_tax_proof_entry SET '. $update_upd_query .' WHERE finacial_setting_id = "'. $finacial_setting_id .'" and emp_code = "'. $emp_code .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array('success' => true, 'message' => "Successfully proof data is updated!!!"));
}
}
}
public function delete_section(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$this->save_info();
$category = $this->input->post('category');
$emp_code = $this->input->post('emp_code');
$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($emp_code){
$update_query = 'UPDATE cw_tax_proof_entry SET trans_status = 0, '. $update_upd_query .' WHERE finacial_setting_id = "'. $finacial_setting_id .'" and emp_code = "'. $emp_code .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array('success' => true, 'message' => "Deleted your data successfully!!!"));
}else{
echo json_encode(array('success' => false, 'message' => "Server timeout!!!"));
}
}
public function upload_documents(){
$this->save_info();
$emp_code = $this->input->post('emp_code_val');
$cal_name = str_replace(" ", "_", $this->input->post('cal_name'));
$fin_set_id = $this->financial_info[0]->prime_financial_setting_id;
$created_on = date("Y-m-d H:i:s");
$user_id = $this->logged_id;
$file_path = "./proof_entry/$emp_code/$cal_name/"."ENC_".base64_encode(strtotime(date('d-m-Y H:i')))."ENC_";
if(!file_exists($file_path)) {
mkdir($file_path, 0777, true);
}
$sts = 0;
if(!empty($_FILES)){
$upload_dir = $file_path."/";
$date = date_create();
$timestamp = date_timestamp_get($date);
$act_name = $_FILES['file']['name'];
$filename = "ENC_".base64_encode(strtotime(date('d-m-Y H:i')))."ENC_".str_replace(' ', '_', $_FILES['file']['name']);
$uploaded_file = $upload_dir.$filename;
move_uploaded_file($_FILES['file']['tmp_name'],$uploaded_file);
$sts = 1;
}
if((int)$sts === 1){
$proof_entry_doc_insert_qry = 'insert into cw_tax_proof_entry_doc (emp_code,fin_set_id,cal_name,file_path,act_file_name,import_name,trans_created_by,trans_created_date) values ("'.$emp_code.'","'.$fin_set_id.'","'.$cal_name.'","'.$file_path.'","'.$act_name.'","'.$filename.'","'.$user_id.'","'.$created_on.'")';
$proof_entry_doc_insert_info = $this->db->query("CALL sp_a_run ('INSERT','$proof_entry_doc_insert_qry')");
$proof_entry_doc_insert_result = $proof_entry_doc_insert_info->result();
$proof_entry_doc_insert_info->next_result();
}
return true;
}
public function upload_doc_lists(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$cal_name = $this->input->post('cal_name');
$emp_code = $this->input->post('emp_code');
$fin_id = $this->input->post('fin_id');
$doc_list_qry = 'select * from cw_tax_proof_entry_doc where trans_status = 1 and emp_code ="'.$emp_code.'" and fin_set_id ="'.$fin_id.'" and cal_name ="'.$cal_name.'"';
$doc_list_info = $this->db->query("CALL sp_a_run ('SELECT','$doc_list_qry')");
$doc_list_result = $doc_list_info->result();
$doc_list_info->next_result();
$tr_line = "";
$i =1;
foreach($doc_list_result as $doc_rslt){
$imp_doc_id = $doc_rslt->prime_tax_proof_entry_doc_id;
$act_file_name = $doc_rslt->act_file_name;
$file_path = $doc_rslt->file_path;
$import_name = $doc_rslt->import_name;
$get_code = explode('ENC_', $file_path);
$get_decode = base64_decode($get_code[1]);
// echo $get_code[1]." ::$get_decode<br>";
$get_decode_time = date('d-m-Y H:i',$get_decode);
$view_file_path = "$file_path/$import_name";
$curren_date = strtotime(date('d-m-Y H:i'));
$get_minutes = $curren_date- $get_decode;
$status = 1;
$tr_line .= "<tr id='$imp_doc_id'><td>$i</td><td>".$act_file_name."</td><td><a onclick=openfile('$view_file_path','$status') class='btn btn-xs btn-info'><i class='fa fa-eye' aria-hidden='true'></i> View </a><a href='$file_path/$import_name' class='btn btn-xs btn-success' download><i class='fa fa-download' aria-hidden='true'></i> Download</a> <a class='btn btn-xs btn-danger' onclick=delete_doc('$imp_doc_id')> <i class='fa fa-trash-o' aria-hidden='true'></i> Delete</a></td></tr>";
$i++;
}
$table_info = "<table class='table table-bordered' id='doc_list'>
<thead>
<tr>
<th>Si. No</th>
<th>File Lists</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
echo json_encode(array('table_info' => $table_info));
}
public function delete_doc(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$imp_id = $this->input->post('imp_id');
$created_on = date("Y-m-d H:i:s");
$user_id = $this->logged_id;
if($imp_id){
$update_query = 'UPDATE cw_tax_proof_entry_doc SET trans_status = 0, trans_deleted_by = "'. $user_id .'", trans_deleted_date = "'. $created_on .'" WHERE prime_tax_proof_entry_doc_id = "'. $imp_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
}
echo json_encode(array('success' => true, 'message' => "Deleted your data successfully!!!"));
}
}
?>