/// <inheritdoc />
public IEnumerable <MspProject> GetAll()
{
var projects = new List <MspProject>();
var result = Connection.Query(
"SELECT wf.workorderid, wf.udf_char1, wo.title, wo.description, wo.siteid, cs.technicianid, cs.ts_starttime, cs.ts_endtime, cs.worklogtypeid, wlf.udf_long1 " +
"FROM workorder_fields as wf " +
"LEFT JOIN workorder as wo ON wf.workorderid = wo.workorderid " +
"LEFT JOIN workordertocharge as wotc ON wf.workorderid = wotc.workorderid " +
"LEFT JOIN chargestable as cs ON wotc.chargeid = cs.chargeid " +
"LEFT JOIN worklog_fields as wlf ON cs.chargeid = wlf.worklogid " +
"ORDER BY udf_char1", transaction: Transaction);
MspProject project = null;
MspRequest currentRequest = null;
foreach (var res in result)
{
// Requests that don't belong to a project are skipped
if (string.IsNullOrEmpty(res.udf_char1))
{
continue;
}
// Create a new project when a new project code is encountered
if (project == null || !string.Equals(res.udf_char1, project.Code))
{
project = new MspProject(0, res.udf_char1)
{
Code = res.udf_char1,
Requests = new List <MspRequest>()
};
projects.Add(project);
}
// Create a new request when a new request title is encountered
if (currentRequest == null || !string.Equals(res.title, currentRequest.Title))
{
currentRequest = new MspRequest(res.title, res.description, res.siteid);
project.AddRequest(currentRequest);
}
var worklog = new MspWorklog(
new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(res.ts_starttime),
new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(res.ts_endtime),
res.description,
res.udf_long1,
res.technicianid
);
currentRequest.AddWorklog(worklog);
}
return(projects);
}
private void AddOrUpdateWorklog(MspWorklog worklog, long requestId)
{
if (worklog.Id == 0)
{
AddWorklog(worklog, requestId, 2601);
}
else
{
UpdateWorklog(worklog);
}
}
private void AddWorklog(MspWorklog worklog, long requestId, long technicianId)
{
// Creates the worklog in MSP
const string worklogQuery =
"INSERT INTO chargestable(chargeid, technicianid, createdby, description, timespent, ts_starttime, ts_endtime, worklogtypeid) " +
"VALUES (nextval('chargeid_seq'), @TechnicianId, @TechnicianId, @Description, @TimeSpent, @StartTime, @EndTime, @WorklogTypeId) RETURNING chargeid";
var worklogParam = new
{
TechnicianId = technicianId,
Description = worklog.Description,
TimeSpent = (worklog.WorkEndedDateTime - worklog.WorkStartedDateTime).Minutes * 60 * 1000,
StartTime = worklog.WorkStartedDateTime,
EndTime = worklog.WorkEndedDateTime,
WorklogTypeId = 1
};
worklog.Id = Connection.ExecuteScalar <long>(worklogQuery, worklogParam, Transaction);
// Adds extra field values to MSP db
const string worklogExtraQuery =
"INSERT INTO worklog_fields(worklogid, udf_long1) " +
"VALUES (@WorklogId, @Kilometers)";
var worklogExtraParam = new
{
WorklogId = worklog.Id,
Kilometers = worklog.KilometresCovered
};
Connection.Execute(worklogExtraQuery, worklogExtraParam, Transaction);
// Links the worklog with the request
// Adds extra field values to MSP db
const string worklogRquestLinkQuery =
"INSERT INTO workordertocharge(workorderid, chargeid) " +
"VALUES (@WorklogId, @RequestId)";
var worklogRequestLinkParam = new
{
WorklogId = worklog.Id,
RequestId = requestId
};
Connection.Execute(worklogRquestLinkQuery, worklogRequestLinkParam, Transaction);
}
private void UpdateWorklog(MspWorklog worklog)
{
const string query =
"UPDATE chargestable SET technicianid = @TechId, ts_starttime = @StartTime, ts_endtime = @EndTime, timespent = @TimeSpend, worklogtypeid = @WorklogtypeId " +
"WHERE workorderid = @WorkorderId";
var param = new
{
TechId = 2601,
StartTime = worklog.WorkStartedDateTime,
EndTime = worklog.WorkEndedDateTime,
TimeSpend = (worklog.WorkEndedDateTime - worklog.WorkStartedDateTime).Minutes * 60 * 1000,
WorklogtypeId = 0,
WorkorderId = worklog.Id
};
Connection.Execute(query, param, Transaction);
}
/// <summary>
/// Default constructor for the MSP worklog
/// </summary>
/// <param name="worklog"><see cref="MspWorklog"/></param>
/// <param name="dapperUnitOfWorkFactory"><see cref="IDapperUnitOfWorkFactory"/></param>
public MspWorklogAdapter(MspWorklog worklog, IDapperUnitOfWorkFactory dapperUnitOfWorkFactory)
{
_worklog = worklog;
_dapperUnitOfWorkFactory = dapperUnitOfWorkFactory;
EmployeeEmailAddress = GetTechnicianEmail(worklog.MspTechnicianId);
}
