public Result GetAllListingsByEmployerV2(EmployerEntity employer)
        {
            var result = new Result();

            try
            {
                var credentials = new CredentialsManager();

                var con   = new DapperConnectionManager();
                var query = new QueryEntity
                {
                    Entity = employer,
                    Query  = @"SELECT *
                          FROM JobListings
                          WHERE EmployerId= @EmployerId
                    "
                };

                result = con.ExecuteQuery <JobListingEntity>(query);
                var listing_list = (List <JobListingEntity>)result.Entity;

                var formatted_listing_list = new List <dynamic>();
                var listing_cri_man        = new JobListingCriteriaManager();
                foreach (JobListingEntity listing in listing_list)
                {
                    var criteria           = (List <JobListingCriteriaEntity>)listing_cri_man.GetCriteriaByListingId(listing.JobListingId).Entity;
                    var formatted_Criteria = transformCriteria(criteria, Mode.DICTQuestionIdValue);
                    listing.JobListingCriteria_Dict_QuestionID_Value = formatted_Criteria;
                }

                result.Entity = listing_list;
                return(result);
            }
            catch (Exception ex)
            {
                if (result == null)
                {
                    result = new Result();
                }
                Logger.Log(ex);
                result.Entity  = null;
                result.Success = false;
                result.Message = "An error occurred" + ex.Message;
            }
            return(result);
        }
        public Result GetListingById(int listingId)
        {
            var result = new Result();

            try
            {
                var credentials = new CredentialsManager();

                var con   = new DapperConnectionManager();
                var query = new QueryEntity
                {
                    Entity = new { JobListingId = listingId },
                    Query  = @"SELECT *
                          FROM JobListings
                          where JobListingId = @JobListingId"
                };


                result = con.ExecuteGetOneItemQuery <JobListingEntity>(query);

                var listing         = (JobListingEntity)result.Entity;
                var listing_cri_man = new JobListingCriteriaManager();


                var criteria = (List <JobListingCriteriaEntity>)listing_cri_man.GetCriteriaByListingId(listing.JobListingId).Entity;
                listing.JobListingCriteria = criteria;

                result.Entity = listing;

                return(result);
            }
            catch (Exception ex)
            {
                if (result == null)
                {
                    result = new Result();
                }
                Logger.Log(ex);
                result.Entity  = null;
                result.Success = false;
                result.Message = "An error occurred" + ex.Message;
            }
            return(result);
        }
        public Result GetAllListings()
        {
            var result = new Result();

            try
            {
                var credentials = new CredentialsManager();

                var con   = new DapperConnectionManager();
                var query = new QueryEntity
                {
                    Entity = new { },
                    Query  = @"SELECT *
                          FROM JobListings
                    "
                };

                result = con.ExecuteQuery <JobListingEntity>(query);
                var listing_list = (List <JobListingEntity>)result.Entity;

                var listing_cri_man = new JobListingCriteriaManager();
                foreach (JobListingEntity listing in listing_list)
                {
                    var criteria = (List <JobListingCriteriaEntity>)listing_cri_man.GetCriteriaByListingId(listing.JobListingId).Entity;
                    listing.JobListingCriteria = criteria;
                }

                result.Entity = listing_list;
                return(result);
            }
            catch (Exception ex)
            {
                if (result == null)
                {
                    result = new Result();
                }
                Logger.Log(ex);
                result.Entity  = null;
                result.Success = false;
                result.Message = "An error occurred" + ex.Message;
            }
            return(result);
        }
        public Result GetPotentialApplicantsByListingId(int jobListingId)
        {
            var result = new Result();

            try
            {
                var con         = new DapperConnectionManager();
                var query       = new QueryEntity();
                var credentials = new CredentialsManager();

                var JLM  = new JobListingManager();
                var JLCM = new JobListingCriteriaManager();

                //Get Listing

                var listing = (JobListingEntity)JLM.GetListingById(jobListingId).Entity;
                if (listing == null)
                {
                    result.Success = false;
                    result.Message = "Listing not exist";
                    return(result);
                }


                listing.maxSalary = listing.maxSalary == 0 ? 200000 : listing.maxSalary;
                listing.minSalary = listing.minSalary == 0 ? 40000 : listing.minSalary;



                // get criteria
                var Listing_Re = JLCM.GetCriteriaByListingId(jobListingId);
                if (!Listing_Re.Success)
                {
                    result.Success = false;
                    result.Message = Listing_Re.Message;
                    return(result);
                }
                var criteria = (List <JobListingCriteriaEntity>)Listing_Re.Entity;

                // Assemble inner join query
                string        query_string   = String.Format(@"WITH SRC AS (SELECT UserId FROM Users WHERE {0} > = minsalary  AND maxsalary >=  {1} AND IsLookingForJob = 1 ) SELECT DISTINCT T0.UserId FROM SRC AS T0 ", listing.maxSalary, listing.minSalary);
                List <String> select_queries = new List <String>();
                int           counter        = 1;
                foreach (JobListingCriteriaEntity criterion in criteria)
                {
                    /*                    select_queries.Add(String.Format(" (SELECT UserId FROM NurseSelfAssessmentAnswers WHERE AspectId = {0} AND Value >= {1} ) AS T{2} ON H.UserId = T{2}.UserId "
                     *                      , criterion.AspectId, criterion.Value , counter));*/
                    //query_string += String.Format(" INNER JOIN (SELECT DISTINCT UserId FROM NurseSelfAssessmentAnswers WHERE AspectId = {0} AND Value >= {1} ) AS T{2} ON T{3}.UserId = T{2}.UserId ", criterion.AspectId, criterion.Value, counter, counter - 1);
                    query_string += String.Format("INTERSECT (SELECT UserId FROM NurseSelfAssessmentAnswers WHERE QuestionId = {0} AND Value >= {1} ) ", criterion.QuestionId, criterion.Value);

                    counter++;
                }

                query.Query = query_string;
                return(con.ExecuteQuery <int>(query));
            }
            catch (Exception ex)
            {
                if (result == null)
                {
                    result = new Result();
                }
                Logger.Log(ex);
                result.Entity  = null;
                result.Success = false;
                result.Message = ex.Message;
            }
            return(result);
        }