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