SafeDispatch/SafeMobileLIB_DLL/DBmanagers/DBTicketingManager.cs
2024-06-07 16:20:57 +03:00

1547 lines
58 KiB
C#

using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using System.Xml;
using System.Collections;
using System.Linq;
using System.Text.RegularExpressions;
namespace SafeMobileLib
{
public class DBTicketingManager : DBmanager
{
public DBTicketingManager(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)
{
}
#region Job Tickets
public int getMaxTicketID()
{
int ticket_id = -1;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "SELECT value FROM sequences where name = 'jobticketing'";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
ticket_id = Convert.ToInt32(cmd.ExecuteScalar());
}
if (ticket_id == -1)
{
command = "INSERT INTO sequences(name,value,idx) VALUES ('jobticketing',0,1)";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.Prepare();
cmd.ExecuteNonQuery();
}
ticket_id = 1;
}
command = "UPDATE sequences SET value = value + 1 WHERE name = 'jobticketing'";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.Prepare();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message, ConsoleColor.Red);
}
return ticket_id;
}
public sqlResponse insert_job_ticket_from_sd(string imei, int ticket_id, string mess, string seqID, int sched_time)
{
sqlResponse resp = sqlResponse.SQLerror;
Int32 sc_id = 0;
Int32 job_status = 0;
int specified_end_time = -1;
string[] messageString = mess.Split("^".ToCharArray());
string text = messageString[0];
string comment = messageString[1];
string priority = messageString[2];
string createdBy = messageString[3];
if(messageString[4]!=null && messageString[4]!="")
specified_end_time = Convert.ToInt32(messageString[4]);
string shareWith = messageString[5];
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 = "SELECT status_id from jobticketstatusesset where is_first_state = 1";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
job_status = Convert.ToInt32(cmd.ExecuteScalar());
}
string query = "INSERT INTO jobtickets (ticket_id, name, sc_id, job_status,deleted,creation_time,comment,priority,dispatcher, sched_timegmt ";
if (specified_end_time > -1) query += ", end_time_planned ";
query += ", share_with, seq_id, status, is_expired) VALUES(@ticket_id, @name, @sc_id, @job_status, 0, @creation_time, @comment, @priority, @dispatcher, @sched_timegmt";
if (specified_end_time > -1) query += ", " + specified_end_time;
query += ", @share_with, @seq_id, 0, false)";
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
{
cmd.Parameters.Add("@ticket_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = ticket_id;
cmd.Parameters.Add("@name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = text;
cmd.Parameters.Add("@sc_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = sc_id;
cmd.Parameters.Add("@job_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = job_status;
cmd.Parameters.Add("@creation_time", NpgsqlTypes.NpgsqlDbType.Integer).Value = DateTime.Now.ToUniversalTime().DateTo70Format();
cmd.Parameters.Add("@comment", NpgsqlTypes.NpgsqlDbType.Varchar).Value = comment;
cmd.Parameters.Add("@priority", NpgsqlTypes.NpgsqlDbType.Varchar).Value = priority;
cmd.Parameters.Add("@dispatcher", NpgsqlTypes.NpgsqlDbType.Varchar).Value = createdBy;
cmd.Parameters.Add("@sched_timegmt", NpgsqlTypes.NpgsqlDbType.Integer).Value = sched_time;
cmd.Parameters.Add("@share_with", NpgsqlTypes.NpgsqlDbType.Varchar).Value = shareWith;
cmd.Parameters.Add("@seq_id", NpgsqlTypes.NpgsqlDbType.Varchar).Value = seqID;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
//=======================================
//insert into job ticketing history
//=======================================
query = "INSERT INTO jobtickets_log (ticket_id, modified_date , job_status, sc_id) VALUES(@ticket_id, @modified_date , @job_status, @sc_id)";
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
{
cmd.Parameters.Add("@ticket_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = ticket_id;
cmd.Parameters.Add("@modified_date", NpgsqlTypes.NpgsqlDbType.Integer).Value = DateTime.Now.ToUniversalTime().DateTo70Format();
cmd.Parameters.Add("@job_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = job_status;
cmd.Parameters.Add("@sc_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = sc_id;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
}
}
catch (Exception o)
{
Console.WriteLine("File: DBTicketingManager.cs Meth: insert_job_ticket_from_sd " + o.Message.ToString());
}
return resp;
}
public sqlResponse update_ticket_delivered(string seqID, int status)
{
sqlResponse resp = sqlResponse.SQLerror;
// status = 1 : GW read the msg and delivered it
// status = 2 : GW received the confirm from radio unit
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string query = "UPDATE jobtickets SET status=" + status + " where seq_id='" + seqID + "'";
Console.WriteLine(query);
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
{
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
}
}
catch (Exception o)
{
Console.WriteLine($"update_ticket_delivered: {o.Message}", ConsoleColor.Red);
}
return resp;
}
public sqlResponse insert_ticket_response(String imei, string mess)
{
sqlResponse resp = sqlResponse.SQLerror;
//InitConnection();
Int32 status_id = 0;
Int32 sc_id = 0;
Int32 is_last_state = 0;
Int32 end_time = -1;
object updateResponse = null;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand($"SELECT sc_id from subscriber where imei='{imei}' LIMIT 1", connection))
{
sc_id = Convert.ToInt32(cmd.ExecuteScalar());
}
string status = Regex.Replace(mess, "[0-9]", "");
Int32 ticket_id = Convert.ToInt32(Regex.Replace(mess, "[a-zA-z]", ""));
string command = $"SELECT status_id, is_last_state from jobticketstatusesset where status='{status}'";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
status_id = dr.GetInt32(0);
is_last_state = dr.GetInt32(1);
}
}
}
if (status_id > 0)
{
string query = "UPDATE jobtickets SET job_status = @job_status, start_time = (CASE WHEN start_time IS NULL THEN @start_time else start_time END)";
if (is_last_state == 1) query += " , end_time = " + DateTime.UtcNow.DateTo70Format().ToString();
query += " WHERE ticket_id = @ticket_id AND deleted = 0";
query += " RETURNING ticket_id";
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
{
cmd.Parameters.Add("@ticket_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = ticket_id;
cmd.Parameters.Add("@job_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = status_id;
cmd.Parameters.Add("@start_time", NpgsqlTypes.NpgsqlDbType.Integer).Value = DateTime.UtcNow.DateTo70Format();
cmd.Prepare();
updateResponse = cmd.ExecuteScalar();
}
resp = (updateResponse != null) ? sqlResponse.done : sqlResponse.noUpdate;
if (updateResponse != null)
{
//insert into job ticketing history
query = "INSERT INTO jobtickets_log (ticket_id, modified_date , job_status, sc_id) VALUES(@ticket_id, @modified_date , @job_status, @sc_id)";
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
{
cmd.Parameters.Add("@ticket_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = ticket_id;
cmd.Parameters.Add("@modified_date", NpgsqlTypes.NpgsqlDbType.Integer).Value = DateTime.UtcNow.DateTo70Format();
cmd.Parameters.Add("@job_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = status_id;
cmd.Parameters.Add("@sc_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = sc_id;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
}
}
}
}
catch (Exception o)
{
Console.WriteLine("File: SMdb_access.cs Meth: insert_ticket_response : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return resp;
}
public sqlResponse delete_job_ticket_from_sd(int ticket_id, bool canDelete)
{
sqlResponse resp = sqlResponse.SQLerror;
try
{
string queryNormalUser = "UPDATE jobtickets SET deleted = 1 WHERE ticket_id = @ticket_id";
string querySpecial = "DELETE FROM jobtickets WHERE ticket_id = @ticket_id";
string query = canDelete ? querySpecial : queryNormalUser;
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
{
cmd.Parameters.Add("@ticket_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = ticket_id;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
if (canDelete)
{
string query_log = "DELETE FROM jobtickets_log WHERE ticket_id = @ticket_id";
using (NpgsqlCommand cmd = new NpgsqlCommand(query_log, connection))
{
cmd.Parameters.Add("@ticket_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = ticket_id;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
}
}
}
catch (Exception o)
{
Utils.WriteLine("File: DBTicketingManager.cs Meth: delete_job_ticket_from_sd " + o.Message.ToString(), ConsoleColor.Red);
}
return resp;
}
public List<JobTickets> getAllTickets(string dispatch_name, string dispatcher_id, bool isAdmin, bool bringDeleted)
{
List<JobTickets> jtList = new List<JobTickets>();
string query = "SELECT ticket_id, jt.name, case when jt.deleted = 1 then jt.comment || ' (is deleted)' else jt.comment end, " +
" v.name, jts.status, jt.creation_time, jt.priority, jt.dispatcher, jt.end_time_planned, jt.share_with, " +
" jt.start_time,jt.end_time, jt.is_expired, jt.deleted, jt.sched_timegmt " +
"FROM jobtickets jt JOIN vehicle v ON v.id = jt.sc_id " +
"JOIN jobticketstatusesset jts ON jts.status_id = jt.job_status WHERE ";
if (!bringDeleted)
query += " deleted = 0 AND ";
if (!isAdmin)
query += " (dispatcher = '" + dispatch_name + "' or share_with like '%" + dispatcher_id + ";%') ";
query += "ORDER by ticket_id";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
{
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
JobTickets jt = new JobTickets(Convert.ToInt32(Reader.GetValue(0).ToString()), Reader.GetValue(1).ToString(),
Reader.GetValue(2).ToString(), Reader.GetValue(3).ToString(), Reader.GetValue(4).ToString(),
Reader.GetValue(5).ToString(), Reader.GetValue(6).ToString(), Reader.GetValue(7).ToString(),
Reader.GetValue(8).ToString(), Reader.GetValue(9).ToString(), Reader.GetValue(10).ToString(),
Reader.GetValue(11).ToString(), Reader.GetBoolean(12), Reader.GetInt32(13), Reader.GetInt32(14));
JobTickets jobTicket = jtList.Find(x => x.Ticket_id.Equals(jt.Ticket_id));
if (jobTicket == null)
jtList.Add(jt);
else
{
jobTicket.Imei = jobTicket.Imei + "," + jt.Imei;
}
}
}
}
}
Utils.WriteLine("Got " + jtList.Count + " tickets");
}
catch (Exception ee)
{
Console.WriteLine(ee.Message, ConsoleColor.Red);
}
return jtList;
}
public List<JobTickets> getAllTicketsSimulator(string dispatch_name, string dispatcher_id, bool isAdmin, bool bringDeleted)
{
List<JobTickets> jtList = new List<JobTickets>();
string query = "SELECT ticket_id, jt.name, case when jt.deleted = 1 then jt.comment || ' (is deleted)' else jt.comment end, " +
" v.name, jts.status, jt.creation_time, jt.priority, jt.dispatcher, jt.end_time_planned, jt.share_with, " +
" jt.start_time,jt.end_time, jt.is_expired, jt.deleted, jt.sched_timegmt " +
"FROM jobtickets jt JOIN vehicle v ON v.id = jt.sc_id " +
"JOIN jobticketstatusesset jts ON jts.status_id = jt.job_status WHERE ";
if (!bringDeleted)
query += " deleted = 0 AND ";
if (!isAdmin)
query += " (dispatcher = '" + dispatch_name + "' or share_with like '%" + dispatcher_id + ";%') ";
query += "ORDER by ticket_id";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
{
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
JobTickets jt = new JobTickets(Convert.ToInt32(Reader.GetValue(0).ToString()), Reader.GetValue(1).ToString(), Reader.GetValue(2).ToString(),
Reader.GetValue(3).ToString(), Reader.GetValue(4).ToString(), Reader.GetValue(5).ToString(), Reader.GetValue(6).ToString(),
Reader.GetValue(7).ToString(), Reader.GetValue(8).ToString(), Reader.GetValue(9).ToString(), Reader.GetValue(10).ToString(), Reader.GetValue(11).ToString(), Reader.GetBoolean(12),
Reader.GetInt32(13), Reader.GetInt32(14));
jtList.Add(jt);
}
}
}
}
Utils.WriteLine("Got " + jtList.Count + " tickets");
}
catch (Exception ee)
{
Console.WriteLine(ee.Message, ConsoleColor.Red);
}
return jtList;
}
public List<JobTickets> get_All_Expiring_Tickets()
{
List<JobTickets> jtList = new List<JobTickets>();
int currentUniversalDate = (int)DateTime.Now.ToUniversalTime().DateTo70Format();
string query = "SELECT t.ticket_id, t.end_time_planned, s.imei, t.name, t.sc_id " +
"FROM jobtickets t " +
"JOIN subscriber s ON s.sc_id = t.sc_id " +
"WHERE t.end_time_planned > @end_time_planned " +
"ORDER by t.ticket_id";
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
{
cmd.Parameters.Add("@end_time_planned", NpgsqlTypes.NpgsqlDbType.Integer).Value = currentUniversalDate;
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
JobTickets jt = new JobTickets()
{
Ticket_id = Reader.GetInt32(0),
End_time_specified_int = Reader.GetInt32(1),
Imei = Reader.GetString(2),
Name = Reader.GetString(3),
sc_id = Reader.GetInt32(4)
};
int seconds = (int)(jt.End_time_specified_int - currentUniversalDate);
if (seconds <= 610 && seconds >= 589)
{
jtList.Add(jt);
}
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.ToString());
}
return jtList;
}
public List<JobTickets_log> getAllTickets_log()
{
List<JobTickets_log> jtList = new List<JobTickets_log>();
string command = "SELECT ticket_id, status, modified_date, v.name, sc.imei " +
"FROM jobtickets_log jt " +
"JOIN jobticketstatusesset jts ON jts.status_id = jt.job_status " +
"JOIN vehicle v ON v.id = jt.sc_id " +
"JOIN subscriber sc ON sc.sc_id = jt.sc_id " +
"ORDER by ticket_id, v.name, modified_date";
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())
{
JobTickets_log jt = new JobTickets_log()
{
Ticket_id = Reader.GetInt32(0),
Job_status = Reader.GetString(1),
Modified_date = Reader.GetInt32(2).ToString(),
ModifiedDate70 = Reader.GetInt32(2),
Vehicle_name = Reader.GetString(3),
Imei = Reader.GetString(4)
};
jtList.Add(jt);
}
}
}
JobTickets_log aux = new JobTickets_log();
foreach (JobTickets_log obj in jtList)
{
if (aux.Imei == obj.Imei && aux.Ticket_id == obj.Ticket_id)
{
obj.PreviousJobStatus = aux.Job_status;
}
aux = obj;
}
jtList = jtList.OrderBy(x => x.Modified_date).ToList();
}
}
catch (Exception ee)
{
Console.WriteLine(ee.ToString());
}
return jtList;
}
public List<Int32> updateTicketExpireDate(uint DatetimeNow)
{
TicketResponse resp;
List<Int32> expired_tickets_idList = new List<int>();
try
{
//string command = $"UPDATE jobtickets SET is_expired = true, job_status = 6 WHERE end_time_planned < @end_time_planned and "
// + " job_status not in (3,6) RETURNING ticket_id ");
string command = "UPDATE jobtickets SET is_expired = true, job_status = 6 " +
$"WHERE end_time_planned < {DatetimeNow} AND job_status NOT IN (3,6) RETURNING ticket_id ";
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
NpgsqlCommand cmd = new NpgsqlCommand(command, connection);
{
//job status = 6 for "Overdue" job status = 3 for "Completed"
//cmd.Parameters.Add("@end_time_planned", NpgsqlTypes.NpgsqlDbType.Integer).Value = DatetimeNow;
cmd.Prepare();
//cmd.ExecuteNonQuery();
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
expired_tickets_idList.Add(Reader.GetInt32(0));
}
}
}
resp = TicketResponse.done;
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = TicketResponse.SQLerror;
}
return expired_tickets_idList;
}
public List<JobTickets> get_ALL_jobtickets_not_delivered()
{
List<JobTickets> ret = new List<JobTickets>();
JobTickets obj;
try
{
string command = "SELECT s.imei , t.ticket_id, t.name, t.seq_id, t.sc_id, t.sched_timegmt,t.comment, t.end_time_planned from jobtickets t, subscriber s" +
" where t.deleted =0 and t.status=0 and s.sc_id = t.sc_id ORDER BY t.sc_id";
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
obj = new JobTickets();
obj.Imei = dr.GetString(0);
obj.Name = "<JTS>" + dr.GetInt32(1).ToString() + Environment.NewLine + dr.GetString(2);
obj.seq_id = dr.GetString(3);
obj.sc_id = dr.GetInt32(4);
obj.sched_timegmt = dr.GetInt32(5);
if (dr.GetValue(6).ToString() != "") obj.Name += string.Format(" ({0})", dr.GetString(6));
if (dr.GetValue(7).ToString() != "") obj.Name += string.Format(" until {0}", dr.GetInt32(7).GetDTLocalFromSeconds()).ToString();
ret.Add(obj);
}
}
}
}
}
catch (Exception o)
{
Console.WriteLine("File: DBTicketingManager.cs Meth: get_ALL_jobtickets_not_delivered : " + o.Message.ToString());
throw new ArgumentException(o.Message.ToString());
}
return ret;
}
#endregion
#region Job Ticket Predefine
public TicketResponse InsertUpdate_Define_Ticket(PredefineJobTicket pdf)
{
TicketResponse resp;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
object result = null;
string command = "SELECT COUNT(name) FROM definejobticket WHERE name = '" + pdf.Name + "\'";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
result = cmd.ExecuteScalar();
}
if (result.ToString() == "0")
{
command = "INSERT INTO definejobticket (name, text , comment, priority, expire_date, schedule_date, share_with,handled_by )" +
" VALUES(@name, @text , @comment, @priority, @expire_date, @schedule_date, @share_with, @handled_by)";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.Parameters.Add("@name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Name;
cmd.Parameters.Add("@text", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Text;
cmd.Parameters.Add("@comment", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Comment;
cmd.Parameters.Add("@priority", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Priority;
cmd.Parameters.Add("@expire_date", NpgsqlTypes.NpgsqlDbType.Integer).Value = pdf.Enddate;
cmd.Parameters.Add("@schedule_date", NpgsqlTypes.NpgsqlDbType.Integer).Value = pdf.Scheduledate;
cmd.Parameters.Add("@share_with", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Share_with;
cmd.Parameters.Add("@handled_by", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Handled_by;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = TicketResponse.done;
}
else
{
command = "UPDATE definejobticket SET name = @name, text = @text , comment = @comment, priority = @priority, " +
"expire_date = @expire_date, schedule_date = @schedule_date, share_with = @share_with, handled_by = @handled_by where name = @name";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.Parameters.Add("@name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Name;
cmd.Parameters.Add("@text", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Text;
cmd.Parameters.Add("@comment", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Comment;
cmd.Parameters.Add("@priority", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Priority;
cmd.Parameters.Add("@expire_date", NpgsqlTypes.NpgsqlDbType.Integer).Value = pdf.Enddate;
cmd.Parameters.Add("@schedule_date", NpgsqlTypes.NpgsqlDbType.Integer).Value = pdf.Scheduledate;
cmd.Parameters.Add("@share_with", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Share_with;
cmd.Parameters.Add("@handled_by", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pdf.Handled_by;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = TicketResponse.done;
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = TicketResponse.SQLerror;
}
return resp;
}
public List<PredefineJobTicket> get_all_PredefineTickets()
{
List<PredefineJobTicket> jtList = new List<PredefineJobTicket>();
string command = " SELECT name, text, priority, comment, share_with, handled_by, expire_date, schedule_date " +
" FROM definejobticket " +
" 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())
{
PredefineJobTicket jt = new PredefineJobTicket()
{
Name = Reader.GetString(0),
Text = Reader.GetString(1),
Priority = Reader.GetString(2),
Comment = Reader.GetString(3),
Share_with = Reader.GetValue(4).ToString(),
Handled_by = Reader.GetValue(5).ToString(),
Enddate = (Reader.GetValue(6).ToString() != "") ? Reader.GetInt32(6) : 0,
Scheduledate = (Reader.GetValue(7).ToString() != "") ? Reader.GetInt32(7) : -1
};
jtList.Add(jt);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.ToString());
}
return jtList;
}
public sqlResponse delete_Predefine_Ticket(string ticket_name)
{
sqlResponse resp = sqlResponse.SQLerror;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string query = "DELETE from definejobticket WHERE name = @name";
using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
{
cmd.Parameters.Add("@name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = ticket_name;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = sqlResponse.done;
}
}
catch (Exception o)
{
Console.WriteLine("File: DBTicketingManager.cs Meth: delete_Predefine_Ticket " + o.Message.ToString());
}
return resp;
}
#endregion
#region Job Ticket Statuses
public TicketResponse addTicketStatusToDb(TicketingStatus ts)
{
TicketResponse resp;
int isFirstStateDefault = 0;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "SELECT COUNT(status_id) FROM jobticketstatusesset";
object isEmptyStatus = null;
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
isEmptyStatus = cmd.ExecuteScalar();
}
if (isEmptyStatus.ToString() == "0")
isFirstStateDefault = 1;
command = "SELECT COUNT(status_id) FROM jobticketstatusesset WHERE status=\'" + ts.Status + "\'";
object result = null;
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
result = cmd.ExecuteScalar();
}
if (result != null && result.ToString() == "0")
{
command = "INSERT INTO jobticketstatusesset (status, color, sound_on, sound_file, is_first_state, is_last_state) " +
" VALUES(@status,@color,@sound_on,@sound_file,@is_first_state,@is_last_state)";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.Parameters.Add("@status", NpgsqlTypes.NpgsqlDbType.Varchar).Value = ts.Status;
cmd.Parameters.Add("@color", NpgsqlTypes.NpgsqlDbType.Varchar).Value = ts.Color;
cmd.Parameters.Add("@sound_on", NpgsqlTypes.NpgsqlDbType.Integer).Value = ts.SoundOn;
cmd.Parameters.Add("@sound_file", NpgsqlTypes.NpgsqlDbType.Varchar).Value = ts.SoundFile;
cmd.Parameters.Add("@is_first_state", NpgsqlTypes.NpgsqlDbType.Integer).Value = (isFirstStateDefault == 1) ? isFirstStateDefault : ts.IsFirstState;
cmd.Parameters.Add("@is_last_state", NpgsqlTypes.NpgsqlDbType.Integer).Value = ts.IsLastState;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = TicketResponse.done;
}
else
{
resp = TicketResponse.alreadyInDB;
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = TicketResponse.SQLerror;
}
return resp;
}
public TicketResponse updateTicketStatusToDb(TicketingStatus ts)
{
TicketResponse resp;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "SELECT COUNT(status_id) FROM jobticketstatusesset WHERE status_id=\'" + ts.StatusID + "\'";
object result = null;
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
result = cmd.ExecuteScalar();
}
if (result != null && Convert.ToInt32(result) > 0)
{
command = "SELECT COUNT(status_id) FROM jobticketstatusesset WHERE status=\'" + ts.Status + "\'" + "and status_id <>\'" + ts.StatusID + "\'";
result = null;
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
result = cmd.ExecuteScalar();
}
if (result != null && result.ToString() == "0")
{
command = "UPDATE jobticketstatusesset SET status=@status, color=@color, sound_on=@sound_on, sound_file=@sound_file, is_first_state=@is_first_state, is_last_state=@is_last_state where status_id=@status_id";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.Parameters.Add("@status", NpgsqlTypes.NpgsqlDbType.Varchar).Value = ts.Status;
cmd.Parameters.Add("@color", NpgsqlTypes.NpgsqlDbType.Varchar).Value = ts.Color;
cmd.Parameters.Add("@sound_on", NpgsqlTypes.NpgsqlDbType.Integer).Value = ts.SoundOn;
cmd.Parameters.Add("@sound_file", NpgsqlTypes.NpgsqlDbType.Varchar).Value = ts.SoundFile;
cmd.Parameters.Add("@is_first_state", NpgsqlTypes.NpgsqlDbType.Integer).Value = ts.IsFirstState;
cmd.Parameters.Add("@is_last_state", NpgsqlTypes.NpgsqlDbType.Integer).Value = ts.IsLastState;
cmd.Parameters.Add("@status_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = ts.StatusID;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = TicketResponse.done;
}
else
{
resp = TicketResponse.alreadyInDB;
}
}
else
{
resp = TicketResponse.ticketNotInDB;
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = TicketResponse.SQLerror;
}
return resp;
}
public TicketResponse resetDefaultStatus()
{
TicketResponse resp;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "UPDATE jobticketstatusesset SET is_first_state=0";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = TicketResponse.done;
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = TicketResponse.SQLerror;
}
return resp;
}
public TicketResponse resetCompletionStatus()
{
TicketResponse resp;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "UPDATE jobticketstatusesset SET is_last_state=0";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.Prepare();
cmd.ExecuteNonQuery();
}
resp = TicketResponse.done;
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = TicketResponse.SQLerror;
}
return resp;
}
public TicketResponse deleteTicketingStatus(string ticketStatus)
{
//TODO before delete ticket status check job ticketing transactions
//InitConnection();
TicketResponse resp;
Int32 status_id = -1;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "SELECT status_id FROM jobticketstatusesset WHERE status=\'" + ticketStatus + "\'";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
status_id = Convert.ToInt32(cmd.ExecuteScalar());
}
if (status_id > -1)
{
command = "SELECT COUNT(job_status) FROM jobtickets WHERE job_status =\'" + status_id + "\'";
object tmp1 = null;
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
tmp1 = cmd.ExecuteScalar();
}
if (tmp1 != null && tmp1.ToString() == "0")
{
command = $"DELETE FROM jobticketstatusesset WHERE status='{ticketStatus}'";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
cmd.ExecuteNonQuery();
}
resp = TicketResponse.done;
}
else
{
resp = TicketResponse.alreadyInTransactions;
}
}
else
{
resp = TicketResponse.ticketNotInDB;
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.Message + " " + ee.StackTrace + " " + ee.Source);
resp = TicketResponse.SQLerror;
}
return resp;
}
public List<TicketingStatus> getAllTicketStatuses()
{
List<TicketingStatus> tsList = new List<TicketingStatus>();
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "SELECT status_id, status, color, sound_on, sound_file, is_first_state, is_last_state FROM jobticketstatusesset ORDER by status_id";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
{
while (Reader.Read())
{
TicketingStatus ts = new TicketingStatus()
{
StatusID = Reader.GetInt32(0),
Status = Reader.GetString(1),
Color = Reader.GetString(2),
SoundOn = Reader.GetInt32(3),
SoundFile = Reader.GetString(4),
IsFirstState = Reader.GetInt32(5),
IsLastState = Reader.GetInt32(6)
};
tsList.Add(ts);
}
}
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.ToString());
}
return tsList;
}
public int getStatusID(string ticketStatus)
{
int status_id = -1;
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = getConnectionString();
connection.Open();
string command = "SELECT status_id FROM jobticketstatusesset WHERE status=\'" + ticketStatus + "\'";
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
{
status_id = Convert.ToInt32(cmd.ExecuteScalar());
}
}
}
catch (Exception ee)
{
Console.WriteLine(ee.ToString());
}
return status_id;
}
#endregion
}
public class TicketingStatus
{
private int statusID;
public int StatusID
{
get { return statusID; }
set { statusID = value; }
}
private string status;
public string Status
{
get { return status; }
set { status = value; }
}
private string color;
public string Color
{
get { return color; }
set { color = value; }
}
private int soundOn;
public int SoundOn
{
get { return soundOn; }
set { soundOn = value; }
}
private string soundFile;
public string SoundFile
{
get { return soundFile; }
set { soundFile = value; }
}
private int isFirstState;
public int IsFirstState
{
get { return isFirstState; }
set { isFirstState = value; }
}
private int isLastState;
public int IsLastState
{
get { return isLastState; }
set { isLastState = value; }
}
public TicketingStatus()
{
}
}
public class JobTickets
{
private string ticketInfo;
public string TicketInfo
{
get { return ticketInfo; }
set { TicketInfo = value; }
}
private int ticket_id;
public int Ticket_id
{
get { return ticket_id; }
set { ticket_id = value; }
}
private string name;
public string Name
{
get { return name; }
set { name = value; }
}
private string comment;
public string Comment
{
get { return comment; }
set { comment = value; }
}
private string imei;
public string Imei
{
get { return imei; }
set { imei = value; }
}
private string status;
public string Status
{
get { return status; }
set { status = value; }
}
private string creation_time;
public string Creation_time
{
get { return creation_time; }
set { creation_time = value; }
}
private string start_time;
public string Start_time
{
get { return start_time; }
set { start_time = value; }
}
private int creation_time_int;
public int Creation_time_int
{
get { return creation_time_int; }
set { creation_time_int = value; }
}
private int start_time_int;
public int Start_time_int
{
get { return start_time_int; }
set { start_time_int = value; }
}
private string end_time;
public string End_time
{
get { return end_time; }
set { end_time = value; }
}
private int end_time_int;
public int End_time_int
{
get { return end_time_int; }
set { end_time_int = value; }
}
private string end_time_specified;
public string End_time_specified
{
get { return end_time_specified; }
set { end_time_specified = value; }
}
private int end_time_specified_int;
public int End_time_specified_int
{
get { return end_time_specified_int; }
set { end_time_specified_int = value; }
}
private string _lastUpdate;
public string lastUpdate
{
get { return _lastUpdate; }
set { _lastUpdate = value; }
}
private Int64 _lastUpdate70;
public Int64 lastUpdate70
{
get { return _lastUpdate70; }
set { _lastUpdate70 = value; }
}
private string priority;
public string Priority
{
get { return priority; }
set { priority = value; }
}
private string dispatcher;
public string Dispatcher
{
get { return dispatcher; }
set { dispatcher = value; }
}
private string share_with;
public string Share_with
{
get { return share_with; }
set { share_with = value; }
}
private int deleted;
public int Deleted
{
get { return deleted; }
set { deleted = value; }
}
private bool is_expired;
public bool IsExpired
{
get { return is_expired; }
set { is_expired = value; }
}
public String seq_id;
public int sc_id;
public int sched_timegmt;
public JobTickets()
{ }
public JobTickets(int _ticket_id, string _name, string _comment, string _imei, string _status, string _creation_time_int, string _priority,
string _dispatcher, string _end_time_specified_int, string _share_with, string _start_time_int, string _end_time_int, bool isExpired, int deleted, Int32 sched_time)
{
this.Ticket_id = _ticket_id;
this.Name = _name;
this.Comment = _comment;
this.Imei = _imei;
this.Status = _status;
if (_creation_time_int != "" && _creation_time_int!=string.Empty)
this.Creation_time_int = Convert.ToInt32(_creation_time_int);
if (_end_time_specified_int != "" && _end_time_specified_int != string.Empty && _end_time_specified_int != "-1")
this.End_time_specified_int = Convert.ToInt32(_end_time_specified_int);
if (_end_time_int != "" && _end_time_int != string.Empty && _end_time_int != "-1")
this.End_time_int = Convert.ToInt32(_end_time_int);
if (_end_time_int != "" && _end_time_int != string.Empty && _end_time_int != "-1")
this.End_time_int = Convert.ToInt32(_end_time_int);
this.Priority = _priority;
this.Share_with = _share_with;
this.Dispatcher = _dispatcher;
if (_start_time_int != "" && _start_time_int != string.Empty)
this.Start_time_int = Convert.ToInt32(_start_time_int);
this.is_expired = isExpired;
this.deleted = deleted;
this.sched_timegmt = sched_time;
}
}
public class JobTickets_log
{
private int ticket_id;
public int Ticket_id
{
get { return ticket_id; }
set { ticket_id = value; }
}
private string job_status;
public string Job_status
{
get { return job_status; }
set { job_status = value; }
}
private string modified_date;
public string Modified_date
{
get { return modified_date; }
set { modified_date = value; }
}
private Int64 _modifiedDate70;
public Int64 ModifiedDate70
{
get { return _modifiedDate70; }
set { _modifiedDate70 = value; }
}
private string vehicle_name;
public string Vehicle_name
{
get { return vehicle_name; }
set { vehicle_name = value; }
}
private string imei;
public string Imei
{
get { return imei; }
set { imei = value; }
}
private string previousJobStatus;
public string PreviousJobStatus
{
get { return previousJobStatus; }
set { previousJobStatus = value; }
}
public JobTickets_log()
{ }
}
public class PredefineJobTicket
{
private string name;
public string Name
{
get { return name; }
set { name = value; }
}
private string text;
public string Text
{
get { return text; }
set { text = value; }
}
private string comment;
public string Comment
{
get { return comment; }
set { comment = value; }
}
private string priority;
public string Priority
{
get { return priority; }
set { priority = value; }
}
private int enddate;
public int Enddate
{
get { return enddate; }
set { enddate = value; }
}
private int scheduledate;
public int Scheduledate
{
get { return scheduledate; }
set { scheduledate = value; }
}
private string share_with;
public string Share_with
{
get { return share_with; }
set { share_with = value; }
}
private string handled_by;
public string Handled_by
{
get { return handled_by; }
set { handled_by = value; }
}
public PredefineJobTicket()
{ }
}
public enum TicketResponse
{
done,
alreadyInDB,
SQLerror,
ticketNotInDB,
alreadyInTransactions
}
}