//CONSTRUCTOR
        public EditEmployeeWindowViewModel()
        {
            LoadComboboxesCommand = new RelayCommand <object>(LoadComboboxes);
            EmployeeStatuses      = new ObservableCollection <EmployeeStatusModel>();
            ValidationErrors      = new ObservableCollection <string>();
            Messenger.Default.Register <EmployeeToken>(this, OnNewEmployeeToken);

            Departments                = new ObservableCollection <DepartmentModel>();
            JobTitles                  = new ObservableCollection <JobTitleModel>();
            NewCitation                = new CitationModel();
            NewPhone                   = new PhoneModel();
            AddPhoneCommand            = new RelayCommand <object>(AddPhone);
            AddEmailCommand            = new RelayCommand <object>(AddEmail);
            AddCertificationCommand    = new RelayCommand <object>(AddCertification);
            AddCitationCommand         = new RelayCommand <object>(AddCitation);
            AddRestrictionCommand      = new RelayCommand <object>(AddRestriction);
            AddDocumentCommand         = new RelayCommand <object>(AddDocument);
            RemovePhoneCommand         = new RelayCommand <ObservableCollection <PhoneModel> >(RemovePhone);
            RemoveEmailCommand         = new RelayCommand <ObservableCollection <EmailModel> >(RemoveEmail);
            RemoveCertificationCommand = new RelayCommand <ObservableCollection <CertificationModel> >(RemoveCertification);
            RemoveCitationCommand      = new RelayCommand <ObservableCollection <CitationModel> >(RemoveCitation);
            RemoveRestrictionCommand   = new RelayCommand <ObservableCollection <RestrictionModel> >(RemoveRestriction);
            RemoveDocumentCommand      = new RelayCommand <ObservableCollection <DocumentModel> >(RemoveDocument);
            ValidateNewEmployeeCommand = new RelayCommand <object>(ValidateNewEmployee);
        }
