mercredi 7 août 2019

Convert MySql Union query to laravel

I want to convert this query to Laravel query and i want to add where clause with customer id. I tried to convert this query into laravel query but is not working for me. It shows error Unknown column 'null' in 'field list'. This Query is working on Sql. .

SELECT * FROM ((SELECT sale_invoice_masters.id,
sale_invoice_masters.customer_id, NULL AS amount, NULL AS recovery_date,
 sale_invoice_masters.grand_total,
 sale_invoice_masters.cash_amount, sale_invoice_masters.credit_amount, 
sale_invoice_masters.sale_date FROM sale_invoice_masters) 

UNION ALL(SELECT recoveries.id ,recoveries.customer_id,
recoveries.amount,  recoveries.recovery_date,
 NULL AS grand_total, NULL AS cash_amount,
NULL AS credit_amount,NULL AS sale_date FROM recoveries))
 results ORDER BY sale_date DESC , recovery_date DESC

public function getCustomerRecord(Request $request)
    {

        $customers = Customer::where('id', $request->get('customer_id'))->get();
        $customerID = Customer::find($customers);

    $customer_credit_sales = SaleInvoiceMaster::select("sale_invoice_masters.id"
            , "sale_invoice_masters.customer_id",
            'null as amount',
            'null as recovery_date',
            "sale_invoice_masters.grand_total",
            "sale_invoice_masters.cash_amount","sale_invoice_masters.credit_amount",
            "sale_invoice_masters.sale_date");

        $recoveries = Recovery::select(["recoveries.id"
                , "recoveries.customer_id"
                , "recoveries.amount",
            "recoveries.recovery_date",
            'null as grand_total',
            'null as cash_amount',
            'null as credit_amount',
            'null as sale_date',
            ])
            ->unionAll($customer_credit_sales)->where('customer_id',$request->get('customer_id'))
            ->get();

        $htmlCredit = '';
        foreach ($recoveries as $sales) {

            $htmlCredit .= '<tr id="row_' . $sales->id . '">' .
                '<td > ' . $sales->customer_id . '</td > ' .
                '<td > ' . $sales->sale_date . '</td > ' .
                '<td > ' . $sales->recovery_date . '</td > ' .
                '<td > ' . $sales->total_amount . '</td > ' .
                '<td > ' . $sales->cash_amount . '</td > ' .
                '<td > ' . $sales->credit_amount . '</td > ' .
                '<td > ' . $sales->amount . '</td > ' .
                '</tr> ';
        }

        $result = array('name' => $customerID[0]->name, 'contact' => $customerID[0]->contact,
            'city' => $customerID[0]->city, 'balance' => $customerID[0]->opening_balance,
            'registration_date' => \Carbon\Carbon::parse($customerID[0]->registration_date)->format('d-m-Y'),
            'htmlCredit' => $htmlCredit);
        echo json_encode($result);
}



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire