# [TNG] Thanh lý NPP

## 1. Load danh sách NPP

- SQL lấy danh sách, danh sách được lấy cho tất cả loại, không phân biệt NPP, Nhân viên, do dùng chung 1 hàm load **KendoUI**

-- Ctrl + f7 vào SQL Developer để dễ đọc.
WITH r1 AS ( SELECT DISTINCT s1.shop_id FROM shop s1 WHERE 1 = 1 AND s1.status = 1 AND s1.shop_id IN ( SELECT DISTINCT s2.shop_id FROM shop s2 WHERE 1 = 1 AND s2.status = 1 START WITH s2.shop_id =1 CONNECT BY PRIOR s2.shop_id = s2.parent_shop_id ) ),r2 AS ( SELECT DISTINCT s.shop_id FROM shop s WHERE 1 = 1 AND s.status = 1 START WITH s.shop_id IN ( SELECT shop_id FROM r1 ) CONNECT BY PRIOR s.shop_id = s.parent_shop_id ) SELECT shop_id AS shopid, shop_code AS shopcode, shop_name AS shopname, shop_code || ' - ' || shop_name AS text, parent_shop_id AS parentid, ( SELECT cn.node_ordinal FROM organization cn WHERE cn.status = 1 AND cn.node_type = 1 AND cn.organization_id = s.organization_id AND ROWNUM = 1 ) AS islevel, s.status AS status, st.specific_type AS objecttype FROM ( ( SELECT shop_id, shop_code, shop_name, shop_code || ' - ' || shop_name, parent_shop_id, shop_type_id, organization_id, s.status status FROM shop s WHERE s.shop_id IN ( SELECT shop_id FROM r2 ) ) UNION ( SELECT shop_id, shop_code, shop_name, shop_code || ' - ' || shop_name, parent_shop_id, shop_type_id, organization_id, s.status status FROM shop s START WITH s.shop_id IN ( SELECT shop_id FROM r2 ) CONNECT BY s.shop_id = PRIOR s.parent_shop_id ) ) s JOIN shop_type st ON st.shop_type_id = s.shop_type_id WHERE 1 = 1 ORDER BY islevel, shopcode ASC

## 2. Load nhân viên từ NPP đã chọn
> Truyền SHOP_ID và SPECIFIC_TYPE vào tuỳ theo loại NV

WITH lstshop AS  (  SELECT DISTINCT shop_id, shop_code  FROM shop  where status = 1  START WITH shop_id in (77 ) and status = 1  CONNECT BY prior shop_id = parent_shop_id  union all  (SELECT DISTINCT shop_id, shop_code  FROM shop  where status = 1  and shop_id not in ( 77) START WITH shop_id in ( 77 ) and status = 1  CONNECT BY prior parent_shop_id = shop_id)  )  select distinct s.staff_id as nvbhId, s.staff_code as nvbhCode, s.staff_name as nvbhName from staff s   join STAFF_TYPE st  on s.STAFF_TYPE_ID = st.STAFF_TYPE_ID  where 1 = 1  and st.SPECIFIC_TYPE = 1  and s.shop_id in (select shop_id from lstshop)  AND ( s.shop_id in (-1,-1,1,3,4,5,6,7,8,9,10,11,12,13,14,15,17,16,19,18,21,20,23,22,25,24,27,26,29,28,31,30,34,35,32,33,38,39,36,37,42,43,40,41,46,47,44,45,51,50,49,48,55,54,53,52,59,58,57,56,63,62,61,60,68,69,70,71,64,65,66,67,76,77,78,79,72,73,74,75,85,84,87,86,81,80,83,82,93,92,95,94,89,88,91,90,102,103,100,101,98,99,96,97,110,111,108,109,106,107,104,105,119,118,117,116,115,114,113,112,127,126,125,124,123,122,121,120,137,136,139,138,141,140,143,142,129,128,131,130,133,132,135,134,152,153,154,155,156,157,158,159,144,145,146,147,148,149,150,151,171,170,169,168,175,174,173,172,163,162,161,160,167,166,165,164,186,187,184,185,190,191,188,189,178,179,176,177,182,183,180,181,205,204,206,201,200,203,202,197,196,199,198,193,192,195,194,220,221,222,223,216,217,218,219,212,213,214,215,208,209,210,211,239,238,237,236,235,234,233,231,230,229,228,227,226,225,224,254,255,252,253,250,251,248,249,246,247,244,245,242,243,240,241,258,259,256,257,262,263,260,261,266,267,264,265,270,268,269))  order by nvbhId, nvbhCode, nvbhName

## 3. Load danh sách **Chuyển đổi từ loại NV**

 select stt.* from staff_type stt  where 1 = 1  and stt.status = 1  order by stt.name 

## 4. Load danh sách bên phải sau khi chọn ds *Chuyển đổi từ loại NV*

