<?php

namespace Sts\WebToko\BO;

use Sts\PleafCore\DefaultBusinessFunction;
use Sts\PleafCore\BusinessFunction;
use Sts\PleafCore\QueryBuilder;
use Sts\PleafCore\CreateNativeQuery;
use Sts\WebToko\Helpers\ConditionExpression;
use Sts\WebToko\Model\PuPr;
use Sts\WebToko\Model\PuPrItem;

class GetPurchaseRequestAdvanceList extends DefaultBusinessFunction implements BusinessFunction {

    public function getDescription() {
     return "Get Purchase Request Advance List";
    }

    public function process($dto) {

        $supplier_id = $dto["supplier_id"];
        $record_owner_id = $dto["record_owner_id"];
        $date_from = $dto["date_from"];
        $date_to = $dto["date_to"];
        $doc_no = $dto["doc_no"];
        $flg_preorder = $dto["flg_preorder"];
        $status_doc = $dto["status_doc"];
        $limit = $dto["limit"];
        $offset = $dto["offset"];

        $queryBuilder = new QueryBuilder();
        $queryBuilder
                    ->add(" SELECT ")
                    ->add(" A.pr_id, A.doc_no, A.doc_date, A.supplier_id, f_get_supplier_code(A.supplier_id) AS supplier_code, ")
                    ->add(" A.ext_doc_no, A.ext_doc_date, A.remark, A.curr_code, A.flg_from_preorder, ")
                    ->add(" f_get_supplier_name(A.supplier_id) AS supplier_name, f_get_doc_desc(A.doc_type_id) AS doc_desc, ")
                    ->add(" A.status_doc, COALESCE(SUM(B.qty_request), 0) AS total_qty_request, COUNT(B.product_id) AS total_product, ")
                    ->add(" COALESCE(SUM(B.item_amount), 0) AS total_item_amount, COALESCE(SUM(B.qty_quotation), 0) AS total_qty_quotation, ")
                    ->add(" COALESCE(SUM(B.final_item_amount), 0) AS final_item_amount, ")
                    ->add(" COALESCE(SUM(B.bargain_unit_price), 0) AS total_bargain_unit_price, COALESCE(SUM(B.final_unit_price), 0) AS final_unit_price, A.version ")
                    ->add(" FROM ")->add(PuPr::getTableName())->add(" A ")
                    ->add(" LEFT JOIN ")->add(PuPrItem::getTableName())->add(" B ON A.pr_id = B.pr_id ")
                    ->add(" WHERE A.record_owner_id = :record_owner_id ")
//                    ->add(" AND A.doc_date BETWEEN :date_from AND :date_to ")
                    ->addIfNotEmpty($date_from, " AND A.doc_date >= :date_from ")
                    ->addIfNotEmpty($date_to, " AND A.doc_date <= :date_to ")
                    ->addIfNotEquals($doc_no, _EMPTY_VALUE, "AND".ConditionExpression::likeCaseInsensitive(" A.doc_no ", $doc_no))
                    ->addIfNotEquals($status_doc, _EMPTY_VALUE, " AND A.status_doc = :status_doc ")
                    ->addIfNotEquals($supplier_id, -1, " AND A.supplier_id = :supplier_id ")
                    ->addIfNotEmpty($flg_preorder, " AND A.flg_from_preorder = :flg_preorder ")
                    ->add(" GROUP BY A.pr_id, A.doc_no, A.doc_date, A.supplier_id, A.doc_type_id, A.status_doc, ")
                    ->add("     A.ext_doc_no, A.ext_doc_date, A.remark, A.curr_code, A.flg_from_preorder, A.version, A.update_datetime")
                    ->add(" ORDER BY A.doc_no DESC, A.doc_date DESC, A.update_datetime DESC")
                    ->add(" LIMIT :limit OFFSET :offset ");

        $query = new CreateNativeQuery($queryBuilder->toString());
        $query->setParameter("record_owner_id", $record_owner_id);
        if($status_doc != _EMPTY_VALUE)$query->setParameter("status_doc", $status_doc);
        if($supplier_id != -1)$query->setParameter("supplier_id", $supplier_id);
        if($flg_preorder != _EMPTY_VALUE)$query->setParameter("flg_preorder", $flg_preorder);
        if($date_from != _EMPTY_VALUE)$query->setParameter("date_from", $date_from);
        if($date_to != _EMPTY_VALUE)$query->setParameter("date_to", $date_to);
        $query->setParameter("limit", $limit);
        $query->setParameter("offset", $offset);

        $result = $query->getResultList();

        return [
            "purchase_request_list" => $result
        ];

    }

    protected function rules() {

        return [
            "supplier_id" => "required",
            "record_owner_id" => "required",
            "doc_type_id" => "required",
            "limit" => "required",
            "offset" => "required"
        ];

    }

}