/// <summary>
/// 麻醉患者手术信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public List <dynamic> GetAnesShiftOperInfoList(OperQueryParaModel model)
{
string sql = sqlDict.GetSQLByKey("GetAnesShiftOperInfoList");
DateTime?startTime = null;
DateTime?endTime = null;
if (!string.IsNullOrWhiteSpace(model.SHIFT_DATE))
{
if (model.SHIFT_TYUPE == "1")// 白班
{
startTime = Convert.ToDateTime(model.SHIFT_DATE).Date.AddHours(8);
endTime = Convert.ToDateTime(model.SHIFT_DATE).Date.AddHours(18);
}
else if (model.SHIFT_TYUPE == "0")// 夜班
{
startTime = Convert.ToDateTime(model.SHIFT_DATE).Date.AddHours(18);
endTime = Convert.ToDateTime(model.SHIFT_DATE).Date.AddDays(1).AddHours(8);
}
}
List <dynamic> list = dapper.Set <dynamic>().Query(sql, new
{
StartDate = startTime,
EndDate = endTime,
});
return(list);
}
/// <summary>
/// 手术信息EChart图标统计
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual IList <dynamic> QueryOperEChartReportForDirectionIndex(OperQueryParaModel model)
{
//string sql = sqlDict.GetSQLByKey("QueryOperInfosForDirectorIndex");
//年月日SQL暂时未定怎么存放 暂时放此处
string sql = "";
if (model.TimeType == "month")
{
// DateTime date = Convert.ToDateTime(Convert.ToDateTime(model.StartDate).Year + "-" + Convert.ToDateTime(model.StartDate).Month);
string date = Convert.ToDateTime(model.StartDate).ToString("yyyy-MM-dd");
//统计月
sql = @"
select a.s_date name, nvl(b.value, 0) value
from (select rownum s_date
from dual
connect by rownum <= last_day(to_date('" + date + @"', 'yyyy-mm-dd')) -
to_date('" + date + @"', 'yyyy-mm-dd') + 1) a
left join (
select to_number(to_char(START_DATE_TIME, 'dd')) name,
count(1) value
from MED_OPERATION_MASTER
where Oper_Status_Code >= 35
and to_char(START_DATE_TIME, 'yyyy-MM') =
to_char(to_date('" + date + @"', 'yyyy-MM-dd'), 'yyyy-MM')
group by to_char(START_DATE_TIME, 'dd')
order by to_char(START_DATE_TIME, 'dd')) b
on a.s_date = b.name
order by a.s_date";
}
//统计年
else if (model.TimeType == "year")
{
//sql = @"select to_number(to_char(START_DATE_TIME,'MM')) name,count(1) value
// from MED_OPERATION_MASTER where Oper_Status_Code>=35 and to_char(START_DATE_TIME, 'yyyy') = to_char(to_date('" + Convert.ToDateTime(model.StartDate).ToShortDateString() + @"', 'yyyy-MM-dd'), 'yyyy') group by to_char(START_DATE_TIME, 'MM')
// order by to_char(START_DATE_TIME, 'MM')";
sql = @"select a.mon name, nvl(b.value, 0) value
from (select level mon from dual connect by level < 13) a
left join (select to_number(to_char(START_DATE_TIME, 'MM')) name,
count(1) value
from MED_OPERATION_MASTER a
where Oper_Status_Code >= 35
and to_char(START_DATE_TIME, 'yyyy') =
to_char(to_date('" + Convert.ToDateTime(model.StartDate) + @"', 'yyyy-MM-dd'), 'yyyy') group by to_char(START_DATE_TIME, 'MM')
order by to_char(START_DATE_TIME, 'MM')) b
on a.mon = b.name
order by a.mon";
}
try
{
return(dapper.Set <dynamic>().Query(sql, new { }));
}
catch (Exception ex)
{
Logger.Error("查询工作量信息QueryOperEChartReportForDirectionIndex:" + model.TimeType, ex);
return(null);
}
}
public void SaveWeChatCloudOperInfoData()
{
OperQueryParaModel model = new OperQueryParaModel();
dynamic operinfolist = WeiXinQuery.GetOperInfo(model);
PostObject obj = new PostObject();
obj.Data = JsonConvert.SerializeObject(operinfolist);;
WebApiVisitor.PostAccessApi(webFrontEndUrl + "api/FrontEndMachine/SaveWeChatCloudOperInfoData", obj);
}
/// <summary>
/// 手术信息EChart图标下方集中统计统计
/// </summary>
public dynamic GetWeiXinOperInfo(OperQueryParaModel model)
{
//string sql = sqlDict.GetSQLByKey("QueryOperInfosForDirectorIndex");
//年月日SQL暂时未定怎么存放 暂时放此处
string sql = "";
if (model.TimeType == "month")
{
//统计月
sql = @"with temp as
(select *
from medsurgery.MED_OPERATION_MASTER m
WHERE TO_CHAR(START_DATE_TIME, 'YYYY') = :Year
AND TO_CHAR(START_DATE_TIME, 'MM') = :Month and m.Oper_Status_Code>=35 )
select
( select count(1) from temp ) TotalCount,
decode((select count(1) operCount from (select 1 from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd'))),0,0, round(( select count(1) from temp )/ (select count(1) operCount from (select 1 from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd'))),0)) DayAvgCount,
(select dateTime from (select to_char(START_DATE_TIME,'yyyy-MM-dd') dateTime, count(1) operCount from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd') order by count(1) desc) where rownum=1) MostOperDate,
(select operCount from (select to_char(START_DATE_TIME,'yyyy-MM-dd') dateTime, count(1) operCount from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd') order by count(1) desc) where rownum=1) MostOperCount,
(select dateTime from (select to_char(START_DATE_TIME,'yyyy-MM-dd') dateTime, count(1) operCount from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd') order by count(1) ) where rownum=1) LeastOperDate,
(select operCount from (select to_char(START_DATE_TIME,'yyyy-MM-dd') dateTime, count(1) operCount from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd') order by count(1) ) where rownum=1) LeastOperCount,
( select count(1) from temp where to_char(START_DATE_TIME,'day') ='星期六' or to_char(START_DATE_TIME,'day')='星期日' ) WeekOperCount,
( select count(1) from temp where Emergency_Ind=1 ) EmergencyCount
from dual
";
return(dapper.Set <dynamic>().Query(sql, new { Year = Convert.ToDateTime(model.StartDate).Year, Month = Convert.ToDateTime(model.StartDate).Month }).FirstOrDefault());
}
else if (model.TimeType == "year")
{
//统计年
sql = @"with temp as
(select *
from medsurgery.MED_OPERATION_MASTER m
WHERE TO_CHAR(START_DATE_TIME, 'YYYY') = :Year and m.Oper_Status_Code>=35 )
select
( select count(1) from temp ) TotalCount,
decode((select count(1) operCount from (select 1 from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd'))),0,0, round(( select count(1) from temp )/ (select count(1) operCount from (select 1 from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd'))),0)) DayAvgCount,
(select dateTime from (select to_char(START_DATE_TIME,'yyyy-MM-dd') dateTime, count(1) operCount from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd') order by count(1) desc) where rownum=1) MostOperDate,
(select operCount from (select to_char(START_DATE_TIME,'yyyy-MM-dd') dateTime, count(1) operCount from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd') order by count(1) desc) where rownum=1) MostOperCount,
(select dateTime from (select to_char(START_DATE_TIME,'yyyy-MM-dd') dateTime, count(1) operCount from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd') order by count(1) ) where rownum=1) LeastOperDate,
(select operCount from (select to_char(START_DATE_TIME,'yyyy-MM-dd') dateTime, count(1) operCount from temp group by to_char(START_DATE_TIME,'yyyy-MM-dd') order by count(1) ) where rownum=1) LeastOperCount,
( select count(1) from temp where to_char(START_DATE_TIME,'day') ='星期六' or to_char(START_DATE_TIME,'day')='星期日' ) WeekOperCount,
( select count(1) from temp where Emergency_Ind=1 ) EmergencyCount
from dual
";
return(dapper.Set <dynamic>().Query(sql, new { Year = Convert.ToDateTime(model.StartDate).Year, Month = Convert.ToDateTime(model.StartDate).Month }).FirstOrDefault());
}
//统计年
return(dapper.Set <dynamic>().Query(sql, new { }).FirstOrDefault());
}
public RequestResult <dynamic> QueryOutOperatingAnesRecord(OperQueryParaModel model)
{
IList <dynamic> MedOperationMasterList = AnesInfo.GetOutOperatingAnesRecord(model);
dynamic dynamicInfo = new { Total = MedOperationMasterList.Count, CurrentData = MedOperationMasterList.Skip((model.CurrentPage - 1) * model.PageSize).Take(model.PageSize).ToList() };
return(Success <dynamic>(dynamicInfo));
}
/// <summary>
/// 查询文书PDF列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public IList <AnesDocumentPDFSrcEntity> QueryAnesDocumentPDF(OperQueryParaModel model)
{
string sql = sqlDict.GetSQLByKey("QueryAnesDocumentPDF");
return(dapper.Set <AnesDocumentPDFSrcEntity>().Query(sql, new
{
PatientId = model.PatientId,
VisitId = model.VisitId,
OperId = model.OperId,
DocName = model.SearchMark
}));
}
/// <summary>
/// 病例病程
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public IList <dynamic> GetOutOperatingAnesRecord(OperQueryParaModel model)
{
string sql = sqlDict.GetSQLByKey("GetOutOperatingAnesRecord");
return(dapper.Set <dynamic>().Query(sql, new
{
StartDate = Convert.ToDateTime(model.StartDate),
EndDate = Convert.ToDateTime(model.EndDate).AddDays(1),
INP_NO = model.InpNo,
ANES_DOCTOR = model.AnesDoctor
}));
}
public RequestResult <dynamic> GetAnesthesiaEvent(OperQueryParaModel model)
{
DateTime startTime = DateTime.MinValue;
DateTime endTime = DateTime.MinValue;
DateTime.TryParse(model.StartDate, out startTime);
DateTime.TryParse(model.EndDate, out endTime);
return(Success <dynamic>(surgicalMonitorService.GetAnesthesiaEvent(model.PatientId, model.VisitId, model.OperId, startTime, endTime)));
}
public RequestPageResult <List <dynamic> > GetPatientInfoList(OperQueryParaModel model)
{
List <dynamic> list = nurseManageService.GetPatientInfoList(model);
List <dynamic> pageList = list;
//分页处理
if (list.Count > model.PageSize)
{
pageList = pageList.Skip((model.CurrentPage - 1) * model.PageSize).Take(model.PageSize).ToList();
}
return(PageSuccess(pageList, model.PageSize, model.CurrentPage, list.Count));
}
/// <summary>
/// 获取医院信息数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public IList <MED_WECHAT_HOSPITALS> GetHospitalList(OperQueryParaModel model)
{
string sql = @" select *
from MED_WECHAT_HOSPITALS a
where (a.hospital_name like '%' || @HospitalNameKey || '%'
or a.short_name like '%' || @HospitalNameKey || '%' or @HospitalNameKey is null) and (a.province like '%' || @ProvinceKey || '%'
or a.city like '%' || @ProvinceKey || '%' or @ProvinceKey is null )";
try
{
return(dapper.Set <MED_WECHAT_HOSPITALS>().Query(sql, new { HospitalNameKey = model.QueryType, ProvinceKey = model.SearchMark }));
}
catch (Exception ex)
{
Logger.Error("读取医院信息数据GetHospitalList:", ex);
return(null);
}
}
public bool SaveWeChatCloudStatisticsDataAll(OperQueryParaModel para)
{
DateTime start = Convert.ToDateTime(para.StartDate);
DateTime end = Convert.ToDateTime(para.EndDate).AddMonths(1).AddDays(-1);
OperQueryParaModel model = new OperQueryParaModel();
bool result = false;
if (start < end)
{
do
{
model.StartDate = start.ToString();
this.SaveWeChatCloudStatisticsData(model);
start = start.AddMonths(1);
result = true;
}while (start < end);
}
return(result);
}
public IList <OperQuerytForAnesQueryEntity> GetChargePatientList(OperQueryParaModel model)
{
string sql = sqlDict.GetSQLByKey("GetChargePatientQuery");
DateTime?startTime = null;
DateTime?endTime = null;
if (!string.IsNullOrWhiteSpace(model.StartDate))
{
startTime = Convert.ToDateTime(model.StartDate);
endTime = Convert.ToDateTime(model.EndDate).AddDays(1);
}
return(dapper.Set <OperQuerytForAnesQueryEntity>().Query(sql, new
{
STATUS = model.StatusType.Equals("已审核") ? "2" : "1",
PatName = model.PatName,
InpNo = model.InpNo,
AnesDoctor = model.AnesDoctor,
StartDate = startTime,
EndDate = endTime,
}));
}
/// <summary>
/// 手术查询
/// </summary>
/// <param name="model">对象</param>
/// <returns></returns>
public IList <OperQuerytForAnesQueryEntity> GetOperList(OperQueryParaModel model)
{
string sql = sqlDict.GetSQLByKey("GetOperListForAnesQuery");
DateTime?startTime = null;
DateTime?endTime = null;
if (!string.IsNullOrWhiteSpace(model.StartDate))
{
startTime = Convert.ToDateTime(model.StartDate);
endTime = Convert.ToDateTime(model.EndDate).AddDays(1);
}
return(dapper.Set <OperQuerytForAnesQueryEntity>().Query(sql, new
{
PatName = model.PatName,
InpNo = model.InpNo,
AnesDoctor = model.AnesDoctor,
model.OperDept,
model.OperRoomNo,
StartDate = startTime,
EndDate = endTime,
}));
}
public void SaveWeChatCloudQuanlityData(OperQueryParaModel para)
{
List <MED_QC_REPORT_IND> list = WeiXinQuery.GetQuanlityControlReportInd();
//获取服务器时间
DateTime dt = DateTime.Now;
if (para.StartDate == "")
{
dt = WeiXinQuery.GetSysDate().AddMonths(-1);
}
else
{
dt = Convert.ToDateTime(para.StartDate);
}
//DateTime dt = DateTime.Now.AddMonths(-3); //WeiXinQuery.GetSysDate().AddMonths(-1);
MED_QC_REPORT_IND find = list.Find(t => t.REPORT_MONTH == (dt.ToString("yyyyMM")));
if (find != null)
{
if (!string.IsNullOrEmpty(find.REPORT_NAME))
{
IList <MED_QC_REPORT_LIST> reportList = WeiXinQuery.GetQuanlityControlReportBakItemList(find.REPORT_NAME);
find.list = reportList;
PostObject obj = new PostObject();
obj.Data = JsonConvert.SerializeObject(find);
obj.ReportMonth = dt.ToString();
try
{
WebApiVisitor.PostAccessApi(webFrontEndUrl + "api/FrontEndMachine/SaveWeChatCloudQuanlityData", obj);
}
catch (Exception ex)
{ }
}
}
}
public void SaveWeChatCloudStatisticsData(OperQueryParaModel para)
{
List <PostObject> list = new List <PostObject>(); //汇总list
OperQueryParaModel model = new OperQueryParaModel(); //工作量
if (para.StartDate == "")
{
model.StartDate = WeiXinQuery.GetSysDate().ToString();
}
else
{
model.StartDate = para.StartDate.ToString();
}
model.TimeType = "month";
dynamic listWorkTime = WeiXinQuery.QueryOperEChartReportForDirectionIndex(model); //获取图标
list.Add(GetPostObject("工作量明细", JsonConvert.SerializeObject(listWorkTime), model.StartDate));
dynamic listWorkTimeDetail = WeiXinQuery.GetWeiXinOperInfo(model);//获取图标明细
list.Add(GetPostObject("工作量", JsonConvert.SerializeObject(listWorkTimeDetail), model.StartDate));
model.QueryType = "ASA";
dynamic listASA = WeiXinQuery.QueryRightEChartReportForDirectionIndex(model);//获取ASA
list.Add(GetPostObject("ASA分级", JsonConvert.SerializeObject(listASA), model.StartDate));
model.QueryType = "AnesMethod";
dynamic listMethod = WeiXinQuery.QueryRightEChartReportForDirectionIndex(model);//获取麻醉方式
list.Add(GetPostObject("麻醉方式", JsonConvert.SerializeObject(listMethod), model.StartDate));
try
{
WebApiVisitor.PostAccessApi(webFrontEndUrl + "api/FrontEndMachine/SaveWeChatCloudStatisticsData", list);
}
catch (Exception ex)
{
}
}
public RequestResult <OperInfosForDoctorIndexEntity> QueryOperInfosForDoctorIndex(OperQueryParaModel model)
{
OperInfosForDoctorIndexEntity list = AnesQuery.QueryOperInfosForDoctorIndex(model);
return(Success(list));
}
public RequestResult <IList <EChartsItemEntity> > QueryRightEChartReportForDirectionIndex(OperQueryParaModel modela)
{
IList <EChartsItemEntity> list = AnesQuery.QueryRightEChartReportForDirectionIndex(modela);
return(Success(list));
}
public RequestResult <OperReportForDirectionEntity> QueryOperReportForDirectionIndex(OperQueryParaModel modela)
{
OperReportForDirectionEntity list = AnesQuery.QueryOperReportForDirectionIndex(modela);
return(Success(list));
}
public RequestResult <List <AnesDocumentPDFSrcEntity> > GetUnUploadPatientInfos(OperQueryParaModel model)
{
return(Success(nurseManageService.GetUnUploadPatientInfos(model)));
}
public RequestPageResult <IList <OperQuerytForAnesQueryEntity> > GetChargePatientList(OperQueryParaModel model)
{
IList <OperQuerytForAnesQueryEntity> list = ChargeInfo.GetChargePatientList(model);
return(PageSuccess(list, 0, 0, list.Count));
}
public RequestPageResult <List <MED_ANES_SHIFT_PATIENTINFO> > GetAnesShiftPatientInfoList(OperQueryParaModel model)
{
List <MED_ANES_SHIFT_PATIENTINFO> list = anesShiftService.GetAnesShiftPatientInfoList(model);
return(PageSuccess(list, model.PageSize, model.CurrentPage, list.Count));
}
public RequestResult <IList <AnesDocumentPDFSrcEntity> > QueryAnesDocumentPDF(OperQueryParaModel model)
{
//string docName = "";
//switch (model.SearchMark)
//{
// case "1":
// docName = "麻醉同意书";
// break;
// case "2":
// docName = "术前访视单";
// break;
// case "3":
// docName = "麻醉计划单";
// break;
// case "4":
// docName = "麻醉记录单";
// break;
// case "5":
// docName = "麻醉总结单";
// break;
// case "6":
// docName = "术后随访单";
// break;
// case "7":
// docName = "镇痛记录单";
// break;
// case "8":
// docName = "复苏记录单";
// break;
//}
//model.SearchMark = docName;
IList <AnesDocumentPDFSrcEntity> list = PatientRecord.QueryAnesDocumentPDF(model);
return(Success(list));
}
public RequestPageResult <IList <OperQuerytForDoctorIndexEntity> > QueryOperListForNurseIndex(OperQueryParaModel model)
{
IList <OperQuerytForDoctorIndexEntity> list = AnesQuery.GetOperListForNurseIndex(model);
IList <OperQuerytForDoctorIndexEntity> pageList = list;
//分页处理
if (list.Count > model.PageSize)
{
//pageList = (IList<OperQuerytForDoctorIndexEntity>)list.Where(p => (p.ID - 1) >=
//(model.CurrentPage - 1) * model.PageSize && (p.ID - 1) < model.CurrentPage * model.PageSize).ToList(); ;
pageList = pageList.Skip((model.CurrentPage - 1) * model.PageSize).Take(model.PageSize).ToList();
}
return(PageSuccess(pageList, model.PageSize, model.CurrentPage, list.Count));
}
/// <summary>
/// 病例病程
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public OperPatientProgressEntity QueryOperPatientProgress(OperQueryParaModel model)
{
// string sql = @"select a.patient_id,
// a.visit_id,
// a.oper_id,
// b.Name,
// b.Sex,
// ROUND((a.SCHEDULED_DATE_TIME - b.DATE_OF_BIRTH) / 365, 0) AS AGE,
// a.Bed_No,
// e.Inp_No,
// e.ADMISSION_DATE_TIME,
// c.Dept_Name,
// nvl(d.User_Name, a.Anes_Doctor) AnesDoctor,
// nvl(d1.User_Name, a.Surgeon) Surgeon,
// DIAG_BEFORE_OPERATION,
// OPERATION_NAME,
// OPER_POSITION,
// A.Oper_Scale,
// a.Scheduled_Date_Time,
// f.Height,
// f.Weight,
// g.item_value before_BP1,
// g1.Item_Value before_BP2,
// g2.Item_Value before_P,
// g3.Item_Value brfore_R,
// g4.Item_Value before_XDT,
// decode(g5.Item_Value, '0', '有', '1', '无', '无') before_YWGMS,
// a.ANES_METHOD,
// a.Asa_Grade,
// TO_CHAR(TRUNC((NVL((a.End_Date_Time - a.Start_Date_Time) * 24, 0) * 60) / 60,
// 0)) || '小时' ||
// TO_CHAR(ROUND(MOD((NVL((a.End_Date_Time - a.Start_Date_Time) * 24, 0) * 60),
// 60),
// 0)) || '分钟' AS OPERTIME,
// a.In_Date_Time,
// a.Out_Date_Time,
// a.Start_Date_Time,
// a.End_Date_Time,
// a.Anes_Start_Time,
// a.Anes_End_Time,
// a.In_Pacu_Date_Time,
// a.Out_Pacu_Date_Time, nvl(mead.Docs,'无') Docs
// from Med_Operation_Master a
// left join Med_Pat_Master_Index b
// on a.Patient_Id = b.Patient_Id
// left join Med_Dept_Dict c
// on a.Dept_Code = c.Dept_Code
// left join Med_His_Users d
// on a.Anes_Doctor = d.User_Job_Id
// left join Med_His_Users d1
// on a.Surgeon = d1.User_Job_Id
// left join Med_Pats_In_Hospital e
// on a.Patient_Id = e.Patient_Id
// and a.Visit_Id = e.Visit_Id
// left join MED_ANESTHESIA_PLAN_PMH f
// on a.Patient_Id = f.Patient_Id
// and a.Visit_Id = f.Visit_Id
// and a.Oper_Id = f.Oper_Id
// left join MED_PREOPERATIVE_EXPANSION g
// on a.Patient_Id = g.Patient_Id
// and a.Visit_Id = g.Visit_Id
// and a.Oper_Id = g.Oper_Id
// and g.item_name = '术前访视_BP'
// left join MED_PREOPERATIVE_EXPANSION g1
// on a.Patient_Id = g1.Patient_Id
// and a.Visit_Id = g1.Visit_Id
// and a.Oper_Id = g1.Oper_Id
// and g1.item_name = '术前访视单_低压'
// left join MED_PREOPERATIVE_EXPANSION g2
// on a.Patient_Id = g2.Patient_Id
// and a.Visit_Id = g2.Visit_Id
// and a.Oper_Id = g2.Oper_Id
// and g2.item_name = '术前访视_P'
// left join MED_PREOPERATIVE_EXPANSION g3
// on a.Patient_Id = g3.Patient_Id
// and a.Visit_Id = g3.Visit_Id
// and a.Oper_Id = g3.Oper_Id
// and g3.item_name = '术前访视_R'
// left join MED_PREOPERATIVE_EXPANSION g4
// on a.Patient_Id = g4.Patient_Id
// and a.Visit_Id = g4.Visit_Id
// and a.Oper_Id = g4.Oper_Id
// and g4.item_name = '术前访视_心电图'
// left join MED_PREOPERATIVE_EXPANSION g5
// on a.Patient_Id = g5.Patient_Id
// and a.Visit_Id = g5.Visit_Id
// and a.Oper_Id = g5.Oper_Id
// and g5.item_name = '术前访视_药物过敏史'
// left join (select patient_id,visit_id,archive_key Oper_Id ,to_char(replace(wm_concat(Mr_Sub_Class),',',';')) Docs from Med_Emr_Archive_Detial
//group by patient_id,visit_id,archive_key) mead on a.Patient_Id=mead.Patient_Id and a.Visit_Id=mead.Visit_Id and a.Oper_Id=mead.Oper_Id
// where a.patient_id = :PatientId and a.visit_Id =:VisitId and a.oper_Id=:OperId
// ";
string sql = sqlDict.GetSQLByKey("QueryOperPatientProgress");
return(dapper.Set <OperPatientProgressEntity>().Query(sql, new
{
PatientId = model.PatientId,
VisitId = model.VisitId,
OperId = model.OperId
}).FirstOrDefault());
}
public RequestResult <IList <dynamic> > QueryRightEChartReportForDirectionIndex(OperQueryParaModel modela)
{
IList <dynamic> list = WeiXinQuery.QueryRightEChartReportForDirectionIndex(modela);
return(Success(list));
}
public RequestResult <dynamic> QueryWeiXinOperInfo(OperQueryParaModel modela)
{
return(Success(WeiXinQuery.GetWeiXinOperInfo(modela)));
}
public RequestResult <OperPatientProgressEntity> QueryOperPatientProgress(OperQueryParaModel model)
{
OperPatientProgressEntity list = PatientRecord.QueryOperPatientProgress(model);
return(Success(list));
}
/// <summary>
/// 饼图EChart图标统计
/// </summary>
public IList <dynamic> QueryRightEChartReportForDirectionIndex(OperQueryParaModel model)
{
//string sql = sqlDict.GetSQLByKey("QueryOperInfosForDirectorIndex");
//年月日SQL暂时未定怎么存放 暂时放此处
string sql = "";
if (model.QueryType == "ASA")
{
#region ASA统计
if (model.TimeType == "month")
{
//统计月
sql = @"select ASA name,COUNT(1) value from ( SELECT
CASE WHEN INSTR('{0}',T.OLD_ASA_GRADE)>0 THEN '{1}'
WHEN INSTR('{2}',T.OLD_ASA_GRADE)>0 THEN '{3}'
WHEN INSTR('{4}',T.OLD_ASA_GRADE)>0 THEN '{5}'
WHEN INSTR('{6}',T.OLD_ASA_GRADE)>0 THEN '{7}'
WHEN INSTR('{8}',T.OLD_ASA_GRADE)>0 THEN '{9}'
WHEN INSTR('{10}',T.OLD_ASA_GRADE)>0 THEN '{11}'
ELSE '其他' END AS ASA
FROM (
SELECT
START_DATE_TIME, ','||TRIM(ASA_GRADE)||',' OLD_ASA_GRADE
FROM V_MED_ANES_INFO WHERE TO_CHAR(START_DATE_TIME,'YYYY')=:Year AND TO_CHAR(START_DATE_TIME,'MM')=:Month
) T
) main
group by Asa order by ASA
";
List <DictXML.DictGroup> lstASA = mdkConfig.XmlDict.GetGroupByName("ASAGrade");
List <string> sqlStr = new List <string>();
sqlStr = mdkConfig.XmlDict.ReplaceParamsForSql(lstASA, sqlStr);
sql = string.Format(sql, sqlStr.ToArray());
return(dapper.Set <dynamic>().Query(sql, new { Year = Convert.ToDateTime(model.StartDate).Year, Month = Convert.ToDateTime(model.StartDate).Month }));
}
else if (model.TimeType == "year")
{
//统计年
sql = @"select ASA name,COUNT(1) value from ( SELECT
CASE WHEN INSTR('{0}',T.OLD_ASA_GRADE)>0 THEN '{1}'
WHEN INSTR('{2}',T.OLD_ASA_GRADE)>0 THEN '{3}'
WHEN INSTR('{4}',T.OLD_ASA_GRADE)>0 THEN '{5}'
WHEN INSTR('{6}',T.OLD_ASA_GRADE)>0 THEN '{7}'
WHEN INSTR('{8}',T.OLD_ASA_GRADE)>0 THEN '{9}'
WHEN INSTR('{10}',T.OLD_ASA_GRADE)>0 THEN '{11}'
ELSE '其他' END AS ASA
FROM (
SELECT
START_DATE_TIME, ','||TRIM(ASA_GRADE)||',' OLD_ASA_GRADE
FROM V_MED_ANES_INFO WHERE TO_CHAR(START_DATE_TIME,'YYYY')=:Year
) T
) main
group by Asa order by ASA
";
List <DictXML.DictGroup> lstASA = mdkConfig.XmlDict.GetGroupByName("ASAGrade");
List <string> sqlStr = new List <string>();
sqlStr = mdkConfig.XmlDict.ReplaceParamsForSql(lstASA, sqlStr);
sql = string.Format(sql, sqlStr.ToArray());
return(dapper.Set <dynamic>().Query(sql, new { Year = Convert.ToDateTime(model.StartDate).Year }));
}
#endregion
}
else if (model.QueryType == "AnesMethod")
{
if (model.TimeType == "month")
{
sql = @"select nvl(mad.ANAESTHESIA_TYPE,'其他麻醉') name,COUNT(1) value from medsurgery.MED_OPERATION_MASTER m
left join MED_ANESTHESIA_DICT mad on m.Anes_Method = mad.Anaesthesia_Name
WHERE m.oper_status_code>=35 and TO_CHAR(START_DATE_TIME, 'YYYY') = :Year AND TO_CHAR(START_DATE_TIME,'MM')= :Month
group by nvl(mad.ANAESTHESIA_TYPE,'其他麻醉') ";
return(dapper.Set <dynamic>().Query(sql, new { Year = Convert.ToDateTime(model.StartDate).Year, Month = Convert.ToDateTime(model.StartDate).Month }));
}
else if (model.TimeType == "year")
{
sql = @"select nvl(mad.ANAESTHESIA_TYPE,'其他麻醉') name,COUNT(1) value from medsurgery.MED_OPERATION_MASTER m
left join MED_ANESTHESIA_DICT mad on m.Anes_Method = mad.Anaesthesia_Name
WHERE m.oper_status_code>=35 and TO_CHAR(START_DATE_TIME, 'YYYY') = :Year
group by nvl(mad.ANAESTHESIA_TYPE,'其他麻醉')";
return(dapper.Set <dynamic>().Query(sql, new { Year = Convert.ToDateTime(model.StartDate).Year }));
}
}
return(dapper.Set <dynamic>().Query(sql, new { }));
}
public RequestPageResult <IList <OperQuerytForAnesQueryEntity> > QueryOperList(OperQueryParaModel model)
{
IList <OperQuerytForAnesQueryEntity> list = PatientRecord.GetOperList(model);
IList <OperQuerytForAnesQueryEntity> pageList = list;
//分页处理
if (list.Count > model.PageSize)
{
pageList = pageList.Skip((model.CurrentPage - 1) * model.PageSize).Take(model.PageSize).ToList();
}
return(PageSuccess(pageList, model.PageSize, model.CurrentPage, list.Count));
}
//查询手术信息
public dynamic GetOperInfo(OperQueryParaModel model)
{
//string sql = sqlDict.GetSQLByKey("QueryOperInfosForDirectorIndex");
//针对oracle ora-03113: 通信通道的文件结束 报错使用以下SQL
string sql = @" select
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t ) as TodayTotalCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where t.Oper_Status_Code>=35) as TodayCompletedCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where t.Oper_Status_Code>=5 and t.Oper_Status_Code<=35) as TodayOperatingCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where t.Oper_Status_Code=-80) as TodayCancelCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where t.Oper_Status_Code>=0 and Oper_Status_Code<5) as TodayWaitingCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where Emergency_Ind=1 ) as TodayEmergencyCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where Isolation_Ind=2 or INFECTED_IND =2) as TodayGLGRCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where oper_class in (2,3) ) as TodayRescueCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where t.anes_death=1 and t.death_after_oper =1 ) as TodayDeathCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where CANCELED_TYPE = '1' or PACU_TEMPERATURE =1 or CONS_DISTURBANCE =1
or TRACHEA_6H = 1 or OXYGEN_SATURATION =1 or ANES_ANAPHYLAXIS =1 or
ANES_DEATH = 1 or RES_TRACT_OBSTRUCE =1 or CENTRAL_VENOUS = 1
or ANES_START_24H_DEATH = 1 or ANES_START_24H_STOP = 1 or SPINAL_ANES_COMP =1
or TRACHEA_HOARSE = 1 or AFTER_ANES_COMA = 1 or USE_REMINDERS = 1 or
RES_TRACT_OBSTRUCE = 1 or OTHER_NOT_EXP = 1 or NO_PLAN_IN_ICU = 1) as TodayUnExpectCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where t.Oper_Status_Code=65) as TodayToICUCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate,'YYYY-MM-DD')) t where TRUNC((T.OUT_PACU_DATE_TIME-T.IN_PACU_DATE_TIME)*1440 ) >= 120) as TodayPacuCount,
(select count(1) from (
select * from Med_Operation_Master m where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate+1,'YYYY-MM-DD')
) t ) as TomorrowTotalCount,
(select count(1) from (
select * from Med_Operation_Master m
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate-1,'YYYY-MM-DD')) t ) as YesterdayTotalCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate-1,'YYYY-MM-DD')) t where t.Oper_Status_Code>=35) as YesterdayCompletedCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate-1,'YYYY-MM-DD')) t where t.Oper_Status_Code>=0 and Oper_Status_Code<5) as YesterdayWaitingCount,
(select count(1) from (
select * from Med_Operation_Master m left join MED_ANESTHESIA_INPUT_DATA a on m.patient_id=a.Patient_Id and m.visit_id = a.Visit_Id and m.oper_id=a.Oper_Id
where to_char(m.scheduled_date_time,'YYYY-MM-DD')=to_char(sysdate-1,'YYYY-MM-DD')) t where t.Oper_Status_Code=-80) as YesterdayCancelCount,
(select count(1) from (
select * from Med_Operation_Master m
where to_char(m.start_date_time,'YYYY-MM-DD')=to_char(sysdate-1,'YYYY-MM-DD')) t where Isolation_Ind=2 or INFECTED_IND =2) as YesterdayGLGRCount,
(select count(1) from (
select * from Med_Operation_Master m
where to_char(m.start_date_time,'YYYY-MM-DD')=to_char(sysdate-1,'YYYY-MM-DD')) t where oper_class in (2,3) ) as YesterdayRescueCount,
(select count(1) from (
select * from Med_Operation_Master m
where to_char(m.start_date_time,'YYYY-MM-DD')=to_char(sysdate-1,'YYYY-MM-DD')) t where t.Oper_Status_Code=65) as YesterdayToICUCount,
(select count(1) from (
select * from Med_Operation_Master m
where to_char(m.start_date_time,'YYYY-MM-DD')=to_char(sysdate-1,'YYYY-MM-DD')) t where TRUNC((T.OUT_PACU_DATE_TIME-T.IN_PACU_DATE_TIME)*1440 ) >= 120) as YesterdayPacuCount
from dual
";
return(dapper.Set <dynamic>().Query(sql, new { }).FirstOrDefault());
}