public static List <ProductPriceModel> FetchProductList(ProductListRequest request, PagerModel pager, bool isExport)
{
#region SQL
var SqlStr = @"SELECT T.CP_Brand AS Brand ,
T.PID AS PID ,
T.DisplayName AS ProductName ,
S.cost AS Cost ,
P.LowestPrice AS LowestLimit ,
T.cy_list_price AS Price ,
S.taobao_tuhuid AS TBPID ,
S.taobao_tuhuprice AS TBPrice ,
S.taobao2_tuhuid AS TB2PID ,
S.taobao2_tuhuprice AS TB2Price ,
S.tianmao1_tuhuprice AS TM1Price ,
S.tianmao1_tuhuid AS TM1PID ,
S.tianmao2_tuhuprice AS TM2Price ,
S.tianmao2_tuhuid AS TM2PID ,
S.tianmao3_tuhuprice AS TM3Price ,
S.tianmao3_tuhuid AS TM3PID ,
S.tianmao4_tuhuid AS TM4PID ,
S.tianmao4_tuhuprice AS TM4Price ,
S.jingdongflagship_tuhuprice AS JDFlagShipPrice ,
S.jingdongflagship_tuhuid AS JDFlagShipPID ,
S.jingdong_tuhuprice AS JDPrice ,
S.jingdong_tuhuid AS JDPID,
e.CanUseCoupon as CanUseCoupon,
s.num_threemonth,
p.LowestPrice_Normal,
p.LowestPrice_Promotion,
p.CouponPrice_Normal,
p.CouponPrice_Promotion
FROM Tuhu_productcatalog..vw_Products AS T WITH ( NOLOCK )
LEFT JOIN Tuhu_productcatalog..TireLowestPrice AS P WITH ( NOLOCK ) ON T.PID = P.PID
LEFT JOIN Tuhu_bi..dm_Product_SalespredictData AS S WITH ( NOLOCK ) ON T.PID = S.PID
left join Tuhu_productcatalog..tbl_ProductExtraProperties as e WITH ( NOLOCK ) on e.pid=t.pid
WHERE T.IsShow = 1
AND ( ( T.PID LIKE '%' + @pid + '%'
OR @pid IS NULL
)
AND T.PID LIKE 'TR-%'
)
AND ( T.DisplayName LIKE '%' + @productname + '%'
OR @productname IS NULL
)
AND ( T.CP_Brand = @Brand
OR @Brand IS NULL
)
AND ( @OnSaleStatus = 0
OR @OnSaleStatus = 1
AND T.OnSale = 1
OR @OnSaleStatus = 2
AND T.OnSale = 0
)
AND ( {0}
)" ;
SqlStr += isExport ? ";" : @"ORDER BY s.num_threemonth desc
OFFSET @start ROWS FETCH NEXT @step ROWS ONLY;" ;
#endregion +
pager.TotalItem = FetchProductListCount(request);
List <string> ManyCustomPrice = request.ManyCustomPrice?.Split('|').ToList();
int Contrast = request.Contrast;
string SingleCustomPrice = GetSqlParameter(request.SingleCustomPrice);
double Proportion = request.Proportion;
var sqlPara = "1 = 1";
if (request.Type > 0 && ManyCustomPrice != null && !string.IsNullOrWhiteSpace(SingleCustomPrice) && Contrast != 0)
{
sqlPara = "1 <> 1";
var ContrastSig = Contrast == -1 ? "<=" : ">=";
foreach (var item in ManyCustomPrice)
{
var realPara = GetSqlParameter(item);
if (!string.IsNullOrWhiteSpace(realPara))
{
sqlPara += $@" OR ( ISNULL({realPara}, 0) > 0
AND {realPara} / IIF(ISNULL({SingleCustomPrice}, 0) = 0, 1, {SingleCustomPrice}) {ContrastSig} {Proportion})" ;
}
}
}
//string.Format(SqlStr, sqlPara);
SqlStr = string.Format(SqlStr, sqlPara);
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
using (var cmd = new SqlCommand(SqlStr))
{
cmd.CommandTimeout = 3 * 60;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@pid", request.PID);
cmd.Parameters.AddWithValue("@productname", request.ProductName);
cmd.Parameters.AddWithValue("@OnSaleStatus", request.OnSale);
cmd.Parameters.AddWithValue("@Brand", request.Brand);
cmd.Parameters.AddWithValue("@start", (request.PageIndex - 1) * request.PageSize);
cmd.Parameters.AddWithValue("@step", request.PageSize);
var result = dbHelper.ExecuteDataTable(cmd);
return(result.ConvertTo <ProductPriceModel>()?.ToList() ?? new List <ProductPriceModel>());
}
}
}
public static int FetchProductListCount(ProductListRequest request)
{
#region SQL
var SqlStr = @"SELECT COUNT(1)
FROM Tuhu_productcatalog..vw_Products AS T WITH ( NOLOCK )
LEFT JOIN Tuhu_productcatalog..TireLowestPrice AS P WITH ( NOLOCK ) ON T.PID = P.PID
LEFT JOIN Tuhu_bi..dm_Product_SalespredictData AS S WITH ( NOLOCK ) ON T.PID = S.PID
WHERE T.IsShow = 1
AND ( ( T.PID LIKE '%' + @pid + '%'
OR @pid IS NULL
)
AND T.PID LIKE 'TR-%'
)
AND ( T.DisplayName LIKE '%' + @productname + '%'
OR @productname IS NULL
)
AND ( T.CP_Brand = @Brand
OR @Brand IS NULL
)
AND ( @OnSaleStatus = 0
OR @OnSaleStatus = 1
AND T.OnSale = 1
OR @OnSaleStatus = 2
AND T.OnSale = 0
)
AND ( {0}
);" ;
#endregion
List <string> ManyCustomPrice = request.ManyCustomPrice?.Split('|').ToList();
int Contrast = request.Contrast;
string SingleCustomPrice = GetSqlParameter(request.SingleCustomPrice);
double Proportion = request.Proportion;
var sqlPara = "1=1";
if (request.Type > 0 && ManyCustomPrice != null && !string.IsNullOrWhiteSpace(SingleCustomPrice) && Contrast != 0)
{
sqlPara = "1 <> 1";
var ContrastSig = Contrast == -1 ? "<=" : ">=";
foreach (var item in ManyCustomPrice)
{
var realPara = GetSqlParameter(item);
if (!string.IsNullOrWhiteSpace(realPara))
{
sqlPara += $@" OR ( ISNULL({realPara}, 0) > 0
AND {realPara} / IIF(ISNULL({SingleCustomPrice}, 0) = 0, 1, {SingleCustomPrice}) {ContrastSig} {Proportion})" ;
}
}
}
SqlStr = string.Format(SqlStr, sqlPara);
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
using (var cmd = new SqlCommand(SqlStr))
{
cmd.CommandTimeout = 3 * 60;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@pid", request.PID);
cmd.Parameters.AddWithValue("@productname", request.ProductName);
cmd.Parameters.AddWithValue("@OnSaleStatus", request.OnSale);
cmd.Parameters.AddWithValue("@Brand", request.Brand);
var result = dbHelper.ExecuteScalar(cmd);
if (Int32.TryParse(result?.ToString(), out int value))
{
return(value);
}
return(0);
}
}
}
public static IEnumerable <SelectListModel> SelectListNew(string Departments, string VehicleIDS,
string PriceRanges, string VehicleBodyTypes, string Specifications, string Brands, int IsRof, string PID,
string Province, string City, decimal?StartPrice, decimal?EndPrice)
{
string rofCondition = "";
if (IsRof == 3)
{
rofCondition = " AND CHARINDEX(SS.Item,T.RofTireSize)>0";
}
else if (IsRof == 2)
{
rofCondition = " AND CHARINDEX(SS.Item,T.RofTireSize)<=0";
}
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
#region SQL
var sql = string.Format(@"SELECT TT.BrandCategory,
TT.Brand,
TT.Vehicle,
TT.Tires,
TT.MinPrice,
TT.TiresMatch,
TT.VehicleId,
TT.TireSize,
VTR.PID,
VTR.Postion,
BI.ProductId,
VTR.PKID,
VTR.Reason,
BI.RowNumber,
VTR.RecommendedPicture,VTR.StartTime,VTR.EndTime
FROM ( SELECT T.BrandCategory,
T.Brand,
T.Vehicle,
T.Tires,
T.MinPrice,
T.TiresMatch,
T.RofTireSize,
T.VehicleId,
SS.Item AS TireSize
FROM ( SELECT VT.BrandCategory,
VT.Brand,
VT.Vehicle,
VT.Tires,
VT.MinPrice,
VT.TiresMatch,
VT.RofTireSize,
VT.ProductID AS VehicleId
FROM Gungnir.dbo.tbl_Vehicle_Type AS VT WITH ( NOLOCK )
WHERE (
VT.ProductID IN ( SELECT *
FROM Gungnir..Split(@VehicleIDS, ',') AS S )
OR @VehicleIDS = N'全部' )
AND (
VT.BrandCategory IN ( SELECT *
FROM Gungnir..Split(@Departments, ',') AS S )
OR @Departments = N'全部'
OR (
CHARINDEX(N'其它', @Departments) > 0
AND ISNULL(VT.BrandCategory, '') = '' ) )
AND (
@PriceRanges = N'全部'
OR (
CHARINDEX(N'高', @PriceRanges) > 0
AND VT.MinPrice >= 16 )
OR (
CHARINDEX(N'中', @PriceRanges) > 0
AND VT.MinPrice < 16
AND VT.MinPrice >= 8 )
OR (
CHARINDEX(N'Between',@PriceRanges) > 0
AND VT.MinPrice >= @StartPrice
AND VT.MinPrice <= @EndPrice
)
OR (
CHARINDEX(N'低', @PriceRanges) > 0
AND VT.MinPrice < 8
AND ISNULL(VT.MinPrice, 0) > 0 )
)
AND (
@VehicleBodyTypes = N'全部'
OR VT.VehicleBodyType IN ( SELECT *
FROM Gungnir..Split(@VehicleBodyTypes, ',') AS S2 ) )
AND ISNULL(VT.Tires, '') <> ''
) AS T
CROSS APPLY Gungnir..SplitString(T.Tires, ';', 1) AS SS
WHERE (@Specifications =N'不限' OR SS.Item IN (SELECT * FROM Gungnir.dbo.Split(@Specifications,',') AS S3))
{0}
) AS TT
LEFT JOIN Tuhu_productcatalog.dbo.tbl_VehicleTireRecommend AS VTR WITH ( NOLOCK )
ON TT.VehicleId = VTR.VehicleId COLLATE Chinese_PRC_CI_AS
AND TT.TireSize = VTR.TireSize COLLATE Chinese_PRC_CI_AS" , rofCondition);
if (string.IsNullOrWhiteSpace(Province) || string.IsNullOrWhiteSpace(City))
{
sql += @" LEFT JOIN ( SELECT T.VehicleId,
T.TireSize,
T.ProductId,
T.RowNumber
FROM (SELECT CTR.VehicleId,
CTR.TireSize,
CTR.ProductId,
ROW_NUMBER() OVER(PARTITION BY CTR.VehicleId, CTR.TireSize ORDER BY CTR.Grade DESC) AS RowNumber
FROM Tuhu_bi.dbo.tbl_CarTireRecommendation AS CTR WITH(NOLOCK)
) AS T
WHERE T.RowNumber <= 4
) AS BI
ON BI.VehicleId = TT.VehicleId COLLATE Chinese_PRC_CI_AS AND BI.TireSize COLLATE Chinese_PRC_CI_AS = TT.TireSize";
}
else
{
sql += @" LEFT JOIN ( SELECT T.VehicleId,
T.TireSize,
T.ProductId,
T.RowNumber
FROM (SELECT CTR.VehicleId,
CTR.TireSize,
CTR.ProductId,
ROW_NUMBER() OVER(PARTITION BY CTR.VehicleId, CTR.TireSize ORDER BY CTR.Grade DESC) AS RowNumber
FROM Tuhu_bi.dbo.tbl_CarTireRecommendation_Region AS CTR WITH(NOLOCK)
JOIN Gungnir.dbo.tbl_region AS R WITH(NOLOCK) ON CTR.CityID=R.PKID
WHERE R.RegionName=@City
) AS T
WHERE T.RowNumber <= 4
) AS BI
ON BI.VehicleId = TT.VehicleId COLLATE Chinese_PRC_CI_AS AND BI.TireSize COLLATE Chinese_PRC_CI_AS = TT.TireSize";
}
if ((string.IsNullOrWhiteSpace(Brands) || Brands == "不限") && string.IsNullOrWhiteSpace(PID))
{
//品牌不限PID不限
}
else
{
sql += @" INNER JOIN Tuhu_productcatalog.dbo.vw_Products AS VP
ON VP.PID = VTR.PID
WHERE (@Brands=N'不限' OR VP.CP_Brand COLLATE Chinese_PRC_CI_AS IN (SELECT * FROM Gungnir.dbo.Split(@Brands,',') AS S4))
AND (@PID IS NULL OR VTR.PID =@PID)";
}
sql += @" ORDER BY TT.VehicleId";
#endregion
return(dbHelper.ExecuteDataTable(sql, CommandType.Text, new SqlParameter[] {
new SqlParameter("@VehicleIDS", VehicleIDS.Contains("全部") || string.IsNullOrWhiteSpace(VehicleIDS) ? "全部" :VehicleIDS),
new SqlParameter("@Departments", Departments.Contains("全部") || string.IsNullOrWhiteSpace(Departments) ? "全部" :Departments),
new SqlParameter("@PriceRanges", PriceRanges.Contains("全部") || string.IsNullOrWhiteSpace(PriceRanges) ? "全部" :PriceRanges),
new SqlParameter("@VehicleBodyTypes", VehicleBodyTypes.Contains("全部") || string.IsNullOrWhiteSpace(VehicleBodyTypes) ? "全部" :VehicleBodyTypes),
new SqlParameter("@Specifications", Specifications.Contains("不限") || string.IsNullOrWhiteSpace(Specifications) ? "不限" :Specifications),
new SqlParameter("@Brands", Brands.Contains("不限") || string.IsNullOrWhiteSpace(Brands) ? "不限" :Brands),
//new SqlParameter("@Province",string.IsNullOrWhiteSpace(Province) ? null:Province),
new SqlParameter("@City", string.IsNullOrWhiteSpace(City) ? null:City),
new SqlParameter("@PID", string.IsNullOrWhiteSpace(PID) ? null:PID),
new SqlParameter("@StartPrice", StartPrice),
new SqlParameter("@EndPrice", EndPrice)
}).ConvertTo <SelectListModel>());
}
}