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' ) OR ( 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); } }