SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBrecordingsManager.cs

423 lines
17 KiB
C#
Raw Permalink Normal View History

2024-02-22 16:43:59 +00:00
using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using System.Collections;
namespace SafeMobileLib
{
public class DBrecordingsManager:DBmanager
{
public DBrecordingsManager(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 List<Recording> GetAllRecordingsForGW(int gwID, int radiogwID,Int32 sc_id,Int64 start_time,Int64 stop_time,Int32 call_type)
{
List<Recording> retList = new List<Recording>();
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
String glwhere = $"WHERE gw_id = {gwID} and radiogw_id = {radiogwID}";
if (sc_id != 0) glwhere += $" AND dispatcher_id= {sc_id} AND typesd=0";
if (start_time != 0) glwhere += $" AND time_start >= {start_time} AND time_stop <= {stop_time}";
if (call_type != 10) glwhere += $" AND calltype = {call_type}";
Console.WriteLine("Glwhere:" + glwhere);
string command = "SELECT rec_id, time_start, time_stop, s.imei::int as subscriber_id, hdd_file_path, gw_id,radiogw_id,typesd,calltype " +
" ,g.cps_id as group_id, dispatcher_id " +
"FROM \"VoiceRecordings\" vr " +
" LEFT JOIN subscriber s on vr.subscriber_id = s.sc_id " +
" LEFT JOIN groups g on vr.group_id = g.id " +
glwhere +
" ORDER BY time_start desc " +
" LIMIT 100";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
Console.WriteLine(cmd.CommandText);
using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
if (dr != null)
retList = GetRecordingList(dr);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("GetAllRecordingsInInterval() :" + ex.Message);
}
return retList;
}
public List<Recording> GetAllRecordingsForSD(int userID, Int32 sc_id, Int64 start_time, Int64 stop_time)
{
List<Recording> retList = new List<Recording>();
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
//String glwhere = "WHERE gw_id=0 and subscriber_id=" + userID.ToString();
String glwhere = "WHERE gw_id=0 AND user_id = {userID}";
if (sc_id != 0) glwhere += $" AND radiogw_id = {sc_id}";
if (start_time != 0) glwhere += $" AND time_start >= {start_time} AND time_stop <= {stop_time}";
string command = "SELECT rec_id, time_start, time_stop, s.imei::int as subscriber_id, hdd_file_path, gw_id,radiogw_id,typesd,calltype "
+ ", g.cps_id as group_id, dispatcher_id "
+ " FROM \"VoiceRecordings\" vr "
+ " LEFT JOIN subscriber s on vr.subscriber_id = s.sc_id "
+ " LEFT JOIN groups g on vr.group_id = g.id "
+ glwhere
+ " ORDER BY time_start desc"
+ " LIMIT 100";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
Console.WriteLine(cmd.CommandText);
using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
if (dr != null)
retList = GetRecordingList(dr);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"GetAllRecordingsInInterval() : {ex.Message}");
}
return retList;
}
private List<Recording> GetRecordingList(NpgsqlDataReader dr)
{
List<Recording> retList = new List<Recording>();
if (dr == null)
return retList;
while (dr.Read())
{
Int64 rec_id = Convert.ToInt64(dr["rec_id"]);
Int64 time_start = Convert.ToInt64(dr["time_start"]);
Int64 time_stop = Convert.ToInt64(dr["time_stop"]);
int? subsc_id = (dr["subscriber_id"] == DBNull.Value) ? null : (int?)(dr["subscriber_id"]); ;
string hdd_file_path = dr["hdd_file_path"].ToString();
int gw_id = Convert.ToInt32(dr["gw_id"]);
int radiogw_id = Convert.ToInt32(dr["radiogw_id"]);
int typesd = Convert.ToInt32(dr["typesd"]);
int calltype = Convert.ToInt32(dr["calltype"]);
int? dispatcher_id = (dr["dispatcher_id"] == DBNull.Value) ? null : (int?)(dr["dispatcher_id"]);
int? group_id = (dr["group_id"] == DBNull.Value) ? null : (int?)(dr["group_id"]);
Recording obj = new Recording(rec_id,
time_start,
time_stop,
DateTime.Now,
DateTime.Now,
hdd_file_path,
subsc_id,
gw_id,
radiogw_id,
typesd,
calltype,
group_id,
dispatcher_id);
retList.Add(obj);
}
return retList;
}
//FROM "VoiceRecordings" where ((subscriber_id=101 and gw_id=4 and radiogw_id=16)or(subscriber_id=2 and gw_id=1 and radiogw_id=8)) and time_start>1370012345
// parameters order
// 0 typesd 0 =gateway 1 = dispather
// 1 gw_id
// 2 radiogw_id //sc_id from the field in case o dispatcher
// 3 subscriber_id // user_id of dispatcher
public List<Recording> GetAllRecordingsForDispatcherAndAssignGateway(ArrayList parameters, Int64 start_time, Int64 stop_time, Int32 useridx)
{
List<Recording> retList = new List<Recording>();
try
{
Int32 index = 0;
Int32 typesd = 0; Int32 gw_id = 0; Int32 radiogw_id = 0; Int32 subcriber_id = 0;
String glwhere = "";
while (index<parameters.Count)
{
typesd = (Int32)parameters[index];
gw_id= (Int32)parameters[index+1];
radiogw_id = (Int32)parameters[index+2];
subcriber_id = (Int32)parameters[index+3];
if (index == 0) glwhere += "where (";
else glwhere += " or ";
if (typesd == 0)
{
glwhere += $"(gw_id = {gw_id} and radiogw_id= {radiogw_id} ";// +" and typesd=0";
if (subcriber_id != 0) glwhere += $" AND subscriber_id= {subcriber_id}";
glwhere += ")";
}
else
{
glwhere += $"(gw_id= {gw_id} AND subscriber_id= {subcriber_id}";
if (radiogw_id != 0) glwhere += $" AND radiogw_id= {radiogw_id}";
glwhere += ")";
}
index = index + 4;
}
glwhere += $"OR (gw_id = 0 and (dispatcher_id={useridx} or radiogw_id={useridx}))"; //for dispatcher to dispatcher call
glwhere += ")";
if (start_time != 0) glwhere += $" and time_start >= {start_time} and time_stop <= {stop_time}";
Console.WriteLine("Glwhere:" + glwhere);
string command = "SELECT rec_id, time_start, time_stop, s.imei::int as subscriber_id, hdd_file_path, gw_id,radiogw_id,typesd,calltype" +
" , g.cps_id as group_id, dispatcher_id " +
" FROM \"VoiceRecordings\" vr " +
" LEFT JOIN subscriber s on vr.subscriber_id = s.sc_id " +
" LEFT JOIN groups g on vr.group_id = g.id " +
glwhere +
" ORDER BY time_start desc " +
" LIMIT 500";
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
Console.WriteLine(cmd.CommandText);
using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
if (dr != null)
retList = GetRecordingList(dr);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"GetAllRecordingsInInterval() : {ex.Message}");
}
return retList;
}
/// <summary>
/// to allow the foreign keys to be used we need to add one more column (user_id) and also allow null values for
/// subscriber_id and group_id
///
/// ALTER TABLE "VoiceRecordings" ADD COLUMN dispatcher_id integer NULL; // add column dispatcher_id
/// ALTER TABLE "VoiceRecordings" ALTER COLUMN subscriber_id DROP NOT NULL; // remove not null from subscribe_id
/// ALTER TABLE "VoiceRecordings" ALTER COLUMN group_id DROP NOT NULL; // remove not null from group_id
/// </summary>
/// <param name="rec"></param>
/// <returns></returns>
public sqlResponse addRecording(Recording rec)
{
sqlResponse resp;
try
{
StringBuilder buffer = new StringBuilder(4096);
buffer.Append($"INSERT INTO \"VoiceRecordings\" (rec_id, subscriber_id, dispatcher_id, gw_id, radiogw_id, hdd_file_path, time_start, time_stop, typesd, calltype, group_id) ");
buffer.Append($"SELECT {rec.id} as rec_id, #subsID# as sc_id, #dispatcherID# AS dispatcher_id, {rec.gwID} as gw_id, {rec.radioGWID} as radiogw_id, '{rec.hddLocation}' as hdd_file_path, {rec.startTime} as time_start, {rec.endTime} as time_stop, {rec.typeSD} as typesd, {rec.calltype} as calltype, #groupID# as group_id");
if (rec.calltype == 0) // dispatcher - radio (private call)
{
buffer.Replace("#subsID#", $"(SELECT sc_id FROM subscriber WHERE imei = '{rec.subs_imei}')");
buffer.Replace("#dispatcherID#", rec.dispatcher_id.ToString());
buffer.Replace("#groupID#", "null");
}
else if (rec.calltype == 1) // dispatcher - radio (group call)
{
buffer.Replace("#subsID#", "null");
buffer.Replace("#dispatcherID#", rec.dispatcher_id.ToString());
buffer.Replace("#groupID#", $"(SELECT id FROM groups WHERE cps_id = '{rec.group_cpsId}')");
}
else if (rec.calltype == 2) // dispatcher - radio (all call)
{
buffer.Replace("#subsID#", "null");
buffer.Replace("#dispatcherID#", rec.dispatcher_id.ToString());
buffer.Replace("#groupID#", "null");
}
else if (rec.calltype == 4) // radio - dispatcher (private call)
{
buffer.Replace("#subsID#", $"(SELECT sc_id FROM subscriber WHERE imei = '{rec.subs_imei}')");
buffer.Replace("#dispatcherID#", rec.dispatcher_id.ToString());
buffer.Replace("#groupID#", "null");
}
else if (rec.calltype == 5) // radio - dispatcher (group call)
{
buffer.Replace("#subsID#", $"(SELECT sc_id FROM subscriber WHERE imei = '{rec.subs_imei}')");
buffer.Replace("#dispatcherID#", "null");
buffer.Replace("#groupID#", $"(SELECT id FROM groups WHERE cps_id = '{rec.group_cpsId}')");
}
else if (rec.calltype == 6) // radio - dispatcher (all call)
{
buffer.Replace("#subsID#", $"(SELECT sc_id FROM subscriber WHERE imei = '{rec.subs_imei}')");
buffer.Replace("#dispatcherID#", "null");
buffer.Replace("#groupID#", "null");
}
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(buffer.ToString(), connection))
{
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
}
}
resp = sqlResponse.done;
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = sqlResponse.SQLerror;
}
return resp;
}
public string getFilePath(long id)
{
string filePath ="";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = $"SELECT hdd_file_path FROM \"VoiceRecordings\" WHERE rec_id={id}";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
Console.WriteLine(cmd.CommandText);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
filePath = reader.GetString(0);
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"getFilePath() : {ex.Message}");
}
return filePath;
}
}
public class Recording
{
public Int64 id;
public long startTime;
public long endTime;
public DateTime startDateTime;
public DateTime endDateTime;
public string hddLocation;
public int? subs_imei;
public int gwID;
public int radioGWID;
public int typeSD;
public int calltype;
public int? group_cpsId;
public int? dispatcher_id;
public int subID;
public int Group_ID;
public Recording(Int64 _id, long _startTime, long _endTime, DateTime _startDateTime, DateTime _endDateTime,
string _hddLocation, int? _subsc_imei, int _gwID, int _radioGWID,
int _typeSD, int _calltype, int? _group_cpsId, int? _dispatcher_id)
{
id = _id;
startTime = _startTime;
endTime = _endTime;
startDateTime = _startDateTime;
endDateTime = _endDateTime;
hddLocation = _hddLocation;
subs_imei = _subsc_imei;
gwID = _gwID;
radioGWID = _radioGWID;
typeSD = _typeSD;
calltype = _calltype;
group_cpsId = _group_cpsId;
dispatcher_id = _dispatcher_id;
}
}
public class RecordingComparer : IComparer
{
public RecordingComparer() : base() { }
int IComparer.Compare(object x, object y)
{
Recording X = x as Recording;
Recording Y = y as Recording;
if (X.startTime < Y.startTime) return 1;
else if (X.startTime == Y.startTime) return 0;
else return -1;
}
}
}