<?php
class Check_on_hold extends MY_Controller
{
  //re-upload to devone
    function __construct()
    {
        parent::__construct();
    }
    function lokal()
    {
        echo "<H4>Tagihan Lokal Hold Status</H4><br/>";
        $mou_id = "0";
        $query = "SELECT GROUP_CONCAT(M_MouID SEPARATOR ',') as mou_id
        FROM
        (SELECT
        distinct
        M_MouID
        FROM 
        f_bill_issue
        join f_bill on F_BillIssueF_BillID = F_BillID
        and F_BillIsActive = 'Y'
        join m_mou on M_MouID = F_BillIssueM_MouID
        and F_BillIssueIsAllMou = 'N'
        and M_MouIsActive = 'H' 
        and F_BillIssueIsActive = 'Y'
        and F_BillIssueIsLunas = 'N'
        and F_BillIssueIsReceive = 'Y'
                join m_company on M_MouM_CompanyID = M_CompanyID
        
        union
        SELECT
        distinct
        M_MouID
        FROM 
        f_bill_issue
        join f_bill on F_BillIssueF_BillID = F_BillID
        and F_BillIsActive = 'Y'
        join f_bill_mou on F_BillIssueF_BillID = F_BillMouF_BillID
                join m_mou on M_MouID = F_BillMouM_MouID
        and F_BillIssueIsAllMou = 'Y'
        and M_MouIsActive = 'H' 
        and F_BillIssueIsActive = 'Y'
        and F_BillIssueIsLunas = 'N'
        and F_BillIssueIsReceive = 'Y'
                join m_company on M_MouM_CompanyID = M_CompanyID) a;";
        $mou_id =  $this->db->query($query)->row()->mou_id;

    $sql = "select 
        M_CompanyName,
        M_MouName, F_BillNo, M_MouID, F_BillID ,
        max(F_PaymentDate) PaymentDate,'' Aktivasi,F_BillIssueIsAllMou
        from m_mou 
        join m_company on M_MouIsActive = 'H' 
        and M_MouM_CompanyID = M_CompanyID
        and M_MouM_BillTypeID = 3
      join f_bill_issue ON F_BillIssueM_MouID = M_MouID and F_BillIssueIsAllMou = 'N' AND F_BillIssueIsActive = 'Y'
            join f_bill on F_BillID = F_BillIssueF_BillID
                and F_BillIsActive = 'Y' and F_BillIsLunas = 'Y'
            join f_bill_detail on F_BillID = F_BillDetailF_BillID 
              and F_BillDetailIsActive = 'Y'
            join f_payment on F_BillDetailT_OrderHeaderID = F_PaymentT_OrderHeaderID
            and F_PaymentIsActive = 'Y'
        where M_MouID NOT IN($mou_id)
            group by M_MouID,F_BillID
      union
      select
      M_CompanyName,
              M_MouName, F_BillNo, M_MouID, F_BillID ,
              max(F_PaymentDate) PaymentDate,'' Aktivasi, F_BillIssueIsAllMou 
              from m_mou 
              join m_company on M_MouIsActive = 'H' 
              and M_MouM_CompanyID = M_CompanyID
              and M_MouM_BillTypeID = 3
          join f_bill_issue ON F_BillIssueM_MouID = M_MouID and F_BillIssueIsAllMou = 'Y' AND F_BillIssueIsActive = 'Y'
          join f_bill_mou on F_BillIssueF_BillID = F_BillMouF_BillID AND M_MouID = F_BillMouM_MouID
              join f_bill on F_BillID = F_BillIssueF_BillID
                  and F_BillIsActive = 'Y' and F_BillIsLunas = 'Y'
              join f_bill_detail on F_BillID = F_BillDetailF_BillID 
                and F_BillDetailIsActive = 'Y'
              join f_payment on F_BillDetailT_OrderHeaderID = F_PaymentT_OrderHeaderID
              and F_PaymentIsActive = 'Y'
              where M_MouID NOT IN($mou_id)
              group by M_MouID,F_BillID
              order by M_MouID
        ";
        $qry = $this->db->query($sql);

        if (!$qry) {
            echo json_encode([
                "status" => "ERR",
                "message" => $this->db->error()["message"],
            ]);
            exit();
        }
        echo $this->print_table_style();
        $rows = $qry->result_array();
        if (count($rows) == 0) {
            echo "No Data";
        }
        foreach ($rows as $idx => $r) {
            $url_fix = "/one-api/tools/check_on_hold/lokal_fix/" . $r["M_MouID"];
            $rows[$idx][
                "Aktivasi"
            ] = "<button onClick=\"document.location.href='{$url_fix}'\"
            style='padding:5px; color:white; border: solid 0px; border-radius: 5px;
            background-color:blue; margin-top:20px; display:block;' >Aktivasi</button>";
        }
        $this->print_table($rows, array_keys($rows[0]));
    }
    
