/// <summary> /// 获取月结记录 /// </summary> private void QueryStoreInterval() { Neusoft.FrameWork.Management.DataBaseManger dataManager = new Neusoft.FrameWork.Management.DataBaseManger(); if (this.OperDept.ID == "") { return; } DataSet ds = new DataSet(); string exeInterval = string.Format(this.sqlInterval, this.OperDept.ID); if (dataManager.ExecQuery(exeInterval, ref ds) == -1) { MessageBox.Show(Language.Msg("获取月结记录发生错误")); return; } if (ds != null && ds.Tables.Count > 0) { this.cmbMonthStoreInterval.Items.Clear(); foreach (DataRow dr in ds.Tables[0].Rows) { string strInterval = NConvert.ToDateTime(dr[0]).ToString() + "--" + NConvert.ToDateTime(dr[1]).ToString(); this.cmbMonthStoreInterval.Items.Add(strInterval); } } }
/// <summary> /// 汇总查询 /// </summary> /// <returns></returns> protected int QueryTotal() { Neusoft.FrameWork.Management.DataBaseManger dataManager = new Neusoft.FrameWork.Management.DataBaseManger(); if (this.OperDept.ID == "") { return(-1); } this.Clear(); DataSet ds = new DataSet(); this.lbDept.Text = "库存科室:" + this.OperDept.Name; this.lbDate.Text = "统计周期:" + this.BeginTime.ToString() + "至" + this.EndTime.ToString(); string exeTotal = string.Format(this.sqlTotal, this.OperDept.ID, this.BeginTime.ToString(), this.EndTime.ToString()); if (dataManager.ExecQuery(exeTotal, ref ds) == -1) { MessageBox.Show(Language.Msg("获取年结汇总记录发生错误")); return(-1); } if (ds != null && ds.Tables.Count > 0) { this.fpHead.DataSource = ds; this.Sum(); } return(1); }
private void GetRecipeLabelItem(string drugDept, string drugCode, ref Neusoft.HISFC.Models.Pharmacy.Item item) { Neusoft.FrameWork.Management.DataBaseManger dataBaseMgr = new Neusoft.FrameWork.Management.DataBaseManger(); string strSql = @"select t.trade_name,t.regular_name,t.formal_name,t.other_name, t.english_regular,t.english_other,t.english_name,t.caution,t.store_condition,t.base_dose,s.place_code, t.custom_code from pha_com_baseinfo t,pha_com_stockinfo s where t.drug_code = s.drug_code and s.drug_dept_code = '{0}' and s.drug_code = '{1}'"; strSql = string.Format(strSql, drugDept, drugCode); if (dataBaseMgr.ExecQuery(strSql) != -1) { if (dataBaseMgr.Reader.Read()) { item.Name = dataBaseMgr.Reader[0].ToString(); item.NameCollection.RegularName = dataBaseMgr.Reader[1].ToString(); item.NameCollection.FormalName = dataBaseMgr.Reader[2].ToString(); item.NameCollection.OtherName = dataBaseMgr.Reader[3].ToString(); item.NameCollection.EnglishRegularName = dataBaseMgr.Reader[4].ToString(); item.NameCollection.EnglishOtherName = dataBaseMgr.Reader[5].ToString(); item.NameCollection.EnglishName = dataBaseMgr.Reader[6].ToString(); item.Product.Caution = dataBaseMgr.Reader[7].ToString(); item.Product.StoreCondition = dataBaseMgr.Reader[8].ToString(); item.BaseDose = Neusoft.FrameWork.Function.NConvert.ToDecimal(dataBaseMgr.Reader[9].ToString()); item.User01 = dataBaseMgr.Reader[10].ToString(); item.NameCollection.UserCode = dataBaseMgr.Reader[11].ToString(); } } }
/// <summary> /// 数据查询 /// </summary> private void QueryData() { if (this.cmbMonthStoreInterval.Text == "") { return; } if (this.OperDept.ID == "") { return; } Neusoft.FrameWork.Management.DataBaseManger dataManager = new Neusoft.FrameWork.Management.DataBaseManger(); string strInterval = this.cmbMonthStoreInterval.Text; string strBegin = strInterval.Substring(0, strInterval.IndexOf("--")); string strEnd = strInterval.Substring(strInterval.IndexOf("--") + 2); string exeDetail = string.Format(this.sqlTotal, this.OperDept.ID, strBegin, strEnd); DataSet ds = new DataSet(); this.lbDept.Text = "库存科室:" + this.OperDept.Name; this.lbDate.Text = "统计区间:" + this.cmbMonthStoreInterval.Text; if (dataManager.ExecQuery(exeDetail, ref ds) == -1) { MessageBox.Show(Language.Msg("获取月结明细发生错误")); return; } if (ds != null && ds.Tables.Count > 0) { this.fpDetail.Rows.Count = 0; this.fpHead.DataSource = ds; int iTotIndex = this.fpHead.Rows.Count; this.fpHead.Rows.Add(iTotIndex, 1); this.fpHead.Cells[iTotIndex, 0].Text = "合计:"; if (iTotIndex == 0) { for (int i = 1; i < this.fpHead.Columns.Count; i++) { this.fpHead.Cells[iTotIndex, i].Text = "0"; } } else { for (int i = 1; i < this.fpHead.Columns.Count; i++) { this.fpHead.Cells[iTotIndex, i].Formula = "SUM(" + (char)(65 + i) + "1:" + (char)(65 + i) + iTotIndex.ToString() + ")"; } } this.fpHead.Rows[iTotIndex].Font = new System.Drawing.Font("宋体", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(134))); } }
/// <summary> /// 数据检索 /// </summary> protected void QueryData() { if (!this.IsValid()) { return; } this.neuSpread1.ActiveSheet = this.neuSpread1_Sheet1; if (this.neuSpread1.Sheets.Contains(this.neuSpread1_Sheet2)) { this.neuSpread1.Sheets.Remove(this.neuSpread1_Sheet2); } Neusoft.FrameWork.Management.DataBaseManger dataManager = new Neusoft.FrameWork.Management.DataBaseManger(); string sqlIndex = this.GetSqlIndex(); string sql = ""; dataManager.Sql.GetSql(sqlIndex, ref sql); sql = string.Format(sql, this.cmbData.Tag.ToString(), Neusoft.FrameWork.Function.NConvert.ToDateTime(this.dtBegin.Text).ToString(), Neusoft.FrameWork.Function.NConvert.ToDateTime(this.dtEnd.Text).ToString()); DataSet ds = new DataSet(); if (dataManager.ExecQuery(sql, ref ds) == -1) { MessageBox.Show(Language.Msg("工作量查询出错 \n" + dataManager.Err)); return; } this.neuSpread1_Sheet1.Rows.Count = 0; if (ds.Tables[0].Rows.Count > 0) { this.neuSpread1_Sheet1.DataSource = ds; } try { if (this.rbPerson.Checked) { this.Sum(2, 3, 4, 5); } else { if (this.rbTerminalShow.Checked) { this.Sum(1, 2, 3, 4); } else { this.Sum(2, 3, 4, 5); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
/// <summary> /// /// </summary> public void Query() { Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("ÕýÔÚ½øÐвéѯ...ÇëÉÔºò"); Application.DoEvents(); string exeSql = ""; if (this.ckAll.Checked) //È«²¿Ò©Æ· { exeSql = this.sql1; } else { if (this.cmbDrug.Tag != null && this.cmbDrug.Tag.ToString() != "") { exeSql = string.Format(this.sql2, this.cmbDrug.Tag.ToString()); } } Neusoft.FrameWork.Management.DataBaseManger dataManager = new Neusoft.FrameWork.Management.DataBaseManger(); DataSet ds = new DataSet(); if (dataManager.ExecQuery(exeSql, ref ds) == -1) { Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); MessageBox.Show(Language.Msg("Ö´ÐÐSqlÓï¾ä·¢Éú´íÎó" + dataManager.Err)); return; } //if (ds != null && ds.Tables.Count > 0) //{ // foreach (System.Data.DataRow dr in ds.Tables[0].Rows) // { // if (ds.Tables[0].Columns.Contains("¿â´æ¿ÆÊÒ")) // dr["¿â´æ¿ÆÊÒ"] = this.deptHelper.GetName(dr["¿â´æ¿ÆÊÒ"].ToString()); // } //} this.dv = new DataView(ds.Tables[0]); this.neuSpread1_Sheet1.DataSource = dv; this.SetFormat(); if (this.neuSpread1.ActiveSheet != this.neuSpread1_Sheet1) { this.neuSpread1.ActiveSheet = this.neuSpread1_Sheet1; } Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); }
/// <summary> /// 明细查询 /// </summary> /// <param name="iRowIndex">索引行</param> /// <returns>成功返回1 失败返回-1</returns> protected int QueryDetail(int iRowIndex) { if (this.neuSpread1.ActiveSheet == this.fpDetail) { return(-1); } if (iRowIndex == this.fpHead.Rows.Count - 1) { return(-1); } if (this.OperDept.ID == "") { return(-1); } if (this.fpHead.Rows.Count <= 0) { return(-1); } Neusoft.FrameWork.Management.DataBaseManger dataManager = new Neusoft.FrameWork.Management.DataBaseManger(); DateTime dtBegin = NConvert.ToDateTime(this.fpHead.Cells[iRowIndex, 0].Text); DateTime dtEnd = NConvert.ToDateTime(this.fpHead.Cells[iRowIndex, 1].Text); string exeDetail = string.Format(this.sqlDetail, this.OperDept.ID, dtBegin.ToString(), dtEnd.ToString()); DataSet ds = new DataSet(); if (dataManager.ExecQuery(exeDetail, ref ds) == -1) { MessageBox.Show(Language.Msg("获取月结明细发生错误")); return(-1); } if (ds != null && ds.Tables.Count > 0) { this.fpDetail.DataSource = ds; this.neuSpread1.ActiveSheet = this.fpDetail; } return(1); }
private void fpSpread1_CellDoubleClick(object sender, FarPoint.Win.Spread.CellClickEventArgs e) { if (this.cmbMonthStoreInterval.Text == "") { return; } if (this.OperDept.ID == "") { return; } if (this.fpHead.Rows.Count <= 0) { return; } Neusoft.FrameWork.Management.DataBaseManger dataManager = new Neusoft.FrameWork.Management.DataBaseManger(); string strInterval = this.cmbMonthStoreInterval.Text; string strBegin = strInterval.Substring(0, strInterval.IndexOf("--")); string strEnd = strInterval.Substring(strInterval.IndexOf("--") + 2); string exeDetail = string.Format(this.sqlDetail, this.OperDept.ID, strBegin, strEnd); DataSet ds = new DataSet(); if (dataManager.ExecQuery(exeDetail, ref ds) == -1) { MessageBox.Show(Language.Msg("获取月结明细发生错误")); return; } if (ds != null && ds.Tables.Count > 0) { this.fpDetail.DataSource = ds; this.neuSpread1.ActiveSheet = this.fpDetail; } }
/// <summary> /// 明细查询 /// </summary> /// <param name="operCode">欲查询人员编码</param> protected void QueryDetail(string operCode) { this.neuSpread1_Sheet2.Rows.Count = 0; DateTime beginTime = Neusoft.FrameWork.Function.NConvert.ToDateTime(this.dtBegin.Text); DateTime endTime = Neusoft.FrameWork.Function.NConvert.ToDateTime(this.dtEnd.Text); DateTime queryBeginDate = beginTime; DateTime queryEndDate = System.DateTime.MinValue; Neusoft.FrameWork.Management.DataBaseManger dataManager = new Neusoft.FrameWork.Management.DataBaseManger(); string sqlIndex = ""; if (this.isDrugTerminalQuery) { sqlIndex = "Pharmacy.Item.ClinicQuery.Druged.Oper.Detail"; } else { sqlIndex = "Pharmacy.Item.ClinicQuery.Send.Oper.Detail"; } string sql = ""; if (dataManager.Sql.GetSql(sqlIndex, ref sql) == -1) { MessageBox.Show(Language.Msg("根据索引获取Sql语句出错 \n" + dataManager.Err)); return; } DataSet dsDetail = null; string formatSql = ""; while (queryEndDate < endTime) { queryEndDate = queryBeginDate.Date.AddDays(1); if (queryEndDate > endTime) { queryEndDate = endTime; } formatSql = string.Format(sql, operCode, queryBeginDate, queryEndDate, this.cmbData.Tag.ToString()); DataSet ds = new DataSet(); if (dataManager.ExecQuery(formatSql, ref ds) == -1) { MessageBox.Show(Language.Msg("工作量查询出错 \n" + dataManager.Err)); return; } if (ds.Tables[0].Rows.Count > 0) { if (dsDetail == null) { dsDetail = ds.Clone(); } foreach (DataRow dr in ds.Tables[0].Rows) { dsDetail.Tables[0].Rows.Add(new object[] { dr[0], dr[1], dr[2], dr[3], dr[4], dr[5], dr[6] }); } } queryBeginDate = queryEndDate; } if (dsDetail != null && dsDetail.Tables.Count > 0 && dsDetail.Tables[0].Rows.Count > 0) { this.neuSpread1_Sheet2.DataSource = dsDetail; } if (this.neuSpread1.Sheets.Contains(this.neuSpread1_Sheet2)) { this.neuSpread1.ActiveSheet = this.neuSpread1_Sheet2; } }
/// <summary> /// 获取床位日报信息 /// </summary> /// <param name="deptCode">科室编码 如科室编码为空则统计全院床位日报信息</param> /// <returns></returns> protected int QueryHosDayReport() { this.lbDate.Text = "日期:" + this.BeginDate.ToString("yyyy-MM-dd") + " 至 " + this.EndDate.ToString("yyyy-MM-dd"); this.lbDept.Text = "统计范围:全院"; #region 床位明细 转入、转出 string execDetailSql = @"select t.dept_code,t.stat_type,t.extend,count(*) --select * from met_cas_dayreport_detail t where t.stat_date >= to_date('{0}','yyyy-mm-dd') and t.stat_date <= to_date('{1}','yyyy-mm-dd') and t.valid_state = '0' group by t.dept_code,t.stat_type,t.extend order by t.dept_code"; execDetailSql = string.Format(execDetailSql, this.BeginDate.ToString("yyyy-MM-dd"), this.EndDate.ToString("yyyy-MM-dd")); DataSet ds = new DataSet(); Neusoft.FrameWork.Management.DataBaseManger dataManager = new Neusoft.FrameWork.Management.DataBaseManger(); if (dataManager.ExecQuery(execDetailSql, ref ds) == -1) { MessageBox.Show("床位日报查询发生错误" + dataManager.Err); return(-1); } this.neuSpread1_Sheet1.Rows.Count = 0; if (ds != null && ds.Tables[0].Rows.Count > 0) { #region 床位日报信息处理 string privDept = ""; int rowIndex = -1; foreach (DataRow dr in ds.Tables[0].Rows) { if (privDept != dr[0].ToString()) { rowIndex++; this.neuSpread1_Sheet1.Rows.Add(rowIndex, 1); this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColDeptCode].Text = dr[0].ToString(); this.neuSpread1_Sheet1.Cells[rowIndex, 0].Text = this.deptHelper.GetName(dr[0].ToString()); privDept = dr[0].ToString(); } switch (dr[1].ToString()) { case "K": //接诊 case "C": //召回 decimal originalNum = NConvert.ToDecimal(this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColInNum].Text); this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColInNum].Text = (originalNum + NConvert.ToDecimal(dr[3])).ToString(); break; case "O": //出院 switch (dr[2].ToString()) { case "1": this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColOut1].Text = NConvert.ToDecimal(dr[3]).ToString(); break; case "2": this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColOut2].Text = NConvert.ToDecimal(dr[3]).ToString(); break; case "3": this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColOut3].Text = NConvert.ToDecimal(dr[3]).ToString(); break; case "4": this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColOut4].Text = NConvert.ToDecimal(dr[3]).ToString(); break; } decimal totNum = NConvert.ToDecimal(this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColTot].Text); this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColTot].Text = (totNum + NConvert.ToDecimal(dr[3])).ToString(); break; case "RI": //转入 this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColTransferIn].Text = NConvert.ToDecimal(dr[3]).ToString(); break; case "RO": //转出 this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColTransferOut].Text = NConvert.ToDecimal(dr[3]).ToString(); break; case "OF": //无费退院 decimal totOFNum = NConvert.ToDecimal(this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColTot].Text); this.neuSpread1_Sheet1.Cells[rowIndex, (int)ColumnEnum.ColTot].Text = (totOFNum + NConvert.ToDecimal(dr[3])).ToString(); break; } } #endregion } #endregion #region 期初、期末人数 string execTotSql = @"select s.dept_code,sum(s.in_normal + s.in_return),sum(s.out_normal + s.out_withdrawal),sum(s.in_transfer), sum(s.out_transfer),sum(s.bed_stand), (select t.beginning_num from met_cas_inpatientdayreport t where t.dept_code=s.dept_code and t.date_stat=to_date('{0}','yyyy-mm-dd' )) as beginning_num, (select t.end_num from met_cas_inpatientdayreport t where t.dept_code=s.dept_code and t.date_stat=to_date('{1}','yyyy-mm-dd'))as end_num from met_cas_inpatientdayreport s where s.date_stat >= to_date('{0}','yyyy-mm-dd') and s.date_stat <= to_date('{1}','yyyy-mm-dd') group by s.dept_code order by s.dept_code"; execTotSql = string.Format(execTotSql, this.BeginDate.ToString("yyyy-MM-dd"), this.EndDate.ToString("yyyy-MM-dd")); DataSet dsTot = new DataSet(); if (dataManager.ExecQuery(execTotSql, ref dsTot) == -1) { MessageBox.Show("床位日报查询发生错误" + dataManager.Err); return(-1); } if (dsTot != null && dsTot.Tables[0].Rows.Count > 0) { foreach (DataRow dr in dsTot.Tables[0].Rows) { for (int i = 0; i < this.neuSpread1_Sheet1.Rows.Count; i++) { if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColDeptCode].Text == dr[0].ToString()) { //入院人数 this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColInNum].Text = NConvert.ToDecimal(dr[1]).ToString(); //出院人数 this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColTot].Text = NConvert.ToDecimal(dr[2]).ToString(); //转入人数 this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColTransferIn].Text = NConvert.ToDecimal(dr[3]).ToString(); //转出人数 this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColTransferOut].Text = NConvert.ToDecimal(dr[4]).ToString(); //实开床位 this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColStandardNum].Text = NConvert.ToDecimal(dr[5]).ToString(); //期初人数 this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColBeginNum].Text = NConvert.ToDecimal(dr[6]).ToString(); //期末人数 this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColEndNum].Text = NConvert.ToDecimal(dr[7]).ToString(); if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColInNum].Text == "0") { this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColInNum].Text = ""; } if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColTot].Text == "0") { this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColTot].Text = ""; } if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColTransferIn].Text == "0") { this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColTransferIn].Text = ""; } if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColTransferOut].Text == "0") { this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColTransferOut].Text = ""; } if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColStandardNum].Text == "0") { this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColStandardNum].Text = ""; } if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColBeginNum].Text == "0") { this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColBeginNum].Text = ""; } if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColEndNum].Text == "0") { this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColEndNum].Text = ""; } //if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColOutStat].Text == "0") // this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColOutStat].Text = ""; //if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColOutTot].Text == "0") // this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColOutTot].Text = ""; } } } } #endregion #region 出院总床日数 string execOutStatSql = @"select t.dept_code,sum(trunc(decode(s.IN_STATE,'N',s.oper_date,s.out_date)) - trunc(s.in_date)) from met_cas_dayreport_detail t,fin_ipr_inmaininfo s where t.stat_date >= to_date('{0}','yyyy-mm-dd') and t.stat_date <= to_date('{1}','yyyy-mm-dd') and t.valid_state = '0' and t.stat_type in ('O','OF') and t.inpatient_no = s.inpatient_no group by t.dept_code"; execOutStatSql = string.Format(execOutStatSql, this.BeginDate.ToString("yyyy-MM-dd"), this.EndDate.ToString("yyyy-MM-dd")); DataSet dsOutStat = new DataSet(); if (dataManager.ExecQuery(execOutStatSql, ref dsOutStat) == -1) { MessageBox.Show("床位日报查询发生错误" + dataManager.Err); return(-1); } if (dsOutStat != null && dsOutStat.Tables[0].Rows.Count > 0) { foreach (DataRow dr in dsOutStat.Tables[0].Rows) { for (int i = 0; i < this.neuSpread1_Sheet1.Rows.Count; i++) { if (this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColDeptCode].Text == dr[0].ToString()) { this.neuSpread1_Sheet1.Cells[i, (int)ColumnEnum.ColOutStat].Text = NConvert.ToDecimal(dr[1]).ToString(); } } } } #endregion #region 合计统计 this.SetSum((int)ColumnEnum.ColDept, (int)ColumnEnum.ColStandardNum, (int)ColumnEnum.ColBeginNum, (int)ColumnEnum.ColInNum, (int)ColumnEnum.ColTransferIn, (int)ColumnEnum.ColTransferOut, (int)ColumnEnum.ColTot, (int)ColumnEnum.ColOutTot, (int)ColumnEnum.ColOut1, (int)ColumnEnum.ColOut2, (int)ColumnEnum.ColOut3, (int)ColumnEnum.ColOut4, (int)ColumnEnum.ColEndNum, (int)ColumnEnum.ColOutStat); #endregion return(1); }