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"当期履职报告报送情况统计表已生成")