import appuifw, e32, e32db import os, time dbpath = "e:\\okodz" itemdb = "okodzitem.db" class Item: inout_list = [unicode("支出", "utf-8"), unicode("収入", "utf-8")] def __init__(self, r = None): if r: self.itemstr = r.col(1) self.inout = r.col(2) self.itemnum = r.col(3) # registered time else: self.itemstr = unicode('分類なし', 'utf-8') self.inout = 0 self.itemnum = 0 return def get_form(self): result = [(unicode("分類","utf-8"), 'text', self.itemstr), \ (unicode("支出/収入","utf-8"), 'combo', (self.inout_list, self.inout))] return result def set_from_form(self, form): self.itemstr = form[0][2] self.inout = form[1][2][1] self.itemnum = int(time.time()) return def sql_update(self, old): sql = "UPDATE items SET itemstr = '%s', inout = %d WHERE itemnum = %d"% \ (self.itemstr, self.inout, old.itemnum) return unicode(sql) def sql_add(self): sql = "INSERT INTO items (itemstr, inout, itemnum) VALUES ('%s', %d, %d)"%(self.itemstr, self.inout, self.itemnum) return unicode(sql) def sql_delete(self): if self.itemnum != 0: sql1 = "DELETE FROM items WHERE itemnum = %d"%(self.itemnum) # sql2 = "UPDATE events SET item = 0 WHERE item = %d"%(self.itemnum) return unicode(sql1) else: appuifw.note(unicode("この項目は削除できません",'utf-8'), 'error') return '' class ItemList: sql_create = u"CREATE TABLE items (itemstr VARCHAR, inout INTEGER, itemnum INTEGER)" def __init__(self, db_name): try: self.native_db = e32db.Dbms() self.native_db.open(db_name) except: self.native_db.create(db_name) self.native_db.open(db_name) self.native_db.execute(self.sql_create) item0 = Item() self.add(item0) return def get_all_items(self): dbv = e32db.Db_view() dbv.prepare(self.native_db, u"SELECT * from items WHERE NOT itemstr = '' ORDER BY itemnum DESC") dbv.first_line() results = [] for i in range(dbv.count_line()): dbv.get_line() i = Item(dbv) results.append(i) dbv.next_line() return results def modify(self, i, oi): self.native_db.execute(i.sql_update(oi)) return def add(self, i): self.native_db.execute(i.sql_add()) return def delete(self, i): sql1 = i.sql_delete() if sql1: self.native_db.execute(sql1) # if sql2: g_OkodzDB.execute(sql2) return def close(self): self.native_db.close() return #endclass class App: sql_create = u"CREATE TABLE events (date TIMESTAMP, amount INTEGER, item INTEGER, comment VARCHAR)" def __init__(self): self.lock = e32.Ao_lock() self.lock2 = e32.Ao_lock() self.lock3 = e32.Ao_lock() self.exit_flag = self.exit_flag2 = self.exit_flag3 = False appuifw.app.exit_key_handler = self.abort self.menu_createdb = (unicode("新規",'utf-8'), self.handle_createdb) appuifw.app.menu = [self.menu_createdb] return def handle_createdb(self): input = appuifw.query(u"Enter db-filename","text") if not input : return db_name = os.path.join(dbpath, input + ".db") #.encode('utf-8') if input and not os.path.exists(db_name.encode('utf-8')): native_db = e32db.Dbms() native_db.create(db_name) native_db.open(db_name) native_db.execute(self.sql_create) self.show_dbselect_view() return def abort(self): # exit application self.exit_flag = True self.lock.signal() return def abort2(self): # exit main view self.exit_flag2 = True self.lock2.signal() return def abort3(self): # exit item view self.exit_flag3 = True self.lock3.signal() return def initialize_db(self, db_name): self.okodz = Okodz(db_name) return def initialize_itemdb(self, db_name): self.okodzitem = ItemList(db_name) return def unknown_category_check(self, entry_list): z = [] # unknown payment category search. results = [] for e in entry_list: if not g_item_expenses_all.has_key(e.itemnum): z.append(e.itemnum) e.itemnum = 0 results.append(e) #for zi in z: # sql = "UPDATE events SET item = 0 WHERE item = %d"%zi return results def update_entry_list(self): self.entry_list = self.unknown_category_check(self.okodz.get_all_entries()) return def update_item_list(self): self.item_list = self.okodzitem.get_all_items() global g_item_expenses_all g_item_expenses_all = {} for item in self.item_list: if item.inout == 1: sign = '+' else: sign = '-' g_item_expenses_all[item.itemnum] = (item.itemstr, sign) return def show_item_view(self): self.update_item_list() content = [] for item in self.item_list: content.append((item.itemstr, item.inout_list[item.inout])) self.item_view.set_list(content) appuifw.app.menu = [self.menu_additem, self.menu_deleteitem, self.menu_abort3] return def show_main_view(self): self.update_item_list() self.update_entry_list() if not self.entry_list: content = [(unicode("(空)","utf-8"), u"")] else: datestr = unicode("%d/%d/%d %02d:%02d:%02d", 'utf-8') content = [] for item in self.entry_list: (yr, mo, da, h, m, s, wd, jd, ds) = time.localtime(item.unixtime()) content.append( (unicode(datestr%(yr,mo,da,h,m,s)), item.get_item_text()+item.get_amount())) self.main_view.set_list(content) if not self.entry_list: appuifw.app.menu = [self.menu_add, self.menu_edititem, self.menu_abort] else: appuifw.app.menu = [self.menu_add, self.menu_delete, self.menu_summary, self.menu_edititem, self.menu_csvwrite, self.menu_abort] return def show_dbselect_view(self): self.L2 = [] self.nodbfile = True for filename in os.listdir(dbpath): if os.path.splitext(filename)[-1] == ".db" and filename != itemdb : self.L2.append(unicode(filename,"utf-8")) self.nodbfile = False if not self.L2: self.L2 = [u"(No Databese File.)"] self.lb = appuifw.Listbox(self.L2, self.handler_L2) appuifw.app.body = self.lb return def handle_edititem(self): self.menu_additem = (unicode("追加",'utf-8'), self.handle_additem) self.menu_abort3 = (unicode("終了",'utf-8'), self.abort3) self.menu_deleteitem = (unicode("削除",'utf-8'), self.handle_deleteitem) self.item_view = appuifw.Listbox([(u"Loading...", u"")], self.handle_view_item) appuifw.app.menu = [] appuifw.app.body = self.item_view while not self.exit_flag3: appuifw.app.exit_key_handler = self.abort3 self.show_item_view() self.lock3.wait() self.exit_flag3 = False appuifw.app.exit_key_handler = self.abort2 appuifw.app.body = self.main_view self.show_main_view() return def handler_L2(self): selectedfile = self.L2[self.lb.current()] if self.nodbfile: return self.initialize_db(os.path.join(dbpath, selectedfile)) self.main_view = appuifw.Listbox([(u"Loading...", u"")], self.handle_view_entry) appuifw.app.body = self.main_view self.entry_list = [] self.menu_add = (unicode("追加",'utf-8'), self.handle_add) self.menu_abort = (unicode("終了",'utf-8'), self.abort) self.menu_summary = (unicode("集計",'utf-8'), self.handle_summary) self.menu_delete = (unicode("削除",'utf-8'), self.handle_delete) self.menu_csvwrite = (unicode("CSV書き出し",'utf-8'), self.handle_csvwrite) self.menu_edititem = (unicode("項目編集",'utf-8'), self.handle_edititem) appuifw.app.menu = [] while not self.exit_flag2: appuifw.app.exit_key_handler = self.abort2 self.show_main_view() self.lock2.wait() self.exit_flag2 = False appuifw.app.exit_key_handler = self.abort self.menu_createdb = (unicode("新規",'utf-8'), self.handle_createdb) appuifw.app.menu = [self.menu_createdb] appuifw.app.body = self.lb return def handle_csvwrite2(self, date_range): filename = os.path.join(dbpath, appuifw.query(u"Enter filename","text")) + ".txt" filename = filename.encode('utf-8') if os.path.isdir(filename): appuifw.note(u"Directry already exists as that name.") return if os.path.exists(filename): overwrite = appuifw.query(u"Already exists. Overwrite?",'query') if not overwrite: appuifw.note(u"canceled.") return fp = open(filename, "w") for item in self.entry_list: if item.timestamp < date_range.begintime or \ item.timestamp > date_range.endtime: continue (yr, mo, da, h, m, s, wd, jd, ds) = time.localtime(item.timestamp) writeentry = u'%d/%d/%d, %02d:%02d:%02d, "%s", %d, "%s"'%( yr, mo, da, h, m, s, g_item_expenses_all[item.itemnum][0], item.amount, item.comment) print >>fp, writeentry.encode("utf-8") fp.close() appuifw.note(unicode("%sに書き出しました"%filename,"utf-8"), 'info') return def handle_csvwrite(self): date_range = DateRange() data = date_range.get_form(self.entry_list[-1], self.entry_list[0]) flags = appuifw.FFormEditModeOnly f = appuifw.Form(data, flags) a = f.execute() date_range.set_from_form(f) self.handle_csvwrite2(date_range) self.lock.signal() return def handle_additem(self): new_item = Item() data = new_item.get_form() flags = appuifw.FFormEditModeOnly f = appuifw.Form(data, flags) f.execute() new_item.set_from_form(f) if new_item.itemstr != '': self.okodzitem.add(new_item) self.lock3.signal() return def handle_deleteitem(self): index = self.item_view.current() self.okodzitem.delete(self.item_list[index]) self.update_item_list() self.lock3.signal() return def handle_add(self): new_entry = Entry() data = new_entry.get_form() flags = appuifw.FFormEditModeOnly f = appuifw.Form(data, flags) f.execute() new_entry.set_from_form(f) if new_entry.amount != 0: self.okodz.add(new_entry) self.lock2.signal() return def handle_delete(self): if self.entry_list: index = self.main_view.current() if appuifw.query(unicode("削除しますか？",'utf-8'), 'query'): self.okodz.delete(self.entry_list[index]) self.lock2.signal() return def handle_summary(self): #item_expenses_all = self.entry_list[0].item_expenses_all #itemnum = len(g_item_expenses_all.keys()) sums = {} for k in g_item_expenses_all.keys(): sums[k] = 0 for e in self.entry_list: sums[e.itemnum] += e.amount self.show_summary(g_item_expenses_all, sums) self.lock2.signal() return def show_summary(self, item_expenses_all, sums): data = [] total = 0 #item_expenses = map(lambda x: x[0], g_item_expenses_all) #item_sign = map(lambda x: x[1], g_item_expenses_all) #for item, sign, amount_sum in zip(item_expenses, item_sign, sums): for k in g_item_expenses_all.keys(): try: amount_sum = sums[k] except KeyError: amount_sum = 0 sign = g_item_expenses_all[k][1] if sign == '+': total -= amount_sum elif sign == '-': total += amount_sum data.append(((unicode("合計","utf-8"), 'text', unicode(str(total),'utf-8')))) for k in g_item_expenses_all.keys(): #for item, sign, amount_sum in zip(item_expenses, item_sign, sums): data.append((g_item_expenses_all[k][0], 'number', sums[k])) flags = appuifw.FFormViewModeOnly f = appuifw.Form(data, flags) f.execute() return def handle_view_entry(self): if self.entry_list: index = self.main_view.current() self.show_entry(self.entry_list[index]) self.lock2.signal() return def handle_view_item(self): if self.item_list: index = self.item_view.current() self.show_item(self.item_list[index]) self.lock3.signal() return def show_entry(self, entry): data = entry.get_form() modified = Entry() flags = appuifw.FFormEditModeOnly f = appuifw.Form(data, flags) f.execute() modified.set_from_form(f) self.okodz.modify(modified, entry) return def show_item(self, item): data = item.get_form() modified = Item() flags = appuifw.FFormEditModeOnly f = appuifw.Form(data, flags) f.execute() modified.set_from_form(f) self.okodzitem.modify(modified, item) return def run(self): while not self.exit_flag: self.show_dbselect_view() self.lock.wait() self.close() return def close(self): appuifw.app.menu = [] appuifw.app.body = None appuifw.app.exit_key_handler = None self.exit_flag = False return #endclass class DateRange: def __init__(self): return def getDate(self, utime): (yr, mo, da, h, m, s, wd, jd, ds) = \ time.localtime(utime) m += 60*h s += 60*m return float(utime-s) def get_form(self, begin_entry, end_entry): begintime = begin_entry.unixtime() endtime = end_entry.unixtime() result = [(unicode("開始","utf-8"), 'date', self.getDate(begintime)),\ (unicode("終了","utf-8"), 'date', self.getDate(endtime))] return result def set_from_form(self, form): self.begintime = form[0][2] self.endtime = form[1][2]+86399 return #endclass class Entry: def __init__(self, r = None): if r: self.timestamp = r.col(1) self.amount = r.col(2) self.itemnum = r.col(3) self.comment = r.col(4) else: self.timestamp = time.time() self.amount = 0 self.itemnum = 0 self.comment = u"" return def get_form(self): # conv unixtime -> form acceptable (yr, mo, da, h, m, s, wd, jd, ds) = \ time.localtime(self.timestamp) m += 60*h s += 60*m result = [(unicode("日付","utf-8"), 'date', float(self.timestamp - s)), \ (unicode("時間","utf-8"), 'time', float(s)), \ (unicode("金額","utf-8"), 'number', int(self.amount)), ] item_expenses = map(lambda x: x[0], g_item_expenses_all.values()) if self.itemnum == 0: result.append((unicode("分類","utf-8"), 'combo', (item_expenses, 0))) else: result.append((unicode("分類","utf-8"), 'combo', (item_expenses, g_item_expenses_all.keys().index(self.itemnum)))) result.append((unicode("備考","utf-8"), 'text', self.comment)) return result def set_from_form(self, form): self.timestamp = form[0][2] + form[1][2] self.amount = form[2][2] self.itemnum = g_item_expenses_all.keys()[form[3][2][1]] self.comment = form[4][2] return def sql_update(self, old): sql = "UPDATE events SET date = #%s#, amount = %d, item = %d, comment = '%s' WHERE date = #%s# AND amount = %d and item = %d "% \ (e32db.format_time(self.timestamp),self.amount, self.itemnum, self.comment, e32db.format_time(old.timestamp), old.amount, old.itemnum) return unicode(sql) def sql_add(self): sql = "INSERT INTO events (date, amount, item, comment) VALUES (#%s#, %d, %d, '%s')" \ %(e32db.format_time(self.timestamp),self.amount, self.itemnum, self.comment) return unicode(sql) def sql_delete(self): sql = "DELETE FROM events WHERE date=#%s#"%e32db.format_time(self.timestamp) return unicode(sql) def unixtime(self): return self.timestamp def get_item_text(self): ret = g_item_expenses_all[self.itemnum][0] return ret def get_amount(self): return unicode(" %d円"%self.amount, 'utf-8') #endclass class Okodz: def __init__(self, db_name): try: self.native_db = e32db.Dbms() self.native_db.open(db_name) except: self.native_db.create(db_name) self.native_db.open(db_name) self.native_db.execute(Entry.sql_create) # global g_OkodzDB # g_OkodzDB = self.native_db return def get_all_entries(self): dbv = e32db.Db_view() dbv.prepare(self.native_db, u"SELECT * from events ORDER BY date DESC") dbv.first_line() results = [] for i in range(dbv.count_line()): dbv.get_line() e = Entry(dbv) results.append(e) dbv.next_line() return results def modify(self, e, oe): self.native_db.execute(e.sql_update(oe)) return def add(self, e): self.native_db.execute(e.sql_add()) return def delete(self, e): self.native_db.execute(e.sql_delete()) return def close(self): self.native_db.close() return #endclass # ---- main ---- def main(): app = App() if os.path.isdir(dbpath): pass else: try: os.mkdir(dbpath) except: import sys print >>sys.stderr, "Invalid dbpathdir." return app.initialize_itemdb(unicode(os.path.join(dbpath, itemdb),"utf-8")) app.run() return if __name__ == "__main__": appuifw.app.title = unicode("おこづぱい", "utf-8") e32.ao_yield() try: main() except: import sys; sys.exit()