public List <MailServer_m> IMAP_List()
{
string command = "SELECT * FROM ConnectionSMTP WHERE type = 'imap'";
List <MailServer_m> list = mySql.ConnectionSMTP_DataSource(command);
mySql.close();
return(list);
}
public List <Projekt_Bevont_struct> Data_SzakmaiProject()
{
string command = "SELECT coalesce((SELECT count(jelolt_id) FROM projekt_jelolt_kapcs WHERE projekt_id = projektek.id),0) as jeloltek_db, projektek.id, megnevezes_projekt, megnevezes_munka FROM projektek INNER JOIN munkakor ON munkakor.id = projektek.munkakor INNER JOIN projekt_ertesitendok_kapcs ON projektek.id = projekt_ertesitendok_kapcs.projekt_id WHERE projektek.statusz = 1 AND ertesitendok_id = " + session.UserData[0].id + "";
List <Projekt_Bevont_struct> list = mySql.getSzakmaiProject(command);
mySql.close();
return(list);
}
public void add(string comment, int project_id, int applicant_id, int teljesites)
{
DateTime dateTime = DateTime.Now;
if (teljesites >= 10)
{
teljesites = 10;
}
string command = "INSERT INTO megjegyzesek (jelolt_id,projekt_id,hr_id,hr_nev,megjegyzes,datum,ertekeles) VALUES (" + applicant_id + "," + project_id + "," + sess.UserData[0].id + ",'" + sess.UserData[0].name + "','" + comment + "','" + dateTime.ToString("yyyy. MM. dd.") + "'," + teljesites + ")";
mySql.update(command);
mySql.close();
}
public List <ertesitendok_struct> Data_Ertesitendok()
{
string command = "SELECT * FROM users WHERE kategoria = 0";
List <ertesitendok_struct> list = mySql.getErtesitendok(command);
mySql.close();
return(list);
}
public void Activity_write()
{
//List<activity_struct> list)
Model.MySql mySql = new Model.MySql();
DateTime dateTime = DateTime.Now;
string command = "INSERT INTO `activity_log` (`id`, `hr_id`, `nev`, `tipus`, `leiras`, `datum`) VALUES(NULL, 1, 'nev', 'tipus', 'leiras', '" + dateTime.ToString() + "')";
//string query = "INSERT INTO `activity_log` (`id`, `hr_id`, `nev`, `tipus`, `leiras`, `datum`) VALUES(NULL, "+list[1]+ ", '" + list[2] + "', '" + list[3] + "', '" + list[4] + "', '"+ dateTime.ToString("hu-HU") + "')";
mySql.update(command);
mySql.close();
}
public bool userValidation(string name, string pass)
{
if (mySql.rowCount("SELECT count(id) FROM users WHERE username='******'") == 1)
{
DateTime dateTime = DateTime.Now;
sqLite.update("UPDATE users SET belepve = '" + dateTime.ToString("yyyy. MM. dd.") + "' WHERE username = '******';");
mySql.close();
return(true);
}
else
{
mySql.close();
}
return(false);
}
public void telephoneFilterInsert(int ismerte, int muszakok, string utazas) //javított
{
string command = "UPDATE projekt_jelolt_kapcs SET allapota = 1 WHERE projekt_id = " + pControl.ProjektID + " AND jelolt_id = " + aControl.ApplicantID + "";
mySql.update(command);
command = "UPDATE jeloltek SET pmk_ismerte = " + ismerte + " WHERE id = " + aControl.ApplicantID + "";
mySql.update(command);
command = "INSERT INTO jelolt_statisztika (id, jelolt_id, utazas, muszakok) VALUES(null, " + aControl.ApplicantID + ", '" + utazas + "', " + muszakok + ")";
mySql.update(command);
mySql.close();
}
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);
}
public List <JeloltListItems> applicantList(List <string> searchValue) //javított
{
string command = "SELECT " +
"coalesce((SELECT count(projekt_id) FROM interjuk_kapcs WHERE jelolt_id = jeloltek.id GROUP BY jelolt_id),0) as interjuk_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,kolcsonzott,email " +
"FROM jeloltek " +
"LEFT JOIN megjegyzesek ON jeloltek.id = megjegyzesek.jelolt_id " +
"LEFT JOIN munkakor on jeloltek.munkakor = munkakor.id " +
"LEFT JOIN projekt_jelolt_kapcs ON jeloltek.id = projekt_jelolt_kapcs.jelolt_id " +
"WHERE jeloltek.id LIKE '%%'";
if (searchValue[0] != "")
{
command += " AND jeloltek.nev LIKE '%" + searchValue[0] + "%' ";
}
if (searchValue[1] != "")
{
command += " AND jeloltek.lakhely LIKE '%" + searchValue[1] + "%' ";
}
if (searchValue[2] != "")
{
command += " AND jeloltek.email LIKE '%" + searchValue[2] + "%' ";
}
if (searchValue[3] != "")
{
command += " AND jeloltek.szuldatum <= " + searchValue[3] + " ";
}
if (searchValue[4] != "" && searchValue[4] != "0")
{
command += "AND jeloltek.tapasztalat_ev >= " + searchValue[4] + " ";
}
if (searchValue[5] != "")
{
command += " AND jeloltek.reg_date LIKE '%" + searchValue[5] + "%' ";
}
if (searchValue[6] != "" && searchValue[6] != "0")
{
command += " AND coalesce((SELECT count(projekt_id) FROM interjuk_kapcs WHERE jelolt_id = jeloltek.id Group by projekt_id),0) >= " + searchValue[6] + " ";
}
if (searchValue[7] != "")
{
command += " AND jeloltek.neme LIKE '%" + searchValue[7] + "%' ";
}
if (searchValue[8] != "")
{
command += " AND jeloltek.munkakor LIKE '%" + searchValue[8] + "%' ";
}
if (searchValue[9] != "")
{
command += " AND jeloltek.vegz_terulet LIKE '%" + searchValue[9] + "%' ";
}
if (searchValue[10] != "")
{
command += " AND megjegyzesek.megjegyzes LIKE '%" + searchValue[10] + "%' ";
}
if (searchValue[11] == "1")
{
command += " AND projekt_jelolt_kapcs.id IS NULL ";
}
command += " GROUP BY jeloltek.email ";
switch (searchValue[12])
{
case "1":
command += " ORDER BY jeloltek.id" + searchValue[13];
break;
case "2":
command += " ORDER BY jeloltek.nev" + searchValue[13];
break;
case "3":
command += " ORDER BY jeloltek.munkakor" + searchValue[13];
break;
case "4":
command += " ORDER BY interjuk_db" + searchValue[13];
break;
case "5":
command += " ORDER BY jeloltek.szuldatum" + searchValue[13];
break;
case "6":
command += " ORDER BY jeloltek.reg_date" + searchValue[13];
break;
default:
command += " ORDER BY jeloltek.reg_date DESC";
break;
}
command += " LIMIT 25";
List <JeloltListItems> list = mySql.getApplicantList(command);
mySql.close();
return(list);
}