Exemple #2
0
        /// <summary>
        /// Sets the Citation home image.
        /// </summary>
        /// <param name="argModel">
        /// The argument model.
        /// </param>
        /// <returns>
        /// </returns>
        /// <exception cref="ArgumentNullException">
        /// argModel
        /// </exception>
        public static CitationModel SetHomeImage(CitationModel argModel)
        {
            if (argModel is null)
            {
                throw new ArgumentNullException(nameof(argModel));
            }

            // Try media reference collection first
            HLinkHomeImageModel hlink = argModel.GMediaRefCollection.FirstHLinkHomeImage;

            // Check Source for Image
            if ((!hlink.Valid) && (argModel.GSourceRef.DeRef.HomeImageHLink.LinkToImage))
            {
                hlink = argModel.GSourceRef.DeRef.HomeImageHLink;
            }

            // Handle the link if we can
            if (!hlink.Valid)
            {
                argModel.HomeImageHLink.HomeImageType = CommonConstants.HomeImageTypeSymbol;
            }
            else
            {
                argModel.HomeImageHLink.HomeImageType = CommonConstants.HomeImageTypeThumbNail;
                argModel.HomeImageHLink.HLinkKey      = hlink.HLinkKey;
            }

            // Get colour
            Application.Current.Resources.TryGetValue("CardBackGroundCitation", out var varCardColour);
            argModel.HomeImageHLink.HomeSymbolColour = (Color)varCardColour;

            return(argModel);
        }
        /// <summary>
        /// Handles navigation in wards and sets up the event model parameter.
        /// </summary>
        /// <param name="e">
        /// The <see cref="NavigatedToEventArgs"/> instance containing the event data.
        /// </param>
        /// <param name="viewModelState">
        /// The parameter is not used.
        /// </param>
        public override void PopulateViewModel()
        {
            // Handle HLinkKeys
            CitationObject = DV.CitationDV.GetModelFromHLinkString(BaseNavParamsHLink.HLinkKey);

            if (CitationObject != null)
            {
                BaseTitle     = CitationObject.GetDefaultText;
                BaseTitleIcon = CommonConstants.IconCitation;

                // Get media image
                HLinkHomeImageModel mediaImage = CitationObject.HomeImageHLink;
                mediaImage.CardType = DisplayFormat.MediaCardLarge;
                BaseDetail.Add(mediaImage);

                //// Get Note
                //CardGroup noteCardGroup = new CardGroup();
                //NoteCardFull noteCard = new NoteCardFull();
                //noteCard.BindingContext = CitationObject.GetFirstModel;
                //noteCardGroup.Add(noteCard);
                //BaseDetail.Add(noteCardGroup);

                // Get basic details
                CardGroup t = new CardGroup {
                    Title = "Header Details"
                };

                t.Add(new CardListLineCollection
                {
                    new CardListLine("Card Type:", "Citation Detail"),
                    new CardListLine("Date:", CitationObject.GDateContent.GetLongDateAsString),
                    new CardListLine("Page:", CitationObject.GPage),
                    new CardListLine("Confidence:", CitationObject.GConfidence.ToString(CultureInfo.CurrentCulture)),
                });

                t.Add(DV.CitationDV.GetModelInfoFormatted(CitationObject));

                BaseDetail.Add(t);

                // Add Source details
                HLinkSourceModel sourceCard = CitationObject.GSourceRef;
                sourceCard.CardType = DisplayFormat.SourceCardSmall;
                BaseDetail.Add(sourceCard);

                // If only one note (the most common case) just display it in a large format,
                // otherwise setup a list of them.
                if (CitationObject.GNoteRefCollection.Count > 0)
                {
                    // TODO Fix this NoteObject = CitationObject.GNoteRefCollection[0].DeRef;
                }

                // Add remaining details
                BaseDetail.Add(CitationObject.GMediaRefCollection.GetCardGroup());
                BaseDetail.Add(CitationObject.GNoteRefCollection.GetCardGroup());
                BaseDetail.Add(CitationObject.GTagRef.GetCardGroup());
                BaseDetail.Add(CitationObject.GSourceAttributeCollection);

                BaseDetail.Add(CitationObject.BackHLinkReferenceCollection.GetCardGroup());
            }
        }
        /// <summary>
        /// Helper method to sort and set the firt image link.
        /// </summary>
        public void SortAndSetFirst()
        {
            // Set the first image link. Assumes main image is manually set to the first image in
            // Gramps if we need it to be, e.g. Citations.
            ICitationModel tempCitationModel = new CitationModel();

            FirstHLinkHomeImage.HomeImageType = CommonEnums.HomeImageType.Unknown;

            if (Count > 0)
            {
                // Step through each citationmodel hlink in the collection
                for (int i = 0; i < Count; i++)
                {
                    tempCitationModel = DV.CitationDV.CitationData.GetModelFromHLink(this[i]);

                    if (tempCitationModel.HomeImageHLink.LinkToImage)
                    {
                        FirstHLinkHomeImage = tempCitationModel.HomeImageHLink;
                        break;
                    }
                }

                // Sort the collection
                List <HLinkCitationModel> t = this.OrderBy(HLinkCitationModel => HLinkCitationModel.DeRef.GDateContent.SortDate).ToList();

                Items.Clear();

                foreach (HLinkCitationModel item in t)
                {
                    Items.Add(item);
                }
            }
        }
        /// <summary>
        /// Sets the Citation home image.
        /// </summary>
        /// <param name="argModel">
        /// The argument model.
        /// </param>
        /// <returns>
        /// </returns>
        /// <exception cref="ArgumentNullException">
        /// argModel
        /// </exception>
        public static CitationModel SetHomeImage(CitationModel argModel)
        {
            if (argModel is null)
            {
                throw new ArgumentNullException(nameof(argModel));
            }

            // Try media reference collection first
            HLinkHomeImageModel hlink = argModel.GMediaRefCollection.FirstHLinkHomeImage;

            // Check Source for Image
            if ((!hlink.Valid) && (argModel.GSourceRef.DeRef.HomeImageHLink.LinkToImage))
            {
                hlink = argModel.GSourceRef.DeRef.HomeImageHLink;
            }

            // Handle the link if we can
            if (hlink.Valid)
            {
                argModel.HomeImageHLink.HomeImageType = CommonEnums.HomeImageType.ThumbNail;
                argModel.HomeImageHLink.HLinkKey      = hlink.HLinkKey;
            }

            return(argModel);
        }
 public void Add(Citation citation)
 {
     lock (addLock)
     {
         citationModel = mapper.Map(citation);
         objectRepository.Set(citationModel);
         objectRepository.Save();
     }
 }
