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)