Gecko [ nrupathungauniversityblr.ac.in ]

Name	Size	Permission
api	[ DIR ]	drwxrwxrwx
assets	[ DIR ]	drwxrwxrwx
docs	[ DIR ]	drwxrwxrwx
img	[ DIR ]	drwxrwxrwx
img-clients	[ DIR ]	drwxrwxrwx
img-dept	[ DIR ]	drwxrwxrwx
img-empl	[ DIR ]	drwxrwxrwx
js	[ DIR ]	drwxrwxrwx
src	[ DIR ]	drwxrwxrwx
upload_docs	[ DIR ]	drwxrwxrwx
.ftpquota	14 B	-rwxr-xr-x
account_heads.php	5.56 KB	-rwxr-xr-x
agents.php	4.38 KB	-rwxr-xr-x
agents_save.php	8.31 KB	-rwxr-xr-x
attendance.php	2.83 KB	-rwxr-xr-x
attendance_report.php	7.45 KB	-rwxr-xr-x
attendance_report_excel.php	14.66 KB	-rw-rw-r--
control.php	8.22 KB	-rwxr-xr-x
departments.php	18.14 KB	-rwxr-xr-x
documents.php	10 KB	-rwxr-xr-x
documents_form.php	3.64 KB	-rwxr-xr-x
documents_grid.php	1.14 KB	-rwxr-xr-x
employees.php	1.08 KB	-rwxr-xr-x
employees_form.php	18.18 KB	-rwxr-xr-x
employees_grid.php	2.75 KB	-rwxr-xr-x
expense.php	3.35 KB	-rwxr-xr-x
expense_advance.php	7.38 KB	-rwxr-xr-x
expense_advance2.php	7.85 KB	-rw-rw-r--
expense_report.php	8.1 KB	-rwxr-xr-x
expense_report_summary.php	2.16 KB	-rwxr-xr-x
header.php	7.52 KB	-rwxr-xr-x
holidays.php	6.66 KB	-rwxr-xr-x
index.css	1.67 KB	-rw-rw-r--
index.html	4.69 KB	-rw-rw-r--
index11.08.2020.html	3.08 KB	-rwxr-xr-x
index19.04.2019.html	2.32 KB	-rwxr-xr-x
index27.05.2021.css	1.62 KB	-rwxr-xr-x
index27.05.2021.html	2.58 KB	-rwxr-xr-x
indexdir.php	2.82 KB	-rwxr-xr-x
items.php	5.11 KB	-rwxr-xr-x
itemstype.php	4.51 KB	-rwxr-xr-x
mainpage.php	2.88 KB	-rwxr-xr-x
mainpage19.04.2019.php	2.49 KB	-rwxr-xr-x
mc_print.php	7.15 KB	-rwxr-xr-x
messages.php	4.1 KB	-rwxr-xr-x
password_request.php	1.49 KB	-rwxr-xr-x
pg_app_edit.php	29.3 KB	-rwxr-xr-x
reminders.php	7.81 KB	-rwxr-xr-x
salary.php	3.18 KB	-rwxr-xr-x
salary_statement.php	3.77 KB	-rw-rw-r--
send_mail.php	3.58 KB	-rwxr-xr-x
send_sms.php	2.49 KB	-rwxr-xr-x
staff_move.php	11.7 KB	-rwxr-xr-x
style.css	16.12 KB	-rwxr-xr-x
style08.07.2020.css	14.33 KB	-rwxr-xr-x
style19.04.2019.css	12.65 KB	-rwxr-xr-x
task_reminder.php	1.09 KB	-rwxr-xr-x
task_reminder_form.php	4.72 KB	-rwxr-xr-x
task_reminder_grid.php	2.86 KB	-rwxr-xr-x
task_reminder_old.php	11.43 KB	-rwxr-xr-x
task_review.php	7.75 KB	-rwxr-xr-x
task_update.php	7.63 KB	-rwxr-xr-x
upcoming_events.php	3.12 KB	-rw-rw-r--
user_departments.php	3.63 KB	-rwxr-xr-x
user_logs.php	3.84 KB	-rwxr-xr-x
user_password.php	4.09 KB	-rwxr-xr-x
user_password_change.php	1.83 KB	-rwxr-xr-x
user_password_request.php	1.19 KB	-rwxr-xr-x
user_password_request13.12.202...	1.61 KB	-rwxr-xr-x
user_rights.php	2.96 KB	-rwxr-xr-x
user_setup.php	9.86 KB	-rwxr-xr-x
work_assign.php	1.13 KB	-rw-rw-r--
work_assign_form.php	4.84 KB	-rw-rw-r--
work_assign_grid.php	3.76 KB	-rw-rw-r--
work_entry.php	1.13 KB	-rwxr-xr-x
work_entry_form.php	8.02 KB	-rwxr-xr-x
work_entry_form_org.php	1.48 KB	-rwxr-xr-x
work_entry_grid.php	3.21 KB	-rwxr-xr-x
work_entry_report.php	4.01 KB	-rwxr-xr-x
work_review.php	14.09 KB	-rwxr-xr-x

