internal static DataTable GetTriggers()
{
DataTable dataTable = null;
DataTable tempDataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (!DBAdmin.TableExists("TRIGGERS"))
{
return(null);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM TRIGGERS";
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
return(dataTable);
}
internal static DataTable GetEventLog()
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
string query;
if (DBAdmin.TableExists("EVENT_LOG"))
{
query = "SELECT * FROM EVENT_LOG";
}
else
{
return(null);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(query, dbConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
return(dataTable);
}
internal static DataTable GetRulesetRules(int rulesetId)
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (!DBAdmin.TableExists("RULESET_RULES"))
{
return(null);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM [RULESET_RULES] WHERE [RulesetId] = @rulesetId";
cmd.Parameters.Add(new SQLiteParameter("@rulesetId", DbType.Int32)
{
Value = rulesetId
});
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
return(dataTable);
}
internal static DataTable GetSession(string sessionName)
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (!DBAdmin.TableExists("SESSIONS"))
{
return(null);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM SESSIONS WHERE [SessionName] = @sessionName";
cmd.Parameters.Add(new SQLiteParameter("@sessionName", DbType.String)
{
Value = sessionName
});
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
return(dataTable);
}
public static bool LoadRulesetFromDB(Session session)
{
DataTable ruleSetTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (!DBAdmin.TableExists("RULESETS"))
{
return(false);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM RULESETS";
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
ruleSetTable = tempDataTable;
}
}
}
}
}
if (ruleSetTable != null)
{
foreach (DataRow dr in ruleSetTable.Rows)
{
string rulesetName = dr["RulesetName"].ToString();
if (rulesetName.Equals(session.SessionName))
{
string rulesetIdstr = dr["RulesetId"].ToString();
string duplicatesCheckstr = dr["CheckDuplicates"].ToString();
string conflictsCheckstr = dr["CheckConflicts"].ToString();
if (Int32.TryParse(rulesetIdstr, out int rulesetId) &&
Int32.TryParse(duplicatesCheckstr, out int duplicatesCheck) &&
Int32.TryParse(conflictsCheckstr, out int conflictsCheck))
{
string deviceName = dr["DeviceName"].ToString();
var ruleset = new Ruleset(session, deviceName, rulesetId)
{
CheckDuplicates = duplicatesCheck == 1,
CheckConflicts = conflictsCheck == 1
};
ruleset.LoadRulesFromDB();
session.Ruleset = ruleset;
}
break;
}
}
return(true);
}
return(false);
}
internal static void LoadAudioClipsListFromDB(int sessionId, List <int> actionClipsList)
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (DBAdmin.TableExists("SESSION_CLIPS"))
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM SESSION_CLIPS WHERE [SessionId] = @sessionId;";
cmd.Parameters.Add(new SQLiteParameter("@sessionId", DbType.Int32)
{
Value = sessionId
});
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
}
if (dataTable != null)
{
foreach (DataRow row in dataTable.Rows)
{
if (Int32.TryParse(row["AudioClipId"].ToString(), out int audioClipId))
{
actionClipsList.Add(audioClipId);
}
}
Logger.AddLogEntry(LogCategory.INFO, "ActionClips Inventory Loaded");
}
}
public static bool LoadOnlineVoicesFromDB(List <string> voices)
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
string query;
if (DBAdmin.TableExists("ONLINE_VOICES"))
{
query = "SELECT * FROM ONLINE_VOICES";
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(query, dbConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
}
if (dataTable != null)
{
foreach (DataRow dr in dataTable.Rows)
{
voices.Add(dr["Voice"].ToString());
}
return(true);
}
else
{
return(false);
}
}
public static bool LoadSessionsFromDB(List <Session> sessions)
{
DataTable sessionsTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (!DBAdmin.TableExists("SESSIONS"))
{
return(false);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM SESSIONS";
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (sessionsTable = new DataTable())
{
sessionsTable.Load(reader);
}
}
}
}
}
if (sessionsTable != null)
{
foreach (DataRow dr in sessionsTable.Rows)
{
string sessionIdstr = dr["SessionId"].ToString();
string enableCastDisplaystr = dr["EnableCastDisplay"].ToString();
string isRulesetStr = dr["IsRuleset"].ToString();
string keepAliveStr = dr["KeepAlive"].ToString();
string speedRatioStr = dr["SpeedRatio"].ToString();
if (Int32.TryParse(sessionIdstr, out int sessionId) &&
Int32.TryParse(isRulesetStr, out int isRuleset) &&
Int32.TryParse(enableCastDisplaystr, out int enableCastDisplay) &&
Int32.TryParse(keepAliveStr, out int keepAlive) &&
Double.TryParse(speedRatioStr, out double speedRatio))
{
string sessionName = dr["SessionName"].ToString();
var session = new Session()
{
SessionId = sessionId,
SessionName = sessionName,
CastDisplayEnabled = enableCastDisplay == 1,
KeepAlive = keepAlive == 1,
SpeedRatio = speedRatio,
IsRuleset = isRuleset == 1
};
SessionClipsTableMgr.LoadAudioClipsListFromDB(session.SessionId, session.SessionAudioClipsList);
if (isRuleset == 1)
{
RulesetsTableMgr.LoadRulesetFromDB(session);
}
sessions.Add(session);
}
}
return(true);
}
return(false);
}
public static bool LoadTimeTriggerClipsFromDB(List <AudioClip> timeTriggerClips)
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
string query;
if (DBAdmin.TableExists("TIMECLIPS"))
{
query = "SELECT * FROM TIMECLIPS";
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(query, dbConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
}
if (dataTable != null)
{
timeTriggerClips.Clear();
string[] propsList = { "Category", "Label", "ButtonColour", "StatementText", "StatementAudioFile" };
bool allOK = true;
foreach (var prop in propsList)
{
if (!dataTable.Columns.Contains(prop))
{
Logger.AddLogEntry(LogCategory.ERROR, String.Format("LoadTimeTRiggerClipsFromDB: {0} not found.", prop));
allOK = false;
}
}
if (allOK)
{
foreach (DataRow dr in dataTable.Rows)
{
timeTriggerClips.Add(new AudioClip()
{
Category = dr["Category"].ToString(),
Label = dr["Label"].ToString(),
ButtonColour = dr["ButtonColour"].ToString(),
StateText = dr["StatementText"].ToString(),
StateAudioFile = dr["StatementAudioFile"].ToString(),
});
}
return(true);
}
else
{
return(false);
}
}
return(false);
}
public static bool LoadAudioClipsFromDB(List <AudioClip> audioClips)
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
string query;
if (DBAdmin.TableExists("AUDIOCLIPS"))
{
query = "SELECT * FROM AUDIOCLIPS";
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(query, dbConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
}
if (dataTable != null)
{
audioClips.Clear();
string[] propsList = { "ClipId", "Category", "Label", "DeviceName", "ButtonColour", "IsVisible",
"StatementText", "ConfirmationText", "CheckText", "StatementAudioFile", "ConfirmationAudioFile",
"CheckAudioFile", "Tooltip" };
bool allOK = true;
foreach (var prop in propsList)
{
if (!dataTable.Columns.Contains(prop))
{
Logger.AddLogEntry(LogCategory.ERROR, String.Format("LoadAudioClipsFromDB: {0} not found.", prop));
allOK = false;
}
}
if (allOK)
{
foreach (DataRow dr in dataTable.Rows)
{
string clipIdstr = dr["ClipId"].ToString();
string isVisiblestr = dr["IsVisible"].ToString();
if (Int32.TryParse(clipIdstr, out int clipId) && Int32.TryParse(isVisiblestr, out int isVisible))
{
audioClips.Add(new AudioClip()
{
ClipId = clipId,
Category = dr["Category"].ToString(),
Label = dr["Label"].ToString(),
DeviceName = dr["DeviceName"].ToString(),
ButtonColour = dr["ButtonColour"].ToString(),
IsVisible = isVisible == 1,
StateText = dr["StatementText"].ToString(),
ConfirmText = dr["ConfirmationText"].ToString(),
CheckText = dr["CheckText"].ToString(),
StateAudioFile = dr["StatementAudioFile"].ToString(),
ConfirmAudioFile = dr["ConfirmationAudioFile"].ToString(),
CheckAudioFile = dr["CheckAudioFile"].ToString(),
Tooltip = dr["Tooltip"].ToString(),
});
}
}
return(true);
}
else
{
return(false);
}
}
return(false);
}