def add_route(db: AccountDatabase, route_map: RouteMap): # add route to database db.add_colume('ROUTE CHAR(32)', 'ABBR CHAR(32)') stm = 'UPDATE TEMP SET ROUTE=?, ABBR=? WHERE SCHOOL=?' for school, (route, abbr) in route_map.schools.items(): db.cur.execute(stm, (route, abbr, school)) # ckeck for non-routed schools cur = db.select('DISTINCT SCHOOL, ROUTE') to_del = [] for i in cur: if not i[1]: utils.log('学校“{}”无对应路线,已丢弃其所有记录'.format(i[0])) to_del.append(i[0]) for i in to_del: db.cur.execute('DELETE FROM TEMP WHERE SCHOOL={}'.format(repr(i))) db.conn.commit() # add order routes = [None] * len(route_map.rt_idx) for r, i in route_map.rt_idx.items(): routes[i] = r db.add_order('ROUTE', routes) schools = [None] * len(route_map.sc_idx) for s, i in route_map.sc_idx.items(): schools[i] = s db.add_order('SCHOOL', schools)
def handle(manifest: Sheet, client_list: Sheet, does_sales: bool, does_annually: bool, does_monthly: bool): db = AccountDatabase(manifest, ARGS, 10) workbook = xlwt.Workbook(encoding='utf-8') db.add_order('SALES', SALESMEN) db.add_order('KIND', KINDS) # add y, m, d to table dates = [i[0] for i in db.distinct('DATE')] db.add_colume('YEAR INT', 'MONTH INT', 'DAY INT') for i in dates: t = time.strptime(i, '%Y-%m-%d') s = 'YEAR={}, MONTH={}, DAY={}'.format(t.tm_year, t.tm_mon, t.tm_mday) db.update(s, 'DATE={}'.format(repr(i))) years = db.sorted_one('YEAR') months = db.sorted_one('MONTH', 'YEAR={}'.format(years[-1])) # add basic_flag to table db.add_colume('BASIC INT') for i in KINDS: db.update('BASIC=1', 'KIND={}'.format(repr(i))) # add sales_map to table if does_sales or does_monthly: db.add_colume('SALES CHAR(16)') db.client_map = make_client_map(client_list) clients = [i[0] for i in db.distinct('CLIENT')] for cli in clients: sal = db.client_map.setdefault(cli, '其他') if sal == '其他': log('客户“{}”无对应业务员,归为“其他”'.format(cli)) db.update('SALES={}'.format(repr(sal)), 'CLIENT={}'.format(repr(cli))) db.sales_map = {} for cli, sal in db.client_map.items(): db.sales_map.setdefault(sal, []).append(cli) for clis in db.sales_map.values(): clis.sort() # write salesmen if does_sales: sales = db.sorted_one('SALES') for s in sales: db.set_where('SALES={}'.format(repr(s))) salesman(workbook.add_sheet(s), db) # write anually if does_annually: for y in years: db.set_where('YEAR={}'.format(y)) annually(workbook.add_sheet('{}总(料型)'.format(y)), db) # write monthly if does_monthly: for y in years: db.set_where('YEAR={}'.format(y)) monthly(workbook.add_sheet('{}总(客户)'.format(y)), db) y, m = years[-1], months[-1] cur = db.distinct('DAY', 'YEAR={} AND MONTH={}'.format(y, m)) d = max([i[0] for i in cur]) path = '{:02d}.{:02d}.{:02d}-销量邮件表.xls'.format(y % 100, m, d) workbook.save(path)