Code Editor : attendance_report_excel.php

<?php

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL', (PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

include("sys_session.php");
include("sys_connect.php");
include("sys_mainphp.php");
$resp_mesg = "";
$resp_stat = "";
$resp_file = "";

$d = 1;
$mnth_days = cal_days_in_month(CAL_GREGORIAN, substr($_POST['attn_mnth'], 5, 2), substr($_POST['attn_mnth'], 0, 4));
if ($_POST['attn_mnth'] == date('Y-m')) {
    $entr_days = date('d') - 1;
    $rept_days = date('d');
} else {
    $entr_days = $mnth_days;
    $rept_days = $mnth_days;
}
for ($d = 1; $d <= $entr_days; $d++) {
    $caln_date = $_POST['attn_mnth'] . '-' . substr('0' . $d, -2, 2);
    if ($caln_date != date('Y-m-d')) {
        // INSERT HOLIDAY STATUS
        $mysql = "insert ignore into attendance (fdeptcode,fattndate,femplcode,fattnstat,fupdtuser,fattnremk,fupdttime)
        select em.fdeptcode,'$caln_date',em.femplcode,if(dayname('$caln_date')='Sunday','WO','HD') as fattnstat,
        'AUTO' as fupdtuser,'' as fattnremk, now() as fupdttime 
        from masempl em
        inner join masholiday hd on em.fdeptcode = hd.fdeptcode and hd.fholidate='$caln_date'
        where fempltype='p' and femplstat='T' and fattnentr='T';";
        $myres = mysqli_query($mycon, $mysql);

// INSERT ABSENT STATUS
        $mysql = "insert ignore into attendance (fdeptcode,fattndate,femplcode,fattnstat,fupdtuser,fattnremk,fupdttime)
        select em.fdeptcode,'$caln_date',em.femplcode,if(dayname('$caln_date')='Sunday','WO','AB') as fattnstat,
        'AUTO' as fupdtuser,'' as fattnremk, now() as fupdttime from masempl em
        left join attendance at on em.femplcode = at.femplcode and fattndate='$caln_date'
        where fempltype='p' and femplstat='T' and fattnentr='T' and at.femplcode is null;";
        $myres = mysqli_query($mycon, $mysql);
    }
}

require_once("phpexcel/Classes/PHPExcel.php");
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Logisys")
    ->setLastModifiedBy("Logisys")
    ->setTitle("Office 2007 XLSX Test Document")
    ->setSubject("Office 2007 XLSX Test Document")
    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
    ->setKeywords("office 2007 openxml php")
    ->setCategory("Report File");
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 2);

$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(10);
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);

$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1.0);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.5);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.5);
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1.0);

$objPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
$objPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);

$objPHPExcel->getActiveSheet()->setShowGridlines(true);

$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HLogisys, Bangalore' . chr(10) . 'Attendance Summary Report');
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . '&CPage &P of &N');

$mysql = "SET SESSION group_concat_max_len = 1000000;";
$myres = mysqli_query($mycon, $mysql);

