public override bool GetOperatorStatus() { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { return db.LiveChat_Operators.Count(x => x.IsOnline) > 0; } }
public override List <WebRequest> GetRequest(DateTime lastRequestDate) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var last = from a in db.LiveChat_LogAccesses where a.RequestedTime > lastRequestDate group a by a.VisitorIP into g select new { Id = (from log in g select log.LogAccessID).Max(), Ip = g.Key }; var requests = from a in db.LiveChat_LogAccesses join l in last on a.LogAccessID equals l.Id select a; List <WebRequest> results = new List <WebRequest>(); foreach (var r in requests.ToList()) { results.Add(new WebRequest() { DomainName = r.DomainRequested, PageRequested = r.PageRequested, Referrer = r.Referrer, Requested = r.RequestedTime, RequestId = r.LogAccessID, VisitorIp = r.VisitorIP, VisitorUserAgent = r.VisitorUserAgent }); } return(results); } }
public override SendTranscriptViewModel GetTranscript(Guid chatId) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var request = db.LiveChat_ChatRequests.SingleOrDefault(x => x.ChatID == chatId); if (request != null) { SendTranscriptViewModel vm = new SendTranscriptViewModel(); vm.Email = request.VisitorEmail; vm.Requested = request.RequestDate; vm.Accepted = request.AcceptDate ?? DateTime.MinValue; vm.Conversation = new List <ChatMessage>(); foreach (var m in db.LiveChat_ChatMessages.OrderBy(x => x.MessageID) .Where(x => x.ChatID == chatId).ToList()) { ChatMessage msg = new ChatMessage(); msg.ChatId = m.ChatID; msg.Message = m.Message; msg.MessageId = m.MessageID; msg.Name = m.FromName; vm.Conversation.Add(msg); } return(vm); } return(null); } }
public override bool GetOperatorStatus() { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { return(db.LiveChat_Operators.Count(x => x.IsOnline) > 0); } }
public override List <WebRequest> VisitorPages(string visitorIp) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var pages = db.LiveChat_LogAccesses.OrderBy(x => x.LogAccessID).Where(x => x.RequestedTime >= DateTime.Now.AddDays(-1)).ToList(); List <WebRequest> results = new List <WebRequest>(); foreach (var p in pages) { results.Add(new WebRequest() { DomainName = p.DomainRequested, PageRequested = p.PageRequested, Referrer = p.Referrer, Requested = p.RequestedTime, RequestId = p.LogAccessID, VisitorIp = p.VisitorIP, VisitorUserAgent = p.VisitorUserAgent }); } return(results); } }
public override List<WebRequest> GetRequest(DateTime lastRequestDate) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var last = from a in db.LiveChat_LogAccesses where a.RequestedTime > lastRequestDate group a by a.VisitorIP into g select new { Id = (from log in g select log.LogAccessID).Max(), Ip = g.Key }; var requests = from a in db.LiveChat_LogAccesses join l in last on a.LogAccessID equals l.Id select a; List<WebRequest> results = new List<WebRequest>(); foreach (var r in requests.ToList()) { results.Add(new WebRequest() { DomainName = r.DomainRequested, PageRequested = r.PageRequested, Referrer = r.Referrer, Requested = r.RequestedTime, RequestId = r.LogAccessID, VisitorIp = r.VisitorIP, VisitorUserAgent = r.VisitorUserAgent }); } return results; } }
public override List <ChatRequest> GetPendingInvitations() { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var operators = db.LiveChat_Operators.ToList(); var invites = db.LiveChat_ChatRequests.Where(x => x.AcceptDate != null && x.Department == "op-invite").OrderBy(x => x.RequestDate); List <ChatRequest> pendingInvites = new List <ChatRequest>(); foreach (var r in invites) { pendingInvites.Add(new ChatRequest() { Accepted = r.AcceptDate, ChatId = r.ChatID, Closed = r.ClosedDate, Department = r.Department, OperatorId = r.OperatorID, OperatorName = r.OperatorID > -1 ? operators.Single(x => x.OperatorID == r.OperatorID).OperatorName : "n/a", Requested = r.RequestDate, VisitorEmail = r.VisitorEmail, VisitorIp = r.VisitorIP, VisitorName = r.VisitorName, VisitorUserAgent = r.VisitorUserAgent, WasAccepted = r.AcceptDate != null }); } return(pendingInvites); } }
public override bool HasNewMessage(Guid chatId, long lastMessageId) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { return(db.LiveChat_ChatMessages.Count(x => x.ChatID == chatId && x.MessageID > lastMessageId) > 0); } }
public override bool Delete(Operator op) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { db.LiveChat_Operators.DeleteAllOnSubmit(db.LiveChat_Operators.Where(x => x.OperatorID == op.OperatorId)); db.SubmitChanges(); return(true); } }
public override void RemoveChatRequest(ChatRequest req) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { db.LiveChat_ChatRequests.DeleteAllOnSubmit(db.LiveChat_ChatRequests.Where(x => x.ChatID == req.ChatId)); db.SubmitChanges(); } }
public override void UpdateStatus(int operatorId, bool isOnline) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var op = db.LiveChat_Operators.SingleOrDefault(x => x.OperatorID == operatorId); if (op != null) { op.IsOnline = isOnline; db.SubmitChanges(); } } }
public override void UpdateStatus(int operatorId, bool isOnline) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var op = db.LiveChat_Operators.SingleOrDefault(x => x.OperatorID == operatorId); if (op != null) { op.IsOnline = isOnline; db.SubmitChanges(); } } }
public override Guid RequestChat(ChatRequest request) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { if (request.Department == null) { request.Department = ""; } var existing = db.LiveChat_ChatRequests.SingleOrDefault(x => x.VisitorIP == request.VisitorIp && x.OperatorID == -1); if (existing != null) { return(existing.ChatID); } // Get the less busy operator int operatorId = -1; var results = from o in db.LiveChat_Operators join c in db.LiveChat_ChatRequests on o.OperatorID equals c.OperatorID into req from r in req.DefaultIfEmpty() where o.IsOnline && o.Department.Contains(request.Department) group o by o.OperatorID into g select new { Id = g.Key, Count = g.Count() }; if (results != null && results.Count() > 0) { operatorId = results.OrderBy(x => x.Count).First().Id; } else { // WTH operatorId = db.LiveChat_Operators.First(x => x.IsOnline).OperatorID; } LiveChat_ChatRequest entity = new LiveChat_ChatRequest(); entity.ChatID = request.ChatId; entity.Department = request.Department; entity.OperatorID = operatorId; entity.RequestDate = DateTime.Now; entity.VisitorEmail = request.VisitorEmail; entity.VisitorIP = request.VisitorIp; entity.VisitorName = request.VisitorName; entity.VisitorUserAgent = request.VisitorUserAgent.Length > 100 ? request.VisitorUserAgent.Substring(0, 100) : request.VisitorUserAgent; db.LiveChat_ChatRequests.InsertOnSubmit(entity); db.SubmitChanges(); return(entity.ChatID); } }
public override void AddChatMessage(ChatMessage msg) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { LiveChat_ChatMessage entity = new LiveChat_ChatMessage(); entity.ChatID = msg.ChatId; entity.FromName = msg.Name; entity.Message = msg.Message; //TODO: Change SentDate to DateTime //entity.SentDate = DateTime.Now; db.LiveChat_ChatMessages.InsertOnSubmit(entity); db.SubmitChanges(); } }
public override List <Operator> List() { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var ops = db.LiveChat_Operators.ToList(); List <Operator> results = new List <Operator>(); foreach (var op in ops) { results.Add(new Operator(op.OperatorID, op.OperatorName, op.OperatorPassword, op.OperatorEmail, op.IsOnline, op.Department)); } return(results); } }
public override bool RejectInvite(ChatRequest req) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var entity = db.LiveChat_ChatRequests.SingleOrDefault(x => x.ChatID == req.ChatId); if (entity != null) { entity.ClosedDate = DateTime.Now; entity.Department = "visitor-rejected"; db.SubmitChanges(); return(true); } return(false); } }
public override bool LogRequest(WebRequest req) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { LiveChat_LogAccess webrequest = new LiveChat_LogAccess(); webrequest.DomainRequested = req.DomainName; webrequest.PageRequested = req.PageRequested; webrequest.Referrer = req.Referrer; webrequest.RequestedTime = DateTime.Now; webrequest.VisitorIP = req.VisitorIp; webrequest.VisitorUserAgent = req.VisitorUserAgent.Length > 100 ? req.VisitorUserAgent.Substring(0, 100) : req.VisitorUserAgent; db.LiveChat_LogAccesses.InsertOnSubmit(webrequest); db.SubmitChanges(); return(true); } }
public override bool LogRequest(WebRequest req) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { LiveChat_LogAccess webrequest = new LiveChat_LogAccess(); webrequest.DomainRequested = req.DomainName; webrequest.PageRequested = req.PageRequested; webrequest.Referrer = req.Referrer; webrequest.RequestedTime = DateTime.Now; webrequest.VisitorIP = req.VisitorIp; webrequest.VisitorUserAgent = req.VisitorUserAgent.Length > 100 ? req.VisitorUserAgent.Substring(0, 100) : req.VisitorUserAgent; db.LiveChat_LogAccesses.InsertOnSubmit(webrequest); db.SubmitChanges(); return true; } }
public override Guid RequestChat(ChatRequest request) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { if (request.Department == null) request.Department = ""; var existing = db.LiveChat_ChatRequests.SingleOrDefault(x => x.VisitorIP == request.VisitorIp && x.OperatorID == -1); if (existing != null) return existing.ChatID; // Get the less busy operator int operatorId = -1; var results = from o in db.LiveChat_Operators join c in db.LiveChat_ChatRequests on o.OperatorID equals c.OperatorID into req from r in req.DefaultIfEmpty() where o.IsOnline && o.Department.Contains(request.Department) group o by o.OperatorID into g select new { Id = g.Key, Count = g.Count() }; if (results != null && results.Count() > 0) operatorId = results.OrderBy(x => x.Count).First().Id; else { // WTH operatorId = db.LiveChat_Operators.First(x => x.IsOnline).OperatorID; } LiveChat_ChatRequest entity = new LiveChat_ChatRequest(); entity.ChatID = request.ChatId; entity.Department = request.Department; entity.OperatorID = operatorId; entity.RequestDate = DateTime.Now; entity.VisitorEmail = request.VisitorEmail; entity.VisitorIP = request.VisitorIp; entity.VisitorName = request.VisitorName; entity.VisitorUserAgent = request.VisitorUserAgent.Length > 100 ? request.VisitorUserAgent.Substring(0, 100) : request.VisitorUserAgent; db.LiveChat_ChatRequests.InsertOnSubmit(entity); db.SubmitChanges(); return entity.ChatID; } }
public override bool AcceptRequest(Guid id, int operatorId) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var req = db.LiveChat_ChatRequests.SingleOrDefault(x => x.ChatID == id); if (req != null) { req.AcceptDate = DateTime.Now; db.SubmitChanges(); return(true); } return(false); } }
public override ChatRequest InviteVisitor(int operatorId, string visitorIp, string prompt) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { LiveChat_ChatRequest invite = new LiveChat_ChatRequest(); invite.AcceptDate = null; invite.ChatID = Guid.NewGuid(); invite.ClosedDate = null; //HACK: this will serve as an indicator when visitor display chat button invite.Department = "op-invite"; invite.OperatorID = operatorId; invite.RequestDate = DateTime.Now; invite.VisitorEmail = ""; invite.VisitorIP = visitorIp; invite.VisitorName = "Me"; invite.VisitorUserAgent = ""; db.LiveChat_ChatRequests.InsertOnSubmit(invite); try { db.SubmitChanges(); ChatRequest req = new ChatRequest(); req.Accepted = null; req.ChatId = invite.ChatID; req.Closed = null; req.Department = invite.Department; req.OperatorId = invite.OperatorID; req.Requested = invite.RequestDate; req.VisitorEmail = invite.VisitorEmail; req.VisitorIp = invite.VisitorIP; req.VisitorName = invite.VisitorName; req.VisitorUserAgent = invite.VisitorUserAgent; req.WasAccepted = false; return(req); } catch { throw; } } }
public override ChatRequest CheckForInvites(string visitorIp) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var invites = db.LiveChat_ChatRequests.OrderByDescending(x => x.RequestDate).Where(x => x.VisitorIP == visitorIp && x.Department == "op-invite" && x.AcceptDate == null && x.ClosedDate == null).ToList(); if (invites != null && invites.Count() > 0) { // do they have more than 1 invite var invite = invites.First(); if (invites.Count() > 1) { invites.Remove(invite); foreach (var inv in invites) { inv.ClosedDate = DateTime.Now; inv.Department = "Visitor had more than 1 invite pending..."; } db.SubmitChanges(); } ChatRequest req = new ChatRequest(); req.Accepted = null; req.ChatId = invite.ChatID; req.Closed = null; req.Department = invite.Department; req.OperatorId = invite.OperatorID; req.Requested = invite.RequestDate; req.VisitorEmail = invite.VisitorEmail; req.VisitorIp = invite.VisitorIP; req.VisitorName = invite.VisitorName; req.VisitorUserAgent = invite.VisitorUserAgent; req.WasAccepted = false; return(req); } return(null); } }
public override Operator LogIn(string userName, string password) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var entity = db.LiveChat_Operators.SingleOrDefault(x => x.OperatorName == userName && x.OperatorPassword == password); if (entity == null) return null; Operator op = new Operator(); op.Department = entity.Department; op.Email = entity.OperatorEmail; op.IsOnline = entity.IsOnline; op.OperatorId = entity.OperatorID; op.OperatorName = entity.OperatorName; op.Password = entity.OperatorPassword; return op; } }
public override bool Save(Operator op) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var entity = db.LiveChat_Operators.SingleOrDefault(x => x.OperatorID == op.OperatorId); if (entity == null) { entity = new LiveChat_Operator(); db.LiveChat_Operators.InsertOnSubmit(entity); } entity.Department = op.Department; entity.OperatorEmail = op.Email; entity.OperatorName = op.OperatorName; entity.OperatorPassword = op.Password.Length == 0 ? entity.OperatorPassword : op.Password; db.SubmitChanges(); return(true); } }
public override Operator LogIn(string userName, string password) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var entity = db.LiveChat_Operators.SingleOrDefault(x => x.OperatorName == userName && x.OperatorPassword == password); if (entity == null) { return(null); } Operator op = new Operator(); op.Department = entity.Department; op.Email = entity.OperatorEmail; op.IsOnline = entity.IsOnline; op.OperatorId = entity.OperatorID; op.OperatorName = entity.OperatorName; op.Password = entity.OperatorPassword; return(op); } }
public override List <ChatMessage> GetMessages(Guid chatId, long lastCheck) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var messages = db.LiveChat_ChatMessages.OrderBy(x => x.MessageID).Where(x => x.ChatID == chatId && x.MessageID > lastCheck).ToList(); List <ChatMessage> results = new List <ChatMessage>(); foreach (var m in messages) { results.Add(new ChatMessage() { ChatId = m.ChatID, Message = m.Message, MessageId = m.MessageID, Name = m.FromName, Sent = DateTime.Now }); } return(results); } }
public override List<ChatMessage> GetMessages(Guid chatId, long lastCheck) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var messages = db.LiveChat_ChatMessages.OrderBy(x => x.MessageID).Where(x => x.ChatID == chatId && x.MessageID > lastCheck).ToList(); List<ChatMessage> results = new List<ChatMessage>(); foreach (var m in messages) { results.Add(new ChatMessage() { ChatId = m.ChatID, Message = m.Message, MessageId = m.MessageID, Name = m.FromName, Sent = DateTime.Now }); } return results; } }
public override bool CreateDatabase(string password) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { //TODO: Refactor this db.ExecuteCommand(@" CREATE TABLE [dbo].[LiveChat_ChatMessages]( [MessageID] [bigint] IDENTITY(1,1) NOT NULL, [ChatID] [uniqueidentifier] NOT NULL, [FromName] [varchar](100) NOT NULL CONSTRAINT [DF_ASPLiveSupport_ChatMessages_FromName] DEFAULT (''), [Message] [varchar](3000) NOT NULL CONSTRAINT [DF_ASPLiveSupport_ChatMessages_Message] DEFAULT (''), [SentDate] [bigint] NOT NULL, CONSTRAINT [PK_ASPLiveSupport_ChatMessages] PRIMARY KEY CLUSTERED ( [MessageID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[LiveChat_ChatRequests]( [ChatID] [uniqueidentifier] NOT NULL, [VisitorIP] [varchar](50) NOT NULL CONSTRAINT [DF_ASPLiveSupport_ChatRequests_VisitorIP] DEFAULT (''), [VisitorName] [varchar](100) NOT NULL CONSTRAINT [DF_LiveChat_ChatRequests_VisitorName] DEFAULT (''), [VisitorEmail] [varchar](225) NOT NULL CONSTRAINT [DF_LiveChat_ChatRequests_VisitorEmail] DEFAULT (''), [VisitorUserAgent] [varchar](125) NOT NULL CONSTRAINT [DF_LiveChat_ChatRequests_VisitorUserAgent] DEFAULT (''), [OperatorID] [int] NOT NULL CONSTRAINT [DF_LiveChat_ChatRequests_OperatorID] DEFAULT ((-1)), [Department] varchar(100) NULL, [RequestDate] [smalldatetime] NOT NULL CONSTRAINT [DF_ASPLiveSupport_ChatRequests_RequestDate] DEFAULT (getdate()), [AcceptDate] [smalldatetime] NULL, [ClosedDate] [smalldatetime] NULL, CONSTRAINT [PK_ASPLiveSupport_ChatRequests] PRIMARY KEY CLUSTERED ( [ChatID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[LiveChat_LogAccess]( [LogAccessID] [int] IDENTITY(1,1) NOT NULL, [PageRequested] [varchar](500) NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_PageRequested] DEFAULT (''), [DomainRequested] [varchar](250) NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_DomainRequested] DEFAULT (''), [RequestedTime] [smalldatetime] NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_RequestedTime] DEFAULT (getdate()), [Referrer] [varchar](500) NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_Referrer] DEFAULT (''), [VisitorUserAgent] [varchar](100) NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_VisitorUserAgent] DEFAULT (''), [VisitorIP] [varchar](50) NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_VisitorIP] DEFAULT (''), CONSTRAINT [PK_ASPLiveSupport_LogAccess] PRIMARY KEY CLUSTERED ( [LogAccessID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[LiveChat_Operators]( [OperatorID] [int] IDENTITY(1,1) NOT NULL, [OperatorName] [varchar](100) NOT NULL CONSTRAINT [DF_ASPLiveSupport_Operators_OperatorName] DEFAULT (''), [OperatorPassword] [varchar](50) NOT NULL CONSTRAINT [DF_ASPLiveSupport_Operators_OperatorPassword] DEFAULT ('nopw'), [OperatorEmail] [varchar](250) NOT NULL CONSTRAINT [DF_ASPLiveSupport_Operators_OperatorEmail] DEFAULT (''), [IsOnline] [bit] NOT NULL CONSTRAINT [DF_ASPLiveSupport_Operators_OperatorStatus] DEFAULT ((0)), Department varchar(500) NOT NULL CONSTRAINT [DF_LiveChat_Operators_Department] DEFAULT ('support'), CONSTRAINT [PK_ASPLiveSupport_Operators] PRIMARY KEY CLUSTERED ( [OperatorID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] "); LiveChat_Operator admin = new LiveChat_Operator(); admin.Department = ""; admin.IsOnline = false; admin.OperatorEmail = "*****@*****.**"; admin.OperatorName = "admin"; admin.OperatorPassword = password; db.LiveChat_Operators.InsertOnSubmit(admin); db.SubmitChanges(); return(true); } }
public override List<Operator> GetOnlineOperator() { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var ops = db.LiveChat_Operators.Where(x => x.IsOnline).ToList(); List<Operator> results = new List<Operator>(); foreach (var op in ops) { results.Add(new Operator(op.OperatorID, op.OperatorName, op.OperatorPassword, op.OperatorEmail, op.IsOnline, op.Department)); } return results; } }
public override List<WebRequest> VisitorPages(string visitorIp) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var pages = db.LiveChat_LogAccesses.OrderBy(x => x.LogAccessID).Where(x => x.RequestedTime >= DateTime.Now.AddDays(-1)).ToList(); List<WebRequest> results = new List<WebRequest>(); foreach (var p in pages) { results.Add(new WebRequest() { DomainName = p.DomainRequested, PageRequested = p.PageRequested, Referrer = p.Referrer, Requested = p.RequestedTime, RequestId = p.LogAccessID, VisitorIp = p.VisitorIP, VisitorUserAgent = p.VisitorUserAgent }); } return results; } }
public override void RemoveChatRequest(ChatRequest req) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { db.LiveChat_ChatRequests.DeleteAllOnSubmit(db.LiveChat_ChatRequests.Where(x => x.ChatID == req.ChatId)); db.SubmitChanges(); } }
public override SendTranscriptViewModel GetTranscript(Guid chatId) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var request = db.LiveChat_ChatRequests.SingleOrDefault(x => x.ChatID == chatId); if (request != null) { SendTranscriptViewModel vm = new SendTranscriptViewModel(); vm.Email = request.VisitorEmail; vm.Requested = request.RequestDate; vm.Accepted = request.AcceptDate ?? DateTime.MinValue; vm.Conversation = new List<ChatMessage>(); foreach(var m in db.LiveChat_ChatMessages.OrderBy(x => x.MessageID) .Where(x => x.ChatID == chatId).ToList()) { ChatMessage msg = new ChatMessage(); msg.ChatId = m.ChatID; msg.Message = m.Message; msg.MessageId = m.MessageID; msg.Name = m.FromName; vm.Conversation.Add(msg); } return vm; } return null; } }
public override bool RejectInvite(ChatRequest req) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var entity = db.LiveChat_ChatRequests.SingleOrDefault(x => x.ChatID == req.ChatId); if (entity != null) { entity.ClosedDate = DateTime.Now; entity.Department = "visitor-rejected"; db.SubmitChanges(); return true; } return false; } }
public override List <ChatRequest> GetChatRequests(int operatorId, string[] departments) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var pending = db.LiveChat_ChatRequests.Where(x => x.AcceptDate == null && (x.OperatorID == -1 || x.OperatorID == operatorId)).ToList(); //TODO: Elegantly manage this in another way bool found = false; List <LiveChat_ChatRequest> toRemove = new List <LiveChat_ChatRequest>(); foreach (var req in pending) { found = false; foreach (string dep in departments) { found = dep.ToLower() == req.Department.ToLower(); if (found) { break; } } if (!found) { // if the request has been idle (not answer for more than 3 minutes if (req.RequestDate > DateTime.Now.AddMinutes(-3)) { //TODO: Swtich operator toRemove.Add(req); } } } foreach (var r in toRemove) { pending.Remove(r); } List <ChatRequest> results = new List <ChatRequest>(); foreach (var req in pending) { results.Add(new ChatRequest() { Accepted = req.AcceptDate, ChatId = req.ChatID, Closed = req.ClosedDate, Department = req.Department, OperatorId = req.OperatorID, Requested = req.RequestDate, VisitorEmail = req.VisitorEmail, VisitorIp = req.VisitorIP, VisitorName = req.VisitorName, VisitorUserAgent = req.VisitorUserAgent, WasAccepted = req.AcceptDate.HasValue }); } return(results); } }
public override void AddChatMessage(ChatMessage msg) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { LiveChat_ChatMessage entity = new LiveChat_ChatMessage(); entity.ChatID = msg.ChatId; entity.FromName = msg.Name; entity.Message = msg.Message; //TODO: Change SentDate to DateTime //entity.SentDate = DateTime.Now; db.LiveChat_ChatMessages.InsertOnSubmit(entity); db.SubmitChanges(); } }
public override bool CreateDatabase(string password) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { //TODO: Refactor this db.ExecuteCommand(@" CREATE TABLE [dbo].[LiveChat_ChatMessages]( [MessageID] [bigint] IDENTITY(1,1) NOT NULL, [ChatID] [uniqueidentifier] NOT NULL, [FromName] [varchar](100) NOT NULL CONSTRAINT [DF_ASPLiveSupport_ChatMessages_FromName] DEFAULT (''), [Message] [varchar](3000) NOT NULL CONSTRAINT [DF_ASPLiveSupport_ChatMessages_Message] DEFAULT (''), [SentDate] [bigint] NOT NULL, CONSTRAINT [PK_ASPLiveSupport_ChatMessages] PRIMARY KEY CLUSTERED ( [MessageID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[LiveChat_ChatRequests]( [ChatID] [uniqueidentifier] NOT NULL, [VisitorIP] [varchar](50) NOT NULL CONSTRAINT [DF_ASPLiveSupport_ChatRequests_VisitorIP] DEFAULT (''), [VisitorName] [varchar](100) NOT NULL CONSTRAINT [DF_LiveChat_ChatRequests_VisitorName] DEFAULT (''), [VisitorEmail] [varchar](225) NOT NULL CONSTRAINT [DF_LiveChat_ChatRequests_VisitorEmail] DEFAULT (''), [VisitorUserAgent] [varchar](125) NOT NULL CONSTRAINT [DF_LiveChat_ChatRequests_VisitorUserAgent] DEFAULT (''), [OperatorID] [int] NOT NULL CONSTRAINT [DF_LiveChat_ChatRequests_OperatorID] DEFAULT ((-1)), [Department] varchar(100) NULL, [RequestDate] [smalldatetime] NOT NULL CONSTRAINT [DF_ASPLiveSupport_ChatRequests_RequestDate] DEFAULT (getdate()), [AcceptDate] [smalldatetime] NULL, [ClosedDate] [smalldatetime] NULL, CONSTRAINT [PK_ASPLiveSupport_ChatRequests] PRIMARY KEY CLUSTERED ( [ChatID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[LiveChat_LogAccess]( [LogAccessID] [int] IDENTITY(1,1) NOT NULL, [PageRequested] [varchar](500) NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_PageRequested] DEFAULT (''), [DomainRequested] [varchar](250) NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_DomainRequested] DEFAULT (''), [RequestedTime] [smalldatetime] NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_RequestedTime] DEFAULT (getdate()), [Referrer] [varchar](500) NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_Referrer] DEFAULT (''), [VisitorUserAgent] [varchar](100) NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_VisitorUserAgent] DEFAULT (''), [VisitorIP] [varchar](50) NOT NULL CONSTRAINT [DF_ASPLiveSupport_LogAccess_VisitorIP] DEFAULT (''), CONSTRAINT [PK_ASPLiveSupport_LogAccess] PRIMARY KEY CLUSTERED ( [LogAccessID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[LiveChat_Operators]( [OperatorID] [int] IDENTITY(1,1) NOT NULL, [OperatorName] [varchar](100) NOT NULL CONSTRAINT [DF_ASPLiveSupport_Operators_OperatorName] DEFAULT (''), [OperatorPassword] [varchar](50) NOT NULL CONSTRAINT [DF_ASPLiveSupport_Operators_OperatorPassword] DEFAULT ('nopw'), [OperatorEmail] [varchar](250) NOT NULL CONSTRAINT [DF_ASPLiveSupport_Operators_OperatorEmail] DEFAULT (''), [IsOnline] [bit] NOT NULL CONSTRAINT [DF_ASPLiveSupport_Operators_OperatorStatus] DEFAULT ((0)), Department varchar(500) NOT NULL CONSTRAINT [DF_LiveChat_Operators_Department] DEFAULT ('support'), CONSTRAINT [PK_ASPLiveSupport_Operators] PRIMARY KEY CLUSTERED ( [OperatorID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] "); LiveChat_Operator admin = new LiveChat_Operator(); admin.Department = ""; admin.IsOnline = false; admin.OperatorEmail = "*****@*****.**"; admin.OperatorName = "admin"; admin.OperatorPassword = password; db.LiveChat_Operators.InsertOnSubmit(admin); db.SubmitChanges(); return true; } }
public override List<ChatRequest> GetChatRequests(int operatorId, string[] departments) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var pending = db.LiveChat_ChatRequests.Where(x => x.AcceptDate == null && (x.OperatorID == -1 || x.OperatorID == operatorId)).ToList(); //TODO: Elegantly manage this in another way bool found = false; List<LiveChat_ChatRequest> toRemove = new List<LiveChat_ChatRequest>(); foreach (var req in pending) { found = false; foreach (string dep in departments) { found = dep.ToLower() == req.Department.ToLower(); if (found) break; } if (!found) { // if the request has been idle (not answer for more than 3 minutes if (req.RequestDate > DateTime.Now.AddMinutes(-3)) { //TODO: Swtich operator toRemove.Add(req); } } } foreach (var r in toRemove) pending.Remove(r); List<ChatRequest> results = new List<ChatRequest>(); foreach (var req in pending) { results.Add(new ChatRequest() { Accepted = req.AcceptDate, ChatId = req.ChatID, Closed = req.ClosedDate, Department = req.Department, OperatorId = req.OperatorID, Requested = req.RequestDate, VisitorEmail = req.VisitorEmail, VisitorIp = req.VisitorIP, VisitorName = req.VisitorName, VisitorUserAgent = req.VisitorUserAgent, WasAccepted = req.AcceptDate.HasValue }); } return results; } }
public override bool Save(Operator op) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var entity = db.LiveChat_Operators.SingleOrDefault(x => x.OperatorID == op.OperatorId); if (entity == null) { entity = new LiveChat_Operator(); db.LiveChat_Operators.InsertOnSubmit(entity); } entity.Department = op.Department; entity.OperatorEmail = op.Email; entity.OperatorName = op.OperatorName; entity.OperatorPassword = op.Password.Length == 0 ? entity.OperatorPassword : op.Password; db.SubmitChanges(); return true; } }
public override bool HasNewMessage(Guid chatId, long lastMessageId) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { return db.LiveChat_ChatMessages.Count(x => x.ChatID == chatId && x.MessageID > lastMessageId) > 0; } }
public override bool Delete(Operator op) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { db.LiveChat_Operators.DeleteAllOnSubmit(db.LiveChat_Operators.Where(x => x.OperatorID == op.OperatorId)); db.SubmitChanges(); return true; } }
public override List<ChatRequest> GetPendingInvitations() { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var operators = db.LiveChat_Operators.ToList(); var invites = db.LiveChat_ChatRequests.Where(x => x.AcceptDate != null && x.Department == "op-invite").OrderBy(x => x.RequestDate); List<ChatRequest> pendingInvites = new List<ChatRequest>(); foreach (var r in invites) { pendingInvites.Add(new ChatRequest() { Accepted = r.AcceptDate, ChatId = r.ChatID, Closed = r.ClosedDate, Department = r.Department, OperatorId = r.OperatorID, OperatorName = r.OperatorID > -1 ? operators.Single(x => x.OperatorID == r.OperatorID).OperatorName : "n/a", Requested = r.RequestDate, VisitorEmail = r.VisitorEmail, VisitorIp = r.VisitorIP, VisitorName = r.VisitorName, VisitorUserAgent = r.VisitorUserAgent, WasAccepted = r.AcceptDate != null }); } return pendingInvites; } }
public override ChatRequest InviteVisitor(int operatorId, string visitorIp, string prompt) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { LiveChat_ChatRequest invite = new LiveChat_ChatRequest(); invite.AcceptDate = null; invite.ChatID = Guid.NewGuid(); invite.ClosedDate = null; //HACK: this will serve as an indicator when visitor display chat button invite.Department = "op-invite"; invite.OperatorID = operatorId; invite.RequestDate = DateTime.Now; invite.VisitorEmail = ""; invite.VisitorIP = visitorIp; invite.VisitorName = "Me"; invite.VisitorUserAgent = ""; db.LiveChat_ChatRequests.InsertOnSubmit(invite); try { db.SubmitChanges(); ChatRequest req = new ChatRequest(); req.Accepted = null; req.ChatId = invite.ChatID; req.Closed = null; req.Department = invite.Department; req.OperatorId = invite.OperatorID; req.Requested = invite.RequestDate; req.VisitorEmail = invite.VisitorEmail; req.VisitorIp = invite.VisitorIP; req.VisitorName = invite.VisitorName; req.VisitorUserAgent = invite.VisitorUserAgent; req.WasAccepted = false; return req; } catch { throw; } } }
public override bool AcceptRequest(Guid id, int operatorId) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var req = db.LiveChat_ChatRequests.SingleOrDefault(x => x.ChatID == id); if (req != null) { req.AcceptDate = DateTime.Now; db.SubmitChanges(); return true; } return false; } }
public override ChatRequest CheckForInvites(string visitorIp) { using (LCSKDbDataContext db = new LCSKDbDataContext(connectionString)) { var invites = db.LiveChat_ChatRequests.OrderByDescending(x => x.RequestDate).Where(x => x.VisitorIP == visitorIp && x.Department == "op-invite" && x.AcceptDate == null && x.ClosedDate == null).ToList(); if(invites != null && invites.Count() > 0) { // do they have more than 1 invite var invite = invites.First(); if (invites.Count() > 1) { invites.Remove(invite); foreach (var inv in invites) { inv.ClosedDate = DateTime.Now; inv.Department = "Visitor had more than 1 invite pending..."; } db.SubmitChanges(); } ChatRequest req = new ChatRequest(); req.Accepted = null; req.ChatId = invite.ChatID; req.Closed = null; req.Department = invite.Department; req.OperatorId = invite.OperatorID; req.Requested = invite.RequestDate; req.VisitorEmail = invite.VisitorEmail; req.VisitorIp = invite.VisitorIP; req.VisitorName = invite.VisitorName; req.VisitorUserAgent = invite.VisitorUserAgent; req.WasAccepted = false; return req; } return null; } }