/// <summary> /// 床位校验 /// </summary> /// <returns>True 校验通过 False 校验失败</returns> internal static bool BedVerify() { if (LicenceBedQty != -1) { Neusoft.FrameWork.Management.DataBaseManger dataBase = new Neusoft.FrameWork.Management.DataBaseManger(); string bedNumSql = @"select count(*) from com_bedinfo"; decimal bedQty = -1; try { bedQty = Neusoft.FrameWork.Function.NConvert.ToDecimal(dataBase.ExecSqlReturnOne(bedNumSql)); } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } if (LicenceBedQty < bedQty) { MessageBox.Show("维护总床位数已超过系统授权限制,请与供应商联系 \n\n授权床位数:" + LicenceBedQty.ToString() + " 当前床位数:" + bedQty.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Stop); return(false); } } return(true); }
/// <summary> /// 获得 /// </summary> /// <param name="id"></param> /// <returns></returns> public virtual string GetSetting(string id) { string sql = "select xml from emr_setting where id='{0}'"; sql = string.Format(sql, id); Neusoft.FrameWork.Management.DataBaseManger manager = new Neusoft.FrameWork.Management.DataBaseManger(); this.SetDB(manager); return(manager.ExecSqlReturnOne(sql)); }
protected override int OnQuery(object sender, object neuObject) { if (this.dtBegin.Value > this.dtEnd.Value) { MessageBox.Show("查询起始时间不能大于查询截止时间", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return(-1); } string drugCode = this.ucDrugList1.CurrentSheetView.Cells[this.ucDrugList1.CurrentSheetView.ActiveRowIndex, 0].Text; if (this.hsDrug.ContainsKey(drugCode)) { this.currentDrugCode = drugCode; Neusoft.HISFC.Models.Pharmacy.Item info = this.hsDrug[drugCode] as Neusoft.HISFC.Models.Pharmacy.Item; this.lbTitle.Text = info.NameCollection.RegularName + "明细帐"; this.lbDrugDept.Text = "库存科室:" + this.DrugDept.Name; this.lbSpecs.Text = "规格:" + info.Specs; this.lbDosage.Text = "剂型:" + this.dosageHelper.GetName(info.DosageForm.ID.ToString()); this.lbPackUnit.Text = "包装单位:" + info.PackUnit; if (this.hsProduce.ContainsKey(info.Product.Producer.ID)) { this.lbCompany.Text = "生产厂家:" + this.hsProduce[info.Product.Producer.ID].ToString(); } } if (string.IsNullOrEmpty(this.DrugCode)) { MessageBox.Show("请选择需统计的药品"); return(-1); } DateTime queryBeginDate = System.DateTime.Now.AddYears(-100); DateTime queryEndDate = System.DateTime.Now.AddYears(100); #region 获取统计起始时间 Neusoft.FrameWork.Management.DataBaseManger dataManagement = new Neusoft.FrameWork.Management.DataBaseManger(); string execBeginSql = string.Format(this.getQueryBeginDate, this.DrugDept.ID, Neusoft.FrameWork.Function.NConvert.ToDateTime(this.dtBegin.Text).ToString()); string beginDateString = dataManagement.ExecSqlReturnOne(execBeginSql); queryBeginDate = Neusoft.FrameWork.Function.NConvert.ToDateTime(beginDateString); string execEndSql = string.Format(this.getQueryEndDate, this.DrugDept.ID, Neusoft.FrameWork.Function.NConvert.ToDateTime(this.dtEnd.Text).ToString()); string endDateString = dataManagement.ExecSqlReturnOne(execEndSql); queryEndDate = Neusoft.FrameWork.Function.NConvert.ToDateTime(endDateString); if (queryEndDate == DateTime.MinValue) { queryEndDate = dataManagement.GetDateTimeFromSysDateTime().AddDays(1); } #endregion this.lbStatDate.Text = "会计区间:" + queryBeginDate.ToString() + " - " + queryEndDate.ToString(); ArrayList alAllData = new ArrayList(); #region 数据检索 ArrayList alInData = this.QueryItemizedData(this.inItemizedBillSql, DataSource.In, queryBeginDate, queryEndDate); if (alInData == null) { return(-1); } alAllData.AddRange(alInData); ArrayList alOutData = this.QueryItemizedData(this.outItemizedBillSql, DataSource.Out, queryBeginDate, queryEndDate); if (alOutData == null) { return(-1); } alAllData.AddRange(alOutData); ArrayList alAdjustData = this.QueryItemizedData(this.adjuseItemizedBillSql, DataSource.Adjust, queryBeginDate, queryEndDate); if (alAdjustData == null) { return(-1); } alAllData.AddRange(alAdjustData); ArrayList alCheckData = this.QueryItemizedData(this.checkItemizedBillSql, DataSource.Check, queryBeginDate, queryEndDate); if (alCheckData == null) { return(-1); } alAllData.AddRange(alCheckData); ArrayList alMsCurrentData = this.QueryItemizedData(this.msCurrentItemizedBillSql, DataSource.MonthCurrent, queryBeginDate, queryEndDate); if (alMsCurrentData == null) { return(-1); } alAllData.AddRange(alMsCurrentData); ArrayList alMsLastData = this.QueryItemizedData(this.msLastItemizedBillSql, DataSource.MonthLast, queryBeginDate, queryEndDate); if (alMsLastData == null) { return(-1); } alAllData.AddRange(alMsLastData); #endregion CompareDate sortData = new CompareDate(); alAllData.Sort(sortData); return(this.ShowData(alAllData)); }
/// <summary> /// 获取SQL语句 /// </summary> /// <returns></returns> private Neusoft.FrameWork.Models.NeuObject getSQL() { //没有数据时返回 if (myPreDefine == null || this.neuFpEnter1_Sheet1.RowCount == 0) { return(null); } string sql = "SELECT {0} \nFROM " + neuFpEnter1_Sheet1.Cells[0, (int)ColSet.视图].Text + "\n"; //不显示重复的数据 if (this.ckDistinct.Checked) { sql = "SELECT DISTINCT {0} \nFROM " + neuFpEnter1_Sheet1.Cells[0, (int)ColSet.视图].Text + "\n"; } string groupFields = ""; //纪录分组的字段,逗号隔开 string fieldAndcustomFields = ""; //字段和字段别名, string sumCols = ""; //求和的列 string sortCols = ""; //排序的列 string filters = ""; //过滤的列 string customWheres = ""; //条件表达式 自定义的where条件 int colIndex = -1; //列索引,由于求和,排序 bool isNeedGroup = false; //是否需要分组 for (int rowIndex = 0; rowIndex < this.neuFpEnter1_Sheet1.RowCount; rowIndex++) { //自定义Where条件 string customWhere = neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.条件表达式].Text; if (!string.IsNullOrEmpty(customWhere)) { customWheres += "\nAND " + customWhere; } //跳过没有选中的字段 if (!Neusoft.FrameWork.Function.NConvert.ToBoolean(neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.中].Value)) { continue; } //列索引必须记录 用户选择一列,字段没选择时不计入 colIndex++; //字段别名 string customField = neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.字段别名].Text; //字段 string field = neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.视图].Text + "." + neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.字段名称].Text; //表达式 用表达式替换字段,但不替换字段别名 string f = neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.字段表达式].Text; if (!string.IsNullOrEmpty(f)) { field = f; } //分组求和\平均,求和的字段不在group by 中,其它必须group by if (Neusoft.FrameWork.Function.NConvert.ToBoolean(neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.平均数].Value)) { isNeedGroup = true; field = "ROUND(AVG(" + field + "), " + DecimalValue.ToString() + ")"; } else { if (!Neusoft.FrameWork.Function.NConvert.ToBoolean(neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.分组求和].Value)) { //自定义了分组字段,则用自定义分组字段 string customGroup = neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.分组字段].Text; if (string.IsNullOrEmpty(customGroup)) { groupFields += neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.视图].Text + "." + neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.字段名称].Text; } else { groupFields += customGroup; } groupFields += ",\n "; } else { isNeedGroup = true; field = "SUM(" + field + ")"; } } fieldAndcustomFields += (colIndex == 0 ? "" : " ") + field + " " + customField + ",\n"; //合计 if (Neusoft.FrameWork.Function.NConvert.ToBoolean(neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.添加合计].Value)) { sumCols += colIndex.ToString() + ","; } //排序 if (Neusoft.FrameWork.Function.NConvert.ToBoolean(neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.排序].Value)) { sortCols += colIndex.ToString() + ","; } //过滤 if (Neusoft.FrameWork.Function.NConvert.ToBoolean(neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.过滤].Value)) { if (string.IsNullOrEmpty(customField)) { filters += neuFpEnter1_Sheet1.Cells[rowIndex, (int)ColSet.字段名称].Text + ","; } else { filters += customField + ","; } } } //没有选择字段,则查询全部 if (string.IsNullOrEmpty(fieldAndcustomFields)) { for (int row = 0; row < this.neuFpEnter1_Sheet1.RowCount; row++) { this.neuFpEnter1_Sheet1.Cells[row, (int)ColSet.中].Value = true; } sql = "SELECT * FROM " + neuFpEnter1_Sheet1.Cells[0, (int)ColSet.视图].Text + "\n"; } else { sql = string.Format(sql, fieldAndcustomFields.TrimEnd(',', '\n')); } //分组 if (!string.IsNullOrEmpty(groupFields) && isNeedGroup) { groupFields = "\nGROUP BY " + groupFields; groupFields = groupFields.TrimEnd(',', '\n', ' '); } else { groupFields = ""; } //强制where条件 string sysWhere = this.myPreDefine.User01; if (!string.IsNullOrEmpty(sysWhere)) { if (sysWhere.IndexOf("Local.CustomQuery") != -1) { //保存后强制where被sql索引替换,重新获取 string view = "" + neuFpEnter1_Sheet1.Cells[0, (int)ColSet.视图].Text + ""; string getViewSql = "select sql_where from com_customquery_viewinfo where view_name = '{0}'"; getViewSql = string.Format(getViewSql, view.ToLower()); sysWhere = dbMgr.ExecSqlReturnOne(getViewSql); if (sysWhere == "-1") { getViewSql = string.Format(getViewSql, view); sysWhere = dbMgr.ExecSqlReturnOne(getViewSql); if (sysWhere == "-1") { this.richTextBox1.SuperText = "获取强制条件发生错误\n" + dbMgr.Err; return(null); } } } } if (string.IsNullOrEmpty(sysWhere)) { sysWhere = "\nWHERE 1=1 "; } //二级权限 if (!string.IsNullOrEmpty(this.myPreDefine.User02)) { // sysWhere = sysWhere.Replace("usercode", this.dbMgr.Operator.ID); } //sql检测 string errInfo = ""; string checkedSql = sql + sysWhere; checkedSql += customWheres + groupFields; if (!this.checkSQL(checkedSql, ref errInfo)) { this.richTextBox1.SuperText = errInfo; this.neuTabControl1.SelectedIndex = 2; return(null); } this.richTextBox1.SuperText = errInfo; sql += sysWhere + customWheres; //强制where sql += groupFields; //分组 Neusoft.FrameWork.Models.NeuObject o = new Neusoft.FrameWork.Models.NeuObject(); o.ID = this.getSQLID(); o.Memo = this.myPreDefine.Name; o.User03 = this.myPreDefine.Name; o.User01 = ""; o.User02 = "自定义查询"; o.Name = sql; if (string.IsNullOrEmpty(filters)) { this.myReport.FilerType = Neusoft.HISFC.Components.Common.Controls.ucPrivePowerReport.EnumFilterType.过滤; } else { this.myReport.FilerType = Neusoft.HISFC.Components.Common.Controls.ucPrivePowerReport.EnumFilterType.汇总过滤; } //查询结果报表属性赋值 this.myReport.SQL = o.Name; this.myReport.Filters = filters; this.myReport.SumColIndexs = sumCols; this.myReport.SortColIndexs = sortCols; this.myReport.RightAdditionTitle = ""; this.myReport.SQLIndexs = o.ID; this.myPreDefine.User03 = o.Name; return(o); }