public async Task <IEnumerable <ApplicationModel> > MissingApplicationData(string[] SourceMarkets, string sourcemarket, string[] JobFamilys, string jobfamily, string[] JobTitles, string jobtitle, string[] Jumps, string jump)
{
using (var connection = new SqlConnection(connectionString))
{
var sql2 = @"
select
s.Id,
s.StaffID,
ISNULL(NULLIF(s.FirstName,''),'No Name defined') FirstName,
ISNULL(NULLIF(s.LastName,''),'No Name defined') LastName,
ISNULL(p.Destination,'No destination') Destination,
ISNULL(NULLIF(s.Nat,''),'Nationality is missing') Nat,
s.CentralID,
s.SourceMarket,
s.DateModified,
ISNULL(NULLIF(p.JobTitle,''),'No JobTitle') JobTitle,
p.JobFamily,
s.Driver,
s.Email,
s.DateModified,
ISNULL(NULLIF(p.Season,''),'Season is missing / Not assigned') Season,
ISNULL(NULLIF(s.Status,''),'No ') Status
from
Staff s
left join PositionAssign pa on s.StaffID = pa.StaffID
left join position p
on pa.MPLID = p.MPLID";
sql2 += MultiSelectExtension.MultiSelect6(SourceMarkets, JobFamilys, JobTitles, Jumps);
sql2 += " AND NOT EXISTS(SELECT * FROM Application WHERE s.StaffID = Application.StaffID) Order by s.LastName ";
try
{
var result = await connection.QueryAsync <ApplicationModel>(sql2, new { SourceMarkets, JobFamilys, JobTitles, Jumps });
return(result);
}
catch (Exception e)
{
Console.Write(e);
return(null);
}
}
}
public async Task <IEnumerable <StaffPositionAllocations> > GetStaffData(string[] SourceMarkets, string sourcemarket, string[] JobFamilys, string jobfamily, string[] PositionTypes, string positiontype)
{
using (var connection = new SqlConnection(_connectionString))
{
var sql2 = @"
select
s.Id,
s.StaffID,
ISNULL(NULLIF(s.FirstName,''),'No Name defined') FirstName,
ISNULL(NULLIF(s.LastName,''),'No Name defined') LastName,
ISNULL(NULLIF(s.FirstName,'') + ' ' + NULLIF(s.LastName,''),'No Name defined') FirstNameLastName,
ISNULL(p.HeadOf,'Unassigned') HeadOf,
ISNULL(p.Destination,'No destination') Destination,
ISNULL(p.JobTitle, 'No title assigned') JobTitle,
ISNULL(NULLIF(s.Nat,''),'Stateless') Nat,
pa.MPLID,
pa.StartDate,
p.ConceptHotel,
p.MPLSourceMarket,
p.Season,
s.SourceMarket,
ISNULL(NULLIF(s.Email,''),'No Email') Email,
s.PositionType,
s.DateJoined,
s.Driver,
s.DrivingYear,
s.DateModified,
s.DateCreated,
s.Status
from Staff s
left join PositionAssign pa on pa.StaffID = s.StaffID
left join Position p on p.MPLID = pa.MPLID";
sql2 += MultiSelectExtension.MultiSelect3(SourceMarkets, JobFamilys, PositionTypes); //multi filter select handling in resource
sql2 += " And s.Status = 'Active' Order By FirstName";
try
{
//WHERE(s.SourceMarket = @sourcemarket OR @sourcemarket = 'ALL') AND(p.JobFamily = @jobfamily OR @jobfamily = 'ALL')
//var result = await connection.QueryAsync<StaffPositionAllocations>(sql2, new { sourcemarket, jobfamily });
var result = await connection.QueryAsync <StaffPositionAllocations>(sql2, new { SourceMarkets, JobFamilys, PositionTypes });
return(result);
}
catch (Exception e)
{
Console.Write(e);
return(null);
}
}
}
// public async Task<IEnumerable<ApplicationModel>> MissingManagerCommentsApplicationData(string[] SourceMarkets, string sourcemarket, string[] JobFamilys, string jobfamily, string[] JobTitles, string jobtitle, string[] Jumps, string jump)
// {
// using (var connection = new SqlConnection(connectionString))
// {
// var sql2 = @"
// select
// s.Id,
// s.StaffID,
//ISNULL(NULLIF(s.FirstName,''),'No Name defined') FirstName,
// ISNULL(NULLIF(s.LastName,''),'No Name defined') LastName,
// ISNULL(s.Destination,'No destination') Destination,
// ISNULL(NULLIF(s.Nat,''),'Stateless') Nat,
// s.CentralID,
// s.SourceMarket,
// s.ApplicationType,
// s.Education,
// s.DateModified,
// ISNULL(NULLIF(s.JobTitle,''),'No JobTitle') JobTitle,
// s.JobFamily,
// s.DrivingLicense,
// ISNULL(NULLIF(s.PreferToWork,''),'No PreferToWork') PreferToWork,
// s.Rating,
// s.Email,
// s.DateModified,
// s.Season,
// s.WorkPeriodStart,
// s.WorkPeriodEnd,
// ISNULL(NULLIF(s.Status,''),'No ') Status
// from Application s";
// sql2 += MultiSelectExtension.MultiSelect7(SourceMarkets, JobFamilys, JobTitles, Jumps);
// sql2 += " And (s.ManagerComments = '' or s.ManagerComments Is null) Order by s.FirstName DESC";
// // sql2 += MultiSelectExtension.MultiSelect3(SourceMarkets, JobFamilys, PositionTypes); //multi filter select handling in resource
// // sql2 += " And (s.Status = 'Leave Of Absence' Or s.Status='Resigned') Order by s.FirstName";
// // WHERE(s.SourceMarket = @sourcemarket OR @sourcemarket = 'ALL') AND (p.JobFamily = @jobfamily OR @jobfamily = 'ALL')
// try
// {
// var result = await connection.QueryAsync<ApplicationModel>(sql2, new { SourceMarkets, JobFamilys, JobTitles, Jumps });
// return result;
// }
// catch (Exception e)
// {
// Console.Write(e);
// return null;
// }
// }
// }
public async Task <IEnumerable <ApplicationModel> > PlanToResignApplicationData(string[] SourceMarkets, string sourcemarket, string[] JobFamilys, string jobfamily, string[] JobTitles, string jobtitle, string[] Jumps, string jump)
{
using (var connection = new SqlConnection(connectionString))
{
var sql2 = @"
select
s.Id,
s.StaffID,
ISNULL(NULLIF(s.FirstName,''),'No Name defined') FirstName,
ISNULL(NULLIF(s.LastName,''),'No Name defined') LastName,
ISNULL(NULLIF(s.Nat,''),'Stateless') Nat,
s.CentralID,
s.SourceMarket,
s.ApplicationType,
s.MidYearReview,
s.MidYearRating,
s.OverallRating,
staff.DateOfBirth,
staff.Education,
s.DateModified,
s.JobFamily,
staff.Driver,
ISNULL(NULLIF(s.PreferToWork,''),'No PreferToWork') PreferToWork,
s.Rating,
s.Email,
s.DateModified,
s.Season,
s.WorkPeriodStart,
s.WorkPeriodEnd,
ISNULL(NULLIF(s.Status,''),'No ') Status
from Application s join staff on staff.StaffID=s.StaffID";
sql2 += MultiSelectExtension.MultiSelect7(SourceMarkets, JobFamilys, JobTitles, Jumps);
sql2 += " And (s.Status In ('Plan To Resign')) Order by s.FirstName DESC";
try
{
var result = await connection.QueryAsync <ApplicationModel>(sql2, new { SourceMarkets, JobFamilys, JobTitles, Jumps });
return(result);
}
catch (Exception e)
{
Console.Write(e);
return(null);
}
}
}
public async Task <IEnumerable <ApplicationModel> > MissingManagerCommentsApplicationData(string[] SourceMarkets, string sourcemarket, string[] JobFamilys, string jobfamily, string[] JobTitles, string jobtitle, string[] Jumps, string jump)
{
using (var connection = new SqlConnection(connectionString))
{
var baseSelect = @"s.Id,
s.StaffID,
ISNULL(NULLIF(s.FirstName,''),'No Name defined') FirstName,
ISNULL(NULLIF(s.LastName,''),'No Name defined') LastName,
ISNULL(NULLIF(s.Nat,''),'Stateless') Nat,
s.CentralID,
staff.SourceMarket,
s.ApplicationType,
s.MidYearReview,
s.MidYearRating,
s.OverallRating,
staff.DateOfBirth,
staff.Education,
s.DateModified,
p.JobFamily,
staff.Driver,
ISNULL(NULLIF(s.PreferToWork,''),'No PreferToWork defined') PreferToWork,
s.Rating,
s.Email,
s.DateModified,
s.Season,
s.WorkPeriodStart,
s.WorkPeriodEnd,
ISNULL(NULLIF(s.Status,''),'No ') Status
from Application s join staff on staff.StaffID=s.StaffID
inner join PositionAssign pa on pa.StaffID = s.StaffID
inner join Position p on pa.MPLID = p.MPLID
Where s.PlanToResign Is NULL And s.Status Not In ('Plan To Resign')";
string sql2 = "";
sql2 += MultiSelectExtension.MultiSelect5(SourceMarkets, JobFamilys, JobTitles, Jumps);
var first = $"select ISNULL(NULLIF(s.FirstJobTitle,''),'No JobTitle') JobTitle, ISNULL(s.FirstDest,'No destination') Destination, {baseSelect} {sql2} ";
string sql3 = "";
sql3 += MultiSelectExtension.MultiSelect5_2(SourceMarkets, JobFamilys, JobTitles, Jumps);
var second = $"select ISNULL(NULLIF(s.SecondJobTitle,''),'No JobTitle') JobTitle, ISNULL(s.SecondDest,'No destination') Destination, {baseSelect} {sql3} ";
string sql4 = "";
sql4 += MultiSelectExtension.MultiSelect5_3(SourceMarkets, JobFamilys, JobTitles, Jumps);
var third = $"select ISNULL(NULLIF(s.ThirdJobTitle,''),'No JobTitle') JobTitle, ISNULL(s.ThirdDest,'No destination') Destination, {baseSelect} {sql4} ";
string sql5 = "";
sql5 += MultiSelectExtension.MultiSelect5_4(SourceMarkets, JobFamilys, JobTitles, Jumps);
var fourth = $"select ISNULL(NULLIF(s.FourthJobTitle,''),'No JobTitle') JobTitle, ISNULL(s.FourthDest,'No destination') Destination, {baseSelect} {sql5} ";
//var third = $"select ISNULL(NULLIF(s.ThirdJobTitle,''),'No JobTitle') JobTitle, ISNULL(s.ThirdDest,'No destination') Destination, {baseSelect} ";
//var fourth = $"select ISNULL(NULLIF(s.FourthJobTitle,''),'No JobTitle') JobTitle, ISNULL(s.FourthDest,'No destination') Destination, {baseSelect} ";
var sql = $"{first} union all {second} union all {third} union all {fourth}";
//var sql = $"{first} ";
// sql += MultiSelectExtension.MultiSelect5(SourceMarkets, JobFamilys, JobTitles, Jumps);
//var first = $"select ISNULL(NULLIF(s.FirstJobTitle,''),'No JobTitle') JobTitle, ISNULL(s.FirstDest,'No destination') Destination, {baseSelect} and s.FirstDest is not null and s.FirstDest is null and s.FirstDest != '' and s.FirstJobTitle is not null and s.FirstJobTitle != '' ";
//var second = $"select ISNULL(NULLIF(s.SecondJobTitle,''),'No JobTitle') JobTitle, ISNULL(s.SecondDest,'No destination') Destination, {baseSelect} and s.SecondDest is not null and s.SecondDest is null and s.SecondDest != '' and s.SecondJobTitle is not null and s.SecondJobTitle != ''";
//var third = $"select ISNULL(NULLIF(s.ThirdJobTitle,''),'No JobTitle') JobTitle, ISNULL(s.ThirdDest,'No destination') Destination, {baseSelect} and s.ThirdDest is not null and s.ThirdDest is null ) and s.ThirdDest != '' and s.ThirdJobTitle is not null and s.ThirdJobTitle != ''";
//var fourth = $"select ISNULL(NULLIF(s.FourthJobTitle,''),'No JobTitle') JobTitle, ISNULL(s.FourthDest,'No destination') Destination, {baseSelect} and s.FourthDest is not null and s.FourthDest is null and s.FourthDest != '' and s.FourthJobTitle is not null and s.FourthJobTitle != ''";
//sql += "Order by s.FirstName DESC";
try
{
var result = await connection.QueryAsync <ApplicationModel>(sql, new { SourceMarkets, JobFamilys, JobTitles, Jumps });
return(result);
}
catch (Exception e)
{
Console.Write(e);
return(null);
}
}
}
