Dawin Dawin - 1 month ago 8
JSON Question

Add filter using XML

I have method and Query like this:

public static string GetChartEnergy(string initDate, string endDate, string type)
{
structure.Add(initDate.CreateQueryStructure(endDate, true, null, "convert(datetime,'15/' + CONVERT(varchar(10), k.Month) + '/' + CONVERT(varchar(10), k.Year), 103)", null, false));
structure.Add(type.CreateQueryStructure(string.Empty, false, "CASE WHEN m.Type = 1 THEN 'Agua' ELSE CASE WHEN m.Type = 2 THEN 'Luz' ELSE 'Gas' END END AS Type", " m.type", "m.Type", false));
}


CreateQueryStructure is this one:

public static QueryStructure CreateQueryStructure(this String value, string endDate, bool isDate,
string columnName, string whereName, string groupByName, bool isNullField)
{
QueryStructure structure = new QueryStructure();

if (!string.IsNullOrEmpty(value))
{
if (value != ",")
{
if (isDate)
{
//obtiene la estructura para un filtro entre fechas
structure.ColumnSelect = columnName;
structure.ColumnGroupBy = groupByName;
structure.ColumnWhere = string.Format("({0} BETWEEN convert(datetime,\'{1}\', 103) and convert(datetime,\'{2}\', 103))", whereName, value.Remove(value.Length - 1), endDate.Remove(value.Length - 1));
structure.Values = null;
structure.Operator = Operator.Nothing;
}
else
{
if (isNullField)
{
//obtiene la estructura de un filtro por un campo que es null o no
if (value.Remove(value.Length - 1) != "-1")
{
structure.ColumnWhere = string.Format("{0} IS{1} NULL", whereName,
value.Remove(value.Length - 1) == "0"
? " NOT" :
string.Empty);

structure.Values = null;
structure.Operator = Operator.And;
}
}
else
{
//obtiene la estructura de un campo aplicando la regla IN seleccionando
//el campo a mostrar y el campo en groupBy
structure.ColumnSelect = columnName;
structure.ColumnGroupBy = groupByName;
structure.ColumnWhere = whereName;
structure.Values = value.Remove(value.Length - 1);
structure.Operator = Operator.And;
}
}
}
}

return structure;
}


OUTPUT:
"(convert(datetime,'15/' + CONVERT(varchar(10), k.Month) + '/' + CONVERT(varchar(10), k.Year), 103) BETWEEN convert(datetime,'01/01/2014', 103) and convert(datetime,'31/10/2016', 103))"


Now when it pass from two
CreateQueryStructure
I have another method like:

public static string GetChartInfo(List<QueryStructure> queryStructure, string procedureName)
{
var queryWhere = queryStructure.GetWhere();
}


So now it pass to
GetWhere
:

public static string GetWhere(this List<QueryStructure> filters)
{
string result = string.Empty;

if (filters != null && filters.Count > 0)
{
if (filters.Select(x => x.ColumnWhere).Any())
{
result += "WHERE ";

foreach (var filter in filters)
{
if (filter.Operator != Operator.Nothing)
{
result += " " + filter.Operator.ToString() + " ";
}

if (!string.IsNullOrEmpty(filter.Values))
{
result += filter.ColumnWhere + " IN (";

result += filter.Values;

result += ") ";

}
else
{
result += filter.ColumnWhere;
}
}
}
}

return result;
}


And finally it return Output with where clause:

"WHERE (convert(datetime,'15/' + CONVERT(varchar(10), k.Month) + '/' + CONVERT(varchar(10), k.Year), 103) BETWEEN convert(datetime,'01/01/2014', 103) and convert(datetime,'31/10/2016', 103)) And m.type IN (2) "


And I want to change where clause to get only items of currentUser, so I get currentUser in method sending parameter from controller like:

public static string GetChartEnergy(string initDate, string endDate, string type, int currentUser) //there I have currentUser


Now, how can I add this filter to:

structure.Add(initDate.CreateQueryStructure(endDate, true, null, "convert(datetime,'15/' + CONVERT(varchar(10), k.Month) + '/' + CONVERT(varchar(10), k.Year), 103)", null, false));


in linq I just need to do some like
x => x.user == currentUser
but in xml I donĀ“t know how, can any one help me?. Regards

Update: Query Structure class:

public class QueryStructure
{
public string ColumnGroupBy { get; set; }

public string ColumnSelect { get; set; }

public string ColumnWhere { get; set; }
public Operator Operator { get; set; }

public string Values { get; set; }

}

Answer

Looking at your methods it will be very simpler:

public static string GetChartEnergy(string initDate, string endDate, string type,string currentUser)
    {
structure.Add(initDate.CreateQueryStructure(endDate, true, null, "convert(datetime,'15/' + CONVERT(varchar(10), k.Month) + '/' +  CONVERT(varchar(10), k.Year), 103)", null, false));
structure.Add(type.CreateQueryStructure(string.Empty, false, "CASE WHEN m.Type = 1 THEN 'Agua' ELSE CASE WHEN m.Type = 2 THEN 'Luz' ELSE 'Gas' END END AS Type", " m.type", "m.Type", false));

structure.Add(curentUser.CreateQueryStructure(string.Empty, false, string.Empty, "m.User", string.Empty, false));
}

assuming m.User is the user column to filter which will give you output like this

WHERE (convert(datetime,'15/' + CONVERT(varchar(10), k.Month) + '/' +  CONVERT(varchar(10), k.Year), 103) BETWEEN convert(datetime,'01/01/2014', 103) and convert(datetime,'31/10/2016', 103)) And  m.type IN (2) AND m.User IN("yourusername")