public async Task <IActionResult> GetCarManageRecordDto([FromQuery] SCarManageRecordDto sCarManageRecordDto) { try { if (sCarManageRecordDto.SignInDateS == "" || sCarManageRecordDto.SignInDateS == null) { sCarManageRecordDto.SignInDateS = _config.GetSection("LogicSettings:MinDate").Value; } if (sCarManageRecordDto.SignInDateE == "" || sCarManageRecordDto.SignInDateE == null) { sCarManageRecordDto.SignInDateE = _config.GetSection("LogicSettings:MaxDate").Value; } var data = await _cMSCarManageRecordDAO.GetCarManageRecordDto(sCarManageRecordDto); PagedList <CarManageRecordDto> result = PagedList <CarManageRecordDto> .Create(data, sCarManageRecordDto.PageNumber, sCarManageRecordDto.PageSize, sCarManageRecordDto.IsPaging); Response.AddPagination(result.CurrentPage, result.PageSize, result.TotalCount, result.TotalPages); return(Ok(result)); } catch (Exception ex) { return(StatusCode(500, $"Internal server error: {ex}.")); } }
public async Task <List <CarManageRecordDto> > GetCarManageRecordDto(SCarManageRecordDto sCarManageRecordDto) { List <SqlParameter> pc = new List <SqlParameter> { new SqlParameter("@LicenseNumber", sCarManageRecordDto.LicenseNumber != null ? sCarManageRecordDto.LicenseNumber.Trim(): (object)DBNull.Value), new SqlParameter("@SignInDateS", sCarManageRecordDto.SignInDateS), new SqlParameter("@SignInDateE", sCarManageRecordDto.SignInDateE) }; /* * var data = _context.GetCarManageRecordDto * .FromSqlRaw("EXECUTE dbo.CMS_GetCarManageRecordDto @LicenseNumber,@SignInDateS,@SignInDateE", pc.ToArray()) * .ToList(); */ string strWhere = ""; if (sCarManageRecordDto.LicenseNumber == "" || sCarManageRecordDto.LicenseNumber == null) { strWhere += " WHERE SignInDate between @SignInDateS and @SignInDateE"; } else { strWhere += " WHERE SignInDate between @SignInDateS and @SignInDateE AND @LicenseNumber = LicenseNumber "; } string strSQL = string.Format(@" SELECT CP.CompanyName AS CompanyName ,PlateNumber AS PlateNumber ,DriverName AS DriverName ,LicenseNumber AS LicenseNumber ,SignInDate AS SignInDate ,TempNumber AS TempNumber ,SignInReason AS SignInReason ,GoodsName AS GoodsName ,GoodsCount AS GoodsCount ,DPM.DepartmentName AS DepartmentName ,ContactPerson AS ContactPerson ,SealNumber AS SealNumber ,DriverSign AS DriverSign ,SignOutDate AS SignOutDate ,GuardName AS GuardName ,C.CarSize AS CarSize ,CP.CompanyDistance AS CompanyDistance ,0 AS isDisplay FROM CMSCarManageRecord AS CMR left join CMSCompany AS CP on CP.Id = CMR.CompanyId left join CMSDepartment AS DPM on DPM.ID = CMR.DepartmentId left join CMSCar AS C on C.Id = CMR.CarId "); strSQL += strWhere; var data = await _context.GetCarManageRecordDto.FromSqlRaw(strSQL, pc.ToArray()).ToListAsync(); return(data); }
public async Task <IActionResult> ExportReport(SCarManageRecordDto sCarManageRecordDto) { if (sCarManageRecordDto.SignInDateS == "" || sCarManageRecordDto.SignInDateS == null) { sCarManageRecordDto.SignInDateS = _config.GetSection("LogicSettings:MinDate").Value; } if (sCarManageRecordDto.SignInDateE == "" || sCarManageRecordDto.SignInDateE == null) { sCarManageRecordDto.SignInDateE = _config.GetSection("LogicSettings:MaxDate").Value; } var data = await _cMSCarManageRecordDAO.GetCarManageRecordDto(sCarManageRecordDto); byte[] result = CommonExportReport(data, "TempCarRecord.xlsx"); return(File(result, "application/xlsx")); }