public async Task <Model.JobOrder> GetJobOrderByID(long id)
{
var param = new DynamicParameters();
param.Add("@ID", id);
var mapper = new JobOrderMapper();
return((await SqlMapper.QueryAsync(_unitOfWork.Connection,
"GetJobOrderByID",
new[]
{
typeof(Model.JobOrder),
typeof(JobOrderLineItem),
typeof(Customer),
typeof(Officer),
typeof(Premise),
typeof(Attachment),
typeof(Attendee),
typeof(Officer),
typeof(Log),
typeof(Findings),
typeof(Officer),
typeof(FindingsLineItem),
typeof(Attachment),
typeof(Attachment),
typeof(Person),
typeof(ContactInfo)
},
obj =>
{
return mapper.Map(obj[0] as Model.JobOrder,
obj[1] as JobOrderLineItem,
obj[4] as Premise,
obj[2] as Customer,
obj[14] as Person,
obj[15] as ContactInfo,
obj[6] as Attendee,
obj[3] as Officer,
obj[5] as Attachment,
obj[7] as Officer,
obj[8] as Log,
obj[9] as Findings,
obj[10] as Officer,
obj[11] as FindingsLineItem,
obj[12] as Attachment,
obj[13] as Attachment);
},
param,
splitOn: "ID,LineItemID,CustomerID,OfficerID,PremiseID,LineItemAttachment,Attendee,Invitee," +
"LogID,Findings,FindingsOfficer,FindingsLineItem,FLIAttachment,Signature,PersonID" +
",ContactInfoID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).FirstOrDefault());
}
public async Task <IEnumerable <Model.JobOrder> > GetJobOrders(Guid assignedTo,
DateTimeOffset?lastupdatedOn)
{
var param = new DynamicParameters();
param.Add("@AssignedTo", assignedTo);
param.Add("@LastUpdatedOn", lastupdatedOn);
var mapper = new JobOrderMapper();
return((await SqlMapper.QueryAsync(_unitOfWork.Connection,
"GetJobOrderByAssignedToID",
new[]
{
typeof(Model.JobOrder),
typeof(Attendee),
typeof(Officer),
typeof(Findings),
typeof(FindingsLineItem),
typeof(Premise),
typeof(Attachment),
typeof(Attachment),
typeof(Customer),
typeof(JobOrderLineItem),
typeof(Person),
typeof(ContactInfo)
},
obj =>
{
return mapper.Map(obj[0] as Model.JobOrder,
obj[9] as JobOrderLineItem,
obj[5] as Premise,
obj[8] as Customer,
obj[10] as Person,
obj[11] as ContactInfo,
obj[1] as Attendee,
obj[2] as Officer,
obj[6] as Attachment,
findings: obj[3] as Findings,
findingsLineItem: obj[4] as FindingsLineItem,
findingsLineItemAttachment: obj[7] as Attachment);
},
param,
splitOn: "ID,AttendeeID,OfficerID,FindingsID,FindingsLineItemID" +
",PremiseID,AttachmentID,FindingsAttachmentID,CustomerID,LineItemID,PersonID" +
",ContactInfoID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).Distinct());
}
public async Task <IEnumerable <Model.JobOrder> > Select(
JobOrderOptions options)
{
var statusTable = new DataTable();
statusTable.Columns.Add("Val", typeof(int));
if (options.Status?.Any() ?? false)
{
foreach (var status in options.Status)
{
statusTable.Rows.Add(status);
}
}
var typeTable = new DataTable();
typeTable.Columns.Add("Val", typeof(int));
if (options.Type?.Any() ?? false)
{
foreach (var type in options.Type)
{
typeTable.Rows.Add(type);
}
}
var assignedToTable = new DataTable();
assignedToTable.Columns.Add("Val", typeof(Guid));
if (options.AssignedTo?.Any() ?? false)
{
foreach (var assignedTo in options.AssignedTo)
{
assignedToTable.Rows.Add(assignedTo);
}
}
var mapper = new JobOrderMapper();
var param = new DynamicParameters();
param.Add("@ID", options.ID);
param.Add("@CustomerName", options.Customer);
param.Add("@Premise", options.Premise);
param.Add("@From", options.From);
param.Add("@To", options.To);
param.Add("@Type", typeTable.AsTableValuedParameter("dbo.SmallIntType"));
param.Add("@Status", statusTable.AsTableValuedParameter("dbo.SmallIntType"));
param.Add("@AssignedTo", assignedToTable.AsTableValuedParameter("dbo.UniqueIdentifierType"));
return((await SqlMapper.QueryAsync(_unitOfWork.Connection,
"SelectJobOrder",
new[]
{
typeof(Model.JobOrder),
typeof(JobOrderLineItem),
typeof(Customer),
typeof(Officer),
typeof(Premise),
typeof(Person),
typeof(ContactInfo)
},
obj =>
{
return mapper.Map(obj[0] as Model.JobOrder,
obj[1] as JobOrderLineItem,
obj[4] as Premise,
obj[2] as Customer,
obj[5] as Person,
obj[6] as ContactInfo,
officer: obj[3] as Officer);
},
param,
splitOn: "ID,LineItemID,CustomerID,OfficerID,PremiseID,PersonID,ContactInfoID",
commandType: CommandType.StoredProcedure,
transaction: _unitOfWork.Transaction)).Distinct());
}