Exemple #7
0
        public async Task LoadCitationsAsync()
        {
            _iocCommonNotifications.DataLogEntryAdd("Loading Citation data");
            {
                try
                {
                    // Run query
                    IEnumerable <XElement> de =
                        from el in localGrampsXMLdoc.Descendants(ns + "citation")
                        select el;

                    // Loop through results to get the Citation
                    foreach (XElement pcitation in de)
                    {
                        CitationModel loadCitation = new CitationModel();

                        // Citation attributes
                        loadCitation.LoadBasics(GetBasics(pcitation));

                        if (loadCitation.Id == "C0520")
                        {
                        }

                        // Citation fields
                        loadCitation.GDateContent = GetDate(pcitation);

                        loadCitation.GPage = GetElement(pcitation.Element(ns + "page"));

                        loadCitation.GConfidence = GetDataConfidence(pcitation);

                        loadCitation.GNoteRefCollection = GetNoteCollection(pcitation);

                        // ObjectRef loading
                        loadCitation.GMediaRefCollection = await GetObjectCollection(pcitation).ConfigureAwait(false);

                        loadCitation.GSourceAttributeCollection = GetSrcAttributeCollection(pcitation);

                        loadCitation.GSourceRef.HLinkKey = GetHLinkKey(pcitation.Element(ns + "sourceref"));

                        loadCitation.GTagRef = GetTagCollection(pcitation);

                        // Save the citation
                        DataStore.Instance.DS.CitationData.Add(loadCitation);
                    }
                }
                catch (Exception e)
                {
                    _iocCommonNotifications.NotifyException("Exception loading Citations form XML", e);
                    throw;
                }

                _iocCommonNotifications.DataLogEntryReplace("Citation load complete");

                return;
            }
        }
        public void AddCitation(object e)
        {
            var newCitation = new CitationModel()
            {
                Name = NewCitation.Name,
                Date = NewCitation.Date,
            };

            _selectedEmployee.Citations.Add(newCitation);
        }
        public async Task <IActionResult> Citations([FromBody] CitationModel model, Guid Id)
        {
            var response = new APIResponse <CitationModel>()
            {
                Success = true
            };

            try
            {
                var currentPageNum = model.Page;
                var offset         = (model.PageSize * currentPageNum) - model.PageSize;

                //Get a list of Accounts for a user
                var userAccount = await CommonContext.UserAccounts.Include(m => m.Account).ThenInclude(m => m.Partition).Where(m => m.UserId == Id).ToListAsync();

                var citations = new List <Citation>();

                //Loop through each AccountContext for this user and get a list of Citations
                foreach (var commonAccount in userAccount)
                {
                    //Get the correct account database based on the partition that was chosen for the account.
                    var accountCtx = ContextsUtility.CreateAccountContext(Cryptography.Decrypt(commonAccount.Account.Partition.ConnectionString));

                    var citationsForAccount = accountCtx.Citations.Include(x => x.Violation)
                                              .Include(x => x.AssignedTo)
                                              .Include(x => x.Comments).Include(m => m.CreateUser)
                                              .Include(x => x.Attachments).ThenInclude(m => m.Attachment)
                                              .OrderByDescending(x => x.CreateUtc)
                                              .Where(m => m.AccountId == commonAccount.AccountId).AsQueryable();

                    citations.AddRange(citationsForAccount);
                }


                var totalQueryCount = citations.Count();
                var results         = citations.Skip(offset).Take(model.PageSize).ToList();
                var data            = Mapper.Map <List <CitationListModel> >(results);
                model.CitationList = data;
                model.CurrentPage  = currentPageNum;
                model.ItemsPerPage = model.PageSize;
                model.TotalItems   = totalQueryCount;
                response.Data      = model;
                response.Success   = true;
                return(Ok(response));
            }
            catch (Exception ex)
            {
                response.Success = false;
                response.Message = ex.Message;
                return(Ok(response));
            }
        }
        public async Task <IActionResult> Get([FromBody] CitationModel model)
        {
            var response = new APIResponse <CitationModel>()
            {
                Success = true
            };
            var currentPageNum = model.Page;
            var offset         = (model.PageSize * currentPageNum) - model.PageSize;

            var citations = _accountCtx.Citations
                            .Include(x => x.Violation)
                            .Include(x => x.AssignedTo)
                            .Include(x => x.Comments).Include(m => m.CreateUser)
                            .Include(x => x.Attachments).ThenInclude(m => m.Attachment)
                            .OrderByDescending(x => x.CreateUtc)
                            .ForAccount(CommonAccount.Id).AsQueryable();

            if (!string.IsNullOrWhiteSpace(model.CreatedFrom))
            {
                citations = citations.Where(s => s.CreateUtc >= (Convert.ToDateTime(model.CreatedFrom).UTCToAccountLocalTime(CommonAccount.CityTimeZone)));
            }
            if (!string.IsNullOrWhiteSpace(model.CreatedTo))
            {
                citations = citations.Where(s => s.CreateUtc <= (Convert.ToDateTime(model.CreatedTo).UTCToAccountLocalTime(CommonAccount.CityTimeZone)));
            }
            if (model.CreatedById != null)
            {
                citations = citations.Where(s => s.CreateUserId == model.CreatedById);
            }
            if (!string.IsNullOrWhiteSpace(model.LicensePlate))
            {
                citations = citations.Where(s => s.LicensePlate == model.LicensePlate);
            }

            var totalQueryCount = await citations.CountAsync();

            var results = await citations.Skip(offset).Take(model.PageSize).ToListAsync();

            var data = Mapper.Map <List <CitationListModel> >(results);

            model.CitationList = data;
            model.CurrentPage  = currentPageNum;
            model.ItemsPerPage = model.PageSize;
            model.TotalItems   = totalQueryCount;
            response.Data      = model;
            response.Success   = true;
            return(Ok(response));
        }
