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');

//     $builder->select("
//         d.name AS dealer_name,

//         COUNT(DISTINCT c.id) AS total_customers,

//         IFNULL(SUM(a.subsidy_amount),0) AS subsidy_amount,

//         IFNULL(SUM(a.farmer_share),0) AS farmer_share_amount
//     ");

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

//     $builder->join(
//         'ae_payments a',
//         'a.customer_id = c.id',
//         'left'
//     );

//     $builder->groupBy('d.id');

//     $data = $builder->get()->getResultArray();

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

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

");

// =========================================
    // CUSTOMER JOIN
    // =========================================

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

// =========================================
    // AE TRANSACTION SUBQUERY
    // =========================================

$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');

// =========================================
    // AE PAYMENT SUBQUERY WITH DATE FILTER
    // =========================================

$paymentSubquery = "

(

SELECT

dealer_name,

SUM(subsidy_amount)
                    AS subsidy_paid,

SUM(farmer_share)
                    AS farmer_share_paid

FROM ae_payments

// =========================================
    // DATE FILTER
    // =========================================

if($from_date && $to_date){

$paymentSubquery .= "

WHERE

(

subsidy_payment_date

BETWEEN

'$from_date'

AND

'$to_date'

)

(

farmer_payment_date

BETWEEN

'$from_date'

AND

'$to_date'

)

";
    }

$paymentSubquery .= "

GROUP BY dealer_name

) p

$builder->join(

$paymentSubquery,

'p.dealer_name = d.name',

'left'

);

// =========================================
    // DEALER FILTER
    // =========================================

if($dealer_name){

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

// =========================================
    // GROUP
    // =========================================

$builder->groupBy('d.id');

// =========================================
    // RESULT
    // =========================================

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

return $this->response->setJSON($data);
}
    public function index()
    {
        return view('reports/subsidy_report');
    }

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

//     $builder = $db->table('ae_transactions a');

//     $builder->select("
    //         c.unique_code AS code,
    //         c.farmer_name,
    //         CONCAT(c.address, ', ', c.taluk) AS farmer_address,
    //         a.subsidy_amount,
    //         a.subsidy_utr,
    //         a.subsidy_date AS date,
    //         a.billing_price AS total_amt,
    //         a.tds,
    //         a.gst,
    //         IFNULL(d.tan_no, '-') AS tan_no
    //     ");

//     $builder->join('create_customer c', 'c.id = a.customer_id', 'left');
    //     $builder->join('dealers d', 'd.id = c.dealer_id', 'left');

//     $data = $builder->get()->getResultArray();

//     return $this->response->setJSON($data);
    // }
