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);
}