EVOLUTION-NINJA

Edit File: Promoter.php

<?php
namespace App\Controllers;

class Promoter extends BaseController{

public function __construct()  
    { 
    
         date_default_timezone_set('Asia/Kolkata');
          $this->db = \Config\Database::connect(); 
    }

public function my_pin_request(){

$promoterId = session()->get('id');
            // $stockist = $this->request->getPost('pin_stockist');
            $no_of_pins = $this->request->getPost('no_of_pin');
            $scheme = $this->request->getPost('scheme');
            $payment_status = $this->request->getPost('payment_status');
            $remarks = $this->request->getPost('remarks');
            $requestId = "RI" . str_pad(rand(10000000, 99999999), 8, '0', STR_PAD_LEFT);

$builder=$this->db->table('promoter_requests');
   
    $data=[
        // "stockist"=>$stockist,
        "scheme"=>$scheme,
        "promoter_id"=>$promoterId,
        "request_id"=>$requestId,
        "total_slots"=>$no_of_pins,
        "payment_status"=>$payment_status ,
        "remarks"=>$remarks,
        "created_at" => date('Y-m-d H:i:s'),
        'status' => 'Pending'
        
        ];
        
        
       if( $builder->insert($data)){
        return $this->response->setJSON(['result' => 1, 'message' => 'insert successfully']);
           
       }
       else{
      return $this->response->setJSON(['result' => 1, 'message' => 'Failed to add']);
       }
}

public function create_customer()
{

$this->db->transStart();
    
    $promoterId = session()->get('id');
    // $refer = $this->request->getPost('refer');
    $scheme = $this->request->getPost('scheme');
    $name = $this->request->getPost('name');
    $email = $this->request->getPost('email');
    $mobile = $this->request->getPost('mobile');
    $state = $this->request->getPost('state');
    $city = $this->request->getPost('city');
    $pincode = $this->request->getPost('pincode');
    $address = $this->request->getPost('address');
    $card_no = $this->request->getPost('card_no');

$customer = $this->db->table('Customers')
                     ->where('card_no', $card_no)
                     ->get()
                     ->getRow(); // Get a single row

if ($customer) {
    return $this->response->setJSON(['result' => '0', 'message' => 'Card number already exists']);
}

$request = $this->db->table('promoter_requests')
        ->where('promoter_id', $promoterId)
        ->where('status', 'Approved')
        ->where('used_slots < total_slots')
        ->orderBy('created_at', 'ASC')
        ->get()
        ->getRow();

if (!$request) {
        return $this->response->setJSON(['result' => 0, 'message' => 'No available request ID. Please contact the admin.']);
    }

$requestId = $request->request_id;
    $userId = 'UID' . rand(1000, 9999) . '-' . $name;
    $password = bin2hex(random_bytes(4));

$data = [
        'user_id' => $userId,
        'scheme' => $scheme,
        'name' => $name,
        'email' => $email,
        'mobile' => $mobile,
        'state' => $state,
        'city' => $city,
        'pincode' => $pincode,
        'address' => $address,
        'card_no' => $card_no,
        'request_id' => $requestId,
        'promoter_id' => $promoterId,
        'password' => $password,
        'created_at' => date('Y-m-d H:i:s'),
    ];
    
    $data1 = [
        'user_id' => $userId,
        'scheme' => $scheme,
        'promoter_id' => $promoterId,
        'amount'=>1000,
        'remarks' => 'test',
        'payment_date' => date('Y-m-d'),
        'end_date' => date('Y-m-d', strtotime('+12 months')),
        'created_at' => date('Y-m-d H:i:s'),
    ];
    
    $this->db->table('Customers')->insert($data);
    $this->db->table('payments')->insert($data1);

$this->db->table('promoter_requests')
        ->where('id', $request->id)
        ->update(['used_slots' => $request->used_slots + 1]);

$totalusersRefered = $this->db->table('promoter_payments')
        ->where('promoter_id', $promoterId)
        ->countAllResults();

$initialBonus = 0;

if ($totalusersRefered == 0 || $totalusersRefered <= 25) {
        $initialBonus = 250;
    } elseif ($totalusersRefered > 25 && $totalusersRefered <= 50) {
        $initialBonus = 300;
    } elseif ($totalusersRefered > 50 && $totalusersRefered <= 150) {
        $initialBonus = 350;
    } elseif ($totalusersRefered > 150 && $totalusersRefered <= 250) {
        $initialBonus = 400;
    } elseif ($totalusersRefered > 250 && $totalusersRefered <= 350) {
        $initialBonus = 450;
    } elseif ($totalusersRefered > 350 && $totalusersRefered <= 500) {
        $initialBonus = 500;
    }

$presentPromoterPayment = [
        'promoter_id' => $promoterId,
        'user_id' => $userId,
        'amount' => $initialBonus,
        'scheme' => $scheme,
        'description' => 'Initial payment for new customer registration by present promoter',
        'created_at' => date('Y-m-d H:i:s'),
    ];
    
    //   print_r($presentPromoterPayment);die();
    
    $this->db->table('promoter_payments')->insert($presentPromoterPayment);
  
   $this->db->transComplete();
   
    if ($this->db->transStatus() === false) {
        return $this->response->setJSON(['result' => 0, 'message' => 'Failed to create customer. Please try again.']);
    }

return $this->response->setJSON(['result' => 1, 'message' => 'Customer created successfully.']);
}

public function customers_detail_list()
{
    
    $promoterId = session()->get('id');
    try {
        $builder = $this->db->table('Customers');
        $builder->select('Customers.*, schemes.id as scheme_id , schemes.scheme');
        $builder->join('schemes', 'Customers.scheme = schemes.id', 'left');
        $builder->where('Customers.promoter_id',$promoterId );
        $builder->orderBy('Customers.created_at', 'DESC');
        $query = $builder->get();

$data = $query->getResult();
// print_r($data);die();
        if ($data) {
            return $this->response->setJSON($data);
        } else {
            return $this->response->setJSON(['result' => 0, 'message' => 'No data found']);
        }
    } catch (\Exception $e) {
        return $this->response->setJSON(['result' => 0, 'message' => $e->getMessage()]);
    }
}

public function get_customer_details(){
    
     $customerId = $this->request->getGet('id');
    //  print_r($customerId);die();

$customer = $this->db->table('Customers')
                         ->where('id', $customerId)
                         ->get()
                         ->getRow();

if ($customer) {

return $this->response->setJSON([
            'result' => 1,
            'data' => $customer
        ]);
    } else {
        return $this->response->setJSON([
            'result' => 0,
            'message' => 'Customer not found.'
        ]);
    }
}

public function delete_customer_details()
{
    $customerId = $this->request->getPost('id'); // Use getPost for POST requests

if (!$customerId) {
        return $this->response->setJSON([
            'result' => 0,
            'message' => 'Customer ID is required.'
        ]);
    }

// Check if the customer exists
    $customer = $this->db->table('Customers')
                         ->where('id', $customerId)
                         ->get()
                         ->getRow();

if (!$customer) {
        return $this->response->setJSON([
            'result' => 0,
            'message' => 'Customer not found.'
        ]);
    }

// Attempt to delete the customer
    $deleted = $this->db->table('Customers')
                        ->where('id', $customerId)
                        ->delete();

if ($deleted) {
        return $this->response->setJSON([
            'result' => 1,
            'message' => 'Customer deleted successfully.'
        ]);
    } else {
        return $this->response->setJSON([
            'result' => 0,
            'message' => 'Failed to delete customer.'
        ]);
    }
}

public function pin_request_list(){
       $promoterId = session()->get('id');
       
    try {
        $builder = $this->db->table('promoter_requests');
        
  
        $builder->select('promoter_requests.*,schemes.id,schemes.scheme');
         $builder->join('schemes','schemes.id = promoter_requests.scheme','left');
        
        //$builder->join('Customers', 'Customers.request_id = promoter_requests.request_id','inner');
   
        $builder->where('promoter_requests.promoter_id',$promoterId );
     
        //$builder->groupBy('promoter_requests.request_id');
  
        $query = $builder->get();

$data = $query->getResult();
//   print_r($data);die();
        if ($data) {
    
            return $this->response->setJSON($data);
          
        } else {
           
            return $this->response->setJSON(['result' => 0, 'message' => 'No data available']);
        }
    } catch (\Exception $e) {
     
        return $this->response->setJSON(['result' => 0, 'message' => 'Error: ' . $e->getMessage()]);
    }
}

//   written

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, 
            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 getCustomerDetails()
    {
  
        $customerId = $this->request->getGet('id');

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

$query = $builder->getWhere(['id' => $customerId]);
        $customer = $query->getRowArray();

if ($customer) {
            return $this->response->setJSON(['result' => 1, 'data' => $customer]);
        } else {
            return $this->response->setJSON(['result' => 0, 'message' => 'Customer not found']);
        }
 }
    
    
    
    
public function fetch_scheme(){
        
    $builder = $this->db->table('schemes'); 
        
    $builder->select('id ,scheme'); 
    $builder->where('status','active'); 
    $query = $builder->get();
    $schemes= $query->getResultArray();

if ($schemes) {
        return $this->response->setJSON($schemes);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'No schemes found']);
    }
        
 }
    
    public function fetch_promoter(){
        
    $builder = $this->db->table('user'); 
        
    $builder->select('id ,promoter_id'); 
    $query = $builder->get();
    $promoters= $query->getResultArray();

if ($promoters) {
        return $this->response->setJSON($promoters);
    } else {
        return $this->response->setJSON(['result' => 0, 'message' => 'No schemes found']);
    }
        
    }

