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