Script Membuat Laporan Harian "Paket Komplit"

Mungkin diantara kita, kita? udah saya aja deh :D sering kebingungan disaat saya di minta untuk buat Laporan Harian yg isinya kompleks,  secara kayaknya kalo mau di pecah2 jadi per item Laporan bakal buat diri sendiri ribet. dan akhirnya ini saya buat Script yg bekerja di Server SIAK dan terhubung dengan Server KTP-el menggunakan DB LINK dengan nama ORCL dan LOCAL, karena di script ini ada item untuk jumlah perekaman dan jumlah cetak ktp dan bagi yg belum bisa buat DB link silahkan lihat Cara Koneksi Antar Database dengan Menggunakan Database Link (DB-Link)

Langkah 1.
Remote database SIAK pake Toad yah., dan pastikan sudah ada DB-Link ke DB KTP-el Cetak dengan nama ORCL dan DB-Link  ke DB KTP-el Rekam dengan nama LOCAL
alter session set nls_date_format = 'DD-MM-YYYY';

Langkah 2.
Paste ke Editor untuk cari data Laporan Harian ini
alter session set nls_date_format = 'DD-MM-YYYY';
select a.DAY TGL
, TO_CHAR(TO_DATE(a.day), 'DAY' , 'nls_date_language = INDONESIAN') AS HARI
, case when KTP_BARU_LK is null then 0 else KTP_BARU_LK end KTP_BARU_LK
, case when KTP_BARU_PR is null then 0 else KTP_BARU_PR end KTP_BARU_PR
, case when KTP_RUSAK_LK is null then 0 else KTP_RUSAK_LK end KTP_RUSAK_LK
, case when KTP_RUSAK_PR is null then 0 else KTP_RUSAK_PR end KTP_RUSAK_PR
, case when KTP_UBAH_DATA_LK is null then 0 else KTP_UBAH_DATA_LK end KTP_UBAH_DATA_LK
, case when KTP_UBAH_DATA_PRP is null then 0 else KTP_UBAH_DATA_PRP end KTP_UBAH_DATA_PR
, case when KTP_HILANG_LK is null then 0 else KTP_HILANG_LK end KTP_HILANG_LK
, case when KTP_HILANG_PR is null then 0 else KTP_HILANG_PR end KTP_HILANG_PR
, case when perihal_lahin_lk is null then 0 else perihal_lahin_lk end ktp_lain_lk
, case when perihal_lahin_pr is null then 0 else perihal_lahin_pr end ktp_lain_pr 
, case when cetak_kosong_lk is null then 0 else cetak_kosong_lk end cetak_kosong_lk
, case when cetak_kosong_pr is null then 0 else cetak_kosong_pr end cetak_kosong_pr
, case when REKAM_LK is null then 0 else REKAM_LK end REKAM_LK
, case when REKAM_PR is null then 0 else REKAM_PR end REKAM_PR
, case when REKAM_PRR is null then 0 else REKAM_PRR end REKAM_PRR
, case when REKAM_DPR is null then 0 else REKAM_DPR end REKAM_DPR
, case when REKAM_SFE is null then 0 else REKAM_SFE end REKAM_SFE
, case when KK_CETAK_TTE is null then 0 else KK_CETAK_TTE end KK_TTE_CETAK
, case when KIA_PR is null then 0 else KIA_PR end KIA_PR
, case when KIA_LK is null then 0 else KIA_LK end KIA_LK
, case when SUKET_LK is null then 0 else SUKET_LK end SUKET_LK
, case when SUKET_PR is null then 0 else SUKET_PR end SUKET_PR
, case when PINDAH_LK is null then 0 else PINDAH_LK end PINDAH_LK
, case when PINDAH_PR is null then 0 else PINDAH_PR end PINDAH_PR
, case when DATANG_LK is null then 0 else DATANG_LK end DATANG_LK
, case when DATANG_PR is null then 0 else DATANG_PR end DATANG_PR
, case when AKTE_LK is null then 0 else AKTE_LK end AKTE_LAHIR_TTE_LK
, case when AKTE_PRP is null then 0 else AKTE_PRP end AKTE_LAHIR_TTE_PR
, case when MATI_LK is null then 0 else MATI_LK end AKTE_MATI_TTE_LK
, case when MATI_PRP is null then 0 else MATI_PRP end AKTE_MATI_TTE_PR
, case when CERAI_TTE is null then 0 else CERAI_TTE end AKTE_CERAI_TTE
, case when KAWIN_KRISTEN is null then 0 else KAWIN_KRISTEN end AKTE_KAWIN_TTE_KRISTEN
, case when KAWIN_KHATOLIK is null then 0 else KAWIN_KHATOLIK end AKTE_KAWIN_TTE_KATHOLIK
, case when KAWIN_HINDU is null then 0 else KAWIN_HINDU end AKTE_KAWIN_TTE_HINDU
, case when KAWIN_BUDHA is null then 0 else KAWIN_BUDHA end AKTE_KAWIN_TTE_KAWIN_BUDDHA
, case when KAWIN_KHONG is null then 0 else KAWIN_KHONG end AKTE_KAWIN_TTE_KAWIN_KHONHUCU
, case when KAWIN_YME is null then 0 else KAWIN_YME end AKTE_KAWIN_TTE_KAWIN_YME
, case when SAH_ANAK_LK is null then 0 else SAH_ANAK_LK end AKTE_SAH_ANAK_TTE_LK
, case when SAH_ANAK_PR is null then 0 else SAH_ANAK_PR end AKTE_SAH_ANAK_TTE_PR
from 
(SELECT TRUNC(to_date(SYSDATE,'dd-mm-yyyy')) - LEVEL + 1 AS day
FROM dual
--- ganti freq tanggal
CONNECT BY level <=30) a 
LEFT JOIN (
select  to_char(pejabat_process_date,'dd-mm-yyyy') tgl
, sum (case when bayi_jns_kelamin = 1 and cert_status = 4 then 1 else 0 end) akte_lk
, sum (case when bayi_jns_kelamin = 2 and cert_status = 4 then 1 else 0 end) akte_prp
from bsre_kelahiran
group by to_char(pejabat_process_date,'dd-mm-yyyy')
) b on a.day = b.tgl 
LEFT JOIN (
select  to_char(pejabat_process_date,'dd-mm-yyyy') tgl
, sum (case when cert_status = 4 then 1 else 0 end) kk_cetak_tte
from bsre_kartu_keluarga
group by to_char(pejabat_process_date,'dd-mm-yyyy')
) c ON a.day=c.tgl 
LEFT JOIN (
select  to_char(pejabat_process_date,'dd-mm-yyyy') tgl
, sum (case when mati_jns_kelamin = 1 and cert_status = 4 then 1 else 0 end) mati_lk
, sum (case when mati_jns_kelamin = 2 and cert_status = 4 then 1 else 0 end) mati_prp
from bsre_kematian
group by to_char(pejabat_process_date,'dd-mm-yyyy')
) d on a.day = d.tgl
LEFT JOIN (
select  to_char(pejabat_process_date,'dd-mm-yyyy') tgl
, sum (case when cert_status = 4 then 1 else 0 end) cerai_tte
from bsre_perceraian
group by to_char(pejabat_process_date,'dd-mm-yyyy')
) e on a.day = e.tgl
LEFT JOIN (
select  to_char(pejabat_process_date,'dd-mm-yyyy') tgl
, sum (case when kawin_agama = 2 and cert_status = 4 then 1 else 0 end) kawin_kristen
, sum (case when kawin_agama = 3 and cert_status = 4 then 1 else 0 end) kawin_khatolik
, sum (case when kawin_agama = 4 and cert_status = 4 then 1 else 0 end) kawin_hindu
, sum (case when kawin_agama = 5 and cert_status = 4 then 1 else 0 end) kawin_budha
, sum (case when kawin_agama = 6 and cert_status = 4 then 1 else 0 end) kawin_khong
, sum (case when kawin_agama = 7 and cert_status = 4 then 1 else 0 end) kawin_yme
from bsre_perkawinan
group by to_char(pejabat_process_date,'dd-mm-yyyy')
) f on a.day = f.tgl
LEFT JOIN (
select  to_char(pejabat_process_date,'dd-mm-yyyy') tgl
, sum (case when anak_jns_kelamin =  1 and cert_status = 4 then 1 else 0 end) sah_anak_lk
, sum (case when anak_jns_kelamin =  2 and cert_status = 4 then 1 else 0 end) sah_anak_pr
from bsre_pengesahan_anak
group by to_char(pejabat_process_date,'dd-mm-yyyy')
) g on a.day = g.tgl
LEFT JOIN (
select to_char(printed_date,'dd-mm-yyyy') tgl
, sum (case when jenis_klmin =  1 then 1 else 0 end) kia_lk
, sum (case when jenis_klmin =  2 then 1 else 0 end) kia_pr
 from T5_SEQN_KIA_PRINT group by to_char(printed_date,'dd-mm-yyyy')
 ) h on a.day= h.tgl