$mysql = "select date_format(fattndate,'%Y-%m') as fattnmnth,dp.fdeptcode,dp.fdeptname,em.femplcode,em.femplname,dn.fdesnname,em.femplmobl,em.fsalrhead,
sum(case when fattnstat='PR' then 1 else 0 end) as ftotpr,
sum(case when fattnstat='AB' then 1 else 0 end) as ftotab,
sum(case when fattnstat='CL' then 1 else 0 end) as ftotcl,
sum(case when fattnstat in ('HD','WO')  then 1 else 0 end) as ftothd,
sec_to_time(sum(case when flogidiff>'-00:00:00' then time_to_sec(flogidiff) else 0 end)) as ftottime,
sum(case when flogidiff>'-00:00:00' then 1 else 0 end) as ftotlate,
group_concat(at.fattndate,',',at.fattnstat,',',at.flogitime,',',at.flogotime,',',flogidiff order by at.fattndate separator '|') as fattndetl 
from masempl em
inner join attendance at on em.femplcode = at.femplcode and date_format(at.fattndate,'%Y-%m')='$_POST[attn_mnth]'
inner join masdept dp on em.fdeptcode = dp.fdeptcode and dp.fdeptcode like '$_POST[dept_code]' and dp.fattnenbl='T'
inner join masdesn dn on em.fdesncode = dn.fdesncode
where fempltype='p' 
group by em.femplcode
order by em.fsalrhead,em.fdeptcode,dn.fdesnordr,dn.fdesncode,em.fempltype,em.femplname";
$myres = mysqli_query($mycon, $mysql);
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Sl. No.')
    ->setCellValue('B1', 'Company')
    ->setCellValue('C1', 'Client')
    ->setCellValue('D1', 'Month')
    ->setCellValue('E1', 'Empl ID')
    ->setCellValue('F1', 'Empl Name')
    ->setCellValue('G1', 'Summary')
    ->setCellValue('G2', 'Present')
    ->setCellValue('H2', 'Absent')
    ->setCellValue('I2', 'Leave With Pay')
    ->setCellValue('j2', 'Paid Holidays')
    ->setCellValue('K2', 'Leave W/O Pay')
    ->setCellValue('L2', 'Tot Late in H:M')
    ->setCellValue('M2', 'No. of Times Late');

$d = 1;
$j = 12;
$k = 13;
$l = 14;
for ($d = 1; $d <= $mnth_days; $d++) {
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue(getNameFromNumber($d + $j) . '1', $d)
        ->setCellValue(getNameFromNumber($d + $j) . '2', 'In Time')
        ->setCellValue(getNameFromNumber($d + $k) . '1', $d)
        ->setCellValue(getNameFromNumber($d + $k) . '2', 'Out Time')
        ->setCellValue(getNameFromNumber($d + $l) . '1', $d)
        ->setCellValue(getNameFromNumber($d + $l) . '2', 'Loss in Min.');

$objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $j) . '1')->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $k) . '1')->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $l) . '1')->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $j) . '2')->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $k) . '2')->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $l) . '2')->getAlignment()->setHorizontal('center')->setVertical('center');

$objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $j) . '1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $k) . '1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $l) . '1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $j) . '2')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $k) . '2')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $l) . '2')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->mergeCells(getNameFromNumber($d + $j) . '1:' . getNameFromNumber($d + $j + 2) . '1');

$j = $j + 2;
    $k = $k + 2;
    $l = $l + 2;
}

