public GamePrizeInfo GetModelByPrizeGradeAndGameId(PrizeGrade grade, int gameId)
{
string query = "Select * From [Hishop_PromotionGamePrizes] where PrizeGrade=@PrizeGrade and GameId=@GameId ";
System.Data.Common.DbCommand sqlStringCommand = this._db.GetSqlStringCommand(query);
this._db.AddInParameter(sqlStringCommand, "@PrizeGrade", System.Data.DbType.Int32, (int)grade);
this._db.AddInParameter(sqlStringCommand, "@GameId", System.Data.DbType.Int32, gameId);
GamePrizeInfo result;
using (System.Data.IDataReader dataReader = this._db.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToModel <GamePrizeInfo>(dataReader);
}
return(result);
}
public AttributeInfo GetAttribute(int attributeId)
{
AttributeInfo attributeInfo = new AttributeInfo();
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_AttributeValues WHERE AttributeId = @AttributeId ORDER BY DisplaySequence DESC; SELECT * FROM Hishop_Attributes WHERE AttributeId = @AttributeId;");
this.database.AddInParameter(sqlStringCommand, "AttributeId", System.Data.DbType.Int32, attributeId);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
IList <AttributeValueInfo> attributeValues = ReaderConvert.ReaderToList <AttributeValueInfo>(dataReader);
dataReader.NextResult();
attributeInfo = ReaderConvert.ReaderToModel <AttributeInfo>(dataReader);
attributeInfo.AttributeValues = attributeValues;
}
return(attributeInfo);
}
public OrderSplitInfo GetOrderSplitInfoByOrderIDAndNum(int orderidnum, string oldorderid)
{
string query = "select * from vshop_OrderSplit where OldOrderID=@OldOrderID and OrderIDNum=@OrderIDNum";
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
this.database.AddInParameter(sqlStringCommand, "OrderIDNum", System.Data.DbType.Int32, orderidnum);
this.database.AddInParameter(sqlStringCommand, "OldOrderID", System.Data.DbType.String, oldorderid);
OrderSplitInfo result = null;
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToModel <OrderSplitInfo>(dataReader);
}
return(result);
}
public AttributeInfo GetAttribute(int attributeId)
{
AttributeInfo info = new AttributeInfo();
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_AttributeValues WHERE AttributeId = @AttributeId ORDER BY DisplaySequence DESC; SELECT * FROM Hishop_Attributes WHERE AttributeId = @AttributeId;");
this.database.AddInParameter(sqlStringCommand, "AttributeId", DbType.Int32, attributeId);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
IList <AttributeValueInfo> list = ReaderConvert.ReaderToList <AttributeValueInfo>(reader);
reader.NextResult();
info = ReaderConvert.ReaderToModel <AttributeInfo>(reader);
info.AttributeValues = list;
}
return(info);
}
public ProductReviewInfo GetProductReview(int reviewId)
{
ProductReviewInfo info = new ProductReviewInfo();
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_ProductReviews WHERE ReviewId=@ReviewId");
this.database.AddInParameter(sqlStringCommand, "ReviewId", DbType.Int32, reviewId);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
if (reader.Read())
{
info = ReaderConvert.ReaderToModel <ProductReviewInfo>(reader);
}
}
return(info);
}
public LineItemInfo GetReturnMoneyByOrderIDAndProductID(string orderId, string skuid, int itemid)
{
string query = "select top 1 * from Hishop_OrderItems where OrderId=@OrderId and ID=@ID";
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
this.database.AddInParameter(sqlStringCommand, "OrderId", System.Data.DbType.String, orderId);
this.database.AddInParameter(sqlStringCommand, "SkuId", System.Data.DbType.String, skuid);
this.database.AddInParameter(sqlStringCommand, "ID", System.Data.DbType.Int32, itemid);
LineItemInfo result = null;
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToModel <LineItemInfo>(dataReader);
}
return(result);
}
public StoreMessage GetModel(int ID)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM vw_Hishop_StoreMessage WHERE ID = @ID");
this.database.AddInParameter(sqlStringCommand, "ID", DbType.Int32, ID);
StoreMessage info = null;
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
if (reader.Read())
{
info = ReaderConvert.ReaderToModel <StoreMessage>(reader);
}
}
return(info);
}
public ProductTypeInfo GetProductType(int typeId)
{
ProductTypeInfo info = null;
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_ProductTypes WHERE TypeId = @TypeId;SELECT * FROM Hishop_ProductTypeBrands WHERE ProductTypeId = @TypeId");
this.database.AddInParameter(sqlStringCommand, "TypeId", DbType.Int32, typeId);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
info = ReaderConvert.ReaderToModel <ProductTypeInfo>(reader);
reader.NextResult();
while (reader.Read())
{
info.Brands.Add((int)reader["BrandId"]);
}
}
return(info);
}
public PrizeRecordInfo GetUserPrizeRecord(int activityid)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("select top 1 * from Vshop_PrizeRecord where ActivityId=@ActivityId and UserID=@UserID order by RecordId desc");
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString());
this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, activityid);
this.database.AddInParameter(sqlStringCommand, "UserID", DbType.Int32, HiContext.Current.User.UserId);
PrizeRecordInfo result;
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToModel <PrizeRecordInfo>(dataReader);
}
return(result);
}
public TopicInfo GetTopic(int TopicId)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("select *, (SELECT Keys FROM vshop_Reply WHERE [ReplyType] = @ReplyType AND ActivityId = t.TopicId) AS Keys from Vshop_Topics t where TopicId=@TopicId");
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString());
this.database.AddInParameter(sqlStringCommand, "TopicId", DbType.Int32, TopicId);
this.database.AddInParameter(sqlStringCommand, "ReplyType", DbType.Int32, 512);
TopicInfo result = null;
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToModel <TopicInfo>(dataReader);
}
return(result);
}
public LotteryActivityInfo GetLotteryActivityInfo(int activityid)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("select ActivityId,ActivityName,ActivityType,StartTime,EndTime,ActivityDesc,ActivityPic,ActivityKey,PrizeSetting,MaxNum from Vshop_LotteryActivity ");
stringBuilder.Append(" where ActivityId=@ActivityId ");
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString());
this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.Int32, activityid);
LotteryActivityInfo result = null;
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToModel <LotteryActivityInfo>(dataReader);
}
return(result);
}
public LineItemInfo GetLineItemInfo(int id, string orderid)
{
string str = string.Empty;
if (!string.IsNullOrEmpty(orderid))
{
str = " and OrderId=@OrderId";
}
string query = "select top 1 * from Hishop_OrderItems where Id=@Id ";
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
this.database.AddInParameter(sqlStringCommand, "Id", DbType.Int32, id);
this.database.AddInParameter(sqlStringCommand, "OrderId", DbType.String, orderid);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToModel <LineItemInfo>(reader));
}
}
public BrandTagInfo GetBrandTag(int brandTagId)
{
BrandTagInfo brandCategoryInfo = new BrandTagInfo();
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Ecshop_BrandTag WHERE BrandTagId = @BrandId;SELECT * FROM Ecshop_BrandToBrandTag WHERE BrandTagId = @BrandId");
this.database.AddInParameter(sqlStringCommand, "BrandId", DbType.Int32, brandTagId);
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
brandCategoryInfo = ReaderConvert.ReaderToModel <BrandTagInfo>(dataReader);
IList <int> list = new List <int>();
dataReader.NextResult();
while (dataReader.Read())
{
list.Add((int)dataReader["BrandId"]);
}
brandCategoryInfo.BrandCategoryInfos = list;
}
return(brandCategoryInfo);
}
public ActivityInfo GetAct(string name)
{
ActivityInfo info = null;
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Activities WHERE ActivitiesName= @Name");
this.database.AddInParameter(sqlStringCommand, "Name", DbType.Int32, name);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
info = ReaderConvert.ReaderToModel <ActivityInfo>(reader);
}
sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Activities_Detail WHERE ActivitiesId = @ID");
this.database.AddInParameter(sqlStringCommand, "ID", DbType.Int32, info.ActivitiesId);
using (IDataReader reader2 = this.database.ExecuteReader(sqlStringCommand))
{
IList <ActivityDetailInfo> list = ReaderConvert.ReaderToList <ActivityDetailInfo>(reader2);
info.Details = list;
}
return(info);
}
public BrandCategoryInfo GetBrandCategory(int brandId)
{
BrandCategoryInfo info = new BrandCategoryInfo();
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_BrandCategories WHERE BrandId = @BrandId;SELECT * FROM Hishop_ProductTypeBrands WHERE BrandId = @BrandId");
this.database.AddInParameter(sqlStringCommand, "BrandId", DbType.Int32, brandId);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
info = ReaderConvert.ReaderToModel <BrandCategoryInfo>(reader);
IList <int> list = new List <int>();
reader.NextResult();
while (reader.Read())
{
list.Add((int)reader["ProductTypeId"]);
}
info.ProductTypes = list;
}
return(info);
}
public int MermberCanbuyNum(string ActivityId, int userid)
{
MermberCanbuyNumInfo info;
int num = 0;
string query = " select\r\n (select EachCanBuyNum from Vshop_OneyuanTao_Detail where ActivityId=@ActivityId) as t,\r\n (select isnull(SUM(BuyNum),0) from Vshop_OneyuanTao_ParticipantMember where ActivityId=@ActivityId and UserId=@UserId) as b";
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, userid);
this.database.AddInParameter(sqlStringCommand, "ActivityId", DbType.String, ActivityId);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
info = ReaderConvert.ReaderToModel <MermberCanbuyNumInfo>(reader);
}
if (info != null)
{
num = info.t - info.b;
}
return(num);
}
public ActivityInfo GetAct(string name)
{
ActivityInfo activityInfo = null;
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Activities WHERE ActivitiesName= @Name");
this.database.AddInParameter(sqlStringCommand, "Name", System.Data.DbType.Int32, name);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
activityInfo = ReaderConvert.ReaderToModel <ActivityInfo>(dataReader);
}
sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Activities_Detail WHERE ActivitiesId = @ID");
this.database.AddInParameter(sqlStringCommand, "ID", System.Data.DbType.Int32, activityInfo.ActivitiesId);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
IList <ActivityDetailInfo> details = ReaderConvert.ReaderToList <ActivityDetailInfo>(dataReader);
activityInfo.Details = details;
}
return(activityInfo);
}
public LineItemInfo GetLatestOrderItemByProductIDAndUserID(int productid, int userid)
{
string query = string.Format(string.Concat(new object[]
{
"select top 1 a.* from Hishop_OrderItems a left join Hishop_ProductReviews b on a.skuid= b.skuid and a.orderid=b.orderid left join Hishop_Orders c on a.orderid=c.orderid where c.userid=",
userid,
" and a.productid=",
productid,
" and a.OrderItemsStatus = {0} and b.orderid is null order by a.Id desc"
}), 5);
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
LineItemInfo result = null;
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToModel <LineItemInfo>(dataReader);
}
return(result);
}
public LineItemInfo GetLineItemInfo(int id, string orderid)
{
string empty = string.Empty;
if (!string.IsNullOrEmpty(orderid))
{
}
string query = "select top 1 * from Hishop_OrderItems where Id=@Id ";
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
this.database.AddInParameter(sqlStringCommand, "Id", System.Data.DbType.Int32, id);
this.database.AddInParameter(sqlStringCommand, "OrderId", System.Data.DbType.String, orderid);
LineItemInfo result = null;
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToModel <LineItemInfo>(dataReader);
}
return(result);
}
public AppSupplierInfo GetAppSupplier(int supplierId, int userid)
{
StringBuilder strtable = new StringBuilder();
strtable.Append("select A.*,");
strtable.Append("(select count(C1.Id) from Ecshop_SupplierCollect C1 where C1.SupplierId=A.SupplierId) as CollectCount,");
strtable.AppendFormat("case when (select count(C2.SupplierId) from Ecshop_SupplierCollect C2 where C2.SupplierId=A.SupplierId and C2.Userid={0})>0 then 1 else 0 end as IsCollect", userid);
strtable.Append(" from Ecshop_Supplier A ");
strtable.AppendFormat(" where A.supplierId = @supplierId ");
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(strtable.ToString());
this.database.AddInParameter(sqlStringCommand, "supplierId", DbType.Int32, supplierId);
AppSupplierInfo result = null;
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToModel <AppSupplierInfo>(dataReader);
}
return(result);
}
public ReplaceInfo GetReplaceInfo(string OrderId, string SkuId = "")
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT TOP 1 * FROM Hishop_OrderReplace WHERE OrderId = @OrderId");
if (!string.IsNullOrEmpty(SkuId))
{
builder.Append(" AND SkuId = @SkuID");
}
else
{
builder.Append(" AND (SkuId IS NULL OR SkuId='')");
}
builder.Append(" ORDER BY ReplaceId DESC");
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
this.database.AddInParameter(sqlStringCommand, "OrderId", DbType.String, OrderId);
this.database.AddInParameter(sqlStringCommand, "SkuId", DbType.String, SkuId);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToModel <ReplaceInfo>(reader));
}
}
public ProductInfo GetProductDetails(int productId)
{
ProductInfo info = null;
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Products WHERE ProductId = @ProductId;SELECT skus.ProductId, skus.SkuId, s.AttributeId, s.ValueId, skus.SKU, skus.SalePrice, skus.CostPrice, skus.Stock, skus.[Weight] FROM Hishop_SKUItems s right outer join Hishop_SKUs skus on s.SkuId = skus.SkuId WHERE skus.ProductId = @ProductId ORDER BY (SELECT DisplaySequence FROM Hishop_Attributes WHERE AttributeId = s.AttributeId) DESC;SELECT s.SkuId, smp.GradeId, smp.MemberSalePrice FROM Hishop_SKUMemberPrice smp INNER JOIN Hishop_SKUs s ON smp.SkuId=s.SkuId WHERE s.ProductId=@ProductId;SELECT AttributeId, ValueId FROM Hishop_ProductAttributes WHERE ProductId = @ProductId;SELECT * FROM Hishop_ProductTag WHERE ProductId=@ProductId");
this.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, productId);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
string str;
info = ReaderConvert.ReaderToModel <ProductInfo>(reader);
if (info == null)
{
return(info);
}
reader.NextResult();
while (reader.Read())
{
str = (string)reader["SkuId"];
if (!info.Skus.ContainsKey(str))
{
info.Skus.Add(str, DataMapper.PopulateSKU(reader));
}
if ((reader["AttributeId"] != DBNull.Value) && (reader["ValueId"] != DBNull.Value))
{
info.Skus[str].SkuItems.Add((int)reader["AttributeId"], (int)reader["ValueId"]);
}
}
reader.NextResult();
while (reader.Read())
{
str = (string)reader["SkuId"];
info.Skus[str].MemberPrices.Add((int)reader["GradeId"], (decimal)reader["MemberSalePrice"]);
}
}
return(info);
}
