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.");
}
}
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");
}
//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);
}
//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");
}
//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);
}
//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);
}
//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);
}
//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 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);
}
//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);
}
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 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);
}
//Deletes comment from database
public void delete()
{
string sql = "deleteComment @comment=" + commentID;
SqlComm.SqlExecute(sql);
}
