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;
            }
        }