/// <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>
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="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>
/// 建立 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="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>
/// 取得 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="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="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>
/// 建立資料庫命令產生器。
/// </summary>
/// <param name="sessionInfo">連線資訊。</param>
/// <param name="programDefine">程式定義。</param>
public static IDbCommandBuilder CreateDbCommandBuilder(GSessionInfo sessionInfo, GProgramDefine programDefine)
{
string sMessage;
switch (sessionInfo.DatabaseType)
{
case EDatabaseType.SQLServer:
return(new GSqlCommandBuilder(sessionInfo.SessionGuid, programDefine));
case EDatabaseType.Oracle:
return(new GOracleCommandBuilder(sessionInfo.SessionGuid, programDefine));
default:
sMessage = StrFunc.StrFormat(@"DatabaseType={0} Not Supported IDbCommandBuilder", BaseFunc.GetEnumName(sessionInfo.DatabaseType));
throw new NotSupportedException(sMessage);
}
}
/// <summary>
/// 依資料庫類型建立資料庫命令輔助類別。
/// </summary>
/// <param name="databaseType">資料庫類型。</param>
/// <param name="commandText">命令字串。</param>
public static IDbCommandHelper CreateDbCommandHelper(EDatabaseType databaseType, string commandText)
{
string sMessage;
switch (databaseType)
{
case EDatabaseType.SQLServer:
return(new GSqlCommandHelper(commandText));
case EDatabaseType.Oracle:
return(new GOracleCommandHelper(commandText));
default:
sMessage = StrFunc.StrFormat(@"DatabaseType={0} Not Supported IDbCommandHelper", BaseFunc.GetEnumName(databaseType));
throw new NotSupportedException(sMessage);
}
}
/// <summary>
/// 取得過濾欄位名稱。
/// </summary>
/// <param name="fieldDefine">欄位定義。</param>
/// <param name="isDetail">是否為明細欄位。</param>
private string GetFieldName(GFieldDefine fieldDefine, bool isDetail)
{
if (BaseFunc.IsNotNull(this.Args.TableJoinProvider))
{
if (isDetail)
{
return(this.Args.TableJoinProvider.GetDetailDbFieldName(this.DbCommandHelper, fieldDefine));
}
else
{
return(this.Args.TableJoinProvider.GetDbFieldName(this.DbCommandHelper, fieldDefine));
}
}
else
{
return(StrFunc.StrFormat("{0}.{1}", "A", this.DbCommandHelper.GetFieldName(fieldDefine.DbFieldName)));
}
}
/// <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>
/// 取得 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>
protected string NewFilterParameterName()
{
string sName;
bool bFlag;
bFlag = false;
do
{
_FilterIndex = _FilterIndex + 1;
sName = StrFunc.StrFormat("Filter_{0}", _FilterIndex);
sName = GetParameterName(sName);
// 參數名稱不存在則設定 Flag 為 True，以跳離迴圈
if (!this.DbCommand.Parameters.Contains(sName))
{
bFlag = true;
}
} while (bFlag == false);
return(sName);
}
/// <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)));
}
}
/// <summary>
/// 取得 Db Parameter 的回傳的參數。
/// </summary>
/// <param name="fieldDefine">欄位定義。</param>
/// <param name="comparison">比較運算子。</param>
/// <param name="value">過濾條件值。</param>
private string GetDbParameter(GFieldDefine fieldDefine, EComparisonOperator comparison, object value)
{
var oDbParameter = this.DbCommandHelper.AddFilterParameter(fieldDefine);
if (comparison == EComparisonOperator.Like &&
(fieldDefine.DbType == EFieldDbType.String || fieldDefine.DbType == EFieldDbType.Text))
{
// Like 加入 % 符號
if (BaseFunc.CStr(value).Contains("%"))
{
oDbParameter.Value = value;
}
else
{
oDbParameter.Value = StrFunc.StrFormat("%{0}%", value);
}
}
else
{
oDbParameter.Value = value;
}
return(oDbParameter.ParameterName);
}
/// <summary>
/// 物件描述文字。
/// </summary>
public override string ToString()
{
return(StrFunc.StrFormat("{0} {1} {2}", this.FieldName, this.ComparisonOperator, this.FilterValue));
}
/// <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);
}
}
}
/// <summary>
/// 取得日期變數值。
/// </summary>
/// <param name="dateTimeUnit">日期單位。</param>
private string GetDateVariable(EDateUnit dateTimeUnit)
{
DateFunc.GetDateRange(DateTime.Now, dateTimeUnit, out DateTime oStartDate, out DateTime oEndTime);
return(StrFunc.StrFormat("{0},{1}", oStartDate.ToString("yyyy/MM/dd"), oEndTime.ToString("yyyy/MM/dd")));
}
/// <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>
public override string ToString()
{
return(StrFunc.StrFormat("{0} {1}", this.FieldName, this.SortDirection));
}
/// <summary>
/// 物件的描述文字。
/// </summary>
public override string ToString()
{
return(StrFunc.StrFormat("{0} - {1}", this.ViewID, this.Name));
}