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)