<?php namespace App\Models;

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

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

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

    public function Records()
    {
        $db = db_connect();
        $db->transStart();
        $SQL = "SELECT * FROM `users` ORDER BY `users`.`full_name` ASC";

        $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 `users` 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;
    }


}

