hsppaymentrequest.php

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

		$selectFields[] = "`".self::DB_FIELD_MAIL_ADDRESS."`";
		$selectFields[] = "`".self::DB_FIELD_COMMENTS."`";
		$selectFields[] = "`".self::DB_FIELD_DATE_PAID."`";
		$selectFields[] = "`".self::DB_FIELD_CHECK_NUMBER."`";
		$selectFields[] = "`".self::DB_FIELD_STATUS."`";
		$selectFields[] = "`".self::DB_FIELD_HR_NOTES."`";

		$selectOrder = "ASC";
		$selectOrderBy = "`".self::DB_FIELD_ID."`";

		$selectConditions[] = "(`".self::DB_FIELD_STATUS."` = ".self::HSP_PAYMENT_REQUEST_STATUS_PAID . ")";
		$selectConditions[] = "`".self::DB_FIELD_EMPLOYEE_ID."` = $employeeId";
		$selectConditions[] = "`".self::DB_FIELD_DATE_INCURRED."` BETWEEN  DATE('$year-01-01') AND DATE('$year-12-31')";

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

		$dbConnection = new DMLFunctions();

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

		$requests = self::_buildObjArr($result);

		if (count($requests) > 0) {
			return $requests;
		} else {
			return null;
		}
	}

	public static function totalGrantedPayments($employeeId, $year, $type) {
		if (!CommonFunctions::isValidId($employeeId)) {
			throw new HspPaymentRequestException("Invalid employee id", HspPaymentRequestException::INVALID_EMPLOYEE_ID);
		}

		$sqlBuilder = new SQLQBuilder();

		$selectTable = "`".self::HSP_PAYMENT_REQUEST_DB_TABLE."`";

		$selectFields[] = "SUM(`".self::DB_FIELD_EXPENSE_AMOUNT."`)";

		$selectOrder = "ASC";
		$selectOrderBy = "`".self::DB_FIELD_ID."`";

		$selectConditions[] = "`".self::DB_FIELD_STATUS."` = ".self::HSP_PAYMENT_REQUEST_STATUS_PAID;

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

		$dbConnection = new DMLFunctions();

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

		$requests = self::_buildObjArr($result);

		if (count($requests) > 0) {
			return $requests;
		} else {
			return null;
		}
	}

	public function payHspRequest() {
		$request = self::getHspRequest($this->id);

		if (($request == null) || ($request->getStatus() == self::HSP_PAYMENT_REQUEST_STATUS_DELETED)) {
			throw new HspPaymentRequestException("HSP Request not found", HspPaymentRequestException::HSP_REQUEST_NOT_FOUND);
		}

		if ($request->getStatus() == self::HSP_PAYMENT_REQUEST_STATUS_PAID) {
			throw new HspPaymentRequestException("Already paid", HspPaymentRequestException::ALREADY_PAID);
		}

		if ($this->datePaid == null) {
			throw new HspPaymentRequestException("Date paid not specified", HspPaymentRequestException::INVALID_REQUEST);
		}

		if ($this->checkNumber == null) {
			throw new HspPaymentRequestException("Check paid not specified", HspPaymentRequestException::INVALID_REQUEST);
		}

		$this->status = self::HSP_PAYMENT_REQUEST_STATUS_PAID;

		return $this->_update();
	}

	public function deleteHspRequest() {
		$request = self::getHspRequest($this->id);

		if (($request == null) || ($request->getStatus() == self::HSP_PAYMENT_REQUEST_STATUS_DELETED)) {
			throw new HspPaymentRequestException("HSP Request not found", HspPaymentRequestException::HSP_REQUEST_NOT_FOUND);
		}

		if ($request->getStatus() == self::HSP_PAYMENT_REQUEST_STATUS_PAID) {
			throw new HspPaymentRequestException("Already paid", HspPaymentRequestException::ALREADY_PAID);
		}

		$this->status = self::HSP_PAYMENT_REQUEST_STATUS_DELETED;

		return $this->_update();
	}

	public function denyHspRequest() {
		$request = self::getHspRequest($this->id);

		if (($request == null) || ($request->getStatus() == self::HSP_PAYMENT_REQUEST_STATUS_DELETED)) {
			throw new HspPaymentRequestException("HSP Request not found", HspPaymentRequestException::HSP_REQUEST_NOT_FOUND);
		}

		if ($request->getStatus() == self::HSP_PAYMENT_REQUEST_STATUS_PAID) {
			throw new HspPaymentRequestException("Already paid", HspPaymentRequestException::ALREADY_PAID);
		}

		$this->status = self::HSP_PAYMENT_REQUEST_STATUS_DENIED;

		return $this->_update();
	}

	public function updateRequest() {
		$request = self::getHspRequest($this->id);

		if (($request == null) || ($request->getStatus() == self::HSP_PAYMENT_REQUEST_STATUS_DELETED)) {
			throw new HspPaymentRequestException("HSP Request not found", HspPaymentRequestException::HSP_REQUEST_NOT_FOUND);
		}

		if ($request->getStatus() == self::HSP_PAYMENT_REQUEST_STATUS_PAID) {
			throw new HspPaymentRequestException("Already paid", HspPaymentRequestException::ALREADY_PAID);
		}

		return $this->_update();
	}

	private function _update() {
		if (!CommonFunctions::isValidId($this->id)) {
			throw new HspPaymentRequest("Invalid id", HspPaymentRequest::INVALID_ID);
		}

		$arrTable = '`'.self::HSP_PAYMENT_REQUEST_DB_TABLE.'`';

		if ($this->dateIncurred != null) {
			$updateFields[] = '`'.self::DB_FIELD_DATE_INCURRED.'`';
			$arrRecordsList[] = "'".$this->dateIncurred."'";
		}
		if ($this->providerName != null) {
			$updateFields[] = '`'.self::DB_FIELD_PROVIDER_NAME.'`';
			$arrRecordsList[] = "'".$this->providerName."'";
		}
		if ($this->personIncurringExpense != null) {
			$updateFields[] = '`'.self::DB_FIELD_PERSON_INCURRING_EXPENSE.'`';
			$arrRecordsList[] = "'". $this->personIncurringExpense."'";
		}
		if ($this->expenseDescription != null) {
			$updateFields[] = '`'.self::DB_FIELD_EXPENSE_DESCRIPTION.'`';
			$arrRecordsList[] = "'". $this->expenseDescription."'";
		}
		if ($this->expenseAmount != null) {
			$updateFields[] = '`'.self::DB_FIELD_EXPENSE_AMOUNT.'`';
			$arrRecordsList[] = "'". $this->expenseAmount."'";
		}
		if ($this->paymentMadeTo != null) {
			$updateFields[] = '`'.self::DB_FIELD_PAYMENT_MADE_TO.'`';
			$arrRecordsList[] = "'".$this->paymentMadeTo."'";
		}
		if ($this->thirdPartyAccountNumber != null) {
			$updateFields[] = '`'.self::DB_FIELD_THIRD_PARTY_ACCOUNT_NUMBER.'`';
			$arrRecordsList[] = "'".$this->thirdPartyAccountNumber."'";
		}
		if ($this->mailAddress != null) {
			$updateFields[] = '`'.self::DB_FIELD_MAIL_ADDRESS.'`';
			$arrRecordsList[] = "'".$this->mailAddress."'";
		}
		if ($this->comments != null) {
			$updateFields[] = '`'.self::DB_FIELD_COMMENTS.'`';
			$arrRecordsList[] = "'". $this->comments."'";
		}
		if ($this->status != null) {
			$updateFields[] = '`'.self::DB_FIELD_STATUS.'`';
			$arrRecordsList[] = "'". $this->status."'";
		}
		if ($this->datePaid != null) {
			$updateFields[] = '`'.self::DB_FIELD_DATE_PAID.'`';
			$arrRecordsList[] = "'".$this->datePaid."'";
		}
		if ($this->checkNumber != null) {
			$updateFields[] = '`'.self::DB_FIELD_CHECK_NUMBER.'`';
			$arrRecordsList[] = "'". $this->checkNumber."'";
		}
		if ($this->hrNotes != null) {
			$updateFields[] = '`'.self::DB_FIELD_HR_NOTES.'`';
			$arrRecordsList[] = "'". $this->hrNotes."'";
		}

		$updateConditions[0] = "`".self::DB_FIELD_ID."` = '".$this->id."'";

		$sqlBuilder = new SQLQBuilder();
		$query = $sqlBuilder->simpleUpdate($arrTable, $updateFields, $arrRecordsList, $updateConditions);

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

		if ($result === false) {
			throw new HspPaymentRequestException("Error in update", HspPaymentRequestException::ERROR_IN_DB_QUERY);
		}

		return mysql_affected_rows();
	}

	private static function _buildObjArr($result) {

		$objArr = array();

		while ($row = mysql_fetch_assoc($result)) {
			$tmpArr = new HspPaymentRequest();
			$tmpArr->setId($row[self::DB_FIELD_ID]);
			$tmpArr->setHspId($row[self::DB_FIELD_HSP_ID]);
			$tmpArr->setEmployeeId($row[self::DB_FIELD_EMPLOYEE_ID]);
			$tmpArr->setDateIncurred($row[self::DB_FIELD_DATE_INCURRED]);
			$tmpArr->setProviderName($row[self::DB_FIELD_PROVIDER_NAME]);
			$tmpArr->setPersonIncurringExpense($row[self::DB_FIELD_PERSON_INCURRING_EXPENSE]);
			$tmpArr->setExpenseDescription($row[self::DB_FIELD_EXPENSE_DESCRIPTION]);
			$tmpArr->setExpenseAmount($row[self::DB_FIELD_EXPENSE_AMOUNT]);
			$tmpArr->setPaymentMadeTo($row[self::DB_FIELD_PAYMENT_MADE_TO]);
			$tmpArr->setThirdPartyAccountNumber($row[self::DB_FIELD_THIRD_PARTY_ACCOUNT_NUMBER]);
			$tmpArr->setMailAddress($row[self::DB_FIELD_MAIL_ADDRESS]);
			$tmpArr->setComments($row[self::DB_FIELD_COMMENTS]);
			$tmpArr->setDatePaid($row[self::DB_FIELD_DATE_PAID]);
			$tmpArr->setCheckNumber($row[self::DB_FIELD_CHECK_NUMBER]);
			$tmpArr->setStatus($row[self::DB_FIELD_STATUS]);
			$tmpArr->setHrNotes($row[self::DB_FIELD_HR_NOTES]);

			$objArr[] = $tmpArr;
		}

		return $objArr;
	}
	/**
	* Get Dependants of a employee
	* @param int $empId ID of the employee
	* @return array $dependant array of names of dependents
	* */
	public static function fetchDependants($empId) {
		$sqlBuilder = new SQLQBuilder();

		$selectTable = "`".self::EMP_DEPENDENT_DB_TABLE."`";

		$selectFields[] = "`".self::DB_FIELD_EMP_DEPENDENT_NAME."`";

		$selectOrder = "ASC";
		$selectOrderBy = "`".self::DB_FIELD_EMP_DEPENDENT_NAME."`";

		$selectConditions[] = "`".self::DB_FIELD_EMP_NUMBER."` = ".$empId;

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

		$dbConnection = new DMLFunctions();

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

		$dependents = null;

		if (mysql_num_rows($result) > 0) {
			while($row = mysql_fetch_array($result)) {
				$dependents[] = $row[0];
			}
		}
		return $dependents;
	}

	/**
	* Get children of a employee
	* @param int $empId ID of the employee
	* @return array $children array of names of childrens
	* */
	public static function fetchChildren($empId) {
		$sqlBuilder = new SQLQBuilder();

		$selectTable = "`".self::EMP_CHILDREN_DB_TABLE."`";

		$selectFields[] = "`".self::DB_FIELD_EMP_CHILDREN_NAME."`";

		$selectOrder = "ASC";
		$selectOrderBy = "`".self::DB_FIELD_EMP_CHILDREN_NAME."`";

		$selectConditions[] = "`".self::DB_FIELD_EMP_NUMBER."` = ".$empId;

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

		$dbConnection = new DMLFunctions();

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

		$children = null;

		if (mysql_num_rows($result) > 0) {
			while($row = mysql_fetch_array($result)) {
				$children[] = $row[0];
			}
		}
		return $children;
	}

	/**
	 * Get hsp request details - dateInquried, expenseDescription,
	 * personInquringExpense, amount
	 * @param int $hspId
	 * @return array
	 */
	public function fetchHspRequestDetails($hspId) {
		$sqlBuilder = new SQLQBuilder();

		$selectTable = "`".self::HSP_PAYMENT_REQUEST_DB_TABLE."`";

		$selectFields[] = "`".self::DB_FIELD_DATE_INCURRED."`";
		$selectFields[] = "`".self::DB_FIELD_EXPENSE_DESCRIPTION."`";
		$selectFields[] = "`".self::DB_FIELD_PERSON_INCURRING_EXPENSE."`";
		$selectFields[] = "`".self::DB_FIELD_EXPENSE_AMOUNT."`";

		$selectOrder = "ASC";
		$selectOrderBy = "`".self::DB_FIELD_DATE_INCURRED."`";

		$selectConditions[] = "`".self::DB_FIELD_ID."` = '".$hspId."'";

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

		$dbConnection = new DMLFunctions();

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

		$detail = null;
		if (mysql_num_rows($result) > 0) {
			$i = 0;
			while($row = mysql_fetch_array($result)) {
				$detail = $row;
			}
		}
		return $detail;
	}

	public function isDataChangedByAdmin($hspRequest) {
		$isChanged = false;
		$msg = 'HR Admin Changing HSP Request (ID-' . $hspRequest->getId().')';
		$dateInqTemp = $hspRequest->getDateIncurred();
		if(!isset($dateInqTemp)) {
			return $isChanged;
		}
		if($this->dateIncurred != $hspRequest->getDateIncurred()) {
			$isChanged = true;
			$msg = $msg . "'\r\nDate Incurred: " . $this->dateIncurred . " -> " . $hspRequest->getDateIncurred();
		}
		if($this->providerName != $hspRequest->getProviderName()) {
			$isChanged = true;
			$msg = $msg . "\r\nProvider Name: " . $this->providerName . " -> " . $hspRequest->getProviderName();
		}
		if($this->personIncurringExpense != $hspRequest->getPersonIncurringExpense()) {
			$isChanged = true;
			$msg = $msg . "\r\nPerson Incurring Expense: " . $this->personIncurringExpense . " -> " . $hspRequest->getPersonIncurringExpense();
		}
		if($this->expenseDescription != $hspRequest->getExpenseDescription()) {
			$isChanged = true;
			$msg = $msg . "\r\nExpense Description: " . $this->expenseDescription . " -> " . $hspRequest->getExpenseDescription();
		}
		if($this->expenseAmount != $hspRequest->getExpenseAmount()) {
			$isChanged = true;
			$msg = $msg . "\r\nExpense Amount: " . $this->expenseAmount . " -> " . $hspRequest->getExpenseAmount();
		}
		if($this->paymentMadeTo != $hspRequest->getPaymentMadeTo()) {
			$isChanged = true;
			$msg = $msg . "\r\nPayment Made To: " . $this->paymentMadeTo . " -> " . $hspRequest->getPaymentMadeTo();
		}
		if($this->thirdPartyAccountNumber != $hspRequest->getThirdPartyAccountNumber()) {
			$isChanged = true;
			$msg = $msg . "\r\nThird Party Account Number: " . $this->thirdPartyAccountNumber . " -> " . $hspRequest->getThirdPartyAccountNumber();
		}
		if($this->mailAddress != $hspRequest->getMailAddress()) {
			$isChanged = true;
			$msg = $msg . "\r\nMail Address: " . $this->mailAddress . " -> " . $hspRequest->getMailAddress();
		}
		if($this->comments != $hspRequest->getComments()) {
			$isChanged = true;
			$msg = $msg . "\r\nComments: " . $this->comments . " -> " . $hspRequest->getComments();
		}

		if (!$isChanged) {
			return $isChanged;
		}else {
			return $msg;
		}

	}

	/**
	 * For a given employee and a HSP scheme, if there are new approved payments from last updated date,
	 * this function returns the sum of expense amounts of those payments.
	 */

	public static function calculateNewHspUsed($empId, $hspPlanId, $lasUpdatedDate, $currentYear=true) {

		if ($currentYear) {

			$currentYearStart = date('Y')."-01-01";

		    if ($lasUpdatedDate < $currentYearStart) {
		        $lasUpdatedDate = $currentYearStart;
		    }

		}

	    $selectTable = "`".self::HSP_PAYMENT_REQUEST_DB_TABLE."`";
		$selectFields[0] = "SUM(".self::DB_FIELD_EXPENSE_AMOUNT.")";
		$selectConditions[0] = "`".self::DB_FIELD_EMPLOYEE_ID."` = '".$empId."'";
		$selectConditions[1] = "`".self::DB_FIELD_HSP_ID."` = '".$hspPlanId."'";
		$selectConditions[2] = "`".self::DB_FIELD_DATE_PAID."` > '".$lasUpdatedDate."'";
		$selectConditions[3] = "`".self::DB_FIELD_STATUS."` = ".self::HSP_PAYMENT_REQUEST_STATUS_PAID;

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

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

		if ($result) {

			$resultArray = $dbConnection->dbObject->getArray($result);

			if ($resultArray[0] != null) {
			    return $resultArray[0];
			} else {
			    return 0;
			}

		} else {
		    return 0;
		}



	}
}

class HspPaymentRequestException extends Exception {
	const ERROR_IN_DB_QUERY = 1;
	const INVALID_ROW_COUNT = 2;
	const INVALID_ID = 3;
	const HSP_NOT_FOUND = 4;
	const HSP_REQUEST_NOT_FOUND = 5;
	const ALREADY_PAID = 6;
	const INVALID_EMPLOYEE_ID = 7;
	const INVALID_REQUEST = 8;
	const NO_HSP = 9;
	const HSP_TERMINATED = 10;
	const HSP_NOT_ENOUGH_BALANCE_REMAINING = 11;
	const EXCEED_LIMIT = 12;
	const INVALID_YEAR = 13;
	const INVALID_DATE = 14;
	const INVALID_DATE_PREVIOUS_YEAR = 15;
}
?>