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());
}
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()));
}
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;
}
public static string DtGreaterEqual(WhereData data)
{
return(SQL(data, "datediff(day,'{1}',{0}) > =0"));
}
public static string DtLessEqual(WhereData data) { return SQL(data, "datediff(day,'{1}',{0}) < =0"); }
public static string CharindexEqual(WhereData data)
{
return(SQL(data, "isnull(CHARINDEX('{0}',{1}),0)=0"));
}
public static string NotIn(WhereData data)
{
return(SQL(data, " {0} not in ({1})"));
}
public static string Child(WhereData data)
{
return(SQL(data, "{0} in (select ID from [dbo].[GetChild]('{0}','{1}'))"));
}
public static string DateRange(WhereData data)
{
return(GetDateRangeSql(data, '到'));
}
public static string EndWith(WhereData data)
{
return(SQL(data, "{0} like '%{1}'"));
}
public static string DateRange(WhereData data) { return GetDateRangeSql(data, '到'); }
public static string Between(WhereData data) { return SQL(data, "{0} between '{1}' '{0}'"); }
public static string EndWith(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 StartWith(WhereData data) { return SQL(data, "{0} like '{1}%'"); }
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 LessEqualSql(WhereData data)
{
return(SQL(data, "{0} <= {1}"));
}
public static string GreaterEqual(WhereData data) { return SQL(data, "{0} >= '{1}'"); }
public static string Map(WhereData data) { return SQL(data, "{0} in (select {0} from {0} where {0}='{1}')"); }
public static string Between(WhereData data)
{
return(SQL(data, "{0} between '{1}' '{0}'"));
}
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 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 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 DtGreaterEqual(WhereData data) { return SQL(data, "datediff(day,'{1}',{0}) > =0"); }
public static string Replace(WhereData data)
{
return(SQL(data, " ISNULL({0},0)=0 or {0} not in ({1})"));
}
public static string NotNull(WhereData data)
{
return(SQL(data, "({0} is not null or {0}<>'') "));
}
public static string NotEqual(WhereData data)
{
return(SQL(data, "{0} <> '{1}'"));
}
public static string LessEqual(WhereData data) { return SQL(data, "{0} <= '{1}'"); }
public static string SqlEqual(WhereData data)
{
return(SQL(data, "{0} = {1}"));
}
public static string Like(WhereData data)
{
return(SQL(data, "{0} like '%{1}%'"));
}
public static string IsNull(WhereData data)
{
return(SQL(data, "{0} is {1}"));
}
public static string LikeDate(WhereData data)
{
return(SQL(data, " CONVERT(varchar,{0},20) like '%{1}%'"));
}
public static string GreaterEqual(WhereData data)
{
return(SQL(data, "{0} >= '{1}'"));
}
public static string StartWith(WhereData data)
{
return(SQL(data, "{0} like '{1}%'"));
}
public static string Less(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 DtLessEqual(WhereData data)
{
return(SQL(data, "datediff(day,'{1}',{0}) < =0"));
}
public static string NotEqual(WhereData data) { return SQL(data, "{0} <> '{1}'"); }
