File: /home/cafsindia/.trash/application_bkold/controllers/Twentyfour_q_quarterly.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Twentyfour_q_quarterly extends Action_controller{
public function __construct(){
parent::__construct('twentyfour_q_quarterly');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$this->load->view("$this->control_name/manage",$data);
}
public function check_exist($month_type){
$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;
$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);
//check all the 3 months has data
$challan_qry = 'SELECT COUNT(DISTINCT c.process_month) AS month 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_array();
$challan_data->next_result();
foreach($challan_rslt as $val){
$month = $val['month'];
}
if($month === '3'){
echo json_encode(array('success' => TRUE, 'message' => "Generated Successfully..!"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "No Records Found.!"));
}
}
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,c.pan as pan,c.gstin 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){
$filename = dirname(__FILE__)."/php_excel/PHPExcel/IOFactory.php";
include($filename);
$inputFileType = PHPExcel_IOFactory::identify('./excel_write/24Q_Quarterly.xlsx');
$excel2 = PHPExcel_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 = PHPExcel_Cell::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()->setCellValue("A".$j, $j-3).";";
$excel2->getActiveSheet()->setCellValue($col_name.$j, "$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 = PHPExcel_Cell::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()->setCellValue("A".$j, $j-3).";";
$excel2->getActiveSheet()->setCellValue($col_name.$j, "$row_value").";";
$excel2->getActiveSheet()->setCellValue("E".$j, $sal_end_date).";";
$excel2->getActiveSheet()->setCellValue("L".$j, $sal_end_date).";";
$x++;
}
$j++;
}
//Challan Entry - END
$z++;
}
$excel2->setActiveSheetIndex(0);
$filename = "24q_quarterly_$month_val.xls";
ob_end_clean();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
ob_end_clean();
$objWriter = PHPExcel_IOFactory::createWriter($excel2, "Excel5");
$objWriter->save('php://output');
}
}
//Get Excel column from number
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;
}
}
}
?>