SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBiconsManager.cs

509 lines
15 KiB
C#
Raw Permalink Normal View History

2024-02-22 16:43:59 +00:00
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Npgsql;
using System.Collections;
using System.IO;
using System.Data;
namespace SafeMobileLib.DBmanagers
{
public class DBiconsManager : DBmanager
{
public DBiconsManager(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)
{
}
/// <summary>
/// Type 1 = place and 2 = units
/// </summary>
/// <param name="IconName"></param>
/// <param name="Imagedata"></param>
/// <returns></returns>
public sqlResponse Insert_Place_Icons(String IconName, byte[] Imagedata)
{
sqlResponse resp;
string placetype_id = "";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "insert into placetype (type_name) VALUES('" + IconName + "')";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.ExecuteNonQuery();
}
command = "SELECT currval(pg_get_serial_sequence('placetype', 'type_id'))";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
placetype_id = cmd.ExecuteScalar().ToString();
}
command = "INSERT INTO newicons(icontype,id,imagedata) SELECT @Type, @Id, @ImageData";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.Parameters.Add("@Type", NpgsqlTypes.NpgsqlDbType.Integer).Value = 1;
cmd.Parameters.Add("@Id", NpgsqlTypes.NpgsqlDbType.Varchar).Value = placetype_id;
cmd.Parameters.Add("@ImageData", NpgsqlTypes.NpgsqlDbType.Bytea).Value = Imagedata;
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
resp = sqlResponse.SQLerror;
}
return resp;
}
/// <summary>
/// Type 1 = place and 2 = units
/// </summary>
/// <param name="IconName"></param>
/// <param name="Imagedata"></param>
/// <returns></returns>
public sqlResponse Insert_Units_Icons(String IconName, byte[] Imagedata)
{
sqlResponse resp;
string car_id = "";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "SELECT MAX(id) FROM car";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
car_id = cmd.ExecuteScalar().ToString();
}
Int64 carId = 1000;
Int64.TryParse(car_id, out carId);
// increment the new carId
carId = carId + 1;
command = "INSERT INTO car (id, name, pattern, displayed_name) "
+ " VALUES(" + (carId) + ",'" + IconName + "','custom_" + IconName.ToLower().Trim().Replace(' ', '_')
+ "','" + IconName + "')";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.ExecuteNonQuery();
}
command = "INSERT INTO newicons(icontype,id,imagedata) SELECT @Type, @Id, @ImageData";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.Parameters.Add("@Type", NpgsqlTypes.NpgsqlDbType.Integer).Value = 2;
cmd.Parameters.Add("@Id", NpgsqlTypes.NpgsqlDbType.Double).Value = carId;
cmd.Parameters.Add("@ImageData", NpgsqlTypes.NpgsqlDbType.Bytea).Value = Imagedata;
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = sqlResponse.SQLerror;
}
return resp;
}
public Byte[] Get_Place_Image(Int32 ID)
{
Byte[] productImageByte = null;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = $"SELECT ImageData FROM newicons where id={ID} and icontype=1";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
productImageByte = (Byte[])cmd.ExecuteScalar();
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
return productImageByte;
}
public Byte[] Get_Unit_Image(Int32 ID)
{
Byte[] productImageByte = null;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = $"SELECT ImageData FROM newicons where id={ID} and icontype=2";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
productImageByte = (Byte[])cmd.ExecuteScalar();
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
}
return productImageByte;
}
//need to update all old stuff one existing ID
public sqlResponse Delete_Units_Icons(Int32 ID, Int32 firstID)
{
sqlResponse resp;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = $"UPDATE vehicle SET driver_id= {firstID} WHERE driver_id={ID}";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.ExecuteNonQuery();
}
command = $"DELETE FROM car WHERE id={ID}";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.ExecuteNonQuery();
}
command = $"DELETE FROM newicons WHERE id={ID}";
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 List<IconTheme> GetIconThemes()
{
List<IconTheme> ret = new List<IconTheme>();
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand("SELECT id, name, prefix FROM icon_theme", connection))
{
using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
IconTheme obj = new IconTheme();
obj.Id = (int)dr.GetInt32(0);
obj.Name = dr.GetString(1);
obj.Prefix = dr.GetString(2);
ret.Add(obj);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
}
return ret;
}
public List<Place> getAllPlaceIcons()
{
List<Place> ret = new List<Place>();
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "SELECT type_name, type_id from placetype ORDER by type_name";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Place obj = new Place();
obj.type = dr.GetString(0);
obj.idx = dr.GetInt32(1);
obj.pic = "";
ret.Add(obj);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
}
return ret;
}
public List<Car> getAllUnitIcons()
{
List<Car> suidList = new List<Car>();
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "SELECT name, id, pattern, displayed_name from car ORDER by name";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Car obj = new Car();
obj.Name = dr.GetString(0);
obj.idx = dr.GetInt32(1);
obj.iconPattern = dr.IsDBNull(2) ? obj.Name.ToLower().Trim().Replace(' ', '_') : dr.GetString(2);
obj.DisplayedName = dr.IsDBNull(3) ? obj.Name : dr.GetString(3);
suidList.Add(obj);
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return suidList;
}
}
public class Place
{
public string name;
public string desc { get; set; }
public double lat { get; set; }
public double lng { get; set; }
public string type { get; set; }
public string pic { get; set; }
public string address { get; set; }
public Int32 idx { get; set; }
public Int32 useridx { get; set; }
public String Name
{
get
{
return name;
}
}
public String Type
{
get
{
return type;
}
}
}
public class IconTheme
{
private int id;
public int Id
{
get { return id; }
set { id = value; }
}
private string name;
public string Name
{
get { return name; }
set { name = value; }
}
private string prefix;
public string Prefix
{
get { return prefix; }
set { prefix = value; }
}
public IconTheme()
{
this.id = 0;
this.name = "";
this.prefix = "";
}
}
public class PlaceIcons
{
private int id;
public int Id
{
get { return id; }
set { id = value; }
}
private string placename;
public string Placename
{
get { return placename; }
set { placename = value; }
}
private string pic;
public string Pic
{
get { return pic; }
set { pic = value; }
}
public PlaceIcons(int id, string placename, string pic)
{
this.id = id;
this.placename = placename;
this.pic = pic;
}
}
public class UnitIcons
{
private int id;
public int Id
{
get { return id; }
set { id = value; }
}
private string unitname;
public string Unitname
{
get { return unitname; }
set { unitname = value; }
}
private string pic;
public string Pic
{
get { return pic; }
set { pic = value; }
}
public UnitIcons(int id, string unitname, string pic)
{
this.id = id;
this.unitname = unitname;
this.pic = pic;
}
}
public class UnitIconsComparer : IComparer
{
public UnitIconsComparer() : base() { }
int IComparer.Compare(object x, object y)
{
UnitIcons X = x as UnitIcons;
UnitIcons Y = y as UnitIcons;
return String.Compare(X.Unitname, Y.Unitname);
}
}
public class PlaceIconsComparer : IComparer
{
public PlaceIconsComparer() : base() { }
int IComparer.Compare(object x, object y)
{
PlaceIcons X = x as PlaceIcons;
PlaceIcons Y = y as PlaceIcons;
return String.Compare(X.Placename, Y.Placename);
}
}
}