public static PaymentAdditionalInfo GetOrderIdByPaymentIdAndCode(int paymentMethodId, string responseCode)
{
return(SQLDataAccess.ExecuteReadOne(
"Select * From [Order].[OrderPaymentInfo] Where PaymentMethodID = @PaymentMethodID AND Value = @Code",
CommandType.Text,
reader => new PaymentAdditionalInfo
{
PaymentMethodId = SQLDataHelper.GetInt(reader, "PaymentMethodID"),
OrderId = SQLDataHelper.GetInt(reader, "OrderID"),
Name = SQLDataHelper.GetString(reader, "Name"),
Value = SQLDataHelper.GetString(reader, "Value")
},
new SqlParameter("@PaymentMethodID", paymentMethodId),
new SqlParameter("@Code", responseCode)));
}
public static MailFormatType GetMailFormatType(int mailFormatTypeId)
{
var mailFormatType = SQLDataAccess.ExecuteReadOne <MailFormatType>("SELECT * FROM [Settings].[MailFormatType] WHERE MailFormatTypeID = @MailFormatTypeID",
CommandType.Text,
reader => new MailFormatType
{
MailFormatTypeID = SQLDataHelper.GetInt(reader, "MailFormatTypeID"),
TypeName = SQLDataHelper.GetString(reader, "TypeName"),
SortOrder = SQLDataHelper.GetInt(reader, "SortOrder"),
Comment = SQLDataHelper.GetString(reader, "Comment"),
}, new SqlParameter("@MailFormatTypeID", mailFormatTypeId)
);
return(mailFormatType);
}
public static StoreReview GetStoreReview(int id)
{
return(SQLDataAccess.ExecuteReadOne <StoreReview>(
"SELECT *, (SELECT Count(ID) FROM [Module].[StoreReview] WHERE [ParentID] = ParentReview.ID) as ChildsCount FROM [Module].[StoreReview] as ParentReview WHERE [ID] = @ID",
CommandType.Text,
(reader) =>
{
var review = GetStoreReviewFromReader(reader);
review.ChildrenReviews = SQLDataHelper.GetInt(reader, "ChildsCount") > 0
? GetStoreReviewsByParentId(
SQLDataHelper.GetInt(reader, "ParentID"))
: new List <StoreReview>();
return review;
},
new SqlParameter("@ID", id)));
}
public static StoreFaq GetStoreFaq(int id)
{
return(SQLDataAccess.ExecuteReadOne <StoreFaq>(
"SELECT *, (SELECT Count(ID) FROM [Module].[StoreFaq] WHERE [ParentID] = ParentFaq.ID) as ChildsCount FROM [Module].[StoreFaq] as ParentFaq WHERE [ID] = @ID",
CommandType.Text,
(reader) =>
{
var Faq = GetStoreFaqFromReader(reader);
Faq.ChildrenFaqs = SQLDataHelper.GetInt(reader, "ChildsCount") > 0
? GetStoreFaqsByParentId(
SQLDataHelper.GetInt(reader, "ParentID"))
: new List <StoreFaq>();
return Faq;
},
new SqlParameter("@ID", id)));
}
public static NewsCategory GetNewsCategoryById(int id)
{
return(SQLDataAccess.ExecuteReadOne <NewsCategory>(
"SELECT * FROM [Settings].[NewsCategory] where NewsCategoryID=@NewsCategoryID",
CommandType.Text,
reader => new NewsCategory
{
NewsCategoryID = SQLDataHelper.GetInt(reader, "NewsCategoryID"),
Name = SQLDataHelper.GetString(reader, "Name"),
SortOrder = SQLDataHelper.GetInt(reader, "SortOrder"),
UrlPath = SQLDataHelper.GetString(reader, "UrlPath")
},
new SqlParameter {
ParameterName = "@NewsCategoryID", Value = id
}
));
}
public static MetaInfo GetMetaInfo(int objId, MetaType type)
{
return(SQLDataAccess.ExecuteReadOne <MetaInfo>("Select * from SEO.MetaInfo where ObjId=@objId and Type=@type", CommandType.Text,
reader => new MetaInfo(SQLDataHelper.GetInt(reader, "MetaID"),
SQLDataHelper.GetInt(reader, "ObjId"),
(MetaType)Enum.Parse(typeof(MetaType), SQLDataHelper.GetString(reader, "Type"), true),
SQLDataHelper.GetString(reader, "Title"),
SQLDataHelper.GetString(reader, "MetaKeywords"),
SQLDataHelper.GetString(reader, "MetaDescription")),
new SqlParameter {
ParameterName = "@objId", Value = objId
},
new SqlParameter {
ParameterName = "@type", Value = type.ToString()
}
));
}
public static MailFormat GetMailFormat(int mailFormatId)
{
var mailFormat = SQLDataAccess.ExecuteReadOne <MailFormat>("SELECT * FROM [Settings].[MailFormat] WHERE MailFormatID = @MailFormatID",
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")
}, new SqlParameter("@MailFormatID", mailFormatId));
return(mailFormat);
}
public static Country GetCountry(int id)
{
var cacheKey = CountryCacheKey + id;
if (CacheManager.Contains(cacheKey))
{
return(CacheManager.Get <Country>(cacheKey));
}
var country = SQLDataAccess.ExecuteReadOne("SELECT * FROM [Customers].[Country] Where CountryID = @id",
CommandType.Text, GetCountryFromReader, new SqlParameter("@id", id));
if (country != null)
{
CacheManager.Insert(cacheKey, country);
}
return(country);
}
public static Coupon GetCustomerCoupon(Guid customerId)
{
var coupon = SQLDataAccess.ExecuteReadOne <Coupon>
("Select * From Catalog.Coupon Where CouponID = (Select CouponID From Customers.CustomerCoupon Where CustomerID = @CustomerID)",
CommandType.Text, GetFromReader,
new SqlParameter {
ParameterName = "@CustomerID", Value = customerId
});
if (coupon != null)
{
if ((coupon.ExpirationDate == null || coupon.ExpirationDate > DateTime.Now) && (coupon.PossibleUses == 0 || coupon.PossibleUses > coupon.ActualUses))
{
return(coupon);
}
DeleteCustomerCoupon(coupon.CouponID, customerId);
return(null);
}
return(null);
}
public static GiftCertificate GetCustomerCertificate(Guid customerId)
{
var certificate = SQLDataAccess.ExecuteReadOne <GiftCertificate>
("Select * From Catalog.Certificate Where CertificateID = (Select CertificateID From Customers.CustomerCertificate Where CustomerID = @CustomerID)",
CommandType.Text, GetFromReader,
new SqlParameter {
ParameterName = "@CustomerID", Value = customerId
});
if (certificate != null)
{
if (certificate.Paid && !certificate.Used)
{
return(certificate);
}
DeleteCustomerCertificate(certificate.CertificateId);
return(null);
}
return(null);
}
public static IpZone GetZoneByCity(string city, int?countryID)
{
return(SQLDataAccess.ExecuteReadOne(
"Select Top(1) City.CityID, City.CityName, Region.RegionID, Region.RegionName, Country.CountryID, Country.CountryName " +
"From Customers.City " +
"Inner Join Customers.Region On Region.RegionId = City.RegionId " +
"Inner Join Customers.Country On Country.CountryId = Region.CountryId " +
"Where @City = LOWER(CityName) and (Country.CountryID=@countryID OR @countryID is null)" +
"Order by Country.CountryID",
CommandType.Text,
reader => new IpZone()
{
CountryId = SQLDataHelper.GetInt(reader, "CountryId"),
CountryName = SQLDataHelper.GetString(reader, "CountryName"),
RegionId = SQLDataHelper.GetInt(reader, "RegionID"),
Region = SQLDataHelper.GetString(reader, "RegionName"),
CityId = SQLDataHelper.GetInt(reader, "CityId"),
City = SQLDataHelper.GetString(reader, "CityName"),
},
new SqlParameter("@City", city), new SqlParameter("@countryID", (object)countryID ?? DBNull.Value)));
}
public static ShoppingCartItem GetExistsShoppingCartItem(Guid customerId, int offerId, string attributesXml, ShoppingCartType shoppingCartType)
{
return
(SQLDataAccess.ExecuteReadOne(
"SELECT * FROM [Catalog].[ShoppingCart] WHERE [CustomerId] = @CustomerId AND [OfferId] = @OfferId AND [ShoppingCartType] = @ShoppingCartType AND [AttributesXml] = @AttributesXml",
CommandType.Text,
(reader) => new ShoppingCartItem
{
ShoppingCartItemId = SQLDataHelper.GetInt(reader, "ShoppingCartItemId"),
ShoppingCartType = (ShoppingCartType)SQLDataHelper.GetInt(reader, "ShoppingCartType"),
OfferId = SQLDataHelper.GetInt(reader, "OfferID"),
CustomerId = SQLDataHelper.GetGuid(reader, "CustomerId"),
AttributesXml = SQLDataHelper.GetString(reader, "AttributesXml"),
Amount = SQLDataHelper.GetFloat(reader, "Amount"),
CreatedOn = SQLDataHelper.GetDateTime(reader, "CreatedOn"),
UpdatedOn = SQLDataHelper.GetDateTime(reader, "UpdatedOn"),
},
new SqlParameter("@CustomerId", customerId),
new SqlParameter("@OfferId", offerId),
new SqlParameter("@AttributesXml", attributesXml ?? String.Empty),
new SqlParameter("@ShoppingCartType", (int)shoppingCartType)));
}
public static ReviewEntity GetReviewEntity(int reviewId)
{
var type = (EntityType)SQLDataAccess.ExecuteScalar<int>("Select Type From CMS.Review Where ReviewId=@ReviewId", CommandType.Text,
new SqlParameter() { ParameterName = "@ReviewId", Value = reviewId });
switch (type)
{
case EntityType.Product:
return SQLDataAccess.ExecuteReadOne("Select Product.ProductID, Name, Photo.Description, PhotoName From Catalog.Product left join catalog.Photo on Product.ProductID = Photo.ObjId and Type=@type and main = 1 Where Product.ProductID = (Select EntityID From CMS.Review Where ReviewId=@ReviewId )",
CommandType.Text,
reader => new ReviewEntity()
{
Type = EntityType.Product,
Name = SQLDataHelper.GetString(reader, "Name"),
ReviewEntityId = SQLDataHelper.GetInt(reader, "ProductID"),
Photo = SQLDataHelper.GetString(reader, "PhotoName"),
PhotoDescription = SQLDataHelper.GetString(reader, "Description")
},
new SqlParameter() { ParameterName = "@ReviewId", Value = reviewId },
new SqlParameter("@type", PhotoType.Product.ToString()));
default:
throw new NotImplementedException();
}
}
public static Customer GetCustomer(Guid customerId)
{
//var cacheName = CacheNames.GetCustomerCacheObjectName(customerId.ToString());
//if (CacheManager.Contains(cacheName))
//{
// var item = CacheManager.Get<Customer>(cacheName);
// if (item != null)
// return item;
//}
var customer = SQLDataAccess.ExecuteReadOne <Customer>("SELECT * FROM [Customers].[Customer] WHERE [CustomerID] = @CustomerID", CommandType.Text,
Customer.GetFromSqlDataReader, new SqlParameter {
ParameterName = "@CustomerID", Value = customerId
});
if (customer == null)
{
customer = new Customer();
}
//CacheManager.Insert<Customer>(cacheName, customer);
return(customer);
}
public static Review GetReview(int reviewId)
{
return SQLDataAccess.ExecuteReadOne<Review>("SELECT * FROM [CMS].[Review] WHERE ReviewId = @ReviewId",
CommandType.Text, GetFromReader,
new SqlParameter("@ReviewId", reviewId));
}
public static Brand GetBrandById(int brandId)
{
return SQLDataAccess.ExecuteReadOne<Brand>("Select * From Catalog.Brand where BrandID=@BrandID", CommandType.Text,
GetBrandFromReader, new SqlParameter("@BrandID", brandId));
}
public static Currency GetCurrencyByIso3(string iso3)
{
return(SQLDataAccess.ExecuteReadOne <Currency>("SELECT Catalog.Currency.* FROM Catalog.Currency where CurrencyIso3 = @iso3", CommandType.Text, GetCurrencyFromReader, new SqlParameter("@iso3", iso3)));
}
public static TaxElement GetTax(int id)
{
return(SQLDataAccess.ExecuteReadOne("SELECT * FROM [Catalog].[Tax] WHERE [TaxId] = @id", CommandType.Text, ReadTax, new SqlParameter("@id", id)));
}
public static Photo GetPhoto(int photoId)
{
return(SQLDataAccess.ExecuteReadOne <Photo>("SELECT * FROM [Catalog].[Photo] WHERE [PhotoID] = @PhotoID",
CommandType.Text,
GetPhotoFromReader, new SqlParameter("@PhotoID", photoId)));
}
public static PaymentMethod GetPaymentMethodByName(string name)
{
return(SQLDataAccess.ExecuteReadOne <PaymentMethod>(
"SELECT top(1) * FROM [Order].[PaymentMethod] WHERE [Name] = @Name",
CommandType.Text, reader => GetPaymentMethodFromReader(reader), new SqlParameter("@Name", name)));
}
public static PaymentMethod GetPaymentMethodByType(PaymentType type)
{
return(SQLDataAccess.ExecuteReadOne <PaymentMethod>(
"SELECT top(1) * FROM [Order].[PaymentMethod] WHERE [PaymentType] = @PaymentType",
CommandType.Text, reader => GetPaymentMethodFromReader(reader), new SqlParameter("@PaymentType", (int)type)));
}
public static MailFormat Get(int mailFormatId)
{
return(SQLDataAccess.ExecuteReadOne("SELECT * FROM [Settings].[MailFormat] WHERE MailFormatID = @MailFormatID", CommandType.Text,
GetMailFormatFromReader, new SqlParameter("@MailFormatID", mailFormatId)));
}
public static OrderConfirmation Get(Guid customerId)
{
return(SQLDataAccess.ExecuteReadOne("Select * from [Order].OrderConfirmation where CustomerId=@CustomerId",
CommandType.Text, GetFromReader,
new SqlParameter("@CustomerId", customerId)));
}
public static Country GetCountryByName(string countryName)
{
return(SQLDataAccess.ExecuteReadOne("SELECT * FROM [Customers].[Country] Where CountryName = @CountryName",
CommandType.Text, GetCountryFromReader, new SqlParameter("@CountryName", countryName)));
}
public static Country GetCountryByIso2(string iso2)
{
return(SQLDataAccess.ExecuteReadOne("SELECT * FROM [Customers].[Country] Where CountryISO2 = @iso2",
CommandType.Text, GetCountryFromReader, new SqlParameter("@iso2", iso2)));
}
public static TResult ModuleExecuteReadOne <TResult>(string query, CommandType commandType, Func <SqlDataReader, TResult> function, params SqlParameter[] parameters)
{
return(SQLDataAccess.ExecuteReadOne <TResult>(query, commandType, function, parameters));
}
public static Currency GetCurrency(int idCurrency)
{
return(SQLDataAccess.ExecuteReadOne <Currency>("SELECT Catalog.Currency.* FROM Catalog.Currency where CurrencyID = @id", CommandType.Text, GetCurrencyFromReader, new SqlParameter("@id", idCurrency)));
}
public static CallbackCustomer GetCallbackCustomer(int id)
{
return(SQLDataAccess.ExecuteReadOne <CallbackCustomer>("SELECT * FROM [Module].[" + _moduleName + "] Where ID=@ID",
CommandType.Text, GetCallbackCustomerFromReader, new SqlParameter("@ID", id)));
}
public static Color GetColor(string colorName)
{
return(SQLDataAccess.ExecuteReadOne <Color>("Select Top 1 * from Catalog.Color where colorName=@colorName order by SortOrder",
CommandType.Text,
GetFromReader, new SqlParameter("@colorName", colorName)));
}
public static PaymentMethod GetPaymentMethod(int paymentMethodId)
{
return(SQLDataAccess.ExecuteReadOne <PaymentMethod>(
"SELECT * FROM [Order].[PaymentMethod] WHERE [PaymentMethodID] = @PaymentMethodID", CommandType.Text, reader => GetPaymentMethodFromReader(reader),
new SqlParameter("@PaymentMethodID", paymentMethodId)));
}
