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]
