|
WITH PrioritizedPinjaman AS (
|
|
SELECT
|
|
cms_anggota_id,
|
|
cms_kartupinjaman_id,
|
|
pinjamanno,
|
|
statusangsuran,
|
|
COALESCE(dateinvoiced, datereceived) AS tanggal_cair,
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY cms_anggota_id
|
|
ORDER BY
|
|
CASE
|
|
WHEN statusangsuran = 'AC' THEN 1 -- Prioritaskan pinjaman aktif
|
|
ELSE 2 -- Pinjaman lainnya
|
|
END,
|
|
GREATEST(dateinvoiced, datereceived) DESC -- Urutkan berdasarkan tanggal cair termuda
|
|
) AS rn
|
|
FROM cms_kartupinjaman
|
|
WHERE statusangsuran = 'AC'
|
|
OR cms_kartupinjaman_id IN (
|
|
SELECT cms_kartupinjaman_id
|
|
FROM cms_kartuangsuran
|
|
WHERE tanggallunas IS NOT NULL
|
|
OR datedoc IS NOT NULL
|
|
)
|
|
),
|
|
PinjamanCount AS (
|
|
SELECT
|
|
cms_anggota_id,
|
|
COUNT(CASE WHEN statusangsuran = 'AC' THEN 1 END) AS jumlah_pinjaman_aktif,
|
|
COUNT(CASE WHEN statusangsuran != 'IA' THEN 1 END) AS jumlah_pinjaman_total
|
|
FROM cms_kartupinjaman
|
|
GROUP BY cms_anggota_id
|
|
)
|
|
|
|
SELECT
|
|
'Anggota Referator' AS kategori,
|
|
TO_CHAR(CURRENT_DATE + INTERVAL '1 day', 'YYYY-MM-DD') AS tgl_tarik_data,
|
|
ca.value AS no_anggota,
|
|
ckp.nama_perusahaan AS Affco,
|
|
ca.tanggal_masuk_kerja::DATE AS tgl_Kartap,
|
|
cca.tgl_masuk_koperasi::DATE AS tgl_masuk_koperasi,
|
|
lp.pinjamanno,
|
|
lp.tanggal_cair,
|
|
lp.statusangsuran,
|
|
CASE
|
|
WHEN lp.statusangsuran = 'PA' THEN COALESCE(ck.tanggallunas, ck.datedoc) -- Ambil tanggal lunas jika status PA
|
|
WHEN lp.statusangsuran = 'AC' THEN NULL -- Kosongkan jika status AC
|
|
ELSE NULL -- Kosongkan untuk status lainnya jika diperlukan
|
|
END AS tanggal_lunas_terakhir, -- Kolom ini ambil sesuai logika yang diinginkan
|
|
CASE
|
|
WHEN COALESCE(ck.tanggallunas, ck.datedoc) IS NOT NULL THEN
|
|
EXTRACT(YEAR FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))) * 12 +
|
|
EXTRACT(MONTH FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc)))
|
|
ELSE NULL -- Kosongkan jika tidak ada tanggal lunas
|
|
END AS bulan_terakhir_lunas, -- Menghitung selisih bulan dari tanggal lunas terakhir (baik tanggallunas atau datedoc)
|
|
pc.jumlah_pinjaman_aktif, -- Jumlah pinjaman aktif untuk masing-masing anggota
|
|
pc.jumlah_pinjaman_total -- Jumlah pinjaman total untuk masing-masing anggota
|
|
FROM cms_anggota ca
|
|
LEFT JOIN cms_calon_anggota cca
|
|
ON ca.cms_anggota_id = cca.cms_anggota_id
|
|
LEFT JOIN cms_keanggotaan_perusahaan ckp
|
|
ON ca.cms_keanggotaan_perusahaan_id = ckp.cms_keanggotaan_perusahaan_id
|
|
LEFT JOIN PrioritizedPinjaman lp
|
|
ON ca.cms_anggota_id = lp.cms_anggota_id
|
|
AND lp.rn = 1 -- Ambil data prioritas pertama (pinjaman aktif atau pinjaman dengan tanggal cair terbaru)
|
|
LEFT JOIN cms_kartuangsuran ck
|
|
ON lp.cms_kartupinjaman_id = ck.cms_kartupinjaman_id
|
|
LEFT JOIN PinjamanCount pc
|
|
ON ca.cms_anggota_id = pc.cms_anggota_id -- Menghubungkan dengan jumlah pinjaman berdasarkan anggota
|
|
WHERE 1=1
|
|
and ca.status_anggota IS NULL
|
|
AND lp.pinjamanno IS NOT null
|
|
and ca.value = @PARAMETER.NO_ANGGOTA@
|
|
AND (
|
|
(EXTRACT(YEAR FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))) * 12 +
|
|
EXTRACT(MONTH FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))) <= (
|
|
SELECT value::INTEGER FROM AD_SysConfig WHERE name = 'CMS_REFERATOR_BATAS_AKTIF'
|
|
))
|
|
OR COALESCE(
|
|
EXTRACT(YEAR FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))) * 12 +
|
|
EXTRACT(MONTH FROM AGE(CURRENT_DATE, COALESCE(ck.tanggallunas, ck.datedoc))),
|
|
NULL
|
|
) IS NULL -- Validasi bahwa bulan tidak lebih dari batas atau bernilai NULL
|
|
);
|