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