-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Waktu pembuatan: 24 Jun 2024 pada 18.48
-- Versi server: 10.6.18-MariaDB-cll-lve
-- Versi PHP: 8.1.28

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `mbek1154_eoffice`
--

DELIMITER $$
--
-- Prosedur
--
CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `jabatan_baru` ()   BEGIN
declare cid_unit_kerja INTEGER;
declare cid_skpd INTEGER;
declare selesai INTEGER;
declare cnama_jabatan VARCHAR(255);
declare cur1 cursor for 
SELECT a.id_unit_kerja,c.id_skpd,
TRIM(b.jab_utuh) nama_jabatan
FROM ref_unit_kerja a
LEFT JOIN tb_skpd b ON a.idskpd=b.idskpd AND a.idparent=b.idparent
LEFT JOIN ref_skpd c ON a.id_skpd=c.id_skpd
WHERE a.idskpd IS NOT NULL and a.idparent IS NOT NULL ;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unit_kerja,cid_skpd,cnama_jabatan;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO ref_jabatan_baru (id_unit_kerja,id_skpd,nama_jabatan)
							VALUES
							(cid_unit_kerja,cid_skpd,cnama_jabatan)
							;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `jabatan_skpd_induk` ()   BEGIN
declare cid_unit_kerja INTEGER;
declare cid_skpd INTEGER;
declare selesai INTEGER;
declare cnama_jabatan VARCHAR(255);
declare cur1 cursor for 
SELECT a.id_skpd,trim(a.jab_utuh) as jabatan
FROM ref_skpd a WHERE a.id_skpd !=177 ;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cnama_jabatan;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO ref_jabatan_baru (id_unit_kerja,id_skpd,nama_jabatan)
							VALUES
							(0,cid_skpd,cnama_jabatan)
							;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_ref_kegiatan` ()   BEGIN
declare cid_kode_kegiatan TEXT;
declare cid_nama_kegiatan TEXT;
declare cid_ref_program INTEGER;
declare cid_urusan INTEGER;
declare cid_sub_urusan INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT *
FROM
(SELECT
467 id_ref_program,
4 id_urusan,
33 id_sub_urusan,
CONCAT('4.01',SUBSTR(a.kode_kegiatan,5,8)) kode_kegiatan,
a.nama_kegiatan
FROM sc_ref_kegiatan a 
WHERE substr(a.kode_kegiatan,1,4)='X.XX'
AND substr(a.kode_kegiatan,11,2)!='10'
UNION
SELECT
468 id_ref_program,
4 id_urusan,
34 id_sub_urusan,
CONCAT('4.02',SUBSTR(a.kode_kegiatan,5,8)) kode_kegiatan,
a.nama_kegiatan
FROM sc_ref_kegiatan a 
WHERE substr(a.kode_kegiatan,1,4)='X.XX'
AND substr(a.kode_kegiatan,11,2)!='10') z 
ORDER BY z.kode_kegiatan
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_ref_program,cid_urusan,cid_sub_urusan,cid_kode_kegiatan,cid_nama_kegiatan;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_ref_kegiatan (id_ref_program, id_urusan, id_sub_urusan,kode_kegiatan,nama_kegiatan) 
							VALUES (cid_ref_program, cid_urusan, cid_sub_urusan,cid_kode_kegiatan,cid_nama_kegiatan);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_ref_sub_kegiatan` ()   BEGIN
declare cid_kode_kegiatan TEXT;
declare cid_nama_kegiatan TEXT;
declare cid_ref_program INTEGER;
declare cid_urusan INTEGER;
declare cid_sub_urusan INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT *
FROM
(SELECT
467 id_program,
4 id_urusan,
33 id_sub_urusan,
CONCAT('4.01',SUBSTR(a.kode_sub_kegiatan,5,11)) kode_sub_kegiatan,
a.nama_sub_kegiatan
FROM sc_ref_sub_kegiatan a 
WHERE substr(a.kode_sub_kegiatan,1,4)='X.XX'
AND substr(a.kode_sub_kegiatan,11,2)!='10'
UNION
SELECT
468 id_program,
4 id_urusan,
34 id_sub_urusan,
CONCAT('4.02',SUBSTR(a.kode_sub_kegiatan,5,11)) kode_sub_kegiatan,
a.nama_sub_kegiatan
FROM sc_ref_sub_kegiatan a 
WHERE substr(a.kode_sub_kegiatan,1,4)='X.XX'
AND substr(a.kode_sub_kegiatan,11,2)!='10') z 
ORDER BY z.kode_sub_kegiatan
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_ref_program,cid_urusan,cid_sub_urusan,cid_kode_kegiatan,cid_nama_kegiatan;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_ref_sub_kegiatan (id_program, id_urusan, id_sub_urusan,kode_sub_kegiatan,nama_sub_kegiatan) 
							VALUES (cid_ref_program, cid_urusan, cid_sub_urusan,cid_kode_kegiatan,cid_nama_kegiatan);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_sc_renstra_indi_program` ()   BEGIN
declare cid_unik_program TEXT;
declare cid_target1 TEXT;
declare cid_pagu1 INTEGER;
declare cid_target2 TEXT;
declare cid_pagu2 INTEGER;
declare cid_target3 TEXT;
declare cid_pagu3 INTEGER;
declare cid_target4 TEXT;
declare cid_pagu4 INTEGER;
declare cid_target5 TEXT;
declare cid_pagu5 INTEGER;
declare cid_target_awal TEXT;
declare cid_target_akhir TEXT;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
a.id_unik_indikator,
a.target_1,
a.pagu_1,
a.target_2,
a.pagu_2,
a.target_3,
a.pagu_3,
a.target_4,
a.pagu_4,
a.target_5,
a.pagu_5,
a.target_awal,
a.target_akhir
FROM data_renstra_program a
WHERE a.active!=0 AND a.id_unik!='0' ;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_program,
				cid_target1,
				cid_pagu1,
				cid_target2,
				cid_pagu2,
				cid_target3,
				cid_pagu3,
				cid_target4,
				cid_pagu4,
				cid_target5,
				cid_pagu5,
				cid_target_awal,
				cid_target_akhir;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_renstra_program_indikator (id_unik_renstra_indikator_program,
target_tahun_1,
target_tahun_1_rp,
target_tahun_2,
target_tahun_2_rp,
target_tahun_3,
target_tahun_3_rp,
target_tahun_4,
target_tahun_4_rp,
target_tahun_5,
target_tahun_5_rp,
target_awal,
target_akhir) 
							VALUES (cid_unik_program,
				cid_target1,
				cid_pagu1,
				cid_target2,
				cid_pagu2,
				cid_target3,
				cid_pagu3,
				cid_target4,
				cid_pagu4,
				cid_target5,
				cid_pagu5,
				cid_target_awal,
				cid_target_akhir);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_sc_renstra_program` ()   BEGIN
