/// <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); }