LAMPIRAN L1. Tabel Metadata RS UD KOJA a. Nama Tabel
: Identitas Pasien
Primary Key
: NoCM
Deskripsi tabel
: Tabel yang berisi data-data identitas lengkap pasien
Nama Field
Tipe Field
Ukuran Field
Keterangan
NoCM
varchar
(11)
Kode pasien rumah sakit
namalengkap
varchar
(40)
Identitas nama pasien
namakeluarga
varchar
(30)
Identitas sebagai penjamin
tempatlahir
varchar
(20)
Identitas tempat lahir pasien
tanggallahir
datetime
-
Identitas tanggal lahir pasien
umur
int
(3)
Identitas umur pasien
jeniskelamin
varchar
(1)
Identitas jenis kelamin pasien
statuspernikahan
varchar
(20)
Identitas status pernikahan pasien
pendidikan
varchar
(15)
Identitas jenjang pendidikan pasien
pekerjaan
varchar
(20)
Identitas jenis pekerjaan pasien
alamat
varchar
(60)
Identitas alamat lengkap pasien
notelp
varchar
(15)
Identitas no telp pasien
Agama
varchar
(18)
Identitas agama pasien
b. Nama Tabel
: Kamar
Primary Key
: KodeKamar
Description Tabel
: Tabel yang berisi data-data lengkap kamar / ruangan perawatan yang disediakan RSUD Koja
Nama Field
Tipe Field
Ukuran Field
Keterangan
KodeRuangRawat
varchar
(4)
Kode Ruang rawat
Kodekamar
varchar
(5)
Kode Kamar rawat L1
Bagiankamar
varchar
Statuskamar
bit
JmlhTT
int
(50)
Nama jenis ruang rawat Status pemakaian ruangan Jumlah tempat tidur masing-masing ruangan
(5)
c. Nama Tabel
: Dokter
Primary Key
: NRKDokter
Deskripsi tabel
: Tabel yang berisi data-data identitas lengkap dokter yang betugas di RSUD Koja.
Nama Field
Tipe Field
Ukuran Field
Keterangan
NRKDokter
varchar
(5)
No. Induk dokter
NamaDokter
varchar
(35)
Identitas nama dokter
Alamat
varchar
(40)
Identitas alamat lengkap dokter
TlpRmh
varchar
(12)
Identitas alamat lengkap dokter
NoHP
varchar
(13)
Identitas No.Handphone dokter
Kota
varchar
(20)
Identitas alamat kota pasien
Nip
varchar
(9)
No.Induk Pegawai masing-masing dokter
d. Nama Tabel
: SM F
Primary Key
: KodeSM F
Deskripsi tabel
: Tabel SM F berisi data identitas SM F/Poli yang ada di RSUD Koja
Nama Field
Tipe Field
Ukuran Field
Keterangan
KodeSM F
varchar
(2)
Kode dari SM F
NamaSM F
varchar
(35)
Nama masing-masing SM F
DeskripsiFungsi
varchar
(50)
Deskripsi singkat dari SM F
L2
e. Nama Tabel
: Poliklinik
Primary Key
: KodePoliklinik
Deskripsi tabel
: Tabel poliklinik berisi data identitas poliklinik yang tersedia.
Nama Field KodePoliklinik
Tipe Field varchar
Ukuran Field (30)
Keterangan No.kode poliklinik
NamaPoliklinik
varchar
(35)
Nama poliklinik
Lantai
varchar
(15)
Lokasi poloklinik
JenisPoliklinik
char
(1)
Jenis golongan poliklinik
KodePelayananM edik
varchar
(4)
No.kode jenis pelayanan
f. Nama Tabel
: JadualPeriksaRs
Primary Key
: KodeJadwalRajal
Deskripsi tabel
: Tabel yang berisi jadwal pemeriksaan rawat jalan
Nama Field KodeJadwalRajal
Tipe Field varchar
NamaRJ
varchar
WaktuBuka
datetime
Deskripsi mulai waktu kunjungan
WaktuTutup
datetime
Deskripsi tutup waktu kunjungan
Deskripsi
varchar
g. Nama Tabel
Ukuran Field (4) (20)
(50)
Nama Ruang Rajal
Deskripsi singkat ruang rawat jalan
: RegistrasiRS
Primary Key
: NoRegistrasi
Deskripsi tabel
: Tabel yang berisi data pendaftaran pasien baru
Nama Field NoCM NoRegistrasi
Tipe Field varchar varchar
Ukuran Field (11) (10) L3
Keterangan Kode jadwal di rawat jalan
Keterangan No.Keanggotaan pasien No.Urutan registrasi
TanggalRegistrasi WaktuRegistrasi
datetime datetime
h. Nama Tabel
Tanggal awal registrasi Jam registrasi
: RuangRawat
Primary Key
: KodeRuangRawat
Deskripsi tabel
: Table yang berisi data pelayanan medik
Nama Field KodePelayananM edik KodeRuangRawat Nmruangrawat GedungLantai Status
Tipe Field varchar varchar varchar varchar bit
i. Nama Tabel
Ukuran Field (4) (4) (50) (60)
Keterangan Id Pelayanan M edik Id Ruang Perawatan Nomer ruangan rawat Lokasi ruang Kondisi apakah terpakai atau kosong
: TarifTindakanM edikIGD
Primary Key
: KodePelayanan
Deskripsi tabel
: Tabel yang berisi data tentang Surat Keputusan
Nama Field NoSK KodeKlpkTM KodeKlpkTarifTM KodeSM F KodePelayanan UraianTindakan JS JP TarifSendiri
Tipe Field varchar varchar varchar varchar varchar varchar money money money
j. Nama Tabel
Ukuran Field (3) (2) (2) (2) (9) (80)
Keterangan Identitas nomor Surat Keputusan Kd jenis Tindakan M edik Kd tarif tindakan medik Kd SM F Kd Pelayanan M edik Jenis tindakan yang telah diberikan
Biaya yang dibayar sendiri
: BillingIGDKlpkPasien
Primary Key
: IdTran
Deskripsi tabel
:Tabel yang berisi data tentang transaksi pembayaran kelompok pasien di IGD L4
Nama Field
Tipe Field
NoCM NoCM Temp IdTran TanggalTransaksi WaktuCetakStruk NoRegistrasi NoStruk KodePelayananM edik KodeDokter NRKKasir KodeJadwalIGD NamaKlpkPasien PenjaminBayar
varchar varchar Int datetime datetime varchar varchar varchar varchar varchar varchar varchar varchar
KodePelayanan UraianTindakan Kuantitas TarifSendiri TotalBiaya Piutang SelisihBiaya SisaBiaya Iurbiayaspaskes Biayaspaskesterpakai
varchar varchar int money money money money money money money
Bayar Bebas Statustanggungan
money money varchar
JasaSarana JasaPelayanan JasaDokter JasaAskep InsentifTM RajalDr
money money money money money
InsentifTM RajalPrwt
money
Ukuran Field (11) (10)
(10) (9) (4) (5) (5) (4) (30) (35) (9) (40)
NoKeanggotaan Pasien Identitas Kartu M edikal Sementara Identitas Transaksi Tanggal saat transaksi Jam saat pencetakan struk Nomor Pendaftaran Nomor struk Identitas Pelayanan M edik Identitas Dokter Identitas No Kasir Identitas Jadwal IGD Nama kelompok pasien Informasi penjamin yg akan membayar Identitas Pelayanan Penjelasan tentang tindakan Jumlah transaksi Biaya yang dibayarkan sendiri Jumlah total biaya IGD Pendapatan yang tertunda
Biaya diluar surat penjamin askes Biaya menggunakan surat penjamin askes Jumlah yang harus dibayar (50)
L5
Keterangan
Status pasien menggunakan tanggungan atau tidak Biaya jasa sarana Biaya jasa pelayanan Biaya jasa Dokter Biaya jasa asistem keperawatan Jasa tindakan medic dokter pada rawat jalan Jasa tindakan medic perawat pada rawat jalan
k. Nama Tabel
: TarifTindakanM edikRajal
Primary Key
: KodePelayanan
Deskripsi tabel
: Tabel yang berisi data tentang tarif tindakan medik
Nama Field NoSK KodeKlpkTM KodeKlpkTarifTM KodeSM F KodePelayanan UraianTindakan JS JP TarifSendiri
Tipe Field varchar varchar varchar varchar varchar varchar money money money
l. Nama Tabel
Ukuran Field (3) (2) (2) (2) (9) (80)
Keterangan Identitas surat keterangan Identitas kel. Tindakan medik Identitas kel. Tarif tindakan medik Identitas SMF Kode pelayanan medik Deskripsi tentang tindakan
Biaya yang dibayarkan sendiri
: BillingRajalPagiKlpkPasien
Primary Key
: idTran
Deskripsi tabel
: Tabel yang berisi data tentang transaksi pembayaran kelompok pasien di Rawat Jalan
Nama Field
Tipe Field
NoCM NoLaporanBilling TglTransaksi IdTran WaktuCetakStruk NoRegistrasi NoStruk KodePoliklinik KodeDokter kodedokterluarlab kodedokterluarrad NRKKasir KodeJadwalRajal NamaKlpkPasien
varchar varchar datetime Int datetime varchar varchar varchar varchar varchar varchar(5) varchar(5) varchar(4) varchar(30)
Ukuran Field (10) (20)
(10) (9) (30) (5) (5)
L6
Keterangan Identitas Pasien Nomor laporan bill Tanggal saat transaksi Identitas Transaksi Waktu pencetakan struk Identitas registrasi Identitas struk Identitas Pliklinik Identitas dokter Identitas dokter diluar lab Identitas kode luar Identitas Kasir Identitas Jadwal Rawat Jalan Nama pasien per kelompok
PenjaminBayar
varchar
(35)
KodePelayanan UraianTindakan Kuantitas TarifSendiri TarifCito TotalBiaya Piutang SelisihBiaya SisaBiaya IurBiayaSPAskes
varchar varchar int money money money money money money money
(9) (100)
BiayaSpAskesTerpakai
money
Bayar Bebas StatusTanggungan
money money varchar
JS JP JasaDokter
money money
Biaya total Pendapatan yang belum tertagih
Biaya di luar Surat Penjamin Askes Biaya menggunakan Surat Penjamin yang terpakai Jumlah yang dibayarkan (50)
money
IntensifTM RajalDokter
money
IntensifTM RajalPerawat
money
m. Nama Tabel
Status menggunakan tanggungan atau tidak
Tindakan jasa yang dilakukan dokter Tindakan jasa yang dilakukan oleh Asisten Keperawatan Jasa tindakan medic dokter pada rawat jalan Jasa tindakan medic perawat pada rawat jalan
money
JasaAskep
Informasi penjamin yg akan membayar Identitas pelayanan Deskripsi tindakan Jumlah transaksi Biaya yang dibayarkan sendiri
: WilayahTinggalPasien
Primary Key
: Kodepos
Deskripsi tabel
: Tabel yang berisi data tentang Wilayah tingggal pasien
Nama Field NoCM
Tipe Field varchar
Ukuran Field (11) L7
Keterangan Id Kartu Pasien
KodePos KodeKelurahan KodeKecamatan KodeKodyaKab KodePropinsi
varchar varchar varchar varchar varchar
n. Nama Tabel
(5) (5) (5) (5) (2)
: Propinsi
Primary Key
: KodePropinsi
Deskripsi tabel
: Tabel yang berisi data tentan g propinsi
Nama Field
Tipe Field
Ukuran Field
Keterangan
KodePropinsi NamaPropinsi
varchar varchar
(2) (30)
Identitas Propinsi Nama Propinsi
o. Nama Tabel
: Kabupaten
Primary Key
: KodeKabupaten
Deskripsi tabel
: Tabel yang berisi data tentang kabupaten
Nama Field KodePropinsi KodeKabupaten NamaKabupaten
Tipe Field
Ukuran Field
Varchar Varchar Varchar
p. Nama Tabel
(2) (3) (50)
Keterangan No Kode Propinsi No Kode Kabupaten Nama kabupaten
: Kecamatan
Primary Key
: KodeKecamatan
Deskripsi tabel
: Tabel yang berisi data tentang kecamatan
Nama Field KodeKodyaKab KodeKecamatan NamaKecamatan
Tipe Field
Ukuran Field
varchar varchar varchar
(50) (3) (30)
L8
NoKode Pos NoKode Kelurahan NoKode Kecamatan NoKodya Kabupaten NoKode Propinsi
Keterangan No Kodya Kabupaten No Kecamatan Nama Kecamatan
q. Nama Tabel
: Kelurahan
Primary Key
: Kodekelurahan
Deskripsi tabel
: Table yang berisi kumpulan data kelurahan pasien
yang pernha berobat
Nama Field KodeKecamatan KodeKelurahan NamaKelurahan KodePos
Tipe Field
Ukuran Field
varchar varchar varchar varchar
r. Nama Tabel
(3) (5) (40) (5)
Keterangan No Kode Kecamatan No Kode Kelurahan Nama Kelurahan Kode Pos
: StrukBilling
Primary Key
: NoStruk
Deskripsi tabel
: Table yang berisi kumpulan data billing yang telah
diselesaikan oleh pasien.
NoRegistrasi NoStruk TglStruk WaktuStruk GrandTotalBiaya
Tipe Field varchar varchar datetime datetime money
GrandTotalBayar
money
GrandTotalPiutang
money
GrandTotalSisaBiaya GrandTotalBebas SPAskesTerpakai
money money money
Nama Field
Ukuran Field (10) (9)
L9
Keterangan Nomor Pendaftaran Nomer Struk Tanggal penerbitan struk Waktu penerbitan struk Grand total biya yang harus dibayar pasien Grand total uang yang diberikan oleh pasien Total tagihan yang belum diselesaikan atas nama pasien Total sisa biaya yang telah dibayar Total pembebasan biaya Surat Pemotongan asuransi yang digunakan pasien
s. Nama Tabel
: TransaksiPelayananRinap
Primary Key
: IdTran
Deskripsi tabel
: Table yang berisi kumpulan data Transaksi yang
telah dilakukan dan harus diselesaikan oleh pasien
idTran
Tipe Field int
Tglwkttransaksi NoRegistrasi NoTagihan NoKwitansi NRKKasir kodepelayananM edik KodeRuangRawat KodeKelas KodeKamar NoTT NRKDokter NRKPerawat namaklpkpasien PenjaminBayar
datetime varchar varchar varchar varchar varchar varchar varchar varchar varchar varchar varchar varchar varchar
Notransaksi Kodepelayanan Uraiantindakan Kuantitas Tarip SubTotalBiaya SubTotalPiutang SubTotalSelisihBiaya
varchar varchar varchar int money money money money
Nama Field
Ukuran Field
Identitas Transaksi (10) (9) (11) (5) (4) (4) (3) (5) (6) (5) (5) (30) (35) (11) (9) (100)
SubTotalSisaBiaya money SubTotalPotongan real SubNilaiTotalPotongan money SubTotalBebasBiaya money L10
Keterangan
Tanggal saat transaksi Nomor Pendaftaran Nomor Tagihan Nomor struk / Kwitansi Identitas No Kasir Identitas Pelayanan M edik Kode Ruang rawat Kode Kelas ruang perawatan Kode Kamar perawatan Nomer Tempat Tidur Identitas Dokter Identitas Perawat Nama kelompok pasien Informasi penjamin yg akan membayar Nomor Transaksi Identitas Pelayanan Penjelasan tentang tindakan Jumlah transaksi Biaya yang dibayarkan sendiri Total biaya sementara Total biaya yang belum ditagih Total Selisih biaya yang harus dibayar Total biaya yang belum dibayar Total potongan biaya Nilai total potongan biaya Nilai total biaya yang dibebaskan
SubTotalBayar kdjnststariprs kdstsbayarrs kdststanggunganrs ststanggungan Nilaijasasarana Nilaijasapelayanan Nilaijasars Nilaijpmedisdktr Nilaijpaskep Nilaijpprwtan stsbayartagihan ststutuptransaksi statusbataltransaksi tglbataltransaksi
money varchar varchar char varchar money money money money money money bit varchar bit datetime
(50) (50) (2) (50)
(5)
L11
Nilai Total yang harus dibayar Kode jenis tarif yang digunakan Status pembayaran RS Kode Tanggungan RS Status tanggungan RS Harga jasa sarana yang digunakan Harga jas pelayanan yang digunakan Harga jasa Rumah Sakit Harga Pemeriksaan medis Harga Asisten Perawat Harga jasa perawat Status tagihan Status penutupan tagihan Status pembatalan tagihan Tanggal transaksi pembayaran
L2. Lampiran Coding Form Dashboard Imports System.Data.SqlClient Public Class Dashboard Private Sub runsmile() Dim con As New SqlConnection("Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security = True") Dim cmdjln09 As New SqlCommand("select dbo.smilejalan('2009')", con) Dim cmdjl10 As New SqlCommand("select dbo.smilejalan('2010')", con) Dim cmdinap9 As New SqlCommand("select dbo.smileinap('2009')", con) Dim cmdinap10 As New SqlCommand("select dbo.smileinap('2010')", con) Dim cmdigd9 As New SqlCommand("select dbo.smileigd('2009')", con) Dim cmd7i As New SqlCommand("select dbo.smileigd('2010')", con) Try con.Open() labelgoaligd.Text = "TARGET : " & cmdigd9.ExecuteScalar() labelvalueigdd.Text = "REALISASI : " & cmd7i.ExecuteScalar() labelgoalinap.Text = "TARGET : " & cmdinap9.ExecuteScalar() labelvalueinapp.Text = "REALISASI : " & cmdinap10.ExecuteScalar() labelgoaljalan.Text = "TARGET : " & cmdjln09.ExecuteScalar() labelvaluejalann.Text = "REALISASI : " & cmdjl10.ExecuteScalar() con.Close() Catch ex As SqlException End Try End Sub Private Sub RunGauge() Dim con As New SqlConnection("Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security = True") Dim cmd1 As New SqlCommand("select dbo.fngoungeTotaltransaksi('2010')", con) Dim cmd2 As New SqlCommand("select dbo.fngoungeavgRinap('2010')", con) L12
Dim cmd4 As New SqlCommand("select dbo.fnGolrajal('2009','2010')", con) Dim cmd5i As New SqlCommand("select dbo.fngoungeTotaltransaksirajal('2010')", con) Dim cmd6i As New SqlCommand("select dbo.fngoungeavgRajal('2010')", con) Dim cmd7i As New SqlCommand("select dbo.fnGolrajalan('2009','2010')", con) Dim cmd8 As New SqlCommand("select dbo.fngoungeTotaligd('2010')", con) Dim cmd9 As New SqlCommand("select dbo.fngoungeavgigd('2010')", con) Dim cmd9i As New SqlCommand("select dbo.fnGoligd('2009','2010') ", con) Try con.Open() goungetotalinap.Value = cmd1.ExecuteScalar() LabelComponent1.Text = "NILAI : " & cmd1.ExecuteScalar() goungeAvgInap.Value = cmd2.ExecuteScalar() Labelavginap.Text = "NILAI : " & cmd2.ExecuteScalar() StateIndicatorComponent1.StateIndex = cmd4.ExecuteScalar '---------------------gaungetotaljalan.Value = cmd5i.ExecuteScalar() labeltotaljalan.Text = "NILAI : " & cmd5i.ExecuteScalar() gaungeavgjalan.Value = cmd6i.ExecuteScalar() labelavgjalan.Text = "NILAI : " & cmd6i.ExecuteScalar() gaungetotaligd.Value = cmd5i.ExecuteScalar() StateIndicatorComponent4.StateIndex = cmd7i.ExecuteScalar() '--------------------------------------gaungeavgigd.Value = cmd9.ExecuteScalar() labelavgigd.Text = "NILAI: " & cmd9.ExecuteScalar() gaungetotaligd.Value = cmd8.ExecuteScalar() labeltotaligd.Text = "NILAI : " & cmd8.ExecuteScalar() StateIndicatorComponent3.StateIndex = cmd9i.ExecuteScalar() con.Close() Catch ex As SqlException End Try End Sub
L13
'url http://yandinotebook/ReportServer/Pages/ReportViewer.aspx?%2fReport+Proje ct2%2fReport11&rs:Command=Render Private Sub Dashboard_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'OLAP_SKRIPSIDataSet18.toppoli' table. You can move, or remove it, as needed. Me.ToppoliTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet18.toppoli) 'TODO: This line of code loads data into the 'Datsetmonthrajal.monthrajal' table. You can move, or remove it, as needed. Me.MonthrajalTableAdapter1.Fill(Me.Datsetmonthrajal.monthraja l) 'TODO: This line of code loads data into the 'Datsetinapmonth.monthrainap' table. You can move, or remove it, as needed. Me.MonthrainapTableAdapter1.Fill(Me.Datsetinapmonth.monthrain ap) 'TODO: This line of code loads data into the 'OLAP_SKRIPSIDataSet17.top5dokterigd' table. You can move, or remove it, as needed. GetServerPath() RunGauge() runsmile() Me.Top5dokterigdTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet17.to p5dokterigd) 'TODO: This line of code loads data into the 'OLAP_SKRIPSIDataSet16.top5dokterinap' table. You can move, or remove it, as needed. Me.Top5dokterinapTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet16.t op5dokterinap) 'TODO: This line of code loads data into the 'OLAP_SKRIPSIDataSet15.top5dokterrajal' table. You can move, or remove it, as needed. Me.Top5dokterrajalTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet15. top5dokterrajal) 'TODO: This line of code loads data into the 'OLAP_SKRIPSIDataSet14.monthrajal' table. You can move, or remove it, as needed. L14
Me.MonthrajalTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet14.month rajal) 'TODO: This line of code loads data into the 'OLAP_SKRIPSIDataSet13.monthrainap' table. You can move, or remove it, as needed. Me.MonthrainapTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet13.mont hrainap) 'TODO: This line of code loads data into the 'OLAP_SKRIPSIDataSet12.monthigd' table. You can move, or remove it, as needed. Me.MonthigdTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet12.monthig d) 'Me.MonthrainapTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet13.mon thrainap) 'Me.JTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet8.j) 'Me.ITableAdapter.Fill(Me.OLAP_SKRIPSIDataSet7.i) 'Me.TableeTableAdapter.FillBy2(Me.OLAP_SKRIPSIDataSet2.tablee ) Try Me.MonthigdTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet12.monthig d) Catch ex As System.Exception System.Windows.Forms.MessageBox.Show(ex.Message) End Try Try Me.MonthrainapTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet13.mont hrainap) Catch ex As System.Exception System.Windows.Forms.MessageBox.Show(ex.Message) End Try Try Me.MonthrajalTableAdapter.Fill(Me.OLAP_SKRIPSIDataSet14.month rajal) Catch ex As System.Exception System.Windows.Forms.MessageBox.Show(ex.Message) End Try End Sub
L15
Private Sub cmbtotalinap_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbtotalinap.SelectedIndexChanged GetServerPath() Dim con As New SqlConnection("Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security = True") Dim cmd6 As New SqlCommand("select dbo.fngoungeTotaltransaksi('" & cmbtotalinap.Text & "')", con) Try con.Open() goungetotalinap.Value = cmd6.ExecuteScalar() LabelComponent1.Text = "NILAI : " & cmd6.ExecuteScalar() con.Close() Catch ex As SqlException End Try End Sub Private Sub cmbavginap_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbavginap.SelectedIndexChanged GetServerPath() Dim con As New SqlConnection("Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security = True") Dim cmd7 As New SqlCommand("select dbo.fngoungeavgRinap('" & cmbavginap.Text & "')", con) Try con.Open() goungeAvgInap.Value = cmd7.ExecuteScalar() Labelavginap.Text = "value : " & cmd7.ExecuteScalar() con.Close() Catch ex As SqlException End Try End Sub Private Sub cmbtotaljalan_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbtotaljalan.SelectedIndexChanged GetServerPath() Dim con As New SqlConnection("Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security = True")
L16
Dim cmd5i As New SqlCommand("select dbo.fngoungeTotaltransaksirajal('" & cmbtotaljalan.Text & "')", con) Try con.Open() gaungetotaljalan.Value = cmd5i.ExecuteScalar() labeltotaljalan.Text = "NILAI : " & cmd5i.ExecuteScalar() con.Close() Catch ex As SqlException End Try End Sub Private Sub cmbavgjalan_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbavgjalan.SelectedIndexChanged GetServerPath() Dim con As New SqlConnection("Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security = True") Dim cmd6i As New SqlCommand("select dbo.fngoungeavgRajal('" & cmbavgjalan.Text & "')", con) Try con.Open() gaungeavgjalan.Value = cmd6i.ExecuteScalar() labelavgjalan.Text = "NILAI : " & cmd6i.ExecuteScalar() con.Close() Catch ex As SqlException End Try End Sub Private Sub cmbavgigd_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbavgigd.SelectedIndexChanged GetServerPath() Dim con As New SqlConnection("Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security = True") Dim cmd6ii As New SqlCommand(" select dbo.fngoungeavgigd('" & cmbavgigd.Text & "')", con) Try con.Open() gaungeavgigd.Value = cmd6ii.ExecuteScalar() labelavgigd.Text = "NILAI : " & cmd6ii.ExecuteScalar() con.Close() Catch ex As SqlException End Try L17
End Sub Private Sub cmbtotaligd_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbtotaligd.SelectedIndexChanged GetServerPath() Dim con As New SqlConnection("Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security = True") Dim cmd6ai As New SqlCommand("select dbo.fngoungeTotaligd('" & cmbtotaligd.Text & "')", con) Try con.Open() gaungetotaligd.Value = cmd6ai.ExecuteScalar() labeltotaligd.Text = "NILAI : " & cmd6ai.ExecuteScalar() con.Close() Catch ex As SqlException End Try End Sub End Class Form Pivot Rawat Inap Imports System.Globalization Public Class PivotRawatInap Dim culture As CultureInfo = New CultureInfo("id-ID", True) Private Sub ChartControl1_BoundDataChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChartControl1.BoundDataChanged culture.NumberFormat.CurrencySymbol = "Rp." System.Threading.Thread.CurrentThread.CurrentCulture = culture DevExpress.Utils.FormatInfo.AlwaysUseThreadFormat = True fieldTotalPendapataninap.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric fieldTotalPendapataninap.CellFormat.FormatString = "c2" End Sub Private Sub ChartControl1_CustomDrawSeriesPoint(ByVal sender As System.Object, ByVal e As DevExpress.XtraCharts.CustomDrawSeriesPointEventArgs) Handles ChartControl1.CustomDrawSeriesPoint Dim dval As Double = Convert.ToDouble(e.LabelText)
L18
e.LabelText = dval.ToString(fieldTotalPendapataninap.CellFormat.FormatStrin g) End Sub End Class Form Pivot Rawat Jalan Imports Imports Imports Imports
DevExpress.XtraPivotGrid System.Threading.Thread System.Globalization DevExpress.XtraPivotGrid.Data
Public Class PivotRawatJalan Dim culture As New CultureInfo("id-ID", True) Private Sub PivotGridControl3_CustomCellDisplayText(ByVal sender As System.Object, ByVal e As DevExpress.XtraPivotGrid.PivotCellDisplayTextEventArgs) Handles PivotGridControl3.CustomCellDisplayText Dim dVal As Double = 0 If e.Value Is Nothing And e.Value = Double.TryParse(e.Value, dVal) Then Dim currencySymbol As String = System.Globalization.CultureInfo.CurrentCulture.NumberFormat. CurrencySymbol() e.DisplayText = String.Format("{0}0.00", "") End If End Sub Private Sub ChartControl2_BoundDataChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChartControl2.BoundDataChanged Dim culture As CultureInfo = New CultureInfo("id-ID", True) culture.NumberFormat.CurrencySymbol = "Rp." System.Threading.Thread.CurrentThread.CurrentCulture = culture DevExpress.Utils.FormatInfo.AlwaysUseThreadFormat = True fieldTotalPendapataninap.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric fieldTotalPendapataninap.CellFormat.FormatString = "c2" End Sub End Class Form Pivot Igd L19
Imports Imports Imports Imports Imports
DevExpress.XtraCharts DevExpress.XtraPivotGrid.PivotSummaryDataRow DevExpress.Data.PivotGrid DevExpress.Data System.Globalization
Public Class PivotIgd Dim culture As CultureInfo = New CultureInfo("id-ID", True) Private Sub ChartControl2_BoundDataChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChartControl2.BoundDataChanged culture.NumberFormat.CurrencySymbol = "Rp." System.Threading.Thread.CurrentThread.CurrentCulture = culture DevExpress.Utils.FormatInfo.AlwaysUseThreadFormat = True fieldTotalPendapataninap.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric fieldTotalPendapataninap.CellFormat.FormatString = "c2" End Sub Private Sub ChartControl2_CustomDrawSeriesPoint(ByVal sender As System.Object, ByVal e As DevExpress.XtraCharts.CustomDrawSeriesPointEventArgs) Handles ChartControl2.CustomDrawSeriesPoint End Sub End Class SQL FUNCTION --faktarawatjalan select distinct jl.idtran,dtw.DateKey,sp.pasienid,didok.DokterID,pl.poliklini kid,djn.idsmf,jl.Kuantitas,jl.TotalBiaya from BillingRajalPagiKlpkPasien jl,OLAP_SKRIPSI.dbo.Dimpasien sp,RegistrasiRS rgs,OLAP_SKRIPSI.dbo.dimensismf djn ,OLAP_SKRIPSI.dbo.DimensiDokter didok,OLAP_SKRIPSI.dbo.DimDate dtw,OLAP_SKRIPSI.dbo.dimensipoliklinik pl where jl.NoRegistrasi=rgs.NoRegistrasi and jl.KodePoliklinik= pl.KodePoliklinik and djn.kodepelayanan=jl.KodePelayanan and rgs.NoCM=sp.KdPasien collate SQL_LATIN1_GENERAL_CP1_CI_AS L20
and didok.KdDokter collate SQL_LATIN1_GENERAL_CP1_CI_AS =jl.KodeDokter and CONVERT(date,dtw.fulldate,105)=CONVERT(date,jl.TglTransaksi,1 05) order by dtw.DateKey asc --faktarawatinap select distinct dtw.DateKey,dps.PasienID,didok.DokterID,dpo.idlayanan,djn.ids mf,dk.KamarID,trp.Kuantitas,trp.subtotalbiaya from TransaksiPelayananRinap trp,RegistrasiRS rgs,OLAP_SKRIPSI.dbo.dimensipoli dpo, OLAP_SKRIPSI.dbo.DimensiPasien dps,OLAP_SKRIPSI.dbo.DimensiDokter didok, OLAP_SKRIPSI.dbo.dimensismf djn,OLAP_SKRIPSI.dbo.DimDate dtw,OLAP_SKRIPSI.dbo.DimensiKamar dk where trp.NoRegistrasi=rgs.NoRegistrasi and dpo.kodepelayananmedik collate SQL_LATIN1_GENERAL_CP1_CI_AS=trp.kodepelayananMedik and dps.KdPasien collate SQL_LATIN1_GENERAL_CP1_CI_AS = rgs.NoCM and djn.kodepelayanan = trp.Kodepelayanan and dk.kdruangrawat collate SQL_LATIN1_GENERAL_CP1_CI_AS = trp.KodeRuangRawat and dk.KodeKamar collate SQL_LATIN1_GENERAL_CP1_CI_AS = trp.KodeKamar and didok.KdDokter collate SQL_LATIN1_GENERAL_CP1_CI_AS =trp.NRKDokter and CONVERT(date,dtw.fulldate,105)=CONVERT(date,trp.Tglwkttransak si,105) --faktaIGD select distinct dtw.DateKey,dps.PasienID,didok.DokterID,dpo.idlayanan,djn.ids mf,trp.Kuantitas,trp.TotalBiaya from BillingIGDKlpkPasien trp,RegistrasiRS rgs,OLAP_SKRIPSI.dbo.dimensipoli dpo, OLAP_SKRIPSI.dbo.DimPasien dps,OLAP_SKRIPSI.dbo.DimensiDokter didok, OLAP_SKRIPSI.dbo.dimensismf djn,OLAP_SKRIPSI.dbo.DimDate dtw where trp.NoRegistrasi=rgs.NoRegistrasi and dpo.kodepelayananmedik collate SQL_LATIN1_GENERAL_CP1_CI_AS=trp.kodepelayananMedik and dps.KdPasien collate SQL_LATIN1_GENERAL_CP1_CI_AS = rgs.NoCM L21
and djn.kodepelayanan = trp.Kodepelayanan and didok.KdDokter collate SQL_LATIN1_GENERAL_CP1_CI_AS =trp.KodeDokter and CONVERT(date,dtw.fulldate,105)=CONVERT(date,trp.TanggalTransa ksi,105) USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[fngoungeTottransaksirajal] Script Date: 01/21/2011 05:37:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fngoungeTottransaksirajal](@TahunAkhir CHAR(4)) RETURNs numeric --DECIMAL(5,2) AS BEGIN DECLARE @ValueAkhir numeric SET @ValueAkhir = (SELECT SUM(Totaltransaksi) FROM faktaPendapatanrajal WHERE DateKey IN(SELECT DateKey FROM DimDate WHERE [Year] = @TahunAkhir )) RETURN (@ValueAkhir) END GO ----------gounge total inap USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[fngoungeTotaltransaksi] Script Date: 01/21/2011 05:37:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fngoungeTotaltransaksi](@TahunAkhir CHAR(4)) RETURNs numeric --DECIMAL(5,2) AS L22
BEGIN DECLARE @ValueAkhir numeric SET @ValueAkhir = (SELECT SUM(Totaltransaksi) FROM faktaPendapatanRwtinap WHERE DateKey IN(SELECT DateKey FROM DimDate WHERE [Year] = @TahunAkhir ))
RETURN (@ValueAkhir) END --gounge totaligd USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[fngoungeTotaligd] Script Date: 01/21/2011 05:36:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fngoungeTotaligd](@TahunAkhir CHAR(4)) RETURNs numeric --DECIMAL(5,2) AS BEGIN DECLARE @ValueAkhir numeric SET @ValueAkhir = (SELECT SUM(Totaltransaksi) FROM faktapendapatanigdd WHERE DateKey IN(SELECT DateKey FROM DimDate WHERE [Year] = @TahunAkhir )) RETURN (@ValueAkhir) END
--function smile rajal USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[smilejalan] Script Date: 01/21/2011 05:34:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER
FUNCTION [dbo].[smilejalan](@TahunAwal CHAR(4)) L23
RETURNs numeric --DECIMAL(5,2) AS BEGIN DECLARE @ValueAwal numeric SET @ValueAwal = (SELECT SUM(Totaltransaksi) FROM faktaPendapatanrajal WHERE DateKey IN(SELECT DateKey FROM DimDate WHERE [Year] = @TahunAwal and [SemesterNumberOfYear] = 1)) RETURN (@ValueAwal) END GO ---function smile inap USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[smileinap] Script Date: 01/21/2011 05:34:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[smileinap](@TahunAwal CHAR(4)) RETURNs numeric --DECIMAL(5,2) AS BEGIN DECLARE @ValueAwal numeric SET @ValueAwal = (SELECT SUM(Totaltransaksi) FROM faktaPendapatanRwtinap WHERE DateKey IN(SELECT DateKey FROM DimDate WHERE [Year] = @TahunAwal and [SemesterNumberOfYear] = 1)) RETURN (@ValueAwal) END GO ---function smile IGD USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[smileigd] Script Date: 01/21/2011 05:34:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO L24
ALTER FUNCTION [dbo].[smileigd](@TahunAwal CHAR(4)) RETURNs numeric --DECIMAL(5,2) AS BEGIN DECLARE @ValueAwal numeric SET @ValueAwal = (SELECT SUM(Totaltransaksi) FROM faktapendapatanigdd WHERE DateKey IN(SELECT DateKey FROM DimDate WHERE [Year] = @TahunAwal and [SemesterNumberOfYear] = 1)) RETURN (@ValueAwal) END GO USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[fnGolrajalan] Script Date: 01/21/2011 05:49:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fnGolrajalan](@TahunAkhir CHAR(4), @TahunAwal CHAR(4)) RETURNS TINYINT AS BEGIN DECLARE @Penjualan numeric DECLARE @retVal TINYINT SET @Penjualan = (select dbo.fngoungerajal(@TahunAkhir,@TahunAwal)) IF (@Penjualan > 0) SET @retVal = 3 ELSE IF (@Penjualan = 0) SET @retVal = 2 ELSE SET @retVal = 1 RETURN (@retVal) END GO USE [OLAP_SKRIPSI] GO
L25
/****** Object: UserDefinedFunction [dbo].[fnGoligd] Script Date: 01/21/2011 05:49:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fnGoligd](@TahunAkhir CHAR(4), @TahunAwal CHAR(4)) RETURNS TINYINT AS BEGIN DECLARE @Penjualan numeric DECLARE @retVal TINYINT SET @Penjualan = (select dbo.fngoungeigd(@TahunAkhir,@TahunAwal)) IF (@Penjualan > 0) SET @retVal = 3 ELSE IF (@Penjualan = 0) SET @retVal = 2 ELSE SET @retVal = 1 RETURN (@retVal) END GO USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[fnGolrajal] Script Date: 01/21/2011 05:49:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fnGolrajal](@TahunAkhir CHAR(4), @TahunAwal CHAR(4)) RETURNS TINYINT AS BEGIN DECLARE @Penjualan numeric DECLARE @retVal TINYINT
L26
SET @Penjualan = (select dbo.fngoungerajal(@TahunAkhir,@TahunAwal)) IF (@Penjualan > 0) SET @retVal = 3 ELSE IF (@Penjualan = 0) SET @retVal = 2 ELSE SET @retVal = 1 RETURN (@retVal) END GO USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[fngoungeavgRinap] Script Date: 01/21/2011 05:53:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fngoungeavgRinap](@TahunAkhir CHAR(4)) RETURNs numeric --DECIMAL(5,2) AS BEGIN DECLARE @ValueAkhir numeric SET @ValueAkhir = (SELECT AVG(TotalPendapataninap) FROM faktaPendapatanRwtinap WHERE DateKey IN(SELECT DateKey FROM DimDate WHERE [Year] = @TahunAkhir )) RETURN (@ValueAkhir) END GO USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[fngoungeavgRajal] Script Date: 01/21/2011 05:52:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fngoungeavgRajal](@TahunAkhir CHAR(4)) RETURNs numeric --DECIMAL(5,2) AS BEGIN L27
DECLARE @ValueAkhir numeric SET @ValueAkhir = (SELECT AVG(TotalPendapataninap) FROM faktaPendapatanrajal WHERE DateKey IN(SELECT DateKey FROM DimDate WHERE [Year] = @TahunAkhir )) RETURN (@ValueAkhir) END GO USE [OLAP_SKRIPSI] GO /****** Object: UserDefinedFunction [dbo].[fngoungeavgigd] Script Date: 01/21/2011 05:52:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fngoungeavgigd](@TahunAkhir CHAR(4)) RETURNs numeric --DECIMAL(5,2) AS BEGIN DECLARE @ValueAkhir numeric SET @ValueAkhir = (SELECT AVG(TotalPendapataninap) FROM faktapendapatanigdd WHERE DateKey IN(SELECT DateKey FROM DimDate WHERE [Year] = @TahunAkhir ))
RETURN (@ValueAkhir) END GO
L28
