1: <?php
2:
3:
4: /**
5: * mapping.inc.php
6: * <br/> untuk melakukan pemetaan antara basis data Institusi dengan basis data PDDIKTI
7: * <br/> profil https://id.linkedin.com/in/basitadhi
8: * <br/> buat 2015-10-30
9: * <br/> rev 2017-03-01
10: * <br/> sifat open source
11: * <br/> catatan:
12: * <br/> 1. rule nama kolom PDDIKTI:
13: * <br/> --- dengan raw. -> nama kolom akan ditampilkan tanpa alias, contoh: raw.kolom1 akan ditampilkan kolom1
14: * <br/> --- tanpa raw. -> diberikan fungsi trim pada nama kolom, contoh: kolom1 akan ditampilkan trim(kolom1)
15: * <br/> --- dengan alias -> nama kolom akan ditampilkan apa adanya, contoh: p.kolom1 akan ditampilkan p.kolom1
16: * <br/> 2. yang dimaksud tabel institusi dapat berupa tabel, view atau query
17: * <br/> 3. peta yang harus diisi:
18: * <br/> -- a. webservice::cek_tabel()------------ : field, table, pk
19: * <br/> -- b. webservice::pddikti_sinkron_guid()- : guid, pk
20: * <br/> -- c. webservice::pddikti_injek()-------- : field, inject, lihat 2. Sync GUID
21: * <br/> -- d. webservice::pddikti_ekstrak()------ : field, extract
22: * @author Basit Adhi Prabowo, S.T. <basit@unisayogya.ac.id>
23: * @access public
24: * @todo Penanganan data dengan tanda petik, misalnya pada nama
25: */
26: class mapdb
27: {
28: var $peta; /* pemetaaan tabel dan kolom Institusi dengan PDDIKTI */
29: var $isdipetakan=false; /* apakah pemetaan sudah dipetakan? */
30:
31: /**
32: * pemetaan kolom PDDIKTI dan Institusi
33: * <br/> indeks:
34: * <br/> - field
35: * <br/> isi:
36: * <br/> nama_tabel_pddikti1 => array(field1.1_pddikti => field1.1_institusi, field1.2_pddikti => field1.2_institusi, ...),
37: * <br/> nama_tabel_pddikti2 => array(field2.1_pddikti => field2.1_institusi, field2.2_pddikti => field2.2_institusi, ...),
38: * <br/> ...
39: * <br/> catatan:
40: * <br/> - primary key harus di kolom pertama
41: */
42: private function peta_kolom()
43: {
44: $this->peta["field"] = array ( /* tabel Referensi */
45: "agama" => array("id_agama" => "kdagama", "nm_agama" => "agama"),
46: "bentuk_pendidikan" => array("id_bp" => "", "nm_bp" => "", "a_jenj_paud" => "", "a_jenj_tk" => "", "a_jenj_sd" => "", "a_jenj_smp" => "", "a_jenj_sma" => "", "a_jenj_tinggi" => "", "dir_bina" => "", "a_aktif" => ""),
47: "ikatan_kerja_dosen" => array("id_ikatan_kerja" => "", "nm_ikatan_kerja" => "", "ket_ikatan_kerja" => ""),
48: "semester" => array("id_smt" => "kdtahunakademik", "id_thn_ajaran" => "tahunajaran", "nm_smt" => "tahunakademik", "smt" => "semester", "a_periode_aktif" => "isaktif", "tgl_mulai" => "tanggalawal", "tgl_akhir" => "tanggalakhir"),
49: "jurusan" => array("id_jur" => "kodeprodi", "nm_jur" => "namaprodi", "nm_intl_jur" => "", "u_sma" => "", "u_smk" => "", "u_pt" => "", "u_slb" => "", "id_jenj_didik" => "", "id_induk_jurusan" => "", "id_kel_bidang" => ""),
50: "jabfung" => array("id_jabfung" => "kdjafa", "nm_jabfung" => "jafa"),
51: "ikatan_kerja_dosen" => array("id_ikatan_kerja" => "kdikatankerjadosen", "nm_ikatan_kerja" => "ikatankerjadosen", "ket_ikatan_kerja" => ""),
52: "jenis_keluar" => array("id_jenis_keluar" => "kdjeniskeluar", "ket_keluar" => "jeniskeluar", "a_pd" => "", "a_ptk" => ""),
53: "jenjang_pendidikan" => array("id_jenj_didik" => "kdjenjang", "nm_jenj_didik" => "jenjang", "u_jenj_lemb" => "", "u_jenj_org" => ""),
54: "penghasilan" => array("id_penghasilan" => "kdpenghasilan", "nm_penghasilan" => "penghasilan", "batas_bawah" => "", "batas_atas" => ""),
55: "wilayah" => array("id_wil" => "idwil", "nm_wil" => "wil", "asal_wil" => "", "kode_bps" => "", "kode_dagri" => "", "kode_keu" => "", "id_induk_wilayah" => "idindukwil", "id_level_wil" => "level", "id_negara" => ""),
56: "negara" => array("id_negara" => "kdnegara", "nm_negara" => "namanegara", "a_ln" => "isluarnegeri", "benua" => "benua"),
57: "pekerjaan" => array("id_pekerjaan" => "kdpekerjaanpddikti", "nm_pekerjaan" => "pekerjaan"),
58: "jenis_pendaftaran" => array("id_jns_daftar" => "kdjeniskelaspddikti", "nm_jns_daftar" => "jeniskelas"),
59: /* tabel Isian */
60: /* primary key tidak digunakan untuk memasukkan data ke FEEDER PDDIKTI */
61: /* informasi pemetaan dengan tabel institusi ada pada $this->peta["table"] */
62: "mata_kuliah" => array("id_mk" => "", "id_sms" => "", "id_jenj_didik" => "", "kode_mk" => "", "nm_mk" => "", "jns_mk" => "", "kel_mk" => "", "sks_mk" => "", "sks_tm" => "", "sks_prak" => "", "sks_prak_lap" => "", "sks_sim" => "", "metode_pelaksanaan_kuliah" => "", "a_sap" => "", "a_silabus" => "", "a_bahan_ajar" => "", "acara_prak" => "", "a_diktat" => "", "tgl_mulai_efektif" => "", "tgl_akhir_efektif" => ""),
63: "nilai_transfer" => array("id" => "guid", "id_reg_pd" => "guidmahasiswa", "id_mk" => "guidmatakuliah", "kode_mk_asal" => "kodematakuliahasal", "nm_mk_asal" => "matakuliahasal", "sks_asal" => "sksasal", "sks_diakui" => "sks", "nilai_huruf_asal" => "nilaihurufasal", "nilai_huruf_diakui" => "nilai", "nilai_angka_diakui" => "nilaiangka"),
64: "kelas_kuliah" => array("id_kls" => "guid", "id_sms" => "guid_prodi", "id_smt" => "kdtahunakademik", "id_mk" => "guid_matakuliah", "nm_kls" => "kelas", "sks_mk" => "sks"),
65: "mata_kuliah_kurikulum" => array("id" => "guid", "id_kurikulum_sp" => "guid_kurikulum", "id_mk" => "guid_matakuliah", "smt" => "semester", "a_wajib" => "wajib"),
66: "kuliah_mahasiswa" => array("id" => "guid", "id_smt" => "kdtahunakademik", "id_reg_pd" => "guidmahasiswa", "ips" => "ips", "sks_smt" => "sks", "ipk" => "ipk", "sks_total" => "skstotal", "id_stat_mhs" => "kdaktivitasmhs"), /*untuk mahasiswa yang Non Aktif dan Keluar */
67: "mahasiswa_pt keluar" => array("id" => "guidlulus", "id_reg_pd" => "guid", "id_jns_keluar" => "kdaktivitasmhs", "tgl_keluar" => "tglkeluar", "ket" => "keterangan"), /*untuk update data mahasiswa yang Keluar pada tabel mahasiswa */
68: "kuliah_mahasiswa aktif" => array("id" => "guidinsert", "id_smt" => "kdtahunakademik", "id_reg_pd" => "guidmahasiswa", "ips" => "ips", "sks_smt" => "skss", "ipk" => "ipk", "sks_total" => "sks", "id_stat_mhs" => "kdaktivitasmhs"), /*untuk mahasiswa yang Aktif */
69: "kuliah_mahasiswa aktif_update" => array("id" => "guidupdate", "id_smt" => "kdtahunakademik", "id_reg_pd" => "guidmahasiswa", "ips" => "ips", "sks_smt" => "skss", "ipk" => "ipk", "sks_total" => "sks", "id_stat_mhs" => "kdaktivitasmhs"), /*untuk update data mahasiswa yang Aktif */
70: "kuliah_mahasiswa lulus_keaktifan" => array("id" => "guidlulus", "id_smt" => "kdtahunakademik", "id_reg_pd" => "guidmahasiswa", "id_stat_mhs" => "kdaktivitasmhs"), /*untuk update data mahasiswa yang Lulus */
71: "mahasiswa_pt lulus" => array("id" => "guidlulus", "id_reg_pd" => "guid", "id_jns_keluar" => "kdaktivitasmhs", "tgl_keluar" => "tglkeluar", "ket" => "keterangan", "jalur_skripsi" => "jalurskripsi", "judul_skripsi" => "judulkaryatulis", "sk_yudisium" => "nosk", "tgl_sk_yudisium" => "tglsk", "ipk" => "ipk", "no_seri_ijazah" => "noijazah"), /*untuk update data mahasiswa yang Lulus pada tabel mahasiswa */
72: "dosen_pembimbing" => array("id" => "guid", "id_ptk" => "guiddosen", "id_reg_pd" => "guidmahasiswa", "urutan_promotor" => "nourut"),
73: "mahasiswa" => array("id" => "guid", "nm_pd" => "namalengkap", "jk" => "jeniskelamin", "nik" => "nik", "tmpt_lahir" => "tempatlahir", "tgl_lahir" => "tanggallahir", "a_terima_kps" => "statuskps", "id_kk" => "kdkebutuhankhusus", "id_agama" => "kdagama", "jln" => "alamatlengkap", "rt" => "rt", "rw" => "rw", "nm_dsn" => "dusun", "ds_kel" => "kelurahan", "id_wil" => "kodekecpddikti", "kode_pos" => "kodepos", "no_tel_rmh" => "notelpon", "email" => "email", "nm_ayah" => "namaayah", "tgl_lahir_ayah" => "tgllahirayah", "id_jenjang_pendidikan_ayah" => "kdpendidikanayah", "id_pekerjaan_ayah" => "kdpekerjaan", "id_penghasilan_ayah" => "kdpenghasilanayah", "nm_ibu_kandung" => "namaibu", "tgl_lahir_ibu" => "tgllahiribu", "id_jenjang_pendidikan_ibu" => "kdpendidikanibu", "id_pekerjaan_ibu" => "kdpekerjaanibu", "id_penghasilan_ibu" => "kdpenghasilanibu", "kewarganegaraan" => "kdkewarganegaraan", "id_kebutuhan_khusus_ayah" => "idkebutuhankhususayah", "id_kebutuhan_khusus_ibu" => "idkebutuhankhususibu", "nik_ayah" => "nikayah", "nik_ibu" => "nikibu", "npwp" => "npwp"),
74: "mahasiswa_pt" => array("id" => "guid", "id_sms" => "guidprodi", "id_pd" => "guidmahasiswa", "id_sp" => "guidinstitusi", "id_jns_daftar" => "kdprogkul", "nipd" => "nim", "tgl_masuk_sp" => "tglawalkuliah", "a_pernah_paud" => "ispernahpaud", "a_pernah_tk" => "ispernahtk", "mulai_smt" => "mulaisemester", "sks_diakui" => "sksdiakui", "id_jalur_masuk" => "jalurmasuk", "id_pt_asal" => "kdptasal", "id_prodi_asal" => "kdprodiasal", "no_peserta_ujian" => "nopesertaujian"),
75: "nilai krs" => array("id" => "guidkrs", "id_kls" => "guidpenawaran", "id_reg_pd" => "guidmahasiswa", "nilai_angka" => "nilairiil", "nilai_huruf" => "nilai", "nilai_indeks" => "nilaiangka"),
76: "nilai update" => array("id" => "guidnilai", "id_kls" => "guidpenawaran", "id_reg_pd" => "guidmahasiswa", "nilai_angka" => "nilairiil", "nilai_huruf" => "nilai", "nilai_indeks" => "nilaiangka"),
77: "ajar_dosen" => array("id" => "tt.guid", "id_reg_ptk" => "id_reg_ptk", "id_kls" => "guidkelas", "jml_tm_renc" => "rencana", "jml_tm_real" => "realisasi", "id_jns_eval" => "jeniseval", "sks_subst_tot" => "skssubsttot"),
78: "mahasiswa_pt updatedata" => array("id" => "null", "id_reg_pd" => "guid", "id_sms" => "guidprodi", "id_pd" => "guidmahasiswa", "id_sp" => "guidinstitusi", "id_jns_daftar" => "kdprogkul", "nipd" => "nim", "tgl_masuk_sp" => "tglawalkuliah", "a_pernah_paud" => "ispernahpaud", "a_pernah_tk" => "ispernahtk", "mulai_smt" => "mulaisemester", "sks_diakui" => "sksdiakui", "id_jalur_masuk" => "jalurmasuk", "id_pt_asal" => "kdptasal", "id_prodi_asal" => "kdprodiasal", "no_peserta_ujian" => "nopesertaujian"),
79: "mahasiswa updatedata" => array("id" => "null", "id_pd" => "guid", "nm_pd" => "namalengkap", "jk" => "jeniskelamin", "nik" => "nik", "a_terima_kps" => "statuskps", "id_kk" => "kdkebutuhankhusus", "id_agama" => "kdagama", "jln" => "alamatlengkap", "rt" => "rt", "rw" => "rw", "nm_dsn" => "dusun", "ds_kel" => "kelurahan", "id_wil" => "kodekecpddikti", "kode_pos" => "kodepos", "no_tel_rmh" => "notelpon", "email" => "email", "nm_ayah" => "namaayah", "tgl_lahir_ayah" => "tgllahirayah", "id_jenjang_pendidikan_ayah" => "kdpendidikanayah", "id_pekerjaan_ayah" => "kdpekerjaan", "id_penghasilan_ayah" => "kdpenghasilanayah", "tgl_lahir_ibu" => "tgllahiribu", "id_jenjang_pendidikan_ibu" => "kdpendidikanibu", "id_pekerjaan_ibu" => "kdpekerjaanibu", "id_penghasilan_ibu" => "kdpenghasilanibu", "kewarganegaraan" => "kdkewarganegaraan", "id_kebutuhan_khusus_ayah" => "idkebutuhankhususayah", "id_kebutuhan_khusus_ibu" => "idkebutuhankhususibu", "nik_ayah" => "nikayah", "nik_ibu" => "nikibu", "npwp" => "npwp"),
80: "dosen_pt" => array("id" => "null", "id_reg_ptk" => "id_reg_ptk", "id_sdm" => "id_sdm", "id_thn_ajaran" => "tahun")
81: );
82: }
83:
84: /**
85: * pemetaan tabel PDDIKTI dan Institusi
86: * <br/> indeks:
87: * <br/> - table
88: * <br/> isi:
89: * <br/> nama_tabel_pddikti1 => array("nama" => nama_tabel_institusi1, "filter" => filter_tabel_institusi1),
90: * <br/> - atau -
91: * <br/> nama_tabel_pddikti2 => array("nama" => "", "filter" => "", "data" => array(indeks2.1=>data2.1, indeks2.2=>data2.2, ...)),
92: * <br/> ...
93: * <br/> dimana:
94: * <br/> - nama-- : nama tabel institusi
95: * <br/> - filter : filter data untuk tabel institusi
96: * <br/> - data-- : OPSIONAL - membuat data sendiri, tidak mengambil dari tabel. jika diisi, maka "nama" dan "filter" akan diabaikan. berupa array()
97: */
98: private function peta_tabel()
99: {
100: $this->peta["table"] = array ( /* tabel Referensi */
101: "agama" => array("nama" => "pt_agama", "filter" => ""),
102: "semester" => array("nama" => "pddikti_v_tahunakademik", "filter" => ""),
103: "jurusan" => array("nama" => "ak_programstudi", "filter" => ""),
104: "jabfung" => array("nama" => "pt_jafa", "filter" => ""),
105: "ikatan_kerja_dosen" => array("nama" => "ak_ikatankerjadosen", "filter" => ""),
106: "jenis_keluar" => array("nama" => "ak_jeniskeluar", "filter" => ""),
107: "jenjang_pendidikan" => array("nama" => "pt_jenjangpendidikan", "filter" => ""),
108: "penghasilan" => array("nama" => "ak_penghasilan", "filter" => ""),
109: "wilayah" => array("nama" => "pt_v_propinsikabupatenkecamatan", "filter" => ""),
110: "negara" => array("nama" => "pt_negara", "filter" => ""),
111: "pekerjaan" => array("nama" => "pt_pekerjaan", "filter" => ""),
112: "jenis_pendaftaran" => array("nama" => "ak_jeniskelas", "filter" => ""),
113: /* tabel Referensi yang tidak ada di WebService */
114: "kel_mk" => array("nama" => "", "filter" => "", "data" => array("A" => "MPK", "B" => "MKK", "C" => "MKB", "D" => "MPB", "E" => "MBB", "F" => "MKU/MKDU", "G" => "MKDK", "H" => "MKK")),
115: "jns_mk" => array("nama" => "", "filter" => "", "data" => array("A" => "Wajib", "B" => "Pilihan", "C" => "Wajib Peminatan", "D" => "Pilihan Peminatan", "S" => "Skripsi/Tugas Akhir"))
116: /* tabel Isian tidak diisikan di sini */
117: );
118: }
119:
120: /**
121: * pemetaan untuk memasukkan data dari Institusi ke PDDIKTI
122: * <br/> indeks:
123: * <br/> - inject
124: * <br/> isi:
125: * <br/> nama_tabel_pddikti1 => array("table" => nama_tabel_institusi1, "filter" => filter_tabel_institusi1, "type" => type1, "ignoreinject" => ignoreinject1, "tahunakademik" => tahunakademik_tabel_institusi1, "tandatahunakademik" => tandatahunakademik_tabel_institusi1, "istahunakademikkrs" => istahunakademikkrs_tabel_institusi1, "fieldupdate" => array(fieldupdate_tabel_pddikti1, ...), "fieldwhere" => array(fieldwhere_tabel_pddikti1, ...), "jenisfilter" => jenisfilter1),
126: * <br/> nama_tabel_pddikti2 => array("table" => nama_tabel_institusi2, "filter" => filter_tabel_institusi2, "type" => type2, "ignoreinject" => ignoreinject2, "tahunakademik" => tahunakademik_tabel_institusi2, "tandatahunakademik" => tandatahunakademik_tabel_institusi2, "istahunakademikkrs" => istahunakademikkrs_tabel_institusi2, "fieldupdate" => array(fieldupdate_tabel_pddikti2, ...), "fieldwhere" => array(fieldwhere_tabel_pddikti2, ...), "jenisfilter" => jenisfilter2),
127: * <br/> ...
128: * <br/> dimana:
129: * <br/> table-------------- : nama tabel institusi
130: * <br/> filter------------- : filter data untuk tabel institusi
131: * <br/> type--------------- : insert (memasukkan data ke PDDIKTI) atau update (memperbarui data yang ada di PDDIKTI)
132: * <br/> ignoreinject------- : true (diabaikan ketika injeksi massal) atau false (dijalankan ketika injeksi massal)
133: * <br/> tahunakademik------ : OPSIONAL - tahun akademik dari data yang akan diambil
134: * <br/> tandatahunakademik- : OPSIONAL - =, <, >, <=, atau >=
135: * <br/> istahunakademikkrs- : OPSIONAL - tahunakademikkrs adalah tahun akademik aktif, di mana pada saat itu mahasiswa melakukan KRS. true (tahun akademik tidak berubah) atau false (tahun akademik menjadi tahun akademik sebelumnya)
136: * <br/> fieldupdate-------- : OPSIONAL - digunakan ketika type="update", kolom-kolom di PDDIKTI yang akan diubah. berupa array()
137: * <br/> fieldwhere--------- : OPSIONAL - digunakan ketika type="update", filter data di PDDIKTI yang akan diubah. berupa array()
138: * <br/> jenisfilter-------- : OPSIONAL - internalfilter (mengganti [internalfilter] dengan "filter") atau string kosong
139: * <br/> catatan:
140: * <br/> - format nama tabel pddikti: [nama_tabel_pddikti] atau [nama_tabel_pddikti]<spasi>[keterangan]
141: * <br/> - Ingat! indeks "tahunakademik" pada "inject" harus bisa di-query-kan di this->peta["table"] pada this->peta["guid"], perhatikan alias tabel
142: * <br/> - "id" pada this->peta["field"] otomatis dibuat isnull()
143: */
144: private function peta_injek()
145: {
146: $this->peta["inject"] = array ( // tipe insert: table, filter, tahunakademik, tandatahunakademik, istahunakademikkrs, type=insert
147: /*"mata_kuliah_kurikulum" => array( "table" => "pddikti_v_matakuliahkurikulum mk",
148: "filter" => "not isnull(guid_kurikulum) and not isnull(guid_matakuliah)",
149: "type" => "insert",
150: "ignoreinject" => false
151: ),
152: "kelas_kuliah" => array( "table" => "pddikti_v_penawaranmatakuliah pm",
153: "filter" => "not isnull(guid_matakuliah)",
154: "tahunakademik" => "pm.kdtahunakademik",
155: "tandatahunakademik" => "=",
156: "istahunakademikkrs" => true,
157: "type" => "insert",
158: "ignoreinject" => false
159: ),
160: "mahasiswa" => array( "table" => "pddikti_v_mahasiswa p",
161: "filter" => "isnull(guid) and p.isignore=0",
162: "tahunakademik" => "p.kdtamasuk",
163: "tandatahunakademik" => "=",
164: "istahunakademikkrs" => true,
165: "type" => "insert",
166: "ignoreinject" => false
167: ),
168: "mahasiswa_pt" => array( "table" => "pddikti_v_mahasiswa_pt m",
169: "filter" => "isnull(guid) and not isnull(guidmahasiswa) and m.isignore=0",
170: "tahunakademik" => "m.kdtamasuk",
171: "tandatahunakademik" => "=",
172: "istahunakademikkrs" => true,
173: "type" => "insert",
174: "ignoreinject" => false
175: ),
176: "nilai_transfer" => array( "table" => "pddikti_v_nilai_transfer nt",
177: "filter" => "isnull(guid) and nt.isignoremahasiswa=0 and nt.isignorematakuliah=0 and not isnull(guidmahasiswa) and not isnull(guidmatakuliah)",
178: "tandatahunakademik" => "=",
179: "istahunakademikkrs" => true,
180: "type" => "insert",
181: "ignoreinject" => false
182: ),
183: "nilai krs" => array( "table" => "pddikti_v_krs k", //krs
184: "filter" => "k.isignoremahasiswa=0 and k.isignorepenawaran=0 and not isnull(guidmahasiswa) and not isnull(guidpenawaran)",
185: "tahunakademik" => "k.kdtahunakademik",
186: "tandatahunakademik" => "=",
187: "istahunakademikkrs" => true,
188: "type" => "insert",
189: "ignoreinject" => false
190: ),
191: // tipe update: table, filter, tahunakademik, tandatahunakademik, istahunakademikkrs, type=update, fieldupdate
192: "nilai update" => array( "table" => "select sql_cache k.kdkrsnilai AS kdkrsnilai, m.isignore AS isignoremahasiswa, p.isignore AS isignorepenawaran, k.kdtahunakademik AS kdtahunakademik, k.guidnilai AS guid, k.guidkrs AS guidkrs, p.guid AS guidpenawaran, m.guid AS guidmahasiswa, 9 AS asaldata, NULL AS nilairiil, k.nilai AS nilai, k.nilaiangka AS nilaiangka, nim, kodematakuliah from ak_krsnilai_nonremidial k join ak_mahasiswa m ON ((m.kdmahasiswa = k.kdmahasiswa)) join ak_penawaranmatakuliah p ON ((p.kdpenawaran = k.kdpenawaran)) where [internalfilter]",
193: "filter" => "(k.nilai <= 'E' or k.nilai = 'T') and not isnull(guidkrs)",
194: "jenisfilter" => "internalfilter",
195: "tahunakademik" => "k.kdtahunakademik",
196: "tandatahunakademik" => "=",
197: "istahunakademikkrs" => false,
198: "type" => "update",
199: "fieldupdate" => array("nilai_angka", "nilai_huruf", "nilai_indeks"),
200: "fieldwhere" => array("id_kls","id_reg_pd"),
201: "ignoreinject" => false
202: ),/*
203: "ajar_dosen" => array( "table" => "select sql_cache kdtimteaching, guid, guidkelas, id_reg_ptk, ceil(sum(rencana)) as rencana, ceil(sum(realisasi)) as realisasi, jeniseval, skssubsttot
204: * from
205: * (
206: * (select tt.kdtimteaching, tt.guid, pm.guid as guidkelas, id_reg_ptk, sum(bebansksepsbed)*7 as rencana, sum(bebansksepsbed)*7 as realisasi, 1 as jeniseval, 0 as skssubsttot, tt.kdpenawaran, tt.kdpersonepsbed from ak_jadwalkuliah jk join ak_timteaching tt on tt.kdtimteaching=ifnull(jk.kdtimteachingperubahan, jk.kdtimteaching) join ak_penawaranmatakuliah pm on pm.kdpenawaran=tt.kdpenawaran join pt_person p on p.kdperson=tt.kdpersonepsbed join ak_penugasan pn on (pn.id_sdm=p.guiddosen and pn.tahun=floor(jk.kdtahunakademik/10)) where [internalfilter] group by tt.kdpenawaran, tt.kdpersonepsbed)
207: union all
208: (select tt.kdtimteaching, tt.guid, pm.guid as guidkelas, id_reg_ptk, sum(bebansksepsbed)*7 as rencana, sum(bebansksepsbed)*7 as realisasi, 1 as jeniseval, 0 as skssubsttot, kl.kdpenawaran, tt.kdpersonepsbed from ak_jadwalkuliah_lab jk join ak_timteaching_lab tt on tt.kdtimteaching=ifnull(jk.kdtimteachingperubahan, jk.kdtimteaching) join ak_kelompok kl on kl.kdkelompok=tt.kdkelompok join ak_penawaranmatakuliah pm on pm.kdpenawaran=kl.kdpenawaran join pt_person p on p.kdperson=tt.kdpersonepsbed join ak_penugasan pn on (pn.id_sdm=p.guiddosen and pn.tahun=floor(jk.kdtahunakademik/10)) where [internalfilter] group by kl.kdpenawaran, tt.kdpersonepsbed)
209: * ) ajar_dosen
210: * group by kdpenawaran, kdpersonepsbed",
211: "filter" => "tt.isignore=0 and pm.isignore=0 and isrealisasi=1",
212: "jenisfilter" => "internalfilter",
213: "tahunakademik" => "jk.kdtahunakademik,tt.kdtahunakademik,pm.kdtahunakademik",
214: "tandatahunakademik" => "=",
215: "istahunakademikkrs" => false,
216: "type" => "insert",
217: "ignoreinject" => false
218: ),*//*
219: "kuliah_mahasiswa" => array( "table" => "pddikti_v_kuliah_mahasiswa c",
220: "filter" => "isnull(c.guid) and not isnull(c.guidmahasiswa) and c.isignore=0 and c.isignoremahasiswa=0 and c.kdaktivitasmhs not in('L', 'A')",
221: "tahunakademik" => "c.kdtahunakademik",
222: "tandatahunakademik" => "=",
223: "istahunakademikkrs" => true,
224: "type" => "insert",
225: "ignoreinject" => false
226: ),
227: "mahasiswa_pt keluar" => array( "table" => "pddikti_v_keluar_mahasiswa m",
228: "filter" => "isignore=0",
229: "tahunakademik" => "kdtahunakademik",
230: "tandatahunakademik" => "=",
231: "istahunakademikkrs" => true,
232: "type" => "update",
233: "fieldupdate" => array("id_jns_keluar","tgl_keluar","ket"),
234: "fieldwhere" => array("id_reg_pd"),
235: "ignoreinject" => false
236: ),
237: "kuliah_mahasiswa aktif" => array( "table" => "select sql_cache kdrekapipk, ri.kdtahunakademik, m.guid as guidmahasiswa, ips, skss, ipk, sks, kdaktivitasmhs, guidinsert, guidupdate from (select distinct kdmahasiswa, kdtahunakademik, 'A' as kdaktivitasmhs from ak_krsnilai) krs join ak_rekap_ipk ri on (ri.kdmahasiswa=krs.kdmahasiswa and ri.kdtahunakademik=krs.kdtahunakademik) join ak_mahasiswa m on m.kdmahasiswa=ri.kdmahasiswa where [internalfilter]",
238: // "filter" => "isnull(guidinsert) and m.isignore=0 and (isnull(kdyudisium) or kdyudisium=0 or kdyudisium='')",
239: "filter" => "isnull(guidinsert) and m.isignore=0", //--> mahasiswa lulus itu harus aktif terlebih dahulu, kemudian baru statusnya berubah menjadi lulus.. meskipun data pernah masuk tidak masalah, nanti akan tertolak, daripada data tidak masuk
240: "jenisfilter" => "internalfilter",
241: "tahunakademik" => "ri.kdtahunakademik",
242: "tandatahunakademik" => "=",
243: "istahunakademikkrs" => true,
244: "type" => "insert",
245: "ignoreinject" => false
246: ),
247: "kuliah_mahasiswa aktif_update" => array( "table" => "select sql_cache kdrekapipk, ri.kdtahunakademik, m.guid as guidmahasiswa, ips, skss, ipk, sks, kdaktivitasmhs, guidinsert, guidupdate from (select distinct kdmahasiswa, kdtahunakademik, 'A' as kdaktivitasmhs from ak_krsnilai) krs join ak_rekap_ipk ri on (ri.kdmahasiswa=krs.kdmahasiswa and ri.kdtahunakademik=krs.kdtahunakademik) join ak_mahasiswa m on m.kdmahasiswa=ri.kdmahasiswa where [internalfilter]",
248: "filter" => "m.isignore=0 and (isnull(kdyudisium) or kdyudisium=0 or kdyudisium='')",
249: "jenisfilter" => "internalfilter",
250: "tahunakademik" => "ri.kdtahunakademik",
251: "tandatahunakademik" => "=",
252: "istahunakademikkrs" => false,
253: "type" => "update",
254: "fieldupdate" => array("ips", "sks_smt", "ipk", "sks_total"),
255: "fieldwhere" => array("id_smt","id_reg_pd","id_stat_mhs"),
256: "ignoreinject" => false
257: ),
258: "kuliah_mahasiswa lulus_keaktifan" => array( "table" => "pddikti_v_lulus_keaktifan y",
259: "filter" => "y.isignore=0",
260: "tahunakademik" => "y.kdtahunakademik",
261: "tandatahunakademik" => "=",
262: "istahunakademikkrs" => false,
263: "type" => "update",
264: "fieldupdate" => array("id_stat_mhs"),
265: "fieldwhere" => array("id_smt","id_reg_pd"),
266: "ignoreinject" => false
267: ),
268: "mahasiswa_pt lulus" => array( "table" => "pddikti_v_lulus_mahasiswa m",
269: "filter" => "isignore=0",
270: "tahunakademik" => "m.kdtahunakademik",
271: "tandatahunakademik" => "=",
272: "istahunakademikkrs" => false,
273: "type" => "update",
274: "fieldupdate" => array("id_jns_keluar","tgl_keluar","ket","sk_yudisium","tgl_sk_yudisium","ipk","no_seri_ijazah","jalur_skripsi","judul_skripsi"),
275: "fieldwhere" => array("id_reg_pd"),
276: "ignoreinject" => false
277: ),
278: "dosen_pembimbing" => array( "table" => "pddikti_v_dosen_pembimbing p",
279: "filter" => "isignore=0",
280: "tahunakademik" => "kdtahunakademik",
281: "tandatahunakademik" => "=",
282: "istahunakademikkrs" => false,
283: "type" => "insert",
284: "ignoreinject" => false
285: ),*/
286: "mahasiswa_pt updatedata" => array( "table" => "pddikti_v_mahasiswa_pt m",
287: "filter" => "m.isignore=0",
288: "tahunakademik" => "m.kdtamasuk",
289: "tandatahunakademik" => "=",
290: "istahunakademikkrs" => true,
291: "type" => "update",
292: "fieldupdate" => array("mulai_smt", "sks_diakui"),
293: "fieldwhere" => array("id_reg_pd"),
294: "ignoreinject" => true
295: ),
296: "mahasiswa updatedata" => array( "table" => "pddikti_v_mahasiswa p",
297: "filter" => "p.isignore=0",
298: "tahunakademik" => "p.kdtamasuk",
299: "tandatahunakademik" => "=",
300: "istahunakademikkrs" => true,
301: "type" => "update",
302: "fieldupdate" => array("jk","nik","a_terima_kps","id_kk","id_agama","jln","rt","rw","nm_dsn","ds_kel","id_wil","kode_pos","telepon_rumah","email","nm_ayah","tgl_lahir_ayah", "id_jenjang_pendidikan_ayah","id_pekerjaan_ayah","id_penghasilan_ayah","tgl_lahir_ibu","id_jenjang_pendidikan_ibu","id_pekerjaan_ibu","id_penghasilan_ibu","kewarganegaraan","id_kebutuhan_khusus_ayah", "id_kebutuhan_khusus_ibu"),
303: "infotambahanerror" => "namalengkap",
304: "fieldwhere" => array("id_pd"),
305: "ignoreinject" => true
306: )
307: );
308: }
309:
310: /**
311: * pemetaan untuk mengambil data dari PDDIKTI ke Institusi
312: * <br/> indeks:
313: * <br/> - extract
314: * <br/> isi:
315: * <br/> nama_tabel_pddikti1 => array("table" => nama_tabel_institusi1, "uniquefield" => uniquefield_tabel_pddikti1, "istahunakademikkrs" => istahunakademikkrs1, "filtertahunakademik" => filtertahunakademik_tabel_pddikti1),
316: * <br/> nama_tabel_pddikti2 => array("table" => nama_tabel_institusi2, "uniquefield" => uniquefield_tabel_pddikti2, "istahunakademikkrs" => istahunakademikkrs2, "filtertahunakademik" => filtertahunakademik_tabel_pddikti2),
317: * <br/> ...
318: * <br/> dimana:
319: * <br/> table--------------- : nama tabel institusi
320: * <br/> uniquefield--------- : kolom-kolom sebagai parameter data akan diambil dari PDDIKTI ke Institusi
321: * <br/> istahunakademikkrs-- : OPSIONAL - tahunakademikkrs adalah tahun akademik aktif, di mana pada saat itu mahasiswa melakukan KRS. true (tahun akademik tidak berubah) atau false (tahun akademik menjadi tahun akademik sebelumnya)
322: * <br/> filtertahunakademik- : filter data untuk tabel PDDIKTI berdasarkan tahun akademik; menggunakan [tahunakademik], atau [tahun]
323: */
324: private function peta_ekstrak()
325: {
326: $this->peta["extract"] = array ( "dosen_pt" => array( "table" => "ak_penugasan",
327: "uniquefield" => array("id_sdm,id_reg_ptk","id_sdm,id_reg_ptk"),
328: "istahunakademikkrs" => false,
329: "filtertahunakademik" => "t.id_thn_ajaran=[tahun]"
330: )
331: );
332: }
333:
334: /**
335: * pemetaan kunci primer PDDIKTI dan Institusi.
336: * <br/> indeks:
337: * <br/> - pk
338: * <br/> isi:
339: * <br/> nama_tabel_pddikti1 => array(kunciprimer1_pddikti => kunciprimer1_institusi),
340: * <br/> nama_tabel_pddikti2 => array(kunciprimer2_pddikti => kunciprimer2_institusi),
341: * <br/> ...
342: * <br/> catatan:
343: * <br/> - semua tabel di FEEDER PDDIKTI dengan tipe REF harus dimasukkan, tampilkan dengan $ws->ListTable()
344: */
345: private function peta_pk()
346: {
347: $this->peta["pk"] = array ( /* tabel Referensi */
348: "agama" => array("id_agama", "kdagama"),
349: "bentuk_pendidikan" => array("id_bp", ""),
350: "ikatan_kerja_dosen" => array("id_ikatan_kerja", "kdikatankerjadosen"),
351: "jabfung" => array("id_jabfung", "kdjafa"),
352: "jenis_evaluasi" => array("id_jns_eval", ""),
353: "jenis_keluar" => array("id_jns_keluar", "kdjeniskeluar"),
354: "jenis_sert" => array("id_jns_sert", ""),
355: "jenis_sms" => array("id_jns_sms", ""),
356: "jenis_subst" => array("id_jns_subst", ""),
357: "jenjang_pendidikan" => array("id_jenj_didik", "kdjenjang"),
358: "jurusan" => array("id_jur", "kodeprodi"),
359: "kebutuhan_khusus" => array("id_kk", ""),
360: "lembaga_pengangkat" => array("id_lemb_angkat", ""),
361: "level_wilayah" => array("id_level_wil", ""),
362: "negara" => array("id_negara", "kdnegara"),
363: "pangkat_gol" => array("id_pangkat_gol", ""),
364: "pekerjaan" => array("id_pekerjaan", "kdpekerjaan"),
365: "jenis_pendaftaran" => array("id_jns_daftar", "kdjeniskelaspddikti"),
366: "penghasilan" => array("id_penghasilan", "kdpenghasilan"),
367: "semester" => array("id_smt", "kdtahunakademik"),
368: "status_keaktifan_pegawai" => array("id_stat_aktif", ""),
369: "status_kepegawaian" => array("id_stat_pegawai", ""),
370: "status_mahasiswa" => array("id_stat_mhs", ""),
371: "wilayah" => array("id_wil", "idwil"),
372: "tahun_ajaran" => array("id_thn_ajaran", ""),
373: /* tabel Isian hanya mengisi PK untuk institusi */
374: "satuan_pendidikan" => array("", "idkonfigurasi"),
375: "sms" => array("", "kodeprodi"),
376: "kurikulum" => array("", "kdkurikulum"),
377: "mata_kuliah" => array("", "kdmatakuliah"),
378: "mahasiswa" => array("id", "p.kdperson"),
379: "mahasiswa_pt" => array("id", "kdmahasiswa"),
380: "dosen" => array("", "p.kdperson"),
381: "kelas_kuliah" => array("id_kls", "kdpenawaran"),
382: "kuliah_mahasiswa" => array("id", "kdmhsckd"),
383: "kuliah_mahasiswa aktif" => array("id", "kdrekapipk"),
384: "kuliah_mahasiswa aktif_update" => array("id", "kdrekapipk"),
385: "kuliah_mahasiswa lulus_keaktifan" => array("id", "kdrekapipk"),
386: "mahasiswa_pt lulus" => array("id", "m.kdmahasiswa"),
387: "mahasiswa_pt keluar" => array("id", "m.kdmahasiswa"),
388: "nilai_transfer" => array("id", "kdkrsnilai"),
389: "mata_kuliah_kurikulum" => array("id", "kdmatakuliah"),
390: "nilai krs" => array("id", "kdkrsnilai"),
391: "nilai update" => array("id", "kdkrsnilai"),
392: "ajar_dosen" => array("id", "kdtimteaching"),
393: "dosen_pembimbing" => array("id", "kdkaryatulispenguji"),
394: "mahasiswa_pt updatedata" => array("id", "null"),
395: "mahasiswa updatedata" => array("id", "null")
396: );
397: }
398:
399: /**
400: * pemetaan guid PDDIKTI dan Institusi
401: * <br/> indeks:
402: * <br/> - guid
403: * <br/> isi:
404: * <br/> nama_tabel_pddikti1 => array("guid" => array(field_guid_pddikti1, field_guid_institusi1), "variable" => array(field_pddikti1, field_institusi1), "table" => array("check" => tabel_institusi_untuk_cek1, "update" => tabel_institusi_untuk_update1), "prerequisite" => parameter_yang_harus_ada_pada_tabel_pddikti1, "filter" => filter_tabel_institusi1, "infotambahanerror" => field_tambahan_ketika_error1, "order by" => parameter_pengurutan1, "forcedouble" => perilaku_ketika_ada_data_rangkap1),
405: * <br/> nama_tabel_pddikti2 => array("guid" => array(field_guid_pddikti2, field_guid_institusi2), "variable" => array(field_pddikti2, field_institusi2), "table" => array("check" => tabel_institusi_untuk_cek2, "update" => tabel_institusi_untuk_update2), "prerequisite" => parameter_yang_harus_ada_pada_tabel_pddikti2, "filter" => filter_tabel_institusi2, "infotambahanerror" => field_tambahan_ketika_error2, "order by" => parameter_pengurutan2, "forcedouble" => perilaku_ketika_ada_data_rangkap2),
406: * <br/> ...
407: * <br/> dimana:
408: * <br/> guid--------------- : nama kolom PDDIKTI yang terdapat kunci primer untuk PDDIKTI dan Institusi
409: * <br/> variable----------- : kolom yang digunakan untuk menyamakan data
410: * <br/> table-------------- : tabel yang dicek di institusi, tabel yang diupdate di institusi
411: * <br/> prerequisite------- : kolom yang PDDIKTI yang harus ada isinya
412: * <br/> filter------------- : filter untuk tabel institusi
413: * <br/> infotambahanerror-- : info tambahan ketika ada kesalahan
414: * <br/> order by----------- : parameter pengurutan tabel institusi
415: * <br/> forcedouble-------- : perilaku ketika ada data rangkap
416: */
417: private function peta_guid()
418: {
419: $this->peta["guid"] = array ( /* tabel Referensi tidak memerlukan mapping ini */
420: "satuan_pendidikan" => array( "guid" => array("id_sp", "k.guid"),
421: "variable" => array("npsn", "k.kodept"),
422: "table" => array("check" => "konfigurasi k", "update" => "konfigurasi k"),
423: "prerequisite" => "",
424: "filter" => "",
425: "infotambahanerror" => "",
426: "order by" => "",
427: "forcedouble" => ""
428: ),
429: "sms" => array( "guid" => array("id_sms", "ps.guid"),
430: "variable" => array("kode_prodi", "ps.kodeprodi"),
431: "table" => array("check" => "ak_programstudi ps", "update" => "ak_programstudi ps"),
432: "prerequisite" => "id_sp",
433: "filter" => "",
434: "infotambahanerror" => "namaprodi",
435: "order by" => "ps.kodeprodi",
436: "forcedouble" => ""
437: ),
438: "kurikulum" => array( "guid" => array("id_kurikulum_sp", "k.guid"),
439: "variable" => array("raw.id_sms,raw.id_smt", "ps.guid,k.tahun"),
440: "table" => array("check" => "ak_kurikulum k join ak_programstudi ps on ps.kdunitkerja=k.kdunitkerja", "update" => "ak_kurikulum k"),
441: "prerequisite" => "",
442: "filter" => "k.isignore=0",
443: "infotambahanerror" => "kurikulum",
444: "order by" => "",
445: "forcedouble" => ""
446: ),
447: "mata_kuliah" => array( "guid" => array("id_mk", "m.guid"),
448: "variable" => array("raw.kode_mk,s.id_sms,raw.sks_mk", "m.kodematakuliah,p.guid,m.sks"),
449: "table" => array("check" => "ak_matakuliah m join ak_kurikulum k on k.kdkurikulum=m.kdkurikulum join ak_programstudi p on p.kdunitkerja=k.kdunitkerja", "update" => "ak_matakuliah m"),
450: "prerequisite" => "",
451: "filter" => "m.isignore=0",
452: "infotambahanerror" => "matakuliah",
453: "order by" => "m.kodematakuliah,m.sks",
454: "forcedouble" => ""
455: ),
456: "mata_kuliah_kurikulum" => array( "guid" => array("id_mk", "m.guidmkkurikulum"),
457: "variable" => array("p.id_kurikulum_sp,p.id_mk", "k.guid,m.guid"),
458: "table" => array("check" => "ak_matakuliah m join ak_kurikulum k on k.kdkurikulum=m.kdkurikulum", "update" => "ak_matakuliah m"),
459: "prerequisite" => "",
460: "filter" => "m.isignore=0 and k.isignore=0",
461: "infotambahanerror" => "matakuliah",
462: "order by" => "",
463: "forcedouble" => ""
464: ),
465: "mahasiswa" => array( "guid" => array("id_pd", "p.guidmahasiswa"),
466: "variable" => array("nm_pd,raw.tmpt_lahir,raw.tgl_lahir", "upper(p.namalengkap),upper(p.tempatlahir),p.tanggallahir"),
467: "table" => array("check" => "pddikti_v_mahasiswa p", "update" => "pt_person p"),
468: "prerequisite" => "",
469: "filter" => "p.isignore=0",
470: "infotambahanerror" => "",
471: "order by" => "p.namalengkap",
472: "forcedouble" => array( "table" => "mahasiswa_pt",
473: "field" => "id_pd"
474: )
475: ),
476: "mahasiswa_pt" => array( "guid" => array("id_reg_pd", "m.guid"),
477: "variable" => array("nipd,p.id_sms", "m.nim,p.guid"),
478: "table" => array("check" => "ak_mahasiswa m join ak_programstudi p on m.kdunitkerja=p.kdunitkerja", "update" => "ak_mahasiswa m"),
479: "prerequisite" => "",
480: "filter" => "m.isignore=0",
481: "infotambahanerror" => "m.kdperson",
482: "order by" => "m.nim",
483: "forcedouble" => ""
484: ),
485: "dosen" => array( "guid" => array("id_ptk", "p.guiddosen"),
486: "variable" => array("nm_ptk,raw.tmpt_lahir,raw.tgl_lahir", "upper(p.namalengkap),upper(tempatlahir),p.tanggallahir"),
487: /*"variable" => array("raw.nidn", "d.nidn"), */
488: "table" => array("check" => "pt_person p", "update" => "pt_person p"),
489: /*"table" => array("check" => "pt_person p join ak_dosen d on d.kdperson=p.kdperson", "update" => "pt_person p"), */
490: "prerequisite" => "",
491: "filter" => "p.kdperson in (select d.kdperson from ak_dosen d where d.isignore=0)",
492: "infotambahanerror" => "p.namalengkap",
493: "order by" => "p.namalengkap",
494: "forcedouble" => ""
495: ),
496: "kelas_kuliah" => array( "guid" => array("id_kls", "pm.guid"),
497: "variable" => array("p.id_smt,raw.nm_kls,p.id_mk", "pm.kdtahunakademik,kelasepsbed as kelas,m.guid as guidmatakuliah"),
498: "table" => array("check" => "ak_penawaranmatakuliah pm join ak_matakuliah m on m.kdmatakuliah=pm.kdmatakuliah join ak_tahunakademik ta on ta.kdtahunakademik=pm.kdtahunakademik", "update" => "ak_penawaranmatakuliah pm"),
499: "prerequisite" => "",
500: "filter" => "ta.isremidial=0 and pm.kdtahunakademik>=20091 and not isnull(m.guid) and pm.isignore=0 and m.isignore=0",
501: "infotambahanerror" => "m.kodematakuliah,m.matakuliah,kelas",
502: "order by" => "m.kdkurikulum,m.kodematakuliah",
503: "forcedouble" => ""
504: ),
505: "kuliah_mahasiswa" => array( "guid" => array("id_reg_pd,id_smt", "c.guid"),
506: "variable" => array("raw.id_reg_pd,id_smt,id_stat_mhs", "m.guid,c.kdtahunakademik,c.kdaktivitasmhs"),
507: "table" => array("check" => "ak_mahasiswa_ckd c join ak_mahasiswa m on m.kdmahasiswa=c.kdmahasiswa join pt_person p on p.kdperson=m.kdperson", "update" => "ak_mahasiswa_ckd c"),
508: "prerequisite" => "",
509: "filter" => "c.isignore=0 and m.isignore=0 and c.kdaktivitasmhs not in('L', 'A')",
510: "infotambahanerror" => "m.nim,p.namalengkap",
511: "order by" => "c.kdaktivitasmhs, c.kdtahunakademik,m.nim",
512: "forcedouble" => ""
513: ),
514: "mahasiswa_pt keluar" => array( "guid" => array("id_reg_pd", "m.guidlulus"),
515: "variable" => array("raw.id_reg_pd,p.id_jns_keluar", "m.guid,jeniskeluar"),
516: "table" => array("check" => "(select 4 as jeniskeluar) l,ak_mahasiswa m join ak_mahasiswa_ckd c on c.kdmahasiswa=m.kdmahasiswa", "update" => "ak_mahasiswa m"),
517: "prerequisite" => "",
518: "filter" => "m.isignore=0 and kdaktivitasmhs='K'",
519: "infotambahanerror" => "m.nim,c.kdtahunakademik",
520: "order by" => "c.kdtahunakademik,m.nim",
521: "forcedouble" => ""
522: ),
523: "kuliah_mahasiswa aktif" => array( "guid" => array("id_reg_pd,id_smt", "ri.guidinsert"),
524: "variable" => array("raw.id_reg_pd,id_smt,id_stat_mhs", "m.guid,ri.kdtahunakademik,krs.kdaktivitasmhs"),
525: "table" => array("check" => "(select distinct kdmahasiswa, kdtahunakademik, 'A' as kdaktivitasmhs from ak_krsnilai) krs join ak_rekap_ipk ri on (ri.kdmahasiswa=krs.kdmahasiswa and ri.kdtahunakademik=krs.kdtahunakademik) join ak_mahasiswa m on m.kdmahasiswa=ri.kdmahasiswa", "update" => "ak_rekap_ipk ri"),
526: "prerequisite" => "",
527: "filter" => "m.isignore=0 and (isnull(kdyudisium) or kdyudisium=0 or kdyudisium='')",
528: "infotambahanerror" => "m.nim,ri.kdtahunakademik",
529: "order by" => "ri.kdtahunakademik,m.nim",
530: "forcedouble" => ""
531: ),
532: "kuliah_mahasiswa aktif_update" => array( "guid" => array("id_reg_pd,id_smt", "ri.guidupdate"),
533: "variable" => array("raw.id_reg_pd,id_smt,id_stat_mhs", "m.guid,ri.kdtahunakademik,krs.kdaktivitasmhs"),
534: "table" => array("check" => "(select distinct kdmahasiswa, kdtahunakademik, 'A' as kdaktivitasmhs from ak_krsnilai) krs join ak_rekap_ipk ri on (ri.kdmahasiswa=krs.kdmahasiswa and ri.kdtahunakademik=krs.kdtahunakademik) join ak_mahasiswa m on m.kdmahasiswa=ri.kdmahasiswa", "update" => "ak_rekap_ipk ri"),
535: "prerequisite" => "",
536: "filter" => "m.isignore=0 and (isnull(kdyudisium) or kdyudisium=0 or kdyudisium='')",
537: "infotambahanerror" => "m.nim,ri.kdtahunakademik",
538: "order by" => "ri.kdtahunakademik,m.nim",
539: "forcedouble" => ""
540: ),
541: "kuliah_mahasiswa lulus_keaktifan" => array( "guid" => array("id_reg_pd,id_smt", "ri.guidlulus"),
542: "variable" => array("raw.id_reg_pd,id_smt,id_stat_mhs", "m.guid,y.kdtahunakademik,l.lulus"),
543: "table" => array("check" => "(select 'L' as lulus) l,ak_mahasiswa m join ak_yudisium y on y.kdyudisium = m.kdyudisium join ak_rekap_ipk ri on ri.kdtahunakademik = y.kdtahunakademik and m.kdmahasiswa = ri.kdmahasiswa", "update" => "ak_rekap_ipk ri"),
544: "prerequisite" => "",
545: "filter" => "m.isignore=0",
546: "infotambahanerror" => "m.nim,ri.kdtahunakademik",
547: "order by" => "ri.kdtahunakademik,m.nim",
548: "forcedouble" => ""
549: ),
550: "mahasiswa_pt lulus" => array( "guid" => array("id_reg_pd", "m.guidlulus"),
551: "variable" => array("raw.id_reg_pd,p.id_jns_keluar", "m.guid,jeniskeluar"),
552: "table" => array("check" => "(select 1 as jeniskeluar) l,pddikti_v_lulus_mahasiswa m", "update" => "ak_mahasiswa m"),
553: "prerequisite" => "",
554: "filter" => "m.isignore=0",
555: "infotambahanerror" => "m.nim,m.kdtahunakademik",
556: "order by" => "m.kdtahunakademik,m.nim",
557: "forcedouble" => ""
558: ),
559: "dosen_pembimbing" => array( "guid" => array("id_ptk,id_reg_pd", "guid"),
560: "variable" => array("raw.id_ptk,raw.id_reg_pd", "guiddosen,guidmahasiswa"),
561: "table" => array("check" => "pddikti_v_dosen_pembimbing p", "update" => "ak_karyatulis_penguji p"),
562: "prerequisite" => "",
563: "filter" => "isignore=0",
564: "infotambahanerror" => "",
565: "order by" => "",
566: "forcedouble" => ""
567: ),
568: "nilai_transfer" => array( "guid" => array("id_ekuivalensi", "e.guid"),
569: "variable" => array("p.id_reg_pd,p.id_mk", "m.guid,mk.guid"),
570: "table" => array("check" => "ak_krsnilai_equivalensi e join ak_mahasiswa m on m.kdmahasiswa=e.kdmahasiswa join ak_matakuliah mk on mk.kdmatakuliah=e.kdmatakuliah", "update" => "ak_krsnilai_equivalensi e"),
571: "prerequisite" => "",
572: "filter" => "e.isignore=0 and m.isignore=0 and mk.isignore=0 and not isnull(m.guid)",
573: "infotambahanerror" => "nim,matakuliah",
574: "order by" => "nim,matakuliah",
575: "forcedouble" => ""
576: ),
577: "nilai krs" => array( "guid" => array("id_kls,id_reg_pd", "k.guidkrs"),
578: "variable" => array("p.id_kls,p.id_reg_pd", "p.guid,m.guid"),
579: "table" => array("check" => "ak_krsnilai_nonremidial k join ak_mahasiswa m on m.kdmahasiswa=k.kdmahasiswa join ak_penawaranmatakuliah p on p.kdpenawaran=k.kdpenawaran", "update" => "ak_krsnilai_nonremidial k"),
580: "prerequisite" => "",
581: "filter" => "m.isignore=0 and p.isignore=0 and k.isignore=0",
582: "infotambahanerror" => "nim, kodematakuliah, p.kdpenawaran",
583: "order by" => "nim",
584: "forcedouble" => ""
585: ),
586: "nilai update" => array( "guid" => array("id_kls,id_reg_pd", "k.guidnilai"),
587: "variable" => array("p.id_kls,p.id_reg_pd", "p.guid,m.guid"),
588: "table" => array("check" => "ak_krsnilai_nonremidial k join ak_mahasiswa m on m.kdmahasiswa=k.kdmahasiswa join ak_penawaranmatakuliah p on p.kdpenawaran=k.kdpenawaran", "update" => "ak_krsnilai_nonremidial k"),
589: "prerequisite" => "",
590: "filter" => "m.isignore=0 and p.isignore=0 and k.isignore=0 and (k.nilai<='E' or k.nilai='T') and not isnull(guidkrs)",
591: "infotambahanerror" => "nim",
592: "order by" => "nim",
593: "forcedouble" => ""
594: ),
595: "ajar_dosen" => array( "guid" => array("id_ajar", "tt.guid"),
596: "variable" => array("p.id_reg_ptk,raw.id_kls", "id_reg_ptk,pm.guid"),
597: "table" => array("check" => "ak_timteaching tt join ak_penawaranmatakuliah pm on pm.kdpenawaran=tt.kdpenawaran join pt_person p on p.kdperson=tt.kdpersonepsbed join ak_penugasan pn on (pn.id_reg=p.guiddosen and pn.tahun=floor(tt.kdtahunakademik/10))", "update" => "ak_timteaching tt"),
598: "prerequisite" => "",
599: "filter" => "tt.isignore=0 and pm.isignore=0",
600: "infotambahanerror" => "",
601: "order by" => "",
602: "forcedouble" => ""
603: )
604: );
605: }
606:
607: /**
608: * pemetaan update NIDN di Institusi, mengambil data dari PDDIKTI
609: * <br/> indeks:
610: * <br/> - updatenidn
611: * <br/> isi:
612: * <br/> array("table" => array(nama_tabel_institusi, nama_tabel_pddikti), "nidn" => array(nama_kolom_nidn_institusi, nama_kolom_nidn_pddikti), "guid" => guid, "filter" => filter)
613: * <br/> dimana:
614: * <br/> table--- : nama tabel institusi dan PDDIKTI. berupa array()
615: * <br/> nidn---- : nama kolom nidn institusi dan PDDIKTI. berupa array()
616: * <br/> guid---- : nama kolom PDDIKTI yang terdapat kunci primer dari dosen
617: * <br/> filter-- : filter untuk tabel institusi
618: * <br/> catatan:
619: * <br/> - hanya 1 array
620: */
621: private function peta_updatenidn()
622: {
623: $this->peta["updatenidn"] = array ( "table" => array("dosen", "ak_dosen"),
624: "nidn" => array("nidn", "nidn"),
625: "guid" => "id_ptk",
626: "filter" => "kdperson in (select kdperson from pt_person where guidperson=:guid)"
627: );
628: }
629:
630: /**
631: * mengembalikan pemetaan antara tabel dan kolom PDDIKTI dengan Institusi untuk keperluan pengecekan, sinkronisasi, injeksi dan ekstraksi data
632: * @return string
633: * - pemetaan antara tabel dan kolom PDDIKTI dengan Institusi
634: */
635: function peta()
636: {
637: if (!$this->isdipetakan)
638: {
639: $this->peta_kolom();
640: $this->peta_tabel();
641: $this->peta_injek();
642: $this->peta_ekstrak();
643: $this->peta_pk();
644: $this->peta_guid();
645: $this->peta_updatenidn();
646: }
647: return $this->peta;
648: }
649: }
650: ?>