SummaryInvoice

<?php

namespace Sts\CmsActivity\BO;

use Sts\PleafCore\BusinessFunction;
use Sts\CmsActivity\Model\SlInvoiceVisitPlan;
use DB;
use Log;

/**
 * @author Bahar, 08/11/2019
 * @in
 *
 * @out
 */

class GetSummaryInvoice implements BusinessFunction
{

    public function getDescription()
    {
        return "Mendapatkan Daftar Summary Invoice Customer";
    }

    public function execute($dto)
    {
        \Log::info('GetSumarryInvoice');

        $admin_process_visit_plan_id  = $dto["admin_process_visit_plan_id"];

        $query = " SELECT A.customer_id, f_get_partner_code(A.customer_id) AS customer_code, f_get_partner_name(A.customer_id) AS customer_name,
        COALESCE(SUM(D.payment_amount),0) AS payment_amount,
        count(1) AS total_invoice, COUNT(1) FILTER (WHERE A.status = 'E') AS total_invoice_exchange,
                COUNT(1) FILTER (WHERE A.status = 'P') AS total_invoice_payment, COUNT(1) FILTER (WHERE A.status = 'N') AS total_unexchange_invoice,
                COUNT(1) FILTER (WHERE A.status = 'C') AS total_cancel_invoice
                        FROM sl_invoice_visit_plan A
                        INNER JOIN sl_admin_process_visit_plan B ON A.admin_process_visit_plan_id = B.admin_process_visit_plan_id
                        LEFT JOIN sl_invoice_payment C ON A.admin_process_visit_plan_id = C.admin_process_visit_plan_id AND A.customer_id = C.customer_id
                        LEFT JOIN sl_invoice_payment_item D ON C.invoice_payment_id = D.invoice_payment_id
                        WHERE A.admin_process_visit_plan_id = $admin_process_visit_plan_id
                        GROUP BY A.customer_id
        ";

        $query2 = " SELECT COALESCE(SUM(D.payment_amount) FILTER (WHERE D.type_payment = 'CASH'),0) AS cash,
        COALESCE(SUM(D.payment_amount) FILTER (WHERE D.type_payment = 'NONCASH'),0) AS non_cash
        FROM sl_invoice_visit_plan A
        INNER JOIN sl_admin_process_visit_plan B ON A.admin_process_visit_plan_id = B.admin_process_visit_plan_id
        LEFT JOIN sl_invoice_payment C ON A.admin_process_visit_plan_id = C.admin_process_visit_plan_id AND A.customer_id = C.customer_id
        LEFT JOIN sl_invoice_payment_item D ON C.invoice_payment_id = D.invoice_payment_id
        WHERE A.admin_process_visit_plan_id = $admin_process_visit_plan_id
        ";

        $result = DB::select($query);
        $result2 = collect(DB::select($query2))->first();

        return [
            "summaryCustomerList" => $result,
            "summaryPayment"  => $result2
        ];
    }
}