/** * 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)); } }
/** * * Decrypts given value * Compatible with mysql: aes_decrypt(unhex($val), $key) * * @param string $crypt Value to decrypt * @return string Decrypted value */ public static function decrypt($crypt) { if (empty($crypt) || !KeyHandler::keyExists()) { return $crypt; } self::init(); $crypt = pack("H*", $crypt); $decrypt = self::$cryptographicScheme->decrypt($crypt); return $decrypt; }
/** * 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'); }
/** * 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); } }
/** * 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); } }