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; } }