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