<?php

namespace Sts\WebToko\BO;

use Sts\PleafCore\BusinessFunction;
use Sts\WebToko\Model\Product;
use Sts\WebToko\Model\RecordOwner;
use Sts\WebToko\Model\InProductBalance;
use Sts\WebToko\Model\InProductBalanceStock;
use Sts\WebToko\Model\Warehouse;
use Sts\WebToko\Model\SellPriceLevel;
use DB;
/**
 * @in
 * @In(["keyword","string","true","doc no OP"])
 * @In(["limit","numeric","true","limit"])
 * @In(["offset","numeric","true","offset"])
 */
class GetProductPosList implements BusinessFunction {

    public function getDescription(){
        return "Get Product Pos List";
    }

    public function execute($dto){

        $keyword        = strtoupper($dto["keyword"]);

        $limit          = $dto["limit"];
        $offset         = $dto["offset"];
        $recordOwnerId  = $dto["record_owner_id"];

        $recordOwner    = RecordOwner::find($recordOwnerId);

        $raw = "
        WITH product_list AS (

                SELECT A.product_id, A.product_code, A.product_name, A.product_local_name, A.part_no, A.brand_name,
                B.qty1, B.qty2, B.qty3, B.qty4, B.qty5, B.price1, B.price2, B.price3, B.price4, B.price5, B.price1 as price,
                D.qty as current_qty, D.product_balance_id, D.product_balance_stock_id
                FROM m_product A
                JOIN m_sell_price_level B ON B.product_id = A.product_id
                JOIN in_product_balance C ON C.product_id = A.product_id
                JOIN in_product_balance_stock D ON D.product_balance_id = C.product_balance_id
                    AND A.product_id = C.product_id
                    AND D.warehouse_id = :warehouseId
                    AND D.product_status = 'GOOD'
                    AND D.record_owner_id = :recordOwnerId
                WHERE A.record_owner_id IN (:recordOwnerId, -99)
                AND B.record_owner_id = :recordOwnerId
                AND (UPPER(A.product_code) LIKE :keyword OR UPPER(A.product_name) LIKE :keyword)
                ORDER BY A.product_name ASC
                LIMIT :limit OFFSET :offset
            )
            SELECT *, f_get_product_ctgr_name_by_product_id(product_id) as category_name, f_get_sub_product_ctgr_name_by_product_id(product_id) as sub_category_name
            FROM product_list
            ";

        $result = DB::select(DB::raw($raw), [
            "warehouseId"   => $recordOwner->warehouse_id,
            "recordOwnerId" => $recordOwnerId,
            "keyword"       => "%".$keyword."%",
            "limit"         => $limit,
            "offset"        => $offset
        ]);

        return [
            "productList" => $result
        ];
    }
}