File: //home/cafsindia/hrms_allyindian_com/application_bk/controllers/Challan_entry.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Challan_entry extends Action_controller{
public function __construct(){
parent::__construct('challan_entry');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//PAGE INFO FUNCTION
$this->page_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 = "";
if($role_result){
$pick_key = array_column($role_result, "prime_category_id");
$pick_val = array_column($role_result, "category_name");
$category_list = array_combine( $pick_key, $pick_val);
}
if($category_list){
$category_list = array("" => "---- Select Category ----") + $category_list;
}
$data['category_list'] = $category_list;
$bank_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_bank` where trans_status = 1 ')");
$bank_result = $bank_info->result();
$bank_info->next_result();
$bank_list = "";
if($bank_result){
$bank_id = array_column($bank_result, "prime_bank_id");
$bank_name = array_column($bank_result, "bank_name");
$bank_list = array_combine( $bank_id, $bank_name);
}
if($bank_list){
$bank_list = array("" => "---- Select Bank ----") + $bank_list;
}
$data['bank_list'] = $bank_list;
$challan_info = $this->db->query("CALL sp_a_run ('SELECT','select challan_no from cw_challan_entry where trans_status =1')");
$challan_rslt = $challan_info->result();
$challan_list = "";
$challan_info->next_result();
if($challan_info){
$challan_id = array_column($challan_rslt, "challan_no");
$challan_drop = array_combine( $challan_id, $challan_id);
}
if($challan_id){
$challan_list = array("" => "---- Select Challan ----") + $challan_drop;
}
// echo "<pre>";
// print_r($challan_list); die;
$data['challan_list'] = $challan_list;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//FUNCTION FOR VIEW DATA
public function view_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$category = $this->input->post('category');
$roles = implode('","', $category);
$process_month = $this->input->post('process_month');
$process_type = (int)$this->input->post('process_type');
$challan_no = $this->input->post('challan_list');
//QUERY FOR EMPLOYEES ALREADY EXIST OR NOT IN SAME MONTH
if($process_type === 1){//entry
$challan_exist_employee = 'select emp_code from cw_challan_entry_line where trans_status=1 and process_month="'.$process_month.'"';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$challan_exist_employee')");
$exist_rslt = $exist_data->result();
$exist_data->next_result();
$emp_list = array_column($exist_rslt, "emp_code");
$exist_employee = implode('","', $emp_list);
//TABLE DATA QUERY
$tax_list_qry ='select cw_transactions.employee_code,cw_employees.emp_name,cw_transactions.monthly_tds, ROUND(cw_transactions.monthly_tds-(cw_transactions.monthly_tds / 104 * 100)) AS edu_cess,
ROUND((cw_transactions.monthly_tds / 104 * 100)) AS tds from cw_transactions inner join cw_employees on cw_employees.employee_code = cw_transactions.employee_code where cw_transactions.trans_status =1 and cw_transactions.trans_status=1 and cw_transactions.process_month="'.$process_month.'" and cw_transactions.role in("'.$roles.'") and cw_transactions.employee_code not in("'.$exist_employee.'")';
$tax_list_data = $this->db->query("CALL sp_a_run ('SELECT','$tax_list_qry')");
$tax_list_rslt = $tax_list_data->result();
$tax_list_data->next_result();
if($tax_list_rslt){
echo json_encode(array("success"=>true,"message"=>"success",'table_data'=>$tax_list_rslt,"table_name"=>"process"));
}else{
echo json_encode(array("success"=>false,"message"=>"No Records Found"));
}
}else
if($process_type === 2){//view
$view_list_qry = 'select cw_challan_entry_line.emp_code as employee_code,cw_employees.emp_name,tot_tax_ded as monthly_tds,edu_cess,cw_challan_entry_line.tds as tds from cw_challan_entry_line inner join cw_employees on cw_employees.employee_code = cw_challan_entry_line.emp_code where cw_challan_entry_line.trans_status=1 and cw_challan_entry_line.process_month="'.$process_month.'" and cw_challan_entry_line.challan_no = "'.$challan_no.'"';
$view_list_data = $this->db->query("CALL sp_a_run ('SELECT','$view_list_qry')");
$view_list_rslt = $view_list_data->result();
$view_list_data->next_result();
if($view_list_rslt){
echo json_encode(array("success"=>true,"message"=>"success",'table_data'=>$view_list_rslt,"table_name"=>"view"));
}else{
echo json_encode(array("success"=>false,"message"=>"No Records Found"));
}
}else{//delete
//challan entry line table delete
$entry_line_dlt = 'delete from cw_challan_entry where process_month="'.$process_month.'" and challan_no="'.$challan_no.'"';
$this->db->query("CALL sp_a_run ('RUN','$entry_line_dlt')");
$entry_line_dlt_rows = $this->db->affected_rows();
$challan_entry_dlt ='delete from cw_challan_entry_line where process_month="'.$process_month.'" and challan_no="'.$challan_no.'"';
$this->db->query("CALL sp_a_run ('RUN','$challan_entry_dlt')");
$challan_entry_dlt_rows = $this->db->affected_rows();
if ($entry_line_dlt_rows > 0 && $challan_entry_dlt_rows > 0) {
echo json_encode(array("success"=>true,"message"=>"Data Successfully Deleted","table_data"=>""));
} else {
echo json_encode(array("success"=>false,"message"=>"No Records found"));
}
}
}
public function get_challan_list(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$process_month = $this->input->post('process_month');
$challan_qry = 'select challan_no from cw_challan_entry where process_month = "'.$process_month.'" and trans_status =1';
$challan_info = $this->db->query("CALL sp_a_run ('SELECT','$challan_qry')");
$challan_rslt = $challan_info->result_array();
$challan_info->next_result();
if($challan_rslt){
echo json_encode(array("success"=>true,"list_data"=>$challan_rslt));
}else{
echo json_encode(array("success"=>false,"message"=>"No Challans Avalilable in this Month"));
}
}
public function save_data(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$categ = $this->input->post('category');
$category = explode(',', $categ);
$category_ins = implode(',', $category);
$roles = implode('","', $category);
$process_month = $this->input->post('process_month');
$challan_date = date('Y-m-d',strtotime($this->input->post('challan_date')));
$challan_no = $this->input->post('challan_no');
$bank_name = $this->input->post('bank_name');
$cheque_no = $this->input->post('cheque_no');
$book_entry = $this->input->post('book_entry');
$selected_id = $this->input->post('sel_id');
$process_type = $this->input->post('process_type');
$sel_emp_code = implode('","', $selected_id);
$check_duplicate_entry ='select count(*)as entry_count from cw_challan_entry where process_month="'.$process_month.'" and challan_no="'.$challan_no.'"';
$check_entry_data = $this->db->query("CALL sp_a_run ('SELECT','$check_duplicate_entry')");
$chk_entry_rslt = $check_entry_data->result();
$check_entry_data->next_result();
if((int)$chk_entry_rslt[0]->entry_count > 0){
echo json_encode(array("success"=>false,"message"=>"This process month and challan no already exist"));
exit(0);
}
$tax_sum_qry ='select sum(cw_transactions.monthly_tds)as monthly_tds,SUM(monthly_tds / 104 * 100) AS tds_amt from cw_transactions where cw_transactions.trans_status=1 and cw_transactions.process_month ="'.$process_month.'" and cw_transactions.role in("'.$roles.'") and cw_transactions.employee_code in("'.$sel_emp_code.'")';
$tax_sum_data = $this->db->query("CALL sp_a_run ('SELECT','$tax_sum_qry')");
$tax_sum_rslt = $tax_sum_data->result();
$tax_sum_data->next_result();
$monthly_tds = $tax_sum_rslt[0]->monthly_tds;
$tds_amt = $tax_sum_rslt[0]->tds_amt; //formula column
$tot_edu_cess = $monthly_tds - $tds_amt;
// $tot_surcharge_amt = $tax_sum_rslt[0]->tot_surcharge_amt;
$tot_surcharge_amt = 0;
$tax_total = $tds_amt + $tot_edu_cess + $tot_surcharge_amt;
$created_on = date("Y-m-d h:i:s");
$financial_info = $this->get_financial_year();
$fin_id = $financial_info[0]->prime_financial_setting_id;
$start_date = $financial_info[0]->start_date;
$end_date = $financial_info[0]->end_date;
$challan_entry_ins_qry = 'INSERT INTO cw_challan_entry(financial_setting_id,process_month,category,surcharge_amt,tds_amt,edu_cess,tot_tax_dep,bank_name,cheque_no,challan_no,tax_dep_date,book_entry,trans_created_by,trans_created_date) VALUES ("'.$fin_id.'","'.$process_month.'","'.$category_ins.'","'.$tot_surcharge_amt.'","'.$tds_amt.'","'.$tot_edu_cess.'","'.$tax_total.'","'.$bank_name.'","'.$cheque_no.'","'.$challan_no.'","'.$challan_date.'","'.$book_entry.'","'.$this->logged_id.'","'.$created_on.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$challan_entry_ins_qry')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
$challan_entry_line_qry = 'SELECT cw_transactions.employee_code,cw_transactions.monthly_tds,ROUND((cw_transactions.monthly_tds / 104 * 100)) AS tds_amt,cw_transactions.role,cw_transactions.total_earnings FROM cw_transactions WHERE cw_transactions.trans_status = 1 AND cw_transactions.process_month = "'.$process_month.'" AND cw_transactions.role IN ("'.$roles.'") AND cw_transactions.employee_code IN ("'.$sel_emp_code.'")';
$tax_list_data = $this->db->query("CALL sp_a_run ('SELECT','$challan_entry_line_qry')");
$tax_list_rslt = $tax_list_data->result_array();
$tax_list_data->next_result();
$build_query = "";
foreach($tax_list_rslt as $key => $val){
$emp_code = $val['employee_code'];
$month_tds = $val['monthly_tds'];
$tds_formula = $val['tds_amt']; // formula value
$calc_edu_cess= $month_tds - $tds_formula;
$surcharge_amt= $val['surcharge_amt'];
$role = $val['role'];
$total_earnings = $val['total_earnings'];
$build_query .= '("'.$insert_id.'",'.'"'.$emp_code.'",'.'"'.$role.'",'.'"'.$tds_formula.'",'.'"'.$calc_edu_cess.'",'.'"'.$surcharge_amt.'",'.'"'.$process_month.'",'.'"'.$challan_date.'",'.'"'.$challan_no.'",'.'"'.$bank_name.'",'.'"'.$cheque_no.'",'.'"'.$book_entry.'",'.'"'.$month_tds.'",'.'"'.$month_tds.'",'.'"'.$total_earnings.'",'.'"'.$this->logged_id.'",'.'"'.$created_on.'"),';
}
$insert_val = rtrim($build_query, ',');
$challan_entry_ins_qry = 'INSERT INTO cw_challan_entry_line(challan_entry_id,emp_code,category,tds,edu_cess,surcharge_amt,process_month,deposit_date,challan_no,bank_name,cheque_no,book_entry,tot_tax_ded,tot_tax_dep,taxable_amt,trans_created_by,trans_created_date) values'.$insert_val.'';
$line_insert_info = $this->db->query($challan_entry_ins_qry);
// $line_insert_result = $line_insert_info->result();
// $line_insert_info->next_result();
$line_insert_id = $line_insert_result[0]->ins_id;
$challan_exist_employee = 'select emp_code from cw_challan_entry_line where trans_status=1 and process_month="'.$process_month.'"';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$challan_exist_employee')");
$exist_rslt = $exist_data->result();
$exist_data->next_result();
$emp_list = array_column($exist_rslt, "emp_code");
$exist_employee = implode('","', $emp_list);
//TABLE DATA QUERY
$tax_list_qry ='select cw_transactions.employee_code,cw_employees.emp_name,cw_transactions.monthly_tds, ROUND(cw_transactions.monthly_tds-(cw_transactions.monthly_tds / 104 * 100)) AS edu_cess,
ROUND((cw_transactions.monthly_tds / 104 * 100)) AS tds from cw_transactions inner join cw_employees on cw_employees.employee_code = cw_transactions.employee_code where cw_transactions.trans_status =1 and cw_transactions.trans_status=1 and cw_transactions.process_month="'.$process_month.'" and cw_transactions.role in("'.$roles.'") and cw_transactions.employee_code not in("'.$exist_employee.'")';
$tax_list_data = $this->db->query("CALL sp_a_run ('SELECT','$tax_list_qry')");
$tax_list_rslt = $tax_list_data->result();
$tax_list_data->next_result();
if($line_insert_info){
echo json_encode(array("success"=>true,"message"=>"success",'table_data'=>$tax_list_rslt,"table_name"=>"process"));
}else{
echo json_encode(array("success"=>false,"message"=>"No Records Inserted"));
}
}
}
?>