With ListSTAFF_SHOP as ( SELECT             DISTINCT st.STAFF_TYPE_ID,             st.name STAFFTYPE ,              sht.SHOP_TYPE_ID ,              sht.name SHOPTYPE,              ogDV.ORGANIZATION_ID                FROM                ORGANIZATION ogNV           JOIN                ORGANIZATION ogDV                   ON ogNV.PARENT_ORG_ID = ogDV.ORGANIZATION_ID            JOIN                STAFF_TYPE st                       ON ogNV.NODE_TYPE_ID = st.STAFF_TYPE_ID        JOIN                 SHOP_TYPE sht                       ON ogDV.NODE_TYPE_ID = sht.SHOP_TYPE_ID         WHERE               1=1             AND ogNV.status =1     AND ogDV.status =1               AND st.status =1                AND sht.status =1               AND ogNV.NODE_TYPE =2                   AND st.SPECIFIC_TYPE IN (1,2,3)                 AND st.STAFF_TYPE_ID = 1046                 ) SELECT  DISTINCT st.*         FROM                ORGANIZATION ogNV           JOIN       ORGANIZATION ogDV                    ON ogNV.PARENT_ORG_ID = ogDV.ORGANIZATION_ID            JOIN                STAFF_TYPE st                       ON ogNV.NODE_TYPE_ID = st.STAFF_TYPE_ID         JOIN                SHOP_TYPE sht              ON ogDV.NODE_TYPE_ID = sht.SHOP_TYPE_ID          WHERE               1=1    AND ogNV.status =1               AND ogDV.status =1              AND st.status =1                AND sht.status =1               AND ogNV.NODE_TYPE =2          AND st.SPECIFIC_TYPE IN (                    1,2,3               )              AND ogDV.ORGANIZATION_ID in (                        SELECT                      ORGANIZATION_ID                     FROM                        ORGANIZATION START WITH ORGANIZATION_ID in(SELECT                           ORGANIZATION_ID            FROM                         ListSTAFF_SHOP)                 CONNECT BY                          PRIOR PARENT_ORG_ID =ORGANIZATION_ID )       ORDER BY st.specific_type, st.name

## 5. Chỉnh sửa hiển thị MIỀN VÙNG:
- Hiện tại đang hiển thị toàn bộ - > **SAI**
- Bổ sung thêm đoạn lấy từ **organization** trường **cn.node_ordinal**
- Đoạn SQL thêm vào gốc ở **WHERE**:

AND (
         SELECT
             cn.node_ordinal
         FROM
             organization cn
         WHERE
             cn.status = 1
             AND cn.node_type = 1
             AND cn.organization_id = s.organization_id
             AND ROWNUM = 1
     ) IN (
         76
     )

- Theo đó thì:
- 76: NPP
- 75,74: Miền
- 73: Vùng
- Câu SQL hoàn chỉnh:

WITH r1 AS (
     SELECT DISTINCT
         s1.shop_id
     FROM
         shop s1
     WHERE
         1 = 1
         AND s1.status = 1
         AND s1.shop_id IN (
             SELECT DISTINCT
                 s2.shop_id
             FROM
                 shop s2
             WHERE
                 1 = 1
                 AND s2.status = 1
             START WITH
                 s2.shop_id = 1
             CONNECT BY
                 PRIOR s2.shop_id = s2.parent_shop_id
         )
 ),r2 AS (
     SELECT DISTINCT
         s.shop_id
     FROM
         shop s
     WHERE
         1 = 1
         AND s.status = 1
     START WITH
         s.shop_id IN (
             SELECT
                 shop_id
             FROM
                 r1
         )
     CONNECT BY
         PRIOR s.shop_id = s.parent_shop_id
 )
 SELECT
     shop_id            AS shopid,
     shop_code          AS shopcode,
     shop_name          AS shopname,
     shop_code
     || ' - '
     || shop_name AS text,
     parent_shop_id     AS parentid,
     (
         SELECT
             cn.node_ordinal
         FROM
             organization cn
         WHERE
             cn.status = 1
             AND cn.node_type = 1
             AND cn.organization_id = s.organization_id
             AND ROWNUM = 1
     ) AS islevel,
     s.status           AS status,
     st.specific_type   AS objecttype,
     (SELECT
             cn.node_ordinal
         FROM
             organization cn
         WHERE
             cn.status = 1
             AND cn.node_type = 1
             AND cn.organization_id = s.organization_id
             AND ROWNUM = 1)
 FROM
     (
         ( SELECT
             shop_id,
             shop_code,
             shop_name,
             shop_code
             || ' - '
             || shop_name,
             parent_shop_id,
             shop_type_id,
             organization_id,
             s.status   status
         FROM
             shop s
         WHERE
             s.shop_id IN (
                 SELECT
                     shop_id
                 FROM
                     r2
             )
         )
         UNION
         ( SELECT
             shop_id,
             shop_code,
             shop_name,
             shop_code
             || ' - '
             || shop_name,
             parent_shop_id,
             shop_type_id,
             organization_id,
             s.status   status
         FROM
             shop s
         START WITH
             s.shop_id IN (
                 SELECT
                     shop_id
                 FROM
                     r2
             )
         CONNECT BY
             s.shop_id = PRIOR s.parent_shop_id
         )
     ) s
     JOIN shop_type st ON st.shop_type_id = s.shop_type_id
 WHERE
     1 = 1
     AND (
         SELECT
             cn.node_ordinal
         FROM
             organization cn
         WHERE
             cn.status = 1
             AND cn.node_type = 1
             AND cn.organization_id = s.organization_id
             AND ROWNUM = 1
     ) IN (
         75,74
     )
 ORDER BY
     islevel,
     shopcode ASC;