public static IEnumerable <ThirdPartyMiaoShaModel> GetThirdPartyMiaoShaItems(PriceSelectModel model, int PriceDifStatus, string shopCode, PagerModel pager)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
var sql = @"
SELECT MS.PID,VP.DisplayName AS ProductName,VP.cy_list_price AS Price,MS.Type,MS.Skuid,MS.Title,MS.Price AS ThirdPartyPrice,MS.MiaoShaPrice,MS.StartTime,MS.EndTime FROM Tuhu_productcatalog..MiaoShaoProductMapping MS WITH (NOLOCK)
Left Join Tuhu_productcatalog.dbo.vw_Products AS VP WITH (NOLOCK)
on MS.PID=VP.PID
where MS.ShopCode=@shopCode AND( VP.PID LIKE '%' + @PID + '%'
OR @PID IS NULL
)
AND ( VP.DisplayName LIKE '%' + @ProductName
+ '%'
OR @ProductName IS NULL
)
AND ( @Brands IS NULL
OR VP.CP_Brand COLLATE Chinese_PRC_CI_AS IN (
SELECT *
FROM Gungnir.dbo.Split(@Brands, ';') )
)
AND ( @TireSizes IS NULL
OR VP.TireSize COLLATE Chinese_PRC_CI_AS = @TireSizes
)
" ;
if (PriceDifStatus == 1)
{
sql += " AND (VP.cy_list_price > MS.MiaoShaPrice AND VP.cy_list_price > 0)";
}
else if (PriceDifStatus == 0)
{
sql += " AND (VP.cy_list_price = MS.MiaoShaPrice AND VP.cy_list_price > 0)";
}
else if (PriceDifStatus == -1)
{
sql += " AND (VP.cy_list_price < MS.MiaoShaPrice AND VP.cy_list_price > 0)";
}
sql += "order by MS.Type desc,MS.StartTime desc OFFSET(@PageIndex - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY";
using (var cmd = new SqlCommand(sql))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(new SqlParameter[] {
new SqlParameter("@PID", string.IsNullOrWhiteSpace(model.PID)?null:model.PID),
new SqlParameter("@ProductName", string.IsNullOrWhiteSpace(model.ProductName)?null:model.ProductName),
new SqlParameter("@Brands", string.IsNullOrWhiteSpace(model.Brand)?null:model.Brand),
new SqlParameter("@TireSizes", string.IsNullOrWhiteSpace(model.TireSize)?null:model.TireSize),
new SqlParameter("@shopCode", shopCode),
new SqlParameter("@PageIndex", pager.CurrentPage),
new SqlParameter("@PageSize", pager.PageSize)
});
return(dbHelper.ExecuteDataTable(cmd).ConvertTo <ThirdPartyMiaoShaModel>());
}
}
}
public static int GetMiaoShaItemCount(PriceSelectModel model, string shopCode, int PriceDifStatus)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
var sql = @"
SELECT count(1) FROM Tuhu_productcatalog..MiaoShaoProductMapping MS WITH (NOLOCK)
Left Join Tuhu_productcatalog.dbo.vw_Products AS VP WITH (NOLOCK)
on MS.PID=VP.PID
where MS.ShopCode=@shopCode AND( VP.PID LIKE '%' + @PID + '%'
OR @PID IS NULL
)
AND ( VP.DisplayName LIKE '%' + @ProductName
+ '%'
OR @ProductName IS NULL
)
AND ( @Brands IS NULL
OR VP.CP_Brand COLLATE Chinese_PRC_CI_AS IN (
SELECT *
FROM Gungnir.dbo.Split(@Brands, ';') )
)
AND ( @TireSizes IS NULL
OR VP.TireSize COLLATE Chinese_PRC_CI_AS = @TireSizes
)
" ;
if (PriceDifStatus == 1)
{
sql += " AND (VP.cy_list_price > MS.MiaoShaPrice AND VP.cy_list_price > 0)";
}
else if (PriceDifStatus == 0)
{
sql += " AND (VP.cy_list_price = MS.MiaoShaPrice AND VP.cy_list_price > 0)";
}
else if (PriceDifStatus == -1)
{
sql += " AND (VP.cy_list_price < MS.MiaoShaPrice AND VP.cy_list_price > 0)";
}
using (var cmd = new SqlCommand(sql))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(new SqlParameter[] {
new SqlParameter("@PID", string.IsNullOrWhiteSpace(model.PID)?null:model.PID),
new SqlParameter("@ProductName", string.IsNullOrWhiteSpace(model.ProductName)?null:model.ProductName),
new SqlParameter("@Brands", string.IsNullOrWhiteSpace(model.Brand)?null:model.Brand),
new SqlParameter("@TireSizes", string.IsNullOrWhiteSpace(model.TireSize)?null:model.TireSize),
new SqlParameter("@shopCode", shopCode),
});
var OBJ = dbHelper.ExecuteScalar(cmd);
return(Convert.ToInt32(OBJ));
}
}
}
public PartialViewResult MiaoShaProductList(PriceSelectModel selectModel, int PriceDifStatus, string shopCode, int pageIndex = 1, int pageSize = 10)
{
PagerModel pager = new PagerModel(pageIndex, pageSize);
var model = ThirdShopManager.FetchThirdPartyMiaoShaItems(selectModel, pager, shopCode, PriceDifStatus);
ViewBag.PriceDifStatus = PriceDifStatus;
ViewBag.SelectModel = selectModel;
ViewBag.shopCode = shopCode;
return(PartialView(new ListModel <ThirdPartyMiaoShaModel>()
{
Pager = pager, Source = model
}));
}
/// <summary>
/// 获取产品列表
/// </summary>
/// <param name="selectModel"></param>
/// <param name="pageType"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PartialViewResult PriceProductList(PriceSelectModel selectModel, string pageType, int pageIndex = 1, int pageSize = 10)
{
PagerModel pager = new PagerModel(pageIndex, pageSize);
var monitorManager = new CompetingProductsMonitorManager();
var monitorList = monitorManager.GetProductList(selectModel, pager);
ViewBag.SelectModel = selectModel;
ViewBag.PageType = pageType;
return(PartialView(new ListModel <CompetingProductsMonitorModel>()
{
Pager = pager, Source = monitorList
}));
}
public static IEnumerable <TireListModel> SelectPriceProductList(PriceSelectModel model, PagerModel pager, bool isExport = false)
{
IEnumerable <TireListModel> result = new List <TireListModel>();
try
{
result = DalTirePrice.SelectPriceProductList2(model, pager, isExport);
}
catch (Exception ex)
{
logger.Error(ex);
}
return(result);
}
public static IEnumerable <TireListModel> SelectPriceProductList(PriceSelectModel model, PagerModel pager, bool isExport = false)
{
IEnumerable <TireListModel> result = new List <TireListModel>();
try
{
//兼容逻辑处理
model.Rof = string.IsNullOrEmpty(model.Rof?.Trim()) ? "不限" : model.Rof.Trim(); //是否防爆
model.IsShow = model.IsShow ?? 2; //默认查所有的
model.StockOut = model.StockOut ?? 2; //默认查所有的
result = DalTirePrice.SelectPriceProductList2(model, pager, isExport);
result?.ToList().ForEach((t) =>
{
t.QPLPrice = null;
});
}
catch (Exception ex)
{
logger.Error(ex);
}
return(result);
}
/// <summary>
/// 查询产品列表
/// </summary>
/// <param name="conn"></param>
/// <param name="pids"></param>
/// <returns></returns>
public IEnumerable <CompetingProductsMonitorModel> GetProductList(PriceSelectModel selectModel, PagerModel pager)
{
//获取商品列表
var list = PriceManager.SelectPriceProductList(selectModel, pager);
//AutoMapper初始化配置文件
var config = new MapperConfiguration(cfg => cfg.CreateMap <TireListModel, CompetingProductsMonitorModel>());
var mapper = config.CreateMapper();
//集合类型转换
var productMonitorlist = mapper.Map <IEnumerable <CompetingProductsMonitorModel> >(list);
var pids = list.Select(g => g.PID).ToList();
using (var conn = ProcessConnection.OpenConfigurationReadOnly)
{
//根据Pids查询竞品中最低价商品
var monitorList = dal.GetProductsMonitorbyPids(conn, pids);
foreach (var pid in pids)
{
var rel = productMonitorlist.Where(g => g.PID == pid).First();
if (monitorList.Count() > 0)
{
var monitorModel = monitorList.Where(g => g.Pid == pid).FirstOrDefault();
if (monitorModel != null)
{
rel.MonitorCount = monitorModel.MonitorCount;
rel.ItemID = monitorModel.ItemID;
rel.SkuID = monitorModel.SkuID;
rel.ItemCode = monitorModel.ItemCode;
rel.Title = monitorModel.Title;
rel.ShopCode = monitorModel.ShopCode;
rel.MinPrice = monitorModel.MinPrice;
rel.LastUpdateDateTime = monitorModel.LastUpdateDateTime;
}
}
}
return(productMonitorlist);
}
}
public static List <ThirdPartyMiaoShaModel> FetchThirdPartyMiaoShaItems(PriceSelectModel selectModel, PagerModel pager, string shopCode, int PriceDifStatus)
{
pager.TotalItem = DALThirdShop.GetMiaoShaItemCount(selectModel, shopCode, PriceDifStatus);
return(DALThirdShop.GetThirdPartyMiaoShaItems(selectModel, PriceDifStatus, shopCode, pager)?.ToList());
}
