/// <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 UpdateRequest(MspRequest request)
{
const string query = "UPDATE workorder SET title = @Title, description = @Description WHERE workorderid = @WorkorderId";
var param = new
{
Title = request.Title,
Description = request.Description
};
Connection.Execute(query, param, Transaction);
foreach (var worklog in request.Worklogs)
{
AddOrUpdateWorklog(worklog, request.Id);
}
}
private void AddRequest(MspRequest request, string projectCode)
{
// Add request
const string requestQuery =
"INSERT INTO workorder(workorderid, requesterid, createdbyid, createdtime, title, description) " +
"VALUES (nextval('workorderid_seq'), @RequesterId, @CreatedbyId, @CreatedTime, @Title, @Description) RETURNING workorderid";
var requestParam = new
{
RequesterId = 2702,
CreatedbyId = 2702,
CreatedTime = DateTimeOffset.Now.ToUnixTimeMilliseconds(),
Title = request.Title,
Description = request.Description
};
request.Id = Connection.ExecuteScalar <long>(requestQuery, requestParam, Transaction);
// Add project code to request
const string query =
"INSERT INTO workorder_fields(workorderid, udf_char1) " +
"VALUES (@RequestId, @ProjectCode)";
var param = new
{
RequestId = request.Id,
ProjectCode = projectCode
};
Connection.Execute(query, param, Transaction);
// Add worklogs
foreach (var worklog in request.Worklogs)
{
AddWorklog(worklog, request.Id, 2601);
}
}
