/// <summary> /// Update a user's profile. Maybe we could instead have properties for each /// member and update the database in the destructor ... /// NOTE: That would not be a good idea due to the garbage collection issues /// that I discovered with the DBDriver class. /// </summary> public void updateProfile() { //this isn't working... why? is my update wrong? //no, a multiple update like that works... //is it losing something elsewhere? DBDriver myDB = new DBDriver(); myDB.Query = "update users set password=@pwd, security=@sec where id=@uID;"; //TODO: Add in changing username(need to verify availability) //myDB.addParam("@name", this.userName); myDB.addParam("@pwd", this.password); myDB.addParam("@sec", this.role); myDB.addParam("@uID", this.id); myDB.nonQuery(); myDB.Query = "update person set firstName=@first, lastName=@last, address=@address, city=@city, " + "state=@state, zip=@zip, phoneNumber=@phone, email=@mail where id=@uID;"; myDB.addParam("@first", this.firstName); myDB.addParam("@last", this.lastName); myDB.addParam("@address", this.address); myDB.addParam("@city", this.city); myDB.addParam("@state", this.state); myDB.addParam("@zip", this.zip); myDB.addParam("@phone", this.phone); myDB.addParam("@mail", this.email); myDB.addParam("@uID", this.id); myDB.nonQuery(); }
/// <summary> /// Remove a Task /// </summary> /// <param name="id">project id</param> public static void remove(string id) { DBDriver myDB = new DBDriver(); myDB.Query = "delete from Tasks where id=@id"; myDB.addParam("@id", id); myDB.nonQuery(); }
static public void declineNewUser(string delID) { DBDriver myDB = new DBDriver(); myDB.Query = "delete from newUsers where id=@id;"; myDB.addParam("@id", delID); myDB.nonQuery(); myDB.Query = "delete from person where id=@id;"; myDB.addParam("@id", delID); myDB.nonQuery(); }
/// <summary> /// Gets a DataSet with all Projects /// </summary> /// <returns></returns> public static DataSet getProjectsDataSet() { DBDriver myDB = new DBDriver(); myDB.Query = "select * from projects;"; DataSet ds = new DataSet(); myDB.createAdapter().Fill(ds); return(ds); }
/// <summary> /// Gets a DataSet of Tasks in the specified Module /// </summary> /// <param name="id">Module id</param> /// <returns>Tasks DataSet</returns> public static DataSet getTasksDataSet(string id) { DBDriver myDB = new DBDriver(); myDB.Query = "select * from tasks\n" + "where moduleID = @ID;"; myDB.addParam("@ID", id); DataSet ds = new DataSet(); myDB.createAdapter().Fill(ds); return(ds); }
/// <summary> /// Verify that an email address exists in the database /// </summary> /// <param name="email">email address to verify</param> /// <returns>true if it exists, false if it doesn't</returns> static public bool verifyEmailExists(string email) { DBDriver myDB = new DBDriver(); myDB.Query = "select count(*) from person where email=@email;"; myDB.addParam("@email", email); int k = Convert.ToInt32(myDB.scalar()); if (k == 1) { return(true); } return(false); }
/// <summary> /// Insert a Project in the database /// </summary> /// <param name="name"></param> /// <param name="mID">Assigned manager</param> /// <param name="description"></param> /// <param name="startDate">Start date</param> public static void create(string name, string mID, string description, string startDate) { DBDriver myDB = new DBDriver(); myDB.Query = "insert into projects (name, managerID, description, startDate) \n" + "values ( @name, @mID, @desc, @start, @end );"; myDB.addParam("@name", name); myDB.addParam("@mID", mID); myDB.addParam("@desc", description); myDB.addParam("@start", startDate); myDB.nonQuery(); }
/// <summary> /// Insert a Module in the database /// </summary> /// <param name="name"></param> /// <param name="pID">Project to add to</param> /// <param name="description"></param> /// <param name="startDate">Start date</param> public static void create(string name, string pID, string description, string startDate) { DBDriver myDB = new DBDriver(); myDB.Query = "insert into modules (name, projectID, description, startDate) \n" + "values ( @name, @pID, @desc, @start);"; myDB.addParam("@name", name); myDB.addParam("@pID", pID); myDB.addParam("@desc", description); myDB.addParam("@start", startDate); myDB.nonQuery(); }
/// <summary> /// Gets a DataSet with Projects assigned to a PM /// </summary> /// <param name="mgrID">Project Manager</param> /// <returns></returns> public static DataSet getProjectsDataSet(string mgrID) { DBDriver myDB = new DBDriver(); myDB.Query = "select * from projects \n" + "where managerID=@mgrID;"; myDB.addParam("@mgrID", mgrID); DataSet ds = new DataSet(); myDB.createAdapter().Fill(ds); return(ds); }
static public User approveNewUser(string ID) { DBDriver myDB = new DBDriver(); myDB.Query = "insert into users select * from newUsers where id=@id;"; myDB.addParam("@id", ID); myDB.nonQuery(); //need to delete said info from the newUsers table myDB.Query = "delete from newUsers where id=@id;"; myDB.addParam("@id", ID); myDB.nonQuery(); return(new User(ID)); }
/// <summary> /// Insert a Task in the database /// </summary> /// <param name="name"></param> /// <param name="mID">Module to add to</param> /// <param name="description"></param> /// <param name="startDate">Start date</param> public static void create(string name, string mID, string description, string startDate) { DBDriver myDB = new DBDriver(); myDB.Query = "insert into tasks (name, moduleID, description, startDate, complete) \n" + "values ( @name, @mID, @desc, @start, @complete );"; myDB.addParam("@name", name); myDB.addParam("@mID", mID); myDB.addParam("@desc", description); myDB.addParam("@start", startDate); myDB.addParam("@complete", PMT.TaskStatus.UNASSIGNED); myDB.nonQuery(); }
/// <summary> /// Gets a DataSet filled with a projects fields /// </summary> public DataSet getDataSet() { DBDriver myDB = new DBDriver(); myDB.Query = "select * from projects \n" + "where ID = @ID;"; myDB.addParam("@ID", id); DataSet ds = new DataSet(); myDB.createAdapter().Fill(ds); return(ds); }
bool CustomAuthenticate(string username, string password) { DBDriver db = new DBDriver(); string q = "select count(*) from softeng4.users where userName='******';"; db.Query = q; int k = (int)db.scalar(); if (k == 0) { //user does not exist in DB ErrorLabel.Text = "You have entered an unknown username."; return(false); } else { q = "select count(*) from softeng4.users u where u.userName='******' and u.password='******'"; db.Query = q; k = (int)db.scalar(); if (k == 0) { //password incorrect ErrorLabel.Text = "You have entered an incorrect password."; return(false); } else { //successful authentication q = "select u.security s, u.ID id, p.firstName fname, p.lastName lname from softeng4.users u, softeng4.person p where u.ID = p.ID and u.username='******'"; db.Query = q; SqlDataReader dr = db.createReader(); dr.Read(); user = new User(dr["id"].ToString()); db.close(); // create the cookie Response.Cookies["user"].Values.Add("role", user.Role); Response.Cookies["user"].Values.Add("id", user.ID); Response.Cookies["user"].Values.Add("name", user.UserName); Response.Cookies["user"].Values.Add("fname", user.FirstName); Response.Cookies["user"].Values.Add("lname", user.LastName); return(true); } } }
/// <summary> /// Update a Task /// </summary> /// <param name="name"></param> /// <param name="description"></param> /// <param name="startDate"></param> new public void update(string name, string description, string startDate) { base.update(name, description, startDate); DBDriver myDB = new DBDriver(); myDB.Query = "update tasks \n" + "set name=@name, description=@desc, startDate=@start\n" + "where id=@id;"; myDB.addParam("@id", this.id); myDB.addParam("@name", name); myDB.addParam("@desc", description); myDB.addParam("@start", startDate); myDB.nonQuery(); }
bool CustomAuthenticate(string username, string password) { DBDriver db = new DBDriver(); string q="select count(*) from softeng4.users where userName='******';"; db.Query = q; int k=(int)db.scalar(); if(k==0) { //user does not exist in DB ErrorLabel.Text = "You have entered an unknown username."; return false; } else { q="select count(*) from softeng4.users u where u.userName='******' and u.password='******'"; db.Query = q; k=(int)db.scalar(); if(k==0) { //password incorrect ErrorLabel.Text = "You have entered an incorrect password."; return false; } else { //successful authentication q="select u.security s, u.ID id, p.firstName fname, p.lastName lname from softeng4.users u, softeng4.person p where u.ID = p.ID and u.username='******'"; db.Query = q; SqlDataReader dr=db.createReader(); dr.Read(); user = new User(dr["id"].ToString()); db.close(); // create the cookie Response.Cookies["user"].Values.Add("role", user.Role); Response.Cookies["user"].Values.Add("id", user.ID); Response.Cookies["user"].Values.Add("name", user.UserName); Response.Cookies["user"].Values.Add("fname", user.FirstName); Response.Cookies["user"].Values.Add("lname", user.LastName); return true; } } }
public Task(string name, string moduleID, string description, string startDate, string compexity) : base(name, description, startDate) { DBDriver db = new DBDriver(); // Create Adapter db.Query = "select * from Tasks"; SqlDataAdapter da = db.createAdapter(); // We need this to get an ID back from the database da.MissingSchemaAction = MissingSchemaAction.AddWithKey; // Create and initialize CommandBuilder SqlCommandBuilder dbCB = new SqlCommandBuilder(da); // New DataSet DataSet ds = new DataSet(); // Populate DataSet with data da.Fill(ds, "Tasks"); // Get reference to our table DataTable table = ds.Tables["Tasks"]; // Create new row DataRow row = table.NewRow(); // Store data in the row row["Name"] = this.name = name; row["moduleID"] = this.modID = moduleID; row["description"] = this.description = description; row["startDate"] = this.startDate = startDate; row["complete"] = this.status = TaskStatus.UNASSIGNED; row["complexity"] = this.complexity = compexity; // Add row back to table table.Rows.Add(row); // Update data source da.Update(ds, "Tasks"); // Get newFileID if (!row.IsNull("ID")) { id = row["ID"].ToString(); } }
/// <summary> /// Create a new User /// </summary> /// <param name="un">The username</param> /// <param name="pwd">the Password</param> /// <param name="r">the security/role</param> /// <param name="fn">the first name</param> /// <param name="ln">the last name</param> /// <param name="em">the email address</param> /// <param name="ph">the phone number</param> /// <param name="ad">the address</param> /// <param name="c">the city</param> /// <param name="s">the state</param> /// <param name="z">thezip code</param> public User(string un, string pwd, string r, string fn, string ln, string em, string ph, string ad, string c, string s, string z) { this.userName = un; this.password = Encryption.encrypt(pwd); //this.id = id; this.role = r; this.firstName=fn; this.lastName=ln; this.email=em; this.phone=ph; this.address=ad; this.city=c; this.state=s; this.zip=z; myDB=new DBDriver(); myDB.Query = "insert into person (firstName, lastName, address, city, state, zip, phoneNumber, email) " + "values (@first,@last,@address,@city,@state,@zip,@phone,@email);"; myDB.addParam("@first", firstName); myDB.addParam("@last", lastName); myDB.addParam("@address", address); myDB.addParam("@city", city); myDB.addParam("@state", state); myDB.addParam("@zip", zip); myDB.addParam("@phone", phone); myDB.addParam("@email", email); myDB.nonQuery(); //get the user id from the person table to satisfy the user tables foreign constraint myDB.Query="select id from person where email=@email;"; myDB.addParam("@email", email); this.id=myDB.scalar().ToString(); // TODO: when administrator approves, this is transferred to the users table // for now, this is stored in the newUsers table myDB.Query = "insert into newUsers (ID, password, userName, security)\n" + "values (@id, @pwd,@username,@sec);"; myDB.addParam("@id", id); myDB.addParam("@pwd", this.password); myDB.addParam("@username", this.userName); myDB.addParam("@sec", this.role); myDB.nonQuery(); }
/// <summary> /// Create a new User /// </summary> /// <param name="un">The username</param> /// <param name="pwd">the Password</param> /// <param name="r">the security/role</param> /// <param name="fn">the first name</param> /// <param name="ln">the last name</param> /// <param name="em">the email address</param> /// <param name="ph">the phone number</param> /// <param name="ad">the address</param> /// <param name="c">the city</param> /// <param name="s">the state</param> /// <param name="z">thezip code</param> public User(string un, string pwd, string r, string fn, string ln, string em, string ph, string ad, string c, string s, string z) { this.userName = un; this.password = Encryption.encrypt(pwd); //this.id = id; this.role = r; this.firstName = fn; this.lastName = ln; this.email = em; this.phone = ph; this.address = ad; this.city = c; this.state = s; this.zip = z; myDB = new DBDriver(); myDB.Query = "insert into person (firstName, lastName, address, city, state, zip, phoneNumber, email) " + "values (@first,@last,@address,@city,@state,@zip,@phone,@email);"; myDB.addParam("@first", firstName); myDB.addParam("@last", lastName); myDB.addParam("@address", address); myDB.addParam("@city", city); myDB.addParam("@state", state); myDB.addParam("@zip", zip); myDB.addParam("@phone", phone); myDB.addParam("@email", email); myDB.nonQuery(); //get the user id from the person table to satisfy the user tables foreign constraint myDB.Query = "select id from person where email=@email;"; myDB.addParam("@email", email); this.id = myDB.scalar().ToString(); // TODO: when administrator approves, this is transferred to the users table // for now, this is stored in the newUsers table myDB.Query = "insert into newUsers (ID, password, userName, security)\n" + "values (@id, @pwd,@username,@sec);"; myDB.addParam("@id", id); myDB.addParam("@pwd", this.password); myDB.addParam("@username", this.userName); myDB.addParam("@sec", this.role); myDB.nonQuery(); }
/// <summary> /// Create a project object from the database /// </summary> /// <param name="id">a project id</param> public Project(string id) : base(id) { myDB = new DBDriver(); myDB.Query = "select * from projects \n" + "where ID = @ID;"; myDB.addParam("@ID", id); DataSet ds = new DataSet(); myDB.createAdapter().Fill(ds); DataTable table = ds.Tables[0]; DataRow row = table.Rows[0]; this.name = row["Name"].ToString(); this.id = row["id"].ToString(); this.mgrID = row["managerID"].ToString(); this.description = row["description"].ToString(); this.startDate = row["startDate"].ToString(); this.expEndDate = row["expEndDate"].ToString(); this.actEndDate = row["actEndDate"].ToString(); }
/// <summary> /// Create user from id /// </summary> /// <param name="id">User ID number</param> public User(string id) { // get user info from db this.id = id; DBDriver myDB = new DBDriver(); myDB.Query = "select * from person p, users u where p.id=@id and u.id=p.id;"; myDB.addParam("@id", this.id); SqlDataReader dr = myDB.createReader(); dr.Read(); this.userName = dr["userName"].ToString(); this.address = dr["address"].ToString(); this.city = dr["city"].ToString(); this.email = dr["email"].ToString(); this.firstName = dr["firstName"].ToString(); this.lastName = dr["lastName"].ToString(); this.password = dr["password"].ToString(); this.phone = dr["phoneNumber"].ToString(); this.role = dr["security"].ToString(); this.state = dr["state"].ToString(); this.zip = dr["zip"].ToString(); myDB.close(); }
/// <summary> /// Verify that a username exists in the database /// </summary> /// <param name="userName">the username to verify</param> /// <returns>true if it exists, false if it doesn't</returns> static public bool verifyUserNameExists(string userName, bool isNew) { DBDriver myDB = new DBDriver(); myDB.Query = "select count(*) from users where userName=@name;"; myDB.addParam("@name", userName); int k = Convert.ToInt32(myDB.scalar()); if (k != 1) { if (isNew) { myDB.Query = "select count(*) from newUsers where userName=@name;"; myDB.addParam("@name", userName); k = Convert.ToInt32(myDB.scalar()); } } if (k == 1) { return(true); } return(false); }
public Module(string name, string projectID, string description, string startDate) : base(name, description, startDate) { DBDriver db = new DBDriver(); // Create Adapter db.Query = "select * from Modules"; SqlDataAdapter da = db.createAdapter(); // We need this to get an ID back from the database da.MissingSchemaAction = MissingSchemaAction.AddWithKey; // Create and initialize CommandBuilder SqlCommandBuilder dbCB = new SqlCommandBuilder(da); // New DataSet DataSet ds = new DataSet(); // Populate DataSet with data da.Fill(ds, "Modules"); // Get reference to our table DataTable table = ds.Tables["Modules"]; // Create new row DataRow row = table.NewRow(); // Store data in the row row["Name"] = this.name = name; row["projectID"] = this.projID = projectID; row["description"] = this.description = description; row["startDate"] = this.startDate = startDate; // Add row back to table table.Rows.Add(row); // Update data source da.Update(ds, "Modules"); // Get newFileID if( !row.IsNull("ID") ) id = row["ID"].ToString(); }
public static void declineNewUser(string delID) { DBDriver myDB=new DBDriver(); myDB.Query="delete from newUsers where id=@id;"; myDB.addParam("@id", delID); myDB.nonQuery(); myDB.Query="delete from person where id=@id;"; myDB.addParam("@id", delID); myDB.nonQuery(); }
public static User approveNewUser(string ID) { DBDriver myDB=new DBDriver(); myDB.Query="insert into users select * from newUsers where id=@id;"; myDB.addParam("@id", ID); myDB.nonQuery(); //need to delete said info from the newUsers table myDB.Query="delete from newUsers where id=@id;"; myDB.addParam("@id", ID); myDB.nonQuery(); return new User(ID); }
/// <summary> /// Create user from id /// </summary> /// <param name="id">User ID number</param> public User(string id) { // get user info from db this.id=id; DBDriver myDB=new DBDriver(); myDB.Query="select * from person p, users u where p.id=@id and u.id=p.id;"; myDB.addParam("@id", this.id); SqlDataReader dr=myDB.createReader(); dr.Read(); this.userName=dr["userName"].ToString(); this.address=dr["address"].ToString(); this.city=dr["city"].ToString(); this.email=dr["email"].ToString(); this.firstName=dr["firstName"].ToString(); this.lastName=dr["lastName"].ToString(); this.password=dr["password"].ToString(); this.phone=dr["phoneNumber"].ToString(); this.role=dr["security"].ToString(); this.state=dr["state"].ToString(); this.zip=dr["zip"].ToString(); myDB.close(); }
/// <summary> /// Update a Task /// </summary> /// <param name="name"></param> /// <param name="description"></param> /// <param name="startDate"></param> public new void update(string name, string description, string startDate) { base.update(name, description, startDate); DBDriver myDB = new DBDriver(); myDB.Query="update tasks \n" +"set name=@name, description=@desc, startDate=@start\n" +"where id=@id;"; myDB.addParam("@id", this.id); myDB.addParam("@name", name); myDB.addParam("@desc", description); myDB.addParam("@start", startDate); myDB.nonQuery(); }
/// <summary> /// Assign a Developer /// </summary> /// <param name="dev"></param> public void assignDeveloper(string devID) { this.devID = devID; DBDriver db = new DBDriver(); db.Query = "insert into assignments (taskID, devID, dateAss)\n" + "values (@taskID, @devID, @date)"; db.addParam("@taskID", this.id); db.addParam("@devID", devID); db.addParam("@date", Convert.ToString(DateTime.Now)); db.nonQuery(); db.Query = "update tasks set complete = @complete\n" + "where ID = @taskID;"; db.addParam("@complete", PMT.TaskStatus.INPROGRESS); db.addParam("@taskID", this.id); db.nonQuery(); db.Query = "select competence from compLevels where ID = @devID"; db.addParam("@devID", devID); SqlDataReader dr = db.createReader(); dr.Read(); string competence = dr["competence"].ToString(); db.close(); string length; if( complexity == "Low" ) db.Query = "select lowComplexity as length from compmatrix where compLevel = @competence"; else if ( complexity == "Medium" ) db.Query = "select medComplexity as length from compmatrix where compLevel = @competence"; else if ( complexity == "High" ) db.Query = "select highComplexity as length from compmatrix where compLevel = @competence"; db.addParam("@competence", competence); dr = db.createReader(); dr.Read(); length = dr["length"].ToString(); db.close(); //TimeSpan temp = new TimeSpan(Convert.ToInt32(length), 0, 0, 0); DateTime start = Convert.ToDateTime(this.startDate); double hours = Convert.ToDouble(length); double days = Math.Ceiling(hours/8); DateTime end = start.AddDays(days); this.expEndDate = end.ToShortDateString(); db.Query = "update tasks set expEndDate = @expEndDate\n" + "where ID = @taskID;"; db.addParam("@expEndDate", this.expEndDate); db.addParam("@taskID", this.id); db.nonQuery(); //TODO // modid = ||select moduleid from tasks where id = @taskid; // //maximum = max of ||select tasks.expenddate from tasks where tasks.moduleid = @modid // //update modules set expenddate = @maximum where id = @modid // //projid = ||select projectid from modules where id = @modid // //maximum = max of ||select expenddate from modules where projectid = @projid // //update project set expenddate = @maximum where id = @projid }
/// <summary> /// Create a project object from the database /// </summary> /// <param name="id">a project id</param> public Project(string id) : base(id) { myDB = new DBDriver(); myDB.Query = "select * from projects \n" + "where ID = @ID;"; myDB.addParam("@ID", id); DataSet ds = new DataSet(); myDB.createAdapter().Fill(ds); DataTable table = ds.Tables[0]; DataRow row = table.Rows[0]; this.name = row["Name"].ToString(); this.id = row["id"].ToString(); this.mgrID = row["managerID"].ToString(); this.description = row["description"].ToString(); this.startDate = row["startDate"].ToString(); this.expEndDate = row["expEndDate"].ToString(); this.actEndDate = row["actEndDate"].ToString(); }
/// <summary> /// Update a user's profile. Maybe we could instead have properties for each /// member and update the database in the destructor ... /// NOTE: That would not be a good idea due to the garbage collection issues /// that I discovered with the DBDriver class. /// </summary> public void updateProfile() { //this isn't working... why? is my update wrong? //no, a multiple update like that works... //is it losing something elsewhere? DBDriver myDB=new DBDriver(); myDB.Query="update users set password=@pwd, security=@sec where id=@uID;"; //TODO: Add in changing username(need to verify availability) //myDB.addParam("@name", this.userName); myDB.addParam("@pwd", this.password); myDB.addParam("@sec", this.role); myDB.addParam("@uID", this.id); myDB.nonQuery(); myDB.Query="update person set firstName=@first, lastName=@last, address=@address, city=@city, " +"state=@state, zip=@zip, phoneNumber=@phone, email=@mail where id=@uID;"; myDB.addParam("@first", this.firstName); myDB.addParam("@last", this.lastName); myDB.addParam("@address", this.address); myDB.addParam("@city", this.city); myDB.addParam("@state", this.state); myDB.addParam("@zip", this.zip); myDB.addParam("@phone", this.phone); myDB.addParam("@mail", this.email); myDB.addParam("@uID", this.id); myDB.nonQuery(); }
/// <summary> /// Assign a Developer /// </summary> /// <param name="dev"></param> public void assignDeveloper(string devID) { this.devID = devID; DBDriver db = new DBDriver(); db.Query = "insert into assignments (taskID, devID, dateAss)\n" + "values (@taskID, @devID, @date)"; db.addParam("@taskID", this.id); db.addParam("@devID", devID); db.addParam("@date", Convert.ToString(DateTime.Now)); db.nonQuery(); db.Query = "update tasks set complete = @complete\n" + "where ID = @taskID;"; db.addParam("@complete", PMT.TaskStatus.INPROGRESS); db.addParam("@taskID", this.id); db.nonQuery(); db.Query = "select competence from compLevels where ID = @devID"; db.addParam("@devID", devID); SqlDataReader dr = db.createReader(); dr.Read(); string competence = dr["competence"].ToString(); db.close(); string length; if (complexity == "Low") { db.Query = "select lowComplexity as length from compmatrix where compLevel = @competence"; } else if (complexity == "Medium") { db.Query = "select medComplexity as length from compmatrix where compLevel = @competence"; } else if (complexity == "High") { db.Query = "select highComplexity as length from compmatrix where compLevel = @competence"; } db.addParam("@competence", competence); dr = db.createReader(); dr.Read(); length = dr["length"].ToString(); db.close(); //TimeSpan temp = new TimeSpan(Convert.ToInt32(length), 0, 0, 0); DateTime start = Convert.ToDateTime(this.startDate); double hours = Convert.ToDouble(length); double days = Math.Ceiling(hours / 8); DateTime end = start.AddDays(days); this.expEndDate = end.ToShortDateString(); db.Query = "update tasks set expEndDate = @expEndDate\n" + "where ID = @taskID;"; db.addParam("@expEndDate", this.expEndDate); db.addParam("@taskID", this.id); db.nonQuery(); //TODO // modid = ||select moduleid from tasks where id = @taskid; // //maximum = max of ||select tasks.expenddate from tasks where tasks.moduleid = @modid // //update modules set expenddate = @maximum where id = @modid // //projid = ||select projectid from modules where id = @modid // //maximum = max of ||select expenddate from modules where projectid = @projid // //update project set expenddate = @maximum where id = @projid }
/// <summary> /// Gets a DataSet filled with a projects fields /// </summary> public DataSet getDataSet() { DBDriver myDB = new DBDriver(); myDB.Query = "select * from projects \n" + "where ID = @ID;"; myDB.addParam("@ID", id); DataSet ds = new DataSet(); myDB.createAdapter().Fill(ds); return ds; }
/// <summary> /// Gets a DataSet with Projects assigned to a PM /// </summary> /// <param name="mgrID">Project Manager</param> /// <returns></returns> public static DataSet getProjectsDataSet(string mgrID) { DBDriver myDB = new DBDriver(); myDB.Query = "select * from projects \n" +"where managerID=@mgrID;"; myDB.addParam("@mgrID", mgrID); DataSet ds = new DataSet(); myDB.createAdapter().Fill(ds); return ds; }
/// <summary> /// Gets a DataSet with all Projects /// </summary> /// <returns></returns> public static DataSet getProjectsDataSet() { DBDriver myDB = new DBDriver(); myDB.Query = "select * from projects;"; DataSet ds = new DataSet(); myDB.createAdapter().Fill(ds); return ds; }
/// <summary> /// Insert a Project in the database /// </summary> /// <param name="name"></param> /// <param name="mID">Assigned manager</param> /// <param name="description"></param> /// <param name="startDate">Start date</param> public static void create(string name, string mID, string description, string startDate) { DBDriver myDB = new DBDriver(); myDB.Query="insert into projects (name, managerID, description, startDate) \n" +"values ( @name, @mID, @desc, @start, @end );"; myDB.addParam("@name", name); myDB.addParam("@mID", mID); myDB.addParam("@desc", description); myDB.addParam("@start", startDate); myDB.nonQuery(); }
/// <summary> /// Verify that an email address exists in the database /// </summary> /// <param name="email">email address to verify</param> /// <returns>true if it exists, false if it doesn't</returns> public static bool verifyEmailExists(string email) { DBDriver myDB=new DBDriver(); myDB.Query="select count(*) from person where email=@email;"; myDB.addParam("@email", email); int k=Convert.ToInt32(myDB.scalar()); if(k==1) return true; return false; }
/// <summary> /// Gets a DataSet of Tasks in the specified Module /// </summary> /// <param name="id">Module id</param> /// <returns>Tasks DataSet</returns> public static DataSet getTasksDataSet(string id) { DBDriver myDB = new DBDriver(); myDB.Query = "select * from tasks\n" + "where moduleID = @ID;"; myDB.addParam("@ID", id); DataSet ds = new DataSet(); myDB.createAdapter().Fill(ds); return ds; }
/// <summary> /// Verify that a username exists in the database /// </summary> /// <param name="userName">the username to verify</param> /// <returns>true if it exists, false if it doesn't</returns> public static bool verifyUserNameExists(string userName, bool isNew) { DBDriver myDB=new DBDriver(); myDB.Query="select count(*) from users where userName=@name;"; myDB.addParam("@name", userName); int k=Convert.ToInt32(myDB.scalar()); if(k!=1) if(isNew) { myDB.Query="select count(*) from newUsers where userName=@name;"; myDB.addParam("@name", userName); k=Convert.ToInt32(myDB.scalar()); } if(k==1) return true; return false; }
/// <summary> /// Remove a Task /// </summary> /// <param name="id">project id</param> public static void remove(string id) { DBDriver myDB = new DBDriver(); myDB.Query = "delete from Tasks where id=@id"; myDB.addParam("@id", id); myDB.nonQuery(); }
/// <summary> /// Insert a Task in the database /// </summary> /// <param name="name"></param> /// <param name="mID">Module to add to</param> /// <param name="description"></param> /// <param name="startDate">Start date</param> public static void create(string name, string mID, string description, string startDate) { DBDriver myDB = new DBDriver(); myDB.Query="insert into tasks (name, moduleID, description, startDate, complete) \n" +"values ( @name, @mID, @desc, @start, @complete );"; myDB.addParam("@name", name); myDB.addParam("@mID", mID); myDB.addParam("@desc", description); myDB.addParam("@start", startDate); myDB.addParam("@complete", PMT.TaskStatus.UNASSIGNED); myDB.nonQuery(); }
/// <summary> /// Insert a Module in the database /// </summary> /// <param name="name"></param> /// <param name="pID">Project to add to</param> /// <param name="description"></param> /// <param name="startDate">Start date</param> public static void create(string name, string pID, string description, string startDate) { DBDriver myDB = new DBDriver(); myDB.Query="insert into modules (name, projectID, description, startDate) \n" +"values ( @name, @pID, @desc, @start);"; myDB.addParam("@name", name); myDB.addParam("@pID", pID); myDB.addParam("@desc", description); myDB.addParam("@start", startDate); myDB.nonQuery(); }