API tools faq
paste
Advertisement
Guest User

unit test upload salesman

a guest
Sep 13th, 2018
102
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 19.50 KB | None | 0 0
raw download clone embed print report
  1. BEGIN;
  2.  
  3. -- clear data-data yang terlibat, dont worry data akan di rollback di bawah
  4. DELETE FROM t_ou;
  5. DELETE FROM t_ou_type;
  6. DELETE FROM m_ou_structure;
  7. DELETE FROM m_partner;
  8. DELETE FROM m_partner_type;
  9. DELETE FROM m_partner_ou;
  10. DELETE FROM m_salesman_custom;
  11. DELETE FROM t_policy;
  12. DELETE FROM t_policy_ou;
  13. DELETE FROM t_user;
  14. DELETE FROM t_user_props;
  15. DELETE FROM t_user_role;
  16. DELETE FROM jl_upload_parameter;
  17. DELETE FROM ul_salesman;
  18.  
  19. -- init data sesuai kebutuhan
  20.  
  21. -- # init data ou type
  22. INSERT INTO t_ou_type(
  23.             ou_type_id, tenant_id, ou_type_code, ou_type_name, create_datetime,
  24.             create_user_id, update_datetime, update_user_id, version, active,
  25.             active_datetime, non_active_datetime, flg_bu, flg_accounting,
  26.             flg_legal, flg_sub_bu, flg_branch)
  27.     VALUES (2000001, 11, 'OU-001', 'OU bu or branch', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  28.             -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  29.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' ', 'Y', 'N',
  30.             'N', 'N', 'Y'),
  31.             (2000002, 11, 'OU-002', 'OU 002', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  32.             -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  33.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' ', 'Y', 'N',
  34.             'Y', 'Y', 'N');
  35.  
  36. -- # init data ou
  37. INSERT INTO t_ou(
  38.             ou_id, tenant_id, ou_code, ou_name, ou_parent_id, ou_type_id,
  39.             create_datetime, create_user_id, update_datetime, update_user_id,
  40.             version, active, active_datetime, non_active_datetime)
  41.     VALUES (1000001, 11, 'OU-01', 'OU 01', -99, 2000001,
  42.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  43.             0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' '),
  44.             (1000002, 11, 'OU-02', 'OU 02', -99, 2000001,
  45.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  46.             0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' '),
  47.             (1000003, 11, 'OU-03', 'OU 03', -99, 2000001,
  48.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  49.             0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' '),
  50.             (1000004, 11, 'OU-04', 'OU 04', -99, 2000001,
  51.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  52.             0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' '),
  53.             (1000005, 11, 'OU-05', 'OU 05', -99, 2000002,
  54.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  55.             0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' ');
  56.  
  57. -- # init data partner
  58. INSERT INTO m_partner(
  59.         partner_id, tenant_id, partner_code, partner_name, ctgr_partner_id,
  60.         partner_ext_name, npwp_id, holding_id, flg_holding, rank, create_datetime,
  61.         create_user_id, update_datetime, update_user_id, version, active,
  62.         active_datetime, non_active_datetime, line_of_business, price_level,
  63.         industry_type, npwp_status)
  64. VALUES
  65.         -- SALESMAN-001
  66.         (10001, 11, 'SALESMAN-001', 'SALESMAN 001', -99,
  67.         '', -99, -99, 'N', 'G', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  68.         -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  69.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' ', ' ', ' ',
  70.         ' ', ' '),
  71.         -- SALESMAN-002
  72.         (10002, 11, 'SALESMAN-002', 'SALESMAN 002', -99,
  73.         '', -99, -99, 'N', 'G', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  74.         -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  75.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' ', ' ', ' ',
  76.         ' ', ' '),
  77.         -- CUSTOMER-002
  78.         (10003, 11, 'CUSTOMER-001', 'CUSTOMER 001', 1000001,
  79.         '', -99, -99, 'N', 'G', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  80.         -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  81.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' ', ' ', ' ',
  82.         ' ', ' ');
  83.  
  84. -- # init data partner type
  85. INSERT INTO m_partner_type(
  86.         tenant_id, partner_id, group_partner, type_partner_id,
  87.         due_date, curr_code, amount_limit, flg_commision, create_datetime,
  88.         create_user_id, update_datetime, update_user_id, version, active,
  89.         active_datetime, non_active_datetime)
  90. VALUES (11, 10001, 'E', 1000004, --> type partner SLS
  91.         -99, 'IDR', -99::numeric, 'N', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  92.         -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  93.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' '),
  94.         (11, 10002, 'E', 1000004, --> type partner SLS
  95.         -99, 'IDR', -99::numeric, 'N', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  96.         -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  97.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' '),
  98.         (11, 10003, 'C', 1000001,  --> type partner CUST
  99.         -99, 'IDR', -99::numeric, 'N', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  100.         -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  101.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' ');
  102.  
  103. -- # init data partner ou      
  104. INSERT INTO m_partner_ou(
  105.         partner_id, ou_id, version, create_datetime, create_user_id,
  106.         update_datetime, update_user_id)
  107. VALUES (10001, 1000001, 0, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  108.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1),
  109.         (10002, 1000002, 0, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  110.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1),
  111.         (10003, 1000003, 0, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  112.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1);
  113.  
  114. -- # init data policy
  115. INSERT INTO t_policy(
  116.         policy_id, tenant_id, policy_code, policy_name, create_datetime,
  117.         create_user_id, update_datetime, update_user_id, version, active,
  118.         active_datetime, non_active_datetime)
  119. VALUES (201, 11, 'POLICY-201', 'POLICY 201', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  120.         -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  121.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' '),
  122.         (202, 11, 'POLICY-202', 'POLICY 202', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  123.         -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  124.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' '),
  125.         (203, 11, 'POLICY-203', 'POLICY 203', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  126.         -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y',
  127.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), ' ');
  128.        
  129. -- # init data policy ou
  130. INSERT INTO t_policy_ou(
  131.             policy_ou_id, policy_id, ou_id, create_datetime, create_user_id,
  132.             update_datetime, update_user_id, version, active, active_datetime,
  133.             non_active_datetime)
  134.     VALUES (301, 201, 1000001, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  135.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  136.             ' '),
  137.             (302, 202, 1000002, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  138.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  139.             ' '),
  140.             (303, 201, 1000002, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  141.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  142.             ' '),
  143.             (304, 203, 1000003, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  144.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  145.             ' ');
  146.        
  147. -- # init data user
  148. WITH insert_data_user AS (
  149.     INSERT INTO t_user(
  150.         user_id, tenant_id, username, email, fullname, password, phone,
  151.         role_default_id, private_key, create_datetime, create_user_id,
  152.         update_datetime, update_user_id, version, active, active_datetime,
  153.         non_active_datetime, ou_default_id, policy_default_id)
  154.     VALUES (101, 11, 'SALESMAN-001', '', 'SALESMAN 001', 'SALESMAN-001'||'_'||to_char(current_timestamp, 'YYYYMMDDHH24MISS'), '',
  155.         15000075, md5('SALESMAN-001'||'_'||to_char(current_timestamp, 'YYYYMMDDHH24MISS')), to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  156.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  157.         ' ', 1000001, 201),
  158.         (102, 11, 'SALESMAN-002', '', 'SALESMAN 002', 'SALESMAN-002'||'_'||to_char(current_timestamp, 'YYYYMMDDHH24MISS'), '',
  159.         15000075, md5('SALESMAN-002'||'_'||to_char(current_timestamp, 'YYYYMMDDHH24MISS')), to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  160.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  161.         ' ', 1000002, 202)
  162.     RETURNING *
  163. ), insert_data_user_prop AS (
  164.    
  165.     -- # init data user props
  166.     INSERT INTO t_user_props(
  167.                 prop_key, user_id, prop_val, create_datetime, create_user_id,
  168.                 update_datetime, update_user_id, version)
  169.     SELECT 'count.login', A.user_id, 0::character varying, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  170.                 to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0
  171.     FROM insert_data_user A
  172.    
  173.     UNION ALL
  174.    
  175.     SELECT 'last.login', A.user_id, ' ', to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  176.                 to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 1, 0
  177.     FROM insert_data_user A
  178. )
  179. -- # init data user role
  180. INSERT INTO t_user_role(
  181.             user_id, role_id, policy_id, create_datetime, create_user_id,
  182.             update_datetime, update_user_id, version, active, active_datetime,
  183.             non_active_datetime)
  184. SELECT A.user_id, A.role_default_id, A.policy_default_id, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1,
  185.             to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, 0, 'Y', to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
  186.             ' '
  187. FROM insert_data_user A;
  188.  
  189. -- # init data upload parameter
  190. INSERT INTO jl_upload_parameter(
  191.         upload_header_id, key, value, create_user_id,
  192.         create_datetime, update_user_id, update_datetime, version)
  193. VALUES (111, 'tenantId', '11', -1,
  194.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  195.         (112, 'tenantId', '11', -1,
  196.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  197.         (113, 'tenantId', '11', -1,
  198.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  199.         (114, 'tenantId', '11', -1,
  200.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  201.         (115, 'tenantId', '11', -1,
  202.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  203.         (116, 'tenantId', '11', -1,
  204.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  205.         (117, 'tenantId', '11', -1,
  206.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  207.         (118, 'tenantId', '11', -1,
  208.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  209.         (119, 'tenantId', '11', -1,
  210.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  211.         (120, 'tenantId', '11', -1,
  212.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  213.        
  214.         (111, 'userId', '-1', -1,
  215.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  216.         (112, 'userId', '-1', -1,
  217.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  218.         (113, 'userId', '-1', -1,
  219.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  220.         (114, 'userId', '-1', -1,
  221.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  222.         (115, 'userId', '-1', -1,
  223.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  224.         (116, 'userId', '-1', -1,
  225.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  226.         (117, 'userId', '-1', -1,
  227.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  228.         (118, 'userId', '-1', -1,
  229.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  230.         (119, 'userId', '-1', -1,
  231.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  232.         (120, 'userId', '-1', -1,
  233.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  234.        
  235.         (111, 'datetime', '20180913000000', -1,
  236.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  237.         (112, 'datetime', '20180913000000', -1,
  238.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  239.         (113, 'datetime', '20180913000000', -1,
  240.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  241.         (114, 'datetime', '20180913000000', -1,
  242.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  243.         (115, 'datetime', '20180913000000', -1,
  244.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  245.         (116, 'datetime', '20180913000000', -1,
  246.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  247.         (117, 'datetime', '20180913000000', -1,
  248.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  249.         (118, 'datetime', '20180913000000', -1,
  250.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  251.         (119, 'datetime', '20180913000000', -1,
  252.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0),
  253.         (120, 'datetime', '20180913000000', -1,
  254.         to_char(current_timestamp, 'YYYYMMDDHH24MISS'), -1, to_char(current_timestamp, 'YYYYMMDDHH24MISS'), 0);
  255.  
  256. INSERT INTO ul_salesman(
  257.             upload_header_id, status, message, update_status,
  258.             kode_ou, kode_salesman, first_name, last_name, active)
  259. VALUES (111, '', '', '',
  260.         'OU-01', 'SALESMAN-001', 'SLS', '01', 'Y'),
  261.         (111, '', '', '',
  262.         'OU-02', 'SALESMAN-002', 'SLS', '02', 'N'),
  263.         (111, '', '', '',
  264.         'OU-03', 'SALESMAN-003', 'SLS', '03', 'Y'),
  265.         (111, '', '', '',
  266.         'OU-04', 'SALESMAN-004', 'SLS', '04', 'Y');
  267.  
  268. /** RUN TEST SUCCESS **/
  269. SELECT ul_upload_salesman(111);SELECT * FROM ul_salesman WHERE upload_header_id = 111;
  270.  
  271. WITH data_result_test_success AS (
  272.     /** Pastikan perubahan nama pada salesman **/
  273.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'Perubahan nama Salesman SALESMAN-001' AS case
  274.     FROM m_partner
  275.     WHERE partner_code = 'SALESMAN-001'
  276.     AND partner_name = 'SLS 01'
  277.    
  278.     UNION ALL
  279.    
  280.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'Perubahan nama Salesman SALESMAN-002' AS case
  281.     FROM m_partner
  282.     WHERE partner_code = 'SALESMAN-002'
  283.     AND partner_name = 'SLS 02'
  284.    
  285.     UNION ALL
  286.    
  287.     /** Pastikan data user full name berubah **/
  288.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'Perubahan nama Username SALESMAN-001' AS case
  289.     FROM t_user
  290.     WHERE username = 'SALESMAN-001'
  291.     AND fullname = 'SLS 01'
  292.    
  293.     UNION ALL
  294.    
  295.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'Perubahan nama Username SALESMAN-002' AS case
  296.     FROM t_user
  297.     WHERE username = 'SALESMAN-002'
  298.     AND fullname = 'SLS 02'
  299.    
  300.     UNION ALL
  301.    
  302.     /** Pastikan terbentuk salesman baru **/
  303.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'New data Salesman SALESMAN-003' AS case
  304.     FROM m_partner
  305.     WHERE partner_code = 'SALESMAN-003'
  306.     AND partner_name = 'SLS 03'
  307.    
  308.     UNION ALL
  309.    
  310.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'New data Salesman SALESMAN-004' AS case
  311.     FROM m_partner
  312.     WHERE partner_code = 'SALESMAN-004'
  313.     AND partner_name = 'SLS 04'
  314.    
  315.     UNION ALL
  316.    
  317.     /** Pastikan data user terbentuk **/
  318.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END message, 'New data User SALESMAN-003' AS case
  319.     FROM t_user
  320.     WHERE username = 'SALESMAN-003'
  321.     AND fullname = 'SLS 03'
  322.    
  323.     UNION ALL
  324.    
  325.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END message, 'New data User SALESMAN-004' AS case
  326.     FROM t_user
  327.     WHERE username = 'SALESMAN-004'
  328.     AND fullname = 'SLS 04'
  329.    
  330.     UNION ALL
  331.    
  332.     /**
  333.      * Pastikan data user props terbentuk untuk SALESMAN-003 & SALESMAN-004
  334.      */
  335.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'New data User Props count.login SALESMAN-003' AS case
  336.     FROM t_user_props
  337.     WHERE prop_key = 'count.login'
  338.     AND f_get_username(user_id) = 'SALESMAN-003'
  339.    
  340.     UNION ALL
  341.    
  342.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'New data User Props last.login SALESMAN-003' AS case
  343.     FROM t_user_props
  344.     WHERE prop_key = 'last.login'
  345.     AND f_get_username(user_id) = 'SALESMAN-003'
  346.    
  347.     UNION ALL
  348.    
  349.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'New data User Props count.login SALESMAN-004' AS case
  350.     FROM t_user_props
  351.     WHERE prop_key = 'count.login'
  352.     AND f_get_username(user_id) = 'SALESMAN-004'
  353.    
  354.     UNION ALL
  355.    
  356.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'New data User Props last.login SALESMAN-004' AS case
  357.     FROM t_user_props
  358.     WHERE prop_key = 'last.login'
  359.     AND f_get_username(user_id) = 'SALESMAN-004'
  360.    
  361.     UNION ALL
  362.    
  363.     /**
  364.      * Pastikan data user role terbentuk untuk SALESMAN-003 & SALESMAN-004
  365.      */
  366.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'New data User Role SALESMAN-003' AS case
  367.     FROM t_user_role
  368.     WHERE f_get_username(user_id) = 'SALESMAN-003'
  369.     AND role_id = 15000075
  370.    
  371.     UNION ALL
  372.    
  373.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'New data User Role SALESMAN-004' AS case
  374.     FROM t_user_role
  375.     WHERE f_get_username(user_id) = 'SALESMAN-004'
  376.     AND role_id = 15000075
  377.    
  378.     UNION ALL
  379.    
  380.     /**
  381.      * Pastikan terbentuk data t_policy untuk OU-04
  382.      * yang di pakai SALESMAN-004
  383.      * karena belum ada data policy ou dengan ou = OU-04 **/
  384.     SELECT CASE WHEN COUNT(1) = 0 THEN 'FAIL' ELSE 'SUCCESS' END AS message, 'New data Policy dari data OU-04 yang di pakai SALESMAN-004' AS case
  385.     FROM t_policy
  386.     WHERE policy_code = 'OU-04_20180913000000'
  387.     AND policy_name = 'OU 04'
  388. )
  389.  
  390. /** SHOW RESULT TEST SUCCESS **/
  391. SELECT * FROM data_result_test_success;
  392.  
  393. ROLLBACK;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!