/// <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);
}
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="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>
/// <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);
}
//通过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);
}
/// <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);
}
/// <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);
}
/// <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="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);
}
/// <summary>
/// 添加新用户酒豆
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool InsertHuoli(CustomerEntity entity, int UserId, decimal huoli, decimal lockHuoli)
{
string sql = @"INSERT INTO [HuoLiTotal] ([UserId],[HuoLi],[LockedHuoLi],[CreateTime],[CreateBy]) Values(
@UserId,@HuoLi,@LockedHuoLi,getdate(),@CreateBy)";
var parameters = DbSFO2OMain.CreateParameterCollection();
parameters.Append("@UserId", UserId);
parameters.Append("@HuoLi", huoli);
parameters.Append("@LockedHuoLi", lockHuoli);
parameters.Append("@CreateBy", 0);
return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0);
}
/// <summary>
/// 修改申诉
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool EditRefund(RefundOrderEntity model)
{
string sql = @"Update RefundOrderInfo
Set RefundReason=@RefundReason,RefundType=@RefundType,RefundDescription=@RefundDescription,RefundStatus=@RefundStatus,ImagePath=@ImagePath,ProductStatus=@ProductStatus
Where RefundCode=@RefundCode;";
var parameters = DbSFO2OMain.CreateParameterCollection();
parameters.Append("@RefundCode", model.RefundCode);
parameters.Append("@RefundType", model.RefundType);
parameters.Append("@RefundReason", model.RefundReason);
parameters.Append("@RefundDescription", model.RefundDescription);
parameters.Append("@RefundStatus", model.RefundStatus);
parameters.Append("@ImagePath", model.ImagePath);
parameters.Append("@ProductStatus", model.ProductStatus);
return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0);
}
/// <summary>
/// 支付失败
/// </summary>
public bool UpdatePayFailure(OrderPaymentEntity entity)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE OrderPayment ");
strSql.Append("SET TradeCode=@TradeCode,PayStatus=@PayStatus, ");
strSql.Append("PayCompleteTime=@PayCompleteTime,PayBackRemark=@PayBackRemark ");
strSql.Append("WHERE PayCode=@PayCode ");
var parameters = DbSFO2OMain.CreateParameterCollection();
parameters.Append("@PayCode", entity.PayCode);
parameters.Append("@TradeCode", entity.TradeCode);
parameters.Append("@PayStatus", entity.PayStatus);
parameters.Append("@PayCompleteTime", entity.PayCompleteTime);
parameters.Append("@PayBackRemark", entity.PayBackRemark);
return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0);
}
/// <summary>
/// 添加新用户
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Add(OrderInfoLogEntity entity)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into OrderInfoLog(");
strSql.Append("OrderCode,CurrentStatus,AfterStatus,OperateIp,Remark,CreateBy,CreateTime)");
strSql.Append(" values (");
strSql.Append("@OrderCode,@CurrentStatus,@AfterStatus,@OperateIp,@Remark,@CreateBy,@CreateTime)");
var parameters = DbSFO2OMain.CreateParameterCollection();
parameters.Append("@OrderCode", entity.OrderCode);
parameters.Append("@CurrentStatus", entity.CurrentStatus);
parameters.Append("@AfterStatus", entity.AfterStatus);
parameters.Append("@OperateIp", entity.OperateIp);
parameters.Append("@Remark", entity.Remark);
parameters.Append("@CreateBy", entity.CreateBy);
parameters.Append("@CreateTime", entity.CreateTime);
return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters) > 0);
}
/// <summary>
/// 点击搜索按钮，更新热词搜索记录表
/// </summary>
/// <param name="keyword">关键词</param>
/// <param name="source">来源:(1.M站 2.PC 3.APP)默认值1</param>
public bool AddSearchHotWordRecord(string keyword, int source)
{
try
{
//将来如果需要区分开来源统计的话
//需要在where条件里添加source属性：where Keyword=@keyword and DATEDIFF(day,SearchDate,GETDATE())=0) and [Source]=@source
string sql = @"DECLARE @Id int,@Currdate int
set @Id=(select top 1 ID from HotWordSearchLog where Keyword=@keyword and DATEDIFF(day,SearchDate,GETDATE())=0)
print @Id
if(@Id>0)
begin
--print '有结果:'+cast(@Id as varchar(10))+'开始执行update操作'
update HotWordSearchLog set SearchCount=SearchCount+1 where ID=@Id
end
else
begin
--print '木有结果,开始执行Insert操作'
INSERT INTO [HotWordSearchLog]([Keyword],[SearchDate],[SearchCount],[Source])
VALUES(@keyword,convert(datetime,convert(varchar(10),getdate(),120)),1,@source)
end";
var parameters = DbSFO2OMain.CreateParameterCollection();
parameters.Append("@keyword", keyword);
parameters.Append("@source", source);
if (DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) == 1)
{
//如果考虑到关键词搜索数量太大的话，那就不用写这个了，这个log有个好处是能统计某个时段的搜索热门词汇
//LogHelper.Info("关键词更新成功！关键词：" + keyword);
return(true);
}
else
{
LogHelper.ErrorMsg("关键词更新失败，关键词：" + keyword);
return(false);
}
}
catch (Exception ex)
{
LogHelper.ErrorMsg(ex.Message);
return(false);
}
}
/// <summary>
/// 退款单商品
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
public bool AddRefundProduct(RefundProductEntity model, string orderCode)
{
string sql = @"Insert Into RefundOrderProducts(RefundCode,spu,sku,Quantity,UnitPrice,RMBUnitPrice,TaxRate,IsBearDuty,HuoLi,Coupon) Values(@RefundCode,@spu,@sku,@Quantity,@UnitPrice,@RMBUnitPrice,@TaxRate,@IsBearDuty,@HuoLi,@Coupon);";
// sql += "Update OrderProducts Set RefundQuantity=isnull(RefundQuantity,0)+1 Where OrderCode=@OrderCode And Spu=@spu and Sku=@sku;";
// sql += @"if exists(select * from OrderProducts where ordercode=@OrderCode and Quantity=RefundQuantity)
// begin
// update OrderInfo set OrderStatus=5 where OrderCode=@OrderCode
// end";
var parameters = DbSFO2OMain.CreateParameterCollection();
parameters.Append("@RefundCode", model.RefundCode);
parameters.Append("@spu", model.Spu);
parameters.Append("@sku", model.Sku);
parameters.Append("@Quantity", model.Quantity);
parameters.Append("@UnitPrice", model.UnitPrice);
parameters.Append("@RMBUnitPrice", model.RMBUnitPrice);
parameters.Append("@TaxRate", model.TaxRate);
parameters.Append("@IsBearDuty", model.IsBearDuty);
parameters.Append("@HuoLi", model.HuoLi);
parameters.Append("@Coupon", model.Coupon);
return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0);
}
/// <summary>
/// 编辑地址
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Edit(AddressEntity model)
{
string sql = @"Update AddressInfo Set ProvinceId=@ProvinceId,CityId=@CityId,AreaId=@AreaId,PostCode=@PostCode,Address=@Address,Receiver=@Receiver,Phone=@Phone,
PapersType=@PapersType,PapersCode=@PapersCode,UpdateTime=@UpdateTime,UpdateBy=@UpdateBy
Where Id=@Id And UserId=@UserId;";
var parameters = DbSFO2ORead.CreateParameterCollection();
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("@PapersType", model.PapersType);
parameters.Append("@PapersCode", model.PapersCode);
parameters.Append("@UpdateTime", model.UpdateTime);
parameters.Append("@UpdateBy", model.UpdateBy);
parameters.Append("@Id", model.Id);
parameters.Append("@UserId", model.UserId);
return(DbSFO2OMain.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0);
}
