EVOLUTION-NINJA

Edit File: Report.php

<?php
namespace App\Controllers;

class Report extends BaseController{
    
 public function __construct()  
    { 
    
         date_default_timezone_set('Asia/Kolkata');
          $this->db = \Config\Database::connect(); 
}     
    
public function getFilteredCustomerDetails()
{
   
    $fromDate = $this->request->getGet('fromDate');
    $toDate = $this->request->getGet('toDate');
    $scheme = $this->request->getGet('scheme');

$db = \Config\Database::connect();
    $builder = $db->table('Customers');

$builder->join('user', 'user.id = Customers.promoter_id', 'left');
    $builder->join('schemes', 'schemes.id = Customers.scheme', 'left');

if ($scheme) {
        $builder->where('Customers.scheme', $scheme);
    }

if ($fromDate && $toDate) {
        $fromDate = date('Y-m-d', strtotime($fromDate));
        $toDate = date('Y-m-d', strtotime($toDate));

$builder->where('Customers.created_at >=', $fromDate . ' 00:00:00')
                ->where('Customers.created_at <=', $toDate . ' 23:59:59');
    }

if ($scheme || ($fromDate && $toDate)) {

$builder->select('
            Customers.id, 
            Customers.name, 
            Customers.user_id, 
            Customers.mobile, 
            schemes.scheme, 
            Customers.card_no, 
            Customers.created_at, 
            user.name as promoter_name
        ');

$query = $builder->get();
        $customers = $query->getResultArray();

return $this->response->setJSON($customers);
    } else {
     
        return $this->response->setJSON([
            'success' => false,
            'message' => 'Please apply at least one filter to fetch data.',
            'data' => []
        ]);
    }
}
    
    
 public function get_initialPins_report() {
    $fromDate = $this->request->getPost('fromDate');
    $toDate = $this->request->getPost('toDate');
    $scheme=$this->request->getPost('scheme');

$builder = $this->db->table('promoter_requests');
    $builder->select("
        DATE_FORMAT(promoter_requests.created_at, '%b - %Y') as month_year,
        schemes.scheme as scheme_name,
        SUM(promoter_requests.total_slots) as total_slots
    ");
    $builder->join('schemes', 'promoter_requests.scheme = schemes.id');

if ($fromDate) {
        $builder->where('promoter_requests.created_at >=', $fromDate);
    }
    if ($toDate) {
        $builder->where('promoter_requests.created_at <=', $toDate);
    }

if ($scheme) {
        $builder->where('promoter_requests.scheme',$scheme);
    }
    
    $builder->groupBy(["DATE_FORMAT(promoter_requests.created_at, '%b - %Y')", "promoter_requests.scheme"]);

$query = $builder->get();
    $data = $query->getResult();

return $this->response->setJSON(['data' => $data]);
}  
    
    
    
 public function get_repayment_report() {
    $fromDate = $this->request->getPost('fromDate');
    $toDate = $this->request->getPost('toDate');
    $scheme=$this->request->getPost('scheme');

$builder = $this->db->table('promoter_repayment_requests');
    $builder->select("
        DATE_FORMAT(promoter_repayment_requests.created_at, '%b - %Y') as month_year,
        schemes.scheme as scheme_name,
        SUM(promoter_repayment_requests.total_slots) as total_slots
    ");
    $builder->join('schemes', 'promoter_repayment_requests.scheme = schemes.id');

if ($fromDate) {
        $builder->where('promoter_repayment_requests.created_at >=', $fromDate);
    }
    if ($toDate) {
        $builder->where('promoter_repayment_requests.created_at <=', $toDate);
    }

if ($scheme) {
        $builder->where('promoter_repayment_requests.scheme',$scheme);
    }

$builder->groupBy(["DATE_FORMAT(promoter_repayment_requests.created_at, '%b - %Y')", "promoter_repayment_requests.scheme"]);

$query = $builder->get();
    $data = $query->getResult();

return $this->response->setJSON(['data' => $data]);
}
 
    
    
//   public function getWithdrawReportData()
// {
   
//     $fromDate = $this->request->getGet('fromDate');
//     $toDate = $this->request->getGet('toDate');
//     $scheme = $this->request->getGet('scheme');

//     $db = \Config\Database::connect();
//     $builder = $db->table('promoter_payments');

//     $builder->join('user', 'user.id = promoter_payments.promoter_id', 'left');
//     $builder->join('schemes', 'schemes.id = promoter_payments.scheme', 'left');

//     if ($scheme) {
//         $builder->where('promoter_payments.scheme', $scheme);
//     }

//     if ($fromDate && $toDate) {
//         $fromDate = date('Y-m-d', strtotime($fromDate));
//         $toDate = date('Y-m-d', strtotime($toDate));

//         $builder->where('promoter_payments.created_at >=', $fromDate . ' 00:00:00')
//                 ->where('promoter_payments.created_at <=', $toDate . ' 23:59:59');
//     }

//     if ($scheme || ($fromDate && $toDate)) {

//         $builder->select('
//             promoter_payments.id, 
//             promoter_payments.user_id, 
//             promoter_payments.amount,
//             promoter_payments.created_at, 
//             user.name as promoter_name,
//             user.promoter_id as promoter_id,
//             schemes.scheme, 
//         ');

//         $query = $builder->get();
//         $customers = $query->getResultArray();

//         return $this->response->setJSON($customers);
//     } else {
     
//         return $this->response->setJSON([
//             'success' => false,
//             'message' => 'Please apply at least one filter to fetch data.',
//             'data' => []
//         ]);
//     }
// }
  
    public function getWithdrawReportData()
{
   
    $fromDate = $this->request->getGet('fromDate');
    $toDate = $this->request->getGet('toDate');
    $scheme = $this->request->getGet('scheme');

$db = \Config\Database::connect();
    $builder = $db->table('withdraw_requests');

$builder->join('user', 'user.id = withdraw_requests.promoter_id', 'left');
    $builder->join('schemes', 'schemes.id = withdraw_requests.scheme', 'left');

if ($scheme) {
        $builder->where('withdraw_requests.scheme', $scheme);
    }

if ($fromDate && $toDate) {
        $fromDate = date('Y-m-d', strtotime($fromDate));
        $toDate = date('Y-m-d', strtotime($toDate));

$builder->where('withdraw_requests.created_at >=', $fromDate . ' 00:00:00')
                ->where('withdraw_requests.created_at <=', $toDate . ' 23:59:59');
    }

if ($scheme || ($fromDate && $toDate)) {

$builder->select('
            withdraw_requests.id, 
        
            withdraw_requests.amount,
            withdraw_requests.created_at, 
            user.name as promoter_name,
            user.promoter_id as promoter_id,
            schemes.scheme, 
        ');

$query = $builder->get();
        $customers = $query->getResultArray();

return $this->response->setJSON($customers);
    } else {
     
        return $this->response->setJSON([
            'success' => false,
            'message' => 'Please apply at least one filter to fetch data.',
            'data' => []
        ]);
    }
}
     
    
  
public function getpaymentReportData()
{
    $fromDate = $this->request->getGet('fromDate');
    $toDate = $this->request->getGet('toDate');
    $scheme = $this->request->getGet('scheme');
    $user = $this->request->getGet('user');

$db = \Config\Database::connect();
    $builder = $db->table('payments');
      $builder->select('
            payments.id,
            payments.amount,
            payments.created_at,
            Customers.name,
            Customers.user_id,
            schemes.scheme as scheme_name
        ');

$builder->join('Customers', 'Customers.user_id = payments.user_id', 'left');
    $builder->join('schemes', 'schemes.id = payments.scheme', 'left');

if (!empty($scheme)) {
        $builder->where('payments.scheme', $scheme);
    }

if (!empty($user)) {
        $builder->where('payments.user_id', $user);
    }

if (!empty($fromDate) && !empty($toDate)) {
        $fromDate = date('Y-m-d', strtotime($fromDate));
        $toDate = date('Y-m-d', strtotime($toDate));

if ($fromDate && $toDate && strtotime($fromDate) && strtotime($toDate)) {
            $builder->where('payments.created_at >=', $fromDate . ' 00:00:00')
                    ->where('payments.created_at <=', $toDate . ' 23:59:59');
        } else {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Invalid date format.',
                'data' => []
            ]);
        }
    }

if (!empty($scheme) || (!empty($fromDate) && !empty($toDate)) || !empty($user)) {
        $builder->select('
            payments.id,
            payments.amount,
            payments.created_at,
            Customers.name,
            Customers.user_id,
            schemes.scheme as scheme_name
        ');

$query = $builder->get();
        $customers = $query->getResultArray();

return $this->response->setJSON([
            'success' => true,
            'data' => $customers
        ]);
    } else {
        return $this->response->setJSON([
            'success' => false,
            'message' => 'Please apply at least one filter to fetch data.',
            'data' => []
        ]);
    }
}

public function get_initialPins_approved_details() {

$toDate = date('Y-m-d');
$fromDate = date('Y-m-d', strtotime('-2 months', strtotime($toDate)));
        
        
    $fromDate = $this->request->getPost('fromDate');
    $toDate = $this->request->getPost('toDate');
    $scheme = $this->request->getPost('scheme');

$builder = $this->db->table('promoter_requests');
    $builder->select("
        promoter_requests.promoter_id,
        promoter_requests.request_id,
        promoter_requests.request_to,
        promoter_requests.total_slots,
        promoter_requests.used_slots,
        promoter_requests.scheme,
        promoter_requests.created_at,
        promoter_requests.status,
        schemes.id AS scheme_id,
        schemes.scheme AS scheme_name,
        user.id,user.promoter_id as promoter,user.name
    ");
    $builder->join('schemes', 'promoter_requests.scheme = schemes.id');
    $builder->join('user', 'promoter_requests.promoter_id = user.id');
    $builder->where('promoter_requests.status', 'approved');

if ($fromDate) {
        $builder->where('promoter_requests.created_at >=', $fromDate);
    }
    if ($toDate) {
        $builder->where('promoter_requests.created_at <=', $toDate);
    }
    if ($scheme) {
        $builder->where('promoter_requests.scheme', $scheme);
    }

$query = $builder->get();
    $data = $query->getResult();

return $this->response->setJSON(['data' => $data]);
}
 
   
    
    
    
          
public function get_repayment_approvedPins() {

$toDate = date('Y-m-d');
$fromDate = date('Y-m-d', strtotime('-2 months', strtotime($toDate)));

$fromDate = $this->request->getPost('fromDate');
    $toDate = $this->request->getPost('toDate');
    $scheme=$this->request->getPost('scheme');

$builder = $this->db->table('promoter_repayment_requests');
    $builder->select("
        promoter_repayment_requests.promoter_id,
        promoter_repayment_requests.request_id,
       
        promoter_repayment_requests.total_slots,
        promoter_repayment_requests.used_slots,
        promoter_repayment_requests.scheme,
        promoter_repayment_requests.created_at,
        promoter_repayment_requests.status,
        schemes.id AS scheme_id,
        schemes.scheme AS scheme_name,user.id,user.promoter_id as promoter,user.name
    ");
    $builder->join('schemes', 'promoter_repayment_requests.scheme = schemes.id');
    $builder->join('user', 'promoter_repayment_requests.promoter_id = user.id');
   $builder->where('promoter_repayment_requests.status','Approved');
 
    if ($fromDate) {
        $builder->where('promoter_repayment_requests.created_at >=', $fromDate);
    }
    if ($toDate) {
        $builder->where('promoter_repayment_requests.created_at <=', $toDate);
    }

if ($scheme) {
        $builder->where('promoter_repayment_requests.scheme',$scheme);
    }
    
    // $builder->groupBy(["DATE_FORMAT(promoter_repayment_requests.created_at, '%b - %Y')", "promoter_repayment_requests.scheme"]);

$query = $builder->get();
    $data = $query->getResult();

return $this->response->setJSON(['data' => $data]);
}

public function get_promoters_report() {
    $toDate = date('Y-m-d');
    $fromDate = date('Y-m-d', strtotime('-1 months', strtotime($toDate)));

$fromDate = $this->request->getGet('fromDate') ?? $fromDate;
    $toDate = $this->request->getGet('toDate') ?? $toDate;
    $scheme = $this->request->getGet('scheme');
    $user = $this->request->getGet('user');
    
    // print_r($scheme);die();

$builder = $this->db->table('payments');
    $builder->select("
        payments.promoter_id,
        payments.user_id,
        user.name AS user_name,
        promoter.name AS promoter_name,
        promoter.promoter_id AS promoter,
        payments.amount,
        schemes.scheme AS scheme_name
    ");
    $builder->join('schemes', 'payments.scheme = schemes.id', 'left');
    $builder->join('user', 'payments.user_id = user.id', 'left'); // join to get user's name
    $builder->join('user as promoter', 'payments.promoter_id = promoter.id', 'left'); // join again to get promoter name

if ($fromDate) {
        $builder->where('payments.created_at >=', $fromDate);
    }
    if ($toDate) {
        $builder->where('payments.created_at <=', $toDate);
    }
    if ($scheme) {
        $builder->where('payments.scheme', $scheme);
    }
    if ($user) {
        $builder->where('payments.promoter_id', $user);
    }

// No groupBy needed now
    $query = $builder->get();
    $data = $query->getResult();

return $this->response->setJSON(['data' => $data]);
}

}?>