$i = 3;
while ($myrow = mysqli_fetch_object($myres)) {
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A' . $i, $i - 2)
        ->setCellValue('B' . $i, $myrow->fsalrhead)
        ->setCellValue('C' . $i, $myrow->fdeptname)
        ->setCellValue('D' . $i, $myrow->fattnmnth)
        ->setCellValue('E' . $i, $myrow->femplcode)
        ->setCellValue('F' . $i, $myrow->femplname)
        ->setCellValue('G' . $i, $myrow->ftotpr)
        ->setCellValue('H' . $i, $myrow->ftotab)
        ->setCellValue('I' . $i, $myrow->ftotcl)
        ->setCellValue('J' . $i, $myrow->ftothd)
        ->setCellValue('K' . $i, $myrow->ftotab)
        ->setCellValue('L' . $i, $myrow->ftottime)
        ->setCellValue('M' . $i, $myrow->ftotlate);

$d = 1;
    $j = 12;
    $k = 13;
    $l = 14;
    $attn_indx = explode('|', $myrow->fattndetl);
    for ($d = 1; $d <= $rept_days; $d++) {
        $attn_date = "";
        $attn_stat = "";
        $logi_time = "";
        $logo_time = "";
        $logi_diff = "";
        $attn_remk = "";

if (!isset($attn_indx[$d - 1])) {
            $attn_date = "-";
            $attn_stat = "-";
            $logi_time = "-";
            $logo_time = "-";
            $logi_diff = "-";
            $attn_remk = "-";
        } else {
            $attn_indz = explode(',', $attn_indx[$d - 1]);
            $attn_date = $attn_indz[0];
            $attn_stat = $attn_indz[1];
            $logi_time = $attn_indz[2];
            $logo_time = $attn_indz[3];
            $logi_diff = $attn_indz[4];

if ($attn_stat == 'AB') {
                $attn_remk = "Absent";
            } elseif ($attn_stat == 'WO') {
                $attn_remk = "Sunday";
            } elseif ($attn_stat == 'HD') {
                $attn_remk = "Holiday";
            } else {
                $attn_remk = $logi_diff;
            }
        }
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue(getNameFromNumber($d + $j) . $i, $logi_time)
            ->setCellValue(getNameFromNumber($d + $k) . $i, $logo_time)
            ->setCellValue(getNameFromNumber($d + $l) . $i, $attn_remk);
        $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $j) . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
        $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $k) . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
        $objPHPExcel->getActiveSheet()->getStyle(getNameFromNumber($d + $l) . $i)->getAlignment()->setHorizontal('center')->setVertical('center');

$j = $j + 2;
        $k = $k + 2;
        $l = $l + 2;
    }
    $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(25);
    $objPHPExcel->getActiveSheet()->getRowDimension($i + 1)->setRowHeight(25);
    $i++;
}

$objPHPExcel->getActiveSheet()->freezePane('A3');

$objPHPExcel->getActiveSheet()->mergeCells('A1:A2');
$objPHPExcel->getActiveSheet()->mergeCells('B1:B2');
$objPHPExcel->getActiveSheet()->mergeCells('C1:C2');
$objPHPExcel->getActiveSheet()->mergeCells('D1:D2');
$objPHPExcel->getActiveSheet()->mergeCells('E1:E2');
$objPHPExcel->getActiveSheet()->mergeCells('F1:F2');
$objPHPExcel->getActiveSheet()->mergeCells('G1:M1');

$objPHPExcel->getActiveSheet()->getStyle('A1:X2')->getAlignment()->setWrapText(true);

$objPHPExcel->getActiveSheet()->getStyle('A1:A' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('B1:B' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('C1:C' . $i)->getAlignment()->setHorizontal('left')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('D1:D' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('E1:E' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('F1:F' . $i)->getAlignment()->setHorizontal('left')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('G1:G' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('H1:H' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('I1:I' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('J1:J' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('K1:K' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('L1:L' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('M1:M' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');

$objPHPExcel->getActiveSheet()->getStyle('A1:X2')->getAlignment()->setHorizontal('center')->setVertical('center');
$objPHPExcel->getActiveSheet()->getStyle('A1:X2')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);
$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(30);

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10);

$objPHPExcel->getActiveSheet()->setTitle('attendance_summary');

$objPHPExcel->setActiveSheetIndex(0);

$file_name = "attendance_summary_report_" . $_POST['attn_mnth'] . ".xlsx";
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $file_name . '"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($file_name);
$resp_file = $file_name;
echo json_encode(array("mesg" => $resp_mesg, "stat" => $resp_stat, "file" => $resp_file));

${this.title}

Code Editor : attendance_report_excel.php