/// <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="fields"></param>
/// <param name="linkProgID"></param>
/// <param name="linkDisplayName"></param>
private void AddLinkReturnField(GFieldDefineCollection fields, GProgramDefine linkProgDefine)
{
var linkProgID = linkProgDefine.ProgID;
var linkDisplayName = linkProgDefine.DisplayName;
var linkField = new GFieldDefine {
FieldName = $"{linkProgID}ID", DisplayName = $"{linkDisplayName}編號", MaxLength = 10, LinkProgID = linkProgID
};
linkField.LinkReturnFields.Add(new GLinkReturnField {
SourceField = SysFields.ID, DestField = $"{linkProgID}ID"
});
linkField.LinkReturnFields.Add(new GLinkReturnField {
SourceField = SysFields.ViewID, DestField = $"TMP_{linkProgID}ID"
});
linkField.LinkReturnFields.Add(new GLinkReturnField {
SourceField = SysFields.Name, DestField = $"TMP_{linkProgID}Name"
});
fields.Add(linkField);
fields.Add(new GFieldDefine {
FieldName = $"TMP_{linkProgID}ID", DisplayName = $"{linkDisplayName}代碼", FieldType = EFieldType.LinkField, LinkFieldName = $"{linkProgID}ID"
});
fields.Add(new GFieldDefine {
FieldName = $"TMP_{linkProgID}Name", DisplayName = $"{linkDisplayName}名稱", FieldType = EFieldType.LinkField, LinkFieldName = $"{linkProgID}ID"
});
}
/// <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>
/// 加入AllowNull設定
/// </summary>
/// <param name="dataCol"></param>
/// <param name="fieldDefine"></param>
private void SetAllowNullSetting(DataColumn dataCol, GFieldDefine fieldDefine)
{
if (fieldDefine.AllowNull)
{
dataCol.AllowDBNull = true;
dataCol.DefaultValue = DBNull.Value;
}
}
/// <summary>
/// 依欄位定義新增命令參數。
/// </summary>
/// <param name="fieldDefine">欄位定義。</param>
public virtual DbParameter AddParameter(GFieldDefine fieldDefine)
{
DbParameter oParameter;
oParameter = AddParameter(fieldDefine.FieldName, fieldDefine.DbType);
oParameter.SourceColumn = fieldDefine.FieldName;
return(oParameter);
}
/// <summary>
/// 依欄位定義新增 SourceVersion=Original 的命令參數。
/// </summary>
/// <param name="fieldDefine">欄位定義。</param>
public virtual DbParameter AddOriginalParameter(GFieldDefine fieldDefine)
{
DbParameter oParameter;
oParameter = AddParameter(fieldDefine.FieldName + "_ORG", fieldDefine.DbType);
oParameter.SourceColumn = fieldDefine.FieldName;
oParameter.SourceVersion = DataRowVersion.Original;
return(oParameter);
}
public void DoCreateProgramDefine()
{
var helper = new InitDataHelper();
var progDefine = new GProgramDefine();
Console.WriteLine("ProgID：");
progDefine.ProgID = Console.ReadLine();
Console.WriteLine("DisplayName：");
progDefine.DisplayName = Console.ReadLine();
Console.WriteLine("Start Create Table");
var tableDefine = new GTableDefine();
tableDefine.PrimaryKey = progDefine.ProgID + "Id";
progDefine.Tables.Add(tableDefine);
Console.WriteLine("TableName：");
tableDefine.TableName = Console.ReadLine();
Console.WriteLine("DbTableName：");
tableDefine.DbTableName = Console.ReadLine();
Console.WriteLine("Table DisplayName：");
tableDefine.DisplayName = Console.ReadLine();
var createField = true;
while (createField)
{
Console.WriteLine("Start Create Field Define");
var fieldDefine = new GFieldDefine();
tableDefine.Fields.Add(fieldDefine);
Console.WriteLine("FieldName：");
fieldDefine.FieldName = Console.ReadLine();
Console.WriteLine("DisplayName：");
fieldDefine.DisplayName = Console.ReadLine();
Console.WriteLine("DbType：");
Console.WriteLine("(0：String, 1：Text, 2：Boolean, 3：Integer, 4：Double, 5：Currency, 6：DateTime, 7：GUID, 8：Binary)");
var dbType = int.Parse(Console.ReadLine());
fieldDefine.DbType = BaseFunc.CEnum <EFieldDbType>(dbType, 0);
Console.WriteLine("MaxLength：");
fieldDefine.MaxLength = int.Parse(Console.ReadLine());
Console.WriteLine("AllowNull：(0: false, 1: true)");
fieldDefine.AllowNull = BaseFunc.CBool(Console.ReadLine());
Console.WriteLine("LinkFieldName：");
fieldDefine.LinkFieldName = Console.ReadLine();
Console.WriteLine("LinkProgID：");
fieldDefine.LinkProgID = Console.ReadLine();
Console.WriteLine("Continue Create Field Define? 1：Yes? 0：No?");
var ans = Console.ReadLine();
if (ans.SameTextOr("No", "0"))
{
createField = false;
}
Console.WriteLine("================================================");
}
helper.ProgDefineToJson(progDefine);
Console.WriteLine($"已產生程式定義檔案：{progDefine.ProgID}/{progDefine.DisplayName}");
Console.ReadKey();
}
/// <summary>
/// 依欄位定義新增過濾絛件命令參數。
/// </summary>
/// <param name="fieldDefine">欄位定義。</param>
public virtual DbParameter AddFilterParameter(GFieldDefine fieldDefine)
{
DbParameter oParameter;
string sName;
sName = this.NewFilterParameterName();
oParameter = AddParameter(sName, fieldDefine.DbType);
oParameter.Direction = ParameterDirection.Input;
return(oParameter);
}
/// <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="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>
/// <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>
/// <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="fieldDefine">欄位定義。</param>
public override DbParameter AddFilterParameter(GFieldDefine fieldDefine)
{
return(base.AddFilterParameter(fieldDefine));
}
