SHARE
TWEET

Untitled

a guest Nov 19th, 2019 95 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT tbl.MemberID,
  2.        tbl.Member,
  3.        SUM(tbl.jumkomisi) AS 'TotKomisi',
  4.        tbl.Bank AS 'Bank',
  5.        tbl.SandiNi,
  6.        aktif,
  7.        (CASE
  8.             WHEN tbl.SandiNi='0011111'
  9.                  AND SUM(tbl.jumkomisi)<10000 THEN 'Tidak Transfer'
  10.             WHEN tbl.SandiNi!='0011111'
  11.                  AND SUM(tbl.jumkomisi)<15000 THEN 'Tidak Transfer'
  12.             ELSE 'Transfer'
  13.         END) AS transfer
  14. FROM
  15.   (SELECT rg.ID AS MemberID,
  16.           CONCAT(rg.NamaMember, '(', rg.KdMember, ')') AS 'Member',
  17.           SUM(kd.Nilai) AS jumkomisi,
  18.           bi.NAMA_BANK AS Bank,
  19.           bi.SANDI_NI AS 'SandiNi',
  20.           kd.Jenis AS Jenis,
  21.           CASE
  22.               WHEN EXISTS
  23.                      (SELECT *
  24.                       FROM PenjualanData
  25.                       WHERE PenjualID=MemberID
  26.                         AND Tgl BETWEEN '2019-9-20' AND '2019-10-19'
  27.                         AND StatusPembayaran='PAID')
  28.                    AND
  29.                      (SELECT sum(Total)
  30.                       FROM PenjualanData p
  31.                       JOIN PenjualanDetailData pd ON pd.PenjualanID=p.ID
  32.                       WHERE p.PenjualID=MemberID
  33.                         AND p.Tgl BETWEEN '2019-9-20' AND '2019-10-19'
  34.                         AND p.StatusPembayaran='PAID'
  35.                         AND pd.BarangID=1) >= 64000 THEN 'Aktif'
  36.               WHEN kd.MemberID=1 THEN 'Aktif'
  37.               ELSE 'Hangus'
  38.           END AS aktif
  39.    FROM KomisiData kd
  40.    LEFT JOIN RegisterModel rg ON kd.MemberID = rg.ID
  41.    LEFT JOIN PenjualanData pd ON pd.ID = kd.PenjualanID
  42.    LEFT JOIN RegisterModel rg2 ON pd.PenjualID=rg2.ID
  43.    LEFT JOIN RegisterModel rg3 ON kd.ReferID=rg3.ID
  44.    LEFT JOIN bicodedata bi ON bi.ID=rg.BiCodeID
  45.    WHERE kd.isRedeem = 0
  46.      AND kd.TglKomisi BETWEEN '2019-9-20' AND '2019-10-19'
  47.      AND kd.Jenis='JUAL'
  48.      AND
  49.        (SELECT COUNT(*)
  50.         FROM PenjualanData
  51.         WHERE PenjualID=MemberID
  52.           AND Tgl BETWEEN '2019-9-20' AND '2019-10-19'
  53.           AND StatusPembayaran='PAID')>0
  54.    GROUP BY Member,
  55.             memberid,
  56.             bank,
  57.             sandini,
  58.             jenis,
  59.             aktif
  60.    UNION ALL SELECT rg.ID AS MemberID,
  61.                     CONCAT(rg.NamaMember, '(', rg.KdMember, ')') AS 'Member',
  62.                     SUM(kd.Nilai) AS jumkomisi,
  63.                     bi.NAMA_BANK AS Bank,
  64.                     bi.SANDI_NI AS 'SandiNi',
  65.                     kd.Jenis AS Jenis,
  66.                     'Aktif' AS aktif
  67.    FROM KomisiData kd
  68.    LEFT JOIN RegisterModel rg ON kd.MemberID = rg.ID
  69.    LEFT JOIN PenjualanData pd ON pd.ID = kd.PenjualanID
  70.    LEFT JOIN RegisterModel rg2 ON pd.PenjualID=rg2.ID
  71.    LEFT JOIN RegisterModel rg3 ON kd.ReferID=rg3.ID
  72.    LEFT JOIN bicodedata bi ON bi.ID=rg.BiCodeID
  73.    WHERE kd.isRedeem = 0
  74.      AND kd.TglKomisi < '2019-10-19'
  75.      AND (kd.Jenis='DAFTAR'
  76.           OR kd.Jenis='KODEUNIK')
  77.    GROUP BY Member,
  78.             memberid,
  79.             bank,
  80.             sandini,
  81.             jenis
  82.    UNION ALL SELECT rg.ID AS MemberID,
  83.                     CONCAT(rg.NamaMember, '(', rg.KdMember, ')') AS 'Member',
  84.                     SUM(kd.Nilai) AS jumkomisi,
  85.                     bi.NAMA_BANK AS Bank,
  86.                     bi.SANDI_NI AS 'SandiNi',
  87.                     kd.Jenis AS Jenis,
  88.                     'Aktif' AS aktif
  89.    FROM KomisiData kd
  90.    LEFT JOIN RegisterModel rg ON kd.MemberID = rg.ID
  91.    LEFT JOIN PenjualanData pd ON pd.ID = kd.PenjualanID
  92.    LEFT JOIN RegisterModel rg2 ON pd.PenjualID=rg2.ID
  93.    LEFT JOIN RegisterModel rg3 ON kd.ReferID=rg3.ID
  94.    LEFT JOIN bicodedata bi ON bi.ID=rg.BiCodeID
  95.    WHERE kd.isRedeem = 0
  96.      AND kd.Jenis != 'KODEUNIK'
  97.      AND kd.TglKomisi < '2019-09-20'
  98.    GROUP BY Member,
  99.             memberid,
  100.             bank,
  101.             sandini,
  102.             jenis) tbl
  103. WHERE MemberID IS NOT NULL
  104. GROUP BY Member,
  105.          memberid,
  106.          bank,
  107.          sandini,
  108.          aktif
  109. HAVING NOT ((SandiNi='0011111'
  110.              AND SUM(jumkomisi)<10000)
  111.             OR (SandiNi!='0011111'
  112.                 AND SUM(jumkomisi)<15000))
RAW Paste Data
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!
 
Top