Exemple #11
0
        /// <summary>
        /// Handles navigation in wards and sets up the event model parameter.
        /// </summary>
        /// <param name="e">
        /// The <see cref="NavigatedToEventArgs"/> instance containing the event data.
        /// </param>
        /// <param name="viewModelState">
        /// The parameter is not used.
        /// </param>
        public override void PopulateViewModel()
        {
            // Handle HLinkKeys
            CitationObject = DV.CitationDV.GetModelFromHLinkString(BaseNavParamsHLink.HLinkKey);

            // Trigger refresh of View fields via INotifyPropertyChanged
            RaisePropertyChanged(string.Empty);

            if (CitationObject != null)
            {
                BaseTitle     = CitationObject.GetDefaultText;
                BaseTitleIcon = CommonConstants.IconCitation;

                // Get basic details
                //   CardGroup t = new CardGroup { Title = "Header Details" };

                BaseDetail.Add(new CardListLineCollection
                {
                    new CardListLine("Card Type:", "Citation Detail"),
                    new CardListLine("Page:", CitationObject.GPage),
                    new CardListLine("Confidence:", CitationObject.GConfidence.ToString(CultureInfo.CurrentCulture)),
                });

                // Get date card
                BaseDetail.Add(CitationObject.GDateContent.AsCardListLine());

                BaseDetail.Add(DV.CitationDV.GetModelInfoFormatted(CitationObject));

                //BaseDetail.Add(t);

                // If only one note (the most common case) just display it in a large format,
                // otherwise setup a list of them.
                if (CitationObject.GNoteRefCollection.Count > 0)
                {
                    // TODO Fix this NoteObject = CitationObject.GNoteRefCollection[0].DeRef;
                }

                // TODO BaseDetail.Add(CitationObject.GSourceAttributeCollection);
            }
        }
