public List <CD_Consultation> GetConsultationByUser(string userID, string consultationType = null)
{
string filter = "1=1";
if (!string.IsNullOrWhiteSpace(userID))
{
GI_UserInfo user = EntityOperate <GI_UserInfo> .GetEntityById(userID, "USERID");
if (user != null)
{
if (consultationType == "1")
{
filter += string.Format(" and ( ApplyDoctorCode = '{0}'", userID);
filter += string.Format(" or ( ApplyDepartCode = '{0}' and ApplyDoctorCode is null ))", user.DpetID);
// filter += string.Format(" and ApplyWardCode = '{0}'", user.InpatientID);
}
else
{
filter += string.Format(" and ( RequesterCode = '{0}'", userID);
filter += string.Format(" or RequestDepartCode = '{0}')", user.DpetID);
}
}
}
filter += " order by a.consultationtype desc,a.applytime";
List <CD_Consultation> list = EntityOperate <CD_Consultation> .GetEntityListBySQL(@"select a.consultationid,a.organid,a.inpatientid,a.deptid,
a.wardid,a.bednum,a.consultationstate,a.consultationtype,a.hospitaltype,a.hospitalname,a.applytime,a.diseasesummary,a.applydepartcode,
a.applydepartname,a.applywardcode,a.applywardname,a.applydoctorcode,a.applydoctorname,a.requestdepartcode,a.requestercode,a.verifiertime,
a.verifiercode,a.replytime,a.replycontent,a.replydepartcode,a.replydepartname,a.replywardcode,a.replywardname,a.replydoctorcode,
a.replydoctorname,a.del,a.creator,a.createtime,a.updater,a.updatetime, b.name as inpatientname,b.HEALTHCARD as InpatientCode,
dept.DeptName as requestdepartName,ward.DeptName as InpatientWardName
from CD_Consultation a ,cd_inpatient b,ai_deptinfo dept,ai_deptinfo ward where a.inpatientid=b.inpatientid and dept.isinpatient=0 and ward.isinpatient=1 and a.requestdepartcode=dept.DeptID and a.wardID=ward.DeptID and " + filter);
if (list == null || list.Count <= 0)
{
return(new List <CD_Consultation>());
}
list.ForEach((f) =>
{
f.ConsultationStateName = f.ConsultationState == "2" ? "已回复" : "新开";
f.ConsultationTypeName = f.ConsultationType == "2" ? "<label style='color:red'>紧急</label>" : "普通";
f.HospitalName = f.HospitalType == "2" ? f.HospitalName : "本院";
if (!string.IsNullOrWhiteSpace(f.RequesterCode))
{
GI_UserInfo Requester = EntityOperate <GI_UserInfo> .GetEntityById(f.RequesterCode, "UserID");
f.RequesterName = Requester.UserName;
}
});
return(list);
}
/// <summary>
/// 根据住院病人id体温单
/// </summary>
/// <param name="InpatientId">住院病人ID</param>
/// <param name="MeasureTime">测量时间</param>
/// <returns></returns>
public List <CN_TemperatureChart> GetListByInpatientId(string InpatientId, string MeasureTime = "")
{
string MeasureTimeStr = "";
//测量时间
if (!string.IsNullOrEmpty(MeasureTime))
{
MeasureTimeStr = "and to_char(MeasureTime,'yyyy-mm-dd')=to_char('" + MeasureTime + "','yyyy-mm-dd')";
}
string sql = string.Format("select * from CN_TemperatureChart where InpatientId='{0}' {1} ", InpatientId, MeasureTimeStr);
List <CN_TemperatureChart> list = EntityOperate <CN_TemperatureChart> .GetEntityListBySQL(sql);
return(list);
}
public string GetAll()
{
return(base.ExecuteActionJsonResult("获取系统业务参数信息列表", () =>
{
List <GI_SYSAPPParam> list = EntityOperate <GI_SYSAPPParam> .GetEntityListBySQL("select * from GI_SYSAPPPARAM");
int curpage = 0, limit = 3;
int.TryParse(Request["page"], out curpage);
int.TryParse(Request["limit"], out limit);
var myskip = curpage > 0?limit * (curpage - 1):0;
return new WebApi_Result()
{
data = list.Skip(myskip).Take(limit), count = list.Count
};
}));
}
/// <summary>
/// 获得转科信息
/// </summary>
/// <param name="where">条件</param>
/// <returns></returns>
public List <CD_Transfer> GetList(string where = "")
{
string sql = string.Format(@"
select t.*,i.name InpatientName,i.HealthCard,olddept.deptname OldDeptName ,oldward.deptname OldWardName,currentdept.deptname CurrentDeptName,currentward.deptname CurrentWardName
from CD_TRANSFER t
left join CD_INPATIENT i on i.inpatientid=t.inpatientid
left join AI_DEPTINFO olddept on olddept.Deptid=t.olddeptid
left join AI_DEPTINFO oldward on oldward.Deptid=t.oldwardid
left join AI_DEPTINFO currentdept on currentdept.Deptid=t.currentdeptid
left join AI_DEPTINFO currentward on currentward.Deptid=t.currentwardid
where 1=1 {0} order by ConversionTime desc
", where);
List <CD_Transfer> list = EntityOperate <CD_Transfer> .GetEntityListBySQL(sql);
return(list);
}
/// <summary>
/// 根据病人ID查询病历查阅记录
/// </summary>
/// <param name="InpatientId">病人ID</param>
/// <param name="AccessState">审阅状态</param>
/// <returns></returns>
public List <CD_MedicalRecordAccessRecord> GetListByInpatientId(string InpatientId, int AccessState = -1, string ApplyUserName = "")
{
string filter = "";
//审阅状态
if (AccessState >= 0)
{
filter += " and AccessState=" + AccessState;
}
//申请医生姓名
if (!string.IsNullOrWhiteSpace(ApplyUserName))
{
filter += " and u.UserName like '%" + ApplyUserName + "%' ";
}
string sql = "select m.*,u.UserName as ApplyUserName from CD_MedicalRecordAccessRecord m left join GI_UserInfo u on u.UserId=m.applyuserid where m.InpatientId='" + InpatientId + "' " + filter;
List <CD_MedicalRecordAccessRecord> list = EntityOperate <CD_MedicalRecordAccessRecord> .GetEntityListBySQL(sql);
return(list);
}
public WebApi_Result GetPagedResult <T>(string Filter) where T : class
{
List <T> list = EntityOperate <T> .GetEntityListBySQL("select * from " + typeof(T).Name + " where " + Filter);
if (list == null || list.Count() <= 0)
{
return(new WebApi_Result()
{
code = 1, msg = "未查询到任何数据"
});
}
int curpage = 0, limit = 3;
int.TryParse(Request["page"], out curpage);
int.TryParse(Request["limit"], out limit);
var myskip = curpage > 0 ? limit * (curpage - 1) : 0;
return(new WebApi_Result()
{
data = list.Skip(myskip).Take(limit), count = list.Count
});
}
public ArrayList ReadTTData(string sFirstDate, string InpatientId)
{
ArrayList alTT = new ArrayList(); //返回一周的数据
CD_Inpatient inpatient = EntityOperate <CD_Inpatient> .GetEntityById(InpatientId, "InpatientId"); //获得病人信息
DateTime dateTimeQuery = DateTime.Parse(sFirstDate);
string sEndDate = dateTimeQuery.AddDays(6).ToString("yyyy-MM-dd");
string startTime = sFirstDate + " 00:00:00";
string strCysj = "";//出院时间
if (inpatient.LeaveTime != null)
{
strCysj = inpatient.LeaveTime.Value.ToString("yyyy-MM-dd");//出院时间
}
//if (strCysj != "") //2010.7.13 注释 病人状态为在院时，cysj为当前时间（Doctors_PatientIofoCTR 588行 控制），所以判断条件加了病人状态
if (strCysj != "")
{
TimeSpan time = DateTime.Parse(strCysj.Substring(0, 10)) - DateTime.Parse(sEndDate);
if (time.Days < 0)
{
sEndDate = strCysj.Substring(0, 10);
}
}
string endTime = sEndDate + " 23:59:59";
StringBuilder strSQL = new StringBuilder(
//"SELECT * FROM T_ZYHS_TWJL WHERE zyxh='" + pi.zyxh + "' AND clsj>='" + startTime + "' AND clsj<='" + endTime + "' ORDER BY clsj,jlsj,cllx";
"SELECT * FROM CN_TemperatureChart WHERE inpatientId = '" + inpatient.InpatientId + "' and MeasureTime >= to_date('" + startTime + "','yyyy-mm-dd hh24:mi:ss') and MeasureTime <=to_date('" + endTime + "','yyyy-mm-dd hh24:mi:ss') order by MeasureTime,CreateTime,TestType"
);
List <CN_TemperatureChart> tcList = EntityOperate <CN_TemperatureChart> .GetEntityListBySQL(strSQL.ToString());//获得体温列表
foreach (CN_TemperatureChart tc in tcList)
{
string sTt = "0"; // reader["ttqd"].ToString();
string sBC = tc.Event; //体温补充内容
if (sTt.Trim() != "" || sBC == "请假")
{
string sTtlx = tc.PainScoreId; //疼痛评分 reader["TTLx"].ToString();
string sID = tc.ChartId; //体温单id
string sNid = tc.Updater; //护士id，更新人id
string sClsj = tc.MeasureTime.ToString(); //测量时间
if (sTtlx.Trim() != "" && sTtlx.Trim() != "0")
{
sTtlx = "1";
}
if (sClsj.Length > 19)
{
sClsj = sClsj.Substring(0, 19);
}
try
{
DateTime.Parse(sClsj);
}
catch (Exception ex)
{
break;
}
int nClsj = Convert.ToInt32(sClsj.Substring(0, 2));
if (sBC == "请假" || sBC == "外出" || sBC == "检查") //"请假" "外出" "检查"不需要显示
{
sTtlx = "-2"; //出来类型=-2代表不需显示
}
float tt = 0;
float.TryParse(sTt, out tt);
TTClass ttClass = new TTClass(DateTime.Parse(sClsj), tt);
ttClass.CeliangLeixing = sTtlx;
alTT.Add(ttClass);
}
}
return(alTT);
}
/// <summary>
/// 读取体温信息
/// </summary>
/// <param name="sFirstDate">甘特图开始时间：住院时间+（查看周数-1）*7</param>
/// <param name="BirthRecordId">新生儿出生记录id</param>
public ArrayList ReadTemperatureData(string sFirstDate, string BirthRecordId, string InpatientId)
{
ArrayList alTT = new ArrayList(); //返回一周的数据
CD_Inpatient inpatient = EntityOperate <CD_Inpatient> .GetEntityById(InpatientId, "InpatientId"); //获得病人信息
DateTime dateTimeQuery = DateTime.Parse(sFirstDate); //开始时间转为日期类型
string sEndDate = dateTimeQuery.AddDays(6).ToString("yyyy-MM-dd"); //结束时间
string startTime = sFirstDate + " 00:00:00"; //开始时间加上时分秒
string endTime = sEndDate + " 23:59:59"; //结束时间加上时分秒
string strCysj = inpatient.LeaveTime.Value.ToString("yyyy-MM-dd"); //出院时间
//如果出院时间不为空，结束时间改为出院时间
if (strCysj != "")
{
TimeSpan time = DateTime.Parse(strCysj.Substring(0, 10)) - DateTime.Parse(sEndDate);
if (time.Days < 0)
{
sEndDate = strCysj.Substring(0, 10);
}
}
if (BirthRecordId == "")//如果新生儿出生记录id不为空
{
BirthRecordId = " and BirthRecordId is null ";
}
else
{
BirthRecordId = " and yexh='" + BirthRecordId + "' ";
}
StringBuilder strSQL = new StringBuilder(
//"SELECT * FROM T_ZYHS_TWJL WHERE zyxh='" + pi.zyxh + "'" + BirthRecordId + " AND clsj>='" + startTime + "' AND clsj<='" + endTime + "' and (TWJLLB='1' or TWJLLB is null) ORDER BY clsj,cllx"
"SELECT * FROM CN_TemperatureChart WHERE inpatientId = '" + inpatient.InpatientId + "' " + BirthRecordId + " and MeasureTime >= to_date('" + startTime + "','yyyy-mm-dd hh24:mi:ss') and MeasureTime <=to_date('" + endTime + "','yyyy-mm-dd hh24:mi:ss') order by MeasureTime,CreateTime,TestType"
);
List <CN_TemperatureChart> tcList = EntityOperate <CN_TemperatureChart> .GetEntityListBySQL(strSQL.ToString());//获得体温列表
foreach (CN_TemperatureChart tc in tcList)
{
string sID = tc.ChartId; //体温单id
string sNid = tc.Updater; //护士id，更新人id
string sClsj = tc.MeasureTime.ToString(); //测量时间
string sTiwen = tc.Degree; //体温
string sMianbo = tc.Pulse; //脉搏
string sHuxi = tc.Breath; //呼吸
string sXy1 = tc.SystolicPressure; //收缩压
string sXy2 = tc.DiastolicPressure; //舒张压
string sTwlx = tc.TypeCode; //体温类型代码
string sSpo2 = tc.Spo; //氧饱和度
string sCllx = tc.TestType.ToString(); //测量类型，有常规 - 0和降温后半小时 - 1，主要用来画图
string sIsMb = ""; //脉搏类型？0脉搏1心率
if (!string.IsNullOrEmpty(tc.Pulse))
{
sIsMb = "0";
}
else if (!string.IsNullOrEmpty(tc.HeartRate))
{
sIsMb = "1";
}
int nClsj = Convert.ToInt32(sClsj.Substring(0, 2));
string sBC = tc.Event; //体温补充内容
if (sBC == "请假" || sBC == "外出" || sBC == "检查") //"请假" "外出" "检查"不需要显示
{
sCllx = "-2"; //出来类型=-2代表不需显示
//sTiwen = "37";
}
if (sTiwen == "" || sTiwen == "0")//体温如果输入0度，则重新赋为42
{
sTiwen = "42";
}
if (sMianbo == "" || sMianbo == "0")//如果脉搏输入0次，则脉搏赋240
{
sMianbo = "200";
}
if (sHuxi == "0")
{
sHuxi = "";
}
if (sCllx == "1" || sCllx == "2") //半小时1小时记录，脉搏呼吸不显示
{
sMianbo = "200"; //240超出了体温单范围，不会显示出来
sHuxi = "";
TimeTwMbHx ttmh = new TimeTwMbHx(DateTime.Parse(sClsj), float.Parse(sTiwen), int.Parse(sMianbo), sHuxi);
ttmh.TiwenLeixing = sTwlx;
ttmh.CeliangLeixing = sCllx;
ttmh.MiaoboLeixing = sIsMb;
ttmh.sBC = sBC;
ttmh.sXy1 = sXy1;
ttmh.sXy2 = sXy2;
ttmh.sSpo2 = sSpo2;
alTT.Add(ttmh);
}
else
{
TimeTwMbHx ttmh = new TimeTwMbHx(DateTime.Parse(sClsj), float.Parse(sTiwen), int.Parse(sMianbo), sHuxi);
ttmh.TiwenLeixing = sTwlx;
ttmh.CeliangLeixing = sCllx;
ttmh.MiaoboLeixing = sIsMb;
ttmh.sBC = sBC;
ttmh.sXy1 = sXy1;
ttmh.sXy2 = sXy2;
ttmh.sSpo2 = sSpo2;
alTT.Add(ttmh);
}
}
return(alTT);
}
/// <summary>
/// 根据ID获取手卫生依从性调查评分 数据分析表
/// </summary>
/// <param name="iFilter"></param>
/// <param name="HANDID"></param>
public Tuple <List <HandHygieneGroupAnalysis>, List <HandHygieneDczs>, List <HandHygieneOpModel> > GetHandHygiAnalysisSourceList(CommonFilter iFilter, string HANDID)
{
string filter = "1=1";
filter += iFilter.GetQueryString();
Func <string, int> funcSort = v =>
{
var sort = 0;
switch (v)
{
case "护士":
sort = 1;
break;
case "医技":
sort = 2;
break;
case "医生":
sort = 3;
break;
case "工人":
sort = 4;
break;
}
return(sort);
};
List <BUS_HANDHYGIENE_SOURCE> handHygiSource = EntityOperate <BUS_HANDHYGIENE_SOURCE> .GetEntityList(filter + (string.IsNullOrWhiteSpace(HANDID) == true ? "" : " and HANDID='" + HANDID + "'"));
var handHygieneGroupList = handHygiSource.GroupBy(p => p.ZY).Select(g => new HandHygieneGroupAnalysis
{
handhygiene_source = g,
zy = g.Key,
sort = funcSort(g.Key),
currentSum = g.Count()
}).OrderBy(g => g.sort).ToList();
//调查总数
#region
var dczsSql = string.Format(@" select * from
(select count(jcbrq) jcbrq ,zy from bus_handhygiene_source where jcbrq != '正确' and handid ={0} group by zy )
pivot
(
sum(jcbrq) for
zy in ('医生' ys,'护士' hs,'医技' yj,'工人' gr)
)
union all
select * from
(select count(JCBRH) JCBRH ,zy from bus_handhygiene_source where JCBRH != '正确' and handid ={0} group by zy )
pivot
(
sum(JCBRH) for
zy in ('医生' ys,'护士' hs,'医技' yj,'工人' gr)
)
union all
select * from
(select count(JCWJWPQ) JCWJWPQ ,zy from bus_handhygiene_source where JCWJWPQ != '正确' and handid ={0} group by zy )
pivot
(
sum(JCWJWPQ) for
zy in ('医生' ys,'护士' hs,'医技' yj,'工人' gr)
)
union all
select * from
(select count(JCBRHJH) JCBRHJH ,zy from bus_handhygiene_source where JCBRHJH != '正确' and handid = {0} group by zy )
pivot
(
sum(JCBRHJH) for
zy in ('医生' ys,'护士' hs,'医技' yj,'工人' gr)
)
union all
select * from
(select count(JCWWH) JCWWH ,zy from bus_handhygiene_source where JCWWH != '正确' and handid = {0} group by zy )
pivot
(
sum(JCWWH) for
zy in ('医生' ys,'护士' hs,'医技' yj,'工人' gr)
)
union all
select * from
(select count(PCQ) PCQ ,zy from bus_handhygiene_source where PCQ != '正确' and handid = {0} group by zy )
pivot
(
sum(PCQ) for
zy in ('医生' ys,'护士' hs,'医技' yj,'工人' gr)
)", HANDID);
#endregion
var dczsListSource = EntityOperate <HandHygieneDczs> .GetEntityListBySQL(dczsSql);
var ysOpCount = dczsListSource.Sum(p => p.ys); //医生操作总数
var hsOpCount = dczsListSource.Sum(p => p.hs); //护士操作总数
var yjOpCount = dczsListSource.Sum(p => p.yj); //医技操作总数
var grOpCount = dczsListSource.Sum(p => p.gr); //工人操作总数
dczsListSource.Add(new HandHygieneDczs {
ys = ysOpCount, hs = hsOpCount, yj = yjOpCount, gr = grOpCount
});
// List<HandHygieneDczs> dczsList = new List<HandHygieneDczs>();
// string[] itemArr = { "接触病人前", "接触病人后", "无菌操作前", "接触病人周围环境后", "接触污物（摘手套）后", "配餐前", "总数" };
var opModelList = new List <HandHygieneOpModel>();
string[] zyArr = { "医生", "护士", "医技", "工人" };
foreach (var zyItem in zyArr)
{
//获取操作列表sql
var getOpList = string.Format(@"select sum(case jcbrq when '消' then 1 else 0 end) x,sum(case jcbrq when '洗' then 1 else 0 end) xx, sum(case jcbrq when '未' then 1 else 0 end) w
from bus_handhygiene_source where zy = '{0}' and handid = {1} union all --医生接触病人前
select sum(case JCBRH when '消' then 1 else 0 end) x,sum(case JCBRH when '洗' then 1 else 0 end) xx, sum(case JCBRH when '未' then 1 else 0 end) w
from bus_handhygiene_source where zy = '{0}' and handid = {1} union all --接触病人后
select sum(case JCWJWPQ when '消' then 1 else 0 end) x,sum(case JCWJWPQ when '洗' then 1 else 0 end) xx, sum(case JCWJWPQ when '未' then 1 else 0 end) w
from bus_handhygiene_source where zy = '{0}' and handid = {1} union all --接触无菌物品前
select sum(case JCBRHJH when '消' then 1 else 0 end) x,sum(case JCBRHJH when '洗' then 1 else 0 end) xx, sum(case JCBRHJH when '未' then 1 else 0 end) w
from bus_handhygiene_source where zy = '{0}' and handid = {1} union all --接触病人周围环境后
select sum(case JCWWH when '消' then 1 else 0 end) x,sum(case JCWWH when '洗' then 1 else 0 end) xx, sum(case JCWWH when '未' then 1 else 0 end) w
from bus_handhygiene_source where zy = '{0}' and handid = {1} union all --接触污物（摘手套）后
select sum(case PCQ when '消' then 1 else 0 end) x,sum(case PCQ when '洗' then 1 else 0 end) xx, sum(case PCQ when '未' then 1 else 0 end) w
from bus_handhygiene_source where zy = '{0}'and handid = {1} --配餐前", zyItem, HANDID);
HandHygieneOpModel opModel = new HandHygieneOpModel();
var opList = EntityOperate <OpCategory> .GetEntityListBySQL(getOpList);
opModel.zyName = zyItem;
opModel.opList = opList;
var opCategoryModel = new OpCategory {
x = opList.Select(p => p.x).Sum(), xx = opList.Select(p => p.xx).Sum(), w = opList.Select(p => p.w).Sum()
};
opModel.opList.Add(opCategoryModel);
opModelList.Add(opModel);
}
return(new Tuple <List <HandHygieneGroupAnalysis>, List <HandHygieneDczs>, List <HandHygieneOpModel> >(handHygieneGroupList, dczsListSource, opModelList));
}
