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 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="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);
}
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>
/// 更新orderProduct表
/// </summary>
public bool UpdateOrderProduct(OrderProductsEntity model)
{
string strSql = " UPDATE OrderProducts SET SFQty =@SFQty,MQty =@MQty WHERE OrderCode=@OrderCode AND Spu=@Spu AND Sku=@Sku ";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@OrderCode", model.OrderCode);
parameters.Append("@Spu", model.Spu);
parameters.Append("@MQty", model.MQty);
parameters.Append("@SFQty", model.SFQty);
parameters.Append("@Sku", model.Sku);
return(DbSFO2OMain.ExecuteSqlNonQuery(strSql, parameters) > 0);
}
/// <summary>
/// 更新已存在的产品数量
/// </summary>
/// <param name="userId"></param>
/// <param name="sku"></param>
/// <param name="qty"></param>
/// <param name="salesTerritory"></param>
/// <returns></returns>
public bool UpdateQty(int userId, string sku, int qty, int salesTerritory, PromotionEntity promotion, bool isSum = false)
{
//TODO：更新购物车的促销价格
string sqlSum = @"UPDATE [ShoppingCart]
SET
[Quantity] = Quantity+@Quantity
,[LastTime] = @LastTime
,[SortTime] = @SortTime
,[IsChecked] = 1
{0}
WHERE [UserId]=@UserId and ( [CountryId]=@CountryId or CountryId=3 ) and [Sku]=@Sku ";
string sql = @"UPDATE [ShoppingCart]
SET
[Quantity] = @Quantity
,[LastTime] = @LastTime
,[SortTime] = @SortTime
{0}
WHERE [UserId]=@UserId and ( [CountryId]=@CountryId or CountryId=3 ) and [Sku]=@Sku ";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserId", userId);
parameters.Append("@CountryId", salesTerritory);
parameters.Append("@Sku", sku);
parameters.Append("@Quantity", qty);
parameters.Append("@LastTime", DateTime.Now);
parameters.Append("@SortTime", DateTime.Now);
//if (promotion != null)
//{
//sqlSum = string.Format(sqlSum, ",DiscountPrice=@DiscountPrice");
//sql = string.Format(sql, ",DiscountPrice=@DiscountPrice");
//parameters.Append("@DiscountPrice", promotion.DiscountPrice);
//}
//else
//{
sqlSum = string.Format(sqlSum, " ");
sql = string.Format(sql, " ");
//}
return(DbSFO2OMain.ExecuteSqlNonQuery(isSum ? sqlSum : sql, parameters) > 0);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
}
/// <summary>
/// 更新我的酒豆详情
/// </summary>
public bool updateHuoLiTotal(decimal huoli, int userId)
{
string sql = @" UPDATE HuoLiTotal SET HuoLi = HuoLi+@huoli WHERE UserId=@userId";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@huoli", huoli);
parameters.Append("@userId", userId);
return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
}
public bool updateIsPush(int userId, int type)
{
string sql = @"UPDATE Customer SET IsPushingInfo =@type WHERE ID=@userId";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@userId", userId);
parameters.Append("@type", type);
return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
}
/// <summary>
/// 下单后删除购物车
/// </summary>
/// <param name="userId"></param>
/// <param name="currentSalesTerritory"></param>
/// <returns></returns>
public bool DeleteByUserId(object userId, int currentSalesTerritory)
{
string sql = @" DELETE FROM [ShoppingCart] WHERE (CountryId=@CountryId or CountryId=3) AND IsChecked = 1 AND UserId=@UserId ";
StringBuilder sbSql = new StringBuilder();
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserId", userId);
parameters.Append("@CountryId", currentSalesTerritory);
return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
}
/// <summary>
/// 插入我的酒豆log
/// </summary>
public bool insertHuoliLog(MyHL myHL)
{
string sql = @" INSERT INTO HuoLiLog
(
UserId,
Direction,
OriginalHuoLi,
ChangedHuoLi,
CurrentHuoLi,
[Description],
TradeCode,
CreateTime
)
VALUES
(
@UserId,
@Direction,
@OriginalHuoLi,
@ChangedHuoLi,
@CurrentHuoLi,
@Description,
@TradeCode,
@CreateTime
)";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserId", myHL.userId);
parameters.Append("@Direction", myHL.Direction);
parameters.Append("@OriginalHuoLi", myHL.OriginalHuoLi);
parameters.Append("@ChangedHuoLi", myHL.ChangedHuoLi);
parameters.Append("@CurrentHuoLi", myHL.CurrentHuoLi);
parameters.Append("@Description", myHL.Description);
parameters.Append("@TradeCode", myHL.TradeCode);
parameters.Append("@CreateTime", myHL.addTime);
return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
}
/// <summary>
/// 添加sku信息
/// </summary>
/// <param name="userId"></param>
/// <param name="qty"></param>
/// <param name="exchangeRate"></param>
/// <param name="entity"></param>
/// <returns></returns>
public bool AddSkuInfo(int userId, int qty, decimal exchangeRate, Model.Product.ProductSkuEntity entity, PromotionEntity promotion)
{
//TODO：更新购物车的促销价格
string sql = @"INSERT INTO [ShoppingCart]( [ShoppingCartId],[UserId] ,[CountryId] ,[Spu] ,[Sku] ,[UnitPrice] ,[Quantity]
,[TaxRate] ,[ExchangeRate] ,[CartStatus] ,[IsChecked] ,[CreateTime] ,[LastTime],[SortTime] {0}) VALUES
( @ShoppingCartId ,@UserId ,@CountryId ,@Spu ,@Sku ,@UnitPrice ,@Quantity ,@TaxRate ,@ExchangeRate ,@CartStatus ,@IsChecked ,@CreateTime ,@LastTime ,@SortTime {1}) ";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@ShoppingCartId", Guid.NewGuid());
parameters.Append("@UserId", userId);
parameters.Append("@CountryId", entity.SalesTerritory);
parameters.Append("@Spu", entity.Spu);
parameters.Append("@Sku", entity.Sku);
parameters.Append("@UnitPrice", entity.Price);
parameters.Append("@Quantity", qty);
parameters.Append("@TaxRate", entity.TaxRate);
parameters.Append("@ExchangeRate", exchangeRate);
parameters.Append("@CartStatus", 1);
parameters.Append("@IsChecked", 1);
parameters.Append("@CreateTime", DateTime.Now);
parameters.Append("@LastTime", DateTime.Now);
parameters.Append("@SortTime", DateTime.Now);
if (promotion != null)
{
sql = string.Format(sql, ",DiscountPrice", ",@DiscountPrice");
parameters.Append("@DiscountPrice", promotion.DiscountPrice);
}
else
{
sql = string.Format(sql, " ", "");
}
return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
}
/// <summary>
/// 确认收货
/// </summary>
/// <param name="orderCode"></param>
/// <returns></returns>
public bool ConfirmOrder(string orderCode)
{
string sql = @" UPDATE OrderInfo
SET
OrderStatus =@OrderStatus
,OrderCompletionTime=GETDATE()
WHERE OrderCode=@OrderCode ";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@OrderCode", orderCode);
parameters.Append("@OrderStatus", OrderStatusEnum.Complete);
return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
//对为申诉的商品生成结算单
}
public bool updateActivityInfoVisible(int userId, int visible, DateTime currentTime)
{
string sql = @"UPDATE InformationToCustomer
SET InformationToCustomer.Visible = @visible
FROM InformationToCustomer
INNER JOIN Information
ON Information.Id = InformationToCustomer.InformationId
WHERE InformationToCustomer.UserId = @userId
AND Information.StartTime > @currentTime" ;
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@userId", userId);
parameters.Append("@visible", visible);
parameters.Append("@currentTime", currentTime);
return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) >= 0);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
}
public bool DeleteItem(object userId, string[] sku, int currentSalesTerritory)
{
string sql = @" DELETE FROM [ShoppingCart] WHERE UserId=@UserId AND (CountryId=@CountryId or CountryId=3) {0}";
StringBuilder sbSql = new StringBuilder();
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserId", userId);
parameters.Append("@CountryId", currentSalesTerritory);
for (int index = 0; index < sku.Length; index++)
{
var s = sku[index];
sbSql.Append(string.Format(sql, " AND Sku=@sku" + index + " "));
parameters.Append("@Sku" + index, s);
}
return(DbSFO2OMain.ExecuteSqlNonQuery(sbSql.ToString(), parameters) > 0);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
}
/// <summary>
/// 新增结算商品
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
public bool AddSettleProduct(SettleProductEntity e)
{
string sql = @"Insert into SettlementOrderProducts(SettlementCode,spu,Sku,Quantity,UnitPrice,RmbUnitPrice,TaxRate,RmbAmount,Amount,RmbTaxAmount,TaxAmount,RmbSettlementAmount,SettlementAmount,IsBearDuty,Commission)
Values(@SettlementCode,@spu,@Sku,@Quantity,@UnitPrice,@RmbUnitPrice,@TaxRate,@RmbAmount,@Amount,@RmbTaxAmount,@TaxAmount,@RmbSettlementAmount,@SettlementAmount,@IsBearDuty,@Commission);";
var parameters = DbSFO2OMain.CreateParameterCollection();
parameters.Append("@SettlementCode", e.SettlementCode);
parameters.Append("@spu", e.Spu);
parameters.Append("@Sku", e.Sku);
parameters.Append("@Quantity", e.Quantity);
parameters.Append("@UnitPrice", e.UnitPrice);
parameters.Append("@RmbUnitPrice", e.RmbUnitPrice);
parameters.Append("@TaxRate", e.TaxRate);
parameters.Append("@RmbAmount", e.RmbAmount);
parameters.Append("@Amount", e.Amount);
parameters.Append("@RmbTaxAmount", e.RmbTaxAmount);
parameters.Append("@TaxAmount", e.TaxAmount);
parameters.Append("@RmbSettlementAmount", e.RmbSettlementAmount);
parameters.Append("@SettlementAmount", e.SettlementAmount);
parameters.Append("@IsBearDuty", e.IsBearDuty);
parameters.Append("@Commission", e.Commission);
return(DbSFO2OMain.ExecuteSqlNonQuery(sql, parameters) > 0);
}
public bool UpdateItem(string newSku, int userId, string sku, int qty, int salesTerritory, bool isExistNew, Model.Product.ProductSkuEntity entity)
{
var sqlDel = @"DELETE FROM [ShoppingCart] WHERE UserId=@UserId AND Sku=@sku and (CountryId=@CountryId or CountryId=3) ";
var sqlUpdate = @" update [ShoppingCart]
SET Sku = @NewSku ,
[LastTime] = GETDATE() ,
[Quantity] = {0} @Quantity,
UnitPrice=@UnitPrice,
TaxRate=@TaxRate
WHERE UserId=@UserId AND Sku=@Sku AND ( CountryId=@CountryId or CountryId=3 ) ";
sqlUpdate = string.Format(sqlUpdate, isExistNew ? " Quantity +" : "");
if (!isExistNew)
{
try
{
var parametersUpdate = DbSFO2OMain.CreateParameterCollection();
parametersUpdate.Append("@NewSku", newSku);
parametersUpdate.Append("@Quantity", qty);
parametersUpdate.Append("@Sku", sku);
parametersUpdate.Append("@UserId", userId);
parametersUpdate.Append("@CountryId", salesTerritory);
parametersUpdate.Append("@UnitPrice", entity.Price);
parametersUpdate.Append("@TaxRate", entity.TaxRate);
var result = DbSFO2OMain.ExecuteSqlNonQuery(sqlUpdate, parametersUpdate);
return(result > 0);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
}
//已存在新的
using (var con = DbSFO2OMain.CreateConnection())
{
con.Open();
var trans = con.BeginTransaction();
try
{
var parametersDel = DbSFO2OMain.CreateParameterCollection();
parametersDel.Append("@UserId", userId);
parametersDel.Append("@CountryId", salesTerritory);
parametersDel.Append("@Sku", sku);
var parametersUpdate = DbSFO2OMain.CreateParameterCollection();
parametersUpdate.Append("@NewSku", newSku);
parametersUpdate.Append("@Quantity", qty);
parametersUpdate.Append("@Sku", newSku);
parametersUpdate.Append("@UserId", userId);
parametersUpdate.Append("@CountryId", salesTerritory);
parametersUpdate.Append("@UnitPrice", entity.Price);
parametersUpdate.Append("@TaxRate", entity.TaxRate);
var delete = DbSFO2OMain.ExecuteSqlNonQuery(sqlDel, parametersDel, trans);
var result = DbSFO2OMain.ExecuteSqlNonQuery(sqlUpdate, parametersUpdate, trans);
if (delete > 0 && result > 0)
{
trans.Commit();
return(true);
}
else
{
trans.Rollback();
return(false);
}
}
catch (Exception ex)
{
LogHelper.Error(ex);
trans.Rollback();
return(false);
}
}
}