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);