//get recruiter details for jobemails subjects
public StringBuilder subjectdirectappconfirm(int jobid)
{
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
StringBuilder sbr = new StringBuilder();
using (connreader)
{
MySqlCommand command = new MySqlCommand(
"SELECT * from getsubjectemailapps where idjobs = " + jobid + "; ",
connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
sbr.Append(reader.GetString(1));
sbr.Append(" / ");
sbr.Append(reader.GetString(2));
}
}
else
{
//ct = 0;
}
reader.Close();
}
return(sbr);
}
//activate accounts for users and recruitersm
public void activate_acc(int uusertype, string uusername, string keytopass)
{
//define vars here
//int rEmailtype = 0;
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "UPDATE USERS SET uActivation = null where uUsername = @username and uusertype = @usertype and uactivation = @keytopas";
com.Parameters.Add("@usertype", MySqlDbType.Int16).Value = uusertype;
com.Parameters.Add("@username", MySqlDbType.VarChar).Value = uusername;
com.Parameters.Add("@keytopas", MySqlDbType.VarChar).Value = keytopass;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
public void addspamrec(int spamid, string spamreason, string remaddr, string usragnt, int pageid)
{
//
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "INSERT INTO tb_spamreport(spamindex, spamreason, dtentered, ipuser, useragents, jobid) values ( @spamid, @spamreason, @spamdates, @ipusr, @useragents, @pageid);";
com.Parameters.Add("@spamid", MySqlDbType.Int32).Value = spamid;
com.Parameters.Add("@spamreason", MySqlDbType.VarChar).Value = spamreason;
com.Parameters.Add("@spamdates", MySqlDbType.DateTime).Value = DateTime.Now;
com.Parameters.Add("@ipusr", MySqlDbType.VarChar).Value = remaddr;
com.Parameters.Add("@useragents", MySqlDbType.VarChar).Value = usragnt;
com.Parameters.Add("@pageid", MySqlDbType.Int32).Value = pageid;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
//add job rec assignments
public void insertjobmapping(int idjobs, int catid, int litermid, int empid)
{
//
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "INSERT INTO jobtable(idjobs, catid, termid, empid) values (@idjobs ,@catid , @litermid ,@empid );";
com.Parameters.Add("@idjobs", MySqlDbType.Int32).Value = idjobs;
com.Parameters.Add("@catid", MySqlDbType.Int32).Value = catid;
com.Parameters.Add("@litermid", MySqlDbType.Int32).Value = litermid;
com.Parameters.Add("@empid", MySqlDbType.Int32).Value = empid;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
//get details page
public string getdetailspagecats(int jobid, int cats)
{
//store rec details
StringBuilder sbr = new StringBuilder();
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("select sterm from getdetailspages where idJobs= " + jobid + " and catid = " + cats + " ;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
sbr.Append(reader.GetString(0));
sbr.Append("<br>");
}
}
else
{
//return null;
}
reader.Close();
}
return(sbr.ToString());
}
//add jobs
public void insertjobs(int idjobs, string sTitle, string sShortDescription, string sDescription, string ssalarytext, int ssalarymin, int ssalarymax, string sref, string startdate, string enddate)
{
//myquerystring = "INSERT INTO jobs values ('" + idjobs + "','" + sTitle + "','" + sShortDescription + "' ,'" + sDescription + "', '" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "' ,'" + ssalarytext + "' ,'" + ssalarymin + "' ,'" + ssalarymax + "' ,'" + sref + "', '" + sTitle + "', '" + Convert.ToDateTime(startdate).ToString("yyyy:MM:dd hh:mm:ss") + "', '" + Convert.ToDateTime(enddate).ToString("yyyy:MM:dd hh:mm:ss") + "' )";
//
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "INSERT INTO jobs values (@idjobs ,@sTitle ,@sShortDescription ,@sDescription ,@dtentered ,@ssalarytext ,@ssalarymin ,@ssalarymax ,@sref ,@sTitle , @startdate , @enddate);";
com.Parameters.Add("@idjobs", MySqlDbType.Int32).Value = idjobs;
com.Parameters.Add("@sTitle", MySqlDbType.VarChar).Value = sTitle;
com.Parameters.Add("@sShortDescription", MySqlDbType.VarChar).Value = sShortDescription;
com.Parameters.Add("@sDescription", MySqlDbType.Text).Value = sDescription;
com.Parameters.Add("@dtentered", MySqlDbType.DateTime).Value = DateTime.Now;
com.Parameters.Add("@ssalarytext", MySqlDbType.VarChar).Value = ssalarytext;
com.Parameters.Add("@ssalarymin", MySqlDbType.Int16).Value = ssalarymin;
com.Parameters.Add("@ssalarymax", MySqlDbType.Int16).Value = ssalarymax;
com.Parameters.Add("@sref", MySqlDbType.VarChar).Value = sref;
com.Parameters.Add("@startdate", MySqlDbType.Date).Value = startdate;
com.Parameters.Add("@enddate", MySqlDbType.Date).Value = enddate;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
//edit jobs
public void updatejobs(int idjobs, string sTitle, string sShortDescription, string sDescription, string ssalarytext, int ssalarymin, int ssalarymax, string sref, string sdate, string edate)
{
//
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "UPDATE jobs SET sTitle=@sTitle , sShortDescription = @sShortDescription ,sDescription = @sDescription , dtentereddate= @curdates , ssalarytext = @ssalarytext ,sminsal = @ssalarymin , smaxsal = @ssalarymax , sref = @sref , sfreetext= @sTitle , sjobstartdate = @sdate , sjobenddate= @edate where idjobs = @idjob;";
com.Parameters.Add("@sTitle", MySqlDbType.VarChar).Value = sTitle;
com.Parameters.Add("@sShortDescription", MySqlDbType.VarChar).Value = sShortDescription;
com.Parameters.Add("@sDescription", MySqlDbType.Text).Value = sDescription;
com.Parameters.Add("@curdates", MySqlDbType.DateTime).Value = DateTime.Now;
com.Parameters.Add("@ssalarytext", MySqlDbType.VarChar).Value = ssalarytext;
com.Parameters.Add("@ssalarymin", MySqlDbType.Int32).Value = ssalarymin;
com.Parameters.Add("@ssalarymax", MySqlDbType.Int32).Value = ssalarymax;
com.Parameters.Add("@sref", MySqlDbType.VarChar).Value = sref;
com.Parameters.Add("@sdate", MySqlDbType.Date).Value = sdate;
com.Parameters.Add("@edate", MySqlDbType.Date).Value = edate;
com.Parameters.Add("@idjob", MySqlDbType.Int32).Value = idjobs;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
//get get application details for JS
public string[] getapplicationdetails(int applyid)
{
//store rec details
string[] tempst = new string[2];
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("select idapplications, aprofilesummary, completeurl from getapp where idapplications = " + applyid + " limit 1;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
tempst[0] = reader.GetString(1);
tempst[1] = reader.GetString(2);
}
}
else
{
return(null);
}
reader.Close();
}
return(tempst);
}
//get advertizing rec count
public int getcountrecswadvert()
{
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
int ct = 0;
using (connreader)
{
MySqlCommand command = new MySqlCommand(
"SELECT count(empid) FROM getallrec;",
connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ct = reader.GetInt32(0);
}
}
else
{
//ct = 0;
}
reader.Close();
}
return(ct);
}
//add users
public void insertusers(string rusername, string fname, int uisprimary, string lname, string rpassword, int rtype, int idUsers, string pwdhint, int ucandidateid, string uhash)
{
//
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "INSERT INTO users(idusers, uusertype, uusername, upassword, upasswordhint, uFirstName, uLastName, uIsPrimary, uCandidateID, uActivation) values (@idUsers ,@rtype ,@rusername ,@rpassword ,@pwdhint ,@fname ,@lname ,1 , @ucandidateid, @uhash);";
com.Parameters.Add("@idUsers", MySqlDbType.Int32).Value = idUsers;
com.Parameters.Add("@rtype", MySqlDbType.Int16).Value = rtype;
com.Parameters.Add("@rusername", MySqlDbType.VarChar).Value = rusername;
com.Parameters.Add("@rpassword", MySqlDbType.VarChar).Value = rpassword;
com.Parameters.Add("@pwdhint", MySqlDbType.VarChar).Value = pwdhint;
com.Parameters.Add("@fname", MySqlDbType.VarChar).Value = fname;
com.Parameters.Add("@lname", MySqlDbType.VarChar).Value = lname;
com.Parameters.Add("@ucandidateid", MySqlDbType.Int32).Value = ucandidateid;
com.Parameters.Add("@uhash", MySqlDbType.VarChar).Value = uhash;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
//max candidate id
public int maxcandidateid()
{
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
int ct = 0;
using (connreader)
{
MySqlCommand command = new MySqlCommand("SELECT max(idCandidates)+1 FROM candidates;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ct = reader.GetInt32(0);
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
}
return(ct);
}
//get contact person details page
public string contactperson(string jobid)
{
//store rec details
string arrayrec = string.Empty;
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("SELECT concat_ws(' ',uFirstname,' ',uLastName) from recusers where idjobs ='" + jobid + "' limit 1;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
arrayrec = reader.GetString(0);
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
}
return(arrayrec);
}
//jobseeker password or admin 2
public void chgpwdjswkey(string keyval, string pwds)
{
//recalc password hash
CLPwdhash clpdh = new CLPwdhash();
string hashedpwd = clpdh.getMd5Hash(pwds);
//
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "UPDATE users set uPassword = '******' where uPasswordresetcode= '" + keyval + "' and uUserType = 2; ";
com.Parameters.Add("@keyval", MySqlDbType.VarChar).Value = keyval;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
//get max app id
public int getmaxappid()
{
//store rec details
int maxval = 0;
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("select max(idapplications)+1 from applications limit 1;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
maxval = reader.GetInt32(0);
}
}
reader.Close();
}
return(maxval);
}
//get article id to update the logo
public int getarticleids(string uusername)
{
int arrayrec = 0;
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("SELECT sarticleid from recruiters rec, recusermapper rmap, users usr where rec.empid = rmap.empid and rmap.idusers = usr.idusers and usr.uusername ='******' limit 1;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
arrayrec = reader.GetInt32(0);
}
}
else
{
//return 0;
}
reader.Close();
}
return(arrayrec);
}
//get recruiters logo for profiles form
public string getrecformimage(int recsids)
{
//store rec details
string arrayrec = string.Empty;
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("SELECT replace(article_data,'c:/testdir/','/articles/')as Article_data from recruiters,Articles where recruiters.sArticleID=Articles.sArticleID and recruiters.empid = " + recsids + " limit 1; ", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
arrayrec = reader.GetString(0);
}
}
else
{
return(null);
}
reader.Close();
}
return(arrayrec);
}
public void runcandidateupdate(string cfirstname, string clastname, string address1, string address2, string address3, string town, string county, string country, string dateofbirth, string postcode, string hometel, string worktel, string uusername)
{
//
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "update users usr, candidates can SET can.cCandidateName= @candidaten , can.cFirstName=@cfirstname, can.cLastName =@clastname, can.cAddress1=@cAddress1, can.cAddress2=@cAddress2, can.cAddress3=@cAddress3, can.cTown = @cTown, can.cCounty=@cCounty, can.cCountry=@cCountry, can.cPostcode=@cPostcode, can.cDateofBirth=@dateofbirth ,can.cHomephone=@hometel ,can.cWorkphone=@worktel where can.idcandidates = usr.ucandidateid and usr.uusertype=2 and usr.uusername=@uusername ;";
com.Parameters.Add("@cfirstname", MySqlDbType.VarChar).Value = cfirstname;
com.Parameters.Add("@clastname", MySqlDbType.VarChar).Value = clastname;
com.Parameters.Add("@cAddress1", MySqlDbType.VarChar).Value = address1;
com.Parameters.Add("@cAddress2", MySqlDbType.VarChar).Value = address2;
com.Parameters.Add("@cAddress3", MySqlDbType.VarChar).Value = address3;
com.Parameters.Add("@cTown", MySqlDbType.VarChar).Value = town;
com.Parameters.Add("@cCounty", MySqlDbType.VarChar).Value = county;
com.Parameters.Add("@cCountry", MySqlDbType.VarChar).Value = country;
com.Parameters.Add("@cpostcode", MySqlDbType.VarChar).Value = postcode;
com.Parameters.Add("@dateofbirth", MySqlDbType.Date).Value = dateofbirth;
com.Parameters.Add("@hometel", MySqlDbType.VarChar).Value = hometel;
com.Parameters.Add("@worktel", MySqlDbType.VarChar).Value = worktel;
com.Parameters.Add("@uusername", MySqlDbType.VarChar).Value = uusername;
com.Parameters.Add("@candidaten", MySqlDbType.VarChar).Value = cfirstname + " " + clastname;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
//get recid for blocking
public int getrecid(string recname)
{
//store rec details
int arrayrec = 0;
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("SELECT empid from recruiters where sRecruiterName ='" + recname + "' limit 1;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
arrayrec = reader.GetInt32(0);
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
}
return(arrayrec);
}
//update recruiter table information
public void runrectableupdate(string add1, string add2, string add3, string town, string county, string postcode, string compname, string compwebsite, string companyintro, string businessdetail, string uusername)
{
//
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "update recruiters rec, users usr, recusermapper rmap set rec.sRecruiterName =@compname , rec.sAddress1= @add1 , rec.sAddress2= @add2 , rec.sAddress3= @add3 , rec.sTown= @town , rec.sCounty= @county , rec.sPostcode= @postcode ,rec.sWebsite= @compwebsite ,rec.sDescription= @companyintro ,rec.sCompletedesc= @businessdetail where rec.empid = rmap.empid and rmap.idusers = usr.idusers and usr.uusername = @uusername and usr.uUserType = 1;";
com.Parameters.Add("@compname", MySqlDbType.VarChar).Value = compname;
com.Parameters.Add("@add1", MySqlDbType.VarChar).Value = add1;
com.Parameters.Add("@add2", MySqlDbType.VarChar).Value = add2;
com.Parameters.Add("@add3", MySqlDbType.VarChar).Value = add3;
com.Parameters.Add("@town", MySqlDbType.VarChar).Value = town;
com.Parameters.Add("@county", MySqlDbType.VarChar).Value = county;
com.Parameters.Add("@postcode", MySqlDbType.VarChar).Value = postcode;
com.Parameters.Add("@compwebsite", MySqlDbType.VarChar).Value = compwebsite;
com.Parameters.Add("@companyintro", MySqlDbType.VarChar).Value = companyintro;
com.Parameters.Add("@businessdetail", MySqlDbType.VarChar).Value = businessdetail;
com.Parameters.Add("@uusername", MySqlDbType.VarChar).Value = uusername;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
//get recruiterid by name
public int RecName(string usrname)
{
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
int ct = 0;
using (connreader)
{
MySqlCommand command = new MySqlCommand("select rec.EmpID from recruiters rec, recusermapper rmap, users usr where rec.EmpID = rmap.EmpID and rmap.idUsers = usr.idUsers and usr.uUsername = '******' limit 1;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ct = reader.GetInt32(0);
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
}
return(ct);
}
//aggregate block
/////////////////////////////////////////////
//get job count
public int getcountjobs()
{
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
int ct = 0;
using (connreader)
{
MySqlCommand command = new MySqlCommand(
"SELECT count(idjobs) FROM jobs where sjobenddate>= curdate();",
connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ct = reader.GetInt32(0);
}
}
else
{
//ct = 0;
}
reader.Close();
}
return(ct);
}
//register a new template
public void registeremailtemplate(string ebody, int etemplatechkid, string etemplatename, string ehead, string efoot)
{
//define vars here
//int rEmailtype = 0;
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "INSERT INTO emailtemplates(eTemplateName, edescription, eTemplatechkid, eHeader, eFooter) values (@templatename, @eb1, @eb2, @ehead, @efoot)";
com.Parameters.Add("@eb1", MySqlDbType.Text).Value = ebody;
com.Parameters.Add("@eb2", MySqlDbType.Int16).Value = etemplatechkid;
com.Parameters.Add("@templatename", MySqlDbType.VarChar).Value = etemplatename;
com.Parameters.Add("@ehead", MySqlDbType.Text).Value = ehead;
com.Parameters.Add("@efoot", MySqlDbType.Text).Value = efoot;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
public void AddApplicationcan(int licandidateid, string firstname, string lastname, string dateofbirth, string profilesummary, int mxdocid, string aAppEmail)
{
//
using (MySqlConnection con = new MySqlConnection())
{
//Clconnect kenect = new Clconnect();
con.ConnectionString = Clconnect.makeconn();
con.Open();
using (MySqlCommand com = con.CreateCommand())
{
com.CommandType = System.Data.CommandType.Text;
com.CommandText = "INSERT INTO Applications(licandidateid, dtEntered, aFirstName, aLastName, aDateofbirth, aProfileSummary, documentID, aApplicationEmail) values (@licandidateid, @tdate, @firstname , @lastname , @dateofbirth, @profilesummary , @mxdocid , @aAppEmail);";
com.Parameters.Add("@tdate", MySqlDbType.DateTime).Value = DateTime.Now;
com.Parameters.Add("@firstname", MySqlDbType.VarChar).Value = firstname;
com.Parameters.Add("@lastname", MySqlDbType.VarChar).Value = lastname;
com.Parameters.Add("@dateofbirth", MySqlDbType.Date).Value = dateofbirth;
com.Parameters.Add("@profilesummary", MySqlDbType.VarChar).Value = profilesummary;
com.Parameters.Add("@mxdocid", MySqlDbType.Int32).Value = mxdocid;
com.Parameters.Add("@aAppEmail", MySqlDbType.VarChar).Value = aAppEmail;
com.Parameters.Add("@licandidateid", MySqlDbType.Int32).Value = licandidateid;
int reslt = com.ExecuteNonQuery();
}
con.Close();
}
}
//get details for salaries
//get details page
public string getdetailspagecats(int jobid, int cats, int jobsal)
{
//store rec details
StringBuilder sbr = new StringBuilder();
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("select minrange,maxrange from getdetailspages where idJobs= " + jobid + " and catid = " + cats + " order by minrange;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
int i = 0;
string j = string.Empty;
string k = string.Empty;
if (reader.HasRows)
{
while (reader.Read())
{
if (i == 0)
{
k = reader.GetString(0);
}
j = reader.GetString(1);
i++;
}
}
else
{
//Console.WriteLine("No rows found.");
}
//check which value is greater
sbr.Append(k);
sbr.Append(" - ");
sbr.Append(j);
reader.Close();
}
return(sbr.ToString());
}
//getrecruiterdetails
public string[] RecDetails(string usrname)
{
//store rec details
string[] arrayrec = new string[16];
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("select rec.*,usr.* from recruiters rec, recusermapper rmap, users usr where rec.EmpID = rmap.EmpID and rmap.idUsers = usr.idUsers and usr.uUsername = '******' limit 1;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
arrayrec[0] = reader.GetString("uFirstName");
arrayrec[1] = reader.GetString("uLastName");
arrayrec[2] = reader.GetString("sRecruiterName");
arrayrec[3] = reader.GetString("sAddress1");
arrayrec[4] = reader.GetString("sAddress2");
arrayrec[5] = reader.GetString("sAddress3");
arrayrec[6] = reader.GetString("sTown");
arrayrec[7] = reader.GetString("sCounty");
arrayrec[8] = reader.GetString("sCountry");
arrayrec[9] = reader.GetString("sPostcode");
arrayrec[10] = "XXXXXXXXXX";
arrayrec[11] = reader.GetString("uPasswordHint");
arrayrec[12] = reader.GetString("sEmailAddress");
arrayrec[13] = reader.GetString("sWebsite");
arrayrec[14] = reader.GetString("sdescription");
arrayrec[15] = reader.GetString("scompletedesc");
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
}
return(arrayrec);
}
//user to get direct applications for candidates who have registered as can.
public string[] getcandidatesindb(string uusername)
{
//fname, lname, dob, email
//store rec details
string[] arrayrec = new string[5];
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("select usr.uusername, can.cfirstname, can.clastname, can.cdateofbirth, can.idCandidates from candidates can, users usr where can.idcandidates = usr.ucandidateid and usr.uUserName = '******' limit 1;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
try
{
arrayrec[0] = reader.GetString(0);
arrayrec[1] = reader.GetString(1);
arrayrec[2] = reader.GetString(2);
arrayrec[3] = reader.GetString(3);
arrayrec[4] = reader.GetString(4);
}
catch
{
return(null);
}
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
}
return(arrayrec);
}
//get candidate detail page
public string[] getcandidatedetails(string susername)
{
//store rec details
string[] arrayrec = new string[12];
MySqlConnection connreader = new MySqlConnection();
//Clconnect kenect = new Clconnect();
connreader.ConnectionString = Clconnect.makeconn();
using (connreader)
{
MySqlCommand command = new MySqlCommand("SELECT cfirstname, clastname, caddress1, caddress2, caddress3, ctown, ccounty, ccountry, cpostcode, chomephone, cworkphone , uusername, cdateofbirth FROM getusercans where uusername = '******' limit 1;", connreader);
connreader.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
arrayrec[0] = reader.GetString("cfirstname");
arrayrec[1] = reader.GetString("clastname");
arrayrec[2] = reader.GetString("caddress1");
arrayrec[3] = reader.GetString("caddress2");
arrayrec[4] = reader.GetString("caddress3");
arrayrec[5] = reader.GetString("ctown");
arrayrec[6] = reader.GetString("ccounty");
arrayrec[7] = reader.GetString("ccountry");
arrayrec[8] = reader.GetString("cpostcode");
arrayrec[9] = reader.GetString("chomephone");
arrayrec[10] = reader.GetString("cworkphone");
arrayrec[11] = reader.GetString("cdateofbirth");
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
}
return(arrayrec);
}
public DataSet getJobviewdata(int sEmpID)
{
DataSet Ds = new DataSet();
//Clconnect kenect = new Clconnect();
string myconstring = Clconnect.makeconn();
MySqlConnection mycon = new MySqlConnection();
mycon.ConnectionString = myconstring;
MySqlDataAdapter myda = new MySqlDataAdapter("select count(empid) as jobviews, dtentered as dateviewed from jobviews group by empid, dtentered having empid = '" + sEmpID + "';", mycon);
mycon.Open();
myda.Fill(Ds, "jobviews");
mycon.Close();
return(Ds);
}
public DataSet getpjjobviewdata(int sEmpID)
{
DataSet Ds = new DataSet();
//Clconnect kenect = new Clconnect();
string myconstring = Clconnect.makeconn();
MySqlConnection mycon = new MySqlConnection();
mycon.ConnectionString = myconstring;
MySqlDataAdapter myda = new MySqlDataAdapter("select idjobs as jobviews, dtentered as dateviewed from jobpostedview where empid = " + sEmpID + ";", mycon);
mycon.Open();
myda.Fill(Ds, "jobpostedview");
mycon.Close();
return(Ds);
}
public void addview(int empid, string dtentered)
{
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
string myquerystring;
myquerystring = "INSERT INTO jobviews(Empid, dtentered) values (" + empid + ", '" + dtentered + "')";
//Clconnect kenect = new Clconnect();
conn.ConnectionString = Clconnect.makeconn();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = myquerystring;
cmd.ExecuteNonQuery();
conn.Close();
}
