1463 lines
54 KiB
C#
1463 lines
54 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using Npgsql;
|
|||
|
|
using System.Collections;
|
|||
|
|
|
|||
|
|
namespace SafeMobileLib
|
|||
|
|
{
|
|||
|
|
public class DBgroupsManager:DBmanager
|
|||
|
|
{
|
|||
|
|
public DBgroupsManager(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 AddNewGroup(string name,Int32 cps_id,Int32 type, int r_gw_id, out sqlResponse resp)
|
|||
|
|
{
|
|||
|
|
object temp = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = $"SELECT count(\"name\") FROM groups where name='{name}'";
|
|||
|
|
using (NpgsqlCommand cmd2 = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
temp = cmd2.ExecuteScalar();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (temp.ToString() == "0")
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
string groupid = string.Empty;
|
|||
|
|
command = $"INSERT INTO groups (\"name\",cps_id,type,r_gw_id) VALUES ('{name}',{cps_id},{type},{(r_gw_id > 0 ? r_gw_id.ToString() : "null") }) RETURNING id;";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
groupid = cmd.ExecuteScalar().ToString();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (type != (int)GroupType.CATEGORY.value)
|
|||
|
|
{
|
|||
|
|
command = $"INSERT INTO sip_manager (sip_id,id,type) VALUES((SELECT max(sip_id)+1 FROM sip_manager),{groupid},{(int)ContactType.GROUP})";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
resp = sqlResponse.done;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
resp = sqlResponse.noUpdate;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ex.ToString(), ConsoleColor.Red);
|
|||
|
|
resp = sqlResponse.SQLerror;
|
|||
|
|
throw ex;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Group> GetAllGroups()
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
return GetAllGroups(string.Empty);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Group> GetAllGroups(string gt)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
List<Group> ret = new List<Group>();
|
|||
|
|
string where = "";
|
|||
|
|
GroupType groupType;
|
|||
|
|
if (gt != string.Empty)
|
|||
|
|
{
|
|||
|
|
groupType = GroupType.FromDbName(gt);
|
|||
|
|
where = string.Format("where type ={0}", (int)groupType.value);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
string command = "SELECT grp.id,grp.name,grp.cps_id,grp.type, grp.r_gw_id, r.name "
|
|||
|
|
+ "FROM groups as grp "
|
|||
|
|
+ " LEFT JOIN radio_gw r ON r.\"ID\" = coalesce(grp.r_gw_id, -1) "
|
|||
|
|
+ where +
|
|||
|
|
" order by grp.name";
|
|||
|
|
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())
|
|||
|
|
{
|
|||
|
|
Group g = new Group()
|
|||
|
|
{
|
|||
|
|
Id = Reader.IsDBNull(0) ? -1 : Reader.GetInt32(0),
|
|||
|
|
Name = Reader.IsDBNull(1) ? "" : Reader.GetString(1),
|
|||
|
|
CPS_Id = Reader.IsDBNull(2) ? -1 : Reader.GetInt32(2),
|
|||
|
|
Type = Reader.IsDBNull(3) ? -1 : Reader.GetInt32(3),
|
|||
|
|
RgwID = Reader.IsDBNull(4) ? -1 : Reader.GetInt32(4),
|
|||
|
|
GatewayName = Reader.IsDBNull(5) ? "" : Reader.GetString(5)
|
|||
|
|
};
|
|||
|
|
|
|||
|
|
|
|||
|
|
ret.Add(g);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return ret;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#region Group<=>Unit Relation
|
|||
|
|
|
|||
|
|
public List<Vehicles> getAllUnitsAndDispatchersForCurrentGroup(string grp)
|
|||
|
|
{
|
|||
|
|
List<Vehicles> unitsLits = new List<Vehicles>();
|
|||
|
|
|
|||
|
|
string command = "SELECT id, name, icon, type "
|
|||
|
|
+ " FROM ("
|
|||
|
|
+ " SELECT veh.id as id, veh.name as name, veh.driver_id as icon, 0 as type"
|
|||
|
|
+ " FROM vehicle veh"
|
|||
|
|
+ " INNER JOIN vehicle_group vg ON vg.sc_id = veh.id"
|
|||
|
|
+ " WHERE vg.grp_ip = " + grp
|
|||
|
|
+ " AND veh.type = " + (int)DeviceType.SMARTPHONE
|
|||
|
|
+ " UNION"
|
|||
|
|
+ " SELECT usr.userid, usr.login, 0, 1 as type"
|
|||
|
|
+ " FROM users usr"
|
|||
|
|
+ " INNER JOIN users_group ug ON ug.userid = usr.userid"
|
|||
|
|
+ " WHERE ug.grp_id = " + grp
|
|||
|
|
+ " ) as a ORDER BY a.name";
|
|||
|
|
|
|||
|
|
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())
|
|||
|
|
{
|
|||
|
|
Vehicles veh = new Vehicles()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
VehName = Reader.GetString(1),
|
|||
|
|
ImgID = Reader.GetInt32(2),
|
|||
|
|
Type = Reader.GetInt32(3)
|
|||
|
|
};
|
|||
|
|
unitsLits.Add(veh);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return unitsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Vehicles> getAllOtherUnitsAndDispatchersForCurrentGroup(string grp)
|
|||
|
|
{
|
|||
|
|
List<Vehicles> unitsLits = new List<Vehicles>();
|
|||
|
|
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = "SELECT id, name, icon, type "
|
|||
|
|
+ " FROM ("
|
|||
|
|
+ " SELECT veh.id as id, veh.name as name, veh.driver_id as icon, 0 as type"
|
|||
|
|
+ " FROM vehicle veh"
|
|||
|
|
+ " WHERE veh.id NOT IN (SELECT sc_id FROM vehicle_group WHERE grp_ip = " + grp + ")"
|
|||
|
|
+ " AND veh.type = " + (int)DeviceType.SMARTPHONE
|
|||
|
|
+ " UNION"
|
|||
|
|
+ " SELECT usr.userid as id, usr.login as name, 0 as icon, 1 as type"
|
|||
|
|
+ " FROM users usr"
|
|||
|
|
+ " WHERE usr.userid NOT IN (SELECT userid FROM users_group WHERE grp_id = " + grp + ")"
|
|||
|
|
+ " AND usr.user_type <> 1"
|
|||
|
|
+ " ) as a ORDER BY a.name";
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|||
|
|
{
|
|||
|
|
while (Reader.Read())
|
|||
|
|
{
|
|||
|
|
Vehicles veh = new Vehicles()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
VehName = Reader.GetString(1),
|
|||
|
|
ImgID = Reader.GetInt32(2),
|
|||
|
|
Type = Reader.GetInt32(3)
|
|||
|
|
};
|
|||
|
|
unitsLits.Add(veh);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return unitsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Group> getAllGroupsForCurrentUnit(string veh)
|
|||
|
|
{
|
|||
|
|
List<Group> groupsLits = new List<Group>();
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = "SELECT g.id, g.name"
|
|||
|
|
+ " FROM groups g"
|
|||
|
|
+ " INNER JOIN vehicle_group vg ON vg.grp_ip = g.id"
|
|||
|
|
+ " WHERE vg.sc_id = " + veh
|
|||
|
|
+ " AND g.type = " + (int)GroupType.MOBILETALK.value
|
|||
|
|
+ " ORDER BY g.name";
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
while (Reader.Read())
|
|||
|
|
{
|
|||
|
|
Group grp = new Group()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
Name = Reader.GetString(1)
|
|||
|
|
};
|
|||
|
|
groupsLits.Add(grp);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Group> getAllOtherGroupsForCurrentUnit(string veh)
|
|||
|
|
{
|
|||
|
|
List<Group> groupsLits = new List<Group>();
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = "SELECT g.id, g.name"
|
|||
|
|
+ " FROM groups g"
|
|||
|
|
+ " WHERE g.id NOT IN (SELECT grp_ip FROM vehicle_group WHERE sc_id = " + veh + ")"
|
|||
|
|
+ " AND g.type = " + (int)GroupType.MOBILETALK.value
|
|||
|
|
+ " ORDER BY g.name";
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|||
|
|
{
|
|||
|
|
while (Reader.Read())
|
|||
|
|
{
|
|||
|
|
Group grp = new Group()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
Name = Reader.GetString(1)
|
|||
|
|
};
|
|||
|
|
groupsLits.Add(grp);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Group> getAllGroupsForCurrentUser(string user)
|
|||
|
|
{
|
|||
|
|
List<Group> groupsLits = new List<Group>();
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = "SELECT g.id, g.name"
|
|||
|
|
+ " FROM groups g"
|
|||
|
|
+ " INNER JOIN users_group ug ON ug.grp_id = g.id"
|
|||
|
|
+ " WHERE ug.userid = " + user
|
|||
|
|
+ " AND g.type = " + (int)GroupType.MOBILETALK.value
|
|||
|
|
+ " ORDER BY g.name";
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|||
|
|
{
|
|||
|
|
while (Reader.Read())
|
|||
|
|
{
|
|||
|
|
Group grp = new Group()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
Name = Reader.GetString(1)
|
|||
|
|
};
|
|||
|
|
groupsLits.Add(grp);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Group> getAllTalkGroupsForUnits(string unitsList)
|
|||
|
|
{
|
|||
|
|
List<Group> groupsLits = new List<Group>();
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = "SELECT distinct g.id, g.name"
|
|||
|
|
+ " FROM groups g"
|
|||
|
|
+ " INNER JOIN vehicle_group vg ON vg.grp_ip = g.id"
|
|||
|
|
+ " WHERE vg.sc_id in " + unitsList
|
|||
|
|
+ " AND g.type = " + (int)GroupType.RADIOTALK.value
|
|||
|
|
+ " ORDER BY g.name";
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|||
|
|
{
|
|||
|
|
while (Reader.Read())
|
|||
|
|
{
|
|||
|
|
Group grp = new Group()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
Name = Reader.GetString(1)
|
|||
|
|
};
|
|||
|
|
groupsLits.Add(grp);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public string getTalkGroupNameFromCpsId(int cpsId)
|
|||
|
|
{
|
|||
|
|
string groupName = "";
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = "SELECT distinct g.name"
|
|||
|
|
+ " FROM groups g"
|
|||
|
|
+ " WHERE g.cps_id = " + cpsId
|
|||
|
|
+ " AND g.type = " + (int)GroupType.RADIOTALK.value;
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|||
|
|
{
|
|||
|
|
while (Reader.Read())
|
|||
|
|
{
|
|||
|
|
groupName = Reader.GetString(0);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupName;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Group> getAllTalkGroupsAvailableForLinx(int sc_id, out int currentGroup)
|
|||
|
|
{
|
|||
|
|
string command = string.Empty;
|
|||
|
|
|
|||
|
|
currentGroup = -1;
|
|||
|
|
List<Group> groupsLits = new List<Group>();
|
|||
|
|
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
command = "SELECT distinct g.id, g.name "
|
|||
|
|
+ " FROM groups g "
|
|||
|
|
+ " INNER JOIN vehicle_group vg ON vg.grp_ip = g.id "
|
|||
|
|
+$" WHERE vg.sc_id = {sc_id} AND g.type = {(int)GroupType.RADIOTALK.value} ORDER BY g.name";
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
object temp = cmd.ExecuteScalar();
|
|||
|
|
if (temp != null)
|
|||
|
|
currentGroup = Convert.ToInt32(temp.ToString());
|
|||
|
|
|
|||
|
|
command = " SELECT x.id, x.name FROM groups x"
|
|||
|
|
+ " where x.id not in "
|
|||
|
|
+ " ("
|
|||
|
|
+ " SELECT DISTINCT g.id FROM groups g"
|
|||
|
|
+ " INNER JOIN vehicle_group vg ON vg.grp_ip = g.id"
|
|||
|
|
+ " INNER JOIN vehicle v ON v.id = vg.sc_id "
|
|||
|
|
+ $" WHERE v.type = {(int)DeviceType.SMARTPHONE} AND g.type = {(int)GroupType.RADIOTALK.value} and vg.sc_id <> {sc_id}"
|
|||
|
|
+ " UNION"
|
|||
|
|
+ " SELECT DISTINCT g.id FROM groups g"
|
|||
|
|
+ " INNER JOIN radio_mobile rm ON rm.radio_grp_id = g.id"
|
|||
|
|
+ $" WHERE g.type = {(int)GroupType.RADIOTALK.value}"
|
|||
|
|
+ $" ) and x.type = {(int)GroupType.RADIOTALK.value}";
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd1 = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
using (NpgsqlDataReader Reader = cmd1.ExecuteReader())
|
|||
|
|
{
|
|||
|
|
while (Reader.Read())
|
|||
|
|
{
|
|||
|
|
Group grp = new Group()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
Name = Reader.GetString(1)
|
|||
|
|
};
|
|||
|
|
groupsLits.Add(grp);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Vehicles> getAllRadioTalkGroupsAvailableForMobileGroup()
|
|||
|
|
{
|
|||
|
|
List<Vehicles> groupsLits = new List<Vehicles>();
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = "SELECT x.id, x.name FROM groups x"
|
|||
|
|
+ " where x.id not in "
|
|||
|
|
+ " ("
|
|||
|
|
+ " SELECT DISTINCT g.id FROM groups g"
|
|||
|
|
+ " INNER JOIN vehicle_group vg ON vg.grp_ip = g.id"
|
|||
|
|
+ " INNER JOIN vehicle v ON v.id = vg.sc_id "
|
|||
|
|
+ $" WHERE v.type = {(int)DeviceType.SMARTPHONE} AND g.type = {(int)GroupType.RADIOTALK.value}"
|
|||
|
|
+ " UNION"
|
|||
|
|
+ " SELECT DISTINCT g.id FROM groups g"
|
|||
|
|
+ " INNER JOIN radio_mobile rm ON rm.radio_grp_id = g.id"
|
|||
|
|
+ $" WHERE g.type = {(int)GroupType.RADIOTALK.value}"
|
|||
|
|
+ $" ) and x.type = {(int)GroupType.RADIOTALK.value}";
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|||
|
|
{
|
|||
|
|
while (Reader.Read())
|
|||
|
|
{
|
|||
|
|
Vehicles grp = new Vehicles()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
VehName = Reader.GetString(1),
|
|||
|
|
Type = (int)ContactType.GROUP
|
|||
|
|
};
|
|||
|
|
groupsLits.Add(grp);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Vehicles> getAllRadioTalkGroupsAssignedToMobileGroup(int grp_id)
|
|||
|
|
{
|
|||
|
|
List<Vehicles> groupsLits = new List<Vehicles>();
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = $"SELECT distinct g.id, g.name, {(int)ContactType.GROUP} FROM groups g "
|
|||
|
|
+ " INNER JOIN radio_mobile rm"
|
|||
|
|
+ $" ON rm.radio_grp_id = g.id WHERE rm.mobile_grp_id = {grp_id} AND g.type = {(int)GroupType.RADIOTALK.value} ORDER BY g.name";
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|||
|
|
{
|
|||
|
|
while (Reader.Read())
|
|||
|
|
{
|
|||
|
|
Vehicles grp = new Vehicles()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
VehName = Reader.GetString(1),
|
|||
|
|
Type = Reader.GetInt32(2)
|
|||
|
|
};
|
|||
|
|
groupsLits.Add(grp);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Vehicles> getAllMobileAvailableFoRadioGroup()
|
|||
|
|
{
|
|||
|
|
List<Vehicles> groupsLits = new List<Vehicles>();
|
|||
|
|
|
|||
|
|
string command = "select *"
|
|||
|
|
+ " from"
|
|||
|
|
+ " ("
|
|||
|
|
+ " select id || '+' || type as key, id, name, type, img from"
|
|||
|
|
+ " ("
|
|||
|
|
+ $" select v.id as id, v.name as name, {(int)ContactType.UNIT} as type, v.driver_id as img"
|
|||
|
|
+ " from vehicle v"
|
|||
|
|
+ " join subscriber_mobile sm on sm.sc_id = v.id"
|
|||
|
|
+ " UNION"
|
|||
|
|
+ $" select g.id as id, g.name as name, {(int)ContactType.GROUP} as type, {(int)ContactType.GROUP} as img"
|
|||
|
|
+ $" from groups g where g.type = {(int)GroupType.MOBILETALK.value}"
|
|||
|
|
+ " ) as toti"
|
|||
|
|
+ " ) as x"
|
|||
|
|
+ " where x.key not in"
|
|||
|
|
+ " (select id || '+' || type as key from"
|
|||
|
|
+ " ("
|
|||
|
|
+ " SELECT"
|
|||
|
|
+ " distinct"
|
|||
|
|
+ $" g.id as id, g.name as name, {(int)ContactType.GROUP} as type, {(int)ContactType.GROUP} as img"
|
|||
|
|
+ " FROM groups g"
|
|||
|
|
+ " INNER JOIN radio_mobile rm ON rm.mobile_grp_id = g.id"
|
|||
|
|
+ $" WHERE g.type = {(int)GroupType.MOBILETALK.value}"
|
|||
|
|
+ " UNION"
|
|||
|
|
+ " SELECT"
|
|||
|
|
+ " distinct"
|
|||
|
|
+ $" v.id as id, v.name as name, {(int)ContactType.UNIT} as type, v.driver_id as img"
|
|||
|
|
+ " FROM vehicle v"
|
|||
|
|
+ " INNER JOIN vehicle_group vg ON vg.sc_id = v.id"
|
|||
|
|
+ $" inner join groups g ON vg.grp_ip = g.id AND g.type = {(int)GroupType.RADIOTALK.value}"
|
|||
|
|
+ " ORDER BY name"
|
|||
|
|
+ " )careau"
|
|||
|
|
+ " )"
|
|||
|
|
+ " ORDER BY name";
|
|||
|
|
|
|||
|
|
|
|||
|
|
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())
|
|||
|
|
{
|
|||
|
|
Vehicles grp = new Vehicles()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(1),
|
|||
|
|
VehName = Reader.GetString(2),
|
|||
|
|
Type = Reader.GetInt32(3),
|
|||
|
|
ImgID = Reader.GetInt32(4)
|
|||
|
|
};
|
|||
|
|
groupsLits.Add(grp);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Vehicles> getAllMobilesAssignedToRadioGroup(int grp_id)
|
|||
|
|
{
|
|||
|
|
List<Vehicles> groupsLits = new List<Vehicles>();
|
|||
|
|
|
|||
|
|
string command = $"SELECT distinct g.id, g.name as name, {(int)ContactType.GROUP} as type, {(int)ContactType.GROUP} as img FROM groups g "
|
|||
|
|
+ " INNER JOIN radio_mobile rm"
|
|||
|
|
+ $" ON rm.mobile_grp_id = g.id WHERE rm.radio_grp_id = {grp_id} AND g.type = {(int)GroupType.MOBILETALK.value} "
|
|||
|
|
+ " UNION"
|
|||
|
|
+ $" SELECT distinct v.id, v.name as name, {(int)ContactType.UNIT} as type, v.driver_id as img FROM vehicle v "
|
|||
|
|
+ " INNER JOIN vehicle_group vg ON vg.sc_id = v.id"
|
|||
|
|
+ " inner join groups g ON vg.grp_ip = g.id"
|
|||
|
|
+ $" WHERE vg.grp_ip = {grp_id} AND g.type = {(int)GroupType.RADIOTALK.value} ORDER BY name";
|
|||
|
|
|
|||
|
|
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())
|
|||
|
|
{
|
|||
|
|
Vehicles grp = new Vehicles()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
VehName = Reader.GetString(1),
|
|||
|
|
Type = Reader.GetInt32(2),
|
|||
|
|
ImgID = Reader.GetInt32(3)
|
|||
|
|
};
|
|||
|
|
groupsLits.Add(grp);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public int getLinxSIPIdForGroupSIPid(int groupSipID)
|
|||
|
|
{
|
|||
|
|
int LinxSipId = -1;
|
|||
|
|
|
|||
|
|
string command = "SELECT sml.sip_id FROM groups g"
|
|||
|
|
+ " JOIN vehicle_group vg ON vg.grp_ip = g.id"
|
|||
|
|
+ $" JOIN sip_manager sm ON sm.id = g.id AND sm.type = {(int)ContactType.GROUP}"
|
|||
|
|
+ $" JOIN sip_manager sml ON sml.id = vg.sc_id AND sml.type = {(int)ContactType.UNIT}"
|
|||
|
|
+ $" WHERE sm.sip_id = {groupSipID}"
|
|||
|
|
+ " UNION"
|
|||
|
|
+ " select sm2.sip_id from sip_manager sm1"
|
|||
|
|
+ $" join radio_mobile rm on rm.radio_grp_id = sm1.id and sm1.type = {(int)ContactType.GROUP}"
|
|||
|
|
+ $" join sip_manager sm2 on sm2.id = rm.mobile_grp_id and sm2.type = {(int)ContactType.GROUP}"
|
|||
|
|
+ $" WHERE sm1.sip_id = {groupSipID}";
|
|||
|
|
|
|||
|
|
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
object temp = cmd.ExecuteScalar();
|
|||
|
|
if (temp != null)
|
|||
|
|
LinxSipId = Convert.ToInt32(temp.ToString());
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return LinxSipId;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Group> getAllOtherGroupsForCurrentUser(string user)
|
|||
|
|
{
|
|||
|
|
List<Group> groupsLits = new List<Group>();
|
|||
|
|
|
|||
|
|
string command = "SELECT g.id, g.name"
|
|||
|
|
+ " FROM groups g"
|
|||
|
|
+ " WHERE g.id NOT IN (SELECT grp_id FROM users_group WHERE userid = " + user + ")"
|
|||
|
|
+ " AND g.type = " + (int)GroupType.MOBILETALK.value
|
|||
|
|
+ " ORDER BY g.name";
|
|||
|
|
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())
|
|||
|
|
{
|
|||
|
|
Group grp = new Group()
|
|||
|
|
{
|
|||
|
|
Id = Reader.GetInt32(0),
|
|||
|
|
Name = Reader.GetString(1)
|
|||
|
|
};
|
|||
|
|
groupsLits.Add(grp);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return groupsLits;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// add connection between group and vehicle
|
|||
|
|
public vehResponse addVehicleGroupConnection(string vehId, string grpID)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
vehResponse resp = vehResponse.done;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = $"INSERT INTO vehicle_group(sc_id, grp_ip) VALUES({vehId},{grpID})";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
resp = vehResponse.SQLerror;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//remove connection between group and vehicle
|
|||
|
|
public vehResponse removeVehicleGroupConnection(string vehId, string grpID)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
vehResponse resp = vehResponse.done;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = $"DELETE FROM vehicle_group WHERE sc_id={vehId} and grp_ip={grpID}";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
resp = vehResponse.SQLerror;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public vehResponse cleanRadioTalkConnection(string grpID)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
vehResponse resp = vehResponse.done;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
|
|||
|
|
string command = $"DELETE FROM vehicle_group WHERE grp_ip ={grpID}; DELETE FROM radio_mobile WHERE radio_grp_id ={grpID}; ";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
resp = vehResponse.SQLerror;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// add connection between radio and group
|
|||
|
|
public vehResponse addRadioMobileConnection(string radio_grup, string mobile_grup)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
vehResponse resp = vehResponse.done;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = $"INSERT INTO radio_mobile(radio_grp_id, mobile_grp_id) VALUES({radio_grup},{mobile_grup})";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
resp = vehResponse.SQLerror;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//remove connection between radio and mobile
|
|||
|
|
public vehResponse removeRadioMobileConnection(string radio_grup, string mobile_grup)
|
|||
|
|
{
|
|||
|
|
vehResponse resp = vehResponse.done;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = $"DELETE FROM radio_mobile WHERE radio_grp_id = {radio_grup} and mobile_grp_id = {mobile_grup}";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
resp = vehResponse.SQLerror;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
// add connection between group and vehicle
|
|||
|
|
public vehResponse addUserGroupConnection(string userid, string grpID)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
vehResponse resp = vehResponse.done;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
|
|||
|
|
string command = $"INSERT INTO users_group(userid, grp_id) VALUES({userid},{grpID})";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
resp = vehResponse.SQLerror;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//remove connection between group and vehicle
|
|||
|
|
public vehResponse removeUserGroupConnection(string userid, string grpID)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
vehResponse resp = vehResponse.done;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = $"DELETE FROM users_group WHERE userid = {userid} and grp_id = {grpID}";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source, ConsoleColor.Red);
|
|||
|
|
resp = vehResponse.SQLerror;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
public sqlResponse DeleteGroup(int indx)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
sqlResponse resp;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string query = $"DELETE FROM sip_manager sm USING groups g WHERE g.id = sm.id AND"
|
|||
|
|
+ $" sm.type = {(int)ContactType.GROUP} AND g.id ={indx};"
|
|||
|
|
+ $" DELETE from groups where id={indx}";
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
resp = sqlResponse.done;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ex.ToString(), ConsoleColor.Red);
|
|||
|
|
resp = sqlResponse.SQLerror;
|
|||
|
|
throw ex;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public sqlResponse EditGroupName(int id, string newName,Int32 cps_id, int newRGW, out sqlResponse resp)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
resp = sqlResponse.SQLerror;
|
|||
|
|
string query = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
Console.WriteLine($"CPSID is: {cps_id} ID: {id}");
|
|||
|
|
|
|||
|
|
object temp = null;
|
|||
|
|
|
|||
|
|
NpgsqlCommand cmd2 = new NpgsqlCommand("SELECT count(name) FROM groups where name='" + newName + "'", connection);
|
|||
|
|
{
|
|||
|
|
temp = cmd2.ExecuteScalar();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if ((temp.ToString() == "0") || (cps_id != 0))
|
|||
|
|
{
|
|||
|
|
if (cps_id == 0)
|
|||
|
|
query = $"UPDATE groups SET name ='{newName}', cps_id= {cps_id} where id = {id}";
|
|||
|
|
else
|
|||
|
|
query = $"UPDATE groups SET name ='{newName}', cps_id= {cps_id}, r_gw_id = {newRGW} where id = {id}";
|
|||
|
|
|
|||
|
|
Console.WriteLine(query);
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
resp = sqlResponse.done;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
resp = sqlResponse.noUpdate;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ex.ToString(), ConsoleColor.Red);
|
|||
|
|
resp = sqlResponse.SQLerror;
|
|||
|
|
throw ex;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public sqlResponse AddGroupElement(int sc_id, int groupID, int groupType)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
|
|||
|
|
string command = $"DELETE FROM vehicle_group vg USING groups g " +
|
|||
|
|
$" WHERE vg.grp_ip = g.id AND sc_id = {sc_id} AND g.type = {groupType}";
|
|||
|
|
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
command = $"INSERT INTO vehicle_group (sc_id,grp_ip) VALUES ({sc_id} , {groupID} )";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
resp = sqlResponse.done;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ex.ToString(), ConsoleColor.Red);
|
|||
|
|
throw ex;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public sqlResponse AddGroupElementBulk(string idList, string[] IDList, int groupID, int groupType)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|||
|
|
NpgsqlTransaction transaction = null;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
transaction = connection.BeginTransaction();
|
|||
|
|
|
|||
|
|
string command = "DELETE FROM vehicle_group vg USING groups g WHERE vg.grp_ip = g.id AND sc_id in " + idList + " AND g.type =" + groupType;
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection, transaction))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
string commandToExecute = "";
|
|||
|
|
|
|||
|
|
foreach (var sc_idS in IDList)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
commandToExecute += $"INSERT INTO vehicle_group (sc_id, grp_ip) VALUES ({sc_idS} , {groupID});";
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine("AddGroupElementBulk error: " + ex.ToString(), ConsoleColor.Red);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(commandToExecute, connection, transaction))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
transaction.Commit();
|
|||
|
|
resp = sqlResponse.done;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
Utils.WriteLine(ex.ToString(), ConsoleColor.Red);
|
|||
|
|
transaction?.Rollback();
|
|||
|
|
|
|||
|
|
throw ex;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public sqlResponse AddRangeGroupElement(ArrayList list_id, int groupID, int groupType)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|||
|
|
NpgsqlTransaction transaction = null;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
transaction = connection.BeginTransaction();
|
|||
|
|
|
|||
|
|
NpgsqlCommand cmd;
|
|||
|
|
foreach (Int32 sc_id in list_id)
|
|||
|
|
{
|
|||
|
|
cmd = new NpgsqlCommand("DELETE FROM vehicle_group vg USING groups g WHERE vg.grp_ip = g.id AND sc_id = " + sc_id + " AND g.type =" + groupType, connection, transaction);
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
|
|||
|
|
cmd = new NpgsqlCommand($"INSERT INTO vehicle_group (sc_id,grp_ip) VALUES ({sc_id} , {groupID})", connection, transaction);
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
transaction.Commit();
|
|||
|
|
resp = sqlResponse.done;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ex.ToString(), ConsoleColor.Red);
|
|||
|
|
transaction?.Rollback();
|
|||
|
|
throw ex;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public sqlResponse RemoveGroupElementBulk(string idList, int groupType)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = "DELETE FROM vehicle_group vg USING groups g " +
|
|||
|
|
$" WHERE vg.grp_ip = g.id AND sc_id in {idList} AND g.type = {groupType}";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
resp = sqlResponse.done;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ex.ToString(), ConsoleColor.Red);
|
|||
|
|
throw ex;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public sqlResponse RemoveGroupElement(int sc_id, int groupType)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
connection.ConnectionString = getConnectionString();
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
string command = "DELETE FROM vehicle_group vg USING groups g " +
|
|||
|
|
$" WHERE vg.grp_ip = g.id AND sc_id = {sc_id} AND g.type = {groupType} ";
|
|||
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
resp = sqlResponse.done;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ex.ToString(), ConsoleColor.Red);
|
|||
|
|
throw ex;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return resp;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public Group GetGroupForSCID(int sc_id,int grpType)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
Group gr= null;
|
|||
|
|
|
|||
|
|
List<Group> ret = new List<Group>();
|
|||
|
|
|
|||
|
|
string command = "SELECT grp.id, grp.name, grp.cps_id, grp.type, coalesce(grp.r_gw_id, -1) r_gw_id "
|
|||
|
|
+ " FROM groups as grp "
|
|||
|
|
+ " INNER JOIN vehicle_group as v_grp ON (grp.id = v_grp.grp_ip) "
|
|||
|
|
+ $" WHERE v_grp.sc_id={sc_id} AND grp.type = {grpType} ";
|
|||
|
|
|
|||
|
|
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())
|
|||
|
|
{
|
|||
|
|
int grpID = Convert.ToInt32(reader["id"]);
|
|||
|
|
string name = reader["name"].ToString();
|
|||
|
|
int cps_id = Convert.ToInt32(reader["cps_id"]);
|
|||
|
|
int type = Convert.ToInt32(reader["type"]);
|
|||
|
|
int r_gw_id = Convert.ToInt32(reader["r_gw_id"]);
|
|||
|
|
|
|||
|
|
|
|||
|
|
gr = new Group(grpID, name, cps_id, type, r_gw_id);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ee)
|
|||
|
|
{
|
|||
|
|
Utils.WriteLine(ee.ToString(), ConsoleColor.Red);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return gr;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public class Group
|
|||
|
|
{
|
|||
|
|
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 int cps_id;
|
|||
|
|
|
|||
|
|
public int CPS_Id
|
|||
|
|
{
|
|||
|
|
get { return cps_id; }
|
|||
|
|
set { cps_id = value; }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private int type;
|
|||
|
|
|
|||
|
|
public int Type
|
|||
|
|
{
|
|||
|
|
get { return type; }
|
|||
|
|
set { type = value; }
|
|||
|
|
}
|
|||
|
|
private int r_gw_id;
|
|||
|
|
|
|||
|
|
public int RgwID
|
|||
|
|
{
|
|||
|
|
get { return r_gw_id; }
|
|||
|
|
set { r_gw_id = value; }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private int sipID;
|
|||
|
|
public int SipID
|
|||
|
|
{
|
|||
|
|
get { return sipID; }
|
|||
|
|
set { sipID = value; }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private string gw_and_radioID;
|
|||
|
|
|
|||
|
|
public string Gw_and_radioID
|
|||
|
|
{
|
|||
|
|
get { return gw_and_radioID; }
|
|||
|
|
set { gw_and_radioID = value; }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private string gatewayName;
|
|||
|
|
|
|||
|
|
public string GatewayName
|
|||
|
|
{
|
|||
|
|
get { return gatewayName; }
|
|||
|
|
set { gatewayName = value; }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private string groupMembers;
|
|||
|
|
|
|||
|
|
public string GroupMembers
|
|||
|
|
{
|
|||
|
|
get { return groupMembers; }
|
|||
|
|
set { groupMembers = value; }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public Group(int id, string name,int cps_id,int type, int r_gw_id)
|
|||
|
|
{
|
|||
|
|
this.id = id;
|
|||
|
|
this.name = name;
|
|||
|
|
this.cps_id = cps_id;
|
|||
|
|
this.type = type;
|
|||
|
|
this.r_gw_id = r_gw_id;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public Group(int id, string name, int cps_id)
|
|||
|
|
{
|
|||
|
|
this.id = id;
|
|||
|
|
this.name = name;
|
|||
|
|
this.cps_id = cps_id;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public Group()
|
|||
|
|
{
|
|||
|
|
// TODO: Complete member initialization
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|