/// <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> /// 取得 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="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> /// 取得 SELECT 欄位。 /// </summary> /// <param name="helper">資料庫命令輔助類別。</param> /// <param name="fieldDefine">欄位定義。</param> public string GetSelectField(IDbCommandHelper helper, GFieldDefine fieldDefine) { var oMapping = this.Mappings[fieldDefine.FieldName]; if (BaseFunc.IsNull(oMapping)) { if (StrFunc.SameText(fieldDefine.FieldName, fieldDefine.DbFieldName)) { return(StrFunc.StrFormat("{0}.{1}", "A", helper.GetFieldName(fieldDefine.FieldName))); } else { return(StrFunc.StrFormat("{0}.{1} As {2}", "A", helper.GetFieldName(fieldDefine.DbFieldName), fieldDefine.FieldName)); } } else { return(StrFunc.StrFormat("{0}.{1} As {2}", oMapping.TableAlias, helper.GetFieldName(oMapping.SourceFieldName), fieldDefine.FieldName)); } }
/// <summary> /// 取得選取明細欄位。 /// </summary> /// <param name="helper">資料庫命令輔助類別。</param> /// <param name="fieldDefine">欄位定義。</param> public string GetDetailSelectField(IDbCommandHelper helper, GFieldDefine fieldDefine) { GLinkFieldMapping oMapping; string sTableAlias; string sFieldName; sTableAlias = "DA"; sFieldName = StrFunc.StrFormat("{0}.{1}", this.TableName, fieldDefine.FieldName); oMapping = this.Mappings[sFieldName]; if (BaseFunc.IsNull(oMapping)) { return(StrFunc.StrFormat("{0}.{1} As {2}", sTableAlias, helper.GetFieldName(fieldDefine.DbFieldName), helper.GetFieldName(sFieldName))); } else { return(StrFunc.StrFormat("{0}.{1} As {2}", oMapping.TableAlias, helper.GetFieldName(oMapping.SourceFieldName), helper.GetFieldName(sFieldName))); } }