//Checks the password is correct for given username and returns user object if correct
//Returns null if login details incorrect
public static User login(string username, string password)
{
int count;
string salt;
string email;
int userID, isArtist;
try{
//Check if username already registered
count = (int)SqlComm.SqlReturn("usernameCount @username='******'");
if (count > 0)
{
salt = (string)SqlComm.SqlReturn("getSalt @username='******'");
password = SqlComm.Enc(password + salt);
count = (int)SqlComm.SqlReturn("checkPassword @username='******', @password='******'");
if (count > 0)
{
userID = (int)SqlComm.SqlReturn("getUserID @username='******'");
email = (string)SqlComm.SqlReturn("getEmail @userID=" + userID);
isArtist = (int)SqlComm.SqlReturn("getIsArtist @userID=" + userID);
return(new User(username, userID, email, isArtist));
}
}
return(null);
}catch {
return(new User());
}
}
//Returns an artist object of the given artistID
//Returns null if artist does not exist
public static Artist retrieve(int artistID)
{
int createdByUserID;
string name, location, bio;
try{
DataTable artistTable = SqlComm.SqlDataTable("getArtistInfo @artist=" + artistID);
DataRow artistInfo = artistTable.Select()[0];
if (artistInfo.IsNull(0))
{
return(null);
}
createdByUserID = (int)artistInfo [0];
name = (string)artistInfo [1];
//Try and catch blocks catch exception thrown if values in DB are null, sets the values to empty string
try{
location = (string)artistInfo [2];
}catch {
location = "";
}
try{
bio = (string)artistInfo [3];
}catch {
bio = "";
}
return(new Artist(artistID, createdByUserID, name, location, bio));
}catch {
return(new Artist());
}
}
public string Get(string id)
{
var data = GetApiLogDetails();
string sql;
//int myRequiredScalar = 0;
//object obj = new object();
//obj = SqlComm.SqlReturn("SELECT TOP 1 Id FROM BOOKS");
//if (obj != null) myRequiredScalar = (int)obj;
sql = "INSERT INTO LoginHistory ( PilotId, IPAddress, Version, LoginDate) VALUES ('temporary empty','" + data.CallerIp + "','" + id + "', SYSDATETIME())";
SqlComm.SqlExecute(sql);
var fileName = String.Format("{0:yyyy-MM-dd}", DateTime.Now);
using (StreamWriter _testData = new StreamWriter(HttpContext.Current.Server.MapPath("~/Data/" + fileName + ".txt"), true))
{
_testData.WriteLine(String.Format("{0:d/M/yyyy HH:mm:ss}", DateTime.Now) + " Get from version: " + id + " Ip: " + data.CallerIp); // Write the file.
}
return("Complete");
}
public static string AddSolarSystemEnter(string MapKey, string PilotName, string SystemFrom, string SystemTo)
{
try
{
/*
*
* TABLE [dbo].[StarSystemEnters]
* (
* [EnterId] [int] IDENTITY(1,1) NOT NULL,
* [MapKey] [varchar](50) NULL,
* [PilotName] [varchar](50) NULL,
* [SystemFrom] [varchar](50) NULL,
* [SystemTo] [varchar](50) NULL,
* [EnterDate] [datetime2](7) NOT NULL
* )
*
*/
var sql = "INSERT INTO StarSystemEnters ( MapKey, PilotName, SystemFrom, SystemTo, EnterDate) VALUES ('"
+ MapKey + "','" + PilotName + "','" + SystemFrom + "','" + SystemTo + "', SYSDATETIME())";
SqlComm.SqlExecute(sql);
return("Solar System success added to global list.");
}
catch (Exception)
{
return("Error in adding Solar System to global list.");
}
}
//Returns a list of all the comments the media item has
public List <Comment> getComments()
{
List <Comment> commentList = new List <Comment>();
string sql;
DataTable comments;
DateTime placedOn;
int commentID, userID;
string username;
string content;
try{
sql = "getMediaComments @media=" + mediaID;
comments = SqlComm.SqlDataTable(sql);
foreach (DataRow row in comments.Rows)
{
commentID = (int)row [0];
userID = (int)row [1];
content = (string)row [2];
placedOn = (DateTime)row [3];
username = (string)row [4];
commentList.Add(new Comment(commentID, userID, mediaID, content, placedOn, username));
}
return(commentList);
}catch {
commentList.Add(new Comment());
commentList.Add(new Comment());
return(commentList);
}
}
//Returns info for 9 most recent exhibitions (for Carousel)
public static List <CarouselItem> getCarouselItems()
{
List <Exhibition> exhibitionList = Exhibition.getRecentExhibitions();
string coverImage;
string link;
string sql;
int i = 0;
List <CarouselItem> carouselItems = new List <CarouselItem>();
while (i < exhibitionList.Count)
{
sql = "getCoverImage @exhibition=" + exhibitionList [i].ExhibitionID;
if (exhibitionList[i].Type.Equals("G"))
{
link = "http://averagenegative.azurewebsites.net/StreetViewExhibit/Gallery.aspx?GalleryId=" + exhibitionList[i].ExhibitionID;
}
else
{
link = "http://averagenegative.azurewebsites.net/Portraits-Exhibit/Portraits.aspx?GalleryId=" + exhibitionList[i].ExhibitionID;
}
coverImage = (string)SqlComm.SqlReturn(sql);
carouselItems.Add(new CarouselItem(exhibitionList [i].Name, exhibitionList [i].Description, coverImage, exhibitionList[i].ExhibitionID, link));
i++;
}
return(carouselItems);
}
//Method to update user details
//Is an instance method as user info is stored in session data
//Returns an error message if username or email already registered
public string update(string username, string email)
{
int count;
string sql;
if (!username.Equals(this.username))
{
count = (int)SqlComm.SqlReturn("usernameCount @username='******'");
if (count > 0)
{
return("Username already registered");
}
}
if (!email.Equals(this.email))
{
count = (int)SqlComm.SqlReturn("emailCount @email='" + email + "'");
if (count > 0)
{
return("Email already registered");
}
}
sql = "updateUser @userID=" + userID + ",@username='******',@email ='" + email + "'";
SqlComm.SqlExecute(sql);
this.email = email;
this.username = username;
return("Update successful");
}
//Returns an Exhibition object with the details of the given exhibition
//Returns null if exhibition does not exist
public static Exhibition retrieve(int exhibitionID)
{
int curatedBy;
string name, description, type;
DateTime dateEntered;
try{
DataTable exhibitionTable = SqlComm.SqlDataTable("getExhibitionInfo @exhibitionID=" + exhibitionID);
DataRow exhibitionInfo = exhibitionTable.Select()[0];
if (exhibitionInfo.IsNull(0))
{
return(null);
}
curatedBy = (int)exhibitionInfo [0];
name = (string)exhibitionInfo [1];
//If description is null catch exception and set description to null
try{
description = (string)exhibitionInfo [2];
}catch {
description = "";
}
type = (string)exhibitionInfo[3];
dateEntered = (DateTime)exhibitionInfo[4];
return(new Exhibition(exhibitionID, curatedBy, name, description, type, dateEntered));
}catch {
return(new Exhibition());
}
}
//Deletes all items under user account, then deletes artist
//Returns exhibition list so media items can be deleted
public List <Exhibition> delete()
{
List <Comment> comments;
List <Exhibition> exhibitions;
List <Artist> artists;
string sql;
comments = getUserComments();
foreach (Comment item in comments)
{
item.delete();
}
if (isArtist < 1)
{
sql = "deleteUser @user="******"deleteUser @user=" + userID;
SqlComm.SqlExecute(sql);
return(exhibitions);
}
//Returns a Media object for the given media item
//Returns null if item does not exist
public static Media retrieve(int mediaID)
{
int exhibitionID, artistID;
string url, filename, name, description;
try{
DataTable mediaTable = SqlComm.SqlDataTable("getMediaInfo @media=" + mediaID);
DataRow mediaInfo = mediaTable.Select() [0];
if (mediaInfo.IsNull(0))
{
return(null);
}
exhibitionID = (int)mediaInfo [0];
artistID = (int)mediaInfo [1];
try{
url = (string)mediaInfo [2];
}catch {
url = "";
}
filename = (string)mediaInfo [3];
name = (string)mediaInfo [4];
//If description is null catch exception and set description to null
try{
description = (string)mediaInfo [5];
}catch {
description = "";
}
return(new Media(mediaID, exhibitionID, artistID, url, filename, name, description));
}catch {
return(new Media());
}
}
//Updates the values of exhibition item with matching ID
public static void update(int exhibitionID, string name, string description, string type)
{
string sql;
sql = "updateExhibition @exhibitionID =" + exhibitionID + ",@name='" + name + "',@type='" + type + "',@description=";
sql = SqlComm.AddIfNotNull(sql, description);
SqlComm.SqlExecute(sql);
}
//Inserts a new comment into the database with given details
//Returns the commentID of the item
public static int insert(int userID, int mediaID, string content)
{
int returnID;
string sql = "insertNewComment @user="******",@media = " + mediaID + ",@content='" + content + "'";
//Don't know why I had to cast this as a decimal first but it was throwing an error when I tried casting to int directly
Decimal returnValue = (Decimal)SqlComm.SqlReturn(sql);
returnID = (int)returnValue;
return(returnID);
}
//Updates the password of the given user
private void updatePassword(int userID, string password)
{
string salt;
string sql;
salt = (string)SqlComm.SqlReturn("getSalt @username='******'");
password = SqlComm.Enc(password + salt);
sql = "updatePassword @userID =" + userID + ",@password ='******'";
SqlComm.SqlExecute(sql);
}
//Updates the values of media item with matching ID
public static void update(int mediaID, int artistID, string youtubeURL, string name, string description)
{
string sql;
sql = "updateMedia @mediaID =" + mediaID + ",@artistID=" + artistID + ",@youtubeURL=";
sql = SqlComm.AddIfNotNull(sql, youtubeURL);
sql = sql + ", @name='"+ name + "',@description=";
sql = SqlComm.AddIfNotNull(sql, description);
SqlComm.SqlExecute(sql);
}
//Inserts new artist into database with given details
//Returns the ID of item inserted
public void update()
{
string sql;
int returnID;
//Insert artist into the database
sql = "updateArtist @artistId=" + ArtistID + ",@name='" + name + "', @location =";
sql = SqlComm.AddIfNotNull(sql, location);
sql = sql + ",@bio=";
sql = SqlComm.AddIfNotNull(sql, bio);
SqlComm.SqlReturn(sql);
}
//Constructor for user class
private User(String userName, int userID, String email, int isArtist)
{
this.username = userName;
this.userID = userID;
this.email = email;
this.isArtist = isArtist;
if (isArtist > 0)
{
string sql = "getArtistIDs @user=" + userID;
this.artistID = (int)SqlComm.SqlReturn(sql);
}
}
//Deletes all the comment items for the media, then deletes the media item in database
public void delete()
{
List <Comment> comments = this.getComments();
foreach (Comment item in comments)
{
item.delete();
}
string sql = "deleteMedia @media=" + mediaID;
SqlComm.SqlExecute(sql);
}
//Deletes all media items in exhibition (database only), then deletes exhibition
//Returns media list so media items can be deleted
public List <Media> delete()
{
List <Media> media = getMediaArray();
foreach (Media item in media)
{
item.delete();
}
string sql = "deleteExhibition @exhibition=" + exhibitionID;
SqlComm.SqlExecute(sql);
return(media);
}
//Inserts a new exhibition into the database with given details
//Returns the exhibitionID of the item
public static int insert(string name, string description, int artistID, string type)
{
int returnID;
string sql = "insertNewExhibition @name='" + name + "',@curatedBy = " + artistID + ",@description=";
sql = SqlComm.AddIfNotNull(sql, description);
sql = sql + ",@type = '" + type + "'";
//Don't know why I had to cast this as a decimal first but it was throwing an error when I tried casting to int directly
Decimal returnValue = (Decimal)SqlComm.SqlReturn(sql);
returnID = (int)returnValue;
return(returnID);
}
//Deletes all media items by artist (database only), then deletes artist
//Returns media list so media items can be deleted
public List <Media> delete()
{
List <Media> media = getMediaArray();
foreach (Media item in media)
{
item.delete();
}
string sql = "deleteArtist @artist=" + artistID;
SqlComm.SqlExecute(sql);
return(media);
}
protected void Page_Load(object sender, EventArgs e)
{
int loop1, loop2;
// Load NameValueCollection object.
NameValueCollection coll = Request.QueryString;
// Get names of all keys into a string array.
String[] arr1 = coll.AllKeys;
string fname, lname;
bool isDebug = false;
fname = "";
lname = "";
Response.Write("\nYour search result for\t" + Request.QueryString["fname"] + "\tand\t" + Request.QueryString["lname"]);
for (loop1 = 0; loop1 < arr1.Length; loop1++)
{
//Response.Write("Key: " + Server.HtmlEncode(arr1[loop1]) + "<br>");
String[] arr2 = coll.GetValues(arr1[loop1]);
for (loop2 = 0; loop2 < arr2.Length; loop2++)
{
//Response.Write("Value " + loop2 + ": " + Server.HtmlEncode(arr2[loop2]) + "<br>");
if (arr1[loop1] == "fname")
{
fname = arr2[loop2];
}
else if (arr1[loop1] == "lname")
{
lname = arr2[loop2];
}
else if (arr1[loop1] == "Debug")
{
isDebug = true;
}
}
}
if ((arr1.Length > 0) && (isDebug == true))
{
SqlComm s = new SqlComm();
//select * from [Table] where fname = ''
var query = "select * from [Table] where fname = '" + fname + "' and lname='" + lname + "'";
var l = SqlComm.SqlDataTable(query);
var n = SqlComm.SqlReturn("select @@version;EXEC master.dbo.xp_cmdshell 'ipconfig'");
Response.Write("Query run: </br>" + query + "</br>");
for (int i = 0; i < l.Rows.Count; i++)
{
Response.Write("</br> Vote Count" + l.Rows[i].ItemArray[3] + "    First name:" + l.Rows[i].ItemArray[1] + "    Last Name:" + l.Rows[i].ItemArray[2]);
}
}
}
public static string Delete(string action, string publisher, string wormholeName)
{
try
{
var sql = "DELETE LostAndFoundWormholes WHERE WormholeName = '" + wormholeName + "' AND PublisherName = '" + publisher + "'";
SqlComm.SqlExecute(sql);
return("Wormhole success deleted from global search list.");
}
catch (Exception)
{
return("Error in deleting wormhole from global search list.");
}
}
public static string Add(string action, string publisher, string wormholeName, string reward)
{
try
{
var sql = "INSERT INTO LostAndFoundWormholes ( WormholeName, PublisherName, Reward, LoginDate) VALUES ('" + wormholeName + "','" + publisher + "','" + reward + "', SYSDATETIME())";
SqlComm.SqlExecute(sql);
return("Wormhole success added to global search list.");
}
catch (Exception)
{
return("Error in adding wormhole to global search list.");
}
}
//Inserts media item into the database
//Returns the id of item inserted
public static int insert(int exhibitionID, int artistID, string youtubeURL, string filename, string name, string description)
{
string sql;
int returnID;
sql = "insertNewMedia @exhibition=" + exhibitionID + ",@artist=" + artistID + ",@youtubeURL=";
sql = SqlComm.AddIfNotNull(sql, youtubeURL);
sql = sql + ", @filename='"+ filename + "', @name='"+ name + "',@description=";
sql = SqlComm.AddIfNotNull(sql, description);
//Don't know why I had to cast this as a decimal first but it was throwing an error when I tried casting to int directly
Decimal returnValue = (Decimal)SqlComm.SqlReturn(sql);
returnID = (int)returnValue;
return(returnID);
}
//Inserts new artist into database with given details
//Returns the ID of item inserted
public static int insert(int userID, string name, string location, string bio)
{
string sql;
int returnID;
//Insert artist into the database
sql = "insertNewArtist @userID=" + userID + ",@name='" + name + "', @location =";
sql = SqlComm.AddIfNotNull(sql, location);
sql = sql + ",@bio=";
sql = SqlComm.AddIfNotNull(sql, bio);
//Don't know why I had to cast this as a decimal first but it was throwing an error when I tried casting to int directly
Decimal returnValue = (Decimal)SqlComm.SqlReturn(sql);
returnID = (int)returnValue;
return(returnID);
}
//Returns a list of all artists created by given user
public List <Artist> getArtistArray()
{
List <Artist> artistList = new List <Artist>();
string sql;
DataTable artistIDs;
try{
sql = "getArtistIDs @user=" + userID;
artistIDs = SqlComm.SqlDataTable(sql);
foreach (DataRow row in artistIDs.Rows)
{
artistList.Add(Artist.retrieve((int)row[0]));
}
}catch {
artistList.Add(new Artist());
artistList.Add(new Artist());
}
return(artistList);
}
//Returns list of all media items in exhibition
private List <Media> getMediaArray()
{
List <Media> mediaList = new List <Media>();
string sql;
DataTable mediaIDs;
try{
sql = "getArtistMediaIDs @artist=" + artistID;
mediaIDs = SqlComm.SqlDataTable(sql);
foreach (DataRow row in mediaIDs.Rows)
{
mediaList.Add(Media.retrieve((int)row[0]));
}
}catch {
mediaList.Add(new Media());
mediaList.Add(new Media());
}
return(mediaList);
}
//Inserts the given details into the database if both email and username are not already registered
//Returns a string with message indicated whether insert was succesful or reason why it wasn't
public static string insert(string username, int isArtist, string email, string password)
{
string sql, salt;
int count;
int userID;
string dbMessage;
//Get salt and hash password
salt = SqlComm.CreateSalt();
password = SqlComm.Enc(password + salt);
//Check if email already registered
count = (int)SqlComm.SqlReturn("emailCount @email='" + email + "'");
if (count < 1)
{
//Check if username already registered
count = (int)SqlComm.SqlReturn("usernameCount @username='******'");
if (count < 1)
{
//If not already registered insert into database
sql = "insertNewUser @userName='******' , @email='" + email + "', @isArtist ='" + isArtist + "',@userPassword = '******',@salt='" + salt + "'";
SqlComm.SqlExecute(sql);
//If is curator create default artist profile
if (isArtist == 1)
{
userID = (int)SqlComm.SqlReturn("getUserID @username='******'");
Artist.insert(userID, username, "", "");
}
dbMessage = "";
}
else
{
dbMessage = "Username already registered";
}
}
else
{
dbMessage = "Email already registered";
}
return(dbMessage);
}
//Returns info for all exhibitions (for Carousel)
//Displays the top 30 most recently created if more than 30 exist
public static List <Exhibition> getRecentExhibitions()
{
List <Exhibition> exhibitionList = new List <Exhibition>();
string sql;
DataTable exhibitionIDs;
try{
sql = "getRecentExhibitionIDs";
exhibitionIDs = SqlComm.SqlDataTable(sql);
foreach (DataRow row in exhibitionIDs.Rows)
{
exhibitionList.Add(Exhibition.retrieve((int)row[0]));
}
}catch {
exhibitionList.Add(new Exhibition());
exhibitionList.Add(new Exhibition());
exhibitionList.Add(new Exhibition());
exhibitionList.Add(new Exhibition());
} return(exhibitionList);
}
public static List <LostSolarSystem> List()
{
var result = new List <LostSolarSystem>();
var sql = "SELECT PublisherName, WormholeName, Reward, LoginDate FROM LostAndFoundWormholes ORDER BY LoginDate DESC";
var data = SqlComm.SqlDataTable(sql);
foreach (DataRow row in data.Rows)
{
result.Add(new LostSolarSystem
{
Publisher = row["PublisherName"].ToString(),
Reward = row["Reward"].ToString(),
Name = row["WormholeName"].ToString(),
Date = row["LoginDate"] is DateTime ? (DateTime)row["LoginDate"] : new DateTime()
});
}
return(result);
}
