597 lines
29 KiB
C#
597 lines
29 KiB
C#
|
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;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|