declare cid_unik_program TEXT;
declare cid_teks_sasaran TEXT;
declare cid_unik_sasaran INTEGER;
declare cid_nama_skpd TEXT;
declare cid_kode_skpd TEXT;
declare cid_sasaran_rpjm INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
DISTINCT(a.id_unik),
a.kode_sasaran,
b.id_program_rpjmd
FROM data_renstra_program a
LEFT JOIN sc_rpjmd_program b ON b.id_ref_program=a.id_program
WHERE a.active!=0 AND id_unik!='0'
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_program,cid_teks_sasaran,cid_unik_sasaran;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_renstra_program (id_unik_program, id_unik_sasaran,id_program_rpjmd) 
							VALUES (cid_unik_program, cid_teks_sasaran,cid_unik_sasaran);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_sc_renstra_sasaran` ()   BEGIN
declare cid_unik_program TEXT;
declare cid_teks_sasaran TEXT;
declare cid_unik_sasaran INTEGER;
declare cid_nama_skpd TEXT;
declare cid_kode_skpd TEXT;
declare cid_sasaran_rpjm INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT DISTINCT(a.id_unik),
a.sasaran_teks,
a.id_unit,
a.nama_skpd,
a.kode_skpd,
c.id_sasaran_rpjmd
FROM data_renstra_sasaran a 
LEFT JOIN data_renstra_tujuan b ON b.id_unik=a.kode_tujuan
LEFT JOIN sc_rpjmd_sasaran c ON c.id_unik_sasaran=b.kode_sasaran_rpjm
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_program,cid_teks_sasaran,cid_unik_sasaran,cid_nama_skpd,cid_kode_skpd,cid_sasaran_rpjm;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_renstra_sasaran (id_unik_sasaran_renstra, nama_sasaran_renstra, id_unit,nama_skpd,kode_skpd,id_sasaran_rpjmd) 
							VALUES (cid_unik_program, cid_teks_sasaran,cid_unik_sasaran,cid_nama_skpd,cid_kode_skpd,cid_sasaran_rpjm);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_sc_renstra_sasaran_indikator` ()   BEGIN
declare cid_unik_sasaran TEXT;
declare cid_unik_indikator TEXT;
declare cid_text_indikator TEXT;
declare cid_satuan TEXT;
declare cid_target_awal TEXT;
declare cid_tahun1 TEXT;
declare cid_tahun2 TEXT;
declare cid_tahun3 TEXT;
declare cid_tahun4 TEXT;
declare cid_tahun5 TEXT;
declare cid_target_akhir TEXT;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
id_unik,
a.id_unik_indikator,
a.indikator_teks,
a.satuan,
a.target_awal,
a.target_1,
a.target_2,
a.target_3,
a.target_4,
a.target_5,
a.target_akhir
FROM
	data_renstra_sasaran a 	
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_sasaran,cid_unik_indikator,cid_text_indikator,cid_satuan,
				cid_target_awal,cid_tahun1,cid_tahun2,cid_tahun3,cid_tahun4,cid_tahun5,cid_target_akhir;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_renstra_sasaran_indikator  (id_unik_sasaran, id_unik_sasaran_indikator, 
							nama_indikator_sasaran_renstra,satuan, target_awal, target_tahun_1, target_tahun_2, 
							target_tahun_3, target_tahun_4, target_tahun_5,target_akhir) 
							VALUES (cid_unik_sasaran,cid_unik_indikator,cid_text_indikator,cid_satuan,cid_target_awal
				,cid_tahun1,cid_tahun2,cid_tahun3,cid_tahun4,cid_tahun5,cid_target_akhir);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_sc_renstra_sasaran_skpd` ()   BEGIN
declare cid_unik_sasaran INTEGER;
declare cid_unik_indikator INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
a.id_sasaran_renstra,
a.id_skpd
FROM
sc_renstra_sasaran a
WHERE !ISNULL(a.id_skpd)
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_sasaran,cid_unik_indikator;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_renstra_sasaran_unit_kerja (id_sasaran_renstra, id_unit_kerja) 
							VALUES (cid_unik_sasaran,cid_unik_indikator);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_sc_rpjmd_prog` ()   BEGIN
declare cid_program INTEGER;
declare cid_unik_program TEXT;
declare cid_unik_tujuan TEXT;
declare cid_unik_sasaran TEXT;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
DISTINCT(a.id_program) id,
a.tujuan_teks,
a.sasaran_teks
FROM data_rpjmd_program a 
ORDER BY a.id_program
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_program,cid_unik_tujuan,cid_unik_sasaran;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_rpjmd_program (id_ref_program, id_unik_rpjmd_tujuan, id_unik_rpjmd_sasaran) 
							VALUES (cid_program, cid_unik_tujuan, cid_unik_sasaran);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_sc_rpjmd_prog_indikator` ()   BEGIN
declare cid_program INTEGER;
declare cid_unik_program TEXT;
declare cid_unik_indikator TEXT;
declare cid_indikator TEXT;
declare cid_satuan TEXT;
declare cid_target_awal TEXT;
declare cid_target_akhir TEXT;
declare cid_target_1 TEXT;
declare cid_target_2 TEXT;
declare cid_target_3 TEXT;
declare cid_target_4 TEXT;
declare cid_target_5 TEXT;
declare cid_pagu_1 INTEGER;
declare cid_pagu_2 INTEGER;
declare cid_pagu_3 INTEGER;
declare cid_pagu_4 INTEGER;
declare cid_pagu_5 INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
a.id_program,
a.id_unik,
a.id_unik_indikator,
a.indikator,
a.satuan,
a.target_awal,
a.target_akhir,
a.target_1,
a.pagu_1,
a.target_2,
a.pagu_2,
a.target_3,
a.pagu_3,
a.target_4,
a.pagu_4,
a.target_5,
a.pagu_5
FROM
data_rpjmd_program a
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_program,
cid_unik_program,
cid_unik_indikator,
cid_indikator,
cid_satuan,
cid_target_awal,
cid_target_akhir,
cid_target_1,
cid_pagu_1,
cid_target_2,
cid_pagu_2,
cid_target_3,
cid_pagu_3,
cid_target_4,
cid_pagu_4,
cid_target_5,
cid_pagu_5;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_rpjmd_program_indikator (
id_unik_rpjmd_program,
id_unik_rpjmd_program_indikator,
nama_indikator_program_rpjmd,
satuan,
target_awal,
target_akhir,
target_tahun_1,
target_tahun_1_rp,
target_tahun_2,
target_tahun_2_rp,
target_tahun_3,
target_tahun_3_rp,
target_tahun_4,
target_tahun_4_rp,
target_tahun_5,
target_tahun_5_rp) 
	VALUES (cid_unik_program,
cid_unik_indikator,
cid_indikator,
cid_satuan,
cid_target_awal,
cid_target_akhir,
cid_target_1,
cid_pagu_1,
cid_target_2,
cid_pagu_2,
cid_target_3,
cid_pagu_3,
cid_target_4,
cid_pagu_4,
cid_target_5,
cid_pagu_5
);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_sc_rpjmd_prog_indikator_sasaran` ()   BEGIN
declare cid_program INTEGER;
declare cid_unik_program TEXT;
declare cid_unik_indikator TEXT;
declare cid_indikator TEXT;
declare cid_satuan TEXT;
declare cid_target_awal TEXT;
declare cid_target_akhir TEXT;
declare cid_target_1 TEXT;
declare cid_target_2 TEXT;
declare cid_target_3 TEXT;
declare cid_target_4 TEXT;
declare cid_target_5 TEXT;
declare cid_pagu_1 INTEGER;
declare cid_pagu_2 INTEGER;
declare cid_pagu_3 INTEGER;
declare cid_pagu_4 INTEGER;
declare cid_pagu_5 INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
a.id_program_rpjmd,
c.id_indikator_sasaran_rpjmd
FROM sc_rpjmd_program a 
LEFT JOIN sc_rpjmd_sasaran b ON a.id_sasaran_rpjmd=b.id_sasaran_rpjmd
LEFT JOIN sc_rpjmd_sasaran_indikator c ON b.id_sasaran_rpjmd=c.id_sasaran_rpjmd
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into 
cid_pagu_1,
cid_pagu_2;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_rpjmd_program_sasaran_indikator (
id_program_rpjmd, id_indikator_sasaran_rpjmd) 
	VALUES (
cid_pagu_1,
cid_pagu_2
);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_sc_rpj_prog_sasaran` ()   BEGIN
declare cid_program INTEGER;
declare cid_ref_program INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
id_program_rpjmd,
b.id_indikator_sasaran_rpjmd
FROM
sc_rpjmd_program a 
LEFT JOIN sc_rpjmd_sasaran_indikator b ON b.id_unik_sasaran = a.id_unik_rpjmd_sasaran
ORDER BY a.id_unik_rpjmd_program
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_program, cid_ref_program ;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_rpjmd_program_sasaran_indikator (id_program_rpjmd, id_indikator_sasaran_rpjmd)
							VALUES
							(cid_program, cid_ref_program)
							;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_insert_skpd_sc_rpjmd_prog_indikator` ()   BEGIN
