public void LoadStaticDB()
{
Master.DB.Start(Master.Con);
{
MySqlCommand cmd = new MySqlCommand("Select * " +
"From jobs "
, Master.Con)
{
CommandType = CommandType.Text
};
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
JobsData temp = new JobsData();
temp.Id = Convert.ToInt16(reader.GetValue(reader.GetOrdinal("id")));
temp.Title = Master.Text.ToUpperFirstLetter(reader.GetString(reader.GetOrdinal("title")));
if (temp != null)
{
Jobs.Add(temp);
}
}
}
}
//stänger anslutningen såklart
Master.DB.End(Master.Con);
}
}
public void Remove(UserData user, JobsData job, bool db = false)
{
if (Exist(user, job))
{
user.MyJobs.Remove(job);
if (db)
{
Master.DB.Start(Master.Con);
{
MySqlCommand cmd = new MySqlCommand("DELETE FROM users_jobs WHERE userid = @userid" +
"AND jobid = @jobid", Master.Con);
cmd.Parameters.Add(new MySqlParameter("@userid", MySqlDbType.Int16, 11));
cmd.Parameters["@userid"].Value = user.Id;
cmd.Parameters.Add(new MySqlParameter("@jobid", MySqlDbType.Int16, 11));
cmd.Parameters["@jobid"].Value = job.Id;
// Execute Command (for Delete, Insert or Update).
cmd.ExecuteNonQuery();
//stänger anslutningen såklart
Master.DB.End(Master.Con);
}
}
}
}
public void Add(UserData user, JobsData job, bool db = false)
{
if (!Exist(user, job))
{
user.MyJobs.Add(job);
if (db)
{
Master.DB.Start(Master.Con);
{
MySqlCommand cmd = new MySqlCommand("INSERT INTO users_jobs (userid, jobid) "
+ " values (@userid, @jobid) ", Master.Con);
cmd.Parameters.Add(new MySqlParameter("@userid", MySqlDbType.Int16, 11));
cmd.Parameters["@userid"].Value = user.Id;
cmd.Parameters.Add(new MySqlParameter("@jobid", MySqlDbType.Int16, 11));
cmd.Parameters["@jobid"].Value = job.Id;
// Execute Command (for Delete, Insert or Update).
cmd.ExecuteNonQuery();
//stänger anslutningen såklart
Master.DB.End(Master.Con);
}
}
}
}
public void AddList(JobsData job)
{
if (ExistList(job))
{
Jobs.Add(job);
}
}
public void RemoveList(JobsData job)
{
if (ExistList(job))
{
Jobs.Remove(job);
}
}
public bool ExistList(JobsData job)
{
if (Jobs.Count > 0)
{
if (Jobs.Contains(job))
{
return(true);
}
}
return(false);
}
//Laddar alla jobb där någon har sökt
public List <JobsData> LoadAllDB(UserData user)
{
List <JobsData> users = new List <JobsData>();
Master.DB.Start(Master.Con);
{
MySqlCommand cmd = new MySqlCommand("SELECT * " +
"FROM users_jobs "
, Master.Con)
{
CommandType = CommandType.Text
};
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
JobsData temp = this.LoadById(Convert.ToInt16(reader.GetValue(reader.GetOrdinal("jobid"))));
//ansökarens stage
temp.Stage = Convert.ToInt16(reader.GetValue(reader.GetOrdinal("stage")));
temp.UserId = Convert.ToInt16(reader.GetValue(reader.GetOrdinal("userid")));
if (temp != null)
{
if (!users.Contains(temp))
{
users.Add(temp);
}
}
}
}
}
//stänger anslutningen såklart
Master.DB.End(Master.Con);
}
return(users);
}
//Laddar dina jobb från db, kollar sedan listan från allmänna jobs
public void LoadMyDB(UserData user)
{
Master.DB.Start(Master.Con);
{
MySqlCommand cmd = new MySqlCommand("SELECT * " +
"FROM users_jobs " +
"WHERE userid = @userid "
, Master.Con)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@userid", user.Id);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
JobsData temp = this.LoadById(Convert.ToInt16(reader.GetValue(reader.GetOrdinal("jobid"))));
//din nivå i jobbet, intervju > iqtest osv..
temp.Stage = Convert.ToInt16(reader.GetValue(reader.GetOrdinal("stage")));
if (temp != null)
{
if (!Exist(user, temp))
{
Add(user, temp);
}
}
}
}
}
//stänger anslutningen såklart
Master.DB.End(Master.Con);
}
}
public void Update(UserData user, JobsData job)
{
Master.DB.Start(Master.Con);
{
MySqlCommand cmd = new MySqlCommand("UPDATE users_jobs " +
"SET stage = @stage," +
"points = @points, " +
"time = @time," +
"vip = @vip " +
"WHERE userid = @userid " +
"AND jobid = @jobid "
, Master.Con);
cmd.Parameters.Add(new MySqlParameter("@stage", MySqlDbType.Int16, 2));
cmd.Parameters["@stage"].Value = job.Stage;
cmd.Parameters.Add(new MySqlParameter("@points", MySqlDbType.Int16, 2));
cmd.Parameters["@points"].Value = job.Points;
cmd.Parameters.Add(new MySqlParameter("@time", MySqlDbType.Int16, 11));
cmd.Parameters["@time"].Value = job.Time;
cmd.Parameters.Add(new MySqlParameter("@vip", MySqlDbType.Int16, 1));
cmd.Parameters["@vip"].Value = job.Vip;
cmd.Parameters.Add(new MySqlParameter("@userid", MySqlDbType.Int16, 11));
cmd.Parameters["@userid"].Value = job.UserId;
cmd.Parameters.Add(new MySqlParameter("@jobid", MySqlDbType.Int16, 3));
cmd.Parameters["@jobid"].Value = job.Id;
// Execute Command (for Delete, Insert or Update).
cmd.ExecuteNonQuery();
//stänger anslutningen såklart
Master.DB.End(Master.Con);
}
}
public bool Exist(UserData user, JobsData job)
{
return(user.MyJobs.Contains(job));
}