LEFT JOIN (
select to_char(printed_date,'dd-mm-yyyy') tgl
, sum (case when jenis_klmin =  'LAKI-LAKI' then 1 else 0 end) suket_lk
, sum (case when jenis_klmin =  'PEREMPUAN' then 1 else 0 end) suket_pr
 from T7_HIST_SUKET group by to_char(printed_date,'dd-mm-yyyy')
 ) i on a.day=i.tgl
LEFT JOIN (
select 
to_char(created_date,'dd-mm-yyyy') tgl,
sum(case when  klmin_pemohon = 1 then 1 else 0 end) pindah_lk,
sum(case when klmin_pemohon  = 2 then 1 else 0 end) pindah_pr
from pindah_header
group by to_char(created_date,'dd-mm-yyyy')
) j on a.day = j.tgl
LEFT JOIN (
select to_char(a.created_date,'dd-mm-yyyy') tgl
, sum (case when b.jenis_klmin = 1 then 1 else 0 end) datang_lk
, sum (case when b.jenis_klmin = 2 then 1 else 0 end) datang_pr
from datang_header a left join  datang_detail b on a.no_datang = b.no_datang and a.nik_pemohon = b.nik
group by to_char(a.created_date,'dd-mm-yyyy')
) k on a.day = k.tgl
LEFT JOIN (
select to_char(created,'dd-mm-yyyy') tgl
, sum (case when upper(jenis_klmin) ='LAKI-LAKI' then 1 else 0 end) rekam_lk
, sum (case when upper(jenis_klmin) ='PEREMPUAN' then 1 else 0 end) rekam_pr
, sum (case when current_status_code ='PRINT_READY_RECORD' then 1 else 0 end) rekam_prr
, sum (case when current_status_code ='SENT_FOR_ENROLLMENT' then 1 else 0 end) rekam_sfe
, sum (case when current_status_code ='DUPLICATE_RECORD' then 1 else 0 end) rekam_dpr
 from demographics@local
 group by to_char(created,'dd-mm-yyyy')
 ) l on a.day=l.tgl