declare cid_program INTEGER;
declare cid_unik_indikator TEXT;
declare cid_indikator TEXT;
declare cid_satuan TEXT;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_indikator_program_rpjmd,
a.id_unik_rpjmd_program_indikator,
b.kode_skpd,
b.nama_skpd
FROM sc_rpjmd_program_indikator a 
LEFT JOIN data_rpjmd_program b ON b.id_unik_indikator = a.id_unik_rpjmd_program_indikator
ORDER BY b.kode_skpd
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_program,
cid_unik_indikator,
cid_indikator,
cid_satuan ;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              INSERT INTO sc_rpjmd_program_indikator_skpd (
id_indikator_program_rpjmd,
id_unik_rpjmd_program_indikator,
kode_skpd,
nama_skpd
) 
	VALUES (cid_program,
cid_unik_indikator,
cid_indikator,
cid_satuan
);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_jenjab_pegawai` ()   BEGIN
declare cid_skpd VARCHAR(18);
declare cijabatan INTEGER;
declare cnama VARCHAR(500);
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.nip,b.idjenjab,
	 CASE
   WHEN TRIM(b.gdp)!='' AND TRIM(b.gdb)='' THEN CONCAT(TRIM(b.gdp),' ',TRIM(b.nama))
   WHEN TRIM(b.gdp)='' AND TRIM(b.gdb)!='' THEN CONCAT(TRIM(b.nama),', ',TRIM(b.gdb))
	 WHEN TRIM(b.gdp)='' AND TRIM(b.gdb)=''  THEN TRIM(b.nama)
   ELSE CONCAT(TRIM(b.gdp),' ',TRIM(b.nama),',',TRIM(b.gdb))
END AS nama
from 
pegawai a 
LEFT JOIN tb_01 b ON a.nip=b.nip
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cijabatan,cnama;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE pegawai c SET c.idjenjab=cijabatan, c.nama_lengkap=cnama
							WHERE TRIM(c.nip)=TRIM(cid_skpd);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_ref_skpd` ()   BEGIN
declare cid_skpd INTEGER;
declare cinduk INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_skpd,
b.id_skpd induk
FROM ref_skpd a
LEFT JOIN ref_skpd b ON a.idparent=b.idskpd
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cinduk;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE ref_skpd b SET b.id_skpd_induk=cinduk
							WHERE b.id_skpd=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_unit_kerja_dinas-lv1` ()   BEGIN
declare cid_skpd INTEGER;
declare cinduk INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_unit_kerja,
b.id_skpd induk
FROM ref_unit_kerja a
LEFT JOIN ref_skpd b ON a.idparent=b.idskpd
WHERE a.idparent<>'01' AND TRIM(LEFT(a.idparent,2))<>'01'
AND b.id_skpd IS NOT NULL;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cinduk;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE ref_unit_kerja b SET b.id_skpd=cinduk, b.id_induk=0, b.level_unit_kerja=1
							WHERE b.id_unit_kerja=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_unit_kerja_dinas_lv2` ()   BEGIN
declare cid_skpd INTEGER;
declare cidskpd INTEGER;
declare cinduk INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_unit_kerja,c.id_skpd,
b.id_unit_kerja induk
FROM ref_unit_kerja a
LEFT JOIN ref_unit_kerja b ON a.idparent=b.idskpd
LEFT JOIN ref_unit_kerja c ON c.idskpd=a.idparent
WHERE LENGTH(a.idparent)=5 and LEFT(a.idparent,2)<>'01'
AND b.id_unit_kerja is not null
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cidskpd,cinduk;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE ref_unit_kerja b SET b.id_induk=cinduk, b.level_unit_kerja=2, b.id_skpd=cidskpd
							WHERE b.id_unit_kerja=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_unit_kerja_dinas_lv3` ()   BEGIN
declare cid_skpd INTEGER;
declare cidskpd INTEGER;
declare cinduk INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_unit_kerja,c.id_skpd,
b.id_unit_kerja induk
FROM ref_unit_kerja a
LEFT JOIN ref_unit_kerja b ON a.idparent=b.idskpd
LEFT JOIN ref_unit_kerja c ON c.idskpd=a.idparent
WHERE LENGTH(a.idparent)=8 and LEFT(a.idparent,2)<>'01'
AND b.id_unit_kerja is not null
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cidskpd,cinduk;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE ref_unit_kerja b SET b.id_induk=cinduk, b.level_unit_kerja=2, b.id_skpd=cidskpd
							WHERE b.id_unit_kerja=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_unit_kerja_setda-lv1` ()   BEGIN
declare cid_skpd INTEGER;
declare cinduk INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_unit_kerja,
b.id_skpd induk
FROM ref_unit_kerja a
LEFT JOIN ref_skpd b ON a.idparent=b.idskpd
WHERE a.idparent='01'
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cinduk;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE ref_unit_kerja b SET b.id_skpd=cinduk, b.id_induk=0, b.level_unit_kerja=1
							WHERE b.id_unit_kerja=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_unit_kerja_setda_lv2` ()   BEGIN
declare cid_skpd INTEGER;
declare cidskpd INTEGER;
declare cinduk INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_unit_kerja,c.id_skpd,
b.id_unit_kerja induk
FROM ref_unit_kerja a
LEFT JOIN ref_unit_kerja b ON a.idparent=b.idskpd
LEFT JOIN ref_unit_kerja c ON c.idskpd=a.idparent
WHERE LENGTH(a.idparent)=5 and LEFT(a.idparent,2)='01'
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cidskpd,cinduk;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE ref_unit_kerja b SET b.id_induk=cinduk, b.level_unit_kerja=2, b.id_skpd=cidskpd
							WHERE b.id_unit_kerja=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_unit_kerja_setda_lv3` ()   BEGIN
declare cid_skpd INTEGER;
declare cidskpd INTEGER;
declare cinduk INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_unit_kerja,c.id_skpd,
b.id_unit_kerja induk
FROM ref_unit_kerja a
LEFT JOIN ref_unit_kerja b ON a.idparent=b.idskpd
LEFT JOIN ref_unit_kerja c ON c.idskpd=a.idparent
WHERE LENGTH(a.idparent)=8 and LEFT(a.idparent,2)='01'
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cidskpd,cinduk;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE ref_unit_kerja b SET b.id_induk=cinduk, b.level_unit_kerja=3, b.id_skpd=cidskpd
							WHERE b.id_unit_kerja=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_jab_pegawai` ()   BEGIN
declare cid_skpd INTEGER;
declare cijabatan VARCHAR(255);
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_pegawai,
z.nama_jabatan
FROM
(SELECT b.idskpd,b.jab_asn,
TRIM(b.jab_utuh) nama_jabatan
FROM ref_unit_kerja a
LEFT JOIN tb_skpd b ON a.idskpd=b.idskpd AND a.idparent=b.idparent
LEFT JOIN ref_skpd c ON a.id_skpd=c.id_skpd
WHERE a.idskpd IS NOT NULL and a.idparent IS NOT NULL) z
LEFT JOIN pegawai a ON z.idskpd=a.idskpd AND z.jab_asn=a.idjenjab
WHERE a.id_pegawai is NOT null
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cijabatan;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE pegawai b SET b.jabatan=cijabatan
							WHERE b.id_pegawai=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_jab_unit_pegawai` ()   BEGIN
