public ActionResult Index()
{
WheelAdapterConfigQuery query = new WheelAdapterConfigQuery();
query.PageIndex = 0;
query.PageDataQuantity = 10;
query.TotalCount = 0;
ViewBag.query = query;
return(View());
}
public List <VehicleTypeInfo> QueryVehicleTypeInfoByTID(WheelAdapterConfigQuery query)
{
try
{
return(handlerReadOnly.QueryVehicleTypeInfoByTID(query));
}
catch (TuhuBizException)
{
throw;
}
catch (Exception ex)
{
var exception = new DownloadAppException(1, "QueryVehicleTypeInfoByTID", ex);
Logger.Log(Level.Error, exception, "QueryVehicleTypeInfoByTID");
throw ex;
}
}
public PartialViewResult IndexList(WheelAdapterConfigQuery query)
{
List <VehicleTypeInfo> vehicletypeinfolist = new List <VehicleTypeInfo>();
if (query.QueryWay == 0)
{
vehicletypeinfolist = WheelAdapterConfigManager.QueryVehicleTypeInfo(query);
}
else
{
vehicletypeinfolist = WheelAdapterConfigManager.QueryVehicleTypeInfoByTID(query);
}
var pager = new PagerModel(query.PageIndex, query.PageDataQuantity);
ViewBag.query = query;
pager.TotalItem = query.TotalCount;
return(PartialView(new ListModel <VehicleTypeInfo>()
{
Pager = pager, Source = vehicletypeinfolist
}));
}
public List <VehicleTypeInfo> QueryVehicleTypeInfoByTID(WheelAdapterConfigQuery query)
{
Func <SqlConnection, List <VehicleTypeInfo> > action = (connection) => DalWheelAdapterConfig.QueryVehicleTypeInfoByTID(connection, query);
return(dbManager.Execute(action));
}
public static List <VehicleTypeInfo> QueryVehicleTypeInfo(SqlConnection conn, WheelAdapterConfigQuery query)
{
string sqlcount = @"SELECT Count(1)
FROM Gungnir..tbl_Vehicle_Type AS VT with(nolock) INNER JOIN Gungnir..tbl_Vehicle_Type_Timing AS VTM with(nolock)
ON VT.ProductID=VTM.VehicleID
AND(@brand IS NULL OR VT.Brand=@brand)
AND(@vehicleid IS NULL OR VTM.VehicleID=@vehicleid)
AND(@pailiang IS NULL OR VTM.PaiLiang=@pailiang)
AND(@year IS NULL OR VTM.ListedYear<=@year)
AND(@year IS NULL OR VTM.StopProductionYear>=@year OR VTM.StopProductionYear IS NULL)
AND(@tid IS NULL OR VTM.TID=@tid)
LEFT JOIN Configuration..WheelAdapterConfigWithTid AS WAC with(nolock) ON WAC.TID COLLATE Chinese_PRC_CI_AS = VTM.TID"
+ (query.IsInfoSpecified.GetValueOrDefault() == 2 ? @" WHERE WAC.TID IS NULL" :
(query.IsInfoSpecified.GetValueOrDefault() == 1 ? @" WHERE WAC.TID IS NOT NULL" : @""));
string sql = @"SELECT VTM.PKID,VTM.TID,VT.Brand,VT.Vehicle,VTM.PaiLiang,VTM.Nian,VTM.SalesName,WAC.PCD,WAC.ET,
WAC.MinET,WAC.MaxET,WAC.CB,WAC.BoltNutSpec,WAC.BoltNut,WAC.MinWheelSize,WAC.MaxWheelSize,WAC.MinWheelWidth,WAC.MaxWheelWidth
FROM Gungnir..tbl_Vehicle_Type AS VT with(nolock) INNER JOIN Gungnir..tbl_Vehicle_Type_Timing AS VTM with(nolock)
ON VT.ProductID=VTM.VehicleID
AND(@brand IS NULL OR VT.Brand=@brand)
AND(@vehicleid IS NULL OR VTM.VehicleID=@vehicleid)
AND(@pailiang IS NULL OR VTM.PaiLiang=@pailiang)
AND(@year IS NULL OR VTM.ListedYear<=@year)
AND(@year IS NULL OR VTM.StopProductionYear>=@year OR VTM.StopProductionYear IS NULL)
AND(@tid IS NULL OR VTM.TID=@tid)
LEFT JOIN Configuration..WheelAdapterConfigWithTid AS WAC with(nolock) ON WAC.TID COLLATE Chinese_PRC_CI_AS = VTM.TID "
+ (query.IsInfoSpecified.GetValueOrDefault() == 2 ? @"WHERE WAC.TID IS NULL " :
(query.IsInfoSpecified.GetValueOrDefault() == 1 ? @"WHERE WAC.TID IS NOT NULL " : @"")) +
@"ORDER BY VT.Brand ASC OFFSET @pagesdata ROWS FETCH NEXT @pagedataquantity ROWS ONLY";
var sqlParam = new[]
{
new SqlParameter("@brand", query.BrandCriterion),
new SqlParameter("@vehicleid", query.VehicleCriterion),
new SqlParameter("@pailiang", query.PaiLiangCriterion),
new SqlParameter("@year", query.YearCriterion),
new SqlParameter("@tid", query.SalesNameCriterion),
new SqlParameter("@pagesdata", (query.PageIndex - 1) * query.PageDataQuantity),
new SqlParameter("@pagedataquantity", query.PageDataQuantity),
};
query.TotalCount = (int)SqlHelper.ExecuteScalar(conn, CommandType.Text, sqlcount, sqlParam);
return(SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql, sqlParam).ConvertTo <VehicleTypeInfo>().ToList());
}