def dispalyDataForLastYearsKelases (self, level, row):
# last years classes
grid = self.grid
sql ="SELECT f.id, f.name, s.name \
FROM forms f \
JOIn schools ON s.id = f.school_id \
WHERE f.course_level=%d AND f.schYr =%d" % (level, gVar.schYr-1)
classes = fetch.getAllDict(sql)
if classes:
self.labelRowKelas(row)
grid.AppendRows(1); row += 1
##rintrow, 'D rows=', grid.GetNumberRows()
tot_now, tot_out, tot_cont, tot_retake, sub_tot, tot_tot = (0, 0, 0, 0, 0, 0)
for myClass in classes:
form_id = myClass['id']
class_name = myClass['name']
now = fetch.batchPopulation(form_id) #GetClassPopulation
tot_now +=now
cont = fetch.numberOfStudents_reregistering(form_id)
tot_cont +=cont
out = fetch.numberOfStudents_leaving(form_id)
tot_out += out
retake = fetch.numberOfStudents_retaking(form_id)
tot_retake += retake
sub_tot = cont + retake
tot_tot += sub_tot
grid.SetRowAttr(row, self.attrDetailsKelas)
grid.SetCellValue( row, 0, "Form")
grid.SetCellValue( row, 1, str(form_id))
grid.SetCellValue( row, 2, class_name)
grid.SetCellValue( row, 3, str(now))
grid.SetCellValue( row, 4, str(out))
grid.SetCellValue( row, 5, str(cont))
grid.SetCellValue( row, 6, str(retake))
grid.SetCellValue( row, 7, str(sub_tot))
grid.AppendRows(1); row += 1
##rintrow, 'E rows=', grid.GetNumberRows()
# display totals
grid.SetRowAttr(row, self.attrTotalsForm)
grid.SetCellValue( row, 0, "totals")
grid.SetCellValue( row, 3, str(tot_now))
grid.SetCellValue( row, 4, str(tot_out))
grid.SetCellValue( row, 5, str(tot_cont))
grid.SetCellValue( row, 6, str(tot_retake))
grid.SetCellValue( row, 7, str(tot_tot))
grid.AppendRows(1); row += 1
##rintrow, 'F rows=', grid.GetNumberRows()
return row
def getCourseIDSForNewYear(self, level):
sql ="SELECT c.id, c.name \
FROM courses c \
JOIN courses_by_year cby ON c.id = cby.course_id \
WHERE cby.schYr = %d \
AND c.level=%d" % (gVar.schYr, int(level))
res = fetch.getAllDict(sql)
##rint sql, res
return res
def main(self):
self.name=''
heading = (("ID",0), ("Contact Name",105), ("Type",100), ("Students",100),('',0))
self.vList.SetColumns(heading)
sql = "SELECT Kode, NamaA, NamaI FROM OrangTua"
res = fetch.getAllDict(sql)
self.contacts = {}
l = []
for r in res:
##rintr
k = r['Kode']+10000
a = r['NamaA']
i = r['NamaI']
if a:
l.append((k,a,'Father'))
self.contacts[k]=a
if i:
k = k + 10000
l.append((k,i,'Mother'))
self.contacts[k]=i
sql = "SELECT Kode, Nama FROM Wali"
res = fetch.getAllDict(sql)
for r in res:
k = r['Kode']
n = r['Nama']
if a:
l.append((k,n,'Guardian'))
self.contacts[k] = n
sorted(l,key=lambda x: x[1])
self.updateList(self.contacts)
def OnPaymentItemSelected(self, evt):
pid = self.payments_list.GetSelectedID()
index = self.payments_list.GetFirstSelected()
reciptNo = self.payments_list.getColumnText(index, 1)
if self.pay_records:
txt = "Record: %d/%d" % (index+1, self.pay_records)
else: txt = 'No Records'
self.txt_ctrl_pay_records.SetValue(txt)
sql = "SELECT ii.product_id, ii.name, ii.other, \
ii.date_from, ii.date_to, ii.month_from, pd.month_to, \
ii.price, Format(int(pd.amount),'#,###') AS amount, ii.item_name \
FROM invoices i \
JOIN invoice_items ii ON i.id = ii.invoice_id \
WHERE i.ck_ref='%s'" % reciptNo # Format(int(invoice_items.amount),'#,###')
mylist = fetch.getAllDict(sql)
index = 0
myDict = {}
for row in mylist:
name_value = [index, ]
# prepare name
item_name =row['item_name']
if item_name:
name_value.append(item_name)
# perpare value
name_value.append(row['amount'])
#name_value[0] = index
name_value = tuple(name_value)
myDict[index] = name_value
index +=1
self.details_list.SetItemMap(myDict)
self.detail_records = len(myDict)
if myDict:
txt = "Record: 1/%d" % self.detail_records
else: txt = 'No Records'
self.txt_ctrl_details_records.SetValue(txt)
sql = "SELECT Format(SUM(amount),'#,###') \
FROM invoice_items \
WHERE ck_ref='%s'" % reciptNo
#rintsql
self.txt_ctrl_details_total.SetValue(str(fetch.getStr(sql)))
def displayData(self):
self.list_ctrl.DeleteAllItems()
for school_id in [1,2,3,4]:
self.list_ctrl.Append(('School','','','',''))
course_ids = fetch.courses_forSchool_forYear(school_id, gVar.schYr)
#rint'course_ids ', course_ids
for course_id in course_ids:
self.list_ctrl.Append(('Course','','','',''))
sql = "SELECT fee \
FROM scgool_fees \
WHERE schYr = %d AND %d IN (course_ids)" % ( gVar.schYr, course_id)
res = fetch.getAllDict(sql)
#rintsql, res
for fee in res:
self.list_ctrl.Append(('Fee','2','3','4','5'))
def invoice_details(self, invoice_id):
sql = "SELECT item_name, qnty, price, total_amount \
FROM acc_invoice_items \
WHERE invoice_id =%d \
ORDER BY id" % invoice_id
item_list = fetch.getAllDict(sql)
sql = "SELECT i.amount, i.ck_ref, i.date, i.schYr, s.name, s.NoInduk, f.name AS form_name \
FROM acc_invoices i \
JOIN students s ON s.id=i.student_id \
JOIN students_by_form sbf ON sbf.student_id = s.id \
JOIN forms f ON sbf.form_id = f.id \
WHERE i.id=%d" % invoice_id
inv_details = fetch.getOneDict(sql)
return (inv_details, item_list)
def listItems(self, grid, debit):
sql = "SELECT p.Kode, p.Nama, Jumlah \
FROM ((Jurnal AS j \
INNER JOIN JurnalD AS jd ON j.Kode = jd.KJurnal) \
INNER JOIN Perkiraan AS p ON p.Kode = jd.KPerkiraan) \
WHERE j.Kode =%d \
AND jd.Debet=%s" % (self.jurnal_id, debit)
res = fetch.getAllDict(sql)
idx = 0; mylist = {}
for row in res:
KPerkiraan = row['Kode']
sectionCode = self.formatSectionCode(KPerkiraan)
Jumlah = format(row['Jumlah'], '0,.0f') #Jumlah = row['Jumlah']
details = "%s : %s" % (sectionCode, row['Nama'])
mylist[idx] = (KPerkiraan, details, Jumlah)
idx += 1
grid.Populate(mylist.items())
def displaySchoolDetails(self):
sql = (
"SELECT * FROM schools \
WHERE is = %d"
% gVar.school_id
)
res = fetch.getAllDict(sql)
# rint 'displaySchoolDetails' , res
if not res:
return
address = res["address"]
school_name = res["name"]
school_type = res["type"]
isCK = res["isCK"]
self.text_ctrl_school_name.SetValue(school_name)
self.choice_type.SetSelection(min_level)
# self.text_ctrls_address.SetValue(max_level)
self.checkbox_is_ck.SetValue(isCK)
def OnLogin(self, evt):
import hashlib
import os
name = self.text_ctrl_name.GetValue()
password = str(self.text_ctrl_password.GetValue())
# salt = os.urandom(16)
m = hashlib.md5()
m.update(password) # (salt + password)
x = m.hexdigest()
sql = "SELECT * FROM users WHERE name ='%s' AND password='******'" % (name, password)
# rintsql
# rintfetch.getAllDict(sql)
if fetch.getAllDict(sql): # change to check and set up user privilages
# temp
gVar.userIsAdmin = True
self.GetTopLevelParent().loggedIn()
def displayData(self):
self.student_id = student_id = gVar.student_id
#return
#rint"panel_edit_booking : displayData"
self.clearCtrls()
if student_id == 0:
self.enableCtrls(True)
self.button_edit.SetLabelText('Cancel')
sql = "SELECT MAX (id) FROM students"
student_id = fetch.getDig(sql)+1
self.label_ctrl_booking_id.SetLabelText(str(student_id))
else:
self.button_save.Hide()
self.button_edit.SetLabelText('Edit')
self.enableCtrls(False)
loadCmb.courses_forYear(self.choice_joining_course, gVar.schYr)
self.text_ctrl_student_name.Enable(student_id == 0)
if student_id:
sql = "SELECT * FROM students WHERE student_id = %d" % int(student_id)
res = fetch.getOneDict(sql)
name = res['name']
student_id = res['student_id']
gender = res['gender']
register_course_id = res['register_course_id']
registration_status = res['reg_status']
dob = res['dob']
schYr = res['register_schYr']
self.label_heading.SetLabelText('Editing Booking For:')
self.label_ctrl_booking_id.SetLabelText(str(student_id))
self.text_ctrl_student_name.SetValue(name)
if gender:
self.choice_gender.SetSelection(0)
else:
self.choice_gender.SetSelection(1)
self.date_dob.SetValue(dob)
course_id = res['register_course_id']
loadCmb.restore(self.choice_joining_course, course_id)
#KSekolahPindah = res['KSekolahPindah']
#loadCmb.restore(self.choice_previous_school, KSekolahPindah)
#if not registration_status: registration_status = 0
loadCmb.restore(self.choice_status, registration_status)
sql = "SELECT * \
FROM acc_invoices i \
JOIN acc_invoice_items ii ON ii.invoice_id = i.id \
WHERE i.student_id = %d ORDER BY (ck_ref)" % gVar.student_id
res = fetch.getAllDict(sql)
mystr =""
for r in res:
k = r.pop('student_id')
ck_ref = r.pop('ck_ref')
mystr += '%s : %s \n' % ('ck_ref', ck_ref)
date = r.pop('Tanggal')
mystr += '%s : %s \n' % ('Date', date)
for key in r:
val = r[key]
if val:
l = '%s : %s \n' % (key, r[key])
mystr += l
mystr += "\n"
self.text_ctrl_notes.SetValue(mystr)
"""
self.date_booking_fee = DateCtrl(self.panel_booking, -1)
self.text_ctrl_booking_receiptNo = wx.TextCtrl(self.panel_booking, -1, "")
self.date_test = DateCtrl(self.panel_booking, -1)
self.choice_result_test = wx.Choice(self.panel_booking, -1, choices=["A: Strong", "B: Good", "C: Medium", "D:Weak", "E:Very Weak"])
self.date_retest = DateCtrl(self.panel_booking, -1)
self.choice_result_retest = wx.Choice(self.panel_booking, -1, choices=["Acceptable", "Not Acceptable"])
self.date_offer_letter_sent = DateCtrl(self.panel_booking, -1)
self.text_ctrl_offer_ref = wx.TextCtrl(self.panel_booking, -1, "")
self.date_offer_accepted = DateCtrl(self.panel_booking, -1)
self.text_ctrl_accept_receiptNo = wx.TextCtrl(self.panel_booking, -1, "")
self.text_ctrl_notes = wx.TextCtrl(self.panel_notes, -1, "", style=wx.TE_MULTILINE)"""
else:
self.enableCtrls()
def displayData(self):
account_id, datefrom, dateto = self.getVars()
#rint'displayData', account_id, datefrom, dateto
if datefrom and dateto:
sql = "SELECT j.id, j.transaction_date, j.title, j.details, \
FORMAT(IIF (ji.debit = True, ji.amount,), '#,##0') AS Debit , \
FORMAT(IIF (ji.debit = False, ji.amount,), '#,##0') AS Credit \
FROM acc_journal j \
JOIN acc_journal_items d ON j.id = ji.journal_id \
JOIN acc_supliers s ON j.supplier_id = s.id \
WHERE j.transaction_date BETWEEN '%s' AND '%s' \
AND j.account_id = %d \
ORDER BY j.transaction_date ASC " % (datefrom, dateto, account_id)
elif datefrom and not dateto:
sql = "SELECT j.id, j.transaction_date, j.title, j.details, \
FORMAT(IIF (ji.debit = True, ji.amount,), '#,##0') AS Debit , \
FORMAT(IIF (ji.debit = False, ji.amount,), '#,##0') AS Credit \
FROM acc_journal j \
JOIN acc_journal_items d ON j.id = ji.journal_id \
JOIN acc_supliers s ON j.supplier_id = s.id \
WHERE j.transaction_date => '%s' \
AND j.account_id = %d \
ORDER BY j.transaction_date ASC" % (datefrom, account_id)
elif not datefrom and dateto:
sql = "SELECT j.id, j.transaction_date, j.title, j.details, \
FORMAT(IIF (ji.debit = True, ji.amount,), '#,##0') AS Debit , \
FORMAT(IIF (ji.debit = False, ji.amount,), '#,##0') AS Credit \
FROM acc_journal j \
JOIN acc_journal_items d ON j.id = ji.journal_id \
JOIN acc_supliers s ON j.supplier_id = s.id \
WHERE j.transaction_date <= '%s' \
AND j.account_id = %d \
ORDER BY j.transaction_date ASC" % (dateto, account_id)
else:
sql = "SELECT j.id, j.transaction_date, j.title, j.details, \
FORMAT(IIF (ji.debit = True, ji.amount,), '#,##0') AS Debit , \
FORMAT(IIF (ji.debit = False, ji.amount,), '#,##0') AS Credit \
FROM acc_journal j \
JOIN acc_journal_items d ON j.id = ji.journal_id \
JOIN acc_supliers s ON j.supplier_id = s.id \
AND j.account_id = %d \
ORDER BY j.transaction_date ASC" % (account_id,)
res = fetch.getAllDict(sql)
self.records = len(res)
#rint sql, self.records
#rint res
if self.records:
txt = "Record: 1/%d " % self.records
else:
txt = "Record: 0/0 "
self.text_ctrl_records.SetLabelText(txt)
self.records = len(res)
gridrows = self.grid.GetNumberRows()
pos = 0
if gridrows > self.records:
extrarows = gridrows - self.records
self.grid.DeleteRows(pos, extrarows)
else:
extrarows = self.records - gridrows
self.grid.InsertRows(pos, extrarows)
self.Layout()
rowNo = 0
for row in res:
#rint row
Kode, date, Transaksi, Keterangan, Debit, Credit = row['id'], row['Expr1001'], row['title'], row['details'], row['Debit'], row['Credit'],
self.grid.SetRowLabelValue(rowNo, '')
self.grid.SetCellValue(rowNo, 0, str(Kode))
self.grid.SetCellValue(rowNo, 1, str(date))
self.grid.SetCellValue(rowNo, 2, str(Transaksi)+str(Keterangan))
#self.grid.SetCellValue(rowNo, 3, str(Keterangan))
self.grid.SetCellValue(rowNo, 4, str(Debit))
self.grid.SetCellValue(rowNo, 5, str(Credit))
self.grid.SetCellAlignment(rowNo, 4, wx.ALIGN_RIGHT,wx.ALIGN_RIGHT)
self.grid.SetCellAlignment(rowNo, 5, wx.ALIGN_RIGHT,wx.ALIGN_RIGHT)
rowNo += 1
self.text_ctrl_opening_balance.SetValue(self.digitgroup(self._getStartBalance(account_id, datefrom, dateto )))
self.text_ctrl_closing_balance.SetValue(self.digitgroup(self._GetEndBalance(account_id, datefrom, dateto )))
credit = self.digitgroup(self._sumCredit(account_id, datefrom, dateto ))
debit = self.digitgroup(self._sumDebit(account_id, datefrom, dateto ))
self.text_ctrl_credit_total.SetValue(credit)
self.text_ctrl_debit_total.SetValue(debit)