public IEnumerable<UpsellProductDto> GetCustomUpsellProducts(int dataPlatformType, int videoPlatformType)
{
using (var db = new UpsellEntities())
{
var products = from product in db.UpsellCustomProductDefinitions
join productClass in db.UpsellProductClassDefinitions on product.product_class equals productClass.id
where ((productClass.platform_type == dataPlatformType) || (productClass.platform_type == videoPlatformType) ||
(dataPlatformType != (int)UpsellPlatformType.None) && (productClass.platform_type == (int) UpsellPlatformType.AnyData) ||
(videoPlatformType != (int)UpsellPlatformType.None) && (productClass.platform_type == (int)UpsellPlatformType.AnyVideo))
select product;
return TypeAdapter.Adapt<IQueryable<UpsellCustomProductDefinition>, List<UpsellProductDto>>(products);
}
}
private static void AddOfflineProductFailures(int productId,OfflineOrderSubmitResponse failedResponse)
{
using (var db = new UpsellEntities())
{
db.UpsellProductFailures.AddObject(new UpsellProductFailure
{
product_id = productId,
created_on = (new DateTimeOffset(DateTime.Now)).DateTime,
fail_cause = failedResponse.Content,
status_code = Convert.ToInt32(failedResponse.StatusCode).ToString()
});
db.SaveChanges();
}
}
private static bool UpdateRecordStatus(int recordId, UpsellStatus newStatus)
{
bool isOperationSuccessful;
using (var db = new UpsellEntities())
{
var record = db.UpsellSubmissions.FirstOrDefault(x => x.id == recordId);
if (record != null)
{
record.upsell_status = (int) newStatus;
}
db.SaveChanges();
isOperationSuccessful = true;
}
return isOperationSuccessful;
}
public UpsellAddRecordResult AddRecord(UpsellSubmissionDto submission, IEnumerable<UpsellProductChangeDto> products)
{
// insert UpsellProducts entries
var offlineProducts = new List<UpsellSubmittedProduct>();
DateTimeOffset submissionDate = new DateTimeOffset(DateTime.Now);
int newId;
using (var db = new UpsellEntities())
{
var address = submission.SubscriberAddress;
var newRecord = new UpsellSubmission
{
subscriber_id = submission.SubscriberId,
subscriber_name = submission.SubscriberName,
subscriber_address1 = address.Address1,
subscriber_address2 = address.Address2,
subscriber_city = address.City,
subscriber_state = address.State,
subscriber_zipcode = address.Zip,
telephone_number = submission.TelephoneNumber,
submitted_by = submission.SubmittedBy,
authorized_by = submission.AuthorizedBy,
submission_date = submissionDate,
upsell_status = (int) UpsellStatus.New,
last_touched_by = string.Empty,
last_touched_at = null,
account_type = (int)submission.AccountType
};
// insert UpsellSubmissions entry
db.UpsellSubmissions.AddObject(newRecord);
db.SaveChanges();
newId = newRecord.id;
foreach (var product in products)
{
var newProduct = new UpsellProductChanges
{
upsell_id = newRecord.id,
product_name = product.Name,
product_description = product.Description,
product_action = product.Action.GetStringValue()
};
db.UpsellProductChanges.AddObject(newProduct);
db.SaveChanges();
//prepare products for offline submission as well.
//Capturing here since we need product id for further processing
offlineProducts.Add(new UpsellSubmittedProduct
{
ProductId = newProduct.id,
ProductName = product.Name,
ProductDescription = product.Description,
productAction = product.Action.GetStringValue()
});
}
}
return GetRecordResult(newId, submission, submissionDate.ToString(), offlineProducts);
}
public UpsellNoteDto UpdateRecordNotes(int recordId, string noteText, string userId)
{
using (var db = new UpsellEntities())
{
// if note text is empty, throw exception
if (string.IsNullOrWhiteSpace(noteText))
{
throw new ArgumentException("Note text cannot be empty");
}
var timestamp = new DateTimeOffset(DateTime.Now);
// get record
var record = db.UpsellSubmissions.FirstOrDefault(r => r.id.Equals(recordId));
// if no record found, throw exception
if (record == null)
{
throw new ArgumentException(string.Format("Record with ID[{0}] not found", recordId));
}
var newNote = new UpsellNoteDto
{
UpsellId = recordId,
AddedBy = userId,
AddedOn = timestamp,
NoteText = noteText
};
// add new note text
db.UpsellNotes.AddObject(TypeAdapter.Adapt<UpsellNoteDto, UpsellNote>(newNote));
db.SaveChanges();
// update record
record.last_touched_by = userId;
record.last_touched_at = timestamp;
db.SaveChanges();
// add audit record
db.UpsellAudits.AddObject(new UpsellAudit
{
upsell_id = recordId,
action_performed_by = userId,
action_date = timestamp,
action_performed = (int)UpsellAction.AddedNote
});
db.SaveChanges();
return newNote;
}
}
public void UpdateRecord(int recordId, int newStatus, string billingOrderNumber, string userId)
{
using (var db = new UpsellEntities())
{
var timestamp = new DateTimeOffset(DateTime.Now);
// get record
var record = db.UpsellSubmissions.FirstOrDefault(r => r.id.Equals(recordId));
// if no record found, throw exception
if (record == null)
{
throw new ArgumentException(string.Format("Record with ID[{0}] not found", recordId));
}
// update record
record.upsell_status = newStatus;
record.billing_order_number = billingOrderNumber;
record.last_touched_by = userId;
record.last_touched_at = timestamp;
db.SaveChanges();
// add audit record
db.UpsellAudits.AddObject(new UpsellAudit
{
upsell_id = recordId,
action_performed_by = userId,
action_date = timestamp,
action_performed = GetUpsellAction(newStatus)
});
db.SaveChanges();
}
}
public List<string> GetUpsellServiceClassesByPlatformType(int dataPlatformType, int videoPlatformType)
{
using (var db = new UpsellEntities())
{
var classes = from definition in db.UpsellProductClassDefinitions
where (definition.enabled == 1) &&
((definition.platform_type == dataPlatformType) || (definition.platform_type == videoPlatformType) ||
(dataPlatformType != (int)UpsellPlatformType.None) && (definition.platform_type == (int)UpsellPlatformType.AnyData) ||
(videoPlatformType != (int)UpsellPlatformType.None) && (definition.platform_type == (int)UpsellPlatformType.AnyVideo))
select definition.product_class;
return classes.ToList();
}
}
public QueryResult GetUpsellRecords(string userId, DataRequest request)
{
using (var db = new UpsellEntities())
{
// get any in-progress record last touched by current user
var inProgressRecords = db.UpsellSubmissions
.Where(r => (r.upsell_status == (int)UpsellStatus.InProgress) &&
r.last_touched_by.Equals(userId, StringComparison.OrdinalIgnoreCase));
if (inProgressRecords.Any())
{
return inProgressRecords.Select(r => new UpsellSubmissionDto
{
RecordId = r.id,
SubscriberId = r.subscriber_id,
SubscriberName = r.subscriber_name,
SubscriberAddress = new AddressDto
{
Address1 = r.subscriber_address1,
Address2 = r.subscriber_address2,
City = r.subscriber_city,
State = r.subscriber_state,
Zip = r.subscriber_zipcode
},
TelephoneNumber = r.telephone_number,
SubmittedBy = r.submitted_by,
SubmissionDateOffset = r.submission_date,
Status = (UpsellStatus)(r.upsell_status ?? 0),
LastTouchedAt = r.last_touched_at,
LastTouchedBy = r.last_touched_by
}).ToQueryResult();
}
// if no in-progress record was found, get all records
return db.UpsellSubmissions.Select(r => new UpsellSubmissionDto
{
RecordId = r.id,
SubscriberId = r.subscriber_id,
SubscriberName = r.subscriber_name,
SubscriberAddress = new AddressDto
{
Address1 = r.subscriber_address1,
Address2 = r.subscriber_address2,
City = r.subscriber_city,
State = r.subscriber_state,
Zip = r.subscriber_zipcode
},
TelephoneNumber = r.telephone_number,
SubmittedBy = r.submitted_by,
SubmissionDateOffset = r.submission_date,
Status = (UpsellStatus)(r.upsell_status ?? 0),
LastTouchedAt = r.last_touched_at,
LastTouchedBy = r.last_touched_by
}).ToQueryResult(request);
}
}
public IEnumerable<UpsellProductClassDefinitionDto> GetUpsellProductClassesDefinitions()
{
using (var db = new UpsellEntities())
{
var upsellProductClassDefinitions = db.UpsellProductClassDefinitions.Where(s => (s.enabled == 1));
return TypeAdapter.Adapt<IEnumerable<UpsellProductClassDefinition>, IEnumerable<UpsellProductClassDefinitionDto>>(upsellProductClassDefinitions);
}
}
public DetailedUpsellSubmissionDto GetRecordById(int recordId, string userId)
{
using (var db = new UpsellEntities())
{
var timestamp = new DateTimeOffset(DateTime.Now);
// get record from database
var record = db.UpsellSubmissions.FirstOrDefault(r => r.id.Equals(recordId));
// check for no result
if (record == null)
{
throw new Exception(string.Format("Could not retrieve record with ID [{0}] from database", recordId));
}
// check if record is in-progress and not in use by current user
var lastTouchedBy = record.last_touched_by;
if ((record.upsell_status == (int)UpsellStatus.InProgress) && !lastTouchedBy.Equals(userId, StringComparison.OrdinalIgnoreCase))
{
throw new Exception(string.Format("Record with ID [{0}] has been opened by user [{1}] and is locked", recordId, lastTouchedBy));
}
// lock record if necessary
if ((record.upsell_status == (int)UpsellStatus.New) || (record.upsell_status == (int)UpsellStatus.Incomplete))
{
record.upsell_status = (int)UpsellStatus.InProgress;
record.last_touched_by = userId;
record.last_touched_at = timestamp;
db.SaveChanges();
}
// get complete record with products, notes and audit trail
var products = db.UpsellProductChanges.Where(p => p.upsell_id == recordId).ToList();
var notes = db.UpsellNotes.Where(n => n.upsell_id == recordId).ToList();
var auditTrail = db.UpsellAudits.Where(a => a.upsell_id == recordId).ToList();
var upsellRecordDto = new DetailedUpsellSubmissionDto
(
TypeAdapter.Adapt<UpsellSubmission,UpsellSubmissionDto>(record),
TypeAdapter.Adapt<IEnumerable<UpsellProductChanges>, IEnumerable<UpsellProductChangeDto>>(products),
TypeAdapter.Adapt<IEnumerable<UpsellNote>, IEnumerable<UpsellNoteDto>>(notes),
TypeAdapter.Adapt<IEnumerable<UpsellAudit>, IEnumerable<UpsellAuditDto>>(auditTrail)
);
// add audit entry
db.UpsellAudits.AddObject(new UpsellAudit
{
upsell_id = recordId,
action_performed_by = userId,
action_date = timestamp,
action_performed = (int)UpsellAction.Opened
});
db.SaveChanges();
//To do - Change the UpsellProductChangeDto Obj and add whether it is send to WFM team
foreach (var p in upsellRecordDto.Products)
{
//If any of these products are in the product failures then mark them
//as unsuccessful submission to WFM Service
var productFails = db.UpsellProductFailures.SingleOrDefault(x => x.product_id == p.ProductId);
if (productFails != null)
{
p.IsOfflineSubmissionSuccess = false;
}
else
{
p.IsOfflineSubmissionSuccess = true;
}
}
// return record
return upsellRecordDto;
}
}
