private static string GetDateRangeSql(WhereData cp, char _separator, bool _ignoreEmpty = true)
{
var sSql = string.Empty;
var _from = "datediff(day,'{1}',{0}) >=0";
var _to = "datediff(day,'{1}',{0})<=0";
var values = ZConvert.ToString(cp.Value).Split(_separator);
if (values.Length == 1)
{
values = new string[] { values[0], values[0] }
}
;
if (!string.IsNullOrWhiteSpace(values[0]) || !_ignoreEmpty)
{
sSql = string.Format(_from, cp.Column, values[0]);
}
if (!string.IsNullOrWhiteSpace(values[1]) || !_ignoreEmpty)
{
sSql += (sSql.Length > 0 ? " and " : string.Empty) + string.Format(_to, cp.Column, values[1]);
}
return(sSql);
}
}
private static string SQL(WhereData cp, string stringFormat)
{
var list = cp.Extend.ToList();
list.Insert(0, cp.Value);
list.Insert(0, cp.Column);
return(string.Format(stringFormat, list.ToArray()));
}
public static string Like(WhereData data)
{
return(SQL(data, "{0} like '%{1}%'"));
}
public static string In(WhereData data)
{
return(SQL(data, "{0} in ({1})"));
}
public static string DtLessEqual(WhereData data)
{
return(SQL(data, "datediff(day,'{1}',{0}) < =0"));
}
public static string LessEqual(WhereData data)
{
return(SQL(data, "{0} <= '{1}'"));
}
public static string DtGreaterEqual(WhereData data)
{
return(SQL(data, "datediff(day,'{1}',{0}) > =0"));
}
public static string GreaterEqual(WhereData data)
{
return(SQL(data, "{0} >= '{1}'"));
}
public static string Between(WhereData data)
{
return(SQL(data, "{0} between '{1}' '{0}'"));
}
public static string MapChild(WhereData data)
{
return(SQL(data, "{0} in (select {0} from {2} where {3} in (select ID from [dbo].[GetChild]('{4}','{1}')))"));
}
public static string Child(WhereData data)
{
return(SQL(data, "{0} in (select ID from [dbo].[GetChild]('{0}','{1}'))"));
}
public static string Map(WhereData data)
{
return(SQL(data, "{0} in (select {0} from {0} where {0}='{1}')"));
}
public static string DateRange(WhereData data)
{
return(GetDateRangeSql(data, '到'));
}
public static string StartWith(WhereData data)
{
return(SQL(data, "{0} like '{1}%'"));
}
public static string NotEqual(WhereData data)
{
return(SQL(data, "{0} <> '{1}'"));
}
public static string StartWithPY(WhereData data)
{
return(SQL(data, "{0} like '{1}%' or [dbo].[fun_getPY]({0}) like N'{1}%'"));
}
public static string EndWith(WhereData data)
{
return(SQL(data, "{0} like '%{1}'"));
}