public List <CaseArrival> GetNotifications(DateTime afterDate, long uID)
{
List <CaseArrival> news = new List <CaseArrival>();
using (BizSparkSupportEntities dc = new BizSparkSupportEntities())
{
var row = dc.Employees.Where(a => a.EmployeeID == uID).FirstOrDefault();
int catid = (int)row.CategoryID;
var result = dc.Cases.Where(a => a.CategoryID == catid && a.SubmissionDate > afterDate).OrderByDescending(a => a.SubmissionDate).ToList();
foreach (Case c in result)
{
CaseArrival ar = new CaseArrival();
ar.CaseID = c.CaseID;
ar.Subject = c.Subject;
ar.StartUpName = dc.Startups.Where(a => a.CompanyID == c.StartupID).FirstOrDefault().CompanyName;
ar.PriorityColor = dc.Priorities.Where(a => a.PriorityID == c.PriorityID).FirstOrDefault().PriorityColor;
ar.listText = "New Case Added From ( " + ar.StartUpName + " )";
news.Add(ar);
}
var result1 = dc.Employee_Case.Where(a => a.EmployeeID == uID && a.AssignDate > afterDate).OrderByDescending(a => a.AssignDate).ToList();
foreach (Employee_Case c in result1)
{
CaseArrival ar = new CaseArrival();
ar.CaseID = c.CaseID;
var cases = dc.Cases.Where(a => a.CaseID == c.CaseID).FirstOrDefault();
ar.Subject = cases.Subject;
ar.StartUpName = dc.Startups.Where(a => a.CompanyID == cases.StartupID).FirstOrDefault().CompanyName;
ar.PriorityColor = dc.Priorities.Where(a => a.PriorityID == cases.PriorityID).FirstOrDefault().PriorityColor;
ar.listText = "You Are Assigned To New Case From ( " + ar.StartUpName + " )";
news.Add(ar);
}
var result2 = dc.Messages.Where(a => a.ReceiverID == uID && a.SubmissionDate > afterDate).OrderByDescending(a => a.SubmissionDate).ToList();
foreach (Message c in result2)
{
CaseArrival ar = new CaseArrival();
ar.CaseID = c.CaseID;
var cases = dc.Cases.Where(a => a.CaseID == c.CaseID).FirstOrDefault();
ar.Subject = cases.Subject;
ar.caseMsg = c.MessageContent;
ar.StartUpName = dc.Startups.Where(a => a.CompanyID == cases.StartupID).FirstOrDefault().CompanyName;
ar.PriorityColor = dc.Priorities.Where(a => a.PriorityID == cases.PriorityID).FirstOrDefault().PriorityColor;
ar.listText = "New Message From ( " + ar.StartUpName + " ) About ( " + ar.Subject + " ) : <br>" + ar.caseMsg;
news.Add(ar);
}
}
return(news);
}
public void reregistermsgs(DateTime currentTime, long uID)
{
NotificationComponent.SharedInstance.clientID = uID;
using (BizSparkSupportEntities dc = new BizSparkSupportEntities())
{
var row = dc.Employees.Where(a => a.EmployeeID == uID).FirstOrDefault();
int catid = (int)row.CategoryID;
NotificationComponent.SharedInstance.CatID = catid;
conStr = ConfigurationManager.ConnectionStrings["sqlConString"].ConnectionString;
SqlDependency.Start(conStr);
string sqlCommand2 = @"SELECT [MessageID],[SenderID],[ReceiverID] from [dbo].[Messages] where [SubmissionDate] > @AddedOn AND [ReceiverID] = @empID";
//you can notice here I have added table name like this [dbo].[Contacts] with [dbo], its mendatory when you use Sql Dependency
using (SqlConnection con = new SqlConnection(conStr))
{
SqlCommand cmd2 = new SqlCommand(sqlCommand2, con);
cmd2.Parameters.AddWithValue("@AddedOn", currentTime);
cmd2.Parameters.AddWithValue("@empID", uID);
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
cmd2.Notification = null;
//////////////////////////////////////////
SqlDependency sqlDep2 = new SqlDependency(cmd2);
sqlDep2.OnChange += sqlDep_OnChange2;
//we must have to execute the command here
using (SqlDataReader reader = cmd2.ExecuteReader())
{
// nothing need to add here now
}
}
}
}
public List <CaseArrival> GetMessages(DateTime afterDate, long uID)
{
List <CaseArrival> news = new List <CaseArrival>();
using (BizSparkSupportEntities dc = new BizSparkSupportEntities())
{
var result2 = dc.Messages.Where(a => a.ReceiverID == uID && a.SubmissionDate > afterDate).OrderByDescending(a => a.SubmissionDate).ToList();
foreach (Message c in result2)
{
CaseArrival ar = new CaseArrival();
ar.CaseID = c.CaseID;
var cases = dc.Cases.Where(a => a.CaseID == c.CaseID).FirstOrDefault();
ar.Subject = cases.Subject;
ar.StartUpName = dc.Startups.Where(a => a.CompanyID == c.ReceiverID).FirstOrDefault().CompanyName;
ar.PriorityColor = dc.Priorities.Where(a => a.PriorityID == cases.PriorityID).FirstOrDefault().PriorityColor;
ar.caseMsg = c.MessageContent;
ar.listText = "New Message From ( Support Team ) : <br> " + ar.caseMsg;
news.Add(ar);
}
}
return(news);
}
public GenericRepository(BizSparkSupportEntities dbContext)
{
this.dbContext = dbContext;
this.dbSet = dbContext.Set <TEntity>();
}