/// <summary>
/// 获取活动列表
/// </summary>
/// <param name="userId"></param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <returns></returns>
public List <InformationEntity> GetActivityInfoList(int userId, int pageindex, int pagesize)
{
int startIndex = (pageindex - 1) * pagesize + 1;
int endIndex = pageindex * pagesize;
string sql = @"with msg as
(
select ir.UserId as ReadUserId
,i.Id
,i.Title,i.Content,i.CreateTime
,i.ImagePath,i.LinkUrl
,i.StartTime
from InformationToCustomer itc
LEFT JOIN Information AS i on i.Id=itc.InformationId and i.[Type]=1 and i.WebInnerType=2
LEFT JOIN InformationRead AS ir ON itc.InformationId = ir.InformationId AND ir.UserId = @UserID
where itc.UserId = @UserID
)
select * from (select (select count(1) from msg where StartTime<=GETDATE()) as TotalRecord
,ROW_NUMBER() over(order by StartTime desc)as RIndex,* from msg
where StartTime<=GETDATE()) as a where rindex between @StartIndex and @EndIndex";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@StartIndex", startIndex);
parameters.Append("@EndIndex", endIndex);
parameters.Append("@UserID", userId);
var list = DbSFO2ORead.ExecuteSqlList <InformationEntity>(sql, parameters);
return(list.ToList());
}
/// <summary>
/// 获取某人的订单消息列表
/// </summary>
/// <param name="userId">用户ID</param>
/// <param name="pageindex">页码</param>
/// <param name="pagesize">每页条数</param>
/// <returns>消息List</returns>
public List <InformationEntity> GetOrderInfoList(int userId, int pageindex, int pagesize)
{
int startIndex = (pageindex - 1) * pagesize + 1;
int endIndex = pageindex * pagesize;
string sql = @"with msg as
(
select i.Id
,ir.UserId as ReadUserId
,ROW_NUMBER() over(order by i.createTime desc)as RIndex
,i.Title,i.Content,i.CreateTime
,i.ImagePath,i.LinkUrl
from Information i
left join InformationRead ir on i.Id=ir.InformationId AND ir.UserId = @UserID
where (i.SendUserId=@UserID) and i.[Type]=1 and i.WebInnerType=3
)
select (select count(1) from msg) as TotalRecord,* from msg
where rindex between @StartIndex and @EndIndex";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@StartIndex", startIndex);
parameters.Append("@EndIndex", endIndex);
parameters.Append("@UserID", userId);
var list = DbSFO2ORead.ExecuteSqlList <InformationEntity>(sql, parameters);
return(list.ToList());
}
/// <summary>
/// 退款详情
/// </summary>
/// <param name="refundId"></param>
/// <param name="userId"></param>
/// <returns></returns>
public RefundInfoModel GetRefundInfo(string refundId, int userId)
{
RefundInfoModel model = new RefundInfoModel();
string sql = @"select ISNULL(rp.HuoLi,0.00) AS HuoLi,ro.RefundCode,ro.OrderCode,ro.RefundStatus,ro.RefundType,p.Name,ro.TotalAmount as RefundTotalAmount,ro.RMBTotalAmount as RMBRefundTotalAmount , ro.SupplierId,isnull(ro.ExchangeRate,0)ExchangeRate,
rp.unitPrice,rp.TaxRate, img.ImagePath,sk.MainDicValue,sk.MainValue,sk.SubDicValue,sk.SubValue ,RefundReason,RefundDescription,ro.ImagePath as RefundImagePath,
ro.Commission,DutyAmount,RmbDutyAmount,NoPassReason,isnull(ProductStatus,1)ProductStatus,CompletionTime,ro.CreateTime,PickupTime,ToBePickUpTime,rp.Coupon
from RefundOrderInfo ro
left join RefundOrderProducts rp on ro.RefundCode=rp.RefundCode
left join ProductInfo p on p.Spu=rp.Spu
left join SkuInfo sk on sk.Sku=p.Spu and sk.Sku=rp.Sku
left join ProductImage img on img.Spu=rp.Spu
left join OrderInfo o on o.OrderCode=ro.OrderCode
where img.SortValue=1 and ro.RefundStatus>0 and o.OrderStatus>=0 and ro.RefundCode=@RefundCode and ro.UserId=@UserId";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserId", userId);
parameters.Append("@RefundCode", refundId);
var list = DbSFO2ORead.ExecuteSqlList <RefundInfoModel>(sql, parameters);
if (list.Any())
{
return(list.First());
}
return(model);
}
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);
}
}
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);
}
}
public CustomerEntity GetCustomerEntity(int userId)
{
try
{
const string sql = @"SELECT [ID]
,[UserName]
,[NickName]
,[Password]
,[Mobile]
,[ImageUrl]
,[RegionCode]
,[Gender]
,[PayPassword]
,[Email]
,[Type]
,[Status]
,[LastLoginTime]
,[CreateTime]
,[UpdateBy]
,[UpdateTime]
,[SourceType]
FROM [Customer] (NOLOCK)
WHERE [ID]=@ID ";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@ID", userId);
return(DbSFO2ORead.ExecuteSqlFirst <CustomerEntity>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(null);
}
}
/// <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());
}
///
/// /// <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());
}
/// <summary>
/// 获取购物车列表
/// </summary>
/// <param name="userId"></param>
/// <param name="salesTerritory"></param>
/// <param name="language"></param>
/// <returns></returns>
public int GetMiniShoppingCart(int userId, int salesTerritory, int language)
{
string sql = @" SELECT count(1)
from ShoppingCart sc (NOLOCK)
INNER JOIN skuinfo s ON s.Sku=sc.Sku AND s.[Status] = 3
INNER JOIN productInfo p (NOLOCK) ON p.id=s.SpuId AND p.SalesTerritory=sc.CountryId
WHERE p.LanguageVersion=@LanguageVersion
AND sc.UserId=@UserId
AND (sc.CountryId=@CountryId or sc.CountryId=3 )";
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserId", userId);
parameters.Append("@CountryId", salesTerritory);
parameters.Append("@LanguageVersion", language);
return(DbSFO2ORead.ExecuteSqlScalar <int>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(0);
}
}
/// <summary>
/// 获取有效的促销信息
/// </summary>
/// <param name="skus"></param>
/// <returns></returns>
public IList <PromotionEntity> GetAvaliablePromotionEntities(string[] skus)
{
if (skus == null || !skus.Any())
{
throw new ArgumentException("skus");
}
try
{
string skusTmp = string.Join(",", skus);
string sql = @"DECLARE @str NVARCHAR(500)
SET @str=@Skus
;WITH tbSku
as ( SELECT fs.c1 FROM dbo.func_splitidString(@str,',') AS fs)
SELECT ps.PromotionId, ps.Sku, ps.DiscountRate, ps.DiscountPrice,p.SupplierId,
p.PromotionName, p.StartTime, p.EndTime, p.PromotionLable, p.PromotionCost,
p.PromotionStatus, p.PromotionType, p.CreateTime, p.CreateBy
FROM PromotionSku AS ps
INNER JOIN tbSku AS ts ON ps.Sku=ts.c1
LEFT JOIN promotions p ON ps.PromotionId=p.Id
WHERE p.PromotionStatus=2
";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@Skus", skusTmp);
return(DbSFO2ORead.ExecuteSqlList <PromotionEntity>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(null);
}
}
public bool GetDividedPercentStationZDID(int DPID, string ZDID)
{
try
{
const string sql = @"SELECT
dps.Id,
dps.DPID,
dps.ZDID,
dps.ZDName,
dps.ZDNO,
dps.ZDAddress,
dps.VisitedTimes,
dps.CreateTime,
dps.CreateBy
FROM
DividedPercentStation AS dps
WHERE dps.DPID = @DPID AND dps.ZDID = @ZDID" ;
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@DPID", DPID);
parameters.Append("@ZDID", ZDID);
return(DbSFO2ORead.ExecuteSqlFirst <CustomerEntity>(sql, parameters) != null);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(false);
}
}
/// <summary>
/// 获取有效的促销信息
/// </summary>
/// <param name="skus"></param>
/// <returns></returns>
public IList <PromotionEntity> GetAvaliablePromotionEntitiesTeam(int proid)
{
if (proid == 0)
{
throw new ArgumentException("proid");
}
try
{
string sql = @"SELECT ps.PromotionId, ps.Sku, ps.DiscountRate, ps.DiscountPrice,p.SupplierId,
p.PromotionName, p.StartTime, p.EndTime, p.PromotionLable, p.PromotionCost,
p.PromotionStatus, p.PromotionType, p.CreateTime, p.CreateBy
FROM PromotionSku AS ps
LEFT JOIN promotions p ON ps.PromotionId=p.Id
WHERE ps.PromotionId = @PromotionId
ORDER BY p.ID desc
";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@PromotionId", proid);
return(DbSFO2ORead.ExecuteSqlList <PromotionEntity>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(null);
}
}
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);
}
}
/// <summary>
/// 注册成功后，获得优惠券批次信息
/// </summary>
/// <param name="RegisterTime"></param>
/// <returns></returns>
public List <GiftCardBatchEntity> GetGiftCardBatchEntityForRegisterSucc(DateTime RegisterTime)
{
string sql = @"SELECT gcb.BatchId
,gcb.BatchName
,gcb.CardSum
,gcb.CardNumber
,gcb.CardType
,gcb.BeginTime
,gcb.EndTime
,gcb.ExpiryDays
,gcb.SatisfyPrice
,gcb.SatisfyUser
,gcb.SatisfyProduct
,gcb.Remarks
,gcb.DownloadCounts
,gcb.[Enable]
,gcb.CreateTime
,gcb.Creater
FROM GiftCardBatch AS gcb
WHERE gcb.[Enable] = 1
AND gcb.EndTime >= @RegisterTime AND gcb.BeginTime <= @RegisterTime" ;
try
{
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@RegisterTime", RegisterTime);
return(DbSFO2ORead.ExecuteSqlList <GiftCardBatchEntity>(sql, parameters).ToList());
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(new List <GiftCardBatchEntity>());
}
}
public OrderPaymentEntity GetOrderPaymentByPayCode(string PayCode)
{
const string sql = @"SELECT [Id]
,[PayCode]
,[TradeCode]
,[UserId]
,[OrderType]
,[OrderCode]
,[PayAmount]
,[PaidAmount]
,[PayPlatform]
,[PayType]
,[PayStatus]
,[PayTerminal]
,[PayCompleteTime]
,[PayBackRemark]
,[Remark]
,[CreateTime]
,[CreateBy]
FROM [OrderPayment] (NOLOCK)
WHERE PayCode=@PayCode ";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@PayCode", PayCode);
return(DbSFO2ORead.ExecuteSqlFirst <OrderPaymentEntity>(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="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 int GetUserTempByUserName(string UserName)
{
string sql = "SELECT COUNT(1) FROM CustomerTemp AS c WHERE c.UserName = @UserName ";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserName", UserName);
return(Convert.ToInt32(DbSFO2ORead.ExecuteScalar(CommandType.Text, sql, parameters)));
}
/// <summary>
/// 获取用户所在地区
/// </summary>
/// <param name="userid">用户id</param>
/// <returns>1.大陆 2.中华人民共和国大陆地区</returns>
public int GetUserRegion(int userid)
{
string sql = @"select case RegionCode when '86' then 1 else 2 end as 'SendID' from Customer where ID=@UserID";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserID", userid);
return(Convert.ToInt32(DbSFO2ORead.ExecuteScalar(CommandType.Text, sql, parameters)));
}
/// <summary>
/// 明星商品
/// </summary>
/// <param name="id"></param>
/// <param name="ExchangeRate"></param>
/// <param name="userId"></param>
/// <returns></returns>
public IList <ProductInfoModel> getProductList(int id, decimal ExchangeRate, int userId)
{
string sql = string.Empty;
if (userId == 0)
{
sql = @"SELECT DISTINCT bsp.Spu,bsp.SortValue,ps.DiscountRate,CASE WHEN ISNULL(bsp.ImageUrl,'') = ''
THEN (SELECT pi3.ImagePath
FROM ProductImage AS pi3 WHERE pi3.Spu=pi2.Spu AND pi3.SortValue=1)
ELSE bsp.ImageUrl
END
AS ImagePath,pi2.Name,bsp.[Description] as Description,ps.DiscountPrice * @ExchangeRate as DiscountPrice,pi2.MinPrice * @ExchangeRate as MinPrice,pi2.Spu AS SPU
,CASE
WHEN (SELECT isnull(SUM(s.ForOrderQty),0) FROM Stock AS s WHERE s.Spu=si.Spu)>
pi2.MinForOrder THEN 1
ELSE 2 END as compare
FROM BrandStarProduct AS bsp
LEFT JOIN ProductInfo AS pi2 ON pi2.Spu = bsp.Spu AND pi2.BrandId = bsp.BrandId
LEFT JOIN SkuInfo AS si ON si.SpuId = pi2.Id
LEFT JOIN Stock AS s ON s.Spu = si.Spu AND s.Sku = si.Sku
LEFT JOIN PromotionSku AS ps ON ps.Spu=si.Spu AND ps.Sku = si.Sku
AND ps.PromotionId IN(
SELECT p.Id FROM Promotions AS p WHERE p.PromotionStatus=2 AND p.PromotionType=1
)
WHERE pi2.LanguageVersion=1 AND bsp.BrandId=@id
AND EXISTS (SELECT * FROM SkuInfo AS si2 WHERE si2.[Status]=3 AND pi2.LanguageVersion=1 AND si2.Spu=bsp.Spu) ORDER BY bsp.SortValue asc";
}
else
{
sql = @"SELECT DISTINCT bsp.Spu,bsp.SortValue,fi.spu AS fiSpu,ps.DiscountRate,CASE WHEN ISNULL(bsp.ImageUrl,'') = ''
THEN (SELECT pi3.ImagePath
FROM ProductImage AS pi3 WHERE pi3.Spu=pi2.Spu AND pi3.SortValue=1)
ELSE bsp.ImageUrl
END
AS ImagePath,pi2.Name,bsp.[Description] as Description,ps.DiscountPrice * @ExchangeRate as DiscountPrice,pi2.MinPrice * @ExchangeRate as MinPrice,pi2.Spu AS SPU
,CASE
WHEN (SELECT isnull(SUM(s.ForOrderQty),0) FROM Stock AS s WHERE s.Spu=si.Spu)>
pi2.MinForOrder THEN 1
ELSE 2 END as compare
FROM BrandStarProduct AS bsp
LEFT JOIN ProductInfo AS pi2 ON pi2.Spu = bsp.Spu AND pi2.BrandId = bsp.BrandId
LEFT JOIN SkuInfo AS si ON si.SpuId = pi2.Id
LEFT JOIN Stock AS s ON s.Spu = si.Spu AND s.Sku = si.Sku
LEFT JOIN PromotionSku AS ps ON ps.Spu=si.Spu AND ps.Sku = si.Sku
AND ps.PromotionId IN(
SELECT p.Id FROM Promotions AS p WHERE p.PromotionStatus=2 AND p.PromotionType=1
)
LEFT JOIN FavoriteInfo AS fi ON fi.Spu = pi2.Spu AND fi.userId=@userId AND fi.isDelete=0
WHERE pi2.LanguageVersion=1 AND bsp.BrandId=@id
AND EXISTS (SELECT * FROM SkuInfo AS si2 WHERE si2.[Status]=3 AND pi2.LanguageVersion=1 AND si2.Spu=bsp.Spu) ORDER BY bsp.SortValue asc";
}
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@id", id);
parameters.Append("@ExchangeRate", ExchangeRate);
parameters.Append("@userId", userId);
return(DbSFO2ORead.ExecuteSqlList <ProductInfoModel>(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));
}
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);
}
}
// 通过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="UserId"></param>
/// <returns></returns>
public IList <InformationEntity> GetInformationLast(int UserId)
{
try
{
string sql = @" SELECT * FROM
(SELECT TOP 1 i.Id
,i.[Type] AS InfoType
,i.WebInnerType
,i.Title
,i.CreateTime
FROM Information AS i
WHERE i.WebInnerType = 1 and i.CreateTime>=(select c.CreateTime from Customer c where ID=@UserId)
and i.SendDest in((select case RegionCode when '86' then 1 else 2 end as 'SendID' from Customer where ID=@UserId),3)
ORDER BY i.CreateTime DESC) AS SystemInfo
UNION ALL
SELECT * FROM
(SELECT TOP 1 i.Id
,i.[Type] AS InfoType
,i.WebInnerType
,i.Title
,i.StartTime
FROM InformationToCustomer AS itc
INNER JOIN Information AS i ON i.Id = itc.InformationId and i.StartTime<=GETDATE()
WHERE itc.UserId = @UserId AND itc.Visible = 1
ORDER BY i.StartTime DESC) AS ActivityInfo
UNION ALL
SELECT * FROM
(SELECT TOP 1 i.Id
,i.[Type] AS InfoType
,i.WebInnerType
,i.Title
,i.CreateTime
FROM Information AS i
LEFT JOIN InformationRead AS ir ON ir.InformationId = i.Id AND ir.UserId = @UserId
WHERE i.WebInnerType = 3 AND i.SendUserId = @UserId
ORDER BY i.CreateTime DESC) AS OrderInfo
UNION ALL
SELECT * FROM
(SELECT TOP 1 i.Id
,i.[Type] AS InfoType
,i.WebInnerType
,i.Title
,i.CreateTime
FROM Information AS i
WHERE i.WebInnerType = 4 AND i.SendUserId = @UserId
ORDER BY i.CreateTime DESC) AS WebInnerSystemInfo" ;
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@UserId", UserId);
return(DbSFO2ORead.ExecuteSqlList <InformationEntity>(sql, parameters));
}
catch (Exception ex)
{
LogHelper.Error(ex);
return(null);
}
}
/// <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="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 OrderPaymentEntity selectOrderCode(string OrderCode, int PayPlatform)
{
const string sql = @"SELECT TOP 1 PayCode as PayCode FROM OrderPayment AS op WHERE op.OrderCode=@OrderCode AND op.PayPlatform=@PayPlatform ORDER BY op.CreateTime desc";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@OrderCode", OrderCode);
parameters.Append("@PayPlatform", PayPlatform);
return(DbSFO2ORead.ExecuteSqlFirst <OrderPaymentEntity>(sql, parameters));
}
/// <summary>
/// 生成结算单顺序号
/// </summary>
/// <param name="refundCode"></param>
/// <returns></returns>
public int GetSettleCodeNo(string orderCode)
{
string sql = "Select Count(1) From SettlementOrderInfo Where OrderCode=@OrderCode";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@OrderCode", orderCode);
object obj = DbSFO2ORead.ExecuteScalar(CommandType.Text, sql, parameters);
return(obj == null ? 0 : Convert.ToInt32(obj));
}
/// <summary>
/// 查看当前sku qty数量 必须是上架中的商品
/// </summary>
/// <param name="spu"></param>
/// <param name="sku"></param>
/// <returns></returns>
public StockEntity getStockInfo(string spu, string sku)
{
string sql = @" SELECT s.Spu,s.Sku,s.Qty,s.SQty,s.MinSQty,ISNULL(s.LockedQty,0) AS LockedQty,s.ForOrderQty,s.Updatetime,s.Updateby
FROM Stock AS s (NOLOCK) WHERE s.Spu=@spu AND s.Sku=@sku ";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@spu", spu);
parameters.Append("@sku", sku);
return(DbSFO2ORead.ExecuteSqlFirst <StockEntity>(sql, parameters));
}
/// <summary>
///根据系统消息ID获取消息
/// </summary>
/// <param name="infoid">消息id</param>
/// <returns>消息对象</returns>
public InformationEntity GetSysInfoById(int infoid)
{
string sql = @"select SendUserId,Title,Content,CreateTime,SendUserId from Information where [Id]=@InfoId";
var parameters = DbSFO2ORead.CreateParameterCollection();
parameters.Append("@InfoId", infoid);
var model = DbSFO2ORead.ExecuteSqlFirst <InformationEntity>(sql, parameters);
return(model);
}
