def GenBan():
year = fetchvalue('select max(substr(period,1,4)) from brreport')
print(f"当前年份：{year}")
sql = (
'select period,case [type] when "0" then "分管行长" else "运营部负责人" end as typ,'
'branch,name,content from brreport '
f'where period like "{year}%" '
'order by period,typ,branch')
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
with Path(f'~/Downloads/{year}年跟班情况统计.xlsx').write_xlsx(
force=True) as book:
book.add_formats(FORMATS2)
book.add_table(sheet='跟班情况统计',
data=data(),
columns=[
Header('期次', 12, format='normal'),
Header('类型', 15, format='normal'),
Header('分行', 20, format='normal'),
Header('姓名', 15, format='normal'),
Header('类别', 15, format='cnormal'),
Header('内容', 75, format='normal')
])
print('导出文件成功！')
def show_teller(sql, arg):
header = '柜员号，姓名，电话，柜员级别，柜组，机构号，员工号，执行交易组，转账限额，现金限额，认证类型，状态，屏蔽交易，岗位性质，启用日期，停用日期，交易币种，发起交易组，证件种类，证件号码，是否运营人员'.split(
'，')
for tlr in fetch(sql, arg):
tlr = list(tlr)
tlr[3] = {'0': '0-经办', '1': '1-主办', '2': '2-主管'}.get(tlr[3])
tlr[-11] = {'0': '0-密码', '1': '1-指纹'}.get(tlr[-11])
tlr[-8] = {
'0': '0-非管库员',
'1': '1-管库员',
'2': '2-机器柜员',
'3': '3-行外人员'
}.get(tlr[-8])
tlr[-3] = {'1': '1-身份证'}.get(tlr[-3], tlr[-3])
gw = tlr.pop(7)
tprint(zip(header, tlr), {0: '20'})
g = []
jndj = set()
for i, y in enumerate(gw.split(',')):
if i % 2 == 0 and y:
x = POST[i // 2]
v = fetchvalue(
f"select group_concat(memo,'，') from eddj where code in ('{y[:2]}','{y[2:]}')"
)
g.append((x, v))
elif i % 2 == 1:
jndj.add(y)
tprint(g, {0: '20'})
jndj.remove('')
print('技能等级： ', *jndj)
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 export_teller(branchs):
captial = ""
Ver = fetchvalue('select ver from LoadFile where name="teller" ')
with Path(f'~/Documents/业务检查用柜员表（截至{Ver}）.xlsx').write_xlsx(
force=True) as book:
for br in branchs.split(','):
if captial and len(br) < 9:
br = captial[:-len(br)] + br
name = fetchvalue('select mc from ggjgm where jgm=?', [br])
if not name:
continue
book.add_table(sheet=f'{br}-{name}',
data=Data(fetch(query_sql, [br]), converter=conv),
columns=[
Header('柜员号', 11, 'normal'),
Header('姓名', 15, 'normal'),
Header('电话', 12, 'normal'),
Header('柜员级别', 12, 'normal'),
Header('柜组', 9, 'normal'),
Header('工号', 9, 'normal'),
Header('岗位', 25, 'normal'),
Header('执行交易组', 80, 'normal'),
Header('转账限额', 12, 'normal'),
Header('现金限额', 12, 'normal'),
Header('认证类型', 10, 'normal'),
Header('状态', 11, 'normal'),
Header('屏蔽交易', 30, 'normal'),
Header('岗位性质', 12, 'normal'),
Header('启用日期', 12, 'normal'),
Header('终止日期', 12, 'normal'),
Header('交易币种', 20, 'normal'),
Header('发起交易组', 45, 'normal'),
Header('是否运营人员', 12, 'normal'),
Header('证件类型', 15, 'normal'),
Header('证件号码', 19, 'normal'),
Header('技能等级', 8, 'normal'),
])
captial = br
print('导出文件成功！')
def _(filename, *args, **kw):
need_create and createtable() # 第一次执行本函数时建表
file = Path(filename)
name = file.name
a = fetchvalue('select mtime from LoadFile where filename=?',
[name]) # 查询是否已导入
is_imported = a and a >= file.mtime # 判断是否已经导入
if not is_imported:
func(filename, *args, **kw)
execute(
'insert or replace into LoadFile values(?,?)', # 保存记录
[name, file.mtime])
else:
print(f'{name} 已导入，忽略')
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 checkload(filename: str, loadfile: "function", *args,
**kw) -> bool: # 检查文件是否已经导入
'''
检查指定的文件是否已经导入数据库，如未导入执行 loadfile 函数
filename: 待导入的文件
loadfile: 导入文件的程序
*args,**kw : loadfile 除 filename 以外的参数
'''
need_create and createtable() # 第一次执行本函数时建表
file = Path(filename)
name = file.name
a = fetchvalue('select mtime from LoadFile where filename=?',
[name]) # 查询是否已导入
is_imported = a and a >= file.mtime # 判断是否已经导入
if not is_imported:
loadfile(filename, *args, **kw)
with trans():
execute(
'insert or replace into LoadFile values(?,?)', # 保存记录
[name, file.mtime])
return is_imported
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 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"当期履职报告报送情况统计表已生成")
def fetch_period() -> str:
d = fetchvalue('select period from report order by date desc limit 1')
if not d:
raise Exception('无数据记录')
return d
def loadcheck(name: str, path: str, mtime: datetime, ver: str):
checkSQL = "select count(name) from loadfile where name=? and path=? and mtime>=datetime(?)"
doneSQL = "insert or replace into loadfile values(?,?,datetime(?),?)"
if value := fetchvalue(checkSQL, [name, path, mtime]):
raise Exception(f'文件：{path} 已导入')
def get_ver(name):
return fetchvalue("select ver from loadfile where name=?", [name])