Exemple #12
0
 public static async Task <ObservableCollection <EmployeeModel> > EmployeeQueryAsync()
 {
     using (var connection = new System.Data.SqlClient.SqlConnection(CnnString(database)))
     {
         var employees = new Dictionary <int, EmployeeModel>();
         await connection.QueryAsync <EmployeeModel>("dbo.spGetEmployeeData_All", new[]
         {
             typeof(EmployeeModel),
             typeof(EmailModel),
             typeof(JobTitleModel),
             typeof(PhoneModel),
             typeof(DepartmentModel),
             typeof(EmployeeStatusModel),
             typeof(CitationModel),
             typeof(CertificationModel),
             typeof(EquipmentAssignmentRecordModel),
             typeof(RestrictionModel)
         }
                                                     , obj =>
         {
             EmployeeModel employeeModel           = obj[0] as EmployeeModel;
             EmailModel emailModel                 = obj[1] as EmailModel;
             JobTitleModel titleModel              = obj[2] as JobTitleModel;
             PhoneModel phoneModel                 = obj[3] as PhoneModel;
             DepartmentModel departmentModel       = obj[4] as DepartmentModel;
             EmployeeStatusModel statusModel       = obj[5] as EmployeeStatusModel;
             CitationModel citationModel           = obj[6] as CitationModel;
             CertificationModel certificationModel = obj[7] as CertificationModel;
             EquipmentAssignmentRecordModel equipmentAssignmentRecord = obj[8] as EquipmentAssignmentRecordModel;
             RestrictionModel restrictionModel = obj[9] as RestrictionModel;
             //employeemodel
             var employeeEntity = new EmployeeModel();
             if (!employees.TryGetValue(employeeModel.Id, out employeeEntity))
             {
                 employees.Add(employeeModel.Id, employeeEntity = employeeModel);
             }
             //list<emailmodel>
             if (employeeEntity.Emails == null)
             {
                 employeeEntity.Emails = new ObservableCollection <EmailModel>();
             }
             if (emailModel != null)
             {
                 if (!employeeEntity.Emails.Any(x => x.Id == emailModel.Id))
                 {
                     employeeEntity.Emails.Add(emailModel);
                 }
             }
             //phonemodel
             if (employeeEntity.Phones == null)
             {
                 employeeEntity.Phones = new ObservableCollection <PhoneModel>();
             }
             if (phoneModel != null)
             {
                 if (!employeeEntity.Phones.Any(x => x.Id == phoneModel.Id))
                 {
                     employeeEntity.Phones.Add(phoneModel);
                 }
             }
             //title
             if (employeeEntity.JobTitle == null)
             {
                 if (titleModel != null)
                 {
                     employeeEntity.JobTitle = titleModel;
                 }
             }
             //department
             if (employeeEntity.Department == null)
             {
                 if (departmentModel != null)
                 {
                     employeeEntity.Department = departmentModel;
                 }
             }
             //status
             if (employeeEntity.Status == null)
             {
                 if (statusModel != null)
                 {
                     employeeEntity.Status = statusModel;
                 }
             }
             //citation
             if (employeeEntity.Citations == null)
             {
                 employeeEntity.Citations = new ObservableCollection <CitationModel>();
             }
             if (citationModel != null)
             {
                 if (!employeeEntity.Citations.Any(x => x.Id == citationModel.Id))
                 {
                     employeeEntity.Citations.Add(citationModel);
                 }
             }
             //certification
             if (employeeEntity.Certifications == null)
             {
                 employeeEntity.Certifications = new ObservableCollection <CertificationModel>();
             }
             if (certificationModel != null)
             {
                 if (!employeeEntity.Certifications.Any(x => x.Id == certificationModel.Id))
                 {
                     employeeEntity.Certifications.Add(certificationModel);
                 }
             }
             //restriction
             if (employeeEntity.Restrictions == null)
             {
                 employeeEntity.Restrictions = new ObservableCollection <RestrictionModel>();
             }
             if (restrictionModel != null)
             {
                 if (!employeeEntity.Restrictions.Any(x => x.Id == restrictionModel.Id))
                 {
                     employeeEntity.Restrictions.Add(restrictionModel);
                 }
             }
             //equipment record
             if (employeeEntity.EquipmentAssignments == null)
             {
                 employeeEntity.EquipmentAssignments = new ObservableCollection <EquipmentAssignmentRecordModel>();
             }
             if (equipmentAssignmentRecord != null)
             {
                 if (!employeeEntity.EquipmentAssignments.Any(x => x.Id == equipmentAssignmentRecord.Id))
                 {
                     employeeEntity.EquipmentAssignments.Add(equipmentAssignmentRecord);
                 }
             }
             return(employeeEntity);
         });;
         var result             = employees.Values.ToList();
         var employeeCollection = new ObservableCollection <EmployeeModel>(result);
         return(employeeCollection);
     }
 }