LEFT JOIN (
SELECT to_char(personalized_date,'dd-mm-yyyy') TGL
, sum( case when upper(reprint_reason) = 'UPDATE_ELEMENT' OR upper(reprint_reason) = 'PERUBAHAN_ELEMEN' 
    and jenis_klmin = 'LAKI-LAKI' THEN 1 ELSE 0 END) ktp_ubah_data_lk
, sum(case when upper(reprint_reason) = 'UPDATE_ELEMENT' OR upper(reprint_reason) = 'PERUBAHAN_ELEMEN' 
    and jenis_klmin = 'PEREMPUAN' THEN 1 ELSE 0 END) ktp_ubah_data_prp
, sum(case when upper(reprint_reason) = 'CARD_LOST' OR upper(reprint_reason) = 'PERGANTIAN_KARTU_HILANG' 
    and jenis_klmin = 'LAKI-LAKI' THEN 1 ELSE 0 END) ktp_hilang_lk
, sum(case when upper(reprint_reason) = 'CARD_LOST' OR upper(reprint_reason) = 'PERGANTIAN_KARTU_HILANG' 
    and jenis_klmin = 'PEREMPUAN' THEN 1 ELSE 0 END) ktp_hilang_pr
, sum(case when upper(reprint_reason) = 'OTHER_CAUSES' OR upper(reprint_reason) = 'PERIHAL_LAIN' 
    and jenis_klmin = 'LAKI-LAKI' THEN 1 ELSE 0 END) perihal_lahin_lk
, sum(case when upper(reprint_reason) = 'OTHER_CAUSES' OR upper(reprint_reason) = 'PERIHAL_LAIN' 
    and jenis_klmin = 'PEREMPUAN' THEN 1 ELSE 0 END) perihal_lahin_pr
, sum(case when upper(reprint_reason) = 'CARD_BROKEN' OR upper(reprint_reason) = 'PERGANTIAN_KARTU_RUSAK' 
    and jenis_klmin = 'LAKI-LAKI' THEN 1 ELSE 0 END) ktp_rusak_lk
, sum(case when upper(reprint_reason) = 'CARD_BROKEN' OR upper(reprint_reason) = 'PERGANTIAN_KARTU_RUSAK' 
    and jenis_klmin = 'PEREMPUAN' THEN 1 ELSE 0 END) ktp_rusak_pr
, sum(case when upper(reprint_reason) = 'NEW_PRINTED' OR upper(reprint_reason) = 'PENCETAKAN_BARU' 
    and jenis_klmin = 'LAKI-LAKI' THEN 1 ELSE 0 END) ktp_baru_lk
, sum(case when upper(reprint_reason) = 'NEW_PRINTED' OR upper(reprint_reason) = 'PENCETAKAN_BARU' 
    and jenis_klmin = 'PEREMPUAN' THEN 1 ELSE 0 END) ktp_baru_pr
, sum(case when (reprint_reason is null  OR reprint_reason = '')
   and jenis_klmin = 'LAKI-LAKI' THEN 1 ELSE 0 END) cetak_kosong_lk
, sum(case when (reprint_reason is null  OR reprint_reason = '')
   and jenis_klmin = 'PEREMPUAN' THEN 1 ELSE 0 END) cetak_kosong_pr
from card_management@ORCL a LEFT JOIN  demographics@orcl b on a.nik=b.nik
group by to_char(personalized_date,'dd-mm-yyyy') 
) m on a.day=m.tgl
order by a.day desc;
Keterangan : Silahkan ganti --- ganti freq tanggal untuk ganti frequensi berapa hari yg akan di tampilin untuk laporan, script di atas 35 berarti untuk 35 Hari, kalo mau cari 1 hari ganti jadi angka 1, untuk 1 tahun ganti aja jadi 365

Update : 14 Juni 2020

Semoga bermanfaat,

Subscribe to receive free email updates:

0 Response to "Script Membuat Laporan Harian "Paket Komplit""

Posting Komentar