declare cid_skpd VARCHAR(14);
declare cijabatan VARCHAR(255);
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.idskpd,b.jab_utuh jabatan
FROM ref_unit_kerja a 
LEFT JOIN tb_skpd b ON a.idskpd=b.idskpd
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cijabatan;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE pegawai c SET c.jabatan=cijabatan
							WHERE TRIM(c.idskpd)=TRIM(cid_skpd);
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_pangkat_pegawai` ()   BEGIN
declare cid_skpd INTEGER;
declare cid_gol VARCHAR(255);
declare citmt_cpns VARCHAR(255);
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_pegawai, c.pangkat, c.golongan
FROM pegawai a 
JOIN tb_01 b ON b.nip=a.nip
JOIN ref_golongan c ON c.id_golongan=b.idgolrupkt
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cid_gol,citmt_cpns;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE pegawai b SET b.golongan=cid_gol, b.pangkat=citmt_cpns
							WHERE b.id_pegawai=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_pegawai_jabatan` ()   BEGIN
declare cid_skpd INTEGER;
declare cijabatan VARCHAR(255);
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.idskpd,trim(a.jab_utuh) as jabatan
FROM ref_skpd a WHERE a.id_skpd !=177;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cijabatan;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE pegawai b SET b.jabatan=cijabatan
							WHERE b.idskpd=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_ref_kegiatan` ()   BEGIN
declare cid_kegiatan INTEGER;
declare ckode_program INTEGER;
declare cid_program INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
a.id_ref_kegiatan,
b.id_ref_program,
a.id_ref_program prog_kegiatan
FROM sc_ref_kegiatan a 
LEFT JOIN sc_ref_program b ON b.kode_program=SUBSTRING(a.kode_kegiatan,1,7)
WHERE b.id_ref_program!=a.id_ref_program
ORDER BY a.kode_kegiatan
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_kegiatan,ckode_program,cid_program;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_ref_kegiatan b SET b.id_ref_program=ckode_program
							WHERE b.id_ref_kegiatan=cid_kegiatan;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_ref_prog` ()   BEGIN
declare cid_program INTEGER;
declare ckode_program CHAR;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT b.id_program,
a.kode_program
FROM sc_ref_program a
JOIN sc_ref_program_tmp b ON TRIM(a.kode_program)=TRIM(b.kode_program)
ORDER BY b.id_program
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_program,ckode_program;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_ref_program b SET b.id_ref_program=cid_program
							WHERE b.id_ref_program=ckode_program;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_ref_sub_kegiatan` ()   BEGIN
declare cid_kegiatan INTEGER;
declare cid_program INTEGER;
declare cid_sub INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_ref_kegiatan,
a.id_ref_program,
b.id_sub_kegiatan
FROM sc_ref_kegiatan a
right JOIN sc_ref_sub_kegiatan b ON a.kode_kegiatan=SUBSTR(b.kode_sub_kegiatan FROM 1 FOR 12)
ORDER BY a.kode_kegiatan
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_kegiatan,cid_program,cid_sub;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_ref_sub_kegiatan b SET b.id_kegiatan=cid_kegiatan,
							b.id_program=cid_program
							WHERE b.id_sub_kegiatan=cid_sub;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_ref_sub_kegiatan2` ()   BEGIN
declare cid_kegiatan INTEGER;
declare cid_program INTEGER;
declare cid_sub INTEGER;
declare cid_urusan INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
a.id_sub_kegiatan,
b.id_urusan,
b.id_sub_urusan,
b.id_ref_program
FROM sc_ref_sub_kegiatan a 
LEFT JOIN sc_ref_kegiatan b ON b.kode_kegiatan=SUBSTR(a.kode_sub_kegiatan,1,12)
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_kegiatan,cid_program,cid_sub,cid_urusan;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_ref_sub_kegiatan b SET b.id_urusan=cid_program,
							b.id_sub_urusan=cid_sub,
							b.id_program=cid_urusan
							WHERE b.id_sub_kegiatan=cid_kegiatan;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_renstra_program` ()   BEGIN
declare cid_unik_program TEXT;
declare cid_teks_sasaran TEXT;
declare cid_unik_sasaran INTEGER;
declare cid_nama_skpd TEXT;
declare cid_kode_skpd TEXT;
declare cid_sasaran_rpjm INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
DISTINCT(a.kode_sasaran),
b.id_sasaran_renstra,
a.nama_program
FROM data_renstra_program a 
JOIN sc_renstra_sasaran b ON b.id_unik_sasaran_renstra=a.kode_sasaran
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_program,cid_unik_sasaran,cid_nama_skpd;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_renstra_program SET id_sasaran_renstra=cid_unik_sasaran,
							nama_program=cid_nama_skpd 
							WHERE id_unik_sasaran=cid_unik_program;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_renstra_sasaran` ()   BEGIN
declare cid_unik_program TEXT;
declare cid_teks_sasaran TEXT;
declare cid_unik_sasaran INTEGER;
declare cid_nama_skpd TEXT;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
DISTINCT(b.kode_skpd),
a.id_sasaran_renstra
FROM sc_renstra_sasaran a 
LEFT JOIN data_renstra_sasaran b ON b.id_unit = a.id_unit
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_program,cid_unik_sasaran;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_renstra_sasaran SET kode_skpd=cid_unik_program 
							WHERE id_sasaran_renstra=cid_unik_sasaran;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_renstra_sasaran_idskpd` ()   BEGIN
declare cid_unik_program TEXT;
declare cid_teks_sasaran TEXT;
declare cid_unik_sasaran INTEGER;
declare cid_nama_skpd INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
DISTINCT(a.id_unit),
b.id_skpd
FROM
sc_renstra_sasaran a 
LEFT JOIN sc_renstra_sasaran_copy1 b ON b.id_unit=a.id_unit
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_sasaran,cid_nama_skpd;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_renstra_sasaran SET id_skpd=cid_nama_skpd 
							WHERE id_unit=cid_unik_sasaran;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_renstra_sasaran_id_tujuan` ()   BEGIN
declare cid_unik_program TEXT;
declare cid_teks_sasaran TEXT;
declare cid_unik_sasaran INTEGER;
declare cid_nama_skpd INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
a.id_sasaran_rpjmd,
a.id_indikator_tujuan
FROM sc_rpjmd_sasaran a 
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_sasaran,cid_nama_skpd;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_renstra_sasaran SET id_indikator_tujuan=cid_nama_skpd 
							WHERE id_sasaran_rpjmd=cid_unik_sasaran;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_renstra_sasaran_indikator` ()   BEGIN
declare cid_unik_program INTEGER;
declare cid_teks_sasaran TEXT;
declare cid_unik_sasaran INTEGER;
declare cid_nama_skpd TEXT;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
a.id_indikator_sasaran_renstra,
b.id_sasaran_renstra
FROM sc_renstra_sasaran_indikator a 
LEFT JOIN sc_renstra_sasaran b ON b.id_unik_sasaran_renstra=a.id_unik_sasaran
WHERE b.id_unik_sasaran_renstra!=0
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_program,cid_unik_sasaran;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_renstra_sasaran_indikator SET id_sasaran_renstra=cid_unik_sasaran 
							WHERE id_indikator_sasaran_renstra=cid_unik_program;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_renstra_sasaran_koderpjmd` ()   BEGIN
