|
public static RunQuery ( |
||
| command | ||
| return | ||
/// <summary>
/// Deletes everything from the event_log table
/// </summary>
public void EventLogClear()
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_event_log_clear";
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
AddToLog("API - EventLogClear error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
/// <summary>
///
/// </summary>
public static void ProcessRecurring()
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_process_recurring";
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - ProcessRecurring error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
public static void ObjectTypePropertyOptionUpdate(string objectType, string propertyName, string newoption, string oldoption)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_object_type_property_option_update (@objectName, @propertyName, @newoption, @oldoption)";
command.Parameters.AddWithValue("@objectName", objectType);
command.Parameters.AddWithValue("@propertyName", propertyName);
command.Parameters.AddWithValue("@newoption", newoption);
command.Parameters.AddWithValue("@oldoption", oldoption);
try
{ OSAESql.RunQuery(command); }
catch (Exception ex)
{ Logging.GetLogger().AddToLog("API - ObjectTypePropertyOptionUpdate error: " + command.CommandText + " - error: " + ex.Message, true); }
}
}
/// <summary>
/// Add a state to an object type
/// </summary>
/// <param name="Name"></param>
/// <param name="Label"></param>
/// <param name="ObjectType"></param>
public static void ObjectTypeStateAdd(string ObjectType, string Name, string Label, string tooltip)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_object_type_state_add (@ObjectType, @Name, @Label, @Tooltip)";
command.Parameters.AddWithValue("@ObjectType", ObjectType);
command.Parameters.AddWithValue("@Name", Name);
command.Parameters.AddWithValue("@Label", Label);
command.Parameters.AddWithValue("@Tooltip", tooltip);
try
{ OSAESql.RunQuery(command); }
catch (Exception ex)
{ Logging.GetLogger().AddToLog("API - ObjectTypeStateAdd error: " + command.CommandText + " - error: " + ex.Message, true); }
}
}
/// <summary>
///
/// </summary>
public static void RunScheduledMethods()
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_run_scheduled_methods";
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - RunScheduledMethods error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
public static void ScreenObjectAdd(string screen, string objectName, string controlName)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_screen_object_add(@Screen, @ObjectName, @ControlName)";
command.Parameters.AddWithValue("@Screen", screen);
command.Parameters.AddWithValue("@ObjectName", objectName);
command.Parameters.AddWithValue("@ControlName", controlName);
try
{ OSAESql.RunQuery(command); }
catch (Exception ex)
{ Logging.GetLogger().AddToLog("ScreenObjectAdd error: " + command.CommandText + " - error: " + ex.Message, true); }
}
}
/// <summary>
/// Adds an image to the OSAE DB
/// </summary>
/// <param name="name">The name of the image this should not include the path or extension</param>
/// <param name="type">the type of the image e.g. jpg, png do not include the .</param>
/// <param name="imageData">the binary data of the image</param>
public int AddImage(string name, string type, byte[] imageData)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_image_add (@pimage_data, @pimage_name, @pimage_type)";
command.Parameters.AddWithValue("@pimage_data", imageData);
command.Parameters.AddWithValue("@pimage_name", name);
command.Parameters.AddWithValue("@pimage_type", type);
command.Parameters.Add(new MySqlParameter("@id", MySqlDbType.Int32));
command.Parameters["@id"].Direction = System.Data.ParameterDirection.Output;
DataSet ds = OSAESql.RunQuery(command);
return(int.Parse(ds.Tables[0].Rows[0][0].ToString()));
}
}
public static DataSet ObjectStateListGet(string objectName)
{
DataSet ds = new DataSet();
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "SELECT state_name,state_label FROM osae_v_object_state WHERE UPPER(object_name) = UPPER('" + objectName + "') OR UPPER(object_alias) = UPPER('" + objectName + "') ORDER BY state_label asc";
try
{
ds = OSAESql.RunQuery(command);
}
catch (Exception ex)
{ Logging.GetLogger().AddToLog("API - ObjectStateHistoryGet error: " + command.CommandText + " - error: " + ex.Message, true); }
}
return(ds);
}
/// <summary>
/// Update an existing event on an object type
/// </summary>
/// <param name="oldName"></param>
/// <param name="newName"></param>
/// <param name="label"></param>
/// <param name="objectType"></param>
public static void ObjectTypeEventUpdate(string oldName, string newName, string label, string objectType, string tooltip)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_object_type_event_update (@OldName, @NewName, @Label, @ObjectType, @Tooltip)";
command.Parameters.AddWithValue("@OldName", oldName);
command.Parameters.AddWithValue("@NewName", newName);
command.Parameters.AddWithValue("@Label", label);
command.Parameters.AddWithValue("@ObjectType", objectType);
command.Parameters.AddWithValue("@Tooltip", tooltip);
try
{ OSAESql.RunQuery(command); }
catch (Exception ex)
{ Logging.GetLogger().AddToLog("API - ObjectTypeEventUpdate error: " + command.CommandText + " - error: " + ex.Message, true); }
}
}
public static DataSet ObjectStateHistoryGet(string objectName, string from, string to)
{
DataSet ds = new DataSet();
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "SELECT history_timestamp, object_name, state_label FROM osae_v_object_state_change_history WHERE (UPPER(object_name) = UPPER('" + objectName + "') OR UPPER(object_alias) = UPPER('" + objectName + "')) AND history_timestamp BETWEEN '" + from + "' AND '" + to + "' ORDER BY history_timestamp asc";
try
{
ds = OSAESql.RunQuery(command);
}
catch (Exception ex)
{ Logging.GetLogger().AddToLog("API - ObjectStateHistoryGet error: " + command.CommandText + " - error: " + ex.Message, true); }
}
return(ds);
}
public static DataSet ObjectPropertyHistoryGet(string objectName, string propertyName, string from, string to)
{
DataSet ds = new DataSet();
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "SELECT history_timestamp, CASE property_datatype WHEN 'Boolean' THEN IF(property_value='TRUE', 1, 0) ELSE property_value END AS property_value FROM osae_v_object_property_history WHERE (UPPER(object_name) = UPPER('" + objectName + "') OR UPPER(object_alias) = UPPER('" + objectName + "')) and property_name = '" + propertyName + "' AND history_timestamp BETWEEN '" + from + "' AND '" + to + "' ORDER BY history_timestamp asc";
try
{
ds = OSAESql.RunQuery(command);
}
catch (Exception ex)
{ Logging.GetLogger().AddToLog("API - ObjectPropertyHistoryGet error: " + command.CommandText + " - error: " + ex.Message, true); }
}
return(ds);
}
public static void ObjectTypeEventScriptDelete(string eventScriptID)
{
MySqlCommand command = new MySqlCommand();
command.CommandText = "CALL osae_sp_object_type_event_script_delete (@pobjtypeeventscriptid)";
command.Parameters.AddWithValue("@pobjtypeeventscriptid", eventScriptID);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - ObjectTypeEventScriptDelete error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
/// <summary>
/// Delete an object
/// </summary>
/// <param name="Name"></param>
public static void ObjectDeleteByAddress(string address)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_object_delete_by_address (@Address)";
command.Parameters.AddWithValue("@Address", address);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - ObjectDeleteByAddress error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
/// <summary>
/// Delete an object type
/// </summary>
/// <param name="Name"></param>
public static void ObjectTypeDelete(string Name)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_object_type_delete (@Name)";
command.Parameters.AddWithValue("@Name", Name);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - ObjectTypeDelete error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
public static void PatternScriptDelete(string patternScriptID)
{
MySqlCommand command = new MySqlCommand();
command.CommandText = "CALL osae_sp_pattern_script_delete (@ppatternscriptid)";
command.Parameters.AddWithValue("@ppatternscriptid", patternScriptID);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - PatternScriptDelete error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
/// <summary>
/// Delete method from the queue
/// </summary>
/// <param name="methodID"></param>
public static void MethodQueueDelete(int methodID)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_method_queue_delete (@ID)";
command.Parameters.AddWithValue("@ID", methodID);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - MethodQueueDelete error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
/// <summary>
/// Deletes an item from a property array
/// </summary>
/// <param name="objectName"></param>
/// <param name="propertyName"></param>
/// <param name="propertyValue"></param>
public static void ObjectPropertyArrayDelete(string objectName, string propertyName, string propertyValue)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_object_property_array_delete (@ObjectName, @PropertyName, @PropertyValue)";
command.Parameters.AddWithValue("@ObjectName", objectName);
command.Parameters.AddWithValue("@PropertyName", propertyName);
command.Parameters.AddWithValue("@PropertyValue", propertyValue);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{ Logging.GetLogger().AddToLog("API - ObjectPropertyArrayDelete error: " + command.CommandText + " - error: " + ex.Message, true); }
}
}
/// <summary>
/// Add an entry to the event log table
/// </summary>
/// <param name="objectName">Object Name</param>
/// <param name="eventName">Event Name</param>
public void EventLogAdd(string objectName, string eventName, string parameter1 = null, string parameter2 = null)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_event_log_add (@ObjectName, @EventName, @FromObject, @DebugInfo, @Param1, @Param2)";
command.Parameters.AddWithValue("@ObjectName", objectName);
command.Parameters.AddWithValue("@EventName", eventName);
command.Parameters.AddWithValue("@FromObject", logName);
command.Parameters.AddWithValue("@DebugInfo", null);
command.Parameters.AddWithValue("@Param1", parameter1);
command.Parameters.AddWithValue("@Param2", parameter2);
try
{ OSAESql.RunQuery(command); }
catch (Exception ex)
{ AddToLog("API - EventLogAdd error: " + command.CommandText + " - error: " + ex.Message, true); }
}
}
/// <summary>
///
/// </summary>
/// <param name="scheduleName"></param>
public static void ScheduleRecurringDelete(string scheduleName)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_schedule_recurring_delete(@ScheduleName)";
command.Parameters.AddWithValue("@ScheduleName", scheduleName);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("ScheduleRecurringDelete error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
/// <summary>
/// Add a property to an object type
/// </summary>
/// <param name="Name"></param>
/// <param name="ParameterType"></param>
/// <param name="ObjectType"></param>
public static void ObjectTypePropertyAdd(string ObjectType, string Name, string ParameterType, string ParameterObjectType, string ParameterDefault, bool TrackHistory)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_object_type_property_add (@ObjectType, @Name, @ParameterType, @ParameterObjectType, @ParameterDefault, @TrackHistory)";
command.Parameters.AddWithValue("@ObjectType", ObjectType);
command.Parameters.AddWithValue("@Name", Name);
command.Parameters.AddWithValue("@ParameterType", ParameterType);
command.Parameters.AddWithValue("@ParameterObjectType", ParameterObjectType);
command.Parameters.AddWithValue("@ParameterDefault", ParameterDefault);
command.Parameters.AddWithValue("@TrackHistory", TrackHistory);
try
{ OSAESql.RunQuery(command); }
catch (Exception ex)
{ Logging.GetLogger().AddToLog("API - ObjectTypePropertyAdd error: " + command.CommandText + " - error: " + ex.Message, true); }
}
}
/// <summary>
/// propertyLabel is usually left null
/// </summary>
/// <param name="objectName"></param>
/// <param name="propertyName"></param>
/// <param name="propertyValue"></param>
/// <param name="propertyLabel"></param>
public static void ObjectPropertyScraperDelete(int scraperid)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_object_property_scraper_delete (@ScraperID)";
command.Parameters.AddWithValue("@ScraperID", scraperid);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - ObjectPropertyArrayAdd error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
/// <summary>
/// Add an entry to the degug table
/// </summary>
/// <param name="entry">String to add to the debug table</param>
public void DebugLogAdd(string entry)
{
try
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_debug_log_add (@Entry,@Process)";
command.Parameters.AddWithValue("@Entry", entry);
command.Parameters.AddWithValue("@Process", logName);
OSAESql.RunQuery(command);
}
}
catch
{
// Not a lot we can do if it fails here
}
}
public static void PatternAdd(string name)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_pattern_add (@Name)";
command.Parameters.AddWithValue("@Name", name);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - PatterAdd error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
/// <summary>
/// Returns a Dataset of all objects in a specified container
/// </summary>
/// <param name="ContainerName"></param>
/// <returns></returns>
public static OSAEObjectCollection GetObjectsByContainer(string ContainerName)
{
MySqlCommand command = new MySqlCommand();
DataSet dataset = new DataSet();
OSAEObject obj = new OSAEObject();
OSAEObjectCollection objects = new OSAEObjectCollection();
try
{
if (ContainerName == string.Empty)
{
command.CommandText = "SELECT object_name, object_alias, object_description, object_type, address, container_name, enabled, state_name, base_type, coalesce(time_in_state, 0) as time_in_state, last_updated FROM osae_v_object WHERE container_name is null ORDER BY object_name ASC";
}
else
{
command.CommandText = "SELECT object_name, object_alias, object_description, object_type, address, container_name, enabled, state_name, base_type, coalesce(time_in_state, 0) as time_in_state, last_updated FROM osae_v_object WHERE container_name=@ContainerName AND enabled = 1 ORDER BY object_name ASC";
command.Parameters.AddWithValue("@ContainerName", ContainerName);
}
dataset = OSAESql.RunQuery(command);
if (dataset.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dataset.Tables[0].Rows)
{
obj = new OSAEObject(dr["object_name"].ToString(), dr["object_alias"].ToString(), dr["object_description"].ToString(), dr["object_type"].ToString(), dr["address"].ToString(), dr["container_name"].ToString(), int.Parse(dr["enabled"].ToString()));
obj.State.Value = dr["state_name"].ToString();
obj.State.TimeInState = Convert.ToInt64(dr["time_in_state"]);
obj.BaseType = dr["base_type"].ToString();
obj.LastUpd = dr["last_updated"].ToString();
obj.Properties = OSAEObjectPropertyManager.GetObjectProperties(obj.Name);
obj.Methods = GetObjectMethods(obj.Name);
objects.Add(obj);
}
return(objects);
}
return(objects);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - GetObjectsByContainer error: " + ex.Message, true);
return(objects);
}
}
/// <summary>
/// Set the state of an object
/// </summary>
/// <param name="ObjectName">The name of the object to set the state of</param>
/// <param name="State">The state to set the object too</param>
/// <param name="source">Where the message was genreated from e.g. the plugin name (pName)</param>
public static void ObjectStateSet(string ObjectName, string State, string source)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_object_state_set(@ObjectName, @State, @FromObject, @DebugInfo)";
command.Parameters.AddWithValue("@ObjectName", ObjectName);
command.Parameters.AddWithValue("@State", State);
command.Parameters.AddWithValue("@FromObject", source);
command.Parameters.AddWithValue("@DebugInfo", null);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{ Logging.GetLogger().AddToLog("ObjectStateSet error: " + command.CommandText + " - error: " + ex.Message, true); }
}
}
public static void AddScriptProcessor(string Name, string pluginName)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_script_processor_add (@pname, @ppluginname)";
command.Parameters.AddWithValue("@pname", Name);
command.Parameters.AddWithValue("@ppluginname", pluginName);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - AddScriptProcessor error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
public static void ClearMethodQueue()
{
try
{
using (MySqlConnection connection = new MySqlConnection(Common.ConnectionString))
{
connection.Open();
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandText = "SET sql_safe_updates=0; DELETE FROM osae_method_queue;";
OSAESql.RunQuery(command);
}
}
}
catch (Exception ex)
{ Logging.GetLogger().AddToLog("Error clearing method queue details: \r\n" + ex.Message, true); }
}
public static void PatternMatchAdd(string pattern, string match)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_pattern_match_add (@Pattern, @Match)";
command.Parameters.AddWithValue("@Match", match);
command.Parameters.AddWithValue("@Pattern", pattern);
try
{
OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - PatternMatchAdd error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
}
/// <summary>
/// Add a method to an object type
/// </summary>
/// <param name="Name"></param>
/// <param name="Label"></param>
/// <param name="ObjectType"></param>
public static void ObjectTypeMethodAdd(string ObjectType, string Name, string Label, string ParamLabel1, string ParamLabel2, string ParamDefault1, string ParamDefault2)
{
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "CALL osae_sp_object_type_method_add (@ObjectType, @Name, @Label, @ParamLabel1, @ParamLabel2, @ParamDefault1, @ParamDefault2)";
command.Parameters.AddWithValue("@ObjectType", ObjectType);
command.Parameters.AddWithValue("@Name", Name);
command.Parameters.AddWithValue("@Label", Label);
command.Parameters.AddWithValue("@ParamLabel1", ParamLabel1);
command.Parameters.AddWithValue("@ParamLabel2", ParamLabel2);
command.Parameters.AddWithValue("@ParamDefault1", ParamDefault1);
command.Parameters.AddWithValue("@ParamDefault2", ParamDefault2);
try
{ OSAESql.RunQuery(command); }
catch (Exception ex)
{ Logging.GetLogger().AddToLog("API - ObjectTypeMethodAdd error: " + command.CommandText + " - error: " + ex.Message, true); }
}
}
public static DataSet ObjectPropertyListGet(string objectName)
{
DataSet ds = new DataSet();
using (MySqlCommand command = new MySqlCommand())
{
command.CommandText = "SELECT property_name FROM osae_v_object_property WHERE object_name = '" + objectName + "' ORDER BY property_name asc";
try
{
ds = OSAESql.RunQuery(command);
}
catch (Exception ex)
{
Logging.GetLogger().AddToLog("API - ObjectPropertyListGet error: " + command.CommandText + " - error: " + ex.Message, true);
}
}
return(ds);
}
