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();
}
}
}
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();
}
}
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();
}