public function getReport()
{
    $db = \Config\Database::connect();

$builder = $db->table('ae_transactions a');

$builder->select("
    
        c.farmer_id as farmer_code,
        c.farmer_name,
        CONCAT(c.address, ', ', c.taluk) as farmer_address,

a.subsidy_amount,
        a.subsidy_utr,
        a.subsidy_date,
        a.rc_price as total_amt,
        a.tds,
        a.gst,

d.tan_no
    ");

$builder->join(
        'create_customer c',
        'c.id = a.customer_id',
        'left'
    );

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

// ðŸ”· FILTERS

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

if($this->request->getPost('subsidy_utr'))
    {
        $builder->where(
            'a.subsidy_utr',
            $this->request->getPost('subsidy_utr')
        );
    }

if($this->request->getPost('tan_no'))
    {
        $builder->where(
            'd.tan_no',
            $this->request->getPost('tan_no')
        );
    }

// if($this->request->getPost('subsidy_date'))
    // {
    //     $builder->where(
    //         'DATE(a.subsidy_date)',
    //         $this->request->getPost('subsidy_date')
    //     );
    // }

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

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

if(!empty($from_date))
{
    $builder->where(
        'DATE(a.subsidy_date) >=',
        $from_date
    );
}

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

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

return $this->response->setJSON($data);
}
    
    public function exportExcel()
{
    $db = \Config\Database::connect();

$builder = $db->table('ae_transactions a');
    $builder->select("
        c.unique_code AS code,
        c.farmer_name,
        CONCAT(c.address, ', ', c.taluk) AS farmer_address,
        a.subsidy_amount,
        a.subsidy_utr,
        a.subsidy_date AS date,
        a.billing_price AS total_amt,
        a.tds,
        a.gst,
        IFNULL(d.tan_no, '-') AS tan_no
    ");
    $builder->join('create_customer c', 'c.id = a.customer_id', 'left');
    // $builder->join('dealers d', 'd.id = c.dealer_id', 'left');
    $builder->join('dealers d', 'd.name = a.dealer_name', 'left');

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

header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=Subsidy_Report.xls");

echo "<table border='1'>";
    echo "<tr>
            <th>S N</th>
            <th>Code</th>
            <th>Farmer Name</th>
            <th>Farmer Address</th>
            <th>Subsidy</th>
            <th>UTR</th>
            <th>Date</th>
            <th>Total Amt</th>
            <th>TDS</th>
            <th>GST</th>
            <th>TAN No</th>
          </tr>";

$i = 1;
    foreach ($data as $row) {
        echo "<tr>
                <td>{$i}</td>
                <td>{$row['code']}</td>
                <td>{$row['farmer_name']}</td>
                <td>{$row['farmer_address']}</td>
                <td>{$row['subsidy_amount']}</td>
                <td>{$row['subsidy_utr']}</td>
                <td>{$row['date']}</td>
                <td>{$row['total_amt']}</td>
                <td>{$row['tds']}</td>
                <td>{$row['gst']}</td>
                <td>{$row['tan_no']}</td>
              </tr>";
        $i++;
    }

echo "</table>";
    exit;
}

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

//     $builder = $db->table('ae_transactions a');

//     $builder->select("
    //         a.*,
    //         c.farmer_name,
    //         c.dealer_name,
    //         c.dealer_id,
    //         p.product_id,
    //         p.rc_price
    //     ");

//     $builder->join('create_customer c', 'c.id = a.customer_id', 'left');
    //     $builder->join('products p', 'p.product_id = a.product_id', 'left');

//     $builder->where('a.id', $id);

//     $data['payment'] = $builder->get()->getRowArray();

//     return view('createproject/ae_payment_edit', $data);
    // }
 public function edit($id)
{
    $db = \Config\Database::connect();

$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

");

$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();

return view(
        'createproject/ae_payment_edit',
        $data
    );
}
    
public function save_payment()
{
    $model = new \App\Models\AEPaymentModel();

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

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

$amount = floatval($this->request->getPost('total_amount'));

// ✅ CHECK EXISTING
    $existing = $model
        ->where('transaction_id', $transaction_id)
        ->first();

// ============================================
    // DEFAULT VALUES
    // ============================================

$subsidy_amount = 0;
    $farmer_share = 0;

$subsidy_payment_type = null;
    $subsidy_upi_id = null;

$farmer_payment_type = null;
    $farmer_upi_id = null;

// ============================================
    // PRESERVE OLD VALUES
    // ============================================

if ($existing) {

$subsidy_amount =
            floatval($existing['subsidy_amount']);

$farmer_share =
            floatval($existing['farmer_share']);

$subsidy_payment_type =
            $existing['subsidy_payment_type'];

$subsidy_upi_id =
            $existing['subsidy_upi_id'];

$farmer_payment_type =
            $existing['farmer_payment_type'];

$farmer_upi_id =
            $existing['farmer_upi_id'];
    }

// ============================================
    // SUBSIDY PAYMENT
    // ============================================

if ($payment_for == 'subsidy') {

$subsidy_amount = $amount;

$subsidy_payment_type =
            $this->request->getPost('payment_type');

if (
            $this->request->getPost('payment_type')
            == 'Online/UPI'
        ) {

$subsidy_upi_id =
                $this->request->getPost('upi_id');
        }
    }

// ============================================
    // FARMER SHARE PAYMENT
    // ============================================

if ($payment_for == 'farmer_share') {

$farmer_share = $amount;

$farmer_payment_type =
            $this->request->getPost('payment_type');

if (
            $this->request->getPost('payment_type')
            == 'Online/UPI'
        ) {

$farmer_upi_id =
                $this->request->getPost('upi_id');
        }
    }

// ============================================
    // FINAL TOTAL
    // ============================================

$final_total =
        $subsidy_amount + $farmer_share;

// ============================================
// PAYMENT DATES
// ============================================

$subsidy_payment_date = null;
$farmer_payment_date = null;

// preserve old dates
if ($existing) {

$subsidy_payment_date =
        $existing['subsidy_payment_date'];

$farmer_payment_date =
        $existing['farmer_payment_date'];
}

// subsidy selected
if ($payment_for == 'subsidy') {

$subsidy_payment_date = date('Y-m-d');
}

// farmer share selected
if ($payment_for == 'farmer_share') {

$farmer_payment_date = date('Y-m-d');
}

// ============================================
    // SAVE DATA
    // ============================================

$data = [

'transaction_id' => $transaction_id,

'customer_id' =>
            $this->request->getPost('customer_id'),

'product_id' =>
            $this->request->getPost('product_id'),

'dealer_name' =>
            $this->request->getPost('dealer_name'),

// subsidy
        'subsidy_amount' => $subsidy_amount,
        'subsidy_payment_type' =>
            $subsidy_payment_type,
        'subsidy_upi_id' =>
            $subsidy_upi_id,
             'subsidy_payment_date' =>
        $subsidy_payment_date,

// farmer
        'farmer_share' => $farmer_share,
        'farmer_payment_type' =>
            $farmer_payment_type,
        'farmer_upi_id' =>
            $farmer_upi_id,
              'farmer_payment_date' =>
        $farmer_payment_date,

// total
        'total_amount' => $final_total
    ];

// ============================================
    // UPDATE / INSERT
    // ============================================

if ($existing) {

$model->update($existing['id'], $data);

} else {

$model->insert($data);
    }

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

// =====================================================
// FARMER REPORT PAGE
// =====================================================

public function farmerReport()
{
    return view('reports/farmer_report');
}

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

//     $data['invoice'] = $db->table('ae_transactions a')
//         ->select('
//             c.farmer_name,
//             d.name as dealer_name,
//             p.model_name,
//             a.rc_price,
//             a.subsidy_amount,
//             a.farmer_share
//         ')
//         ->join('create_customer c', 'c.id = a.customer_id', 'left')
//         ->join('dealers d', 'd.name = a.dealer_name', 'left')
//         ->join('products p', 'p.product_id = a.product_id', 'left')
//         ->where('a.id', $id)
//         ->get()
//         ->getRowArray();
        
//          // ✅ ADD BEFORE RETURN
//     $data['invoice_no'] =
//         'INV-' . date('Ymd') . '-' . rand(100,999);

//     $data['invoice_time'] =
//         date('h:i:s A');

//     // return view('AEmaster/view_invoice2', $data);
//     return view('invoice/view_invoice2', $data);
    
// }

// =====================================================
// GET FARMER REPORT DATA
// =====================================================
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);
}

}