/// <summary> /// 返回,不同类型的数据源 /// </summary> /// <param name="TypeIndex">当前索引</param> /// <param name="TypeNumber">当前状态值(status)</param> /// <returns></returns> private DataTable GetInitDB(int TypeIndex, int TypeNumber) { DateTime dateStart = System.DateTime.Today.AddMonths(-6); DateTime dateEnd = System.DateTime.Today; //add by zjy 2013-6-14 string valueStr = DrectSoft.Service.DS_SqlService.GetConfigValueByKey("AutoScoreMainpage"); string sql = MedicalRecordManage.Object.DaoCommand._SELECT_MEDICAL_RECORD_BORROW_SQL; if (valueStr.ToLower().Contains("iem_mainpage_diagnosis_sx")) { sql = MedicalRecordManage.Object.DaoCommand._SELECT_MEDICAL_RECORD_BORROW_SQL_ZY; } List <DbParameter> sqlParams = new List <DbParameter>(); sql = sql + " and i.applydocid = @doc"; SqlParameter param1 = new SqlParameter("@doc", SqlDbType.VarChar); param1.Value = m_sUser; sqlParams.Add(param1); if (dateStart != null && dateStart.ToString() != "") { string ds = dateStart.ToString("yyyy-MM-dd"); sql = sql + " and to_char(i.applydate,'yyyy-mm-dd') >= @ds"; SqlParameter param2 = new SqlParameter("@ds", SqlDbType.VarChar); param2.Value = ds; sqlParams.Add(param2); } if (dateEnd != null && dateEnd.ToString() != "") { string de = dateEnd.ToString("yyyy-MM-dd"); sql = sql + " and to_char(i.applydate,'yyyy-mm-dd') <= @de"; SqlParameter param3 = new SqlParameter("@de", SqlDbType.VarChar); param3.Value = de; sqlParams.Add(param3); } if (TypeNumber >= 0) { sql = sql + " and i.status = @st"; SqlParameter param4 = new SqlParameter("@st", SqlDbType.Int); param4.Value = TypeNumber; sqlParams.Add(param4); } DataTable dataTable = DrectSoft.DSSqlHelper.DS_SqlHelper.ExecuteDataTable(sql, sqlParams, CommandType.Text); //压缩DateSet中的记录,将住院诊断信息合并 ComponentCommand.ImpressDataSet(ref dataTable, "id", "cyzd"); //填入状态值 ComponentCommand.InitializeStatusInfo(ref dataTable); //控制状态显示和预警信息显示颜色显示 return(dataTable); }
public void GetInitDB(bool IsUpdate) { if (IsUpdate) { //add by zjy 2013-6-14 string valueStr = DrectSoft.Service.DS_SqlService.GetConfigValueByKey("AutoScoreMainpage"); string sql = MedicalRecordManage.Object.DaoCommand._SELECT_MEDICAL_RECORD_BORROW_SQL; if (valueStr.ToLower().Contains("iem_mainpage_diagnosis_sx")) { sql = MedicalRecordManage.Object.DaoCommand._SELECT_MEDICAL_RECORD_BORROW_SQL_ZY; } //string sql = MedicalRecordManage.Object.DaoCommand._SELECT_MEDICAL_RECORD_BORROW_SQL; List <DbParameter> sqlParams = new List <DbParameter>(); sql = sql + " and i.applydocid = @doc"; SqlParameter param1 = new SqlParameter("@doc", SqlDbType.VarChar); param1.Value = m_sUser; sqlParams.Add(param1); if (this.dateStart.Text.Trim() != null && this.dateStart.Text.Trim() != "") { string ds = this.dateStart.DateTime.ToString("yyyy-MM-dd"); sql = sql + " and to_char(i.applydate,'yyyy-mm-dd') >= @ds"; SqlParameter param2 = new SqlParameter("@ds", SqlDbType.VarChar); param2.Value = ds; sqlParams.Add(param2); } if (this.dateEnd.Text.Trim() != null && this.dateEnd.Text.Trim() != "") { string de = this.dateEnd.DateTime.ToString("yyyy-MM-dd"); sql = sql + " and to_char(i.applydate,'yyyy-mm-dd') <= @de"; SqlParameter param3 = new SqlParameter("@de", SqlDbType.VarChar); param3.Value = de; sqlParams.Add(param3); } if (TypeNumber >= 0) { sql = sql + " and i.status = @st"; SqlParameter param4 = new SqlParameter("@st", SqlDbType.Int); param4.Value = TypeNumber; sqlParams.Add(param4); } DataTable dataTable = DrectSoft.DSSqlHelper.DS_SqlHelper.ExecuteDataTable(sql, sqlParams, CommandType.Text); //压缩DateSet中的记录,将住院诊断信息合并 ComponentCommand.ImpressDataSet(ref dataTable, "id", "cyzd"); //填入状态值 ComponentCommand.InitializeStatusInfo(ref dataTable); //控制状态显示和预警信息显示颜色显示 User_Table = dataTable; } this.dbGrid.DataSource = User_Table; if (TypeIndex == 4) { this.dbGridView.Columns["STATUSDES"].Visible = true; } else { this.dbGridView.Columns["STATUSDES"].Visible = false; } //审核日期 if (TypeIndex == 4 || TypeIndex == 1 || TypeIndex == 3) { this.dbGridView.Columns["SHSJ"].Visible = true; } else { this.dbGridView.Columns["SHSJ"].Visible = false; } }
/// <summary> /// Modify by xlb 2013-05-29 /// 解决同一天数据检索不出问题 /// <auth> Modify by xlb</auth> /// <date>2013-05-29</date> /// </summary> private void LoadData() { try { string valueStr = DrectSoft.Service.DS_SqlService.GetConfigValueByKey("AutoScoreMainpage"); string sql = DaoCommand._SELECT_MEDICAL_RECORD_INPATIENT_SHORT; if (valueStr.ToLower().Contains("iem_mainpage_diagnosis_sx")) { sql = DaoCommand._SELECT_MEDICAL_RECORD_INPATIENT_SHORT_ZY; } //设置过滤条件,本用户已经申请的不再查询出结果 sql = sql + " and i.noofinpat not in ( " + " select w.noofinpat from emr_recordborrow w " + " where w.status < 3 and w.applydocid = " + "'" + m_sUser + "') "; /* * 输入参数 */ List <DbParameter> sqlParams = new List <DbParameter>(); if (this.txtName.Text.Trim() != null && this.txtName.Text.Trim() != "") { sql = sql + " and i.name like @name "; SqlParameter param1 = new SqlParameter("@name", SqlDbType.VarChar); param1.Value = "%" + this.txtName.Text.Trim() + "%"; sqlParams.Add(param1); } if (this.txtNumber.Text.Trim() != null && this.txtNumber.Text.Trim() != "") { sql = sql + " and i.patid like '%'||@patId||'%' "; SqlParameter param2 = new SqlParameter("@patId", SqlDbType.VarChar); param2.Value = this.txtNumber.Text.Trim(); sqlParams.Add(param2); } //if (this.lookUpEditorDiagnosis.Text.Trim() != null && this.lookUpEditorDiagnosis.Text.Trim() != "") //{ // sql = sql + " and i.cyzd like @cyzd "; // SqlParameter param3 = new SqlParameter("@cyzd", SqlDbType.VarChar); // param3.Value = "%" + this.lookUpEditorDiagnosis.Text.Trim() + "%"; // sqlParams.Add(param3); //} if (this.lookUpEditorDepartment.Text != null && this.lookUpEditorDepartment.Text.Trim() != "") { if (lookUpEditorDepartment.CodeValue != "0000") { sql = sql + " and i.outhosdept = @dept"; SqlParameter param4 = new SqlParameter("@dept", SqlDbType.VarChar); param4.Value = lookUpEditorDepartment.CodeValue; sqlParams.Add(param4); } } if (this.dateLeaveStart.Text.Trim() != null && this.dateLeaveStart.Text.Trim() != "") { string ds = this.dateLeaveStart.DateTime.ToString("yyyy-MM-dd 00:00:00"); sql = sql + " and i.outhosdate >= @ds"; SqlParameter param5 = new SqlParameter("@ds", SqlDbType.VarChar); param5.Value = ds; sqlParams.Add(param5); } if (this.dateLeaveEnd.Text.Trim() != null && this.dateLeaveEnd.Text.Trim() != "") { string de = this.dateLeaveEnd.DateTime.ToString("yyyy-MM-dd 23:59:59"); sql = sql + " and i.outhosdate <= @de"; SqlParameter param6 = new SqlParameter("@de", SqlDbType.VarChar); param6.Value = de; sqlParams.Add(param6); } DataTable dataTable = DrectSoft.DSSqlHelper.DS_SqlHelper.ExecuteDataTable(sql, sqlParams, CommandType.Text); //压缩数据源生成新的数据源 //压缩DateSet中的记录,将住院诊断信息合并 ComponentCommand.ImpressDataSet(ref dataTable, "noofinpat", "cyzd"); string wheresql = "1=1 "; if (this.lookUpEditorDiagnosis.Text.Trim() != null && this.lookUpEditorDiagnosis.Text.Trim() != "") { wheresql = wheresql + " and CYZD like '%" + this.lookUpEditorDiagnosis.Text.Trim() + "%'"; } DataRow[] dr = dataTable.Select(wheresql); this.dbGrid.DataSource = ToDataTable(dr); } catch (Exception) { throw; } }
/// <summary> /// 出院病人查询方法 /// </summary> /// <param name="valueStr"></param> private void GoSerachOutHosPatInfo(string valueStr) { try { string sql = MedicalRecordManage.Object.DaoCommand._SELECT_MEDICAL_RECORD_INPATIENT_LONG; if (valueStr.ToLower().Contains("iem_mainpage_diagnosis_sx")) { sql = DaoCommand._SELECT_MEDICAL_RECORD_INPATIENT_LONG_ZY; } List <DbParameter> sqlParams = new List <DbParameter>(); if (this.txtName.Text.Trim() != null && this.txtName.Text.Trim() != "") { sql = sql + " and i.name like '%'||@name||'%' "; SqlParameter param1 = new SqlParameter("@name", SqlDbType.VarChar); param1.Value = this.txtName.Text.Trim(); sqlParams.Add(param1); } if (this.txtNumber.Text.Trim() != null && this.txtNumber.Text.Trim() != "") { sql = sql + " and i.patid like '%'||@patId||'%'"; SqlParameter param2 = new SqlParameter("@patId", SqlDbType.VarChar); param2.Value = this.txtNumber.Text.Trim(); sqlParams.Add(param2); } if (this.lookUpEditorDepartment.Text != null && this.lookUpEditorDepartment.Text.Trim() != "") { if (lookUpEditorDepartment.CodeValue != "0000") { sql = sql + " and i.outhosdept = @code "; SqlParameter param5 = new SqlParameter("@code", SqlDbType.VarChar); param5.Value = lookUpEditorDepartment.CodeValue; sqlParams.Add(param5); } } //add by ck 2013-8-26 if (this.lookUpEditorDoctor.Text != null && this.lookUpEditorDoctor.Text.Trim() != "") { sql = sql + " and i.resident = @inpatientID"; SqlParameter param4 = new SqlParameter("@inpatientID", SqlDbType.VarChar); param4.Value = lookUpEditorDoctor.CodeValue; sqlParams.Add(param4); }//主治医生 if (this.dateAdmitStart.Text.Trim() != null && this.dateAdmitStart.Text.Trim() != "") { string ds = this.dateAdmitStart.DateTime.ToString("yyyy-MM-dd 00:00:00");//开始时间默认当天的0点 sql = sql + " and i.admitdate >= @ds "; SqlParameter param6 = new SqlParameter("@ds", SqlDbType.VarChar); param6.Value = ds; sqlParams.Add(param6); } if (this.dateAdmitEnd.Text.Trim() != null && this.dateAdmitEnd.Text.Trim() != "") { string de = this.dateAdmitEnd.DateTime.ToString("yyyy-MM-dd 23:59:59");//结束时间默认当天23:59:59 sql = sql + " and i.admitdate <= @de "; SqlParameter param7 = new SqlParameter("@de", SqlDbType.VarChar); param7.Value = de; sqlParams.Add(param7); } if (this.dateLeaveStart.Text.Trim() != null && this.dateLeaveStart.Text.Trim() != "") { string ds = this.dateLeaveStart.DateTime.ToString("yyyy-MM-dd 00:00:00"); sql = sql + " and i.outhosdate >= @dss"; SqlParameter param8 = new SqlParameter("@dss", SqlDbType.VarChar); param8.Value = ds; sqlParams.Add(param8); } if (this.dateLeaveEnd.Text.Trim() != null && this.dateLeaveEnd.Text.Trim() != "") { string de = this.dateLeaveEnd.DateTime.ToString("yyyy-MM-dd 23:59:59"); sql = sql + " and i.outhosdate <= @dee"; SqlParameter param9 = new SqlParameter("@dee", SqlDbType.VarChar); param9.Value = de; sqlParams.Add(param9); } if (this.cbxLockStatus.Text.Trim() != null && this.cbxLockStatus.Text.Trim() != "") { int lockstatus = ComponentCommand.GetLockStatusValue(this.cbxLockStatus.Text); if (lockstatus == 4700) { sql = sql + " and (i.islock = @lockstatus OR i.islock is null)"; } else { sql = sql + " and (i.islock = @lockstatus or @lockstatus =0) "; } SqlParameter param10 = new SqlParameter("@lockstatus", SqlDbType.VarChar); param10.Value = lockstatus; sqlParams.Add(param10); } DataTable dataTable = DrectSoft.DSSqlHelper.DS_SqlHelper.ExecuteDataTable(sql, sqlParams, CommandType.Text); //压缩数据源生成新的数据源 //压缩DateSet中的记录,将住院诊断信息合并 ComponentCommand.ImpressDataSet(ref dataTable, "noofinpat", "cyzd", "operation_name"); //add by zjy 2013-6-17 string wheresql = "1=1 "; if (this.lookUpEditorDiagnosis.Text.Trim() != null && this.lookUpEditorDiagnosis.Text.Trim() != "") { wheresql = wheresql + " and cyzd like '%" + this.lookUpEditorDiagnosis.Text.Trim() + "%'"; } if (this.lookUpEditorOperation.Text.Trim() != null && this.lookUpEditorOperation.Text.Trim() != "") { wheresql = wheresql + " and operation_name like '%" + this.lookUpEditorOperation.Text.Trim().Trim() + "%'"; } DataRow[] dr = dataTable.Select(wheresql); if (dr != null && dr.Length > 0) { DataTable tableRecord = ToDataTable(dr); string ResultName = string.Empty;//声明最终要在列表显示的姓名的内容 for (int i = 0; i < tableRecord.Rows.Count; i++) { ResultName = SqlUtil.GetPatsBabyContent(SqlUtil.App, tableRecord.Rows[i]["noofinpat"].ToString()); tableRecord.Rows[i]["Name"] = ResultName; if (tableRecord.Rows[i]["isbaby"].ToString() == "1") { tableRecord.Rows[i]["PATID"] = SqlUtil.GetPatsBabyMother(dataTable.Rows[i]["mother"].ToString()); } } //绑定控件 this.dbGrid.DataSource = tableRecord; lblTip.Text = "共" + tableRecord.Rows.Count.ToString() + "条"; } else { this.dbGrid.DataSource = null; lblTip.Text = "共0条"; } } catch (Exception ex) { MyMessageBox.Show(1, ex); } }
/// <summary> /// 查询加载数据方法 /// Modify by xlb 2013-05-30 /// </summary> private void LoadData() { try { //add by 2013-6-14 xll string valueStr = DrectSoft.Service.DS_SqlService.GetConfigValueByKey("AutoScoreMainpage"); string sql = MedicalRecordManage.Object.DaoCommand._SELECT_MEDICAL_RECORD_BORROW_SQL; if (valueStr.ToLower().Contains("iem_mainpage_diagnosis_sx")) { sql = MedicalRecordManage.Object.DaoCommand._SELECT_MEDICAL_RECORD_BORROW_SQL_ZY; } List <DbParameter> sqlParams = new List <DbParameter>(); if (this.txtDoctor.Text.Trim() != null && this.txtDoctor.Text.Trim() != "") { sql = sql + " and i.applyname like @doc "; SqlParameter param1 = new SqlParameter("@doc", SqlDbType.VarChar); param1.Value = "%" + this.txtDoctor.Text.Trim() + "%"; sqlParams.Add(param1); } if (this.txtName.Text.Trim() != null && this.txtName.Text.Trim() != "") { sql = sql + " and i.name like @name "; SqlParameter param2 = new SqlParameter("@name", SqlDbType.VarChar); param2.Value = "%" + this.txtName.Text.Trim() + "%"; sqlParams.Add(param2); } if (this.txtNumber.Text.Trim() != null && this.txtNumber.Text.Trim() != "") { sql = sql + " and i.patid like '%'||@patId||'%' "; SqlParameter param3 = new SqlParameter("@patId", SqlDbType.VarChar); param3.Value = this.txtNumber.Text.Trim(); sqlParams.Add(param3); } if (this.lookUpEditorDepartment.Text != null && this.lookUpEditorDepartment.Text.Trim() != "") { if (lookUpEditorDepartment.CodeValue != "0000") { sql = sql + " and i.outhosdept = @dept"; SqlParameter param4 = new SqlParameter("@dept", SqlDbType.VarChar); param4.Value = lookUpEditorDepartment.CodeValue; sqlParams.Add(param4); } } if (this.dateStart.Text.Trim() != null && this.dateStart.Text.Trim() != "") { string ds = this.dateStart.DateTime.ToString("yyyy-MM-dd 00:00:00"); sql = sql + " and to_char(i.applydate,'yyyy-mm-dd hh24:mi:ss') >= @ds"; SqlParameter param5 = new SqlParameter("@ds", SqlDbType.VarChar); param5.Value = ds; sqlParams.Add(param5); } if (this.dateEnd.Text.Trim() != null && this.dateEnd.Text.Trim() != "") { string de = this.dateEnd.DateTime.ToString("yyyy-MM-dd 23:59:59"); sql = sql + " and to_char(i.applydate,'yyyy-mm-dd hh24:mi:ss') <= @de"; SqlParameter param6 = new SqlParameter("@de", SqlDbType.VarChar); param6.Value = de; sqlParams.Add(param6); } if (this.cbxStatus.Text != "") { sql = sql + " and i.status = @st"; SqlParameter param7 = new SqlParameter("@st", SqlDbType.VarChar); param7.Value = ComponentCommand.GetStatusValue(this.cbxStatus.Text); sqlParams.Add(param7); } DataTable dataTable = DrectSoft.DSSqlHelper.DS_SqlHelper.ExecuteDataTable(sql, sqlParams, CommandType.Text); //压缩数据源生成新的数据源 //压缩DateSet中的记录,将住院诊断信息合并 ComponentCommand.ImpressDataSet(ref dataTable, "id", "cyzd"); // ComponentCommand.InitializeStatusInfo(ref dataTable); string ResultName = string.Empty;//声明最终要在列表显示的姓名的内容 for (int i = 0; i < dataTable.Rows.Count; i++) { ResultName = SqlUtil.GetPatsBabyContent(SqlUtil.App, dataTable.Rows[i]["noofinpat"].ToString()); dataTable.Rows[i]["Name"] = ResultName; if (dataTable.Rows[i]["isbaby"].ToString() == "1") { dataTable.Rows[i]["PATID"] = SqlUtil.GetPatsBabyMother(dataTable.Rows[i]["mother"].ToString()); } } this.dbGrid.DataSource = dataTable; } catch (Exception ex) { throw ex; } }