public static IQueryable <EindDriveReport> GetDriveReports() { var result = new List <EindDriveReport>(); using (var sqlConnection = new SqlConnection(_connectionString)) { var cmd = new SqlCommand { CommandText = @"select report.Id, Date, Purpose, report.VehicleRegistrationNr, EmploymentId, AmountToReimburse, ManualEntryRemark, IsExtraDistance, ReimburseableDistance, ApproverEmploymentId, ApprovalDate, Rate_Id, CreationDate, Approved, Rejected, Reimbursed, ReimbursementDate, CprNr, RouteDescription, LongRouteDescription from Koerselsindberetning.dbo.DriveReports as report LEFT JOIN Koerselsindberetning.dbo.Status as status ON report.Id = status.DriveReport_Id JOIN Koerselsindberetning.dbo.Profiles as profile ON profile.Id = report.Profile_Id JOIN Koerselsindberetning.dbo.Routes as routes ON routes.DriveReport_Id = report.id ORDER BY CprNr, EmploymentId" , CommandType = CommandType.Text, Connection = sqlConnection }; sqlConnection.Open(); var reader = cmd.ExecuteReader(); while (reader.Read()) { var currentRow = new EindDriveReport { Id = reader.GetInt32(0), Date = reader.GetDateTime(1), Purpose = SafeGetString(reader, 2), RegistrationNumber = SafeGetString(reader, 3), EmploymentID = SafeGetIntFromString(reader, 4), AmmountToReimburse = SafeGetDoubleFromString(reader, 5), ManualEntryRemark = SafeGetString(reader, 6), IsExtraDistance = SafeGetBool(reader, 7), ReimbursableDistance = SafeGetDoubleFromString(reader, 8), ApproverEmploymentID = SafeGetIntFromString(reader, 9), ApprovalDate = SafeGetDateTime(reader, 10), RateID = reader.GetInt32(11), CreationDate = reader.GetDateTime(12), Approved = SafeGetBool(reader, 13), Rejected = SafeGetBool(reader, 14), Reimbursed = SafeGetBool(reader, 15), ReimbursementDate = SafeGetDateTime(reader, 16), CPR = SafeGetString(reader, 17), RouteDescription = SafeGetString(reader, 18), LongRouteDescription = SafeGetString(reader, 19) }; result.Add(currentRow); } } return(result.AsQueryable()); }
private string createRouteString(EindDriveReport report) { var route = ""; var points = DataProvider.GetDriveReportPoints(report.Id).ToList(); for (var i = 0; i < points.Count; i++) { var point = points[i]; route += point.StreetName + " " + point.StreetNumber + ", " + point.ZipCode + " " + point.Town; if (i < points.Count - 1) { route += " -> "; } } return(route); }
private void WriteReportAsCSVLine(EindDriveReport report, StreamWriter writer) { var route = createRouteString(report); var rate = _oldRates.Single(x => x.Id == report.RateID); var status = ReportStatus.Pending; if (report.Reimbursed) { status = ReportStatus.Invoiced; } else if (report.Rejected) { status = ReportStatus.Rejected; } else if (report.Approved) { status = ReportStatus.Accepted; } if (status != ReportStatus.Invoiced) { return; } DateTime approvedDate = report.ApprovalDate ?? new DateTime(1900, 1, 1); DateTime reimbursedDate = report.ReimbursementDate ?? new DateTime(1900, 1, 1); writer.Write(report.Id + "\t"); writer.Write(report.CPR + "\t"); writer.Write(report.EmploymentID + "\t"); writer.Write(report.Date.ToString("dd-MM-yy") + "\t"); writer.Write(report.Purpose + "\t"); writer.Write(report.RegistrationNumber + "\t"); writer.Write(route + "\t"); writer.Write(report.RouteDescription + "\t"); writer.Write(report.ReimbursableDistance + "\t"); writer.Write(report.AmmountToReimburse + "\t"); writer.Write(report.IsExtraDistance + "\t"); writer.Write(rate.TfCode + "\t"); writer.Write(report.ManualEntryRemark + "\t"); writer.Write(status + "\t"); writer.Write(report.ApproverEmploymentID + "\t"); if (approvedDate.Year == 1900) { writer.Write(" " + "\t"); } else { writer.Write(approvedDate.ToString("dd-MM-yy") + "\t"); } if (reimbursedDate.Year == 1900) { writer.Write(" " + "\t"); } else { writer.Write(reimbursedDate.ToString("dd-MM-yy") + "\t"); } writer.Write(report.CreationDate.ToString("dd-MM-yy") + "\t"); writer.WriteLine(); }