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 moveAccounts():
sql = "TRUNCATE acc_accounts"
fetch.updateDB(sql)
#rint'moveAccounts'
sql = "SELECT Kode, Nama, Saldo, Kunci FROM Perkiraan"
res = getAllCol(sql)
#rintsql, res
for row in res:
#rint'row', row
Kode, Nama, Saldo, Kunci = row
sql = "INSERT INTO acc_accounts (code, name, balance, locked) \
VALUES ('%s', '%s', %d, %d)" % (Kode, Nama, Saldo, Kunci)
#rintsql
fetch.updateDB(sql)
# moveAccounts()
def updateAddressIDs():
sql = "SELECT id, aiID, nextItemID FROM address_items"
res = fetch.getAllCol(sql)
i = 0
for row in res:
iid, aiID, nextItemID = row
sql = "SELECT id FROM address_items WHERE aiID = %d" % nextItemID
##rintsql
next_item_id = fetch.getDig(sql)
sql = "UPDATE address_items \
SET next_item_id = %d \
WHERE id = %d" % (next_item_id, iid)
##rintsql
fetch.updateDB(sql)
def update(self, excul_id, activity_id, employee_id=0):
sql = " SELECT id FROM excul \
WHERE id = %d \
AND exculset_id = %d \
AND activity_id = %d \
AND employee_id = %d" % (self.exculset_id, excul_id, activity_id, employee_id)
if fetch.getAll_dict(sql):
pass
#rint'DlgExculActivityListEditor > match found = do nothing'
else:
sql ="UPDATE excul \
SET activity_id = %d, employee_id = %d \
WHERE id = %d" % (activity_id, employee_id, excul_id)
fetch.updateDB(sql)
def onSave(self, evt):
description = self.text_ctrl_description.GetValue()
price = int(self.text_ctrl_price.GetValue())
if not description or not price:
fetch.msg("Price or description missing ")
elif fetch.is_unique_product(sql):
fetch.msg('description not unique')
else:
sql = "INSERT INTO acc_products \
(description, price) \
VALUES ('%s', %d)" % (
description, price)
#rintsql
fetch.updateDB(sql)
self.Destroy()
def moveSiswaPerKelas():
sql = "TRUNCATE students_by_form"
fetch.updateDB(sql)
sql = "SELECT KKelas, NoInduk FROM SiswaPerKelas WHERE TahunAjaran > 2013"
res = getAllDict(sql)
#rintsql, res
for row in res:
form_id = row['KKelas']
NoInduk = row['NoInduk']
sql = "SELECT id FROM students WHERE NoInduk = '%s'" % NoInduk
student_id = fetch.getDig(sql)
sql = "INSERT INTO students_by_form (form_id, student_id) VALUES (%d, %d)" % (form_id, student_id)
#rintsql
fetch.updateDB(sql)
def OnNewDivision(self, evt):
dlg = wx.TextEntryDialog(self, 'division name', 'New division name', '')
if dlg.ShowModal() == wx.ID_OK:
new_name = dlg.GetValue().strip()
if new_name:
sql = "SELECT name \
FROM acc_divisions \
WHERE name = '%s'" % new_name
if fetch.getCount(sql):
msg = "Sorry division '%s' already exists" % new_name
fetch.msg(msg)
else:
sql = "INSERT INTO acc_divisions \
(name) \
VALUES ('%s')" % (new_name)
#rintsql
fetch.updateDB(sql)
self.loadDivisions()
loadCmb.restore_str(self.choice_division, new_name)
dlg.Destroy()
def OnNewSupplier(self, evt):
#rint"open dlgTextEntry 'new supplier' "
dlg = wx.TextEntryDialog(self, 'Supplier Name', 'New Supplier Name', '')
if dlg.ShowModal() == wx.ID_OK:
new_name = dlg.GetValue().strip()
if new_name:
sql = "SELECT name \
FROM acc_suppliers \
WHERE name = '%s'" % new_name
if fetch.getCount(sql):
msg = "Sorry supplier '%s' already exists" % new_name
fetch.msg(msg)
else:
sql = "INSERT INTO acc_suppliers \
(name) \
VALUES ('%s')" % new_name
fetch.updateDB(sql)
self.loadSuppliers()
loadCmb.restore_str(self.choice_supplier, new_name)
def moveAddressItems():
sql = "TRUNCATE address_items "
#rintsql
fetch.c.execute(sql)
fetch.mySQLconn.commit()
sql = "SELECT id, itemName, itemType, nextItemID, postcode \
FROM addressItems"
#rintsql
cursor.execute(sql)
res = cursor.fetchall()
i = 0
for row in res:
iid, itemName, itemType, nextItemID, postcode = row
sql = "INSERT INTO address_items \
(aiID, name, type, next_item_id, postcode) \
VALUES (%d, '%s','%s',%d,'%s')" % (
iid, itemName, itemType, nextItemID, str(postcode))
#rintsql
fetch.updateDB(sql)
def moveForms():
sql = "TRUNCATE forms"
fetch.updateDB(sql)
sql = "SELECT Kode, TahunAjaran, Nama, Sekolah, NoIndukStaf, course_level \
FROM Kelas WHERE Kode > 0"
res = getAllDict(sql)
#rintlen(res)
for row in res:
name = row['Nama']
level = row['course_level']
school_id = row['Sekolah']
NoIndukStaf = row['NoIndukStaf']
staff_id = getStaff_id(NoIndukStaf)
schYr = row['TahunAjaran']
KKelas = row['Kode']
sql = "INSERT INTO forms (name, level, school_id, staff_id, schYr, KKelas) \
VALUES ('%s', %d, %d, %d, %d, %d)" % (name, level, school_id, staff_id, schYr, KKelas)
#rintsql
fetch.updateDB(sql)
def insert(self, activity_id, employee_id):
sql = " INSERT INTO excul \
SET exculset_id = %d, activity_id = %d , employee_id = %d" % (
self.exculset_id, activity_id, employee_id)
fetch.updateDB(sql)
def moveStaff():
sql = "TRUNCATE staff"
fetch.updateDB(sql)
sql = "SELECT ID, Nama, Agama, TgMulai, \
NoInduk, Alamat, Telepon, HP, Gaji, \
TempatLahir, FullTime, TipeGaji, Deposito, KDivisi, \
KJabatan, Pria \
FROM Staf "
res = getAllDict(sql)
#rintsql
for row in res:
ID = row['ID']
if not ID: ID = 0
name = row['Nama']
if not name: name = ''
if name == "Ahmad Rafi'i (EC. Design HG)":
name = "Ahmad Rafi`i (EC. Design HG)"
faith_id = row['Agama']
if not faith_id: faith_id = 0
join_date = row['TgMulai']
if not join_date: join_date = '0000-00-00'
NoInduk = row['NoInduk']
if not NoInduk:NoInduk = ''
address = row['Alamat']
if not address : address = ''
telp = row['Telepon']
if not telp: telp = ''
hp = row['HP']
if not hp: hp = ''
salary = row['Gaji']
if not salary :salary = 0
pob = row['TempatLahir']
if not pob:pob = ''
fulltime = row['FullTime']
if not fulltime:fulltime = 0
salary_type_id = row['TipeGaji']
if not salary_type_id:salary_type_id = 0
deposit = row['Deposito']
if not deposit: deposit= 0
division_id = row['KDivisi']
if not division_id:division_id = 0
position_id = row['KJabatan']
if not position_id: position_id = 0
gender = row['Pria']
if gender == 'N': gender = 0
else: gender = 1
sql = "INSERT INTO staff \
(name, faith_id, join_date, NoInduk, \
address, telp, hp, salary, pob, fulltime, \
salary_type_id, deposit, division_id, position_id, gender ) \
VALUES ('%s', %d, '%s', '%s', \
'%s', '%s', '%s', %d, '%s', %d, \
%d, %d, %d, %d, %d)" % (
name, int(faith_id), join_date, NoInduk,
address, telp, hp, int(salary), pob, int(fulltime),
int(salary_type_id), int(deposit), int(division_id), int(position_id), gender )
#rintsql
fetch.updateDB(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)
