<?php

namespace App\Models;

use App\Core\Database;
use App\Core\Model;
use App\Core\Sms; 

class NatureOfBusiness extends Model {
    
    public function getNatureOfBusinessCategoryCount($array_data)
    {

        $query = "
        SELECT snob.id,snob.name,COUNT(1) as count FROM (SELECT 
        snob.id,
        IF(snob.category_id = 0,snob.id,snob.category_id) AS category_id,
        snob.name,
        snob.is_parent
        FROM customer c
        INNER JOIN vehicle v
        ON c.id = v.customer_record_id
        INNER JOIN sales s
        ON v.id = s.vehicle_id

        LEFT OUTER JOIN source_nature_of_business snob 
        ON c.nature_of_business_id = snob.id

        LEFT OUTER JOIN source_company_dealer scd
        ON v.company_dealer_id = scd.id
        LEFT OUTER JOIN source_company sc
        ON scd.company_id = sc.id 
        LEFT OUTER JOIN source_dealer sd
        ON scd.dealer_id = sd.id

        WHERE 1 
        /*fix where statement*/
        AND s.activity_date != ''  
        AND (snob.category_id != 0 OR snob.is_parent = 1)
        %s /*company*/
        %s /*dealer*/
        %s /*snob*/
        %s /*sales date*/

        GROUP BY c.id
        ORDER BY s.activity_date DESC) t1 
        INNER JOIN source_nature_of_business snob 
        ON t1.category_id = snob.id
        WHERE snob.is_parent = 1
        GROUP BY snob.id 
        ORDER BY count DESC
        ";

        $result =  Database::connect()->runBaseQuery(sprintf($query, $array_data['company_access'], $array_data['dealer_access'], $array_data['category'], $array_data['sales_date']));
        return $result;
    }

    public function getNatureOfBusinessCategories()
    {
        $query = "SELECT * FROM source_nature_of_business WHERE 1 AND is_parent = 1 AND status = 1 ORDER BY name ASC";
        $result =  Database::connect()->runBaseQuery($query);
        return $result;
    }

    public function getNatureOfBusinessInfo($id)
    {
        $query = "SELECT * FROM source_nature_of_business WHERE 1 AND id = ?";
        $queryType = "i";
        $queryValue = array(
            $id,
        );
        return Database::connect()->runQuery($query, $queryType, $queryValue);
    }