declare cid_unik_program TEXT;
declare cid_teks_sasaran TEXT;
declare cid_unik_sasaran INTEGER;
declare cid_nama_skpd INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
	DISTINCT(a.id_unik_sasaran_renstra),
	a.id_sasaran_renstra,
	 d.id_sasaran_rpjmd
FROM
	sc_renstra_sasaran a
	LEFT JOIN data_renstra_sasaran b ON b.id_unik=a.id_unik_sasaran_renstra
	LEFT JOIN data_renstra_tujuan c ON c.id_unik=b.kode_tujuan
	LEFT JOIN sc_rpjmd_sasaran d ON d.id_unik_sasaran = c.kode_sasaran_rpjm
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_program,cid_unik_sasaran,cid_nama_skpd;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_renstra_sasaran SET id_sasaran_rpjmd=cid_nama_skpd 
							WHERE id_sasaran_renstra=cid_unik_sasaran;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_renstra_sasaran_kode_skpd` ()   BEGIN
declare cid_unik_program TEXT;
declare cid_teks_sasaran TEXT;
declare cid_unik_sasaran INTEGER;
declare cid_nama_skpd TEXT;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
DISTINCT(b.kode_skpd),
a.id_sasaran_renstra
FROM sc_renstra_sasaran a 
LEFT JOIN data_renstra_sasaran b ON b.id_unit = a.id_unit
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_program,cid_unik_sasaran;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_renstra_sasaran SET kode_skpd=cid_unik_program 
							WHERE id_sasaran_renstra=cid_unik_sasaran;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_renstra_sasaran_urusan` ()   BEGIN
declare cid_unik_program TEXT;
declare cid_teks_sasaran TEXT;
declare cid_unik_sasaran INTEGER;
declare cid_nama_skpd INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
	DISTINCT(a.id_unik_sasaran_renstra),
	a.id_sasaran_renstra,
	b.id_sub_urusan
FROM
	sc_renstra_sasaran a 	
	LEFT JOIN data_renstra_sasaran b ON b.id_unik=a.id_unik_sasaran_renstra
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_unik_program,cid_unik_sasaran,cid_nama_skpd;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_renstra_sasaran SET id_sub_urusan=cid_nama_skpd 
							WHERE id_sasaran_renstra=cid_unik_sasaran;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_rpjmd_prog` ()   BEGIN
declare cid_program INTEGER;
declare cid_sasaran_rpjmd INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
 a.id_program_rpjmd,
 b.id_sasaran_rpjmd
FROM
sc_rpjmd_program a
left JOIN sc_rpjmd_sasaran b ON b.id_unik_sasaran = a.id_unik_rpjmd_sasaran
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_program,cid_sasaran_rpjmd;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_rpjmd_program a SET a.id_sasaran_rpjmd = cid_sasaran_rpjmd
							WHERE a.id_program_rpjmd=cid_program;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_rpjmd_prog_indikator` ()   BEGIN
declare cid_program INTEGER;
declare cid_ref_program INTEGER;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
a.id_indikator_program_rpjmd,
b.id_program_rpjmd
FROM
sc_rpjmd_program_indikator a
LEFT JOIN sc_rpjmd_program b ON b.id_unik_rpjmd_program=a.id_unik_rpjmd_program
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_program, cid_ref_program ;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_rpjmd_program_indikator a SET a.id_program_rpjmd=cid_ref_program 
							WHERE a.id_indikator_program_rpjmd=cid_program
							;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_sc_rpjmd_prog_indikator_add` ()   BEGIN
declare cid_program INTEGER;
declare cid_target_awal TEXT;
declare cid_target_akhir TEXT;
declare cid_target_1 TEXT;
declare cid_target_2 TEXT;
declare cid_target_3 TEXT;
declare cid_target_4 TEXT;
declare cid_target_5 TEXT;
declare cid_pagu_1 DOUBLE;
declare cid_pagu_2 DOUBLE;
declare cid_pagu_3 DOUBLE;
declare cid_pagu_4 DOUBLE;
declare cid_pagu_5 DOUBLE;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT
b.id_indikator_program_rpjmd,
a.target_awal,
a.target_akhir,
a.target_1,
a.pagu_1,
a.target_2,
a.pagu_2,
a.target_3,
a.pagu_3,
a.target_4,
a.pagu_4,
a.target_5,
a.pagu_5
FROM
(SELECT
a.id_program,
a.id_unik,
a.id_unik_indikator,
a.indikator,
a.satuan,
a.target_awal,
a.target_akhir,
a.target_1,
a.pagu_1,
a.target_2,
a.pagu_2,
a.target_3,
a.pagu_3,
a.target_4,
a.pagu_4,
a.target_5,
a.pagu_5
FROM
data_rpjmd_program a
) a
LEFT JOIN sc_rpjmd_program_indikator b ON a.id_unik_indikator=b.id_unik_rpjmd_program_indikator
WHERE b.id_indikator_program_rpjmd !=''
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into 
				 cid_program,
				 cid_target_awal,
				 cid_target_akhir,
				 cid_target_1,
				 cid_pagu_1,
				 cid_target_2,
				 cid_pagu_2,
				 cid_target_3,
				 cid_pagu_3,
				 cid_target_4,
				 cid_pagu_4,
				 cid_target_5,
				 cid_pagu_5
				 ;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE sc_rpjmd_program_indikator a SET a.target_awal=cid_target_awal,
							a.target_akhir=cid_target_akhir,
							a.target_tahun_1=cid_target_1,
							a.target_tahun_2=cid_target_2,
							a.target_tahun_3=cid_target_3,
							a.target_tahun_4=cid_target_4,
							a.target_tahun_5=cid_target_5,
							a.target_tahun_1_rp=cid_pagu_1,
							a.target_tahun_2_rp=cid_pagu_2,
							a.target_tahun_3_rp=cid_pagu_3,
							a.target_tahun_4_rp=cid_pagu_4,
							a.target_tahun_5_rp=cid_pagu_5 
							WHERE a.id_indikator_program_rpjmd=cid_program
							;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_skpd_pegawai` ()   BEGIN
