/// <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);
}
