private static List <ModuleSettings> GeSettingValuesList() { var settings = SQLDataAccess.ExecuteReadList("Select * From [Settings].[ModuleSettings]", CommandType.Text, reader => new ModuleSettings() { Name = SQLDataHelper.GetString(reader, "Name"), Value = SQLDataHelper.GetObject(reader, "Value"), ModuleName = SQLDataHelper.GetString(reader, "ModuleName"), }); if (settings != null) { CacheManager.Insert(CacheNames.GetModuleSettingsCacheObjectName(), settings); } return(settings); }
/// <summary> /// /// </summary> /// <returns></returns> public static List <ISubscriber> GetSubscribedEmails() { return(SQLDataAccess.ExecuteReadList <ISubscriber>( "SELECT [Subscription].[Email], [Customer].[FirstName], [Customer].[LastName], [Customer].[Phone] FROM [Customers].[Subscription] LEFT JOIN [Customers].[Customer] ON [Subscription].[Email] = [Customer].[Email] WHERE [Subscribe] = 1", CommandType.Text, (reader) => { return new Subscription { Email = SQLDataHelper.GetString(reader, "Email"), FirstName = SQLDataHelper.GetString(reader, "FirstName"), LastName = SQLDataHelper.GetString(reader, "LastName"), Phone = SQLDataHelper.GetString(reader, "Phone"), }; })); }
public static List <PropertyGroup> GetListByCategory(int categoryId) { return(SQLDataAccess.ExecuteReadList( "SELECT [PropertyGroup].[PropertyGroupId],[GroupName],[GroupSortOrder] FROM [Catalog].[PropertyGroup] " + "Join [Catalog].[PropertyGroupCategory] On [PropertyGroup].[PropertyGroupId] = [PropertyGroupCategory].[PropertyGroupId] " + "Where [PropertyGroupCategory].[CategoryId] = @CategoryId " + "Order By GroupSortOrder", CommandType.Text, reader => new PropertyGroup() { PropertyGroupId = SQLDataHelper.GetInt(reader, "PropertyGroupId"), Name = SQLDataHelper.GetString(reader, "GroupName"), SortOrder = SQLDataHelper.GetInt(reader, "GroupSortOrder"), }, new SqlParameter("@categoryId", categoryId))); }
public static List <Brand> GetBrandsByCategoryID(int categoryId, bool inDepth) { string cmd = inDepth ? "Select Brand.BrandID, Brand.BrandName, UrlPath, Brand.SortOrder from Catalog.Brand where BrandID in ( select BrandID from Catalog.Product inner join Catalog.ProductCategories on ProductCategories.ProductID= Product.ProductID and ProductCategories.CategoryID in (select id from Settings.GetChildCategoryByParent(@CategoryID) where Product.Enabled = 1 and Product.CategoryEnabled=1) ) and Enabled=1 Order by Brand.SortOrder,Brand.BrandName" : "Select Brand.BrandID, Brand.BrandName, UrlPath, Brand.SortOrder from Catalog.Brand where BrandID in ( select BrandID from Catalog.Product inner join Catalog.ProductCategories on ProductCategories.ProductID= Product.ProductID and ProductCategories.CategoryID = @CategoryID and Product.Enabled = 1 and Product.CategoryEnabled=1) and Enabled=1 Order by Brand.SortOrder, Brand.BrandName"; return(SQLDataAccess.ExecuteReadList <Brand>(cmd, CommandType.Text, reader => new Brand { BrandId = SQLDataHelper.GetInt(reader, "BrandID"), Name = SQLDataHelper.GetString(reader, "BrandName"), UrlPath = SQLDataHelper.GetString(reader, "UrlPath") }, new SqlParameter { ParameterName = "@CategoryID", Value = categoryId })); }
public static IList <ClientOnlineInfo> GetAllInfo() { List <ClientOnlineInfo> lInfo = SQLDataAccess.ExecuteReadList <ClientOnlineInfo>("SELECT * FROM [Customers].[ClientInfo]", CommandType.Text, reader => new ClientOnlineInfo { SessionId = SQLDataHelper.GetString(reader, "sessionID"), Address = SQLDataHelper.GetString(reader, "Address"), UserAgentBrowser = SQLDataHelper.GetString(reader, "UserAgentBrowser"), UserAgentOS = SQLDataHelper.GetString(reader, "UserAgentOS"), CountryByGeoIp = SQLDataHelper.GetString(reader, "CountryByGeoIp"), LastAccessedPath = SQLDataHelper.GetString(reader, "LastAccessedPath"), Started = SQLDataHelper.GetDateTime(reader, "Started"), Ended = SQLDataHelper.GetDateTime(reader, "Ended") }); return(lInfo); }
protected List <Currency> GetCurrencies() { var dataTable = SQLDataAccess.ExecuteReadList("SELECT CurrencyID, CurrencyValue, CurrencyIso3 FROM [Catalog].[Currency];", CommandType.Text, reader => { var currency = new Currency { CurrencyID = SQLDataHelper.GetInt(reader, "CurrencyID"), Value = SQLDataHelper.GetFloat(reader, "CurrencyValue"), Iso3 = SQLDataHelper.GetString(reader, "CurrencyIso3"), }; return(currency); }); return(dataTable); }
public static List <MailFormat> GetMailFormatList() { List <MailFormat> mailFormats = SQLDataAccess.ExecuteReadList <MailFormat>("SELECT * FROM [Settings].[MailFormat]", CommandType.Text, reader => new MailFormat { MailFormatID = SQLDataHelper.GetInt(reader, "MailFormatID"), FormatName = SQLDataHelper.GetString(reader, "FormatName"), FormatText = SQLDataHelper.GetString(reader, "FormatText"), FormatType = (MailType)SQLDataHelper.GetInt(reader, "FormatType"), SortOrder = SQLDataHelper.GetInt(reader, "SortOrder"), Enable = SQLDataHelper.GetBoolean(reader, "Enable"), AddDate = SQLDataHelper.GetDateTime(reader, "AddDate"), ModifyDate = SQLDataHelper.GetDateTime(reader, "ModifyDate") }); return(mailFormats); }
public static List <StoreReview> GetStoreReviewsByParentId(int parentId, bool isModerated) { return(SQLDataAccess.ExecuteReadList <StoreReview>( "SELECT *, (SELECT Count(ID) FROM [Module].[StoreReview] WHERE [ParentID] = ParentReview.ID) as ChildsCount FROM [Module].[StoreReview] as ParentReview WHERE " + (parentId == 0 ? "[ParentID] is NULL" : "[ParentID] = " + parentId) + (isModerated ? " AND [Moderated] = 1" : string.Empty) + " ORDER BY [DateAdded] DESC", CommandType.Text, (reader) => { var review = GetStoreReviewFromReader(reader); review.ChildrenReviews = SQLDataHelper.GetInt(reader, "ChildsCount") > 0 ? GetStoreReviewsByParentId( SQLDataHelper.GetInt(reader, "ID"), isModerated) : new List <StoreReview>(); return review; } )); }
public static ShoppingCart GetShoppingCart(ShoppingCartType shoppingCartType, Guid customerId) { var templist = SQLDataAccess.ExecuteReadList <ShoppingCartItem> ("SELECT * FROM Catalog.ShoppingCart WHERE ShoppingCartTypeId = @ShoppingCartTypeId and CustomerId = @CustomerId", CommandType.Text, GetFromReader, new SqlParameter { ParameterName = "@ShoppingCartTypeId", Value = (int)shoppingCartType }, new SqlParameter { ParameterName = "@CustomerId", Value = customerId } ); var shoppingCart = new ShoppingCart(); shoppingCart.AddRange(templist); return(shoppingCart); }
public static List <Country> GetCountriesByDisplayInPopup() { const string cacheKey = CountryCacheKey + "DisplayInPopup"; if (CacheManager.Contains(cacheKey)) { return(CacheManager.Get <List <Country> >(cacheKey)); } var countries = SQLDataAccess.ExecuteReadList("Select top 12 * From Customers.Country Where DisplayInPopup=1 Order By SortOrder desc, CountryName asc", CommandType.Text, GetCountryFromReader); if (countries != null) { CacheManager.Insert(cacheKey, countries); } return(countries); }
public static IList <Offer> GetOffersByProductId(int productId) { List <Offer> p = SQLDataAccess.ExecuteReadList <Offer>("[Catalog].[sp_GetProductOffersByProductID]", CommandType.StoredProcedure, reader => new Offer { Price = SQLDataHelper.GetDecimal(reader, "Price"), OfferListId = SQLDataHelper.GetInt(reader, "OfferListId"), Unit = SQLDataHelper.GetString(reader, "Unit"), Amount = SQLDataHelper.GetInt(reader, "Amount"), SupplyPrice = SQLDataHelper.GetDecimal(reader, "SupplyPrice"), ShippingPrice = SQLDataHelper.GetDecimal(reader, "ShippingPrice"), Multiplicity = SQLDataHelper.GetInt(reader, "Multiplicity"), MinAmount = SQLDataHelper.GetNullableInt(reader, "MinAmount"), MaxAmount = SQLDataHelper.GetNullableInt(reader, "MaxAmount") }, new SqlParameter("@ProductID", productId)); return(p); }
public static List<Brand> GetBrands(bool haveProducts) { string cmd = haveProducts ? @"SELECT * FROM [Catalog].[Brand] left join Catalog.Photo on Photo.ObjId=Brand.BrandID and Type=@Type Where (SELECT COUNT(ProductID) From [Catalog].[Product] Where [Product].[BrandID]=Brand.[BrandID]) > 0 ORDER BY [SortOrder], [BrandName]" : "Select * from Catalog.Brand left join Catalog.Photo on Photo.ObjId=Brand.BrandID and Type=@Type Order by [SortOrder], [BrandName]"; return SQLDataAccess.ExecuteReadList<Brand>(cmd, CommandType.Text, reader => new Brand { BrandId = SQLDataHelper.GetInt(reader, "BrandId"), Name = SQLDataHelper.GetString(reader, "BrandName"), Description = SQLDataHelper.GetString(reader, "BrandDescription", string.Empty), BriefDescription = SQLDataHelper.GetString(reader, "BrandBriefDescription", string.Empty), BrandLogo = new Photo(SQLDataHelper.GetInt(reader, "PhotoId"), SQLDataHelper.GetInt(reader, "ObjId"), PhotoType.Brand) { PhotoName = SQLDataHelper.GetString(reader, "PhotoName") }, Enabled = SQLDataHelper.GetBoolean(reader, "Enabled", true), SortOrder = SQLDataHelper.GetInt(reader, "SortOrder"), CountryId = SQLDataHelper.GetInt(reader, "CountryID", 0), UrlPath = SQLDataHelper.GetString(reader, "UrlPath"), BrandSiteUrl = SQLDataHelper.GetString(reader, "BrandSiteUrl") } , new SqlParameter("@Type", PhotoType.Brand.ToString())); }
public static List <PaymentMethod> GetAllPaymentMethods(bool onlyEnabled) { var cacheKey = PaymentCacheKey + (onlyEnabled ? "Active" : "All"); if (CacheManager.Contains(cacheKey)) { return(CacheManager.Get <List <PaymentMethod> >(cacheKey)); } var payments = SQLDataAccess.ExecuteReadList( onlyEnabled ? "SELECT * FROM [Order].[PaymentMethod] left join Catalog.Photo on Photo.ObjId=PaymentMethod.PaymentMethodID and Type=@Type where Enabled=1 ORDER BY [SortOrder]" : "SELECT * FROM [Order].[PaymentMethod] left join Catalog.Photo on Photo.ObjId=PaymentMethod.PaymentMethodID and Type=@Type ORDER BY [SortOrder]", CommandType.Text, reader => GetPaymentMethodFromReader(reader, true), new SqlParameter("@Type", PhotoType.Payment.ToString())); CacheManager.Insert(cacheKey, payments); return(payments); }
public static List <PropertyGroupView> GetGroupsByCategory(int categoryId) { return(SQLDataAccess.ExecuteReadList( @"SELECT [PropertyGroup].[PropertyGroupId],[PropertyGroup].[GroupName], [PropertyId], [Property].[Name] as PropertyName, Type FROM [Catalog].[PropertyGroup] Join [Catalog].[PropertyGroupCategory] On [PropertyGroup].[PropertyGroupId] = [PropertyGroupCategory].[PropertyGroupId] Left Join [Catalog].[Property] On [Property].[GroupId] = [PropertyGroup].[PropertyGroupId] Where [PropertyGroupCategory].[CategoryId] = @CategoryId Order By PropertyGroup.GroupSortOrder, Property.SortOrder", CommandType.Text, reader => new PropertyGroupView() { PropertyId = SQLDataHelper.GetInt(reader, "PropertyId"), PropertyGroupId = SQLDataHelper.GetInt(reader, "PropertyGroupId"), GroupName = SQLDataHelper.GetString(reader, "GroupName"), PropertyName = SQLDataHelper.GetString(reader, "PropertyName"), Type = SQLDataHelper.GetInt(reader, "Type"), }, new SqlParameter("@CategoryId", categoryId))); }
/// <summary> /// return child categories by parent categoryId /// </summary> /// <param name="categoryId"></param> /// /// <param name="hasProducts"></param> /// <returns></returns> public static IList <Category> GetChildCategoriesByCategoryId(int categoryId, bool hasProducts) { return(SQLDataAccess.ExecuteReadList( "[Catalog].[sp_GetChildCategoriesByParentID]", CommandType.StoredProcedure, reader => { var category = new Category { CategoryId = SQLDataHelper.GetInt(reader, "CategoryId"), Name = SQLDataHelper.GetString(reader, "Name"), //Picture = SQLDataHelper.GetString(reader, "Picture"), SortOrder = SQLDataHelper.GetInt(reader, "SortOrder"), ParentCategoryId = SQLDataHelper.GetInt(reader, "ParentCategory"), ProductsCount = SQLDataHelper.GetInt(reader, "Products_Count"), TotalProductsCount = SQLDataHelper.GetInt(reader, "Total_Products_Count"), Description = SQLDataHelper.GetString(reader, "Description"), BriefDescription = SQLDataHelper.GetString(reader, "BriefDescription"), Enabled = SQLDataHelper.GetBoolean(reader, "Enabled"), DisplayStyle = SQLDataHelper.GetString(reader, "DisplayStyle"), //MiniPicture = SQLDataHelper.GetString(reader, "MiniPicture"), UrlPath = SQLDataHelper.GetString(reader, "UrlPath"), ParentsEnabled = SQLDataHelper.GetBoolean(reader, "HirecalEnabled") }; var childCounts = SQLDataHelper.GetInt(reader, "ChildCategories_Count"); category.HasChild = childCounts > 0; category.Picture = new Photo(0, category.CategoryId, PhotoType.CategoryBig) { PhotoName = SQLDataHelper.GetString(reader, "Picture") }; category.MiniPicture = new Photo(0, category.CategoryId, PhotoType.CategorySmall) { PhotoName = SQLDataHelper.GetString(reader, "MiniPicture") }; return category; }, new SqlParameter("@ParentCategoryID", categoryId), new SqlParameter("@hasProducts", hasProducts), new SqlParameter("@bigType", PhotoType.CategoryBig.ToString()), new SqlParameter("@smallType", PhotoType.CategorySmall.ToString()))); }
/// <summary> /// Get related products /// </summary> /// <param name="productId"></param> /// <param name="relatedType"></param> /// <returns></returns> public static List <Product> GetRelatedProducts(int productId, RelatedType relatedType) { List <Product> res = SQLDataAccess.ExecuteReadList <Product>("[Catalog].[sp_GetRelatedProducts]", CommandType.StoredProcedure, reader => new Product { ProductId = SQLDataHelper.GetInt(reader, "ProductId"), ArtNo = SQLDataHelper.GetString(reader, "ArtNo"), Photo = SQLDataHelper.GetString(reader, "Photo"), PhotoDesc = SQLDataHelper.GetString(reader, "PhotoDesc"), Name = SQLDataHelper.GetString(reader, "Name"), BriefDescription = SQLDataHelper.GetString(reader, "BriefDescription"), Discount = SQLDataHelper.GetDecimal(reader, "Discount"), UrlPath = SQLDataHelper.GetString(reader, "UrlPath"), }, new SqlParameter("@ProductID", productId), new SqlParameter("@RelatedType", (int)relatedType), new SqlParameter("@Type", PhotoType.Product.ToString()) ); return(res); }
private IEnumerable <SiteMapData> GetData() { string sqlcommand = "SELECT [CategoryId] as Id, [UrlPath], 'category' as Type, GetDate() as Lastmod FROM [Catalog].[Category] WHERE [Enabled] = 1 and HirecalEnabled =1 and CategoryID <> 0"; sqlcommand += " union "; sqlcommand += @" SELECT [Product].[ProductID] as Id , [Product].[UrlPath], 'product' as Type ,[Product].[DateModified] as Lastmod FROM [Catalog].[Product] " + "INNER JOIN [Catalog].[ProductCategories] ON [Catalog].[Product].[ProductID] = [Catalog].[ProductCategories].[ProductID] " + "INNER JOIN [Catalog].[Category] ON [Catalog].[Category].[CategoryID] = [Catalog].[ProductCategories].[CategoryID] " + "AND [Catalog].[Category].[Enabled] = 1 WHERE [Product].[Enabled] = 1 and [Product].[CategoryEnabled] = 1 and (select Count(CategoryID) from Catalog.ProductCategories where ProductID=[Product].[ProductID])<> 0 "; if (SettingsDesign.NewsVisibility) { sqlcommand += " union "; sqlcommand += "SELECT [NewsID] as Id, News.[UrlPath], 'news' as Type , [AddingDate] as LastMod FROM [Settings].[News]"; } sqlcommand += " union "; sqlcommand += "SELECT [StaticPageID] as Id, [UrlPath], 'page' as Type, [ModifyDate] as Lastmod FROM [CMS].[StaticPage] where IndexAtSiteMap=1 and enabled=1"; sqlcommand += " union "; sqlcommand += "SELECT [BrandID] as Id, [UrlPath], 'brand' as Type, GetDate() as Lastmod FROM [Catalog].[Brand] where enabled=1"; return(SQLDataAccess.ExecuteReadList(sqlcommand, CommandType.Text, GetSiteMapDataFromReader)); }
public static IList <PropertyValue> GetPropertyValuesByCategories(int categoryId, bool useDepth) { return(SQLDataAccess.ExecuteReadList <PropertyValue>( "[Catalog].[sp_GetPropertyInFilter]", CommandType.StoredProcedure, reader => new PropertyValue { PropertyValueId = SQLDataHelper.GetInt(reader, "PropertyValueID"), PropertyId = SQLDataHelper.GetInt(reader, "PropertyID"), Value = SQLDataHelper.GetString(reader, "Value"), Property = new Property { PropertyId = SQLDataHelper.GetInt(reader, "PropertyID"), Name = SQLDataHelper.GetString(reader, "PropertyName"), SortOrder = SQLDataHelper.GetInt(reader, "PropertySortOrder"), Expanded = SQLDataHelper.GetBoolean(reader, "PropertyExpanded"), Type = SQLDataHelper.GetInt(reader, "PropertyType"), Unit = SQLDataHelper.GetString(reader, "PropertyUnit") } }, new SqlParameter("@categoryId", categoryId), new SqlParameter("@useDepth", useDepth))); }
public static List <Brand> GetBrandsByProductOnMain(ProductOnMain.TypeFlag type) { var subCmd = string.Empty; switch (type) { case ProductOnMain.TypeFlag.New: subCmd = "New=1"; break; case ProductOnMain.TypeFlag.Bestseller: subCmd = "Bestseller=1"; break; case ProductOnMain.TypeFlag.Discount: subCmd = "Discount>0"; break; case ProductOnMain.TypeFlag.OnSale: subCmd = "OnSale=1"; break; case ProductOnMain.TypeFlag.Recomended: subCmd = "Recomended=1"; break; } string cmd = "Select Brand.BrandID, Brand.BrandName, UrlPath, Brand.SortOrder from Catalog.Brand where BrandID in (select BrandID from Catalog.Product where " + subCmd + " ) and enabled=1 order by Brand.BrandName"; return(SQLDataAccess.ExecuteReadList <Brand>(cmd, CommandType.Text, reader => new Brand { BrandId = SQLDataHelper.GetInt(reader, "BrandID"), Name = SQLDataHelper.GetString(reader, "BrandName"), UrlPath = SQLDataHelper.GetString(reader, "UrlPath") })); }
public static List <OptionItem> GetCustomOptionItems(int customOptionId) { return(SQLDataAccess.ExecuteReadList <OptionItem>("[Catalog].[sp_GetOptionsByCustomOptionId]", CommandType.StoredProcedure, GetOptionItemFromReader, new SqlParameter("@CustomOptionId", customOptionId))); }
public static List <CustomerGroup> GetCustomerGroupList() { List <CustomerGroup> customerGroupList = SQLDataAccess.ExecuteReadList <CustomerGroup>("SELECT * FROM [Customers].[CustomerGroup] order by GroupDiscount", CommandType.Text, GetCustomerGroupFromReader); return(customerGroupList); }
public static List <Currency> GetAllCurrencies() { return(SQLDataAccess.ExecuteReadList <Currency>("SELECT Catalog.Currency.* FROM Catalog.Currency", CommandType.Text, GetCurrencyFromReader)); }
public static List <Color> GetColors() { return(SQLDataAccess.ExecuteReadList <Color>("Select * from Catalog.Color", CommandType.Text, GetFromReader)); }
public static List<Review> GetReviewChildren(int reviewId) { return SQLDataAccess.ExecuteReadList<Review>("SELECT * FROM [CMS].[Review] WHERE [ParentId] = @ParentId", CommandType.Text, GetFromReader, new SqlParameter("@ParentId", reviewId)); }
public static List <GiftCertificate> GetCertificates() { List <GiftCertificate> certificates = SQLDataAccess.ExecuteReadList <GiftCertificate>("[Catalog].[sp_GetCertificates]", CommandType.StoredProcedure, GetFromReader); return(certificates); }
public static List<Brand> GetBrands() { return SQLDataAccess.ExecuteReadList<Brand>("Select * from Catalog.Brand order by BrandName", CommandType.Text, GetBrandFromReader); }
public static List <CallbackCustomer> GetCallbackCustomers() { return(SQLDataAccess.ExecuteReadList <CallbackCustomer>("SELECT * FROM [Module].[" + _moduleName + "] ORDER BY [DateAdded] DESC", CommandType.Text, GetCallbackCustomerFromReader)); }
/// <summary> /// get categories by productId /// </summary> /// <param name="productId"></param> /// <returns></returns> public static List <Category> GetCategoriesByProductId(int productId) { var res = SQLDataAccess.ExecuteReadList("[Catalog].[sp_GetProductCategories]", CommandType.StoredProcedure, CategoryService.GetCategoryFromReader, new SqlParameter("@ProductID", productId)); return(res); }
public static IEnumerable <TaxElement> GetCertificateTaxes() { return(SQLDataAccess.ExecuteReadList <TaxElement>( "SELECT * FROM [Settings].[GiftCertificateTaxes] inner join Catalog.Tax on GiftCertificateTaxes.TaxID = Tax.TaxID", CommandType.Text, ReadTax)); }
public static List <TaxElement> GetTaxes() { return(SQLDataAccess.ExecuteReadList("SELECT * FROM [Catalog].[Tax]", CommandType.Text, ReadTax)); }