public static DataRow[] getAllOwnedProjectsBySearch(string username, string search)
{
return(Dao.Execute("select project.id, project.name, project.description, account.username from project" +
" inner join user_project_role on project.id = user_project_role.project_id" +
" inner join account on account.auth_id = user_project_role.user_id" +
" where account.username = '******' and project.name like '%" + search + "%' and user_project_role.role_id = '1'"));
}
public static DataRow[] getAllProjectsByUsername(string username)
{
return(Dao.Execute("select project.id, project.name, project.description, account.username from project" +
" inner join user_project_role on project.id = user_project_role.project_id" +
" inner join account on account.auth_id = user_project_role.user_id" +
" where account.username = '******'"));
}
public static void createProject(string userId, string projectName, string projectDescription)
{
string project_id = Dao.Execute("insert into project (name, description) values('" + projectName + "', '" + projectDescription + "');" +
"SELECT SCOPE_IDENTITY();")[0][0].ToString();
Dao.Execute("insert into user_project_role(user_id, project_id, role_id) values('" + userId + "', '" + project_id + "', 1)");
}
public static bool usernameExists(string username)
{
DataRow[] rows = Dao.Execute("select username from account where username = '******'");
if (rows.Length == 0)
{
return(false);
}
return(true);
}
public static string getUserIdByName(string userName)
{
var row = Dao.Execute("select auth_id from account where username = '******'");
if (row.Length == 0)
{
return(null);
}
return(row[0][0].ToString());
}
public static string getRole(string userId, string projectName)
{
var row = Dao.Execute("select role_id from user_project_role inner join project on user_project_role.project_id = project.id where user_id = '" + userId + "' and project.name = '" + projectName + "'");
if (row.Length == 0)
{
return(null);
}
return(row[0][0].ToString());
}
public static DataRow[] getUsers(string project_id)
{
var row = Dao.Execute("select username, user_project_role.role_id, auth_id from account inner join user_project_role on user_project_role.user_id = account.auth_id" +
" where user_project_role.project_id = '" + project_id + "'");
if (row.Length == 0)
{
return(null);
}
return(row);
}
public static void removeUserFromProject(string user_id, string project_id)
{
Dao.Execute("delete from user_project_role where user_id = '" + user_id + "' and project_id = " + project_id + " and role_id != 1");
}
public static void addUserToProject(string user_id, string user_role, string project_id)
{
Dao.Execute("insert into user_project_role(user_id, project_id, role_id) values('" + user_id + "', '" + project_id + "', " + user_role + ")");
}
public static void register(string userId, string username)
{
Dao.Execute("insert into account (auth_id, username) values('" + userId + "', '" + username + "')");
}
