EVOLUTION-NINJA

Edit File: Leadsreport.php

<?php

namespace App\Controllers;
use App\Models\FollowUpModel;

class Leadsreport extends BaseController
{
    public function index(){
        $id = session()->get('id');
        if($id){
        return view('reports/leadsreport');
        }else{
            return redirect()->to('/');
        }
    }
    // public function leads_report()
    // {
    //     // $dr_id = session()->get('dr_id');
    //     $db = \Config\Database::connect();
    
    //     // Get parameters from jqGrid
    //     $page = $this->request->getVar('page');
    //     $limit = $this->request->getVar('rows');
    //     $sidx = $this->request->getVar('sidx');
    //     $sord = $this->request->getVar('sord');
    //     $start_date = $this->request->getVar('start_date');
    //     $end_date = $this->request->getVar('end_date');
    //     $searchField = $this->request->getVar('searchField');
    //     $searchString = $this->request->getVar('searchString');
    //     $caseId = $this->request->getVar('case_id');
    //     $page = $page ? $page : 1;
    //     $limit = $limit ? $limit : 10;
    //     $sidx = $sidx ? $sidx : 'Date';
    //     $sord = $sord ? $sord : 'asc';
    
    //     // Convert the date format from "dd-mm-yyyy" to "yyyy-mm-dd"
    //     if ($start_date) {
    //         $start_date = date('Y-m-d', strtotime($start_date));
    //     }
    //     if ($end_date) {
    //         $end_date = date('Y-m-d', strtotime($end_date));
    //     }
    
    //     $offset = ($page - 1) * $limit;
    //     $builder = $db->table('leads');
    //     if ($start_date && $end_date) {
    //         $builder->where("Date BETWEEN '$start_date' AND '$end_date'");
    //     }
    //     if ($searchField && $searchString) {
    //         $builder->like($searchField, $searchString);
    //     }
    //     if ($caseId) {
    //         $builder->where('id', $caseId);
    //     }
    //     // if ($dr_id) {
    //     //     $builder->where("dr_id",$dr_id);
    //     // }
    //     $builder->orderBy($sidx, $sord);
    //     $builder->limit($limit, $offset);
    //     $query = $builder->get();
    //     $data = $query->getResultArray();
    //     $totalRecords = $db->table('leads')->countAllResults();
    //     $totalPages = ceil($totalRecords / $limit);
    //     $response = [
    //         'page' => $page,
    //         'total' => $totalPages,
    //         'records' => $totalRecords,
    //         'rows' => $data,
    //     ];
    
    //     return $this->response->setJSON($response);
    // }
    public function leads_report()
    {
        $db = \Config\Database::connect();
    
        $toDate = date('Y-m-d');
        $fromDate = date('Y-m-d', strtotime('-3 months', strtotime($toDate)));
    
        $fromdate = $this->request->getPost('fromdate');
        $todate = $this->request->getPost('todate');
        $converted = $this->request->getPost('converted');
        
        if (!$fromdate || !$todate) {
            $fromdate = $fromDate;
            $todate = $toDate;
        }
        $data['from']=$fromdate;
        $data['to']=$todate;

//table
        $builder = $db->table('leads');
        if(isset($converted)){
            $builder->where('converted', $converted );
        }
        $builder->where('date >=', $fromdate );
        $builder->where('date <=', $todate );
        $table = $builder->get()->getResultArray();

$converted=$builder->where('converted',0)->countAllResults();
        $unconverted=$builder->where('converted',1)->countAllResults();
        $data['unconverted']=$unconverted;
        $data['converted']=$converted;
        $data['total']=$unconverted+$converted;

// $query=$db->getLastQuery();
        // echo $query;die();

if ($table) {
            return $this->response->setJSON(['result' => 1,'data'=>$data, 'table'=>$table]);
        } else {
            return $this->response->setJSON(['result' => 0, 'message' => 'No Record Found']);
        }
    }

public function followup_report(){
        $id = session()->get('id');
        if($id){
        return view('reports/followUpReport');
        }else{
            return redirect()->to('/');
        }
    }

public function followup_date()
    {
        $start_date = $this->request->getGet('start_date');
        $end_date = $this->request->getGet('end_date');
        $case_id = $this->request->getGet('case_id');
    
        $followUpModel = new FollowUpModel();
    
        $query = $followUpModel->select('*');
    
        if ($start_date) {
            $query->where('today_date >=', date('Y-m-d', strtotime($start_date)));
        }
    
        if ($end_date) {
            $query->where('today_date <=', date('Y-m-d', strtotime($end_date)));
        }
    
        if ($case_id) {
            $query->where('id', $case_id);
        }
    
        $data = $query->findAll();
    
        return $this->response->setJSON($data);
    }
    
    
    public function get_leads_count()
{
    $followUpModel = new FollowUpModel();
    
    $startDate = $this->request->getGet('start_date');
    $endDate = $this->request->getGet('end_date');
    $caseId = $this->request->getGet('case_id');
   
    $query = $followUpModel->select('leads_id, COUNT(*) as count');

if ($startDate && $endDate) {
        $query->where('today_date >=', date('Y-m-d', strtotime($startDate)))
              ->where('today_date <=', date('Y-m-d', strtotime($endDate)));
    }

if ($caseId) {
        $query->where('id', $caseId);
    }
    $data = $query->groupBy('leads_id')->findAll();
    return $this->response->setJSON($data);
 }
}