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