public string UserRegister(string foaf_nick, string foaf_mbox, string paths_password)
{
var mTimer = new Stopwatch();
mTimer.Start();
try
{
euPaths clseuPaths = new euPaths();
string strQueryUserCheck = "select id from usr where email = '" + foaf_mbox + "';";
int rtnUserID = Utility.DBExecuteScalar(strQueryUserCheck);
if (rtnUserID == 0)
{
string strQuery = "INSERT INTO usr (fk_cog_style_id,usr,foaf_nick,pwd,email) VALUES ('" + 1 + "','" + foaf_mbox + "','" + foaf_nick + "','" + paths_password + "','" + foaf_mbox + "');SELECT currval('usr_id_seq');";
int rtnID = Utility.DBExecuteScalar(strQuery);
if (Convert.ToString(Context.Session.SessionID).Trim() != "")
{
strQuery = "select fk_usr_id from uaction where usession = '" + Convert.ToString(Context.Session.SessionID).Trim() + "';";
int rtnUID = Utility.DBExecuteScalar(strQuery);
if (Context.Session["usr_id"] == null)
{
strQuery = "INSERT INTO workspace (fk_usr_id, isprimary) VALUES (" + rtnID + ", true);";
int workspaceID = Utility.DBExecuteScalar(strQuery);
}
else
{
strQuery = "update workspace set fk_usr_id = " + rtnID + " , isprimary = true where fk_usr_id=" + Context.Session["usr_id"] + ";";
int workspaceID = Utility.DBExecuteScalar(strQuery);
}
strQuery = "update uaction set fk_usr_id = " + rtnID + " where usession = '" + Convert.ToString(Context.Session.SessionID).Trim() + "';";
Utility.DBExecuteNonQuery(strQuery);
strQuery = "update ubehaviour set fk_usr_id = " + rtnID + " where usession = '" + Convert.ToString(Context.Session.SessionID).Trim() + "';";
Utility.DBExecuteNonQuery(strQuery);
}
Context.Session["usr_id"] = rtnID;
Context.Session["isAuthenticated"] = true;
string strRV = clseuPaths.GetCurrentUserDetails(Convert.ToString(rtnID));
Utility.LogRequest(strRV, false, mTimer);
return strRV;
}
else
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.EmailAlreadyExists, "Email already exists");
Utility.LogRequest(mMsg,false, mTimer);
return mMsg;
}
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message);
Utility.LogRequest(mMsg, true, mTimer);
return mMsg;
}
}
public string UserLogin(string foaf_mbox, string paths_password)
{
var mTimer = new Stopwatch();
mTimer.Start();
euPaths clseuPaths = new euPaths();
try
{
int isAuthenticated = clseuPaths.authenticate(foaf_mbox, paths_password);
string strQuery = string.Empty;
string strRV = string.Empty;
if (isAuthenticated > 0)
{
if (Context.Session["isTemporary"] != null && Utility.IsNumeric(Convert.ToString(Context.Session["usr_id"])))
{
strQuery = "select id from workspace where isprimary = true and fk_usr_id =" + isAuthenticated + ";";
int rtnWSID = Utility.DBExecuteScalar(strQuery);
strQuery = "select id from workspace WHERE fk_usr_id=" + Convert.ToString(Context.Session["usr_id"]) + ";";
DataTable dtRtnWSTID = Utility.DBExecuteDataTable(strQuery);
//delete from workspace where id = 18
foreach (DataRow drow in dtRtnWSTID.Rows)
{
strQuery = "delete from workspace where id =" + drow["id"] + ";";
Utility.DBExecuteScalar(strQuery);
}
strQuery = "delete from usr where id =" + Convert.ToString(Context.Session["usr_id"]) + ";";
Utility.DBExecuteScalar(strQuery);
foreach (DataRow drow in dtRtnWSTID.Rows)
{
strQuery = "UPDATE workspace_item SET fk_workspace_id=" + rtnWSID + " WHERE fk_workspace_id=" + drow["id"] + ";";
int rtnID = Utility.DBExecuteNonQuery(strQuery);
}
Context.Session["isTemporary"] = null;
}
Context.Session["usr_id"] = isAuthenticated;
Context.Session["isAuthenticated"] = true;
//Get user data after login
strRV = clseuPaths.GetCurrentUserDetails(Convert.ToString(Context.Session["usr_id"]));
Utility.LogRequest(strRV, false, mTimer);
return strRV;
}
else
{
Context.Session["usr_id"] = null;
Context.Session["isAuthenticated"] = null;
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.AuthenticationFailed);
Utility.LogRequest(mMsg, false, mTimer);
return mMsg;
}
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.OperationFailed, ex.Message);
Utility.LogRequest(mMsg);
return mMsg;
}
}
public string UserLogout()
{
var mTimer = new Stopwatch();
mTimer.Start();
euPaths clseuPaths = new euPaths();
Context.Session["isAuthenticated"] = null;
Context.Session["usr_id"] = null;
Context.Session.Clear();
Context.Session.Abandon();
string strQuery = string.Empty;
string strRV = string.Empty;
if (Context.Session["isAuthenticated"] == null)
{
clseuPaths.createTemporaryUser(Context);
}
//Get anonymous user data.
strRV = clseuPaths.GetCurrentUserDetails(Convert.ToString(Context.Session["usr_id"]));
Utility.LogRequest(strRV, false, mTimer);
return strRV;
}
public string LogAction(string dc_source, string paths_request)
{
var mTimer = new Stopwatch();
mTimer.Start();
euPaths clseuPaths = new euPaths();
if (clseuPaths.requireUser(Context) == false)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.FailedToCreateTemporaryUser);
Utility.LogRequest(mMsg, true, mTimer);
return mMsg;
}
try
{
string strQuery = "INSERT INTO uaction (fk_usr_id,usession,dc_source,paths_request) VALUES ('" + Context.Session["usr_id"] + "', '" + Convert.ToString(Context.Session.SessionID).Trim() + "','" + dc_source + "','" + paths_request + "');";
int rtnID = Utility.DBExecuteNonQuery(strQuery);
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.OperationCompletedSuccessfully, "Operation completed successfully");
Utility.LogRequest(mMsg, false, mTimer);
return mMsg;
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message);
Utility.LogRequest(mMsg, true, mTimer);
return mMsg;
}
}
public string UserGet(string paths_identifier)
{
var mTimer = new Stopwatch();
mTimer.Start();
string strGetUserByID = string.Empty;
euPaths clseuPaths = new euPaths();
DataTable dtab = new DataTable();
try
{
string strQuery = "SELECT uri paths_identifier,foaf_nick, email foaf_mbox ,email_visibility foaf_mbox_visibility,(CASE istemporary when true THEN 'new' ELSE 'registered' END)dc_type,to_char(tstamp,'yyyy-mm-ddTmm:hh:ss') paths_registered FROM usr WHERE uri = '" + paths_identifier + "'";
dtab = Utility.DBExecuteDataTable(strQuery);
strGetUserByID = Utility.DataTableToDictionary(dtab, true);
Utility.LogRequest(strGetUserByID, false, mTimer);
return strGetUserByID;
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.AuthenticationSucceeded, ex.Message);
Utility.LogRequest (mMsg, true, mTimer);
return mMsg;
}
}
public string Current()
{
var mTimer = new Stopwatch();
mTimer.Start();
string strRV = string.Empty;
euPaths clseuPaths = new euPaths();
if (clseuPaths.requireUser(Context) == false)
{
// **** Updated on 12 September 2013
// commented below line and add new line.
// return Utility.GetMsg(Utility.msgStatusCodes.OperationFailed, "Failed to create temporary user");
clseuPaths.requireUser(Context);
// **** Updated on 12 September 2013
}
try
{
strRV = clseuPaths.GetCurrentUserDetails(Convert.ToString(Context.Session["usr_id"]));
Utility.LogRequest(strRV, false, mTimer);
return strRV;
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message);
Utility.LogRequest(mMsg, true, mTimer);
return mMsg;
}
}
public string LogPage(string dc_title, string dc_source)
{
var mTimer = new Stopwatch();
mTimer.Start();
euPaths clseuPaths = new euPaths();
string strQuery = string.Empty;
DataTable dtab;
string strDC = "";
int dRowCount = 0;
//Create user if none exists...
if (clseuPaths.requireUser(Context) == false)
{
var mStr = Utility.GetMsg(Utility.msgStatusCodes.FailedToCreateTemporaryUser);
Utility.LogRequest(mStr, true, mTimer);
return mStr;
}
try
{
strQuery = "select * from ubehaviour where usession = '" + Convert.ToString(Context.Session.SessionID).Trim() + "' order by id desc limit 1;";
dtab = Utility.DBExecuteDataTable(strQuery);
dRowCount = dtab.Rows.Count;
if (dRowCount > 0)
{
strDC = Convert.ToString(dtab.Rows[0]["dc_source"]).Trim();
}
if (strDC != dc_source)
{
strQuery = "INSERT INTO ubehaviour(fk_usr_id, usession,dc_title ,dc_source) VALUES ('" + Context.Session["usr_id"] + "', '" + Convert.ToString(Context.Session.SessionID).Trim() + "', '" + dc_title.Trim() + "', '" + dc_source.Trim() + "');";
Utility.DBExecuteNonQuery(strQuery);
}
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.OperationCompletedSuccessfully, "Operation completed successfully");
Utility.LogRequest(mMsg, false, mTimer);
return mMsg;
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message);
Utility.LogRequest(mMsg, true, mTimer);
return mMsg;
}
}
public string UpdateOrder(string paths_identifier, string paths_item_identifier, string paths_order)
{
var mTimer = new Stopwatch();
mTimer.Start();
string strRV = string.Empty;
euPaths clseuPaths = new euPaths();
DataTable dtab;
clseuPaths.requireUser(Context);
StringBuilder sqlBuilder = new StringBuilder();
string strQuery = string.Empty;
try
{
strQuery = "select id from workspace where uri ='" + paths_identifier + "' ;";
int workspaceID = Utility.DBExecuteScalar(strQuery);
sqlBuilder.Append("UPDATE workspace_item SET ");
if (!string.IsNullOrEmpty(paths_order))
{
if (Utility.IsNumeric(paths_order))
sqlBuilder.Append("paths_order='" + paths_order + "'");
}
sqlBuilder.Append(" WHERE fk_workspace_id='" + workspaceID + "' and ");
sqlBuilder.Append(" uri='" + paths_item_identifier + "';");
Utility.DBExecuteNonQuery(sqlBuilder.ToString());
strQuery = "select wsi.uri paths_identifier,wsi.dc_title,wsi.dc_description,wsi.dc_source,wsi.paths_thumbnail,wsi.type paths_type from workspace ws inner join workspace_item wsi on wsi.fk_workspace_id=ws.id where wsi.fk_workspace_id='" + workspaceID + "' and wsi.uri='" + paths_item_identifier + "'";
dtab = Utility.DBExecuteDataTable(strQuery);
strRV = Utility.DataTableToDictionary(dtab);
strRV = strRV.Replace("[", "").Replace("]", "");
Utility.LogRequest(strRV, false, mTimer);
return strRV;
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message + sqlBuilder.ToString());
Utility.LogRequest(mMsg, true, mTimer);
return mMsg;
}
}
public string Delete(string paths_identifier, string paths_item_identifier)
{
var mTimer = new Stopwatch();
mTimer.Start();
string strRV = string.Empty;
DataTable dtab;
euPaths clseuPaths = new euPaths();
string strQuery = string.Empty;
clseuPaths.requireUser(Context);
try
{
strQuery = "select id from workspace where uri ='" + paths_identifier + "' ;";
int workspaceID = Utility.DBExecuteScalar(strQuery);
strQuery = "select wsi.uri paths_identifier,wsi.dc_title,wsi.dc_description,wsi.dc_source,wsi.paths_thumbnail,wsi.type paths_type from workspace ws inner join workspace_item wsi on wsi.fk_workspace_id=ws.id where wsi.fk_workspace_id='" + workspaceID + "' and wsi.uri='" + paths_item_identifier + "'";
dtab = Utility.DBExecuteDataTable(strQuery);
strRV = Utility.DataTableToDictionary(dtab);
strQuery = "DELETE FROM workspace_item WHERE fk_workspace_id = '" + workspaceID + "' and uri='" + paths_item_identifier + "'";
Utility.DBExecuteNonQuery(strQuery);
strRV = strRV.Replace("[", "").Replace("]", "");
Utility.LogRequest(strRV, false, mTimer);
return strRV;
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message);
Utility.LogRequest(mMsg, true, mTimer);
return mMsg;
}
}
public string Update(string paths_identifier, string paths_item_identifier, string dc_description)
{
string strRV = string.Empty;
euPaths clseuPaths = new euPaths();
DataTable dtab;
clseuPaths.requireUser(Context);
StringBuilder sqlBuilder = new StringBuilder();
string strQuery = string.Empty;
try
{
strQuery = "select id from workspace where uri ='" + paths_identifier + "' ;";
int workspaceID = Utility.DBExecuteScalar(strQuery);
sqlBuilder.Append("UPDATE workspace_item SET ");
if (dc_description !=null)
{
sqlBuilder.Append("dc_description='" + dc_description + "'");
}
sqlBuilder.Append(" WHERE fk_workspace_id='" + workspaceID + "' and ");
sqlBuilder.Append(" uri='" + paths_item_identifier + "';");
Utility.DBExecuteNonQuery(sqlBuilder.ToString());
strQuery = "select wsi.uri paths_identifier,wsi.dc_title,wsi.dc_description,wsi.dc_source,wsi.paths_thumbnail,wsi.type paths_type from workspace ws inner join workspace_item wsi on wsi.fk_workspace_id=ws.id where wsi.fk_workspace_id='" + workspaceID + "' and wsi.uri='" + paths_item_identifier + "'";
dtab = Utility.DBExecuteDataTable(strQuery);
strRV = Utility.DataTableToDictionary(dtab);
strRV = strRV.Replace("[", "").Replace("]", "");
return strRV;
}
catch (Exception ex)
{
return Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message + sqlBuilder.ToString());
}
}
public string Add(string paths_identifier, string dc_title, string dc_description, string dc_source, string paths_thumbnail, string paths_type)
{
var mTimer = new Stopwatch();
mTimer.Start();
string strRV = string.Empty;
euPaths clseuPaths = new euPaths();
string strQuery = string.Empty;
int workspaceID = 0;
DataTable dtab;
clseuPaths.requireUser(Context);
try
{
strQuery = "select * from workspace where uri='" + paths_identifier + "';";
workspaceID = Utility.DBExecuteScalar(strQuery);
if (workspaceID == 0)
{
strQuery = "INSERT INTO workspace (fk_usr_id,isprimary) VALUES ('" + Convert.ToString(Context.Session["usr_id"]) + "',false);SELECT currval('workspace_id_seq');";
workspaceID = Utility.DBExecuteScalar(strQuery);
}
//Insert data into workspace_item table
strQuery = "insert into workspace_item (fk_workspace_id, dc_title, dc_description, dc_source,type,paths_thumbnail) VALUES ('" + workspaceID + "','" + dc_title.Replace("'", "''") + "','" + dc_description.Replace("'", "''") + "','" + dc_source + "','" + paths_type + "','" + paths_thumbnail.Replace("'", "''") + "');SELECT currval('workspace_item_id_seq');";
int workspaceItemID = Utility.DBExecuteScalar(strQuery);
strQuery = "select wsi.uri paths_identifier,wsi.dc_title,wsi.dc_description,wsi.dc_source,wsi.paths_thumbnail,wsi.type paths_type from workspace ws inner join workspace_item wsi on wsi.fk_workspace_id=ws.id where ws.id='" + workspaceID + "' and wsi.id='" + workspaceItemID + "'";
dtab = Utility.DBExecuteDataTable(strQuery);
strRV = Utility.DataTableToDictionary(dtab);
strRV = strRV.Replace("[", "").Replace("]", "");
Utility.LogRequest(strRV, false, mTimer);
return strRV;
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message);
Utility.LogRequest(mMsg, true, mTimer);
return mMsg;
}
}
public string Items(string paths_identifier)
{
var mTimer = new Stopwatch();
mTimer.Start();
string strRV = string.Empty;
string strQuery = string.Empty;
euPaths clseuPaths = new euPaths();
DataTable dtab;
clseuPaths.requireUser(Context);
try
{
DataTable tblURI = new DataTable();
DataColumn colURI = new DataColumn("paths_identifier");
tblURI.Columns.Add(colURI);
DataRow rowURI = tblURI.NewRow();
rowURI[colURI] = paths_identifier;
tblURI.Rows.Add(rowURI);
strQuery = "select id from workspace where uri ='" + paths_identifier + "' ;";
int workspaceID = Utility.DBExecuteScalar(strQuery);
strQuery = "select uri paths_identifier from usr where id in (select fk_usr_id from workspace where id=" + workspaceID + " union select fk_usr_id from usr_workspace where fk_workspace_id = " + workspaceID + ");";
dtab = Utility.DBExecuteDataTable(strQuery);
strQuery = "select wsi.uri paths_identifier,wsi.dc_title,wsi.dc_description,wsi.dc_source,wsi.paths_thumbnail,wsi.type paths_type from workspace ws inner join workspace_item wsi on wsi.fk_workspace_id=ws.id where wsi.fk_workspace_id=" + workspaceID + " order by paths_order;";
DataTable dtab1 = Utility.DBExecuteDataTable(strQuery);
////Updated on 23 September to decode dc_source
//foreach (DataRow drow in dtab1.Rows)
//{
// drow["dc_source"] = HttpUtility.UrlEncode(Utility.FromBase64ForUrlString(Convert.ToString(drow["dc_source"])));
//}
////END Update
Dictionary<string, DataTable> myAdditionalTables = new Dictionary<string, DataTable>();
myAdditionalTables.Add("paths_authorised", dtab);
myAdditionalTables.Add("paths_items", dtab1);
strRV = Utility.DataTableToDictionary(tblURI, true, myAdditionalTables);
//strRV = strRV.Replace("[", "").Replace("]", "");
Utility.LogRequest(strRV, false, mTimer);
return strRV;
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message);
Utility.LogRequest(mMsg, true, mTimer);
return mMsg;
}
}
public string Item(string paths_identifier, string paths_item_identifier)
{
var mTimer = new Stopwatch();
mTimer.Start();
string strRV = string.Empty;
euPaths clseuPaths = new euPaths();
DataTable dtab;
clseuPaths.requireUser(Context);
try
{
string strQuery = "select wsi.uri paths_identifier,wsi.dc_title,wsi.dc_description,wsi.dc_source,wsi.paths_thumbnail,wsi.type paths_type from workspace ws inner join workspace_item wsi on wsi.fk_workspace_id=ws.id where ws.uri='" + paths_identifier + "' and wsi.uri='" + paths_item_identifier + "'";
dtab = Utility.DBExecuteDataTable(strQuery);
////Updated on 23 September to decode dc_source
//foreach (DataRow drow in dtab.Rows)
//{
// drow["dc_source"] = HttpUtility.UrlEncode(Utility.FromBase64ForUrlString(Convert.ToString(drow["dc_source"])));
//}
////END Update
strRV = Utility.DataTableToDictionary(dtab);
strRV = strRV.Replace("[", "").Replace("]", "");
Utility.LogRequest(strRV, false, mTimer);
return strRV;
}
catch (Exception ex)
{
var mMsg = Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message);
Utility.LogRequest(mMsg, true, mTimer);
return mMsg;
}
}
public string AddRating(int fk_rating_scale_id, string fk_rel_uri)
{
string strRV = string.Empty;
euPaths clseuPaths = new euPaths();
if (Context.Session["isAuthenticated"] == null)
{
clseuPaths.createTemporaryUser(Context);
}
try
{
string strQuery = "SELECT count(id) FROM rating WHERE fk_usr_id = '" + Convert.ToString(Context.Session["usr_id"]) + "' and fk_rel_uri = '" + fk_rel_uri + "' and isdeleted = false;";
int intResult = Utility.DBExecuteScalar(strQuery);
int mRatingId;
if (intResult > 0)
{
strQuery = "UPDATE rating SET fk_rating_scale_id = '" + fk_rating_scale_id + "' WHERE fk_usr_id = '" + Convert.ToString(Context.Session["usr_id"]) + "' AND fk_rel_uri = '" + fk_rel_uri + "' AND isdeleted = false;";
Utility.DBExecuteNonQuery(strQuery);
strQuery = "SELECT id FROM rating WHERE fk_usr_id = '" + Convert.ToString(Context.Session["usr_id"]) + "' and fk_rel_uri = '" + fk_rel_uri + "' AND isdeleted = false;";
mRatingId = Utility.DBExecuteScalar(strQuery);
}
else
{
strQuery = "INSERT INTO rating (fk_usr_id, fk_rating_scale_id, fk_rel_uri) VALUES ('" + Convert.ToString(Context.Session["usr_id"]) + "','" + fk_rating_scale_id + "','" + fk_rel_uri + "');SELECT currval('rating_id_seq');";
mRatingId = Utility.DBExecuteScalar(strQuery);
}
strQuery = "SELECT (SELECT COUNT(id) FROM rating WHERE fk_rating_scale_id = 2 AND fk_rel_uri = '" + fk_rel_uri + "' and isdeleted=false) AS likes, (SELECT COUNT(id) FROM rating WHERE fk_rating_scale_id = 1 AND fk_rel_uri = '" + fk_rel_uri + "' and isdeleted=false) AS dislikes";
DataTable dtab = Utility.DBExecuteDataTable(strQuery);
if (dtab.Rows.Count > 0)
{
strRV = Utility.DataTableToDictionary(dtab);
}
else
{
strRV = Utility.GetMsg(Utility.msgStatusCodes.QueryDidNotReturnRecords, "No comment was returned");
}
return strRV;
}
catch (Exception ex)
{
return Utility.GetMsg(Utility.msgStatusCodes.DatabaseSQLError, ex.Message);
}
}