SELECT tbl.MemberID,
       tbl.Member,
       SUM(tbl.jumkomisi) AS 'TotKomisi',
       tbl.Bank AS 'Bank',
       tbl.SandiNi,
       aktif,
       (CASE
            WHEN tbl.SandiNi='0011111'
                 AND SUM(tbl.jumkomisi)<10000 THEN 'Tidak Transfer'
            WHEN tbl.SandiNi!='0011111'
                 AND SUM(tbl.jumkomisi)<15000 THEN 'Tidak Transfer'
            ELSE 'Transfer'
        END) AS transfer
FROM
  (SELECT rg.ID AS MemberID,
          CONCAT(rg.NamaMember, '(', rg.KdMember, ')') AS 'Member',
          SUM(kd.Nilai) AS jumkomisi,
          bi.NAMA_BANK AS Bank,
          bi.SANDI_NI AS 'SandiNi',
          kd.Jenis AS Jenis,
          CASE
              WHEN EXISTS
                     (SELECT *
                      FROM PenjualanData
                      WHERE PenjualID=MemberID
                        AND Tgl BETWEEN '2019-9-20' AND '2019-10-19'
                        AND StatusPembayaran='PAID')
                   AND
                     (SELECT sum(Total)
                      FROM PenjualanData p
                      JOIN PenjualanDetailData pd ON pd.PenjualanID=p.ID
                      WHERE p.PenjualID=MemberID
                        AND p.Tgl BETWEEN '2019-9-20' AND '2019-10-19'
                        AND p.StatusPembayaran='PAID'
                        AND pd.BarangID=1) >= 64000 THEN 'Aktif'
              WHEN kd.MemberID=1 THEN 'Aktif'
              ELSE 'Hangus'
          END AS aktif
   FROM KomisiData kd
   LEFT JOIN RegisterModel rg ON kd.MemberID = rg.ID
   LEFT JOIN PenjualanData pd ON pd.ID = kd.PenjualanID
   LEFT JOIN RegisterModel rg2 ON pd.PenjualID=rg2.ID
   LEFT JOIN RegisterModel rg3 ON kd.ReferID=rg3.ID
   LEFT JOIN bicodedata bi ON bi.ID=rg.BiCodeID
   WHERE kd.isRedeem = 0
     AND kd.TglKomisi BETWEEN '2019-9-20' AND '2019-10-19'
     AND kd.Jenis='JUAL'
     AND
       (SELECT COUNT(*)
        FROM PenjualanData
        WHERE PenjualID=MemberID
          AND Tgl BETWEEN '2019-9-20' AND '2019-10-19'
          AND StatusPembayaran='PAID')>0
   GROUP BY Member,
            memberid,
            bank,
            sandini,
            jenis,
            aktif
   UNION ALL SELECT rg.ID AS MemberID,
                    CONCAT(rg.NamaMember, '(', rg.KdMember, ')') AS 'Member',
                    SUM(kd.Nilai) AS jumkomisi,
                    bi.NAMA_BANK AS Bank,
                    bi.SANDI_NI AS 'SandiNi',
                    kd.Jenis AS Jenis,
                    'Aktif' AS aktif
   FROM KomisiData kd
   LEFT JOIN RegisterModel rg ON kd.MemberID = rg.ID
   LEFT JOIN PenjualanData pd ON pd.ID = kd.PenjualanID
   LEFT JOIN RegisterModel rg2 ON pd.PenjualID=rg2.ID
   LEFT JOIN RegisterModel rg3 ON kd.ReferID=rg3.ID
   LEFT JOIN bicodedata bi ON bi.ID=rg.BiCodeID
   WHERE kd.isRedeem = 0
     AND kd.TglKomisi < '2019-10-19'
     AND (kd.Jenis='DAFTAR'
          OR kd.Jenis='KODEUNIK')
   GROUP BY Member,
            memberid,
            bank,
            sandini,
            jenis
   UNION ALL SELECT rg.ID AS MemberID,
                    CONCAT(rg.NamaMember, '(', rg.KdMember, ')') AS 'Member',
                    SUM(kd.Nilai) AS jumkomisi,
                    bi.NAMA_BANK AS Bank,
                    bi.SANDI_NI AS 'SandiNi',
                    kd.Jenis AS Jenis,
                    'Aktif' AS aktif
   FROM KomisiData kd
   LEFT JOIN RegisterModel rg ON kd.MemberID = rg.ID
   LEFT JOIN PenjualanData pd ON pd.ID = kd.PenjualanID
   LEFT JOIN RegisterModel rg2 ON pd.PenjualID=rg2.ID
   LEFT JOIN RegisterModel rg3 ON kd.ReferID=rg3.ID
   LEFT JOIN bicodedata bi ON bi.ID=rg.BiCodeID
   WHERE kd.isRedeem = 0
     AND kd.Jenis != 'KODEUNIK'
     AND kd.TglKomisi < '2019-09-20'
   GROUP BY Member,
            memberid,
            bank,
            sandini,
            jenis) tbl
WHERE MemberID IS NOT NULL
GROUP BY Member,
         memberid,
         bank,
         sandini,
         aktif
HAVING NOT ((SandiNi='0011111'
             AND SUM(jumkomisi)<10000)
            OR (SandiNi!='0011111'
                AND SUM(jumkomisi)<15000))