<?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/dao/DMLFunctions.php';require_once ROOT_PATH . '/lib/dao/SQLQBuilder.php';require_once ROOT_PATH . '/lib/confs/sysConf.php';require_once ROOT_PATH . '/lib/common/CommonFunctions.php';require_once ROOT_PATH . '/lib/models/eimadmin/export/CSVExportPlugin.php';require_once ROOT_PATH . '/lib/models/hrfunct/EmpDirectDebit.php';/** * Class to generate CSV file for export to Millenium Payroll */class MilleniumPayrollExport implements CSVExportPlugin {	const NAME = "Millenium Payroll Export";	/**	 * Get descriptive name for this plugin	 *	 * @return Name for this plugin	 */	public function getName() {		return self::NAME;	}	/**	 * Get CSV header	 *	 * @return CSV header	 */	public function getHeader() {		$header = "ID,Last Name,First Name,Middle Name,Address 1,Address 2, City,State,Zip,Gender,Birth Date," .		"SSN,Status,Hire Date,CC1,CC2,CC3,CC4,CC5,TaxForm,WorkState,Emp Type,Clock Number," .		"Home Phone,Ethnicity,Base Rate,Default Hours,Salary,Pay Frequency,Auto Pay," .		"FITW Status,FITW Exemptions,SITW State,SITW Status,SITW Exemptions,SITW Exemptions 2," .		"SUI State,Transit,DD1 Account,DD1 Amount,DD1 Amount Code,DD1 Checking,Transit," .		"DD2 Account,DD2 Amount,DD2 Amount Code,DD2 Checking";		return $header;	}	/**	 * Get CSV data as string	 *	 * @return string formatted csv data	 */	public function getCSVData() {		$sql = "SELECT hs_hr_employee.emp_number, employee_id, emp_lastname, emp_firstname, emp_middle_name, emp_street1, emp_street2," .		"city_code,provin_code,emp_zipcode,emp_gender,emp_birthday,emp_ssn_num,emp_status,joined_date, " .		"tax_federal_status, tax_federal_exceptions, tax_state, tax_state_status, tax_state_exceptions, " .		"tax_unemp_state,tax_work_state,custom1,custom2,custom3,custom4,custom5,custom6,custom7,custom8,custom9,custom10, " .		" pay.payperiod_code,sal.ebsal_basic_salary,loc.loc_name,comp.title " .		" FROM hs_hr_employee " .		" LEFT JOIN hs_hr_emp_us_tax tax on (tax.emp_number = hs_hr_employee.emp_number) " .		" LEFT JOIN hs_hr_emp_basicsalary sal on (hs_hr_employee.emp_number = sal.emp_number) " .		" LEFT JOIN hs_hr_payperiod pay on (sal.payperiod_code = pay.payperiod_code) " .		" LEFT JOIN hs_hr_compstructtree comp on (hs_hr_employee.work_station = comp.id) " .		" LEFT JOIN hs_hr_location loc on (comp.loc_code = loc.loc_code) ";		$conn = new DMLFunctions();		$result = $conn->executeQuery($sql);		$csv = "";		if ($result === false) {			throw new Exception("Error in query: " . $sql);		}		while ($row = mysql_fetch_assoc($result)) {			$csv .= $this->_getCSVRow($row) . "\n";		}		return $csv;	}	/**	 * Get CSV row from data retrieved from the database	 *	 * @param array $row Data row from database	 */	private function _getCSVRow($row) {		$genderValues = array (			1 => "M",			2 => "F"		);		$empStatuses = array (			"EST000" => "T",			"EST001" => "A",			"EST002" => "T"		);		$payPeriods = array (			 1 => "W",			 2 => "B",			 3 => "S",			 4 => "M",			 5 => "M1"		);		$transactionTypes = array (			EmpDirectDebit::TRANSACTION_TYPE_BLANK => 'Blank',			EmpDirectDebit::TRANSACTION_TYPE_PERCENTAGE => '%',			EmpDirectDebit::TRANSACTION_TYPE_FLAT => 'Flat',			EmpDirectDebit::TRANSACTION_TYPE_FLAT_MINUS => 'Flat-'		);		$accountTypes = array (			EmpDirectDebit::ACCOUNT_TYPE_CHECKING => "Y",			EmpDirectDebit::ACCOUNT_TYPE_SAVINGS => ""		);		$id = $this->_escape($row['employee_id']); // required		$lastName = $this->_escape($row['emp_lastname']);// required		$firstName = $this->_escape($row['emp_firstname']);// required		$middleName = $this->_escape($row['emp_middle_name']);// optional		$address1 = $this->_escape($row['emp_street1']);// optional		$address2 = $this->_escape($row['emp_street2']);// optional		$city = $this->_escape($row['city_code']);// optional		$state = $this->_escape($row['provin_code']);// optional		$zip = $this->_escape($row['emp_zipcode']);// required		$gender = $this->_escape($this->_getValueFromMap($genderValues, $row['emp_gender']));// optional		$birthDate = $this->_escape($this->_convertDate($row['emp_birthday']));// optional		$ssn = $this->_escape($row['emp_ssn_num']);// required		$status = $this->_escape($this->_getValueFromMap($empStatuses, $row['emp_status']));//required		$hireDate = $this->_escape($this->_convertDate($row['joined_date']));//required		$cc1 = $this->_escape($row['title']);//required (if set up in company)		$cc2 = $this->_escape($row['loc_name']);//required (if set up in company)		$cc3 = $this->_escape($row['custom1']);//required (if set up in company)		$cc4 = $this->_escape($row['custom2']);//required (if set up in company)		$cc5 = $this->_escape($row['custom3']);//required (if set up in company)		$taxForm = "";//optional		$workState = $this->_escape($row['tax_work_state']);//required		$empType = "";//optional		$clockNumber = "";//optional		$homePhone = "";//optional		$ethnicity = "";//optional		$baseRate = "";//optional		$defaultHours = "";//optional		$salary = $this->_escape($row['ebsal_basic_salary']);// required (if salary and not hourly)		$payFrequency = $this->_escape($this->_getValueFromMap($payPeriods, $row['payperiod_code']));// required		$autoPay = "";//optional		$FITWStatus = $this->_escape($row['tax_federal_status']);//required		$FITWExemptions = $this->_escape($row['tax_federal_exceptions']);//required		$SITWState = $this->_escape($row['tax_state']);//required		$SITWStatus = $this->_escape($row['tax_state_status']);//required		$SITWExemptions = $this->_escape($row['tax_state_exceptions']);//required		$SITWExemptions2 = "";//optional		$SUIState = $this->_escape($row['tax_unemp_state']);//required		$transit1 = "";//required if defined		$DD1Account = "";//required if defined		$DD1Amount = "";//required if defined		$DD1AmountCode = "";//required if defined		$DD1Checking = "";//required if defined		$transit2 = "";//required if defined		$DD2Account = "";//required if defined		$DD2Amount = "";//required if defined		$DD2AmountCode = "";//required if defined		$DD2Checking = "";//required if defined		// Get direct debit information		$dd = new EmpDirectDebit();		$ddList = $dd->getEmployeeDirectDebit($row['emp_number']);		if (count($ddList) > 0) {			$transit1 = $this->_escape($ddList[0]->getRoutingNumber());			$DD1Account = $this->_escape($ddList[0]->getAccount());			$DD1Amount = $this->_escape($ddList[0]->getAmount());			$DD1AmountCode = $this->_escape($this->_getValueFromMap($transactionTypes, $ddList[0]->getTransactionType()));			$DD1Checking = $this->_escape($this->_getValueFromMap($accountTypes, $ddList[0]->getAccountType()));		}		if (count($ddList) > 1) {			$transit2 = $this->_escape($ddList[1]->getRoutingNumber());			$DD2Account = $this->_escape($ddList[1]->getAccount());			$DD2Amount = $this->_escape($ddList[1]->getAmount());			$DD2AmountCode = $this->_escape($this->_getValueFromMap($transactionTypes, $ddList[1]->getTransactionType()));			$DD2Checking = $this->_escape($this->_getValueFromMap($accountTypes, $ddList[1]->getAccountType()));		}		$csvRow = "$id,$lastName,$firstName,$middleName,$address1,$address2,$city,$state,$zip,$gender,$birthDate," .		"$ssn,$status,$hireDate,$cc1,$cc2,$cc3,$cc4,$cc5,$taxForm,$workState,$empType,$clockNumber," .		"$homePhone,$ethnicity,$baseRate,$defaultHours,$salary,$payFrequency,$autoPay," .		"$FITWStatus,$FITWExemptions,$SITWState,$SITWStatus,$SITWExemptions,$SITWExemptions2," .		"$SUIState,$transit1,$DD1Account,$DD1Amount,$DD1AmountCode,$DD1Checking,$transit2," .		"$DD2Account,$DD2Amount,$DD2AmountCode,$DD2Checking";		return $csvRow;	}	/**	 * Convert date to mm/dd/yyyy format	 *	 * @param string $date Formatted date	 * @return string date formatted in mm/dd/yyyy format	 */	private function _convertDate($date) {		if ($date != "0000-00-00") {			$timestamp = strtotime($date);			$formattedDate = date("m/d/Y", $timestamp);		} else {			$formattedDate = "";		}		return $formattedDate;	}	private function _escape($value) {		$escapedValue = $value;		if (strpos($escapedValue, ",") !== false) {			if (strpos($escapedValue, '"') !== false) {				$escapedValue = str_replace('"', '""', $escapedValue);			}			$escapedValue = '"' . $escapedValue . '"';		}		return $escapedValue;	}	/**	 * Get the value for the given key from the map (array)	 *	 * @param array $map Associative array	 * @param string $key Key to look for	 *	 * @return string value for given key from array or an empty string if not found.	 */	private function _getValueFromMap($map, $key) {		$value = "";		if (array_key_exists($key, $map)) {			$value = $map[$key];		}		return $value;	}}?>