hspsummary.php

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

    public static function recordsExist($year, $hspPlanId=null) {

    	$selectTable = "`".parent::DB_TABLE_HSP_SUMMARY."`";
    	$selectFields[0] = "`".parent::DB_FIELD_HSP_PLAN_YEAR."`";
    	$selectConditions[0] = "`".parent::DB_FIELD_HSP_PLAN_YEAR."` = '".$year."'";
    	if (isset($hspPlanId)) {
    		$selectConditions[1] = "`".parent::DB_FIELD_HSP_PLAN_ID."` = '".$hspPlanId."'";
    	}

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

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

    	if ($dbConnection->dbObject->numberOfRows($result) > 0) {
    	    return true;
    	} else {
    	    return false;
    	}

    }

    /**
     * This functions returns the number of records that maches given conditions.
     */

	public static function recordsCount($year, $hspPlanId) {

    	$selectTable = "`".parent::DB_TABLE_HSP_SUMMARY."`";
    	$selectFields[0] = "COUNT(summary_id)";
    	$selectConditions[0] = "`".parent::DB_FIELD_HSP_PLAN_YEAR."` = '".$year."'";
		$selectConditions[1] = parent::_twoHspPlansCondition($hspPlanId);

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

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

    	return $row[0];

	}

    /**
     * This function get a database resource as the input and creates a HSP objects array
     * containing the data of the resource.
     */

    private static function _buildSummaryObjects($result) {

        $dbConnection = new DMLFunctions();
        $hspObjArr = null;

        while ($row = $dbConnection->dbObject->getArray($result)) {

            $hspObj = new Hsp();

            $hspObj->setSummaryId($row[0]);
            $hspObj->setEmployeeId($row[1]);
            $hspObj->setHspPlanId($row[2]);
            $hspObj->setHspPlanName(DefineHsp::getHspPlanName($row[2]));
            $hspObj->setEmployeeName(EmpInfo::getFullName($row[1]));
            $hspObj->setHspPlanYear($row[3]);
            $hspObj->setHspPlanStatus($row[4]);
            $hspObj->setAnnualLimit($row[5]);
            $hspObj->setEmployerAmount($row[6]);
            $hspObj->setEmployeeAmount($row[7]);
            $hspObj->setTotalAccrued($row[8]);
            $hspObj->setTotalUsed($row[9]);

           	$currentHspPlan = Config::getHspCurrentPlan();
           	if ($currentHspPlan == 3 || $currentHspPlan == 4 || $currentHspPlan == 5) { // If FSA is avaialbe in current plan
				if($row[2] == 3) {
					$hspObj->setFsaBalance(self::_fetchLastYearFsaBalance($row[1], ($row[3]-1)));
				} else {
					$hspObj->setFsaBalance("NA");
				}
           	}

            $hspObjArr[] = $hspObj;

        }

        return $hspObjArr;

	}

	public function isHspValueChangedByAdmin($existing) {
		$isChanged = false;

                $msg = 'HR Admin Changing HSP Value (Emp ID - '. $existing->getEmployeeId() .', Summary ID -'.$existing->getSummaryId().')';


                if($this->getAnnualLimit() != $existing->getAnnualLimit()) {
                        $isChanged = true;
                        $msg = $msg . "\r\n[Annual Limit] " . $existing->getAnnualLimit() . " -> " . $this->getAnnualLimit();
                }

                if($this->getEmployerAmount() != $existing->getEmployerAmount()) {
                        $isChanged = true;
                        $msg = $msg . "\r\n[Employer Amount] " . $existing->getEmployerAmount() . " -> " . $this->getEmployerAmount();
                }

                if($this->getEmployeeAmount() != $existing->getEmployeeAmount()) {
                        $isChanged = true;
                        $msg = $msg . "\r\n[Employee Amount] " . $existing->getEmployeeAmount() . " -> " . $this->getEmployeeAmount();
                }

                if($this->getTotalAccrued() != $existing->getTotalAccrued()) {
                        $isChanged = true;
                        $msg = $msg . "\r\n[Total Accrued] " . $existing->getTotalAccrued() . " -> " . $this->getTotalAccrued();
                }

                if($this->getTotalUsed() != $existing->getTotalUsed()) {
                        $isChanged = true;
                        $msg = $msg . "\r\n[Total Used] " . $existing->getTotalUsed() . " -> " . $this->getTotalUsed();
                }

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

	}

	/**
	 * This function returns FSA balance of last year if conditions are passed.
	 */

	public static function _fetchLastYearFsaBalance($empId, $year) {

		$selectTable = "`".parent::DB_TABLE_HSP_SUMMARY."`";
		$selectFields[0] = "`".parent::DB_FIELD_TOTAL_ACCRUED."`";
		$selectFields[1] = "`".parent::DB_FIELD_TOTAL_USED."`";
		$selectConditions[0] = "`".parent::DB_FIELD_EMPLOYEE_ID."` = '$empId'";
		$selectConditions[1] = "`".parent::DB_FIELD_HSP_PLAN_ID."` = '3'";
		$selectConditions[2] = "`".parent::DB_FIELD_HSP_PLAN_YEAR."` = '$year'";

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

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

		if ($dbConnection->dbObject->numberOfRows($result) == 1) {
			$row = $dbConnection->dbObject->getArray($result);
			$fsaEndDate = date('Y')."-03-15";
			$currentDate = date('Y-m-d');
			if ($currentDate <= $fsaEndDate) {
			    return ($row[0] - $row[1]);
			} else {
			    return 0;
			}
		} else {
		    return 0;
		}

	}

	/**
	 * This functions brings forward last year balance for HSA and HRA
	 */

	private static function _broughtForwardHspBalance() {

		$yearStart = date('Y')."-01-01";
		$currentDate = date('Y-m-d');

		if ($currentDate >= $yearStart && self::recordsExist(date('Y')-1) && !Config::getHspBroughtForwadYear("HspBroughtForward".date('Y'))) {

			$selectTable = "`".parent::DB_TABLE_HSP_SUMMARY."`";
			$selectFields[0] = "`".parent::DB_FIELD_EMPLOYEE_ID."`";
			$selectFields[1] = "`".parent::DB_FIELD_HSP_PLAN_ID."`";
			$selectFields[2] = "`".parent::DB_FIELD_TOTAL_ACCRUED."`";
			$selectFields[3] = "`".parent::DB_FIELD_TOTAL_USED."`";
			$year = date('Y')-1;
			$selectConditions[0] = "`".parent::DB_FIELD_HSP_PLAN_YEAR."` = '$year'";
			$selectConditions[1] = "`".parent::DB_FIELD_HSP_PLAN_ID."` != '3'";

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

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

			if ($dbConnection->dbObject->numberOfRows($result) > 0) {

				while ($row = $dbConnection->dbObject->getArray($result)) {

					$updateTable = "`".self::DB_TABLE_HSP_SUMMARY."`";
					$updateFields[0] = "`".parent::DB_FIELD_TOTAL_ACCRUED."`";
					$updateValues[0] = "`".parent::DB_FIELD_TOTAL_ACCRUED."`"+($row[2]-$row[3]);

					$updateConditions[0] = "`".parent::DB_FIELD_EMPLOYEE_ID."` = '".$row[0]."'";
					$updateConditions[1] = "`".parent::DB_FIELD_HSP_PLAN_ID."` = '".$row[1]."'";
					$updateConditions[2] = "`".parent::DB_FIELD_HSP_PLAN_YEAR."` = '".date('Y')."'";

					$sqlBuilder2 = new SQLQBuilder();
					$query2 = $sqlBuilder2->simpleUpdate($updateTable, $updateFields, $updateValues, $updateConditions);

					$dbConnection2 = new DMLFunctions();
					$dbConnection2->executeQuery($query2);

				}

			}

			Config::setHspBroughtForwadYear("set", "HspBroughtForward".date('Y'));

		}

	}

	public static function getYears() {

                $sqlBuilder = new SQLQBuilder();

                $selectTable = "`".self::DB_TABLE_HSP_SUMMARY."`";
                $selectFields[] = "DISTINCT(`".self::DB_FIELD_HSP_PLAN_YEAR."`)";
                $query = $sqlBuilder->simpleSelect($selectTable, $selectFields);

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

                if ($dbConnection->dbObject->numberOfRows($result) > 0) {
                        while ($row = $dbConnection->dbObject->getArray($result)) {
                                $years[] = (int)($row[0]);
                        }
                }

                $years[] = date('Y') - 1;
                $years[] = date('Y');
                $years[] = date('Y') + 1;

                $years = array_unique($years);

                sort($years); 

	        return $years;
	
	}

}

/**
 * Exception class
 */

class HspSummaryException extends Exception {

    const HSP_PLAN_NOT_DEFINED = 1;
    const NO_EMPLOYEE_EXISTS = 2;

}
?>