/// <summary> Read most recent filenames for this ticket </summary>
public static void ReadActionAttachmentsForTicket(TicketModel ticketModel, ActionAttachmentsByTicketID ticketActionAttachments, out AttachmentProxy[] mostRecentByFilename)
{
switch (ticketActionAttachments)
{
case ActionAttachmentsByTicketID.ByFilename:
{
DataContext db = ticketModel.Connection._db;
Table <ActionProxy> actionTable = db.GetTable <ActionProxy>();
int[] actionID = (from a in actionTable where a.TicketID == ticketModel.TicketID select a.ActionID).ToArray();
Table <AttachmentProxy> attachmentTable = db.GetTable <AttachmentProxy>();
AttachmentProxy[] allAttachments = attachmentTable.Where(a => actionID.Contains(a.RefID)).ToArray();
List <AttachmentProxy> tmp = new List <AttachmentProxy>();
foreach (AttachmentProxy attachment in allAttachments)
{
if (!tmp.Exists(a => a.FileName == attachment.FileName))
{
tmp.Add(attachment);
}
}
mostRecentByFilename = tmp.ToArray();
}
break;
case ActionAttachmentsByTicketID.KnowledgeBase:
{
string query = SelectActionAttachmentProxy + $"JOIN Actions ac ON a.ActionID = ac.ActionID WHERE ac.TicketID = {ticketModel.TicketID} AND ac.IsKnowledgeBase = 1";
mostRecentByFilename = ticketModel.ExecuteQuery <AttachmentProxy>(query).ToArray();
}
break;
default:
mostRecentByFilename = null;
break;
}
}
/// <summary>
/// READ - read proxy given a model
/// </summary>
public static TProxy Read <TProxy>(IDNode node) where TProxy : class
{
TProxy tProxy = default(TProxy);
switch (typeof(TProxy).Name) // alphabetized list
{
case "ActionProxy": // action
{
ActionModel model = (ActionModel)node;
{
DataContext db = model.Connection._db;
Table <ActionProxy> table = db.GetTable <ActionProxy>();
var query = from row in table where row.ActionID == model.ActionID select row;
tProxy = query.First() as TProxy;
}
//{
// string query = $"SELECT ActionID, ActionTypeID, SystemActionTypeID, Name, TimeSpent, DateStarted, IsVisibleOnPortal, IsKnowledgeBase, ImportID, DateCreated, DateModified, CreatorID, ModifierID, TicketID, Description, SalesForceID, DateModifiedBySalesForceSync, Pinned, ImportFileID FROM Actions WHERE ActionID={model.ActionID}";
// DataRow row = model.Connection.GetRowCollection(query)[0];
// tProxy = new ActionProxy(row) as TProxy;
//}
}
break;
case "ActionProxy[]": // ticket actions
{
TicketModel ticket = (TicketModel)node;
string query = $"SELECT * FROM Actions WHERE ActionID={ticket.TicketID}";
tProxy = ticket.ExecuteQuery <ActionProxy>(query).ToArray() as TProxy;
}
break;
case "AttachmentProxy": // read all attachment types
tProxy = ReadRefTypeProxy <TProxy>(node.Connection, (node as AttachmentModel).AttachmentID);
break;
case "AttachmentProxy[]":
switch (node)
{
case ActionModel model: // action attachments
Table <AttachmentProxy> table = node.Connection._db.GetTable <AttachmentProxy>();
tProxy = table.Where(a => a.RefID == model.ActionID && a.RefType == AttachmentProxy.References.Actions).ToArray() as TProxy;
break;
case null:
default:
if (Debugger.IsAttached)
{
Debugger.Break();
}
break;
}
break;
case "ReminderProxy":
{
TicketReminderModel reminder = (TicketReminderModel)node;
string query = $"SELECT * FROM Reminders WHERE ReminderID = {reminder.ReminderID} AND RefType=17";
tProxy = reminder.ExecuteQuery <ReminderProxy>(query).ToArray() as TProxy;
}
break;
case "SubscriptionModel[]":
{
TicketModel ticket = (TicketModel)node;
string query = $"SELECT * FROM Subscriptions WHERE Reftype = 17 and RefID = {ticket.TicketID} and MarkDeleted = 0";
tProxy = ticket.ExecuteQuery <SubscriptionModel>(query).ToArray() as TProxy;
}
break;
case "TaskAssociationProxy":
{
TaskAssociationModel taskAssociation = (TaskAssociationModel)node;
string query = String.Empty; // = $"SELECT TaskID, RefID, RefType,CreatorID, DateCreated FROM TaskAssociations WHERE TaskID = {model.TaskID} AND RefID = {model.Ticket.TicketID} AND RefType = 17";
tProxy = taskAssociation.ExecuteQuery <TaskAssociationProxy>(query).First() as TProxy;
}
break;
case "TagLinkProxy[]":
{
TicketModel ticket = (TicketModel)node;
//string query = $"SELECT * FROM TagLinks WHERE RefType = 17 AND RefID = {ticket.TicketID}";
//tProxy = ticket.ExecuteQuery<TProxy>(query).ToArray() as TProxy;
Table <TagLinkProxy> table = ticket.Connection._db.GetTable <TagLinkProxy>();
tProxy = table.Where(t => (t.RefType == ReferenceType.Tickets) && (t.RefID == ticket.TicketID)).ToArray() as TProxy;
}
break;
case "TicketProxy": // ticket
{
TicketModel model = (TicketModel)node;
DataContext db = model.Connection._db;
Table <TicketProxy> table = db.GetTable <TicketProxy>();
var query = from row in table where row.TicketID == model.TicketID select row;
tProxy = query.First() as TProxy;
}
break;
case "TicketRelationshipProxy[]":
{
TicketModel model = (TicketModel)node;
//string query = $"SELECT * FROM TicketRelationships WHERE Ticket1ID={model.TicketID} OR Ticket2ID={model.TicketID}";
string query = $"SELECT * FROM TicketRelationships WHERE Ticket1ID={model.TicketID} OR Ticket2ID={model.TicketID}";
tProxy = model.ExecuteQuery <TicketRelationshipProxy>(query).ToArray() as TProxy;
}
break;
case "UserProxy":
{
UserModel user = (UserModel)node;
string query = $"SELECT * FROM Users WHERE UserID={user.UserID}";
tProxy = node.ExecuteQuery <UserProxy>(query).First() as TProxy;
}
break;
default:
if (Debugger.IsAttached)
{
Debugger.Break();
}
break;
}
return(tProxy);
}
