SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBcontactsManager.cs

597 lines
29 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 DBcontactsManager : DBmanager
{
public DBcontactsManager(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)
{
}
#region sipIdRequest
string sipIdRequest =
"SELECT "
+ " userid, sip_id, login, type, gw_id, r_gw_id, r_gw_ip, gw_type, disp_backup, CASE WHEN sip_pswd IS NULL THEN 'safemobile123' else sip_pswd END AS sip_pswd "
+ " FROM "
+ "( "
+ " SELECT "
+ " userid, sm.sip_id AS sip_id, login , " + (int)ContactType.USER + " AS type, -1 AS gw_id, -1 AS r_gw_id, '0.0.0.0' as r_gw_ip, -1 AS gw_type, backup_user_id as disp_backup, null as sip_pswd "
+ " FROM users u"
+ " JOIN sip_manager sm ON u.userid = sm.id"
+ " WHERE user_type <> " + (int)USERTYPE.Admin + "AND sm.sip_id IS NOT NULL AND sm.type = " + (int)ContactType.USER
+ "UNION "
+ " SELECT "
+ " g.cps_id, sm.sip_id AS sip_id, g.name, " + (int)ContactType.GROUP + " AS type, r.\"GW_ID\" AS gw_id, r.\"ID\" AS r_gw_id, r.\"IP\" AS r_gw_ip, r.gw_type, -1 as disp_backup, null as sip_pswd "
+ " FROM groups g "
+ " JOIN sip_manager sm ON g.id = sm.id"
+ " LEFT JOIN radio_gw r ON g.r_gw_id = r.\"ID\" "
+ " WHERE g.type <> " + (int)GroupType.CATEGORY.value + " AND sm.type = " + (int)ContactType.GROUP
+ "UNION "
+ " SELECT "
+ " sb.imei::integer, sm.sip_id as sip_id, v.name, " + (int)ContactType.UNIT + " AS type, s.gateway_id AS gw_id, s.radio_gw_id AS r_gw_id, rg.\"IP\" AS r_gw_ip, rg.gw_type, -1 as disp_backup, scm.sip_pswd as sip_pswd "
+ " FROM vehicle v "
+ " JOIN sip_manager sm ON v.id = sm.id"
+ " LEFT JOIN subscriber sb ON sb.sc_id = v.id "
+ " LEFT JOIN subscriber_mobile scm ON scm.sc_id = sb.sc_id"
+ " LEFT JOIN subs_gateway s ON s.sc_id = v.id "
+ " LEFT JOIN radio_gw rg ON rg.\"ID\" = s.radio_gw_id "
+ " WHERE sm.type = " + (int)ContactType.UNIT
+ " UNION "
+ " SELECT "
+ " rg.imei as userid, sip.sip_id as sip_id, rg.name, " + (int)ContactType.GPS_ID + " AS type,rg.\"GW_ID\" AS gw_id, rg.\"ID\" AS r_gw_id, rg.\"IP\" AS r_gw_ip, rg.gw_type, -1 as disp_backup, null as sip_pswd "
+ " FROM radio_gw rg "
+ " JOIN sip_manager sip ON rg.\"ID\" = sip.id"
+ " WHERE sip.type = " + (int)ContactType.RADIOGATEWAY
+ " {0}"
+ " ) SIP "
+ " ORDER BY type, login ";
#endregion
public Dictionary<Int32,ContactLinx> getAllSipIds(RADIOTYPE radioType)
{
Dictionary<Int32, ContactLinx> sipIds = new Dictionary<int, ContactLinx>();
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format(sipIdRequest, ""), connection))
{
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
ContactLinx sip_contact = new ContactLinx()
{
Sippwd = Reader.GetString(9),
Id = Reader.GetInt32(0),
SipId = Reader.GetInt32(1),
Name = Reader.GetString(2),
Type = (ContactType)Reader.GetInt32(3),
GWandRadioGW = (Reader.IsDBNull(4) ? "-1" : Reader.GetInt32(4).ToString()) + "." + (Reader.IsDBNull(5) ? "-1" : Reader.GetInt32(5).ToString()),
IP = (Reader.IsDBNull(6)) ? "0.0.0.0" : Reader.GetString(6),
GWtype = (Reader.IsDBNull(7) || Reader.GetInt32(7) == -1) ? GatewayType.Unknown : (GatewayType)Reader.GetInt32(7),
DispatcherBackup = Reader.GetInt32(8)
};
//excera hack
if (radioType == RADIOTYPE.EXCERA)
{
sip_contact.SipId = sip_contact.Id;
}
if (!sipIds.ContainsKey(sip_contact.SipId))
sipIds.Add(sip_contact.SipId, sip_contact);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
return sipIds;
}
public List<ContactLinx> getContactsForSIPgw(int radioGwID)
{
List<ContactLinx> contactsLits = new List<ContactLinx>();
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(string.Format(sipIdRequest, " AND rg.\"ID\" = " + radioGwID), connection))
{
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
ContactLinx sip_contact = new ContactLinx()
{
Sippwd = Reader.GetString(9),
Id = Reader.GetInt32(0),
SipId = Reader.GetInt32(1),
Name = Reader.GetString(2),
Type = (ContactType)Reader.GetInt32(3),
GWandRadioGW = (Reader.IsDBNull(4) ? "-1" : Reader.GetInt32(4).ToString()) + "." + (Reader.IsDBNull(5) ? "-1" : Reader.GetInt32(5).ToString()),
IP = (Reader.IsDBNull(6)) ? "0.0.0.0" : Reader.GetString(6),
GWtype = (Reader.IsDBNull(7) || Reader.GetInt32(7) == -1) ? GatewayType.Unknown : (GatewayType)Reader.GetInt32(7),
DispatcherBackup = Reader.GetInt32(8)
};
contactsLits.Add(sip_contact);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
return contactsLits;
}
public List<ContactLinx> getAllContactsForCurrentUnit(string veh)
{
List<ContactLinx> contactsLits = new List<ContactLinx>();
string command = "SELECT mc.contact_id, alc.login, alc.type, alc.int_type, v.driver_id"
+ " FROM mobile_contacts mc"
+ " LEFT JOIN vehicle v on mc.contact_id||mc.contact_type = v.id||'" + ContactType.UNIT.ToString() + "' "
+ " JOIN"
+ " (SELECT userid,login, type, int_type FROM"
+ " (SELECT userid, login , '" + ContactType.USER.ToString() + "' AS type , " + (int)ContactType.USER + " as int_type FROM users WHERE user_type = " + (int)USERTYPE.Dispatcher
+ " UNION"
+ " SELECT id, name, '" + ContactType.GROUP.ToString() + "' AS type, " + (int)ContactType.GROUP + " as int_type FROM groups WHERE type IN ( " + (int)GroupType.MOBILETALK.value + "," + (int)GroupType.RADIOTALK.value + ")"
+ " UNION"
+ " SELECT id, name, '" + ContactType.UNIT.ToString() + "' AS type, " + (int)ContactType.UNIT + " as int_type FROM vehicle /*where name not self*/"
+ " ) contacts"
+ " ORDER BY type) alc ON alc.userid = mc.contact_id AND alc.type = mc.contact_type"
+ $" WHERE mc.sc_id = {veh} "
+ " ORDER BY alc.login";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ContactLinx ctc = new ContactLinx()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Type = (ContactType)reader.GetInt32(3),
IntType = reader.GetInt32(3),
ImageID = reader.IsDBNull(4) ? -1 : reader.GetInt32(4)
};
contactsLits.Add(ctc);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
return contactsLits;
}
public List<ContactLinx> getAllContactsCanCallUnit(string veh)
{
List<ContactLinx> contactsLits = new List<ContactLinx>();
string command = "SELECT mc.sc_id, alc.login, alc.type, alc.int_type, v.driver_id"
+ " FROM mobile_contacts mc"
+ " LEFT JOIN vehicle v on mc.sc_id||mc.contact_type = v.id||'" + ContactType.UNIT.ToString() + "' "
+ " JOIN"
+ " (SELECT userid,login, type, int_type FROM"
+ " (SELECT userid, login , '" + ContactType.USER.ToString() + "' AS type , " + (int)ContactType.USER + " as int_type FROM users WHERE user_type = " + (int)USERTYPE.Dispatcher
+ " UNION"
+ " SELECT id, name, '" + ContactType.GROUP.ToString() + "' AS type, " + (int)ContactType.GROUP + " as int_type FROM groups WHERE type IN ( " + (int)GroupType.MOBILETALK.value + "," + (int)GroupType.RADIOTALK.value + ")"
+ " UNION"
+ " SELECT id, name, '" + ContactType.UNIT.ToString() + "' AS type, " + (int)ContactType.UNIT + " as int_type FROM vehicle /*where name not self*/"
+ " ) contacts"
+ " ORDER BY type) alc ON alc.userid = mc.sc_id AND alc.type = mc.contact_type"
+ $" WHERE mc.contact_id = {veh} "
+ " ORDER BY alc.login";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
ContactLinx ctc = new ContactLinx()
{
Id = Reader.GetInt32(0),
Name = Reader.GetString(1),
Type = (ContactType)Reader.GetInt32(3),
IntType = Reader.GetInt32(3),
ImageID = Reader.IsDBNull(4) ? -1 : Reader.GetInt32(4)
};
contactsLits.Add(ctc);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
return contactsLits;
}
public List<ContactLinx> getAllContactsForCurrentSipId(string Imei)
{
List<ContactLinx> contactsLits = new List<ContactLinx>();
string command = "SELECT mc.contact_id, alc.login, alc.type, alc.int_type, v.driver_id, alc.sip_id, alc.gw_type"
+ " FROM mobile_contacts mc"
+ " JOIN subscriber s ON s.sc_id = mc.sc_id"
+ " LEFT JOIN vehicle v on mc.contact_id||mc.contact_type = v.id||'" + ContactType.UNIT.ToString() + "' "
+ " JOIN"
+ " (SELECT userid,login, type, int_type, sip_id, gw_type FROM"
+ " (SELECT userid, login , '" + ContactType.USER.ToString() + "' AS type , " + (int)ContactType.USER + " as int_type, sm.sip_id, -1 as gw_type FROM users u"
+ " JOIN sip_manager sm ON u.userid = sm.id"
+ " WHERE user_type = " + (int)USERTYPE.Dispatcher + " AND sm.type = " + (int)ContactType.USER
+ " UNION"
+ " SELECT g.id, g.name, '" + ContactType.GROUP.ToString() + "' AS type, " + (int)ContactType.GROUP + " as int_type, sm.sip_id as sip_id, rw.gw_type as gw_type "
+ " FROM groups g"
+ " JOIN sip_manager sm ON g.id = sm.id"
+ " join radio_gw rw on g.r_gw_id = rw.\"ID\""
+ " WHERE g.type IN ( " + (int)GroupType.MOBILETALK.value + "," + (int)GroupType.RADIOTALK.value + ") and sm.type = " + (int)ContactType.GROUP
+ " UNION"
+ " SELECT vh.id, name, '" + ContactType.UNIT.ToString() + "' AS type, " + (int)ContactType.UNIT + " as int_type, sm.sip_id as sip_id, -1 as gw_type "
+ " FROM vehicle vh "
+ " JOIN sip_manager sm ON vh.id = sm.id"
+ " join subscriber sr on sr.sc_id = vh.id "
+ $" WHERE sm.type = {(int)ContactType.UNIT}"
+ " ) contacts"
+ " ORDER BY type) alc ON alc.userid = mc.contact_id AND alc.type = mc.contact_type"
+ $" WHERE s.imei = '{Imei}'"
+ " ORDER BY alc.type";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
ContactLinx ctc = new ContactLinx()
{
Id = Reader.GetInt32(0),
Name = Reader.GetString(1),
Type = (ContactType)Reader.GetInt32(3),
IntType = Reader.GetInt32(3),
ImageID = Reader.IsDBNull(4) ? -1 : Reader.GetInt32(4),
SipId = Reader.IsDBNull(5) ? -1 : Reader.GetInt32(5),
GWtype = Reader.IsDBNull(6) ? GatewayType.Unknown : (GatewayType)Reader.GetInt32(6)
};
contactsLits.Add(ctc);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
return contactsLits;
}
public List<ContactLinx> getAllOtherContactsForCurrentUnit(string veh)
{
List<ContactLinx> contactsListă = new List<ContactLinx>();
string command = "SELECT userid, login, alc.type, int_type, v.driver_id FROM"
+ " (SELECT userid, login, type, int_type FROM"
+ " (SELECT userid, login , '" + ContactType.USER.ToString() + "' AS type , " + (int)ContactType.USER + " as int_type FROM users WHERE user_type = " + (int)USERTYPE.Dispatcher
+ " UNION"
+ " SELECT id, name, '" + ContactType.GROUP.ToString() + "' AS type, " + (int)ContactType.GROUP + " as int_type FROM groups WHERE type IN ( " + (int)GroupType.MOBILETALK.value + "," + (int)GroupType.RADIOTALK.value + ")"
+ " UNION"
+ " SELECT id, name, '" + ContactType.UNIT.ToString() + "' AS type, " + (int)ContactType.UNIT + " as int_type FROM vehicle "
+ " WHERE id <> " + veh
+ " ) contacts"
+ " ORDER BY type) alc"
+ " LEFT JOIN vehicle v on userid||alc.type = v.id||'" + ContactType.UNIT.ToString() + "' "
+ " WHERE alc.userid || alc.type || '" + veh + "' NOT IN"
+ " ("
+ " SELECT mc.contact_id || mc.contact_type || mc.sc_id FROM mobile_contacts mc"
+ " )"
+ " ORDER BY alc.login";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
ContactLinx ctc = new ContactLinx()
{
Id = Reader.GetInt32(0),
Name = Reader.GetString(1),
Type = (ContactType)Reader.GetInt32(3),
IntType = Reader.GetInt32(3),
ImageID = Reader.IsDBNull(4) ? -1 : Reader.GetInt32(4)
};
contactsListă.Add(ctc);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
return contactsListă;
}
public List<ContactLinx> getAllContactsCannotCallUnit(string veh)
{
List<ContactLinx> contactsListă = new List<ContactLinx>();
string command = "SELECT userid, login, alc.type, int_type, v.driver_id FROM"
+ " (SELECT userid, login, type, int_type FROM"
+ " ("
+ " SELECT id as userid, name as login, '" + ContactType.UNIT.ToString() + "' AS type, " + (int)ContactType.UNIT + " as int_type FROM vehicle "
+ $" WHERE id <> {veh}"
+ " ORDER BY type"
+ " ) contacts"
+ " ORDER BY type) alc"
+ " LEFT JOIN vehicle v on userid||alc.type = v.id||'" + ContactType.UNIT.ToString() + "' "
+ $" WHERE alc.userid || alc.type || '{veh}' NOT IN"
+ " ( "
+ " SELECT mc.sc_id || mc.contact_type || mc.contact_id FROM mobile_contacts mc"
+ " ) "
+ " ORDER BY alc.login";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
ContactLinx ctc = new ContactLinx()
{
Id = Reader.GetInt32(0),
Name = Reader.GetString(1),
Type = (ContactType)Reader.GetInt32(3),
IntType = Reader.GetInt32(3),
ImageID = Reader.IsDBNull(4) ? -1 : Reader.GetInt32(4)
};
contactsListă.Add(ctc);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
}
return contactsListă;
}
// add connection between contact and vehicle
public vehResponse addVehicleContactConnection(string vehId, List<ContactLinx> contacts)
{
vehResponse resp = vehResponse.done;
StringBuilder command = new StringBuilder(1024);
foreach(var contact in contacts)
{
command.Append($"INSERT INTO mobile_contacts(sc_id, contact_id,contact_type, contact_type_id) "
+ $"VALUES({vehId},{contact.Id},'{contact.Type}',{contact.IntType});");
}
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command.ToString(), connection))
{
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = vehResponse.SQLerror;
}
return resp;
}
//remove connection between contact and vehicle
public vehResponse removeVehicleContactConnection(string vehId, List<ContactLinx> contacts)
{
vehResponse resp = vehResponse.done;
StringBuilder command = new StringBuilder(1024);
foreach (var contact in contacts)
{
command.Append($"DELETE FROM mobile_contacts WHERE sc_id = {vehId} and contact_id = {contact.Id} and contact_type ='{contact.Type}';");
}
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command.ToString(), connection))
{
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = vehResponse.SQLerror;
}
return resp;
}
// add connection between contact and vehicle
public vehResponse addContactVehicleConnection(string vehId, List<ContactLinx> contacts)
{
vehResponse resp = vehResponse.done;
StringBuilder command = new StringBuilder(1024);
foreach (var contact in contacts)
{
command.Append($"INSERT INTO mobile_contacts(contact_id, sc_id,contact_type, contact_type_id) "
+ $"VALUES({vehId},{contact.Id},'{contact.Type}',{contact.IntType});");
}
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command.ToString(), connection))
{
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = vehResponse.SQLerror;
}
return resp;
}
//remove connection between contact and vehicle
public vehResponse removeContactVehicleConnection(string vehId, List<ContactLinx> contacts)
{
vehResponse resp = vehResponse.done;
StringBuilder command = new StringBuilder(1024);
foreach (var contact in contacts)
{
command.Append($"DELETE FROM mobile_contacts WHERE contact_id = {vehId} and sc_id = {contact.Id} and contact_type ='{contact.Type}';");
}
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command.ToString(), connection))
{
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = vehResponse.SQLerror;
}
return resp;
}
}
}