public static void DeleteWorkflowComponent(int wfcId)
{
MySqlCommand cmd = new MySqlCommand("DELETE FROM WorkflowComponents WHERE WFComponentID=@wfcId");
cmd.Parameters.AddWithValue("@wfcId", wfcId);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
}
public static void ValidLogin(User user)
{
string validQuery = "UPDATE Users SET InvalidLoginCt = 0 where Email = @email";
MySqlCommand cmd = new MySqlCommand(validQuery);
cmd.Parameters.AddWithValue("@email", user.Email);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
}
public static Company CreateCompany(string companyName)
{
Company c = new Company(companyName);
MySqlCommand cmd = new MySqlCommand("INSERT INTO Company (CompanyName) VALUES (@companyName)");
cmd.Parameters.AddWithValue("@companyName", companyName);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
return(c);
}
public static void CreateFeedItem(string feedText, int userId)
{
MySqlCommand cmd = new MySqlCommand("INSERT INTO ActivityFeed (FeedItemText, UserID) VALUES (@feedText, @userId)");
cmd.Parameters.AddWithValue("@feedText", feedText);
cmd.Parameters.AddWithValue("@userId", userId);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
UserUtil.SendEmail(userId, feedText);
}
public static WorkflowModel CreateWorkflow(string workflowName)
{
WorkflowModel w = new WorkflowModel(workflowName);
MySqlCommand cmd = new MySqlCommand("INSERT INTO Workflows (WorkflowName) VALUES (@workflowName)");
cmd.Parameters.AddWithValue("@workflowName", workflowName);
DBConn conn = new DBConn();
w.WorkflowId = conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
return(w);
}
public static WorkflowComponent CreateWorkflowComponent(int workflowID)
{
WorkflowComponent w = new WorkflowComponent(workflowID);
MySqlCommand cmd = new MySqlCommand("INSERT INTO WorkflowComponents(WorkflowID, ComponentTitle, ComponentText) VALUES (@workflowID, '', '')");
cmd.Parameters.AddWithValue("@workflowID", workflowID);
DBConn conn = new DBConn();
w.WFComponentID = conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
return(w);
}
public static void UpdateWorkflowComponent(int wfcId, string title, int formId)
{
MySqlCommand cmd = new MySqlCommand("UPDATE WorkflowComponents SET ComponentTitle=@title, FormID=@formId WHERE WFComponentID=@wfcId");
cmd.Parameters.AddWithValue("@wfcId", wfcId);
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@formId", formId);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
}
public static Form CreateFormTemplate(string formName, string formData)
{
Form f = new Form(formName);
MySqlCommand cmd = new MySqlCommand("INSERT INTO FormTemplates (FormName, FormData) VALUES (@formName, @formData)");
cmd.Parameters.AddWithValue("@formName", formName);
cmd.Parameters.AddWithValue("@formData", formData);
DBConn conn = new DBConn();
f.FormId = conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
return(f);
}
public static void UpdateWorkflow(int workflowId, string workflowName)
{
string query = "UPDATE Workflows SET WorkflowName=@workflowName WHERE WorkflowID=@workflowId";
MySqlCommand cmd = new MySqlCommand(query);
cmd.Parameters.AddWithValue("@workflowId", workflowId);
cmd.Parameters.AddWithValue("@workflowName", workflowName);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
}
public static void AdminPanelToggle(User user, int toggle)
{
string validQuery = "UPDATE Users SET AdminPanelToggle = @toggle where Email = @email";
MySqlCommand cmd = new MySqlCommand(validQuery);
cmd.Parameters.AddWithValue("@email", user.Email);
cmd.Parameters.AddWithValue("@toggle", toggle);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
}
public static Form UpdateFormTemplateApprovers(int formId, string approverIDs)
{
Form f = GetFormTemplate(formId);
MySqlCommand cmd = new MySqlCommand("UPDATE FormTemplates SET ApproverIDs=@approverIDs WHERE FormTemplateID=@formId");
cmd.Parameters.AddWithValue("@approverIDs", approverIDs);
cmd.Parameters.AddWithValue("@formId", formId);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
return(f);
}
public static WorkflowComponent CreateWorkflowComponent(int workflowID, string componenttitle, int formId)
{
WorkflowComponent w = new WorkflowComponent(workflowID, componenttitle, formId);
MySqlCommand cmd = new MySqlCommand("INSERT INTO WorkflowComponents(WorkflowID, ComponentTitle, FormID) VALUES (@workflowID, @componenttitle, @formId)");
cmd.Parameters.AddWithValue("@workflowID", workflowID);
cmd.Parameters.AddWithValue("@componenttitle", componenttitle);
cmd.Parameters.AddWithValue("@formId", formId);
DBConn conn = new DBConn();
w.WFComponentID = conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
return(w);
}
public static Form UpdateFormTemplate(int formId, string formName, string formData)
{
Form f = new Form(formId, formName, formData);
MySqlCommand cmd = new MySqlCommand("UPDATE FormTemplates SET FormName=@formName, FormData=@formData WHERE FormTemplateID=@formId");
cmd.Parameters.AddWithValue("@formName", formName);
cmd.Parameters.AddWithValue("@formData", formData);
cmd.Parameters.AddWithValue("@formId", formId);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
return(f);
}
public static void UpdateUser(int userID, int roleID, String fname, String lname)
{
string query = "UPDATE Users SET RoleID = @roleID, FirstName = @fname, LastName = @lname WHERE UserID = @userID";
MySqlCommand cmd = new MySqlCommand(query);
cmd.Parameters.AddWithValue("@roleID", roleID);
cmd.Parameters.AddWithValue("@fname", fname);
cmd.Parameters.AddWithValue("@lname", lname);
cmd.Parameters.AddWithValue("@userID", userID);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
}
public static Form UpdateFormFile(Form f, string path, string localPath)
{
f.FilePath = path;
f.LocalPath = localPath;
MySqlCommand cmd = new MySqlCommand("UPDATE Forms SET FilePath=@path, UploadedFileName=@localPath WHERE FormID=@formId");
cmd.Parameters.AddWithValue("@path", path);
cmd.Parameters.AddWithValue("@localPath", localPath);
cmd.Parameters.AddWithValue("@formId", f.FormId);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
return(f);
}
public static List <User> GetUsers()
{
string createQuery = "SELECT UserID, RoleID, CompanyID, Token, Email, FirstName, LastName, InvalidLoginCt, AdminPanelToggle from Users";
MySqlCommand cmd = new MySqlCommand(createQuery);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
List <User> users = new List <User>();
while (dr.Read())
{
User u = new User((int)dr["UserID"], (int)dr["RoleID"], (int)dr["CompanyID"], (string)dr["Email"], (string)dr["FirstName"], (string)dr["LastName"], (int)dr["InvalidLoginCt"], (int)dr["AdminPanelToggle"]);
users.Add(u);
}
conn.CloseConnection();
return(users);
}
public static Form GetFormTemplate(int formId)
{
MySqlCommand cmd = new MySqlCommand("SELECT FormTemplateID, FormName, FormData, ApproverIDs FROM FormTemplates WHERE FormTemplateID = @formId");
cmd.Parameters.AddWithValue("@formId", formId);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
Form f = null;
while (dr.Read())
{
f = new Form((int)dr["FormTemplateID"], (string)dr["FormName"], (string)dr["FormData"], (string)dr["ApproverIDs"]);
}
conn.CloseConnection();
return(f);
}
public static Project UpdateProject(int projectId, string projectName, int companyId, int coachId, string projectNotes)
{
string query = "UPDATE Project SET ProjectName=@projectName, CompanyID=@companyId, CoachID=@coachId, ProjectNotes=@projectNotes where ProjectID = @projectId";
MySqlCommand cmd = new MySqlCommand(query);
cmd.Parameters.AddWithValue("@projectName", projectName);
cmd.Parameters.AddWithValue("@companyId", companyId);
cmd.Parameters.AddWithValue("@coachId", coachId);
cmd.Parameters.AddWithValue("@projectNotes", projectNotes);
cmd.Parameters.AddWithValue("@projectId", projectId);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
return(GetProject(projectId));
}
public static Form SubmitForm(int formId, string formName, string formData)
{
string defaultApproved = "0";
string defaultDenied = "0";
Form f = GetForm(formId);
Form ft = GetFormTemplate(f.FormTemplateId);
string[] approvalIDs = new string[0];
if (ft.ApproverIDs.Length > 0)
{
approvalIDs = ft.ApproverIDs.Split(',');
}
if (approvalIDs.Length > 0)
{
int i = 0;
foreach (string roleID in approvalIDs)
{
if (i != 0)
{
defaultApproved += ",0";
defaultDenied += ",0";
}
i++;
}
}
f.FormName = formName;
f.FormData = formData;
MySqlCommand cmd = new MySqlCommand("UPDATE Forms SET FormName=@formName, FormData=@formData, Submission=1, Approved=@approved, Denied=@denied, DenialReason=\"\" WHERE FormID=@formId");
cmd.Parameters.AddWithValue("@formName", formName);
cmd.Parameters.AddWithValue("@formData", formData);
cmd.Parameters.AddWithValue("@formId", formId);
cmd.Parameters.AddWithValue("@denied", defaultDenied);
cmd.Parameters.AddWithValue("@approved", defaultApproved);
DBConn conn = new DBConn();
conn.ExecuteInsertCommand(cmd);
conn.CloseConnection();
Project p = ProjectUtil.GetProject(f.ProjectId);
FeedUtil.CreateProjectFormFeedItem(p.Name + " has form " + formName + " ready for your approval", p.CoachId, p.ProjectId, formId);
return(f);
}
public static List <Company> GetClientCompanies()
{
string query = "SELECT CompanyID, CompanyName from Company where CompanyID != 1 AND CompanyID != -2";
MySqlCommand cmd = new MySqlCommand(query);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
List <Company> companyList = new List <Company>();
while (dr.Read())
{
Company c = new Company((int)dr["CompanyID"], (string)dr["CompanyName"]);
companyList.Add(c);
}
conn.CloseConnection();
return(companyList);
}
public static List <Project> GetWorkflowProjects(int workflowId)
{
MySqlCommand cmd = new MySqlCommand("SELECT ProjectID, WorkflowID, CompanyID, StatusID, CoachID, ProjectName, ProjectNotes from Project WHERE WorkflowID = @workflowId");
cmd.Parameters.AddWithValue("@workflowId", workflowId);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
List <Project> projectList = new List <Project>();
while (dr.Read())
{
Project p = new Project((int)dr["ProjectID"], (int)dr["WorkflowID"], (int)dr["CompanyID"], (int)dr["StatusID"], (int)dr["CoachID"], (string)dr["ProjectName"], (string)dr["ProjectNotes"]);
projectList.Add(p);
}
conn.CloseConnection();
return(projectList);
}
public static List <Project> GetProjects()
{
string query = "SELECT ProjectID, WorkflowID, CompanyID, StatusID, CoachID, ProjectName, ProjectNotes from Project ORDER BY ProjectID DESC";
MySqlCommand cmd = new MySqlCommand(query);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
List <Project> projectList = new List <Project>();
while (dr.Read())
{
Project p = new Project((int)dr["ProjectID"], (int)dr["WorkflowID"], (int)dr["CompanyID"], (int)dr["StatusID"], (int)dr["CoachID"], (string)dr["ProjectName"], (string)dr["ProjectNotes"]);
projectList.Add(p);
}
conn.CloseConnection();
return(projectList);
}
//INCLUDES THINGS LIKE --SELECT FORM--
public static List <Form> GetAllFormTemplates()
{
string query = "SELECT FormTemplateID, FormName, FormData from FormTemplates";
MySqlCommand cmd = new MySqlCommand(query);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
List <Form> formList = new List <Form>();
while (dr.Read())
{
Form f = new Form((int)dr["FormTemplateID"], (string)dr["FormName"], (string)dr["FormData"]);
formList.Add(f);
}
conn.CloseConnection();
return(formList);
}
/*
* public static User CreateUser(int groupId, string token, string email, string firstName, string lastName)
* {
* User u = new User(groupId, token, email, firstName, lastName);
* string createQuery = "INSERT INTO Users (RoleID, Token, Email, FirstName, LastName) VALUES (@roleId,@token,@email,@firstName,@lastName)";
*
* SqlCommand cmd = new SqlCommand(createQuery);
* cmd.Parameters.AddWithValue("@roleId", roleId);
* cmd.Parameters.AddWithValue("@token", token);
* cmd.Parameters.AddWithValue("@email", email);
* cmd.Parameters.AddWithValue("@firstName", firstName);
* cmd.Parameters.AddWithValue("@lastName", lastName);
* cmd.Prepare();
*
* DBConn conn = new DBConn();
* conn.ExecuteInsertCommand(cmd);
*
* return u;
* }
*/
public static User GetUser(string email)
{
string createQuery = "SELECT UserID, RoleID, CompanyID, Token, Email, FirstName, LastName, InvalidLoginCt, AdminPanelToggle from Users where Email = @email";
MySqlCommand cmd = new MySqlCommand(createQuery);
cmd.Parameters.AddWithValue("@email", email);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
User u = null;
while (dr.Read())
{
u = new User((int)dr["UserID"], (int)dr["RoleID"], (int)dr["CompanyID"], (string)dr["Email"], (string)dr["FirstName"], (string)dr["LastName"], (int)dr["InvalidLoginCt"], (int)dr["AdminPanelToggle"]);
}
conn.CloseConnection();
return(u);
}
public static WorkflowModel GetWorkflow(int workflowId)
{
string query = "SELECT WorkflowID, WorkflowName from Workflows WHERE WorkflowID = @workflowId";
MySqlCommand cmd = new MySqlCommand(query);
cmd.Parameters.AddWithValue("@workflowId", workflowId);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
WorkflowModel workflow = null;
while (dr.Read())
{
workflow = new WorkflowModel((int)dr["WorkflowID"], (string)dr["WorkflowName"]);
}
conn.CloseConnection();
return(workflow);
}
//INCLUDES WORKFLOWS LIKE '--SELECT WORKFLOW--'
public static List <WorkflowModel> GetAllWorkflows()
{
string query = "SELECT WorkflowID, WorkflowName from Workflows";
MySqlCommand cmd = new MySqlCommand(query);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
List <WorkflowModel> workflowList = new List <WorkflowModel>();
while (dr.Read())
{
WorkflowModel w = new WorkflowModel((int)dr["WorkflowID"], (string)dr["WorkflowName"]);
workflowList.Add(w);
}
conn.CloseConnection();
return(workflowList);
}
public static List <Role> GetRoles()
{
string query = "SELECT RoleID, RoleName from Roles";
MySqlCommand cmd = new MySqlCommand(query);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
List <Role> roleList = new List <Role>();
while (dr.Read())
{
Role r = new Role((int)dr["RoleID"], (string)dr["RoleName"]);
roleList.Add(r);
}
conn.CloseConnection();
return(roleList);
}
public static Company GetCompany(int companyId)
{
string query = "SELECT CompanyID, CompanyName from Company WHERE CompanyID=@companyId";
MySqlCommand cmd = new MySqlCommand(query);
cmd.Parameters.AddWithValue("@companyId", companyId);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
Company c = null;
while (dr.Read())
{
c = new Company((int)dr["CompanyID"], (string)dr["CompanyName"]);
}
conn.CloseConnection();
return(c);
}
public static List <User> GetClients(int companyId)
{
string createQuery = "SELECT UserID, RoleID, CompanyID, Token, Email, FirstName, LastName, InvalidLoginCt, AdminPanelToggle from Users where CompanyID = @companyId";
MySqlCommand cmd = new MySqlCommand(createQuery);
cmd.Parameters.AddWithValue("@companyId", companyId);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
List <User> clientList = new List <User>();
while (dr.Read())
{
User u = new User((int)dr["UserID"], (int)dr["RoleID"], (int)dr["CompanyID"], (string)dr["Email"], (string)dr["FirstName"], (string)dr["LastName"], (int)dr["InvalidLoginCt"], (int)dr["AdminPanelToggle"]);
clientList.Add(u);
}
conn.CloseConnection();
return(clientList);
}
public static Project GetProject(int projectId)
{
string query = "SELECT ProjectID, WorkflowID, CompanyID, StatusID, CoachID, ProjectName, ProjectNotes from Project where ProjectID = @projectId";
MySqlCommand cmd = new MySqlCommand(query);
cmd.Parameters.AddWithValue("@projectId", projectId);
DBConn conn = new DBConn();
MySqlDataReader dr = conn.ExecuteSelectCommand(cmd);
Project p = null;
while (dr.Read())
{
p = new Project((int)dr["ProjectID"], (int)dr["WorkflowID"], (int)dr["CompanyID"], (int)dr["StatusID"], (int)dr["CoachID"], (string)dr["ProjectName"], (string)dr["ProjectNotes"]);
}
conn.CloseConnection();
return(p);
}