Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement