public static function readKey()
{
if (!self::keyExists()) {
throw new KeyHandlerOldException('Key file does not exist', KeyHandlerOldException::KEY_DOES_NOT_EXIST);
}
if (!is_readable(ROOT_PATH . self::$filePath)) {
throw new KeyHandlerOldException('Key is not readable', KeyHandlerOldException::KEY_NOT_READABLE);
}
if (!self::$keySet) {
self::$key = trim(file_get_contents(ROOT_PATH . self::$filePath));
self::$keySet = true;
}
return self::$key;
}
/**
* Sets up the fixture, for example, open a network connection.
* This method is called before a test is executed.
*
* @access protected
*/
protected function setUp()
{
$this->exportObj = new MilleniumPayrollExport();
$conf = new Conf();
$this->connection = mysql_connect($conf->dbhost . ":" . $conf->dbport, $conf->dbuser, $conf->dbpass);
mysql_select_db($conf->dbname);
$this->_truncateTables();
$sql = "INSERT INTO hs_hr_employee" . "(emp_number, employee_id, emp_lastname, emp_firstname, emp_middle_name, " . "emp_nick_name, emp_smoker, ethnic_race_code, emp_birthday, nation_code, " . "emp_gender, emp_marital_status, emp_ssn_num, emp_sin_num, emp_other_id, " . "emp_dri_lice_num, emp_dri_lice_exp_date, emp_military_service, emp_status, " . "job_title_code, eeo_cat_code, work_station, " . "emp_street1, emp_street2, city_code, coun_code, provin_code, emp_zipcode, " . "emp_hm_telephone, emp_mobile, emp_work_telephone, emp_work_email, " . "sal_grd_code, joined_date,\temp_oth_email) VALUES (" . "'10', 'E1921A', 'Karunadasa', 'Kamal', 'K', " . "NULL, NULL, NULL, '1974-11-20', NULL, " . "1, NULL, '987654320', '', '', " . "null, NULL, NULL, 'EST001', " . "NULL, NULL, NULL, " . "'111 Main Street', 'SUITE A29', 'Houston', 'US', 'TX', '77845', " . "'', '', '', NULL, " . "NULL, '1997-12-11', NULL)";
if (KeyHandlerOld::keyExists()) {
$key = KeyHandlerOld::readKey();
$sql = str_replace("'987654320'", "AES_ENCRYPT('987654320', '{$key}')", $sql);
}
$this->assertTrue(mysql_query($sql), mysql_error());
$sql = "INSERT into hs_hr_emp_us_tax(emp_number, tax_federal_status, tax_federal_exceptions, " . "tax_state, tax_state_status, tax_state_exceptions, tax_unemp_state,tax_work_state) VALUES (" . "10, 'NRA', 2, 'MD', 'NA', 3, 'VA', 'AZ')";
$this->assertTrue(mysql_query($sql), mysql_error());
}
/**
* 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) ";
if (KeyHandlerOld::keyExists()) {
$key = KeyHandlerOld::readKey();
$sql = str_replace("emp_ssn_num", "IF(`emp_ssn_num` IS NOT NULL, AES_DECRYPT(emp_ssn_num, '{$key}'), '') AS `emp_ssn_num`", $sql);
$sql = str_replace("sal.ebsal_basic_salary", "IF(`ebsal_basic_salary` IS NOT NULL, AES_DECRYPT(ebsal_basic_salary, '{$key}'), '') AS `ebsal_basic_salary`", $sql);
}
$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;
}
public static function prepareEncryptFields($encryptFieldsArray, $encryptValuesArray)
{
$encOn = KeyHandlerOld::KeyExists();
$valuesArray = array();
$encryptFieldsArrayCount = count($encryptFieldsArray);
for ($i = 0; $i < $encryptFieldsArrayCount; $i++) {
if ($encOn && self::isEncField($encryptFieldsArray[$i])) {
$key = KeyHandlerOld::readKey();
if ($encryptValuesArray[$i] == null) {
$valuesArray[$i] = null;
} else {
$valuesArray[$i] = "AES_ENCRYPT({$encryptValuesArray[$i]}, '{$key}')";
}
} else {
$valuesArray[$i] = $encryptValuesArray[$i];
}
}
return $valuesArray;
}
/**
* Sets up the fixture, for example, open a network connection.
* This method is called before a test is executed.
*
* @access protected
*/
protected function setUp()
{
$conf = new Conf();
$this->connection = mysql_connect($conf->dbhost . ":" . $conf->dbport, $conf->dbuser, $conf->dbpass);
mysql_select_db($conf->dbname);
$this->_truncateTables();
// insert some test data
$this->_runQuery("INSERT INTO hs_hr_custom_export(export_id, name, fields, headings) VALUES (1, 'Export 1', 'empId,lastName,firstName,middleName,street1,street2,city', '')");
$this->_runQuery("INSERT INTO hs_hr_custom_export(export_id, name, fields, headings) VALUES (2, 'Export 2', 'empId,lastName,firstName,city', 'Employee Id,Last Name,First Name,City')");
$this->_runQuery("INSERT INTO hs_hr_custom_export(export_id, name, fields, headings) VALUES (3, 'Export 3', 'empId,street1,street2,city', 'Employee Id,Address1, Address2, City')");
// insert some employee data
$sql = "INSERT INTO hs_hr_employee" . "(emp_number, employee_id, emp_lastname, emp_firstname, emp_middle_name, " . "emp_nick_name, emp_smoker, ethnic_race_code, emp_birthday, nation_code, " . "emp_gender, emp_marital_status, emp_ssn_num, emp_sin_num, emp_other_id, " . "emp_dri_lice_num, emp_dri_lice_exp_date, emp_military_service, emp_status, " . "job_title_code, eeo_cat_code, work_station, " . "emp_street1, emp_street2, city_code, coun_code, provin_code, emp_zipcode, " . "emp_hm_telephone, emp_mobile, emp_work_telephone, emp_work_email, " . "sal_grd_code, joined_date,\temp_oth_email, " . "custom1, custom2, custom3, custom4, custom5, " . "custom6, custom7, custom8, custom9, custom10) VALUES (" . "'10', 'E1921A', 'Karunadasa', 'Kamal', 'K', " . "NULL, NULL, NULL, '1974-11-20', NULL, " . "1, NULL, '987654320', '', '', " . "null, NULL, NULL, 'EST001', " . "NULL, NULL, NULL, " . "'111 Main Street', 'SUITE A29', 'Houston', 'US', 'TX', '77845', " . "'', '', '', NULL, " . "NULL, '1997-12-11', NULL, " . "'c1', 'c2', 'c3', 'c4', 'c5'," . "'c6', 'c7', 'c8', 'c9', 'c10'" . ")";
if (KeyHandlerOld::keyExists()) {
$key = KeyHandlerOld::readKey();
$sql = str_replace("'987654320'", "AES_ENCRYPT('987654320', '{$key}')", $sql);
}
$this->_runQuery($sql);
$sql = "INSERT into hs_hr_emp_us_tax(emp_number, tax_federal_status, tax_federal_exceptions, " . "tax_state, tax_state_status, tax_state_exceptions, tax_unemp_state,tax_work_state) VALUES (" . "10, 'NRA', 2, 'MD', 'NA', 3, 'VA', 'AZ')";
$this->_runQuery($sql);
// Add direct debit information
$dd = new EmpDirectDebit();
$dd->setEmpNumber(10);
$dd->setRoutingNumber(11111);
$dd->setAccount('AC 1');
$dd->setAmount(121);
$dd->setAccountType('CHECKING');
$dd->setTransactionType('BLANK');
$this->assertTrue($dd->add(), mysql_error());
$dd = new EmpDirectDebit();
$dd->setEmpNumber(10);
$dd->setRoutingNumber(22222);
$dd->setAccount('AC #2');
$dd->setAmount(23);
$dd->setAccountType('SAVINGS');
$dd->setTransactionType('FLATMINUS');
$this->assertTrue($dd->add(), mysql_error());
}
public function testPrepareEncryptFields()
{
$key = KeyHandlerOld::readKey();
$values[] = '123456';
$values[] = 'abcd';
$values[] = 'pqr';
$fields[] = 'emp_ssn_num';
$fields[] = 'ebsal_basic_salary';
$fields[] = 'hs_hr_emp_language';
$expected[] = "AES_ENCRYPT(123456, '{$key}')";
$expected[] = "AES_ENCRYPT(abcd, '{$key}')";
$expected[] = "pqr";
$result = CryptoQuery::prepareEncryptFields($fields, $values);
$this->assertEquals($expected, $result);
$fields = null;
$values = null;
$expected = null;
$fields[] = 'EMP_NUMBER';
$fields[] = 'EMP_SMOKER';
$fields[] = 'ETHNIC_RACE_CODE';
$fields[] = 'EMP_BIRTHDAY';
$fields[] = 'NATION_CODE';
$fields[] = 'EMP_GENDER';
$fields[] = 'EMP_MARITAL_STATUS';
$fields[] = 'EMP_SSN_NUM';
$fields[] = 'EMP_SIN_NUM';
$fields[] = 'EMP_OTHER_ID';
$fields[] = 'EMP_DRI_LICE_NUM';
$fields[] = 'EMP_DRI_LICE_EXP_DATE';
$fields[] = 'EMP_MILITARY_SERVICE';
$values[] = '001';
$values[] = '0';
$values[] = null;
$values[] = null;
$values[] = null;
$values[] = '1';
$values[] = '0';
$values[] = '125';
$values[] = '123';
$values[] = '';
$values[] = '';
$values[] = null;
$values[] = '';
foreach ($values as $value) {
if ($value == null) {
$expected[] = null;
} elseif ($value == 125) {
$expected[] = "AES_ENCRYPT({$value}, '{$key}')";
} else {
$expected[] = $value;
}
}
$result = CryptoQuery::prepareEncryptFields($fields, $values);
$this->assertEquals($expected, $result);
}
/**
* @todo Implement testDeleteKey().
*/
public function testDeleteKey()
{
// When key is not available
try {
KeyHandlerOld::deleteKey();
} catch (Exception $e) {
$this->assertEquals(KeyHandlerOldException::KEY_DOES_NOT_EXIST, $e->getCode());
}
// When key is existing
$filePath = ROOT_PATH . '/lib/confs/cryptokeys/key.ohrm';
$this->assertTrue(KeyHandlerOld::createKey());
$this->assertTrue(KeyHandlerOld::deleteKey());
$this->assertFalse(file_exists($filePath));
// When key is existing, but cannot be deleted
$this->assertTrue(KeyHandlerOld::createKey());
system("chmod 000 {$filePath}");
try {
KeyHandlerOld::deleteKey();
} catch (Exception $e) {
$this->assertEquals(KeyHandlerOldException::KEY_DELETION_FAILIURE, $e->getCode());
}
}