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 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 _connect(database: str=None, **kw):
if not database:
kw = _db_config.copy()
database = kw.pop('database')
if not Path(database).root:
database = str((ROOT/database).with_suffix('.db'))
connection = sqlite3.connect(database, **kw)
with closing(connection):
yield connection
def update():
for path in Path('~/OneDrive/工作/工作档案/履职报告/处理完成').glob('营业主管*.*'):
data = path.sheets(0)
period = path.pname[9:16]
for row in data[2:]:
name = row[4]
if name and not(name.endswith('部') or name.endswith('中心')):
r = execute('update lzwt set reply_person=? where period=? and bh=?',
[name, period, get_md5(row[2])])
if r.rowcount == 0:
print(name, period, get_md5(row[2]))
def load(fname: str, loader: Loader):
if fileinfo := files.get(fname):
path = Path(fileinfo.filename)
mtime = datetime(*fileinfo.date_time) % '%F %H:%M:%S'
loadcheck(loader.table, path.name, mtime, ver)
loader.data = read(fileinfo, fname)
r = loader.load()
if r:
print(f'导入文件 {fname} 成功，行数：{r.rowcount}')
else:
raise Exception('执行SQL错误')
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 _(path: Path):
if drop:
execute(f'delete from {table}')
if callable(proc):
data = proc(path, **kw)
else:
data = path.iter_csv(**kw)
if period:
execute('insert or replace into param_period values(?,?,?)',
[table, period, now() % '%F %T'])
insert(table, fields=fields, data=data, method=method)
if callable(exec):
exec()
print(f'{path.name} 导入成功')
def test(name):
path = (HOME / 'OneDrive/工作/参数备份').find('运营参数*.zip')
doc = ParamList.get(name)
if doc:
with zipfile.ZipFile(path) as z:
files = {}
for fileinfo in z.filelist:
if not (fileinfo.flag_bits & 0x0800):
fileinfo.filename = fileinfo.filename.encode(
'cp437').decode('gbk')
z.NameToInfo[fileinfo.filename] = fileinfo
files[Path(fileinfo.filename).pname] = fileinfo
if files.get(name):
run(loadfile(z, doc, files.get(name), dry=True))
async def load_param():
path = (HOME / 'OneDrive/工作/参数备份').find('运营参数*.zip')
with zipfile.ZipFile(path) as z:
files = {}
for fileinfo in z.filelist:
if not (fileinfo.flag_bits & 0x0800):
fileinfo.filename = fileinfo.filename.encode('cp437').decode(
'gbk')
z.NameToInfo[fileinfo.filename] = fileinfo
files[Path(fileinfo.filename).pname] = fileinfo
#load_teller2(path, z, files.get("users_output"))
coros = [
loadfile(z, doc, files.get(name))
for name, doc in ParamList.items()
]
await wait(coros)
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 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 load_file():
files = Path("~/Downloads").glob('会计履职报告????-??.xls*')
if not files:
print('当前目录无文件')
else:
filename = max(files)
print('当前导入文件：%s' % (filename))
data = []
for rows in filename.iter_sheets():
rows = rows[-1]
if len(rows) < 1:
continue
title = None
for row in rows[1:]:
if row[0]: # 防止空行出现
if title != row[0]:
title = row[0]
nr = [row[20:]]
gh = extract(row[3], r"[a-zA-Z]{1,2}\d{4}")
data.append([
title,
_get_period(row[6]), row[2], gh, row[5] + row[4],
row[6], row[7], row[8], row[9], row[11], row[12],
row[13], row[14], row[15], row[17], row[18],
row[19], nr
])
else:
nr.append(row[20:])
for r in data:
r[-1] = json.dumps(r[-1])
with trans():
sql = f'insert or replace into report {Values(18)}'
cur = executemany(sql, data)
print(f'已导入数据：{cur.rowcount}')
execute('delete from yyzg')
executemany(f'insert into yyzg {Values(11)}', read_yyzg())
print('已导入营业主管数据')
def loadzip():
path = Root.find('运营参数*.zip')
if not path:
print(f'没有找到 运营参数*.zip 文件')
exit(1)
info(f'找到文件 {path}')
with ZipFile(path) as zf:
files = {}
ver = path.pname[-7:]
for fileinfo in zf.filelist:
if not (fileinfo.flag_bits & 0x0800):
fileinfo.filename = fileinfo.filename.encode('cp437').decode(
'gbk')
zf.NameToInfo[fileinfo.filename] = fileinfo
files[Path(fileinfo.filename).pname] = fileinfo
def read(fileinfo, name):
with zf.open(fileinfo) as f:
for row in f:
encoding = 'utf8' if name in ('transactions_output',
'users_output') else 'gbk'
yield row.decode(encoding, errors='ignore').split(',')
@tran
def load(fname: str, loader: Loader):
if fileinfo := files.get(fname):
path = Path(fileinfo.filename)
mtime = datetime(*fileinfo.date_time) % '%F %H:%M:%S'
loadcheck(loader.table, path.name, mtime, ver)
loader.data = read(fileinfo, fname)
r = loader.load()
if r:
print(f'导入文件 {fname} 成功，行数：{r.rowcount}')
else:
raise Exception('执行SQL错误')
else:
# 项目：基本库函数 # 模块：sqlite 数据库 # 作者：黄涛 # License:GPL # Email:[email protected] # 创建：2018-7-18 import sqlite3 from contextlib import contextmanager, closing from orange import Path __all__ = 'db_config', 'begin_tran', 'begin_query' _db_config = None ROOT = Path('~/OneDrive/db') def db_config(database: str, **kw): global _db_config kw['database'] = database _db_config = kw @contextmanager def _connect(database: str=None, **kw): if not database: kw = _db_config.copy() database = kw.pop('database') if not Path(database).root: database = str((ROOT/database).with_suffix('.db')) connection = sqlite3.connect(database, **kw)
# 项目： 参数导入模块 # 模块： 导入压缩包中的参数 # 作者： 黄涛 # License: GPL # Email: [email protected] # 创建：2021-07-10 08:24 from .loader import Loader, loadcheck from zipfile import ZipFile from orange import Path, info, fatal, decode, Data, limit, datetime, includer, decode, mapper, filterer from orange.utils.sqlite import tran Root = Path('~/Documents/参数备份') @mapper def stripper(row: list) -> list: return [x.strip() for x in row] @mapper def null2none(row: list) -> list: return [None if x == 'null' else x for x in row] @mapper def teller_conv(row: list) -> list: nrow = [""] * 22 nrow[:3] = row[:3] nrow[3:7] = row[4:8] nrow[7] = ",".join(row[8:-26]) nrow[8] = row[-26]
# 项目：履职报告上报情况分析 # 模块：分析模块 # 作者：黄涛 # License:GPL # Email:[email protected] # 创建：2018/07/20 # 修订：2018/07/29 程序调整 # 修订：2020-06-15 08:15 调整未及时报送统计功能 from collections import defaultdict from orange import Path, R, arg, cstr, datetime, now, extract from orange.utils.sqlite import connect, execute, executemany, fetch, fetchone,\ executescript, trans, Values, fprint import json ROOT = Path('~/OneDrive/工作/工作档案/履职报告') if not ROOT: ROOT.ensure() def _get_period(date: str) -> str: date = datetime(date).add(days=-25) return date % ('%Y-%m') def read_yyzg(): path = Path("~/Documents/参数备份/营业主管").find('营业主管信息*.*') print(f'导入营业主管文件 {path}') for row in path.sheets(0)[1:]: yield (*row[2:10], *row[:2], row[11])
def read_yyzg():
path = Path("~/Documents/参数备份/营业主管").find('营业主管信息*.*')
print(f'导入营业主管文件 {path}')
for row in path.sheets(0)[1:]:
yield (*row[2:10], *row[:2], row[11])
# 项目：工作平台 # 模块：数据库模型 # 作者：黄涛 # License:GPL # Email:[email protected] # 创建：2018-05-25 18:53 from .__version__ import version as __version__ from orange import R, Path, HOME from .utils import checkload, procdata, loadcheck from orange.utils.sqlite import db_config, execute, executemany, executescript, executefile,\ fetch, fetchone, fetchvalue, trans WORKPATH = Path('~/Documents/工作') __all__ = '__version__', 'R', 'Path', "WORKPATH", "checkload", "HOME" fetchone = fetchone fetch = fetch fetchvalue = fetchvalue
# 项目：履职报告上报情况分析 # 模块：分析模块 # 作者：黄涛 # License:GPL # Email:[email protected] # 创建：2018/07/20 from orange import Path path = Path('D:/履职报告') delete_sql = ''' drop table if exists report; drop table if exists branch; ''' create_sql = ''' create table if not exists report( title text primary key, bgr text, jg text, bgrq text, cs text, fj text, yxqk text, sbmc text, ycnr text, spyj text, fhjj text, zhjj text, shryj text, fzryj text,