/// <summary>
        /// This function extracts the job applications
        /// </summary>
        /// <param name="filters"></param>
        /// <param name="dr"></param> 
        /// <returns></returns>
        // ReSharper disable once FunctionComplexityOverflow
        private static JobApplication ExtractJobApplication(JobApplicationFilter filters, DataRow dr)
        {
            var application = new JobApplication
            {
                TotalCount = (int)dr["RECORDCOUNT"],
                JobApplicationId = (int)dr["JobApplicationId"],
                JobId = (int)dr["JobId"],
                ContactId = (int)dr["CandidateId"],
                ApplicationDate = (DateTime)dr["CreatedDate"],
                ApplicationStatusId = (int)dr["ApplicationStatusId"],
                InternalCandidate = (bool)dr["InternalCandidate"],
                AgentId = (int)dr["AgentId"],
                KqScore = (double)dr["KqScore"],
                LastUpdatedDate = (DateTime?)dr["LastUpdatedDate"],
                ReferrerId = (int)dr["ReferrerId"],
                ReferredBy = (dr["ReferredBy"] ?? "").ToString(),
                ReferringEmpNo = (dr["ReferringEmpNo"] ?? "").ToString(),
                UrlReferrer = (dr["UrlReferrer"] ?? "").ToString(),
                JobTitle =
                    (filters.NeedJobTitle != null && filters.NeedJobTitle.Value)
                        ? (dr["ApplicationJobTitle"] ?? "").ToString()
                        : "",
                ApplicationStatus =
                    (filters.NeedApplicationStatus != null && filters.NeedApplicationStatus.Value)
                        ? (dr["StatusName"] ?? "").ToString()
                        : "",
                Candidate = (filters.NeedCandidateDetails == null || filters.NeedCandidateDetails.Value)
                    ? new Candidate
                    {
                        CandidateId = (int)dr["CandidateId"],
                        Forename = (dr["ForeName"] ?? "").ToString(),
                        Surname = (dr["SurName"] ?? "").ToString(),
                        Title = (dr["Title"] ?? "").ToString(),
                        JobTitle = (dr["JobTitle"] ?? "").ToString(),
                        Employer = (dr["Employer"] ?? "").ToString(),
                        Email = (dr["Email"] ?? "").ToString(),
                        Mobile = (dr["Mobile"] ?? "").ToString(),
                        RefId = dr["RefId"].ToString(),
                        MinSalary = (int)dr["MinSalary"],
                        MaxSalary = (int)dr["MaxSalary"],
                        Rating = (int)dr["Rating"],
                        Location = new Location { LocationId = (int)dr["LocationId"], LocationText = (dr["Location"] ?? "").ToString() },
                        Documents = new List<Document>()
                    }
                    : null,
                Documents = new List<Document>()
            };
            //applicaion documents

            // other job related docs
            var otherJobRelatedDoc = Documents.GetDocumentByDocType(new[] { 4, 5 }, application.ContactId, application.JobId);
            if (application.Candidate != null)
            {  //profile image
                var profilePic = Documents.GetDocumentByDocType(new[] { 3 }, application.ContactId);
                application.Candidate.Documents.AddRange(profilePic);
                application.Candidate.Documents.AddRange(otherJobRelatedDoc);
            }
            else
            {
                application.Documents.AddRange(otherJobRelatedDoc);
            }
            return application;
        }
        /// <summary>
        /// This function adds/update the job application 
        /// </summary> 
        /// <param name="jApplication"></param>
        /// <returns></returns>
        public JobApplicationPostResponse AddUpdateJobApplication(JobApplication jApplication)
        {
            var response = new JobApplicationPostResponse
            {
                ContactId = -1,
                JobApplicationId = -1
            };
            var context = new dbDataContext();
            response.ContactId = jApplication.ContactId;
            // check for job Application - create or update job application details
            var jobApplication = context.tbl_JobApplications.FirstOrDefault(t => t.JobApplicationId == jApplication.JobApplicationId) ?? new tbl_JobApplication();
            jobApplication.CandidateId = jApplication.ContactId;
            jobApplication.JobId = jApplication.JobId;
            jobApplication.AgentId = jApplication.AgentId;
            jobApplication.InternalCandidate = jApplication.InternalCandidate;
            //check for application status changing
            var isAppStatusChanged = (jobApplication.ApplicationStatusId != jApplication.ApplicationStatusId);
            jobApplication.ApplicationStatusId = jApplication.ApplicationStatusId;
            jobApplication.LastUpdatedDate = DateTime.Now;
            jobApplication.ReferrerId = jApplication.ReferrerId;
            jobApplication.ReferredBy = jApplication.ReferredBy;
            jobApplication.ReferringEmpNo = jApplication.ReferringEmpNo;
            jobApplication.UrlReferrer = jApplication.UrlReferrer;
            jobApplication.KqScore = -1;
            try
            {
                var isAdd = false;
                // Add/Update candidate
                if (jobApplication.JobApplicationId <= 0)
                {
                    isAdd = true;
                    jobApplication.CreatedDate = DateTime.Now;
                    jobApplication.ApplicationStatusId = jobApplication.ApplicationStatusId < 1 ? 1 : jobApplication.ApplicationStatusId;
                    context.tbl_JobApplications.InsertOnSubmit(jobApplication);
                }
                context.SubmitChanges();

                // Add Application status changed record

                if (isAdd && jobApplication.ApplicationStatusId != 1)
                {
                    //add the new candidates status first
                    new ApplicationStatuses().ChangeApplicationStatuses(jobApplication.JobApplicationId.ToString(), 1);
                }
                if (isAppStatusChanged)
                    new ApplicationStatuses().ChangeApplicationStatuses(jobApplication.JobApplicationId.ToString(), jobApplication.ApplicationStatusId);


                if (isAdd)
                    // Add History Record 
                    new Histories().AddHistory(new History
                    {
                        RefId = jApplication.ContactId,
                        RefType = "Contact",
                        ClientUserId = jApplication.UpdatedBy,
                        TypeId = 4,
                        SubRefType = "JobApplication",
                        SubRefId = jobApplication.JobApplicationId
                    });

                response.JobApplicationId = jobApplication.JobApplicationId;
                return response;
            }
            catch (Exception e)
            {
                return response;
            }
        }
        /// <summary>
        /// This function adds/update the job application and creates the candidate if couldn't find one
        /// </summary> 
        /// <param name="jApplication"></param>
        /// <returns></returns>
        // ReSharper disable once FunctionComplexityOverflow
        public JobApplicationPostResponse AddUpdateJobApplication(JobApplication jApplication)
        {
            var response = new JobApplicationPostResponse
            {
                ContactId = -1,
                JobApplicationId = -1
            };
            var context = new dbDataContext();
            // check candidate - create or update candidate details
            if (jApplication.JobApplicationId <= 0)
            {
                var contactId = new Contacts().AddUpdateCandidate(jApplication.Candidate);
                if (contactId > 0)
                    jApplication.ContactId = contactId;
                else
                    return response;
            }
            response.ContactId = jApplication.ContactId;
            // check for job Application - create or update job application details
            var jobApplication = context.tbl_JobApplications.FirstOrDefault(t => t.JobApplicationId == jApplication.JobApplicationId) ?? new tbl_JobApplication();
            jobApplication.CandidateId = jApplication.ContactId;
            jobApplication.JobId = jApplication.JobId;
            jobApplication.AgentId = jApplication.AgentId;
            jobApplication.InternalCandidate = jApplication.InternalCandidate;
            //check for application status changing
            var isAppStatusChanged = (jobApplication.ApplicationStatusId != jApplication.ApplicationStatusId);
            jobApplication.ApplicationStatusId = jApplication.ApplicationStatusId;
            jobApplication.LastUpdatedDate = DateTime.Now;
            jobApplication.ReferrerId = jApplication.ReferrerId;
            jobApplication.ReferredBy = jApplication.ReferredBy;
            jobApplication.ReferringEmpNo = jApplication.ReferringEmpNo;
            jobApplication.UrlReferrer = jApplication.UrlReferrer;

            try
            {
                var isAdd = false;
                // Add/Update candidate
                if (jobApplication.JobApplicationId <= 0)
                {
                    isAdd = true;
                    jobApplication.CreatedDate = DateTime.Now;
                    jobApplication.ApplicationStatusId = jobApplication.ApplicationStatusId < 1 ? 1 : jobApplication.ApplicationStatusId;
                    context.tbl_JobApplications.InsertOnSubmit(jobApplication);
                }
                context.SubmitChanges();

                // Add Application status changed record


                if (isAdd && jobApplication.ApplicationStatusId != 1)
                {
                    //add the new candidates status first
                    new ApplicationStatuses().ChangeApplicationStatuses(jobApplication.JobApplicationId.ToString(), 1);
                }
                if (isAppStatusChanged)
                    new ApplicationStatuses().ChangeApplicationStatuses(jobApplication.JobApplicationId.ToString(), jobApplication.ApplicationStatusId);

                //send notification email 
                if (isAdd && jApplication.SendConfirmationEmail)
                {
                    var contact = new Contacts().GetCandidate(jApplication.ContactId);
                    if (contact != null && !string.IsNullOrEmpty(contact.Email))
                    {
                        try
                        {
                            var template = new Templates().GetTemplates(new TemplateFilter { Title = "Job Application Successful" }).FirstOrDefault();
                            if (template != null)
                            {
                                var emailBody = Utils.GetFullEmailReplacingMainTemplateTags(-1, template.TemplateBody);
                                emailBody = new Utils().GetOriginalBody(emailBody, jApplication.ContactId, jApplication.JobId, 0, 0, 0, contact);
                                //send email
                                var email = new Email
                                {
                                    Body = emailBody,
                                    FromAddress = new Recipient { DisplayName = "Resonate Search", MailAddress = "*****@*****.**" },
                                    Subject = template.Subject,
                                    ToAddress = new Recipient { DisplayName = contact.Forename + " " + contact.Surname, MailAddress = contact.Email }
                                };
                                new Emails().SendEmail(email);
                            }
                        }
                        catch (Exception)
                        {
                            //ignore
                        }
                    }
                }
                if (isAdd)
                    // Add History Record 
                    new Histories().AddHistory(new History
                    {
                        RefId = jApplication.ContactId,
                        RefType = "Contact",
                        ClientUserId = jApplication.UpdatedBy,
                        TypeId = 4,
                        SubRefType = "JobApplication",
                        SubRefId = jobApplication.JobApplicationId
                    });

                response.JobApplicationId = jobApplication.JobApplicationId;
                return response;
            }
            catch (Exception)
            {
                return response;
            }
        }
 private JobApplicationPostResponse AddJobApplication(tbl_JobApplicationByEmail application, int candidateId)
 {
     //create job applicaiton object 
     var jobApplication = new JobApplication
     {
         JobApplicationId = -1,
         ApplicationStatusId = 1,
         JobId = application.JobId,
         UpdatedBy = 0,
         ApplicationDate = application.CreatedDate,
         ContactId = candidateId,
         Candidate = new Candidate { CandidateId = candidateId },
         SendConfirmationEmail = false,
         UrlReferrer = application.JobBoard
     };
     return AddUpdateJobApplication(jobApplication);
 }