public string GetObjectHistory(string synapsenamespace, string synapseentityname, string id)
{
string fieldList = SynapseEntityHelperServices.GetEntityAttributes(synapsenamespace, synapseentityname, "1");
if (string.IsNullOrEmpty(fieldList))
{
fieldList = " * ";
}
string keyAttribute = SynapseEntityHelperServices.GetEntityKeyAttribute(synapsenamespace, synapseentityname);
if (string.IsNullOrWhiteSpace(keyAttribute))
{
throw new InterneuronBusinessException(errorCode: 400, errorMessage: "Invalid Parameters supplied - unable to retrieve key attribute column", "Client Error");
}
string sql = "SELECT " + fieldList + " FROM entitystore." + synapsenamespace + "_" + synapseentityname + " WHERE " + keyAttribute + " = @p_keyAttributeValue" + " ORDER BY _sequenceid;";
var paramList = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("p_keyAttributeValue", id)
};
DataSet ds = new DataSet();
ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
var json = DataServices.ConvertDataTabletoJSONString(dt);
return(json);
}
public string ExecuteSQL([FromBody] string data)
{
var parameterList = new List <KeyValuePair <string, object> >();
int index = 0;
string returnValue = string.Empty;
if (!string.IsNullOrEmpty(data))
{
var dataDict = JsonConvert.DeserializeObject <SQLData>(data);
if (dataDict.parameters != null && dataDict.parameters.Count > 0)
{
foreach (var item in dataDict.parameters)
{
parameterList.Insert(index, new KeyValuePair <string, object>(item.name, item.value));
index++;
}
}
try
{
DataSet ds = DataServices.DataSetFromSQL(dataDict.query, parameterList, databaseName: dbStore);
returnValue = DataServices.ConvertDataTabletoJSONString(ds.Tables[0]);
}
catch (Exception ex)
{
HttpContext.Response.StatusCode = (int)HttpStatusCode.InternalServerError;
returnValue = "{ \"Status\": \"Failed\", \"Message\": \"" + ex.Message + ex.StackTrace + "\", \"Value\": \"\"}";
}
}
return(returnValue);
}
public string GetListByAttribute(string synapsenamespace, string synapseentityname, string synapseattributename, string attributevalue, string returnsystemattributes, string orderby, string limit, string offset, string filter)
{
if (string.IsNullOrWhiteSpace(returnsystemattributes))
{
returnsystemattributes = "0";
}
string limitString = "";
if (!string.IsNullOrEmpty(limit))
{
limitString = " LIMIT " + limit;
}
string orderBySting = "";
if (!string.IsNullOrEmpty(orderby))
{
orderBySting = " ORDER BY " + orderby;
}
string offsetString = "";
if (!string.IsNullOrEmpty(offset))
{
offsetString = " OFFSET " + offset;
}
//Stub for later use
string filterString = "";
if (!string.IsNullOrEmpty(filter))
{
filterString = " AND " + filter;
}
string fieldList = SynapseEntityHelperServices.GetEntityAttributes(synapsenamespace, synapseentityname, returnsystemattributes);
if (string.IsNullOrEmpty(fieldList))
{
fieldList = " * ";
}
string sql = "SELECT " + fieldList + " FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE " + synapseattributename + " = @p_keyAttributeValue" + filterString + orderBySting + limitString + offsetString + ";";
var paramList = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("p_keyAttributeValue", attributevalue)
};
DataSet ds = new DataSet();
ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
var json = DataServices.ConvertDataTabletoJSONString(dt);
return(json);
}
public string GetBaseViewListByAttribute(string baseviewname, string synapseattributename, string attributevalue, string orderby, string limit, string offset, string filter)
{
string limitString = "";
if (!string.IsNullOrEmpty(limit))
{
limitString = " LIMIT " + limit;
}
string orderBySting = "";
if (!string.IsNullOrEmpty(orderby))
{
orderBySting = " ORDER BY " + orderby;
}
string offsetString = "";
if (!string.IsNullOrEmpty(offset))
{
offsetString = " OFFSET " + offset;
}
//string fieldList = SynapseEntityHelperServices.GetBaseViewAttributes(baseviewname);
//if (string.IsNullOrEmpty(fieldList))
//{
// fieldList = " * ";
//}
//Stub for later use
string filterString = "";
if (!string.IsNullOrEmpty(filter))
{
filterString = " AND " + filter;
}
string sql = "SELECT * FROM baseview." + baseviewname + " WHERE " + synapseattributename + " = @p_keyAttributeValue" + filterString + orderBySting + limitString + offsetString + "; ";
var paramList = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("p_keyAttributeValue", attributevalue)
};
DataSet ds = new DataSet();
ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
var json = DataServices.ConvertDataTabletoJSONString(dt);
return(json);
}
private string GetReturnObjectByID(string synapsenamespace, string synapseentityname, int sequenceid)
{
string sql = "SELECT * FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE _sequenceid = @p_sequenceid;";
var paramList = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("p_sequenceid", sequenceid)
};
DataSet ds = new DataSet();
ds = DataServices.DataSetFromSQL(sql, paramList);
DataTable dt = ds.Tables[0];
var json = DataServices.ConvertDataTabletoJSONString(dt);
return(json);
}
private Tuple <string, string> PostObjectHandler(string synapsenamespace, string synapseentityname, string data, dynamic connectionObj = null, dynamic transactionObj = null)
{
//27Feb2020-RK
//RMF1 - Remove Feature- check if all relations have a key column value supplied in the post request
//RMF1
//DataTable dtRel = SynapseEntityHelperServices.GetEntityRelations(synapsenamespace, synapseentityname);
string keyAttribute = SynapseEntityHelperServices.GetEntityKeyAttribute(synapsenamespace, synapseentityname);
StringBuilder sb = new StringBuilder();
//dynamic dObj = JObject.Parse(data);
var dataDict = JsonConvert.DeserializeObject <Dictionary <string, object> >(data);
StringBuilder sbCols = new StringBuilder();
StringBuilder sbParams = new StringBuilder();
var paramList = new List <KeyValuePair <string, object> >();
string keyValue = "";
// int iRelationMatches = 0;
int iKeyMatches = 0;
var count = dataDict.Count();
//RK: 08012019 set_createdby : userid from access token
#region set_createdby
var useridClaim = User.FindFirst(_configuration["SynapseCore:Settings:TokenUserIdClaimType"]);
var userid = "unknown";
if (useridClaim != null)
{
userid = useridClaim.Value;
}
else if (dataDict.ContainsKey("_createdby"))
{
userid = Convert.ToString(dataDict["_createdby"]);
}
//check if dataDict has _createdby key
if (dataDict.ContainsKey("_createdby"))
{
//update they key value to userid from toke
dataDict["_createdby"] = userid;
}
else
{
//add _createdby key and set value to userid from token
dataDict.Add("_createdby", userid);
}
#endregion
foreach (KeyValuePair <string, object> item in dataDict)
{
//Count if Key Attribute matches item.Key
var a = item.Key;
var b = a;
if (keyAttribute == item.Key)
{
keyValue = item.Value.ToString();
iKeyMatches++;
}
//Count all Key Columns
//RMF1
/* foreach (DataRow row in dtRel.Rows)
* {
* sb.Append(row[0].ToString() + ",");
* if (item.Key == row[0].ToString())
* {
* iRelationMatches++;
* }
* }*/
if (item.Value != null)
{
sbCols.Append(item.Key + ",");
sbParams.Append("@p_" + item.Key + ",");
paramList.Insert(0, new KeyValuePair <string, object>("p_" + item.Key, item.Value));
}
else //Check it is not a key or relation column that is null
{
if (keyAttribute == item.Key)
{
throw new InterneuronBusinessException(errorCode: 400, errorMessage: "No value supplied for Key Attribute: " + keyAttribute, "Client Error");
}
//Relations
//RMF1
/* foreach (DataRow row in dtRel.Rows)
* {
* if (item.Key == row[0].ToString())
* {
* throw new InterneuronBusinessException(errorCode: 400, errorMessage: "No values supplpied for relation: " + item.Key, "Client Error");
*
* }
* }*/
}
}
//RMF1
/*if (iRelationMatches < dtRel.Rows.Count)
* {
* throw new InterneuronBusinessException(errorCode: 400, errorMessage: "Not all relations have values specified.Please ensure that you have values are specified for all of the following fields: " + StringManipulationServices.TrimEnd(sb.ToString(), ","), "Client Error");
*
* }*/
if (iKeyMatches < 1)
{
throw new InterneuronBusinessException(errorCode: 400, errorMessage: "No value supplied for Key Attribute: " + keyAttribute, "Client Error");
}
string strCols = "INSERT INTO entitystore." + synapsenamespace + "_" + synapseentityname + "(" +
sbCols.ToString().TrimEnd(',') +
") ";
string strParams = " VALUES (" +
sbParams.ToString().TrimEnd(',') +
") RETURNING _sequenceid;";
var sql = strCols + strParams;
DataSet ds = new DataSet();
ds = DataServices.DataSetFromSQL(sql, paramList, existingCon: connectionObj);
DataTable dt = ds.Tables[0];
int id = System.Convert.ToInt32(dt.Rows[0][0].ToString());
//Delete all occurances from entitystorematerialised
string sqlDelete = "DELETE FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE " + keyAttribute + " = @keyValue;";
var paramListDelete = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("keyValue", keyValue)
};
DataServices.executeSQLStatement(sqlDelete, paramListDelete, existingCon: connectionObj);
//Get all the entity's columns
string entityCols = "";
string sqlEntityCols = "SELECT entitysettings.getentityattributestring(@synapsenamespace, @synapseentityname, 1)";
var paramListEntityCols = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("synapsenamespace", synapsenamespace),
new KeyValuePair <string, object>("synapseentityname", synapseentityname)
};
DataSet dsEntityCols = new DataSet();
dsEntityCols = DataServices.DataSetFromSQL(sqlEntityCols, paramListEntityCols, existingCon: connectionObj);
DataTable dtEntityCols = dsEntityCols.Tables[0];
entityCols = dtEntityCols.Rows[0][0].ToString();
//Insert the newly inserted record into the materialised entity
string sqlMaterialisedInsert = string.Empty;
if (connectionObj == null)
{
sqlMaterialisedInsert = "INSERT INTO entitystorematerialised." + synapsenamespace + "_" + synapseentityname + "(" +
entityCols +
") "
+
" SELECT " +
entityCols +
" FROM entityview." + synapsenamespace + "_" + synapseentityname
+ " WHERE " + keyAttribute + " = @keyValue;";
}
else
{
sqlMaterialisedInsert = "INSERT INTO entitystorematerialised." + synapsenamespace + "_" + synapseentityname + "(" +
entityCols +
") "
+
" SELECT " +
entityCols +
" FROM entityview." + synapsenamespace + "_" + synapseentityname
+ " WHERE " + keyAttribute + " = @keyValue RETURNING *;";
}
var paramListMaterialisedInsert = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("keyValue", keyValue)
};
//DataServices.executeSQLStatement(sqlMaterialisedInsert, paramListMaterialisedInsert, existingCon: connectionObj);
var insertedDataInMaterial = string.Empty;
if (connectionObj == null)
{
DataServices.executeSQLStatement(sqlMaterialisedInsert, paramListMaterialisedInsert);
}
else
{
DataSet dsMaterilaized = DataServices.DataSetFromSQL(sqlMaterialisedInsert, paramListMaterialisedInsert, existingCon: connectionObj);
insertedDataInMaterial = DataServices.ConvertDataTabletoJSONString(dsMaterilaized.Tables[0]);
}
//Get the return object
//return GetReturnObjectByID(synapsenamespace, synapseentityname, id);
string returnedData = string.Empty;
if (connectionObj == null)
{
returnedData = GetReturnObjectByID(synapsenamespace, synapseentityname, id);
}
return(new Tuple <string, string>(returnedData, insertedDataInMaterial));
}
public string GetListByPost(string synapsenamespace, string synapseentityname, string returnsystemattributes, string orderby, string limit, string offset, string filter, [FromBody] string data)
{
dynamic results = JsonConvert.DeserializeObject <dynamic>(data);
string filters = results[0].filters.ToString();
StringBuilder filtersToApplySB = new StringBuilder();
filtersToApplySB.Append(" WHERE 1 = 1 ");
JArray obj = Newtonsoft.Json.JsonConvert.DeserializeObject <JArray>(filters);
foreach (var filterApplied in obj)
{
filtersToApplySB.Append(" AND (" + (string)filterApplied["filterClause"] + ")");
}
string filtersSQL = filtersToApplySB.ToString();
string filterparams = results[1].filterparams.ToString();
var paramListFromPost = new List <KeyValuePair <string, object> >();
JArray objParams = Newtonsoft.Json.JsonConvert.DeserializeObject <JArray>(filterparams);
foreach (var paramApplied in objParams)
{
if (paramApplied["paramValue"].Type == JTokenType.Date)
{
var temp = (DateTime)paramApplied["paramValue"];
var value = temp.ToString("yyyy-MM-ddTHH:mm:ss");
paramListFromPost.Insert(0, new KeyValuePair <string, object>((string)paramApplied["paramName"], value));
}
else
{
paramListFromPost.Insert(0, new KeyValuePair <string, object>((string)paramApplied["paramName"], (string)paramApplied["paramValue"]));
}
}
string limitString = "";
if (!string.IsNullOrEmpty(limit))
{
limitString = " LIMIT " + limit;
}
string orderBySting = "";
if (!string.IsNullOrEmpty(orderby))
{
orderBySting = " ORDER BY " + orderby;
}
string offsetString = "";
if (!string.IsNullOrEmpty(offset))
{
offsetString = " OFFSET " + offset;
}
if (string.IsNullOrWhiteSpace(returnsystemattributes))
{
returnsystemattributes = "0";
}
string fieldList = SynapseEntityHelperServices.GetEntityAttributes(synapsenamespace, synapseentityname, returnsystemattributes);
if (string.IsNullOrEmpty(fieldList))
{
fieldList = " * ";
}
//Stub for later use
string filterString = "";
if (!string.IsNullOrEmpty(filter))
{
filterString = " AND " + filter;
}
string sql = "SELECT * FROM (SELECT " + fieldList + " FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE 1=1 " + filterString + orderBySting + limitString + offsetString + ") entview " + filtersSQL + ";";
DataSet ds = new DataSet();
ds = DataServices.DataSetFromSQL(sql, paramListFromPost);
DataTable dt = ds.Tables[0];
var json = DataServices.ConvertDataTabletoJSONString(dt);
return(json);
}
