📄 reportgenerator.php
字号:
<?php
/*
// OrangeHRM is a comprehensive Human Resource Management (HRM) System that captures
// all the essential functionalities required for any enterprise.
// Copyright (C) 2006 OrangeHRM Inc., http://www.orangehrm.com
// OrangeHRM is free software; you can redistribute it and/or modify it under the terms of
// the GNU General Public License as published by the Free Software Foundation; either
// version 2 of the License, or (at your option) any later version.
// OrangeHRM is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
// without even the implied warranty of MERCHANTabILITY or FITNESS FOR A PARTICULAR PURPOSE.
// See the GNU General Public License for more details.
// You should have received a copy of the GNU General Public License along with this program;
// if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
// Boston, MA 02110-1301, USA
*/
require_once ROOT_PATH . '/lib/confs/sysConf.php';
class ReportGenerator {
var $criteria;
var $field;
var $repID;
var $repName;
var $headName;
var $employeeIdLength;
function ReportGenerator() {
$this->criteria=array();
$this->field=array();
$sysConfObj = new sysConf();
$this->employeeIdLength = $sysConfObj->getEmployeeIdLength();
}
function ageToYear($age) {
$currYear= strftime('%Y');
$currMonthDate = strftime('-%m-%d');
$birthYear = (int)$currYear - $age;
return $birthYear . $currMonthDate;
}
function reportQueryBuilder(){
$groupBy = null;
if(isset($this->criteria['AGE'])){
$parameter = $this->criteria['AGE'];
$pointer = explode('|',$parameter);
$criteriaValue['AGE1'] = '\'' . $this->ageToYear($pointer[1]) . '\'';
$criteriaField['AGE1'] = 'a.EMP_BIRTHDAY';
$criteriaTable['AGE1'] = 'HS_HR_EMPLOYEE a';
switch ($pointer[0]) {
case '<' : $criteriaComOper['AGE1'] = "<"; break;
case '>' : $criteriaComOper['AGE1'] = ">"; break;
case 'range' : $criteriaComOper['AGE1'] = "<";
$criteriaValue['AGE2'] = '\'' . $this->ageToYear($pointer[2]) . '\'';
$criteriaField['AGE2'] = 'a.EMP_BIRTHDAY';
$criteriaTable['AGE2'] = 'HS_HR_EMPLOYEE a';
$criteriaComOper['AGE2'] = ">";
}
}
if(isset($this->criteria['EMPNO'])) {
if ($this->criteria['EMPNO'] == '') {
unset($this->criteria['EMPNO']);
} else {
$criteriaValue['EMPNO'] = '\'' .$this->criteria['EMPNO'] . '\'';
$criteriaComOper['EMPNO'] = "=";
$criteriaField['EMPNO'] = 'a.EMP_NUMBER';
$criteriaTable['EMPNO'] = 'HS_HR_EMPLOYEE a';
}
}
if(isset($this->criteria['PAYGRD'])){
$criteriaValue['PAYGRD'] = '\'' . $this->criteria['PAYGRD'] . '\'';
$criteriaField['PAYGRD'] = 'b.SAL_GRD_CODE';
$criteriaTable['PAYGRD'] = 'HS_PR_SALARY_GRADE b';
$criteriaComOper['PAYGRD'] = "=";
$parentTableFieldName['PAYGRD'] = 'b.SAL_GRD_CODE';
$parentTableName['PAYGRD'] = 'HS_PR_SALARY_GRADE b';
$existingTableFieldName['PAYGRD']= 'f.SAL_GRD_CODE';
$existingTableName['PAYGRD'] = 'HS_HR_JOB_TITLE f';
$parentTableFieldName['JOBTITLE'] = 'f.JOBTIT_CODE';
$parentTableName['JOBTITLE'] = 'HS_HR_JOB_TITLE f';
$existingTableFieldName['JOBTITLE'] = 'a.JOB_TITLE_CODE';
$existingTableName['JOBTITLE'] = 'HS_HR_EMPLOYEE a';
}
if (isset($this->criteria['EMPSTATUS'])){
$criteriaValue['EMPSTATUS'] = '\'' . $this->criteria['EMPSTATUS'] . '\'';
$criteriaField['EMPSTATUS'] = 'a.EMP_STATUS';
$criteriaTable['EMPSTATUS'] = 'HS_HR_EMPLOYEE a';
$criteriaComOper['EMPSTATUS'] = "=";
}
// Service Period
if(isset($this->criteria['SERPIR'])){
$parameter = $this->criteria['SERPIR'];
$pointer = explode('|',$parameter);
$criteriaValue['SERPIR1'] = '\'' . $this->ageToYear($pointer[1]) . '\'';
$criteriaField['SERPIR1'] = 'a.JOINED_DATE';
$criteriaTable['SERPIR1'] = 'HS_HR_EMPLOYEE a';
switch ($pointer[0]) {
case '<' : $criteriaComOper['SERPIR1'] = "<"; break;
case '>' : $criteriaComOper['SERPIR1'] = ">"; break;
case 'range' : $criteriaComOper['SERPIR1'] = "<";
//$criteriaValue['SERPIR2'] = '\'' . $this->ageToYear($pointer[2]) . '\'';
$criteriaValue['SERPIR1'] = '\'' . $this->ageToYear($pointer[1]) . '\'';
$criteriaValue['SERPIR2'] = '\'' . $this->ageToYear($pointer[2]) . '\'';
$criteriaField['SERPIR2'] = 'a.JOINED_DATE';
$criteriaTable['SERPIR2'] = 'HS_HR_EMPLOYEE a';
$criteriaComOper['SERPIR2'] = ">";
}
}
// Service Period Ends
// Joined Date
if(isset($this->criteria['JOIDAT'])){
$parameter = $this->criteria['JOIDAT'];
$pointer = explode('|',$parameter);
//$criteriaValue['SERPIR1'] = '\'' . $this->ageToYear($pointer[1]) . '\'';
$criteriaValue['JOIDAT1'] = '\'' . $pointer[1] . '\'';
$criteriaField['JOIDAT1'] = 'a.JOINED_DATE';
$criteriaTable['JOIDAT1'] = 'HS_HR_EMPLOYEE a';
switch ($pointer[0]) {
case '<' : $criteriaComOper['JOIDAT1'] = "<"; break;
case '>' : $criteriaComOper['JOIDAT1'] = ">"; break;
case 'range' : $criteriaComOper['JOIDAT1'] = "<";
$criteriaValue['JOIDAT1'] = '\'' . $pointer[2] . '\'';
$criteriaValue['JOIDAT2'] = '\'' . $pointer[1] . '\'';
$criteriaField['JOIDAT2'] = 'a.JOINED_DATE';
$criteriaTable['JOIDAT2'] = 'HS_HR_EMPLOYEE a';
$criteriaComOper['JOIDAT2'] = ">";
}
}
// Joined Date Ends
if(isset($this->criteria['QUL'])){
$criteriaValue['QUL'] = '\'' . $this->criteria['QUL'] . '\'';
$criteriaField['QUL'] = 'c.EDU_CODE';
$criteriaTable['QUL'] = 'HS_HR_EMP_EDUCATION c';
$criteriaComOper['QUL'] = "=";
}
if(isset($this->criteria['JOBTITLE'])){
$criteriaValue['JOBTITLE'] = '\'' . $this->criteria['JOBTITLE'] . '\'';
$criteriaField['JOBTITLE'] = 'a.JOB_TITLE_CODE';
$criteriaTable['JOBTITLE'] = 'HS_HR_EMPLOYEE a';
$criteriaComOper['JOBTITLE'] = "=";
}
if(isset($this->criteria['LANGUAGE'])){
$criteriaValue['LANGUAGE'] = '\'' . $this->criteria['LANGUAGE'] . '\'';
$criteriaField['LANGUAGE'] = 'm.LANG_CODE';
$criteriaTable['LANGUAGEL'] = 'HS_HR_EMP_LANGUAGE m';
$criteriaComOper['LANGUAGE'] = "=";
$parentTableFieldName['LANGUAGES'] = 'n.LANG_CODE';
$parentTableName['LANGUAGES'] = 'HS_HR_LANGUAGE n';
$existingTableFieldName['LANGUAGES'] = 'm.LANG_CODE';
$existingTableName['LANGUAGES'] = 'HS_HR_EMP_LANGUAGE m';
}
if(isset($this->criteria['SKILL'])){
$criteriaValue['SKILL'] = '\'' . $this->criteria['SKILL'] . '\'';
$criteriaField['SKILL'] = 'h.SKILL_CODE';
$criteriaTable['SKILL'] = 'HS_HR_EMP_SKILL h';
$criteriaComOper['SKILL'] = "=";
$parentTableFieldName['SKILLS'] = 'i.SKILL_CODE';
$parentTableName['SKILLS'] = 'HS_HR_SKILL i';
$existingTableFieldName['SKILLS'] = 'h.SKILL_CODE';
$existingTableName['SKILLS'] = 'HS_HR_EMP_SKILL h';
}
// Select fields
if(isset($this->field['EMPNO'])==1){
$headingName['EMPNO'] = 'Employee No';
$parentTableFieldName['EMPNO'] = 'LPAD(a.`EMP_NUMBER`, '.$this->employeeIdLength.', 0)';
$parentTableName['EMPNO'] = 'HS_HR_EMPLOYEE a';
$existingTableFieldName['EMPNO']= 'a.EMP_NUMBER';
$existingTableName['EMPNO'] = 'HS_HR_EMPLOYEE a';
$parentTableDescription['EMPNO']= 'a.`EMP_NUMBER`, IFNULL(a.`EMPLOYEE_ID`, LPAD(a.`EMP_NUMBER`, '.$this->employeeIdLength.', 0))';
$groupBy['EMPNO'] = $parentTableDescription['EMPNO'];
}
if(isset($this->field['EMPFIRSTNAME'])==1){
$headingName['EMPFIRSTNAME'] = 'Employee First Name';
$parentTableFieldName['EMPFIRSTNAME'] = 'a.EMP_FIRSTNAME';
$parentTableName['EMPFIRSTNAME'] = 'HS_HR_EMPLOYEE a';
$existingTableFieldName['EMPFIRSTNAME']= 'a.EMP_FIRSTNAME';
$existingTableName['EMPFIRSTNAME'] = 'HS_HR_EMPLOYEE a';
$parentTableDescription['EMPFIRSTNAME']= 'a.`EMP_NUMBER`, a.EMP_FIRSTNAME';
$groupBy['EMPFIRSTNAME'] = $parentTableDescription['EMPFIRSTNAME'];
}
if(isset($this->field['EMPLASTNAME'])==1){
$headingName['EMPLASTNAME'] = 'Employee Last Name';
$parentTableFieldName['EMPLASTNAME'] = 'a.EMP_LASTNAME';
$parentTableName['EMPLASTNAME'] = 'HS_HR_EMPLOYEE a';
$existingTableFieldName['EMPLASTNAME']= 'a.EMP_LASTNAME';
$existingTableName['EMPLASTNAME'] = 'HS_HR_EMPLOYEE a';
$parentTableDescription['EMPLASTNAME']= 'a.`EMP_NUMBER`, a.EMP_LASTNAME';
$groupBy['EMPLASTNAME'] = $parentTableDescription['EMPLASTNAME'];
}
if(isset($this->field['ADDRESS1'])==1){
$headingName['ADDRESS1'] = 'Address';
$parentTableFieldName['ADDRESS1'] = 'a.EMP_STREET1';
$parentTableName['ADDRESS1'] = 'HS_HR_EMPLOYEE a';
$existingTableFieldName['ADDRESS1'] = 'a.EMP_STREET1';
$existingTableName['ADDRESS1'] = 'HS_HR_EMPLOYEE a';
$parentTableDescription['ADDRESS1'] = "a.`EMP_NUMBER`, CONCAT(a.EMP_STREET1, ' ', a.EMP_STREET2, ' ', a.CITY_CODE, ' ', a.PROVIN_CODE, ' ', a.COUN_CODE, ' ', a.EMP_ZIPCODE)";
$groupBy['ADDRESS1'] = $parentTableDescription['ADDRESS1'];
}
if(isset($this->field['ADDRESS2'])==1){
$parentTableFieldName['ADDRESS2'] = 'a.EMP_STREET2';
$parentTableName['ADDRESS2'] = 'HS_HR_EMPLOYEE a';
$existingTableFieldName['ADDRESS2'] = 'a.EMP_STREET2';
$existingTableName['ADDRESS2'] = 'HS_HR_EMPLOYEE a';
$parentTableDescription['ADDRESS2'] = 'a.`EMP_NUMBER`, a.EMP_STREET2';
$groupBy['ADDRESS2'] = $parentTableDescription['ADDRESS2'];
}
if(isset($this->field['TELENO'])==1){
$headingName['TELENO'] = 'Telephone';
$parentTableFieldName['TELENO'] = 'a.EMP_HM_TELEPHONE';
$parentTableName['TELENO'] = 'HS_HR_EMPLOYEE a';
$existingTableFieldName['TELENO'] = 'a.EMP_HM_TELEPHONE';
$existingTableName['TELENO'] = 'HS_HR_EMPLOYEE a';
$parentTableDescription['TELENO'] = 'a.`EMP_NUMBER`, a.EMP_HM_TELEPHONE';
$groupBy['TELENO'] = $parentTableDescription['TELENO'];
}
if(isset($this->field['MOBILE'])==1){
$headingName['MOBILE'] = 'Mobile';
$parentTableFieldName['MOBILE'] = 'a.EMP_MOBILE';
$parentTableName['MOBILE'] = 'HS_HR_EMPLOYEE a';
$existingTableFieldName['MOBILE'] = 'a.EMP_MOBILE';
$existingTableName['MOBILE'] = 'HS_HR_EMPLOYEE a';
$parentTableDescription['MOBILE'] = 'a.`EMP_NUMBER`, a.EMP_MOBILE';
$groupBy['MOBILE'] = $existingTableFieldName['MOBILE'];
}
if(isset($this->field['REPORTTO'])==1){
$headingName['REPORTTO'] = 'Report to';
$parentTableFieldName['REPORTTO'] = 'j.EMP_NUMBER';
$parentTableName['REPORTTO'] = 'HS_HR_EMPLOYEE j';
$existingTableFieldName['REPORTTO']= 'g.EREP_SUP_EMP_NUMBER';
$existingTableName['REPORTTO'] = 'HS_HR_EMP_REPORTTO g';
$parentTableDescription['REPORTTO']= "g.EREP_SUP_EMP_NUMBER, CONCAT(j.EMP_FIRSTNAME, ' ', j.EMP_LASTNAME)";
$groupBy['REPORTTO'] = $existingTableFieldName['REPORTTO'];
}
if(isset($this->field['REPORTINGMETHOD'])==1){
$headingName['REPORTINGMETHOD'] = 'Reporting method';
$parentTableFieldName['REPORTINGMETHOD'] = 'g.EREP_SUB_EMP_NUMBER';
$parentTableName['REPORTINGMETHOD'] = 'HS_HR_EMP_REPORTTO g';
$existingTableFieldName['REPORTINGMETHOD']= 'a.EMP_NUMBER';
$existingTableName['REPORTINGMETHOD'] = 'HS_HR_EMPLOYEE a';
$parentTableDescription['REPORTINGMETHOD']= 'g.EREP_SUP_EMP_NUMBER, g.EREP_REPORTING_MODE';
$groupBy['REPORTINGMETHOD'] = $existingTableFieldName['REPORTINGMETHOD'];
}
if(isset($this->field['AGE'])==1) {
$headingName['AGE'] = 'Date of Birth';
$parentTableFieldName['AGE'] = 'a.EMP_BIRTHDAY';
$parentTableName['AGE'] = 'HS_HR_EMPLOYEE a';
$existingTableFieldName['AGE'] = 'a.EMP_BIRTHDAY';
$existingTableName['AGE'] = 'HS_HR_EMPLOYEE a';
$parentTableDescription['AGE'] = "a.`EMP_NUMBER`, IF(STRCMP(DATE_FORMAT(a.EMP_BIRTHDAY, CONCAT(YEAR(a.EMP_BIRTHDAY), '-%m-%d')), '0-00-00'), DATE_FORMAT(a.EMP_BIRTHDAY, CONCAT(YEAR(a.EMP_BIRTHDAY), '-%m-%d')), '鈥⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -