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