private bool verifyCurrentUserProject(int projID)
{
if (Session["UserType"] != null && Convert.ToInt32(Session["UserType"]) == Global.AdminUserType)
{
return(true);
}
if (Session["UserID"] != null)
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("SELECT manager_id FROM pms_project WHERE pms_project.id = @projid;", con);
cmd.Parameters.Add("@projid", SqlDbType.Int).Value = projID;
try
{
con.Open();
int managerID = (int)cmd.ExecuteScalar();
if (managerID == Convert.ToInt32(Session["UserID"]))
{
return(true);
}
} catch (Exception ex) {
//throw (ex);
return(false);
} finally {
con.Close();
}
}
return(false);
}
private void fillProjectsDropDown()
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
DataSet myProjects = new DataSet();
if (Session["UserID"] != null)
{
SqlDataAdapter adptProjs;
if (Global.isAdmin())
{
adptProjs = new SqlDataAdapter("SELECT pms_project.id as [ID], pms_project.name + ' (ID: ' + CAST(pms_project.id AS VARCHAR(12)) + ')' AS [NameAndID] FROM pms_project ORDER BY [NameAndID] ASC;", con);
}
else
{
adptProjs = new SqlDataAdapter("SELECT pms_project.id as [ID], pms_project.name + ' (ID: ' + CAST(pms_project.id AS VARCHAR(12)) + ')' AS [NameAndID] FROM pms_project WHERE pms_project.manager_id = " + Session["UserID"].ToString() + " ORDER BY [NameAndID] ASC;", con);
}
adptProjs.Fill(myProjects);
DropDownList3.DataSource = myProjects;
DropDownList3.DataTextField = "NameAndID";
DropDownList3.DataValueField = "ID";
DropDownList3.DataBind();
DropDownList3.Items.Insert(0, new ListItem("Select a Project", "0"));
}
if (Request.QueryString["ProjectID"] != null)
{
string projID = Request.QueryString["ProjectID"];
DropDownList3.Items.FindByValue(projID).Selected = true;
}
}
private project getCurrentProject(int curprojid)
{
project current = null;
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("SELECT id, name, manager_id, start_date, start_date_flex, end_date, end_date_flex, current_stage_override FROM pms_project WHERE id=@curprojid", con);
cmd.Parameters.Add("@curprojid", SqlDbType.Int).Value = curprojid;
try
{
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
current = new project(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), reader.GetDateTime(3), reader.GetInt32(4), reader.GetDateTime(5), reader.GetInt32(6));
}
}
}
catch
{
}
finally
{
con.Close();
}
return(current);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserType"] == null || Convert.ToInt32(Session["UserType"]) != Global.AdminUserType) //admin
{
Global.Application_AccessDenied(sender, e);
}
else
{
SqlDataSource1.ConnectionString = Global.getConnectionString();
}
Text1.Focus();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserType"] == null || (Convert.ToInt32(Session["UserType"]) != Global.ManagerUserType && Convert.ToInt32(Session["UserType"]) != Global.AdminUserType)) //manager & admin
{
Global.Application_AccessDenied(sender, e);
}
else
{
SqlDataSourceInd.ConnectionString = Global.getConnectionString();
SqlDataSourceCust.ConnectionString = Global.getConnectionString();
}
}
private void LoadGrid(string sortExpr, string sortDirection)
{
ViewState["SortExpression"] = sortExpr;
ViewState["sortDirectionStr"] = sortDirection;
if (Request.QueryString["ProjectID"] != null)
{
//dont forget to check if userid == managerid
int projectID = Convert.ToInt32(Request.QueryString["ProjectID"]);
if (verifyCurrentUserProject(projectID))
{
if (Global.isDebug)
{
Response.Write("Verified Project ID!<br/>");
}
LoadProjectAttributes(projectID);
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("SELECT pms_resource.experience_level as [Experience_Level], pms_resource_role.name AS [Role], pms_resource.last_name + ', ' + pms_resource.first_name AS [Name], pms_resource.email_address AS [Email_Address] FROM pms_resourceproject INNER JOIN pms_resource ON pms_resource.id = pms_resourceproject.resource_id INNER JOIN pms_resource_role ON pms_resource_role.id = pms_resource.role_id WHERE pms_resourceproject.project_id = @projid ORDER BY " + sortExpr + " " + sortDirection + ";", con);
cmd.Parameters.Add("@projid", SqlDbType.Int).Value = projectID;
try
{
con.Open();
SqlDataAdapter DA = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
DA.Fill(ds);
if (ds != null)
{
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
}
catch (Exception ex)
{
throw (ex);
}
finally
{
cmd.Dispose();
con.Dispose();
con.Close();
}
}
else
{
Response.Redirect("~/Manager/MyProjects.aspx");
}
}
else
{
fieldsActive(false);
}
}
protected void submitButton_Click(object sender, EventArgs e)
{
int userType = DropDownList1.SelectedIndex;
if (userType != 0) // if user type is selected
{
String first = Text1.Value,
last = Text2.Value,
email = Text3.Value,
username = Text4.Value,
password = Text5.Value;
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("INSERT INTO pms_user ( username, password, first_name, last_name, email_address, type_id ) VALUES "
+ "(@username, @password, @first, @last, @email, @type);", con);
cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
cmd.Parameters.Add("@first", SqlDbType.VarChar).Value = first;
cmd.Parameters.Add("@last", SqlDbType.VarChar).Value = last;
cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
cmd.Parameters.Add("@type", SqlDbType.Int).Value = userType;
if (Global.isDebug)
{
Response.Write("first: " + first + "last: " + last + "email: " + email + "username: "******"password: "******"<br/>");
}
try {
con.Open();
if (Global.isDebug)
{
Response.Write(cmd.CommandText);
}
cmd.ExecuteNonQuery();
Global.logEventUser(Convert.ToInt32(Session["UserID"]), "Created user " + "\"" + username + "\" as a(n) " + DropDownList1.SelectedValue + "!", 0);
outputLabel.Visible = true;
}
catch (Exception ex)
{
throw (ex);
}
finally
{
cmd.Dispose();
con.Dispose();
con.Close();
}
}
}
protected void RemoveSelectedButton_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("DELETE FROM pms_resourceproject WHERE ", con);
string pc = "", seperator = " OR ";
bool deleteAtLeastOne = false;
int count = 0;
int projectID = Convert.ToInt32(Request.QueryString["ProjectID"]);
string logAction = "Removed resources from project: <br/>";
foreach (GridViewRow row in GridView1.Rows)
{
CheckBox c = (CheckBox)row.FindControl("RemoveSelector");
if (c != null && c.Checked)
{
count++;
string loc = "resid" + count;
deleteAtLeastOne = true;
int resourceID = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);
logAction += "(ID: " + resourceID + ") " + row.Cells[1].Text + " <br/>";
cmd.CommandText += pc;
cmd.CommandText += "(project_id=" + projectID + " AND resource_id=@" + loc + ")";
cmd.Parameters.Add("@" + loc, SqlDbType.Int).Value = resourceID;
pc = seperator;
}
}
if (deleteAtLeastOne)
{
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
con.Close();
cmd.Dispose();
Global.logEventProject(Convert.ToInt32(Session["UserID"]), logAction, projectID);
}
}
string redirstr = "~/Manager/EditProjectResources.aspx?ProjectID=" + projectID.ToString();
Response.Redirect(redirstr);
}
protected void AddResToProj_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("INSERT INTO pms_resourceproject (project_id, resource_id) VALUES ", con);
string pc = "", seperator = ", ";
bool addAtLeastOne = false;
int count = 0;
int projectID = Convert.ToInt32(Request.QueryString["ProjectID"]);
string logAction = "Added resources to project: <br/>";
foreach (GridViewRow row in GridView1.Rows)
{
CheckBox c = (CheckBox)row.FindControl("AddSel");
if (c != null && c.Checked)
{
count++;
string loc = "resid" + count;
addAtLeastOne = true;
int resourceID = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);
logAction += "(ID: " + resourceID + ") " + row.Cells[1].Text + " <br/>";
cmd.CommandText += pc;
cmd.CommandText += "( " + projectID + ", " + resourceID + ")";
pc = seperator;
}
}
if (addAtLeastOne)
{
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
con.Close();
cmd.Dispose();
Global.logEventProject(Convert.ToInt32(Session["UserID"]), logAction, projectID);
}
}
LoadGrid("id", "ASC");
//string redirstr = "~/Manager/SearchResource.aspx?ProjectID=" + projectID.ToString();
//Response.Redirect(redirstr);
}
private int[] getHrsWeeks()
{
project current = getCurrentProject(Convert.ToInt32(Request.QueryString["ProjectID"]));
string tenthruleinput = "";
Boolean go = false;
if (tenthrulereadonly.Value == "??-??-??-??-??-??-??-??-??-??" && DropDownList1.SelectedIndex != 0)
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("SELECT default_tenth_rule FROM pms_resource_role WHERE name=@name", con);
cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = DropDownList1.SelectedValue;
try
{
con.Open();
tenthruleinput = cmd.ExecuteScalar().ToString();
go = true;
}
catch
{
}
finally
{
con.Close();
}
}
else if (tenthrulereadonly.Value != "??-??-??-??-??-??-??-??-??-??")
{
tenthruleinput = tenthrulereadonly.Value.ToString().Replace("-", "");
go = true;
}
else
{
go = false;
}
int[] hrsweeks = new int[10];
if (go)
{
int[] curten = current.calctenthrule(tenthruleinput, Convert.ToInt32(projecthours.Value));
int weeksper = current.getWeeksInOneTenth();
for (int i = 0; i < 10; i++)
{
hrsweeks[i] = curten[i] / weeksper;
}
}
return(hrsweeks);
}
private void LoadGrid(string sortExpr, string sortDirection)
{
ViewState["sortDirectionStr"] = sortDirection;
ViewState["SortExpression"] = sortExpr;
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("SELECT pms_project.id AS ID, pms_project.name AS Name, pms_project.start_date AS [Start Date], pms_project.end_date AS [End Date], pms_customer.name AS Customer, pms_industry.name AS Industry, pms_user.username AS Manager FROM pms_project INNER JOIN pms_customer ON pms_customer.id = pms_project.customer_id INNER JOIN pms_industry ON pms_industry.id = pms_project.industry_id INNER JOIN pms_user ON pms_user.id = pms_project.manager_id", con);
if (Global.isAdmin()) //manager column hidden if manager, visible if admin
{
cmd.CommandText += " ORDER BY " + sortExpr + " " + sortDirection + ";";
GridView1.Columns[6].Visible = true;
}
else
{
GridView1.Columns[6].Visible = false;
cmd.CommandText += " WHERE pms_project.manager_id = @userid ORDER BY " + sortExpr + " " + sortDirection + ";";
cmd.Parameters.Add("@userid", SqlDbType.Int).Value = Convert.ToInt32(Session["UserID"]);
}
try
{
con.Open();
SqlDataAdapter DA = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
DA.Fill(ds);
if (ds != null)
{
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
}
catch (Exception ex)
{
throw (ex);
}
finally
{
cmd.Dispose();
con.Dispose();
con.Close();
}
}
protected void submitButton_Click(object sender, EventArgs e)
{
string first = firstName.Value.ToString();
string last = lastName.Value.ToString();
string em = email.Value.ToString();
string whours = hours.Value.ToString();
string explevel = exp.SelectedValue.ToString();
string ind = industry.SelectedValue.ToString();
string rol = role.SelectedValue.ToString();
string stat = status.SelectedValue.ToString();
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("INSERT INTO pms_resource (first_name, last_name, email_address, industry_id, role_id, status_id, work_hours, experience_level) VALUES (@first, @last, @email, @ind, @role, @status, @hours, @exp);", con);
cmd.Parameters.Add("@first", SqlDbType.VarChar).Value = first;
cmd.Parameters.Add("@last", SqlDbType.VarChar).Value = last;
cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = em;
cmd.Parameters.Add("@ind", SqlDbType.Int).Value = ind;
cmd.Parameters.Add("@role", SqlDbType.Int).Value = rol;
cmd.Parameters.Add("@status", SqlDbType.Int).Value = stat;
cmd.Parameters.Add("@hours", SqlDbType.Int).Value = whours;
cmd.Parameters.Add("@exp", SqlDbType.Int).Value = explevel;
try
{
con.Open();
cmd.ExecuteNonQuery();
Global.logEventUser(Convert.ToInt32(Session["UserID"]), "Added Resource " + "\"" + first + " " + last + "\"!", 0);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
cmd.Dispose();
con.Dispose();
con.Close();
}
outputLabel.Visible = true;
}
protected void submitButton_Click(object sender, EventArgs e)
{
string name = pname.Value.ToString();
string cus = cust.SelectedValue.ToString();
string inds = ind.SelectedValue.ToString();
string sdate = start.Value.ToString();
string edate = end.Value.ToString();
string sflx = sflex.Value.ToString();
string eflx = eflex.Value.ToString();
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("INSERT INTO pms_project (name, start_date, end_date, start_date_flex, end_date_flex, customer_id, industry_id, manager_id) VALUES (@name, @start, @end, @sflex, @eflex, @cust, @ind, @mang);", con);
cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
cmd.Parameters.Add("@start", SqlDbType.Date).Value = sdate;
cmd.Parameters.Add("@end", SqlDbType.Date).Value = edate;
cmd.Parameters.Add("@sflex", SqlDbType.Int).Value = sflx;
cmd.Parameters.Add("@eflex", SqlDbType.Int).Value = eflx;
cmd.Parameters.Add("@cust", SqlDbType.Int).Value = cus;
cmd.Parameters.Add("@ind", SqlDbType.Int).Value = inds;
cmd.Parameters.Add("@mang", SqlDbType.Int).Value = Convert.ToInt32(Session["UserID"]);
try
{
con.Open();
cmd.ExecuteNonQuery();
Global.logEventProject(Convert.ToInt32(Session["UserID"]), "Created Project " + "\"" + name + "\"!", 0);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
cmd.Dispose();
con.Dispose();
con.Close();
}
outputLabel.Visible = true;
}
private void removeResourcesOnCurrentProject(DataTable dt)
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
for (var i = dt.Rows.Count - 1; i >= 0; i--)
{
SqlCommand cmd = new SqlCommand("SELECT * FROM pms_resourceproject WHERE resource_id=" + dt.Rows[i]["id"] + " AND project_id=@projid", con);
cmd.Parameters.Add("@projid", con).Value = Request.QueryString["ProjectID"];
try
{
con.Open();
if (cmd.ExecuteScalar() != null)
{
dt.Rows[i].Delete();
}
}
catch { }
finally { con.Close(); }
}
dt.AcceptChanges();
}
private void LoadGrid(string sortExpr, string sortDirection)
{
Boolean sortingbyID = false;
if (sortExpr == "[id]")
{
sortingbyID = true;
}
ViewState["sortDirectionStr"] = sortDirection;
int[] hrsweeks = new int[10];
ViewState["SortExpression"] = sortExpr;
if (Request.QueryString["ProjectID"] != null)
{
hrsweeks = getHrsWeeks();
}
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("", con);
String pC = " WHERE ";
cmd.CommandText = "SELECT pms_resource.id, last_name + ', ' + first_name AS [Name], " +
" email_address AS 'Email Address', experience_level AS 'Experience Level', " +
" work_hours AS 'Work Hours', pms_industry.name AS 'Industry', pms_resource_role.name" +
" AS 'Role' FROM pms_resource";
cmd.CommandText += " INNER JOIN pms_resource_role ON pms_resource.role_id = pms_resource_role.id";
cmd.CommandText += " INNER JOIN pms_industry ON pms_resource.industry_id = pms_industry.id";
String firstname = Text1.Value;
if (Global.isDebug)
{
Response.Write(firstname);
}
String lastname = Text2.Value;
String email = Text3.Value;
String expmeasure = DropDownList4.SelectedValue;
int explevel = Convert.ToInt32(DropDownList6.SelectedValue);
String workhoursmeasure = DropDownList5.SelectedValue;
int workhourslevel = 40;
if (Text5.Value != "")
{
workhourslevel = Convert.ToInt32(Text5.Value);
}
String industry = DropDownList2.SelectedValue;
String role = DropDownList1.SelectedValue;
if (firstname != "")
{
cmd.CommandText += pC;
cmd.CommandText += "first_name=@firstname";
cmd.Parameters.Add("@firstname", SqlDbType.VarChar).Value = firstname;
pC = " AND ";
}
if (lastname != "")
{
cmd.CommandText += pC;
cmd.CommandText += "last_name=@lastname";
cmd.Parameters.Add("@lastname", SqlDbType.VarChar).Value = lastname;
pC = " AND ";
}
if (email != "")
{
cmd.CommandText += pC;
cmd.CommandText += "email_address=@email";
cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
pC = " AND ";
}
if (explevel != -1)
{
cmd.CommandText += pC;
cmd.CommandText += "experience_level";
if (expmeasure == ">")
{
cmd.CommandText += ">";
}
else if (expmeasure == "≥")
{
cmd.CommandText += ">=";
}
else if (expmeasure == "=")
{
cmd.CommandText += "=";
}
else if (expmeasure == "≤")
{
cmd.CommandText += "<=";
}
else if (expmeasure == "<")
{
cmd.CommandText += "<";
}
cmd.CommandText += "@exp";
cmd.Parameters.Add("@exp", SqlDbType.Int).Value = explevel;
pC = " AND ";
}
if (workhourslevel != -1)
{
cmd.CommandText += pC;
cmd.CommandText += "work_hours";
if (workhoursmeasure == ">")
{
cmd.CommandText += ">";
}
else if (workhoursmeasure == "≥")
{
cmd.CommandText += ">=";
}
else if (workhoursmeasure == "=")
{
cmd.CommandText += "=";
}
else if (workhoursmeasure == "≤")
{
cmd.CommandText += "<=";
}
else if (workhoursmeasure == "<")
{
cmd.CommandText += "<";
}
cmd.CommandText += "@wrkhrs";
cmd.Parameters.Add("@wrkhrs", SqlDbType.Int).Value = workhourslevel;
pC = " AND ";
}
if (industry != "Any")
{
cmd.CommandText += pC;
cmd.CommandText += "pms_industry.name=@industry";
cmd.Parameters.Add("@industry", SqlDbType.VarChar).Value = industry;
pC = " AND ";
}
if (Global.isDebug)
{
Response.Write("INDUSTRY: " + industry + "\n");
}
if (Global.isDebug)
{
Response.Write("ROLE: " + role + "\n");
}
if (role != "Any")
{
cmd.CommandText += pC;
cmd.CommandText += "pms_resource_role.name=@role";
cmd.Parameters.Add("@role", SqlDbType.VarChar).Value = role;
pC = " AND ";
}
cmd.CommandText += " ORDER BY " + sortExpr + " " + sortDirection + ";";
try
{
con.Open();
DataTable dt = new DataTable();
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);
dt.Columns.Add("Rank", typeof(int));
if (Request.QueryString["ProjectID"] != null)
{
calcNumbersResources(dt, hrsweeks);
}
if (Global.isDebug)
{
Response.Write(cmd.CommandText);
}
if (sortingbyID) //default sort -- by rank
{
dt.DefaultView.Sort = "Rank DESC";
dt = dt.DefaultView.ToTable();
}
if (Request.QueryString["ProjectID"] != null)
{
removeResourcesOnCurrentProject(dt);
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
cmd.Dispose();
con.Close();
}
}
private void calcNumbersResources(DataTable dt, int[] hrsweeks)
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
foreach (DataRow row in dt.Rows)
{
string resid = row["id"].ToString();
SqlCommand cmd = new SqlCommand("SELECT pms_project.start_date AS [start], pms_project.start_date_flex AS [startf], pms_project.end_date AS [end], pms_project.end_date_flex AS [endf], pms_resource_role.default_tenth_rule AS [nooverride], pms_resource.work_hours AS [weeklyhours], pms_project.id AS [projid] FROM pms_resourceproject INNER JOIN pms_project ON pms_resourceproject.project_id = pms_project.id INNER JOIN pms_resource ON pms_resource.id = @resid INNER JOIN pms_resource_role ON pms_resource_role.id = pms_resource.role_id WHERE pms_resourceproject.resource_id=@resid;", con);
cmd.Parameters.Add("@resid", SqlDbType.Int).Value = resid;
try {
con.Open();
DataTable data = new DataTable();
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(data);
int rank = 0;
if (data.Rows.Count > 0)
{
int[] numofpeopleonproject = new int[data.Rows.Count];
int[] hourspertenth = new int[10];
Random rand2 = new Random();
for (int i = 0; i < 10; i++)
{
hourspertenth[i] = rand2.Next(5, 13) - Convert.ToInt32(data.Rows[0]["weeklyhours"]);
}
foreach (DataRow row2 in data.Rows)
{
DateTime start = (DateTime)row2["start"];
start.AddDays(Convert.ToInt32(row2["startf"]) * -7);
DateTime end = (DateTime)row2["end"];
start.AddDays(Convert.ToInt32(row2["endf"]) * 7);
int totalweeks = (int)(end - start).TotalDays / 7;
string tenth = row2["nooverride"].ToString();
int[] tenrules = new int[10];
int tentotal = 0;
for (int i = 0, j = 0; j < 10; i += 2, j++)
{
tenrules[j] = Convert.ToInt32(tenth.Substring(i, 2));
tentotal += tenrules[j];
}
Random rand = new Random();
double totalhours = totalweeks * (2.0 + rand.NextDouble() * 7.0);
for (int i = 0; i < 10; i++)
{
hourspertenth[i] += (int)((tenrules[i] / tentotal) * totalhours);
}
}
for (int i = 0; i < 10; i++)
{
if (hourspertenth[i] < hrsweeks[i])
{
rank += Math.Abs(hrsweeks[i] - hourspertenth[i]);
}
}
rank -= (data.Rows.Count * rand2.Next(3, 5));
}
else
{
rank = 10000;
}
row["Rank"] = rank;
}
catch {
con.Close();
}
finally {
con.Close();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserType"] == null || (Convert.ToInt32(Session["UserType"]) != Global.ManagerUserType && Convert.ToInt32(Session["UserType"]) != Global.AdminUserType)) //manager & admin
{
Global.Application_AccessDenied(sender, e);
}
else if (!IsPostBack)
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
DataTable roles = new DataTable(), industries = new DataTable();
SqlDataAdapter adptrole = new SqlDataAdapter("SELECT name FROM pms_resource_role;", con);
adptrole.Fill(roles);
SqlDataAdapter adptindustry = new SqlDataAdapter("SELECT name FROM pms_industry;", con);
adptindustry.Fill(industries);
if (Request.QueryString["ProjectID"] == null)
{
GridView1.Columns[8].Visible = false;
}
else
{
GridView1.Columns[9].Visible = false;
}
//String temp;
//foreach (DataRow row in roles.Rows) {
// temp = row["name"].ToString();
// row["name"] = char.ToUpper(temp[0]) + temp.Substring(1).ToLower();
//}
//foreach (DataRow row in industries.Rows) {
// temp = row["name"].ToString();
// row["name"] = char.ToUpper(temp[0]) + temp.Substring(1).ToLower();
//}
try {
con.Open();
DropDownList1.DataSource = roles;
DropDownList1.DataTextField = "name";
DropDownList1.DataValueField = "name"; // "id" if SELECT *
DropDownList1.DataBind();
DropDownList2.DataSource = industries;
DropDownList2.DataTextField = "name";
DropDownList2.DataValueField = "name";
DropDownList2.DataBind();
} catch (Exception ex) {
throw (ex);
}
finally
{
adptrole.Dispose();
adptindustry.Dispose();
roles.Dispose();
con.Dispose();
con.Close();
}
DropDownList1.Items.Insert(0, new ListItem("Any", "Any"));
DropDownList2.Items.Insert(0, new ListItem("Any", "Any"));
DropDownList4.SelectedIndex = 2;
DropDownList5.SelectedIndex = 4;
Text5.Value = 40.ToString();
}
else
{
}
Text1.Focus();
}
protected void GridView1_RowUpdating1(object sender, GridViewUpdateEventArgs e)
{
int id = Convert.ToInt32(e.Keys[0]);
GridViewRow row = GridView1.Rows[e.RowIndex];
string uUsername = ((TextBox)(row.Cells[0].Controls[0])).Text;
string uPass = ((TextBox)(row.Cells[1].Controls[0])).Text;
string uFirstName = ((TextBox)(row.Cells[2].Controls[0])).Text;
string uLastName = ((TextBox)(row.Cells[3].Controls[0])).Text;
string uEmail = ((TextBox)(row.Cells[4].Controls[0])).Text;
string uType = ((TextBox)(row.Cells[5].Controls[0])).Text;
if (uPass == "" || uUsername == "" || uFirstName == "" || uLastName == "" || uEmail == "" || uType == "")
{
LoadGrid("[Last_Name]", "ASC");
GridView1.EditIndex = -1;
BindData();
}
else
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("UPDATE pms_user SET username=@username,password=@password, first_name=@firstname, last_name=@lastname, email_address=@email, type_id=@type WHERE id=@id;", con);
cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = uUsername;
cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = uPass;
cmd.Parameters.Add("@firstname", SqlDbType.VarChar).Value = uFirstName;
cmd.Parameters.Add("@lastname", SqlDbType.VarChar).Value = uLastName;
cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = uEmail;
int tID = -1;
if (uType.Equals("administrator"))
{
tID = Global.AdminUserType;
cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = tID;
}
else if (uType.Equals("manager"))
{
tID = Global.ManagerUserType;
cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = tID;
}
else
{
LoadGrid("[Last_Name]", "ASC");
GridView1.EditIndex = -1;
BindData();
}
if (tID != -1)
{
cmd.Parameters.Add("@id", SqlDbType.VarChar).Value = id;
try
{
con.Open();
cmd.ExecuteNonQuery();
Global.logEventUser(Convert.ToInt32(Session["UserID"]), "Updated customer with ID=" + "\"" + id + "\"!", 0);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
cmd.Dispose();
con.Dispose();
con.Close();
LoadGrid("[Last_Name]", "ASC");
GridView1.EditIndex = -1;
BindData();
}
}
}
}
private void logChanges(bool managerChanged, bool nameChanged, bool customerChanged, bool industryChanged, bool startdateChanged, bool enddateChanged, bool startdateflexChanged, bool enddateflexChanged, bool overrideChanged, bool stageChanged, bool stageAuto)
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
string currentProjectName = Projectname.Value,
currentCustomerID = DropDownList4.SelectedValue,
currentIndustryID = DropDownList5.SelectedValue,
currentStartDate = Text1.Value,
currentManagerID = DropDownList8.SelectedValue,
currentEndDate = Text5.Value,
currentStartDateFlex = Text3.Value,
currentEndDateFlex = Text4.Value,
currentProjectStage = DropDownList9.SelectedValue;
bool currentStageOverride = check_StageOverride.Checked;
string actionLog = "Changed";
string pre = " ";
string separator = " <br/>";
if (managerChanged)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SELECT first_name + ' ' + last_name AS [name] FROM pms_user WHERE pms_user.id = @id;", con);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = origManagerID;
string origmanagerName = (string)cmd.ExecuteScalar();
cmd.Dispose();
cmd = new SqlCommand("SELECT first_name + ' ' + last_name AS [name] FROM pms_user WHERE pms_user.id = @id;", con);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = currentManagerID;
string currentmanagerName = (string)cmd.ExecuteScalar();
actionLog += pre;
actionLog += "Manager from \"" + origmanagerName + "\" to \"" + currentmanagerName + "\"";
pre = separator;
}
catch (Exception ex)
{
throw (ex);
}
finally
{
con.Close();
}
}
if (nameChanged)
{
actionLog += pre;
actionLog += "Project Name from \"" + origProjectName + "\" to \"" + currentProjectName + "\"";
pre = separator;
}
if (customerChanged)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SELECT name FROM pms_customer WHERE pms_customer.id = @custid;", con);
cmd.Parameters.Add("@custid", SqlDbType.Int).Value = origCustomerID;
string origcustomerName = (string)cmd.ExecuteScalar();
cmd.Dispose();
cmd = new SqlCommand("SELECT name FROM pms_customer WHERE pms_customer.id = @custid;", con);
cmd.Parameters.Add("@custid", SqlDbType.Int).Value = currentCustomerID;
string currentcustomerName = (string)cmd.ExecuteScalar();
actionLog += pre;
actionLog += "Customer from \"" + origcustomerName + "\" to \"" + currentcustomerName + "\"";
pre = separator;
}
catch { }
finally
{
con.Close();
}
}
if (industryChanged)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SELECT name FROM pms_industry WHERE pms_industry.id = @industid;", con);
cmd.Parameters.Add("@industid", SqlDbType.Int).Value = origIndustryID;
string origindustryName = (string)cmd.ExecuteScalar();
cmd.Dispose();
cmd = new SqlCommand("SELECT name FROM pms_industry WHERE pms_industry.id = @industid;", con);
cmd.Parameters.Add("@industid", SqlDbType.Int).Value = currentIndustryID;
string currentindustryName = (string)cmd.ExecuteScalar();
actionLog += pre;
actionLog += "Industry from \"" + origindustryName + "\" to \"" + currentindustryName + "\"";
pre = separator;
}
catch { }
finally
{
con.Close();
}
}
if (startdateChanged)
{
actionLog += pre;
actionLog += "Start date from \"" + origStartDate + "\" to " + currentStartDate + "\"";
pre = separator;
}
if (enddateChanged)
{
actionLog += pre;
actionLog += "End date from \"" + origEndDate + "\" to \"" + currentEndDate + "\"";
pre = separator;
}
if (startdateflexChanged)
{
actionLog += pre;
actionLog += "Start date flexibility from \"" + origStartDateFlex + "\" weeks to \"" + currentStartDateFlex + "\" weeks";
pre = separator;
}
if (enddateflexChanged)
{
actionLog += pre;
actionLog += "End date flexibility from \"" + origEndDateFlex + "\" weeks to \"" + currentEndDateFlex + "\" weeks";
pre = separator;
}
if (overrideChanged)
{
actionLog += pre;
actionLog += "Stage override from \"" + origStageOverride + "\" to \"" + currentStageOverride + "\"";
pre = separator;
}
if (origStageOverride && stageAuto)
{
actionLog += pre;
actionLog += "Stage level checking changed to automatic";
pre = separator;
}
else if (stageChanged)
{
actionLog += pre;
actionLog += "Stage level changed from \"" + origProjectStage + "\" to \"" + currentProjectStage + "\"";
pre = separator;
}
Global.logEventProject(Convert.ToInt32(Session["UserID"]), actionLog, Convert.ToInt32(origProjectID));
//dont forget to check if userid isn't null when clicking button
}
private void LoadProjectAttributes(int projID)
{
if (Global.isDebug)
{
Response.Write("Inside of LoadProjectAttributes Successfully!<br/>");
}
SqlConnection con = new SqlConnection(Global.getConnectionString());
DataSet customers = new DataSet(), industries = new DataSet(), projData = new DataSet(), managers = new DataSet();
SqlDataAdapter adptCustomers = new SqlDataAdapter("SELECT id, name FROM pms_customer;", con),
adptIndustries = new SqlDataAdapter("SELECT id, name FROM pms_industry;", con),
adptUsers = new SqlDataAdapter("SELECT id, first_name + ' ' + last_name + ' (ID ' + CAST(id AS VARCHAR(12)) + ': ' + username + ')' AS name FROM pms_user;", con);
adptCustomers.Fill(customers);
adptIndustries.Fill(industries);
adptUsers.Fill(managers);
//TODO: Create a field for Admin's to change the manager on a project
// fill dropdownlist for customers
DropDownList4.DataSource = customers;
DropDownList4.DataTextField = "name";
DropDownList4.DataValueField = "id";
DropDownList4.DataBind();
// fill dropdownlist for industries
DropDownList5.DataSource = industries;
DropDownList5.DataTextField = "name";
DropDownList5.DataValueField = "id";
DropDownList5.DataBind();
// fill manager dropdown list for admins
if (Global.isAdmin())
{
DropDownList8.DataSource = managers;
DropDownList8.DataTextField = "name";
DropDownList8.DataValueField = "id";
DropDownList8.DataBind();
}
// grab data from DB and fill input fields on form
SqlDataAdapter adptProjectData = new SqlDataAdapter("SELECT id, name, start_date, end_date, start_date_flex, end_date_flex, current_stage_override, customer_id, industry_id, manager_id FROM pms_project WHERE id = @projid;", con); // join users, grab pms_user.username for manager's username
adptProjectData.SelectCommand.Parameters.Add("@projid", SqlDbType.Int).Value = projID;
adptProjectData.Fill(projData);
DataRow result = projData.Tables[0].Rows[0];
Projectname.Value = result["name"].ToString();
DropDownList4.Items.FindByValue(result["customer_id"].ToString()).Selected = true;
DropDownList5.Items.FindByValue(result["industry_id"].ToString()).Selected = true;
if (Global.isAdmin())
{
DropDownList8.Items.FindByValue(result["manager_id"].ToString()).Selected = true;
}
//DateTime parsedDate;
//DateTime.TryParse(result["start_date"].ToString(), null, System.Globalization.DateTimeStyles.None, out parsedDate);
//Text1.Value = parsedDate.ToString("MM/dd/yyyy");
Text1.Value = DateTime.Parse(result["start_date"].ToString(), null, System.Globalization.DateTimeStyles.None).ToString("MM/dd/yyyy");
Text5.Value = DateTime.Parse(result["end_date"].ToString(), null, System.Globalization.DateTimeStyles.None).ToString("MM/dd/yyyy");
Text3.Value = result["start_date_flex"].ToString();
Text4.Value = result["end_date_flex"].ToString();
//set Project Stage
if (result["current_stage_override"] != DBNull.Value)
{
DropDownList9.SelectedValue = result["current_stage_override"].ToString();
DropDownList9.Enabled = true;
check_StageOverride.Checked = true;
origStageOverride = true;
ViewState["origStageOverride"] = origStageOverride;
}
else
{
origStageOverride = false;
ViewState["origStageOverride"] = origStageOverride;
}
// set original data for later logging
origManagerID = result["manager_id"].ToString();
// origManagerNa
origProjectID = projID.ToString();
origProjectName = Projectname.Value;
origCustomerID = DropDownList4.SelectedValue;
origIndustryID = DropDownList5.SelectedValue;
origStartDate = Text1.Value;
origEndDate = Text5.Value;
origStartDateFlex = Text3.Value;
origEndDateFlex = Text4.Value;
origProjectStage = DropDownList9.SelectedValue;
ViewState["origManagerID"] = origManagerID;
ViewState["origProjectID"] = origProjectID;
ViewState["origProjectName"] = origProjectName;
ViewState["origCustomerID"] = origCustomerID;
ViewState["origIndustryID"] = origIndustryID;
ViewState["origStartDate"] = origStartDate;
ViewState["origEndDate"] = origEndDate;
ViewState["origStartDateFlex"] = origStartDateFlex;
ViewState["origEndDateFlex"] = origEndDateFlex;
ViewState["origProjectStage"] = origProjectStage;
//enable fields for editing
fieldsActive(true);
}
protected void UpdateButton_Click(object sender, EventArgs e)
{
if (Session["UserID"] == null)
{
Global.Application_SessionExpired();
return;
}
else
{
string currentProjectID = Request.QueryString["ProjectID"].ToString();
if (currentProjectID.Equals(origProjectID)) // check to make sure project id loaded and project id to save to wasn't altered somehow.
{
string currentManagerID = DropDownList8.SelectedValue,
currentProjectName = Projectname.Value,
currentCustomerID = DropDownList4.SelectedValue,
currentIndustryID = DropDownList5.SelectedValue,
currentStartDate = Text1.Value,
currentEndDate = Text5.Value,
currentStartDateFlex = Text3.Value,
currentEndDateFlex = Text4.Value,
currentProjectStage = DropDownList9.SelectedValue;
bool currentStageOverride = check_StageOverride.Checked;
bool changedStage = false, changedOverride = false, useAuto = false;
if (origStageOverride == true && currentStageOverride == true)
{
changedStage = (origProjectStage == currentProjectStage) ? false : true;
useAuto = false;
}
else if (origStageOverride == false && currentStageOverride == false)
{
useAuto = true;
}
else if (origStageOverride == true && currentStageOverride == false)
{
useAuto = true; changedOverride = true;
}
else if (origStageOverride == false && currentStageOverride == true)
{
changedStage = (origProjectStage == currentProjectStage) ? false : true;
useAuto = false; changedOverride = true;
}
if (check_Manager.Checked || check_ProjectName.Checked || check_Customer.Checked || check_Industry.Checked ||
check_StartDate.Checked || check_EndDate.Checked || check_StartDateFlex.Checked || check_EndDateFlex.Checked || changedStage || useAuto || changedOverride) // check to see if anything was edited
{
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("UPDATE pms_project", con);
String pre = " SET ";
if (check_Manager.Checked)
{
cmd.CommandText += pre;
cmd.CommandText += "manager_id=@managerid";
cmd.Parameters.Add("@managerid", SqlDbType.Int).Value = currentManagerID;
pre = ", ";
}
if (check_ProjectName.Checked)
{
cmd.CommandText += pre;
cmd.CommandText += "name=@projname";
cmd.Parameters.Add("@projname", SqlDbType.VarChar).Value = currentProjectName;
pre = ", ";
}
if (check_Customer.Checked)
{
cmd.CommandText += pre;
cmd.CommandText += "customer_id=@custid";
cmd.Parameters.Add("@custid", SqlDbType.Int).Value = currentCustomerID;
pre = ", ";
}
if (check_Industry.Checked)
{
cmd.CommandText += pre;
cmd.CommandText += "industry_id=@industid";
cmd.Parameters.Add("@industid", SqlDbType.Int).Value = currentIndustryID;
pre = ", ";
}
if (check_StartDate.Checked)
{
cmd.CommandText += pre;
cmd.CommandText += "start_date=@startdate";
cmd.Parameters.Add("@startdate", SqlDbType.Date).Value = currentStartDate;
pre = ", ";
}
if (check_EndDate.Checked)
{
cmd.CommandText += pre;
cmd.CommandText += "end_date=@enddate";
cmd.Parameters.Add("@enddate", SqlDbType.Date).Value = currentEndDate;
pre = ", ";
}
if (check_StartDateFlex.Checked)
{
cmd.CommandText += pre;
cmd.CommandText += "start_date_flex=@startflex";
cmd.Parameters.Add("@startflex", SqlDbType.Int).Value = currentStartDateFlex;
pre = ", ";
}
if (check_EndDateFlex.Checked)
{
cmd.CommandText += pre;
cmd.CommandText += "end_date_flex=@endflex";
cmd.Parameters.Add("@endflex", SqlDbType.Int).Value = currentEndDateFlex;
pre = ", ";
}
if (useAuto)
{
cmd.CommandText += pre;
cmd.CommandText += "current_stage_override=NULL";
}
else if (changedStage)
{
cmd.CommandText += pre;
cmd.CommandText += "current_stage_override=@curstage";
cmd.Parameters.Add("@curstage", SqlDbType.Int).Value = currentProjectStage;
}
else if (changedOverride)
{
cmd.CommandText += pre;
cmd.CommandText += "current_stage_override=@curstage";
cmd.Parameters.Add("@curstage", SqlDbType.Int).Value = currentProjectStage;
}
cmd.CommandText += " WHERE id=@projid";
cmd.Parameters.Add("@projid", SqlDbType.Int).Value = currentProjectID;
cmd.CommandText += ";";
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
con.Close();
}
logChanges(check_Manager.Checked, check_ProjectName.Checked, check_Customer.Checked, check_Industry.Checked, check_StartDate.Checked, check_EndDate.Checked, check_StartDateFlex.Checked, check_EndDateFlex.Checked, changedOverride, changedStage, useAuto);
Response.Redirect("~/Manager/EditProject.aspx?ProjectID=" + Request.QueryString["ProjectID"]);
}
}
}
}
protected static Tuple <Boolean, int, int> Authentication(string username, string password)
{
SqlConnection conn;
SqlCommand cmd;
string lookupPassword = null;
int lookupUserID = -1;
int lookupUserType = -1;
Match usernameMatch = System.Text.RegularExpressions.Regex.Match(password, @"[a-zA-Z0-9_.\-\@]{3,15}");
Match passwordMatch = System.Text.RegularExpressions.Regex.Match(password, @"[A-Za-z0-9_.\-\!\@\#\$\%\&\*]{5,25}");
if ((null == username) || (0 == username.Length) || (username.Length > 15) || (username.Length < 3) || !usernameMatch.Success)
{
System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of username failed.");
return(new Tuple <Boolean, int, int>(false, -1, -1));
}
if ((null == password) || (0 == password.Length) || (password.Length > 25) || (password.Length < 5) || !passwordMatch.Success)
{
System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of password failed.");
return(new Tuple <Boolean, int, int>(false, -1, -1));
}
try
{
conn = new SqlConnection(Global.getConnectionString());
conn.Open();
cmd = new SqlCommand("SELECT id,password,type_id FROM pms_user WHERE username=@username", conn);
cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
lookupUserID = reader.GetInt32(0);
lookupPassword = reader.GetString(1);
lookupUserType = reader.GetInt32(2);
}
}
else
{
System.Diagnostics.Debug.WriteLine("Bad Login for user: {0} ", username);
}
reader.Close();
cmd.Dispose();
conn.Dispose();
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("[ValidateUser] Exception " + ex.Message);
}
if (lookupPassword == null)
{
return(new Tuple <Boolean, int, int>(false, -1, -1));
}
else
{
return(new Tuple <Boolean, int, int>((0 == string.Compare(lookupPassword, password, false)), lookupUserType, lookupUserID));
}
}
private void LoadGrid(string sortExpr, string sortDirection)
{
ViewState["sortDirectionStr"] = sortDirection;
ViewState["SortExpression"] = sortExpr;
SqlConnection con = new SqlConnection(Global.getConnectionString());
SqlCommand cmd = new SqlCommand("", con);
String pC = " WHERE ";
cmd.CommandText = "SELECT pms_user.id, username AS [Username], password AS [Password], first_name AS [First_Name], last_name AS [Last_Name], email_address AS [Email_Address], pms_user_type.name AS [User_Type] FROM pms_user INNER JOIN pms_user_type ON pms_user_type.id=pms_user.type_id";
String firstname = FirstName.Value.ToString();
String lastname = LastName.Value.ToString();
String username = Username.Value.ToString();
String email = Email.Value.ToString();
int usertype = -1;
if (DropDownList1.SelectedValue.Equals("Administrator"))
{
usertype = Global.AdminUserType;
}
else if (DropDownList1.SelectedValue.Equals("Manager"))
{
usertype = Global.ManagerUserType;
}
if (username != "")
{
cmd.CommandText += pC;
cmd.CommandText += "username=@username";
cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
pC = " AND ";
}
if (firstname != "")
{
cmd.CommandText += pC;
cmd.CommandText += "first_name=@firstname";
cmd.Parameters.Add("@firstname", SqlDbType.VarChar).Value = firstname;
pC = " AND ";
}
if (lastname != "")
{
cmd.CommandText += pC;
cmd.CommandText += "last_name=@lastname";
cmd.Parameters.Add("@lastname", SqlDbType.VarChar).Value = lastname;
pC = " AND ";
}
if (email != "")
{
cmd.CommandText += pC;
cmd.CommandText += "email_address=@email";
cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
pC = " AND ";
}
if (usertype != -1)
{
cmd.CommandText += pC;
cmd.CommandText += "type_id=@type";
cmd.Parameters.Add("@type", SqlDbType.Int).Value = usertype;
pC = " AND ";
}
cmd.CommandText += " ORDER BY " + sortExpr + " " + sortDirection + ";";
try
{
con.Open();
DataTable dt = new DataTable();
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
cmd.Dispose();
con.Close();
}
}