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-04-27
10: * <br/> sifat open source
11: * <br/> <img src="mapdb2.png" />
12: * <br/> <img src="mapdb.png" />
13: * <br/> * Pada peta["inject"], jika terdapat indeks "jenisfilter" berisi "internalfilter", maka tabel berisi query yang terdapat [internalfilter], misal: select * from A where where [internalfilter]
14: * <br/> catatan:
15: * <br/> 1. rule nama kolom PDDIKTI:
16: * <br/> --- dengan raw. -> nama kolom akan ditampilkan tanpa alias, contoh: raw.kolom1 akan ditampilkan kolom1
17: * <br/> --- tanpa raw. -> diberikan fungsi trim pada nama kolom, contoh: kolom1 akan ditampilkan trim(kolom1)
18: * <br/> --- dengan alias -> nama kolom akan ditampilkan apa adanya, contoh: p.kolom1 akan ditampilkan p.kolom1
19: * <br/> 2. yang dimaksud tabel institusi dapat berupa tabel, view atau query
20: * <br/> 3. peta yang harus diisi:
21: * <br/> -- a. webservice::cek_tabel()------------ : field, table, pk
22: * <br/> -- b. webservice::pddikti_sinkron_guid()- : guid, pk
23: * <br/> -- c. webservice::pddikti_injek()-------- : field, inject, lihat 2. Sync GUID
24: * <br/> -- d. webservice::pddikti_ekstrak()------ : field, extract
25: * @author Basit Adhi Prabowo, S.T. <basit@unisayogya.ac.id>
26: * @access public
27: * @todo Penanganan data dengan tanda petik, misalnya pada nama
28: */
29: class mapdb
30: {
31: /**
32: * pemetaaan tabel dan kolom Institusi dengan PDDIKTI
33: */
34: var $peta;
35: /**
36: * apakah pemetaan sudah dipetakan?
37: */
38: var $isdipetakan=false;
39:
40: /**
41: * pemetaan kolom PDDIKTI dan Institusi
42: * <br/> indeks:
43: * <br/> - field
44: * <br/> isi:
45: * <br/> nama_tabel_pddikti1 => array(field1.1_pddikti => field1.1_institusi, field1.2_pddikti => field1.2_institusi, ...),
46: * <br/> nama_tabel_pddikti2 => array(field2.1_pddikti => field2.1_institusi, field2.2_pddikti => field2.2_institusi, ...),
47: * <br/> ...
48: * <br/> catatan:
49: * <br/> - primary key harus di kolom pertama
50: */
51: private function peta_kolom()
52: {
53: $this->peta["field"] = array ( /* tabel Referensi */
54: "agama" => array("id_agama" => "kdagama", "nm_agama" => "agama"),
55: "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" => ""),
56: "ikatan_kerja_dosen" => array("id_ikatan_kerja" => "", "nm_ikatan_kerja" => "", "ket_ikatan_kerja" => ""),
57: "semester" => array("id_smt" => "kdtahunakademik", "id_thn_ajaran" => "tahunajaran", "nm_smt" => "tahunakademik", "smt" => "semester", "a_periode_aktif" => "isaktif", "tgl_mulai" => "tanggalawal", "tgl_akhir" => "tanggalakhir"),
58: "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" => ""),
59: "jabfung" => array("id_jabfung" => "kdjafa", "nm_jabfung" => "jafa"),
60: "ikatan_kerja_dosen" => array("id_ikatan_kerja" => "kdikatankerjadosen", "nm_ikatan_kerja" => "ikatankerjadosen", "ket_ikatan_kerja" => ""),
61: "jenis_keluar" => array("id_jenis_keluar" => "kdjeniskeluar", "ket_keluar" => "jeniskeluar", "a_pd" => "", "a_ptk" => ""),
62: "jenjang_pendidikan" => array("id_jenj_didik" => "kdjenjang", "nm_jenj_didik" => "jenjang", "u_jenj_lemb" => "", "u_jenj_org" => ""),
63: "penghasilan" => array("id_penghasilan" => "kdpenghasilan", "nm_penghasilan" => "penghasilan", "batas_bawah" => "", "batas_atas" => ""),
64: "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" => ""),
65: "negara" => array("id_negara" => "kdnegara", "nm_negara" => "namanegara", "a_ln" => "isluarnegeri", "benua" => "benua"),
66: "pekerjaan" => array("id_pekerjaan" => "kdpekerjaanpddikti", "nm_pekerjaan" => "pekerjaan"),
67: "jenis_pendaftaran" => array("id_jns_daftar" => "kdjeniskelaspddikti", "nm_jns_daftar" => "jeniskelas"),
68: /* tabel Isian */
69: /* primary key tidak digunakan untuk memasukkan data ke FEEDER PDDIKTI */
70: /* informasi pemetaan dengan tabel institusi ada pada $this->peta["table"] */
71: "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" => ""),
72: "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"),
73: "kelas_kuliah" => array("id_kls" => "guid", "id_sms" => "guid_prodi", "id_smt" => "kdtahunakademik", "id_mk" => "guid_matakuliah", "nm_kls" => "kelas", "sks_mk" => "sks"),
74: "mata_kuliah_kurikulum" => array("id" => "guid", "id_kurikulum_sp" => "guid_kurikulum", "id_mk" => "guid_matakuliah", "smt" => "semester", "a_wajib" => "wajib"),
75: "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 */
76: "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 */
77: "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 */
78: "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 */
79: "kuliah_mahasiswa lulus_keaktifan" => array("id" => "guidlulus", "id_smt" => "kdtahunakademik", "id_reg_pd" => "guidmahasiswa", "id_stat_mhs" => "kdaktivitasmhs"), /*untuk update data mahasiswa yang Lulus */
80: "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 */
81: "dosen_pembimbing" => array("id" => "guid", "id_ptk" => "guiddosen", "id_reg_pd" => "guidmahasiswa", "urutan_promotor" => "nourut"),
82: "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"),
83: "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"),
84: "nilai krs" => array("id" => "guidkrs", "id_kls" => "guidpenawaran", "id_reg_pd" => "guidmahasiswa", "nilai_angka" => "nilairiil", "nilai_huruf" => "nilai", "nilai_indeks" => "nilaiangka"),
85: "nilai update" => array("id" => "guidnilai", "id_kls" => "guidpenawaran", "id_reg_pd" => "guidmahasiswa", "nilai_angka" => "nilairiil", "nilai_huruf" => "nilai", "nilai_indeks" => "nilaiangka"),
86: "ajar_dosen" => array("id" => "tt.guid", "id_reg_ptk" => "guidpenugasan", "id_kls" => "guidkelas", "jml_tm_renc" => "rencana", "jml_tm_real" => "realisasi", "id_jns_eval" => "jeniseval", "sks_subst_tot" => "skssubsttot"),
87: "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"),
88: "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"),
89: "dosen_pt" => array("id" => "null", "id_reg_ptk" => "id_reg_ptk", "id_sdm" => "id_sdm", "id_thn_ajaran" => "tahun")
90: );
91: }
92:
93: /**
94: * pemetaan tabel PDDIKTI dan Institusi
95: * <br/> indeks:
96: * <br/> - table
97: * <br/> isi:
98: * <br/> nama_tabel_pddikti1 => array("nama" => nama_tabel_institusi1, "filter" => filter_tabel_institusi1),
99: * <br/> - atau -
100: * <br/> nama_tabel_pddikti2 => array("nama" => "", "filter" => "", "data" => array(indeks2.1=>data2.1, indeks2.2=>data2.2, ...)),
101: * <br/> ...
102: * <br/> dimana:
103: * <br/> - nama-- : nama tabel institusi
104: * <br/> - filter : filter data untuk tabel institusi
105: * <br/> - data-- : OPSIONAL - membuat data sendiri, tidak mengambil dari tabel. jika diisi, maka "nama" dan "filter" akan diabaikan. berupa array()
106: */
107: private function peta_tabel()
108: {
109: $this->peta["table"] = array ( /* tabel Referensi */
110: "agama" => array("nama" => "pt_agama", "filter" => ""),
111: "semester" => array("nama" => "pddikti_v_tahunakademik", "filter" => ""),
112: "jurusan" => array("nama" => "ak_programstudi", "filter" => ""),
113: "jabfung" => array("nama" => "pt_jafa", "filter" => ""),
114: "ikatan_kerja_dosen" => array("nama" => "ak_ikatankerjadosen", "filter" => ""),
115: "jenis_keluar" => array("nama" => "ak_jeniskeluar", "filter" => ""),
116: "jenjang_pendidikan" => array("nama" => "pt_jenjangpendidikan", "filter" => ""),
117: "penghasilan" => array("nama" => "ak_penghasilan", "filter" => ""),
118: "wilayah" => array("nama" => "pt_v_propinsikabupatenkecamatan", "filter" => ""),
119: "negara" => array("nama" => "pt_negara", "filter" => ""),
120: "pekerjaan" => array("nama" => "pt_pekerjaan", "filter" => ""),
121: "jenis_pendaftaran" => array("nama" => "ak_jeniskelas", "filter" => ""),
122: /* tabel Referensi yang tidak ada di WebService */
123: "kel_mk" => array("nama" => "", "filter" => "", "data" => array("A" => "MPK", "B" => "MKK", "C" => "MKB", "D" => "MPB", "E" => "MBB", "F" => "MKU/MKDU", "G" => "MKDK", "H" => "MKK")),
124: "jns_mk" => array("nama" => "", "filter" => "", "data" => array("A" => "Wajib", "B" => "Pilihan", "C" => "Wajib Peminatan", "D" => "Pilihan Peminatan", "S" => "Skripsi/Tugas Akhir"))
125: /* tabel Isian tidak diisikan di sini */
126: );
127: }
128:
129: /**
130: * pemetaan untuk memasukkan data dari Institusi ke PDDIKTI<br/>pindah ke peta_injek_usang() jika sudah usang dan perbaiki data dengan peta_injek_perbaiki_usang()
131: * <br/> indeks:
132: * <br/> - inject
133: * <br/> isi:
134: * <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),
135: * <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),
136: * <br/> ...
137: * <br/> dimana:
138: * <br/> table-------------- : nama tabel institusi
139: * <br/> filter------------- : filter data untuk tabel institusi
140: * <br/> type--------------- : insert (memasukkan data ke PDDIKTI) atau update (memperbarui data yang ada di PDDIKTI)
141: * <br/> ignoreinject------- : true (diabaikan ketika injeksi massal) atau false (dijalankan ketika injeksi massal)
142: * <br/> tahunakademik------ : OPSIONAL - tahun akademik dari data yang akan diambil
143: * <br/> tandatahunakademik- : OPSIONAL - =, <, >, <=, atau >=
144: * <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)
145: * <br/> fieldupdate-------- : OPSIONAL - digunakan ketika type="update", kolom-kolom di PDDIKTI yang akan diubah. berupa array()
146: * <br/> fieldwhere--------- : OPSIONAL - digunakan ketika type="update", filter data di PDDIKTI yang akan diubah. berupa array()
147: * <br/> jenisfilter-------- : OPSIONAL - internalfilter (mengganti [internalfilter] dengan "filter") atau string kosong
148: * <br/> catatan:
149: * <br/> - format nama tabel pddikti: [nama_tabel_pddikti] atau [nama_tabel_pddikti]<spasi>[keterangan]
150: * <br/> - Ingat! indeks "tahunakademik" pada "inject" harus bisa di-query-kan di this->peta["table"] pada this->peta["guid"], perhatikan alias tabel
151: * <br/> - "id" pada this->peta["field"] otomatis dibuat isnull()
152: */
153: private function peta_injek()
154: {
155: $this->peta["inject"] = array ( // tipe insert: table, filter, tahunakademik, tandatahunakademik, istahunakademikkrs, type=insert
156: "mata_kuliah_kurikulum" => array( "table" => "pddikti_v_matakuliahkurikulum mk",
157: "filter" => "not isnull(guid_kurikulum) and not isnull(guid_matakuliah)",
158: "type" => "insert",
159: "ignoreinject" => false
160: ),
161: "kelas_kuliah" => array( "table" => "pddikti_v_penawaranmatakuliah pm",
162: "filter" => "not isnull(guid_matakuliah)",
163: "tahunakademik" => "pm.kdtahunakademik",
164: "tandatahunakademik" => "=",
165: "istahunakademikkrs" => true,
166: "type" => "insert",
167: "ignoreinject" => false
168: ),
169: "mahasiswa" => array( "table" => "pddikti_v_mahasiswa p",
170: "filter" => "isnull(guid) and p.isignore=0",
171: "tahunakademik" => "p.kdtamasuk",
172: "tandatahunakademik" => "=",
173: "istahunakademikkrs" => true,
174: "type" => "insert",
175: "ignoreinject" => false
176: ),
177: "mahasiswa_pt" => array( "table" => "pddikti_v_mahasiswa_pt m",
178: "filter" => "isnull(guid) and not isnull(guidmahasiswa) and m.isignore=0",
179: "tahunakademik" => "m.kdtamasuk",
180: "tandatahunakademik" => "=",
181: "istahunakademikkrs" => true,
182: "type" => "insert",
183: "ignoreinject" => false
184: ),
185: "nilai_transfer" => array( "table" => "pddikti_v_nilai_transfer nt",
186: "filter" => "isnull(guid) and nt.isignoremahasiswa=0 and nt.isignorematakuliah=0 and not isnull(guidmahasiswa) and not isnull(guidmatakuliah)",
187: "tahunakademik" => "m.kdtamasuk",
188: "tandatahunakademik" => "=",
189: "istahunakademikkrs" => true,
190: "type" => "insert",
191: "ignoreinject" => false
192: ),
193: "nilai krs" => array( "table" => "pddikti_v_krs k", //krs
194: "filter" => "k.isignoremahasiswa=0 and k.isignorepenawaran=0 and not isnull(guidmahasiswa) and not isnull(guidpenawaran)",
195: "tahunakademik" => "k.kdtahunakademik",
196: "tandatahunakademik" => "=",
197: "istahunakademikkrs" => true,
198: "type" => "insert",
199: "ignoreinject" => false
200: ),
201: // tipe update: table, filter, tahunakademik, tandatahunakademik, istahunakademikkrs, type=update, fieldupdate
202: "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]",
203: "filter" => "(k.nilai <= 'E' or k.nilai = 'T') and not isnull(guidkrs)",
204: "jenisfilter" => "internalfilter",
205: "tahunakademik" => "k.kdtahunakademik",
206: "tandatahunakademik" => "=",
207: "istahunakademikkrs" => false,
208: "type" => "update",
209: "fieldupdate" => array("nilai_angka", "nilai_huruf", "nilai_indeks"),
210: "fieldwhere" => array("id_kls","id_reg_pd"),
211: "ignoreinject" => false
212: ),
213: "ajar_dosen" => array( "table" => "select sql_cache kdtimteaching, guid, guidkelas, id_reg_ptk as guidpenugasan, ceil(sum(rencana)) as rencana, ceil(sum(realisasi)) as realisasi, jeniseval, skssubsttot, datamengajar
214: from
215: (
216: (select tt.kdtimteaching, tt.guid, pm.guid as guidkelas, id_reg_ptk, count(1)*durasislot as rencana, sum(if(isrealisasi,1,0))*durasislot as realisasi, 1 as jeniseval, 0 as skssubsttot, tt.kdpenawaran, tt.kdpersonepsbed, concat(namalengkap, '-', matakuliah, '-', kelas) as datamengajar 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)) join ak_matakuliah m on m.kdmatakuliah=pm.kdmatakuliah where [internalfilter] and kdalasan0<>2 group by tt.kdpenawaran, tt.kdpersonepsbed)
217: union all
218: (select tt.kdtimteaching, tt.guid, pm.guid as guidkelas, id_reg_ptk, count(1)*durasislot as rencana, sum(if(isrealisasi,1,0))*durasislot as realisasi, 1 as jeniseval, 0 as skssubsttot, kl.kdpenawaran, tt.kdpersonepsbed, concat(namalengkap, '-', matakuliah, '-', kelas) as datamengajar 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)) join ak_matakuliah m on m.kdmatakuliah=pm.kdmatakuliah where [internalfilter] and kdalasan0<>2 group by kl.kdpenawaran, tt.kdpersonepsbed)
219: ) ajar_dosen
220: group by kdpenawaran, kdpersonepsbed",
221: "filter" => "tt.isignore=0 and pm.isignore=0",
222: "jenisfilter" => "internalfilter",
223: "tahunakademik" => "tt.kdtahunakademik,pm.kdtahunakademik,jk.kdtahunakademik",
224: "tandatahunakademik" => "=",
225: "istahunakademikkrs" => false,
226: "type" => "insert",
227: "ignoreinject" => false
228: ),
229: "kuliah_mahasiswa" => array( "table" => "pddikti_v_kuliah_mahasiswa c",
230: "filter" => "isnull(c.guid) and not isnull(c.guidmahasiswa) and c.isignore=0 and c.isignoremahasiswa=0 and c.kdaktivitasmhs not in('L', 'A')",
231: "tahunakademik" => "c.kdtahunakademik",
232: "tandatahunakademik" => "=",
233: "istahunakademikkrs" => true,
234: "type" => "insert",
235: "ignoreinject" => false
236: ),
237: "mahasiswa_pt keluar" => array( "table" => "pddikti_v_keluar_mahasiswa m",
238: "filter" => "isignore=0",
239: "tahunakademik" => "kdtahunakademik",
240: "tandatahunakademik" => "=",
241: "istahunakademikkrs" => true,
242: "type" => "update",
243: "fieldupdate" => array("id_jns_keluar","tgl_keluar","ket"),
244: "fieldwhere" => array("id_reg_pd"),
245: "ignoreinject" => false
246: ),
247: "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]",
248: // "filter" => "isnull(guidinsert) and m.isignore=0 and (isnull(kdyudisium) or kdyudisium=0 or kdyudisium='')",
249: "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
250: "jenisfilter" => "internalfilter",
251: "tahunakademik" => "ri.kdtahunakademik",
252: "tandatahunakademik" => "=",
253: "istahunakademikkrs" => true,
254: "type" => "insert",
255: "ignoreinject" => false
256: ),
257: "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]",
258: "filter" => "m.isignore=0 and (isnull(kdyudisium) or kdyudisium=0 or kdyudisium='')",
259: "jenisfilter" => "internalfilter",
260: "tahunakademik" => "ri.kdtahunakademik",
261: "tandatahunakademik" => "=",
262: "istahunakademikkrs" => false,
263: "type" => "update",
264: "fieldupdate" => array("ips", "sks_smt", "ipk", "sks_total"),
265: "fieldwhere" => array("id_smt","id_reg_pd","id_stat_mhs"),
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: "infotambahanerror" => "nim",
276: "fieldwhere" => array("id_reg_pd"),
277: "ignoreinject" => false
278: ),
279: "dosen_pembimbing" => array( "table" => "pddikti_v_dosen_pembimbing p",
280: "filter" => "isignore=0",
281: "tahunakademik" => "kdtahunakademik",
282: "tandatahunakademik" => "=",
283: "istahunakademikkrs" => false,
284: "type" => "insert",
285: "ignoreinject" => false
286: ),
287: "mahasiswa_pt updatedata" => array( "table" => "pddikti_v_mahasiswa_pt m",
288: "filter" => "m.isignore=0",
289: "tahunakademik" => "m.kdtamasuk",
290: "tandatahunakademik" => "=",
291: "istahunakademikkrs" => true,
292: "type" => "update",
293: "fieldupdate" => array("mulai_smt", "sks_diakui"),
294: "fieldwhere" => array("id_reg_pd"),
295: "ignoreinject" => true
296: ),
297: "mahasiswa updatedata" => array( "table" => "pddikti_v_mahasiswa p",
298: "filter" => "p.isignore=0",
299: "tahunakademik" => "p.kdtamasuk",
300: "tandatahunakademik" => "=",
301: "istahunakademikkrs" => true,
302: "type" => "update",
303: "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"),
304: "infotambahanerror" => "namalengkap",
305: "fieldwhere" => array("id_pd"),
306: "ignoreinject" => true
307: )
308: );
309: }
310:
311: /**
312: * pemetaan untuk memasukkan data dari Institusi ke PDDIKTI - sudah usang, dahulu digunakan, tetapi untuk versi berikutnya tidak digunakan
313: * <br/> indeks:
314: * <br/> - inject
315: * <br/> isi:
316: * <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),
317: * <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),
318: * <br/> ...
319: * <br/> dimana:
320: * <br/> table-------------- : nama tabel institusi
321: * <br/> filter------------- : filter data untuk tabel institusi
322: * <br/> type--------------- : insert (memasukkan data ke PDDIKTI) atau update (memperbarui data yang ada di PDDIKTI)
323: * <br/> ignoreinject------- : true (diabaikan ketika injeksi massal) atau false (dijalankan ketika injeksi massal)
324: * <br/> tahunakademik------ : OPSIONAL - tahun akademik dari data yang akan diambil
325: * <br/> tandatahunakademik- : OPSIONAL - =, <, >, <=, atau >=
326: * <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)
327: * <br/> fieldupdate-------- : OPSIONAL - digunakan ketika type="update", kolom-kolom di PDDIKTI yang akan diubah. berupa array()
328: * <br/> fieldwhere--------- : OPSIONAL - digunakan ketika type="update", filter data di PDDIKTI yang akan diubah. berupa array()
329: * <br/> jenisfilter-------- : OPSIONAL - internalfilter (mengganti [internalfilter] dengan "filter") atau string kosong
330: * <br/> catatan:
331: * <br/> - format nama tabel pddikti: [nama_tabel_pddikti] atau [nama_tabel_pddikti]<spasi>[keterangan]
332: * <br/> - Ingat! indeks "tahunakademik" pada "inject" harus bisa di-query-kan di this->peta["table"] pada this->peta["guid"], perhatikan alias tabel
333: * <br/> - "id" pada this->peta["field"] otomatis dibuat isnull()
334: */
335: private function peta_injek_usang()
336: {
337: $this->peta["inject_usang"] = array (
338: //usang pada versi 2.0
339: "kuliah_mahasiswa lulus_keaktifan" => array( "table" => "pddikti_v_lulus_keaktifan y",
340: "filter" => "y.isignore=0",
341: "tahunakademik" => "y.kdtahunakademik",
342: "tandatahunakademik" => "=",
343: "istahunakademikkrs" => false,
344: "type" => "update",
345: "fieldupdate" => array("id_stat_mhs"),
346: "fieldwhere" => array("id_smt","id_reg_pd"),
347: "ignoreinject" => false
348: ),
349: );
350: }
351:
352: /**
353: * memperbaiki data pemetaan untuk memasukkan data dari Institusi ke PDDIKTI karena ada yang sudah usang seperti pada peta_injek_usang()
354: * <br/> indeks:
355: * <br/> - inject
356: * <br/> isi:
357: * <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),
358: * <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),
359: * <br/> ...
360: * <br/> dimana:
361: * <br/> table-------------- : nama tabel institusi
362: * <br/> filter------------- : filter data untuk tabel institusi
363: * <br/> type--------------- : insert (memasukkan data ke PDDIKTI) atau update (memperbarui data yang ada di PDDIKTI)
364: * <br/> ignoreinject------- : true (diabaikan ketika injeksi massal) atau false (dijalankan ketika injeksi massal)
365: * <br/> tahunakademik------ : OPSIONAL - tahun akademik dari data yang akan diambil
366: * <br/> tandatahunakademik- : OPSIONAL - =, <, >, <=, atau >=
367: * <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)
368: * <br/> fieldupdate-------- : OPSIONAL - digunakan ketika type="update", kolom-kolom di PDDIKTI yang akan diubah. berupa array()
369: * <br/> fieldwhere--------- : OPSIONAL - digunakan ketika type="update", filter data di PDDIKTI yang akan diubah. berupa array()
370: * <br/> jenisfilter-------- : OPSIONAL - internalfilter (mengganti [internalfilter] dengan "filter") atau string kosong
371: * <br/> catatan:
372: * <br/> - format nama tabel pddikti: [nama_tabel_pddikti] atau [nama_tabel_pddikti]<spasi>[keterangan]
373: * <br/> - Ingat! indeks "tahunakademik" pada "inject" harus bisa di-query-kan di this->peta["table"] pada this->peta["guid"], perhatikan alias tabel
374: * <br/> - "id" pada this->peta["field"] otomatis dibuat isnull()
375: */
376: private function peta_injek_perbaiki_usang()
377: {
378: $this->peta["inject_perbaiki_usang"] = array (
379: //memperbaiki peta_injek_usang "kuliah_mahasiswa lulus_keaktifan"
380: "kuliah_mahasiswa lulus_keaktifan" => array( "table" => "pddikti_v_lulus_keaktifan_perbaiki y",
381: "filter" => "y.isignore=0",
382: "tahunakademik" => "y.kdtahunakademik",
383: "tandatahunakademik" => "=",
384: "istahunakademikkrs" => false,
385: "type" => "update",
386: "fieldupdate" => array("id_stat_mhs"),
387: "fieldwhere" => array("id_smt","id_reg_pd"),
388: "ignoreinject" => false
389: ),
390: );
391: }
392:
393: /**
394: * pemetaan untuk mengambil data dari PDDIKTI ke Institusi
395: * <br/> indeks:
396: * <br/> - extract
397: * <br/> isi:
398: * <br/> nama_tabel_pddikti1 => array("table" => nama_tabel_institusi1, "uniquefield" => uniquefield_tabel_pddikti1, "istahunakademikkrs" => istahunakademikkrs1, "filtertahunakademik" => filtertahunakademik_tabel_pddikti1),
399: * <br/> nama_tabel_pddikti2 => array("table" => nama_tabel_institusi2, "uniquefield" => uniquefield_tabel_pddikti2, "istahunakademikkrs" => istahunakademikkrs2, "filtertahunakademik" => filtertahunakademik_tabel_pddikti2),
400: * <br/> ...
401: * <br/> dimana:
402: * <br/> table--------------- : nama tabel institusi
403: * <br/> uniquefield--------- : kolom-kolom sebagai parameter data akan diambil dari PDDIKTI ke Institusi
404: * <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)
405: * <br/> filtertahunakademik- : filter data untuk tabel PDDIKTI berdasarkan tahun akademik; menggunakan [tahunakademik], atau [tahun]
406: */
407: private function peta_ekstrak()
408: {
409: $this->peta["extract"] = array ( "dosen_pt" => array( "table" => "ak_penugasan",
410: "uniquefield" => array("id_sdm,id_reg_ptk","id_sdm,id_reg_ptk"),
411: "istahunakademikkrs" => false,
412: "filtertahunakademik" => "t.id_thn_ajaran=[tahun]"
413: )
414: );
415: }
416:
417: /**
418: * pemetaan kunci primer PDDIKTI dan Institusi.
419: * <br/> indeks:
420: * <br/> - pk
421: * <br/> isi:
422: * <br/> nama_tabel_pddikti1 => array(kunciprimer1_pddikti => kunciprimer1_institusi),
423: * <br/> nama_tabel_pddikti2 => array(kunciprimer2_pddikti => kunciprimer2_institusi),
424: * <br/> ...
425: * <br/> catatan:
426: * <br/> - semua tabel di FEEDER PDDIKTI dengan tipe REF harus dimasukkan, tampilkan dengan $ws->ListTable()
427: */
428: private function peta_pk()
429: {
430: $this->peta["pk"] = array ( /* tabel Referensi */
431: "agama" => array("id_agama", "kdagama"),
432: "bentuk_pendidikan" => array("id_bp", ""),
433: "ikatan_kerja_dosen" => array("id_ikatan_kerja", "kdikatankerjadosen"),
434: "jabfung" => array("id_jabfung", "kdjafa"),
435: "jenis_evaluasi" => array("id_jns_eval", ""),
436: "jenis_keluar" => array("id_jns_keluar", "kdjeniskeluar"),
437: "jenis_sert" => array("id_jns_sert", ""),
438: "jenis_sms" => array("id_jns_sms", ""),
439: "jenis_subst" => array("id_jns_subst", ""),
440: "jenjang_pendidikan" => array("id_jenj_didik", "kdjenjang"),
441: "jurusan" => array("id_jur", "kodeprodi"),
442: "kebutuhan_khusus" => array("id_kk", ""),
443: "lembaga_pengangkat" => array("id_lemb_angkat", ""),
444: "level_wilayah" => array("id_level_wil", ""),
445: "negara" => array("id_negara", "kdnegara"),
446: "pangkat_gol" => array("id_pangkat_gol", ""),
447: "pekerjaan" => array("id_pekerjaan", "kdpekerjaan"),
448: "jenis_pendaftaran" => array("id_jns_daftar", "kdjeniskelaspddikti"),
449: "penghasilan" => array("id_penghasilan", "kdpenghasilan"),
450: "semester" => array("id_smt", "kdtahunakademik"),
451: "status_keaktifan_pegawai" => array("id_stat_aktif", ""),
452: "status_kepegawaian" => array("id_stat_pegawai", ""),
453: "status_mahasiswa" => array("id_stat_mhs", ""),
454: "wilayah" => array("id_wil", "idwil"),
455: "tahun_ajaran" => array("id_thn_ajaran", ""),
456: /* tabel Isian hanya mengisi PK untuk institusi */
457: "satuan_pendidikan" => array("", "idkonfigurasi"),
458: "sms" => array("", "kodeprodi"),
459: "kurikulum" => array("", "kdkurikulum"),
460: "mata_kuliah" => array("", "kdmatakuliah"),
461: "mahasiswa" => array("id", "p.kdperson"),
462: "mahasiswa_pt" => array("id", "kdmahasiswa"),
463: "dosen" => array("", "p.kdperson"),
464: "kelas_kuliah" => array("id_kls", "kdpenawaran"),
465: "kuliah_mahasiswa" => array("id", "kdmhsckd"),
466: "kuliah_mahasiswa aktif" => array("id", "kdrekapipk"),
467: "kuliah_mahasiswa aktif_update" => array("id", "kdrekapipk"),
468: "kuliah_mahasiswa lulus_keaktifan" => array("id", "kdrekapipk"),
469: "mahasiswa_pt lulus" => array("id", "m.kdmahasiswa"),
470: "mahasiswa_pt keluar" => array("id", "m.kdmahasiswa"),
471: "nilai_transfer" => array("id", "kdkrsnilai"),
472: "mata_kuliah_kurikulum" => array("id", "kdmatakuliah"),
473: "nilai krs" => array("id", "kdkrsnilai"),
474: "nilai update" => array("id", "kdkrsnilai"),
475: "ajar_dosen" => array("id", "tt.kdtimteaching"),
476: "dosen_pembimbing" => array("id", "kdkaryatulispenguji"),
477: "mahasiswa_pt updatedata" => array("id", "null"),
478: "mahasiswa updatedata" => array("id", "null")
479: );
480: }
481:
482: /**
483: * pemetaan guid PDDIKTI dan Institusi
484: * <br/> indeks:
485: * <br/> - guid
486: * <br/> isi:
487: * <br/> nama_tabel_pddikti1 => array ( 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, "tahunakademikinjectdipakai" => tahunakademikinjectdipakai1), array(...), ... ),
488: * <br/> nama_tabel_pddikti2 => array ( 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, "tahunakademikinjectdipakai" => tahunakademikinjectdipakai2), array(...), ... ),
489: * <br/> ...
490: * <br/> dimana:
491: * <br/> guid------------------------ : nama kolom PDDIKTI yang terdapat kunci primer untuk PDDIKTI dan Institusi
492: * <br/> variable-------------------- : kolom yang digunakan untuk menyamakan data
493: * <br/> table----------------------- : tabel yang dicek di institusi, tabel yang diupdate di institusi
494: * <br/> prerequisite---------------- : kolom yang PDDIKTI yang harus ada isinya
495: * <br/> filter---------------------- : filter untuk tabel institusi
496: * <br/> infotambahanerror----------- : info tambahan ketika ada kesalahan
497: * <br/> order by-------------------- : parameter pengurutan tabel institusi
498: * <br/> forcedouble----------------- : perilaku ketika ada data rangkap
499: * <br/> tahunakademikinjectdipakai-- : berapa banyak parameter tahunakademik yang dipakai dari peta["inject"][namatabel], -1 berarti semua
500: */
501: private function peta_guid()
502: {
503: $this->peta["guid"] = array ( /* tabel Referensi tidak memerlukan mapping ini */
504: "satuan_pendidikan" => array( array( "guid" => array("id_sp", "k.guid"),
505: "variable" => array("npsn", "k.kodept"),
506: "table" => array("check" => "konfigurasi k", "update" => "konfigurasi k"),
507: "prerequisite" => "",
508: "filter" => "",
509: "infotambahanerror" => "",
510: "order by" => "",
511: "forcedouble" => "",
512: "tahunakademikinjectdipakai" => -1
513: )
514: ),
515: "sms" => array( array( "guid" => array("id_sms", "ps.guid"),
516: "variable" => array("kode_prodi", "ps.kodeprodi"),
517: "table" => array("check" => "ak_programstudi ps", "update" => "ak_programstudi ps"),
518: "prerequisite" => "id_sp",
519: "filter" => "",
520: "infotambahanerror" => "namaprodi",
521: "order by" => "ps.kodeprodi",
522: "forcedouble" => "",
523: "tahunakademikinjectdipakai" => -1
524: )
525: ),
526: "kurikulum" => array( array( "guid" => array("id_kurikulum_sp", "k.guid"),
527: "variable" => array("raw.id_sms,raw.id_smt", "ps.guid,k.tahun"),
528: "table" => array("check" => "ak_kurikulum k join ak_programstudi ps on ps.kdunitkerja=k.kdunitkerja", "update" => "ak_kurikulum k"),
529: "prerequisite" => "",
530: "filter" => "k.isignore=0",
531: "infotambahanerror" => "kurikulum",
532: "order by" => "",
533: "forcedouble" => "",
534: "tahunakademikinjectdipakai" => -1
535: )
536: ),
537: "mata_kuliah" => array( array( "guid" => array("id_mk", "m.guid"),
538: "variable" => array("raw.kode_mk,s.id_sms,raw.sks_mk", "m.kodematakuliah,p.guid,m.sks"),
539: "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"),
540: "prerequisite" => "",
541: "filter" => "m.isignore=0",
542: "infotambahanerror" => "matakuliah",
543: "order by" => "m.kodematakuliah,m.sks",
544: "forcedouble" => "",
545: "tahunakademikinjectdipakai" => -1
546: )
547: ),
548: "mata_kuliah_kurikulum" => array( array( "guid" => array("id_mk", "m.guidmkkurikulum"),
549: "variable" => array("p.id_kurikulum_sp,p.id_mk", "k.guid,m.guid"),
550: "table" => array("check" => "ak_matakuliah m join ak_kurikulum k on k.kdkurikulum=m.kdkurikulum", "update" => "ak_matakuliah m"),
551: "prerequisite" => "",
552: "filter" => "m.isignore=0 and k.isignore=0",
553: "infotambahanerror" => "matakuliah",
554: "order by" => "",
555: "forcedouble" => "",
556: "tahunakademikinjectdipakai" => -1
557: )
558: ),
559: "mahasiswa" => array( array( "guid" => array("id_pd", "p.guidmahasiswa"),
560: "variable" => array("nm_pd,raw.tmpt_lahir,raw.tgl_lahir", "upper(p.namalengkap),upper(p.tempatlahir),p.tanggallahir"),
561: "table" => array("check" => "pddikti_v_mahasiswa p", "update" => "pt_person p"),
562: "prerequisite" => "",
563: "filter" => "p.isignore=0",
564: "infotambahanerror" => "nim",
565: "order by" => "p.namalengkap",
566: "forcedouble" => array( "table" => "mahasiswa_pt",
567: "field" => "id_pd"
568: ),
569: "tahunakademikinjectdipakai" => -1
570: )
571: ),
572: "mahasiswa_pt" => array( array( "guid" => array("id_reg_pd", "m.guid"),
573: "variable" => array("nipd,p.id_sms", "m.nim,p.guid"),
574: "table" => array("check" => "ak_mahasiswa m join ak_programstudi p on m.kdunitkerja=p.kdunitkerja", "update" => "ak_mahasiswa m"),
575: "prerequisite" => "",
576: "filter" => "m.isignore=0",
577: "infotambahanerror" => "m.kdperson, nim",
578: "order by" => "m.nim",
579: "forcedouble" => "",
580: "tahunakademikinjectdipakai" => -1
581: )
582: ),
583: "dosen" => array( array( "guid" => array("id_ptk", "p.guiddosen"),
584: "variable" => array("nm_ptk,raw.tmpt_lahir,raw.tgl_lahir", "upper(p.namalengkap),upper(tempatlahir),p.tanggallahir"),
585: /*"variable" => array("raw.nidn", "d.nidn"), */
586: "table" => array("check" => "pt_person p", "update" => "pt_person p"),
587: /*"table" => array("check" => "pt_person p join ak_dosen d on d.kdperson=p.kdperson", "update" => "pt_person p"), */
588: "prerequisite" => "",
589: "filter" => "p.kdperson in (select d.kdperson from ak_dosen d where d.isignore=0)",
590: "infotambahanerror" => "p.namalengkap",
591: "order by" => "p.namalengkap",
592: "forcedouble" => "",
593: "tahunakademikinjectdipakai" => -1
594: )
595: ),
596: "kelas_kuliah" => array( array( "guid" => array("id_kls", "pm.guid"),
597: "variable" => array("p.id_smt,raw.nm_kls,p.id_mk", "pm.kdtahunakademik,kelasepsbed as kelas,m.guid as guidmatakuliah"),
598: "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"),
599: "prerequisite" => "",
600: "filter" => "ta.isremidial=0 and pm.kdtahunakademik>=20091 and not isnull(m.guid) and pm.isignore=0 and m.isignore=0",
601: "infotambahanerror" => "m.kodematakuliah,m.matakuliah,kelas",
602: "order by" => "m.kdkurikulum,m.kodematakuliah",
603: "forcedouble" => "",
604: "tahunakademikinjectdipakai" => -1
605: )
606: ),
607: "kuliah_mahasiswa" => array( array( "guid" => array("id_reg_pd,id_smt", "c.guid"),
608: "variable" => array("raw.id_reg_pd,id_smt,id_stat_mhs", "m.guid,c.kdtahunakademik,c.kdaktivitasmhs"),
609: "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"),
610: "prerequisite" => "",
611: "filter" => "c.isignore=0 and m.isignore=0 and c.kdaktivitasmhs not in('L', 'A')",
612: "infotambahanerror" => "m.nim,p.namalengkap",
613: "order by" => "c.kdaktivitasmhs, c.kdtahunakademik,m.nim",
614: "forcedouble" => "",
615: "tahunakademikinjectdipakai" => -1
616: )
617: ),
618: "mahasiswa_pt keluar" => array( array( "guid" => array("id_reg_pd", "m.guidlulus"),
619: "variable" => array("raw.id_reg_pd,p.id_jns_keluar", "m.guid,jeniskeluar"),
620: "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"),
621: "prerequisite" => "",
622: "filter" => "m.isignore=0 and kdaktivitasmhs='K'",
623: "infotambahanerror" => "m.nim,c.kdtahunakademik",
624: "order by" => "c.kdtahunakademik,m.nim",
625: "forcedouble" => "",
626: "tahunakademikinjectdipakai" => -1
627: )
628: ),
629: "kuliah_mahasiswa aktif" => array( array( "guid" => array("id_reg_pd,id_smt", "ri.guidinsert"),
630: "variable" => array("raw.id_reg_pd,id_smt,id_stat_mhs", "m.guid,ri.kdtahunakademik,krs.kdaktivitasmhs"),
631: "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"),
632: "prerequisite" => "",
633: "filter" => "m.isignore=0 and (isnull(kdyudisium) or kdyudisium=0 or kdyudisium='')",
634: "infotambahanerror" => "m.nim,ri.kdtahunakademik",
635: "order by" => "ri.kdtahunakademik,m.nim",
636: "forcedouble" => "",
637: "tahunakademikinjectdipakai" => -1
638: )
639: ),
640: "kuliah_mahasiswa aktif_update" => array( array( "guid" => array("id_reg_pd,id_smt", "ri.guidupdate"),
641: "variable" => array("raw.id_reg_pd,id_smt,id_stat_mhs", "m.guid,ri.kdtahunakademik,krs.kdaktivitasmhs"),
642: "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"),
643: "prerequisite" => "",
644: "filter" => "m.isignore=0 and (isnull(kdyudisium) or kdyudisium=0 or kdyudisium='')",
645: "infotambahanerror" => "m.nim,ri.kdtahunakademik",
646: "order by" => "ri.kdtahunakademik,m.nim",
647: "forcedouble" => "",
648: "tahunakademikinjectdipakai" => -1
649: )
650: ),
651: "kuliah_mahasiswa lulus_keaktifan" => array( array( "guid" => array("id_reg_pd,id_smt", "ri.guidlulus"),
652: "variable" => array("raw.id_reg_pd,id_smt,id_stat_mhs", "m.guid,y.kdtahunakademik,l.lulus"),
653: "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"),
654: "prerequisite" => "",
655: "filter" => "m.isignore=0",
656: "infotambahanerror" => "m.nim,ri.kdtahunakademik",
657: "order by" => "ri.kdtahunakademik,m.nim",
658: "forcedouble" => "",
659: "tahunakademikinjectdipakai" => -1
660: )
661: ),
662: "mahasiswa_pt lulus" => array( array( "guid" => array("id_reg_pd", "m.guidlulus"),
663: "variable" => array("raw.id_reg_pd,p.id_jns_keluar", "m.guid,jeniskeluar"),
664: "table" => array("check" => "(select 1 as jeniskeluar) l,pddikti_v_lulus_mahasiswa m", "update" => "ak_mahasiswa m"),
665: "prerequisite" => "",
666: "filter" => "m.isignore=0",
667: "infotambahanerror" => "m.nim,m.kdtahunakademik",
668: "order by" => "m.kdtahunakademik,m.nim",
669: "forcedouble" => "",
670: "tahunakademikinjectdipakai" => -1
671: )
672: ),
673: "dosen_pembimbing" => array( array( "guid" => array("id_ptk,id_reg_pd", "guid"),
674: "variable" => array("raw.id_ptk,raw.id_reg_pd", "guiddosen,guidmahasiswa"),
675: "table" => array("check" => "pddikti_v_dosen_pembimbing p", "update" => "ak_karyatulis_penguji p"),
676: "prerequisite" => "",
677: "filter" => "isignore=0",
678: "infotambahanerror" => "",
679: "order by" => "",
680: "forcedouble" => "",
681: "tahunakademikinjectdipakai" => -1
682: )
683: ),
684: "nilai_transfer" => array( array( "guid" => array("id_ekuivalensi", "e.guid"),
685: "variable" => array("p.id_reg_pd,p.id_mk", "m.guid,mk.guid"),
686: "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"),
687: "prerequisite" => "",
688: "filter" => "e.isignore=0 and m.isignore=0 and mk.isignore=0 and not isnull(m.guid)",
689: "infotambahanerror" => "nim,matakuliah",
690: "order by" => "nim,matakuliah",
691: "forcedouble" => "",
692: "tahunakademikinjectdipakai" => -1
693: )
694: ),
695: "nilai krs" => array( array( "guid" => array("id_kls,id_reg_pd", "k.guidkrs"),
696: "variable" => array("p.id_kls,p.id_reg_pd", "p.guid,m.guid"),
697: "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"),
698: "prerequisite" => "",
699: "filter" => "m.isignore=0 and p.isignore=0 and k.isignore=0",
700: "infotambahanerror" => "nim, kodematakuliah, p.kdpenawaran",
701: "order by" => "nim",
702: "forcedouble" => "",
703: "tahunakademikinjectdipakai" => -1
704: )
705: ),
706: "nilai update" => array( array( "guid" => array("id_kls,id_reg_pd", "k.guidnilai"),
707: "variable" => array("p.id_kls,p.id_reg_pd", "p.guid,m.guid"),
708: "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"),
709: "prerequisite" => "",
710: "filter" => "m.isignore=0 and p.isignore=0 and k.isignore=0 and (k.nilai<='E' or k.nilai='T') and not isnull(guidkrs)",
711: "infotambahanerror" => "nim",
712: "order by" => "nim",
713: "forcedouble" => "",
714: "tahunakademikinjectdipakai" => -1
715: )
716: ),
717: "ajar_dosen" => array( array( "guid" => array("id_ajar", "tt.guid"),
718: "variable" => array("p.id_reg_ptk,k.id_kls", "id_reg_ptk,pm.guid"),
719: "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_sdm=p.guiddosen and pn.tahun=floor(tt.kdtahunakademik/10))", "update" => "ak_timteaching tt join ak_penawaranmatakuliah pm on pm.kdpenawaran=tt.kdpenawaran"),
720: "prerequisite" => "",
721: "filter" => "tt.isignore=0 and pm.isignore=0",
722: "infotambahanerror" => "",
723: "order by" => "",
724: "forcedouble" => "",
725: "tahunakademikinjectdipakai" => 2
726: ),
727: array( "guid" => array("id_ajar", "tt.guid"),
728: "variable" => array("p.id_reg_ptk,k.id_kls", "id_reg_ptk,pm.guid"),
729: "table" => array("check" => "ak_timteaching_lab tt join ak_kelompok k on k.kdkelompok=tt.kdkelompok join ak_penawaranmatakuliah pm on pm.kdpenawaran=k.kdpenawaran join pt_person p on p.kdperson=tt.kdpersonepsbed join ak_penugasan pn on (pn.id_sdm=p.guiddosen and pn.tahun=floor(tt.kdtahunakademik/10))", "update" => "ak_timteaching_lab tt join ak_kelompok k on k.kdkelompok=tt.kdkelompok join ak_penawaranmatakuliah pm on pm.kdpenawaran=k.kdpenawaran"),
730: "prerequisite" => "",
731: "filter" => "tt.isignore=0 and pm.isignore=0",
732: "infotambahanerror" => "",
733: "order by" => "",
734: "forcedouble" => "",
735: "tahunakademikinjectdipakai" => 2
736: )
737: )
738: );
739: }
740:
741: /**
742: * pemetaan update NIDN di Institusi, mengambil data dari PDDIKTI
743: * <br/> indeks:
744: * <br/> - updatenidn
745: * <br/> isi:
746: * <br/> array("table" => array(nama_tabel_institusi, nama_tabel_pddikti), "nidn" => array(nama_kolom_nidn_institusi, nama_kolom_nidn_pddikti), "guid" => guid, "filter" => filter)
747: * <br/> dimana:
748: * <br/> table--- : nama tabel institusi dan PDDIKTI. berupa array()
749: * <br/> nidn---- : nama kolom nidn institusi dan PDDIKTI. berupa array()
750: * <br/> guid---- : nama kolom PDDIKTI yang terdapat kunci primer dari dosen
751: * <br/> filter-- : filter untuk tabel institusi
752: * <br/> catatan:
753: * <br/> - hanya 1 array
754: */
755: private function peta_updatenidn()
756: {
757: $this->peta["updatenidn"] = array ( "table" => array("dosen", "ak_dosen"),
758: "nidn" => array("nidn", "nidn"),
759: "guid" => "id_ptk",
760: "filter" => "kdperson in (select kdperson from pt_person where guidperson=:guid)"
761: );
762: }
763:
764: /**
765: * mengembalikan pemetaan antara tabel dan kolom PDDIKTI dengan Institusi untuk keperluan pengecekan, sinkronisasi, injeksi dan ekstraksi data
766: * @return string
767: * - pemetaan antara tabel dan kolom PDDIKTI dengan Institusi
768: */
769: function peta()
770: {
771: if (!$this->isdipetakan)
772: {
773: $this->peta_kolom();
774: $this->peta_tabel();
775: $this->peta_injek();
776: $this->peta_injek_usang();
777: $this->peta_injek_perbaiki_usang();
778: $this->peta_ekstrak();
779: $this->peta_pk();
780: $this->peta_guid();
781: $this->peta_updatenidn();
782: }
783: return $this->peta;
784: }
785: }
786: ?>