1583 lines
59 KiB
C#
1583 lines
59 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))
|
|
{
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
|
|
ticket_id = Convert.ToInt32(Reader.GetValue(0).ToString());
|
|
}
|
|
}
|
|
}
|
|
|
|
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('^');
|
|
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))
|
|
{
|
|
using (NpgsqlDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
while (dr.Read())
|
|
{
|
|
sc_id = dr.GetInt32(0);
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
command = "SELECT status_id from jobticketstatusesset where is_first_state = 1";
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(command, connection))
|
|
{
|
|
using (NpgsqlDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
while (dr.Read())
|
|
{
|
|
job_status = dr.GetInt32(0);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
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}'", connection))
|
|
{
|
|
using (NpgsqlDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
while (dr.Read())
|
|
{
|
|
sc_id = dr.GetInt32(0);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
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))
|
|
{
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
status_id = Reader.GetInt32(0);
|
|
}
|
|
}
|
|
}
|
|
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))
|
|
{
|
|
using (NpgsqlDataReader Reader = cmd.ExecuteReader())
|
|
{
|
|
while (Reader.Read())
|
|
{
|
|
status_id = Convert.ToInt32(Reader.GetValue(0).ToString());
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
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
|
|
}
|
|
|
|
}
|