public AccountManagerViewModel SetUserLevel(AccountManagerViewModel user)
{
AccountManagerViewModel myConn = user;
try
{
using (SqlConnection con = new SqlConnection(QuToolsUtilities.GetConnectionString("internet")))
{
con.Open();
string sqlstr = "UPDATE {0} SET userlevel=@ul";
sqlstr = String.Format(sqlstr, user.UserTable);
SqlCommand da = new SqlCommand(sqlstr, con);
da.Parameters.AddWithValue("@ul", user.Userlevel);
da.ExecuteNonQuery();
}
myConn.ReturnMsg = "User Level updated";
return(myConn);
}
catch (Exception e)
{
myConn.ReturnMsg = e.Message;
return(myConn);
}
}
} //end of checkAdmin()
public AccountManagerViewModel CheckUser(AccountManagerViewModel user)
{
AccountManagerViewModel myConn = user;
string sqlCmd;
try
{
using (SqlConnection connection = new SqlConnection(QuToolsUtilities.GetConnectionString("internet"))) //ConfigurationExtensions.GetConnectionString(ConfigurationString, "internet")))
{
connection.Open();
string username = QuToolsUtilities.GetSession("QUToolsUser"); // HttpContext.Session.GetString("QUToolsUser"); // HttpContext.Current.Session["QUToolsUser"].ToString();
sqlCmd = "select * from {0} where QU_User = @qun";
sqlCmd = String.Format(sqlCmd, user.UserTable);
SqlCommand command = new SqlCommand(sqlCmd, connection);
command.Parameters.AddWithValue("@qun", user.Username);
//command.Parameters.AddWithValue("@db", User.userTable);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["QU_User"].ToString() == username && reader["toolAdmin"].ToString() == "N")
{
myConn.Admin = "No";
myConn.Username = username;
myConn.Userlevel = reader["userlevel"].ToString();
return(myConn);
}
else
{
myConn.Admin = "Yes";
myConn.Username = username;
myConn.Userlevel = reader["userlevel"].ToString();
return(myConn);
}
}
}
connection.Close();
}
}
catch (Exception e)
{
myConn.ReturnMsg = e.Message;
return(myConn);
}
return(myConn);
} //end of checkUser()
} //end of checkUser()
public string DelUser(string deluser, string userTable)
{
try
{
using (SqlConnection con = new SqlConnection(QuToolsUtilities.GetConnectionString("internet")))
{
con.Open();
string sqlstr = "DELETE from {0} where QU_User=@un";
sqlstr = String.Format(sqlstr, userTable);
SqlCommand da = new SqlCommand(sqlstr, con);
da.Parameters.AddWithValue("@un", deluser);
da.ExecuteNonQuery();
}
string returnmsg = "Deleted {0}";
returnmsg = String.Format(returnmsg, deluser);
return(returnmsg);
}
catch (Exception e)
{
return(e.Message);
}
}
private static List <HomeViewModel> GetTags(string category)
{
List <HomeViewModel> generalTagList = new List <HomeViewModel>();
string tagGroup = category.Replace("QUTool_BSIC_Vid_", "");
try
{
using (SqlConnection connection = new SqlConnection(QuToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
string sqlCmd = "select * from {0} order by tagname";
sqlCmd = String.Format(sqlCmd, category);
SqlCommand command = new SqlCommand(sqlCmd, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
generalTagList.Add(new HomeViewModel
{
TagId = reader["tagID"].ToString(),
Tag = reader["tagname"].ToString(),
TagGroup = tagGroup
});
}
}
connection.Close();
}
}
catch
{
return(generalTagList);
}
return(generalTagList);
}
public AccountManagerViewModel GetUsers(AccountManagerViewModel user)
{
AccountManagerViewModel users = user;
users.Userlist = new List <AccountManagerViewModel>();
try
{
using (SqlConnection con = new SqlConnection(QuToolsUtilities.GetConnectionString("internet")))
{
con.Open();
string sqlcmd = "SELECT * FROM {0}";
sqlcmd = String.Format(sqlcmd, user.UserTable);
SqlCommand da = new SqlCommand(sqlcmd, con);
using (SqlDataReader reader = da.ExecuteReader())
{
while (reader.Read())
{
users.Userlist.Add(new AccountManagerViewModel
{ /// Note: Not sure why i'm using List_user quid etc...
ListUser = reader["QU_User"].ToString(),
ListQuid = reader["QUID"].ToString(),
ListAdmin = reader["toolAdmin"].ToString(),
ListUserlevel = reader["userlevel"].ToString()
});
}
return(users);
}
}
}
catch (Exception e)
{
users.ReturnMsg = e.Message;
return(users);
}
}
public AccountManagerViewModel CheckAdmin(AccountManagerViewModel user)
{
AccountManagerViewModel myConn = user; //new AccountManager();
Login myLogin = new Login();
string sqlCmd;
using (SqlConnection connection = new SqlConnection(QuToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
string username = myLogin.ReturnUsername();
sqlCmd = "select * from {0} where QU_User = @qun";
sqlCmd = String.Format(sqlCmd, user.UserTable); //, User.username);
SqlCommand command = new SqlCommand(sqlCmd, connection);
command.Parameters.AddWithValue("@qun", user.Username);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["QU_User"].ToString() == username && reader["toolAdmin"].ToString() == "Y")
{
myConn.Admin = "Yes";
myConn.Userlevel = reader["userlevel"].ToString();
return(myConn);
}
}
}
connection.Close();
}
return(myConn);
} //end of checkAdmin()
public HomeViewModel Search(HomeViewModel searchInfo)
{
if (searchInfo.SearchRawTags != null)
{
searchInfo = ParseTags(searchInfo);
}
else
{
searchInfo.SearchRawTags = "0";
}
string sqlTmpDb = @"
IF Exists (Select * From tempdb..sysobjects Where name like '#QUToolsTemp') DROP TABLE #QUToolsTemp
select * INTO #QUToolsTemp
from (
select sv.VidID,s.tagID, s.tagname, 'school' as 'grouper' from QUTool_BSIC_Vid_SchoolVids sv
left join QUTool_BSIC_Vid_School s on s.tagID = sv.tagID
union
select pv.VidID, p.tagID, p.tagname, 'program' as 'grouper' from QUTool_BSIC_Vid_ProgramVids pv
left join QUTool_BSIC_Vid_Program p on p.tagID = pv.tagID
union
select plv.VidID, pl.tagID, pl.tagname, 'pillar' as 'grouper' from QUTool_BSIC_Vid_PillarVids plv
left join QUTool_BSIC_Vid_Pillars pl on pl.tagID = plv.tagID
union
select ov.VidID, o.tagID, o.tagname, 'othertags' as 'grouper' from QUTool_BSIC_Vid_OtherTagsVids ov
left join QUTool_BSIC_Vid_OtherTags o on o.tagID = ov.tagID
union
select gv.VidID, g.tagID, g.tagname, 'geo' as 'grouper' from QUTool_BSIC_Vid_GeoVids gv
left join QUTool_BSIC_Vid_Geo g on g.tagID = gv.tagID
union
select ev.VidID, e.tagID, e.tagname, 'events' as 'grouper' from QUTool_BSIC_Vid_EventsVids ev
left join QUTool_BSIC_Vid_Events e on e.tagID = ev.tagID
union
select av.VidID, a.tagID, a.tagname av, 'athletics' as 'grouper' from QUTool_BSIC_Vid_AthleticsVids av
left join QUTool_BSIC_Vid_Athletics a on a.tagID = av.tagID
) data";
string sqlstr = BuildSqlStr(searchInfo);
try
{
using (SqlConnection connection = new SqlConnection(QuToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
SqlCommand command1 = new SqlCommand(sqlTmpDb, connection);
command1.ExecuteNonQuery();
SqlCommand command = new SqlCommand(sqlstr, connection);
searchInfo.SearchResults = new List <HomeViewModel>();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
searchInfo.SearchResults.Add(new HomeViewModel
{
VideoName = WebUtility.HtmlDecode(reader["VideoName"].ToString()),
VideoDescription = WebUtility.HtmlDecode(reader["VideoDescription"].ToString()),
Url = reader["URL"].ToString(),
DateCompleted = Convert.ToDateTime(reader["DateCompleted"]),
Duration = reader["Duration"].ToString(),
VideoType = reader["VideoType"].ToString(),
VidId = reader["VidID"].ToString()
});
}
}
connection.Close();
}
}
catch (Exception e)
{
searchInfo.ReturnMsg = e.Message;
return(searchInfo);
}
return(searchInfo);
}
public AccountManagerViewModel AddUser(AccountManagerViewModel user)
{
//Add the user to DB
AccountManagerViewModel returnMsg = user;
string username = user.Username.ToString();
username = username.ToLower();
string userTable = user.UserTable.ToString();
string quId = user.QuId.ToString();
string userlevel = "Z";
if (user.Userlevel != null)
{
userlevel = user.Userlevel.ToString();
}
string adminUser;
if (user.Admin == "Yes")
{
adminUser = "******";
}
else
{
adminUser = "******";
}
// Check to see if username is in datatel
try
{
using (SqlConnection connection = new SqlConnection(QuToolsUtilities.GetConnectionString("datatel")))
{
connection.Open();
string sqlCmd = "select * from person where NTLoginID = @un";
SqlCommand command = new SqlCommand(sqlCmd, connection);
command.Parameters.AddWithValue("@un", username);
using (SqlDataReader reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
returnMsg.ReturnMsg = "Invalid User";
return(returnMsg);
}
}
connection.Close();
}
}
catch (Exception e)
{
returnMsg.ReturnMsg = e.ToString();
return(returnMsg);
}
// Check if user is already in database
try
{
using (SqlConnection connection = new SqlConnection(QuToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
string sqlCmd = "select * from {0} where QU_User = @un";
sqlCmd = String.Format(sqlCmd, userTable);
SqlCommand command = new SqlCommand(sqlCmd, connection);
command.Parameters.AddWithValue("@un", username);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["QU_User"].ToString() == user.Username)
{
connection.Close();
returnMsg.ReturnMsg = "User already Exists.";
return(returnMsg);
}
}
}
connection.Close();
}
}
catch (Exception e)
{
returnMsg.ReturnMsg = e.Message;
return(returnMsg);
}
if (adminUser == "Y" && userlevel != "Z")
{
userlevel = "1111";
}
// All went well... Lets add the user into the table.
string template = "INSERT INTO {0} (QUID, toolAdmin, userlevel, QU_User) VALUES (@qID, @admin, @userlevel, @un)";
template = String.Format(template, user.UserTable);
try
{
using (SqlConnection connection = new SqlConnection(QuToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
using (SqlCommand command = new SqlCommand(template, connection))
{
command.Parameters.AddWithValue("@qID", quId);
command.Parameters.AddWithValue("@admin", adminUser);
command.Parameters.AddWithValue("@userlevel", userlevel);
command.Parameters.AddWithValue("@un", username);
using (SqlDataReader reader = command.ExecuteReader())
connection.Close();
}
}
}
catch (Exception e)
{
returnMsg.ReturnMsg = e.Message;
return(returnMsg);
}
returnMsg.ReturnMsg = "Success";
return(returnMsg);
}