private static Dictionary <string, string> GetPaymentMethodParameters(int paymentMethodId)
{
return
(SQLDataAccess.ExecuteReadDictionary <string, string>(
"SELECT Name, Value FROM [Order].[PaymentParam] WHERE [PaymentMethodID] = @PaymentMethodID",
CommandType.Text, "Name", "Value", new SqlParameter("@PaymentMethodID", paymentMethodId)));
}
public static Dictionary <string, string> GetShippingParams(int shippingMethodId)
{
return
(SQLDataAccess.ExecuteReadDictionary <string, string>(
"SELECT ParamName,ParamValue FROM [Order].[ShippingParam] WHERE ShippingMethodID = @ShippingMethodID",
CommandType.Text, "ParamName", "ParamValue", new SqlParameter("@ShippingMethodID", shippingMethodId)));
}
/// <summary>
/// ¬озвращает словарь: id категории - число товаров с брендом
/// </summary>
/// <param name="brandId"></param>
/// <returns></returns>
public static Dictionary<int, BrandProductCount> GetCategoriesByBrand(int brandId)
{
return SQLDataAccess.ExecuteReadDictionary<int, BrandProductCount>("[Catalog].[sp_GetCategoriesByBrand]", CommandType.StoredProcedure,
"CategoryID", reader => new BrandProductCount
{
InCategoryCount = SQLDataHelper.GetInt(reader, "ProductCount"),
InChildsCategoryCount = SQLDataHelper.GetInt(reader, "ProductCountChilds")
}, new SqlParameter("@BrandID", brandId));
}
public static Dictionary <string, string> GetDbSaasdata()
{
if (IsExist())
{
return(SQLDataAccess.ExecuteReadDictionary <string, string>("Select [Key],[Value] from dbo.SaasData", CommandType.Text, "Key", "Value"));
}
else
{
return(null);
}
}
public static Dictionary <DateTime, float> GetOrdersCountGroupByDay(string group, DateTime minDate, DateTime maxDate,
bool?onlyPayed = null, int?orderStatusId = null)
{
return(SQLDataAccess.ExecuteReadDictionary <DateTime, float>(
"Select DATEADD(" + group + ", DATEDIFF(" + group + ", 0, [OrderDate]), 0) as 'Date', Count([OrderID]) as Count " +
"FROM [Order].[Order] " +
"WHERE [OrderDate] > @MinDate and [OrderDate] <= @MaxDate " +
(onlyPayed != null ? (" and [PaymentDate] is " + ((bool)onlyPayed ? "not" : "") + " null ") : "") +
(orderStatusId != null ? " and [OrderStatusID] = " + orderStatusId : "") +
"GROUP BY DATEADD(" + group + ", DATEDIFF(" + group + ", 0, [OrderDate]), 0)",
CommandType.Text,
"Date", "Count",
new SqlParameter("@MinDate", minDate),
new SqlParameter("@MaxDate", maxDate)));
}
public static Dictionary <int, string> GetPropertiesValuesByNameEndProductId(string text, int productId, int propertyId = 0)
{
var command = "Select DISTINCT PropertyValueID, Value From Catalog.PropertyValue WHERE Value like @name + '%' AND PropertyValueID NOT IN (Select PropertyValueID From Catalog.ProductPropertyValue Where ProductID = @productId)";
var sqlParameters = new List <SqlParameter>()
{
new SqlParameter("@name", text),
new SqlParameter("@productId", productId)
};
if (propertyId != 0)
{
command += " AND PropertyID = @PropertyID";
sqlParameters.Add(new SqlParameter("@PropertyID", propertyId));
}
return(SQLDataAccess.ExecuteReadDictionary <int, string>(command, CommandType.Text, "PropertyValueID", "Value", sqlParameters.ToArray()));
}
private static Dictionary <string, string> GetAllSettings()
{
if (CacheManager.Contains(SettingsCacheKey))
{
return(CacheManager.Get <Dictionary <string, string> >(SettingsCacheKey));
}
var settings =
SQLDataAccess.ExecuteReadDictionary <string, string>("SELECT [Name],[Value] FROM [Settings].[Settings]",
CommandType.Text, "Name", "Value");
if (settings == null)
{
return(new Dictionary <string, string>());
}
CacheManager.Insert(SettingsCacheKey, settings);
return(settings);
}
public static string GetCategoryStringByProductId(int productId, string separator, bool onlySort = false)
{
var categoryDict = SQLDataAccess.ExecuteReadDictionary <int, int>(
"Select CategoryID, SortOrder from Catalog.ProductCategories where ProductID=@id order by Main Desc", CommandType.Text,
"CategoryID", "SortOrder",
new SqlParameter("@id", productId));
if (onlySort)
{
var resSort = categoryDict.Select(
pair =>
String.Format("{0}", pair.Value)).AggregateString(separator);
return(resSort);
}
var res = categoryDict.Select(
pair =>
String.Format("[{0}]", GetParentCategoriesAsString(pair.Key))).AggregateString(',');
return(res);
}
public static Dictionary <int, string> GetPropertiesByName(string name)
{
return(SQLDataAccess.ExecuteReadDictionary <int, string>("Select DISTINCT PropertyID, Name From Catalog.Property WHERE Name like @name + '%'",
CommandType.Text, "PropertyID", "Name", new SqlParameter("@name", name)));
}
