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