SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBgroupsManager.cs

1463 lines
54 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;
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
}
}
}