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