<?php
    include_once("../../cfg/db.php");
    include_once("../../api/controllers/utility.php");

    $utility = new Utility();

    $type = $db->escape(trim($_GET['type']));

    if($type == 1){
        $request_id = "";

        if(isset($_GET['request_id']) && $_GET['request_id'] != ""){
            $request_id = $db->escape($_GET['request_id']);
        }

        $query_total = "SELECT COUNT(*) FROM `txtblast_sms_service_approved` WHERE request_id = '$request_id'"; //total
        $get_details = "SELECT sc.name AS company, sc.code AS company_code, sd.name AS dealer, sd.code AS dealer_code, rss.status 
            FROM text_blast tb 
            INNER JOIN request_status_summary rss ON rss.request_id = tb.id 
            INNER JOIN source_company sc ON sc.id = tb.company_id 
            INNER JOIN source_dealer sd ON sd.id = tb.dealer_id 
            WHERE tb.id = '$request_id'";

        $details = $db->sql_query($get_details);

        while($row = $details->fetch_assoc()) {
            $arr['company'] = $row['company'];
            $arr['dealer'] = $row['dealer'];
            $arr['company_code'] = $row['company_code'];
            $arr['dealer_code'] = $row['dealer_code'];
            $arr['status'] = $row['status'] == 0 ? "<span style='color:orange; font-weight:bold'>PENDING</span>" : 
                ($row['status'] == 1 ? "<span style='color:green; font-weight:bold'>APPROVED / On Going</span>" : 
                ($row['status'] == 2 ? "<span style='color:red; font-weight:bold'>DECLINED</span>" : 
                "<span style='color:blue; font-weight:bold'>CLOSED / Done</span>"));
        }

        $arr['count_total'] = $db->select($query_total);
        echo json_encode($arr);
    } 
    else if($type == 2){
        $company = ""; $dealer = ""; $status = ""; $search = "";

        if(isset($_GET['company']) && $_GET['company'] != ""){
            $company = $_GET['company'];
            $company = " AND sc.id = '$company' ";
        }

        if(isset($_GET['dealer']) && $_GET['dealer'] != ""){
            $dealer = $_GET['dealer'];
            $dealer = " AND sd.id = '$dealer' ";
        }

        if(isset($_GET['status']) && $_GET['status'] != ""){
            $status = $db->escape(trim($_GET['status']));
            $status = " AND rss.status = '$status' ";
        }

        if(isset($_GET['search'])){
            $search = $db -> escape(trim($_GET['search']));
        }

        $count = " count(rs.request_id) ";
        $fields = " rs.request_id, tb.control_number, concat(e.first_name, ' ', e.last_name) AS name, DATE_FORMAT(rs.date, '%M %d, %Y') AS date, 
        IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 2) > 0, 
            2
        ,
            IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 0) > 0,
                0
            ,
                IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 1) > 0, 
                    1
                ,
                    3
                )
            )
        ) AS status, sc.code AS company, sd.name AS dealer";

        $query_total = "SELECT %s 
            FROM `request_status` rs 
            INNER JOIN `text_blast` tb 
            ON rs.request_id = tb.id 
            INNER JOIN `request_status_summary` rss 
            ON rs.request_id = rss.request_id 
            INNER JOIN employee e 
            ON tb.employee_id = e.id 
            INNER JOIN source_company_dealer scd 
            ON e.company_dealer_id = scd.id 
            INNER JOIN source_company sc 
            ON sc.id = scd.company_id 
            INNER JOIN source_dealer sd 
            ON sd.id = scd.dealer_id 
            WHERE 1 
            AND concat(tb.control_number, concat(e.first_name, ' ', e.last_name), sc.code, sd.name) LIKE '%%%s%%' " . $company . $dealer . $status . 
            " GROUP BY rs.request_id 
            ORDER BY rs.date DESC 
            "; //total
        // echo "SELECT COUNT(1) FROM (".sprintf($query_total, $count, $search).") AS count"; return;
        $arr['count_total'] = $db->select("SELECT COUNT(1) FROM (".sprintf($query_total, $count, $search).") AS count");

        echo json_encode($arr);
    } 
    else if($type == 3){
        $request_id = ""; $status = "";

        $request_id = $db->escape(trim($_GET['request_id']));

        if(isset($_GET['status']) && $_GET['status'] != ""){
            $get_status = $db -> escape(trim($_GET['status']));
            if($get_status > 0){
                $status = " AND tbssa.status > 0 ";
            } 
            else {
                $status = " AND tbssa.status = $get_status ";
            }
        }

        $count = " count(tbssa.id) ";
        $fields = " tbssa.cust_id, tbssa.first_name, tbssa.last_name, tbssa.suffix, tbssa.company_name, tbssa.mobile_number, tbssa.status, tbssa.customer_type ";
    
        $company_query = "SELECT %s FROM `txtblast_sms_service_approved` tbssa 
            INNER JOIN `text_blast` tb 
            ON tbssa.request_id = tb.id 
            WHERE tbssa.request_id = '$request_id' " . $status;
        // echo sprintf($company_query, $count); return;

        $arr['count_total'] = $db->select(sprintf($company_query, $count));

        echo json_encode($arr);
    }
?>