workshift.php

国外的人才求职招聘最新版

		$numResults = mysql_num_rows($results);
		if ($numResults == 1) {
			$objs = self::_getWorkshiftsFromResults($results);
			return $objs[0];
		} else if ($numResults == 0) {
			return null;
		} else {
			throw new WorkshiftException("Invalid number of results returned.", WorkshiftException::INVALID_ROW_COUNT);
		}
	}

	/**
	 * Gets workshift hours for all employees
	 *
	 * @return array empid->workshift array for all employees with workshifts.
	 */
	public static function getWorkshiftForAllEmployees() {

		$fields[0] = "b.`" . self::DB_FIELD_EMP_NUMBER . "`";
		$fields[1] = "a.`" . self::DB_FIELD_HOURS . "`";

		$tables[0] = "`" . self::WORKSHIFT_TABLE . "` a ";
		$tables[1] = "`" . self::EMPLOYEE_WORKSHIFT_TABLE . "` b ";

		$joinConditions[1] = "a." . self::DB_FIELD_WORKSHIFT_ID .
							 " = b." . self::DB_FIELD_WORKSHIFT_ID;

		$selectConditions = null;

		$sqlBuilder = new SQLQBuilder();
		$sql = $sqlBuilder->selectFromMultipleTable($fields, $tables, $joinConditions, $selectConditions);

		$conn = new DMLFunctions();
		$results = $conn->executeQuery($sql);

		if ($results === false) {
			throw new WorkshiftException("Error in db query:" . $sql, WorkshiftException::ERROR_IN_DB_QUERY);
		}

		$numResults = mysql_num_rows($results);
		$empWorkshifts = array();

		while($row = mysql_fetch_array($results)) {

			$empId = $row[self::DB_FIELD_EMP_NUMBER];
			$hours = $row[self::DB_FIELD_HOURS];
			$empWorkshifts[$empId] = $hours;
		}

		return $empWorkshifts;
	}

	/**
	 * Remove all employees assigned to this workshift
	 * @return int Number of employees removed from workshift
	 */
	public function removeAssignedEmployees() {

		if (!CommonFunctions::isValidId($this->workshiftId)) {
			throw new WorkshiftException("Invalid id", WorkshiftException::INVALID_ID);
		}

		$sql = sprintf("DELETE FROM %s WHERE %s = %s", self::EMPLOYEE_WORKSHIFT_TABLE,
		                self::DB_FIELD_WORKSHIFT_ID, $this->workshiftId);
		$conn = new DMLFunctions();
		$result = $conn->executeQuery($sql);
		if ($result) {
			$count = mysql_affected_rows();
		} else {
			throw new WorkshiftException("Error in SQL Query: $sql", WorkshiftException::ERROR_IN_DB_QUERY);
		}

		return $count;
	}

	/**
	 * Return list of employees who are not assigned to any workshift
	 * @return array Array of employees
	 */
	public static function getEmployeesWithoutWorkshift() {

		$fields[0] = EmpInfo::EMPLOYEE_FIELD_EMP_NUMBER;
		$fields[1] = EmpInfo::EMPLOYEE_FIELD_EMP_ID;
		$fields[2] = EmpInfo::EMPLOYEE_FIELD_FIRST_NAME;
		$fields[3] = EmpInfo::EMPLOYEE_FIELD_MIDDLE_NAME;
		$fields[4] = EmpInfo::EMPLOYEE_FIELD_LAST_NAME;

		$sqlBuilder = new SQLQBuilder();

		$sqlBuilder->table_name = EmpInfo::EMPLOYEE_TABLE_NAME;
		$sqlBuilder->flg_select = 'true';
		$sqlBuilder->arr_select = $fields;
		$sqlBuilder->field = EmpInfo::EMPLOYEE_FIELD_EMP_NUMBER;
		$sqlBuilder->field2 = self::DB_FIELD_EMP_NUMBER;
		$sqlBuilder->table2_name = self::EMPLOYEE_WORKSHIFT_TABLE;

		$sql = $sqlBuilder->selectFilter();

		$connection = new DMLFunctions();
		$result = $connection->executeQuery($sql);
		if ($result === false) {
			throw new WorkshiftException("Error in db query:" . $sql, WorkshiftException::ERROR_IN_DB_QUERY);
		}

		return self::_getEmployeesFromResults($result);

	}

	/**
	 * Return list of employees who are assigned to this workshift
	 * @return array Array of employees
	 */
	public function getAssignedEmployees() {

		if(!CommonFunctions::isValidId($this->workshiftId)) {
			throw new WorkshiftException("No valid workshift id defined", WorkshiftException::INVALID_ID);
		}

		$fields[0] = "b.`" . EmpInfo::EMPLOYEE_FIELD_EMP_NUMBER . "`";
		$fields[1] = "b.`" . EmpInfo::EMPLOYEE_FIELD_EMP_ID . "`";
		$fields[2] = "b.`" . EmpInfo::EMPLOYEE_FIELD_FIRST_NAME . "`";
		$fields[3] = "b.`" . EmpInfo::EMPLOYEE_FIELD_MIDDLE_NAME . "`";
		$fields[4] = "b.`" . EmpInfo::EMPLOYEE_FIELD_LAST_NAME . "`";

		$tables[0] = "`" . self::EMPLOYEE_WORKSHIFT_TABLE. "` a ";
		$tables[1] = "`" . EmpInfo::EMPLOYEE_TABLE_NAME . "` b ";

		$joinConditions[1] = "a." . self::DB_FIELD_EMP_NUMBER .
							 " = b." . EmpInfo::EMPLOYEE_FIELD_EMP_NUMBER;

		$selectConditions[0] = " a." . self::DB_FIELD_WORKSHIFT_ID . " = " . $this->workshiftId;

		$sqlBuilder = new SQLQBuilder();
		$sql = $sqlBuilder->selectFromMultipleTable($fields, $tables, $joinConditions, $selectConditions);

		$conn = new DMLFunctions();
		$results = $conn->executeQuery($sql);

		if ($results === false) {
			throw new WorkshiftException("Error in db query:" . $sql, WorkshiftException::ERROR_IN_DB_QUERY);
		}

		return self::_getEmployeesFromResults($results);
	}

	/**
	 * Get work shifts defined in the system
	 * @return array array of work shift objects
	 */
	public static function getWorkshifts() {

		$selectFields[0] = self::DB_FIELD_WORKSHIFT_ID;
		$selectFields[1] = self::DB_FIELD_NAME;
		$selectFields[2] = self::DB_FIELD_HOURS;

		$sqlBuilder = new SQLQBuilder();
		$query = $sqlBuilder->simpleSelect(self::WORKSHIFT_TABLE, $selectFields);

		$dbConnection = new DMLFunctions();
		$result = $dbConnection->executeQuery($query);

		if ($result === false) {
			throw new WorkshiftException("Error in db query:" . $query, WorkshiftException::ERROR_IN_DB_QUERY);
		}

		$workshifts = self::_getWorkshiftsFromResults($result);
		return $workshifts;
	}

	/**
	 * Get work shift with given id
	 * @param int $workshiftId Work shift id
	 * @return Workshift workshift object or null if not found
	 */
	public static function getWorkshift($workShiftId) {

		if (!CommonFunctions::isValidId($workShiftId)) {
			throw new WorkshiftException("Invalid id", WorkshiftException::INVALID_ID);
		}

		$sqlBuilder = new SQLQBuilder();

		$selectTable = self::WORKSHIFT_TABLE;

		$selectFields[0] = self::DB_FIELD_WORKSHIFT_ID;
		$selectFields[1] = self::DB_FIELD_NAME;
		$selectFields[2] = self::DB_FIELD_HOURS;

		$selectConditions[0] = self::DB_FIELD_WORKSHIFT_ID . " = " . $workShiftId;

		$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);

		$dbConnection = new DMLFunctions();
		$result = $dbConnection->executeQuery($query);

		$numResults = mysql_num_rows($result);
		if ($numResults == 1) {
			$objs = self::_getWorkshiftsFromResults($result);
			return $objs[0];
		} else if ($numResults == 0) {
			throw new WorkshiftException("Invalid number of results returned.", WorkshiftException::WORKSHIFT_NOT_FOUND);
		} else {
			throw new WorkshiftException("Invalid number of results returned.", WorkshiftException::INVALID_ROW_COUNT);
		}
	}

	/**
	 * Return array of workshift objects from the given results set
	 * @return array Array of Workshift objects
	 */
	private function _getWorkshiftsFromResults($results) {

		$workshiftArray = array();
		while($row = mysql_fetch_array($results)) {

			$workshiftObj = new Workshift();
			$workshiftObj->setWorkshiftId($row[self::DB_FIELD_WORKSHIFT_ID]);
			$workshiftObj->setName($row[self::DB_FIELD_NAME]);
			$workshiftObj->setHoursPerDay($row[self::DB_FIELD_HOURS]);

			$workshiftArray[] = $workshiftObj;
		}

		return $workshiftArray;
	}

	/**
	 * Return array of workshift objects from the given results set
	 * @return array Array of Employees from the results set
	 */
	private static function _getEmployeesFromResults($results) {

		$employees = array();
		while($row = mysql_fetch_array($results)) {
			$employees[] = $row;
		}

		return $employees;

	}

	/**
	 * Delete workshifts
	 * @param array $workShiftIds array of work sheet id's to delete
	 */
	public static function deleteWorkshifts($workshiftIds) {

		if (!is_array($workshiftIds) || empty($workshiftIds)) {
			throw new WorkshiftException("Invalid Parameter", WorkshiftException::INVALID_PARAMETER);
		}

		foreach($workshiftIds as $id) {
			if (!CommonFunctions::isValidId($id)) {
				throw new WorkshiftException("Invalid ID in array", WorkshiftException::INVALID_ID);
			}
		}

		self::_deleteWorkshifts($workshiftIds);
	}
}

class WorkshiftException extends Exception {

	const ERROR_IN_DB_QUERY = 1;
	const INVALID_PARAMETER = 2;
	const INVALID_ID= 3;
	const VALUES_EMPTY_OR_NOT_SET = 4;
	const INVALID_ROW_COUNT = 5;
	const WORKSHIFT_NOT_FOUND = 6;

}
?>