public static string DtLess(WhereData data)
{
return(SQL(data,
APP.DbProvider == DbProviderEnum.SqlServer
? "{0} < '{1} 23:59:59'"
: "{0} < TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss')"));
}
public static string MapChild(WhereData data)
{
return(SQL(data,
APP.DbProvider == DbProviderEnum.SqlServer
? "{0} in (select {0} from {2} where {3} in (select ID from [dbo].[GetChild]('{4}','{1}')))"
: "{0} in (select {0} from {2} where {3} in (select COLUMN_VALUE from table(GetChild('{1}'))))"));
}
private static string GetDateRangeSql(WhereData cp, char[] separator, bool ignoreEmpty = true)
{
var sSql = string.Empty;
string fromDateStr;
string toDateStr;
if (APP.DbProvider == DbProviderEnum.Oracle)
{
fromDateStr = "{0} >= TO_DATE('{1}','yyyy-mm-dd')";
toDateStr = "{0} <= TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss')";
}
else
{
fromDateStr = "datediff(day,'{1}',{0}) >=0";
toDateStr = "datediff(day,'{1}',{0})<=0";
}
var values = ZConvert.ToString(cp.Value).Split(separator);
var startDate = values[0].Trim();
var endDate = values.Length == 2 ? values[1].Trim() : startDate;
if (!string.IsNullOrWhiteSpace(startDate) || !ignoreEmpty)
{
sSql = string.Format(fromDateStr, cp.Column, startDate);
}
if (!string.IsNullOrWhiteSpace(endDate) || !ignoreEmpty)
{
sSql += (sSql.Length > 0 ? " AND " : string.Empty) + string.Format(toDateStr, cp.Column, endDate);
}
return(sSql);
}
public static string DtGreater(WhereData data)
{
return(SQL(data,
APP.DbProvider == DbProviderEnum.SqlServer
? "{0} > '{1}'"
: "{0} > TO_DATE('{1}','yyyy-mm-dd hh24:mi:ss')"));
}
private static string SQL(WhereData cp, string stringFormat)
{
var list = cp.Extend.ToList();
list.Insert(0, cp.Column);
list.Insert(1, cp.Value != null ? cp.Value.ToString().Trim() : null);
return(string.Format(stringFormat, list.ToArray()));
}
public static string Custom(WhereData data)
{
if (data.Value == null || data.Value.ToString().Trim() == "")
{
return(string.Format("{0} = ''", data.Column));
}
return(string.Format((data.Extend[0]).ToString(), data.Value.ToString().Trim()));
}
public static string DateEqual(WhereData data)
{
var dateFormat = "{0} = TO_DATE('{1}','yyyy-mm-dd hh24:mi:ss')";
if (APP.DbProvider == DbProviderEnum.SqlServer)
{
dateFormat = "{0}='{1}'";
}
return(SQL(data, dateFormat));
}
public static string DateRange(WhereData data)
{
return(GetDateRangeSql(data, new[] { '到', ',', '，' }));
}
public static string NotEqual(WhereData data)
{
return(SQL(data, "{0} <> '{1}'"));
}
public static string GreaterEqual(WhereData data)
{
return(SQL(data, "{0} >= '{1}'"));
}
public static string UpperLike(WhereData data)
{
return(SQL(data, "{0} like UPPER('%{1}%')"));
}
public static string UpperEqual(WhereData data)
{
return(SQL(data, "{0} = '{1}'").ToUpper());
}
public static string StartWith(WhereData data)
{
return(SQL(data, "{0} like '{1}%'"));
}
public static string LikePY(WhereData data)
{
return(SQL(data, "{0} like '%{1}%' or [dbo].[fun_getPY]({0}) like N'%{1}%'"));
}
public static string Map(WhereData data)
{
return(SQL(data, "{0} in (select {0} from {0} where {0}='{1}')"));
}
public static string Like(WhereData data)
{
return(SQL(data, "{0} like '%{1}%'"));
}
public static string StartWithPY(WhereData data)
{
return(SQL(data, "{0} like '{1}%' or [dbo].[fun_getPY]({0}) like N'{1}%'"));
}
public static string Between(WhereData data)
{
return(SQL(data, "{0} between '{1}' AND '{0}'"));
}
public static string EndWith(WhereData data)
{
return(SQL(data, "{0} like '%{1}'"));
}
public static string LessEqual(WhereData data)
{
return(SQL(data, "{0} <= '{1}'"));
}
public static string In(WhereData data)
{
return(SQL(data, "{0} in ({1})"));
}
