dbBuilderSelectJoin(
'gmb_events t1
INNER JOIN ref_manufacture_products t2 ON t1.ref_manunfacture_products_id = t2.id
INNER JOIN ref_manunfactures t3 ON t2.ref_manunfactures_id = t3.id
INNER JOIN ref_master_category_product_plant t4 ON t2.ref_master_category_product_plant_id = t4.id',
[
't1.id',
't1.event_name',
't1.decscription',
't1.event_date',
't1.start_date_time',
't1.end_date_time',
't2.ref_manunfactures_id',
't2.ref_master_category_product_plant_id',
't3.plan_name as ref_manunfactures_name',
't4.category_name as category_name',
't1.gmb_question_group_id',
't1.ref_manunfacture_products_id'
],
[],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$stmtRespons = $this->setStatementGetResponCount();
foreach ($rs as $k => $v) {
$rs[$k]['responCount'] = $this->getResponsCount($stmtRespons, $v['id']);
}
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmb_events t1',
[
'count(t1.id) as total',
],
[]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataGmbEventDetail($params)
{
try {
$event = $this->dbBuilderSelectJoin(
'gmb_events t1
INNER JOIN ref_manufacture_products t2 ON t1.ref_manunfacture_products_id = t2.id
INNER JOIN ref_manunfactures t3 ON t2.ref_manunfactures_id = t3.id
INNER JOIN ref_master_category_product_plant t4 ON t2.ref_master_category_product_plant_id = t4.id',
[
't1.*',
't3.plan_name as ref_manunfactures_name',
't4.category_name as ref_manunfacture_products_name'
],
[
['field' => 'id', 'key' => 't1.id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
]
);
$gmb_event_types = $this->dbBuilderSelectJoin(
'gmb_event_types t1
INNER JOIN gmb_question_types t2 ON t1.gmb_question_types_id = t2.id',
[
't2.*',
't1.weight',
'1 as edit'
],
[
['field' => 'gmb_events_id', 'key' => 't1.gmb_events_id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
]
);
$gmb_event_sections = $this->dbBuilderSelectJoin(
'gmb_event_sections t1
INNER JOIN gmb_question_section t2 ON t1.gmb_question_section_id = t2.id',
[
't2.*',
't1.weight',
'1 as edit'
],
[
['field' => 'gmb_events_id', 'key' => 't1.gmb_events_id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
]
);
$gmb_event_category = $this->dbBuilderSelectJoin(
'gmb_event_category t1
INNER JOIN gmb_question_category t2 ON t1.gmb_question_category_id = t2.id',
[
't2.*',
't1.weight',
'1 as edit'
],
[
['field' => 'gmb_events_id', 'key' => 't1.gmb_events_id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
]
);
$gmb_event_asessor = $this->dbBuilderSelectJoin(
'gmb_event_asessor t1
INNER JOIN employement a ON t1.employement_nik = a.nik
INNER JOIN emp_profiles b on a.`emp_profile_id` = b.`id`
LEFT JOIN gmb_event_questions t2 ON t1.gmb_events_id = t2.gmb_events_id
LEFT JOIN gmb_asessor_result t3 ON t2.id = t3.gmb_event_questions_id AND t3.gmb_event_asessor_id = t1.id',
[
"t1.id",
"t1.file_report",
"a.`nik` `empno`",
"concat(a.`nik` , ' - ',COALESCE(first_name, ''), ' ',COALESCE(middle_name, ''), ' ', COALESCE(last_name, '')) `fullname`",
"IF(office_mail, office_mail, emp_email) as email_address",
"'1' AS saved",
"getEmpName(a.`nik`) as name",
"COUNT(*) as total_question",
"SUM(IF(t3.id is not null, 1,0)) as total_answer",
"ROUND((SUM(IF(t3.id is not null, 1,0))/COUNT(*))*100,2) as percentage",
],
[
['field' => 'gmb_events_id', 'key' => 't1.gmb_events_id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
],
"GROUP BY
t1.id,
t1.employement_nik,
t1.gmb_events_id"
);
$stmt = $this->setStatementGetIssues();
$stmt->bindValue(':event_id', $params['id'], PDO::PARAM_INT);
$stmt->execute();
$gmb_event_issues = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmtAssIssue = $this->setStatementGetResponCountAssIssue();
foreach ($gmb_event_issues as $k => $val) {
$gmb_event_issues[$k]['responCount'] = $this->getResponsCount($stmtAssIssue, $val['gmb_asessor_result_id']);
}
$stmt = $this->setStatementGetGeneralIssues();
$stmt->bindValue(':event_id', $params['id'], PDO::PARAM_INT);
$stmt->execute();
$gmb_general_issues = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmtGetcountGenIssue = $this->setStatementGetResponCountGenIssue();
foreach ($gmb_general_issues as $k => $val) {
$gmb_general_issues[$k]['responCount'] = $this->getResponsCount($stmtGetcountGenIssue, $val['id']);
}
$stmt = $this->setStatementGetResponCountGeneralIssue();
$stmt->bindValue(':id', $params['id'], PDO::PARAM_INT);
$stmt->execute();
$rsGenIssue = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = $this->setStatementGetResponCountIssue();
$stmt->bindValue(':id', $params['id'], PDO::PARAM_INT);
$stmt->execute();
$rsIssue = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($gmb_event_asessor as $key => $value) {
$gmb_event_asessor[$key]['score'] = $this->getScoreAssesor($value['id']);
}
foreach ($gmb_event_issues as $k => $v) {
$gmb_event_issues[$k]['images'] = "";
if (!empty($v['file_location'])) {
$gmb_event_issues[$k]['images'] = $this->registry->config->server_address . '/upload/genba-client/' . $v['file_location'];
}
}
$rData['event'] = $event;
$rData['gmb_event_types'] = $gmb_event_types;
$rData['gmb_event_sections'] = $gmb_event_sections;
$rData['gmb_event_category'] = $gmb_event_category;
$rData['gmb_event_asessor'] = $gmb_event_asessor;
$rData['gmb_event_issues'] = $gmb_event_issues;
$rData['gmb_general_issues'] = $gmb_general_issues;
$rData['responCountGeneralIssue'] = count($rsGenIssue) > 0 ? $rsGenIssue[0]['total'] : 0;
$rData['responCountIssue'] = count($rsIssue) > 0 ? $rsIssue[0]['total'] : 0;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataGmbEventDetailByAssesor($params)
{
try {
$event = $this->dbBuilderSelectJoin(
'gmb_event_asessor a
INNER JOIN employement b ON a.employement_nik = b.nik
INNER JOIN emp_profiles c on b.`emp_profile_id` = c.`id`
INNER JOIN gmb_events t1 ON a.gmb_events_id = t1.id
INNER JOIN ref_manufacture_products t2 ON t1.ref_manunfacture_products_id = t2.id
INNER JOIN ref_manunfactures t3 ON t2.ref_manunfactures_id = t3.id
INNER JOIN ref_master_category_product_plant t4 ON t2.ref_master_category_product_plant_id = t4.id',
[
't1.*',
't3.plan_name as ref_manunfactures_name',
't4.category_name as ref_manunfacture_products_name',
"b.`nik` `empno`",
"concat(b.`nik` , ' - ',COALESCE(first_name, ''), ' ',COALESCE(middle_name, ''), ' ', COALESCE(last_name, '')) `fullname`",
"IF(office_mail, office_mail, emp_email) as email_address",
"'1' AS saved",
"getEmpName(b.`nik`) as assesor_name"
],
[
['field' => 'id', 'key' => 'a.id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
]
);
$gmb_event_types = $this->dbBuilderSelectJoin(
'gmb_event_types t1
INNER JOIN gmb_question_types t2 ON t1.gmb_question_types_id = t2.id
INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_types_id
INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id',
[
't2.*',
'COUNT(t3.id) as total_question',
'SUM(IF(t6.id is not null, 1,0)) as total_answer',
'ROUND((SUM(IF(t6.id is not null, 1,0))/COUNT(t3.id))*100,2) as percentage',
'sum(t3.max_score) as total_max_score',
'ROUND(((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))*100,2) as score',
'ROUND((((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))/ (t1.weight/100)),2) as ach_result_section',
't4.gmb_events_id as event_id'
],
[
['field' => 'id', 'key' => 't5.id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
],
" GROUP BY
t1.id"
);
$gmb_event_category = $this->dbBuilderSelectJoin(
'gmb_event_category t1
INNER JOIN gmb_question_category t2 ON t1.gmb_question_category_id = t2.id
INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_category_id
INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id',
[
't2.*',
'COUNT(t3.id) as total_question',
'SUM(IF(t6.id is not null, 1,0)) as total_answer',
'ROUND((SUM(IF(t6.id is not null, 1,0))/COUNT(t3.id))*100,2) as percentage',
'sum(t3.max_score) as total_max_score',
'ROUND(((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))*100,2) as score',
'ROUND((((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))/ (t1.weight/100)),2) as ach_result_section',
't4.gmb_events_id as event_id'
],
[
['field' => 'id', 'key' => 't5.id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
],
" GROUP BY
t1.id"
);
$gmb_event_sections = $this->dbBuilderSelectJoin(
'gmb_event_sections t1
INNER JOIN gmb_question_section t2 ON t1.gmb_question_section_id = t2.id
INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_section_id
INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id
LEFT JOIN gmb_asessor_result_notes t7 ON t6.id = t7.gmb_asessor_result_id',
[
't2.*',
't7.note',
'COUNT(t3.id) as total_question',
'SUM(IF(t6.id is not null, 1,0)) as total_answer',
'ROUND((SUM(IF(t6.id is not null, 1,0))/COUNT(t3.id))*100,2) as percentage',
'sum(t3.max_score) as total_max_score',
'ROUND(((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))*100,2) as score',
'ROUND((((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))/ (t1.weight/100)),2) as ach_result_section',
't4.gmb_events_id as event_id'
],
[
['field' => 'id', 'key' => 't5.id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
],
" GROUP BY
t1.id "
);
$rData['event'] = $event;
$rData['gmb_event_types'] = $gmb_event_types;
$rData['gmb_event_category'] = $gmb_event_category;
$rData['gmb_event_sections'] = $gmb_event_sections;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getQuestionList($params)
{
try {
if ($params['type'] == 'type') {
$question = $this->dbBuilderSelectJoin(
'gmb_event_types t1
INNER JOIN gmb_question_types t2 ON t1.gmb_question_types_id = t2.id
INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_types_id
INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id
LEFT JOIN gmb_answer_detail t7 ON t6.gmb_answer_detail_id = t7.id
LEFT JOIN gmb_asessor_result_notes t8 ON t8.gmb_asessor_result_id = t6.id
LEFT JOIN gmb_assesor_result_files t9 ON t9.gmb_asessor_result_id = t6.id ',
[
't3.id',
't3.question_code',
't3.question',
't7.answer_label',
't6.score',
't8.note',
't9.file_location as file'
],
[
['field' => 'gmb_question_types_id', 'key' => 't1.gmb_question_types_id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
['field' => 'id', 'key' => 't5.id', 'val' => $params['assesor_id'], 'bind' => PDO::PARAM_STR],
]
);
}
if ($params['type'] == 'category') {
$question = $this->dbBuilderSelectJoin(
'gmb_event_category t1
INNER JOIN gmb_question_category t2 ON t1.gmb_question_category_id = t2.id
INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_category_id
INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id
LEFT JOIN gmb_answer_detail t7 ON t6.gmb_answer_detail_id = t7.id
LEFT JOIN gmb_asessor_result_notes t8 ON t8.gmb_asessor_result_id = t6.id
LEFT JOIN gmb_assesor_result_files t9 ON t9.gmb_asessor_result_id = t6.id',
[
't3.id',
't3.question_code',
't3.question',
't7.answer_label',
't6.score',
't8.note',
't9.file_location as file'
],
[
['field' => 'gmb_question_types_id', 'key' => 't1.gmb_question_category_id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
['field' => 'id', 'key' => 't5.id', 'val' => $params['assesor_id'], 'bind' => PDO::PARAM_STR],
]
);
}
if ($params['type'] == 'section') {
$question = $this->dbBuilderSelectJoin(
'gmb_event_sections t1
INNER JOIN gmb_question_section t2 ON t1.gmb_question_section_id = t2.id
INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_section_id
INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id
LEFT JOIN gmb_answer_detail t7 ON t6.gmb_answer_detail_id = t7.id
LEFT JOIN gmb_asessor_result_notes t8 ON t8.gmb_asessor_result_id = t6.id
LEFT JOIN gmb_assesor_result_files t9 ON t9.gmb_asessor_result_id = t6.id',
[
't3.id',
't3.question_code',
't3.question',
't7.answer_label',
't6.score',
't6.gmb_answer_detail_id',
't8.note',
't9.file_location as file'
],
[
['field' => 'gmb_question_types_id', 'key' => 't1.gmb_question_section_id', 'val' => $params['id'], 'bind' => PDO::PARAM_STR],
['field' => 'id', 'key' => 't5.id', 'val' => $params['assesor_id'], 'bind' => PDO::PARAM_STR],
]
);
}
foreach ($question as $key => $value) {
$question[$key]['pilihan'] = $this->dbBuilderSelectJoin(
'gmb_answer_detail t1',
[
't1.*',
],
[
['field' => 'gmb_questions_id', 'key' => 't1.gmb_questions_id', 'val' => $value['id'], 'bind' => PDO::PARAM_STR],
]
);
}
$rData['question'] = $question;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataGmbQuestionType()
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmb_question_types t1',
[
't1.*'
],
[]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataRefManunfactures($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'ref_manunfactures t1',
[
't1.*'
],
[]
);
if ($params['event_id']) {
$rs = $this->dbBuilderSelectJoin(
'ref_manunfactures t1
INNER JOIN gmbs_events t2 ON t1.id = t2.ref_manufactures_id',
[
't1.*'
],
[
['field' => 'id', 'key' => 't2.id', 'val' => $params['event_id'], 'bind' => PDO::PARAM_STR],
]
);
}
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataRefManunfacturesProduct($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'ref_manufacture_products t1
INNER JOIN ref_master_category_product_plant t2 ON t1.ref_master_category_product_plant_id =t2.id',
[
't1.id',
't2.category_name',
'0 as edit'
],
[
['field' => 'ref_manunfactures_id', 'key' => 't1.ref_manunfactures_id', 'val' => $params['ref_manunfactures_id'], 'bind' => PDO::PARAM_STR],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataQuestionSection($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmb_question_section t1',
[
't1.*',
'0 as edit'
],
[
['field' => 'ref_manufacture_products_id', 'key' => 't1.ref_manufacture_products_id', 'val' => $params['ref_manunfacture_products_id'], 'bind' => PDO::PARAM_STR],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataQuestionCategory()
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmb_question_category t1',
[
't1.*',
'0 as edit'
],
[]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveEventGemba($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
$event_date = date("Y-m-d", strtotime($data->event_date));
$start_time = date("H:i", strtotime($data->start_date_time));
$end_time = date("H:i", strtotime($data->end_date_time));
$start_date_time = new DateTime($event_date . ' ' . $start_time);
$end_date_time = new DateTime($event_date . ' ' . $end_time);
if (!isset($data->id)) {
$event = $this->dbBuilderInsert(
'gmb_events',
[
['key' => 'event_name', 'val' => $data->event_name, 'bind' => PDO::PARAM_STR],
['key' => 'decscription', 'val' => $data->description, 'bind' => PDO::PARAM_STR],
['key' => 'event_date', 'val' => $event_date, 'bind' => PDO::PARAM_STR],
['key' => 'start_date_time', 'val' => $start_date_time->format('Y-m-d H:i'), 'bind' => PDO::PARAM_STR],
['key' => 'end_date_time', 'val' => $end_date_time->format('Y-m-d H:i'), 'bind' => PDO::PARAM_STR],
['key' => 'ref_manunfacture_products_id', 'val' => $data->ref_manunfacture_products_id, 'bind' => PDO::PARAM_STR],
['key' => 'gmb_question_group_id', 'val' => $data->gmb_question_group_id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$event) {
return false;
}
} else {
$event = $data->id;
$event_update = $this->dbBuilderUpdate('gmb_events', [
['key' => 'event_name', 'val' => $data->event_name, 'bind' => PDO::PARAM_STR],
['key' => 'decscription', 'val' => $data->description, 'bind' => PDO::PARAM_STR],
['key' => 'event_date', 'val' => $event_date, 'bind' => PDO::PARAM_STR],
['key' => 'start_date_time', 'val' => $start_date_time->format('Y-m-d H:i'), 'bind' => PDO::PARAM_STR],
['key' => 'end_date_time', 'val' => $end_date_time->format('Y-m-d H:i'), 'bind' => PDO::PARAM_STR],
['key' => 'ref_manunfacture_products_id', 'val' => $data->ref_manunfacture_products_id, 'bind' => PDO::PARAM_STR],
['key' => 'gmb_question_group_id', 'val' => $data->gmb_question_group_id, 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $event, 'bind' => PDO::PARAM_INT],
]);
if (!$event_update) {
return false;
}
$this->dbBuilderDelete('gmb_event_types', 'gmb_events_id', $event);
$this->dbBuilderDelete('gmb_event_sections', 'gmb_events_id', $event);
$this->dbBuilderDelete('gmb_event_category', 'gmb_events_id', $event);
$this->dbBuilderDelete('gmb_event_asessor', 'gmb_events_id', $event);
$this->dbBuilderDelete('gmb_questions', 'gmb_events_id', $event);
}
foreach ($data->type as $key => $value) {
$event_types = $this->dbBuilderInsert(
'gmb_event_types',
[
['key' => 'gmb_events_id', 'val' => $event, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $value->weight, 'bind' => PDO::PARAM_STR],
['key' => 'gmb_question_types_id', 'val' => $value->id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$event_types) {
return false;
}
}
foreach ($data->section as $key => $value) {
$event_sections = $this->dbBuilderInsert(
'gmb_event_sections',
[
['key' => 'gmb_events_id', 'val' => $event, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $value->weight, 'bind' => PDO::PARAM_STR],
['key' => 'gmb_question_section_id', 'val' => $value->id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$event_sections) {
return false;
}
}
foreach ($data->gmb_question_category_id as $key => $value) {
$event_category = $this->dbBuilderInsert(
'gmb_event_category',
[
['key' => 'gmb_events_id', 'val' => $event, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $value->weight, 'bind' => PDO::PARAM_STR],
['key' => 'gmb_question_category_id', 'val' => $value->id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$event_category) {
return false;
}
}
foreach ($data->asessor as $key => $value) {
$event_asessor = $this->dbBuilderInsert(
'gmb_event_asessor',
[
['key' => 'gmb_events_id', 'val' => $event, 'bind' => PDO::PARAM_STR],
['key' => 'employement_nik', 'val' => $value->nik->empno, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$event_asessor) {
return false;
}
$mail = new Mail($this->registry);
$address_cc = array();
$maildata['address'] = $value->nik->email_address;
$maildata['address_cc_mail'] = $address_cc;
$maildata['addressname'] = "";
$maildata['empreqname'] = "";
$maildata['attachmentlocation'] = '';
$maildata['attachmentfilename'] = '';
$maildata['subject'] = "Genba Event";
$maildata['link'] = '';
$maildata['message'] = "Genba Event Invite";
$rsmail = $mail->sendMail($maildata);
if (!$rsmail) {
throw new Exception("3");
}
}
foreach ($data->type as $i => $vi) {
foreach ($data->section as $j => $vj) {
foreach ($data->gmb_question_category_id as $k => $vk) {
$question = $this->dbBuilderSelectJoin(
'gmb_questions t1',
[
't1.*'
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR],
['field' => 'gmb_question_section_id', 'key' => 't1.gmb_question_section_id', 'val' => $vj->id, 'bind' => PDO::PARAM_STR],
['field' => 'gmb_question_types_id', 'key' => 't1.gmb_question_types_id', 'val' => $vi->id, 'bind' => PDO::PARAM_STR],
['field' => 'gmb_question_category_id', 'key' => 't1.gmb_question_category_id', 'val' => $vk->id, 'bind' => PDO::PARAM_STR],
['field' => 'gmb_question_group_id', 'key' => 't1.gmb_question_group_id', 'val' => $data->gmb_question_group_id, 'bind' => PDO::PARAM_STR],
]
);
if (count($question) > 0) {
$event_questions = $this->dbBuilderInsert(
'gmb_event_questions',
[
['key' => 'gmb_events_id', 'val' => $event, 'bind' => PDO::PARAM_STR],
['key' => 'gmb_questions_id', 'val' => $question[0]['id'], 'bind' => PDO::PARAM_STR],
],
true
);
if (!$event_questions) {
return false;
}
}
}
}
}
// return false;
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveEventGemba: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveEventGemba: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteGembaEvent($id)
{
try {
$this->registry->db->beginTransaction();
$this->dbBuilderDelete('gmb_events', 'id', $id);
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteGembaEvent: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteGembaEvent: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function getScoreAssesor($id)
{
try {
$sql = "SELECT
ROUND(SUM(tab.score),2) as score
FROM
(
SELECT
t2.*,
t7.note,
COUNT(t3.id) as total_question,
SUM(IF(t6.id is not null, 1,0)) as total_answer,
ROUND((SUM(IF(t6.id is not null, 1,0))/COUNT(t3.id))*100,2) as percentage,
sum(t3.max_score) as total_max_score,
((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))*100 as score,
ROUND((((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))/ (t1.weight/100)),2) as ach_result_section,
t4.gmb_events_id as event_id
FROM
gmb_event_sections t1
INNER JOIN gmb_question_section t2 ON t1.gmb_question_section_id = t2.id
INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_section_id
INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id
LEFT JOIN gmb_asessor_result_notes t7 ON t6.id = t7.gmb_asessor_result_id
WHERE
t5.id =:assesor_id
GROUP BY
t1.id
) tab";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindValue(':assesor_id', $id, PDO::PARAM_STR);
$stmt->execute();
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
return count($rs) > 0 ? $rs[0]['score'] : 0;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataReport($params)
{
try {
$event = $this->dbBuilderSelectJoin(
'gmb_events t1
INNER JOIN ref_manufacture_products t2 ON t1.ref_manunfacture_products_id = t2.id
INNER JOIN ref_manunfactures t3 ON t2.ref_manunfactures_id = t3.id
INNER JOIN ref_master_category_product_plant t4 ON t2.ref_master_category_product_plant_id = t4.id',
[
't1.*',
't3.plan_name as ref_manunfactures_name',
't4.category_name as ref_manunfacture_products_name'
],
[
['field' => 'id', 'key' => 't1.id', 'val' => $params['event_id'], 'bind' => PDO::PARAM_STR],
]
);
if ($params['assesor_id'] != '') {
$event = $this->dbBuilderSelectJoin(
'gmb_event_asessor a
INNER JOIN employement b ON a.employement_nik = b.nik
INNER JOIN emp_profiles c on b.`emp_profile_id` = c.`id`
INNER JOIN gmb_events t1 ON a.gmb_events_id = t1.id
INNER JOIN ref_manufacture_products t2 ON t1.ref_manunfacture_products_id = t2.id
INNER JOIN ref_manunfactures t3 ON t2.ref_manunfactures_id = t3.id
INNER JOIN ref_master_category_product_plant t4 ON t2.ref_master_category_product_plant_id = t4.id',
[
't1.*',
't3.plan_name as ref_manunfactures_name',
't4.category_name as ref_manunfacture_products_name',
"b.`nik` `empno`",
"concat(b.`nik` , ' - ',COALESCE(first_name, ''), ' ',COALESCE(middle_name, ''), ' ', COALESCE(last_name, '')) `fullname`",
"IF(office_mail, office_mail, emp_email) as email_address",
"'1' AS saved",
"getEmpName(b.`nik`) as assesor_name"
],
[
['field' => 'id', 'key' => 'a.id', 'val' => $params['assesor_id'], 'bind' => PDO::PARAM_STR],
]
);
}
$section_where = "WHERE t1.gmb_events_id={$params['event_id']}";
if ($params['assesor_id'] != '') {
$section_where .= " AND t5.id={$params['assesor_id']}";
}
$sql_event_sections = "SELECT
tab.*,
ROUND(avg(tab.score),2) as score,
ROUND(avg(tab.ach_result_section),2) as ach_result_section
FROM
(
SELECT
t2.*,
COUNT(t3.id) as total_question,
SUM(IF(t6.id is not null, 1,0)) as total_answer,
ROUND((SUM(IF(t6.id is not null, 1,0))/COUNT(t3.id))*100,2) as percentage,
sum(t3.max_score) as total_max_score,
ROUND(((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))*100,2) as score,
ROUND((((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))/ (t1.weight/100)),2)*100 as ach_result_section,
t4.gmb_events_id as event_id,
(SELECT
a.question_code
FROM
gmb_questions a
INNER JOIN gmb_question_section b ON a.gmb_question_section_id = b.id
WHERE
b.id=t1.gmb_question_section_id
ORDER BY
a.id ASC LIMIT 1
) first_question,
(SELECT
a.question_code
FROM
gmb_questions a
INNER JOIN gmb_question_section b ON a.gmb_question_section_id = b.id
WHERE
b.id=t1.gmb_question_section_id
ORDER BY
a.id DESC LIMIT 1
) last_question
FROM
gmb_event_sections t1
INNER JOIN gmb_question_section t2 ON t1.gmb_question_section_id = t2.id
INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_section_id
INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id
$section_where
GROUP BY
t1.id,t5.id
) tab
GROUP BY
tab.id";
// var_dump($sql_event_sections);die();
$stmt_event_sections = $this->registry->db->prepare($sql_event_sections);
$stmt_event_sections->execute();
$gmb_event_sections = $stmt_event_sections->fetchAll(PDO::FETCH_ASSOC);
// $section_where=[];
// array_push($section_where,
// ['field' => 'gmb_events_id','key' => 't1.gmb_events_id', 'val' => $params['event_id'], 'bind' => PDO::PARAM_STR]
// );
// if($params['assesor_id'] != ''){
// array_push($section_where,
// ['field' => 'id','key' => 't5.id', 'val' => $params['assesor_id'], 'bind' => PDO::PARAM_STR]
// );
// }
// $gmb_event_sections=$this->dbBuilderSelectJoin('gmb_event_sections t1
// INNER JOIN gmb_question_section t2 ON t1.gmb_question_section_id = t2.id
// INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_section_id
// INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
// INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
// LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id',
// [
// 't2.*',
// 'COUNT(t3.id) as total_question',
// 'SUM(IF(t6.id is not null, 1,0)) as total_answer',
// 'ROUND((SUM(IF(t6.id is not null, 1,0))/COUNT(t3.id))*100,2) as percentage',
// 'sum(t3.max_score) as total_max_score',
// 'ROUND(((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))*100,2) as score',
// 'ROUND((((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))/ (t1.weight/100)),2)*100 as ach_result_section',
// 't4.gmb_events_id as event_id',
// '(SELECT
// a.question_code
// FROM
// gmb_questions a
// INNER JOIN gmb_question_section b ON a.gmb_question_section_id = b.id
// WHERE
// b.id=t1.gmb_question_section_id
// ORDER BY
// a.id ASC LIMIT 1
// ) first_question',
// '(SELECT
// a.question_code
// FROM
// gmb_questions a
// INNER JOIN gmb_question_section b ON a.gmb_question_section_id = b.id
// WHERE
// b.id=t1.gmb_question_section_id
// ORDER BY
// a.id DESC LIMIT 1
// ) last_question'
// ],
// $section_where,
// " GROUP BY
// t1.id "
// );
$gmb_event_types = $this->dbBuilderSelectJoin(
'gmb_event_types t1
INNER JOIN gmb_question_types t2 ON t1.gmb_question_types_id = t2.id',
[
't2.id',
't2.type_name as name',
't1.weight',
'"types" as type'
],
[
['field' => 'gmb_events_id', 'key' => 't1.gmb_events_id', 'val' => $params['event_id'], 'bind' => PDO::PARAM_STR],
]
);
$gmb_event_category = $this->dbBuilderSelectJoin(
'gmb_event_category t1
INNER JOIN gmb_question_category t2 ON t1.gmb_question_category_id = t2.id',
[
't2.id',
't2.category_name as name',
't1.weight',
'"category" as type'
],
[
['field' => 'gmb_events_id', 'key' => 't1.gmb_events_id', 'val' => $params['event_id'], 'bind' => PDO::PARAM_STR],
]
);
$header = array();
foreach ($gmb_event_category as $key => $value) {
$gmb_event_category[$key]['total_score'] = 0;
$gmb_event_category[$key]['total_max_score'] = 0;
array_push($header, $gmb_event_category[$key]);
}
foreach ($gmb_event_types as $key => $value) {
$gmb_event_types[$key]['total_score'] = 0;
$gmb_event_types[$key]['total_max_score'] = 0;
array_push($header, $gmb_event_types[$key]);
}
$total_all_max_score = 0;
foreach ($gmb_event_sections as $i => $vi) {
$category_type = array();
foreach ($header as $j => $vj) {
$where = $section_where;
if ($vj['type'] == 'category') {
// array_push($where,
// ['field' => 'gmb_question_category_id','key' => 't3.gmb_question_category_id', 'val' => $vj['id'], 'bind' => PDO::PARAM_STR]
// );
// array_push($where,
// ['field' => 'gmb_question_section_id','key' => 't2.id', 'val' => $vi['id'], 'bind' => PDO::PARAM_STR]
// );
$where .= " AND t2.id={$vi['id']} AND t3.gmb_question_category_id={$vj['id']}";
// $category=$this->dbBuilderSelectJoin('gmb_event_sections t1
// INNER JOIN gmb_question_section t2 ON t1.gmb_question_section_id = t2.id
// INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_section_id
// INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
// INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
// LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id
// INNER JOIN gmb_event_category t7 ON t3.gmb_question_category_id = t7.gmb_question_category_id',
// [
// 't7.*',
// 'COUNT(t3.id) as total_question',
// 'SUM(IF(t6.id is not null, 1,0)) as total_answer',
// 'ROUND((SUM(IF(t6.id is not null, 1,0))/COUNT(t3.id))*100,2) as percentage',
// 'sum(t3.max_score) as total_max_score',
// 'sum(IFNULL(t6.score,0)) as total_score',
// 'ROUND(((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t7.weight/100))*100,2) as score',
// 'ROUND((((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t7.weight/100))/ (t7.weight/100)),2)*100 as ach_result_section',
// 't4.gmb_events_id as event_id'
// ],
// $where,
// " GROUP BY
// t1.id "
// );
$sql_category = "SELECT
tab.*,
round(avg(tab.total_score),1) as total_score,
round(avg(tab.total_max_score),1) as total_max_score,
avg(tab.score) as score,
avg(tab.ach_result_section) as ach_result_section
FROM
(
SELECT
t7.*,
COUNT(t3.id) as total_question,
SUM(IF(t6.id is not null, 1,0)) as total_answer,
ROUND((SUM(IF(t6.id is not null, 1,0))/COUNT(t3.id))*100,2) as percentage,
sum(t3.max_score) as total_max_score,
ROUND(((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))*100,2) as score,
ROUND((((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))/ (t1.weight/100)),2)*100 as ach_result_section,
t4.gmb_events_id as event_id,
sum(IFNULL(t6.score,0)) as total_score
FROM
gmb_event_sections t1
INNER JOIN gmb_question_section t2 ON t1.gmb_question_section_id = t2.id
INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_section_id
INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id
INNER JOIN gmb_event_category t7 ON t3.gmb_question_category_id = t7.gmb_question_category_id
$where
GROUP BY
t1.id,t5.id
) tab
GROUP BY
tab.id";
// var_dump($sql_category);die();
$stmt_category = $this->registry->db->prepare($sql_category);
$stmt_category->execute();
$category = $stmt_category->fetchAll(PDO::FETCH_ASSOC);
foreach ($category as $k => $vk) {
$category[$k]['name'] = $header[$j]['name'];
$category[$k]['type'] = $header[$j]['type'];
$header[$j]['total_score'] += $vk['total_score'];
$header[$j]['total_max_score'] += $vk['total_max_score'];
$total_all_max_score += $vk['total_max_score'];
array_push($category_type, $category[$k]);
}
}
if ($vj['type'] == 'types') {
// array_push($where,
// ['field' => 'gmb_question_types_id','key' => 't3.gmb_question_types_id', 'val' => $vj['id'], 'bind' => PDO::PARAM_STR]
// );
// array_push($where,
// ['field' => 'gmb_question_section_id','key' => 't2.id', 'val' => $vi['id'], 'bind' => PDO::PARAM_STR]
// );
// $category=$this->dbBuilderSelectJoin('gmb_event_sections t1
// INNER JOIN gmb_question_section t2 ON t1.gmb_question_section_id = t2.id
// INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_section_id
// INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
// INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
// LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id
// INNER JOIN gmb_event_types t7 ON t3.gmb_question_types_id = t7.gmb_question_types_id',
// [
// 't7.*',
// 'COUNT(t3.id) as total_question',
// 'SUM(IF(t6.id is not null, 1,0)) as total_answer',
// 'ROUND((SUM(IF(t6.id is not null, 1,0))/COUNT(t3.id))*100,2) as percentage',
// 'sum(t3.max_score) as total_max_score',
// 'sum(IFNULL(t6.score,0)) as total_score',
// 'ROUND(((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t7.weight/100))*100,2) as score',
// 'ROUND((((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t7.weight/100))/ (t7.weight/100)),2)*100 as ach_result_section',
// 't4.gmb_events_id as event_id'
// ],
// $where,
// " GROUP BY
// t1.id "
// );
$where .= " AND t2.id={$vi['id']} AND t3.gmb_question_types_id={$vj['id']}";
$sql_types = "SELECT
tab.*,
round(avg(tab.total_score),1) as total_score,
round(avg(tab.total_max_score),1) as total_max_score,
avg(tab.score) as score,
avg(tab.ach_result_section) as ach_result_section
FROM
(
SELECT
t7.*,
COUNT(t3.id) as total_question,
SUM(IF(t6.id is not null, 1,0)) as total_answer,
ROUND((SUM(IF(t6.id is not null, 1,0))/COUNT(t3.id))*100,2) as percentage,
sum(t3.max_score) as total_max_score,
ROUND(((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))*100,2) as score,
ROUND((((sum(IFNULL(t6.score,0)) / sum(t3.max_score)) * (t1.weight/100))/ (t1.weight/100)),2)*100 as ach_result_section,
t4.gmb_events_id as event_id,
sum(IFNULL(t6.score,0)) as total_score
FROM
gmb_event_sections t1
INNER JOIN gmb_question_section t2 ON t1.gmb_question_section_id = t2.id
INNER JOIN gmb_questions t3 ON t2.id = t3.gmb_question_section_id
INNER JOIN gmb_event_questions t4 ON t1.gmb_events_id = t4.gmb_events_id AND t3.id = t4.gmb_questions_id
INNER JOIN gmb_event_asessor t5 ON t4.gmb_events_id = t5.gmb_events_id
LEFT JOIN gmb_asessor_result t6 ON t4.id = t6.gmb_event_questions_id AND t6.gmb_event_asessor_id = t5.id
INNER JOIN gmb_event_types t7 ON t3.gmb_question_types_id = t7.gmb_question_types_id
$where
GROUP BY
t1.id,t5.id
) tab
GROUP BY
tab.id";
$stmt_types = $this->registry->db->prepare($sql_types);
$stmt_types->execute();
$types = $stmt_types->fetchAll(PDO::FETCH_ASSOC);
foreach ($types as $k => $vk) {
$types[$k]['name'] = $header[$j]['name'];
$types[$k]['type'] = $header[$j]['type'];
$header[$j]['total_score'] += $vk['total_score'];
$header[$j]['total_max_score'] += $vk['total_max_score'];
$total_all_max_score += $vk['total_max_score'];
array_push($category_type, $types[$k]);
}
}
}
$gmb_event_sections[$i]['category_type'] = $category_type;
}
foreach ($header as $key => $value) {
$header[$key]['percentage_max_score'] = ROUND((($value['total_max_score'] / $total_all_max_score) * 100), 2);
$header[$key]['percentage_score'] = ROUND((($value['total_score'] / $total_all_max_score) * 100), 2);
$header[$key]['total_score_item'] = ROUND((($value['total_score'] / $value['total_max_score']) * 100), 2);
}
$score_event = array_sum(array_column($gmb_event_sections, 'score'));
$all_weight = array_sum(array_column($gmb_event_sections, 'weight'));
$rData['score_event'] = ($score_event / $all_weight) * 100;
$rData['event'] = $event;
$rData['total_all_max_score'] = $total_all_max_score;
$rData['header'] = $header;
$rData['gmb_event_sections'] = $gmb_event_sections;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', Line: ' . $e->getLine() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', Line: ' . $e->getLine() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function sendMail($params, $mail)
{
try {
$profiles = new Profiles($this->registry);
$empReq = $profiles->getEmailInfo(\Helper::getSessionVar('username'));
$empReqto = $profiles->getEmailInfo($params->empno);
$maildata['address'] = $empReqto[0]['mail_address'];
$maildata['addressname'] = $empReqto[0]['full_name'];
$maildata['empreqname'] = $empReq[0]['full_name'];
$maildata['empreqnik'] = \Helper::getSessionVar('username');
$maildata['attachmentlocation'] = '';
$maildata['attachmentfilename'] = "";
$maildata['subject'] = $params->subject;
// $link=$this->registry->config->server_address . 'gembareport/'. $fileName;
$maildata['link'] = '';
$maildata['message'] = "
Dear " . $empReqto[0]['full_name'] . ",
" . $params->content . "
Regards,
HCPORTAL SYSTEM
Email is sent automatically by the hcportal system [" . \Helper::ubah_tgl_no_space(date('Y-m-d')) . "]
Please do not reply!
";
return $mail->sendMail($maildata);
} catch (PDOException $e) {
$this->registry->log->error('Message: ' . $e->getMessage() . ' Line: ' . $e->getLine() . ' File: ' . $e->getFile() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('Message: ' . $e->getMessage() . ' Line: ' . $e->getLine() . ' File: ' . $e->getFile() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataIssues($attributes, $mail, $isUpdate = false)
{
$this->registry->db->beginTransaction();
try {
$nik = \Helper::getSessionVar('username');
$message = "PAYROLL.MESSAGE.FAILMESERRREQ";
$status = "PAYROLL.MESSAGE.FAILED";
$response = $this->sendResponse([], $message, $status);
$stmtIns = $this->setStatementInsStatus();
$stmtUpd = $this->setStatementUpdStatus();
$attributes->due_date = \Helper::utcToIso($attributes->due_date);
$dataExist = $this->checkDataIfExist($attributes);
if ($dataExist) {
$stmt = $stmtUpd;
} else {
$stmt = $stmtIns;
}
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
$result = $this->processData($stmt, $attributes, $nik);
if ($result) {
if (isset($attributes->empno) && !empty($attributes->empno)) {
$attributes->subject = "GENBA - PIC assignment";
$attributes->content = "Dengan berlangsungnya kegiatan GENBA, kami menugaskan bapak/ibu untuk menjadi PIC dalam menghandle issue assessment tentng " . $attributes->issues . ".";
$setMail = $this->sendMail($attributes, $mail);
}
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
$response = $this->sendResponse([], $message, $status);
$this->registry->db->commit();
}
return $response;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Message: ' . $e->getMessage() . ' Line: ' . $e->getLine() . ' File: ' . $e->getFile() . ', user: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Message: ' . $e->getMessage() . ' Line: ' . $e->getLine() . ' File: ' . $e->getFile() . ', user: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
private function processData($stmt, $attributes, $nik)
{
try {
$stmt->bindValue(':gmb_asessor_result_id', $attributes->gmb_asessor_result_id, PDO::PARAM_STR);
$stmt->bindValue(':note', $attributes->note, PDO::PARAM_STR);
$stmt->bindValue(':pic', $attributes->empno, PDO::PARAM_STR);
$stmt->bindValue(':risk_level', $attributes->risk_level, PDO::PARAM_STR);
$stmt->bindValue(':status', $attributes->status, PDO::PARAM_STR);
$stmt->execute();
return true;
} catch (PDOexception $e) {
$this->registry
->log
->error('Message :'
. $e->getMessage() . ', Line: '
. $e->getLine() . ', File: '
. $e->getFile() . ', User: '
. $nik);
return false;
} catch (ErrorException $e) {
$this->registry
->log
->error('Message :'
. $e->getMessage() . ', Line: '
. $e->getLine() . ', File: '
. $e->getFile() . ', User: '
. $nik);
return false;
}
}
public function checkDataIfExist($attributes)
{
try {
$stmt = $this->setStatementGetStatus();
$stmt->bindValue(':id', $attributes->gmb_asessor_result_id, PDO::PARAM_STR);
$stmt->execute();
return $stmt->rowCount() > 0 ? true : false;
} catch (PDOException $e) {
$this->registry->log->error('message: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('message: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getResponsCount($stmt, $id)
{
try {
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
return count($rs) > 0 ? $rs[0]['total'] : 0;
} catch (PDOException $e) {
$this->registry->log->error('message: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('message: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function updateGeneralIssues($attributes)
{
$this->registry->db->beginTransaction();
try {
$nik = \Helper::getSessionVar('username');
$message = "PAYROLL.MESSAGE.FAILMESERRREQ";
$status = "PAYROLL.MESSAGE.FAILED";
$response = $this->sendResponse([], $message, $status);
$stmt = $this->setStatementUpdDueDate();
$attributes->due_date = \Helper::utcToIso($attributes->due_date);
$stmt->bindValue(':id', $attributes->id, PDO::PARAM_STR);
// $stmt->bindValue(':due_date', $attributes->due_date, PDO::PARAM_STR);
$stmt->bindValue(':pic', $attributes->empno, PDO::PARAM_STR);
$stmt->execute();
if ($stmt) {
if (isset($attributes->empno) && !empty($attributes->empno)) {
$attributes->subject = "GENBA - PIC assignment";
$attributes->content = "Dengan berlangsungnya kegiatan GENBA, kami menugaskan bapak/ibu untuk menjadi PIC dalam menghandle issue general.";
// $setMail = $this->sendMail($attributes, $mail);
}
$message = "PAYROLL.MESSAGE.SUCCMESUPD";
$status = "PAYROLL.MESSAGE.SUCCESS";
$response = $this->sendResponse([], $message, $status);
$this->registry->db->commit();
}
return $response;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Message: ' . $e->getMessage() . ' Line: ' . $e->getLine() . ' File: ' . $e->getFile() . ', user: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Message: ' . $e->getMessage() . ' Line: ' . $e->getLine() . ' File: ' . $e->getFile() . ', user: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function updateReportName($params, $file_name)
{
try {
$this->registry->db->beginTransaction();
if (!$params['assesor_id']) {
$event_update = $this->dbBuilderUpdate('gmb_events', [
['key' => 'file_report', 'val' => $file_name, 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $params['event_id'], 'bind' => PDO::PARAM_INT],
]);
if (!$event_update) {
return false;
}
} else {
$event_update = $this->dbBuilderUpdate('gmb_event_asessor', [
['key' => 'file_report', 'val' => $file_name, 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $params['assesor_id'], 'bind' => PDO::PARAM_INT],
]);
if (!$event_update) {
return false;
}
}
$this->registry->db->commit();
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-updateReportName: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-updateReportName: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function sendReport($params, $mail)
{
try {
$fileName = '';
if (!$params['assesor_id']) {
$rs = $this->dbBuilderSelectJoin(
'gmb_events t1',
[
't1.file_report'
],
[
['field' => 'id', 'key' => 't1.id', 'val' => $params['event_id'], 'bind' => PDO::PARAM_STR]
]
);
if (count($rs) > 0) {
$fileName = $rs[0]['file_report'];
}
} else {
$rs = $this->dbBuilderSelectJoin(
'gmb_event_asessor t1',
[
't1.file_report'
],
[
['field' => 'id', 'key' => 't1.id', 'val' => $params['assesor_id'], 'bind' => PDO::PARAM_STR]
]
);
if (count($rs) > 0) {
$fileName = $rs[0]['file_report'];
}
}
$mail_list = explode(';', $params['mail']);
$address = $mail_list[0];
if (count($mail_list) > 0) {
array_splice($mail_list, 0, 1);
}
$address_cc = $mail_list;
$maildata['address'] = $address;
$maildata['address_cc_mail'] = $address_cc;
$maildata['addressname'] = "";
$maildata['empreqname'] = "";
$maildata['attachmentlocation'] = '';
$maildata['attachmentfilename'] = $fileName;
$maildata['subject'] = "Genba Result Report";
$link = $this->registry->config->base_link_access_doc . '/gembareport/' . $fileName;
$maildata['link'] = '';
$rsmail = $mail->sendMail($maildata);
// var_dump($rsmail);die();
if (!$rsmail) {
throw new Exception("3");
}
// }
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
$response = $this->sendResponse([], $message, $status);
return $response;
} catch (PDOException $e) {
$this->registry->log->error('module Admin / sendReport:' . $e->getMessage() . 'line :' . $e->getLine());
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module Admin / sendReport:' . $e->getMessage() . 'line :' . $e->getLine());
return false;
}
}
public function getDataQuestionGroup()
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmb_question_group t1',
[
't1.*',
'0 as edit'
],
[]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataManufatureProduct()
{
try {
$rs = $this->dbBuilderSelectJoin(
'ref_manufacture_products t1
INNER JOIN ref_manunfactures t2 ON t1.ref_manunfactures_id = t2.id
INNER JOIN ref_master_category_product_plant t3 ON t1.ref_master_category_product_plant_id = t3.id',
[
't1.*',
't2.plan_name',
't3.category_name'
],
[]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function setMarkAsRead($attributes)
{
$this->registry->db->beginTransaction();
try {
$nik = \Helper::getSessionVar('username');
$message = "PAYROLL.MESSAGE.FAILMESERRREQ";
$status = "PAYROLL.MESSAGE.FAILED";
$response = $this->sendResponse([], $message, $status);
$stmtCheckGenIssue = $this->setStatementCheckGenIssueRead();
$stmtInsGenIssue = $this->setStatementInsGenIssueRead();
$stmtCheckIssue = $this->setStatementCheckIssueRead();
$stmtInsIssue = $this->setStatementInsIssueRead();
$stmtCheck = $stmtCheckIssue;
$stmtIns = $stmtInsIssue;
if ($attributes['source'] == 'general') {
$stmtCheck = $stmtCheckGenIssue;
$stmtIns = $stmtInsGenIssue;
}
$check = $this->processByIdAndNik($stmtCheck, $attributes['id'], $nik);
if ($check->rowCount() == 0) {
$rs = $this->processByIdAndNik($stmtIns, $attributes['id'], $nik);
if ($rs) {
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
}
}
$response = $this->sendResponse([], $message, $status);
$this->registry->db->commit();
return [$response];
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Message: ' . $e->getMessage() . ' Line: ' . $e->getLine() . ' File: ' . $e->getFile() . ', user: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Message: ' . $e->getMessage() . ' Line: ' . $e->getLine() . ' File: ' . $e->getFile() . ', user: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function processByIdAndNik($stmt, $id, $nik)
{
try {
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->bindValue(':nik', $nik, PDO::PARAM_STR);
$stmt->execute();
return $stmt;
} catch (PDOException $e) {
$this->registry->log->error('Message: ' . $e->getMessage() . ' Line: ' . $e->getLine() . ' File: ' . $e->getFile() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('Message: ' . $e->getMessage() . ' Line: ' . $e->getLine() . ' File: ' . $e->getFile() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function viewDataMasterQuestionCategory($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmb_question_category t1',
[
't1.*'
],
[
['field' => 'category_name', 'key' => 't1.category_name', 'val' => $params['category_name'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmb_question_category t1',
[
'count(t1.id) as total',
],
[]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterQuestionCategory($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmb_question_category',
[
['key' => 'category_name', 'val' => $data->category_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmb_question_category',
[
['key' => 'category_name', 'val' => $data->category_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionCategory: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionCategory: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteQuestionCategory($id)
{
try {
$this->registry->db->beginTransaction();
$this->dbBuilderDelete('gmb_question_category', 'id', $id);
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionCategory: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionCategory: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterQuestionGroup($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmb_question_group t1',
[
't1.*'
],
[
['field' => 'group_name', 'key' => 't1.group_name', 'val' => $params['group_name'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmb_question_group t1',
[
'count(t1.id) as total',
],
[]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterQuestionGroup($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmb_question_group',
[
['key' => 'group_name', 'val' => $data->group_name, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmb_question_group',
[
['key' => 'group_name', 'val' => $data->group_name, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionGroup: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionGroup: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteQuestionGroup($id)
{
try {
$this->registry->db->beginTransaction();
$this->dbBuilderDelete('gmb_question_group', 'id', $id);
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionGroup: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionGroup: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterQuestionSection($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmb_question_section t1
INNER JOIN ref_manufacture_products t2 ON t1.ref_manufacture_products_id = t2.id
INNER JOIN ref_manunfactures t3 ON t2.ref_manunfactures_id = t3.id
INNER JOIN ref_master_category_product_plant t4 ON t2.ref_master_category_product_plant_id = t4.id',
[
't1.*',
't3.plan_name',
't4.category_name'
],
[
['field' => 'ref_manufacture_products_id', 'key' => 't1.ref_manufacture_products_id', 'val' => $params['ref_manufacture_products_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( '],
['field' => 'ref_manufacture_products_id', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'end_symbol' => ' ) ', 'exp' => ' OR '],
['field' => 'section_name', 'key' => 't1.section_name', 'val' => $params['section_name'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE', 'start_symbol' => ' ( '],
['field' => 'section_code', 'key' => 't1.section_code', 'val' => $params['section_code'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE', 'end_symbol' => ' ) '],
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmb_question_section t1',
[
'count(t1.id) as total',
],
[]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterQuestionSection($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmb_question_section',
[
['key' => 'section_name', 'val' => $data->section_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
['key' => 'section_code', 'val' => $data->section_code, 'bind' => PDO::PARAM_STR],
['key' => 'ref_manufacture_products_id', 'val' => $data->ref_manufacture_products_id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmb_question_section',
[
['key' => 'section_name', 'val' => $data->section_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
['key' => 'section_code', 'val' => $data->section_code, 'bind' => PDO::PARAM_STR],
['key' => 'ref_manufacture_products_id', 'val' => $data->ref_manufacture_products_id, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionSection: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionSection: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteQuestionSection($id)
{
try {
$this->registry->db->beginTransaction();
$this->dbBuilderDelete('gmb_question_section', 'id', $id);
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionSection: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionSection: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterQuestionType($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmb_question_types t1',
[
't1.*'
],
[
['field' => 'type_name', 'key' => 't1.type_name', 'val' => $params['type_name'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
['field' => 'code_type', 'key' => 't1.code_type', 'val' => $params['code_type'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmb_question_types t1',
[
'count(t1.id) as total',
],
[]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterQuestionType($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmb_question_types',
[
['key' => 'type_name', 'val' => $data->type_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
['key' => 'code_type', 'val' => $data->code_type, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmb_question_types',
[
['key' => 'type_name', 'val' => $data->type_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
['key' => 'code_type', 'val' => $data->code_type, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionType: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionType: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteQuestionType($id)
{
try {
$this->registry->db->beginTransaction();
$this->dbBuilderDelete('gmb_question_types', 'id', $id);
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionType: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionType: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterQuestion($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmb_questions t1
INNER JOIN gmb_question_group t2 ON t1.gmb_question_group_id = t2.id
INNER JOIN gmb_question_types t3 ON t1.gmb_question_types_id = t3.id
INNER JOIN gmb_question_category t4 ON t1.gmb_question_category_id = t4.id
INNER JOIN gmb_question_section t5 ON t1.gmb_question_section_id = t5.id
INNER JOIN ref_manufacture_products t6 ON t5.ref_manufacture_products_id = t6.id
INNER JOIN ref_manunfactures t7 ON t6.ref_manunfactures_id = t7.id
INNER JOIN ref_master_category_product_plant t8 ON t6.ref_master_category_product_plant_id = t8.id',
[
't1.*',
't2.group_name',
't3.type_name',
't4.category_name',
't5.section_name',
't7.plan_name',
't8.category_name as category_product_name'
],
[
['field' => 'question_code', 'key' => 't1.question_code', 'val' => $params['question_code'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
['field' => 'question', 'key' => 't1.question', 'val' => $params['question'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
['field' => 'question_description', 'key' => 't1.question_description', 'val' => $params['question_description'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
['field' => 'is_need_upload_file', 'key' => 't1.is_need_upload_file', 'val' => $params['is_need_upload_file'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( '],
['field' => 'is_need_upload_file', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'end_symbol' => ' ) ', 'exp' => ' OR '],
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => $params['is_publish'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( '],
['field' => 'is_publish', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'end_symbol' => ' ) ', 'exp' => ' OR '],
['field' => 'gmb_question_types_id', 'key' => 't1.gmb_question_types_id', 'val' => $params['gmb_question_types_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( '],
['field' => 'gmb_question_types_id', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'end_symbol' => ' ) ', 'exp' => ' OR '],
['field' => 'gmb_question_group_id', 'key' => 't1.gmb_question_group_id', 'val' => $params['gmb_question_group_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( '],
['field' => 'gmb_question_group_id', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'end_symbol' => ' ) ', 'exp' => ' OR '],
['field' => 'gmb_question_category_id', 'key' => 't1.gmb_question_category_id', 'val' => $params['gmb_question_category_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( '],
['field' => 'gmb_question_category_id', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'end_symbol' => ' ) ', 'exp' => ' OR '],
['field' => 'gmb_question_section_id', 'key' => 't1.gmb_question_section_id', 'val' => $params['gmb_question_section_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( '],
['field' => 'gmb_question_section_id', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'end_symbol' => ' ) ', 'exp' => ' OR '],
['field' => 'ref_manufacture_products_id', 'key' => 't5.ref_manufacture_products_id', 'val' => $params['ref_manufacture_products_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( '],
['field' => 'ref_manufacture_products_id', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'end_symbol' => ' ) ', 'exp' => ' OR '],
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmb_questions t1',
[
'count(t1.id) as total',
],
[]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterQuestion($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmb_questions',
[
['key' => 'section_name', 'val' => $data->section_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
['key' => 'section_code', 'val' => $data->section_code, 'bind' => PDO::PARAM_STR],
['key' => 'ref_manufacture_products_id', 'val' => $data->ref_manufacture_products_id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmb_questions',
[
['key' => 'section_name', 'val' => $data->section_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
['key' => 'section_code', 'val' => $data->section_code, 'bind' => PDO::PARAM_STR],
['key' => 'ref_manufacture_products_id', 'val' => $data->ref_manufacture_products_id, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestion: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestion: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteQuestion($id)
{
try {
$this->registry->db->beginTransaction();
$this->dbBuilderDelete('gmb_questions', 'id', $id);
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestion: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestion: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function getDataCompany()
{
try {
$rs = $this->dbBuilderSelectJoin(
'company t1',
[
't1.*'
],
[]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataSubArea($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'sub_area t1',
[
't1.*'
],
[
['field' => 'company_id', 'key' => 't1.company_id', 'val' => $params['company_id'], 'bind' => PDO::PARAM_STR],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataSubAreaRefManufacture($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'sub_area t1
INNER JOIN ref_manufacture_sub_area t2 ON t1.id=t2.sub_area_id',
[
't1.*'
],
[
['field' => 'ref_manunfactures_id', 'key' => 't2.ref_manunfactures_id', 'val' => $params['ref_manunfactures_id'], 'bind' => PDO::PARAM_STR],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataGedung($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gedung t1',
[
't1.*'
],
[
['field' => 'sub_area_id', 'key' => 't1.sub_area_id', 'val' => $params['sub_area_id'], 'bind' => PDO::PARAM_STR],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataRefShiftGroup($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'ref_shift_group t1',
[
't1.*'
],
[
['field' => 'sub_area_id', 'key' => 't1.sub_area_id', 'val' => $params['sub_area_id'], 'bind' => PDO::PARAM_STR],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataSector($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gedung_sector t1',
[
't1.*'
],
[
['field' => 'gedung_id', 'key' => 't1.gedung_id', 'val' => $params['gedung_id'], 'bind' => PDO::PARAM_STR],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataLine($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gedung_sector_line t1',
[
't1.*'
],
[
['field' => 'gedung_sector_id', 'key' => 't1.gedung_sector_id', 'val' => $params['sector_id'], 'bind' => PDO::PARAM_STR],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataDivision($params)
{
try {
if (isset($params['company_id'])) {
$company = $params['company_id'];
} else {
$company = \Helper::getSessionVar('empSite');
}
$rs = $this->dbBuilderSelectJoin(
'division t1
INNER JOIN `division_department` t2 ON t1.`id` = t2.`division_id`',
[
'DISTINCT(t1.`id`)',
't1.`description`'
],
[
['field' => 'bu', 'key' => 't2.bu', 'val' => $company, 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataDepartment($params)
{
try {
$where = [];
if (isset($params['company_id'])) {
$company = $params['company_id'];
} else {
$company = \Helper::getSessionVar('empSite');
}
array_push($where, ['field' => 'bu', 'key' => 't1.bu', 'val' => $company, 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
if (isset($params['division_id'])) {
array_push($where, ['field' => 'division_id', 'key' => 't1.division_id', 'val' => $params['division_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
$rs = $this->dbBuilderSelectJoin(
'`division_department` t1 INNER JOIN
`department` t2 ON t1.`department_id` = t2.`id`',
[
't2.id',
't2.description'
],
$where
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataBusinessUnit($params)
{
try {
$where = [];
array_push($where, ['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => 1, 'bind' => PDO::PARAM_STR]);
if (isset($params['line_id'])) {
array_push($where, ['field' => 'gedung_sector_line_id', 'key' => 't1.gedung_sector_line_id', 'val' => $params['line_id'], 'bind' => PDO::PARAM_STR]);
}
$rs = $this->dbBuilderSelectJoin(
'gmbs_business_unit t1
INNER JOIN gedung_sector_line t2 ON t1.gedung_sector_line_id = t2.id
INNER JOIN gedung_sector t3 ON t2.gedung_sector_id = t3.id
INNER JOIN gedung t4 ON t3.gedung_id = t4.id',
[
't1.*',
't2.line_number',
't3.sector_name',
't4.gedung_name'
],
$where
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataSection($params)
{
try {
$where = [];
array_push($where, ['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => 1, 'bind' => PDO::PARAM_STR]);
if (isset($params['gmbs_business_unit_id'])) {
array_push($where, ['field' => 'gmbs_business_unit_id', 'key' => 't1.gmbs_business_unit_id', 'val' => $params['gmbs_business_unit_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if (isset($params['department_id'])) {
array_push($where, ['field' => 'department_id', 'key' => 't1.department_id', 'val' => $params['department_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
$rs = $this->dbBuilderSelectJoin(
'gmbs_question_section t1
INNER JOIN gmbs_business_unit t2 ON t1.gmbs_business_unit_id = t2.id
INNER JOIN gedung_sector_line t3 ON t2.gedung_sector_line_id = t3.id
INNER JOIN gedung_sector t4 ON t3.gedung_sector_id = t4.id
INNER JOIN gedung t5 ON t4.gedung_id = t5.id
INNER JOIN sub_area t6 ON t5.sub_area_id = t6.id
INNER JOIN company t7 ON t6.company_id = t7.id
INNER JOIN department t8 ON t1.department_id = t8.id',
[
't1.*',
't2.bu_name',
't7.description as company_name',
't6.description as sub_area_name',
't5.gedung_name as gedung_name',
't4.sector_name as sector_name',
't3.line_number as line_name',
't8.description as department_name'
],
$where
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataCategory()
{
try {
$where = [];
array_push($where, ['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => 1, 'bind' => PDO::PARAM_STR]);
$rs = $this->dbBuilderSelectJoin(
'gmbs_question_category t1',
[
't1.*'
],
$where
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataRiskLevel()
{
try {
$where = [];
array_push($where, ['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => 1, 'bind' => PDO::PARAM_STR]);
$rs = $this->dbBuilderSelectJoin(
'gmbs_ref_risk_level t1',
[
't1.*'
],
$where
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataWorkScopes()
{
try {
$where = [];
array_push($where, ['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => 1, 'bind' => PDO::PARAM_STR]);
$rs = $this->dbBuilderSelectJoin(
'gmbs_ref_work_scopes t1',
[
't1.*'
],
$where
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterRefBusinessUnit($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmbs_ref_business_unit',
[
['key' => 'bu_name', 'val' => $data->bu_name, 'bind' => PDO::PARAM_STR],
['key' => 'description', 'val' => $data->description, 'bind' => PDO::PARAM_STR],
['key' => 'ref_manunfactures_id', 'val' => $data->ref_manunfactures_id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmbs_ref_business_unit',
[
['key' => 'bu_name', 'val' => $data->bu_name, 'bind' => PDO::PARAM_STR],
['key' => 'description', 'val' => $data->description, 'bind' => PDO::PARAM_STR],
['key' => 'ref_manunfactures_id', 'val' => $data->ref_manunfactures_id, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterRefBusinessUnit: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterRefBusinessUnit: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterRefBusinessUnit($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmbs_ref_business_unit t1
INNER JOIN ref_manunfactures t2 ON t1.ref_manunfactures_id = t2.id ',
[
't1.id',
't1.bu_name',
't1.description',
't1.ref_manunfactures_id as ref_manunfactures_id',
't2.plan_name'
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR],
['field' => 'bu_name', 'key' => 't1.bu_name', 'val' => $params['bu_name'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmbs_ref_business_unit t1
INNER JOIN ref_manunfactures t2 ON t1.ref_manunfactures_id = t2.id ',
[
'count(t1.id) as total',
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR],
]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterBusinessUnit($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
$gmbs_ref_business_unit = $this->dbBuilderSelectJoin(
'gmbs_ref_business_unit t1',
[
't1.*'
],
[
['field' => 'id', 'key' => 't1.id', 'val' => $data->gmbs_ref_business_unit_id, 'bind' => PDO::PARAM_STR],
]
);
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmbs_business_unit',
[
['key' => 'bu_name', 'val' => $gmbs_ref_business_unit[0]['bu_name'], 'bind' => PDO::PARAM_STR],
['key' => 'description', 'val' => $gmbs_ref_business_unit[0]['description'], 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_ref_business_unit_id', 'val' => $data->gmbs_ref_business_unit_id, 'bind' => PDO::PARAM_STR],
['key' => 'gedung_sector_line_id', 'val' => $data->line_id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmbs_business_unit',
[
['key' => 'bu_name', 'val' => $gmbs_ref_business_unit[0]['bu_name'], 'bind' => PDO::PARAM_STR],
['key' => 'description', 'val' => $gmbs_ref_business_unit[0]['description'], 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_ref_business_unit_id', 'val' => $data->gmbs_ref_business_unit_id, 'bind' => PDO::PARAM_STR],
['key' => 'gedung_sector_line_id', 'val' => $data->line_id, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
$message = "Terjadi duplikasi data dalam insert / update data";
$status = "PAYROLL.MESSAGE.FAILED";
$this->sendResponse([], $message, $status);
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterBusinessUnit: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterBusinessUnit: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterBusinessUnit($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmbs_business_unit t1
INNER JOIN gedung_sector_line t2 ON t1.gedung_sector_line_id = t2.id
INNER JOIN gedung_sector t3 ON t2.gedung_sector_id = t3.id
INNER JOIN gedung t4 ON t3.gedung_id = t4.id
INNER JOIN sub_area t5 ON t4.sub_area_id = t5.id
INNER JOIN company t6 ON t5.company_id = t6.id
INNER JOIN gmbs_ref_business_unit t7 ON t1.gmbs_ref_business_unit_id = t7.id',
[
't1.id',
't1.bu_name',
't1.description',
't1.gedung_sector_line_id as line_id',
't2.gedung_sector_id as sector_id',
't3.gedung_id',
't4.sub_area_id',
't5.company_id',
't6.description as company_name',
't5.description as sub_area_name',
't4.gedung_name as gedung_name',
't3.sector_name as sector_name',
't2.line_number as line_name',
't1.gmbs_ref_business_unit_id as gmbs_ref_business_unit_id',
't7.ref_manunfactures_id as ref_manunfactures_id'
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR],
['field' => 'bu_name', 'key' => 't1.bu_name', 'val' => $params['bu_name'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
['field' => 'bu_name', 'key' => 't1.bu_name', 'val' => $params['search'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmbs_business_unit t1
INNER JOIN gedung_sector_line t2 ON t1.gedung_sector_line_id = t2.id
INNER JOIN gedung_sector t3 ON t2.gedung_sector_id = t3.id
INNER JOIN gedung t4 ON t3.gedung_id = t4.id
INNER JOIN sub_area t5 ON t4.sub_area_id = t5.id
INNER JOIN company t6 ON t5.company_id = t6.id',
[
'count(t1.id) as total',
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR],
['field' => 'bu_name', 'key' => 't1.bu_name', 'val' => $params['bu_name'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
['field' => 'bu_name', 'key' => 't1.bu_name', 'val' => $params['search'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterQuestionCategorySelf($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmbs_question_category',
[
['key' => 'category_name', 'val' => $data->category_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmbs_question_category',
[
['key' => 'category_name', 'val' => $data->category_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionCategorySelf: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionCategorySelf: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterQuestionCategorySelf($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmbs_question_category t1',
[
't1.*'
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR],
['field' => 'category_name', 'key' => 't1.category_name', 'val' => $params['category_name'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE'],
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmbs_question_category t1',
[
'count(t1.id) as total',
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR]
]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterQuestionSectionSelf($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
foreach ($data->section_list as $key => $value) {
$master = $this->dbBuilderInsert(
'gmbs_question_section',
[
['key' => 'section_name', 'val' => $value->section_name, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_business_unit_id', 'val' => $data->gmbs_business_unit_id, 'bind' => PDO::PARAM_STR],
['key' => 'department_id', 'val' => $data->department_id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
}
} else {
foreach ($data->section_list as $key => $value) {
$master_update = $this->dbBuilderUpdate(
'gmbs_question_section',
[
['key' => 'section_name', 'val' => $value->section_name, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_business_unit_id', 'val' => $data->gmbs_business_unit_id, 'bind' => PDO::PARAM_STR],
['key' => 'department_id', 'val' => $data->department_id, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionSectionSelf: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterQuestionSectionSelf: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterQuestionSectionSelf($params)
{
try {
$where = [];
array_push($where, ['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR]);
array_push($where, ['field' => 'company_id', 'key' => 't6.company_id', 'val' => $params['company_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'company_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'gmbs_business_unit_id', 'key' => 't1.gmbs_business_unit_id', 'val' => $params['gmbs_business_unit_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'gmbs_business_unit_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'line_id', 'key' => 't2.gedung_sector_line_id', 'val' => $params['line_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'line_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'sector_id', 'key' => 't3.gedung_sector_id', 'val' => $params['sector_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'sector_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'gedung_id', 'key' => 't4.gedung_id', 'val' => $params['gedung_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'gedung_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'sub_area_id', 'key' => 't5.sub_area_id', 'val' => $params['sub_area_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'sub_area_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'department_id', 'key' => 't1.department_id', 'val' => $params['department_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'department_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
if ($params['section_name']) {
array_push($where, ['field' => 'section_name', 'key' => 't1.section_name', 'val' => $params['section_name'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
$rs = $this->dbBuilderSelectJoin(
'gmbs_question_section t1
INNER JOIN gmbs_business_unit t2 ON t1.gmbs_business_unit_id = t2.id
INNER JOIN gedung_sector_line t3 ON t2.gedung_sector_line_id = t3.id
INNER JOIN gedung_sector t4 ON t3.gedung_sector_id = t4.id
INNER JOIN gedung t5 ON t4.gedung_id = t5.id
INNER JOIN sub_area t6 ON t5.sub_area_id = t6.id
INNER JOIN company t7 ON t6.company_id = t7.id
INNER JOIN department t8 ON t1.department_id = t8.id',
[
't1.id',
't1.section_name',
't1.gmbs_business_unit_id',
't1.department_id',
't2.bu_name',
't2.gedung_sector_line_id as line_id',
't3.gedung_sector_id as sector_id',
't4.gedung_id',
't5.sub_area_id',
't6.company_id',
't7.description as company_name',
't6.description as sub_area_name',
't5.gedung_name as gedung_name',
't4.sector_name as sector_name',
't3.line_number as line_name',
't8.description as department_name'
],
$where,
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmbs_question_section t1
INNER JOIN gmbs_business_unit t2 ON t1.gmbs_business_unit_id = t2.id
INNER JOIN gedung_sector_line t3 ON t2.gedung_sector_line_id = t3.id
INNER JOIN gedung_sector t4 ON t3.gedung_sector_id = t4.id
INNER JOIN gedung t5 ON t4.gedung_id = t5.id
INNER JOIN sub_area t6 ON t5.sub_area_id = t6.id
INNER JOIN company t7 ON t6.company_id = t7.id
INNER JOIN department t8 ON t1.department_id = t8.id',
[
'count(t1.id) as total',
],
$where
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterRiskLevel($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmbs_ref_risk_level',
[
['key' => 'risk_level_name', 'val' => $data->risk_level_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmbs_ref_risk_level',
[
['key' => 'risk_level_name', 'val' => $data->risk_level_name, 'bind' => PDO::PARAM_STR],
['key' => 'weight', 'val' => $data->weight, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterRiskLevel: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterRiskLevel: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterRiskLevel($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmbs_ref_risk_level t1',
[
't1.*',
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR]
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmbs_ref_risk_level t1',
[
'count(t1.id) as total',
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR]
]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterWorkScopes($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmbs_ref_work_scopes',
[
['key' => 'scope_name', 'val' => $data->scope_name, 'bind' => PDO::PARAM_STR],
['key' => 'description', 'val' => $data->description, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmbs_ref_work_scopes',
[
['key' => 'scope_name', 'val' => $data->scope_name, 'bind' => PDO::PARAM_STR],
['key' => 'description', 'val' => $data->description, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterWorkScopes: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterWorkScopes: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterWorkScopes($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmbs_ref_work_scopes t1',
[
't1.*',
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR]
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmbs_ref_work_scopes t1',
[
'count(t1.id) as total',
],
[
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR]
]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataMasterActivities($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
foreach ($data->activities as $key => $value) {
$master = $this->dbBuilderInsert(
'gmbs_activities',
[
['key' => 'order_no', 'val' => $value->order_no, 'bind' => PDO::PARAM_STR],
['key' => 'is_publish', 'val' => $value->is_publish, 'bind' => PDO::PARAM_STR],
['key' => 'description', 'val' => $value->description, 'bind' => PDO::PARAM_STR],
['key' => 'description', 'val' => $value->description, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_question_category_id', 'val' => $value->gmbs_question_category_id, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_question_section_id', 'val' => $data->gmbs_question_section_id, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_ref_risk_level_id', 'val' => $value->gmbs_ref_risk_level_id, 'bind' => PDO::PARAM_STR],
['key' => 'reference_link', 'val' => $value->reference_link, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
foreach ($value->answer_detail as $j => $vj) {
$detail = $this->dbBuilderInsert(
'gmbs_answer_detail',
[
['key' => 'answer_score', 'val' => $vj->answer_score, 'bind' => PDO::PARAM_STR],
['key' => 'answer_label', 'val' => $vj->answer_label, 'bind' => PDO::PARAM_STR],
['key' => 'order', 'val' => $vj->order, 'bind' => PDO::PARAM_STR],
['key' => 'qualitative_answer', 'val' => $vj->qualitative_answer, 'bind' => PDO::PARAM_STR],
['key' => 'is_require_note', 'val' => $vj->is_require_note, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_activities_id', 'val' => $master, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$detail) {
return false;
}
}
}
} else {
foreach ($data->activities as $key => $value) {
$master_update = $this->dbBuilderUpdate(
'gmbs_activities',
[
['key' => 'order_no', 'val' => $value->order_no, 'bind' => PDO::PARAM_STR],
['key' => 'is_publish', 'val' => $value->is_publish, 'bind' => PDO::PARAM_STR],
['key' => 'description', 'val' => $value->description, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_question_category_id', 'val' => $value->gmbs_question_category_id, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_question_section_id', 'val' => $data->gmbs_question_section_id, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_ref_risk_level_id', 'val' => $value->gmbs_ref_risk_level_id, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_ref_work_scopes_id', 'val' => $value->gmbs_ref_work_scopes_id, 'bind' => PDO::PARAM_STR],
['key' => 'reference_link', 'val' => $value->reference_link, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
$this->dbBuilderDelete('gmbs_answer_detail', 'gmbs_activities_id', $data->id);
foreach ($value->answer_detail as $j => $vj) {
$detail = $this->dbBuilderInsert(
'gmbs_answer_detail',
[
['key' => 'answer_score', 'val' => $vj->answer_score, 'bind' => PDO::PARAM_STR],
['key' => 'answer_label', 'val' => $vj->answer_label, 'bind' => PDO::PARAM_STR],
['key' => 'order', 'val' => $vj->order, 'bind' => PDO::PARAM_STR],
['key' => 'qualitative_answer', 'val' => $vj->qualitative_answer, 'bind' => PDO::PARAM_STR],
['key' => 'is_require_note', 'val' => $vj->is_require_note, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_activities_id', 'val' => $data->id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$detail) {
return false;
}
}
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterActivities: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataMasterActivities: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataMasterActivities($params)
{
try {
$where = [];
array_push($where, ['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => '1', 'bind' => PDO::PARAM_STR]);
array_push($where, ['field' => 'gmbs_question_category_id', 'key' => 't1.gmbs_question_category_id', 'val' => $params['gmbs_question_category_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'gmbs_question_category_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'gmbs_question_section_id', 'key' => 't1.gmbs_question_section_id', 'val' => $params['gmbs_question_section_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'gmbs_question_section_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'gmbs_ref_risk_level_id', 'key' => 't1.gmbs_ref_risk_level_id', 'val' => $params['gmbs_ref_risk_level_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'gmbs_ref_risk_level_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'gmbs_ref_work_scopes_id', 'key' => 't1.gmbs_ref_work_scopes_id', 'val' => $params['gmbs_ref_work_scopes_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'gmbs_ref_work_scopes_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'gmbs_business_unit_id', 'key' => 't3.gmbs_business_unit_id', 'val' => $params['gmbs_business_unit_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'gmbs_business_unit_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'line_id', 'key' => 't6.gedung_sector_line_id', 'val' => $params['line_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'line_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'sector_id', 'key' => 't7.gedung_sector_id', 'val' => $params['sector_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'sector_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'gedung_id', 'key' => 't8.gedung_id', 'val' => $params['gedung_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'gedung_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'sub_area_id', 'key' => 't9.sub_area_id', 'val' => $params['sub_area_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'sub_area_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
array_push($where, ['field' => 'department_id', 'key' => 't3.department_id', 'val' => $params['department_id'], 'bind' => PDO::PARAM_STR, 'start_symbol' => ' ( ']);
array_push($where, ['field' => 'department_id', 'key' => '', 'val' => 'all', 'bind' => PDO::PARAM_STR, 'exp' => ' OR ', 'end_symbol' => ' ) ']);
if ($params['description']) {
array_push($where, ['field' => 'description', 'key' => 't1.description', 'val' => $params['description'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['search']) {
array_push($where, ['field' => 'description', 'key' => 't1.description', 'val' => $params['search'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
$rs = $this->dbBuilderSelectJoin(
'gmbs_activities t1
INNER JOIN gmbs_question_category t2 ON t1.gmbs_question_category_id = t2.id
INNER JOIN gmbs_question_section t3 ON t1.gmbs_question_section_id = t3.id
INNER JOIN gmbs_ref_risk_level t4 ON t1.gmbs_ref_risk_level_id = t4.id
INNER JOIN gmbs_ref_work_scopes t5 ON t1.gmbs_ref_work_scopes_id = t5.id
INNER JOIN gmbs_business_unit t6 ON t3.gmbs_business_unit_id = t6.id
INNER JOIN gedung_sector_line t7 ON t6.gedung_sector_line_id = t7.id
INNER JOIN gedung_sector t8 ON t7.gedung_sector_id = t8.id
INNER JOIN gedung t9 ON t8.gedung_id = t9.id
INNER JOIN sub_area t10 ON t9.sub_area_id = t10.id
INNER JOIN ref_manufacture_sub_area t11 ON t11.sub_area_id = t10.id
INNER JOIN ref_manunfactures t12 ON t11.ref_manunfactures_id = t12.id
INNER JOIN department t13 ON t3.department_id = t13.id',
[
't1.id',
't1.order_no',
't1.is_publish',
't1.description',
't1.reference_link',
't1.gmbs_question_category_id',
't1.gmbs_question_section_id',
't1.gmbs_ref_risk_level_id',
't1.gmbs_ref_work_scopes_id',
't2.category_name',
't3.section_name',
't4.risk_level_name',
't5.scope_name',
't12.plan_name as ref_manufacture',
't10.description as sub_area',
't9.gedung_name as gedung',
't8.sector_name as sector',
't7.line_number as line',
't7.line_number as line',
't13.description as department',
't6.bu_name as bu_name',
],
$where,
"ORDER BY t3.section_name ASC, t1.order_no ASC LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmbs_activities t1
INNER JOIN gmbs_question_category t2 ON t1.gmbs_question_category_id = t2.id
INNER JOIN gmbs_question_section t3 ON t1.gmbs_question_section_id = t3.id
INNER JOIN gmbs_ref_risk_level t4 ON t1.gmbs_ref_risk_level_id = t4.id
INNER JOIN gmbs_ref_work_scopes t5 ON t1.gmbs_ref_work_scopes_id = t5.id
INNER JOIN gmbs_business_unit t6 ON t3.gmbs_business_unit_id = t6.id
INNER JOIN gedung_sector_line t7 ON t6.gedung_sector_line_id = t7.id
INNER JOIN gedung_sector t8 ON t7.gedung_sector_id = t8.id
INNER JOIN gedung t9 ON t8.gedung_id = t9.id
INNER JOIN sub_area t10 ON t9.sub_area_id = t10.id',
[
'count(t1.id) as total',
],
$where
);
foreach ($rs as $key => $value) {
$rs[$key]['answer_detail'] = $this->dbBuilderSelectJoin(
'gmbs_answer_detail t1',
[
't1.*',
],
[
['field' => 'gmbs_activities_id', 'key' => 't1.gmbs_activities_id', 'val' => $value['id']],
]
);
}
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataLineRunning($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
if (!isset($data->id)) {
$master = $this->dbBuilderInsert(
'gmbs_line_running',
[
['key' => 'running_date', 'val' => date('Y-m-d', strtotime($data->running_date)), 'bind' => PDO::PARAM_STR],
['key' => 'ref_shift_group_id', 'val' => $data->ref_shift_group_id, 'bind' => PDO::PARAM_STR],
['key' => 'gedung_sector_line_id', 'val' => $data->line_id, 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
} else {
$master_update = $this->dbBuilderUpdate(
'gmbs_line_running',
[
['key' => 'running_date', 'val' => date('Y-m-d', strtotime($data->running_date)), 'bind' => PDO::PARAM_STR],
['key' => 'ref_shift_group_id', 'val' => $data->ref_shift_group_id, 'bind' => PDO::PARAM_STR],
['key' => 'gedung_sector_line_id', 'val' => $data->line_id, 'bind' => PDO::PARAM_STR],
],
[
['key' => 'id', 'val' => $data->id, 'bind' => PDO::PARAM_INT],
]
);
if (!$master_update) {
return false;
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataLineRunning: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataLineRunning: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataLineRunning($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmbs_line_running t1
INNER JOIN gedung_sector_line t2 ON t1.gedung_sector_line_id = t2.id
INNER JOIN gedung_sector t3 ON t2.gedung_sector_id = t3.id
INNER JOIN gedung t4 ON t3.gedung_id = t4.id
INNER JOIN sub_area t5 ON t4.sub_area_id = t5.id
INNER JOIN company t6 ON t5.company_id = t6.id
INNER JOIN ref_shift_group t7 ON t1.ref_shift_group_id = t7.id',
[
't1.id',
't1.running_date',
't1.ref_shift_group_id',
't1.gedung_sector_line_id as line_id',
't2.gedung_sector_id as sector_id',
't3.gedung_id',
't4.sub_area_id',
't5.company_id',
't6.description as company_name',
't5.description as sub_area_name',
't4.gedung_name as gedung_name',
't3.sector_name as sector_name',
't2.line_number as line_name',
't7.shift_group_name as shift_group_name'
],
[
// ['field' => 'running_date','key' => 't1.running_date', 'val' => $params['running_date'], 'bind' => PDO::PARAM_STR,'opr'=>'LIKE'],
],
"ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmbs_line_running t1
INNER JOIN gedung_sector_line t2 ON t1.gedung_sector_line_id = t2.id
INNER JOIN gedung_sector t3 ON t2.gedung_sector_id = t3.id
INNER JOIN gedung t4 ON t3.gedung_id = t4.id
INNER JOIN sub_area t5 ON t4.sub_area_id = t5.id
INNER JOIN company t6 ON t5.company_id = t6.id',
[
'count(t1.id) as total',
],
[]
);
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function saveDataEventPic($data, $upload = false)
{
try {
if (!$upload) {
$this->registry->db->beginTransaction();
}
foreach ($data->pic as $key => $value) {
if (!$value->id) {
$master = $this->dbBuilderInsert(
'gmbs_event_pic',
[
['key' => 'pic_nik', 'val' => $value->nik, 'bind' => PDO::PARAM_STR],
['key' => 'ref_shift_group_id', 'val' => $data->ref_shift_group_id, 'bind' => PDO::PARAM_STR],
['key' => 'gmbs_department_group_shift_id', 'val' => $data->gmbs_department_group_shift_id, 'bind' => PDO::PARAM_STR],
['key' => 'action_by', 'val' => \Helper::getSessionVar('username'), 'bind' => PDO::PARAM_STR],
],
true
);
if (!$master) {
return false;
}
}
}
foreach ($data->delete as $key => $value) {
if (!$value->id) {
$this->dbBuilderDelete('gmbs_event_pic', 'id', $value->id);
}
}
if (!$upload) {
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESINS";
$status = "PAYROLL.MESSAGE.SUCCESS";
return $this->sendResponse([], $message, $status);
} else {
return true;
}
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataEventPic: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-saveDataEventPic: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function viewDataEventPic($params)
{
try {
$userCekAdmin = $this->getCekUserAdminSelf($params);
$sub_area_id = '';
$department_id = '';
$where = [];
if ($userCekAdmin) {
$sub_area_id = $userCekAdmin['sub_area_id'];
$department_id = $userCekAdmin['department_id'];
if ($userCekAdmin['is_admin'] == '0') {
array_push($where, ['field' => 'department_id', 'key' => 't9.department_id', 'val' => $department_id, 'bind' => PDO::PARAM_STR]);
array_push($where, ['field' => 'sub_area_id', 'key' => 't6.sub_area_id', 'val' => $sub_area_id, 'bind' => PDO::PARAM_STR]);
}
}
$rs = $this->dbBuilderSelectJoin(
'gmbs_events t1
INNER JOIN ref_manunfactures t2 ON t1.ref_manufactures_id = t2.id
LEFT JOIN gmbs_event_member_lines t3 ON t1.id = t3.gmbs_events_id
LEFT JOIN gedung_sector_line t4 ON t3.gedung_sector_line_id = t4.id
LEFT JOIN gedung_sector t5 ON t4.gedung_sector_id = t5.id
LEFT JOIN gedung t6 ON t5.gedung_id = t6.id
LEFT JOIN sub_area t7 ON t6.sub_area_id = t7.id
LEFT JOIN company t8 ON t7.company_id = t8.id
LEFT JOIN gmbs_event_member_line_departments t9 ON t9.gmbs_event_member_lines_id = t3.id
LEFT JOIN gmbs_department_group_shift t10 ON t10.gmbs_event_member_line_departments_id = t9.id
LEFT JOIN ref_shift_group t11 ON t10.ref_shift_group_id = t11.id
LEFT JOIN department t12 ON t9.department_id = t12.id',
[
't1.id',
't1.description',
't1.start_date',
't1.end_date',
't1.ref_manufactures_id',
't2.plan_name'
],
$where,
"GROUP BY t1.id ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}"
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmbs_events t1
INNER JOIN ref_manunfactures t2 ON t1.ref_manufactures_id = t2.id',
[
'count(t1.id) as total',
],
[]
);
foreach ($rs as $key => $value) {
$where_line = [];
array_push($where_line, ['field' => 'gmbs_events_id', 'key' => 't1.gmbs_events_id', 'val' => $value['id'], 'bind' => PDO::PARAM_STR]);
if ($userCekAdmin) {
$sub_area_id = $userCekAdmin['sub_area_id'];
$department_id = $userCekAdmin['department_id'];
if ($userCekAdmin['is_admin'] == '0') {
array_push($where_line, ['field' => 'department_id', 'key' => 't7.department_id', 'val' => $department_id, 'bind' => PDO::PARAM_STR]);
array_push($where_line, ['field' => 'sub_area_id', 'key' => 't6.sub_area_id', 'val' => $sub_area_id, 'bind' => PDO::PARAM_STR]);
}
}
$rs[$key]['member_line'] = $this->dbBuilderSelectJoin(
'gmbs_event_member_lines t1
INNER JOIN gedung_sector_line t2 ON t1.gedung_sector_line_id = t2.id
INNER JOIN gedung_sector t3 ON t2.gedung_sector_id = t3.id
INNER JOIN gedung t4 ON t3.gedung_id = t4.id
INNER JOIN sub_area t5 ON t4.sub_area_id = t5.id
INNER JOIN company t6 ON t5.company_id = t6.id
INNER JOIN gmbs_event_member_line_departments t7 ON t7.gmbs_event_member_lines_id = t1.id
INNER JOIN gmbs_department_group_shift t8 ON t8.gmbs_event_member_line_departments_id = t7.id
INNER JOIN ref_shift_group t9 ON t8.ref_shift_group_id = t9.id
INNER JOIN department t10 ON t7.department_id = t10.id ',
[
't1.*',
't2.gedung_sector_id as sector_id',
't3.gedung_id',
't4.sub_area_id',
't5.company_id',
't6.description as company_name',
't5.description as sub_area_name',
't4.gedung_name as gedung_name',
't3.sector_name as sector_name',
't2.line_number as line_name',
't9.shift_group_name',
't8.ref_shift_group_id',
't8.id as gmbs_department_group_shift_id',
't10.id as department_id',
't10.description as department_name',
'(SELECT COUNT(*) FROM gmbs_event_pic WHERE gmbs_department_group_shift_id=t8.id) as total_pic'
],
$where_line
);
}
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataPicEvent($params)
{
try {
$gmbs_event_pic = $this->dbBuilderSelectJoin(
'gmbs_event_pic t1',
[
't1.id',
't1.pic_nik as nik',
'getEmpName(t1.pic_nik) as emp_name',
],
[
['field' => 'gmbs_department_group_shift_id', 'key' => 't1.gmbs_department_group_shift_id', 'val' => $params['gmbs_department_group_shift_id'], 'bind' => PDO::PARAM_STR],
]
);
$rData['gmbs_event_pic'] = $gmbs_event_pic;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataEventGembaSelf($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmbs_events t1',
[
't1.*'
],
[]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function viewDataMonitoringGembaSelf($params)
{
try {
$where = [];
array_push($where, ['field' => 'qualitative_answer', 'key' => 't3.qualitative_answer', 'val' => 'Tidak Standar', 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
if ($params['event_id']) {
array_push($where, ['field' => 'gmbs_events_id', 'key' => 't8.gmbs_events_id', 'val' => $params['event_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['ref_manunfactures_id']) {
array_push($where, ['field' => 'ref_manufactures_id', 'key' => 't9.ref_manufactures_id', 'val' => $params['ref_manunfactures_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['sub_area_id']) {
array_push($where, ['field' => 'sub_area_id', 'key' => 't14.sub_area_id', 'val' => $params['sub_area_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['gedung_id']) {
array_push($where, ['field' => 'gedung_id', 'key' => 't13.gedung_id', 'val' => $params['gedung_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['sector_id']) {
array_push($where, ['field' => 'gedung_sector_id', 'key' => 't12.gedung_sector_id', 'val' => $params['sector_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['line_id']) {
array_push($where, ['field' => 'gedung_sector_line_id', 'key' => 't11.gedung_sector_line_id', 'val' => $params['line_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['date']) {
array_push($where, ['field' => 'action_date', 'key' => 't1.action_date', 'val' => date('Y-m-d', strtotime($params['date'])), 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['department_id'] && $params['department_id'] != '') {
array_push($where, ['field' => 'department_id', 'key' => 't6.department_id', 'val' => $params['department_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
$limit = '';
if (isset($params['offset'])) {
$limit = "ORDER BY t1.id LIMIT {$params['offset']}, {$params['jumPage']}";
}
$rs = $this->dbBuilderSelectJoin(
'gmbs_event_pic_activities t1
INNER JOIN gmbs_activities t2 ON t1.gmbs_activities_id = t2.id
INNER JOIN gmbs_answer_detail t3 ON t1.gmbs_answer_detail_id = t3.id
INNER JOIN gmbs_event_pic t4 ON t1.gmbs_event_pic_id = t4.id
INNER JOIN gmbs_department_group_shift t5 ON t4.gmbs_department_group_shift_id = t5.id
INNER JOIN gmbs_event_member_line_departments t6 ON t5.gmbs_event_member_line_departments_id = t6.id
INNER JOIN department t7 ON t6.department_id = t7.id
INNER JOIN gmbs_event_member_lines t8 ON t6.gmbs_event_member_lines_id = t8.id
INNER JOIN gmbs_events t9 ON t8.gmbs_events_id = t9.id
INNER JOIN gmbs_question_section t10 ON t2.gmbs_question_section_id = t10.id
INNER JOIN gmbs_business_unit t11 ON t10.gmbs_business_unit_id = t11.id
INNER JOIN gedung_sector_line t12 ON t11.gedung_sector_line_id = t12.id
INNER JOIN gedung_sector t13 ON t12.gedung_sector_id = t13.id
INNER JOIN gedung t14 ON t13.gedung_id = t14.id
LEFT JOIN gmbs_event_pic_activity_notes t15 ON t15.gmbs_event_pic_activities_id = t1.id',
[
't1.id',
't7.description as department_name',
't11.bu_name',
't12.line_number as line_name',
't10.section_name',
't2.description as activities',
't4.pic_nik',
'getEmpName(t4.pic_nik) as pic_name',
't15.note',
't1.action_date as date_time'
],
$where,
$limit
);
$sqlfilter = 'SELECT FOUND_ROWS() as filter';
$stmtfilter = $this->registry->db->prepare($sqlfilter);
$stmtfilter->execute();
$rsfilter = $stmtfilter->fetchAll(PDO::FETCH_ASSOC);
$rstotal = $this->dbBuilderSelectJoin(
'gmbs_event_pic_activities t1
INNER JOIN gmbs_activities t2 ON t1.gmbs_activities_id = t2.id
INNER JOIN gmbs_answer_detail t3 ON t1.gmbs_answer_detail_id = t3.id
INNER JOIN gmbs_event_pic t4 ON t1.gmbs_event_pic_id = t4.id
INNER JOIN gmbs_department_group_shift t5 ON t4.gmbs_department_group_shift_id = t5.id
INNER JOIN gmbs_event_member_line_departments t6 ON t5.gmbs_event_member_line_departments_id = t6.id
INNER JOIN department t7 ON t6.department_id = t7.id
INNER JOIN gmbs_event_member_lines t8 ON t6.gmbs_event_member_lines_id = t8.id
INNER JOIN gmbs_events t9 ON t8.gmbs_events_id = t9.id
INNER JOIN gmbs_question_section t10 ON t2.gmbs_question_section_id = t10.id
INNER JOIN gmbs_business_unit t11 ON t10.gmbs_business_unit_id = t11.id
INNER JOIN gedung_sector_line t12 ON t11.gedung_sector_line_id = t12.id
INNER JOIN gedung_sector t13 ON t12.gedung_sector_id = t13.id
INNER JOIN gedung t14 ON t13.gedung_id = t14.id
LEFT JOIN gmbs_event_pic_activity_notes t15 ON t15.gmbs_event_pic_activities_id = t1.id',
[
'count(t1.id) as total',
],
$where
);
if (isset($rs) && count($rs) !== 0) {
$qImg = "SELECT * FROM gmbs_event_pic_activity_img WHERE gmbs_event_pic_activities_id = :id";
$stmtImg = $this->registry->db->prepare($qImg);
$pushData = array();
foreach ($rs as $value) {
$stmtImg->bindValue(':id', $value['id'], PDO::PARAM_STR);
$stmtImg->execute();
$resImg = $stmtImg->fetchAll(PDO::FETCH_ASSOC);
$value['photo'] = array();
if (isset($resImg) && count($resImg) !== 0) {
foreach ($resImg as $img) {
$value['photo'][] = array('img' => $this->registry->config->server_address . 'hcportal_docs/upload/self-genba/' . $img['img']);
}
}
$pushData[] = $value;
}
}
$total = $rstotal[0]['total'];
$rData['iTotalDisplayRecords'] = $total;
$rData['iTotalRecords'] = $total;
$rData['aData'] = $pushData;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function viewDataMonitoringGembaSelfGroupDepartment($params)
{
try {
$where = [];
if ($params['event_id']) {
array_push($where, ['field' => 'gmbs_events_id', 'key' => 't8.gmbs_events_id', 'val' => $params['event_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['ref_manunfactures_id']) {
array_push($where, ['field' => 'ref_manufactures_id', 'key' => 't9.ref_manufactures_id', 'val' => $params['ref_manunfactures_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['sub_area_id']) {
array_push($where, ['field' => 'sub_area_id', 'key' => 't14.sub_area_id', 'val' => $params['sub_area_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['gedung_id']) {
array_push($where, ['field' => 'gedung_id', 'key' => 't13.gedung_id', 'val' => $params['gedung_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['sector_id']) {
array_push($where, ['field' => 'gedung_sector_id', 'key' => 't12.gedung_sector_id', 'val' => $params['sector_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['line_id']) {
array_push($where, ['field' => 'gedung_sector_line_id', 'key' => 't11.gedung_sector_line_id', 'val' => $params['line_id'], 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
if ($params['date']) {
array_push($where, ['field' => 'action_date', 'key' => 't4.action_date', 'val' => date('Y-m-d', strtotime($params['date'])), 'bind' => PDO::PARAM_STR, 'opr' => 'LIKE']);
}
$rs = $this->dbBuilderSelectJoin(
'gmbs_event_member_line_departments t1
LEFT JOIN gmbs_department_group_shift t2 ON t2.gmbs_event_member_line_departments_id = t1.id
LEFT JOIN gmbs_event_pic t3 ON t3.gmbs_department_group_shift_id = t2.id
LEFT JOIN gmbs_event_pic_activities t4 ON t4.gmbs_event_pic_id = t3.id
LEFT JOIN gmbs_activities t5 ON t4.gmbs_activities_id = t5.id
LEFT JOIN gmbs_answer_detail t6 ON t4.gmbs_answer_detail_id = t6.id AND t6.qualitative_answer = "Tidak Standar"
INNER JOIN department t7 ON t1.department_id = t7.id
INNER JOIN gmbs_event_member_lines t8 ON t1.gmbs_event_member_lines_id = t8.id
INNER JOIN gmbs_events t9 ON t8.gmbs_events_id = t9.id
LEFT JOIN gmbs_question_section t10 ON t5.gmbs_question_section_id = t10.id
LEFT JOIN gmbs_business_unit t11 ON t10.gmbs_business_unit_id = t11.id
LEFT JOIN gedung_sector_line t12 ON t11.gedung_sector_line_id = t12.id
LEFT JOIN gedung_sector t13 ON t12.gedung_sector_id = t13.id
LEFT JOIN gedung t14 ON t13.gedung_id = t14.id',
[
't14.sub_area_id',
't13.gedung_id',
't12.gedung_sector_id',
't11.gedung_sector_line_id',
't8.gmbs_events_id',
't9.ref_manufactures_id',
't7.id as department_id',
't7.description as department',
't5.gmbs_question_section_id',
'SUM(IF(t6.id is not null,1,0)) as total'
],
$where,
"GROUP BY t7.id"
);
$rData['aData'] = $rs;
$arData = array($rData);
return $rData;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataReffBusinessUnit($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'gmbs_ref_business_unit t1',
[
't1.*'
],
[
['field' => 'ref_manunfactures_id', 'key' => 't1.ref_manunfactures_id', 'val' => $params['ref_manunfactures_id'], 'bind' => PDO::PARAM_STR],
['field' => 'is_publish', 'key' => 't1.is_publish', 'val' => 1, 'bind' => PDO::PARAM_STR],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function searchEmp($params)
{
try {
$sql = $this->sqlSearchEmp();
$stmt = $this->registry->db->prepare($sql);
$stmt->bindValue(':search', '%' . $params['search'] . '%', PDO::PARAM_STR);
$stmt->bindValue(':department_id', $params['department_id'], PDO::PARAM_STR);
$stmt->bindValue(':sub_area_id', $params['sub_area_id'], PDO::PARAM_STR);
$stmt->execute();
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getCekUserAdminSelf($params)
{
try {
$nik = \Helper::getSessionVar('username');
$rs = $this->dbBuilderSelectJoin(
'employement t1
INNER JOIN emp_job t2 ON t1.nik=t2.nik AND t2.id=(SELECT id FROM emp_job WHERE nik = t1.nik AND effective_date<=now() ORDER BY effective_date DESC LIMIT 1)
INNER JOIN mpp_detail t3 ON t2.mpp_detail_id = t3.id
INNER JOIN org_layer t4 ON t3.org_layer_id = t4.id
INNER JOIN emp_personal_sub_area t5 ON t1.nik=t5.nik AND t5.id=(SELECT id FROM emp_personal_sub_area WHERE nik = t1.nik AND effective_date<=now() ORDER BY effective_date DESC LIMIT 1)
LEFT JOIN gmbs_ref_admin t6 ON t1.nik = t6.nik',
[
't1.nik',
't4.entity_id',
't4.division_id',
't4.department_id',
't5.sub_area_id',
'IF(t6.id is not null,1,0) is_admin'
],
[
['field' => 'nik', 'key' => 't1.nik', 'val' => $nik, 'bind' => PDO::PARAM_STR],
]
);
return $rs[0];
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataDivisionDepartment($params)
{
try {
$rs = $this->dbBuilderSelectJoin(
'division_department t1
INNER JOIN division t2 ON t1.division_id = t2.id
INNER JOIN department t3 ON t1.department_id = t3.id',
[
't1.department_id',
't1.division_id',
't2.description as division',
't3.description as department'
],
[
// ['field' => 'nik','key' => 't1.nik', 'val' => $nik, 'bind' => PDO::PARAM_STR],
]
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function deleteActivities($id, $data_array = array())
{
try {
$this->registry->db->beginTransaction();
if (count($data_array) == 0) {
$data_array = array();
array_push($data_array, $id);
}
foreach ($data_array as $key => $value) {
$id = $value;
$rs = $this->dbBuilderSelectJoin(
'gmbs_event_pic_activities t1',
[
't1.*'
],
[
['field' => 'gmbs_activities_id', 'key' => 't1.gmbs_activities_id', 'val' => $id, 'bind' => PDO::PARAM_STR],
]
);
if (count($rs) > 0) {
$gmbs_activities = $this->dbBuilderUpdate('gmbs_activities', [
['key' => 'is_publish', 'val' => '0', 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $id, 'bind' => PDO::PARAM_INT],
]);
if (!$gmbs_activities) {
return false;
}
} else {
$this->dbBuilderDelete('gmbs_activities', 'id', $id);
}
}
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteActivities: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteActivities: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteWorkScopes($id, $data_array = array())
{
try {
$this->registry->db->beginTransaction();
if (count($data_array) == 0) {
$data_array = array();
array_push($data_array, $id);
}
foreach ($data_array as $key => $value) {
$id = $value;
$rs = $this->dbBuilderSelectJoin(
'gmbs_activities t1',
[
't1.*'
],
[
['field' => 'gmbs_ref_work_scopes_id', 'key' => 't1.gmbs_ref_work_scopes_id', 'val' => $id, 'bind' => PDO::PARAM_STR],
]
);
if (count($rs) > 0) {
$gmbs_ref_work_scopes = $this->dbBuilderUpdate('gmbs_ref_work_scopes', [
['key' => 'is_publish', 'val' => '0', 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $id, 'bind' => PDO::PARAM_INT],
]);
if (!$gmbs_ref_work_scopes) {
return false;
}
} else {
$this->dbBuilderDelete('gmbs_ref_work_scopes', 'id', $id);
}
}
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteWorkScopes: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteWorkScopes: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteRiskLevel($id, $data_array = array())
{
try {
$this->registry->db->beginTransaction();
if (count($data_array) == 0) {
$data_array = array();
array_push($data_array, $id);
}
foreach ($data_array as $key => $value) {
$id = $value;
$rs = $this->dbBuilderSelectJoin(
'gmbs_activities t1',
[
't1.*'
],
[
['field' => 'gmbs_ref_risk_level', 'key' => 't1.gmbs_ref_risk_level', 'val' => $id, 'bind' => PDO::PARAM_STR],
]
);
if (count($rs) > 0) {
$gmbs_ref_risk_level = $this->dbBuilderUpdate('gmbs_ref_risk_level', [
['key' => 'is_publish', 'val' => '0', 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $id, 'bind' => PDO::PARAM_INT],
]);
if (!$gmbs_ref_risk_level) {
return false;
}
} else {
$this->dbBuilderDelete('gmbs_ref_risk_level', 'id', $id);
}
}
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteRiskLevel: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteRiskLevel: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteQuestionSectionSelf($id, $data_array = array())
{
try {
$this->registry->db->beginTransaction();
if (count($data_array) == 0) {
$data_array = array();
array_push($data_array, $id);
}
foreach ($data_array as $key => $value) {
$id = $value;
$rs = $this->dbBuilderSelectJoin(
'gmbs_activities t1',
[
't1.*'
],
[
['field' => 'gmbs_question_section_id', 'key' => 't1.gmbs_question_section_id', 'val' => $id, 'bind' => PDO::PARAM_STR],
]
);
if (count($rs) > 0) {
$gmbs_question_section = $this->dbBuilderUpdate('gmbs_question_section', [
['key' => 'is_publish', 'val' => '0', 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $id, 'bind' => PDO::PARAM_INT],
]);
if (!$gmbs_question_section) {
return false;
}
} else {
$this->dbBuilderDelete('gmbs_question_section', 'id', $id);
}
}
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionSectionSelf: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionSectionSelf: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteQuestionCategorySelf($id, $data_array = array())
{
try {
$this->registry->db->beginTransaction();
if (count($data_array) == 0) {
$data_array = array();
array_push($data_array, $id);
}
foreach ($data_array as $key => $value) {
$id = $value;
$rs = $this->dbBuilderSelectJoin(
'gmbs_activities t1',
[
't1.*'
],
[
['field' => 'gmbs_question_category_id', 'key' => 't1.gmbs_question_category_id', 'val' => $id, 'bind' => PDO::PARAM_STR],
]
);
if (count($rs) > 0) {
$gmbs_question_category = $this->dbBuilderUpdate('gmbs_question_category', [
['key' => 'is_publish', 'val' => '0', 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $id, 'bind' => PDO::PARAM_INT],
]);
if (!$gmbs_question_category) {
return false;
}
} else {
$this->dbBuilderDelete('gmbs_question_category', 'id', $id);
}
}
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionCategorySelf: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteQuestionCategorySelf: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteRefBusinessUnit($id, $data_array = array())
{
try {
$this->registry->db->beginTransaction();
if (count($data_array) == 0) {
$data_array = array();
array_push($data_array, $id);
}
foreach ($data_array as $key => $value) {
$id = $value;
$rs = $this->dbBuilderSelectJoin(
'gmbs_business_unit t1',
[
't1.*'
],
[
['field' => 'gmbs_ref_business_unit_id', 'key' => 't1.gmbs_ref_business_unit_id', 'val' => $id, 'bind' => PDO::PARAM_STR],
]
);
if (count($rs) > 0) {
$gmbs_ref_business_unit = $this->dbBuilderUpdate('gmbs_ref_business_unit', [
['key' => 'is_publish', 'val' => '0', 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $id, 'bind' => PDO::PARAM_INT],
]);
if (!$gmbs_ref_business_unit) {
return false;
}
} else {
$this->dbBuilderDelete('gmbs_ref_business_unit', 'id', $id);
}
}
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteRefBusinessUnit: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteRefBusinessUnit: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteBusinessUnit($id, $data_array = array())
{
try {
$this->registry->db->beginTransaction();
if (!isset($data_array)) {
$rs = $this->dbBuilderSelectJoin(
'gmbs_question_section t1',
[
't1.*'
],
[
['field' => 'gmbs_business_unit_id', 'key' => 't1.gmbs_business_unit_id', 'val' => $id, 'bind' => PDO::PARAM_STR],
]
);
if (count($rs) > 0) {
$gmbs_business_unit = $this->dbBuilderUpdate('gmbs_business_unit', [
['key' => 'is_publish', 'val' => '0', 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $id, 'bind' => PDO::PARAM_INT],
]);
if (!$gmbs_business_unit) {
return false;
}
} else {
$this->dbBuilderDelete('gmbs_business_unit', 'id', $id);
}
} else {
if (count($data_array) == 0) {
$data_array = array();
array_push($data_array, $id);
}
foreach ($data_array as $key => $value) {
$id = $value;
$rs = $this->dbBuilderSelectJoin(
'gmbs_question_section t1',
[
't1.*'
],
[
['field' => 'gmbs_business_unit_id', 'key' => 't1.gmbs_business_unit_id', 'val' => $id, 'bind' => PDO::PARAM_STR],
]
);
if (count($rs) > 0) {
$gmbs_business_unit = $this->dbBuilderUpdate('gmbs_business_unit', [
['key' => 'is_publish', 'val' => '0', 'bind' => PDO::PARAM_STR],
], [
['key' => 'id', 'val' => $id, 'bind' => PDO::PARAM_INT],
]);
if (!$gmbs_business_unit) {
return false;
}
} else {
$this->dbBuilderDelete('gmbs_business_unit', 'id', $id);
}
}
}
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteBusinessUnit: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteBusinessUnit: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function deleteLineRunning($id, $data_array = array())
{
try {
$this->registry->db->beginTransaction();
$data_array = json_decode($data_array, true);
if (!$data_array) {
$data_array = array();
}
if (count($data_array) == 0) {
array_push($data_array, $id);
}
foreach ($data_array as $key => $value) {
$id = $value;
$this->dbBuilderDelete('gmbs_line_running', 'id', $id);
}
$this->registry->db->commit();
$message = "PAYROLL.MESSAGE.SUCCMESDEL";
$status = "PAYROLL.MESSAGE.SUCCESS";
return true;
} catch (PDOException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteLineRunning: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESQUERY";
-$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
} catch (ErrorException $e) {
$this->registry->db->rollBack();
$this->registry->log->error('Gemba-deleteLineRunning: ' . $e->getMessage() . ', Line: ' . $e->getLine() . ', File: ' . $e->getFile() . ', User: ' . \Helper::getSessionVar('username'));
$message = "PAYROLL.MESSAGE.FAILMESUNKNOWN";
$status = "PAYROLL.MESSAGE.FAILED";
return $this->sendResponse([], $message, $status);
}
}
public function getDataRefGedungSectorLine($params)
{
try {
$where = [];
$rs = $this->dbBuilderSelectJoin(
'gedung_sector_line t1
INNER JOIN gedung_sector t2 ON t1.gedung_sector_id = t2.id
INNER JOIN gedung t3 ON t2.gedung_id = t3.id
INNER JOIN sub_area t4 ON t3.sub_area_id = t4.id
INNER JOIN company t5 ON t4.company_id = t5.id',
[
't1.id as gedung_sector_line_id',
't1.line_number',
't1.gedung_sector_id',
't2.sector_name',
't3.gedung_name',
't4.description as sub_area',
't5.description as company'
],
$where
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataRefShiftGroupAll($params)
{
try {
$where = [];
$rs = $this->dbBuilderSelectJoin(
'ref_shift_group t1
INNER JOIN sub_area t2 ON t1.sub_area_id = t2.id
INNER JOIN company t3 ON t2.company_id = t3.id ',
[
't1.id as ref_shift_group_id',
't1.shift_group_name',
't2.description as sub_area',
't3.description as company'
],
$where
);
return $rs;
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getBusinessTitle($divisionId, $departmentId, $subAreaId)
{
try {
$sql = "SELECT bt.id as id,bt.description AS name
FROM ndc_business_titles bt
INNER JOIN org_layer ol ON ol.bt_id=bt.id
INNER JOIN sub_area sa ON ol.entity_id=sa.company_id
WHERE ol.division_id = :divisionId
AND ol.department_id = :departmentId
AND sa.id = :subAreaId
AND sa.country_id='IDN'";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindParam(':divisionId', $divisionId, PDO::PARAM_STR);
$stmt->bindParam(':departmentId', $departmentId, PDO::PARAM_STR);
$stmt->bindParam(':subAreaId', $subAreaId, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getOrgLayer($divisionId, $departmentId, $subAreaId)
{
try {
$sql = "
SELECT
ol.id AS id,
ol.description AS name
FROM
org_layer ol
INNER JOIN sub_area sa ON sa.company_id = ol.entity_id
WHERE
sa.id = :subAreaId
AND sa.country_id = 'IDN' -- param country
AND ol.division_id = :divisionId
AND ol.department_id = :departmentId
GROUP BY
ol.id,
ol.description;";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindParam(':divisionId', $divisionId, PDO::PARAM_STR);
$stmt->bindParam(':departmentId', $departmentId, PDO::PARAM_STR);
$stmt->bindParam(':subAreaId', $subAreaId, PDO::PARAM_STR);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getMpp($divisionId, $departmentId, $subAreaId, $olId)
{
try {
$sql = "
SELECT
mpp.id AS id,
mpp.description AS name
FROM
org_layer ol
INNER JOIN mpp_detail mpp ON ol.id = mpp.id
INNER JOIN mpp_detail_transaction mdt ON mpp.id = mdt.mpp_detail_id
AND mdt.effective_date =(
SELECT
MAX(effective_date)
FROM
mpp_detail_transaction
WHERE
mpp_detail_id = mpp.id
AND is_active = '1'
ORDER BY
effective_date DESC
LIMIT
1
)
INNER JOIN sub_area sa ON sa.company_id = ol.entity_id
WHERE
sa.id = :subAreaId
AND sa.country_id = 'IDN' -- param country
AND ol.division_id = :divisionId
AND ol.department_id = :departmentId
AND ol.id = :olId
GROUP BY
mpp.id,
mpp.description;
";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindParam(':divisionId', $divisionId, PDO::PARAM_STR);
$stmt->bindParam(':departmentId', $departmentId, PDO::PARAM_STR);
$stmt->bindParam(':subAreaId', $subAreaId, PDO::PARAM_STR);
$stmt->bindParam(':olId', $olId, PDO::PARAM_STR);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getLineListData()
{
try {
$sql = "select a.*, b.*, c.*, d.*, e.*, g.*, a.id as id, g.id as gmbs_business_unit_id
from gedung_sector_line a
INNER JOIN gedung_sector b on a.gedung_sector_id = b.id
INNER JOIN gedung c on b.gedung_id = c.id
INNER JOIN ref_manufacture_sub_area d on c.sub_area_id = d.sub_area_id
INNER JOIN ref_manunfactures e on d.ref_manunfactures_id = e.id
LEFT JOIN gmbs_business_unit g on a.id = g.gedung_sector_line_id;";
$stmt = $this->registry->db->prepare($sql);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function GetBusinessUnits($search = null)
{
try {
$sql = "SELECT id, bu_name as name FROM gmbs_business_unit WHERE is_publish = 1";
if ($search !== null) {
$sql .= " AND bu_name LIKE :search";
}
$stmt = $this->registry->db->prepare($sql);
if ($search !== null) {
$stmt->bindValue(':search', '%' . $search . '%', PDO::PARAM_STR);
}
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getSector($buId = null, $search = null, $sectorId = null)
{
try {
$sql = "SELECT gedung_sector.id,
CASE
WHEN gedung_sector.sector_name = '-' THEN gedung.gedung_name
ELSE CONCAT(gedung.gedung_name, '-', gedung_sector.sector_name)
END as name
FROM
gedung_sector_line
INNER JOIN gedung_sector ON gedung_sector_line.gedung_sector_id = gedung_sector.id
INNER JOIN gedung ON gedung.id = gedung_sector.gedung_id
LEFT JOIN gmbs_business_unit ON gmbs_business_unit.gedung_sector_line_id = gedung_sector_line.id";
$conditions = [];
$params = [];
if ($sectorId !== null) {
$conditions[] = "gedung_sector.id = :sectorId";
$params[':sectorId'] = $sectorId;
}
if ($buId !== null) {
$conditions[] = "gmbs_business_unit.id = :buId";
$params[':buId'] = $buId;
}
if ($search !== null) {
$conditions[] = "(gedung.gedung_name LIKE :search OR
(gedung_sector.sector_name != '-' AND gedung_sector.sector_name LIKE :search) OR
CASE
WHEN gedung_sector.sector_name = '-' THEN gedung.gedung_name
ELSE CONCAT(gedung.gedung_name, '-', gedung_sector.sector_name)
END LIKE :search)";
$params[':search'] = '%' . $search . '%';
}
if (!empty($conditions)) {
$sql .= " WHERE " . implode(" AND ", $conditions);
}
$sql .= " GROUP BY gedung_sector.id";
$stmt = $this->registry->db->prepare($sql);
foreach ($params as $key => &$val) {
$stmt->bindParam($key, $val, is_int($val) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getTeamMember($nik) {
try {
$sql = "SELECT c.id as mppId, a.nik, getEmpName(a.nik) as emp_name, a.grade
FROM employement a
INNER JOIN emp_job b ON a.nik=b.nik
AND b.effective_date=(SELECT max(ej.effective_date)
FROM emp_job ej
WHERE ej.nik=a.nik
AND ej.effective_date<=current_date())
INNER JOIN mpp_detail c ON b.mpp_detail_id=c.id
INNER JOIN mpp_detail_transaction d ON c.id=d.mpp_detail_id
AND d.effective_date=(SELECT max(mt.effective_date)
FROM mpp_detail_transaction mt
WHERE mt.mpp_detail_id=c.id
AND mt.effective_date<=current_date())
WHERE a.status='active'
AND LEFT(a.grade,1)>=3
AND (
a.nik = :nik
OR
d.level_code LIKE CONCAT(
'%', '-',
(SELECT md.id
FROM emp_job ej2
INNER JOIN mpp_detail md ON ej2.mpp_detail_id = md.id
WHERE ej2.nik = :nik
AND ej2.effective_date = (
SELECT MAX(ej3.effective_date)
FROM emp_job ej3
WHERE ej3.nik = :nik
AND ej3.effective_date <= current_date()
)
),
'-', '%'
)
)
";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindParam(':nik', $nik, PDO::PARAM_STR);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getGedungSectorLines($buId = null, $sectorId = null, $search = null)
{
try {
$sql = "SELECT
gedung_sector_line.id as id, gedung_sector_line.line_number as name
FROM
gedung_sector_line
INNER JOIN gedung_sector ON gedung_sector_line.gedung_sector_id = gedung_sector.id
INNER JOIN gedung ON gedung.id = gedung_sector.gedung_id
LEFT JOIN gmbs_business_unit ON gmbs_business_unit.gedung_sector_line_id = gedung_sector_line.id
WHERE 1=1";
$params = array();
if ($buId != null) {
$sql .= " AND gmbs_business_unit.id = :buId";
$params[":buId"] = $buId;
}
if ($sectorId !== null) {
$sql .= " AND gedung_sector.id = :sectorId";
$params[':sectorId'] = $sectorId;
}
if ($search !== null) {
$sql .= " AND gedung_sector_line.line_number LIKE :search";
$params[':search'] = '%' . $search . '%';
}
$sql .= " GROUP BY gedung_sector_line.id LIMIT 10";
$stmt = $this->registry->db->prepare($sql);
foreach ($params as $key => $value) {
$stmt->bindValue($key, $value);
}
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function searchEmployee($search)
{
try {
$search = '%' . $search . '%';
$sql = "
SELECT
emp.nik,
CONCAT_WS(
' ',
ep.title,
ep.first_name,
ep.middle_name,
ep.last_name
) as name,
ep.first_name,
ep.last_name,
ep.id AS emp_id,
IFNULL(ep.`office_mail`,`emp_email`)AS email,
ep.`photo_address` AS profile_picture,
usr.`group` AS `group_id`,
g.`group` AS `group_name`,
job.job_title as position_name,
CASE
WHEN emp.flag_app = 'SAP' THEN 1
ELSE 0
END AS is_employee,
CASE
WHEN emp.status = 'active' THEN 1
ELSE 0
END AS is_active
FROM
emp_profiles ep
INNER JOIN employement emp ON ep.id = emp.emp_profile_id
INNER JOIN `user` usr ON emp.nik = usr.username
INNER JOIN `group` g ON usr.`group` = g.groupid
LEFT JOIN emp_job job ON emp.nik = job.nik
AND job.id = (
SELECT
id
FROM
emp_job
WHERE
nik = emp.nik
ORDER BY
id DESC
LIMIT
1
)
WHERE
emp.status = 'active' AND
(
CONCAT_WS(
' ',
IF(LENGTH(ep.title), ep.title, NULL),
IF(LENGTH(ep.first_name), ep.first_name, NULL),
IF(LENGTH(ep.middle_name), ep.middle_name, NULL),
IF(LENGTH(ep.last_name), ep.last_name, NULL)
) LIKE :search OR (emp.nik LIKE :search)
) LIMIT 10";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindParam(':search', $search, PDO::PARAM_STR);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getRefManufactures()
{
try {
$sql = "SELECT * FROM ref_manunfactures";
$stmt = $this->registry->db->prepare($sql);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getSubAreaFromManufacture($id)
{
try {
$sql = "SELECT id, description as name
FROM sub_area WHERE id IN
(
SELECT sub_area_id FROM ref_manufacture_sub_area WHERE ref_manunfactures_id = :id
)
";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDivisionFromSubArea($id)
{
try {
$sql = "SELECT divs.id AS id, divs.description AS name FROM org_layer ol
INNER JOIN division divs ON ol.division_id=divs.id
INNER JOIN sub_area sa ON sa.company_id=ol.entity_id
WHERE sa.id=:id
AND sa.country_id='IDN'
GROUP BY divs.id ,divs.description;";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDepartmentDivisionName($departmentId)
{
try {
$this->registry->log->error('departmentId: ' . $departmentId);
$sql = "SELECT department.id AS id, department.description as department, division.description as division
FROM department
INNER JOIN division ON division.id = department.division_id
WHERE department.id = :departmentId LIMIT 1";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindParam(':departmentId', $departmentId, PDO::PARAM_STR);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDepartmentFromDivision($divisionId, $subAreaId)
{
try {
$sql = "SELECT dept.id AS id, dept.description AS name
FROM org_layer ol
INNER JOIN department dept ON ol.department_id=dept.id
INNER JOIN sub_area sa ON sa.company_id=ol.entity_id
WHERE sa.id=:subAreaId -- param area
AND ol.division_id=:divisionId -- param division
GROUP BY dept.id,dept.description;";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindParam(':subAreaId', $subAreaId, PDO::PARAM_INT);
$stmt->bindParam(':divisionId', $divisionId, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getManufactureArea($id)
{
try {
$sql = "SELECT id, area as name FROM ref_manufacture_area WHERE ref_manunfactures_id = :id";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getSubAreaByNik($nik)
{
try {
// First, check for admin subareas
$sqlAdmin = "SELECT DISTINCT(subarea_id) FROM user_admin_subarea WHERE username = :user";
$stmtAdmin = $this->registry->db->prepare($sqlAdmin);
$stmtAdmin->bindValue(':user', $nik, PDO::PARAM_STR);
$stmtAdmin->execute();
$rsAdmin = $stmtAdmin->fetchAll(PDO::FETCH_ASSOC);
if ($rsAdmin) {
$subareaIds = array_column($rsAdmin, 'subarea_id');
return $subareaIds;
}
// If no admin subareas, check for personal subareas
$sql = "SELECT sub_area_id FROM emp_personal_sub_area
WHERE nik = :nik AND effective_date <= CURRENT_DATE
ORDER BY effective_date DESC, id DESC LIMIT 1";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindValue(':nik', $nik, PDO::PARAM_STR);
$stmt->execute();
$rs = $stmt->fetch(PDO::FETCH_ASSOC);
return $rs ? [$rs['sub_area_id']] : [];
} catch (PDOException $e) {
$this->registry->log->error('module gemba: ' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba: ' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function viewDataMasterActivitiesDownload()
{
try {
$sql = "SELECT
t1.id,
t1.order_no,
t1.is_publish,
t1.description,
t1.gmbs_question_category_id,
t1.gmbs_question_section_id,
t1.gmbs_ref_risk_level_id,
t1.gmbs_ref_work_scopes_id,
t1.reference_link,
t1.gmbs_ref_risk_level_id,
t3.department_id,
t3.gmbs_business_unit_id
FROM gmbs_activities t1
INNER JOIN gmbs_question_category t2 ON t1.gmbs_question_category_id = t2.id
INNER JOIN gmbs_question_section t3 ON t1.gmbs_question_section_id = t3.id
ORDER BY t3.section_name ASC";
$stmt = $this->registry->db->prepare($sql);
$stmt->execute();
$rsdata = $stmt->fetchAll(PDO::FETCH_ASSOC);
$qDetail = "SELECT * FROM gmbs_answer_detail WHERE gmbs_activities_id = :id";
$stmtDetail = $this->registry->db->prepare($qDetail);
$dataArr = array();
if (isset($rsdata) && count($rsdata) !== 0) {
foreach ($rsdata as $val) {
$stmtDetail->bindValue(':id', $val['id'], PDO::PARAM_STR);
$stmtDetail->execute();
$rsdataDetail = $stmtDetail->fetchAll(PDO::FETCH_ASSOC);
if (isset($rsdataDetail) && count($rsdataDetail) !== 0) {
foreach ($rsdataDetail as $valDetail) {
if ($valDetail['answer_label'] == 'YES') {
$val['answer_score_yes'] = $valDetail['answer_score'];
$val['is_require_note_yes'] = $valDetail['is_require_note'];
} else {
$val['answer_score_no'] = $valDetail['answer_score'];
$val['is_require_note_no'] = $valDetail['is_require_note'];
}
}
}
$dataArr[] = $val;
}
}
return $dataArr;
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
public function getDataEmployee($empId)
{
$this->registry->log->error($empId);
try {
$sql = "select
a.first_name,
concat_ws(
' ',
ifnull(a.middle_name, ''),
ifnull(a.last_name, '')
) as last_name,
e.description as job_position,
e.division_id as division_id,
e.department_id as department_id,
e.id as org_layer_id,
bt.id as bt_id,
mpp.id as mpp_id,
emp_personal_sub_area.sub_area_id as sub_area_id,
a.photo_address AS profile_picture,
IFNULL(office_mail, emp_email) AS email,
ref_manufacture_sub_area.ref_manunfactures_id AS ref_manufactures_id
FROM
emp_profiles a
inner join employement b on a.id = b.emp_profile_id
inner join emp_job c on b.nik = c.nik
and c.effective_date =(
select
max(ej.effective_date)
from
emp_job ej
where
ej.nik = b.nik
and ej.effective_date <= current_date()
)
inner join mpp_detail d on c.mpp_detail_id = d.id
inner join org_layer e on d.org_layer_id = e.id
INNER JOIN emp_personal_sub_area ON c.nik = emp_personal_sub_area.nik
INNER JOIN sub_area sa ON e.entity_id = sa.company_id
INNER JOIN ndc_business_titles bt ON bt.id = e.bt_id
LEFT JOIN ref_manufacture_sub_area ON ref_manufacture_sub_area.sub_area_id = emp_personal_sub_area.sub_area_id
INNER JOIN mpp_detail mpp ON e.id = mpp.id
where
a.id = :emp_id
ORDER BY
emp_personal_sub_area.effective_date DESC
LIMIT
1;";
$stmt = $this->registry->db->prepare($sql);
$stmt->bindValue(':emp_id', $empId, PDO::PARAM_INT);
$stmt->execute();
$rs = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (count($rs) == 0) {
return null;
}
return $rs[0];
} catch (PDOException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
} catch (ErrorException $e) {
$this->registry->log->error('module gemba :' . $e->getMessage() . ', user: ' . \Helper::getSessionVar('username'));
return false;
}
}
}
