/// <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="commandText">命令字串。</param>
public GBaseDbCommandHelper(string commandText) : this()
{
if (StrFunc.StrIsNotEmpty(commandText))
{
this.SetCommandText(commandText);
}
}
/// <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="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="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="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>
/// 執行 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>
/// 加入過濾條件字串。
/// </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>
/// 取得排序欄位文字
/// </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="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>
/// 建立符合過濾條件的 Delete 語法的資料庫命令。
/// </summary>
/// <param name="tableName">資料表名稱。</param>
/// <param name="filterItems">資料過濾條件項目集合. </param>
public virtual DbCommand BuildDeleteCommand(string tableName, GFilterItemCollection filterItems)
{
IDbCommandHelper oHelper;
StringBuilder oBuffer;
GTableDefine oTableDefine;
GFilterBuilder oFilterBuilder;
GFilterInputArgs oFilterInputArgs;
string sFilter;
string sJoin;
// 無資料表定義或不啟用資料庫命令，則傳回 Null
oTableDefine = this.GeGTableDefine(tableName);
if (BaseFunc.IsNull(oTableDefine))
{
return(null);
}
// 建立資料庫命令輔助類別
oHelper = DatabaseFunc.CreateDbCommandHelper(this.DatabaseType);
oBuffer = new StringBuilder();
if (this.SessionInfo.DatabaseType == EDatabaseType.Oracle)
{
oBuffer.AppendLine(StrFunc.StrFormat("DELETE FROM {0} A ", oTableDefine.DbTableName));
}
else
{
oBuffer.AppendLine(StrFunc.StrFormat("DELETE FROM {0} FROM {0} A WITH ( ROWLOCK ) ", oTableDefine.DbTableName));
}
// 處理過濾條件
if (!BaseFunc.IsEmpty(filterItems))
{
oFilterInputArgs = new GFilterInputArgs(oHelper, this.ProgramDefine, tableName, null, filterItems, this.SessionInfo, true);
oFilterBuilder = new GFilterBuilder(oFilterInputArgs);
sFilter = oFilterBuilder.GetFilter(out sJoin);
if (StrFunc.StrIsNotEmpty(sFilter))
{
oBuffer.AppendLine("Where " + sFilter);
}
}
oHelper.SetCommandText(oBuffer.ToString());
return(oHelper.DbCommand);
}
/// <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>
/// 建立 Update 語法的資料庫命令。
/// </summary>
/// <param name="tableName">資料表名稱。</param>
public virtual DbCommand BuildUpdateCommand(string tableName)
{
IDbCommandHelper oHelper;
StringBuilder oBuffer;
GTableDefine oTableDefine;
GFieldDefine oKeyFieldDefine;
DbParameter oParameter;
string sSeparator, sFormat;
string[] oKeyFields;
// 無資料表定義或不啟用資料庫命令，則傳回 Null
oTableDefine = this.GeGTableDefine(tableName);
if (BaseFunc.IsNull(oTableDefine))
{
return(null);
}
// 建立資料庫命令輔助類別
oHelper = DatabaseFunc.CreateDbCommandHelper(this.DatabaseType);
oBuffer = new StringBuilder();
oBuffer.AppendFormat("UPDATE {0} WITH ( ROWLOCK ) ", oHelper.GetTableName(oTableDefine.DbTableName));
oBuffer.AppendLine();
// 處理 Update 的欄位名稱與值
sSeparator = string.Empty;
oBuffer.AppendLine("SET");
foreach (GFieldDefine fieldDefine in oTableDefine.Fields)
{
// FieldType 為 DataField 才需做更新，但排除 SYS_CompanyID、SYS_ID、SYS_RowID、SYS_INSDAT、SYS_USR
if (fieldDefine.FieldType == EFieldType.DataField && !StrFunc.SameTextOr(fieldDefine.FieldName, SysFields.CompanyID, SysFields.ID, SysFields.RowID, SysFields.InsertDate, SysFields.InsertUser))
{
// 加入命令參數
oParameter = oHelper.AddParameter(fieldDefine);
sFormat = "{0}{1}={2}";
oBuffer.AppendFormat(sFormat, sSeparator, oHelper.GetFieldName(fieldDefine.DbFieldName), oParameter.ParameterName);
oBuffer.AppendLine();
sSeparator = ", ";
}
}
// 更新主鍵優先順序為 SYS_RowID、SYS_CompanyID+SYS_ID，CompanyID+SYS_ID、PrimaryKey
if (oTableDefine.Fields.Contains(SysFields.RowID))
{
oKeyFields = new string[] { SysFields.RowID }
}
;
else if (oTableDefine.Fields.Contains(SysFields.CompanyID) && oTableDefine.Fields.Contains(SysFields.ID))
{
oKeyFields = new string[] { SysFields.CompanyID, SysFields.ID }
}
;
else if (oTableDefine.Fields.Contains(SysFields.CommonCompanyID) && oTableDefine.Fields.Contains(SysFields.ID))
{
oKeyFields = new string[] { SysFields.CommonCompanyID, SysFields.ID }
}
;
else if (StrFunc.StrIsNotEmpty(oTableDefine.PrimaryKey))
{
oKeyFields = StrFunc.StrSplit(oTableDefine.PrimaryKey, ",");
}
else
{
throw new GException("{0} 未包含 Sys_ID 或 Sys_RowID 欄位，無法建立 Update Command", tableName);
}
// 產生更新的 Where 條件
oBuffer.AppendLine("WHERE 1=1");
foreach (string sFieldName in oKeyFields)
{
oKeyFieldDefine = oTableDefine.Fields[sFieldName];
oParameter = oHelper.AddOriginalParameter(oKeyFieldDefine);
sFormat = "AND {0}={1}";
oBuffer.AppendLine(StrFunc.StrFormat(sFormat, oHelper.GetFieldName(oKeyFieldDefine.DbFieldName), oParameter.ParameterName));
}
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);
}
}
}