557 lines
21 KiB
C#
557 lines
21 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using Npgsql;
|
|
using System.Collections;
|
|
using System.Text.RegularExpressions;
|
|
|
|
namespace SafeMobileLib
|
|
{
|
|
public class DBsmsManager:DBmanager
|
|
{
|
|
public DBsmsManager(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 insert_sms_sent2email(string imei, string mess, string email,String seqID)
|
|
{
|
|
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|
Int32 sc_id = 0;
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = $"SELECT sc_id from subscriber where imei = '{imei}' ";
|
|
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
sc_id = Convert.ToInt32(cmd.ExecuteScalar());
|
|
}
|
|
|
|
|
|
command = "INSERT INTO sms (timeGMT,sc_id_sour,sc_id_dest,mess,status,email,seq_id) " +
|
|
$" VALUES( {DateTime.Now.ToUniversalTime().DateTo70Format()}, {sc_id}, 0, '{mess}', 5, '{email}', '{seqID}')";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine($"insert_sms_sent2email : {o.Message}");
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public sqlResponse insert_sms_received(String imei, string mess, int type, string emailaddr, String seqID)
|
|
{
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|
|
|
Int32 sc_id = 0;
|
|
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = $"SELECT sc_id from subscriber where imei='{imei}'";
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
sc_id = Convert.ToInt32(cmd.ExecuteScalar());
|
|
}
|
|
|
|
command = "INSERT INTO sms (timeGMT,sc_id_sour,sc_id_dest,mess,status, email, seq_id) " +
|
|
$" VALUES( {DateTime.Now.ToUniversalTime().DateTo70Format()},{sc_id}, 0, '{mess}', {type}, '','{seqID}')";
|
|
if (type == 7)
|
|
command = " INSERT INTO sms (timeGMT,sc_id_sour,sc_id_dest,mess,status,email,seq_id) " +
|
|
$" VALUES( {DateTime.Now.ToUniversalTime().DateTo70Format()}, 0, {sc_id}, '{mess}', {type},'{emailaddr}','{seqID}')";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
if (type == 3)
|
|
{
|
|
command = string.Format("UPDATE alarm set sc_id={0},timeGMT={1},mess='{2}' where type = 1", sc_id, DateTime.Now.ToUniversalTime().DateTo70Format(), mess);
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine("File: SMdb_access.cs Meth: UPDATE STATUS " + o.Message.ToString());
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public sqlResponse insert_sms_from_sd(string imei, string mess,string seqID,int sched_time, int dispatcherID)
|
|
{
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|
Int32 sc_id = 0;
|
|
|
|
try
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = $"SELECT sc_id from subscriber where imei = '{imei}'";
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
sc_id = Convert.ToInt32(cmd.ExecuteScalar());
|
|
|
|
}
|
|
|
|
command = "INSERT INTO sms (timeGMT, sc_id_sour, sc_id_dest, mess, status, email, seq_id, sched_timegmt, user_id) VALUES(" +
|
|
$"{DateTime.Now.ToUniversalTime().DateTo70Format()}, 0, {sc_id},'{mess}',0,'','{seqID}', {sched_time}, {dispatcherID} )";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine($"insert_sms_from_sd : {o.Message}", ConsoleColor.Red);
|
|
}
|
|
|
|
|
|
return resp;
|
|
}
|
|
|
|
|
|
|
|
public sqlResponse insert_smsgroup_from_sd(Int32 cps_id, string mess, string seqID, int sched_time,int type, string emailaddr)
|
|
{
|
|
|
|
sqlResponse resp = sqlResponse.SQLerror;
|
|
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = "INSERT INTO sms (timeGMT,sc_id_sour,sc_id_dest,mess,status,email,seq_id,sched_timegmt) " +
|
|
$" VALUES( {DateTime.Now.ToUniversalTime().DateTo70Format()}, {cps_id}, {cps_id},'{mess}', {type},'{emailaddr}','{seqID}',{sched_time} )";
|
|
if (type == 7)
|
|
command = "INSERT INTO sms (timeGMT, sc_id_sour, sc_id_dest, mess, status, email, seq_id, sched_timegmt) " +
|
|
$" VALUES( {DateTime.Now.ToUniversalTime().DateTo70Format()}, {cps_id}, {cps_id},'{mess}',{type},'{emailaddr}','{seqID}',{DateTime.Now.ToUniversalTime().DateTo70Format()})";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
resp = sqlResponse.done;
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine($"insert_smsgroup_from_sd: {o.Message}", ConsoleColor.Red);
|
|
}
|
|
|
|
return resp;
|
|
}
|
|
|
|
public sqlResponse update_sms_delivered(string seqID, int status)
|
|
{
|
|
// status = 1 : GW read the msg and delivered it
|
|
// status = 2 : GW received the confirm from radio unit
|
|
|
|
string command = $"UPDATE sms SET status = {status} where seq_id='{seqID}'";
|
|
return RunCommand(command);
|
|
}
|
|
|
|
public List<smsmessage> get_sms_not_delivered(string radioID)
|
|
{
|
|
|
|
List<smsmessage> ret = new List<smsmessage>();
|
|
smsmessage obj;
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = " SELECT s.imei , m.timeGMT, m.idx, m.mess, s.sc_id, m.seq_id,m.sched_timegmt,m.user_id " +
|
|
" FROM sms m, subscriber s" +
|
|
$" WHERE m.deleted = 0 AND m.status = 0 AND s.sc_id = m.sc_id_dest AND s.imei = '{radioID}' ORDER BY m.timeGMT";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
while (dr.Read())
|
|
{
|
|
obj = new smsmessage();
|
|
obj.imei_dest = dr.GetString(0);
|
|
obj.timeGMT = (uint)dr.GetInt32(1);
|
|
obj.idx = dr.GetInt32(2);
|
|
obj.mess = dr.GetString(3);
|
|
obj.sc_id_dest = dr.GetInt32(4);
|
|
obj.status = 0;
|
|
obj.seq_id = dr.GetString(5);
|
|
obj.sched_timeGMT = dr.GetInt32(6);
|
|
obj.dispatcherID = dr.GetInt32(7);
|
|
ret.Add(obj);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine($"get_sms_not_delivered : {o.Message}");
|
|
throw new ArgumentException(o.Message);
|
|
}
|
|
|
|
return ret;
|
|
}
|
|
|
|
public List<smsmessage> get_ALL_Units_sms_not_delivered()
|
|
{
|
|
|
|
List<smsmessage> ret = new List<smsmessage>();
|
|
|
|
smsmessage obj;
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = " SELECT s.imei , m.timeGMT, m.idx, m.mess, s.sc_id, m.seq_id,m.sched_timegmt, m.user_id " +
|
|
" FROM sms m, subscriber s" +
|
|
" WHERE m.deleted = 0 and m.status=0 and s.sc_id=m.sc_id_dest and m.sc_id_dest!=m.sc_id_sour " +
|
|
" ORDER BY m.timeGMT";
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
while (dr.Read())
|
|
{
|
|
obj = new smsmessage();
|
|
obj.imei_dest = dr.GetString(0);
|
|
obj.timeGMT = (uint)dr.GetInt32(1);
|
|
obj.idx = dr.GetInt32(2);
|
|
obj.mess = dr.GetString(3);
|
|
obj.sc_id_dest = dr.GetInt32(4);
|
|
obj.status = 0;
|
|
obj.seq_id = dr.GetString(5);
|
|
obj.sched_timeGMT = dr.GetInt32(6);
|
|
obj.dispatcherID = dr.IsDBNull(7) ? -1 : dr.GetInt32(7);
|
|
ret.Add(obj);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine($"get_ALL_sms_not_delivered : {o.Message}");
|
|
throw new ArgumentException(o.Message);
|
|
}
|
|
|
|
return ret;
|
|
}
|
|
|
|
public List<smsmessage> get_sms_not_delivered_groups()
|
|
{
|
|
|
|
List<smsmessage> ret = new List<smsmessage>();
|
|
smsmessage obj;
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = " SELECT g.cps_id , m.timeGMT, m.idx, m.mess, g.id, m.seq_id,m.sched_timegmt " +
|
|
" FROM sms m, groups g " +
|
|
" WHERE m.deleted = 0 and m.status = 0 and g.cps_id = m.sc_id_dest and m.sc_id_dest = m.sc_id_sour " +
|
|
" ORDER BY m.timeGMT";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
while (dr.Read())
|
|
{
|
|
obj = new smsmessage();
|
|
obj.imei_dest = dr.GetString(0);
|
|
obj.timeGMT = (uint)dr.GetInt32(1);
|
|
obj.idx = dr.GetInt32(2); //message DB id
|
|
obj.mess = dr.GetString(3);
|
|
obj.sc_id_dest = dr.GetInt32(4); //group DB id
|
|
obj.status = 0;
|
|
obj.seq_id = dr.GetString(5);
|
|
obj.sched_timeGMT = dr.GetInt32(6);
|
|
ret.Add(obj);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine(o.Message, ConsoleColor.Red);
|
|
throw new ArgumentException(o.Message.ToString());
|
|
}
|
|
|
|
return ret;
|
|
}
|
|
|
|
public List<smsmessage> Get_SMS_for_unit(int sc_id, int timeGMT)
|
|
{
|
|
|
|
List<smsmessage> ret = new List<smsmessage>();
|
|
smsmessage obj;
|
|
try
|
|
{
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = " SELECT m.status, m.timeGMT, m.idx, m.mess, m.sc_id_sour, m.sc_id_dest, m.seq_id " +
|
|
" FROM sms m " +
|
|
$" WHERE (m.sc_id_sour = {sc_id} or m.sc_id_dest = {sc_id} ) AND m.timeGMT > {timeGMT} " +
|
|
" ORDER BY m.timeGMT";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
while (dr.Read())
|
|
{
|
|
obj = new smsmessage();
|
|
obj.status = dr.GetInt32(0);
|
|
obj.timeGMT = (uint)dr.GetInt32(1);
|
|
obj.idx = dr.GetInt32(2);
|
|
obj.mess = dr.GetString(3);
|
|
obj.sc_id_sour = dr.GetInt32(4);
|
|
obj.sc_id_dest = dr.GetInt32(5);
|
|
obj.seq_id = dr.GetString(6);
|
|
ret.Add(obj);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine(o.Message, ConsoleColor.Red);
|
|
throw new ArgumentException(o.Message);
|
|
}
|
|
|
|
return ret;
|
|
}
|
|
|
|
public smsmessage Get_last_SMS_for_unit(int sc_id)
|
|
{
|
|
|
|
smsmessage ret = new smsmessage();
|
|
|
|
smsmessage obj;
|
|
try
|
|
{
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = " SELECT m.status, m.timeGMT, m.idx, m.mess, m.sc_id_sour, m.sc_id_dest, m.seq_id " +
|
|
" FROM sms m " +
|
|
$" WHERE m.sc_id_sour = {sc_id} or m.sc_id_dest = {sc_id} " +
|
|
" ORDER BY m.timeGMT";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
while (dr.Read())
|
|
{
|
|
obj = new smsmessage();
|
|
obj.status = dr.GetInt32(0);
|
|
obj.timeGMT = (uint)dr.GetInt32(1);
|
|
obj.idx = dr.GetInt32(2);
|
|
obj.mess = dr.GetString(3);
|
|
obj.sc_id_sour = dr.GetInt32(4);
|
|
obj.sc_id_dest = dr.GetInt32(5);
|
|
obj.seq_id = dr.GetString(6);
|
|
ret = obj;
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine(o.Message, ConsoleColor.Red);
|
|
throw new ArgumentException(o.Message);
|
|
}
|
|
|
|
return ret;
|
|
}
|
|
|
|
public Boolean Get_Group_SMS_Status(string mess, string seq_id)
|
|
{
|
|
|
|
int status = -1;
|
|
|
|
using (NpgsqlConnection connection = new NpgsqlConnection())
|
|
{
|
|
|
|
connection.ConnectionString = getConnectionString();
|
|
connection.Open();
|
|
|
|
string command = $" SELECT status from sms where mess = '{mess}' and seq_id = '{seq_id}'";
|
|
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
status = Convert.ToInt32(cmd.ExecuteScalar());
|
|
}
|
|
return (status == 2);
|
|
|
|
}
|
|
}
|
|
|
|
public List<smsmessage> Get_last_SMS_user(Int32 user_id)
|
|
{
|
|
|
|
ArrayList resp = new ArrayList();
|
|
try
|
|
{
|
|
|
|
|
|
string command = "SELECT s.sc_id, l.imei " +
|
|
"FROM lastpos l " +
|
|
" INNER JOIN subscriber as s ON (l.imei = s.imei) " +
|
|
" INNER JOIN subscriber_history as h ON (h.sc_id = s.sc_id) " +
|
|
" INNER JOIN vehicle as v ON (v.id = h.veh_id) " +
|
|
" INNER JOIN vehicle_user as u ON (u.veh_id = v.id) " +
|
|
$" WHERE u.user_id= {user_id}";
|
|
|
|
|
|
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())
|
|
{
|
|
Car tmp = new Car();
|
|
tmp.idx = Convert.ToInt32(Reader.GetValue(0));
|
|
tmp.Name = Reader.GetValue(1).ToString();
|
|
|
|
resp.Add(tmp);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception o)
|
|
{
|
|
Console.WriteLine(o.Message.ToString());
|
|
}
|
|
|
|
|
|
List<smsmessage> ret = new List<smsmessage>();
|
|
if (resp == null) return ret;
|
|
|
|
foreach (Car c in resp)
|
|
{
|
|
smsmessage tempMSG = Get_last_SMS_for_unit(c.idx);
|
|
if (tempMSG != null)
|
|
ret.Add(tempMSG);
|
|
}
|
|
|
|
ArrayList tmpX = new ArrayList();
|
|
foreach (smsmessage obj in ret)
|
|
tmpX.Add(obj);
|
|
tmpX.Sort(new SmsMessageComparer());
|
|
|
|
ret.Clear();
|
|
foreach (smsmessage obj in tmpX)
|
|
ret.Add(obj);
|
|
|
|
return ret;
|
|
}
|
|
}
|
|
|
|
public class smsmessage
|
|
{
|
|
public UInt32 timeGMT;
|
|
public Int32 idx;
|
|
public String mess;
|
|
public Int32 status;
|
|
public String imei_dest;
|
|
public String imei_sour;
|
|
public Int32 sc_id_dest;
|
|
public Int32 sc_id_sour;
|
|
public String seq_id;
|
|
public Int32 sched_timeGMT;
|
|
public Int32 dispatcherID;
|
|
}
|
|
|
|
public class SmsMessageComparer : IComparer
|
|
{
|
|
public SmsMessageComparer() : base() { }
|
|
|
|
int IComparer.Compare(object x, object y)
|
|
{
|
|
smsmessage X = x as smsmessage;
|
|
smsmessage Y = y as smsmessage;
|
|
if (X.timeGMT > Y.timeGMT) return 1;
|
|
else if (X.timeGMT == Y.timeGMT) return 0;
|
|
else return -1;
|
|
}
|
|
}
|
|
}
|