423 lines
17 KiB
C#
423 lines
17 KiB
C#
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;
|
|
}
|
|
}
|
|
}
|