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));
}