SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBsettingsManager.cs

306 lines
10 KiB
C#
Raw Permalink Normal View History

2024-02-22 16:43:59 +00:00
using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using System.Collections;
using System.Data;
namespace SafeMobileLib
{
public class DBsettingsManager: DBmanager
{
public static readonly string EMERGENCY_ONLY_KEY = "emergencyOnly";
public DBsettingsManager(string p_server, string p_dbname, string p_user, string p_password, string p_port)
: base(p_server, p_dbname, p_user, p_password, p_port)
{
}
public sqlResponse addSetting(int userIdx, string key, string value)
{
sqlResponse resp = sqlResponse.noUpdate;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = $"SELECT count(key) FROM \"userSettings\" where user_id = {userIdx} and key = '{key}'";
object result = null;
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
result = cmd.ExecuteScalar();
}
if (result != null && result.ToString() == "0")
{
command = "INSERT INTO \"userSettings\" (user_id, key, value)" +
$" VALUES({userIdx},'{key}','{value}')";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = sqlResponse.SQLerror;
}
return resp;
}
public string getSettingValue(int userIdx, string key)
{
string value = "";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = $"SELECT value FROM \"userSettings\" WHERE user_id= {userIdx} AND key='{key}'";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
value = reader.GetValue(0).ToString();
}
}
}
}
}
catch (Exception ex)
{
Utils.WriteLine("getSettingValue " + ex.ToString(), ConsoleColor.Red);
}
return value;
}
public sqlResponse updateSettingValue(int userIdx, string key, string value)
{
sqlResponse resp;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = $"SELECT COUNT(user_id) FROM \"userSettings\" WHERE user_id={userIdx} AND key='{key}'";
object temp = null;
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
temp = cmd.ExecuteScalar();
}
if (temp != null && temp.ToString() == "1")
{
command = $" UPDATE \"userSettings\" SET value='{value}'" +
$" WHERE user_id={userIdx} AND key='{key}'";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
}
else
{
Console.WriteLine("Setting -" + key + "- not found ... adding this for user with id:" + userIdx.ToString());
addSetting(userIdx, key, value);
resp = sqlResponse.done;
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = sqlResponse.SQLerror;
}
return resp;
}
public sqlResponse deleteSettingForUser(int userIdx)
{
sqlResponse resp;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = $"DELETE FROM \"userSettings\" WHERE user_id={userIdx}";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = sqlResponse.SQLerror;
}
return resp;
}
public Hashtable getAllSettings(int userIdx)
{
Hashtable resp = new Hashtable();
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = $"SELECT key, value FROM \"userSettings\" WHERE user_id = {userIdx}";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
string key = Reader.GetValue(0).ToString();
string value = Reader.GetValue(1).ToString();
if (!resp.ContainsKey(key))
resp.Add(key, value);
2024-02-22 16:43:59 +00:00
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.ToString());
}
return resp;
}
public void SaveSettingsForUser(int userIdx, Hashtable data)
{
StringBuilder sb = new StringBuilder();
foreach (DictionaryEntry Item in data)
{
String key = (string)Item.Key;
String value = (string)Item.Value;
sb.Append(
"UPDATE \"userSettings\" SET \"value\"='" + value + "' WHERE \"key\"='" + key + "' AND user_id = '" + userIdx + "'; "
+ " INSERT INTO \"userSettings\" (user_id, \"key\", \"value\") "
+ $" SELECT {userIdx}, '{key}', '{value}' "
+ $" WHERE NOT EXISTS (SELECT 1 FROM \"userSettings\" WHERE user_id={userIdx} AND \"key\"='{key}'); ");
}
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(sb.ToString(), connection))
{
cmd.ExecuteNonQuery();
}
string command = "DELETE FROM \"userSettings\" WHERE \"key\" LIKE 'onMap%' AND \"value\"='False';";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
}
public void AddDefaultSettings(int userIdx)
{
// start items
addSetting(userIdx, "startLar", "0");
addSetting(userIdx, "startLng", "0");
addSetting(userIdx, "startZoom", "2");
addSetting(userIdx, "startUnits", "0");
addSetting(userIdx, "startAddress", "1");
addSetting(userIdx, "startProxy", "false");
addSetting(userIdx, "startProxyIP", "67.207.214.190");
addSetting(userIdx, "startProxyPORT", "8085");
//history
addSetting(userIdx, "hisCompAddr", "true");
addSetting(userIdx, "hisShowAdr", "true");
addSetting(userIdx, "hisPlayTime", "500");
addSetting(userIdx, "hisPauseTime", "2000");
addSetting(userIdx, "hisStopTime", "300");
addSetting(userIdx, "hisQuick", "false");
addSetting(userIdx, "hisMinSpeed", "0");
//live
addSetting(userIdx, "liveCheck", "false");
addSetting(userIdx, "liveIdleTime", "20");
addSetting(userIdx, "liveNameDisplay", "true");
addSetting(userIdx, "liveMultiColor", "false");
addSetting(userIdx, "reportTime", "0");
addSetting(userIdx, "iconTheme", "pin");
}
}
}