/// <summary>
/// gets the filter for the given field name, if not existing creates a new not-active one with the default 'Contains' field operator
/// </summary>
/// <param name="fieldName"></param>
/// <returns></returns>
public FltAtomExprData GetFilter(string fieldName)
{
if (!FieldFilters.ContainsKey(fieldName))
{
FieldFilters[fieldName] = new FltAtomExprData()
{
Field = fieldName,
Operator = FilterFieldOperator.Equal,
Value = "",
IsActive = false
};
}
return(FieldFilters[fieldName]);
}
public void SetFieldFilterPropertyType(string fieldName, PropertyType type)
{
if (!FieldFilters.ContainsKey(fieldName))
{
FieldFilters[fieldName] = new FltAtomExprData()
{
Field = fieldName,
Operator = FilterFieldOperator.Equal,
IsActive = false
};
}
FltAtomExprData fa = FieldFilters[fieldName];
fa.PropertyType = type;
}
/// <summary>
/// sets the field filter, if not exist, creates it, with default 'Contains' operator. If value is empty string or null or white space, deactivates the filter
/// </summary>
/// <param name="fieldName"></param>
/// <param name="value"></param>
public void SetFieldFilter(string fieldName, string value)
{
if (!FieldFilters.ContainsKey(fieldName))
{
FieldFilters[fieldName] = new FltAtomExprData()
{
Field = fieldName,
Operator = FilterFieldOperator.Equal,
};
}
FltAtomExprData fa = FieldFilters[fieldName];
if (value.IsNotEmpty())
{
fa.IsActive = true;
fa.Value = value;
}
else
{
fa.IsActive = false;
fa.Value = "";
}
}
public object GetAll()
{
var queryValues = Request.RequestUri.ParseQueryString();
int page = Convert.ToInt32(queryValues["page"]);
int start = Convert.ToInt32(queryValues["start"]);
int limit = Convert.ToInt32(queryValues["limit"]);
int id = Convert.ToInt32(queryValues["id"]);
int orden = Convert.ToInt32(queryValues["orden"]);
string strFieldFilters = queryValues["fieldFilters"];
FieldFilters fieldFilters = new FieldFilters();
if (!String.IsNullOrEmpty(strFieldFilters))
{
fieldFilters = JsonConvert.DeserializeObject <FieldFilters>(strFieldFilters);
}
#region Configuramos el orden de la consulta si se obtuvo como parametro
string strOrder = !string.IsNullOrWhiteSpace(queryValues["sort"]) ? queryValues["sort"] : "";
strOrder = strOrder.Replace('[', ' ');
strOrder = strOrder.Replace(']', ' ');
Sort sort;
if (!string.IsNullOrWhiteSpace(strOrder))
{
sort = JsonConvert.DeserializeObject <Sort>(strOrder);
}
else
{
sort = new Sort();
}
#endregion
string query = !string.IsNullOrWhiteSpace(queryValues["query"]) ? queryValues["query"] : "";
int totalRecords = 0;
try
{
if (id == 0)
{
object json;
var lista = repository.GetList(query, fieldFilters, sort, page, start, limit, ref totalRecords);
json = new
{
total = totalRecords,
data = lista,
success = true
};
return(json);
}
else
{
Interested model = repository.Get(id);
object json = new
{
data = model,
success = true
};
return(json);
}
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
object json = new
{
message = ex.Message,
success = false
};
return(json);
}
}
public IList <CustomsClearence> GetList(FieldFilters fieldFilters, string query, Sort sort, int page, int start, int limit, ref int totalRecords)
{
limit = limit + start;
SqlConnection oConn = null;
try
{
oConn = ConnManager.OpenConn();
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
};
string wherepage = (page != 0) ? String.Format("row>{0} and row<={1} ", start, limit) : "1=1";
string where = "1=1";
#region Field Filters
if (fieldFilters.fields != null && fieldFilters.fields.Count > 0)
{
foreach (var item in fieldFilters.fields)
{
string value = item.value;
string name = item.name;
if (item.type == "string" || item.type == "date")
{
value = "'" + value + "'";
}
if (item.type == "date")
{
name = String.Format("CAST({0} as DATE)", name);
}
where += String.Format(" AND {0} = {1}", name, value);
}
}
#endregion Field Filters
if (!string.IsNullOrEmpty(query))
{
string fieldName = "CClearenceId";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Handle Order
string order = "CClearenceId";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
}
string sql = @"WITH qData
AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY {2} {3}) as row
FROM vCustomsClearence
WHERE {0}
)
SELECT {4} *, t5.TotalRecords
FROM qData
INNER JOIN ((select TOP 1 row as TotalRecords from qData order by row desc)) as t5 on 1=1
WHERE {1}
ORDER BY row ";
where = (where.StartsWith("1=1 AND ")) ? where.Replace("1=1 AND ", "") : where;
string topLimit = ((@limit > 0) ? String.Format(" TOP {0} ", @limit) : "");
sql = String.Format(sql, where, wherepage, order, direction, topLimit);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
ConnManager.CloseConn(oConn);
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
throw;
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <CustomsClearence> data = EnumExtension.ToList <CustomsClearence>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}
