public List <PrescriptionDetail> GetPrescriptionDetails(string cardNo, string startDate, string endDate)
{
string sqlMzhj = $"select * from (select 发票流水号,日期,卡号,病人姓名,科室ID,医师,YF_ID,名称,单位,数量,用法,用量 from 划价临时库 where 材质分类 in ('西药','中成药','中草药','中药颗粒','中药饮片') and 科室ID<>0 and 发票流水号<>'-1' union select 发票流水号,日期,卡号,病人姓名,科室ID,医师,YF_ID,名称,单位,数量,用法,用量 from 划价流水帐 where 材质分类 in ('西药','中成药','中草药','中药颗粒','中药饮片') and 科室ID<>0 and 发票流水号<>'-1') as Mzhj where 卡号='{cardNo}' and 日期 between '{startDate}' and '{endDate}'";
DataTable dtMzhj = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzhj).Tables[0];
List <PrescriptionDetail> prescriptionDetails = new List <PrescriptionDetail>();
if (dtMzhj != null && dtMzhj.Rows.Count > 0)
{
for (int i = 0; i < dtMzhj.Rows.Count; i++)
{
PrescriptionDetail prescriptionDetail = new PrescriptionDetail
{
MedicineNO = dtMzhj.Rows[i]["YF_ID"].ToString(),
MedicineName = dtMzhj.Rows[i]["名称"].ToString(),
MedicineCount = Convert.ToDouble(dtMzhj.Rows[i]["数量"]).ToString("0.00"),
MedicineUnit = dtMzhj.Rows[i]["单位"].ToString(),
Usage = dtMzhj.Rows[i]["用法"].ToString() + dtMzhj.Rows[i]["用量"].ToString(),
};
prescriptionDetails.Add(prescriptionDetail);
}
}
return(prescriptionDetails);
}
public IntoHospital GetIntoHospital(Request request)
{
string sqlBak = $"select top 1 * from(select 病人编号,住院号,姓名,入院日期,医保类型,性别,身份证号,年龄,出生日期,民族,婚否,职业,电话,家庭住址,联系人,联系人地址,联系电话,关系,入院诊断,病情,确诊诊断,出院日期,医师代码,科室,病室,床位 from ZY_病案库 where 住院号<>'0') as BAK where 住院号='{request.InPatientNo}' and 身份证号='{request.IdentCard}' order by 入院日期 desc";
DataTable dtBak = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlBak).Tables[0];
if (dtBak != null && dtBak.Rows.Count > 0)
{
string sqlPatBaseInf = $"select top 1 * from (select patId,PatName,MainNarrative,InDate,Diagnosis1 from EH_PatBasInf union select patId,PatName,MainNarrative,InDate,Diagnosis1 from EH_PatBasInfOut) as EH_PatBasInf where PatId='{dtBak.Rows[0]["住院号"].ToString()}' order by InDate desc";
DataTable dtPatBaseInf = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("DzblConnectionString"), sqlPatBaseInf).Tables[0];
IntoHospital intoHospital = new IntoHospital
{
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
HospitalName = ConfigurationManager.AppSettings["HospitalName"],
PatientName = dtBak.Rows[0]["姓名"].ToString(),
PatientAge = dtBak.Rows[0]["年龄"].ToString(),
IdentCard = dtBak.Rows[0]["身份证号"].ToString(),
GenderValue = dtBak.Rows[0]["性别"].ToString() == "男" ? "1" : "0",
Rcvdate = Convert.ToDateTime(dtBak.Rows[0]["入院日期"]).ToString("yyyy-MM-dd"),
ActionInChief = dtPatBaseInf != null && dtPatBaseInf.Rows.Count > 0 && dtPatBaseInf.Rows[0]["MainNarrative"].ToString() != "" ? dtPatBaseInf.Rows[0]["MainNarrative"].ToString() : "暂无",
AllergicHistory = "暂无",
HisPresentIllness = "暂无",
PastHistory = "暂无",
PersonalHistory = "暂无",
FamilyHistory = "暂无",
RecordDate = Convert.ToDateTime(dtBak.Rows[0]["入院日期"]).ToString("yyyy-MM-dd"),
PhysicalExamination = "暂无",
AssistantExamination = "暂无",
PrimaryDiagnosis = dtBak.Rows[0]["入院诊断"].ToString(),
Process = null,
BloodAmylase = null,
UrineRoutines = null,
AmylaseInUrine = null,
BloodRoutine = null,
ColorUltrasound = null,
DischargeDiagnosis = dtBak.Rows[0]["确诊诊断"].ToString(),
Recoder = dtBak.Rows[0]["医师代码"].ToString(),
RecordingTime = Convert.ToDateTime(dtBak.Rows[0]["入院日期"]).ToString("yyyy-MM-dd hh:mm:ss"),
T = null,
P = null,
R = null,
Bp = null,
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
PID = dtBak.Rows[0]["病人编号"].ToString(),
InpatientNo = dtBak.Rows[0]["住院号"].ToString(),
DzjkNo = null
};
return(intoHospital);
}
else
{
return(null);
}
}
public Prescription GetPrescription(Request request)
{
string sqlMzsf = $"select top 1 * from (select 病人姓名,门诊号,卡号,日期,医疗保险号,医保 from 门诊收费流水帐 union select 病人姓名,门诊号,卡号,日期,医疗保险号,医保 from 门诊收费) as MZSF where 卡号='{request.OutPatientNo}' order by 日期 desc";
string sqlGh = $"select * from (select 卡号,门诊号,姓名,出生日期,年龄,性别,民族,身份证,婚姻,职业,通信地址,电话 from 门诊挂号流水帐 union select 卡号,门诊号,姓名,出生日期,年龄,性别,民族,身份证,婚姻,职业,通信地址,电话 from 门诊挂号) as GH where 卡号='{request.OutPatientNo}'";
string sqlMzbl = $"select top 1 卡号,门诊号,时间,过敏史,主诉,现病史,既往史,望闻问切,西医诊断,中医诊断,处理,体检,病程,建议 from MZ_BL where 卡号='{request.OutPatientNo}' order by 时间 desc";
DataTable dtMzsf = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzsf).Tables[0];
DataTable dtGh = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("GHConnectionString"), sqlGh).Tables[0];
DataTable dtMzbl = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzbl).Tables[0];
if (dtMzsf != null && dtMzsf.Rows.Count > 0 && dtGh != null && dtGh.Rows.Count > 0)
{
string startDate = Convert.ToDateTime(dtMzsf.Rows[0]["日期"]).ToString("yyyy-MM-dd") + " 00:00:00";
string endDate = Convert.ToDateTime(dtMzsf.Rows[0]["日期"]).ToString("yyyy-MM-dd") + " 23:59:59";
string sqlMzhj = $"select * from (select 发票流水号,日期,卡号,病人姓名,科室ID,医师,YF_ID,名称,单位,数量,用法,用量,材质分类 from 划价临时库 where 材质分类 in ('西药','中成药','中草药','中药颗粒','中药饮片') and 科室ID<>0 and 发票流水号<>'-1' union select 发票流水号,日期,卡号,病人姓名,科室ID,医师,YF_ID,名称,单位,数量,用法,用量,材质分类 from 划价流水帐 where 材质分类 in ('西药','中成药','中草药','中药颗粒','中药饮片') and 科室ID<>0 and 发票流水号<>'-1') as Mzhj where 卡号='{dtMzsf.Rows[0]["卡号"].ToString()}' and 日期 between '{startDate}' and '{endDate}'";
DataTable dtMzhj = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzhj).Tables[0];
if (dtMzhj != null && dtMzhj.Rows.Count > 0)
{
Prescription prescription = new Prescription
{
MedicalCardNo = dtMzsf.Rows[0]["卡号"].ToString(),
OutPatientNo = dtMzsf.Rows[0]["卡号"].ToString(),
PatientName = dtGh.Rows[0]["姓名"].ToString(),
PatientAge = dtGh.Rows[0]["年龄"].ToString(),
IdentCard = dtGh.Rows[0]["身份证"].ToString(),
GenderValue = dtGh.Rows[0]["性别"].ToString() == "男" ? "1" : "0",
ClinicalDiagnosis = dtMzbl != null && dtMzbl.Rows.Count > 0 && dtMzbl.Rows[0]["西医诊断"].ToString() != "" ? dtMzbl.Rows[0]["西医诊断"].ToString() : "暂无",
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
HospitalName = ConfigurationManager.AppSettings["HospitalName"],
OrderDate = Convert.ToDateTime(dtMzsf.Rows[0]["日期"]).ToString("yyyy-MM-dd"),
DoctorDiagnosis = dtMzbl != null && dtMzbl.Rows.Count > 0 && dtMzbl.Rows[0]["西医诊断"].ToString() != "" ? dtMzbl.Rows[0]["西医诊断"].ToString() : null,
PrescripName = "门诊处方",
PrescripTypeId = dtMzhj.Rows[0]["材质分类"].ToString() == "西药" ? "2" : "1",
PrescripTypeName = dtMzhj.Rows[0]["材质分类"].ToString() == "西药" ? "2" : "1",
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
PID = dtMzsf.Rows[0]["卡号"].ToString(),
DzjkNo = null
};
return(prescription);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
public List <JYDetail> GetJYDetails(JYDetailRequest request)
{
string sqlJyDetail = $"select testno,itemno,itemname,testresult,isnull(resultflag,'') as resultflag,isnull(units,'') as units,isnull(ranges,'') as ranges from lis_reqresult where testno='{request.ReqId}' order by seqno";
DataTable dtJyDetail = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HosdataConnectionString"), sqlJyDetail).Tables[0];
if (dtJyDetail != null && dtJyDetail.Rows.Count > 0)
{
List <JYDetail> jYDetails = new List <JYDetail>();
for (int i = 0; i < dtJyDetail.Rows.Count; i++)
{
JYDetail jYDetail = new JYDetail
{
ItemName = dtJyDetail.Rows[i]["itemname"].ToString(),
Result = dtJyDetail.Rows[i]["testresult"].ToString(),
Units = dtJyDetail.Rows[i]["units"].ToString() != "" ? dtJyDetail.Rows[i]["units"].ToString() : "暂无",
ResultRange = dtJyDetail.Rows[i]["ranges"].ToString() != "" ? dtJyDetail.Rows[i]["ranges"].ToString() : "暂无",
Code = dtJyDetail.Rows[i]["itemno"].ToString(),
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
ReqId = request.ReqId
};
switch (dtJyDetail.Rows[i]["resultflag"].ToString())
{
case "M":
jYDetail.Indicator = "0";
break;
case "L":
jYDetail.Indicator = "1";
break;
case "H":
jYDetail.Indicator = "2";
break;
default:
jYDetail.Indicator = "3";
break;
}
jYDetails.Add(jYDetail);
}
return(jYDetails);
}
else
{
return(null);
}
}
public OutHospital GetOutHospital(Request request)
{
string sqlBak = $"select top 1 * from(select 病人编号,住院号,姓名,入院日期,医保类型,性别,身份证号,年龄,出生日期,民族,婚否,职业,电话,家庭住址,联系人,联系人地址,联系电话,关系,入院诊断,病情,确诊诊断,出院日期,治疗情况,住院天数,医师代码,科室,病室,床位 from ZY_病案库 where 住院号<>'0') as BAK where 出院日期 is not null and 住院号='{request.InPatientNo}' and 身份证号='{request.IdentCard}' order by 入院日期 desc";
DataTable dtBak = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlBak).Tables[0];
if (dtBak != null && dtBak.Rows.Count > 0)
{
string sqlDoc = $"select id,医师代码,医师姓名,所在科室,挂号科室,划价号 from 医师代码 where 医师姓名='{dtBak.Rows[0]["医师代码"].ToString()}'";
DataTable dtDoc = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlDoc).Tables[0];
OutHospital outHospital = new OutHospital
{
PatientName = dtBak.Rows[0]["姓名"].ToString(),
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
HospitalName = ConfigurationManager.AppSettings["HospitalName"],
GenderValue = dtBak.Rows[0]["性别"].ToString() == "男" ? "1" : "0",
PatientAge = dtBak.Rows[0]["年龄"].ToString(),
IdentCard = dtBak.Rows[0]["身份证号"].ToString(),
InpatientNo = dtBak.Rows[0]["住院号"].ToString(),
InTime = Convert.ToDateTime(dtBak.Rows[0]["入院日期"]).ToString("yyyy-MM-dd hh:mm:ss"),
OutTime = dtBak.Rows[0]["出院日期"] is DBNull ? null : Convert.ToDateTime(dtBak.Rows[0]["出院日期"]).ToString("yyyy-MM-dd hh:mm:ss"),
Rcvdiag = dtBak.Rows[0]["入院诊断"].ToString(),
Lvediag = dtBak.Rows[0]["确诊诊断"].ToString(),
Rcvsymptom = dtBak.Rows[0]["入院诊断"].ToString(),
MainChecked = "暂无",
DiagCourse = "暂无",
Lvesymptom = dtBak.Rows[0]["治疗情况"].ToString() != ""? dtBak.Rows[0]["治疗情况"].ToString():"暂无",
Lveda = "暂无",
Diagresult = dtBak.Rows[0]["治疗情况"].ToString() != "" ? dtBak.Rows[0]["治疗情况"].ToString() : "暂无",
AttdoctNo = dtDoc != null && dtDoc.Rows.Count > 0 ? dtDoc.Rows[0]["医师代码"].ToString() : "暂无",
Attdoct = dtBak.Rows[0]["医师代码"].ToString(),
PathologyNo = null,
FillingData = dtBak.Rows[0]["出院日期"] is DBNull ? null : Convert.ToDateTime(dtBak.Rows[0]["出院日期"]).ToString("yyyy-MM-dd hh:mm:ss"),
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
PID = dtBak.Rows[0]["病人编号"].ToString(),
DzjkNo = null
};
return(outHospital);
}
else
{
return(null);
}
}
public List <PatientCharged> GetPatientChargeds(Request request)
{
string sqlBak = $"select top 1 * from(select 病人编号,住院号,姓名,入院日期,医保类型,性别,身份证号,年龄,出生日期,民族,婚否,职业,电话,家庭住址,联系人,联系人地址,联系电话,关系,入院诊断,病情,确诊诊断,出院日期,治疗情况,住院天数,医师代码,科室,病室,床位 from ZY_病案库 where 住院号<>'0') as BAK where 住院号='{request.InPatientNo}' and 身份证号='{request.IdentCard}' order by 入院日期 desc";
DataTable dtBak = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlBak).Tables[0];
if (dtBak != null && dtBak.Rows.Count > 0)
{
string sqlYz = $"select * from (select 病人编号,科室,医师,医嘱类别,日期,名称,规格,单位,单价,数量,用法,用量,执行频率,开始时间,执行人,执行时间,停止时间 from ZY_长期医嘱 union select 病人编号,科室,医师,医嘱类别,日期,名称,规格,单位,单价,数量,用法,用量,执行频率,开始时间,执行人,执行时间,停止时间 from ZY_临时医嘱) as YZ where 病人编号='{dtBak.Rows[0]["病人编号"].ToString()}' order by 开始时间";
DataTable dtYz = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlYz).Tables[0];
if (dtYz != null && dtYz.Rows.Count > 0)
{
string sqlDep = $"select 科室Id, 科室代码,科室名称 from 科室 where 科室名称='{dtBak.Rows[0]["科室"].ToString()}'";
DataTable dtDep = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlDep).Tables[0];
List <PatientCharged> patientChargeds = new List <PatientCharged>();
for (int i = 0; i < dtYz.Rows.Count; i++)
{
PatientCharged patientCharged = new PatientCharged
{
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
HospitalName = ConfigurationManager.AppSettings["HospitalName"],
InpatientNo = dtBak.Rows[0]["住院号"].ToString(),
PatientName = dtBak.Rows[0]["姓名"].ToString(),
PatientAge = dtBak.Rows[0]["年龄"].ToString(),
IdentCard = dtBak.Rows[0]["身份证号"].ToString(),
MedicalCardNo = "暂无",
GenderValue = dtBak.Rows[0]["性别"].ToString() == "男" ? "1" : "0",
Departments = dtDep != null && dtDep.Rows.Count > 0 ? dtDep.Rows[0]["科室名称"].ToString() : "暂无",
Promdate = dtYz.Rows[i]["开始时间"] is DBNull ? "暂无" : Convert.ToDateTime(dtYz.Rows[i]["开始时间"]).ToString("yyyy-MM-dd hh:mm:ss"),
DAmemo = dtYz.Rows[i]["名称"].ToString(),
Doctor = dtYz.Rows[i]["医师"].ToString(),
Nurse = dtYz.Rows[i]["执行人"] is DBNull ? "暂无" : dtYz.Rows[i]["执行人"].ToString(),
DAtype = dtYz.Rows[i]["医嘱类别"].ToString() == "长期医嘱" ? "1" : "2",
Execdate = dtYz.Rows[i]["执行时间"] is DBNull ? "暂无" : Convert.ToDateTime(dtYz.Rows[i]["执行时间"]).ToString("yyyy-MM-dd hh:mm:ss"),
Enddate = dtYz.Rows[i]["停止时间"] is DBNull ? "暂无" : Convert.ToDateTime(dtYz.Rows[i]["停止时间"]).ToString("yyyy-MM-dd hh:mm:ss"),
Medspec = dtYz.Rows[i]["规格"] is DBNull || dtYz.Rows[i]["规格"].ToString().Equals("") ? "暂无" : dtYz.Rows[i]["规格"].ToString(),
Medusage = dtYz.Rows[i]["用法"] is DBNull || dtYz.Rows[i]["用法"].ToString().Equals("") ? "暂无" : dtYz.Rows[i]["用法"].ToString(),
Dose = dtYz.Rows[i]["用量"] is DBNull || dtYz.Rows[i]["用量"].ToString().Equals("") ? "暂无" : dtYz.Rows[i]["用量"].ToString(),
Frequency = dtYz.Rows[i]["执行频率"] is DBNull || dtYz.Rows[i]["执行频率"].ToString().Equals("") ? "暂无" : dtYz.Rows[i]["执行频率"].ToString(),
Provide = dtYz.Rows[i]["用量"] is DBNull || dtYz.Rows[i]["用量"].ToString().Equals("") ? "暂无" : dtYz.Rows[i]["用量"].ToString(),
Checkpart = "暂无",
Remark = "暂无",
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
PID = dtBak.Rows[0]["病人编号"].ToString(),
DzjkNo = null
};
patientChargeds.Add(patientCharged);
}
return(patientChargeds);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
public Patient GetPatient(Request request)
{
Patient patient;
bool isBak = true;
DateTime?dateBak = null, dateMzsf = null;
string sqlBak = $"select top 1 * from(select 病人编号,住院号,姓名,入院日期,医保类型,性别,身份证号,年龄,出生日期,民族,婚否,职业,电话,家庭住址,联系人,联系人地址,联系电话,关系,入院诊断,确诊诊断,出院日期,医师代码,科室,病室,床位 from ZY_病案库 where 住院号<>'0') as BAK where 身份证号='{request.IdentCard}' order by 入院日期 desc";
DataTable dtBak = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlBak).Tables[0];
string sqlGh = $"select * from (select 卡号,门诊号,姓名,出生日期,年龄,性别,民族,身份证,婚姻,职业,通信地址,电话 from 门诊挂号流水帐 union select 卡号,门诊号,姓名,出生日期,年龄,性别,民族,身份证,婚姻,职业,通信地址,电话 from 门诊挂号) as GH where 身份证='{request.IdentCard}'";
DataTable dtGh = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("GHConnectionString"), sqlGh).Tables[0];
DataTable dtMzsf = null;
if (dtBak != null && dtBak.Rows.Count > 0)
{
dateBak = Convert.ToDateTime(dtBak.Rows[0]["入院日期"]);
}
if (dtGh != null && dtGh.Rows.Count > 0)
{
string cardNo = dtGh.Rows[0]["卡号"].ToString();
string sqlMzsf = $"select top 1 * from (select 病人姓名,门诊号,卡号,日期,医疗保险号,医保 from 门诊收费流水帐 union select 病人姓名,门诊号,卡号,日期,医疗保险号,医保 from 门诊收费) as MZSF where 卡号='{cardNo}' order by 日期 desc";
dtMzsf = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzsf).Tables[0];
if (dtMzsf != null && dtMzsf.Rows.Count > 0)
{
dateMzsf = Convert.ToDateTime(dtMzsf.Rows[0]["日期"]);
}
}
if (dateBak == null && dateMzsf == null)
{
return(null);
}
else if (dateBak != null && dateMzsf == null)
{
isBak = true;
}
else if (dateBak == null && dateMzsf != null)
{
isBak = false;
}
else
{
if (dateBak >= dateMzsf)
{
isBak = true;
}
else
{
isBak = false;
}
}
if (isBak)
{
string sqlDep = $"select 科室Id, 科室代码,科室名称 from 科室 where 科室名称='{dtBak.Rows[0]["科室"].ToString()}'";
string sqlDoc = $"select id,医师代码,医师姓名,所在科室,挂号科室,划价号 from 医师代码 where 医师姓名='{dtBak.Rows[0]["医师代码"].ToString()}'";
string sqlPatBaseInf = $"select top 1 * from (select patId,PatName,MainNarrative,InDate,Diagnosis1 from EH_PatBasInf union select patId,PatName,MainNarrative,InDate,Diagnosis1 from EH_PatBasInfOut) as EH_PatBasInf where PatId='{dtBak.Rows[0]["住院号"].ToString()}' order by InDate desc";
DataTable dtDep = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlDep).Tables[0];
DataTable dtDoc = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlDoc).Tables[0];
DataTable dtPatBaseInf = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("DzblConnectionString"), sqlPatBaseInf).Tables[0];
patient = new Patient
{
PID = dtBak.Rows[0]["病人编号"].ToString(),
PatientName = dtBak.Rows[0]["姓名"].ToString(),
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
HospitalName = ConfigurationManager.AppSettings["HospitalName"],
GenderValue = dtBak.Rows[0]["性别"].ToString() == "男" ? "1" : "0",
PatientAge = (DateTime.Now.Year - Convert.ToDateTime(dtBak.Rows[0]["出生日期"]).Year).ToString(),
Birthday = Convert.ToDateTime(dtBak.Rows[0]["出生日期"]).ToString("yyyy-MM-dd"),
Nation = dtBak.Rows[0]["民族"].ToString(),
PatientStature = 0,
PatientWeight = 0,
Phone = dtBak.Rows[0]["电话"].ToString() != "" ? dtBak.Rows[0]["电话"].ToString() : "暂无",
Address = dtBak.Rows[0]["家庭住址"].ToString() != "" ? dtBak.Rows[0]["家庭住址"].ToString() : "暂无",
Contacts = dtBak.Rows[0]["联系人"].ToString(),
RelationShip = dtBak.Rows[0]["关系"].ToString(),
ContactPhone = dtBak.Rows[0]["联系电话"].ToString(),
ContactAddress = dtBak.Rows[0]["联系人地址"].ToString(),
IdentCard = dtBak.Rows[0]["身份证号"].ToString(),
InsuranceTypeCode = "暂无",
InsuranceTypeName = dtBak.Rows[0]["医保类型"].ToString(),
InsuranceNo = "暂无",
DeptCode = dtDep != null && dtDep.Rows.Count > 0 ? dtDep.Rows[0]["科室代码"].ToString() : "暂无",
DeptName = dtBak.Rows[0]["科室"].ToString(),
AppDiagonse = dtPatBaseInf != null && dtPatBaseInf.Rows.Count > 0 && dtPatBaseInf.Rows[0]["Diagnosis1"].ToString() != "" ? dtPatBaseInf.Rows[0]["Diagnosis1"].ToString() : "暂无",
ChiefComplaint = dtPatBaseInf != null && dtPatBaseInf.Rows.Count > 0 && dtPatBaseInf.Rows[0]["MainNarrative"].ToString() != "" ? dtPatBaseInf.Rows[0]["MainNarrative"].ToString() : "暂无",
MedicalHistory = "暂无",
PatientSign = "暂无",
PatientExtraStudy = "暂无",
AuxiliaryRecord = "暂无",
CheckTime = "暂无",
OutPatientNo = "暂无",
InpatientNo = dtBak.Rows[0]["住院号"].ToString(),
InTime = Convert.ToDateTime(dtBak.Rows[0]["入院日期"]).ToString("yyyy-MM-dd hh:mm:ss"),
OutTime = dtBak.Rows[0]["出院日期"] is DBNull ? null : Convert.ToDateTime(dtBak.Rows[0]["出院日期"]).ToString("yyyy-MM-dd hh:mm:ss"),
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
DzjkNo = null
};
return(patient);
}
else
{
patient = new Patient
{
PID = dtMzsf.Rows[0]["卡号"].ToString(),
OutPatientNo = dtMzsf.Rows[0]["卡号"].ToString(),
InsuranceNo = "暂无",
CheckTime = Convert.ToDateTime(dtMzsf.Rows[0]["日期"]).ToString("yyyy-MM-dd hh:mm:ss"),
PatientName = dtGh.Rows[0]["姓名"].ToString(),
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
HospitalName = ConfigurationManager.AppSettings["HospitalName"],
GenderValue = dtGh.Rows[0]["性别"].ToString() == "男" ? "1" : "0",
PatientAge = dtGh.Rows[0]["年龄"].ToString(),
Birthday = "暂无",
Nation = dtGh.Rows[0]["民族"].ToString().Trim(),
PatientStature = 0,
PatientWeight = 0,
Phone = dtGh.Rows[0]["电话"].ToString() != "" ? dtGh.Rows[0]["电话"].ToString() : "暂无",
Address = dtGh.Rows[0]["通信地址"].ToString().Trim(),
Contacts = null,
RelationShip = null,
ContactPhone = null,
ContactAddress = null,
IdentCard = dtGh.Rows[0]["身份证"].ToString(),
InsuranceTypeCode = "暂无",
InsuranceTypeName = "暂无",
DeptCode = "暂无",
DeptName = "暂无",
InpatientNo = "暂无",
InTime = "暂无",
OutTime = null,
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
DzjkNo = null
};
string sqlMzbl = $"select top 1 卡号,门诊号,时间,过敏史,主诉,现病史,既往史,望闻问切,西医诊断,中医诊断,处理,体检,病程,建议 from MZ_BL where 卡号={dtGh.Rows[0]["卡号"].ToString()} order by 时间 desc";
DataTable dtMzbl = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzbl).Tables[0];
if (dtMzbl != null && dtMzbl.Rows.Count > 0)
{
patient.AppDiagonse = dtMzbl.Rows[0]["西医诊断"].ToString().Equals("") ? "暂无" : dtMzbl.Rows[0]["西医诊断"].ToString();
patient.ChiefComplaint = dtMzbl.Rows[0]["主诉"].ToString().Equals("") ? "暂无" : dtMzbl.Rows[0]["主诉"].ToString();
patient.MedicalHistory = dtMzbl.Rows[0]["现病史"].ToString().Equals("") ? "暂无" : dtMzbl.Rows[0]["现病史"].ToString();
patient.PatientSign = "暂无";
patient.PatientExtraStudy = "暂无";
patient.AuxiliaryRecord = "暂无";
}
else
{
patient.AppDiagonse = "暂无";
patient.ChiefComplaint = "暂无";
patient.MedicalHistory = "暂无";
patient.PatientSign = "暂无";
patient.PatientExtraStudy = "暂无";
patient.AuxiliaryRecord = "暂无";
}
return(patient);
}
}
public List <JYRecord> GetJYRecords(Request request)
{
if (string.IsNullOrEmpty(request.OutPatientNo))
{
string sqlBak = $"select top 1 * from(select 病人编号,住院号,姓名,入院日期,医保类型,性别,身份证号,年龄,出生日期,民族,婚否,职业,电话,家庭住址,联系人,联系人地址,联系电话,关系,入院诊断,确诊诊断,出院日期,医师代码,科室,病室,床位 from ZY_病案库 where 住院号<>'0') as BAK where 住院号='{request.InPatientNo}' and 身份证号='{request.IdentCard}' order by 入院日期 desc";
DataTable dtBak = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlBak).Tables[0];
if (dtBak != null && dtBak.Rows.Count > 0)
{
string sqlJy = $"select main.sqh,main.ksdh,main.sqys,detail.xh,detail.sqxmmc,main.brdh,main.brxm,main.sqsj,main.zxsj,main.yblx from Lis_reqdetail as detail left join lis_reqmain as main on detail.sqh=main.sqh where main.brdh = '{dtBak.Rows[0]["住院号"].ToString()}' order by detail.sqh,detail.xh";
string sqlDep = $"select 科室Id, 科室代码,科室名称 from 科室 where 科室名称='{dtBak.Rows[0]["科室"].ToString()}'";
string sqlDoc = $"select id,医师代码,医师姓名,所在科室,挂号科室,划价号 from 医师代码 where 医师姓名='{dtBak.Rows[0]["医师代码"].ToString()}'";
string sqlPatBaseInf = $"select top 1 * from (select patId,PatName,MainNarrative,InDate,Diagnosis1 from EH_PatBasInf union select patId,PatName,MainNarrative,InDate,Diagnosis1 from EH_PatBasInfOut) as EH_PatBasInf where PatId='{dtBak.Rows[0]["住院号"].ToString()}' order by InDate desc";
DataTable dtJy = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HosdataConnectionString"), sqlJy).Tables[0];
DataTable dtDep = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlDep).Tables[0];
DataTable dtDoc = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlDoc).Tables[0];
DataTable dtPatBaseInf = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("DzblConnectionString"), sqlPatBaseInf).Tables[0];
if (dtJy != null && dtJy.Rows.Count > 0)
{
List <JYRecord> jYRecords = new List <JYRecord>();
for (int i = 0; i < dtJy.Rows.Count; i++)
{
JYRecord jYRecord = new JYRecord
{
ReqId = dtJy.Rows[i]["sqh"].ToString(),
ItemClass = dtJy.Rows[i]["yblx"].ToString(),
ItemSubClass = "暂无",
DeptCode = dtDep != null && dtDep.Rows.Count > 0 ? dtDep.Rows[0]["科室代码"].ToString() : "暂无",
Dept = dtJy.Rows[i]["ksdh"].ToString(),
HosName = ConfigurationManager.AppSettings["HospitalName"],
IsKey = "0",
CheckType = "1",
CheckTime = Convert.ToDateTime(dtJy.Rows[i]["zxsj"]).ToString("yyyy-MM-dd hh:mm:ss"),
ReportTime = Convert.ToDateTime(dtJy.Rows[i]["zxsj"]).ToString("yyyy-MM-dd hh:mm:ss"),
ReceiveTime = Convert.ToDateTime(dtJy.Rows[i]["sqsj"]).ToString("yyyy-MM-dd hh:mm:ss"),
CollectTime = Convert.ToDateTime(dtJy.Rows[i]["sqsj"]).ToString("yyyy-MM-dd hh:mm:ss"),
Diagnostic = dtPatBaseInf != null && dtPatBaseInf.Rows.Count > 0 && dtPatBaseInf.Rows[0]["Diagnosis1"].ToString() != "" ? dtPatBaseInf.Rows[0]["Diagnosis1"].ToString() : "暂无",
State = "0",
PatientName = dtBak.Rows[0]["姓名"].ToString(),
GenderValue = dtBak.Rows[0]["性别"].ToString() == "男" ? "1" : "0",
PatientAge = (DateTime.Now.Year - Convert.ToDateTime(dtBak.Rows[0]["出生日期"]).Year).ToString(),
AppDiagonse = dtPatBaseInf != null && dtPatBaseInf.Rows.Count > 0 && dtPatBaseInf.Rows[0]["Diagnosis1"].ToString() != "" ? dtPatBaseInf.Rows[0]["Diagnosis1"].ToString() : "暂无",
ChiefComplaint = dtPatBaseInf != null && dtPatBaseInf.Rows.Count > 0 && dtPatBaseInf.Rows[0]["MainNarrative"].ToString() != "" ? dtPatBaseInf.Rows[0]["MainNarrative"].ToString() : "暂无",
MedicalHistory = "暂无",
CheckPurpose = dtJy.Rows[i]["sqxmmc"].ToString(),
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
PID = dtBak.Rows[0]["病人编号"].ToString(),
IdentCard = dtBak.Rows[0]["身份证号"].ToString(),
OutPatientNo = "暂无",
InpatientNo = dtBak.Rows[0]["住院号"].ToString(),
DzjkNo = null
};
jYRecords.Add(jYRecord);
}
return(jYRecords);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
else
{
string sqlMzsf = $"select top 1 * from (select 病人姓名,门诊号,卡号,日期,医疗保险号,医保 from 门诊收费流水帐 union select 病人姓名,门诊号,卡号,日期,医疗保险号,医保 from 门诊收费) as MZSF where 卡号='{request.OutPatientNo}' order by 日期 desc";
DataTable dtMzsf = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzsf).Tables[0];
if (dtMzsf != null && dtMzsf.Rows.Count > 0)
{
string startDate = Convert.ToDateTime(dtMzsf.Rows[0]["日期"]).ToString("yyyy-MM-dd") + " 00:00:00";
string endDate = Convert.ToDateTime(dtMzsf.Rows[0]["日期"]).ToString("yyyy-MM-dd") + " 23:59:59";
string sqlJy = $"select main.sqh,main.ksdh,main.sqys,detail.xh,detail.sqxmmc,main.brdh,main.brxm,main.sqsj,main.zxsj,main.yblx from Lis_reqdetail as detail left join lis_reqmain as main on detail.sqh=main.sqh where main.brdh = '{request.OutPatientNo}' and sqsj between '{startDate}' and '{endDate}' order by detail.sqh,detail.xh";
DataTable dtJy = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HosdataConnectionString"), sqlJy).Tables[0];
if (dtJy != null && dtJy.Rows.Count > 0)
{
string sqlGh = $"select * from (select 卡号,门诊号,姓名,出生日期,年龄,性别,民族,身份证,婚姻,职业,通信地址,电话 from 门诊挂号流水帐 union select 卡号,门诊号,姓名,出生日期,年龄,性别,民族,身份证,婚姻,职业,通信地址,电话 from 门诊挂号) as GH where 卡号={request.OutPatientNo}";
DataTable dtGh = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("GHConnectionString"), sqlGh).Tables[0];
string sqlMzbl = $"select top 1 卡号,门诊号,时间,过敏史,主诉,现病史,既往史,望闻问切,西医诊断,中医诊断,处理,体检,病程,建议 from MZ_BL where 卡号={request.OutPatientNo} order by 时间 desc";
DataTable dtMzbl = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzbl).Tables[0];
List <JYRecord> jYRecords = new List <JYRecord>();
for (int i = 0; i < dtJy.Rows.Count; i++)
{
JYRecord jYRecord = new JYRecord
{
ReqId = dtJy.Rows[i]["sqh"].ToString(),
ItemClass = dtJy.Rows[i]["yblx"].ToString(),
ItemSubClass = "暂无",
DeptCode = dtJy.Rows[i]["ksdh"].ToString(),
Dept = dtJy.Rows[i]["ksdh"].ToString(),
HosName = ConfigurationManager.AppSettings["HospitalName"],
IsKey = "0",
CheckType = "0",
CheckTime = Convert.ToDateTime(dtJy.Rows[i]["zxsj"]).ToString("yyyy-MM-dd"),
ReportTime = Convert.ToDateTime(dtJy.Rows[i]["zxsj"]).ToString("yyyy-MM-dd"),
ReceiveTime = Convert.ToDateTime(dtJy.Rows[i]["sqsj"]).ToString("yyyy-MM-dd"),
CollectTime = Convert.ToDateTime(dtJy.Rows[i]["sqsj"]).ToString("yyyy-MM-dd"),
Diagnostic = dtMzbl != null && dtMzbl.Rows.Count > 0 && dtMzbl.Rows[0]["西医诊断"].ToString() != "" ? dtMzbl.Rows[0]["西医诊断"].ToString() : "暂无",
State = "0",
PatientName = dtGh.Rows[0]["姓名"].ToString(),
GenderValue = dtGh.Rows[0]["性别"].ToString() == "男" ? "1" : "0",
PatientAge = dtGh.Rows[0]["年龄"].ToString(),
AppDiagonse = dtMzbl != null && dtMzbl.Rows.Count > 0 && dtMzbl.Rows[0]["西医诊断"].ToString() != "" ? dtMzbl.Rows[0]["西医诊断"].ToString() : "暂无",
ChiefComplaint = dtMzbl != null && dtMzbl.Rows.Count > 0 && dtMzbl.Rows[0]["主诉"].ToString() != "" ? dtMzbl.Rows[0]["主诉"].ToString() : "暂无",
MedicalHistory = "暂无",
CheckPurpose = dtJy.Rows[i]["sqxmmc"].ToString(),
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
PID = dtMzsf.Rows[0]["卡号"].ToString(),
IdentCard = dtGh.Rows[0]["身份证"].ToString(),
OutPatientNo = dtMzsf.Rows[0]["卡号"].ToString(),
InpatientNo = "暂无",
DzjkNo = null
};
jYRecords.Add(jYRecord);
}
return(jYRecords);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}
public PatientMedicalRecord GetPatientMedicalRecord(Request request)
{
string sqlMzsf = $"select top 1 * from (select 病人姓名,门诊号,卡号,日期,医疗保险号,医保 from 门诊收费流水帐 union select 病人姓名,门诊号,卡号,日期,医疗保险号,医保 from 门诊收费) as MZSF where 卡号='{request.OutPatientNo}' order by 日期 desc";
string sqlGh = $"select top 1 * from (select 卡号,门诊号,姓名,出生日期,年龄,性别,民族,身份证,婚姻,职业,通信地址,电话 from 门诊挂号流水帐 union select 卡号,门诊号,姓名,出生日期,年龄,性别,民族,身份证,婚姻,职业,通信地址,电话 from 门诊挂号) as GH where 卡号='{request.OutPatientNo}'";
string sqlMzbl = $"select top 1 卡号,门诊号,时间,过敏史,主诉,现病史,既往史,望闻问切,西医诊断,中医诊断,处理,体检,病程,建议 from MZ_BL where 卡号='{request.OutPatientNo}' order by 时间 desc";
DataTable dtMzsf = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzsf).Tables[0];
DataTable dtGh = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("GHConnectionString"), sqlGh).Tables[0];
DataTable dtMzbl = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzbl).Tables[0];
if (dtMzsf != null && dtMzsf.Rows.Count > 0 && dtGh != null && dtGh.Rows.Count > 0)
{
PatientMedicalRecord patientMedicalRecord = new PatientMedicalRecord
{
HospitalId = ConfigurationManager.AppSettings["HospitalId"],
HospitalName = ConfigurationManager.AppSettings["HospitalName"],
MedicalCardNo = dtMzsf.Rows[0]["卡号"].ToString(),
OutPatientNo = dtMzsf.Rows[0]["卡号"].ToString(),
PatientName = dtGh.Rows[0]["姓名"].ToString(),
IdentCard = dtGh.Rows[0]["身份证"].ToString(),
GenderValue = dtGh.Rows[0]["性别"].ToString() == "男" ? "1" : "0",
PatientAge = dtGh.Rows[0]["年龄"].ToString(),
ClinicTime = Convert.ToDateTime(dtMzsf.Rows[0]["日期"]).ToString("yyyy-MM-dd"),
ClinicDepartments = "暂无",
ActionInChief = dtMzbl != null && dtMzbl.Rows.Count > 0 && dtMzbl.Rows[0]["主诉"].ToString() != "" ? dtMzbl.Rows[0]["主诉"].ToString() : "暂无",
HisPresentIllness = dtMzbl != null && dtMzbl.Rows.Count > 0 && dtMzbl.Rows[0]["现病史"].ToString() != "" ? dtMzbl.Rows[0]["现病史"].ToString() : "暂无",
PastHistory = "暂无",
PersonalHistory = "暂无",
AllergicHistory = "暂无",
FamilyHistory = "暂无",
PhysicalExamination = "暂无",
AuxiliaryExaminations = "暂无",
PreliminaryJudgment = dtMzbl != null && dtMzbl.Rows.Count > 0 && dtMzbl.Rows[0]["西医诊断"].ToString() != "" ? dtMzbl.Rows[0]["西医诊断"].ToString() : "暂无",
Other1 = null,
Other2 = null,
Other3 = null,
Other4 = null,
Other5 = null,
PID = dtMzsf.Rows[0]["卡号"].ToString(),
DzjkNo = null
};
string startDate = Convert.ToDateTime(dtMzsf.Rows[0]["日期"]).ToString("yyyy-MM-dd") + " 00:00:00";
string endDate = Convert.ToDateTime(dtMzsf.Rows[0]["日期"]).ToString("yyyy-MM-dd") + " 23:59:59";
string sqlMzhj = $"select * from (select 发票流水号,日期,卡号,病人姓名,科室ID,医师,YF_ID,名称,单位,数量,用法,用量 from 划价临时库 where 科室ID<>0 and 发票流水号<>'-1' union select 发票流水号,日期,卡号,病人姓名,科室ID,医师,YF_ID,名称,单位,数量,用法,用量 from 划价流水帐 where 科室ID<>0 and 发票流水号<>'-1') as Mzhj where 卡号='{dtMzsf.Rows[0]["卡号"].ToString()}' and 日期 between '{startDate}' and '{endDate}'";
DataTable dtMzhj = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlMzhj).Tables[0];
if (dtMzhj != null && dtMzhj.Rows.Count > 0)
{
string sqlDoc = $"select id,医师代码,医师姓名,所在科室,挂号科室,划价号 from 医师代码 where 医师姓名='{dtMzhj.Rows[0]["医师"].ToString()}'";
DataTable dtDoc = SqlCommon.ExecuteSqlToDataSet(SqlCommon.GetConnectionStringFromConnectionStrings("HisConnectionString"), sqlDoc).Tables[0];
patientMedicalRecord.ClinicDepartments = dtDoc != null && dtDoc.Rows.Count > 0 ? dtDoc.Rows[0]["所在科室"].ToString() : null;
}
return(patientMedicalRecord);
}
else
{
return(null);
}
}