public ActionResult Progress(string chosenJob, int chosenSector)
{
var jobSequences = new List<JobSequence>();
ViewBag.CurrentJobTitle = UppercaseWords(chosenJob);
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["JobSequenceDBContext"].ConnectionString);
var stmt = "SELECT DISTINCT nextJob, AvgSalaryNext, Job_Sector_Name , JobSectorNext, timeSameJobTitleNext, timetoNextJobNext, AvgAgeNext " +
"FROM JobSequencesFinalAll t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE [nextJob] LIKE '" + chosenJob + "' and JobSectorNext = " + chosenSector + " " +
"ORDER BY AvgSalaryNext, Job_Sector_Name";
var com = new SqlCommand(stmt, conn);
com.Connection.Open();
var rs = com.ExecuteReader();
if (rs.Read())
{
ViewBag.Sector = rs["Job_Sector_Name"].ToString();
ViewBag.AverageSalary = string.Format("{0:C0}", Convert.ToDecimal(rs["AvgSalaryNext"]));
ViewBag.TimeInJob = rs["timeSameJobTitleNext"].ToString();
ViewBag.TimeToNextJob = rs["timetoNextJobNext"].ToString();
ViewBag.AverageAge = rs["AvgAgeNext"].ToString();
}
rs.Close();
conn.Close();
stmt = "SELECT * FROM (" +
"SELECT t1.*, t2.Job_Sector_Name, '1' as Pref " +
"FROM JobSequencesFinalAll t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE currentJob LIKE '" + chosenJob + "' " +
"AND ([AvgSalaryNext] - [AvgSalaryCurrent]) > -1000 " +
"AND JobSectorCurrent = " + chosenSector + " " +
"AND RANK < 20 AND SeqFreq > 1 " +
"AND nextJob != '" + chosenJob + "'" +
"UNION " +
"SELECT top 5 t1.*, t2.Job_Sector_Name, '0' as Pref " +
"FROM JobSequencesFinalAll t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE currentJob LIKE '" + chosenJob + "' " +
"AND ([AvgSalaryNext] - [AvgSalaryCurrent]) > -1000 " +
"AND JobSectorCurrent != " + chosenSector + " " +
"AND RANK < 20 AND SeqFreq > 1 " +
"AND nextJob != '" + chosenJob + "'" +
"ORDER BY SEQFREQ desc " +
") AS T " +
"ORDER BY currentJob, Pref Desc, SeqFreq desc";
com = new SqlCommand(stmt, conn);
com.Connection.Open();
rs = com.ExecuteReader();
if (rs.Read())
{
var countConn = new SqlConnection(ConfigurationManager.ConnectionStrings["JobSequenceDBContext"].ConnectionString);
var countStmt =
"SELECT SUM(SEQFREQ) as total " +
"FROM JobSequencesFinalAll t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE currentJob LIKE '" + chosenJob + "' AND ([AvgSalaryNext] - [AvgSalaryCurrent]) > -1000 " +
"AND JobSectorCurrent = " + chosenSector + " " +
"AND RANK < 20 AND SeqFreq > 1 " +
"AND nextJob != '" + chosenJob + "'";
var countCom = new SqlCommand(countStmt, countConn);
countCom.Connection.Open();
var countTemp = countCom.ExecuteScalar();
var count = 0;
if (countTemp != DBNull.Value)
{
count = Convert.ToInt32(countTemp);
}
do
{
var js = new JobSequence(UppercaseWords(rs["nextJob"].ToString()),
Convert.ToDecimal(rs["AvgSalaryNext"]),
rs["Job_Sector_Name"].ToString(),
Convert.ToInt32(rs["JobSectorNext"]));
js.Pref = Convert.ToInt32(rs["Pref"]);
if (js.Pref == 1 && count > 0)
{
js.Probability = Convert.ToDecimal(rs["seqfreq"]) / count;
}
else
{
js.Probability = 0;
}
js.AvgLength = Convert.ToInt32(rs["timeSameJobTitleNext"]);
jobSequences.Add(js);
} while (rs.Read());
}
else
{
rs.Close();
com.Connection.Close();
stmt = "SELECT * FROM (" +
"SELECT t1.*, t2.Job_Sector_Name, '1' as Pref " +
"FROM JobSequencesFinalAll t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE currentJob LIKE '" + chosenJob + "' " +
"AND ([AvgSalaryNext] - [AvgSalaryCurrent]) > -1000 " +
"AND JobSectorCurrent = " + chosenSector + " " +
"AND RANK < 20 AND SeqFreq > 0 " +
"AND nextJob != '" + chosenJob + "'" +
"UNION " +
"SELECT t1.*, t2.Job_Sector_Name, '0' as Pref " +
"FROM JobSequencesFinalAll t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE currentJob LIKE '" + chosenJob + "' " +
"AND ([AvgSalaryNext] - [AvgSalaryCurrent]) > -1000 " +
"AND RANK < 20 AND SeqFreq > 0 " +
"AND JobSectorCurrent != " + chosenSector + " " +
"AND nextJob != '" + chosenJob + "'" +
") AS T " +
"ORDER BY currentJob, Pref Desc, SeqFreq desc";
com = new SqlCommand(stmt, conn);
com.Connection.Open();
rs = com.ExecuteReader();
while (rs.Read())
{
var js = new JobSequence(UppercaseWords(rs["nextJob"].ToString()),
Convert.ToDecimal(rs["AvgSalaryNext"]),
rs["Job_Sector_Name"].ToString(),
Convert.ToInt32(rs["JobSectorNext"]));
js.Pref = Convert.ToInt32(rs["Pref"]);
js.Probability = 0;
js.AvgLength = Convert.ToInt32(rs["timeSameJobTitleNext"]);
jobSequences.Add(js);
}
}
rs.Close();
ViewBag.JobTitle = UppercaseWords(chosenJob);
return View(jobSequences);
}
public ActionResult Progress(string chosenJob, int chosenSector)
{
var jobSequences = new List<JobSequence>();
ViewBag.CurrentJobTitle = chosenJob;
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["JobSequenceDBContext"].ConnectionString);
var stmt = "SELECT * FROM (" +
"SELECT t1.*, t2.Job_Sector_Name, '1' as Pref " +
"FROM JobSequencesAllInfowithJobSectors t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE currentJob LIKE '" + chosenJob + "' " +
"AND ([salaryNext] - [salaryCurrent]) > -1000 " +
"AND JobSectorCurrent = " + chosenSector + " " +
"AND RANK < 20 AND SeqFreq > 1 " +
"UNION " +
"SELECT top 5 t1.*, t2.Job_Sector_Name, '0' as Pref " +
"FROM JobSequencesAllInfowithJobSectors t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE currentJob LIKE '" + chosenJob + "' " +
"AND ([salaryNext] - [salaryCurrent]) > -1000 " +
"AND JobSectorCurrent != " + chosenSector + " " +
"AND RANK < 20 AND SeqFreq > 1 " +
"ORDER BY SEQFREQ desc " +
") AS T " +
"ORDER BY currentJob, Pref Desc, SeqFreq desc";
var com = new SqlCommand(stmt, conn);
com.Connection.Open();
var rs = com.ExecuteReader();
if (rs.Read())
{
var countConn = new SqlConnection(ConfigurationManager.ConnectionStrings["JobSequenceDBContext"].ConnectionString);
var countStmt =
"SELECT SUM(SEQFREQ) as total " +
"FROM JobSequencesAllInfowithJobSectors t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE currentJob LIKE '" + chosenJob + "' AND ([salaryNext] - [salaryCurrent]) > -1000 " +
"AND JobSectorCurrent = " + chosenSector + " " +
"AND RANK < 20 AND SeqFreq > 1 ";
var countCom = new SqlCommand(countStmt, countConn);
countCom.Connection.Open();
var countTemp = countCom.ExecuteScalar();
var count = 0;
if (countTemp != DBNull.Value)
{
count = Convert.ToInt32(countTemp);
}
do
{
var js = new JobSequence(rs["nextJob"].ToString(),
Convert.ToDecimal(rs["salaryNext"]),
rs["Job_Sector_Name"].ToString(),
Convert.ToInt32(rs["JobSectorNext"]));
js.Pref = Convert.ToInt32(rs["Pref"]);
if (js.Pref == 1 && count > 0)
{
js.Probability = Convert.ToDecimal(rs["seqfreq"])/count;
}
else
{
js.Probability = 0;
}
js.AvgLength = Convert.ToInt32(rs["AvgLengthNext"]);
jobSequences.Add(js);
} while (rs.Read());
}
else
{
rs.Close();
com.Connection.Close();
stmt = "SELECT * FROM (" +
"SELECT t1.*, t2.Job_Sector_Name, '1' as Pref " +
"FROM JobSequencesAllInfowithJobSectors t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE currentJob LIKE '" + chosenJob + "' " +
"AND ([salaryNext] - [salaryCurrent]) > -1000 " +
"AND JobSectorCurrent = " + chosenSector + " " +
"AND RANK < 20 AND SeqFreq > 0 " +
"UNION " +
"SELECT t1.*, t2.Job_Sector_Name, '0' as Pref " +
"FROM JobSequencesAllInfowithJobSectors t1 " +
"INNER JOIN Lookup_Job_Sectors t2 " +
"ON t1.JobSectorNext = t2.Job_Sector_ID " +
"WHERE currentJob LIKE '" + chosenJob + "' " +
"AND ([salaryNext] - [salaryCurrent]) > -1000 " +
"AND RANK < 20 AND SeqFreq > 0 " +
"AND JobSectorCurrent != " + chosenSector + " " +
") AS T " +
"ORDER BY currentJob, Pref Desc, SeqFreq desc";
com = new SqlCommand(stmt, conn);
com.Connection.Open();
rs = com.ExecuteReader();
while (rs.Read())
{
var js = new JobSequence(rs["nextJob"].ToString(),
Convert.ToDecimal(rs["salaryNext"]),
rs["Job_Sector_Name"].ToString(),
Convert.ToInt32(rs["JobSectorNext"]));
js.Pref = Convert.ToInt32(rs["Pref"]);
js.Probability = 0;
js.AvgLength = Convert.ToInt32(rs["AvgLengthNext"]);
jobSequences.Add(js);
}
}
rs.Close();
ViewBag.JobTitle = chosenJob;
return View(jobSequences);
}