    public function getNatureOfBusinessDataQuery()
    {
        return "SELECT t1.* FROM (
            SELECT 
                c.id,
                IF(
                    snob.category_id = 0, 
                    snob.id, 
                    snob.category_id
                ) AS category_id,
                IF(
                    c.type = 1, 
                    CONCAT(
                        c.last_name,
                        ', ',
                        c.first_name,
                        ' ',
                        c.middle_name
                    ),
                    c.corporation_name
                ) AS customer, 
                c.type AS customer_type,
                cc.mobile_phone_1 AS mobile, 
                cc.email_1 AS email,
                sc.code AS company,
                sd.code AS dealer,
                s.activity_date AS date,
                s.id AS sales_id, 
                sfu.called, 
                sfu.sms, 
                snob.name AS nob
            FROM 
                customer c
            INNER JOIN customer_contact cc 
                ON c.id = cc.customer_id
            INNER JOIN vehicle v
                ON c.id = v.customer_record_id
            INNER JOIN sales s
                ON v.id = s.vehicle_id 
            LEFT OUTER JOIN source_nature_of_business snob 
                ON c.nature_of_business_id = snob.id
            LEFT OUTER JOIN source_company_dealer scd
                ON v.company_dealer_id = scd.id
            LEFT OUTER JOIN source_company sc
                ON scd.company_id = sc.id 
            LEFT OUTER JOIN source_dealer sd
                ON scd.dealer_id = sd.id 
            LEFT OUTER JOIN source_follow_ups sfu 
                ON s.id = sfu.sales_id 
            WHERE 
                1 /*fix where statement*/
                AND s.activity_date != ''  
                AND (
                    snob.category_id != 0 
                    OR snob.is_parent = 1
                )
                %s /*company*/
                %s /*dealer*/
                %s /*sales _date*/
            GROUP BY c.id
                %s /*order*/
            ) t1 
            INNER JOIN source_nature_of_business snob 
                ON t1.category_id = snob.id 
            WHERE 
                1
                %s
                %s /*limit*/
            ";
    }

    public function getNatureOfBusinessDataCount($array_data) {
        $query_final = "SELECT COUNT(1) FROM (" . sprintf($this->getNatureOfBusinessDataQuery(), $array_data['company_access'], $array_data['dealer_access'], $array_data['sales_date'], $array_data['sort'], $array_data['category'], '') . ") t2";
        $result =  Database::connect()->selectBaseQuery($query_final);
        return $result;
    }

    public function getNatureOfBusinessData($array_data) {
        $query = sprintf($this->getNatureOfBusinessDataQuery(), $array_data['company_access'], $array_data['dealer_access'], $array_data['sales_date'], $array_data['sort'], $array_data['category'], $array_data['offset_limit']); 
        // echo $query; exit; 

        $result =  Database::connect()->runBaseQuery($query);
        return $result;
    }

    public function tableAction($array_data) { 
        $offset = $array_data['offset'];
        $limit = $array_data['limit'];
        $sales_id = $array_data['sales_id']; 

        $fields = " sfu.`id`, DATE_FORMAT(sfu.`sms`, '%b %d, %Y / %h:%i:%s %p') AS sms, DATE_FORMAT(sfu.`called`, '%b %d, %Y / %h:%i:%s %p') AS called, concat(e1.`first_name`, ' ', e1.`last_name`) AS sms_done_by, 
                        concat(e2.`first_name`, ' ', e2.`last_name`) AS call_done_by, sfu.`remarks`, sfu.`status` ";
        $count = " COUNT(1) ";

        $json_arr['rows'] = array();
        $action_list = "SELECT %s 
                            FROM `source_follow_ups` sfu 
                            INNER JOIN `employee` e1 
                                ON e1.id = sfu.sms_done_by 
                            INNER JOIN `employee` e2 
                                ON e2.id = sfu.call_done_by 
                            WHERE `sales_id` = '$sales_id'"; // echo sprintf($action_list, $fields); exit; 

        $action_list_query = Database::connect()->runBaseQuery(sprintf($action_list, $fields)); 
        $action_list_count = Database::connect()->selectBaseQuery(sprintf($action_list, $count)); 

        $flag = 0; $count = 2; 
        if(empty($action_list_query)){
            while($count != 0){
                if($flag == 0){
                    $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                        'action' => "SMS", 
                        'data' => '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>',
                        'done_by' => '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>'
                    ))); 
                    $flag = 1; 
                } 
                else {
                    $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                        'id' => $row['id'], 
                        'action' => "CALL", 
                        'data' => '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>', 
                        'done_by' => '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>'
                    ))); 
                    $flag = 0; 
                }

                $count--;
            }
        } 
        else {
            foreach ($action_list_query AS $row) {
                while($count != 0){
                    if($flag == 0){
                        $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                            'id' => $row['id'], 
                            'action' => "SMS", 
                            'remarks' => $row['remarks'], 
                            'data' => $row['sms'] == null ? '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>' : $row['sms'], 
                            'done_by' => $row['sms'] == null ? '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>' : $row['sms_done_by']
                        ))); 
                        $flag = 1; 
                    } 
                    else {
                        $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                            'id' => $row['id'], 
                            'action' => "CALL", 
                            'remarks' => $row['remarks'], 
                            'data' => $row['called'] == null ? '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>' : $row['called'], 
                            'done_by' => $row['called'] == null ? '<span><i class="fas fa-times-circle" style="color: #b22222;"></i></span>' : $row['call_done_by']
                        ))); 
                        $flag = 0; 
                    }
    
                    $count--;
                }
            }
        } 

        $json_arr['total'] = $action_list_count;

        return $json_arr;
    } 

    public function smsCall($array_data) {
        $type = $array_data['type']; 
        $sales_id = $array_data['sales_id']; 
        $phone = $array_data['phone']; 
        $json_arr = array(); 

        $count = Database::connect()->selectBaseQuery("SELECT COUNT(1) FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); 

        if($count > 0){ // has record
            if($type == "sms"){
                $is_done = Database::connect()->selectBaseQuery("SELECT `sms` FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); 
                if(!is_null($is_done)){
                    $json_arr['status'] = 3; // done
                } 
                else {
                    $sms_status = $this->sendMessage($phone); 

                    if($sms_status == 0){ // send sms success
                        $current_datetime = Database::connect()->selectBaseQuery("SELECT NOW()"); 
                        $current_user = $_SESSION['user']['id']; 
                        
                        $update_query = "UPDATE `source_follow_ups` SET `sms` = '$current_datetime', `sms_done_by` = '$current_user' WHERE `sales_id` = '$sales_id'"; // echo $update_query; exit; 
                        $update = Database::connect()->updateBaseQuery($update_query); 

                        if($update > 0){ 
                            $json_arr['status'] = 0; // success
                        } 
                        else { 
                            $json_arr['status'] = 1; // database failed 
                        }
                    } 
                    else {
                        $json_arr['status'] = 2; // sms failed 
                    }
                } 
            } 
            else { // call
                $is_done = Database::connect()->selectBaseQuery("SELECT `called` FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); 
                if(!is_null($is_done)){
                    $json_arr['status'] = 3; // done
                } 
                else {
                    $current_datetime = Database::connect()->selectBaseQuery("SELECT NOW()"); 
                    $current_user = $_SESSION['user']['id']; 
                    
                    $update_query = "UPDATE `source_follow_ups` SET `called` = '$current_datetime', `call_done_by` = '$current_user' WHERE `sales_id` = '$sales_id'"; 
                    $update = Database::connect()->updateBaseQuery($update_query); 

                    if($update > 0){ 
                        $json_arr['status'] = 0; // success
                    } 
                    else { 
                        $json_arr['status'] = 1; // failed 
                    }
                }
            } 
        } 
        else {
            if($type == "sms"){
                $is_done = Database::connect()->selectBaseQuery("SELECT `sms` FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); 
                if($is_done != ""){
                    $json_arr['status'] = 3; // done
                } 
                else {
                    $sms_status = $this->sendMessage($phone); 

                    if($sms_status == 0){
                        $current_datetime = Database::connect()->selectBaseQuery("SELECT NOW()"); 
                        $current_user = $_SESSION['user']['id']; 

                        $insert_query = "INSERT INTO `source_follow_ups`(`sales_id`, `sms`, `sms_done_by`) VALUES('$sales_id', '$current_datetime', '$current_user')"; 
                        $insert = Database::connect()->updateBaseQuery($insert_query); 

                        if($insert > 0){
                            $json_arr['status'] = 0; // success 
                        } 
                        else {
                            $json_arr['status'] = 1; // database failed 
                        }
                    } 
                    else {
                        $json_arr['status'] = 2; // sms failed 
                    }
                }
            } 
            else { // call
                // echo "SELECT `called` FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"; exit; 
                $is_done = Database::connect()->selectBaseQuery("SELECT `called` FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); 
                if($is_done != ""){
                    $json_arr['status'] = 3; // done
                } 
                else {
                    $current_datetime = Database::connect()->selectBaseQuery("SELECT NOW()"); 
                    $current_user = $_SESSION['user']['id']; 

                    $insert_query = "INSERT INTO `source_follow_ups`(`sales_id`, `called`, `call_done_by`) VALUES('$sales_id', '$current_datetime', '$current_user')"; 
                    $insert = Database::connect()->updateBaseQuery($insert_query); 

                    if($insert > 0){
                        $json_arr['status'] = 0; // success 
                    } 
                    else { 
                        $json_arr['status'] = 1; // failed 
                    }
                } 
            }
        } 
        // print_r($json_arr); exit; 

        return $json_arr; 
    } 

    public function remarks($array_data) { 
        $sales_id = $array_data['sales_id']; 
        $remarks = $array_data['remarks']; 
        $json_arr = array(); 
        
        $is_existing = Database::connect()->selectBaseQuery("SELECT COUNT(1) FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); 
        
        if($is_existing > 0){ 
            $current_datetime = Database::connect()->selectBaseQuery("SELECT DATE_FORMAT((SELECT NOW()), '%b %d, %Y / %h:%i:%s %p')"); 
            $current_user = $_SESSION['user']['first_name'] . " " . $_SESSION['user']['last_name'] . " [" . $_SESSION['user']['id'] . "] "; 
            $remarks_last_update = $current_user . $current_datetime; 
            $query = "UPDATE `source_follow_ups` SET `remarks` = '$remarks', `remarks_last_update` = '$remarks_last_update' WHERE `sales_id` = '$sales_id'"; 
            $update = Database::connect()->updateBaseQuery($query); 

            if($update > 0){
                $json_arr['status'] = 0; // update success 
            } 
            else {
                $json_arr['status'] = 1; // update failed 
            }
        } 
        else { // not existing
            $current_datetime = Database::connect()->selectBaseQuery("SELECT DATE_FORMAT((SELECT NOW()), '%b %d, %Y / %h:%i:%s %p')");
            $current_user = $_SESSION['user']['first_name'] . " " . $_SESSION['user']['last_name'] . " [" . $_SESSION['user']['id'] . "] "; 
            $remarks_last_update = $current_user . $current_datetime; 

            $query = "INSERT INTO `source_follow_ups`(`sales_id`, `remarks`, `remarks_last_update`, `status`) 
                        VALUES('$sales_id', '$remarks', '$remarks_last_update', 1)"; // echo $query; exit; 
            $insert = Database::connect()->updateBaseQuery($query); 
            
            if($insert > 0){ 
                $json_arr['status'] = 0; // insert success 
            } 
            else {
                $json_arr['status'] = 1; // insert failed 
            }
        } 

        return $json_arr; 
    } 

    public function sendMessage($phone) { 
        $query = "SELECT `message_header`, `message_body`, `message_footer` FROM `auto_txt_blast` WHERE `id` = 5"; 
        $message_contents = Database::connect()->runBaseQuery($query); 

        $header = ""; $body = ""; $footer = ""; 
        foreach($message_contents AS $row){
            $header = $row['message_header']; 
            $body = $row['message_body']; 
            $footer = $row['message_footer']; 
        } 
        $full_message = $header . "\n\n" . $body . "\n\n" . $footer;

        $sms_data['message'] = $full_message; 
        $sms_data['mobile'] = $phone; 

        $sms_response = Sms::sendSms($sms_data);
        $status = null;

        if($sms_response == 0){ // success
            $status = 0;
        } 
        else {
            $status = 1;
        }

        return $status; 
    } 

    public function loadRemarks($array_data) {
        $sales_id = $array_data['sales_id']; 
        $json_arr = array(); 

        $is_existing = Database::connect()->selectBaseQuery("SELECT COUNT(1) FROM `source_follow_ups` WHERE `sales_id` = '$sales_id'"); 
        if($is_existing > 0){ 
            $query = "SELECT `remarks_last_update` FROM `source_follow_ups` WHERE `sales_id` = $sales_id"; 
            $remarks_log = Database::connect()->selectBaseQuery($query); 

            if(!is_null($remarks_log)){
                $json_arr['status'] = 0; // goods
                $json_arr['remarks_log'] = $remarks_log; 
                $json_arr['remarks'] = Database::connect()->selectBaseQuery("SELECT `remarks` FROM `source_follow_ups` WHERE `sales_id` = $sales_id"); 
            } 
            else {
                $json_arr['status'] = 1; // not good
            }
        } 
        else {
            $json_arr['status'] = 1; // not good
        }

        return $json_arr; 
    }
}