declare cid_skpd INTEGER;
declare cijabatan VARCHAR(255);
declare selesai INTEGER;
declare cur1 cursor for 
SELECT b.id_pegawai ,c.id_skpd
FROM pegawai b 
LEFT JOIN ref_skpd c ON LEFT(b.idskpd,2)=c.idskpd
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cijabatan;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE pegawai b SET b.id_skpd=cijabatan
							WHERE b.id_pegawai=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_tmt_cpns_pegawai` ()   BEGIN
declare cid_skpd INTEGER;
declare cid_gol INTEGER;
declare citmt_cpns DATE;
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_pegawai,b.idgolrucpn,
b.tmtcpn
FROM pegawai a 
JOIN tb_01 b ON b.nip=a.nip
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cid_gol,citmt_cpns;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE pegawai b SET b.cpns_id_golongan=cid_gol, b.cpns_tmt=citmt_cpns
							WHERE b.id_pegawai=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

CREATE DEFINER=`cpses_mbwuba7ybl`@`localhost` PROCEDURE `sp_update_unitkerja_pegawai` ()   BEGIN
declare cid_skpd INTEGER;
declare cijabatan VARCHAR(255);
declare selesai INTEGER;
declare cur1 cursor for 
SELECT a.id_pegawai,
b.id_induk
FROM pegawai a
LEFT JOIN ref_unit_kerja b ON b.idskpd=a.idskpd
;
declare continue handler 
for not found set selesai=1;
    open cur1;
    igmLoop: loop
        fetch cur1 into cid_skpd,cijabatan;
        if selesai = 1 then 
					leave igmLoop; 
				end if;
              UPDATE pegawai b SET b.id_unit_kerja=cijabatan
							WHERE b.id_pegawai=cid_skpd;
    end loop igmLoop;
    close cur1;
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Struktur dari tabel `absen_ket_log`
--

CREATE TABLE `absen_ket_log` (
  `id_ket_log` int(11) NOT NULL,
  `id_pegawai` int(11) NOT NULL,
  `id_skpd` int(11) DEFAULT NULL,
  `tanggal_awal` date NOT NULL,
  `tanggal_akhir` date DEFAULT NULL,
  `jumlah` int(11) DEFAULT NULL,
  `id_ket_absen` char(4) DEFAULT NULL,
  `bukti` varchar(250) DEFAULT NULL,
  `keterangan` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPACT;

-- --------------------------------------------------------

--
-- Struktur dari tabel `absen_ket_log_detail`
--

CREATE TABLE `absen_ket_log_detail` (
  `id_ket_log_detail` int(11) NOT NULL,
  `id_ket_log` int(11) NOT NULL,
  `tanggal` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `absen_koreksi`
--

CREATE TABLE `absen_koreksi` (
  `id_absen_koreksi` int(11) NOT NULL,
  `id_helpdesk` int(11) NOT NULL,
  `id_pegawai` int(11) NOT NULL,
  `tanggal` date NOT NULL,
  `jam_masuk` time DEFAULT NULL,
  `jam_pulang` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `absen_log`
--

CREATE TABLE `absen_log` (
  `id_log` bigint(20) NOT NULL,
  `id_pegawai` int(11) DEFAULT NULL,
  `tanggal` date DEFAULT NULL,
  `jam_masuk` time DEFAULT NULL,
  `jam_pulang` time DEFAULT NULL,
  `latitude_masuk` varchar(255) DEFAULT NULL,
  `longitude_masuk` varchar(255) DEFAULT NULL,
  `latitude_pulang` varchar(255) DEFAULT NULL,
  `longitude_pulang` varchar(255) DEFAULT NULL,
  `foto_masuk` varchar(255) DEFAULT NULL,
  `foto_pulang` varchar(255) DEFAULT NULL,
  `tempat` varchar(255) DEFAULT NULL,
  `masuk_telat` decimal(10,2) DEFAULT NULL COMMENT 'menit',
  `pulang_cepat` decimal(10,2) DEFAULT NULL COMMENT 'menit',
  `waktu_kerja` decimal(10,2) DEFAULT NULL COMMENT 'menit',
  `flag` varchar(255) DEFAULT NULL,
  `id_shift` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `absen_log_bak`
--

CREATE TABLE `absen_log_bak` (
  `id_log` bigint(20) NOT NULL,
  `id_pegawai` int(11) DEFAULT NULL,
  `tanggal` date DEFAULT NULL,
  `jam_masuk` time DEFAULT NULL,
  `jam_pulang` time DEFAULT NULL,
  `latitude_masuk` varchar(255) DEFAULT NULL,
  `longitude_masuk` varchar(255) DEFAULT NULL,
  `latitude_pulang` varchar(255) DEFAULT NULL,
  `longitude_pulang` varchar(255) DEFAULT NULL,
  `foto_masuk` varchar(255) DEFAULT NULL,
  `foto_pulang` varchar(255) DEFAULT NULL,
  `tempat` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `absen_shift`
--

CREATE TABLE `absen_shift` (
  `id_shift` int(11) NOT NULL,
  `nama_shift` varchar(255) DEFAULT NULL,
  `jam_masuk` time DEFAULT NULL,
  `jam_pulang` time DEFAULT NULL,
  `hari1` varchar(1) DEFAULT NULL COMMENT 'senin',
  `hari2` varchar(1) DEFAULT NULL COMMENT 'selasa',
  `hari3` varchar(1) DEFAULT NULL COMMENT 'rabu',
  `hari4` varchar(1) DEFAULT NULL COMMENT 'kamis',
  `hari5` varchar(1) DEFAULT NULL COMMENT 'jumat',
  `hari6` varchar(1) DEFAULT NULL COMMENT 'sabtu',
  `hari7` varchar(1) DEFAULT NULL COMMENT 'minggu',
  `flag` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

--
-- Dumping data untuk tabel `absen_shift`
--

INSERT INTO `absen_shift` (`id_shift`, `nama_shift`, `jam_masuk`, `jam_pulang`, `hari1`, `hari2`, `hari3`, `hari4`, `hari5`, `hari6`, `hari7`, `flag`) VALUES
(1, '5 Hari Kerja', '07:00:00', '15:30:00', 'Y', 'Y', 'Y', 'Y', 'Y', NULL, NULL, NULL),
(2, '6 Hari Kerja', '07:00:00', '14:00:00', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', NULL, NULL),
(3, 'Shift Pagi', '07:00:00', '14:00:00', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', NULL),
(4, 'Shift Siang', '14:00:00', '21:00:00', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', NULL),
(5, 'Shift Malam', '21:00:00', '07:00:00', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'beda_hari');

-- --------------------------------------------------------

--
-- Struktur dari tabel `absen_shift_setting`
--

CREATE TABLE `absen_shift_setting` (
  `setting_id` int(11) NOT NULL,
  `id_pegawai` int(11) DEFAULT NULL,
  `id_shift` int(11) DEFAULT NULL,
  `aktif_shift` int(11) DEFAULT NULL,
  `hitung_tpp` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

--
-- Dumping data untuk tabel `absen_shift_setting`
--

INSERT INTO `absen_shift_setting` (`setting_id`, `id_pegawai`, `id_shift`, `aktif_shift`, `hitung_tpp`) VALUES
(1, 2884, 1, 1, NULL),
(2, 82, 1, 1, NULL),
(3, 1457, 1, 1, NULL),
(4, 4500, 1, 1, NULL),
(5, 2883, 1, 1, NULL),
(6, 1739, 1, 1, NULL),
(7, 3715, 1, 1, NULL),
(8, 7093, 1, 1, NULL),
(9, 3576, 1, 1, NULL),
(10, 4343, 2, 2, NULL),
(11, 7151, 1, 1, NULL),
(12, 7373, 1, 1, NULL),
(13, 1166, 1, 1, NULL),
(14, 81, 1, 1, NULL),
(15, 3937, 1, 1, NULL),
(16, 3157, 2, 2, NULL),
(17, 3982, 1, 1, NULL),
(18, 7485, 1, 1, NULL),
(19, 2755, 1, 1, NULL),
(20, 7489, 1, 1, NULL),
(21, 7490, 1, 1, NULL),
(22, 2913, 1, 1, NULL),
(24, 1, 3, 3, NULL),
(25, 2782, 1, 1, NULL),
(26, 2881, 2, 2, NULL),
(27, 2756, 1, 1, NULL),
(28, 7491, 1, 1, NULL),
(29, 2882, 1, 1, NULL),
(30, 3434, 1, 1, NULL),
(31, 19, 1, 1, NULL),
(32, 7359, 1, 1, NULL),
(33, 7356, 1, 1, NULL),
(34, 7358, 1, 1, NULL),
(35, 7360, 1, 1, NULL),
(36, 7361, 1, 1, NULL),
(37, 7362, 1, 1, NULL),
(38, 146, 1, 1, NULL),
(39, 2, 1, 1, NULL),
(40, 7372, 1, 1, NULL),
(41, 224, 1, 1, NULL),
(42, 7888, 1, 1, NULL),
(43, 6779, 2, 2, NULL),
(44, 6795, 2, 2, NULL),
(45, 6673, 2, 2, NULL),
(46, 7829, 1, 1, NULL),
(47, 321, 1, 1, NULL),
(48, 238, 1, 1, NULL),
(49, 292, 1, 1, NULL),
(50, 7661, 1, 1, NULL),
(51, 7911, 1, 1, NULL);

-- --------------------------------------------------------

--
-- Struktur dari tabel `agenda`
--

CREATE TABLE `agenda` (
  `id_agenda` int(5) NOT NULL,
  `tema` varchar(100) NOT NULL,
  `tema_slug` varchar(100) NOT NULL,
  `isi_agenda` text NOT NULL,
  `tempat` varchar(100) NOT NULL,
  `pengirim` varchar(100) NOT NULL,
  `penerima` text DEFAULT NULL,
  `tgl_mulai` date NOT NULL,
  `tgl_selesai` date NOT NULL,
  `tgl_posting` date NOT NULL,
  `jam` time NOT NULL,
  `nama_file` text DEFAULT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `bambang`
--

CREATE TABLE `bambang` (
  `nip` int(1) DEFAULT NULL,
  `nama_lengkap` int(1) DEFAULT NULL,
  `tahun` int(1) DEFAULT NULL,
  `bulan` int(1) DEFAULT NULL,
  `nama_skpd` int(1) DEFAULT NULL,
  `jumlah` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Struktur dari tabel `bangkom_diklat`
--

CREATE TABLE `bangkom_diklat` (
  `id_diklat` int(10) UNSIGNED NOT NULL,
  `nama_diklat` varchar(191) NOT NULL,
  `kategori_diklat` varchar(191) NOT NULL,
  `jenis_pelatihan` varchar(191) DEFAULT NULL,
  `nilai_kesenjangan` varchar(191) DEFAULT NULL,
  `model_penyelenggara` varchar(191) NOT NULL,
  `penyelenggara` varchar(191) NOT NULL,
  `jadwal` datetime NOT NULL,
  `jam_pelajaran` int(11) NOT NULL,
  `anggaran` double(15,2) NOT NULL,
  `dpa` varchar(191) NOT NULL,
  `kesesuaian` varchar(191) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `bangkom_indikator`
--

CREATE TABLE `bangkom_indikator` (
  `id_indikator` int(10) UNSIGNED NOT NULL,
  `jenis_kompetensi` varchar(191) NOT NULL,
  `nama_kompetensi` varchar(191) NOT NULL,
  `indikator` varchar(3000) NOT NULL,
  `id_skpd` int(11) DEFAULT NULL,
  `jabatan` varchar(191) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `bangkom_peserta`
--

CREATE TABLE `bangkom_peserta` (
  `id_peserta` int(10) UNSIGNED NOT NULL,
  `id_pegawai` int(10) UNSIGNED NOT NULL,
  `jenis_kompetensi` varchar(191) NOT NULL,
  `tahun_kegiatan` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `nilai_kompetensi` varchar(191) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 9,
  `token` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `bangkom_peserta_detail`
--

CREATE TABLE `bangkom_peserta_detail` (
  `id` int(10) UNSIGNED NOT NULL,
  `id_peserta` int(10) UNSIGNED NOT NULL,
  `nama_kompetensi` varchar(191) NOT NULL,
  `nilai_kesenjangan` varchar(191) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `bangkom_peserta_diklat`
--

CREATE TABLE `bangkom_peserta_diklat` (
  `id` int(10) UNSIGNED NOT NULL,
  `id_peserta` int(10) UNSIGNED NOT NULL,
  `id_diklat` int(10) UNSIGNED NOT NULL,
  `status` tinyint(1) NOT NULL,
  `status_diklat` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `bangkom_peserta_indikator`
--

CREATE TABLE `bangkom_peserta_indikator` (
  `id` int(10) UNSIGNED NOT NULL,
  `id_peserta` int(10) UNSIGNED NOT NULL,
  `id_indikator` int(10) UNSIGNED NOT NULL,
  `status` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `banner`
--

CREATE TABLE `banner` (
  `id_banner` int(5) NOT NULL,
  `judul` varchar(100) NOT NULL,
  `url` varchar(100) NOT NULL,
  `gambar` varchar(100) NOT NULL,
  `tgl_posting` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `banner_bawah`
--

CREATE TABLE `banner_bawah` (
  `id_banner` int(5) NOT NULL,
  `judul` varchar(100) NOT NULL,
  `url` varchar(100) NOT NULL,
  `gambar` varchar(100) NOT NULL,
  `tgl_posting` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `berkas_lakip`
--

CREATE TABLE `berkas_lakip` (
  `id_berkas_lakip` int(11) NOT NULL,
  `id_skpd` int(11) NOT NULL,
  `tahun_berkas_lakip` text NOT NULL,
  `renstra` text NOT NULL,
  `rkt` text NOT NULL,
  `pk` text NOT NULL,
  `lkj` text NOT NULL,
  `lainnya` text DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `berkas_unit_kerja`
--

CREATE TABLE `berkas_unit_kerja` (
  `id_berkas` int(11) NOT NULL,
  `id_unit_kerja` int(11) NOT NULL,
  `tahun_berkas` varchar(4) NOT NULL,
  `renstra` text NOT NULL,
  `rkt` text NOT NULL,
  `pk` text NOT NULL,
  `lkj` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

--
-- Dumping data untuk tabel `berkas_unit_kerja`
--

INSERT INTO `berkas_unit_kerja` (`id_berkas`, `id_unit_kerja`, `tahun_berkas`, `renstra`, `rkt`, `pk`, `lkj`) VALUES
(1, 2, '2021', 'Bab_5.pdf', 'Bab_51.pdf', 'Bab_52.pdf', 'Bab_53.pdf'),
(2, 2, '2022', 'Rekap_LKH__DINDA_ROSALIA_A.Md._Bulan_Juni_Tahun_2022_1658132479.pdf', '', '', ''),
(3, 3, '2023', 'searching-bar.png', 'searching-bar_(1).png', 'searching-bar_(1)1.png', 'searching-bar1.png');

-- --------------------------------------------------------

--
-- Struktur dari tabel `berkas_usulan_pensiun`
--

CREATE TABLE `berkas_usulan_pensiun` (
  `id_berkas_usulan` int(11) NOT NULL,
  `id_usulan` int(11) NOT NULL,
  `id_persyaratan_pensiun` int(11) NOT NULL,
  `nama_file` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `buka_tutup_form`
--

CREATE TABLE `buka_tutup_form` (
  `id` int(11) NOT NULL,
  `nama` varchar(45) DEFAULT NULL,
  `slug` varchar(45) DEFAULT NULL,
  `status` enum('Y','N') DEFAULT 'Y',
  `tanggal_mulai` date DEFAULT NULL,
  `tanggal_tutup` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `calendar_pribadi`
--

CREATE TABLE `calendar_pribadi` (
  `id` int(11) NOT NULL,
  `id_pegawai` int(11) DEFAULT NULL,
  `id_skpd` int(11) NOT NULL,
  `title` varchar(126) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `color` varchar(24) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `create_at` datetime DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  `modified_by` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `calendar_umum`
--

CREATE TABLE `calendar_umum` (
  `id` int(11) NOT NULL,
  `id_pegawai` int(11) DEFAULT NULL,
  `id_skpd` int(11) DEFAULT NULL,
  `title` varchar(126) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `color` varchar(24) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `create_at` datetime DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  `modified_by` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `casecade_unit_kerja_iku_sk_renstra`
--

CREATE TABLE `casecade_unit_kerja_iku_sk_renstra` (
  `id_casecade_unit_kerja_iku_sk_renstra` int(11) NOT NULL,
  `id_iku_sk_renstra` int(11) NOT NULL,
  `id_unit_kerja` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `casecade_unit_kerja_iku_sp_renstra`
--

CREATE TABLE `casecade_unit_kerja_iku_sp_renstra` (
  `id_casecade_unit_kerja_iku_sp_renstra` int(11) NOT NULL,
  `id_iku_sp_renstra` int(11) NOT NULL,
  `id_unit_kerja` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `casecade_unit_kerja_iku_ssk_renstra`
--

CREATE TABLE `casecade_unit_kerja_iku_ssk_renstra` (
  `id_casecade_unit_kerja_iku_ssk_renstra` int(11) NOT NULL,
  `id_iku_ssk_renstra` int(11) NOT NULL,
  `id_unit_kerja` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `casecade_unit_kerja_iku_ss_renstra`
--

CREATE TABLE `casecade_unit_kerja_iku_ss_renstra` (
  `id_casecade_unit_kerja_iku_ss_renstra` int(11) NOT NULL,
  `id_iku_ss_renstra` int(11) NOT NULL,
  `id_unit_kerja` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `catatan`
--

CREATE TABLE `catatan` (
  `id_catatan` int(11) NOT NULL,
  `id_pegawai` int(11) NOT NULL,
  `id_skpd` int(11) DEFAULT NULL,
  `nama_catatan` char(25) NOT NULL,
  `isi` text NOT NULL,
  `tanggal` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `category`
--

CREATE TABLE `category` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(50) NOT NULL,
  `category_slug` varchar(50) NOT NULL,
  `category_status` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `category_download`
--

CREATE TABLE `category_download` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(50) NOT NULL,
  `category_status` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `category_video`
--

CREATE TABLE `category_video` (
  `category_video_id` int(11) NOT NULL,
  `category_video_name` varchar(50) NOT NULL,
  `category_video_status` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `covid_lokasi`
--

CREATE TABLE `covid_lokasi` (
  `id_covid_lokasi` int(11) NOT NULL,
  `id_covid_status_pasien` int(11) NOT NULL,
  `usia` int(11) DEFAULT NULL,
  `id_kabupaten` int(11) DEFAULT NULL,
  `id_kecamatan` int(11) DEFAULT NULL,
  `id_desa` int(11) DEFAULT NULL,
  `lat` text DEFAULT NULL,
  `lng` text DEFAULT NULL,
  `addr` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `covid_status_pasien`
--

CREATE TABLE `covid_status_pasien` (
  `id_covid_status_pasien` int(11) NOT NULL,
  `nama_status` varchar(200) NOT NULL,
  `warna` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `data_bkd`
--

CREATE TABLE `data_bkd` (
  `nip` varchar(30) DEFAULT NULL,
  `nama_lengkap` varchar(200) DEFAULT NULL,
  `temlahir` varchar(200) DEFAULT NULL,
  `tgllahir` varchar(100) DEFAULT NULL,
  `lulus` varchar(10) DEFAULT NULL,
  `jurusan` varchar(100) DEFAULT NULL,
  `lembaga` varchar(100) DEFAULT NULL,
  `tmtpang` date DEFAULT NULL,
  `tmtcpns` date DEFAULT NULL,
  `tmtpns` date DEFAULT NULL,
  `tmtjab` date DEFAULT NULL,
  `nss` varchar(100) DEFAULT NULL,
  `unit` varchar(100) DEFAULT NULL,
  `dudukpeg` varchar(100) DEFAULT NULL,
  `usia` varchar(3) DEFAULT NULL,
  `masakerja` varchar(10) DEFAULT NULL,
  `jenis_jabatan` varchar(50) DEFAULT NULL,
  `nama_jabatan` varchar(200) DEFAULT NULL,
  `nama_eselon` varchar(100) DEFAULT NULL,
  `kelamin` varchar(20) DEFAULT NULL,
  `agama` varchar(20) DEFAULT NULL,
  `kawin` varchar(50) DEFAULT NULL,
  `pendidikan` varchar(50) DEFAULT NULL,
  `gol` varchar(50) DEFAULT NULL,
  `pangkat` varchar(50) DEFAULT NULL,
  `nama_dudukpeg` varchar(100) DEFAULT NULL,
  `nama_statuspeg` varchar(100) DEFAULT NULL,
  `unitkerja` varchar(200) DEFAULT NULL,
  `tingkat` varchar(100) DEFAULT NULL,
  `tgl_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `data_bkd2`
