/// <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="provider">資料表關連資訊提供者。</param> public void Execute(GTableJoinProvider provider) { provider.TableJoins.Clear(); provider.Mappings.Clear(); // 取得資料表定義 var oTableDefine = this.ProgramDefine.Tables[this.TableName]; // 取得使用到的欄位集合 var oUseFields = GetUseFields(oTableDefine); this.ActiveTableAlias = "A"; // 取得主要資料表使用欄位集合 var oUseTableFields = GetUseTableFields(oUseFields); // 建立主要資料表關連 BuildTableJoins(provider, oTableDefine, oUseTableFields); // 取得關連明細資料表使用欄位集合 oUseTableFields = GetUseDetailTableFields(oUseFields, out string detailTableName); if (oUseTableFields.Count > 0) { // 明細資料表別名初始值 this.ActiveTableAlias = "DA"; // 取得明細資料表定義 oTableDefine = this.ProgramDefine.Tables[detailTableName]; // 建立主檔與明細關連 BuildDetailTableJoin(provider, oTableDefine.DbTableName); // 建立資料表關連 BuildTableJoins(provider, oTableDefine, oUseTableFields, detailTableName); } }
/// <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> /// 取得 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="helper">資料庫命令輔助類別介面。</param> /// <param name="programDefine">程式定義。</param> /// <param name="tableName">資料表名稱。</param> /// <param name="tableJoinProvider">資料表關連資訊提供者。</param> /// <param name="filterItems">資料過濾條件項目集合。</param> /// <param name="sessionInfo">存取連線相關資料的輔助類別。</param> /// <param name="enableDbParameter">過濾條件是否啟用 DB 參數。</param> public GFilterInputArgs(IDbCommandHelper helper, GProgramDefine programDefine, string tableName, GTableJoinProvider tableJoinProvider, GFilterItemCollection filterItems, GSessionInfo sessionInfo, bool enableDbParameter) { DbCommandHelper = helper; ProgramDefine = programDefine; TableName = tableName; TableJoinProvider = tableJoinProvider; FilterItems = filterItems; SessionInfo = sessionInfo; EnableDbParameter = enableDbParameter; }
/// <summary> /// 建立主檔與明細關連。 /// </summary> /// <param name="provider">資料表關連資訊提供者。</param> /// <param name="detailTableName">明細資料表名稱。</param> private void BuildDetailTableJoin(GTableJoinProvider provider, string detailTableName) { // 建立資料表關連 var oTableJoin = new GTableJoin { Key = "Detail", LeftTableAlias = "A", LeftFieldName = SysFields.ID, RightTableName = detailTableName, RightTableAlias = "DA", RightFieldName = SysFields.MasterID }; provider.TableJoins.Add(oTableJoin); }
/// <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); } } } }
/// <summary> /// 建立 Select 語法的資料庫命令。 /// </summary> /// <param name="tableName">資料表名稱。</param> /// <param name="selectFields">要取得的欄位集合字串,以逗點分隔欄位名稱,空字串表示取得所有欄位。</param> /// <param name="filterItems">資料過濾條件項目集合。</param> /// <param name="userFilter">自訂過濾條件。</param> /// <param name="isOrderBy">執行排序</param> public virtual DbCommand BuildSelectCommand(string tableName, string selectFields, GFilterItemCollection filterItems, string userFilter, bool isOrderBy = true) { IDbCommandHelper oHelper; GTableJoinProvider oTableJoinProvider; StringBuilder oBuffer; StringBuilder oFilterBuffer; IFilterBuilder oFilterBuilder; GTableDefine oTableDefine; GSortFieldCollection oSortFields; GFilterInputArgs oFilterInputArgs; string sCommandText; string sFilter; string sSort; string sJoin; // 無資料表定義則離開 oTableDefine = this.GeGTableDefine(tableName); if (BaseFunc.IsNull(oTableDefine)) { return(null); } // 建立資料庫命令輔助類別 oHelper = DatabaseFunc.CreateDbCommandHelper(this.DatabaseType); // 取得排序欄位集合 oSortFields = GetSortFields(oTableDefine); // 建置資料表關連資訊 oTableJoinProvider = new GTableJoinProvider(); oTableJoinProvider.Execute(this.ProgramDefine, tableName, selectFields, filterItems, oSortFields); oBuffer = new StringBuilder(); // 產生 SELECT 欄位的語法 sCommandText = GetSelectFieldsCommandText(oHelper, oTableJoinProvider, tableName, selectFields); oBuffer.AppendLine(sCommandText); oFilterBuffer = new StringBuilder(); // 查詢過濾條件 oFilterInputArgs = new GFilterInputArgs(oHelper, this.ProgramDefine, tableName, oTableJoinProvider, filterItems, this.SessionInfo, true); oFilterBuilder = new GFilterBuilder(oFilterInputArgs); sFilter = oFilterBuilder.GetFilter(out sJoin); AddFilter(oFilterBuffer, sFilter); // 自訂過濾條件 AddFilter(oFilterBuffer, userFilter); // 產生 FROM 及 JOIN 語法 sCommandText = GeGTableJoinCommandText(oHelper, oTableJoinProvider, oTableDefine); oBuffer.Append(sCommandText); if (StrFunc.StrIsNotEmpty(sJoin)) { oBuffer.Append(sJoin); } // 加入過濾條件 if (oFilterBuffer.Length > 0) { oBuffer.AppendLine(" Where "); oBuffer.AppendLine(oFilterBuffer.ToString()); } // 加入排序語法 if (isOrderBy) { sSort = GetSortCommandText(oHelper, oTableJoinProvider, oTableDefine, oSortFields); if (StrFunc.StrIsNotEmpty(sSort)) { oBuffer.Append(sSort); } } oHelper.SetCommandText(oBuffer.ToString()); return(oHelper.DbCommand); }
/// <summary> /// 建立資料表關連。 /// </summary> /// <param name="key">關連鍵值。</param> /// <param name="provider">資料表關連資訊提供者。</param> /// <param name="fieldDefine">關連欄位定義。</param> /// <param name="returnFields">關連取回欄位集合。</param> /// <param name="leftTableAlias">左側資料表別名。</param> /// <param name="detailTableName">明細資料表名稱。</param> /// <param name="destFieldName">目的欄位名稱。</param> private void BuildTableJoin(string key, GTableJoinProvider provider, GFieldDefine fieldDefine, GStringHashSet returnFields, string leftTableAlias, string detailTableName, string destFieldName = "") { // 取得關連程式定義 var programDefine = CacheFunc.GetProgramDefine(fieldDefine.LinkProgID); if (BaseFunc.IsNull(programDefine)) { throw new GException("'{0}' ProgramDefine not found", fieldDefine.LinkProgID); } // 取得關連資料表定義 var tableDefine = programDefine.MasterTable; foreach (string fieldName in returnFields) { var linkReturnField = fieldDefine.LinkReturnFields.FindByDestField(fieldName); if (BaseFunc.IsNull(linkReturnField)) { throw new GException("'{0}' FieldDefine's LinkReturnFields not find DestField '{1}'", fieldDefine.FieldName, fieldName); } var sourceFieldDefine = tableDefine.Fields[linkReturnField.SourceField]; if (BaseFunc.IsNull(sourceFieldDefine)) { throw new GException("'{0}' TableDefine not find '{1}' FieldDefine", tableDefine.TableName, linkReturnField.SourceField); } if (sourceFieldDefine.FieldType == EFieldType.VirtualField) { throw new GException("'{0}' TableDefine's '{1}' FieldDefine not allow VirtualField", tableDefine.TableName, sourceFieldDefine.FieldName); } var tableJoin = provider.TableJoins[key]; if (BaseFunc.IsNull(tableJoin)) { // 建立資料表關連 tableJoin = new GTableJoin { Key = key, LeftTableAlias = leftTableAlias, LeftFieldName = tableDefine.GetLinkReturnActiveField(fieldDefine).DbFieldName, RightTableName = tableDefine.DbTableName, RightTableAlias = GetActiveTableAlias() }; var sKeyField = tableDefine.Fields.Contains(SysFields.ID) ? SysFields.ID : SysFields.RowID; tableJoin.RightFieldName = tableDefine.Fields[sKeyField].DbFieldName; if (tableDefine.Fields.Contains(SysFields.CompanyID)) { tableJoin.RightCompanyID = tableDefine.Fields[SysFields.CompanyID].DbFieldName; } else if (tableDefine.Fields.Contains(SysFields.CommonCompanyID)) { tableJoin.RightCompanyID = tableDefine.Fields[SysFields.CommonCompanyID].DbFieldName; } provider.TableJoins.Add(tableJoin); } // 若來源欄位的欄位類型是 LinkField,則需往上階找關連來源 if (sourceFieldDefine.FieldType == EFieldType.LinkField) { var linkFieldDefine = tableDefine.GetLinkReturnActiveField(sourceFieldDefine); var sKey = key + "." + linkFieldDefine.LinkProgID; var returnFieldSet = new GStringHashSet { sourceFieldDefine.DbFieldName }; BuildTableJoin(sKey, provider, linkFieldDefine, returnFieldSet, tableJoin.RightTableAlias, detailTableName, fieldName); } else { // 記錄關連欄位對應 var linkFieldMapping = new GLinkFieldMapping(); linkFieldMapping.FieldName = (StrFunc.StrIsNotEmpty(destFieldName)) ? destFieldName : fieldName; if (StrFunc.StrIsNotEmpty(detailTableName)) { linkFieldMapping.FieldName = StrFunc.StrFormat("{0}.{1}", detailTableName, linkFieldMapping.FieldName); } linkFieldMapping.TableAlias = tableJoin.RightTableAlias; linkFieldMapping.SourceFieldName = sourceFieldDefine.DbFieldName; provider.Mappings.Add(linkFieldMapping); } } }