/// <summary> /// 新增退款单 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool AddRefund(RefundOrderEntity model) { string sql = @"Insert Into RefundOrderInfo(RefundCode,RefundType,OrderCode,UserId,RefundReason,RefundDescription,RefundStatus,TotalAmount,RMBTotalAmount,CreateTime,CreateBy,ImagePath,ExchangeRate,SupplierId,RegionCode,ProductStatus,Commission) Values (@RefundCode,@RefundType,@OrderCode,@UserId,@RefundReason,@RefundDescription,@RefundStatus,@TotalAmount,@RMBTotalAmount,@CreateTime,@CreateBy,@ImagePath,@ExchangeRate,@SupplierId,@RegionCode,@ProductStatus,@Commission);"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@RefundCode", model.RefundCode); parameters.Append("@RefundType", model.RefundType); parameters.Append("@OrderCode", model.OrderCode); parameters.Append("@UserId", model.UserId); parameters.Append("@RefundReason", model.RefundReason); parameters.Append("@RefundDescription", model.RefundDescription); parameters.Append("@RefundStatus", model.RefundStatus); parameters.Append("@TotalAmount", model.TotalAmount); parameters.Append("@RMBTotalAmount", model.RMBTotalAmount); parameters.Append("@CreateTime", model.CreateTime); parameters.Append("@CreateBy", model.CreateBy); parameters.Append("@ImagePath", model.ImagePath); parameters.Append("@ExchangeRate", model.ExchangeRate); parameters.Append("@SupplierId", model.SupplierId); parameters.Append("@RegionCode", model.RegionCode); parameters.Append("@ProductStatus", model.ProductStatus); parameters.Append("@Commission", model.Commision); return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0); }
//插入 DividedPercentStationVisitedLog public int InsertVisitedLog(string StationSource, int ChannelId) { string sql = @"INSERT INTO DividedPercentStationVisitedLog ( -- Id -- this column value is auto-generated DPID, ZDID, VisitedDate, VisitedCount ) VALUES ( @DPID, @ZDID, @VisitedDate, @VisitedCount )"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@DPID", ChannelId); parameters.Append("@ZDID", StationSource); parameters.Append("@VisitedDate", DateTime.Now); parameters.Append("@VisitedCount", 1); return(Convert.ToInt32(DbSFO2OMain.ExecuteScalar(CommandType.Text, sql, parameters))); }
/// <summary> /// 添加新地址 /// </summary> /// <param name="model"></param> /// <returns></returns> public int AddAddress(AddressEntity model) { string sql = @"Insert into AddressInfo(UserId,[Type],CountryId,ProvinceId,CityId,AreaId,PostCode,[Address],Receiver,Phone,IsDefault,PapersType,PapersCode,CreateTime,CreateBy,IsEnable) Values(@UserId,@Type,@CountryId,@ProvinceId,@CityId,@AreaId,@PostCode,@Address,@Receiver,@Phone,@IsDefault,@PapersType,@PapersCode,@CreateTime,@CreateBy,@IsEnable);select @@Identity;"; var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@UserId", model.UserId); parameters.Append("@Type", model.Type); parameters.Append("@CountryId", model.CountryId); parameters.Append("@ProvinceId", model.ProvinceId); parameters.Append("@CityId", model.CityId); parameters.Append("@AreaId", model.AreaId); parameters.Append("@PostCode", model.PostCode); parameters.Append("@Address", model.Address); parameters.Append("@Receiver", model.Receiver); parameters.Append("@Phone", model.Phone); parameters.Append("@IsDefault", model.IsDefault); parameters.Append("@PapersType", model.PapersType); parameters.Append("@PapersCode", model.PapersCode); parameters.Append("@CreateTime", model.CreateTime); parameters.Append("@CreateBy", model.CreateBy); parameters.Append("@IsEnable", model.IsEnable); object id = DbSFO2OMain.ExecuteScalar(CommandType.Text, sql, parameters); return(id == null?0:id.AsInt32()); }
public IList <BulletinEntity> GetBulletinEntities(int top = 0) { string sql; if (top == 0) { sql = @"SELECT [Id] ,[Title] ,[Content] ,[CreateTime] ,[LinkUrl] ,[Sort] ,[Status] FROM [Bulletin] WHERE Status=1 order by Sort"; } else { sql = @"SELECT TOP ({0}) [Id] ,[Title] ,[Content] ,[CreateTime] ,[LinkUrl] ,[Sort] ,[Status] FROM [Bulletin] WHERE Status=1 order by Sort"; sql = string.Format(sql, top); } return(DbSFO2OMain.ExecuteSqlList <BulletinEntity>(sql)); }
public bool SelectedItem(int userId, string sku, int currentSalesTerritory, bool selected) { string sql = @" UPDATE ShoppingCart SET IsChecked = {0}, LastTime = GETDATE() WHERE UserId = @UserId AND( CountryId=@CountryId or CountryId=3 ) AND Sku =@Sku" ; try { var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@UserId", userId); parameters.Append("@CountryId", currentSalesTerritory); parameters.Append("@Sku", sku); sql = string.Format(sql, selected ? "1" : "0"); return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0); } catch (Exception ex) { LogHelper.Error(ex); return(false); } }
public List <PromotionSkuListModel> GetPromotionSkuInfo(List <string> skus) { var sql = @"SELECT s.spu,s.Sku,p.Name,s.MainValue,s.SubValue,s.Price ,pImg.ImagePath FROM ProductInfo(NOLOCK) p INNER JOIN SkuInfo(NOLOCK) s ON p.Id=s.SpuId AND p.LanguageVersion=2 AND s.Status=3 LEFT JOIN ProductImage(NOLOCK) pImg ON pImg.Spu = p.Spu AND pImg.SortValue=1 WHERE s.sku IN ({0})"; var db = DbSFO2ORead; var parameters = db.CreateParameterCollection(); StringBuilder sb = new StringBuilder(1000); int index = 0; foreach (var sku in skus) { sb.Append("@sku" + index + ","); parameters.Append("@sku" + index, sku); index++; } return(DbSFO2OMain.ExecuteSqlList <PromotionSkuListModel>(String.Format(sql, sb.Remove(sb.Length - 1, 1).ToString()), parameters).ToList()); }
public bool UpdateMessageStatus(MessageInfo messageInfo) { if (messageInfo == null) { return(false); } string UPDATE_SQL = @" update Message set status = @status where ID=@ID "; var dbParameters = DbSFO2OMain.CreateParameterCollection(); dbParameters.Append("@ID", messageInfo.ID); dbParameters.Append("@status", messageInfo.Status); if (!string.IsNullOrEmpty(messageInfo.UpdateBy)) { UPDATE_SQL = @" update Message set status = @status ,UpdateBy=@UpdateBy,UpdateTime=@UpdateTime where ID=@ID "; dbParameters.Append("@UpdateBy", messageInfo.UpdateBy); dbParameters.Append("@UpdateTime", messageInfo.UpdateTime); } int effectCount = 0; try { effectCount = DbSFO2OMain.ExecuteNonQuery(CommandType.Text, UPDATE_SQL, dbParameters); } catch (Exception ex) { LogHelper.Error(ex); } return(effectCount > 0); }
/// <summary> /// 插入优惠券信息 /// </summary> /// <param name="GiftCardEntity"></param> /// <returns></returns> public bool SaveGiftCard(GiftCardEntity GiftCardEntity, Database db, DbTransaction tran) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into GiftCard("); strSql.Append("BatchId,BatchName,UserId,CardId,CardSum,CardType,Status,BeginTime,EndTime,SatisfyPrice,SatisfyProduct,AddTime)"); strSql.Append(" values ("); strSql.Append("@BatchId,@BatchName,@UserId,@CardId,@CardSum,@CardType,@Status,@BeginTime,@EndTime,@SatisfyPrice,@SatisfyProduct,@AddTime)"); var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@BatchId", GiftCardEntity.BatchId); parameters.Append("@BatchName", GiftCardEntity.BatchName); parameters.Append("@UserId", GiftCardEntity.UserId); parameters.Append("@CardId", GiftCardEntity.CardId); parameters.Append("@CardSum", GiftCardEntity.CardSum); parameters.Append("@CardType", GiftCardEntity.CardType); parameters.Append("@Status", GiftCardEntity.Status); parameters.Append("@BeginTime", GiftCardEntity.BeginTime); parameters.Append("@EndTime", GiftCardEntity.EndTime); parameters.Append("@SatisfyPrice", GiftCardEntity.SatisfyPrice); parameters.Append("@SatisfyProduct", GiftCardEntity.SatisfyProduct); parameters.Append("@AddTime", GiftCardEntity.AddTime); return(db.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters, tran) > 0); }
/// <summary> /// 新增结算单 /// </summary> /// <param name="e"></param> /// <returns></returns> public bool AddSettle(SettleOrderInfoEntity e) { string sql = @"insert into SettlementOrderInfo(SettlementCode,OrderCode,RefundCode,SettlementStatus,SettlementType,SupplierId,ExchangeRate,RmbProductTotalAmount, ProductTotalAmount,RmbProductRefundAmount,ProductRefundAmount,RmbSettlementAmount,SettlementAmount, RmbSupplierBearDutyAmount,SupplierBearDutyAmount,RmbBearDutyAmount,BearDutyAmount,CreateTime,CreateBy) Values(@SettlementCode,@OrderCode,@RefundCode,@SettlementStatus,@SettlementType,@SupplierId,@ExchangeRate, @RmbProductTotalAmount,@ProductTotalAmount,@RmbProductRefundAmount,@ProductRefundAmount,@RmbSettlementAmount,@SettlementAmount, @RmbSupplierBearDutyAmount,@SupplierBearDutyAmount,@RmbBearDutyAmount,@BearDutyAmount,@CreateTime,@CreateBy);"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@SettlementCode", e.SettlementCode); parameters.Append("@OrderCode", e.OrderCode); parameters.Append("@RefundCode", e.RefundCode); parameters.Append("@SettlementStatus", e.SettlementStatus); parameters.Append("@SettlementType", e.SettlementType); parameters.Append("@SupplierId", e.SupplierId); parameters.Append("@ExchangeRate", e.ExchangeRate); parameters.Append("@RmbProductTotalAmount", e.RmbProductTotalAmount); parameters.Append("@ProductTotalAmount", e.ProductTotalAmount); parameters.Append("@RmbProductRefundAmount", e.RmbProductRefundAmount); parameters.Append("@ProductRefundAmount", e.ProductRefundAmount); parameters.Append("@RmbSettlementAmount", e.RmbSettlementAmount); parameters.Append("@SettlementAmount", e.SettlementAmount); parameters.Append("@RmbSupplierBearDutyAmount", e.RmbSupplierBearDutyAmount); parameters.Append("@SupplierBearDutyAmount", e.SupplierBearDutyAmount); parameters.Append("@RmbBearDutyAmount", e.RmbBearDutyAmount); parameters.Append("@BearDutyAmount", e.BearDutyAmount); parameters.Append("@CreateTime", e.CreateTime); parameters.Append("@CreateBy", e.CreateBy); return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0); }
/// /// /// <summary> /// 我的酒豆详情 /// </summary> public List <MyHL> HLDetail(int userId, int typeName, int PageSize, int PageIndex) { string type = null; int startIndex = (PageIndex - 1) * PageSize + 1; int endIndex = PageIndex * PageSize; if (typeName == 0) { type = " and hll.Direction in (1,2)"; } else if (typeName == 1) { type = " and hll.Direction = 1"; } else if (typeName == 2) { type = " and hll.Direction = 2"; } string sql = @"with sputb AS (SELECT hll.UserId,hll.TradeCode,hll.[Description],hll.Direction,hll.ChangedHuoLi,hll.CurrentHuoLi,CONVERT(VARCHAR(23),hll.CreateTime,120) as CreateTime,ROW_NUMBER() OVER (ORDER BY hll.Id DESC ) as rindex FROM HuoLiLog AS hll WHERE hll.UserId=@userId " + type + ") select *,(select count(1) from sputb) as TotalRecord from sputb where rindex between @StartIndex and @EndIndex"; var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@userId", userId); parameters.Append("@StartIndex", startIndex); parameters.Append("@EndIndex", endIndex); var list = DbSFO2OMain.ExecuteSqlList <MyHL>(sql, parameters); return(list.ToList()); }
public bool insertHuoLiTotal(int userId) { string sql = @"INSERT INTO HuoLiTotal ( -- Id -- this column value is auto-generated UserId, HuoLi, LockedHuoLi, -- HuoLiCurrent -- this column value is auto-generated CreateTime, CreateBy ) VALUES ( @UserId, 0, 0, @CreateTime, @CreateBy )"; try { var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@UserId", userId); parameters.Append("@CreateTime", DateTime.Now); parameters.Append("@CreateBy", "system"); return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0); } catch (Exception ex) { LogHelper.Error(ex); return(false); } }
/// <summary> /// /// </summary> /// <param name="entity"></param> /// <returns></returns> public bool Add(OrderPaymentEntity entity) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into OrderPayment("); strSql.Append("PayCode,TradeCode,UserId,OrderType,OrderCode,PayAmount,PaidAmount,PayPlatform,PayType,PayStatus,PayTerminal,PayCompleteTime,PayBackRemark,Remark,CreateTime,CreateBy)"); strSql.Append(" values ("); strSql.Append("@PayCode,@TradeCode,@UserId,@OrderType,@OrderCode,@PayAmount,@PaidAmount,@PayPlatform,@PayType,@PayStatus,@PayTerminal,@PayCompleteTime,@PayBackRemark,@Remark,@CreateTime,@CreateBy)"); var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@PayCode", entity.PayCode); parameters.Append("@TradeCode", entity.TradeCode); parameters.Append("@UserId", entity.UserId); parameters.Append("@OrderType", entity.OrderType); parameters.Append("@OrderCode", entity.OrderCode); parameters.Append("@PayAmount", entity.PayAmount); parameters.Append("@PaidAmount", entity.PaidAmount); parameters.Append("@PayPlatform", entity.PayPlatform); parameters.Append("@PayType", entity.PayType); parameters.Append("@PayStatus", entity.PayStatus); parameters.Append("@PayTerminal", entity.PayTerminal); parameters.Append("@PayCompleteTime", entity.PayCompleteTime); parameters.Append("@PayBackRemark", entity.PayBackRemark); parameters.Append("@Remark", entity.Remark); parameters.Append("@CreateTime", entity.CreateTime); parameters.Append("@CreateBy", entity.CreateBy); return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0); }
public int OrderReadMessage(int userid, int infoid) { try { int result = 0; string sql = @"select count(1) from InformationRead where UserId=@userid and InformationId=@InfoId"; var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@userid", userid); parameters.Append("@InfoId", infoid); bool isRead = DbSFO2ORead.ExecuteScalar(CommandType.Text, sql, parameters).ToString() == "0" ? false : true; //如果已读表不存在词条数据,则插入 if (!isRead) { sql = @"INSERT INTO [InformationRead]([UserId],[InformationId],[CreateTime])VALUES (@userid,@InfoId,GETDATE())"; parameters.Clear(); parameters.Append("@userid", userid); parameters.Append("@InfoId", infoid); result = DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters); } return(result); } catch (Exception ex) { LogHelper.Error(ex); return(-1); } }
/// <summary> /// /// </summary> /// <returns></returns> public bool IsFirstOrderAuthorize(string userName) { string sql = @"SELECT FirstOrderAuthorize FROM Customer WHERE UserName=@UserName "; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@UserName", userName); return(DbSFO2OMain.ExecuteScalar(CommandType.Text, sql, parameters).ToString() == "1" ? true : false); }
/// <summary> /// 更新是否首次下单 /// </summary> /// <param name="entity"></param> /// <returns></returns> public bool UpdateFirstOrderAuthorize(string userName) { string sql = "Update Customer Set FirstOrderAuthorize=1 Where UserName=@UserName"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@UserName", userName); return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0); }
/// <summary> /// 获取所有省份 /// </summary> /// <param name="language"></param> /// <returns></returns> public SourceEntity GetSourcePercentById(int cid) { string sql = "SELECT [Id],[OrderSourceType],[DividedPercent],[Status],[ChannelNo],[ChannelName],[CreateTime],[CreateBy] FROM [DividedPercent] WHERE id=@cid"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@cid", cid); return(DbSFO2ORead.ExecuteSqlFirst <SourceEntity>(sql, parameters)); }
public CustomerEntity getUserInfo(int userId) { string sql = @"SELECT * FROM Customer AS c WHERE c.ID=@userId"; var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@userId", userId); return(DbSFO2OMain.ExecuteSqlFirst <CustomerEntity>(sql, parameters)); }
/// <summary> /// 获得我的酒豆 /// </summary> /// <param name="orderCode">订单号</param> /// <returns></returns> public MyHL getMyHL(int userId) { string sql = @"SELECT hls.HuoLi as countHL,hls.LockedHuoLi as freezeHL,hls.HuoLiCurrent as usableHL FROM dbo.HuoLiTotal AS hls WHERE hls.UserId=@userId"; var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@userId", userId); return(DbSFO2OMain.ExecuteSqlFirst <MyHL>(sql, parameters)); }
// 通过StationSource ChannelId 查询 DividedPercentStationVisitedLog 表中是否有记录 public int selectVisitedLog(string StationSource, int ChannelId) { string sql = "SELECT COUNT(*) FROM DividedPercentStationVisitedLog AS dpsvl WHERE dpsvl.DPID=@ChannelId AND dpsvl.ZDID=@StationSource "; var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@StationSource", StationSource); parameters.Append("@ChannelId", ChannelId); return(Convert.ToInt32(DbSFO2OMain.ExecuteScalar(CommandType.Text, sql, parameters))); }
/// <summary> /// 更新密码 /// </summary> /// <param name="entity"></param> /// <returns></returns> public bool UpdatePassword(CustomerEntity entity) { string sql = "Update Customer Set Password=@Password Where UserName=@UserName"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@Password", MD5Hash.Md5Encrypt(entity.Password)); parameters.Append("@UserName", entity.UserName); return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0); }
//通过StationSource ChannelId 更新 DividedPercentStationVisitedLog 表的记录 public bool UpdateVisitedLog(string StationSource, int ChannelId) { string sql = "UPDATE DividedPercentStationVisitedLog SET VisitedCount = VisitedCount + 1 WHERE DPID=@ChannelId AND ZDID=@StationSource "; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@StationSource", StationSource); parameters.Append("@ChannelId", ChannelId); return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0); }
public bool SelectedItem(int userId, IList <SelectedItem> skus, int currentSalesTerritory, bool selected) { string sql = @" UPDATE ShoppingCart SET IsChecked = {0}, LastTime = GETDATE() WHERE UserId = @UserId AND( CountryId=@CountryId or CountryId=3 ) {1}" ; if (skus == null || !skus.Any()) { throw new ArgumentNullException("skus"); } try { var parameters = DbSFO2ORead.CreateParameterCollection(); parameters.Append("@UserId", userId); parameters.Append("@CountryId", currentSalesTerritory); string sqlTmp = " "; if (skus.Count > 1) { var sqlOr = string.Empty; for (int i = 0; i < skus.Count; i++) { var ptmp = "@Sku" + i; if (i == 0) { sqlOr += " Sku=" + ptmp; } else { sqlOr += " or Sku=" + ptmp; } parameters.Append(ptmp, skus[i].Sku); } sqlTmp = " and (" + sqlOr + ") "; } else { sqlTmp = " and Sku=@Sku "; parameters.Append("@Sku", skus[0].Sku); } sql = string.Format(sql, selected ? "1" : "0", sqlTmp); return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0); } catch (Exception ex) { LogHelper.Error(ex); return(false); } }
/// <summary> /// 获取所有区县 /// </summary> /// <param name="language"></param> /// <returns></returns> public IList <AreaModel> GetAllArea(int language) { string sql = "select AreaId,AreaName,ParentId from Area where IsDelete=0 and LanguageVersion=@LanguageVersion AND IsSFSupport=1"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@LanguageVersion", language); return(DbSFO2ORead.ExecuteSqlList <AreaModel>(sql, parameters)); }
/// <summary> /// 删除地址 /// </summary> /// <param name="id"></param> /// <returns></returns> public bool Delete(int id, int userId) { string sql = "Update AddressInfo Set IsEnable=0 Where Id=@Id And UserId=@UserId"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@Id", id); parameters.Append("@UserId", userId); return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0); }
public int InsertTemp(string UserName, string pwd, int UserType) { string sql = @"INSERT INTO CustomerTemp(UserName,UserPwd,UserType) VALUES(@UserName,@UserPwd,@UserType)"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@UserName", UserName); parameters.Append("@UserPwd", pwd); parameters.Append("@UserType", UserType); return(Convert.ToInt32(DbSFO2OMain.ExecuteScalar(CommandType.Text, sql, parameters))); }
/// <summary> /// 更新退款商品数量 /// </summary> /// <param name="ordercode"></param> /// <param name="spu"></param> /// <param name="sku"></param> /// <returns></returns> public bool UpdateRefundQuantity(string ordercode, string spu, string sku) { string sql = "Update OrderProducts Set RefundQuantity=isnull(RefundQuantity,0)+1 Where OrderCode=@OrderCode And Spu=@spu and Sku=@sku;"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@spu", spu); parameters.Append("@sku", sku); parameters.Append("@OrderCode", ordercode); return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0); }
/// <summary> /// 获取所有省份 /// </summary> /// <param name="language"></param> /// <returns></returns> public IList <ProvinceModel> GetAllProvince(string country, int language) { string sql = "select ProvinceId,ProvinceName,ParentId from province where IsDelete=0 and LanguageVersion=@LanguageVersion And ParentId=@ParentId AND IsSFSupport=1"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@LanguageVersion", language); parameters.Append("@ParentId", country); return(DbSFO2ORead.ExecuteSqlList <ProvinceModel>(sql, parameters)); }
public List <RefundProductEntity> getOrderProductCount(string orderCode, string sku) { string sql = @"SELECT roi.RefundCode FROM RefundOrderInfo AS roi LEFT JOIN RefundOrderProducts AS rop ON rop.RefundCode = roi.RefundCode WHERE rop.Sku=@sku AND roi.OrderCode=@OrderCode and roi.RefundStatus!=-1"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@OrderCode", orderCode); parameters.Append("@sku", sku); var list = DbSFO2ORead.ExecuteSqlList <RefundProductEntity>(sql, parameters); return(list.ToList()); }
/// <summary> /// 查询用户的地址数量 /// </summary> /// <param name="userId"></param> /// <param name="countryId"></param> /// <returns></returns> public int GetUserAddressCount(int userId, int countryId) { string sql = "Select count(1) From AddressInfo (nolock) Where UserId=@UserId And CountryId=(select top 1 CountryId From Country Where Id=@CountryId) and IsEnable=1"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@UserId", userId); parameters.Append("@CountryId", countryId); object cnt = DbSFO2OMain.ExecuteScalar(CommandType.Text, sql, parameters); return(cnt == null ? 0 : cnt.AsInt32()); }
/// <summary> /// 设置默认地址 /// </summary> /// <param name="userId"></param> /// <param name="id"></param> /// <param name="countryId"></param> /// <returns></returns> public bool SetDefaultAddress(int userId, int id, int countryId) { string sql = @"Update AddressInfo Set IsDefault=0 Where UserId=@UserId and CountryId=(select top 1 CountryId From Country Where Id=@CountryId); Update AddressInfo Set IsDefault=1 Where Id=@Id;"; var parameters = DbSFO2OMain.CreateParameterCollection(); parameters.Append("@UserId", userId); parameters.Append("@CountryId", countryId); parameters.Append("@Id", id); return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0); }