public static async Task <IEnumerable <TireInsuranceYearModel> > SelectInstallFeeListAsync( InstallFeeConditionModel condition, PagerModel pager) { var result = await DalTireInsuranceYears.SelectInstallFeeListAsync(condition, pager); return(result); }
public static IEnumerable <InstallFeeModel> SelectInstallFeeList(InstallFeeConditionModel condition, PagerModel pager) { using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir"))) { var para = new SqlParameter[] { new SqlParameter("@Brands", condition.Brands), new SqlParameter("@IsConfig", condition.IsConfig), new SqlParameter("@OnSale", condition.OnSale), new SqlParameter("@Patterns", condition.Patterns), new SqlParameter("@PID", condition.PID), new SqlParameter("@Rims", condition.Rims), new SqlParameter("@Rof", condition.Rof), new SqlParameter("@TireSizes", condition.TireSizes), new SqlParameter("@Winter", condition.Winter), new SqlParameter("@PageIndex", pager.CurrentPage), new SqlParameter("@PageSize", pager.PageSize), }; var para_temp = new SqlParameter[] { new SqlParameter("@Brands", condition.Brands), new SqlParameter("@IsConfig", condition.IsConfig), new SqlParameter("@OnSale", condition.OnSale), new SqlParameter("@Patterns", condition.Patterns), new SqlParameter("@PID", condition.PID), new SqlParameter("@Rims", condition.Rims), new SqlParameter("@Rof", condition.Rof), new SqlParameter("@TireSizes", condition.TireSizes), new SqlParameter("@Winter", condition.Winter) }; pager.TotalItem = SelectInstallFeeList_Count(para_temp, dbHelper); return(dbHelper.ExecuteDataTable(TireSql.InstallFee.select, CommandType.Text, para).ConvertTo <InstallFeeModel>()); } }
public static IEnumerable <InstallFeeModel> SelectInstallFeeList(InstallFeeConditionModel condition, PagerModel pager) => DalInstallFee.SelectInstallFeeList(condition, pager);
public static async Task <IEnumerable <TireInsuranceYearModel> > SelectInstallFeeListAsync(InstallFeeConditionModel condition, PagerModel pager) { string sql = @"SELECT VP.PID , AIF.TireInsuranceYears , VP.DisplayName , VP.TireSize FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) LEFT JOIN Tuhu_productcatalog..tbl_ProductExtraProperties AS AIF WITH ( NOLOCK ) ON AIF.PID = VP.PID WHERE VP.PID LIKE 'TR-%' AND ( @Brands IS NULL OR VP.CP_Brand COLLATE Chinese_PRC_CI_AS IN ( SELECT * FROM Gungnir.dbo.Split(@Brands, ';') ) ) AND ( @Patterns IS NULL OR VP.CP_Tire_Pattern COLLATE Chinese_PRC_CI_AS IN ( SELECT * FROM Gungnir.dbo.Split(@Patterns, ';') ) ) AND ( @TireSizes IS NULL OR VP.TireSize COLLATE Chinese_PRC_CI_AS IN ( SELECT * FROM Gungnir.dbo.Split(@TireSizes, ';') ) ) AND ( @Rims IS NULL OR VP.CP_Tire_Rim COLLATE Chinese_PRC_CI_AS IN ( SELECT * FROM Gungnir.dbo.Split(@Rims, ';') ) ) AND ( @Rof IS NULL OR VP.CP_Tire_ROF = @Rof ) AND ( @Winter IS NULL OR VP.CP_Tire_Snow = @Winter ) AND ( @PID IS NULL OR VP.PID LIKE '%' + @PID + '%' ) AND ( @OnSale IS NULL OR VP.OnSale = @OnSale ) AND ( @IsConfig IS NULL OR @IsConfig = 1 AND AIF.TireInsuranceYears = 2 OR @IsConfig = 0 AND (AIF.TireInsuranceYears IS NULL or AIF.TireInsuranceYears=1 ) ) ORDER BY AIF.PKID DESC OFFSET ( @PageIndex - 1 ) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;"; string countsql = @"SELECT COUNT(1) FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) LEFT JOIN Tuhu_productcatalog..tbl_ProductExtraProperties AS AIF WITH ( NOLOCK ) ON AIF.PID = VP.PID WHERE VP.PID LIKE 'TR-%' AND ( @Brands IS NULL OR VP.CP_Brand COLLATE Chinese_PRC_CI_AS IN ( SELECT * FROM Gungnir.dbo.Split(@Brands, ';') ) ) AND ( @Patterns IS NULL OR VP.CP_Tire_Pattern COLLATE Chinese_PRC_CI_AS IN ( SELECT * FROM Gungnir.dbo.Split(@Patterns, ';') ) ) AND ( @TireSizes IS NULL OR VP.TireSize COLLATE Chinese_PRC_CI_AS IN ( SELECT * FROM Gungnir.dbo.Split(@TireSizes, ';') ) ) AND ( @Rims IS NULL OR VP.CP_Tire_Rim COLLATE Chinese_PRC_CI_AS IN ( SELECT * FROM Gungnir.dbo.Split(@Rims, ';') ) ) AND ( @Rof IS NULL OR VP.CP_Tire_ROF = @Rof ) AND ( @Winter IS NULL OR VP.CP_Tire_Snow = @Winter ) AND ( @PID IS NULL OR VP.PID LIKE '%' + @PID + '%' ) AND ( @OnSale IS NULL OR VP.OnSale = @OnSale ) AND ( @IsConfig IS NULL OR @IsConfig = 1 AND AIF.TireInsuranceYears = 2 OR @IsConfig = 0 AND (AIF.TireInsuranceYears IS NULL or AIF.TireInsuranceYears=1 ) )"; using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir"))) { var para = new SqlParameter[] { new SqlParameter("@Brands", condition.Brands), new SqlParameter("@IsConfig", condition.IsConfig), new SqlParameter("@OnSale", condition.OnSale), new SqlParameter("@Patterns", condition.Patterns), new SqlParameter("@PID", condition.PID), new SqlParameter("@Rims", condition.Rims), new SqlParameter("@Rof", condition.Rof), new SqlParameter("@TireSizes", condition.TireSizes), new SqlParameter("@Winter", condition.Winter), new SqlParameter("@PageIndex", pager.CurrentPage), new SqlParameter("@PageSize", pager.PageSize), }; var para_temp = new SqlParameter[] { new SqlParameter("@Brands", condition.Brands), new SqlParameter("@IsConfig", condition.IsConfig), new SqlParameter("@OnSale", condition.OnSale), new SqlParameter("@Patterns", condition.Patterns), new SqlParameter("@PID", condition.PID), new SqlParameter("@Rims", condition.Rims), new SqlParameter("@Rof", condition.Rof), new SqlParameter("@TireSizes", condition.TireSizes), new SqlParameter("@Winter", condition.Winter) }; var count = await dbHelper.ExecuteScalarAsync(countsql, CommandType.Text, para_temp); int totalcount = 0; int.TryParse(count?.ToString(), out totalcount); pager.TotalItem = totalcount; return(dbHelper.ExecuteDataTable(sql, CommandType.Text, para).ConvertTo <TireInsuranceYearModel>()); } }