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)
