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 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