EVOLUTION-NINJA

Edit File: AEReportController.php

<?php

namespace App\Controllers;

use CodeIgniter\Controller;
use App\Models\AEPaymentModel;

class AEReportController extends Controller
{
    public function dealerReport()
    {
        return view('reports/dealer_report');
    }

public function getDealerReport()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('dealers d');

// =========================================
        // FILTERS
        // =========================================
        $dealer_name = $this->request->getPost('dealer_name');
        $from_date   = $this->request->getPost('from_date');
        $to_date     = $this->request->getPost('to_date');

// =========================================
        // SELECT
        // =========================================
        $builder->select("
            d.name AS dealer_name,
            COUNT(DISTINCT c.id) AS total_customers,
            IFNULL(t.total_subsidy_amount,0) AS total_subsidy_amount,
            IFNULL(t.total_farmer_share_amount,0) AS total_farmer_share_amount,
            IFNULL(t.total_ae_commission,0) AS total_ae_commission,
            IFNULL(t.total_billing_price,0) AS total_billing_price,
            IFNULL(p.subsidy_paid,0) AS subsidy_paid,
            IFNULL(p.farmer_share_paid,0) AS farmer_share_paid,
            (IFNULL(p.subsidy_paid,0) + IFNULL(p.farmer_share_paid,0)) AS total_paid,
            ((IFNULL(t.total_subsidy_amount,0) - IFNULL(t.total_ae_commission,0) + IFNULL(t.total_farmer_share_amount,0)) - (IFNULL(p.subsidy_paid,0) + IFNULL(p.farmer_share_paid,0))) AS balance_amount
        ");

// =========================================
        // JOINS & SUBQUERIES
        // =========================================
        $builder->join('create_customer c', 'c.dealer_name = d.name', 'left');

$builder->join("
            (
                SELECT 
                    dealer_name,
                    SUM(subsidy_amount) AS total_subsidy_amount,
                    SUM(farmer_share) AS total_farmer_share_amount,
                    SUM(ae_commission) AS total_ae_commission,
                    SUM(billing_price) AS total_billing_price
                FROM ae_transactions
                GROUP BY dealer_name
            ) t
        ", 't.dealer_name = d.name', 'left');

$paymentSubquery = "
            (
                SELECT 
                    dealer_name,
                    SUM(subsidy_amount) AS subsidy_paid,
                    SUM(farmer_share) AS farmer_share_paid
                FROM ae_payments
        ";

if ($from_date && $to_date) {
            $paymentSubquery .= " WHERE DATE(created_at) BETWEEN '$from_date' AND '$to_date' ";
        }

$paymentSubquery .= "
                GROUP BY dealer_name
            ) p
        ";

$builder->join($paymentSubquery, 'p.dealer_name = d.name', 'left');

if ($dealer_name) {
            $builder->where('d.name', $dealer_name);
        }

$builder->groupBy('d.id');
        $data = $builder->get()->getResultArray();

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

public function index()
    {
        return view('reports/subsidy_report');
    }

public function getReport()
{
    try {
        $db = \Config\Database::connect();

// 🌟 Robust Subquery: Aggregates payments to ensure exactly ONE row per transaction
        $paymentsSubqueryRaw = "
            (
                SELECT 
                    transaction_id, 
                    SUM(CASE WHEN payment_for = 'subsidy_share' THEN total_amount ELSE 0 END) as cumulative_subsidy_paid,
                    MAX(CASE WHEN payment_for = 'farmer_share' THEN farmer_share ELSE 0 END) as paid_farmer_share,
                    MAX(farmer_share_utr_no) as farmer_share_utr_no,
                    MAX(farmer_payment_date) as farmer_payment_date,
                    MAX(subsidy_share_utr_no) as subsidy_share_utr_no,
                    MAX(subsidy_payment_date) as subsidy_payment_date,
                    MAX(fsn_return_utr_no) as fsn_return_utr_no
                FROM ae_payments
                GROUP BY transaction_id
            ) pay_summary
        ";

$builder = $db->table('ae_transactions a')
            ->select('
                a.*,
                c.farmer_name,
                d.name as dealer_name,
                p.model_name,
                c.paid_amount as farmer_paid,
                COALESCE(pay_summary.cumulative_subsidy_paid, 0) as total_subsidy_paid_till_date,
                COALESCE(pay_summary.paid_farmer_share, 0) as paid_farmer_share,
                pay_summary.farmer_share_utr_no,
                pay_summary.farmer_payment_date,
                pay_summary.subsidy_share_utr_no,
                pay_summary.subsidy_payment_date,
                COALESCE(pay_summary.fsn_return_utr_no, a.fsn_return_utr_no) as fsn_return_utr_no
            ')
            ->join('create_customer c', 'c.id = a.customer_id', 'left')
            ->join('dealers d', 'd.dealer_code = c.dealer_id', 'left')
            ->join('products p', 'p.product_id = a.product_id', 'left')
            ->join($paymentsSubqueryRaw, 'pay_summary.transaction_id = a.id', 'left');

// Filters
        $farmer = $this->request->getPost('farmer_name');
        $dealer = $this->request->getPost('dealer_name');
        $model  = $this->request->getPost('model_name');

if (!empty($farmer)) { $builder->where('c.farmer_name', $farmer); }
        if (!empty($dealer)) { $builder->where('d.name', $dealer); }
        if (!empty($model))  { $builder->where('p.model_name', $model); }

// 🌟 FORCE COLLAPSE: Absolute protection against multiple row renders
        $builder->groupBy('a.id');

$result = $builder->orderBy('a.id', 'DESC')->get()->getResultArray();

return $this->response->setContentType('application/json')
                              ->setBody(json_encode($result, JSON_UNESCAPED_SLASHES | JSON_PRESERVE_ZERO_FRACTION));
                               
    } catch (\Throwable $e) {
        log_message('error', '[DataTables API Error] ' . $e->getMessage() . ' Line: ' . $e->getLine());
        return $this->response->setStatusCode(500)
                              ->setJSON(['status' => false, 'error' => $e->getMessage()]);
    }
}

public function getPaymentHistory($transaction_id)
{
    $db = \Config\Database::connect();
    
    $history = $db->table('ae_payments')
        ->where('transaction_id', $transaction_id)
        ->orderBy('id', 'DESC')
        ->get()
        ->getResultArray();
        
    return $this->response->setJSON($history);
}

public function edit($id)
    {
        $db = \Config\Database::connect();

$transaction = $db->table('ae_transactions')->where('id', $id)->get()->getRowArray();

$builder = $db->table('ae_transactions a');
        $builder->select("
            a.*,
            c.farmer_name,
            c.dealer_name,
            c.dealer_id,
            c.paid_amount as farmer_paid,
            p.product_id,
            p.rc_price,
            ap.subsidy_amount as paid_subsidy,
            ap.farmer_share as paid_farmer_share,
            ap.farmer_share_utr_no
        ");
        $builder->join('create_customer c', 'c.id = a.customer_id', 'left');
        $builder->join('products p', 'p.product_id = a.product_id', 'left');
        $builder->join('ae_payments ap', 'ap.transaction_id = a.id', 'left');
        $builder->where('a.id', $id);

$data['payment'] = $builder->get()->getRowArray();
        
        $runningTotalPaid = $db->table('ae_payments')
            ->where('transaction_id', $id)
            ->where('payment_for', 'subsidy_share')
            ->selectSum('total_amount')
            ->get()
            ->getRowArray();

// $totalPaid = floatval($runningTotalPaid['total_amount'] ?? 0);
        // $payToDealerTarget = floatval($transaction['subsidy_amount'] ?? 0) - floatval($transaction['ae_commission'] ?? 0);

// $data['subsidy_balance_to_pay'] = $payToDealerTarget - $totalPaid;

$totalPaid = floatval($runningTotalPaid['total_amount'] ?? 0);

$subsidyAmount = floatval($transaction['subsidy_amount'] ?? 0);
$aeCommission  = floatval($transaction['ae_commission'] ?? 0);
$gstDifference = floatval($transaction['gst_difference'] ?? 0);

$payToDealerTarget =
    $subsidyAmount
    - $aeCommission
    - $gstDifference;

$data['subsidy_balance_to_pay'] =
    $payToDealerTarget - $totalPaid;

return view('createproject/ae_payment_edit', $data);
    }

// =====================================================
    // 🛠️ REWRITTEN SAFE SAVE PAYMENT METHOD
    // =====================================================
   public function save_payment()
{
    // Ensure we send back JSON/Redirect responses cleanly
    $db = \Config\Database::connect();
    $model = new \App\Models\AEPaymentModel();

// 1. Gather Post Parameters Safely
    $transaction_id = $this->request->getPost('transaction_id');
    $customer_id    = $this->request->getPost('customer_id');
    $product_id     = $this->request->getPost('product_id');
    $payment_for    = $this->request->getPost('payment_for'); 
    $amount         = floatval($this->request->getPost('total_amount') ?? 0);
    $dealer_name    = $this->request->getPost('dealer_name') ?? '';
    
    $form_date = $this->request->getPost('payment_date');
    if (empty($form_date)) {
        $form_date = date('Y-m-d');
    }

if (empty($transaction_id)) {
        return redirect()->back()->with('error', 'Missing Transaction ID');
    }

// 2. Setup clean variable properties for this SPECIFIC installment row
    $subsidy_amount        = 0;
    $farmer_share          = 0;
    $subsidy_payment_type  = null;
    $subsidy_upi_id        = null;
    $subsidy_share_utr_no  = null;
    $subsidy_payment_date  = null;
    $farmer_payment_type   = null;
    $farmer_upi_id         = null;
    $farmer_share_utr_no   = null;
    $farmer_payment_date   = null;

// 3. Populate matching parameters based strictly on what is being submitted right now
    if ($payment_for == 'subsidy_share' || $payment_for == 'smm_subsidy' || $payment_for == 'state_subsidy') {
        
        $subsidy_amount        = $amount; 
        $subsidy_payment_date  = $form_date; 
        $subsidy_share_utr_no  = $this->request->getPost('subsidy_share_utr_no') ?? $this->request->getPost('farmer_share_utr_no'); 
        $subsidy_payment_type  = $this->request->getPost('payment_type');

if ($subsidy_payment_type == 'Online/UPI') {
            $subsidy_upi_id = $this->request->getPost('upi_id');
        }
        
        $final_total   = $subsidy_amount; 
        $payment_label = 'subsidy_share';

} else if ($payment_for == 'farmer_share') {
        
        $farmer_share         = $amount;
        $farmer_payment_date  = $form_date; 
        $farmer_share_utr_no  = $this->request->getPost('farmer_share_utr_no');
        $farmer_payment_type   = $this->request->getPost('payment_type');

if ($farmer_payment_type == 'Online/UPI') {
            $farmer_upi_id = $this->request->getPost('upi_id');
        }

$final_total   = $farmer_share;
        $payment_label = 'farmer_share';
    }

// 4. Build payload data representing just this transaction row
    $data = [
        'transaction_id'       => intval($transaction_id),
        'customer_id'          => intval($customer_id),
        'product_id'           => intval($product_id),
        'dealer_name'          => $dealer_name,
        'payment_for'          => $payment_label,
        
        'subsidy_amount'       => $subsidy_amount,
        'subsidy_share_utr_no' => $subsidy_share_utr_no,
        'subsidy_payment_type' => $subsidy_payment_type,
        'subsidy_upi_id'       => $subsidy_upi_id,
        'subsidy_payment_date' => $subsidy_payment_date,
        
        'farmer_share'         => $farmer_share,
        'farmer_share_utr_no'  => $farmer_share_utr_no,
        'farmer_payment_type'  => $farmer_payment_type,
        'farmer_upi_id'        => $farmer_upi_id,
        'farmer_payment_date'  => $farmer_payment_date,
        
        'total_amount'         => $final_total 
    ];

// 🌟 THE CRITICAL FIX: Always INSERT a new record to build historical data rows!
    $model->insert($data);

return redirect()->to('/ae-report')->with('msg', 'Payment Installment Registered Successfully');
}

public function exportExcel()
{
    $db = \Config\Database::connect();

// 1. Re-use the exact same subquery logic used in getReport() to get calculated values
    $paymentsSubqueryRaw = "
        (SELECT transaction_id, 
            SUM(CASE WHEN payment_for = 'subsidy_share' THEN total_amount ELSE 0 END) as cumulative_subsidy_paid,
            MAX(CASE WHEN payment_for = 'farmer_share' THEN farmer_share ELSE 0 END) as paid_farmer_share,
            MAX(farmer_share_utr_no) as farmer_share_utr_no,
            MAX(farmer_payment_date) as farmer_payment_date,
            MAX(subsidy_share_utr_no) as subsidy_share_utr_no,
            MAX(subsidy_payment_date) as subsidy_payment_date
            
         FROM ae_payments GROUP BY transaction_id) pay_summary";

$builder = $db->table('ae_transactions a')
        ->select('a.*, c.farmer_name, d.name as dealer_name, p.model_name, c.paid_amount as farmer_paid, 
                  pay_summary.*')
        ->join('create_customer c', 'c.id = a.customer_id', 'left')
        ->join('dealers d', 'd.dealer_code = c.dealer_id', 'left')
        ->join('products p', 'p.product_id = a.product_id', 'left')
        ->join($paymentsSubqueryRaw, 'pay_summary.transaction_id = a.id', 'left');

// Apply Filters
    if ($farmer = $this->request->getGet('farmer_name')) $builder->where('c.farmer_name', $farmer);
    if ($dealer = $this->request->getGet('dealer_name')) $builder->where('d.name', $dealer);
    if ($model = $this->request->getGet('model_name'))   $builder->where('p.model_name', $model);

$results = $builder->groupBy('a.id')->orderBy('a.id', 'DESC')->get()->getResultArray();

// Prepare CSV
    $filename = "AE_Report_" . date('YmdHis') . ".csv";
    header("Content-Type: text/csv; charset=utf-8");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    $output = fopen("php://output", "w");
    
    // Add Header Row
    fputcsv($output, [
        'SL NO', 'Farmer', 'Dealer', 'Model', 'RC Price', 'Farmer Share', 'Farmer Paid', 
        'Farmer Share Status', 'FS Return UTR No', 'FS Return Date', 'Total Subsidy', 
        'SMAM Subsidy', 'SMAM RETURN UTR No.', 'SMAM RETURN UTR Date', 'SMAM RETURN TDS', 'SMAM RETURN GST', 'STATE Subsidy', 
        'STATE RETURN UTR No.', 'STATE RETURN UTR Date', 'STATE RETURN TDS', 'STATE RETURN GST', 'Balance Subsidy', 
        'Subsidy Pay to Dealer', 'Subsidy Balance To Pay', 'Subsidy Status', 
        'Subsidy Share Return UTR No', 'Subsidy Share Return Date', 'GST on AE Commission', 'AE Comm'
    ]);

// Loop through results and calculate fields to match the UI
    $slNo = 1;
    foreach ($results as $row) {
        // Calculations (same as your JS logic)
        $totalSub = floatval($row['subsidy_amount'] ?? 0);
        $balSub = $totalSub - floatval($row['smm_subsidy'] ?? 0) - floatval($row['state_subsidy'] ?? 0) - floatval($row['smm_tds1'] ?? 0) - floatval($row['state_tds2'] ?? 0) - floatval($row['smm_gst1'] ?? 0) - floatval($row['state_gst2'] ?? 0);
        
        $target = $totalSub - floatval($row['ae_commission'] ?? 0) - floatval($row['gst_difference'] ?? 0);
        $paidTillDate = floatval($row['cumulative_subsidy_paid'] ?? 0);
        $subsidyBalance = $target - $paidTillDate;
        
        $statusFarmer = (floatval($row['paid_farmer_share'] ?? 0) > 0 && !empty($row['farmer_share_utr_no'])) ? 'Paid to Dealer' : 'Pending';
        $statusSubsidy = ($subsidyBalance <= 0) ? 'Paid to Dealer' : 'Pending';

fputcsv($output, [
            $slNo++, $row['farmer_name'], $row['dealer_name'], $row['model_name'], $row['rc_price'], 
            $row['farmer_share'], $row['farmer_paid'], $statusFarmer, $row['farmer_share_utr_no'], 
            $row['farmer_payment_date'], $row['subsidy_amount'], $row['smm_subsidy'], $row['smm_utr1'], 
            $row['utr1_date'], $row['smm_tds1'], $row['smm_gst1'], $row['state_subsidy'], 
            $row['state_utr2'], $row['utr2_date'], $row['state_tds2'], $row['state_gst2'], 
            number_format($balSub, 2), number_format($target, 2), number_format($subsidyBalance, 2), 
            $statusSubsidy, $row['subsidy_share_utr_no'], $row['subsidy_payment_date'], 
            $row['gst_difference'], $row['ae_commission']
        ]);
    }
    fclose($output);
    exit;
}
    public function farmerReport()
    {
        return view('reports/farmer_report');
    }

public function getFarmerReport()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('create_customer c');

$builder->select("
            c.id,
            c.unique_code AS farmer_code,
            c.farmer_id,
            c.farmer_name,
            c.dealer_name,
            IFNULL(a.farmer_share,0) AS total_price,
            IFNULL(c.paid_amount,0) AS paid_amount,
            (IFNULL(a.farmer_share,0) - IFNULL(c.paid_amount,0)) AS balance_amount,
            c.date AS payment_date
        ");
        $builder->join('ae_transactions a', 'a.customer_id = c.id', 'left');

if($this->request->getPost('farmer_name')) { $builder->where('c.farmer_name', $this->request->getPost('farmer_name')); }
        if($this->request->getPost('dealer_name')) { $builder->where('c.dealer_name', $this->request->getPost('dealer_name')); }

$from_date = $this->request->getPost('from_date');
        $to_date   = $this->request->getPost('to_date');

if(!empty($from_date)) { $builder->where('DATE(c.date) >=', $from_date); }
        if(!empty($to_date))   { $builder->where('DATE(c.date) <=', $to_date); }

$builder->orderBy('c.id', 'DESC');
        $data = $builder->get()->getResultArray();

return $this->response->setJSON($data);
    }
    
    public function consolidatedReport()
{
    return view('reports/consolidated_report');
}
    
public function getConsolidatedData()
{
    $db = \Config\Database::connect();
    $builder = $db->table('ae_transactions t')
        ->select('
            d.dealer_code,
            t.dealer_name,
            c.farmer_id, 
            c.farmer_name, 
            t.rc_price,
            t.farmer_share, 
            COALESCE(p.total_farmer_paid, 0) as farmer_paid,
            (t.farmer_share - COALESCE(p.total_farmer_paid, 0)) as farmer_balance,
            t.subsidy_amount, 
            t.gst_difference,
            t.ae_commission,
            COALESCE(p.total_subsidy_paid, 0) as subsidy_paid,
            ((t.subsidy_amount - t.ae_commission-t.gst_difference) - COALESCE(p.total_subsidy_paid, 0)) as subsidy_balance
        ')
        ->join('create_customer c', 'c.id = t.customer_id', 'left')
        ->join('dealers d', 'd.name = t.dealer_name', 'left')
        ->join('(
            SELECT transaction_id, 
                   SUM(CASE WHEN payment_for = "farmer_share" THEN total_amount ELSE 0 END) as total_farmer_paid,
                   SUM(CASE WHEN payment_for = "subsidy_share" THEN total_amount ELSE 0 END) as total_subsidy_paid
            FROM ae_payments GROUP BY transaction_id
        ) p', 'p.transaction_id = t.id', 'left');

if ($farmer = $this->request->getPost('farmer_name')) $builder->where('c.farmer_name', $farmer);
    if ($dealer = $this->request->getPost('dealer_name')) $builder->where('t.dealer_name', $dealer);

return $this->response->setJSON($builder->get()->getResultArray());
}

public function exportConsolidatedExcel()
{
    $db = \Config\Database::connect();

// 1. Build Query with necessary joins for Dealer Code and Farmer ID
    $builder = $db->table('ae_transactions t')
        ->select('
            d.dealer_code, 
            t.dealer_name, 
            c.farmer_id, 
            c.farmer_name, 
            t.rc_price, 
            t.farmer_share, 
            t.subsidy_amount,
            t.gst_difference,
            t.ae_commission,
            COALESCE(p.total_farmer_paid, 0) as farmer_paid,
            COALESCE(p.total_subsidy_paid, 0) as subsidy_paid
        ')
        ->join('create_customer c', 'c.id = t.customer_id', 'left')
        ->join('dealers d', 'd.name = t.dealer_name', 'left')
        ->join('(
            SELECT transaction_id, 
                   SUM(CASE WHEN payment_for = "farmer_share" THEN total_amount ELSE 0 END) as total_farmer_paid,
                   SUM(CASE WHEN payment_for = "subsidy_share" THEN total_amount ELSE 0 END) as total_subsidy_paid
            FROM ae_payments GROUP BY transaction_id
        ) p', 'p.transaction_id = t.id', 'left');

// 2. Apply Filters (using getGet because export uses GET parameters)
    if ($farmer = $this->request->getGet('farmer_name')) $builder->where('c.farmer_name', $farmer);
    if ($dealer = $this->request->getGet('dealer_name')) $builder->where('t.dealer_name', $dealer);

$results = $builder->get()->getResultArray();

// 3. Prepare CSV
    $filename = "Consolidated_Report_" . date('YmdHis') . ".csv";
    header("Content-Type: text/csv; charset=utf-8");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    
    $output = fopen("php://output", "w");
    
    // Add UTF-8 BOM for Excel compatibility
    fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
    
    // Header Row
    fputcsv($output, [
        'Dealer Code', 'Dealer Name', 'Farmer ID', 'Farmer Name', 'RC Price', 
        'Farmer Share', 'Farmer Share Paid', 'Farmer Share Balance', 
        'Subsidy Share', 'Subsidy Share Paid', 'Subsidy Share Balance', 'GST on AE Commission', 'AE Commission'
    ]);

// 4. Loop and Add Data Rows
    foreach ($results as $row) {
        $farmerBal = $row['farmer_share'] - $row['farmer_paid'];
        $subsidyBal = ($row['subsidy_amount'] - $row['ae_commission'] - $row['gst_difference']) - $row['subsidy_paid'];

fputcsv($output, [
            $row['dealer_code'] ?? '',
            $row['dealer_name'],
            $row['farmer_id'] ?? '',
            $row['farmer_name'],
            $row['rc_price'],
            $row['farmer_share'],
            $row['farmer_paid'],
            number_format($farmerBal, 2, '.', ''),
            $row['subsidy_amount'],
            $row['subsidy_paid'],
            number_format($subsidyBal, 2, '.', ''),
            $row['gst_difference'],
            $row['ae_commission']
        ]);
    }
    
    fclose($output);
    exit;
}

}