def loaddata(date, period, rows):
print(f'报告日期： {date} 期次：{period}')
type_ = get_type(rows[0][0])
bgr = get_bgr(rows[2][0])
branch = get_br(rows[1][0])
print(f'分行: {branch} 报告类型： {type_} ')
print(f'报告人： {bgr} 报告时间： {date}')
if any([x is None for x in [type_, bgr, branch]]):
raise Exception('报告格式不正确')
header = [rows[i][0] for i in range(3)]
contents = [(row[:2]) for row in rows[3:]]
content = json.dumps({'header': header, 'content': contents})
execute('insert or replace into brreport values(?,?,?,?,?,?)',
[period, type_, branch, bgr, date, content])
def loadfile(filename):
def read():
for sheet in filename.worksheets:
if sheet.name.endswith('年汇总'):
data = sheet._cell_values
if len(data) > 3 and len(data[2]) == 9:
for row in data[2:]:
yield [get_md5(row[4]), f'{row[0][:4]}-{row[0][5:7]}', *row[1:]]
execute('delete from lzwt')
sql = (
'insert or replace into lzwt(bh,period,importance,category,branch,content,reporter,'
'reply_dept,reply,status)values(?,?,?,?,?,?,?,?,?,?)'
)
executemany(sql, read())
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 _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 _(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 parse(txt):
year = None
anpai = []
for r in txt.splitlines():
if not year:
k = YEAR.match(r)
if k:
year = k.groups()[0]
else:
if Pattern == r:
anpai.append(r)
with connect():
execute('insert or ignore into jqb(year,anpai) values(?,?)',
[year, "\n".join(anpai)])
print(f'{year}年假期安排获取成功')
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 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 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 load_wenti(filename):
print('处理文件：', filename.pname)
s = 0
for sheet in filename.worksheets:
for row in sheet._cell_values[1:]:
if get_md5("".join(row[7:10])) != row[10]:
r = execute('update lzwt set reply_person=?,status=?,ywxq=? where bh=?', [
*row[7:10], row[0]])
s += r.rowcount
if r.rowcount == 0:
print('Error:', row)
print(f'已更新数据：{s}条')
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 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 loaddfyj(filename):
print(f'导入基准文件：{filename.name}')
# execute('delete from lzwt')
# print('清理已导入的数据')
# excludes = set()
sql = (
'update lzwt set reply_dept=?,reply=?,status=?,importance=? where period=? and bh=?'
)
s = 0
for sheet in filename.worksheets:
if sheet.name in ('重点问题', '一般问题'):
data = sheet._cell_values
if len(data) > 3 and len(data[2]) == 8:
for row in data[2:]:
if row:
bh = get_md5(row[3])
period = f'{row[0][:4]}-{row[0][5:7]}'
r = execute(sql, [*row[5:8], sheet.name, period, bh])
s += r.rowcount
if not r.rowcount:
print("Error", [*row[5:8], sheet.name, period, bh])
print(f'{s} 条记录被更新')
'''
dest='reset_name',
nargs='?',
help='重置某表，重置后可以重新导入数据')
def main(**options):
db_config('params')
info(f'set db param')
if options['list']:
fprintf(
'{0:10s}{1:20s}',
'select type,name from sqlite_master where type in ("table","view")order by name'
)
if name := options['show']:
fprint('select sql from sqlite_master where name=?', [name])
if sql := options['qsql']:
fprint(' '.join(sql))
if sql := options['esql']:
with connect():
sql = ' '.join(sql)
info(f'execute:{sql}')
r = execute(sql)
print(f'{r.rowcount} 行数据受到影响')
if options['load']:
from .load import loadall
info('开始导入参数')
loadall()
info('导入参数完成')
if name := options['reset_name']:
with connect():
r = execute('delete from LoadFile where name=?', [name])
print(f"重置 {name}", '成功' if r.rowcount else '失败')
# 作者： 黄涛 # License: GPL # Email: [email protected] # 创建：2021-07-10 11:40 from orange import Path, info, fatal, decode, Data, limit, datetime from orange.utils.sqlite import fetchvalue, execute, load 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} 已导入') else: execute(doneSQL, [name, path, mtime, ver]) class Loader(): __slots__ = ('clear', 'converters', 'kwargs', 'method', 'table', 'fields', 'data') def __init__(self, table: str, fields: str, *converters, method: str = 'insert', **kw): self.table = table self.fields = fields self.clear = True
