def handle(manifest: Sheet, route: Sheet, does: Iterable[str] = ...): db = AccountDatabase(manifest) path = check_date_get_path(db) workbook = xlwt.Workbook(encoding='utf-8') if does is ...: does = ['routing', 'daily', 'printer'] does = set(does) if 'routing' in does or 'printer' in does: route_map = RouteMap(route) add_route(db, route_map) if 'routing' in does: cur = db.select('DISTINCT ROUTE') routes = route_map.sort_route([i[0] for i in cur if i[0]]) for route in routes: db.set_where('ROUTE={}'.format(repr(route))) distribute(workbook.add_sheet(route), db, route_map) db.set_where() if 'daily' in does: daily_sum(workbook.add_sheet('今日汇总'), db) if 'routing' in does or 'daily' in does: workbook.save(path) if 'printer' in does: text = plain_text(db) with open('标签打印表.txt', 'w', encoding='utf-8') as f: f.write(text)
def handle_school(school: str, db: AccountDatabase, save_path: str): ''' Save one school's data in one workbook. ''' db.set_where('SCHOOL={}'.format(repr(school))) workbook = xlwt.Workbook(encoding='utf-8') pivottable_2d(workbook.add_sheet('日期'), db, 'DATE', 'MEAL') pivottable_2d2(workbook.add_sheet('类别'), db, 'KIND', 'MEAL') pivottable_2d2(workbook.add_sheet('品种'), db, 'NAME', 'MEAL') meals = db.sorted_one('MEAL') for meal in meals: stm = 'SCHOOL={} AND MEAL={}' db.set_where(stm.format(repr(school), repr(meal))) pivottable_3d(workbook.add_sheet('{}每日用餐'.format(meal)), db) workbook.save(save_path)
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)