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 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 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 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 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 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;
}
}