/**
* For a give year, this function updates 'total_used' for all employees
* based on current HSP Scheme and 'hsp_used_last_updated'
*/
public static function updateUsed($year)
{
if (Config::getHspUsedLastUpdated() < date('Y-m-d')) {
$selectTable = "`" . self::DB_TABLE_HSP_SUMMARY . "`";
$selectFields[0] = "`" . self::DB_FIELD_SUMMARY_ID . "`";
$selectFields[1] = "`" . self::DB_FIELD_EMPLOYEE_ID . "`";
$selectFields[2] = "`" . self::DB_FIELD_HSP_PLAN_ID . "`";
$selectFields[3] = "`" . self::DB_FIELD_TOTAL_USED . "`";
$selectConditions[0] = "`" . self::DB_FIELD_HSP_PLAN_YEAR . "` = '" . $year . "'";
$selectConditions[1] = self::_twoHspPlansCondition(Config::getHspCurrentPlan());
$sqlBuilder = new SQLQBuilder();
$query = $sqlBuilder->simpleSelect($selectTable, $selectFields, $selectConditions);
$dbConnection = new DMLFunctions();
$result = $dbConnection->executeQuery($query);
$rowCount = $dbConnection->dbObject->numberOfRows($result);
$hspUsedLastUpdated = Config::getHspUsedLastUpdated();
for ($i = 0; $i < $rowCount; $i++) {
$row = $dbConnection->dbObject->getArray($result);
$updatedArray[$i][0] = $row[0];
$updatedArray[$i][1] = $row[3] + HspPaymentRequest::calculateNewHspUsed($row[1], $row[2], $hspUsedLastUpdated);
}
for ($i = 0; $i < count($updatedArray); $i++) {
$updateTable = "`" . self::DB_TABLE_HSP_SUMMARY . "`";
$updateFields[0] = "`" . self::DB_FIELD_TOTAL_USED . "`";
$updateValues[0] = "'" . $updatedArray[$i][1] . "'";
$updateConditions[0] = "`" . self::DB_FIELD_SUMMARY_ID . "` = '" . $updatedArray[$i][0] . "'";
$query = $sqlBuilder->simpleUpdate($updateTable, $updateFields, $updateValues, $updateConditions);
$dbConnection->executeQuery($query);
}
Config::setHspUsedLastUpdated(date('Y-m-d'));
}
}
public function testCalculateNewHspUsed()
{
$this->assertTrue(mysql_query("TRUNCATE `hs_hr_employee`;", $this->connection), mysql_error());
$this->assertTrue(mysql_query("TRUNCATE `hs_hr_hsp_payment_request`;", $this->connection), mysql_error());
$this->assertTrue(mysql_query("INSERT INTO `hs_hr_employee` ({$this->employeeFields}) VALUES (1, '001', 'Arnold', 'Subasinghe', '', 'Arnold', 0, NULL, NULL, NULL, NULL, NULL, '', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '', '', '', 'AF', '', '', '', '', '', '', NULL, NULL, '')"), mysql_error());
$this->assertTrue(mysql_query("INSERT INTO `hs_hr_hsp_payment_request` ({$this->paymentRequestFields}) " . "VALUES (1, 1, 1, '" . date('Y') . "-02-01', 'Test provider', 'Tester', 'Just testing', '150', 'TestX', '12345GD', " . "'1231, Test Grove, Test City', 'Test', '" . date('Y') . "-02-02', '123552-55821-ff25', 1)"), mysql_error());
$this->assertTrue(mysql_query("INSERT INTO `hs_hr_hsp_payment_request` ({$this->paymentRequestFields}) " . "VALUES (2, 1, 1, '" . date('Y') . "-02-10', 'Test provider', 'Tester', 'Just testing', '100', 'TestX', '12345GD', " . "'1231, Test Grove, Test City', 'Test', '" . date('Y') . "-02-11', '123552-55821-ff25', 1)"), mysql_error());
$this->assertTrue(mysql_query("INSERT INTO `hs_hr_hsp_payment_request` ({$this->paymentRequestFields}) " . "VALUES (3, 1, 1, '" . date('Y') . "-02-20', 'Test provider', 'Tester', 'Just testing', '127', 'TestX', '12345GD', " . "'1231, Test Grove, Test City', 'Test', '" . date('Y') . "-02-21', '123552-55821-ff25', 1)"), mysql_error());
$lastUpdated = date('Y') . "-02-05";
$this->assertEquals(HspPaymentRequest::calculateNewHspUsed(1, 1, $lastUpdated), 227);
$lastUpdated = date('Y') - 1 . "-02-05";
$this->assertEquals(HspPaymentRequest::calculateNewHspUsed(1, 1, $lastUpdated), 377);
$this->assertTrue(mysql_query("TRUNCATE `hs_hr_employee`;", $this->connection), mysql_error());
$this->assertTrue(mysql_query("TRUNCATE `hs_hr_hsp_payment_request`;", $this->connection), mysql_error());
}
