widana

sl_cart_add_item

Feb 20th, 2017
119
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION sl_cart_add_item(bigint, character varying, bigint, bigint, bigint, bigint)
  2.   RETURNS SETOF refcursor AS
  3. $BODY$
  4. DECLARE
  5.     pTenantId               alias for $1;
  6.     pDateTime               alias for $2;              
  7.     pUserId                 alias for $3;
  8.     pPartnerId              alias for $4;
  9.     pProductId              alias for $5;
  10.     pQty                    alias for $6;
  11. --    pStockPointCode           alias for $7;
  12.    
  13.     vYearMonth              character varying(6);
  14.     vDefaultOuId            bigint := -99;
  15.     vStatusDraft            character varying(1);
  16.     vStateDraft             character varying;
  17.     vSlCartId               bigint;
  18.     vSlCartItemId           bigint;
  19.     vSlCartItemLog          bigint;
  20.    
  21.     vSlCartDocNo            character varying(30);
  22.     vSlCartDocScheme        character varying(10);
  23.  
  24.     vProductId              bigint;
  25.     vMemberId               bigint;
  26.     vStockPointId           bigint;
  27.     vCartTypeDs             character varying;
  28.     vCartTypeMember         character varying;
  29.     vFlgDs                  character varying;
  30.     vEvent                  character varying;
  31.  
  32.     vRoundingMode           character varying;
  33.     vDecimalRounding        integer;
  34.     vGrossPrice             numeric := 0;
  35.     vDiscMemberPercent      numeric := 0;
  36.     vDiscMemberAmount       numeric := 0;
  37.     vDiscPromoPercent       numeric := 0;
  38.     vDiscPromoAmount        numeric := 0;
  39.    
  40.     vSubTotalPriceAfterDisc numeric;
  41.     vDsCommission           numeric := 0;
  42.    
  43.     vFlowSoMlmId            bigint;
  44.     vFlagYes                character varying(1);
  45.     vFlagNo                 character varying(1);
  46.     vSpaceValue             character varying(6);
  47.     vFlagRole               character varying(1);
  48.     vReqId                  character varying(50);
  49.     vUserWebDS              bigint;
  50.     vUserSms                bigint;
  51.     vUserMobile             bigint;
  52.    
  53.     vDocNo1                 character varying(10);
  54.     vDocNo2                 character varying(10);
  55.     vDocNoSo                character varying(10);
  56.     vDocNoPo                character varying(10);
  57.    
  58.     pRefHeader              REFCURSOR := 'refHeader';
  59.    
  60. BEGIN
  61.     vUserWebDS              := -3;
  62.     vUserSms                := -2;
  63.     vUserMobile             := -4;
  64.    
  65.     vDecimalRounding        := 0;
  66.     vSubTotalPriceAfterDisc := 0;
  67.     vStatusDraft            := 'D';
  68.     vStateDraft             := 'DRAFT';
  69.     vRoundingMode           :=  'RD';
  70.     vFlagYes                := 'Y';
  71.     vFlagNo                 := 'N';
  72.     vSpaceValue             := ' ';
  73.     vYearMonth              := ' ';
  74.     vCartTypeDs             := 'DS';
  75.     vcartTypeMember         := 'MEMBER';
  76.     vEvent                  := 'MEMBER.ADD';
  77.    
  78.     --vDefaultOuId := 14; diambil dari system config
  79. --    SELECT f_get_value_system_config_by_param_code(pTenantId, 'DEFAULT.OU.ID') INTO vDefaultOuId;
  80.    
  81. --    SELECT f_get_product_by_product_catalog_id(pProductCatalogId) INTO vProductId;
  82.  
  83.     -- cari partner_id
  84. --    SELECT B.partner_id INTO pPartnerId FROM mlm_member A JOIN mlm_ds B ON A.member_id = B.member_id WHERE A.member_code = pStockPointCode;
  85.    
  86.     -- cari member_id ds berdasarkan pStockPointCode
  87. --    SELECT B.member_id INTO vStockPointId FROM mlm_member A JOIN mlm_ds B ON A.member_id = B.member_id WHERE A.member_code = pStockPointCode;
  88.    
  89.     -- untuk mendapatkan member_Id ds maupun member
  90. --    SELECT member_id INTO vMemberId FROM mlm_member WHERE member_code = pMemberCode;
  91.    
  92. /*    --untuk mendapatkan flgDs
  93.     IF EXISTS( SELECT 1 FROM mlm_member A JOIN mlm_ds B ON A.member_id = B.member_id WHERE A.member_code = pMemberCode )
  94.     THEN vFlgDs := vFlagYes;
  95.     ELSE vFlgDs := vFlagNo;
  96.     END IF; */
  97.    
  98.     -- gross price
  99.     -- Harga katalog didapat via function
  100. --  SELECT f_get_catalog_price_by_partner(pPartnerId,pProductCatalogId) INTO vGrossPrice;
  101.  
  102.     -- disc member percent
  103. --    SELECT disc_member_percent INTO vDiscMemberPercent from m_product_catalog WHERE product_catalog_id = pProductCatalogId LIMIT 1;
  104.  
  105.     -- disc member percent
  106. --    SELECT disc_promo_percent INTO vDiscPromoPercent from m_product_catalog WHERE product_catalog_id = pProductCatalogId LIMIT 1;
  107.    
  108. --    SELECT f_get_value_system_config_by_param_code(pTenantId, 'dsCommision')::numeric INTO vDsCommission;
  109.    
  110.     -- TODO:
  111.     -- Samakan dengan model item
  112.     -- vDiscMemberAmount := vGrossPrice * vDiscMemberPercent * 0.01;
  113. --    SELECT f_calc_amount_after_disc(vGrossPrice, vDiscMemberPercent, 'RD', vDecimalRounding) INTO vDiscMemberAmount;
  114.    
  115.     -- vDiscPromoAmount := vGrossPrice * vDiscPromoPercent * 0.01;
  116. --    SELECT f_calc_amount_after_disc(vGrossPrice - vDiscMemberAmount, vDiscPromoPercent, 'RD', vDecimalRounding) INTO vDiscPromoAmount;
  117.    
  118.     -- Check apakah ada CART yang masih DRAFT untuk pPartnerId bersangkutan
  119.     IF EXISTS( SELECT 1 FROM sl_cart WHERE status_doc = vStatusDraft AND partner_id = pPartnerId)
  120.     THEN
  121.             -- ada yang draft, ambil sl cart id nya
  122.             SELECT sl_cart_id INTO vSlCartId FROM sl_cart
  123.             WHERE status_doc = vStatusDraft AND partner_id = pPartnerId
  124.             ORDER BY sl_cart_id DESC
  125.             LIMIT 1;
  126.                
  127.             -- UPDATE sl_cart SET update_user_id, update_datetime, version
  128.             UPDATE sl_cart SET update_user_id = pUserId, update_datetime = pDateTime, version = version + 1 WHERE sl_cart_id = vSlCartId;
  129.    
  130.     ELSE   
  131.             vSlCartId := nextval('sl_cart_seq');
  132.            
  133. --          SELECT f_get_value_system_config_by_param_code(11, 'DOC.NO.SO.PALOMAWEB') INTO vDocNoSo;
  134. --          SELECT f_get_value_system_config_by_param_code(11, 'DOC.NO.PO.PALOMAWEB') INTO vDocNoPo;
  135.             SELECT substring(current_date::text from 3 for 2)||substring(current_date::text from 6 for 2)||'/' INTO vDocNo1;
  136.             SELECT lpad(nextval('sl_cart_seq')::text, 6, '0') INTO vDocNo2;
  137.            
  138. /*          --insert ke sl_cart_commission
  139.             INSERT INTO sl_cart_commission(
  140.                     sl_cart_id, commission_percentage, create_datetime, create_user_id,
  141.                     update_datetime, update_user_id, version)
  142.             VALUES (vSlCartId,vDsCommission,pDateTime, pUserId,
  143.                     pDateTime, pUserId, 0); */
  144.  
  145.             -- Tidak ada yang draft, buat header
  146.             INSERT INTO sl_cart(
  147.                 sl_cart_id, tenant_id, ou_id, partner_id,
  148.                 doc_no, doc_date, remark, status_doc,
  149.                 create_user_id, create_datetime, update_user_id,
  150.                 update_datetime, version)
  151.             SELECT
  152.                 vSlCartId, pTenantId, vDefaultOuId, pPartnerId,
  153.                 vDocNo1||vDocNo2, substring(pDateTime,1,8) as doc_date, '', vStatusDraft,
  154.                 pUserId, pDateTime, pUserId, pDateTime, 0
  155.             FROM m_partner A
  156.             WHERE A.partner_id = pPartnerId;
  157.  
  158.     END IF;
  159.  
  160.     IF EXISTS( SELECT 1 FROM sl_cart_item WHERE sl_cart_id = vSlCartId  AND product_id = pProductId )
  161.  
  162.     THEN
  163.         UPDATE sl_cart_item Z
  164.         SET qty = Z.qty +pQty,
  165.             update_datetime = pDateTime,
  166.             update_user_id = pUserId,
  167.             version = Z.version+1
  168.         WHERE Z.sl_cart_id = vSlCartId
  169.         AND Z.product_id = pProductId;
  170.  
  171.         SELECT sl_cart_item_id INTO vSlCartItemId
  172.         FROM sl_cart_item
  173.         WHERE sl_cart_id = vSlCartId
  174.         AND product_id =pProductId;
  175.    
  176.     ELSE
  177.         vSlCartItemId :=nextval('sl_cart_item_seq');
  178.         -- insert ke sl_cart_item
  179.         INSERT INTO sl_cart_item(
  180.                 sl_cart_item_id,sl_cart_id, product_id, qty, catalog_price,
  181.                 discount_member,discount_member_amount, discount_promo,discount_promo_amount, price_after_discount,
  182.                 so_mlm_id, so_mlm_item_id, create_user_id, create_datetime, update_user_id,
  183.                 update_datetime, version)
  184.         VALUES (vSlCartItemId,vSlCartId, pProductId, pQty, vGrossPrice,
  185.                 vDiscMemberPercent,vDiscMemberAmount,vDiscPromoPercent,vDiscPromoAmount, f_calc_price_after_disc(vGrossPrice, vDiscMemberPercent, vDiscPromoPercent),  
  186.                 -99, -99, pUserId, pDateTime, pUserId,
  187.                 pDateTime, 0);
  188.     END IF;
  189.  
  190.     -- Update nilai nett sell price
  191.     UPDATE sl_cart_item SET total_price_after_disc = COALESCE(qty * price_after_discount,0)
  192.     WHERE sl_cart_id = vSlCartId;  
  193.    
  194.     -- Update sl_cart_item
  195.     WITH item AS (
  196.         SELECT A.sl_cart_id,SUM(B.total_price_after_disc) AS totalPrice,
  197.             SUM(B.catalog_price * B.qty) AS totalHargaKatalog,
  198.             SUM(B.discount_member_amount * B.qty) AS totalDiscMemberAmount,
  199.             SUM(B.discount_promo_amount * B.qty) AS totalDiscPromoAmount
  200.         FROM sl_cart A
  201.         JOIN sl_cart_item B ON A.sl_cart_id = B.sl_cart_id
  202.         WHERE A.sl_Cart_id = vSlCartId
  203.         GROUP BY a.sl_cart_id
  204.     )
  205.     UPDATE sl_cart A
  206.         SET total_ordered_amount = B.totalPrice,
  207.             total_ordered_harga_katalog = B.totalHargaKatalog,
  208.             total_ordered_discount_member_amount = B.totalDiscMemberAmount,
  209.             total_ordered_discount_promo_amount = B.totalDiscPromoAmount,
  210.             update_datetime = pDateTime,update_user_id = pUserId, version = A.version+1
  211.     FROM item B
  212.     WHERE A.sl_cart_id = B.sl_cart_id AND A.sl_cart_id = vSlCartId;
  213.    
  214.     vSlCartItemLog :=  nextval('sl_cart_item_log_seq');
  215.     -- Insert into sl_cart_item_log
  216.     INSERT INTO sl_cart_item_log(
  217.             sl_cart_item_log_id, sl_cart_item_id, qty, event, remark, version,
  218.             create_user_id, create_datetime, update_user_id, update_datetime)
  219.     SELECT  vSlCartItemLog,vSlCartItemId, pQty, vEvent, 'Produk ' || B.product_code || ' berhasil ditambahkan sebanyak ' || pQty :: varchar || ' oleh supplier.',0,
  220.             pUserId,pDateTime,pUserId,pDateTime
  221.     FROM sl_cart_item A
  222.     INNER JOIN m_product B ON A.product_id = B.product_id
  223.     WHERE A.sl_cart_item_id = vSlCartItemId;
  224.    
  225.     -- Data yang akan ditampilkan sebagai result
  226.     Open pRefHeader FOR
  227.         SELECT  
  228.             A.sl_cart_id, B.sl_cart_item_id, A.doc_no, A.doc_date, C.partner_id,  
  229.             D.product_id, D.product_code, D.product_name, B.qty, B.catalog_price,
  230.             B.discount_member, B.discount_promo,B.price_after_discount, B.total_price_after_disc, B.so_mlm_id, B.so_mlm_item_id,
  231.             B.discount_member_amount,B.discount_promo_amount,(B.catalog_price*B.qty) AS total_price,B.version
  232.         FROM sl_cart A
  233.         INNER JOIN sl_cart_item B ON A.sl_cart_id = B.sl_cart_id
  234.         INNER JOIN m_partner C ON C.partner_id = A.partner_id
  235.         INNER JOIN m_product D ON D.product_id = B.product_id
  236.         WHERE A.sl_cart_id = vSlCartId
  237.         AND B.sl_cart_item_id = vSlCartItemId;
  238.     RETURN NEXT pRefHeader;
  239.    
  240. --ELSE
  241. --  Open pRefHeader FOR
  242. -- 
  243. --  SELECT
  244. --      -99 AS sl_cart_id, -99 AS sl_cart_item_id, ' ' AS doc_no, ' ' AS doc_date, '' AS member_code,  
  245. --      -99 AS product_catalog_id, ' ' AS product_catalog_code, ' ' AS product_catalog_name, 0 AS qty, 0 AS catalog_price,
  246. --      0 AS discount_member, 0 AS discount_promo, 0 AS price_after_discount, 0 AS total_price_after_discount, -99 AS so_mlm_id, -99 AS so_mlm_item_id
  247. -- 
  248. --  RETURN NEXT pRefHeader;
  249.    
  250. --END IF;
  251.  
  252.    
  253. END;
  254. $BODY$
  255.   LANGUAGE plpgsql VOLATILE
  256.   COST 100
  257.   ROWS 1000;
  258. /
Advertisement
Add Comment
Please, Sign In to add comment