private string GetOrderToken(int orderId)
{
return(SQLDataAccess.ExecuteScalar <string>(
"SELECT [Token] FROM [ModulePayment].[PayPalExpressCheckout] WHERE [OrderId] = @OrderId",
CommandType.Text,
new SqlParameter("@OrderId", orderId)));
}
public static bool IsPaymentNotUsed(int shippingMethodId, int paymentMethodId)
{
return(SQLDataAccess.ExecuteScalar <int>(
"SELECT Count(PaymentMethodID) FROM [Order].[ShippingPayments] WHERE ShippingMethodID = @ShippingMethodID AND PaymentMethodID = @PaymentMethodID",
CommandType.Text, new SqlParameter("@ShippingMethodID", shippingMethodId),
new SqlParameter("@PaymentMethodID", paymentMethodId)) > 0);
}
public static string GetModuleSetting(string moduleName, string settingName)
{
var result = SQLDataAccess.ExecuteScalar <string>("SELECT [Value] FROM [Settings].[Settings] WHERE [Name] = @settingName;",
CommandType.Text, new SqlParameter("@settingName", moduleName + settingName));
return(result);
}
public static int AddBrand(Brand brand)
{
brand.BrandId = Convert.ToInt32(SQLDataAccess.ExecuteScalar(
"[Catalog].[sp_AddBrand]",
CommandType.StoredProcedure,
new SqlParameter("@BrandName", brand.Name),
new SqlParameter("@BrandDescription", brand.Description ?? (object)DBNull.Value),
new SqlParameter("@BrandBriefDescription", brand.BriefDescription ?? (object)DBNull.Value),
new SqlParameter("@Enabled", brand.Enabled),
new SqlParameter("@SortOrder", brand.SortOrder),
new SqlParameter("@CountryID", brand.CountryId == 0 ? (object)DBNull.Value : brand.CountryId),
new SqlParameter("@UrlPath", brand.UrlPath),
new SqlParameter("@BrandSiteUrl", brand.BrandSiteUrl.IsNotEmpty() ? brand.BrandSiteUrl : (object)DBNull.Value)
));
if (brand.BrandId == 0)
return 0;
// ---- Meta
if (brand.Meta != null)
{
brand.Meta.ObjId = brand.BrandId;
MetaInfoService.SetMeta(brand.Meta);
}
return brand.BrandId;
}
public static int AddCertificate(GiftCertificate certificate)
{
var id = SQLDataAccess.ExecuteScalar <int>("[Catalog].[sp_AddCertificate]", CommandType.StoredProcedure,
new SqlParameter("@CertificateCode", certificate.CertificateCode),
new SqlParameter("@OrderNumber", String.IsNullOrEmpty(certificate.OrderNumber) ? (object)DBNull.Value : certificate.OrderNumber),
new SqlParameter("@FromName", certificate.FromName),
new SqlParameter("@ToName", certificate.ToName),
new SqlParameter("@Used", certificate.Used),
new SqlParameter("@Paid", certificate.Paid),
new SqlParameter("@Enable", certificate.Enable),
new SqlParameter("@Type", certificate.Type),
new SqlParameter("@Sum", certificate.Sum),
new SqlParameter("@Message", certificate.CertificateMessage),
new SqlParameter("@Email", String.IsNullOrEmpty(certificate.Email) ? (object)DBNull.Value : certificate.Email),
new SqlParameter("@Country", String.IsNullOrEmpty(certificate.Country) ? (object)DBNull.Value : certificate.Country),
new SqlParameter("@Zone", String.IsNullOrEmpty(certificate.Zone) ? (object)DBNull.Value : certificate.Zone),
new SqlParameter("@City", String.IsNullOrEmpty(certificate.City) ? (object)DBNull.Value : certificate.City),
new SqlParameter("@Zip", String.IsNullOrEmpty(certificate.Zip) ? (object)DBNull.Value : certificate.Zip),
new SqlParameter("@Address", String.IsNullOrEmpty(certificate.Address) ? (object)DBNull.Value : certificate.Address),
new SqlParameter("@CurrencyCode", certificate.CurrencyCode),
new SqlParameter("@CurrencyValue", certificate.CurrencyValue),
new SqlParameter("@FromEmail", certificate.FromEmail)
);
return(id);
}
/// <summary>
///
/// </summary>
/// <param name="moduleStringId"></param>
/// <returns></returns>
public static bool IsInstallModule(string moduleStringId)
{
return(SQLDataAccess.ExecuteScalar <bool>(
"SELECT [IsInstall] FROM [dbo].[Modules] WHERE [ModuleStringID] = @ModuleStringID",
CommandType.Text,
new SqlParameter("@ModuleStringID", moduleStringId)));
}
/// <summary>
/// get product count by offer
/// </summary>
/// <returns></returns>
public static int GetProductCountByOffer(int offerListId)
{
var res = SQLDataAccess.ExecuteScalar <int>("SELECT count(Catalog.Product.ProductID) FROM Catalog.Product INNER JOIN Catalog.Offer ON Catalog.Product.ProductID = Catalog.Offer.ProductID and OfferListId=@OfferListId",
CommandType.Text, new SqlParameter("@OfferListId", offerListId));
return(res);
}
public static int InsertNews(NewsItem news)
{
CacheManager.Remove(CacheNames.GetNewsForMainPage());
var id = SQLDataAccess.ExecuteScalar <int>("[Settings].[sp_AddNews]", CommandType.StoredProcedure,
new[]
{
new SqlParameter("@NewsCategoryID", news.NewsCategoryID),
new SqlParameter("@AddingDate", news.AddingDate),
new SqlParameter("@Title", news.Title),
//new SqlParameter("@Picture", news.Picture),
new SqlParameter("@TextToPublication", news.TextToPublication),
new SqlParameter("@TextToEmail", news.TextToEmail),
new SqlParameter("@TextAnnotation", news.TextAnnotation),
new SqlParameter("@ShowOnMainPage", news.ShowOnMainPage),
new SqlParameter("@UrlPath", news.UrlPath)
});
// ---- Meta
if (news.Meta != null)
{
if (!news.Meta.Title.IsNullOrEmpty() || !news.Meta.MetaKeywords.IsNullOrEmpty() || !news.Meta.MetaDescription.IsNullOrEmpty() || !news.Meta.H1.IsNullOrEmpty())
{
news.Meta.ObjId = id;
MetaInfoService.SetMeta(news.Meta);
}
}
return(id);
}
public static string GetIso2(string name)
{
return
(SQLDataAccess.ExecuteScalar <string>(
"SELECT [CountryISO2] FROM [Customers].[Country] Where CountryName = @CountryName",
CommandType.Text, new SqlParameter("@CountryName", name)));
}
public static bool IsExistProduct(Guid customerId, ShoppingCartItem item)
{
int productsCount = SQLDataAccess.ExecuteScalar <int>
(" SELECT COUNT([ItemId]) FROM [Catalog].[ShoppingCart] " +
" WHERE [CustomerId] = @CustomerId AND [ItemType] = @ItemType AND " +
" [EntityId] = @EntityId AND " +
" [ShoppingCartTypeId] = @ShoppingCartTypeId AND " +
" [AttributesXml] = @AttributesXml",
CommandType.Text,
new SqlParameter {
ParameterName = "@CustomerId", Value = customerId
},
new SqlParameter {
ParameterName = "@ItemType", Value = item.ItemType
},
new SqlParameter {
ParameterName = "@EntityId", Value = item.EntityId
},
new SqlParameter {
ParameterName = "@AttributesXml", Value = item.AttributesXml ?? string.Empty
},
new SqlParameter {
ParameterName = "@ShoppingCartTypeId", Value = item.ShoppingCartTypeId
}
);
return(productsCount != 0);
}
/// <summary>
/// insert new shoppingCartItem, CreatedOn and UpdatedOn must get on sql GetDate()
/// </summary>
/// <param name = "shoppingCartItem"></param>
public static void InsertShoppingCartItem(ShoppingCartItem shoppingCartItem)
{
shoppingCartItem.ItemId = SQLDataAccess.ExecuteScalar <int>
(@"INSERT INTO Catalog.ShoppingCart (ShoppingCartTypeId, CustomerId, EntityId, AttributesXml, Amount, CreatedOn, UpdatedOn, ItemType)
VALUES (@ShoppingCartTypeId, @CustomerId, @EntityId, @AttributesXml, @Amount, GetDate(), GetDate(), @ItemType); Select SCOPE_IDENTITY();",
CommandType.Text,
new SqlParameter {
ParameterName = "@ShoppingCartTypeId", Value = shoppingCartItem.ShoppingCartTypeId
},
new SqlParameter {
ParameterName = "@CustomerId", Value = shoppingCartItem.CustomerId
},
new SqlParameter {
ParameterName = "@EntityId", Value = shoppingCartItem.EntityId
},
new SqlParameter {
ParameterName = "@AttributesXml", Value = shoppingCartItem.AttributesXml ?? string.Empty
},
new SqlParameter {
ParameterName = "@Amount", Value = shoppingCartItem.Amount
},
new SqlParameter {
ParameterName = "@ItemType", Value = shoppingCartItem.ItemType
}
);
}
public static int GetNewPropertyValueSortOrder(int productId)
{
var intResult = SQLDataHelper.GetInt(SQLDataAccess.ExecuteScalar("SELECT MAX(SortOrder) + 10 FROM [Catalog].[ProductPropertyValue] where ProductID=@ProductID",
CommandType.Text, new SqlParameter("@ProductID", productId)), 10);
return(intResult);
}
public static int GetRegionIdByName(string regionName)
{
var id = SQLDataAccess.ExecuteScalar <int>("SELECT RegionID FROM Customers.Region WHERE RegionName = @name",
CommandType.Text, new SqlParameter("@name", regionName));
return(id);
}
private int GetOrderByToken(string token)
{
return(SQLDataAccess.ExecuteScalar <int>(
"SELECT [OrderId] FROM [ModulePayment].[PayPalExpressCheckout] WHERE [Token] = @Token",
CommandType.Text,
new SqlParameter("@Token", token)));
}
public static bool IsExist(Guid customerId)
{
return(SQLDataAccess.ExecuteScalar <int>("Select Count(CustomerId) from [Order].OrderConfirmation where CustomerId=@CustomerId", CommandType.Text,
new SqlParameter {
ParameterName = "@CustomerId", Value = customerId
}) > 0);
}
/// <summary>
///
/// </summary>
/// <param name="da"></param>
/// <param name="procedureName"></param>
/// <returns></returns>
public static bool IsExistsModuleProcedure(string procedureName)
{
return(Convert.ToBoolean(SQLDataAccess.ExecuteScalar(
@"IF(SELECT Count(name) FROM sysobjects WHERE name = @Procedure AND type = 'P') > 0 Select 1 ELSE Select 0 ",
CommandType.Text,
new SqlParameter("@Procedure", procedureName))));
}
protected int GetCategoriesCount(string moduleName)
{
return(SQLDataAccess.ExecuteScalar <int>("[Settings].[sp_GetExportFeedCategories]",
CommandType.StoredProcedure,
new SqlParameter("@moduleName", moduleName),
new SqlParameter("@onlyCount", true)));
}
public static int GetHierarchyProductsCount(int categoryId)
{
return(SQLDataAccess.ExecuteScalar <int>(@" SELECT COUNT([TProduct].[ProductId]) FROM [Catalog].[Product] AS [TProduct] " +
"WHERE (SELECT COUNT([OfferId]) FROM [Catalog].[Offer] WHERE [Price] > 0 AND [Amount] > 0 AND [OfferListId] = 6 AND [ProductId] = [TProduct].[ProductId]) > 0 " +
"AND (SELECT TOP(1) [Catalog].[ProductCategories].[CategoryId] FROM [Catalog].[ProductCategories] JOIN [Catalog].[Category] on [Category].[CategoryId] = [ProductCategories].[CategoryId] WHERE [ProductCategories].[ProductId] = [TProduct].[ProductId] AND [Enabled] = 1 AND [CategoryEnabled] = 1) IN (SELECT [ID] FROM [Settings].[GetChildCategoryByParent] (@CategoryId)) " +
"AND [Enabled] = 1 AND [CategoryEnabled] = 1", CommandType.Text, new SqlParameter("@CategoryID", categoryId)));
}
/// <summary>
/// Insert meta info to database and return metaid of new metaid
/// </summary>
/// <param name="meta"></param>
/// <returns></returns>
private static int InsertMetaInfo(MetaInfo meta)
{
var id = SQLDataAccess.ExecuteScalar <int>("[SEO].[sp_AddMetaInfo]", CommandType.StoredProcedure,
new[]
{
new SqlParameter {
ParameterName = "@Title", Value = meta.Title ?? SettingsSEO.DefaultMetaTitle
},
new SqlParameter {
ParameterName = "@MetaKeywords", Value = meta.MetaKeywords ?? SettingsSEO.DefaultMetaKeywords
},
new SqlParameter {
ParameterName = "@MetaDescription", Value = meta.MetaDescription ?? SettingsSEO.DefaultMetaDescription
},
new SqlParameter {
ParameterName = "@ObjId", Value = meta.ObjId
},
new SqlParameter {
ParameterName = "@Type", Value = meta.Type.ToString( )
}
}
);
return(id);
}
public static int GetCategoryIdByName(string name)
{
return(SQLDataAccess.ExecuteScalar <int>(
"SELECT Top 1 CategoryID FROM [Catalog].[Category] WHERE [Name] = @Name",
CommandType.Text,
new SqlParameter("@Name", name)));
}
/// <summary>
/// get products count
/// </summary>
/// <returns></returns>
public static int GetProductsCount(int brandId)
{
var res = SQLDataAccess.ExecuteScalar<int>(
"SELECT Count([ProductID]) FROM [Catalog].[Product] Where BrandID=@BrandID", CommandType.Text,
new SqlParameter { ParameterName = "@BrandID", Value = brandId });
return res;
}
public static int GetCategoryIdByUrlPath(string urlPath)
{
return(SQLDataAccess.ExecuteScalar <int>(
"SELECT Top 1 CategoryID FROM [Catalog].[Category] WHERE [UrlPath] = @UrlPath",
CommandType.Text,
new SqlParameter("@UrlPath", urlPath)));
}
public static int GetCheckedReviewsCount(int entityId, EntityType entityType)
{
return SQLDataAccess.ExecuteScalar<int>("SELECT count(ReviewID) FROM [CMS].[Review] WHERE [EntityId] = @EntityId AND [Type] = @Type AND [Checked] = 1",
CommandType.Text,
new SqlParameter("@EntityId", entityId),
new SqlParameter("@Type", (int)entityType));
}
public static bool IsSqlSettingExist(string strKey, string moduleName)
{
return(SQLDataAccess.ExecuteScalar <int>("SELECT COUNT(Name) AS COUNTID FROM [Settings].[ModuleSettings] WHERE [Name] = @Name AND [ModuleName] = @ModuleName",
CommandType.Text,
new SqlParameter("@Name", strKey),
new SqlParameter("@ModuleName", moduleName)) > 0);
}
public static int GetProductCountByType(TypeFlag type)
{
string sqlCmd = "select Count(ProductId) from Catalog.Product where Enabled=1 and CategoryEnabled=1 and {0}";
switch (type)
{
case TypeFlag.Bestseller:
sqlCmd = string.Format(sqlCmd, "bestseller=1");
break;
case TypeFlag.New:
sqlCmd = string.Format(sqlCmd, "new=1");
break;
case TypeFlag.Discount:
sqlCmd = string.Format(sqlCmd, "Discount > 0");
break;
case TypeFlag.OnSale:
sqlCmd = string.Format(sqlCmd, "OnSale = 1");
break;
case TypeFlag.Recomended:
sqlCmd = string.Format(sqlCmd, "Recomended = 1");
break;
default:
throw new NotImplementedException();
}
return(SQLDataAccess.ExecuteScalar <int>(sqlCmd, CommandType.Text));
}
/// <summary>
///
/// </summary>
/// <param name="themeId"></param>
/// <returns></returns>
public static string GetVotingName(int themeId)
{
return(SQLDataAccess.ExecuteScalar <string>(
"SELECT [Name] FROM [Voice].[VoiceTheme] WHERE [VoiceThemeID] = @Theme",
CommandType.Text,
new SqlParameter("@Theme", themeId)));
}
public static bool IsExistsModuleSetting(string moduleName, string settingName)
{
bool result = SQLDataAccess.ExecuteScalar <int>("SELECT COUNT([Value]) FROM [Settings].[Settings] WHERE [Name] = @settingName;",
CommandType.Text, new SqlParameter("@settingName", moduleName + settingName)) > 0;
return(result);
}
public static int AddCustomOption(CustomOption copt)
{
var id = SQLDataHelper.GetInt(SQLDataAccess.ExecuteScalar(
"[Catalog].[sp_AddCustomOption]",
CommandType.StoredProcedure,
new[]
{
new SqlParameter("@Title", copt.Title),
new SqlParameter("@IsRequired", copt.IsRequired),
new SqlParameter("@InputType", copt.InputType),
new SqlParameter("@SortOrder", copt.SortOrder),
new SqlParameter("@ProductID", copt.ProductId)
}
));
if (id != 0)
{
foreach (var optionItem in copt.Options)
{
if (optionItem.Title != null)
{
AddOption(optionItem, id);
}
}
}
return(id);
}
public static void AddCustomerGroup(CustomerGroup customerGroup)
{
customerGroup.CustomerGroupId = SQLDataAccess.ExecuteScalar <int>("INSERT INTO [Customers].[CustomerGroup] ([GroupName], [GroupDiscount]) VALUES (@GroupName, @GroupDiscount); SELECT SCOPE_IdENTITY();",
CommandType.Text,
new SqlParameter("@GroupName", customerGroup.GroupName),
new SqlParameter("@GroupDiscount", customerGroup.GroupDiscount));
}
public static int AddStaticPage(StaticPage page)
{
var id = SQLDataAccess.ExecuteScalar <int>(@"INSERT INTO [CMS].[StaticPage] ([PageName],[PageText],[SortOrder],[AddDate],[ModifyDate],[IndexAtSiteMap],[ParentID],[Enabled],[UrlPath]) VALUES " +
" (@PageName,@PageText,@SortOrder,GETDATE(),GETDATE(),@IndexAtSiteMap,@ParentID,@Enabled, @UrlPath); SELECT scope_identity();",
CommandType.Text,
new[]
{
new SqlParameter("@PageName", page.PageName ?? ""),
new SqlParameter("@PageText", page.PageText ?? ""),
new SqlParameter("@SortOrder", page.SortOrder),
new SqlParameter("@IndexAtSiteMap", page.IndexAtSiteMap),
new SqlParameter("@Enabled", page.Enabled),
page.ParentId == 0 ? new SqlParameter("@ParentID", DBNull.Value) : new SqlParameter("@ParentID", page.ParentId),
new SqlParameter("@UrlPath", page.UrlPath)
}
);
// ---- Meta
if (page.Meta != null)
{
if (!page.Meta.Title.IsNullOrEmpty() || !page.Meta.MetaKeywords.IsNullOrEmpty() || !page.Meta.MetaDescription.IsNullOrEmpty())
{
page.Meta.ObjId = id;
MetaInfoService.SetMeta(page.Meta);
}
}
return(id);
}
