public IList <Estatus> GetList(bool fallido, string query, Sort sort, string tipo, int orden, int page, int start, int limit, ref int totalRecords, ref string errMsg)
{
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";
if (!string.IsNullOrWhiteSpace(tipo))
{
where += String.Format(" and EstatusTipo = '{0}'", tipo);
}
if (orden > 0 && !fallido)
{
where += String.Format(" and EstatusOrden between {0} and {1}", orden, orden + 1);
}
if (fallido)
{
where += String.Format(" and EstatusId in ({0},{1}) ", EstatusImportacion.Asignado, EstatusImportacion.Fallido);
}
if (!string.IsNullOrEmpty(query))
{
string fieldName = "EstatusNombre+(CASE WHEN EstatusTipo='I' Then 'IMPORTACION' Else 'DISTRIBUCION' End)+STR(EstatusOrden)";
where += (!string.IsNullOrEmpty(where) ? " and " : "") +
EnumExtension.generateLikeWhere(query, fieldName);
}
// Ordenamiento
string order = "EstatusOrden";
string direction = "ASC";
if (!string.IsNullOrWhiteSpace(sort.property))
{
order = sort.property;
direction = sort.direction;
if (order == "x_Estatus")
{
order = "EstatusTipo";
}
}
string sql = "SELECT * FROM ( " +
"SELECT *,(CASE WHEN EstatusTipo='I' Then 'IMPORTACION' Else 'DISTRIBUCION' End) as x_Estatus, " +
" ROW_NUMBER() OVER (ORDER BY {2} {3}) as row, " +
" IsNull((select count(*) from Estatus WHERE {0}),0) as TotalRecords " +
" FROM Estatus WHERE {0}) a " +
" WHERE {1} " +
" ORDER BY row";
sql = String.Format(sql, where, wherepage, order, direction);
SqlDataAdapter da = new SqlDataAdapter(sql, oConn);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
LogManager.Write("ERROR:" + Environment.NewLine + "\tMETHOD = " + this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name + Environment.NewLine + "\tMESSAGE = " + ex.Message);
errMsg = ex.Message;
return(null);
}
ConnManager.CloseConn(oConn);
DataTable dt;
dt = ds.Tables[0];
totalRecords = dt.Rows.Count;
if (totalRecords > 0)
{
IList <Estatus> data = EnumExtension.ToList <Estatus>(dt);
totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);
return(data);
}
else
{
return(null);
}
}