def patVisit4Time(connstr, emrConnstr, htmlUrlBase, isSaveXml, isSaveHtml,
isProduction, processescount):
dBegin = datetime.datetime.strptime("2018-01-01 00:00:00",
"%Y-%m-%d %H:%M:%S")
dEnd = datetime.datetime.strptime("2018-03-31 23:59:59",
"%Y-%m-%d %H:%M:%S")
for k in range(0, (dEnd.year - dBegin.year) + 1):
pool = multiprocessing.Pool(processes=processescount)
d1 = datetime.datetime.strptime(
str(dBegin.year + k) + "-01-01 00:00:00", "%Y-%m-%d %H:%M:%S")
d2 = datetime.datetime.strptime(
str(dBegin.year + k) + "-12-31 23:23:59", "%Y-%m-%d %H:%M:%S")
if dBegin.year - d1.year == 0:
d1 = dBegin
if dEnd.year - d2.year == 0:
d2 = dEnd
sql = "select t.patient_id,t.visit_id,M.PATIENT_NAME from dqmc_pat_visit t left join dqmc_pat_master m on T.PATIENT_ID=M.PATIENT_ID where t.register_date between to_date('"
sql += d1.strftime(
"%Y-%m-%d %H:%M:%S"
) + "','yyyy-mm-dd hh24:mi:ss') and to_date('" + d2.strftime(
"%Y-%m-%d %H:%M:%S"
) + "','yyyy-mm-dd hh24:mi:ss') order by t.admission_date_time desc"
logger.info(u"sql：%s" % sql)
rows = oracleHelper.fetchall(connstr, sql)
dataR = pd.DataFrame(
rows, columns=["patient_id", "visit_id", "PATIENT_NAME"])
logger.info(u"由 %s 至 %s 执行开始执行" %
(d1.strftime("%Y-%m-%d %H:%M:%S"),
d2.strftime("%Y-%m-%d %H:%M:%S")))
for i, row in dataR.iterrows():
if isSaveXml:
pool.apply_async(
patVisit4Time2Xml,
(connstr, emrConnstr, Utility.toStr(
row["patient_id"]), Utility.toStr(row["visit_id"]),
Utility.toStr(row["PATIENT_NAME"]),
Utility.toStr(dBegin.year + k), isSaveXml, isSaveHtml,
isProduction))
if isSaveHtml:
pool.apply_async(patVisit4Time2Html,
(connstr, emrConnstr, htmlUrlBase,
Utility.toStr(row["patient_id"]),
Utility.toStr(row["visit_id"]),
Utility.toStr(row["PATIENT_NAME"]),
Utility.toStr(dBegin.year + k),
isSaveXml, isSaveHtml, isProduction))
logger.debug(u"[%d/%d]" % (i, dataR.iloc[:, 0].size))
pool.close()
pool.join(
) # 调用join之前，先调用close函数，否则会出错。执行完close后不会有新的进程加入到pool,join函数等待所有子进程结束
logger.info(u"由 %s 至 %s 执行完成" % (d1.strftime("%Y-%m-%d %H:%M:%S"),
d2.strftime("%Y-%m-%d %H:%M:%S")))
def patVisit(connstr, emrConnstr, htmlUrlBase, isSaveXml, isSaveHtml,
isProduction, pid, vid):
sql = """select t.patient_id,t.visit_id,M.PATIENT_NAME from dqmc_pat_visit t
left join dqmc_pat_master m on T.PATIENT_ID=M.PATIENT_ID where t.patient_id='""" + pid + """' and t.visit_id='""" + vid + """' order by t.admission_date_time desc"""
rows = oracleHelper.fetchall(connstr, sql)
for i, row in pd.DataFrame(
rows, columns=["patient_id", "visit_id",
"PATIENT_NAME"]).iterrows():
lhi = ldr2HbaseInterface(connstr, emrConnstr,
Utility.toStr(row["patient_id"]),
Utility.toStr(row["visit_id"]),
Utility.toStr(row["PATIENT_NAME"]),
"testMultiProc", isSaveXml, isSaveHtml,
isProduction)
if isSaveXml:
try:
lhi.toXmlfile(emrConnstr)
except IOError as info:
logger.error(
u"%s#%s#%s" % (Utility.toStr(
row["patient_id"]), Utility.toStr(row["visit_id"]),
Utility.toStr(row["PATIENT_NAME"])))
logger.error(u"info: %s" % info)
logger.error(u"traceback: %s" % traceback.print_exc())
logger.error(u'traceback.format_exc():\n%s' %
traceback.format_exc())
logger.error(
u'########################################################'
)
logger.error(
u'\n########################################################'
)
if isSaveHtml:
try:
lhi.toHtmlfile(emrConnstr, htmlUrlBase)
except IOError as info:
logger.error(
u"%s#%s#%s" % (Utility.toStr(
row["patient_id"]), Utility.toStr(row["visit_id"]),
Utility.toStr(row["PATIENT_NAME"])))
logger.error(u"info: %s" % info)
logger.error(u"traceback: %s" % traceback.print_exc())
logger.error(u'traceback.format_exc():\n%s' %
traceback.format_exc())
logger.error(
u'########################################################'
)
logger.error(
u'\n########################################################'
)
def comment(self, _fileName, _tffile, _tableName, _timefild):
ignoret = ["诊断类型名称"]
_sql = "select t.PATIENT_ID,t.VISIT_ID"
_columns = []
_columns.append("PATIENT_ID")
_columns.append("VISIT_ID")
f = open(_tffile) # 返回一个文件对象
line = f.readline()
while line:
if line.find("#") < 0:
tup = tuple(eval(line))
if tup[1] == "":
_sql += (",'' as " + tup[0])
else:
if (tup[2] in ignoret):
_sql += ("," + tup[1] + " as " + tup[0])
else:
_sql += (",t." + tup[1] + " as " + tup[0])
_columns.append(tup[0])
line = f.readline()
if not _timefild == None:
_sql += " from " + _tableName + " t inner join dqmc_pat_visit v on t." + _timefild + " between v.admission_date_time and v.discharge_date_time and v.patient_id=t.patient_id and v.patient_id='"
_sql += self.patient_id
_sql += "' and v.visit_id='"
_sql += self.visit_id
_sql += "'"
else:
_sql += " from " + _tableName + " t where t.patient_id='"
_sql += self.patient_id
_sql += "' and t.visit_id='"
_sql += self.visit_id
_sql += "'"
f.close()
rows = oracleHelper.fetchall(self._connstr, _sql)
data = pd.DataFrame(rows, columns=_columns)
items = ""
for index, row in data.iterrows():
values = ""
for c in _columns:
values += self._value % (c, Utility.txml(Utility.toStr(
row[c])), c)
items += self._items % (values)
xml = self._root % (items)
self.saveXmlFile(_fileName, xml)
def toHtmlfile(self, connstrEmr, htmlUrlBase):
sqlList = "SELECT PATIENT_ID, VISIT_ID, FILE_NO, FILE_NAME, TOPIC,LAST_MODIFY_DATE_TIME,MR_CODE FROM MR_FILE_INDEX I "
sqlList += "WHERE VISIT_ID = '" + self.visit_id + "' AND PATIENT_ID = '" + self.patient_id + \
"' AND regexp_like(i.MR_CODE,'AB03030101|AB03030102|AB03030201|AB030303|AB03030302|AB03030402|AB03030502|AB03030602|AB03030702|AB03031102|AB03031101|AB03031002|AB03031001|AB03030902|AB03030901|AB03030804|AB03030802|AB03030801|AP9902|AP9901|AP03030107|AP03030106|AP03030105|AP03030104|AP03030103|AL9904|AL03030101|AP9908|AP9906|AP9904|AD9940|AC9902|AC03030102|AC030301|AB9916|AB9914|AB03031202|AB03031201|AB03030701|AB03030601|AB03030501|AB03030401|AB03030301|AB03030202')" \
" AND (TOPIC LIKE '%脑血管住院病历' OR TOPIC LIKE '%脑血管病住院病历' OR TOPIC LIKE '%会商记录' OR TOPIC LIKE '%其他药物治疗' OR TOPIC LIKE '%非药物治疗' OR TOPIC LIKE '%住院期间血管事件及并发症' OR TOPIC LIKE '%出院时情况' OR TOPIC LIKE '%医疗质量评估')" \
" ORDER BY I.CREATE_DATE_TIME "
# +"'AND (TOPIC LIKE '%脑血管住院病历' OR TOPIC LIKE '%脑血管病住院病历' OR TOPIC LIKE '%会商记录' OR TOPIC LIKE '%其他药物治疗' OR TOPIC LIKE '%非药物治疗' OR TOPIC LIKE '%住院期间血管事件及并发症' OR TOPIC LIKE '%出院时情况' OR TOPIC LIKE '%医疗质量评估') ORDER BY I.CREATE_DATE_TIME "
rows = oracleHelper.fetchall(connstrEmr, sqlList)
rowsDataFrame = pd.DataFrame(rows,
columns=[
"PATIENT_ID", "VISIT_ID", "FILE_NO",
"FILE_NAME", "TOPIC",
"LAST_MODIFY_DATE_TIME", "MR_CODE"
])
if rowsDataFrame.iloc[:, 0].size == 0:
logger.warning(u"该患者无病历数据：%s#%s#%s" %
(self.patient_id, self.visit_id, self.patient_name))
logger.warning(u"sql: %s" % sqlList)
else:
logger.debug(u"该患者有%d份病历：%s#%s#%s" %
(rowsDataFrame.iloc[:, 0].size, self.patient_id,
self.visit_id, self.patient_name))
for i, row in rowsDataFrame.iterrows():
logger.debug(u"正在处理第%d份病历，共%d份病历：%s#%s#%s" %
(i + 1, rowsDataFrame.iloc[:, 0].size,
self.patient_id, self.visit_id, self.patient_name))
htmlUrl = htmlUrlBase + "?patient_id=" + Utility.toStr(
row["PATIENT_ID"]) + "&visit_id="
htmlUrl += Utility.toStr(row["VISIT_ID"]) + "&file_no="
htmlUrl += Utility.toStr(
row["FILE_NO"]) + "&file_name=" + Utility.toStr(
row["FILE_NAME"]) + "&topic=" + quote(
Utility.toStr(
row["TOPIC"])) + "&patient_name=" + quote(
self.patient_name)
try:
if Utility.toStr(row["TOPIC"]).find("会商") > 0:
if (saveHtmlHuiShang):
html = self.getHuiShang(Utility.toStr(
row["FILE_NAME"]))
if saveHtml4Dashuju:
self.saveHtmlFile(
Utility.toStr(row["TOPIC"]),
Utility.toStr(row["MR_CODE"]),
Utility.toStr(row["FILE_NAME"]),
Utility.toStr(row["LAST_MODIFY_DATE_TIME"]),
html)
if saveHtml4Ldr:
self.saveHtmlFile4Ldr(
Utility.toStr(row["TOPIC"]),
Utility.toStr(row["MR_CODE"]),
Utility.toStr(row["FILE_NAME"]),
Utility.toStr(row["LAST_MODIFY_DATE_TIME"]),
html)
else:
html = self.getHttp(htmlUrl)
if saveHtml4Dashuju:
self.saveHtmlFile(
Utility.toStr(row["TOPIC"]),
Utility.toStr(row["MR_CODE"]),
Utility.toStr(row["FILE_NAME"]),
Utility.toStr(row["LAST_MODIFY_DATE_TIME"]), html)
if saveHtml4Ldr:
self.saveHtmlFile4Ldr(
Utility.toStr(row["TOPIC"]),
Utility.toStr(row["MR_CODE"]),
Utility.toStr(row["FILE_NAME"]),
Utility.toStr(row["LAST_MODIFY_DATE_TIME"]), html)
except IOError as info:
logger.error(
u"%s#%s#%s" %
(self.patient_id, self.visit_id, self.patient_name))
logger.error(u"info: %s" % info)
logger.error(u"traceback: %s" % traceback.print_exc())
logger.error(u'traceback.format_exc():\n%s' %
traceback.format_exc())
logger.error(
u'########################################################'
)
logger.error(
u'\n########################################################'
)
logger.debug(u"处理完成%d份病历，共%d份病历：%s#%s#%s" %
(i + 1, rowsDataFrame.iloc[:, 0].size,
self.patient_id, self.visit_id, self.patient_name))
logger.debug(u"处理完成该患者%d共份病历：%s#%s#%s" %
(rowsDataFrame.iloc[:, 0].size, self.patient_id,
self.visit_id, self.patient_name))
def shouYe(self, _fileName, _tffile):
# connection = cx_Oracle.connect(self.emrConnstr)
# cursor = connection.cursor()
_sql = "select v.PATIENT_ID,v.VISIT_ID,V.patient_id as INP_NO,v.IDENTITY,v.UNIT_IN_CONTRACT,v.NEXT_OF_KIN_PHONE,v.NEXT_OF_KIN_ADDR,v.RELATIONSHIP,v.NEXT_OF_KIN,v.MAILING_ADDRESS as MAILING_ADDR_CITY_NAME,pv.OUT_REGISTER_DATE,'' as PHONE_NUMBER,'' as BUSINESS_PHONE_PHONE"
_columns = []
_columns.append("PATIENT_ID")
_columns.append("VISIT_ID")
_columns.append("INP_NO")
_columns.append("IDENTITY")
_columns.append("UNIT_IN_CONTRACT")
_columns.append("NEXT_OF_KIN_PHONE")
_columns.append("NEXT_OF_KIN_ADDR")
_columns.append("RELATIONSHIP")
_columns.append("NEXT_OF_KIN")
_columns.append("MAILING_ADDR_CITY_NAME")
_columns.append("OUT_REGISTER_DATE")
_columns.append("PHONE_NUMBER")
_columns.append("BUSINESS_PHONE_PHONE")
f = open(_tffile) # 返回一个文件对象
line = f.readline()
while line:
if line.find("#") < 0:
tup = tuple(eval(line))
if tup[1] == "":
_sql += (",'' as " + tup[0])
else:
if tup[1] == "DISCHARGE_PASS":
_sql += ", case v.DISCHARGE_PASS when '1' then '医嘱离院'when '2' then '医嘱转院'when '3' then '医嘱转社区'when '4' then '非医嘱离院'when '5' then '死亡'when '9' then '其他' else '其他' end as" + \
tup[0]
else:
_sql += (", " + tup[1] + " as " + tup[0])
_columns.append(tup[0])
line = f.readline()
if self.isProduction:
_sql += """ from pat_visit@toJHEMR v
inner join pat_master_index@toJHEMR m on m.patient_id=v.patient_id
inner join dqmc_pat_visit pv on pv.patient_id=v.patient_id and pv.visit_id=v.visit_id and v.patient_id='""" + self.patient_id + """' and v.visit_id='""" + self.visit_id + """'
left join DEPT_DICT@toJHEMR d on d.dept_code=v.DEPT_ADMISSION_TO
left join DEPT_DICT@toJHEMR d1 on d1.dept_code=v.DEPT_DISCHARGE_FROM
"""
else:
_sql += """ from pat_visit v
inner join pat_master_index m on m.patient_id=v.patient_id
inner join dqmc_pat_visit pv on pv.patient_id=v.patient_id and pv.visit_id=v.visit_id and v.patient_id='""" + self.patient_id + """' and v.visit_id='""" + self.visit_id + """'
left join DEPT_DICT d on d.dept_code=v.DEPT_ADMISSION_TO
left join DEPT_DICT d1 on d1.dept_code=v.DEPT_DISCHARGE_FROM
"""
#_sql += " from pat_visit v inner join pat_master_index m on m.patient_id=v.patient_id inner join dqmc_pat_visit pv on pv.patient_id=v.patient_id and pv.visit_id=v.visit_id and v.patient_id='" + self.patient_id + "' and v.visit_id='" + self.visit_id + "'"
f.close()
rows = oracleHelper.fetchall(self._connstr, _sql)
data = pd.DataFrame(rows, columns=_columns)
items = ""
for index, row in data.iterrows():
values = ""
for c in _columns:
values += self._value % (c, Utility.txml(Utility.toStr(
row[c])), c)
items += self._items % (values)
xml = self._root % (items)
self.saveXmlFile(_fileName, xml)
def labItem(self, _fileName, _tffile, _tableName):
try:
_sql = "select t.PATIENT_ID,t.VISIT_ID"
_columns = []
_columns.append("PATIENT_ID")
_columns.append("VISIT_ID")
f = open(_tffile) # 返回一个文件对象
line = f.readline()
while line:
if line.find("#") < 0:
tup = tuple(eval(line))
if tup[1] == "":
_sql += (",'' as " + tup[0])
else:
if tup[1] == "REPORT_DATE_TIME":
_sql += (
",to_char(to_date(to_char(lr.report_date_time,'yyyy-mm-dd')||' '||replace(lpad(lr.order_no,4),' ','0')||'00','yyyy/mm/dd hh24miss'),'yyyy-mm-dd hh24:mi:ss') as "
+ tup[0])
else:
_sql += (",t." + tup[1] + " as " + tup[0])
_columns.append(tup[0])
line = f.readline()
_sql += " from " + _tableName + """ t
inner join dqmc_lab_report lr on lr.lab_apply_no=t.lab_apply_no
inner join dqmc_pat_visit v on v.patient_id=lr.patient_id
and lr.report_date_time between v.admission_date_time and v.discharge_date_time
and v.patient_id='"""
_sql += self.patient_id
_sql += "' and v.visit_id='"
_sql += self.visit_id
_sql += "'"
f.close()
rows = oracleHelper.fetchall(self._connstr, _sql)
data = pd.DataFrame(rows, columns=_columns)
items = ""
for index, row in data.iterrows():
values = ""
for c in _columns:
values += self._value % (
c, Utility.txml(Utility.toStr(row[c])), c)
items += self._items % (values)
items = self._root % (items)
self.saveXmlFile(_fileName, items)
except IOError as info:
logger.error(u"%s#%s#%s" %
(self.patient_id, self.visit_id, self.patient_name))
logger.error(u"info: %s" % info)
logger.error(u"traceback: %s" % traceback.print_exc())
logger.error(u'traceback.format_exc():\n%s' %
traceback.format_exc())
logger.error(u"_tffile: %s" % _tffile)
logger.error(
u'########################################################')
logger.error(
u'\n########################################################')
def order(self, _fileName, _tffile, _tableName, _timefild):
# tffile="../transform/"
fileName_01 = "../transform/yizhushuju.txt"
tableName_01 = "dqmc_inpat_order"
_sql_01 = "select t.PATIENT_ID,t.VISIT_ID"
f = open(fileName_01) # 返回一个文件对象
line = f.readline()
while line:
if line.find("#") < 0:
tup = tuple(eval(line))
if tup[1] == "":
_sql_01 += (",'' as " + tup[0])
else:
_sql_01 += (",t." + tup[1] + " as " + tup[0])
line = f.readline()
_sql_01 += " from " + tableName_01 + " t where t.patient_id='"
_sql_01 += self.patient_id
_sql_01 += "' and t.visit_id='"
_sql_01 += self.visit_id
_sql_01 += "' union "
f.close()
# logger.info("_sql_01: "+_sql_01)
fileName_01 = "../transform/DQMC_MZ_EXECUTE_ORDERS_DUA_A.txt"
tableName_01 = "DQMC_MZ_EXECUTE_ORDERS_DUA_A"
_sql_01 += "select t.INP_NO as PATIENT_ID,t.VISIT_ID"
f = open(fileName_01) # 返回一个文件对象
line = f.readline()
while line:
if line.find("#") < 0:
tup = tuple(eval(line))
if tup[1] == "":
_sql_01 += (",'' as " + tup[0])
else:
_sql_01 += ("," + tup[1] + " as " + tup[0])
line = f.readline()
_sql_01 += " from " + tableName_01 + " t where t.INP_NO='"
_sql_01 += self.patient_id
_sql_01 += "' and t.visit_id='"
_sql_01 += self.visit_id
_sql_01 += "' union "
f.close()
# logger.info("_sql_01: "+_sql_01)
_sql = "select t.PATIENT_ID,t.VISIT_ID"
_columns = []
_columns.append("PATIENT_ID")
_columns.append("VISIT_ID")
f = open(_tffile) # 返回一个文件对象
line = f.readline()
while line:
if line.find("#") < 0:
tup = tuple(eval(line))
if tup[1] == "":
_sql += (",'' as " + tup[0])
else:
_sql += (",t." + tup[1] + " as " + tup[0])
_columns.append(tup[0])
line = f.readline()
_sql += " from " + _tableName + " t where t.cstype in('检查','处置') and t.patient_id='"
_sql += self.patient_id
_sql += "' and t.visit_id='"
_sql += self.visit_id
_sql += "'"
f.close()
# logger.info("_sql: "+_sql_01+_sql)
rows = oracleHelper.fetchall(self._connstr, _sql_01 + _sql)
data = pd.DataFrame(rows, columns=_columns)
items = ""
for index, row in data.iterrows():
values = ""
for c in _columns:
values += self._value % (c, Utility.txml(Utility.toStr(
row[c])), c)
items += self._items % (values)
xml = self._root % (items)
self.saveXmlFile(_fileName, xml)