public string addNewTag2db(string[] tag)
{
VideoBackendViewModel allTags = new VideoBackendViewModel();
allTags = getAllTags(allTags);
try
{
using (SqlConnection connection = new SqlConnection(QUToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
string sqlCmd = @"INSERT INTO {0} (tagname)
VALUES (@tagname);" ;
sqlCmd = String.Format(sqlCmd, tag[1]);
SqlCommand command = new SqlCommand(sqlCmd, connection);
command.Parameters.Add("@tagname", SqlDbType.VarChar).Value = tag[2];
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception e)
{
return(e.Message);
}
return("Success");
}
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);
}
}
public string returnUsername()
{
try
{
return(QUToolsUtilities.GetSession("QToolsUser"));
}
catch
{
return("None");
}
}
} //end of checkAdmin()
public AccountManagerViewModel checkUser(AccountManagerViewModel User)
{
AccountManagerViewModel myConn = User;
string sqlCmd;
try
{
using (SqlConnection connection = new SqlConnection(QUToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
string username = QUToolsUtilities.GetSession("QToolsUser");
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()
public bool checkSession()
{
try
{
if (QUToolsUtilities.GetSession("QTools") != hashIt(QUToolsUtilities.GetSession("QTools"), "XXccXXddDDddDddddxXdd"))
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
public bool addTag2db(string[] tag, string vidID)
{
VideoBackendViewModel allTags = new VideoBackendViewModel();
allTags = getAllTags(allTags);
/*var theTag = allTags.SchoolTags.First(x => x.Tag_ID == tag[1]);
*
* if (theTag.Tag_ID == null)
* return false;
*/
try
{
using (SqlConnection connection = new SqlConnection(QUToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
string sqlCmd = @"INSERT INTO {0} (vidID, tagID)
VALUES (@vidID, @tagID);" ;
sqlCmd = String.Format(sqlCmd, tag[0]);
SqlCommand command = new SqlCommand(sqlCmd, connection);
int tagID = Convert.ToInt16(tag[1]);
command.Parameters.Add("@vidID", SqlDbType.VarChar).Value = vidID;
command.Parameters.Add("@tagID", SqlDbType.Int).Value = tagID;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception e)
{
return(false);
}
return(true);
}
//QUToolsUtilities QUToolsUtilities = new QUToolsUtilities();
public login(login data)
{
if (data.Username != null && data.Password != null)
{
data.valid = ValidateUser(data.Username, data.Password);
data.hashstring = hashIt(data.Username, "XXccXXddDDddDddddxXdd");
if (data.valid != false)
{
QUToolsUtilities.SetSession("QTools", data.hashstring);
QUToolsUtilities.SetSession("QToolsUser", data.Username);
}
}
else
{
data.valid = false;
VideoBackendController.loginerror += "101";
}
}
} //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);
}
}
public string addVideo2db(VideoBackendViewModel videoInfo, string vidID, string username)
{
//try
//{
using (SqlConnection connection = new SqlConnection(QUToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
string sqlCmd = "INSERT INTO QUTool_BSIC_Vid_VideoList (VidID, VideoName, VideoDescription, URL, DateCompleted, Duration, VideoType, AddedDate, AddedBy)" +
"VALUES (@vidID, @VideoName, @VideoDescription, @URL, @DateCompleted, " +
"@Duration, @VideoType, @AddedDate, @username);";
SqlCommand command = new SqlCommand(sqlCmd, connection);
command.Parameters.Add("@vidID", SqlDbType.VarChar).Value = vidID;
command.Parameters.Add("@VideoName", SqlDbType.VarChar).Value = videoInfo.VideoName;
command.Parameters.Add("@VideoDescription", SqlDbType.VarChar, 9999).Value = videoInfo.VideoDescription;
command.Parameters.Add("@URL", SqlDbType.VarChar).Value = videoInfo.URL;
command.Parameters.Add("@DateCompleted", SqlDbType.DateTime).Value = videoInfo.DateCompleted;
command.Parameters.Add("@Duration", SqlDbType.VarChar).Value = videoInfo.Duration;
command.Parameters.Add("@VideoType", SqlDbType.VarChar).Value = videoInfo.VideoType;
command.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = DateTime.Now;
command.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
command.ExecuteNonQuery();
connection.Close();
}
//}
//catch (Exception e)
//{
// return e.Message.ToString();
//}
return("true");
}
public List <VideoBackendViewModel> getTags(string category)
{
List <VideoBackendViewModel> GeneralTagList = new List <VideoBackendViewModel>();
string tag_group = 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 VideoBackendViewModel
{
Tag_ID = reader["tagID"].ToString(),
Tag = QUToolsUtilities.HtmlDecode(reader["tagname"].ToString()),
Tag_Group = tag_group
});
}
}
connection.Close();
}
}
catch (Exception e)
{
}
return(GeneralTagList);
}
public string cleanLinkEntrys(string vidID)
{
string sqlCmd = @"Delete from QUTool_BSIC_Vid_SchoolVids where vidid = @vidid;
Delete from QUTool_BSIC_Vid_ProgramVids where vidid = @vidid;
Delete from QUTool_BSIC_Vid_PillarVids where vidid = @vidid;
Delete from QUTool_BSIC_Vid_EventsVids where vidid = @vidid;
Delete from QUTool_BSIC_Vid_GeoVids where vidid = @vidid;
Delete from QUTool_BSIC_Vid_AthleticsVids where vidid = @vidid;
Delete from QUTool_BSIC_Vid_OtherTagsVids where vidid = @vidid;
Delete from QUTool_BSIC_Vid_VideoList where vidid = @vidid;" ;
try
{
using (SqlConnection connection = new SqlConnection(QUToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
SqlCommand command = new SqlCommand(sqlCmd, connection);
command.Parameters.Add("@vidid", SqlDbType.VarChar).Value = vidID;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception e)
{
return(e.Message.ToString());
}
return("Success");
}
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...
list_user = reader["QU_User"].ToString(),
list_QUID = reader["QUID"].ToString(),
list_admin = reader["toolAdmin"].ToString(),
list_userlevel = reader["userlevel"].ToString()
});
}
return(users);
}
}
}
catch (Exception e)
{
users.returnMsg = e.Message;
return(users);
}
}
public string renametag(string tag, string newname)
{
string[] tagArray = tag.Split('_');
string db, tagid;
if (tagArray[0] != "0")
{
db = tagArray[0];
tagid = tagArray[1];
}
else
{
db = tagArray[1];
tagid = tagArray[2];
}
switch (db.ToLower())
{
case "school":
db = "QUTool_BSIC_Vid_School";
break;
case "program":
db = "QUTool_BSIC_Vid_Program";
break;
case "pillars":
db = "QUTool_BSIC_Vid_Pillars";
break;
case "events":
db = "QUTool_BSIC_Vid_Events";
break;
case "geographical":
db = "QUTool_BSIC_Vid_Geo";
break;
case "geo":
db = "QUTool_BSIC_Vid_Geo";
break;
case "athletics":
db = "QUTool_BSIC_Vid_Athletics";
break;
case "othertags":
db = "QUTool_BSIC_Vid_OtherTags";
break;
default:
break;
}
string sqlCmd = @"update {0} set tagname = @newname where tagid = @tagid;";
sqlCmd = String.Format(sqlCmd, db);
try
{
using (SqlConnection connection = new SqlConnection(QUToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
SqlCommand command = new SqlCommand(sqlCmd, connection);
command.Parameters.Add("@tagid", SqlDbType.VarChar).Value = tagid;
command.Parameters.Add("@newname", SqlDbType.VarChar).Value = newname;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception e)
{
return(e.Message);
}
return("Success");
}
/*
*
*
* QUTool_BSIC_Vid_VideoList
* VidID int,
* VideoName varchar(250),
* VideoDescription varchar(2000),
* URL varchar(100),
* DateCompleted date,
* Duration int,
* VideoType varchar(50),
*
* TABLES IN USE
* All the following will have VidID and tagID
* QUTool_BSIC_Vid_ProgramVids
* QUTool_BSIC_Vid_SchoolVids
* QUTool_BSIC_Vid_EventsVids
* QUTool_BSIC_Vid_PillarVids
* QUTool_BSIC_Vid_OtherTagsVids
* QUTool_BSIC_Vid_GeoVids
* QUTool_BSIC_Vid_AthleticsVids
*
* All will have tagID and tagname.
* QUTool_BSIC_Vid_Program
* QUTool_BSIC_Vid_School
* QUTool_BSIC_Vid_Events
* QUTool_BSIC_Vid_Pillars
* QUTool_BSIC_Vid_Geo
* QUTool_BSIC_Vid_Athletics
* QUTool_BSIC_Vid_OtherTags
*/
public VideoBackendViewModel getTagsForVideo(VideoBackendViewModel tagList)
{
tagList.GeneralTagList = new List <VideoBackendViewModel>();
string sqlCmd = @"select sv.VidID,s.tagID, s.tagname, 'school' as 'group' from QUTool_BSIC_Vid_SchoolVids sv
left join QUTool_BSIC_Vid_School s on s.tagID = sv.tagID
where VidID = @vidID
union
select pv.VidID, p.tagID, p.tagname, 'Program' as 'group' from QUTool_BSIC_Vid_ProgramVids pv
left join QUTool_BSIC_Vid_Program p on p.tagID = pv.tagID
where VidID = @vidID
union
select plv.VidID, pl.tagID, pl.tagname, 'Pillars' as 'group' from QUTool_BSIC_Vid_PillarVids plv
left join QUTool_BSIC_Vid_Pillars pl on pl.tagID = plv.tagID
where VidID = @vidID
union
select ov.VidID, o.tagID, o.tagname, 'OtherTags' as 'group' from QUTool_BSIC_Vid_OtherTagsVids ov
left join QUTool_BSIC_Vid_OtherTags o on o.tagID = ov.tagID
where VidID = @vidID
union
select gv.VidID, g.tagID, g.tagname, 'Geographical' as 'group' from QUTool_BSIC_Vid_GeoVids gv
left join QUTool_BSIC_Vid_Geo g on g.tagID = gv.tagID
where VidID =@vidID
union
select ev.VidID, e.tagID, e.tagname, 'Events' as 'group' from QUTool_BSIC_Vid_EventsVids ev
left join QUTool_BSIC_Vid_Events e on e.tagID = ev.tagID
where VidID = @vidID
union
select av.VidID, a.tagID, a.tagname av, 'Athletics' as 'group' from QUTool_BSIC_Vid_AthleticsVids av
left join QUTool_BSIC_Vid_Athletics a on a.tagID = av.tagID
where VidID = @vidID" ;
try
{
using (SqlConnection connection = new SqlConnection(QUToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
SqlCommand command = new SqlCommand(sqlCmd, connection);
command.Parameters.Add("@vidID", SqlDbType.VarChar).Value = tagList.vidID;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
tagList.GeneralTagList.Add(new VideoBackendViewModel
{
Tag_ID = reader["tagID"].ToString(),
Tag = reader["tagname"].ToString(),
Tag_Group = reader["group"].ToString()
});
}
}
connection.Close();
}
}
catch (Exception e)
{
tagList.returnMsg = e.Message.ToString();
return(tagList);
}
return(tagList);
}
public VideoBackendViewModel listVideos(string searchParam)
{
// Maybe date?
VideoBackendViewModel vidObj = new VideoBackendViewModel();
vidObj.VideoInfo = new List <VideoBackendViewModel>();
string sqlCmd = "select * from QUTool_BSIC_Vid_VideoList";
bool search = false;
if (searchParam != "all")
{
sqlCmd = "select * from QUTool_BSIC_Vid_VideoList where vidid = @vidid";
search = true;
}
try
{
using (SqlConnection connection = new SqlConnection(QUToolsUtilities.GetConnectionString("internet")))
{
connection.Open();
///sqlCmd = String.Format(sqlCmd, category);
SqlCommand command = new SqlCommand(sqlCmd, connection);
if (search == true)
{
command.Parameters.Add("@vidid", SqlDbType.VarChar).Value = searchParam;
}
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
vidObj.VideoInfo.Add(new VideoBackendViewModel
{
VideoName = QUToolsUtilities.HtmlDecode(reader["Videoname"].ToString()),
VideoDescription = QUToolsUtilities.HtmlDecode(reader["VideoDescription"].ToString()),
URL = QUToolsUtilities.HtmlDecode(reader["URL"].ToString()),
DateCompleted = Convert.ToDateTime(reader["DateCompleted"]),
Duration = QUToolsUtilities.HtmlDecode(reader["Duration"].ToString()),
VideoType = QUToolsUtilities.HtmlDecode(reader["VideoType"].ToString()),
Addedby = reader["AddedBy"].ToString(),
UpdatedDate = Convert.ToDateTime(reader["UpdatedDate"]),
AddedDate = Convert.ToDateTime(reader["AddedDate"]),
vidID = reader["VidID"].ToString()
});
}
}
connection.Close();
}
}
catch (Exception e)
{
vidObj.returnMsg = e.Message.ToString();
return(vidObj);
}
return(vidObj);
}
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 admin_user;
if (User.Admin == "Yes")
{
admin_user = "******";
}
else
{
admin_user = "******";
}
// 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 (admin_user == "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", admin_user);
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);
}
