def create(self):
db = Database()
sql = "insert into project (name, createUser, description) values (%s, %s, %s)"
flag = db.execute(sql, (self.name, self.create_user, self.description))
print(flag)
return flag
def request_news_time_series():
L.info("Start update news time series.")
with open('./json/DXYNews-TimeSeries.json', 'r') as file:
data = json.load(file)
L.info("\tRead {} lines from file".format(len(data)))
db = Database()
update_num = 0
for line in data:
key = [
'id', 'provinceId', 'title', 'summary', 'infoSource', 'sourceUrl',
'pubDate', 'province'
]
data = db.select("select * from news where id={}".format(line['id']))
if not data:
update_num += 1
sql = "insert into news (" + ','.join(
key) + ") values (" + ', '.join(['%s' for k in key]) + ")"
line['pubDate'] = TS2S(line['pubDate'] / 1000.0)
if line['provinceId'] == "":
line['provinceId'] = None
line['summary'] = line['summary'][0:4096]
if 'province' not in line:
line['province'] = None
params = [line[k] for k in key]
db.execute(sql, params)
L.info('\tUpdate {} news data.'.format(update_num))
def get_time_data(level=1, code=86):
db = Database()
whr = "" if level == 1 else " and region_parent={}".format(code)
sql = '''select a.region_code, region_name, numb_confirmed, numb_suspected, numb_die, numb_ok,
a.data_date, DATE(tm) from patients a
join (SELECT region_code, max(data_date) as tm FROM patients
where region_level={}'''.format(level) + whr + '''
group by region_code, DATE(data_date)) b
on a.region_code=b.region_code and a.data_date=b.tm order by data_date'''
dts, latest, yesterday, _yesterday = {}, {}, None, None
regions = get_regions(code, db)
'''数据检查与填充'''
def data_fill(date_):
for p in regions:
if p['code'] in dts[date_]: continue
dts[date_][p['code']] = latest.get(p['code'], [p['code'], 0, p['name'], 0, 0, 0, 0, date_])
for (code, name, confirmed, suspected, die, ok, tm, date_) in db.select(sql):
date_ = str(date_)
if date_ not in dts:
dts[date_] = {}
'''昨日数据检查填补，以下四行 '''
if yesterday: data_fill(yesterday)
_yesterday = yesterday
yesterday = date_
'''新增计算 '''
'''TODO: 地域判别，code可能不是直属地区'''
add_c = confirmed - dts[_yesterday][code][1] if _yesterday and code > 0 else confirmed
'''各地区缓存一份最新数据，用于数据填补'''
latest[code] = [code, confirmed, name, suspected, die, ok, add_c, str(tm)]
dts[date_][code] = latest[code]
if yesterday: data_fill(yesterday)
return dict((k, list(dts[k].values())) for k in dts)
def get_task_hint(user, pid=None, project=None):
if project == "": project = None
if not pid and not project: return []
db = Database()
params = [user]
sql = '''select task, taskId, moduleId from record_day r left join user u
on u.username=r.user where u.orgId=(select orgId from user where username=%s)
and task is not null'''
if pid == None or int(pid) == -1:
sql += " and project=%s"
params.append(project)
else:
sql += " and projectId=%s"
params.append(pid)
sql = "select task, taskId, moduleId from ({}) a group by task, taskId, moduleId".format(sql)
ts = {}
for task, taskId, moduleId in db.selectEx(sql, params):
if task not in ts:
ts[task] = [taskId, moduleId]
else:
if ts[task][0] == None: ts[task] = [taskId, moduleId]
'''End For'''
data = [{'name': k, 'id': fid(v[0]), 'mid': fid(v[1]) } for k, v in ts.items()]
return data
def get_region_citys():
db = Database()
sql = "select name, code, level, parent from region"
regions = {}
for (name, code, level, parent) in db.select(sql):
if level > 2: continue
if level == 0: continue
one = code if level == 1 else parent
if one not in regions: regions[one] = {'children': {}}
if level == 1: regions[code]['name'] = name
if level == 2:
regions[parent]['children'][code] = {'name': name}
'''别名缓存'''
if name in REGION_SHORT:
regions[parent]['children'][code]['names'] = set(
REGION_SHORT[name])
'''End If 1'''
'''End For'''
'''
建立查找索引·将各地区名称-代码列表存入name_code，含别名
特点：常规市含“市”；自治州不含“州”
'''
for p in regions:
regions[p]['name_code'] = {}
for c, v in regions[p]['children'].items():
regions[p]['name_code'][v['name']] = c
for n in v.get('names', []):
regions[p]['name_code'][n] = c
return regions
def get_task_list(mid, create_user, category, department):
db = Database()
keys = ['id', 'name', 'moduleId', 'createUser', 'status', 'executor', 'createTime', 'description',
'category', 'department', 'mark', 'progress', 'planEndTime', 'multiUser']
sql, params = "select " + ','.join(keys) + ", IFNULL(uc, 0) userCount from task t left join \
(select taskId, count(*) uc from \
(select taskId, user from record_day where moduleId=%s group by taskId, user \
) a group by taskId \
) r on t.id=r.taskId \
left join \
(select taskId, max(updateTime) utm from record_day where moduleId=%s group by taskId \
) b on t.id=r.taskId where t.moduleId=%s", [mid, mid, mid]
if create_user != '':
sql += " and createUser=%s"
params.append(create_user)
if category != '':
sql += " and category=%s"
params.append(category)
if department != '':
sql += " and department=%s"
sql += " order by utm, status desc"
data = db.read_all(sql, params)
for line in data:
line['planEndTime'] = str(line['planEndTime'])
line['createTime'] = str(line['createTime'])
return data
def get_rumor_data(keyword=None, type=None, page=None, num=None):
db = Database()
sql = "select * from rumor"
has_where = False
if keyword:
if keyword == '北京':
sql += " where mainSummary not like '%北京时间%' and (mainSummary like '%北京%' or title like '%北京%' or body like '%北京%')"
has_where = True
else:
sql += " where (mainSummary like '%{}%' or title like '%{}%' or body like '%{}%')".format(keyword, keyword, keyword)
has_where = True
if type and 0 <= int(type) <= 2:
if has_where:
sql += " and rumorType={}".format(type)
else:
sql += " where rumorType={}".format(type)
has_where = True
rumor = db.selectDict(sql)
if page and num and int(page) > 0 and int(num) > 0:
page = int(page)
num = int(num)
return rumor[(page - 1) * num:page * num], len(rumor)
else:
return rumor[0:10], len(rumor)
def submit_record(user, jobs, plans):
db = Database()
comands = []
jps, keys, texts = [{}, {}], ["job", "plan"], ["任务", "计划"]
for i, rs in enumerate([jobs, plans]):
for r in rs:
w = WorkRecord()
w.load_data(user, r)
sql, params = w.get_sql(i)
comands.append([sql, params])
if w.task_id == None:
if w.task == "": return False, "任务不能为空！"
continue
if int(w.task_id) == -1: continue
if w.task_id in jps[i]: return False, "%s项重复！" % texts[i]
jps[i][w.task_id] = w.progress
flag, msg = check_record(w, jps[0], db, keys[i])
if not flag: return flag, msg
'''End For 2'''
'''End For 1'''
flag, data = db.Transaction(comands)
if not flag: data = "请检查数据问题或今日是否已提交"
return flag, data
def get_regions(code=86, db=None):
if code == 86: return src_province
if not db: db = Database()
sql = "select code, name from region where parent=%s"
return [{
'name': name,
'code': code
} for (code, name) in db.select(sql, (code, ))]
def create(self):
db = Database()
sql = "insert into module (projectId, name, createUser, description) values (%s, %s, %s, %s)"
flag = db.execute(
sql,
(self.project_id, self.name, self.create_user, self.description))
return flag
def getLatest(db=None):
if not db: db = Database()
dt = {}
sql = '''select region_code, region_name, region_parent, max(data_date) from patients \
group by region_code, region_name, region_parent'''
for (code, name, parent, tm) in db.select(sql):
dt['_'.join([str(code), name, str(parent)])] = tm
return dt
def get_topic(date):
sql = ''
if date:
sql = "select date, topic, new, dead from topic where DATE_FORMAT(date, '%Y%m%d') ='{}'".format(date)
else:
sql = "select date, topic, new, dead from topic order by date desc"
db = Database()
data = db.select(sql)
return data
def request_rumor_type_data(rumor_type=0):
error_times = 0
db = Database()
i = 0
while i < 5:
i += 1
L.info("Preparing for type {} page {}".format(str(rumor_type), str(i)))
time.sleep(3)
rst = request_data(
"https://lab.isaaclin.cn/nCoV/api/rumors?num=50&rumorType={}&page={}"
.format(str(rumor_type), str(i)), str(i))
if rst['success'] == False:
if error_times == 10: break
error_times += 1
L.info(
"This is the {} times fail at getting page {}, will try it again."
.format(str(error_times), str(i)))
i -= 1
continue
else:
error_times = 0
if not rst['results']:
L.info(
"Collecting type {} rumor data finished.".format(rumor_type))
break
comands = []
for line in rst['results']:
ks = line.keys()
params = line['title']
sql = "select * from rumor where title ='{}'".format(params)
data = db.select(sql)
if not data:
sql = "insert into rumor (" + ','.join(
ks) + ") values (" + ', '.join(['%s' for k in ks]) + ")"
params = [line[k] for k in ks]
comands.append([sql, params])
try:
db.Transaction(comands)
L.info("Writing database for {} rumor.".format(str(len(comands))))
except Exception as e:
if error_times == 10: break
error_times += 1
L.info(
"This is the {} times fail at wirting database due to {}, will try this page again."
.format(str(error_times), str(e)))
i -= 1
continue
if error_times == 10:
L.info("Collecting rumor data finished with error.")
else:
L.info("Collecting rumor data finished.")
def create(self):
db = Database()
sql = "insert into task (projectId, moduleId, name, createUser, description, planEndTime, executor) \
values (%s, %s, %s, %s, %s, %s, %s)"
flag = db.execute(
sql, (self.project_id, self.module_id, self.name, self.create_user,
self.description, self.plan_end_time, self.executor))
return flag
def generate_topics():
L.info("Start update topic.")
db = Database()
db.run("delete from topic")
data = db.select("select pubDate, summary from news order by pubDate asc")
now = datetime.date(2019, 12, 31)
news = ""
topic = {}
today = []
yesterday = []
dead = []
new = []
sql = "insert into topic (date, topic, dead, new) values (%s, %s, %s, %s)"
for line in data:
if line[0].date() == now:
news += line[1]
else:
topic.clear()
today.clear()
for keyword, weight in textrank(news, topK=40, withWeight=True):
topic[keyword] = weight
today.append(keyword)
for keyword in today:
if keyword not in yesterday:
new.append(keyword)
for keyword in yesterday:
if keyword not in today:
dead.append(keyword)
db.execute(sql, [
now.strftime("%Y-%m-%d %H:%M:%S"),
str(topic),
str(dead),
str(new)
])
L.info("\tNow processing {}".format(now.strftime("%Y-%m-%d")))
now = line[0].date()
news = line[1]
yesterday = today.copy()
new.clear()
dead.clear()
topic.clear()
today.clear()
for keyword, weight in textrank(news, topK=20, withWeight=True):
topic[keyword] = weight
today.append(keyword)
for keyword in today:
if keyword not in yesterday:
new.append(keyword)
for keyword in yesterday:
if keyword not in today:
dead.append(keyword)
db.execute(
sql,
[now.strftime("%Y-%m-%d %H:%M:%S"),
str(topic),
str(dead),
str(new)])
L.info("\tFinished update topic.")
def import_region():
db = Database()
lines = []
levels = {'province': 1, 'city': 2, 'district': 3}
with open("geojson/adcode-map.json", encoding='utf8') as load_f:
dt = json.load(load_f)
for item in dt:
sql = "insert into region (name, code, level) values (%s, %s, %s)"
param = (item['name'], item['adcode'], levels[item['level']])
lines.append([sql, param])
db.Transaction(lines)
def get_region_data(code=86):
db = Database()
sql = '''
select name, geometry_type, geometry_coordinates, point_type, point_coordinates,
data_date from patient_pos where province_code=%s
'''
lines = []
for (name, tp, coords, p_tp, p_coords, tm) in db.select(sql, (code, )):
lines.append([name, tp, coords, p_tp, p_coords, str(tm)])
return lines
def login_out(user, ip='', address=''):
del_token(user)
db = Database()
sqlU, paramsU = (
"update user set onlineStatus = %s where userName = %s"), (0, user)
sqlL, paramsL = ("insert into logs (user, ip, style, createTime, address) values (%s, %s, 1, %s, %s)"), \
(user, ip, TM(), address)
db.Transaction([[sqlU, paramsU], [sqlL, paramsL]])
return {}
def import_region_special():
db = Database()
lines = []
levels = {'province': 1, 'city': 2, 'district': 3}
for key in ["110000", "500000", "120000", "310000"]:
with open("geojson/" + key + ".json", encoding='utf8') as load_f:
dt = json.load(load_f)
for item in dt["features"]:
sql = "insert into region (name, code, level, parent) values (%s, %s, %s, %s)"
param = (item['properties']['name'], item['id'], 2, key)
lines.append([sql, param])
db.Transaction(lines)
def add_city_code():
db = Database()
sql = "select id, region_name, region_parent FROM patients WHERE region_level=2 and region_code=0"
comands = []
for (id_, name, parent) in db.select(sql):
code = check_city(name, parent)
if not code:
print(parent, name, code)
continue
sql = "update patients set region_code=%s where id=%s"
comands.append([sql, (code, id_)])
db.Transaction(comands)
def import_data_from_files():
db = Database()
idx = 0
for filename in get_files():
idx += 1
print(filename)
commands = load_file_province(filename)
db.Transaction(commands)
print(idx, "OK! ")
print("Finished!")
for c in Position.Errors:
print("未识别的城市：{}, {}处".format(c, Position.Errors[c]))
def set_region_parent():
db = Database()
sql = "select name, code, level from region"
for (name, code, level) in db.select(sql):
if len(str(code)) < 6:
print('code short:', name, code, level)
continue
line = []
if level == 1: line = [code, 86]
elif level == 2: line = [code, str(code)[:2] + "0000"]
elif level == 3: line = [code, str(code)[:4] + "00"]
sql = "update region set parent = %s where code = %s"
db.execute(sql, (line[1], line[0]))
def get_data_latest(level = 1, code=86):
db = Database()
whr = "" if level == 1 else " and region_parent={}".format(code)
sql = '''select a.region_code, region_name, numb_confirmed, numb_suspected, numb_die, numb_ok,
a.data_date from patients a
join (SELECT region_code, max(data_date) as tm FROM patients
where region_level={}'''.format(level) + whr + '''
group by region_code) b
on a.region_code=b.region_code and a.data_date=b.tm'''
lines = []
for (code, name, confirmed, suspected, die, ok, tm) in db.select(sql):
lines.append([code, confirmed, name, suspected, die, ok, str(tm)])
return lines
def add_change(user, t_id, plan_time, progress, reason):
db = Database()
'''获取任务基本信息'''
sql = "select planEndTime, multiUser from task where id=%s"
cnx, cursor = db.Query(sql, (t_id, ))
src_time, multi_user = cursor.fetchone()
'''获取历史变更信息'''
sql = "select id, executor, createTime from plan_change where taskId=%s order by createTime desc limit 1 for update"
cursor = db.select(sql, (t_id, ))
line = cursor.fetchone()
flag = True
if line:
_, executor, tm = line
'''协同任务或该用户已提交计划未执行'''
if multi_user or executor == user:
sql = "select count(*) from record_day where taskId=%s and workday>%s "
db.select(sql, (t_id, tm), cursor)
count = cursor.fetchone()[0]
if count == 0: flag = False
'''End If 2'''
'''End If 1'''
'''有已更改的计划暂未实施，不能新增'''
if not flag:
cursor.close()
return False, "该任务有已更改的计划暂未实施，不能新增''"
sql = "insert into plan_change (taskId, srcTime, planTime, changeUser, executor, progress, reason, multiUser) \
values (%s, %s, %s, %s, %s, %s, %s, %s)"
params = (t_id, src_time, plan_time, user, user, progress, reason, multi_user)
flag, _ = db.Transaction([[sql, params]], cnx, cursor)
return flag, "操作失败"
def detection_data_citys():
db = Database()
unknowns = {}
sql = '''SELECT region_parent, region_name FROM patients WHERE region_level=2
group by region_parent, region_name'''
for (parent, name) in db.select(sql):
if parent not in REGIONS:
L.error("Not in Region Source: {}".format(parent))
continue
if check_city(name, parent): continue
if parent not in unknowns: unknowns[parent] = set()
unknowns[parent].add(name)
for p in unknowns:
print('----------------------')
print([v['name'] for v in REGIONS[p]['children'].values()])
print(list(unknowns[p]))
def request_data_time_series():
L.info("Start update data time series.")
needed = {}
for p in SP:
if p['name'] == '香港特别行政区':
needed['香港'] = p
if p['name'] == '澳门特别行政区':
needed['澳门'] = p
if p['name'] == '台湾省':
needed['台湾'] = p
needed[p['name']] = p
with open('./json/DXYArea-TimeSeries.json', 'r') as file:
data = json.load(file)
db = Database()
history = getLatest(db)
lines = []
for province in data:
if province["provinceName"] in needed:
p = needed[province["provinceName"]]
translate([province], p, lines)
L.info("\tRead {} lines from file".format(len(lines)))
comands = []
for line in lines:
'''排除已有历史数据'''
key = '_'.join([
str(line['region_code']), line['region_name'],
str(line['region_parent'])
])
if key in history and not date_less(history[key], line['data_date']):
continue
ks = line.keys()
sql = "insert into patients (" + ','.join(
ks) + ") values (" + ', '.join(['%s' for k in ks]) + ")"
params = [line[k] for k in ks]
comands.append([sql, params])
L.info("\tNew data lines count:" + str(len(comands)))
if len(comands) > 0:
db.Transaction(comands)
def generate_weibo_topics():
L.info("Start update weibo topic.")
db_weibo = pymysql.connect("localhost", "root", "root", "weibo")
cursor = db_weibo.cursor()
cursor.execute(
"select publish_time, content from weibo order by publish_time asc")
data = cursor.fetchall()
db = Database()
now = datetime.date(2019, 12, 31)
news = ""
topic = {}
sql = "insert into topic (date, topic) values (%s, %s)"
for line in data:
if line[0].date() == now:
news += line[1]
else:
topic.clear()
for keyword, weight in textrank(news, topK=20, withWeight=True):
topic[keyword] = weight
db.execute(sql, [now.strftime("%Y-%m-%d %H:%M:%S"), str(topic)])
L.info("\tNow processing {}".format(now.strftime("%Y-%m-%d")))
now = line[0].date()
news = line[1]
topic.clear()
for keyword, weight in textrank(news, topK=20, withWeight=True):
topic[keyword] = weight
db.execute(sql, [now.strftime("%Y-%m-%d %H:%M:%S"), str(topic)])
L.info("\tFinished update weibo topic.")
def get_module_list(pid, create_user, category, department):
db = Database()
keys = [
'id', 'name', 'projectId', 'createUser', 'status', 'manager',
'createTime', 'description', 'category', 'department', 'mark'
]
sql, params = "select " + ','.join(
keys) + " from module where projectId=%s", [pid]
if create_user != '':
sql += " and createUser=%s"
params.append(create_user)
if category != '':
sql += " and category=%s"
params.append(category)
if department != '':
sql += " and department=%s"
sql += " order by createTime desc"
data = db.read_all(sql, params)
for line in data:
line['createTime'] = str(line['createTime'])
return data
def request_rumor_time_series():
L.info("Start update rumor time series.")
with open('./json/DXYRumors-TimeSeries.json', 'r') as file:
data = json.load(file)
L.info("\tRead {} lines from file".format(len(data)))
db = Database()
update_num = 0
for line in data:
key = [
'id', 'title', 'mainSummary', 'body', 'sourceUrl', 'rumorType',
'crawlTime'
]
data = db.select("select * from rumor where id={}".format(line['id']))
if not data:
update_num += 1
sql = "insert into rumor (" + ','.join(
key) + ") values (" + ', '.join(['%s' for k in key]) + ")"
line['crawlTime'] = TS2S(line['crawlTime'] / 1000.0)
line['mainSummary'] = line['mainSummary'][0:1024]
line['body'] = line['body'][0:1024]
params = [line[k] for k in key]
db.execute(sql, params)
L.info('\tUpdate {} rumor data.'.format(update_num))
def get_task_control(t_id, user=None, db=None):
if not db: db = Database()
'''取任务下所有人的最后提交记录'''
sqlR = '''select r.user, updateTime, progress from record_day r right join (
select max(updateTime) tm from record_day where taskId=%s
) a on r.updateTime=a.tm where taskId=%s order by progress desc limit 1
'''
'''取任务下的最新变更记录'''
sqlP = '''select a.executor, updateTime, progress, multiUser from plan_change a right join
(select max(updateTime) tm from plan_change b where taskId=%s) b
on a.updateTime=b.tm where taskId=%s order by progress limit 1
'''
rec = db.selectLine(sqlR, (t_id, t_id))
pla = db.selectLine(sqlP, (t_id, t_id))
'''逻辑说明：
1.无计划变更则以最后提交为准，无则为0
2.有计划变更则以最新时间对应的进度为准
注：提交时需检查进度合法性
progress - 是否协作任务且今日已由其他人设置。
注：若有最新计划变更，则协作任务不受其他成员今日进度限制
'''
info = {'min': 0, 'progress': None}
if rec:
ru, rt, rp = rec
info['min'] = rp
'''今日他人已设置'''
if user != ru and is_today(rt): info['progress'] = rp
'''有计划则看时间'''
if pla and pla[1] > rt: info['min'], info['progress'] = pla[2], None
else:
'''无提交但有计划变更，则以计划为准'''
if pla: info['min'] = pla[2]
'''End If'''
return info
