EVOLUTION-NINJA

Edit File: AEController.php

<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\AETransactionModel;
use App\Models\CreateCustomerModel;
use App\Models\ProductModel;
use App\Models\DealerModel;

class AEController extends BaseController
{
    public function index()
    {
        $customerModel = new CreateCustomerModel();
        $productModel = new ProductModel();

$data['customers'] = $customerModel->findAll();
        $data['products'] = $productModel->findAll();

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

public function save()
    {
        try {
            $model = new AETransactionModel();
            $customer_id = $this->request->getPost('customer_id');
            
            if (!$customer_id) {
                return $this->response->setJSON(['status' => 0, 'message' => 'Valid Customer selection is required.']);
            }

$db = \Config\Database::connect();

$customer = $db->table('create_customer')
                ->select('farmer_id')
                ->where('id', $customer_id)
                ->get()
                ->getRowArray();

if (!$customer) {
                return $this->response->setJSON(['status' => 0, 'message' => 'Selected customer records do not exist.']);
            }

$farmer_id = $customer['farmer_id'];

$lastTransaction = $db->table('ae_transactions a')
                ->select('a.id')
                ->join('create_customer c', 'c.id = a.customer_id', 'left')
                ->where('c.farmer_id', $farmer_id)
                ->get()
                ->getRowArray();

if ($lastTransaction) {
                return $this->response->setJSON([
                    'status' => 0,
                    'message' => 'This Farmer ID already exists. Duplicate entry not allowed.'
                ]);
            }
            
            $dealer_name = $this->request->getPost('dealer_name');
            $utr1_date = $this->request->getPost('utr1_date');
            $utr2_date = $this->request->getPost('utr2_date');

$data = [
                'customer_id'       => $customer_id,
                'product_id'        => $this->request->getPost('product_id'),
                'rc_price'          => $this->request->getPost('rc_price'),
                'subsidy_amount'    => $this->request->getPost('subsidy_amount'),
                'smm_subsidy'       => $this->request->getPost('smm_subsidy'),
                'state_subsidy'     => $this->request->getPost('state_subsidy'),
                'farmer_share'      => $this->request->getPost('farmer_share'),
                'ae_commission'     => $this->request->getPost('ae_commission'),
                'gst_difference'    => $this->request->getPost('gst_difference'),
                'billing_price'     => $this->request->getPost('billing_price'),
                'smm_utr1'          => $this->request->getPost('smm_utr1'),
                'utr1_date'         => !empty($utr1_date) ? $utr1_date : null,
                'state_utr2'        => $this->request->getPost('state_utr2'),
                'utr2_date'         => !empty($utr2_date) ? $utr2_date : null,
                'smm_tds1'          => $this->request->getPost('smm_tds1'),
                'state_tds2'        => $this->request->getPost('state_tds2'),
                'smm_gst1'          => $this->request->getPost('smm_gst1'),
                'state_gst2'        => $this->request->getPost('state_gst2'),
                'fsn_return_utr_no' => $this->request->getPost('fsn_return_utr_no'),
                'dealer_name'       => $dealer_name,
            ];

if (!$model->insert($data)) {
                return $this->response->setJSON([
                    'status' => 0,
                    'errors' => $model->errors()
                ]);
            }

return $this->response->setJSON([
                'status' => 1,
                'message' => 'Data saved successfully'
            ]);

} catch (\Throwable $e) {
            return $this->response->setJSON([
                'status' => 0,
                'error' => $e->getMessage()
            ]);
        }
    }

public function getProductDetails()
    {
        $productModel = new ProductModel();
        $product_id = $this->request->getPost('product_id');

$product = $productModel->find($product_id);

if ($product) {
            return $this->response->setJSON([
                'status' => 1,
                'data' => $product
            ]);
        }
        return $this->response->setJSON(['status' => 0, 'message' => 'Product not found']);
    }

public function reportPage()
    {
        return view('AEmaster/ae_report');
    }

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

// Subquery 1: Calculate cumulative subsidies paid
            $paymentsSubquery = $db->table('ae_payments')
                ->select('transaction_id, SUM(total_amount) as cumulative_subsidy_paid')
                ->where('payment_for', 'subsidy_share')
                ->groupBy('transaction_id')
                ->getCompiledSelect();

// Subquery 2: Capture Farmer Share context safely
            $farmerPaymentSubquery = $db->table('ae_payments f1')
                ->select('f1.transaction_id, COALESCE(f1.farmer_share_utr_no, "") as farmer_share_utr_no, f1.farmer_payment_date, COALESCE(f1.farmer_share, 0) as paid_farmer_share')
                ->join('(SELECT transaction_id, MAX(id) as max_id FROM ae_payments WHERE payment_for = "farmer_share" GROUP BY transaction_id) f2', 'f1.id = f2.max_id')
                ->getCompiledSelect();

// Subquery 3: Capture Subsidy Share context safely
            $subsidyPaymentSubquery = $db->table('ae_payments s1')
                ->select('s1.transaction_id, s1.subsidy_share_utr_no, s1.subsidy_payment_date, s1.fsn_return_utr_no')
                ->join('(SELECT transaction_id, MAX(id) as max_id FROM ae_payments WHERE payment_for = "subsidy_share" GROUP BY transaction_id) s2', 's1.id = s2.max_id')
                ->getCompiledSelect();

$builder = $db->table('ae_transactions a')
                ->select('
                    a.*,
                    c.farmer_name,
                    d.name as dealer_name,
                    p.model_name,
                    COALESCE(c.paid_amount, 0) as farmer_paid,
                    COALESCE(pay_sum.cumulative_subsidy_paid, 0) as total_subsidy_paid_till_date,
                    COALESCE(pay_farmer.paid_farmer_share, 0) as paid_farmer_share,
                    pay_farmer.farmer_share_utr_no,
                    pay_farmer.farmer_payment_date,
                    pay_sub.subsidy_share_utr_no,
                    pay_sub.subsidy_payment_date,
                    COALESCE(pay_sub.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('(' . $paymentsSubquery . ') pay_sum', 'pay_sum.transaction_id = a.id', 'left')
                ->join('(' . $farmerPaymentSubquery . ') pay_farmer', 'pay_farmer.transaction_id = a.id', 'left')
                ->join('(' . $subsidyPaymentSubquery . ') pay_sub', 'pay_sub.transaction_id = a.id', 'left');

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

$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' => 'System processing fault encountered']);
        }
    }

public function getCustomerDetails()
    {
        $customer_id = $this->request->getPost('customer_id');
        if (!$customer_id) {
            return $this->response->setJSON(['status' => 0, 'message' => 'Missing parameter data']);
        }

$db = \Config\Database::connect();
        $data = $db->table('create_customer c')
            ->select('
                c.share_amount,
                c.product_id,
                d.name as dealer_name,
                p.model_name as product_name
            ')
            ->join('dealers d', 'TRIM(d.dealer_code) = TRIM(c.dealer_id)', 'left')
            ->join('products p', 'p.product_id = c.product_id', 'left')
            ->where('c.id', $customer_id)
            ->get()
            ->getRowArray();

return $this->response->setJSON([
            'status' => 1,
            'data' => $data ? $data : []
        ]);
    }

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

$farmer = $db->table('create_customer')->select('farmer_name')->groupBy('farmer_name')->get()->getResultArray();
        $dealer = $db->table('dealers')->select('name as dealer_name')->groupBy('name')->get()->getResultArray();
        $model = $db->table('products')->select('model_name')->groupBy('model_name')->get()->getResultArray();

return $this->response->setJSON([
            'farmer' => $farmer,
            'dealer' => $dealer,
            'model'  => $model
        ]);
    }
    
    public function delete($id)
    {
        try {
            $db = \Config\Database::connect();
            $db->transStart();

$db->table('ae_payments')->where('transaction_id', $id)->delete();
            $db->table('ae_transactions')->where('id', $id)->delete();

$db->transComplete();

if ($db->transStatus() === FALSE) {
                return $this->response->setJSON([
                    'status' => 0, 
                    'message' => 'Database operation backed out safely.'
                ]);
            }

return $this->response->setJSON([
                'status' => 1,
                'message' => 'Transaction history cleared safely.'
            ]);

} catch (\Throwable $e) {
            return $this->response->setJSON([
                'status' => 0,
                'error' => $e->getMessage()
            ]);
        }
    }

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

$transaction = $db->table('ae_transactions a')
            ->select('a.*, c.farmer_name, d.name as dealer_name, p.model_name')
            ->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')
            ->where('a.id', $id)
            ->get()
            ->getRowArray();

if (!$transaction) {
            return $this->response->setJSON([]);
        }

$subsidyHistory = $db->table('ae_payments')
            ->where('transaction_id', $id)
            ->where('payment_for', 'subsidy_share')
            ->orderBy('id', 'ASC')
            ->get()
            ->getResultArray();

$transaction['payment_history'] = $subsidyHistory;

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

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

$data = $db->table('ae_transactions a')
            ->select('
                a.*,
                c.farmer_name,
                d.name as dealer_name,
                p.model_name,
                ap.subsidy_amount as paid_subsidy
            ')
            ->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('ae_payments ap', 'ap.transaction_id = a.id AND ap.payment_for = "subsidy_share"', 'left')
            ->where('a.id', $id)
            ->get()
            ->getRowArray();

$result['data'] = $data;

return view('AEmaster/ae_edit', $result);
    }

public function update()
{
    $model = new AETransactionModel();
    $id = $this->request->getPost('id');

if (!$id) {
        return $this->response->setJSON([
            'status' => 0,
            'message' => 'Primary Tracking Identifier Missing'
        ]);
    }

$utr1_date = $this->request->getPost('utr1_date');
    $utr2_date = $this->request->getPost('utr2_date');

$data = [
        // 🔹 FIXED: Added your commission and pricing calculations
        'farmer_share'      => $this->request->getPost('farmer_share'),
        'subsidy_amount'    => $this->request->getPost('subsidy_amount'),
        'ae_commission'     => $this->request->getPost('ae_commission'),
        'gst_difference'    => $this->request->getPost('gst_difference'),
        'billing_price'     => $this->request->getPost('billing_price'),

// 🔹 FIXED: Added missing subsidy types 
        'smm_subsidy'       => $this->request->getPost('smm_subsidy'),
        'state_subsidy'     => $this->request->getPost('state_subsidy'),

// Government UTR / Payment Info
        'smm_utr1'          => $this->request->getPost('smm_utr1'),
        'utr1_date'         => !empty($utr1_date) ? $utr1_date : null,
        'state_utr2'        => $this->request->getPost('state_utr2'),
        'utr2_date'         => !empty($utr2_date) ? $utr2_date : null,
        'smm_tds1'          => $this->request->getPost('smm_tds1'),
        'state_tds2'        => $this->request->getPost('state_tds2'),
        'smm_gst1'          => $this->request->getPost('smm_gst1'),
        'state_gst2'        => $this->request->getPost('state_gst2'),
        'fsn_return_utr_no' => $this->request->getPost('fsn_return_utr_no'),
    ];

if ($model->update($id, $data)) {
        return $this->response->setJSON(['status' => 1, 'message' => 'Updated successfully']);
    }
    return $this->response->setJSON(['status' => 0, 'message' => 'Failed to save updates']);
}

public function checkCustomerEligibility()
{
    $customer_id = $this->request->getPost('customer_id');

$model = new \App\Models\AETransactionModel();

$exists = $model
        ->where('customer_id', $customer_id)
        ->first();

if ($exists) {

return $this->response->setJSON([
            'status' => 0,
            'message' => 'Customer already exists'
        ]);
    }

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

}