File: /home/cafsindia/hrms_cafsinfotech_in/application/controllers/Twentyfour_q_quarterly.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
require('./application/libraries/PHPSpreadsheet/autoload.php');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Twentyfour_q_quarterly extends Action_controller{
// Configuration constants
private $quarter_mapping = [
'04' => 'Q1', '07' => 'Q2', '10' => 'Q3', '01' => 'Q4'
];
private $financialInfo = null;
public function __construct(){
parent::__construct('twentyfour_q_quarterly');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
public function check_exist($month_type){
try {
// Input validation
$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....'));
exit;
}
// Validate month type
$this->validateMonthType($month_type);
// Get financial year info with caching
$financial_info = $this->getFinancialYear();
$fin_set_id = $financial_info[0]->prime_financial_setting_id;
$start_date = $financial_info[0]->start_date;
$end_date = $financial_info[0]->end_date;
$currentYear = date('Y', strtotime($start_date));
// Calculate quarter months
$months = $this->calculateQuarterMonths($month_type, $currentYear);
$month_list = "'" . implode("','", $months) . "'";
// Log operation
$this->logOperation('check_exist', [
'month_type' => $month_type,
'months' => $months,
'month_list' => $month_list
]);
// Optimized single query for validation
$validation_qry = "SELECT COUNT(DISTINCT c.process_month) AS months_with_data, COALESCE(SUM(t.monthly_tds), 0) AS calculated_tds,COALESCE(SUM(c.tot_tax_dep), 0) AS challan_tds,COUNT(c.prime_challan_entry_id) AS total_challans,GROUP_CONCAT(DISTINCT c.process_month ORDER BY c.process_month) AS available_months FROM cw_challan_entry c INNER JOIN cw_bank b ON b.prime_bank_id = c.bank_name LEFT JOIN ( SELECT process_month, SUM(monthly_tds) AS monthly_tds FROM cw_transactions WHERE trans_status = 1 GROUP BY process_month ) t ON t.process_month = c.process_month WHERE c.process_month IN ({$month_list}) AND c.trans_status = 1";
$validation_data = $this->db->query("CALL sp_a_run ('SELECT','" . addslashes($validation_qry) . "')");
$validation_result = $validation_data->result_array();
$validation_data->next_result();
if (empty($validation_result)) {
echo json_encode(array('success' => false, 'message' => "No records found for months: " . implode(', ', $months)));
exit;
}
$result = $validation_result[0];
$required_months = 3;
$months_with_data = (int)$result['months_with_data'];
$calculated_tds = (int)$result['calculated_tds'];
$challan_tds = (int)$result['challan_tds'];
// Detailed validation checks
if ($months_with_data < $required_months) {
$missing_months = array_diff($months, explode(',', $result['available_months'] ?? ''));
echo json_encode(array('success' => false, 'message' => "Incomplete data. Missing data for months: " . implode(', ', $missing_months)));
exit;
}
if ($challan_tds !== $calculated_tds) {
echo json_encode(array('success' => false, 'message' => "Challan Amount and TDS Amount are not equal! ($challan_tds != $calculated_tds)"));
exit;
}
// Success response
echo json_encode(array('success' => true, 'message' => "Validation successful! All checks passed."));
} catch (Exception $e) {
echo json_encode(array('success' => false, 'message' => "Validation failed: " . $e->getMessage()));
}
}
public function generate_excel($month_type,$suppress_tax){
$financial_info = $this->get_financial_year();
$fin_set_id = $financial_info[0]->prime_financial_setting_id;
$start_date = $financial_info[0]->start_date;
$end_date = $financial_info[0]->end_date;
$currentYear = date('Y',strtotime($start_date));
$startMonth = $month_type;
$condition = '';
if((int)$suppress_tax === 1){
$condition .= ' AND ch.tot_tax_ded>0 ';
}else if((int)$suppress_tax === 0){
$condition .= '';
}
$months = [];
for ($i = 0; $i < 3; $i++) {
$month = $startMonth + $i;
$year = $currentYear;
if ($month < 4) {
$year += 1;
}
$monthName = sprintf('%02d', $month) . '-' . $year;
$months[] = $monthName;
}
$month_list = implode('","',$months ?? []);
$emp_name_qry = ' SELECT emp_name,f.pan_or_gnr_no as pan,f.tan_no as tan,c.company_name as c_name,c.address as address,c.city AS city,CONCAT(YEAR(f.start_date), "-", YEAR(f.end_date)) AS fin_year,CONCAT(YEAR(f.end_date),"-",YEAR(f.end_date)+1) AS assess_year FROM cw_financial_setting AS f JOIN cw_employees AS e JOIN cw_company_information AS c ON f.incharge_employee = e.employee_code AND c.prime_company_information_id = f.tan_no';
$emp_name_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_name_qry')");
$emp_name_rslt = $emp_name_data->result_array();
$emp_name_data->next_result();
foreach($emp_name_rslt as $val){
$name = $val['emp_name'];
$pan = $val['pan'];
$tan = $val['tan'];
$com_name = $val['c_name'];
$address = $val['address'];
$city = $val['city'];
$fin_year = $val['fin_year'];
$assess_year = $val['assess_year'];
}
$challan_foot_arr = array(1=>"5. Details of salary paid and tax deducted thereon from the Employees ",2=>"Enclose Annexures ,I,II and III",3=>"",4=>"I $name,hereby certify that all the pariculars furnished above are correct and complete.",9=>"Place:",11=>"Date:",13=>"Notes:",14=>"(1) Indicate the type of deductor Government / Others",15=>"(2) Government deductors to give particulars of transfer vouchers; other deductors to give particulars of challan no. regarding deposit into bank.",16=>"(3) Column is relevant only for Government deductors",17=>"(4) Salary includes wages, annuity, pension, gratuity, fees, commission, bonus, repayment of amount deposited",18=>"under the Additional Emoluments (Compulsory Deposit) Act, 1974 or profits in lieu of or in addition to salary or wages,",19=>"including payments made at or in connection with termination of employment advance of salary or any other sums chargeable to income tax under the head 'Salaries'.",20=>"(5) Where an employer deducts from the emoluments paid to an employee or pays on his behalf any",21=>"contributions of that employee to any approved superannuation fund, all such deductions or payments should be included in the statements.",22=>"(6) record on every page totals of each of the columns ");
//Challan wise Data START
$challan_qry = 'SELECT c.tds_amt,c.surcharge_amt,c.edu_cess," " as interest,"" as others,tot_tax_dep,cheque_no,bsr_code,
DATE_FORMAT(c.tax_dep_date, "%d-%m-%Y") as tax_dep_date ,challan_no,IF(book_entry = 1,"Yes","No") as book_entry FROM cw_challan_entry c inner join cw_bank on cw_bank.prime_bank_id = c.bank_name WHERE process_month in ("'.$month_list.'")';
$challan_data = $this->db->query("CALL sp_a_run ('SELECT','$challan_qry')");
$challan_rslt = $challan_data->result();
$challan_data->next_result();
//Challan wise Data END
if($challan_rslt){
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify('./excel_write/24Q_Quarterly.xlsx');
$excel2 = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$excel2 = $excel2->load('./excel_write/24Q_Quarterly.xlsx');//default excel template
//Company Info - START
$excel_sheet = $excel2->setActiveSheetIndex(0);
$excel2->setActiveSheetIndex(0)->setTitle("Company Info");
$excel2->getActiveSheet()->setCellValue("B"."7", "$tan");
$excel2->getActiveSheet()->setCellValue("B"."9", "$pan");
$excel2->getActiveSheet()->setCellValue("B"."11", "$fin_year");
$excel2->getActiveSheet()->setCellValue("B"."14", "$com_name");
$excel2->getActiveSheet()->setCellValue("B"."20", "$address");
$excel2->getActiveSheet()->setCellValue("B"."21", "$city");
$excel2->getActiveSheet()->setCellValue("E"."7", "$assess_year");
//Company Info - END
//Challan Entry - START
$excel_sheet = $excel2->setActiveSheetIndex(1);
$excel2->setActiveSheetIndex(1)->setTitle("Challan Entry");
$high_column = $excel_sheet->getHighestColumn();
$hign_col_num = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($high_column);
$col_arr = array();
for($i=0; $i<$hign_col_num; $i++){
$letter = $this->getNameFromNumber($i);
$col_arr[$i] = $letter;
}
$j = 4;
foreach ($challan_rslt as $key => $row_data){
$x=1;
foreach($row_data as $key => $row_value){
$col_name = $col_arr[$x];
$excel2->getActiveSheet()->getCell("A".$j)->setValue( $j-3).";";
$excel2->getActiveSheet()->getCell($col_name.$j)->setValue("$row_value").";";
$x++;
}
$j++;
}
foreach($challan_foot_arr as $key => $val){
$row = $j+$key;
$excel2->getActiveSheet()->setCellValue("A".$row, $val);
if($key === 3){
$excel2->getActiveSheet()->setCellValue("D".$row, "VERIFICATION");
$excel2->getActiveSheet()->getStyle("D".$row)->getFont()->setBold(true);
}
if($key === 9){
$excel2->getActiveSheet()->setCellValue("E".$row, "Signature of Person responsible for deducting tax at source");
}else
if($key === 11){
$excel2->getActiveSheet()->setCellValue("E".$row, "Name and designation of Person responsible for deducting tax at source");
}
if ($key === 1 || $key === 2 ) {
$excel2->getActiveSheet()->getStyle("A".$row)->getFont()->setBold(true);
}
}
//Challan Entry - END
//Quarter wise Data START
$z = 2;
foreach ($months as $key => $month_val){
$sal_end_date = date("t-m-Y",strtotime("25-".$month_val));
$sheet1_qry = 'SELECT ch.emp_code,e.pan_number,e.emp_name,ch.payment_date,ch.taxable_amt,ch.tds,ch.surcharge_amt,ch.edu_cess,ch.tot_tax_ded,ch.tot_tax_dep,ch.ded_date,DATE_FORMAT(ch.deposit_date, "%d-%m-%Y") as deposit_date FROM cw_challan_entry_line ch INNER JOIN cw_employees e ON e.employee_code = ch.emp_code WHERE ch.process_month = "'.$month_val.'" and e.trans_status = 1 '.$condition.' ';
//Quarter wise Data END
$sheet1_data = $this->db->query("CALL sp_a_run ('SELECT', '$sheet1_qry')");
$sheet1_rslt = $sheet1_data->result_array();
$sheet1_data->next_result();
//Employee Data Entry - START
$excel_sheet = $excel2->setActiveSheetIndex($z);
$excel2->setActiveSheetIndex($z)->setTitle($month_val);
$high_column = $excel_sheet->getHighestColumn();
$hign_col_num = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($high_column);
$col_arr = array();
for($i=0; $i<$hign_col_num; $i++){
$letter = $this->getNameFromNumber($i);
$col_arr[$i] = $letter;
}
$j = 4;
foreach ($sheet1_rslt as $key => $row_data){
$x=1;
foreach($row_data as $key => $row_value){
$col_name = $col_arr[$x];
$excel2->getActiveSheet()->getCell("A".$j)->setValue($j-3).";";
$excel2->getActiveSheet()->getCell($col_name.$j)->setValue("$row_value").";";
$excel2->getActiveSheet()->getCell("E".$j)->setValue($sal_end_date).";";
$excel2->getActiveSheet()->getCell("L".$j)->setValue($sal_end_date).";";
$x++;
}
$j++;
}
//Challan Entry - END
$z++;
}
$excel2->setActiveSheetIndex(0);
$quarter_map = ['04' => 'Q1','07' => 'Q2','10' => 'Q3','01' => 'Q4'];
$quarter_label = isset($quarter_map[$month_type])? $quarter_map[$month_type]: 'QX';
$filename = "24Q_Quarterly_" . $quarter_label . "_" . date('Ymd_His') . ".xls";
$dir = FCPATH . "24q/";
if(!is_dir($dir)){
mkdir($dir, 0777, true);
}
$file_path = "24q/" . $filename;
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excel2, "Xls");
$objWriter->save(FCPATH . $file_path);
$files = [];
$files[] = ['quarter_month' => $quarter_label,'file_type' => 'xls','file_name' => $filename,'file_path' => base_url($file_path)];
// FVU PACKAGE OF ZIP
$fvu_file = $this->generate_fvu_package($month_type, $months, $condition);
if($fvu_file){
$files[] = $fvu_file;
}
$table_html = $this->create_table($files);
echo json_encode(['success' => true,'message' => '24Q file generated successfully','table_data' => $table_html]);
exit;
}
}
//GET EXCEL COLUMNS FOR NUMBERS
public function getNameFromNumber($num){
$numeric = $num % 26;
$letter = chr(65 + $numeric);
$num2 = intval($num / 26);
if($num2 > 0){
return $this->getNameFromNumber($num2 - 1) . $letter;
}else{
return $letter;
}
}
// CREATE TABLE ON SHOW FRONT _ARN 02-01-2026
public function create_table($files){
$td = '';
foreach($files as $f){
$td .= "<td>
<a class='btn btn-success btn-sm' href='{$f['file_path']}' target='_blank'>
<i class='fa fa-download'></i> Download
</a>
</td>";
}
return "<div class='table-responsive'>
<table class='table table-bordered table-striped nowrap' id='table_info' style='width:100%'>
<thead>
<tr>
<th style='width:80px'>Quarter</th>
<th style='width:140px'>Download 24Q (Xls)</th>
<th style='width:140px'>Download FVU (Text)</th>
</tr>
</thead>
<tbody><tr>
<td>{$files[0]['quarter_month']}</td>
$td
</tr>
</tbody>
</table>
</div>";
}
// =============== GENERATE FVU ZIP PACKAGE MAIN FUNCTION =====================
public function generate_fvu_package($month_type, $months, $condition){
$quarter_map = ['04'=>'Q1','07'=>'Q2','10'=>'Q3','01'=>'Q4'];
$quarter = $quarter_map[$month_type] ?? 'QX';
$base_dir = FCPATH . "24q/{$quarter}/";
if(!is_dir($base_dir)){
mkdir($base_dir, 0777, true);
}
$company_info = $this->get_24q_company_info();
$company_info = $company_info[0];
$employee_data = $this->get_24q_employee_data($months, $condition);
if(!$employee_data){
return false;
}
$this->build_form24q_txt($base_dir, $company_info, $quarter, $employee_data);
$txt_file_name = "form24q_".$quarter.".txt";
return ['quarter_month' => $quarter,'file_type' => 'TXT','file_name' => $txt_file_name,'file_path' => base_url("twentyfour_q_quarterly/download_file/{$quarter}/{$txt_file_name}")];
}
// ======================= FILE DOWNLOAD METHOD ===========================
public function download_file($quarter, $filename){
$file_path = FCPATH . "24q/{$quarter}/{$filename}";
if(file_exists($file_path)){
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.basename($file_path).'"');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($file_path));
flush();
readfile($file_path);
exit;
} else {
echo "File not found";
exit;
}
}
// ======================= COMPANY INFORMATION ===========================
public function get_24q_company_info(){
$qry = "SELECT e.emp_name,f.pan_or_gnr_no as pan,f.tan_no AS tan,c.company_name,c.address,c.city,CONCAT(YEAR(f.start_date), '-', YEAR(f.end_date)) AS fin_year FROM cw_financial_setting f JOIN cw_employees e ON e.employee_code = f.incharge_employee
JOIN cw_company_information c ON c.prime_company_information_id = f.tan_no where f.set_as_default_financial_year = 1 and f.trans_status = 1";
$data = $this->db->query($qry);
$rslt = $data->result_array();
$data->next_result();
return $rslt;
}
// ================================= GET EMPLOYEE DATA =====================
public function get_24q_employee_data($months, $condition){
$data = [];
foreach($months as $month){
$qry = "SELECT ch.emp_code,e.emp_name,e.pan_number,ch.taxable_amt,ch.tds,ch.challan_no,ch.deposit_date,b.bsr_code,ch.cheque_no FROM cw_challan_entry_line ch JOIN cw_employees e ON e.employee_code = ch.emp_code JOIN cw_bank b ON b.prime_bank_id = ch.bank_name WHERE ch.process_month = '$month' AND e.trans_status = 1 $condition";
$qry_info = $this->db->query($qry);
$rows = $qry_info->result_array();
$qry_info->next_result();
$data = array_merge($data, $rows);
}
return $data;
}
public function build_form24q_txt($dir, $company, $quarter, $employees){
$lines = [];
$sr = 1;
$today = date('dmY');
/* FY / AY */
$fy = $company['fin_year']; // 2025-2026
$fyNoDash = str_replace('-', '', $fy); // 20252026
$ay = substr($fyNoDash, 4, 4) . substr($fyNoDash, 0, 4); // 20262025
/* -------------------------------------------------
* 1. FH
* ------------------------------------------------- */
$lines[] = "{$sr}^FH^SL1^R^{$today}^1^D^1^SMART HRMS";
$sr++;
/* -------------------------------------------------
* 2. BH
* ------------------------------------------------- */
$lines[] =
"{$sr}^BH^1^3^24Q^^^^^{$company['tan']}^^{$company['pan']}^{$ay}^{$fyNoDash}^{$quarter}^" .
"{$company['company_name']}^NA^{$company['address']}^^{$company['city']}";
$sr++;
/* -------------------------------------------------
* 3. CD (single challan)
* ------------------------------------------------- */
$totalTds = array_sum(array_column($employees, 'tds'));
$challanDate = date('dmY', strtotime($company['challan_date']));
$lines[] =
"{$sr}^CD^1^1^1^N^{$company['bsr_code']}^{$challanDate}^{$company['challan_no']}^^^^" .
"{$totalTds}^0^0^0^0^{$totalTds}";
$sr++;
/* -------------------------------------------------
* 4. DD (ALL quarters)
* ------------------------------------------------- */
$ddNo = 1;
foreach ($employees as $e) {
$monthYear = date('mY'); // processing month
$lines[] =
"{$sr}^DD^1^1^{$ddNo}^O^{$e['emp_code']}^^^{$e['pan']}^^^{$e['emp_name']}^^^" .
"{$e['tds']}^^^{$e['tds']}^{$monthYear}^{$monthYear}^{$challanDate}";
$sr++;
$ddNo++;
}
/* -------------------------------------------------
* 5. SD (ONLY Q4)
* ------------------------------------------------- */
if ($quarter === 'Q4') {
$fromDate = '01042025';
$toDate = '31032026';
$sdNo = 1;
foreach ($employees as $e) {
$lines[] =
"{$sr}^SD^1^{$sdNo}^A^^{$e['pan']}^^{$e['emp_name']}^S^{$fromDate}^{$toDate}^" .
"{$e['gross_salary']}^^0^0^{$e['taxable_salary']}^{$e['tds']}^{$e['gross_salary']}^^1";
$sr++;
$sdNo++;
}
}
/* -------------------------------------------------
* WRITE FILE
* ------------------------------------------------- */
$file = $dir . "form24q_{$quarter}.txt";
file_put_contents($file, implode(PHP_EOL, $lines));
return basename($file);
}
// ======================= HELPER METHODS ===========================
/**
* Validate month type parameter
*/
private function validateMonthType($month_type) {
$valid_months = array_keys($this->quarter_mapping);
if (!in_array($month_type, $valid_months)) {
throw new InvalidArgumentException("Invalid month type: {$month_type}. Valid values: " . implode(', ', $valid_months));
}
return $month_type;
}
/**
* Get financial year info with caching
*/
private function getFinancialYear() {
if ($this->financialInfo === null) {
$this->financialInfo = $this->get_financial_year();
}
return $this->financialInfo;
}
/**
* Calculate quarter months array
*/
private function calculateQuarterMonths($month_type, $currentYear) {
$months = [];
$startMonth = (int)$month_type;
for ($i = 0; $i < 3; $i++) {
$month = $startMonth + $i;
$year = $currentYear;
if ($month < 4) {
$year += 1;
}
$monthName = sprintf('%02d', $month) . '-' . $year;
$months[] = $monthName;
}
return $months;
}
/**
* Log operation for debugging and auditing
*/
private function logOperation($operation, $data = []) {
$logData = [
'operation' => $operation,
'user' => $this->session->userdata('user_id') ?? 'unknown',
'timestamp' => date('Y-m-d H:i:s'),
'data' => $data
];
log_message('info', json_encode($logData));
}
}