// public function promoter_details() {
//     $promoter_id = session()->get('id');

//         print_r($promoter_id);die();
        
//     log_message('debug', 'Promoter ID: ' . $promoter_id);

//     if (!$promoter_id) {
//         return $this->response->setJSON(['error' => 'Promoter ID not found in session']);
//     }

//     $builder = $this->db->table('promoter_payments');

//     $builder->select('user.*, 
//                      user.id as user_id, 
//                      promoter_payments.promoter_id, 
//                      SUM(promoter_payments.amount) as total_earnings,
//                      (SELECT SUM(amount) FROM payments WHERE payments.promoter_id = promoter_payments.promoter_id) as total_amount,
//                      user_details.city, 
//                      user.phone, 
//                      user.email, 
                     
//                      (SELECT COUNT(DISTINCT Customers.id) FROM Customers WHERE Customers.promoter_id = promoter_payments.promoter_id) as total_customers, 
//                      (SELECT COUNT(DISTINCT promoter_repayment_requests.id) FROM promoter_repayment_requests WHERE promoter_repayment_requests.promoter_id = promoter_payments.promoter_id) as total_request, 
//                      (SELECT COUNT(DISTINCT promoter_requests.id) FROM promoter_requests WHERE promoter_requests.promoter_id = promoter_payments.promoter_id) as total_initial');

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

