private static int RegionStockListCount(RegionStockRequest model)
{
string sql = $@"WITH c AS ( SELECT *
FROM [Tuhu_bi].[dbo].[tbl_CarTireRecommendation] WITH ( NOLOCK )
WHERE {string.Format(string.IsNullOrEmpty(model.VehicleId) || string.IsNullOrEmpty(model.TireSize) ? "1=2" : " TireSize = @TireSize and vehicleid = @vehicleid ")}
)
SELECT count(1)
FROM [Tuhu_bi].[dbo].[tbl_CarTireRecommendation_Region_StockList] AS stock
WITH ( NOLOCK )
LEFT JOIN Tuhu_productcatalog..vw_Products AS VMP WITH ( NOLOCK ) ON VMP.PID = stock.pid
{string.Format(string.IsNullOrEmpty(model.VehicleId) ? " left join " : " join ")} c ON c.ProductId = VMP.PID
WHERE stock.cityID = @CityId
AND ( stock.pid LIKE 'TR-%'
AND ( stock.pid LIKE '%' + @PID + '%'
OR @PID IS NULL
)
)
AND ( @ProductName IS NULL
OR VMP.DisplayName LIKE '%' + @ProductName + '%'
)
{string.Format(string.IsNullOrEmpty(model.VehicleId) ? " " : " and c.NoReduceStockGrade is not null ")} ";
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
return(Convert.ToInt32(dbHelper.ExecuteScalar(sql, CommandType.Text, new SqlParameter[] {
new SqlParameter("@PID", model.PID),
new SqlParameter("@CityId", model.CityId),
new SqlParameter("@ProductName", model.ProductName),
new SqlParameter("@vehicleid", model.VehicleId),
new SqlParameter("@TireSize", model.TireSize)
})));
}
}
public static List <RegionStockModel> SelectRegionStockList(RegionStockRequest model, PagerModel pager)
{
string sql = $@"WITH c AS ( SELECT *
FROM [Tuhu_bi].[dbo].[tbl_CarTireRecommendation] WITH ( NOLOCK )
WHERE {string.Format(string.IsNullOrEmpty(model.VehicleId)||string.IsNullOrEmpty(model.TireSize)?"1=2": " TireSize = @TireSize and vehicleid = @vehicleid ")}
)
SELECT stock.pid Pid ,
VMP.DisplayName ,
stock.defaultwareHouse AS DefaultStockName ,
stock.defaultavailnums StockNum ,
stock.defaultsafenums SafeStockNum ,
stock.nationalavailnums AllStockNum ,
stock.nationalsafenums AllSafeStockNum ,
stock.centralavailnums CentralStockNum ,
stock.centralsafenums CentralSafeStockNum ,
stock.regionavailnums RegionavailNums ,
c.NoReduceStockGrade
FROM [Tuhu_bi].[dbo].[tbl_CarTireRecommendation_Region_StockList] AS stock
WITH ( NOLOCK )
LEFT JOIN Tuhu_productcatalog..vw_Products AS VMP WITH ( NOLOCK ) ON VMP.PID = stock.pid
{string.Format(string.IsNullOrEmpty(model.VehicleId) ? " left join " : " join ")} c ON c.ProductId = VMP.PID
WHERE stock.cityID = @CityId
AND ( stock.pid LIKE 'TR-%'
AND ( stock.pid LIKE '%' + @PID + '%'
OR @PID IS NULL
)
)
AND ( @ProductName IS NULL
OR VMP.DisplayName LIKE '%' + @ProductName + '%'
)
{string.Format(string.IsNullOrEmpty(model.VehicleId) ? " " : " and c.NoReduceStockGrade is not null ")}
ORDER BY stock.cityID ,
stock.pid DESC
OFFSET ( @PageIndex - 1 ) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;";
pager.TotalItem = RegionStockListCount(model);
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
return(dbHelper.ExecuteDataTable(sql, CommandType.Text, new SqlParameter[] {
new SqlParameter("@PID", model.PID),
new SqlParameter("@CityId", model.CityId),
new SqlParameter("@ProductName", model.ProductName),
new SqlParameter("@PageIndex", pager.CurrentPage),
new SqlParameter("@PageSize", pager.PageSize),
new SqlParameter("@vehicleid", model.VehicleId),
new SqlParameter("@TireSize", model.TireSize)
}).ConvertTo <RegionStockModel>().ToList());
}
}
public static List <RegionStockModel> SelectRegionStockList(RegionStockRequest model, PagerModel pager) => DalStockoutStatus.SelectRegionStockList(model, pager);
