/**
* Set up model. Calls base class Setup and adds encryption support
* for ssn.
*/
public function setUp()
{
parent::setup();
if (KeyHandler::keyExists()) {
$key = KeyHandler::readKey();
$this->addListener(new EncryptionListener('ssn', $key));
}
}
public function testPrepareEncryptFields()
{
$key = KeyHandler::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[] = '0000-00-00';
$values[] = null;
$values[] = '1';
$values[] = '0';
$values[] = '125';
$values[] = '123';
$values[] = '';
$values[] = '';
$values[] = '0000-00-00';
$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);
}
/**
* Configure doctrine connections to use tablename prefix hs_hr_
*/
public function configureDoctrine(Doctrine_Manager $manager)
{
$manager->setAttribute(Doctrine_Core::ATTR_VALIDATE, Doctrine_Core::VALIDATE_ALL);
$manager->setAttribute(Doctrine_Core::ATTR_USE_NATIVE_ENUM, true);
//
// If using encryption, enable dql callbacks. Needed by EncryptionListener
//
if (KeyHandler::keyExists()) {
$manager->setAttribute(Doctrine_Core::ATTR_USE_DQL_CALLBACKS, true);
}
//$manager->setAttribute(Doctrine::ATTR_TBLNAME_FORMAT, 'hs_hr_%s');
}
public static function readKey()
{
if (!self::keyExists()) {
throw new KeyHandlerException('Key file does not exist', KeyHandlerException::KEY_DOES_NOT_EXIST);
}
if (!is_readable(ROOT_PATH . self::$filePath)) {
throw new KeyHandlerException('Key is not readable', KeyHandlerException::KEY_NOT_READABLE);
}
if (!self::$keySet) {
self::$key = trim(file_get_contents(ROOT_PATH . self::$filePath));
self::$keySet = true;
}
return self::$key;
}
/**
* Initilizes cryptographic scheme
*/
private static function init()
{
if (is_null(self::$cryptographicScheme)) {
$key = KeyHandler::readKey();
$mysqlKey = "";
for ($a = 0; $a < strlen($key); $a++) {
$mysqlKey[$a % 16] = chr(ord($mysqlKey[$a % 16]) ^ ord($key[$a]));
}
$aes = new Crypt_Rijndael(CRYPT_RIJNDAEL_MODE_ECB);
$aes->setKeyLength(128);
$aes->setBlockLength(128);
$aes->setKey($mysqlKey);
self::$cryptographicScheme = $aes;
}
}
/**
* 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 (KeyHandler::keyExists()) {
$key = KeyHandler::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());
}
/**
* Configure doctrine connections to use tablename prefix hs_hr_
*/
public function configureDoctrine(Doctrine_Manager $manager)
{
$manager->setAttribute(Doctrine_Core::ATTR_VALIDATE, Doctrine_Core::VALIDATE_ALL);
$manager->setAttribute(Doctrine_Core::ATTR_USE_NATIVE_ENUM, true);
$manager->setAttribute(Doctrine_Core::ATTR_QUERY_CLASS, 'ohrmDoctrineQuery');
//
// If using encryption, enable dql callbacks. Needed by EncryptionListener
//
if (KeyHandler::keyExists()) {
$manager->setAttribute(Doctrine_Core::ATTR_USE_DQL_CALLBACKS, true);
}
//$manager->setAttribute(Doctrine::ATTR_TBLNAME_FORMAT, 'hs_hr_%s');
// Allow running doctrine:build-schema without error
$isCli = php_sapi_name() == "cli";
if (true == $isCli) {
Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_AUTO_ACCESSOR_OVERRIDE, false);
}
}
public static function prepareEncryptFields($encryptFieldsArray, $encryptValuesArray)
{
$encOn = KeyHandler::KeyExists();
$valuesArray = array();
$encryptFieldsArrayCount = count($encryptFieldsArray);
for ($i = 0; $i < $encryptFieldsArrayCount; $i++) {
if ($encOn && self::isEncField($encryptFieldsArray[$i])) {
$key = KeyHandler::readKey();
if ($encryptValuesArray[$i] == null) {
$valuesArray[$i] = null;
} else {
$valuesArray[$i] = "AES_ENCRYPT({$encryptValuesArray[$i]}, '{$key}')";
}
} else {
$valuesArray[$i] = $encryptValuesArray[$i];
}
}
return $valuesArray;
}
/**
* 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 (KeyHandler::keyExists()) {
$key = KeyHandler::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;
}
/**
* 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 (KeyHandler::keyExists()) {
$key = KeyHandler::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 constructSelectClauseForListGroup($selectStatement, $displayFieldGroup, $displayFields)
{
$fieldList = '';
$isEncryptEnabled = KeyHandler::keyExists();
foreach ($displayFields as $field) {
$fieldName = $field->getName();
if ($isEncryptEnabled && $field->getIsEncrypted()) {
$fieldName = 'AES_DECRYPT(UNHEX(' . $fieldName . '),"' . KeyHandler::readKey() . '")';
}
// If null, change to empty string since CONCAT_WS will skip nulls, causing problems with the field list order.
$fieldName = 'IFNULL(' . $fieldName . ",'')";
if (empty($fieldList)) {
$fieldList = $fieldName;
} else {
$fieldList .= ',' . $fieldName;
}
}
$alias = "DisplayFieldGroup" . $displayFieldGroup->getId();
$clause = "CONCAT_WS('|^^|', " . $fieldList . ")";
$clause = "GROUP_CONCAT(DISTINCT " . $clause . " SEPARATOR '|\\n|' ) AS " . $alias;
if (empty($selectStatement)) {
$selectStatement = $clause;
} else {
$selectStatement .= ',' . $clause;
}
return $selectStatement;
}
/**
* Get CSV Export Data
*
* @return DaoException
*/
public function getCSVExportData()
{
$csv = array();
try {
$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 as workstation" . " 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 (KeyHandler::keyExists()) {
$key = KeyHandler::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);
}
//
// Direct query using PDO connection
//
$dbh = Doctrine_Manager::connection()->getDbh();
$result = $dbh->query($sql);
if ($result) {
$csv = $result->fetchAll();
}
return $csv;
} catch (Exception $e) {
throw new DaoException($e->getMessage());
}
}
public function testConstructSelectClauseForDisplayField()
{
$displayField = new DisplayField();
$fieldName = 'Acme';
$displayField->setName($fieldName);
$options = array(array('is_value_list' => true, 'is_encrypted' => true, 'field_alias' => 'Abcd'), array('is_value_list' => true, 'is_encrypted' => true, 'field_alias' => null), array('is_value_list' => true, 'is_encrypted' => false, 'field_alias' => 'Abcd'), array('is_value_list' => true, 'is_encrypted' => false, 'field_alias' => null), array('is_value_list' => false, 'is_encrypted' => false, 'field_alias' => 'Abcd'), array('is_value_list' => false, 'is_encrypted' => false, 'field_alias' => null), array('is_value_list' => false, 'is_encrypted' => true, 'field_alias' => 'Abcd'), array('is_value_list' => false, 'is_encrypted' => true, 'field_alias' => null));
$encrypt = KeyHandler::keyExists();
if ($encrypt) {
$key = KeyHandler::readKey();
}
foreach ($options as $option) {
$displayField = new DisplayField();
$displayField->setName($fieldName);
$displayField->setIsValueList($option['is_value_list']);
$displayField->setIsEncrypted($option['is_encrypted']);
$displayField->setFieldAlias($option['field_alias']);
$expected = $fieldName;
if ($encrypt && $option['is_encrypted']) {
$expected = 'AES_DECRYPT(UNHEX(' . $fieldName . '),"' . $key . '")';
}
if ($option['is_value_list']) {
$expected = 'GROUP_CONCAT(DISTINCT ' . $expected . " SEPARATOR '|" . '\\n' . "|' ) ";
}
if ($option['field_alias']) {
$expected = $expected . ' AS ' . $option['field_alias'];
}
$selectStatement = null;
$selectStatement = $this->reportGeneratorService->constructSelectClauseForDisplayField($selectStatement, $displayField);
$this->assertEquals($expected, $selectStatement);
$selectStatement = "x";
$expected = 'x,' . $expected;
$selectStatement = $this->reportGeneratorService->constructSelectClauseForDisplayField($selectStatement, $displayField);
$this->assertEquals($expected, $selectStatement);
}
}
function simpleSelect($selectTable, $selectFields, $selectConditions = null, $selectOrderBy = null, $selectOrder = null, $selectLimit = null)
{
/* For Encryption : Begins */
$encOn = KeyHandler::KeyExists();
if ($encOn && CryptoQuery::isEncTable($this->table_name)) {
$selectFields = CryptoQuery::prepareDecryptFields($selectFields);
}
/* For Encryption : Ends */
$query = $this->_buildSelect($selectFields) . " FROM {$selectTable} ";
if (isset($selectConditions)) {
$query .= $this->_buildWhere($selectConditions);
}
if (isset($selectOrderBy)) {
$query .= " ORDER BY {$selectOrderBy} {$selectOrder}";
}
if (isset($selectLimit)) {
$query .= " LIMIT {$selectLimit}";
}
return $query;
}
/**
* @todo Implement testDeleteKey().
*/
public function testDeleteKey()
{
// When key is not available
try {
KeyHandler::deleteKey();
} catch (Exception $e) {
$this->assertEquals(KeyHandlerException::KEY_DOES_NOT_EXIST, $e->getCode());
}
// When key is existing
$filePath = ROOT_PATH . '/lib/confs/cryptokeys/key.ohrm';
$this->assertTrue(KeyHandler::createKey());
$this->assertTrue(KeyHandler::deleteKey());
$this->assertFalse(file_exists($filePath));
// When key is existing, but cannot be deleted
$this->assertTrue(KeyHandler::createKey());
system("chmod 000 {$filePath}");
try {
KeyHandler::deleteKey();
} catch (Exception $e) {
$this->assertEquals(KeyHandlerException::KEY_DELETION_FAILIURE, $e->getCode());
}
}
