/// <summary> /// 處理時間條件(比較運算子為 Equal)。 /// </summary> /// <param name="fieldDefine">欄位定義。</param> /// <param name="filterItem">資料過濾條件項目。</param> /// <param name="isDetail">是否為明細欄位。</param> private string ProcessDateTime(GFieldDefine fieldDefine, GFilterItem filterItem, bool isDetail) { string sFieldName; DateTime oDateValue; DateTime oMinValue; DateTime oMaxValue; string sMinParameterName; string sMaxParameterName; sFieldName = GetFieldName(fieldDefine, isDetail); if (StrFunc.StrIsEmpty(sFieldName)) { return(string.Empty); } // 將過濾值轉型為日期值 oDateValue = BaseFunc.CDateTime(filterItem.FilterValue).Date; // 無法正確轉型為日期值時,過濾條件傳回空字串 if (BaseFunc.IsEmpty(oDateValue)) { return(string.Empty); } oMinValue = oDateValue; sMinParameterName = GetDbParameter(fieldDefine, filterItem.ComparisonOperator, oMinValue); oMaxValue = oDateValue.AddDays(1); sMaxParameterName = GetDbParameter(fieldDefine, filterItem.ComparisonOperator, oMaxValue); // 大於等於今天 And 小於明天 return(StrFunc.StrFormat("({0} >= {1} And {0} < {2})", sFieldName, sMinParameterName, sMaxParameterName)); }
/// <summary> /// 物件描述文字。 /// </summary> public override string ToString() { string sValue; sValue = StrFunc.StrFormat("Left Join {2} {3} On {0}.{1}={3}.{4}", this.LeftTableAlias, this.LeftFieldName, this.RightTableName, this.RightTableAlias, this.RightFieldName); return(sValue); }
/// <summary> /// 處理多選條件。 /// </summary> /// <param name="fieldDefine">欄位定義。</param> /// <param name="filterItem">資料過濾條件項目。</param> /// <param name="isDetail">是否為明細欄位。</param> /// <param name="isIn">是否為In(反之為Not In)</param> private string ProcessIn(GFieldDefine fieldDefine, GFilterItem filterItem, bool isDetail, bool isIn = true) { string sFieldName; string sParameterName; string[] oValues; string sValue; sFieldName = GetFieldName(fieldDefine, isDetail); if (StrFunc.StrIsEmpty(sFieldName)) { return(string.Empty); } oValues = StrFunc.StrSplit(filterItem.FilterValue, ","); sValue = string.Empty; foreach (string value in oValues) { sParameterName = GetDbParameter(fieldDefine, filterItem.ComparisonOperator, value); if (StrFunc.StrIsNotEmpty(sValue)) { sValue += ","; } sValue += sParameterName; } if (isIn) { return(StrFunc.StrFormat("{0} In ({1})", sFieldName, sValue)); } else { return(StrFunc.StrFormat("{0} Not In ({1})", sFieldName, sValue)); } }
/// <summary> /// 取得群組過濾條件。 /// </summary> /// <param name="sourceFilterItems">過濾條件項目集合。</param> public string GetGroupFileter(GFilterItemCollection sourceFilterItems) { StringBuilder oBuilder; GDictionary <GFilterItemCollection> oList; string sFilter; oBuilder = new StringBuilder(); //取得群組過濾條件集合 oList = GetGroupFilterItems(sourceFilterItems); foreach (GFilterItemCollection filterItems in oList.Values) { sFilter = GetFilter(filterItems); if (StrFunc.StrIsNotEmpty(sFilter)) { if (oBuilder.Length > 0) { oBuilder.AppendFormat(" {0} ", filterItems[0].CombineOperator); } oBuilder.Append(sFilter); } } return(oBuilder.ToString()); }
/// <summary> /// 取得過濾條件。 /// </summary> /// <param name="filterItems">過濾條件項目集合。</param> private string GetFilter(GFilterItemCollection filterItems) { StringBuilder oBuilder; string sFilter; oBuilder = new StringBuilder(); foreach (GFilterItem item in filterItems) { sFilter = ProcessFilterItem(item); if (StrFunc.StrIsNotEmpty(sFilter)) { if (oBuilder.Length > 0) { oBuilder.AppendFormat(" {0} ", item.CombineOperator); } oBuilder.AppendLine(sFilter); } } if (oBuilder.Length == 0) { return(string.Empty); } else { return(StrFunc.StrFormat("({0})", oBuilder.ToString())); } }
/// <summary> /// 取得排序語法。 /// </summary> /// <param name="helper">資料庫命令輔助類別。</param> /// <param name="provider">資料表關連資訊提供者。</param> /// <param name="tableDefine">資料表定義。</param> /// <param name="sortFields">排序欄位集合。</param> private string GetSortCommandText(IDbCommandHelper helper, GTableJoinProvider provider, GTableDefine tableDefine, GSortFieldCollection sortFields) { StringBuilder oBuilder; GFieldDefine oFieldDefine; string sDbFieldName; string sSortDirection; // 無排序欄位則傳回空字串 if (BaseFunc.IsEmpty(sortFields) || !sortFields.Any()) { return(string.Empty); } oBuilder = new StringBuilder(); foreach (GSortField sortField in sortFields) { // 取得包含資料表別名的欄位名稱 oFieldDefine = tableDefine.Fields[sortField.FieldName]; sDbFieldName = provider.GetDbFieldName(helper, oFieldDefine); // 排序方式 sSortDirection = sortField.SortDirection == ESortDirection.Ascending ? "ASC" : "DESC"; if (oBuilder.Length > 0) { oBuilder.Append(", "); } oBuilder.Append(StrFunc.StrFormat("{0} {1}", sDbFieldName, sSortDirection)); } return(" Order By " + oBuilder.ToString()); }
/// <summary> /// 取得資料表關連語法。 /// </summary> /// <param name="helper">資料庫命令輔助類別。</param> /// <param name="provider">資料表關連資訊提供者。</param> /// <param name="tableDefine">資料表定義。</param> private string GeGTableJoinCommandText(IDbCommandHelper helper, GTableJoinProvider provider, GTableDefine tableDefine) { StringBuilder oBuffer; oBuffer = new StringBuilder(); oBuffer.AppendFormat("From {0} {1} ", helper.GetTableName(tableDefine.DbTableName), "A"); oBuffer.AppendLine(); foreach (GTableJoin item in provider.TableJoins) { oBuffer.AppendFormat("Left Join {2} {3} On {0}.{1}={3}.{4} ", item.LeftTableAlias, helper.GetFieldName(item.LeftFieldName), helper.GetTableName(item.RightTableName), item.RightTableAlias, helper.GetFieldName(item.RightFieldName)); // 過濾公司編號 if (StrFunc.StrIsNotEmpty(item.RightCompanyID)) { oBuffer.AppendFormat("And {0}.{1}='{2}' ", item.RightTableAlias, helper.GetFieldName(item.RightCompanyID), this.SessionInfo.CompanyID); } oBuffer.AppendLine(); } return(oBuffer.ToString()); }
/// <summary> /// 取得關連明細資料表使用欄位集合。 /// </summary> /// <param name="useFields">取得使用的欄位集合。</param> /// <param name="detailTableName">傳回明細資料表名稱。</param> private GStringHashSet GetUseDetailTableFields(GStringHashSet useFields, out string detailTableName) { GStringHashSet oUseFields; string sDetailTableName; string sTableName; string sFieldName; sDetailTableName = string.Empty; oUseFields = new GStringHashSet(); foreach (string fieldName in useFields) { if (StrFunc.StrContains(fieldName, ".")) { // 折解資料表及欄位名稱 StrFunc.StrSplitFieldName(fieldName, out sTableName, out sFieldName); // 主檔不能同時 Join 多個明細資料表 if (StrFunc.StrIsNotEmpty(sDetailTableName) && !StrFunc.SameText(sDetailTableName, sTableName)) { throw new GException("Master Table can't Join multi Detail Table"); } sDetailTableName = sTableName; oUseFields.Add(sFieldName); } } detailTableName = sDetailTableName; return(oUseFields); }
/// <summary> /// 建立 Delete 語法的資料庫命令。 /// </summary> /// <param name="tableName">資料表名稱。</param> public virtual DbCommand BuildDeleteCommand(string tableName) { IDbCommandHelper oHelper; StringBuilder oBuffer; GTableDefine oTableDefine; GFieldDefine oKeyFieldDefine; string sKeyField, sParameterSymbol; string[] oKeyFields; // 無資料表定義或不啟用資料庫命令,則傳回 Null oTableDefine = this.GeGTableDefine(tableName); if (BaseFunc.IsNull(oTableDefine)) { return(null); } // 建立資料庫命令輔助類別 oHelper = DatabaseFunc.CreateDbCommandHelper(this.DatabaseType); sParameterSymbol = oHelper.ParameterSymbol; oBuffer = new StringBuilder(); oBuffer.AppendFormat("DELETE FROM {0} WITH ( ROWLOCK ) ", oTableDefine.DbTableName); oBuffer.AppendLine("WHERE 1=1"); if (StrFunc.StrIsEmpty(oTableDefine.PrimaryKey)) { // 主檔以 Sys_ID 為更新主鍵,明細檔以 Sys_RowID 為更新主鍵,其他則以 PrimaryKey 為更新主鍵 if (oTableDefine.Fields.Contains(SysFields.ID)) { sKeyField = SysFields.ID; } else if (oTableDefine.Fields.Contains(SysFields.RowID)) { sKeyField = SysFields.RowID; } else { throw new GException("{0} TableDefine 未包含 Sys_ID 或 Sys_RowID 欄位", tableName); } oKeyFieldDefine = oTableDefine.Fields[sKeyField]; oBuffer.AppendLine(StrFunc.StrFormat("AND {1}={0}{2}", sParameterSymbol, oKeyFieldDefine.DbFieldName, oKeyFieldDefine.FieldName)); oHelper.AddParameter(oKeyFieldDefine); } else { // 以自訂的 PrimaryKey 為準 oKeyFields = StrFunc.StrSplit(oTableDefine.PrimaryKey, ","); foreach (string sFieldName in oKeyFields) { oKeyFieldDefine = oTableDefine.Fields[sFieldName]; oBuffer.AppendLine(StrFunc.StrFormat("AND {1}={0}{2}", sParameterSymbol, oKeyFieldDefine.DbFieldName, oKeyFieldDefine.FieldName)); oHelper.AddParameter(oKeyFieldDefine); } } oHelper.SetCommandText(oBuffer.ToString()); return(oHelper.DbCommand);; }
/// <summary> /// 建構函式。 /// </summary> /// <param name="commandText">命令字串。</param> public GBaseDbCommandHelper(string commandText) : this() { if (StrFunc.StrIsNotEmpty(commandText)) { this.SetCommandText(commandText); } }
/// <summary> /// 取得使用到的欄位集合。 /// </summary> /// <param name="tableDefine">資料表定義。</param> private GStringHashSet GetUseFields(GTableDefine tableDefine) { // 取得資料表定義 var useFieldSet = new GStringHashSet(); if (StrFunc.StrIsEmpty(this.SelectFields)) { // 包含所有欄位 foreach (GFieldDefine fieldDefine in tableDefine.Fields) { useFieldSet.Add(fieldDefine.FieldName); } } else { // 加入取回欄位 useFieldSet.Add(this.SelectFields, ","); // 加入篩選欄位 foreach (GFilterItem filterItem in this.FilterItems) { useFieldSet.Add(filterItem.FieldName); } // 加入排序欄位 foreach (GSortField sortField in this.SortFields) { useFieldSet.Add(sortField.FieldName); } } return(useFieldSet); }
/// <summary> /// 取得作用資料列。 /// </summary> /// <param name="dataSet">資料集。</param> /// <param name="tableName">資料表。</param> /// <param name="fieldName">欄位名稱,若不存在指定欄位,會回傳 null。</param> /// <param name="rowIndex">資料列索引。</param> private DataRow GetDataRow(DataSet dataSet, string tableName, string fieldName, int rowIndex) { DataTable oTable; // 資料集為空,則回傳 null if (BaseFunc.IsEmpty(dataSet)) { return(null); } // 資料表不存在,則回傳 null if (!dataSet.Tables.Contains(tableName)) { return(null); } // 欄位不存在,則回傳 null oTable = dataSet.Tables[tableName]; if (!DataFunc.HasField(oTable, fieldName)) { return(null); } // 主檔的資料列索引一徫為 0 if (StrFunc.SameText(dataSet.DataSetName, tableName)) { rowIndex = 0; } // 回傳作用資料列 if (0 <= rowIndex && rowIndex <= oTable.Rows.Count - 1) { return(oTable.Rows[rowIndex]); } else { return(null); } }
/// <summary> /// 檢查過濾條件的 SYS_ID 欄位的資料型別 /// </summary> /// <param name="filterItems">過濾條件集合。</param> /// <remarks> /// 如果過濾條件的 SYS_ID 欄位其查詢值是 * /// 需要判斷 SYS_ID 的欄位資料型別,再依照型別更改為正確的值。 /// </remarks> private void CheckFilterItemsForDbType(GFilterItemCollection filterItems) { if (!this.ProgramDefine.MasterTable.Fields.Contains(SysFields.ID)) { return; } var oFieldDefine = this.ProgramDefine.MasterTable.Fields[SysFields.ID]; foreach (GFilterItem filterItem in filterItems) { if (StrFunc.SameText(filterItem.FieldName, SysFields.ID) && StrFunc.SameText(filterItem.FilterValue, "*")) { // 資料型別是 Interger 時把查詢值換成 -1 if (oFieldDefine.DbType == EFieldDbType.Integer) { filterItem.FilterValue = "-1"; } // 資料型別是 DateTime 時把查詢值換成最小時間 0001/01/01 else if (oFieldDefine.DbType == EFieldDbType.DateTime) { filterItem.FilterValue = BaseFunc.CDateTime("0001/1/1").ToString("yyyy/MM/dd"); } } } }
/// <summary> /// 取得過濾條件。 /// </summary> /// <param name="join">傳出過濾條件需要額外加入的 JOIN 語法。</param> public string GetFilter(out string join) { GFilterItemCollection oFilterItems; GFilterItemsParser oParser; StringBuilder oBuilder; GDictionary <GFilterItemCollection> oList; string sFilter; // 解析過濾條件中的系統變數 oParser = new GFilterItemsParser(this.SessionInfo, this.Args.FilterItems); oFilterItems = oParser.ParseVariable(); join = string.Empty; oBuilder = new StringBuilder(); //取得群組過濾條件集合 oList = GetGroupFilterItems(oFilterItems); foreach (GFilterItemCollection filterItems in oList.Values) { sFilter = GetFilter(filterItems); if (StrFunc.StrIsNotEmpty(sFilter)) { if (oBuilder.Length > 0) { oBuilder.AppendFormat(" {0} ", filterItems[0].CombineOperator); } oBuilder.Append(sFilter); } } return(oBuilder.ToString()); }
/// <summary> /// 解析變數。 /// </summary> /// <param name="value">解析值。</param> /// <remarks> /// 只對單一欄位解析,如:[@@Session]、[@FieldName]、[@Table.FieldName] 與常數。 /// </remarks> public void Parse(string value) { Regex oRegex; MatchCollection oMatches; GroupCollection oGroups; string sPattern; string sWord; string[] oArray; sPattern = @"\[(?<word>@+[\w]*.?[\w]*)\]"; oRegex = new Regex(sPattern, RegexOptions.IgnoreCase); oMatches = oRegex.Matches(value); foreach (Match m in oMatches) { oGroups = m.Groups; sWord = oGroups["word"].Value; if (sWord.Contains("@@")) { // 系統變數 _Value = StrFunc.StrSubstring(sWord, 2); _VariableType = EVariableType.Variable; } else if (sWord.Contains("@")) { // 欄位變數 _Value = StrFunc.StrSubstring(sWord, 1); if (sWord.Contains(".")) { // 資料欄位,格式為 [@Table.FieldName],但其中 [@DB.NULL] 或 [@DB.NOTNULL] 為常數 oArray = StrFunc.StrSplit(this.Value, "."); if (oArray.Length > 0) { if (StrFunc.SameText(StrFunc.StrUpper(oArray[0]), "DB")) { _VariableType = EVariableType.Constant; // DB.NULL 或 DB.NOTNULL 為常數 } else { _VariableType = EVariableType.TableField; } } } else { // 欄位變數,格式為 [@FieldName] _VariableType = EVariableType.Field; // 格式為 [@Table.FieldName] } } return; } _Value = value; _VariableType = EVariableType.Constant; }
/// <summary> /// 系統變數字串傳為系統變數列舉。 /// </summary> /// <param name="variable">系統變數字串。</param> private ESysVariable GetSysVariable(string variable) { string sVariable; // 系統變數字串格式為 [@@系統變數],去除左右標記符號取得系統變數 sVariable = StrFunc.StrLeftCut(variable, "[@@"); sVariable = StrFunc.StrRightCut(sVariable, "]"); return(BaseFunc.CEnum <ESysVariable>(sVariable)); }
/// <summary> /// 執行 Select 方法的實作 /// </summary> /// <param name="inputArgs"></param> /// <param name="outputResult"></param> protected virtual void DoSelect(GSelectInputArgs inputArgs, GSelectOutputResult outputResult) { var sTableName = inputArgs.TableName; var oTableDefine = this.ProgramDefine.Tables[sTableName]; if (BaseFunc.IsNull(oTableDefine)) { return; } // 因為 Find 與 Move 都會統一進入 Select // 所以放在這邊統一做判斷過濾條件是否有包含 SYS_ID 欄位且其值是 * // 如滿足條件則需要判斷資料型別,並更改其查詢值 CheckFilterItemsForDbType(inputArgs.FilterItems); // 取得 Select 的欄位集合字串 var sSelectFields = GetSelectFields(oTableDefine, inputArgs.SelectFields); var oDbCommandBuilder = this.CreateDbCommandBuilder(); var oDbCommand = oDbCommandBuilder.BuildSelectCommand(sTableName, sSelectFields, inputArgs.FilterItems, inputArgs.UserFilter, inputArgs.IsOrderBy); if (BaseFunc.IsNull(oDbCommand)) { return; } var oDataTable = base.DbAccess.ExecuteDataTable(this.DatabaseID, oDbCommand); oDataTable.TableName = sTableName; if (inputArgs.IsBuildVirtualField) { // 加入虛擬欄位 var oVFBuilder = new GVirtualFieldBuilder(oTableDefine, oDataTable); oVFBuilder.Execute(); } else { // 判斷 Select 的欄位是否有虛擬欄位 var oVFBuilder = new GVirtualFieldBuilder(oTableDefine, oDataTable); oVFBuilder.Execute(sSelectFields); } // 設定資料表中每個欄位的預設值 BusinessFunc.SetDataColumnDefaultValue(oTableDefine, oDataTable); // 設定DataTable主索引鍵 if (StrFunc.StrIsNotEmpty(oTableDefine.TablePrimaryKey)) { DataFunc.DataTableSetPrimaryKey(oDataTable, oTableDefine.TablePrimaryKey); } // 資料表同意變更,讓資料表無異動狀態 oDataTable.AcceptChanges(); outputResult.Table = new GEntityTable(oDataTable); }
/// <summary> /// 取得 SELECT 欄位的語法。 /// </summary> /// <param name="helper">資料庫命令輔助類別。</param> /// <param name="provider">資料表關連資訊提供者。</param> /// <param name="tableName">資料表名稱。</param> /// <param name="selectFields">選取得欄位集合字串。</param> /// <param name="selectCount">取得筆數。</param> /// <param name="selectSection">查詢區間</param> private string GetSelectFieldsCommandText(IDbCommandHelper helper, GTableJoinProvider provider, string tableName, string selectFields) { GTableDefine oTableDefine; GFieldDefine oFieldDefine; StringBuilder oBuffer; string[] oSelectFields; string sCommandText; oTableDefine = this.ProgramDefine.Tables[tableName]; oBuffer = new StringBuilder(); if (StrFunc.StrIsEmpty(selectFields)) { foreach (GFieldDefine fieldDefine in oTableDefine.Fields) { if (fieldDefine.FieldType != EFieldType.VirtualField) { if (oBuffer.Length > 0) { oBuffer.AppendLine(","); } sCommandText = provider.GetSelectField(helper, fieldDefine); oBuffer.Append(sCommandText); } } } else { oSelectFields = StrFunc.StrSplit(selectFields, ","); foreach (string fieldName in oSelectFields) { oFieldDefine = this.ProgramDefine.FindField(fieldName); if (oFieldDefine.FieldType != EFieldType.VirtualField) { if (oBuffer.Length > 0) { oBuffer.AppendLine(","); } if (StrFunc.StrContains(fieldName, ".")) { sCommandText = provider.GetDetailSelectField(helper, oFieldDefine); } else { sCommandText = provider.GetSelectField(helper, oFieldDefine); } oBuffer.Append(sCommandText); } } } return(StrFunc.StrFormat("Select \n") + oBuffer.ToString()); }
/// <summary> /// 取得含參數符號的參數名稱。 /// </summary> /// <param name="name">參數名稱。</param> public string GetParameterName(string name) { if (StrFunc.StrLeftWith(name, this.ParameterSymbol)) { return(name); } else { return(this.ParameterSymbol + name); } }
/// <summary> /// 依欄位名稱尋找成員。 /// </summary> /// <param name="fieldName">欄位名稱。</param> public GFilterItem FindByFieldName(string fieldName) { foreach (GFilterItem item in this) { if (StrFunc.SameText(item.FieldName, fieldName)) { return(item); } } return(null); }
/// <summary> /// 加入過濾條件字串。 /// </summary> /// <param name="buffer">過濾條件暫存區。</param> /// <param name="filter">要加入的過濾條件。</param> private void AddFilter(StringBuilder buffer, string filter) { if (StrFunc.StrIsNotEmpty(filter)) { if (buffer.Length > 0) { buffer.Append(" And "); } buffer.Append("(" + filter + ")"); } }
/// <summary> /// 取得 Select 的欄位集合字串。 /// </summary> /// <param name="tableDefine">資料表。</param> /// <param name="selectFields"></param> private string GetSelectFields(GTableDefine tableDefine, string selectFields) { if (StrFunc.StrIsEmpty(selectFields)) { return(string.Empty); } var oSelectFields = StrFunc.StrSplit(selectFields, ","); var oFields = new GStringHashSet(selectFields, ","); return(oFields.ToString(",")); }
/// <summary> /// 移除無查詢值的條件。 /// </summary> /// <returns>把過濾條件值為空的條件剔除。</returns> public GFilterItemCollection RemoveEmpty() { for (int N1 = this.Count - 1; N1 >= 0; N1--) { var filterItem = this[N1]; if (StrFunc.StrIsEmpty(filterItem.FilterValue)) { this.Remove(filterItem); } } return(this); }
/// <summary> /// 取得主要資料表使用欄位集合。 /// </summary> /// <param name="useFields">使用欄位集合。</param> private GStringHashSet GetUseTableFields(GStringHashSet useFields) { var useFieldSet = new GStringHashSet(); foreach (string fieldName in useFields) { if (!StrFunc.StrContains(fieldName, ".")) { useFieldSet.Add(fieldName); } } return(useFieldSet); }
/// <summary> /// 取得排序欄位文字 /// </summary> /// <returns></returns> public string GetSortedText() { var result = ""; foreach (GSortField f in this) { result += (StrFunc.StrIsNotEmpty(result) ? ", " : "") + f.FieldName + (f.SortDirection == ESortDirection.Ascending ? "" : " Desc"); } return(result); }
/// <summary> /// 取得下一個資料表別名。 /// </summary> /// <param name="tableAlias">目前資料表別名。</param> public static string GetNextTableAlias(string tableAlias) { var baseValues = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; var newTableAlias = BaseFunc.GetNextID(tableAlias, baseValues); // 若資料表別名為關鍵字,則重取資料表別名 if (StrFunc.SameTextOr(newTableAlias, "AS", "BY")) { newTableAlias = BaseFunc.GetNextID(newTableAlias, baseValues); } return(newTableAlias); }
/// <summary> /// 處理區間條件。 /// </summary> /// <param name="fieldDefine">欄位定義。</param> /// <param name="filterItem">資料過濾條件項目。</param> /// <param name="isDetail">是否為明細欄位。</param> private string ProcessBetween(GFieldDefine fieldDefine, GFilterItem filterItem, bool isDetail) { StringBuilder oBuilder; string sFieldName; string[] oFilterValues; object oMinValue; object oMaxValue; DateTime oStartDate; DateTime oEndDate; string sMinParameterName; string sMaxParameterName; sFieldName = GetFieldName(fieldDefine, isDetail); if (StrFunc.StrIsEmpty(sFieldName)) { return(string.Empty); } oBuilder = new StringBuilder(); oFilterValues = StrFunc.StrSplit(filterItem.FilterValue, ","); if (oFilterValues.Length != 2) { return(string.Empty); } if (fieldDefine.DbType == EFieldDbType.DateTime) { // 起始日 oStartDate = BaseFunc.CDateTime(oFilterValues[0]); sMinParameterName = GetDbParameter(fieldDefine, filterItem.ComparisonOperator, oStartDate); // 終止日+1 oEndDate = BaseFunc.CDateTime(oFilterValues[1]); sMaxParameterName = GetDbParameter(fieldDefine, filterItem.ComparisonOperator, DateFunc.AddDays(oEndDate, 1)); // 傳回過濾條件,日期區間需大於等於 [起始日] And 小於 [終止日+1] return(StrFunc.StrFormat("({0} >= {1} And {0} < {2})", sFieldName, sMinParameterName, sMaxParameterName)); } else { // 起始值 oMinValue = BaseFunc.CFieldValue(fieldDefine.DbType, oFilterValues[0]); sMinParameterName = GetDbParameter(fieldDefine, filterItem.ComparisonOperator, oMinValue); // 終止值 oMaxValue = BaseFunc.CFieldValue(fieldDefine.DbType, oFilterValues[1]); sMaxParameterName = GetDbParameter(fieldDefine, filterItem.ComparisonOperator, oMaxValue); // 傳回過濾條件 return(StrFunc.StrFormat("({0} >= {1} And {0} <= {2})", sFieldName, sMinParameterName, sMaxParameterName)); } }
/// <summary> /// 取得包含資料表別名的欄位名稱。 /// </summary> /// <param name="helper">資料庫命令輔助類別。</param> /// <param name="fieldDefine">欄位定義。</param> public string GetDbFieldName(IDbCommandHelper helper, GFieldDefine fieldDefine) { GLinkFieldMapping oMapping; oMapping = this.Mappings[fieldDefine.FieldName]; if (BaseFunc.IsNull(oMapping)) { return(StrFunc.StrFormat("{0}.{1}", "A", helper.GetFieldName(fieldDefine.DbFieldName))); } else { return(StrFunc.StrFormat("{0}.{1}", oMapping.TableAlias, helper.GetFieldName(oMapping.SourceFieldName))); } }
/// <summary> /// 取得關連取回欄位集合。 /// </summary> /// <param name="tableDefine">資料表定義。</param> /// <param name="linkFieldName">關連來源欄位。</param> /// <param name="useFields">使用到的欄位集合。</param> private GStringHashSet GetReturnFields(GTableDefine tableDefine, string linkFieldName, GStringHashSet useFields) { GStringHashSet oReturnFields; GFieldDefine oFieldDefine; oReturnFields = new GStringHashSet(); foreach (string fieldName in useFields) { oFieldDefine = tableDefine.Fields[fieldName]; if (oFieldDefine.FieldType == EFieldType.LinkField && StrFunc.SameText(oFieldDefine.LinkFieldName, linkFieldName)) { oReturnFields.Add(fieldName); } } return(oReturnFields); }
/// <summary> /// 建立資料表關連。 /// </summary> /// <param name="provider">資料表關連資訊提供者。</param> /// <param name="tableDefine">資料表定義。</param> /// <param name="useFields">使用到的欄位集合。</param> /// <param name="detailTableName">明細資料表名稱。</param> private void BuildTableJoins(GTableJoinProvider provider, GTableDefine tableDefine, GStringHashSet useFields, string detailTableName = "") { foreach (GFieldDefine fieldDefine in tableDefine.Fields) { if (fieldDefine.FieldType == EFieldType.DataField && StrFunc.StrIsNotEmpty(fieldDefine.LinkProgID)) { var oReturnFields = GetReturnFields(tableDefine, fieldDefine.FieldName, useFields); if (oReturnFields.Count > 0) { var key = StrFunc.StrFormat("{0}.{1}.{2}", tableDefine.TableName, fieldDefine.FieldName, fieldDefine.LinkProgID); var leftTableAlias = StrFunc.StrIsEmpty(detailTableName) ? "A" : "DA"; BuildTableJoin(key, provider, fieldDefine, oReturnFields, leftTableAlias, detailTableName); } } } }