649 lines
24 KiB
PHP
649 lines
24 KiB
PHP
<?php
|
|
|
|
namespace Aiko;
|
|
|
|
use ErrorException;
|
|
use PDO;
|
|
use PDOException;
|
|
|
|
/**
|
|
* This trait is used to manage approval process easier
|
|
* it follow approval pattern naming conventions.
|
|
*
|
|
* example:
|
|
*
|
|
* if the main table is ghk then the child approval table should derrives its name.
|
|
*
|
|
* ghk [main table]
|
|
* ghk_approval [approval_table] with foreign ghk_id
|
|
* ghk_approval_group [approval_group_table] with foreign ghk_approval_id
|
|
*
|
|
* every $mainTable supplied in method will be generated as below
|
|
* {$mainTable}_approval [table],
|
|
* {$mainTable}_approval_group [table],
|
|
* {$mainTable}_id [column],
|
|
* {$mainTable}_approval_id [column],
|
|
* {$mainTable}_approval_group_id [column]
|
|
*/
|
|
trait ApprovalRequestTrait
|
|
{
|
|
private function getApprovalList($table, $whereColumn, $transactionId)
|
|
{
|
|
try {
|
|
$sql = "SELECT a.id,
|
|
getEmpName(a.approved_by) AS name,
|
|
a.approval_type,
|
|
a.approval_date,
|
|
a.reject_comment,
|
|
a.approval_status,
|
|
d.emp_grade AS grade,
|
|
c.photo_address AS images,
|
|
e.description AS job_title
|
|
FROM `$table` a
|
|
INNER JOIN employement b ON a.approved_by = b.nik
|
|
INNER JOIN emp_profiles c ON b.emp_profile_id = c.id
|
|
LEFT JOIN emp_job d ON b.nik = d.nik AND d.effective_date = (SELECT ej.effective_date FROM emp_job ej WHERE ej.nik = d.nik ORDER BY ej.effective_date DESC LIMIT 1)
|
|
LEFT JOIN mpp_detail e ON e.id = d.mpp_detail_id
|
|
LEFT JOIN org_layer f ON e.org_layer_id = f.id
|
|
WHERE a.$whereColumn =:id AND a.approval_status != '0'
|
|
ORDER BY a.approval_level";
|
|
$stmt = $this->registry->db->prepare($sql);
|
|
$stmt->bindValue(':id', $transactionId, PDO::PARAM_INT);
|
|
$stmt->execute();
|
|
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
$storage = new Storage($this->registry);
|
|
$configs = [];
|
|
for ($i = 0; $i < count($res); $i++) {
|
|
$avatar = null;
|
|
if ($res[$i]['images'] != null) {
|
|
$avatar = $storage->url($res[$i]['images'], 'emp_document');
|
|
}
|
|
$appStatus = [
|
|
'text' => 'Undefined',
|
|
'color' => 'grey-700-fg'
|
|
];
|
|
|
|
$icon = [
|
|
'color_fg' => 'grey-500-fg',
|
|
'color_bg' => 'grey-200-bg',
|
|
'icon' => 'icon-account-alert'
|
|
];
|
|
switch ($res[$i]['approval_status']) {
|
|
case '1':
|
|
$appStatus['text'] = 'Waiting Approval';
|
|
$appStatus['color'] = 'blue-800-fg';
|
|
$icon['color_fg'] = 'blue-700-fg';
|
|
$icon['color_bg'] = 'blue-50-bg';
|
|
$icon['icon'] = 'icon-timelapse';
|
|
break;
|
|
case '2':
|
|
$appStatus['text'] = 'Approved';
|
|
$appStatus['color'] = 'green-800-fg';
|
|
$icon['color_fg'] = 'green-700-fg';
|
|
$icon['color_bg'] = 'green-50-bg';
|
|
$icon['icon'] = 'icon-check-circle';
|
|
break;
|
|
case '4':
|
|
$appStatus['text'] = 'Rejected';
|
|
$appStatus['color'] = 'red-800-fg';
|
|
$icon['color_fg'] = 'red-700-fg';
|
|
$icon['color_bg'] = 'red-50-bg';
|
|
$icon['icon'] = 'icon-close-circle-outline';
|
|
break;
|
|
}
|
|
$config = [
|
|
'icon' => $icon,
|
|
'detail' => [
|
|
'title' => $appStatus,
|
|
'subtitle' => $res[$i]['approval_date'],
|
|
'comment' => $res[$i]['reject_comment']
|
|
],
|
|
'card' => [
|
|
'title' => 'Approve as ' . '(' . strtoupper($res[$i]['approval_type']) . ')',
|
|
'image' => $avatar,
|
|
'content' => [
|
|
'main' => $res[$i]['name'],
|
|
'sub' => $res[$i]['job_title'] . ' | ' . $res[$i]['grade']
|
|
]
|
|
],
|
|
'meta' => $res[$i]
|
|
];
|
|
array_push($configs, $config);
|
|
}
|
|
return $configs;
|
|
} catch (PDOException $e) {
|
|
$log = new Log('0');
|
|
$log->error('ApprovalHelper / getApprovalList :'
|
|
. $e->getMessage() . ', Line: '
|
|
. $e->getLine() . ', File: '
|
|
. $e->getFile());
|
|
return [];
|
|
} catch (ErrorException $e) {
|
|
$log = new Log('0');
|
|
$log->error('ApprovalHelper / getApprovalList :'
|
|
. $e->getMessage() . ', Line: '
|
|
. $e->getLine() . ', File: '
|
|
. $e->getFile());
|
|
return [];
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Approval createApprovalRequest
|
|
*
|
|
* dependencies already resolved by internal function with $mainTable as param
|
|
*
|
|
* @uses ApprovalPattern@setApproval
|
|
*
|
|
* @param mixed $approvalPatternObj
|
|
* @param string $mainTable main table approval that will be concated with other table related to query
|
|
* @param string $requestBy
|
|
* @param mixed $transactionId
|
|
* @param string $action
|
|
* @param string $startDate is from setApproval
|
|
* @param string $endDate is from setApproval
|
|
* @param string $effectiveDate is effective by for request by. leave it null if only need latest active employee until this day
|
|
*
|
|
* handle try catch in implementation
|
|
*/
|
|
private function createApprovalRequest(
|
|
$approvalPatternObj,
|
|
$mainTable,
|
|
$requestBy,
|
|
$transactionId,
|
|
$action,
|
|
$startDate = '1970-01-01',
|
|
$endDate = '1970-01-01',
|
|
$effectiveDate = null
|
|
) {
|
|
$stmtApproval = $this->_stmtApprovalSetApproval($mainTable);
|
|
$stmtGroupApproval = $this->_stmtGroupApproval($mainTable);
|
|
$employee = $this->getEmployee($requestBy, $effectiveDate);
|
|
if (count($employee) == 0) {
|
|
throw new ErrorException("Employee not found");
|
|
}
|
|
/** @var \modules\approvalpattern\model\Approvalpattern $approvalPatternObj */
|
|
return $approvalPatternObj->setApproval(
|
|
$transactionId,
|
|
$action,
|
|
$employee['grade'],
|
|
$employee['company_id'],
|
|
$employee['country_id'],
|
|
$employee['sub_area_id'],
|
|
$requestBy,
|
|
$startDate,
|
|
$endDate,
|
|
$requestBy,
|
|
$stmtApproval,
|
|
$stmtGroupApproval
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Approval approve request
|
|
*
|
|
* hard coded approval_status is 2. dependencies already resolved by internal function with $mainTable as param
|
|
*
|
|
* @uses getApproveDependencies
|
|
* @uses ApprovalPattern@doApproval
|
|
*
|
|
* @param mixed $approvalPatternObj
|
|
* @param string $mainTable main table approval that will be concated with other table related to query
|
|
* @param string $requestBy
|
|
* @param mixed $transactionId
|
|
* @param string $startDateAs
|
|
* @param string $endDateAS
|
|
*
|
|
*
|
|
* handle try catch in implementation
|
|
*/
|
|
private function approveRequest(
|
|
$approvalPatternObj,
|
|
$mainTable,
|
|
$requestBy,
|
|
$transactionId,
|
|
$startDateAs = '1970-01-01',
|
|
$endDateAS = '1970-01-01'
|
|
) {
|
|
$deps = $this->getApproveDependencies($mainTable, $startDateAs, $endDateAS);
|
|
/** @var \modules\approvalpattern\model\Approvalpattern $approvalPatternObj */
|
|
return $approvalPatternObj->doApproval(
|
|
$transactionId,
|
|
$requestBy,
|
|
'2',
|
|
$deps[0],
|
|
$deps[1],
|
|
$deps[2],
|
|
$deps[3]
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Approval reject request
|
|
*
|
|
* hard coded approval_status is 4. dependencies already resolved by internal function with $mainTable as param
|
|
*
|
|
* @uses getRejectDependencies
|
|
* @uses ApprovalPattern@doApproval
|
|
*
|
|
* @param mixed $approvalPatternObj
|
|
* @param string $mainTable main table approval that will be concated with other table related to query
|
|
* @param string $requestBy
|
|
* @param mixed $transactionId
|
|
* @param string $comment
|
|
* @param string $startDateAs
|
|
* @param string $endDateAS
|
|
*
|
|
* handle try catch in implementation
|
|
*/
|
|
private function rejectRequest(
|
|
$approvalPatternObj,
|
|
$mainTable,
|
|
$requestBy,
|
|
$transactionId,
|
|
$comment,
|
|
$startDateAs = '1970-01-01',
|
|
$endDateAS = '1970-01-01'
|
|
) {
|
|
$deps = $this->getRejectDependencies($mainTable, $startDateAs, $endDateAS);
|
|
/** @var \modules\approvalpattern\model\Approvalpattern $approvalPatternObj */
|
|
return $approvalPatternObj->doApproval(
|
|
$transactionId,
|
|
$requestBy,
|
|
'4',
|
|
$deps[0],
|
|
$deps[1],
|
|
$deps[2],
|
|
$deps[3],
|
|
$comment
|
|
);
|
|
}
|
|
|
|
|
|
/**
|
|
* Approval unapprove request
|
|
*
|
|
* dependencies already resolved by internal function with $mainTable as param
|
|
*
|
|
* @uses getRejectDependencies
|
|
* @uses ApprovalPattern@doUnApprove
|
|
*
|
|
* @param mixed $approvalPatternObj
|
|
* @param string $mainTable main table approval that will be concated with other table related to query
|
|
* @param string $requestBy
|
|
* @param mixed $transactionId
|
|
*
|
|
* handle try catch in implementation
|
|
*/
|
|
private function unapproveRequest($approvalPatternObj, $mainTable, $requestBy, $transactionId)
|
|
{
|
|
$deps = $this->getUnapproveDependencies($mainTable);
|
|
/** @var \modules\approvalpattern\model\Approvalpattern $approvalPatternObj */
|
|
return $approvalPatternObj->doUnApprove(
|
|
$transactionId,
|
|
$requestBy,
|
|
$deps[0],
|
|
$deps[1],
|
|
$deps[2],
|
|
$deps[3],
|
|
$deps[4]
|
|
);
|
|
}
|
|
|
|
|
|
/**
|
|
* Approval unreject request
|
|
*
|
|
* dependencies already resolved by internal function with $mainTable as param
|
|
*
|
|
* @uses getRejectDependencies
|
|
* @uses ApprovalPattern@doUnApprove
|
|
*
|
|
* @param mixed $approvalPatternObj
|
|
* @param string $mainTable main table approval that will be concated with other table related to query
|
|
* @param string $requestBy
|
|
* @param mixed $transactionId
|
|
*
|
|
* handle try catch in implementation
|
|
*/
|
|
private function unrejectRequest($approvalPatternObj, $mainTable, $requestBy, $transactionId)
|
|
{
|
|
$deps = $this->getUnrejectDependencies($mainTable);
|
|
/** @var \modules\approvalpattern\model\Approvalpattern $approvalPatternObj */
|
|
return $approvalPatternObj->doUnApprove(
|
|
$transactionId,
|
|
$requestBy,
|
|
$deps[0],
|
|
$deps[1],
|
|
$deps[2],
|
|
$deps[3],
|
|
$deps[4]
|
|
);
|
|
}
|
|
|
|
|
|
/**
|
|
* Query helper to resolve stmt dependencies for approval
|
|
*
|
|
* @param string $mainTable main table approval that will be concated with other table related to query
|
|
* @param string $startDateAs
|
|
* @param string $endDateAS
|
|
*
|
|
* @return array
|
|
* return will be array in exact order, it adjusted with doApproval parameters from ApprovalPattern
|
|
* [
|
|
* $stmtApproval,
|
|
* $stmtUpdateApproval,
|
|
* $stmtUpdateNextApproval,
|
|
* $stmtGetLevel
|
|
* ]
|
|
*
|
|
* example :
|
|
*
|
|
* $mainTable = 'incentive_hold_request';
|
|
*
|
|
* this method will resolve query to following table and column
|
|
* [table] incentive_hold_request_group_approval,
|
|
* [table] incentive_hold_request_approval,
|
|
* [table] incentive_hold_request,
|
|
* [column] incentive_hold_request_approval_id,
|
|
* [column] incentive_hold_request_id
|
|
*
|
|
* please using with cautions. this is just query builder to help make standard approval faster.
|
|
* if the feature need more advance customization, please do as usual.
|
|
*
|
|
*/
|
|
private function getApproveDependencies($mainTable, $startDateAs = '1970-01-01', $endDateAS = '1970-01-01')
|
|
{
|
|
$stmtApproval = $this->_stmtApproval($mainTable, $startDateAs, $endDateAS);
|
|
$stmtUpdateApproval = $this->_stmtUpdateApproval($mainTable);
|
|
$stmtUpdateNextApproval = $this->_stmtUpdateNextApproval($mainTable);
|
|
$stmtGetLevel = $this->_stmtGetLevel($mainTable, $startDateAs, $endDateAS);
|
|
|
|
return [
|
|
$stmtApproval,
|
|
$stmtUpdateApproval,
|
|
$stmtUpdateNextApproval,
|
|
$stmtGetLevel
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @param string $mainTable main table approval that will be concated with other table related to query
|
|
* @param string $startDateAs
|
|
* @param string $endDateAS
|
|
*
|
|
* @return array
|
|
*
|
|
* @uses getApproveDependencies
|
|
*/
|
|
private function getRejectDependencies($mainTable, $startDateAs = '1970-01-01', $endDateAS = '1970-01-01')
|
|
{
|
|
return $this->getApproveDependencies($mainTable, $startDateAs, $endDateAS);
|
|
}
|
|
|
|
/**
|
|
* Get Unapprove Statement dependencies
|
|
*
|
|
* @param string $mainTable
|
|
* @return array
|
|
* [
|
|
* $stmtCheckLast,
|
|
* $stmtUpdateGroup,
|
|
* $stmtUpdateLevel1,
|
|
* $stmtUpdateOtherLevel,
|
|
* $stmtCheckApproval
|
|
* ]
|
|
*
|
|
*/
|
|
private function getUnapproveDependencies($mainTable)
|
|
{
|
|
return $this->_stmtUnapproveApproval($mainTable);
|
|
}
|
|
|
|
/**
|
|
* Get Unreject Statement dependencies
|
|
*
|
|
* @param string $mainTable
|
|
* @return array
|
|
* [
|
|
* $stmtCheckLast,
|
|
* $stmtUpdateGroup,
|
|
* $stmtUpdateLevel1,
|
|
* $stmtUpdateOtherLevel,
|
|
* $stmtCheckApproval
|
|
* ]
|
|
*
|
|
*/
|
|
private function getUnrejectDependencies($mainTable)
|
|
{
|
|
return $this->_stmtUnrejectApproval($mainTable);
|
|
}
|
|
|
|
|
|
/**
|
|
* get Employee by effective date.
|
|
*
|
|
* if effective date is null, it will use current date
|
|
*
|
|
* @param string $requestBy
|
|
* @param string|null $effectiveDate
|
|
*
|
|
* @return array
|
|
*/
|
|
private function getEmployee($requestBy, $effectiveDate = null)
|
|
{
|
|
if ($effectiveDate == null) $effectiveDate = date('Y-m-d');
|
|
$stmt = $this->_stmtEmployeeData();
|
|
$stmt->bindValue(':nik', $requestBy, PDO::PARAM_STR);
|
|
$stmt->bindValue(':effectiveDate', $effectiveDate, PDO::PARAM_STR);
|
|
$stmt->execute();
|
|
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
return count($rs) > 0 ? $rs[0] : [];
|
|
}
|
|
|
|
/**
|
|
* Get latest approval status by transaction id
|
|
* @param string $mainTable
|
|
* @param mixed $transactionId
|
|
*
|
|
* @return array multidimentional
|
|
*/
|
|
private function getLatestApproval($mainTable, $transactionId)
|
|
{
|
|
$stmt = $this->_stmtLatestApproval($mainTable);
|
|
$stmt->bindValue(':id', $transactionId);
|
|
$stmt->execute();
|
|
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
return $res;
|
|
}
|
|
|
|
|
|
private function _stmtApproval($mainTable, $startDateAs = '1970-01-01', $endDateAS = '1970-01-01')
|
|
{
|
|
$sql = "SELECT
|
|
a.id,
|
|
a.approval_status,
|
|
a.approval_level,
|
|
a.is_last_approval,
|
|
IF(a.is_group='1', (
|
|
SELECT s.nik_app
|
|
FROM `{$mainTable}_group_approval` s
|
|
WHERE s.`{$mainTable}_approval_id`=a.id
|
|
AND s.nik_app=:approved_by),
|
|
a.approved_by
|
|
) AS approved_by,
|
|
a.days_limit_approval,
|
|
a.is_limit_approval,
|
|
'$startDateAs' AS start_date,
|
|
'$endDateAS' AS end_date
|
|
FROM `{$mainTable}_approval` a
|
|
LEFT JOIN `{$mainTable}` b ON a.`{$mainTable}_id` = b.id
|
|
WHERE a.`{$mainTable}_id` = :transaction_id
|
|
ORDER BY a.approval_level ASC";
|
|
return $this->registry->db->prepare($sql);
|
|
}
|
|
|
|
private function _stmtUpdateApproval($mainTable)
|
|
{
|
|
$sql = "UPDATE {$mainTable}_approval
|
|
SET approval_status = :approval_status,
|
|
approval_date = CURRENT_TIMESTAMP(),
|
|
`approved_by` = :approved_by,
|
|
reject_comment= :reject_comment
|
|
WHERE id = :id";
|
|
return $this->registry->db->prepare($sql);
|
|
}
|
|
|
|
private function _stmtUpdateNextApproval($mainTable)
|
|
{
|
|
$sql = "UPDATE {$mainTable}_approval SET approval_status='1' WHERE id=:id";
|
|
return $this->registry->db->prepare($sql);
|
|
}
|
|
|
|
private function _stmtGetLevel($mainTable, $startDateAs = '1970-01-01', $endDateAS = '1970-01-01')
|
|
{
|
|
$sql = "SELECT approval_level FROM (
|
|
SELECT
|
|
a.id,
|
|
a.approval_status,
|
|
a.approval_level,
|
|
a.is_last_approval,
|
|
IF(a.is_group='1',(
|
|
SELECT s.nik_app
|
|
FROM `{$mainTable}_group_approval` s
|
|
WHERE s.`{$mainTable}_approval_id`=a.id
|
|
AND s.nik_app = :approved_by),
|
|
a.approved_by
|
|
) AS approved_by,
|
|
a.days_limit_approval,
|
|
a.is_limit_approval,
|
|
'$startDateAs' AS start_date,
|
|
'$endDateAS' AS end_date
|
|
FROM `{$mainTable}_approval` a
|
|
LEFT JOIN `{$mainTable}` b ON a.`{$mainTable}_id` = b.id
|
|
WHERE a.`{$mainTable}_id`=:transaction_id
|
|
) AS t
|
|
WHERE t.approved_by = :approved_by
|
|
ORDER BY t.approval_level ASC";
|
|
return $this->registry->db->prepare($sql);
|
|
}
|
|
|
|
|
|
private function _stmtUnrejectApproval($mainTable)
|
|
{
|
|
return $this->_stmtUnapproveApproval($mainTable);
|
|
}
|
|
|
|
private function _stmtUnapproveApproval($mainTable)
|
|
{
|
|
$sqlIsLastApproval = "SELECT COUNT(id) AS total FROM {$mainTable}_approval
|
|
WHERE {$mainTable}_id=:transaction_id
|
|
AND is_last_approval='1'
|
|
AND approval_status='2'";
|
|
|
|
$sqlCheck = "SELECT COUNT(a.id) AS total FROM {$mainTable}_approval a
|
|
WHERE a.{$mainTable}_id=:transaction_id
|
|
AND (a.approved_by=:approved_by OR :approved_by IN (
|
|
SELECT s.nik_app FROM {$mainTable}_group_approval s
|
|
WHERE s.{$mainTable}_approval_id=a.id
|
|
)
|
|
)";
|
|
$sqlUpdateGroup = "UPDATE {$mainTable}_approval SET approved_by='-' WHERE {$mainTable}_id=:transaction_id AND is_group='1'";
|
|
$sqlUpdateLevel1 = "UPDATE {$mainTable}_approval SET approval_status='1' WHERE {$mainTable}_id=:transaction_id AND approval_level=1 ";
|
|
$sqlUpdateOther = "UPDATE {$mainTable}_approval SET approval_status='0' WHERE {$mainTable}_id=:transaction_id AND approval_level>1 ";
|
|
|
|
$stmtCheckLast = $this->registry->db->prepare($sqlIsLastApproval);
|
|
$stmtUpdateGroup = $this->registry->db->prepare($sqlUpdateGroup);
|
|
$stmtUpdateLevel1 = $this->registry->db->prepare($sqlUpdateLevel1);
|
|
$stmtUpdateOtherLevel = $this->registry->db->prepare($sqlUpdateOther);
|
|
$stmtCheckApproval = $this->registry->db->prepare($sqlCheck);
|
|
|
|
return array(
|
|
$stmtCheckLast,
|
|
$stmtUpdateGroup,
|
|
$stmtUpdateLevel1,
|
|
$stmtUpdateOtherLevel,
|
|
$stmtCheckApproval
|
|
);
|
|
}
|
|
|
|
|
|
private function _stmtApprovalSetApproval($mainTable)
|
|
{
|
|
$sql = "INSERT INTO `{$mainTable}_approval` (
|
|
`approval_level`,
|
|
`approval_status`,
|
|
`approval_type`,
|
|
`approved_by`,
|
|
`{$mainTable}_id`,
|
|
`is_group`,
|
|
`is_last_approval`,
|
|
`is_limit_approval`,
|
|
`days_limit_approval`)
|
|
VALUES (
|
|
:approval_level,
|
|
:approval_status,
|
|
:approval_type,
|
|
:approved_by,
|
|
:transaction_id,
|
|
:is_group,
|
|
:is_last_approval,
|
|
:is_limit_approval,
|
|
:days_limit_approval
|
|
);";
|
|
return $this->registry->db->prepare($sql);
|
|
}
|
|
|
|
private function _stmtGroupApproval($mainTable)
|
|
{
|
|
$sql = "INSERT INTO {$mainTable}_group_approval ({$mainTable}_approval_id, nik_app)
|
|
VALUES (:emp_absence_approval_id, :nik_app )";
|
|
return $this->registry->db->prepare($sql);
|
|
}
|
|
|
|
private function _stmtEmployeeData()
|
|
{
|
|
$sql = "SELECT
|
|
SUBSTRING(a.grade,1,1) AS grade,
|
|
b.sub_area_id,
|
|
c.company_id,
|
|
c.country_id,
|
|
ol.`bt_code`,
|
|
ol.abreviation as positionID,
|
|
getEmpJobtitle(a.nik,:effectiveDate) as job_title_name
|
|
FROM employement a
|
|
LEFT JOIN `emp_job` ej ON a.nik=ej.nik
|
|
AND ej.`effective_date`=(SELECT ej1.`effective_date` FROM `emp_job` ej1 WHERE ej1.nik=a.nik AND ej1.`effective_date` <=:effectiveDate ORDER BY ej1.`effective_date` DESC LIMIT 1)
|
|
INNER JOIN `mpp_detail` md on ej.`mpp_detail_id`=md.id
|
|
INNER JOIN org_layer ol on md.org_layer_id=ol.id
|
|
INNER JOIN emp_personal_sub_area b ON a.nik=b.nik
|
|
AND b.effective_date=(SELECT s.effective_date FROM emp_personal_sub_area s
|
|
WHERE s.nik=a.nik AND s.`effective_date` <=:effectiveDate ORDER BY s.`effective_date` DESC LIMIT 1)
|
|
INNER JOIN sub_area c ON b.sub_area_id=c.id
|
|
WHERE a.`nik`=:nik";
|
|
|
|
return $this->registry->db->prepare($sql);
|
|
}
|
|
|
|
|
|
private function _stmtLatestApproval($mainTable)
|
|
{
|
|
$sql = "SELECT a.id,
|
|
getEmpName(a.approved_by) AS name,
|
|
CASE WHEN approval_status='1' THEN
|
|
CONCAT_WS(' ','Waiting Approval', a.approved_by,'-',getEmpName(a.approved_by),'(',UPPER(a.approval_type),')')
|
|
WHEN approval_status='2' THEN
|
|
CONCAT_WS(' ','Approved By', a.approved_by,'-',getEmpName(a.approved_by),'(',UPPER(a.approval_type),')')
|
|
WHEN approval_status='4' THEN
|
|
CONCAT_WS(' ','Rejected By', a.approved_by,'-',getEmpName(a.approved_by),'(',UPPER(a.approval_type),')')
|
|
END AS types,
|
|
reject_comment AS rejected_comment,
|
|
approval_status
|
|
FROM {$mainTable}_approval a
|
|
WHERE a.{$mainTable}_id=:id AND approval_status != '0'
|
|
ORDER BY a.id DESC LIMIT 1";
|
|
return $this->registry->db->prepare($sql);
|
|
}
|
|
}
|