public List <ProjectListItems> Data_ProjectFull(List <string> searchValue)
{
string command = "SELECT coalesce((SELECT count(jelolt_id) FROM projekt_jelolt_kapcs WHERE projekt_id = projektek.id GROUP BY jeloltek.id),0) as jeloltek_db, coalesce((SELECT count(jelolt_id) FROM interjuk_kapcs WHERE projekt_id = projektek.id),0) as interjuk_db, projektek.id, projektek.publikalt, megnevezes_projekt, megnevezes_munka, fel_datum, statusz FROM projektek LEFT JOIN projekt_jelolt_kapcs ON projektek.id = projekt_jelolt_kapcs.projekt_id LEFT JOIN jeloltek ON jeloltek.id = projekt_jelolt_kapcs.jelolt_id LEFT JOIN munkakor ON munkakor.id = projektek.munkakor LEFT JOIN pc ON pc.id = projektek.pc LEFT JOIN megjegyzesek ON projektek.id = megjegyzesek.projekt_id " +
" WHERE projektek.statusz = " + projectSearchMemory[0].statusz;
if (searchValue[0] != "")
{
command += " AND projektek.megnevezes_projekt LIKE '%" + searchValue[0] + "%' ";
}
if (searchValue[1] != "0")
{
command += " AND coalesce((SELECT count(projekt_id) FROM projekt_jelolt_kapcs WHERE projekt_id = projektek.id Group by projekt_id),0) >=" + searchValue[1] + " ";
}
if (searchValue[2] != "")
{
command += " AND projektek.fel_datum LIKE '%" + searchValue[2] + "%' ";
}
if (searchValue[3] != "0")
{
command += " AND coalesce((SELECT count(jelolt_id) FROM interjuk_kapcs WHERE projekt_id = projektek.id Group by jelolt_id),0) >=" + searchValue[3] + " ";
}
if (searchValue[4] != "")
{
command += " AND pc.megnevezes_pc LIKE '%" + searchValue[4] + "%' ";
}
if (searchValue[5] != "" && searchValue[5] != "1")
{
command += " AND projektek.nyelvtudas LIKE '%" + searchValue[5] + "%' ";
}
if (searchValue[6] != "" && searchValue[6] != "1")
{
command += " AND projektek.vegzettseg LIKE '%" + searchValue[6] + "%' ";
}
if (searchValue[7] != "")
{
command += " AND megjegyzesek.megjegyzes LIKE '%" + searchValue[7] + "%' ";
}
if (searchValue[8] != "")
{
command += " AND jeloltek.nev LIKE '%" + searchValue[8] + "%' ";
}
if (searchValue[9] != "")
{
command += " AND projektek.publikalt LIKE '%" + searchValue[9] + "%' ";
}
command += " GROUP BY projektek.id ";
switch (searchValue[10])
{
case "1":
command += " ORDER BY projektek.id" + searchValue[11];
break;
case "2":
command += " ORDER BY projektek.megnevezes_projekt" + searchValue[11];
break;
case "3":
command += " ORDER BY projektek.munkakor" + searchValue[11];
break;
case "4":
command += " ORDER BY jeloltek_db" + searchValue[11];
break;
case "5":
command += " ORDER BY projektek.fel_datum" + searchValue[11];
break;
default:
command += " ORDER BY projektek.fel_datum DESC";
break;
}
List <ProjectListItems> list = mySql.Projekt_MySql_listQuery(command);
mySql.close();
return(list);
}