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