<?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);
        }
    }
}