public static ListModel <VehicleAuditInfoModel> SelectVehicleAuditInfo(VehicleAuthAuditRequest request)
{
List <SqlParameter> sqlParameters = new List <SqlParameter>();
List <SqlParameter> countSqlParameters = new List <SqlParameter>();
string whereSql = " AND VTCI.[Status]=@status AND VA.[Status]=@status ";
sqlParameters.Add(new SqlParameter("@status", request.Status));
countSqlParameters.Add(new SqlParameter("@status", request.Status));
if (!string.IsNullOrEmpty(request.Mobile))
{
whereSql += " AND U.u_mobile_number=@mobile";
sqlParameters.Add(new SqlParameter("@mobile", request.Mobile));
countSqlParameters.Add(new SqlParameter("@mobile", request.Mobile));
}
if ((!string.IsNullOrWhiteSpace(request.CarNo) || !string.IsNullOrWhiteSpace(request.VinCode)) && request.CarId != Guid.Empty)
{
whereSql += " AND VTCI.CarId!=@CarId AND VTCI.Status=1 ";
sqlParameters.Add(new SqlParameter("@CarId", request.CarId));
countSqlParameters.Add(new SqlParameter("@CarId", request.CarId));
whereSql += " AND ( ";
List <string> tempSql = new List <string>();
if (!string.IsNullOrWhiteSpace(request.CarNo))
{
tempSql.Add("CO.u_carno=@CarNo");
sqlParameters.Add(new SqlParameter("@CarNo", request.CarNo));
countSqlParameters.Add(new SqlParameter("@CarNo", request.CarNo));
}
if (!string.IsNullOrWhiteSpace(request.VinCode))
{
tempSql.Add("CO.VinCode=@VinCode");
sqlParameters.Add(new SqlParameter("@VinCode", request.VinCode));
countSqlParameters.Add(new SqlParameter("@VinCode", request.VinCode));
}
whereSql += string.Join(" OR ", tempSql);
whereSql += " )";
}
//如果是待审核数据，从申诉数据库里关联查询
string sql = $@"SELECT
VTCI.Status,
VTCI.LastUpdateDateTime AS LastChangedDate,
VTCI.CreateDateTime AS CreatedDate,
VTCI.certified_time,
VTCI.Channel,
VA.Vehicle_license_img AS ImageUrl,
{(request.Status==1? "CO.u_carno" : "VA.CarNo")} AS CarNumber,
{(request.Status==1? "CO.VinCode" : "VA.ClassNo")} AS VinCode,
VA.User_IdCard_img AS IdCardUrl,
VA.Reason,
CO.CarID,
CO.Brand,
CO.Vehicle,
CO.u_PaiLiang,
CO.u_Nian,
CO.SalesName,
CO.IsDefaultCar,
U.u_mobile_number AS Mobile
FROM tuhu_profiles..VehicleTypeCertificationInfo AS VTCI WITH(NOLOCK)
INNER JOIN Tuhu_profiles..CarObject AS CO WITH(NOLOCK) ON VTCI.CarID = CO.CarID
INNER JOIN Tuhu_profiles..VehicleAuthAppeal AS VA WITH(NOLOCK) ON VTCI.CarID=VA.CarID {(request.Status==1? " AND VA.PKID=(SELECT MAX(PKID) FROM Tuhu_profiles..VehicleAuthAppeal WHERE CarID=VTCI.CarID) " : "")}
INNER JOIN Tuhu_profiles..UserObject AS U WITH(NOLOCK) ON CO.UserID = U.UserID
WHERE CO.IsDeleted = 0 {whereSql}
ORDER BY VTCI.CreateDateTime DESC
OFFSET ( @pageSize * ( @pageIndex - 1 ) ) ROWS
FETCH NEXT @pageSize ROWS ONLY;";
string countSql = $@"SELECT COUNT({(request.Status == 1 ? "DISTINCT VTCI.CarID" : "1")})
FROM tuhu_profiles..VehicleTypeCertificationInfo AS VTCI WITH(NOLOCK)
INNER JOIN Tuhu_profiles..CarObject AS CO WITH(NOLOCK) ON VTCI.CarID = CO.CarID
INNER JOIN Tuhu_profiles..VehicleAuthAppeal AS VA WITH(NOLOCK) ON VTCI.CarID=VA.CarID
INNER JOIN Tuhu_profiles..UserObject AS U WITH(NOLOCK) ON CO.UserID = U.UserID
WHERE CO.IsDeleted = 0 {whereSql}";
sqlParameters.Add(new SqlParameter("@pageIndex", request.PageIndex));
sqlParameters.Add(new SqlParameter("@pageSize", request.PageSize));
var model = new ListModel <VehicleAuditInfoModel>();
using (var cmd = new SqlCommand(sql))
{
cmd.Parameters.AddRange(sqlParameters.ToArray());
var queryResult = DbHelper.ExecuteDataTable(cmd).ConvertTo <VehicleAuditInfoModel>();
model.Source = queryResult;
}
using (var cmd = new SqlCommand(countSql))
{
int totalCount = 0;
cmd.Parameters.AddRange(countSqlParameters.ToArray());
var countResult = DbHelper.ExecuteScalar(cmd);
if (!Convert.IsDBNull(countResult))
{
int.TryParse(countResult.ToString(), out totalCount);
}
model.Pager = new PagerModel()
{
CurrentPage = request.PageIndex,
PageSize = request.PageSize,
TotalItem = totalCount
};
}
return(model);
}
public ListModel <VehicleAuditInfoModel> SelectVehicleAuditInfo(VehicleAuthAuditRequest request)
{
return(DalVehicleTypeCertificationAuditLog.SelectVehicleAuditInfo(request));
}
