public override void Append(DetailedReportResponse report)
{
var data = report.data;
var groupedData = data.GroupBy(g => new { Date = g.start.Date, Project = g.project, Task = g.description, User = g.user });
foreach (var item in groupedData)
{
// Project (system, part of the system, CRnr, workitems) Who When Hours Tag (buggfix, new feature, meeting, update, support)
this.SB.Append($"{item.Key.Date:yyyy-MM-dd};{item.Key.Project};{item.Key.Task};{GetDecimalHours(item.Sum(s => s.dur))}{Environment.NewLine}");
}
}
public void UploadReport(ref SqlConnection conn, DetailedReportResponse reportsResponse)
{
var retryCount = 0;
SqlTransaction tx;
while (true)
{
try
{
if (conn == null)
{
Debug.Log($"Opening a fresh database connection...");
conn = OpenConnection();
Debug.Log($"Successfully opened a fresh database connection.");
}
tx = conn.BeginTransaction();
break;
}
catch (Exception ex)
{
conn?.Close();
conn?.Dispose();
conn = null;
Debug.Log($"Error beginning a new transaction. Error:\n{ex}");
if (++retryCount < RETRY_LIMIT)
{
Debug.Log($"Sleeping for {RETRY_TIME} seconds...");
Thread.Sleep(1000 * RETRY_TIME); //Sleep for a minute.
}
else
{
Debug.Log($"Beginning a new transaction failed {RETRY_LIMIT} times consecutively. Terminating...");
Environment.Exit(0);
}
}
}
var r = reportsResponse.Report;
Debug.Log($"Uploading report {r.Id}...");
using (var cmd = new SqlCommand("INSERT INTO reports VALUES(@id,@score,@comment,@started_at,@ended_at,@inspection_type_id,@inspection_type_name,@area_id,@area_name,@area_division_id,@area_division_name,@inspector_id,@inspector_name,@inspector_email,@inspector_first_name,@inspector_last_name,@inspector_job_title,@inspector_role_id,@inspector_role_name,@survey_id,@survey_name,@final_reflections)", conn, tx))
{
cmd.Parameters.AddWithValue("@id", r.Id);
cmd.Parameters.AddWithValue("@score", r.Score);
cmd.Parameters.AddWithValue("@comment", (object)(r.Comment) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@started_at", r.StartedAtDate);
cmd.Parameters.AddWithValue("@ended_at", r.EndedAtDate);
cmd.Parameters.AddWithValue("@inspection_type_id", (object)(r.InspectionType?.Id) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@inspection_type_name", (object)(r.InspectionType?.Name) ?? DBNull.Value);
var area = r.Area;
cmd.Parameters.AddWithValue("@area_id", (object)(area?.Id) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@area_name", (object)(area?.Name) ?? DBNull.Value);
var division = area?.Division;
cmd.Parameters.AddWithValue("@area_division_id", (object)(division?.Id) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@area_division_name", (object)(division?.Name) ?? DBNull.Value);
var inspector = r.Inspector;
cmd.Parameters.AddWithValue("@inspector_id", (object)(inspector?.Id) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@inspector_name", (object)(inspector?.Name) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@inspector_email", (object)(inspector?.Email) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@inspector_first_name", (object)(inspector?.FirstName) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@inspector_last_name", (object)(inspector?.LastName) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@inspector_job_title", (object)(inspector?.JobTitle) ?? DBNull.Value);
var role = inspector?.Role;
cmd.Parameters.AddWithValue("@inspector_role_id", (object)(role?.Id) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@inspector_role_name", (object)(role?.Name) ?? DBNull.Value);
var survey = r.Survey;
cmd.Parameters.AddWithValue("@survey_id", (object)(survey?.Id) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@survey_name", (object)(survey?.Name) ?? DBNull.Value);
var fr = JSON.GetString(r.FinalReflections);
cmd.Parameters.AddWithValue("@final_reflections", (object)(fr) ?? DBNull.Value);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
foreach (var answer in r.Answers)
{
AddAnswer(answer, r.Id, conn, tx);
}
Debug.Log($"Uploaded.");
tx.Commit();
tx.Dispose();
}
public abstract void Append(DetailedReportResponse report);
