def getMkKodeFromJadwalID(jadwalid): db = kelas.dbConnectSiap() sql = f'select MKKode from simak_trn_jadwal where JadwalID={jadwalid}' with db: cur = db.cursor() cur.execute(sql) return cur.fetchone()[0]
def insert_transkrip(data_krs_mahasiswa_dictionary): db = kelas.dbConnectSiap() sql=f"INSERT INTO `simpati`.`simak_trn_transkrip`" \ f"(`KRSPRCID`, " \ f"`KRSID`, " \ f"`KHSID`, " \ f"`MhswID`, " \ f"`TahunID`, " \ f"`MKID`, " \ f"`MKKode`, " \ f"`SKS`, " \ f"`GradeNilai`, " \ f"`BobotNilai`, " \ f"`StatusKRSID`, " \ f"`MKSetara`, " \ f"`NA`) " \ f"VALUES " \ f'(DEFAULT, ' \ f'{data_krs_mahasiswa_dictionary["KRSID"]}, ' \ f'{data_krs_mahasiswa_dictionary["KHSID"]}, ' \ f'"{data_krs_mahasiswa_dictionary["MhswID"]}", ' \ f'"{data_krs_mahasiswa_dictionary["TahunID"]}", ' \ f'{data_krs_mahasiswa_dictionary["MKID"]}, ' \ f'"{data_krs_mahasiswa_dictionary["MKKode"]}", ' \ f'{data_krs_mahasiswa_dictionary["SKS"]}, ' \ f'"{data_krs_mahasiswa_dictionary["GradeNilai"]}", ' \ f'{data_krs_mahasiswa_dictionary["BobotNilai"]}, ' \ f'"{data_krs_mahasiswa_dictionary["StatusKRSID"]}", ' \ f'"", ' \ f'"N");' with db: cur = db.cursor() cur.execute(sql)
def getProdiIDAndSingkatan(): db = kelas.dbConnectSiap() sql = f'select ProdiID, Singkatan from simak_mst_prodi' with db: cur = db.cursor() cur.execute(sql) return cur.fetchall()
def getData(tahun, npm): db = kelas.dbConnectSiap() sql = f"SELECT " \ f"simpati.simak_mst_mahasiswa.MhswID, " \ f"simpati.simak_mst_mahasiswa.nama, " \ f"iteungformattanggal(STR_TO_DATE(simpati.simak_mst_mahasiswa.TanggalLahir,'%Y-%m-%d')) AS TGL_LAHIR, " \ f"simpati.simak_mst_mahasiswa.nik, " \ f"simak_trn_ta.Judul, " \ f"sum(simpati.simak_trn_transkrip.sks) as SKS_SMT, " \ f"sum(simpati.simak_trn_transkrip.BobotNilai * simpati.simak_trn_transkrip.sks) as T_bobot," \ f"round (sum(simpati.simak_trn_transkrip.BobotNilai * simpati.simak_trn_transkrip.sks) / sum(simpati.simak_trn_transkrip.sks),2) as IPK_Trans, " \ f"simpati.simak_mst_mahasiswa.StatusMhswID, simak_mst_prodi.Nama, simpati.simak_mst_mahasiswa.TanggalEdit " \ f"FROM simpati.simak_mst_mahasiswa " \ f"inner join simak_mst_prodi on simak_mst_prodi.ProdiID=simak_mst_mahasiswa.ProdiID " \ f"INNER JOIN simak_trn_ta ON simpati.simak_mst_mahasiswa.MhswID=simak_trn_ta.MhswID " \ f"left join simpati.simak_trn_transkrip on simak_mst_mahasiswa.MhswID=simpati.simak_trn_transkrip.MhswID " \ f"where simpati.simak_mst_mahasiswa.TahunID in ('{tahun}') and simpati.simak_mst_mahasiswa.MhswID = {npm} " \ f"group by simak_mst_mahasiswa.MhswID, simak_mst_mahasiswa.Nama, simak_mst_prodi.Nama;" with db: cur = db.cursor() cur.execute(sql) data=cur.fetchone() if data != None: fields = map(lambda x: x[0], cur.description) result = dict(zip(fields, data)) else: result=None return result
def updateDateBimbingan(prodiid, dateupdate): db = kelas.dbConnectSiap() sql = "UPDATE simak_mst_prodi SET `Start` = '{dateupdate}' WHERE `ProdiID` = '{prodiid}'".format( dateupdate=dateupdate, prodiid=prodiid) with db: cur = db.cursor() cur.execute(sql)
def data_student_krs(mhsw_id, tahunid): db = kelas.dbConnectSiap() sql = f'SELECT * from simak_trn_krs WHERE MhswID={mhsw_id} and TahunID={tahunid}' with db: cur = db.cursor() cur.execute(sql) return sql_to_dictionary.fetchAllMode(cur.fetchall(), cur)
def getJadwalIDFullApproval(prodiid): db = kelas.dbConnectSiap() sql = f'select JadwalID from simak_trn_jadwal where TahunID=20192 and BKD_Prodi="true" and BKD_Deputi="true" and ProdiID=".{prodiid}."' with db: cur = db.cursor() cur.execute(sql) return cur.fetchall()
def getProdiName(prodiid): db=kelas.dbConnectSiap() sql=f'select Nama from simak_mst_prodi where ProdiID={prodiid}' with db: cur=db.cursor() cur.execute(sql) return cur.fetchone()[0]
def getProdiIDfromNPM(npm): db = kelas.dbConnectSiap() sql = f'SELECT ProdiID FROM `simak_mst_mahasiswa` where Login={npm}' with db: cur = db.cursor() cur.execute(sql) return cur.fetchone()[0]
def getNamaOrangTua(npm): db=kelas.dbConnectSiap() sql=f'select NamaAyah, NamaIbu, HandphoneOrtu from simak_mst_mahasiswa where MhswID={npm}' with db: cur=db.cursor() cur.execute(sql) return cur.fetchone()
def get_npm_distinct(npm_range_begin, npm_range_end, tahunid): db = kelas.dbConnectSiap() sql = f'SELECT DISTINCT(MhswID) FROM simak_trn_krs WHERE TahunID={tahunid} AND MhswID BETWEEN {npm_range_begin} AND {npm_range_end}' with db: cur = db.cursor() cur.execute(sql) return sql_to_dictionary.fetchAllMode(cur.fetchall(), cur)
def getDosenDataDictionaryDump(kode_dosen): db = kelas.dbConnectSiap() sql = f'select * from simak_mst_dosen where Login="******"' with db: cur = db.cursor() cur.execute(sql) data = cur.fetchone() return sql_to_dictionary.fetchOneMode(data, cur)
def getJadwalIDfromDosenID(dosenid): db = kelas.dbConnectSiap() sql = f'select JadwalID, Nama from simak_trn_jadwal where DosenID="{dosenid}" and TahunID={kelas.getTahunID()}' with db: cur = db.cursor() cur.execute(sql) rows = cur.fetchall() return rows
def getDataMahasiswaAktif(): db = kelas.dbConnectSiap() sql = "select MhswID, Nama, EmailOrtu, HandphoneOrtu, ProdiID from simak_mst_mahasiswa where StatusMhswID='A' AND MhswID between 1133001 and 6194500" with db: cur = db.cursor() cur.execute(sql) rows = cur.fetchall() return rows
def getAlamatLengkapOrtu(npm): db = kelas.dbConnectSiap() sql = f'select AlamatOrtu, RTOrtu, RWOrtu, KotaOrtu, PropinsiOrtu, KodePosOrtu, NegaraOrtu from simak_mst_mahasiswa where MhswID={npm}' with db: cur = db.cursor() cur.execute(sql) rows = cur.fetchone() return f'{rows[0]}, RT{rows[1]}/RW{rows[2]}, {rows[3]}, {rows[4]}, {rows[5]}, {rows[6]}'
def getPekerjaanOrtuIbu(npm): db = kelas.dbConnectSiap() sql = f'select Nama from simak_ref_pekerjaan_ortu where Pekerjaan=(select PekerjaanIbu from simak_mst_mahasiswa where MhswID={npm})' with db: cur = db.cursor() cur.execute(sql) rows = cur.fetchone() return rows[0]
def getTahunID(): db=kelas.dbConnectSiap() sql="SELECT TahunID FROM simpati.simak_mst_tahun where NA = 'N' group by TahunID order by TahunID DESC limit 1" with db: cur=db.cursor() cur.execute(sql) row=cur.fetchone() return row[0]
def getProdiSingkatanFromProdiID(prodiid): db=kelas.dbConnectSiap() sql=f'select Singkatan from simak_mst_prodi where ProdiID={prodiid}' with db: cur=db.cursor() cur.execute(sql) row=cur.fetchone() return row[0]
def ipkMahasiswa(npm): db = kelas.dbConnectSiap() sql = f"SELECT IPK FROM `simak_mst_mahasiswa` where MhswID = '{npm}'" with db: cur = db.cursor() cur.execute(sql) row = cur.fetchone() return str(row[0])
def getPresensiFromJadwalID(presensiID): db = kelas.dbConnectSiap() sql = f'select * from simak_trn_presensi_dosen where JadwalID={presensiID} ORDER BY Pertemuan ASC' with db: cur = db.cursor() cur.execute(sql) rows = cur.fetchall() return rows
def getPresensiDosen(jadwalid, rangepertemuan1, rangepertemuan2): db = kelas.dbConnectSiap() sql = f"select PresensiID from simak_trn_presensi_dosen where TahunID=20192 and JadwalID={jadwalid} and Pertemuan > {rangepertemuan1} and Pertemuan < {rangepertemuan2} ORDER BY Pertemuan ASC" with db: cur = db.cursor() cur.execute(sql) row = cur.fetchall() return row
def cekStatusApproveBAP(jadwalid): db = kelas.dbConnectSiap() sql = f"select BKD_Prodi, BKD_Deputi from simak_trn_jadwal where JadwalID={jadwalid}" with db: cur = db.cursor() cur.execute(sql) row = cur.fetchone() return row
def getDeputiAkademik(): db = kelas.dbConnectSiap() sql = "select Nama from simak_mst_pejabat where JenisJabatanID=9" with db: cur = db.cursor() cur.execute(sql) row = cur.fetchone() return row[0]
def getHomebase(num): num=numbers.normalize(num) db=kelas.dbConnectSiap() sql="select `Nama` from simak_mst_prodi WHERE ProdiID=(select Homebase from simak_mst_dosen WHERE Handphone='{phonenumber}')".format(phonenumber=num) with db: cur=db.cursor() cur.execute(sql) row=cur.fetchone() return row[0]
def getDataMahasiswa(npm): db = kelas.dbConnectSiap() sql = f"select MhswID, Nama from simak_mst_mahasiswa where MhswID='{npm}'" with db: cur = db.cursor() cur.execute(sql) row = cur.fetchone() if row is not None: return row
def getTanggalFromPresensiDosen(jadwalid): db = kelas.dbConnectSiap() sql = "select Tanggal from simak_trn_presensi_dosen WHERE JadwalID={jadwalid} ORDER BY Pertemuan ASC".format( jadwalid=jadwalid) with db: cur = db.cursor() cur.execute(sql) rows = cur.fetchall() return rows
def getListStudent(jadwalid): db = kelas.dbConnectSiap() sql = "select j.JadwalID, j.TahunID, j.MKKode, j.Nama, j.DosenID, krs.MhswID from simak_trn_jadwal as j join simak_trn_krs as krs where j.jadwalid = krs.jadwalid and j.tahunid=20192 and j.JadwalID = '{0}'".format( jadwalid) with db: cur = db.cursor() cur.execute(sql) rows = cur.fetchall() return rows
def getPresensiMahasiswa(presensiid): db = kelas.dbConnectSiap() sql = "select MhswID, JenisPresensiID from simak_trn_presensi_mahasiswa where PresensiID={presensiid} order by MhswID".format( presensiid=presensiid) with db: cur = db.cursor() cur.execute(sql) row = cur.fetchall() return row
def getKaProdi(prodiid): db = kelas.dbConnectSiap() sql = "select Nama from simak_mst_pejabat where ProdiID={prodiid} and JenisJabatanID=5".format( prodiid=prodiid) with db: cur = db.cursor() cur.execute(sql) row = cur.fetchone() return row[0]
def getLecturerMail(lecturercode): db = kelas.dbConnectSiap() sql = "select Email from simak_mst_dosen where Login='******'".format( lecturercode=lecturercode) with db: cur = db.cursor() cur.execute(sql) row = cur.fetchone() return row[0]