def monthly(sheet: Worksheet, db: AccountDatabase):
y = db.distinct('YEAR').__next__()[0]
header = (['单位全名', '邮编'] + ['{}月合计'.format(i + 1)
for i in range(12)] + ['年合计'])
for i, j in enumerate(header):
sheet.write(0, i, j, BORDC)
sheet.col(i).width_mismatch = True
sheet.col(i).width = 3400 if i < 1 else 2100
clients = []
for cli in db.sales_map.values():
clients += cli
clients.sort()
for i, cli in enumerate(clients, 1):
sheet.write(i, 0, cli, BORD)
sheet.write(i, 1, db.client_map[cli], BORDC)
rsum = 0
for j in range(1, 13):
whr = 'MONTH={} AND CLIENT={} AND BASIC=1'.format(j, repr(cli))
val = db.select('SUM(WEIGHT)', whr).__next__()[0]
val = val / 1000 if val else 0
sheet.write(i, j + 1, val if val else '', BORD)
rsum += val if val else 0
sheet.write(i, 14, rsum, BORD)
def salesman(sheet: Worksheet, db: AccountDatabase):
s = db.distinct('SALES').__next__()[0]
clients = db.sorted_one('CLIENT')
for i, j in enumerate(HEADER):
sheet.write(0, i, j, BORDC)
sheet.col(i).width_mismatch = True
sheet.col(i).width = 3400 if i < 1 else 2100
nrow = 1
write_sales(sheet, db, s, nrow)
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)