//     $builder->where('promoter_payments.promoter_id', $promoter_id);

//     $builder->groupBy('promoter_payments.promoter_id');

//     // Fetch the data
//     $data = $builder->get()->getRowArray();

//     log_message('debug', 'Query: ' . $this->db->getLastQuery());  // Log the query
//     log_message('debug', 'Data: ' . print_r($data, true));  // Log the result data

//     // Check and return data
//     if ($data) {
//         return $this->response->setJSON($data); // Return the data
//     } else {
//         return $this->response->setJSON(['error' => 'No data found']);
//     }
// }

public function promoter_details() {
    $promoter_id = session()->get('id');

// print_r($promoter_id);
    // die();

log_message('debug', 'Promoter ID: ' . $promoter_id);

if (!$promoter_id) {
        return $this->response->setJSON(['error' => 'Promoter ID not found in session']);
    }

$builder = $this->db->table('user');

$builder->select('user.*, 
                     user.id as user_id, 
                     user_details.city, 
                     user.phone, 
                     user.email, 
                     user.role_name,
                     (SELECT SUM(amount) FROM payments WHERE payments.promoter_id = user.id) as total_amount,
                     (SELECT COUNT(DISTINCT Customers.id) FROM Customers WHERE Customers.promoter_id = user.id) as total_customers, 
                     (SELECT COUNT(DISTINCT promoter_repayment_requests.id) FROM promoter_repayment_requests WHERE promoter_repayment_requests.promoter_id = user.id) as total_request, 
                     (SELECT COUNT(DISTINCT promoter_requests.id) FROM promoter_requests WHERE promoter_requests.promoter_id = user.id) as total_initial');

$builder->join('user_details', 'user.id = user_details.user_id', 'left');

$builder->where('user.id', $promoter_id);

// Fetch the data
    $data = $builder->get()->getRowArray();
    
    // print_r($data);die();

log_message('debug', 'Query: ' . $this->db->getLastQuery()); 
    log_message('debug', 'Data: ' . print_r($data, true));

// Check and return dataCustomer-details
    if ($data) {
        return $this->response->setJSON($data);
    } else {
        return $this->response->setJSON(['error' => 'No data found']);
    }
}
  
public function withdraw_details() {
    $promoter_id = session()->get('id');

log_message('debug', 'Promoter ID: ' . $promoter_id);

if (!$promoter_id) {
        return $this->response->setJSON(['error' => 'Promoter ID not found in session']);
    }

$builder = $this->db->table('user');
    
    $builder->select('user.id as user_id, 
                      user.name, 
                      user.email, 
                      promoter_payments.amount as payment_amount, 
                      promoter_payments.promoter_id,
                      (SELECT SUM(amount) FROM promoter_payments WHERE promoter_payments.promoter_id = user.id) as total_amount');

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

$data = $builder->get()->getRowArray();

log_message('debug', 'Query: ' . $this->db->getLastQuery());
    log_message('debug', 'Data: ' . print_r($data, true));

if ($data) {
        return $this->response->setJSON($data);
    } else {
        return $this->response->setJSON(['error' => 'No data found']);
    }
}

public function withdraw(){
   $builder = $this->db->table('promoter_payments');
    $builder->select('user.*, promoter_payments.promoter_id as promoterId, promoter_payments.scheme, promoter_payments.user_id, SUM(promoter_payments.amount) as total_amount,schemes.id,schemes.scheme as scheme_name');
    $builder->join('user', 'user.id = promoter_payments.promoter_id', 'left');
    $builder->join('user_details', 'user.id = user_details.user_id', 'left');
    $builder->join('schemes', 'schemes.id = promoter_payments.scheme', 'left');
    $builder->where('promoter_payments.promoter_id', session()->get('id'));
    $builder->groupBy('promoter_payments.scheme');
    
    
    $data['payments'] = $builder->get()->getResultArray();

return view('promoter/withdraw_table', $data);
    
    
    
 
} 
   
public function withdraw_amount_request()
{
    $promoterId = $this->request->getPost('promoter_id');
    $amount = $this->request->getPost('amount');
    $scheme = $this->request->getPost('scheme');

if (!$promoterId || !$amount || !$scheme) {
        return $this->response->setJSON([
            'success' => false,
            'message' => 'Invalid data provided.'
        ]);
    }

$builder = $this->db->table('schemes');
    $builder->select('id');
    $builder->where('scheme', $scheme);
    $schemeRow = $builder->get()->getRow();

if (!$schemeRow) {
        return $this->response->setJSON([
            'success' => false,
            'message' => 'Invalid scheme provided.'
        ]);
    }

$schemeId = $schemeRow->id;

$builder = $this->db->table('user');
    $builder->select('id');
    $builder->where('promoter_id', $promoterId);
    $promoterRow = $builder->get()->getRow();

if (!$promoterRow) {
        return $this->response->setJSON([
            'success' => false,
            'message' => 'Invalid promoter provided.'
        ]);
    }

$promoter_Id = $promoterRow->id;

$builder = $this->db->table('promoter_payments');
    $builder->select('SUM(amount) as total_amount');
    $builder->where('promoter_id', $promoter_Id);
    $builder->where('scheme', $schemeId);
    $result = $builder->get()->getRow();

$totalAmount = $result ? $result->total_amount : 0;

if ($totalAmount >= $amount) {
        // Generate a unique request ID
        $requestId = 'REQ' . time() . rand(1000, 9999);

$requestBuilder = $this->db->table('withdraw_requests');
        $requestBuilder->insert([
            'request_id'  => $requestId, // Adding request ID
            'promoter_id' => $promoter_Id,
            'amount'      => $amount,
            'scheme'      => $schemeId,
            'status'      => 'Pending'
        ]);

return $this->response->setJSON([
            'success' => true,
            'message' => 'Withdrawal request sent for approval.',
            'request_id' => $requestId // Return the request ID in the response
        ]);
    }

return $this->response->setJSON([
        'success' => false,
        'message' => 'Insufficient funds or invalid promoter.'
    ]);
}

public function withdraw_request_approval()
{
  
    $builder = $this->db->table('withdraw_requests');
    $builder->select('withdraw_requests.id, withdraw_requests.promoter_id, withdraw_requests.amount,withdraw_requests.created_at, withdraw_requests.status,withdraw_requests.request_id,user.promoter_id as promoter,user.name as name, withdraw_requests.scheme,schemes.scheme as scheme_name');
    $builder->join('user', 'withdraw_requests.promoter_id = user.id', 'left');
    $builder->join('schemes', 'withdraw_requests.scheme= schemes.id', 'left');
    $builder->where('withdraw_requests.status','pending');
    $builder->orderBy('withdraw_requests.created_at', 'DESC');

$data['requests'] = $builder->get()->getResultArray();
    
    
    // print_r( $data['requests'] );die();

return view('admin/withdraw_request_approval', $data);
    
    
    
}
// public function Approved_Withdraw_request()
// {
//     $id = $this->request->getPost('requestId');
//     $amount = $this->request->getPost('amount');
//     $scheme_name = $this->request->getPost('scheme');

//     if (empty($id) || empty($amount) || empty($scheme_name)) {
//         return $this->response->setJSON([
//             'success' => false,
//             'message' => 'Invalid input. Request ID, amount, and scheme are required.'
//         ]);
//     }

//     $schemeBuilder = $this->db->table('schemes');
//     $schemeBuilder->where('scheme', $scheme_name);
//     $scheme = $schemeBuilder->get()->getRow();

//     if (!$scheme) {
//         return $this->response->setJSON([
//             'success' => false,
//             'message' => 'Invalid scheme. Scheme not found.'
//         ]);
//     }

//     $scheme_id = $scheme->id;

//     $builder = $this->db->table('withdraw_requests');
//     $builder->where('promoter_id', $id);
//     $builder->where('amount', $amount);
//     $builder->where('scheme', $scheme_id);
//     $request = $builder->get()->getRow();

//     if (!$request) {
//         return $this->response->setJSON([
//             'success' => false,
//             'message' => 'Invalid request or request not found.'
//         ]);
//     }

//     if ($request->status === 'Pending') {
      
//         $paymentBuilder = $this->db->table('promoter_payments');
//         $paymentBuilder->selectSum('amount');
//         $paymentBuilder->where('promoter_id', $id);
//         $paymentBuilder->where('scheme', $scheme_id);
//         $payment = $paymentBuilder->get()->getRow();

//         if (!$payment || $payment->amount < $amount) {
//             return $this->response->setJSON([
//                 'success' => false,
//                 'message' => 'Insufficient balance to approve the withdrawal request.'
//             ]);
//         }

//         $this->db->transStart();

//         $builder->where('promoter_id', $id);
//         $builder->where('scheme', $scheme_id);
//         $builder->update(['status' => 'Approved']);

//       $newAmount = $payment->amount - (float)$amount;

// $paymentBuilder->where('promoter_id', $id);
// $paymentBuilder->where('scheme', $scheme_id);
// $paymentBuilder->set('amount', 'amount - ' . (float)$amount, false); 
// $paymentBuilder->update();

//         $this->db->transComplete();

//         if ($this->db->transStatus() === false) {
//             return $this->response->setJSON([
//                 'success' => false,
//                 'message' => 'Failed to approve the withdrawal request. Please try again.'
//             ]);
//         }

//         return $this->response->setJSON([
//             'success' => true,
//             'message' => 'Withdrawal request approved successfully.',
//             'remaining_balance' => $newAmount 
//         ]);
//     }

//     return $this->response->setJSON([
//         'success' => false,
//         'message' => 'Request has already been processed.'
//     ]);
// }
public function Approved_Withdraw_request()
{
    $requestId = $this->request->getPost('request_id');

$builder = $this->db->table('withdraw_requests');
    $builder->where('request_id', $requestId);
    $request = $builder->get()->getRow();

if ($request) {
       
        $promoterId = $request->promoter_id;
        $schemeId = $request->scheme;
        $amountToDeduct = $request->amount;

$this->deductAmountFIFO($promoterId, $schemeId, $amountToDeduct);

// Update status to 'approved'
        $this->db->table('withdraw_requests')
                 ->where('request_id', $requestId)
                 ->update(['status' => 'approved']);
        
        return $this->response->setJSON(['success' => true, 'message' => 'Request approved and amount deducted successfully.']);
    }

return $this->response->setJSON(['success' => false, 'message' => 'Invalid request ID.']);
}

private function deductAmountFIFO($promoterId, $schemeId, $amountToDeduct)
{
    $builder = $this->db->table('promoter_payments');
    $builder->where('promoter_id', $promoterId);
    $builder->where('scheme', $schemeId);
    $builder->where('amount >', 0);
    $builder->orderBy('id', 'ASC');
    $payments = $builder->get()->getResult();

foreach ($payments as $payment) {
        if ($amountToDeduct <= 0) break;

$availableAmount = $payment->amount;
        $deductAmount = min($amountToDeduct, $availableAmount);

// Update promoter_payments record
        $this->db->table('promoter_payments')
                 ->where('id', $payment->id)
                 ->update(['amount' => $availableAmount - $deductAmount]);

$amountToDeduct -= $deductAmount; 
    }
}

public function getCustomerTableData($id)
{
   
    $db = \Config\Database::connect();
    $builder = $db->table('Customers');
    
  
    $builder->select('Customers.id, Customers.name, Customers.user_id, Customers.city, payments.amount, payments.payment_date');
    $builder->join('payments', 'payments.user_id = Customers.user_id', 'left');

$query = $builder->getWhere(['Customers.id' => $id]);
    $customerData = $query->getResultArray(); 
    
    if ($customerData) {
        return $this->response->setJSON([
            'result' => 1, 
            'data' => $customerData
        ]);
    } else {
        return $this->response->setJSON([
            'result' => 0, 
            'message' => 'Customer not found or no payments found.'
        ]);
    
        
    }
}

public function getCustomerTableData1($id)
{
    $db = \Config\Database::connect();
    $builder = $db->table('Customers');

$builder->select('Customers.id, Customers.name, Customers.user_id, Customers.city,Customers.card_no,SUM(payments.amount) AS total_amount,payments.amount,payments.payment_date,payments.end_date,user.name AS promoter_name, user.promoter_id');
    $builder->join('payments', 'payments.user_id = Customers.user_id', 'left');
    $builder->join('user', 'user.id = Customers.promoter_id', 'left');

$query = $builder->getWhere(['Customers.id' => $id]);
    $customerData = $query->getRowArray();

$paymentBuilder = $db->table('payments')
        ->select('payment_date, amount')
        ->where('user_id', $customerData['user_id'])
        ->get();
$paidInstallments = $paymentBuilder->getResultArray();

// print_r($paidInstallments);die();

$startDate =  $paidInstallments[0]['payment_date'];

$installmentAmount = 1000;
$installments = [];

for ($i = 0; $i <= 11; $i++) {
    $installments[] = [
        'installment_no' => $i + 1,
        'payment_date' => date('Y-m-d', strtotime("+$i months", strtotime($startDate))),
        'amount' => $installmentAmount
    ];
}

// print_r($installments);die();
    if ($customerData) {
        return view('customer/customer_view', [
            'customerData' => $customerData,
            'installments' => $installments,
            'paidInstallments' => $paidInstallments
        ]);
    } else {
        return view('customer/customer_view', ['message' => 'Customer not found or no payments found.']);
    }
}

public function profiledata()
{
    $promoter_id = session()->get('id');

log_message('debug', 'Promoter ID: ' . $promoter_id);

if (!$promoter_id) {
        return $this->response->setJSON(['error' => 'Promoter ID not found in session']);
    }

$builder = $this->db->table('user');

$builder->select('user.*, 
                     user.id as user_id, 
                     user. name,
                     user.phone, 
                     user.email,
                     user_details.address,
                     user_details.city,
                     user_details.state,
                     user_details.account_no,
                     user_details.account_holder_name,
                     user_details.bank_name,
                     user_details.ifsc_code,
                     user_details.branch,
                     user_details.upi_id,
                     user_details.branch_address,
                     ');
                     
    $builder->join('user_details','user.id= user_details.user_id','left');
    
    // Apply the where condition for the promoter_id
    $builder->where('user.id', $promoter_id);

// Group the data by promoter_id to avoid row duplication
    $builder->groupBy('user.promoter_id');

// Fetch the data
    $data = $builder->get()->getRowArray();

// Log the SQL query and data for debugging
    log_message('debug', 'Query: ' . $this->db->getLastQuery());  // Log the query
    log_message('debug', 'Data: ' . print_r($data, true));  // Log the result data

// Check and return data
    if ($data) {
        return $this->response->setJSON($data); // Return the data
    } else {
        return $this->response->setJSON(['error' => 'No data found']);
    }

}

public function saveProfileData()
{
    $promoter_id = session()->get('id');
    
    
    // print_r($promoter_id);die();

if (!$promoter_id) {
        return $this->response->setJSON(['error' => 'Promoter ID not found in session']);
    }

// Get the POST data
    $name = $this->request->getPost('name');
    $email = $this->request->getPost('email');
    $phone = $this->request->getPost('phone');
    $address = $this->request->getPost('address');
    $city = $this->request->getPost('city');
    $state = $this->request->getPost('state');
    $account_no = $this->request->getPost('account_no');
    $account_holder_name = $this->request->getPost('account_holder_name');
    $bank_name = $this->request->getPost('bank_name');
    $ifsc_code = $this->request->getPost('ifsc');
    $branch = $this->request->getPost('branch');
    $upi_id = $this->request->getPost('upi_id');
    $branch_address = $this->request->getPost('branch_address');

// Begin transaction to ensure data consistency
    $this->db->transStart();
    
    // Update user table
    $this->db->table('user')
        ->where('id', $promoter_id)
        ->update([
            'name' => $name,
            'email' => $email,
            'phone' => $phone
        ]);

$updated=    $this->db->table('user_details')
        ->where('user_id', $promoter_id)
        ->update([
            'address' => $address,
            'city' => $city,
            'state' => $state,
            'account_no' => $account_no,
            'account_holder_name' => $account_holder_name,
            'bank_name' => $bank_name,
            'ifsc_code' => $ifsc_code,
            'branch' => $branch,
            'upi_id' => $upi_id,
            'branch_address' => $branch_address
        ]);

$this->db->transComplete();

print_r($updated);die();
    // Check if transaction was successful
    if ($this->db->transStatus() === FALSE) {
        return $this->response->setJSON(['error' => 'Failed to save profile data']);
    } else {
        return $this->response->setJSON(['success' => 'Profile data saved successfully']);
    }
}

// In your controller (e.g., UserController.php)

public function getCustomersid()
    {
        $promoter_id = session()->get('id');

if (!$promoter_id) {
            return $this->response->setJSON(['error' => 'Customer ID not found in session']);
        }

$builder = $this->db->table('Customers');
        $builder->select('Customers.id,
        Customers.name,
        Customers.user_id,
        Customers.email,
        Customers.city,
        Customers.mobile,
        (SELECT SUM(amount) FROM payments WHERE Customers.id = payments.user_id) as total_payment,
        (SELECT COUNT(payment_date) FROM payments WHERE Customers.id = payments.user_id) as total_months,
        
        '); 
        
        $builder->join('payments','Customers.id = payments.user_id', 'left');
        
        $builder->where('Customers.id', $promoter_id); // Use the session ID to fetch the customer
       $builder->groupBy('Customers.id');

// Fetch the data
        $data = $builder->get()->getRowArray();

// Log for debugging
        log_message('debug', 'Query: ' . $this->db->getLastQuery());  // Log the query
        log_message('debug', 'Data: ' . print_r($data, true));  // Log the result data

// Check and return the customer data
        if ($data) {
            return $this->response->setJSON(['result' => 1, 'data' => $data]); // Return customer data
        } else {
            return $this->response->setJSON(['error' => 'No data found']);
        }
    }

public function getAdminid()
{
    $promoter_id = session()->get('id');

if (!$promoter_id) {
        return $this->response->setJSON(['error' => 'Customer ID not found in session']);
    }

$builder = $this->db->table('admin');
    $builder->select('admin.id,
                     admin.username,
                     admin.role_name,
                     admin.email');

$builder->where('admin.id', $promoter_id);

$adminData = $builder->get()->getRowArray();

log_message('debug', 'Admin Query: ' . $this->db->getLastQuery());  // Log the query
    log_message('debug', 'Admin Data: ' . print_r($adminData, true));  // Log the result data

if (!$adminData) {
        return $this->response->setJSON(['error' => 'Admin data not found']);
    }

$builderCustomer = $this->db->table('Customers');
    $builderCustomer->select('COUNT(id) as total_customers'); // Count the customers
    $customerData = $builderCustomer->get()->getRowArray();
    
    
    $builderpayment = $this->db->table('promoter_repayment_requests');
    $builderpayment->select('COUNT(id) as total_pins');
    $pinsData = $builderpayment->get()->getRowArray();

log_message('debug', 'Customer Query: ' . $this->db->getLastQuery()); 
    log_message('debug', 'Customer Count: ' . print_r($customerData, true));

if (!$customerData) {
        return $this->response->setJSON(['error' => 'Customer count not found']);
    }

$builderAmount = $this->db->table('payments');
    $builderAmount->select('SUM(amount) as total_amount');
    $amountData = $builderAmount->get()->getRowArray();

log_message('debug', 'Amount Query: ' . $this->db->getLastQuery()); 
    log_message('debug', 'Amount Data: ' . print_r($amountData, true));

if (!$amountData) {
        log_message('debug', 'No total amounts found');
    }

// $builderpayment = $this->db->table('promoter_repayment_requests');
    // $builderpayment->select('COUNT(id) as total_pins');
    // $pinsData = $builderpayment->get()->getRowArray();
    
    $builderpayment = $this->db->table('promoter_repayment_requests');
$builderpayment->select('SUM(total_slots) as total_pins');
$pinsData = $builderpayment->get()->getRowArray();

$builderpayment = $this->db->table('user');
    $builderpayment->select('COUNT(id) as total_promoter');
    $promoterData = $builderpayment->get()->getRowArray();
    
    
    // $builderintial = $this->db->table('promoter_requests');
    // $builderintial->select('COUNT(id) as total_intials');
    // $intialData = $builderintial->get()->getRowArray();
    
    
    $builderintial = $this->db->table('promoter_requests');
$builderintial->select('SUM(total_slots) as total_intials');
$intialData = $builderintial->get()->getRowArray();

$adminData['total_customers'] = $customerData['total_customers'];
    $adminData['total_amount'] = $amountData['total_amount'] ?? 0;
    $adminData['total_pins'] = $pinsData['total_pins'] ?? 0;
    $adminData['total_intials'] = $intialData['total_intials'] ?? 0;
    $adminData['total_promoter'] = $promoterData['total_promoter'] ?? 0;

// Return the combined data
    return $this->response->setJSON(['result' => 1, 'data' => $adminData]);
}

public function getPaymentData()
{
    $promoter_id = session()->get('id');

if (!$promoter_id) {
        return $this->response->setJSON(['error' => 'Promoter ID not found in session']);
    }

$builder = $this->db->table('payments');
    
 
    $builder->join('schemes', 'schemes.id = payments.scheme', 'left');

$builder->where('payments.user_id', $promoter_id);

$search = $this->request->getVar('search')['value'];

if (!empty($search)) {

$builder->groupStart() 
                ->like('schemes.scheme', $search) 
                ->orLike('payments.amount', $search) 
                ->orLike('payments.payment_date', $search) 
                ->groupEnd(); 
    }

$totalRecords = $builder->countAllResults(false);

$start = $this->request->getVar('start');
    $length = $this->request->getVar('length');
    $builder->limit($length, $start);

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

$response = [
        'draw' => $this->request->getVar('draw'),
        'recordsTotal' => $totalRecords,  
        'recordsFiltered' => $totalRecords,
        'data' => $payments,
    ];

return $this->response->setJSON($response); 
}

public function update_customer_details()
{

$id = $this->request->getPost('id');

$data = [
        'name'     => $this->request->getPost('name'),
        'email'    => $this->request->getPost('email'),
        'mobile'   => $this->request->getPost('mobile'),
        'card_no'  => $this->request->getPost('card_no'),
        'address'  => $this->request->getPost('address'),
        'state'    => $this->request->getPost('state'),
        'city'     => $this->request->getPost('city'),
        'pinCode'  => $this->request->getPost('pinCode')
    ];

$db = \Config\Database::connect();
    
    $builder = $db->table('Customers');
    $builder->where('id', $id);
    $updated = $builder->update($data);

if ($updated) {
        return $this->response->setJSON(['result' => 1]);
    } else {
        return $this->response->setJSON(['result' => 0]);
    }
}

}?>