public static String UpdateOrdinalPosition(string listId, string listattribute_id, int ordinalposition)
{
string sql = "UPDATE listsettings.listattribute SET ordinalposition = CAST(@ordinalposition AS int) WHERE list_id= @list_id AND listattribute_id = @listattribute_id;";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("list_id", listId),
new KeyValuePair <string, string>("listattribute_id", listattribute_id),
new KeyValuePair <string, string>("ordinalposition", ordinalposition.ToString())
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
var httpErr = new SynapseHTTPError();
httpErr.ErrorCode = "HTTP.400";
httpErr.ErrorType = "Client Error";
httpErr.ErrorDescription = ex.ToString();
return(JsonConvert.SerializeObject(httpErr, Formatting.Indented, new JsonSerializerSettings {
NullValueHandling = NullValueHandling.Ignore
}));
}
return("Ordinal Position Updated");
}
public static String RemoveAttributeFromList(string listId, string attributename)
{
string sqlCheck = "SELECT ordinalposition FROM listsettings.listattribute WHERE list_id= @list_id AND attributename = @attributename;";
var paramListCheck = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("list_id", listId),
new KeyValuePair <string, string>("attributename", attributename),
};
DataSet dsCheck = DataServices.DataSetFromSQL(sqlCheck, paramListCheck);
DataTable dtCheck = new DataTable();
dtCheck = dsCheck.Tables[0];
int ordinalposition = 0;
try
{
ordinalposition = System.Convert.ToInt16(dtCheck.Rows[0]["ordinalposition"].ToString());
}
catch (Exception ex)
{
var a = ex;
}
string sql = "";
sql = @"UPDATE listsettings.listattribute
SET isselected = false,
ordinalposition = null
WHERE list_id = @list_id
AND attributename = @attributename;
UPDATE listsettings.listattribute
SET ordinalposition = ordinalposition - 1
WHERE list_id = @list_id
AND ordinalposition > CAST(@ordinalposition AS INT);
";
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("list_id", listId),
new KeyValuePair <string, string>("attributename", attributename),
new KeyValuePair <string, string>("ordinalposition", ordinalposition.ToString()),
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
var httpErr = new SynapseHTTPError();
httpErr.ErrorCode = "HTTP.400";
httpErr.ErrorType = "Client Error";
httpErr.ErrorDescription = ex.ToString();
return(JsonConvert.SerializeObject(httpErr, Formatting.Indented, new JsonSerializerSettings {
NullValueHandling = NullValueHandling.Ignore
}));
}
return("Attribute removed");
}
public static String AddAttributeToList(string listId, string attributename, int ordinalposition)
{
string sqlCheck = "SELECT COUNT(*) AS recCount FROM listsettings.listattribute WHERE list_id= @list_id AND attributename = @attributename;";
var paramListCheck = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("list_id", listId),
new KeyValuePair <string, string>("attributename", attributename),
};
DataSet dsCheck = DataServices.DataSetFromSQL(sqlCheck, paramListCheck);
DataTable dtCheck = new DataTable();
dtCheck = dsCheck.Tables[0];
int recs = 0;
try
{
recs = System.Convert.ToInt16(dtCheck.Rows[0]["recCount"].ToString());
}
catch { }
string sql = "";
if (recs == 0) //Do insert
{
sql = @"INSERT INTO listsettings.listattribute(listattribute_id, list_id, baseviewattribute_id, attributename, datatype, ordinalposition)
SELECT uuid_generate_v4() AS listattribute_id, @list_id, baseviewattribute_id, @attributename, datatype, CAST(@ordinalposition AS INT) AS ordinalposition
FROM listsettings.baseviewattribute WHERE attributename = @attributename
AND baseview_id IN (SELECT baseview_id FROM listsettings.listmanager WHERE list_id = @list_id);";
}
else //Do update
{
sql = @"UPDATE listsettings.listattribute
SET isselected = true,
ordinalposition = CAST(@ordinalposition AS INT)
WHERE list_id = @list_id
AND attributename = @attributename;";
}
var paramList = new List <KeyValuePair <string, string> >()
{
new KeyValuePair <string, string>("list_id", listId),
new KeyValuePair <string, string>("attributename", attributename),
new KeyValuePair <string, string>("ordinalposition", ordinalposition.ToString()),
};
try
{
DataServices.executeSQLStatement(sql, paramList);
}
catch (Exception ex)
{
var httpErr = new SynapseHTTPError();
httpErr.ErrorCode = "HTTP.400";
httpErr.ErrorType = "Client Error";
httpErr.ErrorDescription = ex.ToString();
return(JsonConvert.SerializeObject(httpErr, Formatting.Indented, new JsonSerializerSettings {
NullValueHandling = NullValueHandling.Ignore
}));
}
return("Attribute added");
}
