<?php namespace App\Models;

use CodeIgniter\Model;
use App\Models\Crud;

class Leads extends Model
{
    var $data = array();
    protected $table = 'leads';

    public function __construct()
    {
        $MainModel = new Main();
        $this->data = $MainModel->DefaultVariable();
    }

    public function Records($type)
    {
        $db = db_connect();
        $db->transStart();
        $data = $this->data;
        //print_r($data['session']);
        $SQLWhere = '';
        if ($data['session']['user_type'] == 'user') {
            //$SQLWhere = " AND `deo_user` = " . $data['session']['id'];
        }

        if ($type != 'all') {
            $SQL = "SELECT * FROM `leads` WHERE `status` != 'final' " . $SQLWhere . " AND `clients` = '" . $type . "' ORDER BY `status`, `leads`.`id` DESC ";
        } else {
            $SQL = "SELECT * FROM `leads` WHERE 1 = 1 " . $SQLWhere . " ORDER BY `status`, `leads`.`id` DESC ";
        }

        $query = $db->query($SQL);
        $leads = $query->getResultArray();
        $db->transComplete();
        //print_r($leads);
        return $leads;
    }

    public function Record($id)
    {
        $db = db_connect();
        $db->transStart();
        $SQL = "SELECT * FROM `leads` WHERE `id` = " . $id;

        $query = $db->query($SQL);
        $leads = $query->getRowArray();
        $db->transComplete();
        //print_r($leads);
        return $leads;
    }

    public function DashboardStats()
    {
        $db = db_connect();
        $db->transStart();
        $SQL = "SELECT DISTINCT `clients`, COUNT(`id`) as `TotalLeads` , 
        (SELECT COUNT(`L2`.`id`) FROM `leads` as `L2` WHERE `L2`.`status`= 'final' and `L2`.`clients` = `leads`.`clients` and `L2`.`system_datetime` > '" . date("Y-m-d") . " 00:00:01' ) as `FinalLeads`
        FROM `leads` 
        WHERE `system_datetime` > '" . date("Y-m-d") . " 00:00:01'
        GROUP BY `clients`";

        $query = $db->query($SQL);
        $leads = $query->getResultArray();
        $db->transComplete();
        $Final = array();
        foreach ($leads as $lead) {
            $Final[$lead['clients']]['total'] = $lead['TotalLeads'];
            $Final[$lead['clients']]['final'] = $lead['FinalLeads'];
        }

        return $Final;
    }

    public function DashboardGraph()
    {
        $db = db_connect();
        $db->transStart();
        $SQL = "SELECT DISTINCT DATE_FORMAT(`system_datetime`, '%D-%M') as `Date`, COUNT(`id`) as `TotalLeads` FROM `leads` 
        WHERE `system_datetime` > '" . date("Y-m-1") . " 00:00:01'
        GROUP BY DATE_FORMAT(`system_datetime`, '%D-%M')
        ORDER BY `system_datetime`";
        $query = $db->query($SQL);
        $records = $query->getResultArray();
        //print_r($records);
        $db->transComplete();
        $Heads = $Stats = array();
        foreach ($records as $record) {
            $Heads[] = $record['Date'];
            $Stats[] = $record['TotalLeads'];
        }
        return array('heads'=>$Heads, 'stats'=>$Stats);
    }

    public function AssingLeadsDEO()
    {
        $db = db_connect();
        $db->transStart();
        $SQL = "SELECT `id` FROM `users`  
        WHERE `lastlogin_at` > '2022-06-15 00:00:01'
        ORDER BY RAND()
        LIMIT 1";
        $query = $db->query($SQL);
        $record = $query->getRowArray();
        //print_r($records);
        $db->transComplete();
        return $record['id'];

    }


}

