PageRenderTime 38ms CodeModel.GetById 14ms RepoModel.GetById 0ms app.codeStats 1ms

/lib/models/eimadmin/CustomImport.php

https://bitbucket.org/wildanm/orangehrm
PHP | 573 lines | 370 code | 99 blank | 104 comment | 32 complexity | b9c159392b68e6f1bf62206088ecf296 MD5 | raw file
Possible License(s): CC-BY-SA-3.0, AGPL-3.0, BSD-3-Clause, AGPL-1.0, GPL-2.0, LGPL-2.1, LGPL-3.0
  1. <?php
  2. /**
  3. * OrangeHRM is a comprehensive Human Resource Management (HRM) System that captures
  4. * all the essential functionalities required for any enterprise.
  5. * Copyright (C) 2006 OrangeHRM Inc., http://www.orangehrm.com
  6. *
  7. * OrangeHRM is free software; you can redistribute it and/or modify it under the terms of
  8. * the GNU General Public License as published by the Free Software Foundation; either
  9. * version 2 of the License, or (at your option) any later version.
  10. *
  11. * OrangeHRM is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
  12. * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
  13. * See the GNU General Public License for more details.
  14. *
  15. * You should have received a copy of the GNU General Public License along with this program;
  16. * if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
  17. * Boston, MA 02110-1301, USA
  18. *
  19. */
  20. require_once ROOT_PATH.'/lib/dao/DMLFunctions.php';
  21. require_once ROOT_PATH.'/lib/dao/SQLQBuilder.php';
  22. require_once ROOT_PATH.'/lib/confs/sysConf.php';
  23. require_once ROOT_PATH.'/lib/common/CommonFunctions.php';
  24. require_once ROOT_PATH . '/lib/common/UniqueIDGenerator.php';
  25. require_once ROOT_PATH . '/lib/models/eimadmin/CustomFields.php';
  26. class CustomImport {
  27. /**
  28. * Table Name
  29. */
  30. const TABLE_NAME = 'hs_hr_custom_import';
  31. const DB_FIELDS_ID = 'import_id';
  32. const DB_FIELDS_NAME = 'name';
  33. const DB_FIELDS_FIELDS = 'fields';
  34. const DB_FIELDS_HAS_HEADING = 'has_heading';
  35. const NO_HEADING = 0;
  36. const HAS_HEADING = 1;
  37. /** CSV Field name constants */
  38. const FIELD_EMPID = 'empId';
  39. const FIELD_LASTNAME = 'lastName';
  40. const FIELD_FIRSTNAME = 'firstName';
  41. const FIELD_MIDDLENAME = 'middleName';
  42. const FIELD_STREET1 = 'street1';
  43. const FIELD_STREET2 = 'street2';
  44. const FIELD_CITY = 'city';
  45. const FIELD_STATE = 'state';
  46. const FIELD_ZIP = 'zip';
  47. const FIELD_GENDER = 'gender';
  48. const FIELD_BIRTHDATE = 'birthDate';
  49. const FIELD_SSN = 'ssn';
  50. const FIELD_JOINEDDATE = 'joinedDate';
  51. const FIELD_WORKSTATION = 'workStation';
  52. const FIELD_CUSTOM1 = 'custom1';
  53. const FIELD_CUSTOM2 = 'custom2';
  54. const FIELD_CUSTOM3 = 'custom3';
  55. const FIELD_CUSTOM4 = 'custom4';
  56. const FIELD_CUSTOM5 = 'custom5';
  57. const FIELD_CUSTOM6 = 'custom6';
  58. const FIELD_CUSTOM7 = 'custom7';
  59. const FIELD_CUSTOM8 = 'custom8';
  60. const FIELD_CUSTOM9 = 'custom9';
  61. const FIELD_CUSTOM10 = 'custom10';
  62. const FIELD_WORKSTATE = 'workState';
  63. const FIELD_FITWSTATUS = 'FITWStatus';
  64. const FIELD_FITWEXCEMPTIONS = 'FITWExemptions';
  65. const FIELD_SITWSTATE = 'SITWState';
  66. const FIELD_SITWSTATUS = 'SITWStatus';
  67. const FIELD_SITWEXCEMPTIONS = 'SITWExemptions';
  68. const FIELD_SUISTATE = 'SUIState';
  69. const FIELD_DD1ROUTING = 'DD1Routing';
  70. const FIELD_DD1ACCOUNT = 'DD1Account';
  71. const FIELD_DD1AMOUNT = 'DD1Amount';
  72. const FIELD_DD1AMOUNTCODE = 'DD1AmountCode';
  73. const FIELD_DD1CHECKING = 'DD1Checking';
  74. const FIELD_DD2ROUTING = 'DD2Routing';
  75. const FIELD_DD2ACCOUNT = 'DD2Account';
  76. const FIELD_DD2AMOUNT = 'DD2Amount';
  77. const FIELD_DD2AMOUNTCODE = 'DD2AmountCode';
  78. const FIELD_DD2CHECKING = 'DD2Checking';
  79. const FIELD_HOME_PHONE = 'HomePhone';
  80. const FIELD_MOBILE_PHONE = 'MobilePhone';
  81. const FIELD_WORK_PHONE = 'WorkPhone';
  82. const FIELD_WORK_EMAIL = 'WorkEmail';
  83. const FIELD_OTHER_EMAIL = 'OtherEmail';
  84. const FIELD_DRIVING_LIC = 'DrivingLic';
  85. /**
  86. * Class Attributes
  87. */
  88. private $id;
  89. private $name;
  90. private $assignedFields;
  91. private $containsHeader;
  92. private $maxFieldLengths;
  93. /**
  94. * Setter method followed by getter method for each
  95. * attribute
  96. */
  97. public function setId($id) {
  98. $this->id = $id;
  99. }
  100. public function getId() {
  101. return $this->id;
  102. }
  103. public function setName($name){
  104. $this->name = $name;
  105. }
  106. public function getName(){
  107. return $this->name;
  108. }
  109. public function setAssignedFields($fields) {
  110. $this->assignedFields = $fields;
  111. }
  112. public function getAssignedFields() {
  113. return $this->assignedFields;
  114. }
  115. public function setContainsHeader($containsHeader) {
  116. return $this->containsHeader = $containsHeader;
  117. }
  118. public function getContainsHeader() {
  119. return $this->containsHeader;
  120. }
  121. public function getFieldCount() {
  122. return count($this->assignedFields);
  123. }
  124. public function __construct() {
  125. $this->maxFieldLengths = self::getMaxFieldLengths();
  126. }
  127. /**
  128. * Get CustomImport with given Id
  129. *
  130. * @param int Custom import id
  131. * @return CustomImport Custom Import object if found or null if not
  132. */
  133. public static function getCustomImport($id) {
  134. if (!CommonFunctions::isValidId($id)) {
  135. throw new CustomImportException("Invalid parameters to getCustomImport(): id = $id", CustomImportException::INVALID_PARAMETERS);
  136. }
  137. $selectCondition[] = self::DB_FIELDS_ID . " = $id";
  138. $list = self::_getList($selectCondition);
  139. $import = count($list) == 0 ? null : $list[0];
  140. return $import;
  141. }
  142. public static function getAllFields() {
  143. $allFields = array(self::FIELD_EMPID, self::FIELD_LASTNAME, self::FIELD_FIRSTNAME, self::FIELD_MIDDLENAME,
  144. self::FIELD_HOME_PHONE, self::FIELD_MOBILE_PHONE, self::FIELD_WORK_PHONE, self::FIELD_WORK_EMAIL,
  145. self::FIELD_OTHER_EMAIL, self::FIELD_DRIVING_LIC,
  146. self::FIELD_STREET1, self::FIELD_STREET2, self::FIELD_CITY, self::FIELD_STATE,
  147. self::FIELD_ZIP, self::FIELD_GENDER, self::FIELD_BIRTHDATE, self::FIELD_SSN,
  148. self::FIELD_JOINEDDATE, self::FIELD_WORKSTATION);
  149. $restOfAllFields = array(self::FIELD_WORKSTATE, self::FIELD_FITWSTATUS,
  150. self::FIELD_FITWEXCEMPTIONS, self::FIELD_SITWSTATE, self::FIELD_SITWSTATUS, self::FIELD_SITWEXCEMPTIONS,
  151. self::FIELD_SUISTATE, self::FIELD_DD1ROUTING, self::FIELD_DD1ACCOUNT, self::FIELD_DD1AMOUNT,
  152. self::FIELD_DD1AMOUNTCODE, self::FIELD_DD1CHECKING, self::FIELD_DD2ROUTING, self::FIELD_DD2ACCOUNT,
  153. self::FIELD_DD2AMOUNT, self::FIELD_DD2AMOUNTCODE, self::FIELD_DD2CHECKING);
  154. $availableCustomFields = CustomFields::getCustomFieldList();
  155. $customFields = array();
  156. foreach($availableCustomFields as $fieldObj) {
  157. $customFields[] = 'custom' . $fieldObj->getFieldNumber();
  158. }
  159. $allFields = array_merge($allFields, $customFields, $restOfAllFields);
  160. return $allFields;
  161. }
  162. /**
  163. * Check if given value is within allowed field length for the given field
  164. *
  165. * @param string $fieldName The field to check
  166. * @param string $value The field value to check
  167. * @return boolean true if field length within allowed limits, false otherwise
  168. */
  169. public function checkFieldLength($fieldName, $value) {
  170. if (isset($this->maxFieldLengths[$fieldName])) {
  171. $maxLength = $this->maxFieldLengths[$fieldName];
  172. if (($maxLength > -1) && (strlen($value) > $maxLength)) {
  173. return false;
  174. }
  175. }
  176. return true;
  177. }
  178. /**
  179. * Get array with maximum allowed field lengths for all supported fields
  180. * Max length is set to -1 where not applicable
  181. *
  182. * @return array Array with maximum allowed field lengths.
  183. */
  184. public static function getMaxFieldLengths() {
  185. $maxLengths = array(
  186. self::FIELD_EMPID => 50,
  187. self::FIELD_LASTNAME => 100,
  188. self::FIELD_FIRSTNAME => 100,
  189. self::FIELD_MIDDLENAME => 100,
  190. self::FIELD_STREET1 => 100,
  191. self::FIELD_STREET2 => 100,
  192. self::FIELD_CITY => 100,
  193. self::FIELD_STATE => 100,
  194. self::FIELD_ZIP => 20,
  195. self::FIELD_GENDER => -1,
  196. self::FIELD_BIRTHDATE => -1,
  197. self::FIELD_SSN => 100,
  198. self::FIELD_JOINEDDATE => -1,
  199. self::FIELD_WORKSTATION => -1,
  200. self::FIELD_CUSTOM1 => 250,
  201. self::FIELD_CUSTOM2 => 250,
  202. self::FIELD_CUSTOM3 => 250,
  203. self::FIELD_CUSTOM4 => 250,
  204. self::FIELD_CUSTOM5 => 250,
  205. self::FIELD_CUSTOM6 => 250,
  206. self::FIELD_CUSTOM7 => 250,
  207. self::FIELD_CUSTOM8 => 250,
  208. self::FIELD_CUSTOM9 => 250,
  209. self::FIELD_CUSTOM10 => 250,
  210. self::FIELD_WORKSTATE => 13,
  211. self::FIELD_FITWSTATUS => 13,
  212. self::FIELD_FITWEXCEMPTIONS => -1,
  213. self::FIELD_SITWSTATE => 13,
  214. self::FIELD_SITWSTATUS => 13,
  215. self::FIELD_SITWEXCEMPTIONS => -1,
  216. self::FIELD_SUISTATE => 13,
  217. self::FIELD_DD1ROUTING => -1,
  218. self::FIELD_DD1ACCOUNT => 100,
  219. self::FIELD_DD1AMOUNT => -1,
  220. self::FIELD_DD1AMOUNTCODE => 20,
  221. self::FIELD_DD1CHECKING => 20,
  222. self::FIELD_DD2ROUTING => -1,
  223. self::FIELD_DD2ACCOUNT => 100,
  224. self::FIELD_DD2AMOUNT => -1,
  225. self::FIELD_DD2AMOUNTCODE => 20,
  226. self::FIELD_DD2CHECKING => 20,
  227. self::FIELD_HOME_PHONE => 50,
  228. self::FIELD_MOBILE_PHONE => 50,
  229. self::FIELD_WORK_PHONE => 50,
  230. self::FIELD_WORK_EMAIL => 50,
  231. self::FIELD_OTHER_EMAIL => 50,
  232. self::FIELD_DRIVING_LIC => 100);
  233. return $maxLengths;
  234. }
  235. /**
  236. * Return array of fields that must be included in import file
  237. * @return array Array of compulsary fields
  238. */
  239. public static function getCompulsaryFields() {
  240. $compulsaryFields = array(self::FIELD_LASTNAME, self::FIELD_FIRSTNAME);
  241. return $compulsaryFields;
  242. }
  243. /**
  244. * Get list of Custom Import objects in the database
  245. * @return Array Array of CustomImport objects
  246. */
  247. public static function getCustomImportList() {
  248. return self::_getList();
  249. }
  250. /**
  251. * Get list of defined Custom Imports in format suitable for view.php
  252. * @return Array 2D array representing custom import objects defined in database.
  253. */
  254. public static function getCustomImportListForView($pageNO,$schStr,$mode,$sortField = 0, $sortOrder = 'ASC') {
  255. $tableName = self::TABLE_NAME;
  256. $arrFieldList[0] = self::DB_FIELDS_ID;
  257. $arrFieldList[1] = self::DB_FIELDS_NAME;
  258. $arrFieldList[2] = self::DB_FIELDS_FIELDS;
  259. $arrFieldList[3] = self::DB_FIELDS_HAS_HEADING;
  260. $sqlBuilder = new SQLQBuilder();
  261. $sqlBuilder->table_name = $tableName;
  262. $sqlBuilder->flg_select = 'true';
  263. $sqlBuilder->arr_select = $arrFieldList;
  264. $sqlQString = $sqlBuilder->passResultSetMessage($pageNO,$schStr,$mode, $sortField, $sortOrder);
  265. $dbConnection = new DMLFunctions();
  266. $result = $dbConnection -> executeQuery($sqlQString); //Calling the addData() function
  267. $i=0;
  268. while ($line = mysql_fetch_array($result, MYSQL_NUM)) {
  269. $arrayDispList[$i][0] = $line[0];
  270. $arrayDispList[$i][1] = $line[1];
  271. $arrayDispList[$i][2] = $line[2];
  272. $i++;
  273. }
  274. if (isset($arrayDispList)) {
  275. return $arrayDispList;
  276. } else {
  277. $arrayDispList = '';
  278. return $arrayDispList;
  279. }
  280. }
  281. /**
  282. * Get the available fields (fields not yet assigned to this CustomImport)
  283. *
  284. * @return array Array of fields not yet assigned to this CustomImport object
  285. */
  286. public function getAvailableFields() {
  287. $allFields = CustomImport::getAllFields();
  288. $available = array_diff($allFields, $this->assignedFields);
  289. return $available;
  290. }
  291. /**
  292. * Delete custom imports with the given ids
  293. *
  294. * @param array $ids Array of import id's
  295. * @return int the number of CustomImport's actually deleted
  296. */
  297. public static function deleteImports($ids) {
  298. $count = 0;
  299. if (!is_array($ids)) {
  300. throw new CustomImportException("Invalid parameter to deleteImports(): ids should be an array", CustomImportException::INVALID_PARAMETERS);
  301. }
  302. foreach ($ids as $id) {
  303. if (!CommonFunctions::isValidId($id)) {
  304. throw new CustomImportException("Invalid parameter to deleteImports(): id = $id", CustomImportException::INVALID_PARAMETERS);
  305. }
  306. }
  307. if (!empty($ids)) {
  308. $sql = sprintf("DELETE FROM %s WHERE `%s` IN (%s)", self::TABLE_NAME,
  309. self::DB_FIELDS_ID, implode(",", $ids));
  310. $conn = new DMLFunctions();
  311. $result = $conn->executeQuery($sql);
  312. if ($result) {
  313. $count = mysql_affected_rows();
  314. }
  315. }
  316. return $count;
  317. }
  318. /**
  319. * Save this CustomImport Object.
  320. * If an id is available the existing values are updated, if not a new
  321. * id is assigned and a new CustomImport is saved
  322. *
  323. */
  324. public function save() {
  325. // Validate fieleds
  326. if (empty($this->name)) {
  327. throw new CustomImportException("Empty name", CustomImportException::EMPTY_IMPORT_NAME);
  328. }
  329. if ($this->_isNameInUse()) {
  330. throw new CustomImportException("Duplicate name", CustomImportException::DUPLICATE_IMPORT_NAME);
  331. }
  332. if (empty($this->assignedFields) || !is_array($this->assignedFields)) {
  333. throw new CustomImportException("No valid Assigned fields", CustomImportException::NO_ASSIGNED_FIELDS);
  334. }
  335. $compulsaryFields = self::getCompulsaryFields();
  336. $allFields = self::getAllFields();
  337. foreach ($this->assignedFields as $field) {
  338. if (!in_array($field, $allFields)) {
  339. throw new CustomImportException("Invalid field name", CustomImportException::INVALID_FIELD_NAME);
  340. }
  341. $key = array_search($field, $compulsaryFields);
  342. if ($key !== FALSE) {
  343. unset($compulsaryFields[$key]);
  344. }
  345. }
  346. if (count($compulsaryFields) > 0) {
  347. throw new CustomImportException("Missing compulsary fields: " . implode(',', $compulsaryFields), CustomImportException::COMPULSARY_FIELDS_NOT_ASSIGNED);
  348. }
  349. if (empty($this->id)) {
  350. $this->_insert();
  351. } else {
  352. $this->_update();
  353. }
  354. }
  355. /**
  356. * Add new CustomImport object to database
  357. */
  358. private function _insert() {
  359. $fields[0] = self::DB_FIELDS_ID;
  360. $fields[1] = self::DB_FIELDS_NAME;
  361. $fields[2] = self::DB_FIELDS_FIELDS;
  362. $fields[3] = self::DB_FIELDS_HAS_HEADING;
  363. $this->id = UniqueIDGenerator::getInstance()->getNextID(self::TABLE_NAME, self::DB_FIELDS_ID);
  364. $values[0] = $this->id;
  365. $values[1] = "'{$this->name}'";
  366. $values[2] = "'" . implode(",", $this->assignedFields) . "'";
  367. $values[3] = $this->containsHeader ? self::HAS_HEADING : self::NO_HEADING;
  368. $sqlBuilder = new SQLQBuilder();
  369. $sqlBuilder->table_name = self::TABLE_NAME;
  370. $sqlBuilder->flg_insert = 'true';
  371. $sqlBuilder->arr_insert = $values;
  372. $sqlBuilder->arr_insertfield = $fields;
  373. $sql = $sqlBuilder->addNewRecordFeature2();
  374. $conn = new DMLFunctions();
  375. $result = $conn->executeQuery($sql);
  376. if (!$result || (mysql_affected_rows() != 1)) {
  377. throw new CustomImportException("Insert failed. $sql", CustomImportException::DB_EXCEPTION);
  378. }
  379. }
  380. /**
  381. * Update existing CustomImport data
  382. */
  383. private function _update() {
  384. $fields[0] = self::DB_FIELDS_ID;
  385. $fields[1] = self::DB_FIELDS_NAME;
  386. $fields[2] = self::DB_FIELDS_FIELDS;
  387. $fields[3] = self::DB_FIELDS_HAS_HEADING;
  388. $values[0] = $this->id;
  389. $values[1] = "'{$this->name}'";
  390. $values[2] = "'" . implode(",", $this->assignedFields) . "'";
  391. $values[3] = $this->containsHeader ? self::HAS_HEADING : self::NO_HEADING;
  392. $sqlBuilder = new SQLQBuilder();
  393. $sqlBuilder->table_name = self::TABLE_NAME;
  394. $sqlBuilder->flg_update = 'true';
  395. $sqlBuilder->arr_update = $fields;
  396. $sqlBuilder->arr_updateRecList = $values;
  397. $sql = $sqlBuilder->addUpdateRecord1(0);
  398. $conn = new DMLFunctions();
  399. $result = $conn->executeQuery($sql);
  400. // Here we don't check mysql_affected_rows because update may be called
  401. // without any changes.
  402. if (!$result) {
  403. throw new CustomImportException("Update failed. SQL=$sql", CustomImportException::DB_EXCEPTION);
  404. }
  405. }
  406. /**
  407. * Check if this objects name is in use
  408. *
  409. * @return boolean true if that name is in use, false otherwise
  410. */
  411. private function _isNameInUse() {
  412. $sql = 'SELECT COUNT(*) FROM ' . self::TABLE_NAME . ' WHERE ' . self::DB_FIELDS_NAME . " = '" . $this->name . "'";
  413. // exclude this object
  414. if (!empty($this->id)) {
  415. $sql .= ' AND ' . self::DB_FIELDS_ID . ' <> ' . $this->id;
  416. }
  417. $result = mysql_query($sql);
  418. $row = mysql_fetch_array($result, MYSQL_NUM);
  419. $count = $row[0];
  420. return ($count != 0);
  421. }
  422. /**
  423. * Get a list of custom import objects with the given conditions.
  424. *
  425. * @param array $selectCondition Array of select conditions to use.
  426. * @return array Array of CustomImport objects. Returns an empty (length zero) array if none found.
  427. */
  428. private static function _getList($selectCondition = null) {
  429. $fields[0] = self::DB_FIELDS_ID;
  430. $fields[1] = self::DB_FIELDS_NAME;
  431. $fields[2] = self::DB_FIELDS_FIELDS;
  432. $fields[3] = self::DB_FIELDS_HAS_HEADING;
  433. $sqlBuilder = new SQLQBuilder();
  434. $sql = $sqlBuilder->simpleSelect(self::TABLE_NAME, $fields, $selectCondition);
  435. $actList = array();
  436. $conn = new DMLFunctions();
  437. $result = $conn->executeQuery($sql);
  438. while ($result && ($row = mysql_fetch_assoc($result))) {
  439. $actList[] = self::_createFromRow($row);
  440. }
  441. return $actList;
  442. }
  443. /**
  444. * Creates a CustomImport object from a resultset row
  445. *
  446. * @param array $row Resultset row from the database.
  447. * @return CustomImport Custom Import object.
  448. */
  449. private static function _createFromRow($row) {
  450. $tmp = new CustomImport();
  451. $tmp->setId($row[self::DB_FIELDS_ID]);
  452. $tmp->setName($row[self::DB_FIELDS_NAME]);
  453. $assignedFields = $row[self::DB_FIELDS_FIELDS];
  454. if (!empty($assignedFields)) {
  455. $tmp->setAssignedFields(explode(",", $assignedFields));
  456. } else {
  457. $tmp->setAssignedFields(array());
  458. }
  459. $hasHeader = ($row[self::DB_FIELDS_HAS_HEADING] == self::HAS_HEADING) ? true : false;
  460. $tmp->setContainsHeader($hasHeader);
  461. return $tmp;
  462. }
  463. }
  464. class CustomImportException extends Exception {
  465. const INVALID_FIELD_NAME = 0;
  466. const NO_ASSIGNED_FIELDS = 1;
  467. const DUPLICATE_IMPORT_NAME = 2;
  468. const EMPTY_IMPORT_NAME = 3;
  469. const DB_EXCEPTION = 4;
  470. const INVALID_PARAMETERS = 5;
  471. const ID_NOT_FOUND = 6;
  472. const COMPULSARY_FIELDS_NOT_ASSIGNED = 7;
  473. }
  474. ?>