public static IEnumerable <InstallNowModel> SelectList(InstallNowConditionModel request, PagerModel pager)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
pager.TotalItem = GetListCount(request, dbHelper);
return(dbHelper.ExecuteDataTable(@"SELECT TINR.* ,
VP.TireSize ,
R1.RegionName AS City ,
ISNULL(R2.RegionName, R1.RegionName) AS Province
FROM Configuration.dbo.tbl_TireInstallNow_Region AS TINR WITH ( NOLOCK )
JOIN Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) ON TINR.PID = VP.PID
JOIN Gungnir.dbo.tbl_region AS R1 WITH ( NOLOCK ) ON TINR.CityId = R1.PKID
LEFT JOIN Gungnir.dbo.tbl_region AS R2 WITH ( NOLOCK ) ON R1.ParentID = R2.PKID
WHERE (TINR.PID LIKE '%'+ @PID+'%'
OR @PID IS NULL)
AND (VP.TireSize = @TireSize
OR @TireSize IS NULL)
AND (TINR.Status = @Status
OR @Status IS NULL)
AND (@CityIds IS NULL
OR TINR.CityId IN ( SELECT *
FROM Gungnir.dbo.Split(@CityIds, ';') ))
ORDER BY TINR.PKID
OFFSET ( @PageIndex - 1 ) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;" , CommandType.Text, new SqlParameter[] {
new SqlParameter("@PID", request.PID),
new SqlParameter("@TireSize", request.TireSize),
new SqlParameter("@Status", request.Status),
new SqlParameter("@CityIds", request.CityIds),
new SqlParameter("@PageSize", pager.PageSize),
new SqlParameter("@PageIndex", pager.CurrentPage)
}).ConvertTo <InstallNowModel>());
}
}
private static int GetListCount(InstallNowConditionModel request, SqlDbHelper dbHelper)
{
return(Convert.ToInt32(dbHelper.ExecuteScalar(@"SELECT COUNT(1)
FROM Configuration.dbo.tbl_TireInstallNow_Region AS TINR WITH ( NOLOCK )
JOIN Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) ON TINR.PID = VP.PID
JOIN Gungnir.dbo.tbl_region AS R1 WITH ( NOLOCK ) ON TINR.CityId = R1.PKID
LEFT JOIN Gungnir.dbo.tbl_region AS R2 WITH ( NOLOCK ) ON R1.ParentID = R2.PKID
WHERE (TINR.PID LIKE '%'+ @PID+'%'
OR @PID IS NULL)
AND (VP.TireSize = @TireSize
OR @TireSize IS NULL)
AND (TINR.Status = @Status
OR @Status IS NULL)
AND (@CityIds IS NULL
OR TINR.CityId IN ( SELECT *
FROM Gungnir.dbo.Split(@CityIds, ';') ))", CommandType.Text, new SqlParameter[] {
new SqlParameter("@PID", request.PID),
new SqlParameter("@TireSize", request.TireSize),
new SqlParameter("@Status", request.Status),
new SqlParameter("@CityIds", request.CityIds)
})));
}
public static IEnumerable <InstallNowModel> SelectList(InstallNowConditionModel request, PagerModel pager) => DalInstallNow.SelectList(request, pager);