/// <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>
/// 建立 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>
/// 處理時間條件(比較運算子為 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>
/// <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>
/// 取得 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>
/// <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>
/// 取得 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>
/// <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="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>
/// 解析並移除不需要的 [欄位變數] 過濾條件。
/// </summary>
/// <param name="programDefine">程式定義。</param>
public GFilterItemCollection ParseRemoveField(GProgramDefine programDefine)
{
GFilterItemCollection oFilterItems;
GFilterItem oFilterItem;
GFieldDefine oFieldDefine;
EVariableType oVariableType;
string sTableName;
string sFieldName;
oFilterItems = new GFilterItemCollection();
foreach (GFilterItem item in this.FilterItems)
{
// 產生過濾條件複本
oFilterItem = item.Clone();
// 解析變數值
this.VariableParser.Parse(item.FilterValue);
// 變數類型
oVariableType = this.VariableParser.VariableType;
if (oVariableType == EVariableType.Field || oVariableType == EVariableType.TableField)
{
// 拆解字串的資料表名稱及欄位名稱
StrFunc.StrSplitFieldName(this.VariableParser.Value, out sTableName, out sFieldName);
if (StrFunc.StrIsEmpty(sTableName))
{
sTableName = programDefine.ProgID;
}
// 只保留存在的 [欄位變數] 過濾條件
oFieldDefine = programDefine.FindField(sTableName, sFieldName);
if (BaseFunc.IsNotNull(oFieldDefine))
{
oFilterItems.Add(oFilterItem);
}
}
else
{
// 加入非 [欄位變數] 過濾條件
oFilterItems.Add(oFilterItem);
}
}
// 移除無查詢值的條件
oFilterItems.RemoveEmpty();
return(oFilterItems);
}
/// <summary>
/// 系統變數過濾條件轉換為一般過濾條件。
/// </summary>
/// <param name="filterItem">系統變數過濾條件。</param>
private GFilterItem ConvertItemForVariable(GFilterItem filterItem)
{
GFilterItem oFilterItem;
ESysVariable oVariable;
string sValue;
// 取得系統變數，若無法正確轉換系統變數，則傳回空字串
try
{
oVariable = GetSysVariable(filterItem.FilterValue);
}
catch
{
return(null);
}
// 取得系統變數值
sValue = GetSysVariableValue(this.SessionInfo, oVariable);
if (StrFunc.StrIsEmpty(sValue))
{
return(null);
}
switch (oVariable)
{
case ESysVariable.CompanyID:
case ESysVariable.DepartmentID:
case ESysVariable.EmployeeID:
case ESysVariable.UserID:
// 轉換為等於過濾條件
oFilterItem = filterItem.Clone();
oFilterItem.ComparisonOperator = EComparisonOperator.Equal;
oFilterItem.FilterValue = sValue;
return(oFilterItem);
default: // 日期相關變數
// 轉換為日期區間過濾條件
oFilterItem = filterItem.Clone();
oFilterItem.ComparisonOperator = EComparisonOperator.Between;
oFilterItem.FilterValue = sValue;
return(oFilterItem);
}
}
/// <summary>
/// 處理一般條件。
/// </summary>
/// <param name="fieldDefine">欄位定義。</param>
/// <param name="filterItem">資料過濾條件項目。</param>
/// <param name="isDetail">是否為明細欄位。</param>
private string ProcessItem(GFieldDefine fieldDefine, GFilterItem filterItem, bool isDetail)
{
string sFieldName;
string sParameterName;
string sComparisonOperator;
object oValue;
sFieldName = GetFieldName(fieldDefine, isDetail);
if (StrFunc.StrIsEmpty(sFieldName))
{
return(string.Empty);
}
if (StrFunc.SameText(filterItem.FilterValue, "DB.NULL"))
{
return(StrFunc.StrFormat("{0} Is Null", sFieldName));
}
else if (StrFunc.SameText(filterItem.FilterValue, "DB.NOTNULL"))
{
return(StrFunc.StrFormat("{0} Is Not Null", sFieldName));
}
else
{
if (fieldDefine.DbType == EFieldDbType.DateTime &&
(filterItem.ComparisonOperator == EComparisonOperator.Equal || filterItem.ComparisonOperator == EComparisonOperator.Like))
{
// DateTime 型別設 Equal 或 Like 皆視為等於，要以區間條件處理
return(ProcessDateTime(fieldDefine, filterItem, isDetail));
}
else
{
sComparisonOperator = filterItem.GetComparisonText();
oValue = BaseFunc.CFieldValue(fieldDefine.DbType, filterItem.FilterValue, DBNull.Value);
if (BaseFunc.IsDBNull(oValue))
{
return(string.Empty);
}
sParameterName = GetDbParameter(fieldDefine, filterItem.ComparisonOperator, oValue);
return(StrFunc.StrFormat("{0} {1} {2}", sFieldName, sComparisonOperator, sParameterName));
}
}
}
/// <summary>
/// 解析過濾條件中的欄位變數。
/// </summary>
/// <param name="dataSet">資料集。</param>
/// <param name="tableName">作用資料表名稱。</param>
/// <param name="rowIndex">作用資料列索引。</param>
public GFilterItemCollection ParseField(DataSet dataSet, string tableName, int rowIndex)
{
var oFilterItems = new GFilterItemCollection();
foreach (GFilterItem item in this.FilterItems)
{
// 產生過濾條件複本
var oFilterItem = item.Clone();
// 解析變數值
this.VariableParser.Parse(item.FilterValue);
// 變數類型
var oVariableType = this.VariableParser.VariableType;
// 取得欄位變數的值
if (oVariableType == EVariableType.Field || oVariableType == EVariableType.TableField)
{
// 拆解字串的資料表名稱及欄位名稱
StrFunc.StrSplitFieldName(this.VariableParser.Value, out string sTableName, out string sFieldName);
if (StrFunc.StrIsEmpty(sTableName))
{
sTableName = tableName;
}
// 取得作用資料列
var oRow = GetDataRow(dataSet, sTableName, sFieldName, rowIndex);
if (BaseFunc.IsNotNull(oRow))
{
oFilterItem.FilterValue = BaseFunc.CStr(oRow[sFieldName]);
}
else
{
oFilterItem.FilterValue = string.Empty;
}
}
oFilterItems.Add(oFilterItem);
}
// 移除無查詢值的條件
oFilterItems.RemoveEmpty();
return(oFilterItems);
}
/// <summary>
/// 判斷指定的欄位集合字串是是否有虛擬欄位，並執行建立虛擬欄位的作業。
/// </summary>
public void Execute(string fields)
{
if (StrFunc.StrIsEmpty(fields))
{
return;
}
var oHelper = new GDataTableHelper(this.DataTable);
var oFields = StrFunc.StrSplit(fields, ",");
foreach (string fieldName in oFields)
{
var oFieldDefine = this.TableDefine.Fields[fieldName];
// 針對 VirtualField 欄位，動態加入 DataColumn
if (BaseFunc.IsNotNull(oFieldDefine) && oFieldDefine.FieldType == EFieldType.VirtualField && !this.DataTable.Columns.Contains(oFieldDefine.FieldName))
{
var dataCol = oHelper.AddColumn(oFieldDefine.FieldName, oFieldDefine.DbType);
SetAllowNullSetting(dataCol, oFieldDefine);
}
}
}
/// <summary>
/// 處理單一條件。
/// </summary>
/// <param name="filterItem">資料過濾條件項目。</param>
private string ProcessFilterItem(GFilterItem filterItem)
{
GFieldDefine oFieldDefine;
string sTableName;
string sFieldName;
bool bDetail;
StrFunc.StrSplitFieldName(filterItem.FieldName, out sTableName, out sFieldName);
if (StrFunc.StrIsEmpty(sTableName))
{
sTableName = this.Args.TableName;
bDetail = false;
}
else
{
bDetail = true;
}
oFieldDefine = this.Args.ProgramDefine.Tables[sTableName].Fields[sFieldName];
if (BaseFunc.IsNull(oFieldDefine))
{
return(string.Empty);
}
switch (filterItem.ComparisonOperator)
{
case EComparisonOperator.Between:
return(ProcessBetween(oFieldDefine, filterItem, bDetail));
case EComparisonOperator.In:
return(ProcessIn(oFieldDefine, filterItem, bDetail));
case EComparisonOperator.NotIn:
return(ProcessIn(oFieldDefine, filterItem, bDetail, false));
default:
return(ProcessItem(oFieldDefine, filterItem, bDetail));
}
}