/// <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>
/// 檢查過濾條件的 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>
/// 取得欄位值後轉型成DateTime
/// </summary>
/// <param name="fieldName">欄位名稱</param>
/// <returns></returns>
public DateTime ValueAsDateTime(string fieldName)
{
if (this.HasField(fieldName))
{
return(BaseFunc.CDateTime(this[fieldName]));
}
return(default(DateTime));
}
/// <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="dbType">欄位資料型別。</param>
/// <param name="value">參數值。</param>
public virtual object GetParameterValue(EFieldDbType dbType, object value)
{
if (dbType == EFieldDbType.DateTime)
{
if (BaseFunc.IsEmpty(BaseFunc.CDateTime(value)))
{
return(DBNull.Value);
}
else
{
return(value);
}
}
else
{
return(value);
}
}
/// <summary>
/// 取得篩選值。
/// </summary>
/// <param name="dbType">欄位資料型別。</param>
/// <param name="value">輸入值。</param>
private string GetFilterValue(EFieldDbType dbType, object value)
{
if (dbType == EFieldDbType.DateTime)
{
if (BaseFunc.IsNullOrDBNull(value))
{
return(string.Empty);
}
else
{
return(BaseFunc.CDateTime(value).ToString("yyyy/MM/dd"));
}
}
else
{
return(BaseFunc.CStr(value));
}
}