You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
375 lines
13 KiB
JavaScript
375 lines
13 KiB
JavaScript
export const Partisipasi = function (tahun, callback) {
|
|
let par = `
|
|
SELECT sum(reg) usp, (sum(usp1) + sum(usp2) + sum(usp3) + sum(bw) + sum(bt) + sum(bn) + sum(pp)) induk FROM (
|
|
SELECT
|
|
'{no}' \`no\`,
|
|
'=SUM(F{no}:J{no})' jml,
|
|
concat('', a.kode) kode,
|
|
a.nama,
|
|
if(
|
|
a.kota = '',
|
|
a.tgllahir,
|
|
concat(a.kota, ',', a.tgllahir)
|
|
) temtala,
|
|
a.jk,
|
|
a.telp,
|
|
a.alamat,
|
|
a.unitkerja,
|
|
a.masuk,
|
|
(
|
|
ifnull(
|
|
IF(b.kodesp = 'REG', b.pokok, 0),
|
|
0
|
|
) + ifnull(
|
|
IF(
|
|
b1.kodesp = 'USP1',
|
|
b1.pokok,
|
|
0
|
|
),
|
|
0
|
|
) + ifnull(
|
|
IF(
|
|
b2.kodesp = 'USP2',
|
|
b2.pokok,
|
|
0
|
|
),
|
|
0
|
|
) + ifnull(
|
|
IF(
|
|
b3.kodesp = 'USP3',
|
|
b3.pokok,
|
|
0
|
|
),
|
|
0
|
|
) + ifnull(
|
|
IF(c.bank = 'cash', c.lunas, 0),
|
|
0
|
|
) + ifnull(bbw.total, 0) + ifnull(d.lunas, 0)
|
|
) \`all\`,
|
|
ifnull(h.total, 0) pdt,
|
|
ifnull(
|
|
IF(
|
|
tk.kodesp = 'tk',
|
|
tk.jumlah,
|
|
0
|
|
),
|
|
0
|
|
) tk,
|
|
ifnull(
|
|
IF(
|
|
sm.kodesp = 'sm',
|
|
sm.jumlah,
|
|
0
|
|
),
|
|
0
|
|
) sm,
|
|
ifnull(
|
|
IF(
|
|
sw.kodesp = 'sw',
|
|
sw.jumlah,
|
|
0
|
|
),
|
|
0
|
|
) sw,
|
|
ifnull(
|
|
IF(
|
|
sp.kodesp = 'sp',
|
|
sp.jumlah,
|
|
0
|
|
),
|
|
0
|
|
) sp,
|
|
ifnull(
|
|
IF(
|
|
swp.kodesp = 'swp',
|
|
swp.jumlah,
|
|
0
|
|
),
|
|
0
|
|
) swp,
|
|
ifnull(bbw.total, 0) bw,
|
|
ifnull(
|
|
IF(b.kodesp = 'REG', b.pokok, 0),
|
|
0
|
|
) reg,
|
|
ifnull(
|
|
IF(
|
|
b1.kodesp = 'USP1',
|
|
b1.pokok,
|
|
0
|
|
),
|
|
0
|
|
) usp1,
|
|
ifnull(
|
|
IF(
|
|
b2.kodesp = 'USP2',
|
|
b2.pokok,
|
|
0
|
|
),
|
|
0
|
|
) usp2,
|
|
ifnull(
|
|
IF(
|
|
b3.kodesp = 'USP3',
|
|
b3.pokok,
|
|
0
|
|
),
|
|
0
|
|
) usp3,
|
|
ifnull(
|
|
IF(c.bank = 'cash', c.lunas, 0),
|
|
0
|
|
) bt,
|
|
ifnull(
|
|
IF(w.bank <> 'cash', w.lunas, 0),
|
|
0
|
|
) bn,
|
|
ifnull(d.lunas, 0) pp,
|
|
'=SUM(C{no}:J{no})' tot
|
|
FROM
|
|
anggota a
|
|
LEFT JOIN (
|
|
SELECT kode, kodesp, sum(
|
|
if(dk = 'd', jumlah, jumlah * -1)
|
|
) jumlah
|
|
FROM msimpan
|
|
WHERE
|
|
tgl LIKE '${tahun}%'
|
|
AND kodesp = 'sm'
|
|
GROUP BY
|
|
kode,
|
|
kodesp
|
|
) sm ON sm.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT kode, kodesp, sum(
|
|
if(dk = 'd', jumlah, jumlah * -1)
|
|
) jumlah
|
|
FROM msimpan
|
|
WHERE
|
|
tgl LIKE '${tahun}%'
|
|
AND kodesp = 'tk'
|
|
GROUP BY
|
|
kode,
|
|
kodesp
|
|
) tk ON tk.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT kode, kodesp, sum(
|
|
if(dk = 'd', jumlah, jumlah * -1)
|
|
) jumlah
|
|
FROM msimpan
|
|
WHERE
|
|
tgl LIKE '${tahun}%'
|
|
AND kodesp = 'sw'
|
|
GROUP BY
|
|
kode,
|
|
kodesp
|
|
) sw ON sw.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT kode, kodesp, sum(
|
|
if(dk = 'd', jumlah, jumlah * -1)
|
|
) jumlah
|
|
FROM msimpan
|
|
WHERE
|
|
tgl LIKE '${tahun}%'
|
|
AND kodesp = 'swp'
|
|
GROUP BY
|
|
kode,
|
|
kodesp
|
|
) swp ON swp.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT kode, kodesp, sum(
|
|
if(dk = 'd', jumlah, jumlah * -1)
|
|
) jumlah
|
|
FROM msimpan
|
|
WHERE
|
|
tgl LIKE '${tahun}%'
|
|
AND kodesp = 'sp'
|
|
GROUP BY
|
|
kode,
|
|
kodesp
|
|
) sp ON sp.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT kodesc kode, 'BW' kodesp, sum(kredit) jumlah
|
|
FROM totsl
|
|
WHERE
|
|
kredit <> 0
|
|
GROUP BY
|
|
kodesc
|
|
) bw ON bw.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT kodesc kode, sum(debet) total
|
|
FROM totsl
|
|
WHERE
|
|
debet <> 0
|
|
AND tgl LIKE '${tahun}%'
|
|
GROUP BY
|
|
kode
|
|
) bbw ON bbw.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT *
|
|
FROM (
|
|
SELECT kode, kodesp, sum(pokok) pokok
|
|
FROM (
|
|
SELECT kode, kodesp, sum(partisipasi) pokok
|
|
FROM spartisipasi
|
|
WHERE
|
|
tgl LIKE '${tahun}%'
|
|
GROUP BY
|
|
kode, kodesp
|
|
UNION ALL
|
|
SELECT mags.kode, m.kodesp, sum(mags.shu) pokok
|
|
FROM mags
|
|
LEFT JOIN mpinjam m ON m.faktur = mags.fkt
|
|
WHERE
|
|
mags.tgl LIKE '${tahun}%'
|
|
AND mags.keterangan NOT LIKE 'Posting: Data Awal%'
|
|
GROUP BY
|
|
kode, kodesp
|
|
) a
|
|
GROUP BY
|
|
kode, kodesp
|
|
HAVING
|
|
kodesp = 'reg'
|
|
) a
|
|
) b ON b.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT *
|
|
FROM (
|
|
SELECT kode, kodesp, sum(pokok) pokok
|
|
FROM (
|
|
SELECT kode, kodesp, sum(partisipasi) pokok
|
|
FROM spartisipasi
|
|
WHERE
|
|
tgl LIKE '${tahun}%'
|
|
GROUP BY
|
|
kode, kodesp
|
|
UNION ALL
|
|
SELECT mags.kode, m.kodesp, sum(mags.shu) pokok
|
|
FROM mags
|
|
LEFT JOIN mpinjam m ON m.faktur = mags.fkt
|
|
WHERE
|
|
mags.tgl LIKE '${tahun}%'
|
|
AND mags.keterangan NOT LIKE 'Posting: Data Awal%'
|
|
GROUP BY
|
|
kode, kodesp
|
|
) a
|
|
GROUP BY
|
|
kode, kodesp
|
|
HAVING
|
|
kodesp = 'USP1'
|
|
) a
|
|
) b1 ON b1.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT *
|
|
FROM (
|
|
SELECT kode, kodesp, sum(pokok) pokok
|
|
FROM (
|
|
SELECT kode, kodesp, sum(partisipasi) pokok
|
|
FROM spartisipasi
|
|
WHERE
|
|
tgl LIKE '${tahun}%'
|
|
GROUP BY
|
|
kode, kodesp
|
|
UNION ALL
|
|
SELECT mags.kode, m.kodesp, sum(mags.shu) pokok
|
|
FROM mags
|
|
LEFT JOIN mpinjam m ON m.faktur = mags.fkt
|
|
WHERE
|
|
mags.tgl LIKE '${tahun}%'
|
|
AND mags.keterangan NOT LIKE 'Posting: Data Awal%'
|
|
GROUP BY
|
|
kode, kodesp
|
|
) a
|
|
GROUP BY
|
|
kode, kodesp
|
|
HAVING
|
|
kodesp = 'USP2'
|
|
) a
|
|
) b2 ON b2.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT *
|
|
FROM (
|
|
SELECT kode, kodesp, sum(pokok) pokok
|
|
FROM (
|
|
SELECT kode, kodesp, sum(partisipasi) pokok
|
|
FROM spartisipasi
|
|
WHERE
|
|
tgl LIKE '${tahun}%'
|
|
GROUP BY
|
|
kode, kodesp
|
|
UNION ALL
|
|
SELECT mags.kode, m.kodesp, sum(mags.shu) pokok
|
|
FROM mags
|
|
LEFT JOIN mpinjam m ON m.faktur = mags.fkt
|
|
WHERE
|
|
mags.tgl LIKE '${tahun}%'
|
|
AND mags.keterangan NOT LIKE 'Posting: Data Awal%'
|
|
GROUP BY
|
|
kode, kodesp
|
|
) a
|
|
GROUP BY
|
|
kode, kodesp
|
|
HAVING
|
|
kodesp = 'USP3'
|
|
) a
|
|
) b3 ON b3.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT 'cash' bank, kodesc kode, sum(tunai - ifnull(kembali, 0)) lunas
|
|
FROM totsl
|
|
WHERE
|
|
tr = 2
|
|
AND tgl LIKE '${tahun}%'
|
|
GROUP BY
|
|
kode
|
|
) c ON c.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT 'bank' bank, kodesc kode, sum(tunai - ifnull(kembali, 0)) lunas
|
|
FROM totsl
|
|
WHERE
|
|
tr <> 2
|
|
AND tgl LIKE '${tahun}%'
|
|
GROUP BY
|
|
kode
|
|
) w ON w.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT totptg.kodesc kode, sum(ptg.lunas) lunas
|
|
FROM ptg
|
|
LEFT JOIN totptg ON ptg.faktur = totptg.faktur
|
|
WHERE
|
|
ptg.tgl LIKE '${tahun}%'
|
|
GROUP BY
|
|
kode
|
|
) d ON d.kode = a.kode
|
|
LEFT JOIN (
|
|
SELECT *
|
|
FROM (
|
|
SELECT kodesc kode, sum(kredit) total
|
|
FROM totsl
|
|
WHERE
|
|
kredit <> 0
|
|
GROUP BY
|
|
kode
|
|
UNION ALL
|
|
SELECT totsl.kodesc kode, sum(ptg.lunas) * -1 total
|
|
FROM ptg
|
|
LEFT JOIN totsl ON totsl.faktur = ptg.fkt
|
|
WHERE
|
|
totsl.kredit <> 0
|
|
GROUP BY
|
|
kode
|
|
) a
|
|
) h ON h.kode = a.kode
|
|
WHERE
|
|
a.status = 0
|
|
AND a.laporan = 1
|
|
GROUP BY
|
|
a.kode
|
|
) a
|
|
|
|
`;
|
|
AuditDevQuery(par, function (x) {
|
|
let [datax] = x
|
|
callback(datax);
|
|
})
|
|
|
|
} |