def pivottable_2d(sheet: Worksheet, db: AccountDatabase,
vert: str, horz: str):
ys = db.sorted_one(vert)
xs = db.sorted_one(horz)
for i, x in enumerate(xs):
sheet.write(0, i+1, x, HEAD)
sheet.write(0, i+2, '总计', HEAD)
sheet.write(0, 0, '', HEAD)
col_sum = [0] * len(xs)
for i, y in enumerate(ys):
sheet.write(i+1, 0, y, DATE)
row_sum = 0
for j, x in enumerate(xs):
where = '{}={} AND {}={}'.format(vert, repr(y), horz, repr(x))
bsum = db.select('SUM(TOTAL)', where).__next__()[0]
sheet.write(i+1, j+1, bsum if bsum else '', TEXT)
row_sum += bsum if bsum else 0
col_sum[j] += bsum if bsum else 0
sheet.write(i+1, j+2, row_sum, TEXT)
sheet.write(i+2, 0, '总计', TEXT)
for j, s in enumerate(col_sum):
sheet.write(i+2, j+1, s, TEXT)
sheet.write(i+2, j+2, sum(col_sum), TEXT)
def pivottable_2d2(sheet: Worksheet, db: AccountDatabase,
vert: str, horz: str):
ys = db.sorted_one(vert)
xs = db.sorted_one(horz)
for i, x in enumerate(xs):
sheet.write_merge(0, 0, i*2+1, i*2+2, x, HEAD)
sheet.write(1, i*2+1, '数量', HEAD)
sheet.write(1, i*2+2, '货款', HEAD)
sheet.write_merge(0, 1, 0, 0, '', HEAD)
col_sum1 = [0] * len(xs)
col_sum2 = [0] * len(xs)
for i, y in enumerate(ys):
sheet.write(i+2, 0, y, TEXT)
for j, x in enumerate(xs):
where = '{}={} AND {}={}'.format(vert, repr(y), horz, repr(x))
sum1 = db.select('SUM(NUMBER)', where).__next__()[0]
sheet.write(i+2, j*2+1, sum1 if sum1 else '', TEXT)
col_sum1[j] += sum1 if sum1 else 0
sum2 = db.select('SUM(TOTAL)', where).__next__()[0]
sheet.write(i+2, j*2+2, sum2 if sum2 else '', TEXT)
col_sum2[j] += sum2 if sum2 else 0
sheet.write(i+3, 0, '总计', TEXT)
for j, s in enumerate(col_sum1):
sheet.write(i+3, j*2+1, s, TEXT)
for j, s in enumerate(col_sum2):
sheet.write(i+3, j*2+2, s, TEXT)
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 plain_text(db: AccountDatabase) -> str:
header = '线路\t客户名称\t餐类\t货品名称\t数量\t规格\t单位'
data = [header]
for r in db.sorted_one('ROUTE'):
for s in db.sorted_one('ABBR', 'ROUTE={}'.format(repr(r))):
line = [r, s]
tar = 'MEAL, NAME, NUMBER, SPEC, UNIT'
whr = 'ABBR={}'.format(repr(s))
for kind, name, num, spec, unit in db.select(tar, whr):
nums = str(num)
nums = nums[:-2] if nums.endswith('.0') else nums
data.append('\t'.join(line + [kind, name, nums, spec, unit]))
return '\n'.join(data)
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, 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 pivottable_3d(sheet: Worksheet, db: AccountDatabase):
dates = db.sorted_one('DATE')
month = xlrd.xldate_as_tuple(dates[0], 0)[1]
meal = db.select('DISTINCT MEAL').__next__()[0]
school = db.select('DISTINCT SCHOOL').__next__()[0]
title = '横县农村义务教育学生营养改善计划每日开餐情况统计 （{}月{}）'
title = title.format(month, meal)
sheet.write_merge(0, 0, 0, len(dates)+1, title, TITLE)
sheet.write(1, 0, '学校名称：{}'.format(school))
sheet.write(2, 0, '明细', HEAD)
for i, date in enumerate(dates):
xld = xlrd.xldate_as_tuple(date, 0)[1]
sheet.write(2, i+1, date, MONTH)
sheet.col(i+1).width_mismatch = True
sheet.col(i+1).width = 1500
sheet.write(2, i+2, '总计', HEAD)
where = 'DATE={} AND NAME={}'
idx = 3
sheet.write(idx, 0, '大米', SUM)
row_sum = 0
for i, date in enumerate(dates):
cur = db.select('SUM(TOTAL)', where.format(date, repr('大米')))
xs = cur.__next__()[0]
row_sum += xs if xs else 0
sheet.write(idx, i+1, xs if xs else '', SUM)
sheet.write(idx, i+2, row_sum, SUM)
idx += 1
kinds = db.sorted_one('KIND')
for kind in kinds:
names = db.sorted_one('NAME', 'KIND={}'.format(repr(kind)))
col_sum = [0] * len(dates)
for name in names:
row_sum = 0
sheet.write(idx, 0, name, TEXT)
for i, date in enumerate(dates):
cur = db.select('SUM(TOTAL)', where.format(date, repr(name)))
xs = cur.__next__()[0]
row_sum += xs if xs else 0
col_sum[i] += xs if xs else 0
sheet.write(idx, i+1, xs if xs else '', TEXT)
sheet.write(idx, i+2, row_sum, TEXT)
idx += 1
sheet.write(idx, 0, '{}合计'.format(kind), SUM)
for i, s in enumerate(col_sum):
sheet.write(idx, i+1, s, SUM)
sheet.write(idx, i+2, sum(col_sum), SUM)
idx += 1
for i in ['每日合计', '每日开餐人数', '人均开餐金额', '陪餐人数']:
sheet.write(idx, 0, i, TEXT)
for j in range(1, len(dates)+2):
sheet.write(idx, j, '', TEXT)
idx += 1
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 check_date_get_path(db: AccountDatabase) -> str:
cur = db.select('DISTINCT DATE')
dates = [i[0] for i in cur]
if len(dates) == 0:
raise KeyError('未发现数据或不能识别，请打开Excel文件并保存后重试')
if len(dates) > 1:
utils.log('建议一次只处理一天的数据，发现{}天'.format(len(dates)))
date = xlrd.xldate_as_tuple(dates[0], 0)
path = '分拣结果 {:02d}月{:02d}日.xls'.format(date[1], date[2])
return path
def handle(sheet: Sheet, log=print):
'''
Handle accounts of all schools in a month.
'''
log('读入表单数据')
db = AccountDatabase(sheet)
cur = db.select('DISTINCT SCHOOL')
schools = [i[0] for i in cur if i[0]]
cur = db.select('DISTINCT DATE')
dates = [i[0] for i in cur if i[0]]
months = set([xlrd.xldate_as_tuple(i, 0)[1] for i in dates])
assert len(months) == 1, '一次只能处理一个月的数据'
month = '{:02d}月'.format([i for i in months][0])
path = os.path.join('.', month)
if not os.path.exists(path):
os.mkdir(path)
for i, school in enumerate(schools):
fpath = os.path.join(path, '{} {}.xls'.format(school, month))
log('{}/{}：{}'.format(i+1, len(schools), school))
handle_school(school, db, fpath)
def daily_sum(sheet: Worksheet, db: AccountDatabase):
header = ['货品类别', '货品名称', '规格', '数量']
for i, j in enumerate(header):
sheet.write(0, i, j, HEAD)
kinds = db.sorted_one('KIND')
idx = 1
for kind in kinds:
sheet.write(idx, 0, kind, KIND)
cur = db.select('DISTINCT NAME, SPEC', 'KIND={}'.format(repr(kind)))
ns = [i for i in cur]
for name, spec in ns:
where = 'NAME={} AND SPEC={}'.format(repr(name), repr(spec))
num = db.select('SUM(NUMBER)', where).__next__()[0]
sheet.write(idx, 1, name)
sheet.write(idx, 2, spec)
sheet.write(idx, 3, num)
idx += 1
sheet.set_panes_frozen(True)
sheet.set_horz_split_pos(1)
def annually(sheet: Worksheet, db: AccountDatabase):
sheet.write(0, 0, '', BORD)
header = HEADER[2:]
for i, h in enumerate(header, 1):
sheet.write(0, i, h, BORDC)
for i in range(len(header) + 1):
sheet.col(i).width_mismatch = True
sheet.col(i).width = 2100
ssum = [0] * len(header)
for month in range(1, 13):
sheet.write(month, 0, '{}月'.format(month), BORD)
# write basic kinds
ksum = 0
for i, kind in enumerate(KINDS, 1):
whr = 'MONTH={} AND KIND={}'.format(month, repr(kind))
val = db.select('SUM(WEIGHT)', whr).__next__()[0]
val = val / 1000 if val else 0
ksum += val
ssum[i - 1] += val
sheet.write(month, i, val if val else '', BORD)
sheet.write(month, i + 1, ksum if ksum else '', BORD)
ssum[i] += ksum
# write extra kinds
for i, kind in enumerate(EXPANDS, i + 2):
try:
kind = '{}.0'.format(int(kind))
except:
pass
whr = 'MONTH={} AND KIND={}'.format(month, repr(kind))
val = db.select('SUM(WEIGHT)', whr).__next__()[0]
val = val / 1000 if val else 0
ssum[i - 1] += val
sheet.write(month, i, val if val else '', BORD)
# write col sum
sheet.write(13, 0, '合计', UBORDC)
for i, val in enumerate(ssum, 1):
sheet.write(13, i, val)
def write_sales(sheet: Worksheet, db: AccountDatabase, sales: str,
nrow: int) -> int:
ssum = [0] * (len(HEADER) - 2)
for cli in db.sales_map[sales]:
sheet.write(nrow, 0, cli, BORD)
sheet.write(nrow, 1, sales, BORDC)
# write basic kinds
ksum = 0
for i, kind in enumerate(KINDS, 2):
whr = 'CLIENT={} AND KIND={}'.format(repr(cli), repr(kind))
val = db.select('SUM(WEIGHT)', whr).__next__()[0]
val = val / 1000 if val else 0
ksum += val
ssum[i - 2] += val
sheet.write(nrow, i, val if val else '', BORD)
sheet.write(nrow, i + 1, ksum if ksum else '', BORD)
ssum[i - 1] += ksum
# write extra kinds
for i, kind in enumerate(EXPANDS, i + 2):
try:
kind = '{}.0'.format(int(kind))
except:
pass
whr = 'CLIENT={} AND KIND={}'.format(repr(cli), repr(kind))
val = db.select('SUM(WEIGHT)', whr).__next__()[0]
val = val / 1000 if val else 0
ssum[i - 2] += val
sheet.write(nrow, i, val if val else '', BORD)
nrow += 1
# write sales sum
sheet.write(nrow, 1, '合计', UBORDC)
for i, val in enumerate(ssum, 2):
sheet.write(nrow, i, val)
nrow += 2
return nrow
def distribute(sheet: Worksheet, db: AccountDatabase, route_map: RouteMap):
cur = db.select('DISTINCT ROUTE')
route = cur.__next__()[0]
sheet.write(0, 0, route, SCHOOL)
sheet.write(0, 1, '规格', SCHOOL)
# write goods tags in kinds' order
kinds = db.sorted_one('KIND')
goods = []
for kind in kinds:
cur = db.select('DISTINCT NAME, SPEC', 'KIND={}'.format(repr(kind)))
goods += list(cur)
for i, (good, spec) in enumerate(goods):
sheet.write(i + 1, 0, good, TEXT)
sheet.write(i + 1, 1, spec, TEXT)
goods_sum = [0] * len(goods)
# write schools in appearing order
cur = db.select('DISTINCT SCHOOL')
schools = route_map.sort_school([i[0] for i in cur])
for i, school in enumerate(schools, 1):
sheet.col(i + 1).width_mismatch = True
sheet.col(i + 1).width = 2000
sheet.write(0, i + 1, route_map.schools[school][1], SCHOOL)
for j, (good, spec) in enumerate(goods):
where = 'SCHOOL={} AND NAME={} AND SPEC={}'.format(
repr(school), repr(good), repr(spec))
cur = db.select('SUM(NUMBER)', where)
value = cur.__next__()[0]
goods_sum[j] += value if value else 0
sheet.write(j + 1, i + 1, value if value else '', TEXT)
# write goods sum
sheet.col(i + 2).width_mismatch = True
sheet.col(i + 2).width = 2000
sheet.write(0, i + 2, '总计', SCHOOL)
for j, gd in enumerate(goods_sum):
sheet.write(j + 1, i + 2, gd, TEXT)
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)