90 lines
3.4 KiB
PHP
90 lines
3.4 KiB
PHP
<?php
|
|
class Generate extends MY_Controller
|
|
{
|
|
var $db;
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
}
|
|
|
|
public function index()
|
|
{
|
|
// $cek = $this->db->query("select database() as current_db")->result();
|
|
// print_r($cek);
|
|
echo "GENERATE API";
|
|
}
|
|
|
|
function generate_etl()
|
|
{
|
|
try {
|
|
$this->db->trans_begin();
|
|
|
|
$sql_search = "SELECT *
|
|
FROM etl_report
|
|
JOIN etl_map_report ON Etl_ReportR_ReportID = Etl_MapReportR_ReportID AND Etl_ReportIsActive = 'Y'
|
|
WHERE
|
|
Etl_ReportIsActive = 'Y' AND
|
|
Etl_ReportStatus = 'N'
|
|
ORDER BY Etl_ReportID ASC";
|
|
$qry_search = $this->db->query($sql_search);
|
|
if ($qry_search) {
|
|
$rows = $qry_search->result_array();
|
|
$all_params = "";
|
|
foreach($rows as $k => $v) {
|
|
$params = $v['Etl_ReportParams'];
|
|
// Decode JSON menjadi array asosiatif
|
|
$dataArray = json_decode($params, true);
|
|
// Ekstrak nilai-nilai "value"
|
|
$values = array_column($dataArray, 'value');
|
|
// Gabungkan nilai-nilai dengan tanda koma
|
|
$x = "'" . implode("','", $values) . "'";
|
|
// Tampilkan hasil
|
|
$all_params = "CALL " . $v['Etl_MapReportSP'] . '(' . $x . ');';
|
|
$sql_process = "UPDATE etl_report SET Etl_ReportStatus = 'P' WHERE Etl_ReportID = '{$v['Etl_ReportID']}'";
|
|
$qry_process = $this->db->query($sql_process);
|
|
if (!$qry_process)
|
|
{
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("etl_report update process", $this->db);
|
|
exit;
|
|
}else{
|
|
$sql = $all_params;
|
|
$query = $this->db->query($sql);
|
|
if (!$query)
|
|
{
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("etl_report select error", $this->db);
|
|
exit;
|
|
}else{
|
|
$sql_done = "UPDATE etl_report SET Etl_ReportStatus = 'D', Etl_ReportComplDate = now() WHERE Etl_ReportID = '{$v['Etl_ReportID']}'";
|
|
$qry_done = $this->db->query($sql_done);
|
|
if (!$qry_done)
|
|
{
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("etl_report update process", $this->db);
|
|
exit;
|
|
}
|
|
}
|
|
$this->clean_mysqli_connection($this->db->conn_id);
|
|
}
|
|
|
|
}
|
|
} else {
|
|
$this->db->trans_rollback();
|
|
$this->sys_error_db("etl_report select error", $this->db);
|
|
exit;
|
|
}
|
|
$this->db->trans_commit();
|
|
$result = array(
|
|
"total" => sizeof($rows),
|
|
"total_display" => sizeof($rows),
|
|
"records" => $all_params
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
}
|