コード例 #1
0
        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);
            }
        }