private TicketQueueModel(UserModel user, TicketModel ticket, int ticketQueueID, bool verify) : base(ticket)
{
Ticket = ticket;
User = user;
TicketQueueID = ticketQueueID;
if (verify)
{
Verify();
}
}
//public TaskAssociationModel[] TaskAssociations() { return TaskAssociationModel.GetTaskAssociations(this); }
//public TicketQueueModel[] TicketQueue() { return TicketQueueModel.GetQueuedTicket(this); }
public TicketModel[] ChildTickets()
{
int[] ticketIDs = IDReader.Read(TicketChild.Children, this);
TicketModel[] childTickets = new TicketModel[ticketIDs.Length];
for (int i = 0; i < ticketIDs.Length; ++i)
{
childTickets[i] = new TicketModel(Organization, ticketIDs[i]);
}
return(childTickets);
}
/// <summary> find all the contact organizations associated with a ticket </summary>
public static OrganizationTicketModel[] GetOrganizationTickets(TicketModel ticket)
{
//$"Select OrganizationID From OrganizationTickets WITH (NOLOCK) WHERE TicketId = {ticket.TicketID}"
int[] customerIDs = IDReader.Read(TicketChild.Customers, ticket);
OrganizationTicketModel[] organizationTickets = new OrganizationTicketModel[customerIDs.Length];
for (int i = 0; i < customerIDs.Length; ++i)
{
organizationTickets[i] = new OrganizationTicketModel(ticket, new OrganizationModel(ticket.Connection, customerIDs[i]), false);
}
return(organizationTickets);
}
//public int TicketID()
//{
// return ExecuteQuery<int>($"SELECT TicketID FROM Actions WITH (NOLOCK) WHERE ActionID = {ActionID}").Min();
//}
public static ActionModel[] GetActions(TicketModel ticket)
{
string query = $"SELECT ActionID FROM Actions WITH (NOLOCK) WHERE TicketId={ticket.TicketID}";
int[] actionIDs = ticket.ExecuteQuery <int>(query).ToArray();
ActionModel[] actions = new ActionModel[actionIDs.Length];
for (int i = 0; i < actionIDs.Length; ++i)
{
actions[i] = new ActionModel(ticket, actionIDs[i], false);
}
return(actions);
}
public static SubscriptionModel[] GetSubscriptions(TicketModel ticket)
{
//query = $"SELECT Subscriptions.userid FROM Subscriptions WITH (NOLOCK) " +
// $"JOIN Users WITH (NOLOCK) on users.userid = Subscriptions.userid " +
// $"WHERE Reftype = 17 and Refid = {ticket.TicketID} and MarkDeleted = 0";
int[] subscriptionUserIDs = IDReader.Read(TicketChild.Subscriptions, ticket);
SubscriptionModel[] subscriptions = new SubscriptionModel[subscriptionUserIDs.Length];
for (int i = 0; i < subscriptionUserIDs.Length; ++i)
{
subscriptions[i] = new SubscriptionModel(ticket, new UserModel(ticket.Connection, subscriptionUserIDs[i]), false);
}
return(subscriptions);
}
public static UserTicketModel[] GetUserTickets(TicketModel ticket)
{
//query = $"SELECT Users.userid FROM Users WITH (NOLOCK)" +
// $"JOIN UserTickets WITH (NOLOCK) on UserTickets.userid = Users.UserID" +
// $" WHERE UserTickets.TicketID = {ticket.TicketID} AND (Users.MarkDeleted = 0)";
int[] contactIDs = IDReader.Read(TicketChild.Contacts, ticket);
UserTicketModel[] contacts = new UserTicketModel[contactIDs.Length];
for (int i = 0; i < contactIDs.Length; ++i)
{
contacts[i] = new UserTicketModel(ticket, new UserModel(ticket.Connection, contactIDs[i]), false);
}
return(contacts);
}
public static AssetTicketModel[] GetAssetTickets(TicketModel ticket)
{
OrganizationModel organization = ticket.Organization;
//int[] ids = IDReader.Read(TicketChild.Asset, ticket); // $"SELECT AssetID From AssetTickets WITH (NOLOCK) WHERE TicketID = {ticket.TicketID}"
string query = $"SELECT AssetID From AssetTickets WITH (NOLOCK) WHERE TicketID = {ticket.TicketID}";
int[] ids = ticket.ExecuteQuery <int>(query).ToArray();
AssetTicketModel[] models = new AssetTicketModel[ids.Length];
for (int i = 0; i < ids.Length; ++i)
{
models[i] = new AssetTicketModel(new AssetModel(organization, ids[i]), ticket, false);
}
return(models);
}
public static int[] Read(TicketChild childID, TicketModel ticket)
{
string query = String.Empty;
switch (childID)
{
case TicketChild.Contacts:
query = $"SELECT Users.userid FROM Users WITH (NOLOCK)" +
$"JOIN UserTickets WITH (NOLOCK) on UserTickets.userid = Users.UserID" +
$" WHERE UserTickets.TicketID = {ticket.TicketID} AND (Users.MarkDeleted = 0)";
break;
case TicketChild.Customers:
query = $"Select Organizationid From OrganizationTickets WITH (NOLOCK) WHERE TicketId = {ticket.TicketID}";
break;
case TicketChild.Subscriptions:
query = $"SELECT Subscriptions.userid FROM Subscriptions WITH (NOLOCK) " +
$"JOIN Users WITH (NOLOCK) on users.userid = Subscriptions.userid " +
$"WHERE Reftype = 17 and Refid = {ticket.TicketID} and MarkDeleted = 0";
break;
case TicketChild.TicketReminders:
query = $"SELECT ReminderID FROM Reminders WITH (NOLOCK) WHERE RefID = {ticket.TicketID} AND Reftype = 17";
break;
case TicketChild.TaskAssociations:
query = $"SELECT TaskID FROM TaskAssociations WITH (NOLOCK) WHERE Refid={ticket.TicketID} and RefType = 17";
break;
case TicketChild.Asset:
query = $"SELECT AssetID From AssetTickets WITH (NOLOCK) WHERE TicketID = {ticket.TicketID}";
break;
case TicketChild.Children:
query = $"SELECT TicketID FROM Tickets WITH(NOLOCK) WHERE ParentID={ticket.TicketID}";
break;
case TicketChild.TicketTagLinks:
query = $"SELECT TagLinkID FROM TagLinks WITH(NOLOCK) WHERE Reftype=17 and RefID = {ticket.TicketID}";
break;
}
return(ticket.ExecuteQuery <int>(query).ToArray());
}
public UserTicketModel(TicketModel ticket, UserModel contact) : this(ticket, contact, true)
{
}
/// <summary> top down - existing action </summary>
public TicketReminderModel(TicketModel ticket, int reminderID) : this(ticket, reminderID, true)
{
}
public OrganizationTicketModel(TicketModel ticket, OrganizationModel organization) : this(ticket, organization, true)
{
}
/// <summary> top down - existing action </summary>
public SubscriptionModel(TicketModel ticket, UserModel user) : this(ticket, user, true)
{
}
public TicketQueueModel(UserModel user, TicketModel ticket, int ticketQueueID) : this(user, ticket, ticketQueueID, true)
{
}
public static int[] Read(TicketAssociation childID, TicketModel destinationTicket, TicketModel sourceTicket)
{
if (destinationTicket.Connection != sourceTicket.Connection)
{
throw new Exception("tickets must come from the same connection");
}
string query = String.Empty;
switch (childID)
{
case TicketAssociation.QueueUsers:
query = $"SELECT TicketQueue.UserID " +
$"FROM TicketQueue WITH (NOLOCK) " +
$"JOIN Users WITH (NOLOCK) on Users.userid = TicketQueue.userid " +
$"LEFT JOIN TicketQueue TicketQueue2 WITH(NOLOCK) on TicketQueue2.userid = TicketQueue.userid and TicketQueue2.ticketid = {destinationTicket.TicketID} " +
$"WHERE TicketQueue.ticketid ={sourceTicket.TicketID} and TicketQueue2.TicketQueueID IS NULL and MarkDeleted =0";
break;
case TicketAssociation.Relationships1:
query = $"SELECT TicketRelationshipID FROM TicketRelationships WITH(NOLOCK) WHERE Ticket1ID={sourceTicket.TicketID} AND Ticket2ID <> {destinationTicket.TicketID}";
break;
case TicketAssociation.Relationships2:
query = $"SELECT TicketRelationshipID FROM TicketRelationships WITH(NOLOCK) WHERE Ticket2ID={sourceTicket.TicketID} and Ticket1ID={destinationTicket.TicketID}";
break;
}
return(sourceTicket.ExecuteQuery <int>(query).ToArray());
}
public AssetTicketModel(AssetModel asset, TicketModel ticket) : this(asset, ticket, true)
{
}
/// <summary> top down - existing action </summary>
public ActionModel(TicketModel ticket, int actionID) : this(ticket, actionID, true)
{
}
