def update_wenti():
for path in Path('~/OneDrive/工作/工作档案/履职报告/系统问题').glob('*.xlsx'):
load_wenti(path)
path = Path('~/OneDrive/工作/工作档案/履职报告/系统问题/履职报告问题处理情况表.xlsx')
sql = (
'select bh,period,category,branch,content,reporter,'
'reply,reply_person,status,ywxq '
'from lzwt where reply_dept="运营管理部" and %s and substr(period,1,4)>"2018" '
'order by period '
)
Headers = [
Header('编号', 0.01, 'Text'),
Header('提出时间', 13.8, 'Text'),
Header('问题分类', 13.8, 'Text'),
Header('机构', 30.73, 'Text'),
Header('具体内容', 50, 'Text'),
Header('报告人', 11.6, 'Text'),
Header('答复意见', 50, 'Text'),
Header('答复人', 11.6, 'Text'),
Header('状态', 11, 'Text'),
Header('业务需求', 50, 'Text'),
Header('校验位', 0.01, 'Text')
]
def conv(row):
s = get_md5("".join(row[7:10]))
return (*row, s)
with path.write_xlsx(force=True) as book:
book.add_formats(FORMATS)
book.add_table(
sheet='待提交需求问题',
columns=Headers,
data=Data(fetch(sql % ('status in ("待提交需求","待优化")')),
converter=conv),
)
book.add_table(
sheet='已提交需求问题',
columns=Headers,
data=Data(fetch(sql % ('status in ("已提交需求","待投产")')),
converter=conv),
)
book.add_table(
sheet='其他问题',
columns=Headers,
data=Data(fetch(sql % ('status in ("待研究","待解决","部分解决")')),
converter=conv),
)
print('导出待解决问题成功')
def _export(qc):
data = fetch(export_sql, [qc])
title = "%s年%s季度分管行领导及部门负责人履职报告问题的答复意见" % tuple(qc.split('-'))
filename = SAVEPATH / '正式答复意见' / ('关于%s.xlsx' % (title))
print(filename)
with filename.write_xlsx() as book:
book.worksheet = '分行履职报告问题表'
book.set_widths(WIDTHS2)
book.add_formats(FORMATS2)
book.A1_E1 = title, 'title'
book.A2 = ['分行名称', '问题描述', '提出人', '答复部门', '答复意见'], "h2"
fh_start, tcr_start = 3, 3
tcr, fh = None, None
for line, row in enumerate(data, 3):
book.row = line
book.B = row[1], 'normal'
book.D = row[3] or "运营管理部", 'cnormal'
book.E = row[4], 'normal'
if fh != row[0]:
if fh:
book['A%d:A%d' % (fh_start, line - 1)] = fh, 'cnormal'
fh_start = line
fh = row[0]
if tcr != row[2]:
if tcr:
book['C%d:C%d' % (tcr_start, line - 1)] = tcr, 'cnormal'
tcr_start = line
tcr = row[2]
if fh:
book['A%d:A%d' % (fh_start, line)] = fh, 'cnormal'
if tcr:
book['C%d:C%d' % (tcr_start, line)] = tcr, 'cnormal'
book.set_border('A2:E%d' % (line))
def data():
for period, typ, branch, name, content in fetch(sql):
content = json.loads(content)
for k, v in content['content']:
v = v.strip()
if '跟班' in k and v != "":
yield period, typ, branch, name, k, v
def khqk(km):
print('\n已开账户情况')
data = fetch('select distinct xh,hm from nbzhhz where km=? order by xh',
[km])
for row in data:
print(f'{km}-{row[0]:03d} {row[1]}')
xh = min(set(range(1, len(data) + 2)) - set([x[0] for x in data]))
print('\n最小未用序号：', xh)
def main(km=None, list_=False):
print(f'数据版本：{get_ver("xxbm")}')
if km:
for kemu, bm, name in fetch("select km,bm,name from xxbm where km=?",
[km]):
print(f'\n{kemu} {name}')
print(f'编码 {bm}')
tprint(zip(names.split(','), split(bm)), format_spec={0: '10'})
if list_:
fprintf("{:6s} {:40s} {:50s}", "select km,bm,name from xxbm")
def export_file():
period = fetchvalue('select max(period) from lzwt')
path = ROOT / f'营业主管履职报告重点问题与答复意见（{str(Period(period))[:-1]}）.xlsx'
with path.write_xlsx() as book:
book.add_formats(FORMATS)
write_curpriod(book, period, '重点问题')
write_curpriod(book, period, '一般问题')
for r in fetch('select distinct substr(period,1,4)as year from lzwt '
'order by year desc'):
write_year(book, r[0])
print(f'导出文件 {path.name} 成功')
update_write_time(path) # 更新写入时间，防止无变更导入
def _publish(filename, qc):
hz = set(x[0] for x in fetch(
'select name from brreport where type=0 and period=?', [qc]))
data = filename.sheets('分行履职报告问题表')
if data:
execute('delete from lzwenti where period=? and type in (0,1)', [qc])
data = tuple(
procdata(data, header='分行,提出人,问题描述,答复部门,答复人,答复意见'.split(',')))
for row in data:
row.extend([0 if row[1] in hz else 1, qc])
r = executemany(
'insert into lzwenti(rpt_branch,rpt_name,content,reply_depart,reply_name,reply,type,period)'
'values(?,?,?,?,?,?,?,?)', data)
print(f'共导入 {r.rowcount} 条数据')
def export_ylb(qc):
if not qc:
qc = fetchvalue(
'select period from brreport order by period desc limit 1')
print('导出期次：%s' % (qc))
wentis = []
for lb in ('分管行长', '运营主管'):
type_ = 0 if lb == '分管行长' else 1
with (SAVEPATH / '报告' / ('%s履职报告（%s）.xlsx' % (lb, qc))).write_xlsx()\
as book:
book.add_formats(FORMATS)
for br, name, content in fetch(ylb_sql, [qc, type_]):
print("%-10s%s" % (br, name))
book.worksheet = br
book.set_widths(WIDTHS)
content = json.loads(content)
header = content['header']
book.A1_B1 = header[0], 'bt'
book.A2_B2 = header[1], 'normal'
book.A3_B3 = header[2], 'normal'
r1, r2 = 4, 4
isWenTi = False
oldlb = None
for lb, nr in content['content']:
nr = nr.strip()
book[f'B{r2}'] = nr, 'normal'
if lb:
isWenTi = '问题' in lb or '意见或建议' in lb
if oldlb and oldlb != lb:
book[f'A{r1}:A{r2-1}'] = oldlb, 'vnormal'
r1 = r2
oldlb = lb
if isWenTi and nr and len(nr) > 10:
wentis.append([br, name, nr])
r2 += 1
book[f'A{r1}:A{r2-1}'] = oldlb, 'vnormal'
filename = SAVEPATH / '问题' / ('分行运营主管履职报告问题%s.xlsx' % (qc))
with filename.write_xlsx() as book:
book.add_formats(FORMATS)
book.worksheet = '分行履职报告问题表'
book.set_widths({'A:B': 9, 'C:C': 80, 'D:E': 15, 'F:F': 80})
book.A = '分行 提出人 问题描述 答复部门 答复人 答复意见'.split(), 'h2'
book + 1
for d in wentis:
book.A = [d[0], d[1]], "vnormal"
book.C = d[2], 'normal'
book.D = ['', ''], 'vnormal'
book.F = '', 'normal'
book + 1
print('共导出问题 %d 条' % (len(wentis)))
def iter_(begin, years=9):
workdays = set()
holidays = {}
for year, anpai, ab in fetch('select * from jqb where year>=?',
[begin[:4]]):
for a in anpai.splitlines():
s = Pattern == a
d = s.groupdict()
holidays.update(
zip(parsedate(d.get('fj'), year), cycle([d['name']])))
sb = d.get('sb')
if sb:
workdays.update(parsedate(sb, year))
year = begin[:4]
obj = fetchone('select * from jqb where year=?', [begin[:4]])
if not obj:
print('起始年份无对应的假期表，请先下载')
exit(1)
ab = obj[2]
if not ab:
ab = input(f'请输入 {year} 年 1 月 1 日的 AB 户标志：')
ab = ab.upper()
if ab not in 'AB':
print('AB户标志输入不正确!')
exit(2)
with connect():
execute('update jqb set ab=? where year=?', [ab, year])
data = []
begin = datetime(f'{year}-1-1')
for d in begin.iter(begin.add(years=years)):
memo = holidays.get(d) or WEEKDAY.get(d.isoweekday())
if not memo:
flag, sx, memo = '0', '0', ''
elif memo.startswith('星期') and d in workdays:
flag, sx, memo = '0', '0', '调休上班'
else:
flag, sx = '1', '2'
ab_ = ab if flag == '1' else "A" if ab == 'B' else "B"
if d.month == 1 and d.day == 1:
if fetchone('select ab from jqb where year =?', [d.year]):
with connect():
execute('update jqb set ab=? where year=?', [ab, d.year])
data.append((d % '%Y%m%d', flag, sx, memo, ab, ab_))
ab = ab_
return data
def write_year(book, year):
data = fetch(
'select period,importance,category,branch,content,reporter,reply_dept,reply,status from lzwt '
'where period like ? '
'order by period,importance desc,rowid ', [f'{year}%'])
if not data:
return
book.worksheet = f'{year}年汇总'
book.set_widths(Widthes2)
book.A1_I1 = f'营业主管履职报告重点问题与答复意见（{year}年）', 'Title'
book.A2 = '提出时间 重要性 问题分类 机构 具体内容 报告人 反馈部门 答复意见 状态'.split(' '), 'Header'
for row, d in enumerate(data, 3):
book.row = row
book.A = [str(Period(d[0])), d[1], d[2], d[3]], 'VText'
book.E = d[4], 'Text'
book.F = d[5:7], 'VText'
book.H = d[7], 'Text'
book.I = d[8], 'VText'
book.set_border(f'A2:I{row}')
def write_curpriod(book, period, importance):
data = fetch(
'select period,importance,category,branch,content,reporter,reply_dept,reply,status from lzwt '
'where period=? and importance =? '
'order by rowid ', [period, importance])
if not data:
return
book.worksheet = importance
book.set_widths(Widthes1)
period = str(Period(period))
book.A1_H1 = f'营业主管履职报告重点问题与答复意见（{period}）', 'Title'
book.A2 = '提出时间 问题分类 机构 具体内容 报告人 反馈部门 答复意见 状态'.split(' '), 'Header'
for row, d in enumerate(data, 3):
book.row = row
book.A = [period, d[2], d[3]], 'VText'
book.D = d[4], 'Text'
book.E = d[5:7], 'VText'
book.G = d[7], 'Text'
book.H = d[8], 'VText'
book.set_border(f'A2:H{row}')
def loadwt(filename):
print(f'导入最新处理完成的文件：{filename.name}')
data = filename.sheets(0)
if len(data) > 3 and len(data[2]) >= 7:
rq = Period(data[0][0]).value
print(f'当前导入文件日期：{rq}')
count = fetchvalue('select count(reporter) from lzwt where period=?',
[rq])
if count > 0:
confirm = input('基准文件中已有数据，是否重新导入？ Y or N')
if confirm.lower() == "n":
return
r = execute('delete from lzwt where period=?', [rq])
print('存量数据已删除')
def procline(line):
print(line)
line = [x.strip() for x in line]
bh = get_md5(line[2])
importance = '重点问题' if '重点' in line[6] else '一般问题'
dfr = ""
if not (line[4].endswith('部') or line[4].endswith('中心')):
dfr = line[4]
line[4] = '运营管理部'
nline = [bh, rq, importance, *line[:7], dfr]
return nline
data = filter(None, map(procline, data[2:]))
sql = (
'insert or replace into lzwt(bh,period,importance,category,branch,content,reporter,'
'reply_dept,reply,status,reply_person)values(?,?,?,?,?,?,?,?,?,?,?)'
)
executemany(sql, data)
s = 0
for r in fetch(
'select importance,count(period) from lzwt '
'where period=? group by importance ', [rq]):
print(*r)
s += int(r[1])
print(f'共导入数据：{s:,d}')
def list_kemu(sql):
for row in fetch(sql):
print(*row, sep='\t')
def do_report():
period = fetch_period()
Downloads = Path("~/Downloads")
print('当前期次：%s' % (period))
print(
f'报告数量：{fetchvalue("select count(distinct title||br)from report where period=?",[period])}')
with (Downloads / "当期履职报告报送情况统计表.xlsx").write_xlsx(force=True)as book:
hd_sql = ('select brorder,brname from brorder a '
'left join report b '
'on instr(b.br,a.brname)>0 and period =? and b.lx="事后监督" '
'where b.br is null '
'order by brorder'
)
print('事后监督报告漏报清单')
print('应报：', fetchvalue('select count(*) from brorder where brname not like "%总行%" and brname not in ("香港分行","义乌分行")'),
"实报：", fetchvalue("select count(*)from report where lx='事后监督' and period=?", [period]))
data = list(fetch(hd_sql, [period]))
if len(data):
fprintf("{0:2d} {1:20s}", hd_sql, [period])
book.add_table(sheet="事后监督未报送", data=fetch(hd_sql, [period]),
columns=[
Header("分行序号", 8),
Header("分行名称", 45)
])
yyzgs = fetchvalue('select count(distinct jg)from yyzg '
f'where jg not like "331000%" and jg not in {Excluders}')
bss = fetchvalue(
'select count(*)from report where period=? and lx="营业主管"', [period])
print(f'营业主管数：{yyzgs},实报：{bss}')
zgwb_sql = (
'select jgmc,xm from yyzg a '
'left join report b on upper(a.ygh)=upper(b.ygh) and period=? and lx="营业主管" '
'where a.js like "a%" and b.ygh is null '
f'and a.jg not like "331000%" and a.jg not in {Excluders}'
)
data = list(fetch(zgwb_sql, [period]))
if len(data):
fprintf('{0:20s} {1:25s}', zgwb_sql, [period])
book.add_table(sheet="营业主管履职报告未报送清单", data=fetch(zgwb_sql, [period]),
columns=[
Header("机构", 40),
Header("姓名", 20)
])
print(' - '*10)
zgyc_sql = (
'select br,name from report b '
'left join yyzg a on upper(a.ygh)=upper(b.ygh) and a.js like "a%" '
'where a.ygh is null and lx="营业主管" and period=? '
)
data = list(fetch(zgyc_sql, [period]))
if len(data):
fprintf('{0:20s} {1:25s}', zgyc_sql, [period])
book.add_table(sheet="营业主管履职报告异常报送清单", data=fetch(zgyc_sql, [period]),
columns=[
Header("机构", 40),
Header("姓名", 20)
])
print(f"当期履职报告报送情况统计表已生成")