--

CREATE TABLE `data_bkd2` (
  `nip` varchar(200) NOT NULL,
  `nama_lengkap` text DEFAULT NULL,
  `temlahir` text DEFAULT NULL,
  `tgllahir` text DEFAULT NULL,
  `lulus` text DEFAULT NULL,
  `jurusan` text DEFAULT NULL,
  `lembaga` text DEFAULT NULL,
  `tmtpang` text DEFAULT NULL,
  `tmtcpns` text DEFAULT NULL,
  `tmtpns` text DEFAULT NULL,
  `tmtjab` text DEFAULT NULL,
  `nss` text DEFAULT NULL,
  `unit` text DEFAULT NULL,
  `dudukpeg` text DEFAULT NULL,
  `usia` text DEFAULT NULL,
  `masakerja` text DEFAULT NULL,
  `jenis_jabatan` text DEFAULT NULL,
  `nama_jabatan` text DEFAULT NULL,
  `nama_eselon` text DEFAULT NULL,
  `kelamin` text DEFAULT NULL,
  `agama` text DEFAULT NULL,
  `kawin` text DEFAULT NULL,
  `pendidikan` text DEFAULT NULL,
  `gol` text DEFAULT NULL,
  `pangkat` text DEFAULT NULL,
  `nama_dudukpeg` text DEFAULT NULL,
  `nama_statuspeg` text DEFAULT NULL,
  `kodeunitkerja` text DEFAULT NULL,
  `unitkerja` text DEFAULT NULL,
  `jenispek` text DEFAULT NULL,
  `kode` text DEFAULT NULL,
  `tkt` text DEFAULT NULL,
  `kdpang` text DEFAULT NULL,
  `tgl_update` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Struktur dari tabel `data_bkd_1`
--

CREATE TABLE `data_bkd_1` (
  `nip` varchar(30) DEFAULT NULL,
  `nama_lengkap` varchar(200) DEFAULT NULL,
  `temlahir` varchar(200) DEFAULT NULL,
  `tgllahir` varchar(100) DEFAULT NULL,
  `lulus` varchar(10) DEFAULT NULL,
  `jurusan` varchar(100) DEFAULT NULL,
  `lembaga` varchar(100) DEFAULT NULL,
  `tmtpang` date DEFAULT NULL,
  `tmtcpns` date DEFAULT NULL,
  `tmtpns` date DEFAULT NULL,
  `tmtjab` date DEFAULT NULL,
  `nss` varchar(100) DEFAULT NULL,
  `unit` varchar(100) DEFAULT NULL,
  `dudukpeg` varchar(100) DEFAULT NULL,
  `usia` varchar(3) DEFAULT NULL,
  `masakerja` varchar(10) DEFAULT NULL,
  `jenis_jabatan` varchar(50) DEFAULT NULL,
  `nama_jabatan` varchar(200) DEFAULT NULL,
  `nama_eselon` varchar(100) DEFAULT NULL,
  `kelamin` varchar(20) DEFAULT NULL,
  `agama` varchar(20) DEFAULT NULL,
  `kawin` varchar(50) DEFAULT NULL,
  `pendidikan` varchar(50) DEFAULT NULL,
  `gol` varchar(50) DEFAULT NULL,
  `pangkat` varchar(50) DEFAULT NULL,
  `nama_dudukpeg` varchar(100) DEFAULT NULL,
  `nama_statuspeg` varchar(100) DEFAULT NULL,
  `unitkerja` varchar(200) DEFAULT NULL,
  `tingkat` varchar(100) DEFAULT NULL,
  `tgl_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;