def endate(s):
if not s:
return datetime(3000, 12, 31)
elif '实际' in s:
return datetime('2100-12-31')
else:
s = s.replace('年', '-').replace('月', '-').replace('日', '').replace(
'前', '').replace('：', ':')
return datetime(s)
def parsedate(s, year):
for r in Rq.finditer(s):
r = r.groupdict()
flag, y1, m1, d1, y2, m2, d2 = tuple(
map(r.__getitem__, 'flag,y1,m1,d1,y2,m2,d2'.split(',')))
y1 = y1 or year
y2 = y2 or y1 or year
d1 = datetime("-".join([y1, m1, d1]))
if flag:
d2 = datetime("-".join([y2, m2 or m1, d2]))
yield from d1.iter(d2 + 1)
else:
yield d1
def dedate(s):
if s.year == 3000:
return None
elif s.year == 2100:
return "实际业务发生时"
else:
s = datetime(s)
return f'{s.year}年{s.month}月{s.day}日 ' + s % "%H:%m" + '前'
def load_nkwg():
path = Downloads.find('resultReg*.xls*')
ver = ""
loadcheck('nkwg', path.name, datetime(path.mtime) % '%F %H:%M:%S', ver)
loader = Loader('nkwg', 29, includer(*range(29)))
loader.data = path.sheets(0)
if r := loader.load():
print(f'导入 {path.name} 成功，行数：{r.rowcount}')
def load_yyzg():
path = Downloads.find('营业主管信息*.xls*')
ver = Ver.extract(path.pname,)
loadcheck('yyzg', path.name, datetime(path.mtime) % '%F %H:%M:%S', ver)
loader = Loader('yyzg', 11, includer(
2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 11), conv)
loader.data = path.sheets(0)[1:]
if r := loader.load():
print(f'导入 {path.name} 成功，行数：{r.rowcount}')
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 export(begindate):
from orange.xlsx import Book
begindate = datetime(begindate or now() + 5) % '%Y%m%d'
data = iter_(begindate)
data = [x for x in data if x[0] >= begindate]
if data:
fn = f'假期参数表{begindate}.xlsx'
with Book(fn) as rpt:
rpt.add_table('A1',
sheet='假期参数表',
columns=Headers,
autofilter=False,
data=data)
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 mailto():
from orange.xlsx import Book
from orange.mail import Mail
begindate = datetime(now() + 5) % '%Y%m%d'
data = iter_(begindate)
data = [x for x in data if x[0] >= begindate]
if data:
filename = f'假期参数表{begindate}.xlsx'
def writer(fn):
with Book(fn) as rpt:
rpt.add_table('A1',
sheet='假期参数表',
columns=Headers,
autofilter=False,
data=data)
mail = Mail(sender='*****@*****.**',
to='*****@*****.**',
subject='假期表参数',
body='假期表参数，请审阅！')
mail.attach(filename, writer=writer)
mail.post()
print('发送邮件成功！')
def _get_period(date: str) -> str:
date = datetime(date).add(days=-25)
return date % ('%Y-%m')
def __set__(self,instance,value):
return super().__set__(instance,datetime(value))