public List <ModelApplicantList> Data_JeloltKapcs()
{
string command = "SELECT coalesce((SELECT count(projekt_id) FROM interview " +
"WHERE jelolt_id = jeloltek.id AND projekt_id = " + Session.ProjektID + " Group by projekt_id),0) as interjuk_db, " +
"coalesce((SELECT count(projekt_id) FROM projekt_jelolt_kapcs WHERE projekt_jelolt_kapcs.jelolt_id = jeloltek.id),0) as project_db, " +
"jeloltek.id,nev,jeloltek.szuldatum,megnevezes_munka,email,reg_date,kepesseg1,kepesseg2,kepesseg3,kepesseg4,kepesseg5, " +
"jeloltek.munkakor, jeloltek.munkakor2, jeloltek.munkakor3, allapota,jeloltek.statusz, jeloltek.friss, jeloltek.kategoria, jeloltek.megjegyzes, (SELECT EXISTS(SELECT * FROM projekt_jelolt_kapcs WHERE projekt_jelolt_kapcs.jelolt_id = jeloltek.id)) as allasban " +
"FROM jeloltek INNER JOIN projekt_jelolt_kapcs ON jeloltek.id = projekt_jelolt_kapcs.jelolt_id " +
"LEFT JOIN projektek ON projektek.id = projekt_jelolt_kapcs.projekt_id " +
"LEFT JOIN munkakor ON jeloltek.munkakor = munkakor.id WHERE projektek.id =" + Session.ProjektID + " GROUP BY jeloltek.id ";
return(ModelApplicantList.GetModelApplicantList(command));
}
public static List <ModelApplicantList> Data_FavoriteApplicants()
{
string command = "SELECT coalesce((SELECT count(projekt_id) FROM interview " +
"WHERE jelolt_id = jeloltek.id Group by projekt_id),0) as interjuk_db, " +
"coalesce((SELECT count(projekt_id) FROM projekt_jelolt_kapcs WHERE projekt_jelolt_kapcs.jelolt_id = jeloltek.id),0) as project_db, " +
"jeloltek.id,nev,jeloltek.szuldatum,megnevezes_munka,email,reg_date,kepesseg1,kepesseg2,kepesseg3,kepesseg4,kepesseg5, " +
"jeloltek.munkakor, jeloltek.munkakor2, jeloltek.munkakor3, allapota,kategoria,jeloltek.statusz, jeloltek.friss, jeloltek.profession_type, jeloltek.megjegyzes, (SELECT EXISTS(SELECT * FROM projekt_jelolt_kapcs WHERE projekt_jelolt_kapcs.jelolt_id = jeloltek.id)) as allasban " +
"FROM jeloltek LEFT JOIN projekt_jelolt_kapcs ON jeloltek.id = projekt_jelolt_kapcs.jelolt_id " +
"LEFT JOIN projektek ON projektek.id = projekt_jelolt_kapcs.projekt_id " +
"LEFT JOIN munkakor ON jeloltek.munkakor = munkakor.id " +
"LEFT JOIN jelolt_megfigyelt ON jeloltek.id = jelolt_megfigyelt.jelolt_id WHERE jelolt_megfigyelt.user_id = " + Session.UserData.id + " GROUP BY jeloltek.id ORDER BY jelolt_megfigyelt.date";
List <ModelApplicantList> list = ModelApplicantList.GetModelApplicantList(command);
return(list);
}
public static List <ModelApplicantList> GetApplicantList(ModelApplicantSearchBar sw)
{
double listNo = (sw.numberLimit != 0 ? sw.numberLimit : 10);
string command = "SELECT coalesce((SELECT count(projekt_id) FROM interview WHERE jelolt_id = jeloltek.id GROUP BY jelolt_id),0) as interjuk_db, " +
"coalesce((SELECT count(projekt_id) FROM projekt_jelolt_kapcs WHERE projekt_jelolt_kapcs.jelolt_id = jeloltek.id),0) as project_db, " +
"(SELECT megnevezes_munka FROM munkakor WHERE munkakor.id = jeloltek.munkakor) as munkakor, " +
"(SELECT megnevezes_munka FROM munkakor WHERE munkakor.id = jeloltek.munkakor2) as munkakor2, " +
"(SELECT megnevezes_munka FROM munkakor WHERE munkakor.id = jeloltek.munkakor3) as munkakor3, " +
"jeloltek.id,jeloltek.nev,szuldatum,reg_date,allapota,jeloltek.statusz,jeloltek.megjegyzes,email,friss,kategoria, " +
"(SELECT EXISTS(SELECT * FROM projekt_jelolt_kapcs WHERE projekt_jelolt_kapcs.jelolt_id = jeloltek.id)) as allasban " +
"FROM jeloltek " +
"LEFT JOIN megjegyzesek ON jeloltek.id = megjegyzesek.jelolt_id " +
"LEFT JOIN munkakor on jeloltek.munkakor = munkakor.id " +
"LEFT JOIN jelolt_cimke_kapcs on jelolt_cimke_kapcs.jelolt_id = jeloltek.id " +
"LEFT JOIN jelolt_cimkek on jelolt_cimkek.id = jelolt_cimke_kapcs.cimke_id " +
"LEFT JOIN projekt_jelolt_kapcs ON jeloltek.id = projekt_jelolt_kapcs.jelolt_id " +
"WHERE jeloltek.id LIKE '%%' AND jeloltek.ervenyes = 1 AND jeloltek.statusz =" + Session.ApplicantStatusz;
if (sw.nev != "")
{
command += " AND jeloltek.nev LIKE '%" + sw.nev + "%' ";
}
if (sw.lakhely != "")
{
command += " AND jeloltek.lakhely LIKE '%" + sw.lakhely + "%' ";
}
if (sw.email != "")
{
command += " AND jeloltek.email LIKE '%" + sw.email + "%' ";
}
if (sw.eletkor != "")
{
command += " AND jeloltek.szuldatum <= " + sw.eletkor + " ";
}
//if (searchValue[0].tapasztalat != "" && searchValue[0].tapasztalat != "0")
//{
// command += "AND jeloltek.tapasztalat_ev >= " + searchValue[0].tapasztalat + " ";
//}
if (sw.regdate != "")
{
command += " AND jeloltek.reg_date LIKE '%" + sw.regdate + "%' ";
}
if (sw.interjuk != "" && sw.interjuk != "0")
{
command += " AND coalesce((SELECT count(projekt_id) FROM interview WHERE jelolt_id = jeloltek.id Group by projekt_id),0) >= " + sw.interjuk + " ";
}
if (sw.nemekStr != "")
{
command += " AND jeloltek.neme LIKE '%" + sw.nemekStr + "%' ";
}
if (sw.munkakorStr != "")
{
command += " AND jeloltek.munkakor LIKE '%" + sw.munkakorStr + "%' ";
}
if (sw.vegzettsegStr != "")
{
command += " AND jeloltek.vegz_terulet LIKE '%" + sw.vegzettsegStr + "%' ";
}
if (sw.cimke != "")
{
command += " AND jelolt_cimkek.cimke_megnevezes LIKE '%" + sw.cimke + "%' ";
}
if (sw.cimke != "")
{
command += " OR megjegyzesek.megjegyzes LIKE '%" + sw.cimke + "%' ";
}
if (sw.szabad == "1")
{
command += " AND projekt_jelolt_kapcs.id IS NULL ";
}
if (sw.allasbanBool)
{
command += " AND projekt_jelolt_kapcs.id IS NOT NULL ";
}
command += " GROUP BY jeloltek.id ";
switch (sw.HeaderSelected)
{
case "1":
command += " ORDER BY jeloltek.nev" + sw.sorrend;
break;
case "2":
command += " ORDER BY jeloltek.statusz" + sw.sorrend;
break;
case "3":
command += " ORDER BY jeloltek.reg_date" + sw.sorrend;
break;
default:
command += " ORDER BY jeloltek.reg_date DESC, friss DESC";
break;
}
command += " LIMIT " + listNo + " OFFSET " + Session.ApplicantSearchPage * listNo + "";
return(ModelApplicantList.GetModelApplicantList(command));
}