    function index()
    {
        echo "<H4>Tagihan Terpusat Hold Status</H4><br/>";
        $mou_id = "0";
        $query = "select GROUP_CONCAT(DISTINCT M_MouID SEPARATOR ',') as mou_id
        from mou_hold_status
        join m_mou on MouHoldStatusM_MouID = M_MouID 
          and M_MouIsActive = 'H'
        join m_company on M_MouM_CompanyID = M_CompanyID
        join f_bill on MouHoldStatusF_BillID = F_BillID 
        and F_BillIsActive = 'Y' and F_BillIsLunas = 'N'
        join f_bill_detail on F_BillID = F_BillDetailF_BillID 
          and F_BillDetailIsActive = 'Y';";
        $mou_id =  $this->db->query($query)->row()->mou_id;
        $sql = "select MouHoldStatusPusatBranchCode
    CabangTitipanPusat,
    MouHoldStatusDueDate DueDate,
    min(MouHoldStatusHoldDate) HoldDate, 
    max(F_PaymentDate) PaymentDate,
    M_CompanyName,
    M_MouName, 
    F_BillNo, M_MouID, F_BillID ,
    group_concat(distinct MouHoldStatusRefNumber) BillPusatRefNumber,
    '' Aktivasi
    from mou_hold_status
    join m_mou on MouHoldStatusM_MouID = M_MouID 
      and M_MouIsActive = 'H'
    join m_company on M_MouM_CompanyID = M_CompanyID
    join f_bill on MouHoldStatusF_BillID = F_BillID 
    and F_BillIsActive = 'Y' and F_BillIsLunas = 'Y'
    join f_bill_detail on F_BillID = F_BillDetailF_BillID 
      and F_BillDetailIsActive = 'Y'
    join f_payment on F_BillDetailT_OrderHeaderID = F_PaymentT_OrderHeaderID
    and F_PaymentIsActive = 'Y'
    where M_MouID NOT IN($mou_id)
    group by M_MouID,F_BillID
    order by M_MouID
    ";
        $qry = $this->db->query($sql);

        if (!$qry) {
            echo json_encode([
                "status" => "ERR",
                "message" => $this->db->error()["message"],
            ]);
            exit();
        }
        echo $this->print_table_style();
        $rows = $qry->result_array();
        if (count($rows) == 0) {
            echo "No Data";
        }
        foreach ($rows as $idx => $r) {
            $url_fix = "/one-api/tools/check_on_hold/fix/" . $r["M_MouID"];
            $rows[$idx][
                "Aktivasi"
            ] = "<button onClick=\"document.location.href='{$url_fix}'\"
            style='padding:5px; color:white; border: solid 0px; border-radius: 5px;
            background-color:blue; margin-top:20px; display:block;' >Aktivasi</button>";
        }
        $this->print_table($rows, array_keys($rows[0]));
    }
    function check_log_table()
    {
        $sql = "select count(*) from hold_open_log";
        $qry = $this->db->query($sql);
        if (!$qry) {
            $sql = "create table hold_open_log(holdOpenLogID int not null auto_increment primary key,
            holdOpenLogDate datetime default current_timestamp(),
            holdOpenLogM_MouID int,
            holdOpenLogJson text,
            key(holdOpenLogDate),
            key(holdOpenLogM_MouID)
            )";
            $qry = $this->db->query($sql);
            if (!$qry) {
                echo "Error : " . $this->db->error()["message"];
                exit();
            }
        }
    }

    function lokal_fix($mouID)
    {
        $this->check_log_table();
        $this->db->trans_begin();
        $sql_check = "select 
        M_CompanyName,
        M_MouName, F_BillNo, M_MouID, F_BillID ,
        max(F_PaymentDate) maxPaymentDate,'' Aktivasi
        from m_mou 
        join m_company on M_MouIsActive = 'H' 
        and M_MouM_CompanyID = M_CompanyID
        and M_MouM_BillTypeID = 3 and M_MouID = ?
        join f_bill on F_BillM_MouID = M_MouID
        and F_BillIsActive = 'Y' and F_BillIsLunas = 'Y'
        join f_bill_detail on F_BillID = F_BillDetailF_BillID 
          and F_BillDetailIsActive = 'Y'
        join f_payment on F_BillDetailT_OrderHeaderID = F_PaymentT_OrderHeaderID
        and F_PaymentIsActive = 'Y'
        group by F_BillID
            ";
        $qryx = $this->db->query($sql_check, [$mouID]);
        if (!$qryx) {
            echo "Error : " . $this->db->error()["message"];
            $this->db->trans_rollback();
            exit();
        }
        $rows = $qryx->result_array();
        print_r($qryx);
        print_r($rows);
        if (count($rows) == 0) {
            echo "Error : MouID $mouID, tidak memenuhi kriteria <br/> <pre>" .
                $this->db->last_query();
            $this->db->trans_rollback();
            exit();
        }
        $dataJson = json_encode($rows);
        $sql =
            "insert into hold_open_log(holdOpenLogM_MouID,holdOpenLogJson) values(?,?)";
        $qry = $this->db->query($sql, [$mouID, $dataJson]);
        if (!$qry) {
            echo "Error : " . $this->db->error()["message"];
            $this->db->trans_rollback();
            exit();
        }

        $qry = $this->db->query(
            "update m_mou set M_MouIsActive = 'Y' where M_MouID = ?",
            [$mouID]
        );

        if (!$qry) {
            echo "Error : " . $this->db->error()["message"];
            $this->db->trans_rollback();
            exit();
        }
        $this->db->trans_commit();
        header("Location: /one-api/tools/check_on_hold/lokal");
    }
    function fix($mouID)
    {
        $this->check_log_table();
        $this->db->trans_begin();
        $sql_check = "select MouHoldStatusPusatBranchCode
            CabangTitipanPusat,
            min(MouHoldStatusHoldDate) HoldDateMin, 
            max(MouHoldStatusHoldDate) HoldDateMax, 
            MouHoldStatusDueDate DueDate,
            M_CompanyName,
            M_MouName, F_BillNo, M_MouID, F_BillID ,
            max(F_PaymentDate) maxPaymentDate
            from mou_hold_status
            join m_mou on MouHoldStatusM_MouID = ?
              and MouHoldStatusM_MouID = M_MouID 
              and M_MouIsActive = 'H'
            join m_company on M_MouM_CompanyID = M_CompanyID
            join f_bill on MouHoldStatusF_BillID = F_BillID 
            and F_BillIsActive = 'Y' and F_BillIsLunas = 'Y'
            join f_bill_detail on F_BillID = F_BillDetailF_BillID 
              and F_BillDetailIsActive = 'Y'
            join f_payment on F_BillDetailT_OrderHeaderID = F_PaymentT_OrderHeaderID
            and F_PaymentIsActive = 'Y'
            group by F_BillID
            ";
        $qryx = $this->db->query($sql_check, [$mouID]);
        if (!$qryx) {
            echo "Error : " . $this->db->error()["message"];
            $this->db->trans_rollback();
            exit();
        }
        $rows = $qryx->result_array();
        print_r($qryx);
        print_r($rows);
        if (count($rows) == 0) {
            echo "Error : MouID $mouID, tidak memenuhi kriteria <br/> <pre>" .
                $this->db->last_query();
            $this->db->trans_rollback();
            exit();
        }
        $dataJson = json_encode($rows);
        $sql =
            "insert into hold_open_log(holdOpenLogM_MouID,holdOpenLogJson) values(?,?)";
        $qry = $this->db->query($sql, [$mouID, $dataJson]);
        if (!$qry) {
            echo "Error : " . $this->db->error()["message"];
            $this->db->trans_rollback();
            exit();
        }

        $qry = $this->db->query(
            "update m_mou set M_MouIsActive = 'Y' where M_MouID = ?",
            [$mouID]
        );

        if (!$qry) {
            echo "Error : " . $this->db->error()["message"];
            $this->db->trans_rollback();
            exit();
        }
        $this->db->trans_commit();
        header("Location: /one-api/tools/check_on_hold");
    }
    function print_table_style()
    {
        return "
        <style>
        th, td {
            padding: 15px;
            text-align: left;
          }
          tr:nth-child(even) {background-color: #f2f2f2;}
          table {
            border: solid 1px ;
      min-width:600px;
            margin-left:auto;
      margin-right:auto;
          }
        </style>
        ";
    }
    public function print_table_one_column($rows)
    {
        $rst = "<table>";
        foreach ($rows as $r) {
            $rst .= "<tr>";
            $rst .= "<td>" . $r . "</td>";
            $rst .= "</tr>";
        }
        $rst .= "</table>";
        return $rst;
    }
    public function print_table($rows, $keys)
    {
        echo "<table>";
        echo "<tr>";
        foreach ($keys as $k) {
            echo "<td>$k</td>";
        }
        echo "</tr>\n";
        foreach ($rows as $r) {
            echo "<tr>";
            foreach ($keys as $k) {
                echo "<td>" . $r[$k] . "</td>";
            }
            echo "</tr>";
        }
        echo "</table>";
    }
}