public ActionResult DataLoad(int page, int rows, string order, string sort, DateTime startTime, DateTime endTime
, string linkPhone, int alarmEventType, string localAddr, string taskResult, int taskAbendReason
, string centerCode, string stationCode, string ambCode, string driver, string doctor, string litter, string isCharge
, string isFill, string nurse, string patientName, string searchBound, string isTest, string CPRIFSuccess, string PatientState)
{
try
{
P_User pUser = new P_User();
pUser = UserOperateContext.Current.Session_UsrInfo;//获取登录人信息
//bool t= UserOperateContext.Current.Session_UsrRole.Contains(10);//判断登录人是否为司机
M_UserLoginInfo loginInfo = new M_UserLoginInfo();
loginInfo.LoginName = pUser.LoginName;
loginInfo.Name = pUser.Name;//姓名
loginInfo.WorkCode = pUser.WorkCode;//工号
loginInfo.DispatchSubCenterID = pUser.P_Department.DispatchSubCenterID;//所属分中心
loginInfo.DispatchSationID = pUser.P_Department.DispatchSationID;//所属分站
//loginInfo.RoleID = pUser.P_UserRole.RoleID;
M_PatientRecordBLL M_PateintRecord = new M_PatientRecordBLL();
var list = M_PateintRecord.GetTasks(page, rows, order, sort, startTime, endTime, linkPhone, alarmEventType, localAddr
, taskResult, taskAbendReason, centerCode, stationCode, ambCode, driver, doctor, litter, isCharge, isFill
, nurse, patientName, searchBound, isTest, loginInfo, CPRIFSuccess, PatientState);
//if (list != null)
//{
//return this.Json(new { total = list, rows = list }, "appliction/json", JsonRequestBehavior.AllowGet);
return this.Json(list, "appliction/json", JsonRequestBehavior.AllowGet);
//}
//else
//{
// return this.Json(new { total = 0, rows = 0 }, "appliction/json", JsonRequestBehavior.AllowGet);
//}
}
catch
{
return this.Json("");
}
}
public object GetTasks(int page, int rows, string order, string sort, DateTime m_BeginTime, DateTime m_EndTime
, string m_LinkPhone, int m_AlarmEventType, string m_LocalAddr, string m_TaskResult, int m_TaskAbendReason
, string m_CenterCode, string m_StationCode, string m_AmbCode, string m_Driver, string m_Doctor, string m_Litter, string m_IsCharge
, string m_IsFill, string m_Nurse, string m_PatientName, string searchBound, string isTest, M_UserLoginInfo loginInfo
, string m_CPRIFSuccess, string m_PatientState)
{
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);//取管理库的链接字符串
StringBuilder sbSQL = new StringBuilder();//sql语句
sbSQL.Append("declare @BeginTime datetime ");
sbSQL.Append("declare @EndTime datetime ");
sbSQL.Append("set @BeginTime='" + m_BeginTime + "' ");
sbSQL.Append("set @EndTime='" + m_EndTime + "' ");
sbSQL.Append("declare @TaskCodeB char(22) ");
sbSQL.Append("declare @TaskCodeE char(22) ");
sbSQL.Append("set @TaskCodeB = convert(char(8),@BeginTime,112)+'00000000000000' ");
sbSQL.Append("set @TaskCodeE = convert(char(8),dateadd(day,1,@EndTime),112)+'00000000000000' ");
sbSQL.Append("SELECT t.任务编码,ae.首次受理时刻,ae.联系电话, 事件类型=tzae.名称");
sbSQL.Append(",ae.患者姓名,ae.现场地址,派车时刻=acc.发送指令时刻,受理类型=tzac.名称,s.中心编码,t.分站编码,");
sbSQL.Append("分站名称=s.名称,amb.实际标识,");
//sbSQL.Append("分站名称=s.名称,amb.实际标识,司机=dbo.GetStr(t.任务编码,3),医生=dbo.GetStr(t.任务编码,4),担架工=dbo.GetStr(t.任务编码,6),护士=dbo.GetStr(t.任务编码,5),");
sbSQL.Append("出车结果=case when t.是否正常结束=1 then '正常' else '异常结束' end, ");
//病历数
sbSQL.Append("病历数= (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecord ");
sbSQL.Append(" p WHERE p.TaskCode=t.任务编码) ");
//收费-添加多收费后 修改为有一个欠费则显示欠费
//sbSQL.Append(",收费数=isnull((SELECT 收费数=case when sum(case when 是否欠费=1 then 1 else 0 end)>0 then '欠费' else '已收' end FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientCharge cr Where cr.TaskCode=t.任务编码 group by TaskCode ),'未收') ");
sbSQL.Append(@"into #tempX
FROM TTask t
JOIN TAcceptEvent acc ON t.事件编码=acc.事件编码 AND t.受理序号=acc.受理序号
JOIN TAlarmEvent ae ON t.事件编码=ae.事件编码
LEFT JOIN TStation s ON t.分站编码=s.编码
LEFT JOIN TAmbulance amb ON t.车辆编码=amb.车辆编码
LEFT JOIN TZAlarmEventType tzae ON tzae.编码=ae.事件类型编码
LEFT JOIN TZAcceptEventType tzac ON tzac.编码=acc.受理类型编码 ");
StringBuilder sbWhereClause = new StringBuilder();
sbWhereClause.Append("where t.任务编码>@TaskCodeB and t.任务编码<=@TaskCodeE ");
WhereClauseUtility.AddDateTimeGreaterThan("ae.首次受理时刻", m_BeginTime, sbWhereClause);
WhereClauseUtility.AddDateTimeLessThan("ae.首次受理时刻", m_EndTime, sbWhereClause);
WhereClauseUtility.AddStringEqual("ae.是否测试", isTest, sbWhereClause);//是否测试
WhereClauseUtility.AddStringLike("ae.患者姓名", m_PatientName, sbWhereClause);//患者姓名
WhereClauseUtility.AddStringLike("ae.联系电话", m_LinkPhone, sbWhereClause);
WhereClauseUtility.AddIntEqual("ae.事件类型编码", m_AlarmEventType, sbWhereClause);
WhereClauseUtility.AddStringLike("ae.现场地址", m_LocalAddr, sbWhereClause);
//WhereClauseUtility.AddStringEqual("t.责任调度人编码", m_DisCode, sbWhereClause);
WhereClauseUtility.AddStringEqual("t.是否正常结束", m_TaskResult, sbWhereClause);
WhereClauseUtility.AddIntEqual("t.异常结束原因编码", m_TaskAbendReason, sbWhereClause);
WhereClauseUtility.AddStringEqual("t.车辆编码", m_AmbCode, sbWhereClause);
if (m_IsCharge == "True")
{
if (sbWhereClause.Length > 0)
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientCharge cr Where cr.IFArrearage='False' and cr.TaskCode=t.任务编码) >0");
else
sbWhereClause.Append(" WHERE (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientCharge cr Where cr.IFArrearage='False' and cr.TaskCode=t.任务编码) >0");
}
else if (m_IsCharge == "False")
{
if (sbWhereClause.Length > 0)
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientCharge cr Where cr.TaskCode=t.任务编码) =0");
else
sbWhereClause.Append(" WHERE (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientCharge cr Where cr.TaskCode=t.任务编码) =0");
}
else if (m_IsCharge == "Qian")
{
if (sbWhereClause.Length > 0)
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientCharge cr Where cr.IFArrearage='True' and cr.TaskCode=t.任务编码) >0");
else
sbWhereClause.Append(" WHERE (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientCharge cr Where cr.IFArrearage='True' and cr.TaskCode=t.任务编码) >0");
}
if (m_IsFill == "True")
{
if (sbWhereClause.Length > 0)
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecord p WHERE p.TaskCode=t.任务编码)>0");
else
sbWhereClause.Append(" WHERE (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecord p WHERE p.TaskCode=t.任务编码)>0");
}
else if (m_IsFill == "False")
{
if (sbWhereClause.Length > 0)
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecord p WHERE p.TaskCode=t.任务编码)=0 ");
else
sbWhereClause.Append(" WHERE (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecord p WHERE p.TaskCode=t.任务编码)=0");
}
if (m_CPRIFSuccess != "-1")
{
if (m_CPRIFSuccess =="ROSC")
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecord p WHERE p.TaskCode=t.任务编码 and p.CPRIFSuccess='" + m_CPRIFSuccess + "')>0 ");
else if (m_CPRIFSuccess == "已审核")
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecordCPR p WHERE p.TaskCode=t.任务编码 and p.CenterIFAuditForXFFS=1 )>0 ");
else if (m_CPRIFSuccess == "未审核")
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecordCPR p WHERE p.TaskCode=t.任务编码 and p.CenterIFAuditForXFFS=0 )>0 ");
else if (m_CPRIFSuccess == "通过")
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecordCPR p WHERE p.TaskCode=t.任务编码 and p.CenterAuditResult='" + m_CPRIFSuccess + "' )>0 ");
else if (m_CPRIFSuccess == "未通过")
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecordCPR p WHERE p.TaskCode=t.任务编码 and p.CenterAuditResult='" + m_CPRIFSuccess + "' )>0 ");
//else
// sbWhereClause.Append(" WHERE (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecord p WHERE p.TaskCode=t.任务编码 and p.CPRIFSuccess='" + m_CPRIFSuccess + "')>0 ");
}
if (m_PatientState != "-1")
{
if (sbWhereClause.Length > 0)
sbWhereClause.Append(" AND (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecord p WHERE p.TaskCode=t.任务编码 and p.MedicalStateCode='" + m_PatientState + "')>0 ");
else
sbWhereClause.Append(" WHERE (SELECT COUNT(*) FROM ").Append(builder.InitialCatalog).Append(".dbo.M_PatientRecord p WHERE p.TaskCode=t.任务编码 and p.MedicalStateCode='" + m_PatientState + "')>0 ");
}
if (searchBound == "4")
{
if (loginInfo.WorkCode != "")
sbWhereClause.Append(" and (SELECT COUNT(*) FROM TTaskPersonLink ttpl WHERE t.任务编码=ttpl.任务编码 and ttpl.人员编码 = (select 编码 from TPerson where 工号='" + loginInfo.WorkCode + "'))>0 ");
else
sbWhereClause.Append(" and 1=2 ");
}
sbSQL.Append(sbWhereClause);
sbSQL.Append(" select 任务编码1=t.任务编码,司机=dbo.GetStr(t.任务编码,3),医生=dbo.GetStr(t.任务编码,4),担架工=dbo.GetStr(t.任务编码,6),护士=dbo.GetStr(t.任务编码,5)");
sbSQL.Append(@"into #tempY
FROM TTask t
JOIN TAlarmEvent ae ON t.事件编码=ae.事件编码 ");
sbSQL.Append("where t.任务编码>@TaskCodeB and t.任务编码<=@TaskCodeE ");
WhereClauseUtility.AddDateTimeGreaterThan("ae.首次受理时刻", m_BeginTime, sbSQL);
WhereClauseUtility.AddDateTimeLessThan("ae.首次受理时刻", m_EndTime, sbSQL);
WhereClauseUtility.AddStringEqual("ae.是否测试", isTest, sbSQL);//是否测试
sbSQL.Append(" select identity(int,1,1) as 行号, * into #tempZ ");
sbSQL.Append(" from #tempX t ");
sbSQL.Append(" left join #tempY t1 on t.任务编码=t1.任务编码1 ");
sbSQL.Append(" where 1=1 ");
//if (m_IsFill == "True")
//{
// sbSQL.Append(" AND 病历数>0");
//}
//else if (m_IsFill == "False")
//{
// sbSQL.Append(" AND 病历数=0 ");
//}
switch (searchBound)//检索范围
{
case "1"://全部
WhereClauseUtility.AddStringEqual("中心编码", m_CenterCode, sbSQL);
WhereClauseUtility.AddStringEqual("分站编码", m_StationCode, sbSQL);
WhereClauseUtility.AddStringLike("司机", m_Driver, sbSQL);
WhereClauseUtility.AddStringLike("医生", m_Doctor, sbSQL);
WhereClauseUtility.AddStringLike("担架工", m_Litter, sbSQL);
WhereClauseUtility.AddStringLike("护士", m_Nurse, sbSQL);
break;
case "2"://分中心
WhereClauseUtility.AddStringEqual("分站编码", m_StationCode, sbSQL);
WhereClauseUtility.AddStringEqual("中心编码", loginInfo.DispatchSubCenterID, sbSQL);
WhereClauseUtility.AddStringLike("司机", m_Driver, sbSQL);
WhereClauseUtility.AddStringLike("医生", m_Doctor, sbSQL);
WhereClauseUtility.AddStringLike("担架工", m_Litter, sbSQL);
WhereClauseUtility.AddStringLike("护士", m_Nurse, sbSQL);
break;
case "3"://分站
WhereClauseUtility.AddStringEqual("中心编码", m_CenterCode, sbSQL);
WhereClauseUtility.AddStringEqual("分站编码", loginInfo.DispatchSationID, sbSQL);
WhereClauseUtility.AddStringLike("司机", m_Driver, sbSQL);
WhereClauseUtility.AddStringLike("医生", m_Doctor, sbSQL);
WhereClauseUtility.AddStringLike("担架工", m_Litter, sbSQL);
WhereClauseUtility.AddStringLike("护士", m_Nurse, sbSQL);
break;
case "4"://本人
//WhereClauseUtility.AddStringEqual("中心编码", m_CenterCode, sbSQL);
//WhereClauseUtility.AddStringEqual("分站编码", m_StationCode, sbSQL);
break;
}
sbSQL.Append(@"ORDER BY 首次受理时刻 desc ");
sbSQL.Append(@"select top " + rows + " A.* from #tempZ A where 行号>" + (page - 1) * rows + " order by 行号 ");
sbSQL.Append(@"SELECT count(*) FROM #tempZ t ");
sbSQL.Append(" drop table #tempX,#tempY,#tempZ ");
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sbSQL.ToString(), null);
List<M_TaskPartInfo> list = new List<M_TaskPartInfo>();
if (ds.Tables[0].Rows.Count > 0)
{
M_TaskPartInfo info = new M_TaskPartInfo();
foreach (DataRow dr in ds.Tables[0].Rows)
{
info = new M_TaskPartInfo();
info.TaskCode = dr["任务编码"].ToString();
info.PatientName = dr["患者姓名"].ToString();
info.FirstAcceptTime = dr["首次受理时刻"].ToString();
info.ContactTelephone = dr["联系电话"].ToString();
info.EventType = dr["事件类型"].ToString();
info.SceneAddress = dr["现场地址"].ToString();
info.AcceptType = dr["受理类型"].ToString();
info.Station = dr["分站名称"].ToString();
info.ActualLogo = dr["实际标识"].ToString();
info.Driver = dr["司机"].ToString();
info.Doctor = dr["医生"].ToString();
info.Nurse = dr["护士"].ToString();
info.OutCarResults = dr["出车结果"].ToString();
info.PatientNumber = dr["病历数"].ToString();
//info.ChargeNumber = dr["收费数"].ToString();
//info.IsSubmit = dr["是否提交"].ToString();
list.Add(info);
}
}
else
{
list = null;
var resultK = new { total = 0, rows = 0 };//当查询没有数据返回;
return resultK;
}
int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
var result = new { total = total, rows = list };
//return list;
return result;
}
catch (Exception ex)
{
LogUtility.Error("M_PatientRecordDAL/GetTasks("+m_BeginTime+" -- "+ m_EndTime+")", ex.ToString());
return null;
}
}