Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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
- ];
- }
- }
Add Comment
Please, Sign In to add comment