private bool ProcessBulkRows(Worksheet worksheet, ref int rowPosition, DataContext importDataContext)
        {
            while (!string.IsNullOrEmpty(worksheet.Cells[rowPosition, 1].Value2?.ToString()))
            {
                string number = worksheet.Cells[rowPosition, 1].Value2?.ToString();
                string date = worksheet.Cells[rowPosition, 2].Text;
                string time = worksheet.Cells[rowPosition, 3].Text;
                string addressLine1 = worksheet.Cells[rowPosition, 4].Value2?.ToString();
                string agent = worksheet.Cells[rowPosition, 5].Value2?.ToString();
                string branch = worksheet.Cells[rowPosition, 6].Value2?.ToString();
                string personInstructing = worksheet.Cells[rowPosition, 7].Value2?.ToString();
                string clerkName = worksheet.Cells[rowPosition, 8].Value2?.ToString();

                string addressLine2 = worksheet.Cells[rowPosition, 9].Value2?.ToString();
                string town = worksheet.Cells[rowPosition, 10].Value2?.ToString();
                string county = worksheet.Cells[rowPosition, 11].Value2?.ToString();
                string postcode = worksheet.Cells[rowPosition, 12].Value2?.ToString();

                string jobType = worksheet.Cells[rowPosition, 13].Value2?.ToString();
                string propertyType = worksheet.Cells[rowPosition, 14].Value2?.ToString();
                string additionalRooms = worksheet.Cells[rowPosition, 15].Value2?.ToString();
                double? basePrice = ParseDoubleValue(worksheet.Cells[rowPosition, 16].Value2?.ToString());
                double? discount = ParseDoubleValue(worksheet.Cells[rowPosition, 17].Value2?.ToString());
                double? net = ParseDoubleValue(worksheet.Cells[rowPosition, 18].Value2?.ToString());
                double? vat = ParseDoubleValue(worksheet.Cells[rowPosition, 19].Value2?.ToString());
                double? grossCharge = ParseDoubleValue(worksheet.Cells[rowPosition, 20].Value2?.ToString());

                string addressee = worksheet.Cells[rowPosition, 21].Value2?.ToString();
                string invoiceAddressLine1 = worksheet.Cells[rowPosition, 22].Value2?.ToString();
                string invoiceAddressLine2 = worksheet.Cells[rowPosition, 23].Value2?.ToString();
                string invoiceTown = worksheet.Cells[rowPosition, 24].Value2?.ToString();
                string invoiceCounty = worksheet.Cells[rowPosition, 25].Value2?.ToString();
                string invoicePostCode = worksheet.Cells[rowPosition, 26].Value2?.ToString();


                string orderRef = worksheet.Cells[rowPosition, 27].Value2?.ToString();
                string paymentType = worksheet.Cells[rowPosition, 28].Value2?.ToString();
                string paymentDate = worksheet.Cells[rowPosition, 29].Text.ToString();
                string paymentRef = worksheet.Cells[rowPosition, 30].Value2?.ToString();
                string comments = worksheet.Cells[rowPosition, 31].Value2?.ToString();
                string reference = worksheet.Cells[rowPosition, 32].Value2?.ToString();
                string clerkTelephone = worksheet.Cells[rowPosition, 33].Value2?.ToString();

                // create the model objects and persist them

                Appointment appointmentToImport = new Appointment()
                {
                    JobNumberTag = number,
                    Date = DateTime.Parse(date).Date                    
                };

                Job jobToImport = new Job()
                {
                    Number = number,
                    Appointment = appointmentToImport,
                    Agent = agent,
                    AddressLine1 = addressLine1,
                    AddressLine2 = addressLine2,
                    Town = town,
                    County = county,
                    Postcode = postcode,
                    Branch = branch,
                    AdditionalRooms = additionalRooms,
                    Comments = comments,
                    PersonInstructing = personInstructing,
                    Clerk = FindClerk(clerkName, clerkTelephone, importDataContext),
                    JobType = ParseJobType(jobType, importDataContext),
                    PropertyType = ParsePropertyType(propertyType, importDataContext),
                    Status = GetStatus(JobStatus.OPEN, importDataContext)
                };

                Invoice invoiceToImport = new Invoice()
                {
                    Addressee = addressee,
                    AddressLine1 = invoiceAddressLine1,
                    AddressLine2 = invoiceAddressLine2,
                    BasePrice = basePrice,
                    County = invoiceCounty,
                    Discount = discount,
                    GrossCharge = grossCharge,
                    Job = jobToImport,
                    NetWorth = net,
                    VAT = vat,
                    OrderReference = orderRef,
                    PaymentDate = ParseDateValue(paymentDate),
                    PaymentReference = paymentRef,
                    PaymentType = paymentType,
                    Postcode = invoicePostCode,
                    Town = invoiceTown
                };
                jobToImport.Invoice = invoiceToImport;

                // apply changes
                importDataContext.Appointments.AddIfNotExists(appointmentToImport,
                    a => a.JobNumberTag == appointmentToImport.JobNumberTag);
                importDataContext.Jobs.AddIfNotExists(jobToImport, j => j.Number == jobToImport.Number);
                importDataContext.SaveChanges();

                rowPosition++;
                if (rowPosition % 1000 == 0)
                {
                    Console.Write("\b\b\b\b\b\b\b\b\b\b");
                    Console.Write(rowPosition.ToString().PadLeft(10));
                    return false;
                }
            }

            return true;
        }
        public void Test001_CreateJob_AllNew()
        {
            long countAppointments = AppointmentsRepository.Instance.GetAllAppointments().Count;
            long countInvoices = InvoicesRepository.Instance.GetAllInvoices().Count;
            long countPropertyTypes = PropertyTypeRepository.Instance.GetAllPropertyTypes().Count;
            long countJobTypes = JobTypeRepository.Instance.GetAllJobTypes().Count;
            long countJobs = JobsRepository.Instance.GetAllJobs().Count;

            Appointment brandNewAppointment = new Appointment()
            {
                JobNumberTag = "TEST!1",
                Date = DateTime.Now                
            };

            Job brandNewJob = new Job()
            {
                Number = "TEST!1",
                Appointment = brandNewAppointment,
                Agent = "",
                AddressLine1 = "",
                AddressLine2 = "",
                Town = "",
                County = "",
                Postcode = "",
                Branch = "",
                AdditionalRooms = "",
                Comments = "",
                PersonInstructing = "",
                Clerk = UsersRepository.Instance.GetUserByNameAndTelephone("Test User","072TEST"),
                JobType = JobTypeRepository.Instance.CreateOrUpdate(new JobType() { Type = "TEST!JobType"}),
                PropertyType = PropertyTypeRepository.Instance.CreateOrUpdate(new PropertyType() {  Description = "TEST!PT"}),
                Status = StatusRepository.GetStatus(JobStatus.OPEN)
            };

            Invoice brandNewInvoice = new Invoice()
            {
                Addressee = "",
                AddressLine1 = "",
                AddressLine2 = "",
                BasePrice = 0,
                County = "",
                Discount = 0,
                GrossCharge = 0,
                Job = brandNewJob,
                NetWorth = 0,
                VAT = 0,
                OrderReference = "",
                PaymentDate = null,
                PaymentReference = "",
                PaymentType = "",
                Postcode = "",
                Town = ""
            };
            brandNewJob.Invoice = brandNewInvoice;

            JobsRepository.Instance.CreateOrUpdate(brandNewJob);

            Assert.IsTrue(AppointmentsRepository.Instance.GetAllAppointments().Count > countAppointments);
            Assert.IsTrue(JobTypeRepository.Instance.GetAllJobTypes().Count >= countJobTypes);
            Assert.IsTrue(PropertyTypeRepository.Instance.GetAllPropertyTypes().Count >= countPropertyTypes);
            Assert.IsTrue(InvoicesRepository.Instance.GetAllInvoices().Count > countInvoices);
            Assert.IsTrue(JobsRepository.Instance.GetAllJobs().Count > countJobs);

            Assert.IsNotNull(JobsRepository.Instance.GetJobByNumber("TEST!1"));
        }
 public ModelAppointment(Appointment app)
 {
     mOriginalAppointment = app;
 }