public static void CreateIndexFromDb()
{
using (var luceneDirectory = LuceneDirectory)
using (var analyzer = new RussianAnalyzer(Version))
using (var writer = new IndexWriter(luceneDirectory, analyzer, true, IndexWriter.MaxFieldLength.UNLIMITED))
{
writer.SetRAMBufferSizeMB(20);
// add data to lucene search index (replaces older entries if any)
var source = SQLDataAccess.ExecuteReadIEnumerable("select Product.productId, Product.ArtNo, Name, " +
// "(select ' ' + Offer.Artno from Catalog.Offer where Offer.ProductID=Product.productid FOR XML path('')) as OfferArtno, " +
@"(select Value from[Catalog].[PropertyValue] join[Catalog].[ProductPropertyValue] on [Catalog].[PropertyValue].PropertyValueID =[Catalog].[ProductPropertyValue].PropertyValueID
where[PropertyID] = 333
and
[Catalog].[ProductPropertyValue].ProductID =[Catalog].[Product].ProductId) as Article " +
"from [Catalog].[Product] where CategoryEnabled=1 and Enabled=1", CommandType.Text,
reader => new SampleData(
SQLDataHelper.GetInt(reader, "productId"),
//SQLDataHelper.GetString(reader, "ArtNo") + " " + SQLDataHelper.GetString(reader, "Article") ,
SQLDataHelper.GetString(reader, "Article"),
SQLDataHelper.GetString(reader["Name"])
)
);
foreach (var item in source)
{
AddToLuceneIndex(item, writer);
}
// close handles
writer.Optimize();
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static IEnumerable <AdvMenuItem> GetMenuItems(EMenuType menuType)
{
return(SQLDataAccess.ExecuteReadIEnumerable <AdvMenuItem>(
string.Format("SELECT * FROM {0}", MenuTypeTables[menuType]),
CommandType.Text,
GetMenuItemFromReader));
}
public static IEnumerable<Review> GetCheckedReviews(int entityId, EntityType entityType)
{
return SQLDataAccess.ExecuteReadIEnumerable<Review>("SELECT * FROM [CMS].[Review] WHERE [EntityId] = @EntityId AND [Type] = @entityType AND [Checked] = 1",
CommandType.Text, GetFromReader,
new SqlParameter("@EntityId", entityId),
new SqlParameter("@entityType", (int)entityType));
}
public static IEnumerable <Carousel> GetCarousel(int carouselID)
{
return(SQLDataAccess.ExecuteReadIEnumerable <Carousel>("sp_GetCarousel", CommandType.StoredProcedure, GetCarouselFromReader,
new SqlParameter {
ParameterName = "@CarouselID", Value = carouselID
}));
}
public static IEnumerable <StaticPage> GetChildStaticPages(int parentId, bool enabledOnly)
{
string command = enabledOnly
? "SELECT * FROM [CMS].[StaticPage] WHERE [ParentID] = @ParentID and enabled=1 Order by SortOrder"
: "SELECT * FROM [CMS].[StaticPage] WHERE [ParentID] = @ParentID Order by SortOrder";
return(SQLDataAccess.ExecuteReadIEnumerable <StaticPage>(command, CommandType.Text, GetStaticPageFromReader, new SqlParameter("@ParentID", parentId)));
}
public static IEnumerable <NewsItem> GetNewsByCategoryID(int categoryID)
{
return(SQLDataAccess.ExecuteReadIEnumerable <NewsItem>(
"SELECT * FROM [Settings].[News] WHERE NewsCategoryID=@NewsCategoryID ORDER BY [AddingDate], [NewsID] DESC",
CommandType.Text,
GetNewsFromReader,
new SqlParameter("@NewsCategoryID", categoryID)));
}
public static IEnumerable <PaymentMethod> GetAllPaymentMethods(bool onlyEnabled)
{
return(SQLDataAccess.ExecuteReadIEnumerable <PaymentMethod>(
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())));
}
/// <summary>
/// return list of photos by type
/// </summary>
/// <param name="objId"></param>
/// <param name="type"></param>
/// <returns></returns>
public static IEnumerable <Photo> GetPhotos(int objId, PhotoType type)
{
var list = SQLDataAccess.ExecuteReadIEnumerable <Photo>("SELECT * FROM [Catalog].[Photo] WHERE [objId] = @objId and type=@type ORDER BY [PhotoSortOrder]",
CommandType.Text, GetPhotoFromReader,
new SqlParameter("@objId", objId),
new SqlParameter("@type", type.ToString()));
return(list);
}
/// <summary>
///
/// </summary>
/// <param name="mItemParentId"></param>
/// <param name="menuType"></param>
/// <returns></returns>
public static IEnumerable <AdvMenuItem> GetChildMenuItemsByParentId(int mItemParentId, EMenuType menuType)
{
return(SQLDataAccess.ExecuteReadIEnumerable <AdvMenuItem>(
string.Format("SELECT MenuItemID, MenuItemParentID, MenuItemName, MenuItemIcon, MenuItemUrlPath, MenuItemUrlType, SortOrder, ShowMode, Enabled, Blank, NoFollow, (SELECT Count(MenuItemID) FROM {0} AS c WHERE c.MenuItemParentID = p.MenuItemID) as Child_Count FROM {0} as p WHERE {1} order by [SortOrder]", MenuTypeTables[menuType], mItemParentId == 0 ? "[MenuItemParentID] is Null" : "[MenuItemParentID] = " + mItemParentId),
CommandType.Text,
(reader) =>
{
var mItem = GetMenuItemFromReader(reader);
mItem.HasChild = SQLDataHelper.GetInt(reader, "Child_Count") > 0;
return mItem;
}));
}
public static IEnumerable <Carousel> GetAllCarouselsMainPage()
{
return(SQLDataAccess.ExecuteReadIEnumerable <Carousel>("Select PhotoId,ObjId,URL,PhotoName From CMS.Carousel left join Catalog.Photo on Photo.ObjId=Carousel.CarouselID and Type=@Type Where Enabled=1 Order by SortOrder",
CommandType.Text,
reader => new Carousel
{
URL = SQLDataHelper.GetString(reader, "URL"),
Picture = new Photo(SQLDataHelper.GetInt(reader, "PhotoId"), SQLDataHelper.GetInt(reader, "ObjId"), PhotoType.Carousel)
{
PhotoName = SQLDataHelper.GetString(reader, "PhotoName")
}
}, new SqlParameter("@Type", PhotoType.Carousel.ToString())));
}
protected IEnumerable <ExportFeedCategories> GetCategories(string moduleName)
{
return(SQLDataAccess.ExecuteReadIEnumerable <ExportFeedCategories>("[Settings].[sp_GetExportFeedCategories]",
CommandType.StoredProcedure,
reader => new ExportFeedCategories
{
Id = SQLDataHelper.GetInt(reader, "CategoryID"),
ParentCategory = SQLDataHelper.GetInt(reader, "ParentCategory"),
Name = SQLDataHelper.GetString(reader, "Name")
},
new SqlParameter("@moduleName", moduleName),
new SqlParameter("@onlyCount", false)));
}
public static IEnumerable <NewsCategory> GetNewsCategories()
{
return(SQLDataAccess.ExecuteReadIEnumerable(
"SELECT *, (Select Count(NewsID) FROM [Settings].[News] WHERE NewsCategoryID = [Settings].[NewsCategory].[NewsCategoryID]) as CountNews FROM [Settings].[NewsCategory]",
CommandType.Text,
reader => new NewsCategory
{
NewsCategoryID = SQLDataHelper.GetInt(reader, "NewsCategoryID"),
Name = SQLDataHelper.GetString(reader, "Name"),
SortOrder = SQLDataHelper.GetInt(reader, "SortOrder"),
CountNews = SQLDataHelper.GetInt(reader, "CountNews"),
UrlPath = SQLDataHelper.GetString(reader, "UrlPath")
}));
}
/// <summary>
/// return list of filename to delete
/// </summary>
/// <param name="objId"></param>
/// <param name="type"></param>
/// <returns></returns>
public static IEnumerable <string> GetNamePhotos(int objId, PhotoType type)
{
if (objId == 0)
{
return(SQLDataAccess.ExecuteReadIEnumerable <string>("SELECT PhotoName FROM [Catalog].[Photo] WHERE type=@type",
CommandType.Text, reader => SQLDataHelper.GetString(reader, "PhotoName"),
new SqlParameter("@type", type.ToString())));
}
return(SQLDataAccess.ExecuteReadIEnumerable <string>("SELECT PhotoName FROM [Catalog].[Photo] WHERE [objId] = @objId and type=@type",
CommandType.Text, reader => SQLDataHelper.GetString(reader, "PhotoName"),
new SqlParameter("@objId", objId),
new SqlParameter("@type", type.ToString())));
}
private static IList <SiteMapData> GetSiteMapData()
{
var res = new List <SiteMapData>();
res.AddRange(SQLDataAccess.ExecuteReadIEnumerable <SiteMapData>("SELECT [CategoryId],[UrlPath] FROM [Catalog].[Category] WHERE [Enabled] = '1'", CommandType.Text, GetSiteMapDataFromReader));
res.AddRange(SQLDataAccess.ExecuteReadIEnumerable <SiteMapData>(
@"SELECT [Product].[ProductID], [Product].[DateModified],[UrlPath]
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 (select Count(CategoryID) from Catalog.ProductCategories where ProductID=[Product].[ProductID])<> 0",
CommandType.Text,
GetSiteMapDataFromReader));
res.AddRange(SQLDataAccess.ExecuteReadIEnumerable <SiteMapData>("SELECT [NewsID], [AddingDate],[UrlPath] FROM [Settings].[News]", CommandType.Text, GetSiteMapDataFromReader));
res.AddRange(SQLDataAccess.ExecuteReadIEnumerable <SiteMapData>("SELECT [StaticPageID],[ModifyDate],[UrlPath] FROM [CMS].[StaticPage] where IndexAtSiteMap=1", CommandType.Text, GetSiteMapDataFromReader));
return(res);
}
public static IEnumerable <PropertyValue> GetPropertyValuesByCategories(int categoryId, bool useDepth)
{
return(SQLDataAccess.ExecuteReadIEnumerable <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")
}
},
new SqlParameter("@categoryId", categoryId),
new SqlParameter("@useDepth", useDepth)
));
}
protected IEnumerable <ExportFeedProduts> GetProduts(string moduleName)
{
return(SQLDataAccess.ExecuteReadIEnumerable <ExportFeedProduts>("[Settings].[sp_GetExportFeedProducts]",
CommandType.StoredProcedure,
reader => new ExportFeedProduts
{
ProductID = SQLDataHelper.GetInt(reader, "ProductID"),
Amount = SQLDataHelper.GetInt(reader, "Amount"),
UrlPath = SQLDataHelper.GetString(reader, "UrlPath"),
Price = SQLDataHelper.GetDecimal(reader, "Price"),
Discount = SQLDataHelper.GetDecimal(reader, "Discount"),
ParentCategory = SQLDataHelper.GetInt(reader, "ParentCategory"),
Name = SQLDataHelper.GetString(reader, "Name"),
Description = SQLDataHelper.GetString(reader, "Description"),
BriefDescription = SQLDataHelper.GetString(reader, "BriefDescription"),
Photo = SQLDataHelper.GetString(reader, "Photo"),
//Added by Evgeni
BrandName = SQLDataHelper.GetString(reader, "BrandName"),
ArtNo = SQLDataHelper.GetString(reader, "ArtNo"),
},
new SqlParameter("@moduleName", moduleName),
new SqlParameter("@selectedCurrency", ExportFeed.GetModuleSetting(moduleName, "Currency")),
new SqlParameter("@onlyCount", false)));
}
/// <summary>
/// delete category by categoryId
/// </summary>
/// <param name="categoryId"></param>
/// <param name="updateCache">refresh cache</param>
/// <returns>return list of file namme image</returns>
private static IEnumerable <int> DeleteCategory(int categoryId, bool updateCache)
{
if (categoryId == 0)
{
throw new Exception("deleting Root catregory");
}
if (updateCache)
{
CacheManager.Remove(CacheNames.GetCategoryCacheObjectName(categoryId));
CacheManager.RemoveByPattern("MenuCatalog");
var cacheName = CacheNames.GetBottomMenuCacheObjectName();
if (CacheManager.Contains(cacheName))
{
CacheManager.Remove(cacheName);
}
}
return(SQLDataAccess.ExecuteReadIEnumerable("[Catalog].[sp_DeleteCategoryWithSubCategoies]",
CommandType.StoredProcedure,
reader => SQLDataHelper.GetInt(reader, "CategoryID"),
new SqlParameter("@id", categoryId)));
}
protected IEnumerable <ExportFeedProduts> GetProduts(string moduleName)
{
return(SQLDataAccess.ExecuteReadIEnumerable <ExportFeedProduts>("[Settings].[sp_GetExportFeedProducts]",
CommandType.StoredProcedure,
reader => new ExportFeedProduts
{
ProductId = SQLDataHelper.GetInt(reader, "ProductID"),
OfferId = SQLDataHelper.GetInt(reader, "OfferId"),
//Заменяем настоящий ArtNo на альтернативный из свойств
ArtNo = GetAltArtNoFromProperty(SQLDataHelper.GetInt(reader, "ProductID")),
Amount = SQLDataHelper.GetInt(reader, "Amount"),
UrlPath = SQLDataHelper.GetString(reader, "UrlPath"),
Price = SQLDataHelper.GetFloat(reader, "Price"),
ShippingPrice = SQLDataHelper.GetFloat(reader, "ShippingPrice"),
Discount = SQLDataHelper.GetFloat(reader, "Discount"),
ParentCategory = SQLDataHelper.GetInt(reader, "ParentCategory"),
Name = SQLDataHelper.GetString(reader, "Name"),
Description = SQLDataHelper.GetString(reader, "Description"),
BriefDescription = SQLDataHelper.GetString(reader, "BriefDescription"),
Photos = SQLDataHelper.GetString(reader, "Photos"),
SalesNote = SQLDataHelper.GetString(reader, "SalesNote"),
ColorId = SQLDataHelper.GetInt(reader, "ColorId"),
ColorName = SQLDataHelper.GetString(reader, "ColorName"),
SizeId = SQLDataHelper.GetInt(reader, "SizeId"),
SizeName = SQLDataHelper.GetString(reader, "SizeName"),
BrandName = SQLDataHelper.GetString(reader, "BrandName"),
Main = SQLDataHelper.GetBoolean(reader, "Main"),
GoogleProductCategory = SQLDataHelper.GetString(reader, "GoogleProductCategory"),
Gtin = SQLDataHelper.GetString(reader, "Gtin"),
Adult = SQLDataHelper.GetBoolean(reader, "Adult"),
ManufacturerWarranty = SQLDataHelper.GetBoolean(reader, "ManufacturerWarranty")
},
new SqlParameter("@moduleName", moduleName),
new SqlParameter("@selectedCurrency", ExportFeed.GetModuleSetting(moduleName, "Currency")),
new SqlParameter("@onlyCount", false)));
}
public static IEnumerable<Review> GetReviewList()
{
return SQLDataAccess.ExecuteReadIEnumerable<Review>("SELECT * FROM [CMS].[Review]", CommandType.Text, GetFromReader);
}
public static IEnumerable <StaticPage> GetAllStaticPages()
{
return(SQLDataAccess.ExecuteReadIEnumerable <StaticPage>("SELECT * FROM [CMS].[StaticPage] Order by SortOrder", CommandType.Text,
GetStaticPageFromReader));
}
public static IEnumerable <StaticPage> GetRootStaticPages()
{
return(SQLDataAccess.ExecuteReadIEnumerable <StaticPage>("SELECT * FROM [CMS].[StaticPage] Where ParentID is NULL OR [ParentID] = 0 Order by SortOrder", CommandType.Text,
GetStaticPageFromReader));
}
public static IEnumerable <RedirectSeo> GetRedirectsSeo()
{
return(SQLDataAccess.ExecuteReadIEnumerable <RedirectSeo>("SELECT * FROM [Settings].[Redirect]", CommandType.Text, GetRedirectSeoFromReader));
}
