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