306 lines
10 KiB
C#
306 lines
10 KiB
C#
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);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
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");
|
|
}
|
|
}
|
|
}
|