Exemple #13
0
        /// <summary>
        /// Load Citations from external storage.
        /// </summary>
        /// <returns>
        /// Flag if loaded successfully.
        /// </returns>
        public async Task LoadCitationsAsync()
        {
            await DataStore.CN.MajorStatusAdd("Loading Citation data").ConfigureAwait(false);

            {
                // Load notes

                try
                {
                    // Run query
                    var de =
                        from el in localGrampsXMLdoc.Descendants(ns + "citation")
                        select el;

                    // Loop through results to get the Citation

                    foreach (XElement pcitation in de)
                    {
                        CitationModel loadCitation = DV.CitationDV.NewModel();

                        // Citation attributes
                        loadCitation.Id     = GetAttribute(pcitation, "id");
                        loadCitation.Change = GetDateTime(pcitation, "change");
                        loadCitation.Priv   = SetPrivateObject(GetAttribute(pcitation, "priv"));
                        loadCitation.Handle = GetAttribute(pcitation, "handle");

                        if (loadCitation.Id == "C0656")
                        {
                        }

                        // Citation fields

                        loadCitation.GDateContent = GetDate(pcitation);

                        loadCitation.GPage = GetElement(pcitation.Element(ns + "page"));

                        loadCitation.GConfidence = GetElement(pcitation.Element(ns + "confidence"));

                        loadCitation.GNoteRefCollection = GetNoteCollection(pcitation);

                        // ObjectRef loading
                        loadCitation.GMediaRefCollection = await GetObjectCollection(pcitation).ConfigureAwait(false);

                        loadCitation.GSourceAttributeCollection = GetSrcAttributeCollection(pcitation);

                        loadCitation.GSourceRef.HLinkKey = GetAttribute(pcitation.Element(ns + "sourceref"), "hlink");

                        loadCitation.GTagRef = GetTagCollection(pcitation);

                        // set the Home image or symbol now that everything is laoded
                        loadCitation = SetHomeImage(loadCitation);

                        // save the event
                        DV.CitationDV.CitationData.Add(loadCitation);
                    }
                }
                catch (Exception e)
                {
                    DataStore.CN.NotifyException("Exception loading Citations form XML", e);
                    throw;
                }
            }

            await DataStore.CN.MajorStatusDelete().ConfigureAwait(false);

            return;
        }
 public UpdateCitationCommand(CitationObjectRepository <CitationModel> objectRepository, CitationModel citation)
 {
     this.objectRepository = objectRepository;
     this.citation         = citation;
 }
 public AddCitationCommand(IObjectRepository <CitationModel> objectRepository, CitationModel citationModel, ICitationModelMapper mapper)
 {
     this.objectRepository = objectRepository;
     this.mapper           = mapper;
     this.citationModel    = citationModel;
 }
