private List <string> ReadMedicationOrderWorkSheet(TokenStringViewModel viewModel, ExcelWorksheet wk)
        {
            if (wk.Name != "Med Order Information" ||
                ((string)wk.Cells[1, 1].Value).Trim() != "Medication ID # (Pocket Nurse item ID)" ||
                ((string)wk.Cells[1, 2].Value).Trim() != "Medication Name" ||
                ((string)wk.Cells[1, 3].Value).Trim() != "Dose" ||
                ((string)wk.Cells[1, 4].Value).Trim() != "Frequency" ||
                ((string)wk.Cells[1, 5].Value).Trim() != "Route" ||
                (((string)wk.Cells[1, 6].Value).Trim() != "Patient ID" && ((string)wk.Cells[1, 6].Value).Trim() != "Patient ID 1"))
            {
                // Identification
                ModelState.AddModelError("medication-order", "This doesn't appear to be a medication-order worksheet");
                return(new List <string>());
            }
            if (wk.Dimension.Rows <= 1)
            {
                // Not enough rows (no medication orders)
                ModelState.AddModelError("medication-order", $"No medication orders {wk.Dimension.Rows}");
            }
            else
            {
                // Process data for this sheet
                // Skip the headers
                for (int i = wk.Dimension.Start.Row + 1;
                     i <= wk.Dimension.End.Row;
                     i++)
                {
                    // Order not associated with a patient?
                    if (wk.Cells[i, 6].Value == null)
                    {
                        continue;
                    }

                    var medicationOrder = new MedicationOrderRaw();
                    var patientIdIndex  = 6;
                    do
                    {
                        var patientId = Convert.ToString(wk.Cells[i, patientIdIndex].Value);
                        medicationOrder.PatientIdList.Add(patientId);
                    } while (patientIdIndex > wk.Dimension.End.Column ||
                             wk.Cells[i, patientIdIndex].Value == null);
                    medicationOrder.PocketNurseItemId = Convert.ToString(wk.Cells[i, 1].Value);
                    medicationOrder.MedicationName    = (string)wk.Cells[i, 2].Value;
                    medicationOrder.Dose      = Convert.ToString(wk.Cells[i, 3].Value);
                    medicationOrder.Frequency = Convert.ToString(wk.Cells[i, 4].Value);
                    medicationOrder.Route     = Convert.ToString(wk.Cells[i, 5].Value);
                    viewModel.AddMedicationOrder(medicationOrder);
                }
            }
            return(viewModel.MedicationOrders);
        }
        private List <string> ReadNotInFormularyWorkSheet(TokenStringViewModel viewModel, ExcelWorksheet wk)
        {
            var retval = new List <NotInFormulary>();

            if (wk.Dimension.Rows <= 1)
            {
                // Not enough rows (not in formulary)
                ModelState.AddModelError("notinformulary", $"No NotInFormulary rows {wk.Dimension.Rows}");
            }
            else if (wk.Dimension.Columns < 8)
            {
                // Not enough columns (not in formulary)
                ModelState.AddModelError("notinformulary", $"Not enough NotInFormulary columns {wk.Dimension.Columns}");
            }
            else if (wk.Name != "Items not in PN formulary" ||
                     ((string)wk.Cells[1, 1].Value).Trim() != "Generic Name" ||
                     ((string)wk.Cells[1, 2].Value).Trim() != "Alias" ||
                     ((string)wk.Cells[1, 3].Value).Trim() != "Strength" ||
                     ((string)wk.Cells[1, 4].Value).Trim() != "Unit" ||
                     ((string)wk.Cells[1, 5].Value).Trim() != "volume" ||
                     ((string)wk.Cells[1, 6].Value).Trim() != "unit" ||
                     (((string)wk.Cells[1, 7].Value).Trim() != "Total container volume for Liq/Inj" && ((string)wk.Cells[1, 7].Value).Trim() != "Total container volume for Liq/IV") ||
                     ((string)wk.Cells[1, 8].Value).Trim() != "Route")
            {
                // Identification
                ModelState.AddModelError("notinformulary", "This doesn't appear to be a notinformulary worksheet");
            }
            else
            {
                // Process data for this sheet
                // Skip the headers
                for (int i = wk.Dimension.Start.Row + 1;
                     i <= wk.Dimension.End.Row;
                     i++)
                {
                    viewModel.AddNotInFormulary(new NotInFormulary()
                    {
                        _id                  = -1,
                        GenericName          = (string)wk.Cells[i, 1].Value,
                        Alias                = (string)wk.Cells[i, 2].Value,
                        Strength             = wk.Cells[i, 3].Value == null ? string.Empty : wk.Cells[i, 3].Value.GetType() == typeof(string) ? (string)wk.Cells[i, 3].Value : Convert.ToString(wk.Cells[i, 3].Value),
                        StrengthUnit         = (string)wk.Cells[i, 4].Value,
                        Volume               = wk.Cells[i, 5].Value == null ? string.Empty : wk.Cells[i, 5].Value.GetType() == typeof(string) ? (string)wk.Cells[i, 5].Value : Convert.ToString(wk.Cells[i, 5].Value),
                        VolumeUnit           = (string)wk.Cells[i, 6].Value,
                        TotalContainerVolume = (string)wk.Cells[i, 7].Value,
                        Route                = (string)wk.Cells[i, 8].Value,
                    });
                }
            }
            return(viewModel.NotInFormulary);
        }
        public IActionResult Upload(IFormFile file)
        {
            // Bail out of the file was not uploaded
            if (file == null)
            {
                // No file
                ModelState.AddModelError("cabinet", "NULL file passed to Upload action in Import controller");
                return(RedirectToAction("Index"));
            }
            if (Path.GetExtension(file.FileName) != ".xlsx")
            {
                // Invalid file type (extension)
                ModelState.AddModelError("cabinet", "Invalid file extension to Upload action in Import controller");
                return(RedirectToAction("Index"));
            }

            long size = file.Length;

            if (file.Length > 0)
            {
                //var appUser = await GetCurrentUserAsync();
                var appUser = _userRepository.CurrentUser();

                // process uploaded file
                // Don't rely on or trust the FileName property without validation.
                using (var pck = new OfficeOpenXml.ExcelPackage())
                {
                    using (var stream = file.OpenReadStream())
                    {
                        pck.Load(stream);
                    }

                    var wsCount = pck.Workbook.Worksheets.Count();

                    // There should be four worksheets
                    if (wsCount < 4)
                    {
                        // Not enough worksheets
                        ModelState.AddModelError("cabinet", $"Not enough worksheets -> {pck.Workbook.Worksheets.Count}");
                        return(RedirectToAction("Index"));
                    }
                    OmniSession session = ReadSessionWorkSheet(pck.Workbook.Worksheets[0]);
                    if (session == null)
                    {
                        return(RedirectToAction("Index"));
                    }
                    var viewModel = new TokenStringViewModel(session);

                    var patientList = ReadPatientWorkSheet(viewModel, pck.Workbook.Worksheets[1]);

                    var medicationOrderList = ReadMedicationOrderWorkSheet(viewModel, pck.Workbook.Worksheets[2]);

                    var notInFormularyList = ReadNotInFormularyWorkSheet(viewModel, pck.Workbook.Worksheets[3]);

                    viewModel.WriteToFiles();

                    return(View(viewModel));
                }
            }
            return(NotFound());
        }
        private List <string> ReadPatientWorkSheet(TokenStringViewModel viewModel, ExcelWorksheet wk)
        {
            // Not enough rows to include header and data for patient
            if (wk.Dimension.Rows <= 1)
            {
                // Not enough rows
                ModelState.AddModelError("patient", $"Patient worksheet doesn't have enough rows {wk.Dimension.Rows}");
                return(new List <string>());
            }
            if (wk.Dimension.Columns < 6)
            {
                // Not enough columns
                ModelState.AddModelError("patient", $"Patient worksheet doesn't have enough columns {wk.Dimension.Columns}");
                return(new List <string>());
            }
            if (wk.Name != "Patient Information" ||
                ((string)wk.Cells[1, 1].Value).Trim() != "Patient First Name" ||
                ((string)wk.Cells[1, 2].Value).Trim() != "Patient Last name" ||
                ((string)wk.Cells[1, 3].Value).Trim() != "DOB" ||
                ((string)wk.Cells[1, 4].Value).Trim() != "MRN" ||
                ((string)wk.Cells[1, 5].Value).Trim() != "Patient ID (if different than MRN)" ||
                ((string)wk.Cells[1, 6].Value).Trim() != "Allergies")
            {
                // Identification
                ModelState.AddModelError("patient", "This doesn't appear to be a patient worksheet");
                return(new List <string>());
            }
            // Process data for this sheet
            // Skip the headers
            for (int i = wk.Dimension.Start.Row + 1;
                 i <= wk.Dimension.End.Row;
                 i++)
            {
                // Skip blank rows
                if (wk.Cells[i, 1].Value == null ||
                    wk.Cells[i, 2].Value == null)
                {
                    continue;
                }

                var patient = new Patient
                {
                    First = (string)wk.Cells[i, 1].Value,
                    Last  = (string)wk.Cells[i, 2].Value
                };
                patient.FullName = patient.First + " " + patient.Last;
                if (wk.Cells[i, 3].Value != null)
                {
                    if (wk.Cells[i, 3].Value.GetType() == typeof(DateTime))
                    {
                        patient.DOB = (DateTime)wk.Cells[i, 3].Value;
                    }
                    else if (wk.Cells[i, 3].Value.GetType() == typeof(string))
                    {
                        var re = new Regex(@"(\d{1,2})[/-](\d{1,2})");
                        var m  = re.Match((string)wk.Cells[i, 3].Value);
                        if (m.Success)
                        {
                            patient.DOB = new DateTime(1, int.Parse(m.Groups[1].Value), int.Parse(m.Groups[2].Value));
                        }
                        else
                        {
                            // Invalid date format
                            ModelState.AddModelError("patient", $"Patient DOB is a string but it is an urecognizable format {(string)wk.Cells[i, 3].Value}");
                        }
                    }
                    else
                    {
                        // Invalid type
                        ModelState.AddModelError("patient", $"Patient DOB is not a string and not DateTime {wk.Cells[i, 3].Value.GetType()}");
                    }
                }
                patient.MRN       = Convert.ToString(wk.Cells[i, 4].Value);
                patient.PatientId = (string)wk.Cells[i, 5].Value;
                if (patient.PatientId == null)
                {
                    patient.PatientId = patient.MRN;
                }
                var allergies = (string)wk.Cells[i, 6].Value;
                viewModel.AddPatient(patient, allergies);
            }
            return(viewModel.Patients);
        }