public static DataTable GetAdminProductsByType(TypeFlag type, int count)
{
string sqlCmd;
switch (type)
{
case TypeFlag.Bestseller:
sqlCmd = "select Top(@count) Product.ProductId, Name from Catalog.Product where Bestseller=1 order by SortBestseller";
break;
case TypeFlag.New:
sqlCmd = "select Top(@count) Product.ProductId, Name from Catalog.Product where New=1 order by SortNew";
break;
case TypeFlag.Discount:
sqlCmd = "select Top(@count) Product.ProductId, Name from Catalog.Product where Discount > 0 order by SortDiscount";
break;
case TypeFlag.OnSale:
sqlCmd = "select Top(@count) Product.ProductId, Name from Catalog.Product where OnSale=1 order by SortOnSale";
break;
case TypeFlag.Recomended:
sqlCmd = "select Top(@count) Product.ProductId, Name from Catalog.Product where Recomended=1 order by SortOnRecomended";
break;
default:
throw new NotImplementedException();
}
return(SQLDataAccess.ExecuteTable(sqlCmd, CommandType.Text, new SqlParameter {
ParameterName = "@count", Value = count
}));
}
/// <summary>
/// Statistic by frequency search
/// </summary>
/// <returns></returns>
public static DataTable GetFrequencySearchStatistic(DateTime date)
{
return(SQLDataAccess.ExecuteTable(
"SELECT [Request], COUNT([Request]) AS numOfRequest, [ResultCount],[SearchTerm],[Description] FROM [Statistic].[SearchStatistic] WHERE [Date] >= Convert(date, @Date) GROUP BY [Request],[ResultCount],[SearchTerm],[Description] ORDER BY numOfRequest DESC",
CommandType.Text,
new SqlParameter("@Date", date)));
}
public static DataTable GetStoreReviews()
{
return(SQLDataAccess.ExecuteTable(
"SELECT * FROM [Module].[StoreReview] ORDER BY [DateAdded] DESC",
CommandType.Text
));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static DataTable GetHistorySearchStatistic(int numRows)
{
return(SQLDataAccess.ExecuteTable(
"SELECT TOP(@NumRows) * FROM [Statistic].[SearchStatistic] ORDER BY Date DESC",
CommandType.Text,
new SqlParameter("@NumRows", numRows)));
}
/// <summary>
/// return shipping service by his id
/// </summary>
/// <param name="shippingMethodId"></param>
/// <returns>ShippingMethod</returns>
public static DataTable GetShippingPayments(int shippingMethodId)
{
return
(SQLDataAccess.ExecuteTable(
"SELECT [PaymentMethod].[PaymentMethodID], [PaymentMethod].[Name], (Select Count(PaymentMethodID) From [Order].[ShippingPayments] Where PaymentMethodID = [PaymentMethod].[PaymentMethodID] AND ShippingMethodID = @ShippingMethodID) as [Use] FROM [Order].[PaymentMethod]",
CommandType.Text,
new SqlParameter("@ShippingMethodID", shippingMethodId)));
}
protected void btnShowBrokenCategoriesClick(object sender, EventArgs e)
{
lvBrokenCcategories.DataSource =
SQLDataAccess.ExecuteTable(
"Select Name From Catalog.Category as chiledCat Where (Select COUNT(CategoryID) from Catalog.Category Where CategoryID = chiledCat.ParentCategory) = 0",
CommandType.Text);
lvBrokenCcategories.DataBind();
}
public static DataTable GetTopProductsBySum()
{
return(SQLDataAccess.ExecuteTable(
"Select Top(10) ProductID, Name, ArtNo, " +
"(Select [UrlPath] From [Catalog].[Product] Where [ProductId] = [OrderItems].[ProductID]) as UrlPath, " +
" Sum([Amount]*[Price]) as Summary " +
"From [Order].[OrderItems] " +
"Group By [ProductID], [Name], [ArtNo] " +
"Order By Summary Desc",
CommandType.Text));
}
public static DataTable GetProductsByType(TypeFlag type, int count)
{
string sqlCmd = "select Top(@count) Product.ProductId, Product.ArtNo, Name, BriefDescription, " +
"(CASE WHEN Offer.ColorID is not null THEN (Select TOP(1) PhotoId From [Catalog].[Photo] WHERE ([Photo].ColorID = Offer.ColorID or [Photo].ColorID is null) and [Product].[ProductID] = [Photo].[ObjId] and Type=@Type order by main desc, PhotoSortOrder) ELSE (Select TOP(1) PhotoId From [Catalog].[Photo] WHERE [Product].[ProductID] = [Photo].[ObjId] and Type=@Type order by main desc, PhotoSortOrder) END) AS PhotoId, " +
"(CASE WHEN Offer.ColorID is not null THEN (Select TOP(1) PhotoName From [Catalog].[Photo] WHERE ([Photo].ColorID = Offer.ColorID or [Photo].ColorID is null) and [Product].[ProductID] = [Photo].[ObjId] and Type=@Type order by main desc, PhotoSortOrder) ELSE (Select TOP(1) PhotoName From [Catalog].[Photo] WHERE [Product].[ProductID] = [Photo].[ObjId] and Type=@Type order by main desc, PhotoSortOrder) END) AS Photo, " +
"(CASE WHEN Offer.ColorID is not null THEN (Select TOP(1) [Photo].[Description] From [Catalog].[Photo] WHERE ([Photo].ColorID = Offer.ColorID or [Photo].ColorID is null) and [Product].[ProductID] = [Photo].[ObjId] and Type=@Type) ELSE (Select TOP(1) [Photo].[Description] From [Catalog].[Photo] WHERE [Product].[ProductID] = [Photo].[ObjId] and Type=@Type AND [Photo].[Main] = 1) END) AS PhotoDesc, " +
"Discount, Ratio, RatioID, AllowPreOrder, Recomended, New, BestSeller, OnSale, UrlPath, " +
"ShoppingCartItemID, Price, " +
"(Select Max(Offer.Amount) from catalog.Offer Where ProductId=[Product].[ProductID]) as Amount," +
" Offer.OfferID, Offer.ColorID, MinAmount, " +
(SettingsCatalog.ComplexFilter ?
"(select [Settings].[ProductColorsToString]([Product].[ProductID])) as Colors":
"null as Colors") +
" from Catalog.Product " +
"LEFT JOIN [Catalog].[Offer] ON [Product].[ProductID] = [Offer].[ProductID] and Offer.main=1 " +
"LEFT JOIN Catalog.Photo on Product.ProductID=Photo.ObjId and Type=@Type and Photo.main=1 " +
"LEFT JOIN [Catalog].[ShoppingCart] ON [Catalog].[ShoppingCart].[OfferID] = [Catalog].[Offer].[OfferID] AND [Catalog].[ShoppingCart].[ShoppingCartType] = @ShoppingCartType AND [ShoppingCart].[CustomerID] = @CustomerId " +
"Left JOIN [Catalog].[Ratio] on Product.ProductId=Ratio.ProductID and Ratio.CustomerId=@CustomerId " +
"where {0} and Enabled=1 and CategoryEnabled=1 and [Settings].[CountCategoriesByProduct](Product.ProductID) > 0 order by {1}";
switch (type)
{
case TypeFlag.Bestseller:
sqlCmd = string.Format(sqlCmd, "Bestseller=1", "SortBestseller");
break;
case TypeFlag.New:
sqlCmd = string.Format(sqlCmd, "New=1", "SortNew, [DateModified] DESC");
break;
case TypeFlag.Discount:
sqlCmd = string.Format(sqlCmd, "Discount>0", "SortDiscount");
break;
case TypeFlag.OnSale:
sqlCmd = string.Format(sqlCmd, "OnSale=1", "[DateModified] DESC");
break;
case TypeFlag.Recomended:
sqlCmd = string.Format(sqlCmd, "Recomended=1", "[DateModified] DESC");
break;
default:
throw new NotImplementedException();
}
return(SQLDataAccess.ExecuteTable(sqlCmd, CommandType.Text,
new SqlParameter {
ParameterName = "@count", Value = count
},
new SqlParameter("@CustomerId", CustomerContext.CustomerId.ToString()),
new SqlParameter("@Type", PhotoType.Product.ToString()),
new SqlParameter("@ShoppingCartType", (int)ShoppingCartType.Compare)));
}
public static DataTable GetTopCustomersBySumPrice()
{
return(SQLDataAccess.ExecuteTable(
"Select Top(10) [CustomerID], [Email], " +
"(Select top 1 [FirstName]+' '+[LastName] From [Order].[OrderCustomer] as c Where c.[CustomerID] = [OrderCustomer].[CustomerID]) as fio, " +
"Sum([Order].[Sum]) as Summary " +
"From [Order].[OrderCustomer] " +
"Join [Order].[Order] On [Order].[OrderID] = [OrderCustomer].[OrderId] " +
"Group By [CustomerID], Email " +
"Order By Summary Desc",
CommandType.Text));
}
protected DataTable GetCurrencies()
{
var dataTable = new DataTable();
try
{
dataTable = SQLDataAccess.ExecuteTable("SELECT CurrencyValue, CurrencyIso3 FROM [Catalog].[Currency];", CommandType.Text);
}
catch (Exception ex)
{
Debug.LogError(ex);
}
return(dataTable);
}
public static DataTable GetProductsByType(TypeFlag type, int count)
{
string sqlCmd;
switch (type)
{
//Changed by Evgeni to order by random
//case TypeFlag.Bestseller:
// sqlCmd = "select Top(@count) Product.ProductId, ArtNo, Name, PhotoName as Photo, [Photo].[Description] AS PhotoDesc, Discount, Ratio, RatioID, OrderByRequest, Recomended, New, BestSeller, OnSale, UrlPath, ItemId, Price, Offer.Amount from Catalog.Product LEFT JOIN [Catalog].[Offer] ON [Product].[ProductID] = [Offer].[ProductID] and offerListid=6 left Join Catalog.Photo on Product.ProductID=Photo.ObjId and Type=@Type and main=1 LEFT JOIN [Catalog].[ShoppingCart] ON [Catalog].[ShoppingCart].[EntityID] = [Catalog].[Product].[ProductID] AND [Catalog].[ShoppingCart].[ShoppingCartTypeId] = 3 AND [ShoppingCart].[CustomerID] = @CustomerId Left JOIN [Catalog].[Ratio] on Product.ProductId=Ratio.ProductID and Ratio.CustomerId=@CustomerId where Bestseller=1 and Enabled=1 and HirecalEnabled=1 and [Settings].[CountCategoriesByProduct](Product.ProductID) > 0 order by SortBestseller";
// break;
//case TypeFlag.New:
// sqlCmd = "select Top(@count) Product.ProductId, ArtNo, Name, PhotoName as Photo, [Photo].[Description] AS PhotoDesc, Discount, Ratio, RatioID, OrderByRequest, Recomended, New, BestSeller, OnSale, UrlPath, ItemId, Price, Offer.Amount from Catalog.Product LEFT JOIN [Catalog].[Offer] ON [Product].[ProductID] = [Offer].[ProductID] and offerListid=6 left Join Catalog.Photo on Product.ProductID=Photo.ObjId and Type=@Type and main=1 LEFT JOIN [Catalog].[ShoppingCart] ON [Catalog].[ShoppingCart].[EntityID] = [Catalog].[Product].[ProductID] AND [Catalog].[ShoppingCart].[ShoppingCartTypeId] = 3 AND [ShoppingCart].[CustomerID] = @CustomerId Left JOIN [Catalog].[Ratio] on Product.ProductId= Ratio.ProductID and Ratio.CustomerId=@CustomerId where New=1 and Enabled=1 and HirecalEnabled=1 and [Settings].[CountCategoriesByProduct](Product.ProductID) > 0 order by SortNew";
// break;
//case TypeFlag.Discount:
// sqlCmd = "select Top(@count) Product.ProductId, ArtNo, Name, PhotoName as Photo, [Photo].[Description] AS PhotoDesc, Discount, Ratio, RatioID, OrderByRequest, Recomended, New, BestSeller, OnSale, UrlPath, ItemId, Price, Offer.Amount from Catalog.Product LEFT JOIN [Catalog].[Offer] ON [Product].[ProductID] = [Offer].[ProductID] and offerListid=6 left Join Catalog.Photo on Product.ProductID=Photo.ObjId and Type=@Type and main=1 LEFT JOIN [Catalog].[ShoppingCart] ON [Catalog].[ShoppingCart].[EntityID] = [Catalog].[Product].[ProductID] AND [Catalog].[ShoppingCart].[ShoppingCartTypeId] = 3 AND [ShoppingCart].[CustomerID] = @CustomerId Left JOIN [Catalog].[Ratio] on Product.ProductId= Ratio.ProductID and Ratio.CustomerId=@CustomerId where Discount > 0 and Enabled=1 and HirecalEnabled=1 and [Settings].[CountCategoriesByProduct](Product.ProductID) > 0 order by SortDiscount";
//sqlCmd = "select Top(@count) Product.ProductId, ArtNo, Name, PhotoName as Photo, [Photo].[Description] AS PhotoDesc, Discount, Ratio, RatioID, OrderByRequest, Recomended, New, BestSeller, OnSale, UrlPath, ItemId, Price, Offer.Amount, Offer.MinAmount from Catalog.Product LEFT JOIN [Catalog].[Offer] ON [Product].[ProductID] = [Offer].[ProductID] and offerListid=6 left Join Catalog.Photo on Product.ProductID=Photo.ObjId and Type=@Type and main=1 LEFT JOIN [Catalog].[ShoppingCart] ON [Catalog].[ShoppingCart].[EntityID] = [Catalog].[Product].[ProductID] AND [Catalog].[ShoppingCart].[ShoppingCartTypeId] = 3 AND [ShoppingCart].[CustomerID] = @CustomerId Left JOIN [Catalog].[Ratio] on Product.ProductId=Ratio.ProductID and Ratio.CustomerId=@CustomerId where Bestseller=1 and Enabled=1 and HirecalEnabled=1 and [Settings].[CountCategoriesByProduct](Product.ProductID) > 0 order by SortBestseller";
// break;
case TypeFlag.Bestseller:
sqlCmd = "select Top(@count) Product.ProductId, ArtNo, Name, PhotoName as Photo, [Photo].[Description] AS PhotoDesc ,Discount, Ratio, RatioID, OrderByRequest, Recomended, New, BestSeller, OnSale, UrlPath, ItemId, Price, Offer.Amount, Offer.MinAmount from Catalog.Product LEFT JOIN [Catalog].[Offer] ON [Product].[ProductID] = [Offer].[ProductID] and offerListid=6 left Join Catalog.Photo on Product.ProductID=Photo.ObjId and Type=@Type and main=1 LEFT JOIN [Catalog].[ShoppingCart] ON [Catalog].[ShoppingCart].[EntityID] = [Catalog].[Product].[ProductID] AND [Catalog].[ShoppingCart].[ShoppingCartTypeId] = 3 AND [ShoppingCart].[CustomerID] = @CustomerId Left JOIN [Catalog].[Ratio] on Product.ProductId=Ratio.ProductID and Ratio.CustomerId=@CustomerId where Bestseller=1 and Enabled=1 and HirecalEnabled=1 and [Settings].[CountCategoriesByProduct](Product.ProductID) > 0 ORDER BY SortBestseller";
break;
case TypeFlag.New:
sqlCmd = "select Top(@count) Product.ProductId, ArtNo, Name, PhotoName as Photo, [Photo].[Description] AS PhotoDesc,Discount, Ratio, RatioID, OrderByRequest, Recomended, New, BestSeller, OnSale, UrlPath, ItemId, Price, Offer.Amount, Offer.MinAmount from Catalog.Product LEFT JOIN [Catalog].[Offer] ON [Product].[ProductID] = [Offer].[ProductID] and offerListid=6 left Join Catalog.Photo on Product.ProductID=Photo.ObjId and Type=@Type and main=1 LEFT JOIN [Catalog].[ShoppingCart] ON [Catalog].[ShoppingCart].[EntityID] = [Catalog].[Product].[ProductID] AND [Catalog].[ShoppingCart].[ShoppingCartTypeId] = 3 AND [ShoppingCart].[CustomerID] = @CustomerId Left JOIN [Catalog].[Ratio] on Product.ProductId= Ratio.ProductID and Ratio.CustomerId=@CustomerId where New=1 and Enabled=1 and HirecalEnabled=1 and [Settings].[CountCategoriesByProduct](Product.ProductID) > 0 order by SortNew";
break;
case TypeFlag.Discount:
sqlCmd = "select Top(@count) Product.ProductId, ArtNo, Name, PhotoName as Photo, [Photo].[Description] AS PhotoDesc,Discount, Ratio, RatioID, OrderByRequest, Recomended, New, BestSeller, OnSale, UrlPath, ItemId, Price, Offer.Amount, Offer.MinAmount from Catalog.Product LEFT JOIN [Catalog].[Offer] ON [Product].[ProductID] = [Offer].[ProductID] and offerListid=6 left Join Catalog.Photo on Product.ProductID=Photo.ObjId and Type=@Type and main=1 LEFT JOIN [Catalog].[ShoppingCart] ON [Catalog].[ShoppingCart].[EntityID] = [Catalog].[Product].[ProductID] AND [Catalog].[ShoppingCart].[ShoppingCartTypeId] = 3 AND [ShoppingCart].[CustomerID] = @CustomerId Left JOIN [Catalog].[Ratio] on Product.ProductId= Ratio.ProductID and Ratio.CustomerId=@CustomerId where Discount > 0 and Enabled=1 and HirecalEnabled=1 and [Settings].[CountCategoriesByProduct](Product.ProductID) > 0 order by SortDiscount";
break;
default:
throw new NotImplementedException();
//
}
return(SQLDataAccess.ExecuteTable(sqlCmd, CommandType.Text, new SqlParameter {
ParameterName = "@count", Value = count
},
new SqlParameter("@CustomerId", CustomerSession.CustomerId.ToString()),
new SqlParameter("@Type", PhotoType.Product.ToString())));
}
public static DataTable ModuleExecuteTable(string query, CommandType commandType, params SqlParameter[] parameters)
{
return(SQLDataAccess.ExecuteTable(query, commandType, parameters));
}
