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;