/// <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>
/// 處理區間條件。
/// </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="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="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));
}
}