Exemple #16
0
        public async Task <ObservableCollection <EmployeeModel> > GetEmployeeList()
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString("WorkDeskDB")))
            {
                var sql       = @"SELECT e.id, e.FirstName, e.LastName, e.Nickname, e.HireDate,
                                em.id as ID, em.Address, em.Type, 
                                jt.id as ID, jt.Name, 
                                p.id as ID, p.Number, p.Type,
                                d.id as ID, d.Name,
                                es.id as ID, es.Name,
						        ecitt.id as ID, cit.Name, ecitt.Date, ecitt.Description,
                                ecert.id as ID, cet.Name, cet.Description, ecert.InitialDate, ecert.EmployeeID,
						        eqa.id as ID, eq.InventoryID, eq.Description, eqa.DateOut, eqa.DateIn, eqa.ConditionOut, eqa.ConditionIn, eqa.DueDate   


                        FROM dbo.Employees e 
                        LEFT JOIN dbo.Emails em
                        ON em.EmployeeID = e.id
                        LEFT JOIN dbo.JobTitles jt                           
                        ON e.JobTitleID = jt.id
                        LEFT JOIN Phones p
                        ON p.EmployeeID = e.id
                        LEFT JOIN dbo.Departments d
                        ON e.DepartmentID = d.id
                        LEFT JOIN dbo.EmployeeStatus es  
                        ON e.StatusID = es.id
				        LEFT JOIN dbo.EmployeeCitationType ecitt
				        ON e.id = ecitt.EmployeeID
				        LEFT JOIN dbo.CitationTypes cit
				        ON ecitt.CitationTypeID = cit.id
				        LEFT JOIN dbo.EmployeeCertificationType ecert
				        ON e.id = ecert.EmployeeID
				        LEFT JOIN dbo.CertificationType cet
				        ON ecert.NameID = cet.id
				        LEFT JOIN EquipmentAssignments eqa
				        ON eqa.EmployeeID = e.id
				        LEFT JOIN Equipment eq
				        ON eqa.EquipmentID = eq.id"                ;
                var employees = new Dictionary <int, EmployeeModel>();
                await connection.QueryAsync <EmployeeModel>
                    (sql,
                    new[]
                {
                    typeof(EmployeeModel),
                    typeof(EmailModel),
                    typeof(TitleModel),
                    typeof(PhoneModel),
                    typeof(DepartmentModel),
                    typeof(EmployeeStatusModel),
                    typeof(CitationModel),
                    typeof(CertificationModel),
                    typeof(EquipmentAssignmentRecordModel)
                }
                    , obj =>
                {
                    EmployeeModel employeeModel           = obj[0] as EmployeeModel;
                    EmailModel emailModel                 = obj[1] as EmailModel;
                    TitleModel titleModel                 = obj[2] as TitleModel;
                    PhoneModel phoneModel                 = obj[3] as PhoneModel;
                    DepartmentModel departmentModel       = obj[4] as DepartmentModel;
                    EmployeeStatusModel statusModel       = obj[5] as EmployeeStatusModel;
                    CitationModel citationModel           = obj[6] as CitationModel;
                    CertificationModel certificationModel = obj[7] as CertificationModel;
                    EquipmentAssignmentRecordModel equipmentAssignmentRecord = obj[8] as EquipmentAssignmentRecordModel;

                    //employeemodel
                    EmployeeModel employeeEntity = new EmployeeModel();
                    if (!employees.TryGetValue(employeeModel.ID, out employeeEntity))
                    {
                        employees.Add(employeeModel.ID, employeeEntity = employeeModel);
                    }

                    //list<emailmodel>
                    if (employeeEntity.Emails == null)
                    {
                        employeeEntity.Emails = new ObservableCollection <EmailModel>();
                    }
                    if (emailModel != null)
                    {
                        if (!employeeEntity.Emails.Any(x => x.ID == emailModel.ID))
                        {
                            employeeEntity.Emails.Add(emailModel);
                        }
                    }

                    //phonemodel
                    if (employeeEntity.Phones == null)
                    {
                        employeeEntity.Phones = new ObservableCollection <PhoneModel>();
                    }
                    if (phoneModel != null)
                    {
                        if (!employeeEntity.Phones.Any(x => x.ID == phoneModel.ID))
                        {
                            employeeEntity.Phones.Add(phoneModel);
                        }
                    }

                    //title
                    if (employeeEntity.JobTitle == null)
                    {
                        if (titleModel != null)
                        {
                            employeeEntity.JobTitle = titleModel;
                        }
                    }

                    //department
                    if (employeeEntity.Department == null)
                    {
                        if (departmentModel != null)
                        {
                            employeeEntity.Department = departmentModel;
                        }
                    }

                    //status
                    if (employeeEntity.JobStatus == null)
                    {
                        if (statusModel != null)
                        {
                            employeeEntity.JobStatus = statusModel;
                        }
                    }

                    //citation
                    if (employeeEntity.Citations == null)
                    {
                        employeeEntity.Citations = new ObservableCollection <CitationModel>();
                    }
                    if (citationModel != null)
                    {
                        if (!employeeEntity.Citations.Any(x => x.ID == citationModel.ID))
                        {
                            employeeEntity.Citations.Add(citationModel);
                        }
                    }

                    //certification
                    if (employeeEntity.Certifications == null)
                    {
                        employeeEntity.Certifications = new ObservableCollection <CertificationModel>();
                    }
                    if (certificationModel != null)
                    {
                        if (!employeeEntity.Certifications.Any(x => x.ID == certificationModel.ID))
                        {
                            employeeEntity.Certifications.Add(certificationModel);
                        }
                    }

                    //equipment record
                    if (employeeEntity.EquipmentAssignments == null)
                    {
                        employeeEntity.EquipmentAssignments = new ObservableCollection <EquipmentAssignmentRecordModel>();
                    }
                    if (equipmentAssignmentRecord != null)
                    {
                        if (!employeeEntity.EquipmentAssignments.Any(x => x.ID == equipmentAssignmentRecord.ID))
                        {
                            employeeEntity.EquipmentAssignments.Add(equipmentAssignmentRecord);
                        }
                    }
                    return(employeeEntity);
                });;

                var result = employees.Values.ToList();
                ObservableCollection <EmployeeModel> employeeCollection = new ObservableCollection <EmployeeModel>(result);
                return(employeeCollection);
            }
        }