public IList <ModelVendorShipAddress> 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 = "VendorShipAddress"; where += (!string.IsNullOrEmpty(where) ? " and " : "") + EnumExtension.generateLikeWhere(query, fieldName); } // Handle Order string order = "VendorShipKey"; 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 vVendorShipAddress 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) { 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 <ModelVendorShipAddress> data = dt.ToList <ModelVendorShipAddress>(); totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]); return(data); } else { return(null); } }
public IList <QuoteHeader> GetList(string query, Filter filter, Sort sort, int page, int start, int limit, ref int totalRecords, ref string errMsg) { 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}", start) : "1=1"; string where = "1=1"; int VendorKey = 0; #region Filtros if (!string.IsNullOrWhiteSpace(filter.property)) { if (filter.property == "VendorKey") { VendorKey = Convert.ToInt32(filter.value); if (VendorKey > 0) { where += String.Format(" AND QHeaderDraft = 0 AND TotalItems > 0 "); } } else { where += String.Format(" and {0} = {1}", filter.property, filter.value); } } #endregion Filtros if (!string.IsNullOrEmpty(query)) { string fieldName = ""; fieldName = "CONVERT(VARCHAR(10), QHeaderDateBegin,103)"; where += (!string.IsNullOrEmpty(where) ? " and " : "") + EnumExtension.generateLikeWhere(query, fieldName); } // Handle Orders string order = "QHeaderDateBegin"; string direction = "DESC"; if (!string.IsNullOrWhiteSpace(sort.property)) { order = sort.property; direction = sort.direction; } string top = (@limit > 0) ? String.Format(" TOP {0} ", @limit) : ""; string sql = @"WITH qData AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY {2} {3}) as row FROM vQuoteHeaders WHERE {0} ) SELECT {4} a.*, Records.Total as TotalRecords ,Interested.Total as Interested ,Offers.Total as Offers ,CAST((CASE WHEN OS.QOfferStatus = 2 THEN 1 ELSE 0 END) AS BIT) as wasDesisted FROM qData a CROSS APPLY (select MAX(row) as Total from qData) as Records CROSS APPLY (select count(*) as Total from (select distinct QHeaderKey,QMessageFromVendorKey from QuoteMessages) as c where c.QHeaderKey = a.QHeaderKey and c.QMessageFromVendorKey IS NOT NULL) as Interested CROSS APPLY (select count(*) as Total from (select distinct QHeaderKey,VendorKey from QuoteOffers) as c where c.QHeaderKey = a.QHeaderKey) as Offers OUTER APPLY (select MAX(QOfferStatus) as QOfferStatus from QuoteOffers QO WHERE QO.QHeaderKey = a.QHeaderKey AND QO.VendorKey = @VendorKey) as OS WHERE {1} ORDER BY row "; where = (where.StartsWith("1=1 AND ")) ? where.Replace("1=1 AND ", "") : where; sql = String.Format(sql, where, wherepage, order, direction, top); SqlDataAdapter da = new SqlDataAdapter(sql, oConn); da.SelectCommand.Parameters.Add("@VendorKey", SqlDbType.Int).Value = VendorKey; 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 <QuoteHeader> data = dt.ToList <QuoteHeader>(); totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]); return(data); } else { return(null); } }
public IList <Role> GetList(string query, Sort sort, 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.IsNullOrEmpty(query)) { string fieldName = "a.RoleName"; where += (!string.IsNullOrEmpty(where) ? " and " : "") + EnumExtension.generateLikeWhere(query, fieldName); } // Handle Order string order = "a.RoleKey"; string direction = "ASC"; if (!string.IsNullOrWhiteSpace(sort.property)) { order = sort.property; direction = sort.direction; } string sql = "SELECT * FROM ( " + "SELECT a.*, " + " ROW_NUMBER() OVER (ORDER BY {2} {3}) as row, " + " IsNull((select count(*) from Roles a WHERE {0}),0) as TotalRecords " + " FROM Roles a 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 <Role> data = dt.ToList <Role>(); totalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]); return(data); } else { return(null); } }