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.
858 lines
37 KiB
Plaintext
858 lines
37 KiB
Plaintext
create or REPLACE VIEW anggota_member AS
|
|
SELECT a.kode, a.nama, if(b.avatar <> '', concat('https://www.warpekamart.com/olshop/assets/images/user/',b.avatar), '') avatar FROM anggota a
|
|
LEFT JOIN members b ON a.kode = b.kode;
|
|
|
|
create or replace VIEW akn AS
|
|
select `mainacc`.`ekuitas` AS `ekuitas`,`mainacc`.`mainkel` AS `amain`,`mainacc`.`subkel` AS `asub`,`acc`.`kode` AS `kode`,`acc`.`app` AS `app`,`acc`.`main` AS `main`,`acc`.`nama_main` AS `nama_main`,`acc`.`dk` AS `dk`,`acc`.`nr` AS `nr`,`acc`.`nama` AS `nama`,`acc`.`username` AS `username`,`acc`.`userlog` AS `userlog` from (`acc` left join `mainacc` on((`mainacc`.`kode` = `acc`.`main`)));
|
|
|
|
create or replace VIEW anggota_aktif AS
|
|
select `anggota`.`kode` AS `kode`,`anggota`.`nama` AS `nama` from `anggota` where ((`anggota`.`status` = 0) and (`anggota`.`laporan` = 1));
|
|
|
|
create or replace VIEW customer AS
|
|
select `a`.`kode` AS `kode`,`a`.`lama` AS `lama`,`a`.`masuk` AS `masuk`,`a`.`nama` AS `nama`,`a`.`nip` AS `nip`,`a`.`ktp` AS `ktp`,`a`.`dept` AS `dept`,`a`.`unit` AS `unit`,`a`.`unitkerja` AS `unitkerja`,`a`.`bagian` AS `bagian`,`a`.`status` AS `status`,`a`.`golkar` AS `golkar`,`a`.`gaji` AS `gaji`,`a`.`alamat` AS `alamat`,`a`.`telp` AS `telp`,`a`.`kota` AS `kota`,`a`.`pokok` AS `pokok`,`a`.`wajib` AS `wajib`,`a`.`manasuka` AS `manasuka`,`a`.`bw` AS `bw`,`a`.`tk` AS `tk`,`a`.`swp` AS `swp`,`a`.`uang` AS `uang`,`a`.`spuang` AS `spuang`,`a`.`bank` AS `bank`,`a`.`spbank` AS `spbank`,`a`.`spbarang` AS `spbarang`,`a`.`kunci` AS `kunci`,`a`.`nomor` AS `nomor`,`a`.`keluar` AS `keluar`,`a`.`dk` AS `dk`,`a`.`batas` AS `batas`,`a`.`idcard` AS `idcard`,`a`.`idlog` AS `idlog`,`a`.`kdpos` AS `kdpos`,`a`.`tgllahir` AS `tgllahir`,`a`.`jk` AS `jk`,`a`.`prima` AS `prima`,`a`.`jurubayar` AS `jurubayar`,`a`.`rekapgol` AS `rekapgol`,`d`.`keterangan` AS `deptket` from (`anggota` `a` left join `dept` `d` on((`d`.`kode` = `a`.`dept`)));
|
|
|
|
create or replace VIEW group_partisipasi AS
|
|
select `partisipasi`.`tahun` AS `tahun`,`partisipasi`.`kode` AS `kode`,`partisipasi`.`nama` AS `nama`,`partisipasi`.`reg` AS `usp`,((((((`partisipasi`.`usp1` + `partisipasi`.`usp2`) + `partisipasi`.`usp3`) + `partisipasi`.`bt`) + `partisipasi`.`bn`) + `partisipasi`.`bw`) + `partisipasi`.`pp`) AS `induk` from `partisipasi`;
|
|
|
|
create or replace VIEW total_angsuran AS
|
|
select substring_index(substring_index(`mags`.`fkt`,'/',2),'/',-(1)) AS `kodesp`,(sum(`mags`.`pokok`) * -(1)) AS `pokok` from `mags` group by substring_index(substring_index(`mags`.`fkt`,'/',2),'/',-(1));
|
|
|
|
create or replace VIEW total_penarikan AS
|
|
select `msimpan`.`kodesp` AS `kodesp`,(sum(`msimpan`.`jumlah`) * -(1)) AS `total` from `msimpan` where (`msimpan`.`dk` = 'K') group by `msimpan`.`kodesp` union all select 'BW' AS `kodesp`,(sum(`totsl`.`debet`) * -(1)) AS `total` from `totsl` where ((not((`totsl`.`faktur` like 'FS%'))) and (`totsl`.`debet` <> 0));
|
|
|
|
create or replace VIEW total_pinjaman AS
|
|
select `mpinjam`.`kodesp` AS `kodesp`,sum(`mpinjam`.`pokok`) AS `pokok` from `mpinjam` group by `mpinjam`.`kodesp`;
|
|
|
|
create or replace VIEW total_simpanan AS
|
|
select `msimpan`.`kodesp` AS `kodesp`,sum(`msimpan`.`jumlah`) AS `total` from `msimpan` where (`msimpan`.`dk` = 'D') group by `msimpan`.`kodesp`;
|
|
|
|
create or replace VIEW v_beban_acc AS
|
|
select `acc`.`kode` AS `kode`,`acc`.`main` AS `main`,`acc`.`nama_main` AS `nama_main`,`acc`.`nama` AS `nama` from `acc` where ((`acc`.`nama` like 'b.%') and (`acc`.`app` = 'acc')) order by `acc`.`kode`;
|
|
|
|
create or replace VIEW v_beban_usp AS
|
|
select `acc`.`kode` AS `kode`,`acc`.`main` AS `main`,`acc`.`nama_main` AS `nama_main`,`acc`.`nama` AS `nama` from `acc` where ((`acc`.`nama` like 'b.%') and (`acc`.`app` = 'usp')) order by `acc`.`kode`;
|
|
|
|
create or replace VIEW view_stk AS
|
|
select 'aw' AS `trx`,`stk`.`kode` AS `kode`,`stk`.`gudang` AS `gudang`,`stk`.`awal` AS `qty` from `stk` where (`stk`.`gudang` <> '') union select 'sl' AS `trx`,`a`.`kode` AS `kode`,`a`.`gudang` AS `gudang`,sum(`a`.`qty`) AS `qty` from (`sl` `a` left join `stock` `b` on((`a`.`kode` = `b`.`kode`))) group by `a`.`kode`,`a`.`gudang` union select 'slr' AS `trx`,`a`.`kode` AS `kode`,`a`.`gudang` AS `gudang`,sum(`a`.`qty`) AS `qty` from (`slr` `a` left join `stock` `b` on((`a`.`kode` = `b`.`kode`))) group by `a`.`kode`,`a`.`gudang` union select 'pc' AS `trx`,`a`.`kode` AS `kode`,`a`.`gudang` AS `gudang`,sum(`a`.`qty`) AS `qty` from (`pc` `a` left join `stock` `b` on((`a`.`kode` = `b`.`kode`))) group by `a`.`kode`,`a`.`gudang` union select 'pcr' AS `trx`,`a`.`kode` AS `kode`,`a`.`gudang` AS `gudang`,sum(`a`.`qty`) AS `qty` from (`pcr` `a` left join `stock` `b` on((`a`.`kode` = `b`.`kode`))) group by `a`.`kode`,`a`.`gudang` union select 'ad' AS `trx`,`a`.`kode` AS `kode`,`a`.`gudang` AS `gudang`,sum(`a`.`qty`) AS `qty` from (`adjst` `a` left join `stock` `b` on((`a`.`kode` = `b`.`kode`))) group by `a`.`kode`,`a`.`gudang`;
|
|
|
|
create or replace VIEW view_saldo AS
|
|
select `view_stk`.`kode` AS `kode`,`view_stk`.`gudang` AS `gudang`,ifnull(sum((if(((`view_stk`.`trx` = 'sl') or (`view_stk`.`trx` = 'sl2') or (`view_stk`.`trx` = 'pcr')),-(1),1) * `view_stk`.`qty`)),0) AS `akhir` from `view_stk` group by `view_stk`.`kode`,`view_stk`.`gudang`;
|
|
|
|
create or replace VIEW sisa_pinjaman AS
|
|
select `b`.`kodesp` AS `kodesp`,(`b`.`pokok` - `a`.`pokok`) AS `total` from (`total_pinjaman` `b` left join `total_angsuran` `a` on((`b`.`kodesp` = `a`.`kodesp`)));
|
|
|
|
create or replace VIEW sisa_simpanan AS
|
|
select `a`.`kodesp` AS `kodesp`,(`a`.`total` - `b`.`total`) AS `total` from (`total_simpanan` `a` left join `total_penarikan` `b` on((`a`.`kodesp` = `b`.`kodesp`)));
|
|
|
|
create or replace VIEW tahun_awal AS
|
|
select year(`totbb`.`tgl`) AS `tahun`,lpad(month(`totbb`.`tgl`),2,0) AS `bulan`,concat(year(`totbb`.`tgl`),'-',lpad(month(`totbb`.`tgl`),2,0)) AS `dates` from `totbb` where (`totbb`.`keterangan` like 'SALDO NERACA%') order by `totbb`.`tgl` desc limit 1;
|
|
|
|
CREATE OR REPLACE VIEW tglposting_angsuran AS
|
|
SELECT tgl, posting FROM mags WHERE posting IS NOT NULL AND posting <> 'dataawal' GROUP BY posting,tgl;
|
|
|
|
CREATE OR REPLACE VIEW tglposting_simpanan AS
|
|
SELECT tgl, posting FROM msimpan WHERE posting IS NOT NULL AND posting <> 'dataawal' GROUP BY posting,tgl;
|
|
|
|
CREATE OR REPLACE VIEW posting_simpanan AS
|
|
SELECT concat(posting, kodesp, kode) kodec, posting, kodesp, kode, tgl FROM msimpan
|
|
WHERE posting IS NOT NULL AND posting <> 'dataawal';
|
|
|
|
CREATE OR REPLACE VIEW posting_tagihan AS
|
|
SELECT
|
|
concat(kodetagihan,kodesp, kode) kodec
|
|
, m.kodetagihan
|
|
, m.faktur
|
|
, m.kodesp
|
|
, m.golkar
|
|
, m.kode
|
|
, m.jumlah
|
|
, m.pokok
|
|
, m.bunga
|
|
FROM mtagihan m
|
|
WHERE
|
|
m.statusposting = 1
|
|
AND (
|
|
m.kodesp = 'sm'
|
|
OR m.kodesp = 'tk'
|
|
OR m.kodesp = 'swp'
|
|
OR m.kodesp = 'sp'
|
|
OR m.kodesp = 'sw'
|
|
OR m.kodesp = 'bw'
|
|
);
|
|
|
|
CREATE OR REPLACE VIEW data_posting_piutang AS
|
|
SELECT totptg.posting, totptg.kodesc kode, ptg.fkt, totptg.tgl FROM totptg
|
|
LEFT JOIN ptg ON ptg.faktur = totptg.faktur
|
|
WHERE posting IS NOT NULL;
|
|
|
|
CREATE OR REPLACE VIEW posting_gagalbayar_piutang AS
|
|
SELECT
|
|
m.kodetagihan
|
|
, m.faktur
|
|
, m.kodesp
|
|
, m.golkar
|
|
, m.kode
|
|
, m.jumlah
|
|
, m.pokok
|
|
, m.bunga
|
|
FROM mtagihan m
|
|
LEFT JOIN data_posting_piutang c ON c.fkt = m.faktur
|
|
AND c.posting = m.kodetagihan
|
|
LEFT JOIN mgagalbayar h ON h.faktur = m.faktur
|
|
AND h.kodetagihan = m.kodetagihan
|
|
WHERE
|
|
m.statusposting = 1
|
|
AND (
|
|
m.kodesp = 'pt'
|
|
)
|
|
AND c.fkt IS NULL
|
|
AND h.kodetagihan IS NULL
|
|
GROUP BY m.kodetagihan, m.faktur;
|
|
|
|
CREATE OR REPLACE VIEW posting_mags AS
|
|
select
|
|
`mags`.`tgl` AS `tgl`
|
|
,`mags`.`fkt` AS `faktur`
|
|
,`mags`.`posting` AS `posting`
|
|
from `mags`
|
|
where (`mags`.`posting` is not null);
|
|
|
|
|
|
CREATE OR REPLACE VIEW tgl_posting_piutang AS
|
|
SELECT posting, tgl FROM data_posting_piutang GROUP BY posting;
|
|
|
|
CREATE OR REPLACE VIEW posting_gagalbayar_pinjaman AS
|
|
SELECT
|
|
m.kodetagihan
|
|
, m.faktur
|
|
, m.kodesp
|
|
, m.golkar
|
|
, m.kode
|
|
, m.jumlah
|
|
, m.pokok
|
|
, m.bunga
|
|
FROM mtagihan m
|
|
LEFT JOIN posting_mags c ON c.faktur = m.faktur
|
|
AND c.posting = m.kodetagihan
|
|
LEFT JOIN mgagalbayar h ON h.faktur = m.faktur
|
|
AND h.kodetagihan = m.kodetagihan
|
|
WHERE
|
|
m.statusposting = 1
|
|
AND (
|
|
m.kodesp = 'usp1'
|
|
OR m.kodesp = 'usp2'
|
|
OR m.kodesp = 'usp3'
|
|
OR m.kodesp = 'reg'
|
|
OR m.kodesp = 'khusus'
|
|
)
|
|
AND c.faktur IS NULL
|
|
AND h.kodetagihan IS NULL
|
|
GROUP BY m.kodetagihan, m.faktur;
|
|
|
|
CREATE OR REPLACE VIEW posting_gagalbayar_simpanan AS
|
|
SELECT
|
|
a.kodetagihan
|
|
, a.faktur
|
|
, a.kodesp
|
|
, a.golkar
|
|
, a.kode
|
|
, a.jumlah
|
|
, a.pokok
|
|
, a.bunga
|
|
FROM posting_tagihan a
|
|
LEFT JOIN psimpanan b ON a.kodec = b.kodec
|
|
LEFT JOIN mgagalbayar h ON h.kodetagihan = a.kodetagihan
|
|
AND h.kodesp = a.kodesp
|
|
AND h.kode = a.kode
|
|
WHERE b.kodec IS NULL AND h.kodetagihan IS NULL;
|
|
|
|
|
|
CREATE OR REPLACE VIEW pos_tgl AS
|
|
SELECT posting, tgl FROM tglposting_simpanan
|
|
UNION
|
|
SELECT posting, tgl FROM tgl_posting_piutang
|
|
UNION
|
|
SELECT posting, tgl FROM tglposting_angsuran;
|
|
|
|
-- function
|
|
|
|
DROP FUNCTION IF EXISTS cekPartisipasi;
|
|
create FUNCTION `cekPartisipasi`(
|
|
dataValue1 VARCHAR(255),
|
|
dataValue2 VARCHAR(255)
|
|
) RETURNS tinyint(1)
|
|
BEGIN
|
|
DECLARE dataCount INT;
|
|
|
|
-- Pemeriksaan apakah data ada di tabel
|
|
SELECT COUNT(*) INTO dataCount
|
|
FROM partisipasi
|
|
WHERE tahun = dataValue1 AND kode = dataValue2;
|
|
|
|
-- Jika ada data, kembalikan TRUE, jika tidak kembalikan FALSE
|
|
RETURN dataCount > 0;
|
|
END;
|
|
|
|
DROP FUNCTION IF EXISTS cekPembagianShu;
|
|
create FUNCTION `cekPembagianShu`(
|
|
dataValue1 VARCHAR(255),
|
|
dataValue2 VARCHAR(255),
|
|
dataValue3 VARCHAR(255)
|
|
) RETURNS tinyint(1)
|
|
BEGIN
|
|
DECLARE dataCount INT;
|
|
|
|
-- Pemeriksaan apakah data ada di tabel
|
|
SELECT COUNT(*) INTO dataCount
|
|
FROM pembagianshu
|
|
WHERE tahun = dataValue1 AND kode = dataValue2 AND app = dataValue3;
|
|
|
|
-- Jika ada data, kembalikan TRUE, jika tidak kembalikan FALSE
|
|
RETURN dataCount > 0;
|
|
END;
|
|
|
|
DROP FUNCTION IF EXISTS cekShuBuku;
|
|
create FUNCTION `cekShuBuku`(
|
|
dataValue1 VARCHAR(255),
|
|
dataValue2 VARCHAR(255)
|
|
) RETURNS tinyint(1)
|
|
BEGIN
|
|
DECLARE dataCount INT;
|
|
|
|
-- Pemeriksaan apakah data ada di tabel
|
|
SELECT COUNT(*) INTO dataCount
|
|
FROM shubuku
|
|
WHERE tahun = dataValue1 AND kode = dataValue2;
|
|
|
|
-- Jika ada data, kembalikan TRUE, jika tidak kembalikan FALSE
|
|
RETURN dataCount > 0;
|
|
END;
|
|
|
|
DROP FUNCTION IF EXISTS partisipasi;
|
|
create FUNCTION `partisipasi`(
|
|
thn VARCHAR(255),
|
|
type VARCHAR(255)
|
|
) RETURNS varchar(255) CHARSET utf8
|
|
BEGIN
|
|
DECLARE dataCount VARCHAR(255);
|
|
|
|
-- Pemeriksaan apakah data ada di tabel
|
|
SELECT IF(type = 'usp', a.usp, a.induk) INTO dataCount
|
|
FROM (
|
|
SELECT '02' kode, 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}:I{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}:I{no})' tot
|
|
FROM anggota a
|
|
LEFT JOIN (
|
|
SELECT kode, kodesp, sum(if(dk = 'd', jumlah, jumlah * -1 )) jumlah FROM msimpan
|
|
WHERE tgl LIKE concat(thn,'%') 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 concat(thn,'%') 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 concat(thn,'%') 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 concat(thn,'%') 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 concat(thn,'%') 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, debet total FROM totsl WHERE debet <> 0 AND tgl LIKE concat(thn,'%') 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 concat(thn,'%') 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 concat(thn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(thn,'%') 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 concat(thn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(thn,'%') 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 concat(thn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(thn,'%') 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 concat(thn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(thn,'%') 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 concat(thn,'%') 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 concat(thn,'%')
|
|
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 ) a;
|
|
|
|
|
|
-- Jika ada data, kembalikan TRUE, jika tidak kembalikan FALSE
|
|
RETURN ifnull(dataCount,0);
|
|
END;
|
|
|
|
DROP FUNCTION IF EXISTS shubagi;
|
|
create FUNCTION `shubagi`(
|
|
thn VARCHAR(255),
|
|
appv VARCHAR(255),
|
|
kodev VARCHAR(255)
|
|
) RETURNS varchar(255) CHARSET utf8
|
|
BEGIN
|
|
DECLARE dataCount VARCHAR(255);
|
|
|
|
-- Pemeriksaan apakah data ada di tabel
|
|
SELECT a.shu INTO dataCount
|
|
FROM (
|
|
SELECT
|
|
kode, nama, tahun, app, persen, total * 1000
|
|
/ IF(kode = '02',ifnull(partisipasi(tahun, app),0) , ifnull(simpananshu(tahun,app),0)) shu
|
|
FROM pembagianshu
|
|
WHERE kode = 01 OR kode = 02 AND tahun = thn HAVING kode = kodev AND app = appv
|
|
) a;
|
|
-- Jika ada data, kembalikan TRUE, jika tidak kembalikan FALSE
|
|
RETURN ifnull(dataCount,0);
|
|
END;
|
|
|
|
DROP FUNCTION IF EXISTS simpananshu;
|
|
create FUNCTION `simpananshu`(
|
|
thn VARCHAR(255),
|
|
app VARCHAR(255)
|
|
) RETURNS varchar(255) CHARSET utf8
|
|
BEGIN
|
|
DECLARE dataCount VARCHAR(255);
|
|
|
|
-- Pemeriksaan apakah data ada di tabel
|
|
SELECT IF(app = 'acc', a.spsw, a.smswp) INTO dataCount
|
|
FROM (
|
|
SELECT
|
|
sum(IF(kodesp = 'SP' OR kodesp = 'SW', IF(dk = 'D', jumlah, jumlah * -1) , 0) ) spsw,
|
|
sum(IF(kodesp = 'SM' OR kodesp = 'SWP', IF(dk = 'D', jumlah, jumlah * -1) , 0) ) smswp
|
|
FROM msimpan WHERE tgl <= concat(thn,'-12-31')
|
|
) a;
|
|
|
|
-- Jika ada data, kembalikan TRUE, jika tidak kembalikan FALSE
|
|
RETURN ifnull(dataCount,0);
|
|
END;
|
|
|
|
DROP FUNCTION IF EXISTS pembulatan500;
|
|
CREATE FUNCTION `pembulatan500`(value INT) RETURNS int(11)
|
|
BEGIN
|
|
DECLARE remainder INT;
|
|
|
|
SET remainder = value % 500;
|
|
|
|
IF remainder > 0 THEN
|
|
SET value = value + (500 - remainder);
|
|
END IF;
|
|
|
|
RETURN value;
|
|
END;
|
|
|
|
-- create procedural
|
|
|
|
DROP PROCEDURE IF EXISTS UpdatePartisipasi;
|
|
create PROCEDURE `UpdatePartisipasi`(
|
|
IN dataValueThn VARCHAR(255)
|
|
)
|
|
BEGIN
|
|
INSERT INTO partisipasi
|
|
SELECT dataValueThn tahun, kode, nama, reg, usp1, usp2, usp3, bt, bn, bw, pp FROM (
|
|
SELECT
|
|
concat('',a.kode) kode
|
|
, a.nama
|
|
, ( 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
|
|
FROM anggota a
|
|
LEFT JOIN (
|
|
SELECT kode, kodesp, sum(if(dk = 'd', jumlah, jumlah * -1 )) jumlah FROM msimpan
|
|
WHERE tgl LIKE concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%')
|
|
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 WHERE cekPartisipasi('2023', kode) = 0;
|
|
|
|
|
|
UPDATE partisipasi aa, (
|
|
SELECT dataValueThn tahun, kode, nama, reg, usp1, usp2, usp3, bt, bn, bw, pp FROM (
|
|
SELECT
|
|
concat('',a.kode) kode
|
|
, a.nama
|
|
, ( 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
|
|
FROM anggota a
|
|
LEFT JOIN (
|
|
SELECT kode, kodesp, sum(if(dk = 'd', jumlah, jumlah * -1 )) jumlah FROM msimpan
|
|
WHERE tgl LIKE concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') AND mags.keterangan NOT LIKE 'Posting: Data Awal%' AND mags.bunga <> 0 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%') 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 concat(dataValueThn,'%')
|
|
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 ) bb SET aa.reg = bb.reg
|
|
, aa.usp1 = bb.usp1
|
|
, aa.usp2 = bb.usp2
|
|
, aa.usp3 = bb.usp3
|
|
, aa.bt = bb.bt
|
|
, aa.bn = bb.bn
|
|
, aa.bw = bb.bw
|
|
, aa.pp = bb.pp
|
|
WHERE aa.tahun = bb.tahun AND aa.kode = bb.kode;
|
|
|
|
END;
|
|
|
|
DROP PROCEDURE IF EXISTS cekCashSimpanan;
|
|
CREATE PROCEDURE `cekCashSimpanan`()
|
|
BEGIN
|
|
UPDATE msimpan aa,(
|
|
SELECT a.posting, IF(a.dk = 'D', b.rekdebit, b.rekkredit) cash FROM (
|
|
SELECT posting, dk, cash FROM msimpan WHERE tgl LIKE concat(year(now()),'%') AND posting IS NOT NULL GROUP BY posting
|
|
) a
|
|
LEFT JOIN (
|
|
SELECT posting, rekdebit, rekkredit FROM lap_bb WHERE tgl LIKE concat(year(now()),'%') AND posting IS NOT NULL GROUP BY posting
|
|
) b ON a.posting = b.posting WHERE IF(a.dk = 'D', b.rekdebit, b.rekkredit) IS NOT NULL
|
|
) bb SET aa.cash = bb.cash
|
|
WHERE aa.posting = bb.posting;
|
|
END;
|
|
|
|
DROP PROCEDURE IF EXISTS UpdateShuBuku;
|
|
create PROCEDURE `UpdateShuBuku`(
|
|
IN val_tahun VARCHAR(255)
|
|
)
|
|
BEGIN
|
|
-- Select data from your_table based on a parameter
|
|
|
|
UPDATE shubuku aa, (
|
|
SELECT
|
|
shubuku.tahun
|
|
, shubuku.kode
|
|
, shubuku.nama, spokok + swajib spokokwajib
|
|
, b.induk jml_p_induk
|
|
, round( (spokok + swajib) / 1000 * a.simp ) shusimpanan
|
|
, round(b.induk /1000 * c.simp) shupartisipasi
|
|
, round( (spokok + swajib) / 1000 * a.simp ) + round(b.induk /1000 * c.simp) shuinduk
|
|
, sms + swp smsswp
|
|
, b.usp partisipasi
|
|
, round((sms + swp)/1000 * d.simp) uspsimpanan
|
|
, round(b.usp / 1000 * e.simp) usppartisipasi
|
|
, round((sms + swp)/1000 * d.simp) + round(b.usp / 1000 * e.simp) shuusp
|
|
, (round( (spokok + swajib) / 1000 * a.simp ) + round(b.induk /1000 * c.simp)) +
|
|
(round((sms + swp)/1000 * d.simp) + round(b.usp / 1000 * e.simp)) total
|
|
FROM shubuku
|
|
LEFT JOIN (
|
|
SELECT val_tahun tahun, shubagi(val_tahun, 'acc', '01') simp
|
|
) a ON a.tahun = shubuku.tahun
|
|
LEFT JOIN (
|
|
SELECT val_tahun tahun, shubagi(val_tahun, 'usp', '01') simp
|
|
) d ON d.tahun = shubuku.tahun
|
|
LEFT JOIN (
|
|
SELECT * FROM group_partisipasi WHERE tahun = val_tahun
|
|
) b ON b.kode = shubuku.kode
|
|
LEFT JOIN (
|
|
SELECT val_tahun tahun, shubagi(val_tahun, 'acc', '02') simp
|
|
) c ON c.tahun = shubuku.tahun
|
|
LEFT JOIN (
|
|
SELECT val_tahun tahun, shubagi(val_tahun, 'usp', '02') simp
|
|
) e ON e.tahun = shubuku.tahun
|
|
WHERE shubuku.tahun = val_tahun
|
|
) bb SET
|
|
aa.spokokwajib = bb.spokokwajib
|
|
, aa.jml_p_induk = bb.jml_p_induk
|
|
, aa.shusimpanan = bb.shusimpanan
|
|
, aa.shupartisipasi = bb.shupartisipasi
|
|
, aa.shuinduk = bb.shuinduk
|
|
, aa.smsswp = bb.smsswp
|
|
, aa.partisipasi = bb.partisipasi
|
|
, aa.uspsimpanan = bb.uspsimpanan
|
|
, aa.usppartisipasi = bb.usppartisipasi
|
|
, aa.shuusp = bb.shuusp
|
|
, aa.total = bb.total
|
|
WHERE aa.tahun = bb.tahun AND aa.kode = bb.kode;
|
|
END;
|
|
|
|
DROP TRIGGER IF EXISTS update_nama_msimpan;
|
|
CREATE TRIGGER update_nama_msimpan
|
|
AFTER INSERT ON lap_bb
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE msimpan aa, (
|
|
SELECT b.faktur, b.kode, a.nama FROM msimpan b
|
|
LEFT JOIN anggota a ON a.kode = b.kode WHERE b.nama IS NULL
|
|
) bb SET aa.nama = bb.nama WHERE aa.faktur = bb.faktur;
|
|
|
|
INSERT INTO psimpanan
|
|
SELECT a.* FROM posting_simpanan a
|
|
LEFT JOIN psimpanan b ON a.kodec = b.kodec
|
|
WHERE b.kodec IS NULL;
|
|
|
|
END;
|
|
|
|
DROP TRIGGER IF EXISTS update_gagalbayar_mpinjam;
|
|
CREATE TRIGGER update_gagalbayar_mpinjam
|
|
AFTER INSERT ON mpinjam
|
|
FOR EACH ROW
|
|
BEGIN
|
|
|
|
INSERT INTO mgagalbayar (kodetagihan, faktur, kodesp, golkar, kode, jumlah, pokok, bunga)
|
|
SELECT kodetagihan, faktur, kodesp, golkar, kode, jumlah, pokok, bunga FROM posting_gagalbayar_pinjaman;
|
|
|
|
UPDATE mgagalbayar aa, (
|
|
SELECT b.tgl, a.kodetagihan FROM mgagalbayar a
|
|
LEFT JOIN tglposting_angsuran b ON a.kodetagihan = b.posting
|
|
WHERE a.tgl IS NULL
|
|
) bb SET aa.tgl = bb.tgl WHERE aa.kodetagihan = bb.kodetagihan;
|
|
|
|
END;
|
|
|
|
DROP TRIGGER IF EXISTS update_gagalbayar_piutang;
|
|
CREATE TRIGGER update_gagalbayar_piutang
|
|
AFTER INSERT ON totptg
|
|
FOR EACH ROW
|
|
BEGIN
|
|
|
|
INSERT INTO mgagalbayar (kodetagihan, faktur, kodesp, golkar, kode, jumlah, pokok, bunga)
|
|
SELECT kodetagihan, faktur, kodesp, golkar, kode, jumlah, pokok, bunga FROM posting_gagalbayar_piutang;
|
|
|
|
UPDATE mgagalbayar aa, (
|
|
SELECT b.tgl, a.kodetagihan FROM mgagalbayar a
|
|
LEFT JOIN pos_tgl b ON a.kodetagihan = b.posting
|
|
WHERE a.tgl IS NULL
|
|
) bb SET aa.tgl = bb.tgl WHERE aa.kodetagihan = bb.kodetagihan;
|
|
|
|
END;
|
|
|
|
DROP TRIGGER IF EXISTS update_gagalbayar_msimpan;
|
|
CREATE TRIGGER update_gagalbayar_msimpan
|
|
AFTER INSERT ON msimpan
|
|
FOR EACH ROW
|
|
BEGIN
|
|
|
|
INSERT INTO mgagalbayar (kodetagihan, faktur, kodesp, golkar, kode, jumlah, pokok, bunga)
|
|
SELECT kodetagihan, faktur, kodesp, golkar, kode, jumlah, pokok, bunga FROM posting_gagalbayar_simpanan;
|
|
|
|
UPDATE mgagalbayar aa, (
|
|
SELECT b.tgl, a.kodetagihan FROM mgagalbayar a
|
|
LEFT JOIN tglposting_simpanan b ON a.kodetagihan = b.posting
|
|
WHERE a.tgl IS NULL
|
|
) bb SET aa.tgl = bb.tgl WHERE aa.kodetagihan = bb.kodetagihan;
|
|
|
|
END; |