def addColumns_toGuardians(): sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'OrangTua'" res = fetch.getAllDict(sql) for row in res: COLUMN_NAME = row['COLUMN_NAME'] #rintCOLUMN_NAME sql = "ALTER TABLE guardians ADD %s INT(4)" % COLUMN_NAME
def update_father_mother_guardian_id_into_students(): sql = " SELECT id, KOrangTua, KWali FROM students" res = fetch.getAllDict(sql) for row in res: sid = row['id'] KOrangTua = row['KOrangTua'] KWali = row['KWali'] #rint'Student:', sid, 'KOrangTua:', KOrangTua, ' KWali:', KWali if KWali: sql = "SELECT new_id FROM Wali WHERE Kode = %d" % KWali #rintsql new_id = fetch.getDig(sql) sql = "UPDATE students SET guardian_id = %d WHERE id =%d" % (new_id, sid) fetch.updateDB(sql) if KOrangTua: sql = "SELECT father_id, mother_id FROM OrangTua WHERE Kode = %d" % KOrangTua #rintsql res = fetch.getOneDict(sql) father_id = res['father_id'] mother_id = res['mother_id'] sql = "UPDATE students \ SET father_id = %d, mother_id =%d \ WHERE id = %d" % (father_id, mother_id, sid) fetch.updateDB(sql)
def upadateForms_setStaff_id(): sql = "SELECT id, NoInduk FROM staff" res = fetch.getAllDict(sql) for row in res: id = row['id'] NoInduk = row['NoInduk'] sql = "UPDATE forms SET staff_id = %d WHERE "
def addColumnsToStudents(): sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'siswa'" res = fetch.getAllDict(sql) #rintres[0] for row in res: COLUMN_NAME = row['COLUMN_NAME'] sql = "ALTER TABLE students ADD %s INT(1)" % COLUMN_NAME #rintsql fetch.c.execute(sql) fetch.mySQLconn.commit()
def fixDobStudent(): sql = "SELECT id, dob FROM students " res = fetch.getAllDict(sql) for row in res: sid = row['id'] dob = row['dob'] ##rint'sid', sid, ' dob:',dob if not dob : dob ='0000-00-00' sql = "UPDATE students SET dob2 = '%s' WHERE id =%d" % (dob, int(sid)) ##rintsql try: #rintfetch.updateDB(sql) except:
def listItems(self, grid, debit): sql = "SELECT p.id, p.name, j.amount \ FROM acc_journal j \ JOIN acc_journal_items jd ON j.id = jd.journal_id) \ JOIN acc_accounts p ON p.id = jd.account_id) \ WHERE j.id =%d \ AND jd.debit=%s" % (self.journal_id, debit) res = fetch.getAllDict(sql) idx = 0; mylist = {} for row in res: account_id = row['id'] sectionCode = self.formatSectionCode(account_id) total = format(row['amount'], '0,.0f') #amount = row['amount'] details = "%s : %s" % (sectionCode, row['name']) mylist[idx] = (account_id, details, total) idx += 1 grid.Populate(mylist.items())
def showData(self): """ TYPE CLASSNAME CURRENT_POPULATION LEAVING RETAKE CONTINUE TOTAL CLASS_SIZE CLASSES PENDING_BOOKINGS notes heading CLASSNAME CURRENT_POPULATION LEAVING RETAKE CONTINUE kelas ClassName CurrentPopulation Leaving Retake Continue kelas ClassName CurrentPopulation Leaving Retake Continue tot Totals CurrentPopulation Leaving Retake Continue heading NEXTCLASSNAME NEW CONTINUE TOTAL CLASS_SIZE CLASSES PENDING_BOOKINGS course nextclassname New Continue tot Pending_Bookings course nextclassname New Continue tot Pending_Bookings space """ mylist = [] mylist.append(("TYPE", "FORM NAME", "POP.NOW", "OUT", "RETAKE", "CONT.", "TOTAL", "FORM SIZE", "FORMS", "PENDING BOOKINGS", "NOTES") ) # list of course ids for year sql ="SELECT courses \ FROM courses_by_year \ WHERE schYr = %s" % gVar.schYr course_ids = fetch.getList(sql) sql ="SELECT level \ FROM courses_levels \ ORDER BY course_level" course_levels = fetch.getList(sql) for level in course_levels: for course_id in course_ids: sql ="SELECT id, name, school_id \ FROM courses \ WHERE level=%d AND id =%d" % (int(level), int(course_id)) res = fetch.getAllDict(sql) for row in res: mylist.append(row) #rintmylist for level in range(3,20): # show last years classes & students sql ="SELECT id, name, school_id \ FROM forms \ WHERE level=%d AND schYr =%d" % (level, gVar.schYr-1) forms = fetch.getAllDict(sql) level_totals =[0,0,0,0,0] for myForm in forms: form_id = myForm[0] now = fetch.formPopulation(form_id) #GetClassPopulation level_totals[0]+=now cont = 4 #GetRereg level_totals[1]+=cont out = 2 #GetStudentsLeaving level_totals[2]+=out redo = 1 #GetStudentsRetaking level_totals[3]+=redo subtot = cont-out+redo level_totals[4]+=subtot index = self.listCtrl.Append((myForm[1], now, cont, out, redo, subtot)) self.listCtrl.SetItemData(index, form_id) self.listCtrl.Append(('Totals', str(level_totals[0]), str(level_totals[1]), str(level_totals[2]), str(level_totals[3]), str(level_totals[4]))) # list of Kelas for this sch yr sql ="SELECT c.id, c.name \ FROM courses c \ JOIN students ON s.join_course_id = c.id) \ WHERE s.register_schYr = %d AND c.level=%d" % (gVar.schYr, level ) res2 = fetch.getAllDict(sql) for row in res2: self.listCtrl.Append(('New reg','','','')) #sql ="SELECT * FROM cSiswa WHERE TahunAjaran = %d AND Kelas =%d" % (gVar.schYr, ) return for course_id in results: sql ="SELECT id, school_id, level, name \ FROM courses \ WHERE id =%d" % int(course_id) course = fetch.getOneDict(sql) mylist.append(course) mylist.sort(key=lambda tup: tup[1]) mylist = [(tup[1], tup) for tup in mylist] school_ids = list(set(mylist)) sql ="SELECT level, name \ FROM courses \ ORDER BY level, name" results = fetch.getAllDict(sql)
def move_fromOrangTua_toGuardians(): #sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'wali'" #res = fetch.getAllDict(sql) ##rintres #sql = "TRUNCATE guardians" #fetch.updateDB(sql) # fathers sql = "SELECT Kode, NamaA, TempatLahirA,TgLahirA,AgamaA,KewarganegaraanA,\ PekerjaanA,PekerjaanLainA,AlamatA,WilayahA,TeleponA,HPA FROM OrangTua " res = fetch.getAllDict(sql) for row in res: # #rint'row', row Kode = row['Kode'] Nama = row['NamaA'] TempatLahir = row['TempatLahirA'] TgLahir = row['TgLahirA'] Agama = row['AgamaA'] Kewarganegaraan = row['KewarganegaraanA'] Pekerjaan = row['PekerjaanA'] PekerjaanLain = row['PekerjaanLainA'] Alamat = row['AlamatA'] Wilayah = row['WilayahA'] Telepon = row['TeleponA'] HP = row['HPA'] if not Kode : Kode = 0 if not Nama : Nama = '' if not TempatLahir : TempatLahir = '' if TgLahir: #rint'TgLahir' #TgLahir = fetch.convertDate_fromAccess_toMYSQL(TgLahir) else: TgLahir = '0000-00-00' if not Kewarganegaraan : Kewarganegaraan='' if not PekerjaanLain : PekerjaanLain='' if not Alamat : Alamat = '' if not Wilayah : Wilayah = '' if not Telepon : Telepon = '' if not HP : HP = '' if not Agama : Agama=0 if not Pekerjaan : Pekerjaan=0 sql = "INSERT INTO guardians (\ Kode, Nama, TempatLahir, TgLahir, Agama, Kewarganegaraan,\ Pekerjaan, PekerjaanLain, Alamat, \ Wilayah, Telepon, HP ) \ VALUES (%d, '%s', '%s', '%s', %d, '%s', \ %d, '%s', '%s', \ '%s', '%s', '%s')" % ( Kode, Nama, TempatLahir, TgLahir, Agama, Kewarganegaraan, Pekerjaan, PekerjaanLain, Alamat, Wilayah, Telepon, HP) #rintsql inserted_id = fetch.updateDB(sql) #rintinserted_id sql = "UPDATE OrangTua SET father_id = %d WHERE Kode=%d" % (inserted_id, Kode) #rintsql fetch.updateDB(sql) # mothers sql = "SELECT Kode, NamaI, TempatLahirI,TgLahirI,AgamaI,KewarganegaraanI,\ PekerjaanI, PekerjaanLainI, AlamatA, SamaDenganAyah, AlamatI, WilayahI, TeleponI, HPI FROM OrangTua " res = fetch.getAllDict(sql) for row in res: # #rint'row', row Kode = row['Kode'] Nama = row['NamaI'] TempatLahir = row['TempatLahirI'] TgLahir = row['TgLahirI'] Agama = row['AgamaI'] Kewarganegaraan = row['KewarganegaraanI'] Pekerjaan = row['PekerjaanI'] PekerjaanLain = row['PekerjaanLainI'] AlamatA = row['AlamatA'] SamaAyah = row['SamaDenganAyah'] if SamaAyah: Alamat = row['AlamatA'] else: Alamat = row['AlamatI'] Wilayah = row['WilayahI'] Telepon = row['TeleponI'] HP = row['HPI'] if not Kode : Kode = 0 if not Nama : Nama = '' if not TempatLahir : TempatLahir = '' if TgLahir: #rint'TgLahir' #TgLahir = fetch.convertDate_fromAccess_toMYSQL(TgLahir) else: TgLahir = '0000-00-00' if not Kewarganegaraan : Kewarganegaraan='' if not PekerjaanLain : PekerjaanLain='' if not Alamat : Alamat = '' if not Wilayah : Wilayah = '' if not Telepon : Telepon = '' if not HP : HP = '' if not Agama : Agama=0 if not Pekerjaan : Pekerjaan=0 sql = "INSERT INTO guardians (\ Kode, Nama, TempatLahir, TgLahir, Agama, Kewarganegaraan,\ Pekerjaan, PekerjaanLain, Alamat, \ Wilayah, Telepon, HP ) \ VALUES (%d, '%s', '%s', '%s', %d, '%s', \ %d, '%s', '%s', \ '%s', '%s', '%s')" % ( Kode, Nama, TempatLahir, TgLahir, Agama, Kewarganegaraan, Pekerjaan, PekerjaanLain, Alamat, Wilayah, Telepon, HP) #rintsql inserted_id = fetch.updateDB(sql) #rintinserted_id sql = "UPDATE OrangTua \ SET mother_id = %d \ WHERE Kode=%d" % (inserted_id, Kode) #rintsql fetch.updateDB(sql)
def move_fromWali_toGuardians(): #sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'wali'" #res = fetch.getAllDict(sql) ##rintres sql = "TRUNCATE guardians" fetch.updateDB(sql) sql = "SELECT Kode, Nama, TempatLahir,TgLahir,Agama,Kewarganegaraan,\ Pekerjaan,PekerjaanLain,Alamat,Wilayah,Telepon,HP FROM wali " res = fetch.getAllDict(sql) for row in res: # #rint'row', row Kode = row['Kode'] Nama = row['Nama'] TempatLahir = row['TempatLahir'] TgLahir = row['TgLahir'] Agama = row['Agama'] Kewarganegaraan = row['Kewarganegaraan'] Pekerjaan = row['Pekerjaan'] PekerjaanLain = row['PekerjaanLain'] Alamat = row['Alamat'] Wilayah = row['Wilayah'] Telepon = row['Telepon'] HP = row['HP'] if not Kode : Kode = 0 if not Nama : Nama = '' if not TempatLahir : TempatLahir = '' if TgLahir: #rint'TgLahir' #TgLahir = fetch.convertDate_fromAccess_toMYSQL(TgLahir) else: TgLahir = '0000-00-00' if not Kewarganegaraan : Kewarganegaraan='' if not PekerjaanLain : PekerjaanLain='' if not Alamat : Alamat = '' if not Wilayah : Wilayah = '' if not Telepon : Telepon = '' if not HP : HP = '' if not Agama : Agama=0 if not Pekerjaan : Pekerjaan=0 sql = "INSERT INTO guardians (\ Kode, Nama, TempatLahir, TgLahir, Agama, Kewarganegaraan,\ Pekerjaan, PekerjaanLain, Alamat, \ Wilayah, Telepon, HP ) \ VALUES (%d, '%s', '%s', '%s', %d, '%s', \ %d, '%s', '%s', \ '%s', '%s', '%s')" % ( Kode, Nama, TempatLahir, TgLahir, Agama, Kewarganegaraan, Pekerjaan, PekerjaanLain, Alamat, Wilayah, Telepon, HP) #rintsql inserted_id = fetch.updateDB(sql) #rintinserted_id sql = "UPDATE Wali SET new_id = %d WHERE Kode=%d" % (inserted_id, Kode) #rintsql fetch.updateDB(sql)
def moveStudents(): sql = "TRUNCATE students " fetch.updateDB(sql) sql = "SELECT * FROM siswa" res = fetch.getAllDict(sql) for row in res: NISN = row['NISN'] Nama = row['Nama'] Panggilan = row['Panggilan'] CKID = row['CKID'] Pria = row['Pria'] Agama = row['Agama'] Kapal = row['Kapal'] AnakKe = row['AnakKe'] TgLahir = row['TgLahir'] TempatLahir = row['TempatLahir'] NoInduk = row['NoInduk'] HP = row['HP'] GolDarah = row['GolDarah'] KOrangTua = row['KOrangTua'] KWali = row['KWali'] HubunganWali = row['HubunganWali'] TinggalDengan = row['TinggalDengan'] PergiDengan = row['PergiDengan'] TgKeluar = row['TgKeluar'] NoSKHUN = row['NoSKHUN'] TahunSKHUN = row['TahunSKHUN'] NoSTLTamat = row['NoSTLTamat'] NoSTLAsal = row['NoSTLAsal'] TahunSTLAsal = row['TahunSTLAsal'] TahunTamat = row['TahunTamat'] TgDiterima = row['TgDiterima'] KKelasDiterima = row['KKelasDiterima'] SaudaraTiri = row['SaudaraTiri'] SaudaraAngkat = row['SaudaraAngkat'] SaudaraKandung = row['SaudaraKandung'] AlasanPindah = row['AlasanPindah'] NoSuratPindah = row['NoSuratPindah'] AlasanKeluar = row['AlasanKeluar'] KSekolahPindah = row['KSekolahPindah'] KSekolahAsal = row['KSekolahAsal'] Deposito = row['Deposito'] MedicalRecord = row['MedicalRecord'] BookForYear = row['BookForYear'] if not CKID: CKID=0 a = "%s, %d, %s," % ( NISN, int(CKID), NoInduk,) TgLahir = fetch.convertDate_fromAccess_toMYSQL(TgLahir) #rint'TgLahir', TgLahir b = "%s, %s, %s, %s," % (Nama, Panggilan, TgLahir, TempatLahir,) if Pria =='N': Pria = 0 elif Pria =='Y': Pria = 1 else: Pria = 1 if not GolDarah: GolDarah=0 if not Agama: Agama = 0 if not Kapal : Kapal= 0 c = "%d, %s, %d, %d, %d," % (int(Pria), HP, int(Kapal), int(GolDarah), int(Agama),) if not KOrangTua: KOrangTua =0 if not KWali: KWali = 0 if not TinggalDengan: TinggalDengan = 0 if not PergiDengan: PergiDengan = 0 d = "%d, %d, %s, %d, %d, " % (int(KOrangTua), int(KWali), HubunganWali, int(TinggalDengan), int(PergiDengan),) if not SaudaraKandung:SaudaraKandung=0 if not SaudaraTiri:SaudaraTiri=0 if not SaudaraAngkat:SaudaraAngkat=0 if not AnakKe:AnakKe=0 e = "%d, %d, %d, %d," % (int(SaudaraKandung), int(SaudaraTiri), int(SaudaraAngkat), int(AnakKe),) if not KSekolahAsal: KSekolahAsal = 0 if not KKelasDiterima:KKelasDiterima = 0 TgDiterima = fetch.convertDate_fromAccess_toMYSQL(TgDiterima) f = "%d, %s, %d, " % (int(KSekolahAsal), TgDiterima, int(KKelasDiterima),) if not KSekolahPindah:KSekolahPindah = 0 g = "%s, %d, %s," % (NoSuratPindah, int(KSekolahPindah), AlasanPindah,) if not TahunSTLAsal: TahunSTLAsal = 0 if not TahunTamat : TahunTamat = 0 h = "%d, %s, %s, %d," % (int(TahunSTLAsal), NoSTLAsal, NoSTLTamat, int(TahunTamat),) if not TahunSKHUN: TahunSKHUN = 0 if not Deposito: Deposito = 0 TgKeluar = fetch.convertDate_fromAccess_toMYSQL(TgKeluar) i = "%s, %d, %s, %s, %d, " % (NoSKHUN, int(TahunSKHUN), AlasanKeluar, TgKeluar, int(Deposito),) if not BookForYear: BookForYear = 0 j = "%s, %s" % (MedicalRecord, int(BookForYear)) sql = "INSERT INTO students ( \ NISN, CKID, NoInduk, \ name, callname, dob, pob, \ gender, hp, ship_id, blood_type_id, faith_id, \ KOrangTua, KWali, guardian_relationship, lives_with, travels_with, \ siblings_by_birth, siblings_step, siblings_adopted, child_no, \ previous_school_id, join_date, join_class_id, \ transfer_out_ref, next_school_id, join_reason, \ YrSTL, NoSTL, NoGraduateSTL, YrGraduate, \ NoSKHUN, YrSKHUN, exit_reason, exit_date, Deposito, \ medicalRecord, book_for_yer) \ VALUES ('%s', %d, '%s', \ '%s', '%s', '%s', '%s', \ %d, '%s', %d, %d, %d, \ %d, %d, '%s', %d, %d, \ %d, %d, %d, %d, \ %d, '%s', %d, \ '%s', %d, '%s', \ %d, '%s', '%s', %d, \ '%s', %d, '%s', '%s', %d, \ '%s', '%s')" % ( NISN, int(CKID), NoInduk, Nama, Panggilan, TgLahir, TempatLahir, int(Pria), HP, int(Kapal), int(GolDarah), int(Agama), int(KOrangTua), int(KWali), HubunganWali, int(TinggalDengan), int(PergiDengan), int(SaudaraKandung), int(SaudaraTiri), int(SaudaraAngkat), int(AnakKe), int(KSekolahAsal), TgDiterima, int(KKelasDiterima), NoSuratPindah, int(KSekolahPindah), AlasanPindah, int(TahunSTLAsal), NoSTLAsal, NoSTLTamat, int(TahunTamat), NoSKHUN, int(TahunSKHUN), AlasanKeluar, TgKeluar, int(Deposito), MedicalRecord, BookForYear) #rintsql fetch.updateDB(sql)