public static bool InitialiseDiary()
{
// init connection to database
I4IDBEntities ent = new I4IDBEntities();
try
{
for (int i = 0; i < 30; i++)
{
AppointmentDiary item = new AppointmentDiary();
// record ID is auto generated
item.Title = "Appt: " + i.ToString();
item.SomeImportantKey = i;
item.StatusENUM = GetRandomValue(0, 3); // random is exclusive - we have three status enums
if (i <= 5) // create a few appointments for todays date
{
item.DateTimeScheduled = GetRandomAppointmentTime(false, true);
}
else
{ // rest of appointments on previous and future dates
if (i % 2 == 0)
item.DateTimeScheduled = GetRandomAppointmentTime(true, false); // flip/flop between date ahead of today and behind today
else item.DateTimeScheduled = GetRandomAppointmentTime(false, false);
}
item.AppointmentLength = GetRandomValue(1, 5) * 15; // appoiment length in blocks of fifteen minutes in this demo
ent.AppointmentDiaries.Add(item);
ent.SaveChanges();
}
}
catch (Exception)
{
return false;
}
return ent.AppointmentDiaries.Count() > 0;
}
public static List <DiaryEvent> LoadAllAppointmentsInDateRange(double start, double end)
{
var fromDate = ConvertFromUnixTimestamp(start);
var toDate = ConvertFromUnixTimestamp(end);
using (I4IDBEntities ent = new I4IDBEntities())
{
var rslt = ent.AppointmentDiaries.Where(s => s.DateTimeScheduled >= fromDate && System.Data.Objects.EntityFunctions.AddMinutes(s.DateTimeScheduled, s.AppointmentLength) <= toDate);
List <DiaryEvent> result = new List <DiaryEvent>();
foreach (var item in rslt)
{
DiaryEvent rec = new DiaryEvent();
rec.AppointmentID = item.AppointmentID;
rec.SomeImportantKeyID = item.SomeImportantKey;
rec.StartDateString = item.DateTimeScheduled.ToString("s"); // "s" is a preset format that outputs as: "2009-02-27T12:12:22"
rec.EndDateString = item.DateTimeScheduled.AddMinutes(item.AppointmentLength).ToString("s"); // field AppointmentLength is in minutes
rec.Title = item.Title + " - " + item.AppointmentLength.ToString() + " mins";
rec.StatusString = Enums.GetName <AppointmentStatus>((AppointmentStatus)item.StatusENUM);
rec.StatusColor = Enums.GetEnumDescription <AppointmentStatus>(rec.StatusString);
string ColorCode = rec.StatusColor.Substring(0, rec.StatusColor.IndexOf(":"));
rec.ClassName = rec.StatusColor.Substring(rec.StatusColor.IndexOf(":") + 1, rec.StatusColor.Length - ColorCode.Length - 1);
rec.StatusColor = ColorCode;
result.Add(rec);
}
return(result);
}
}
public static List<DiaryEvent> LoadAllAppointmentsInDateRange(double start, double end)
{
var fromDate = ConvertFromUnixTimestamp(start);
var toDate = ConvertFromUnixTimestamp(end);
using ( I4IDBEntities ent = new I4IDBEntities())
{
var rslt = ent.AppointmentDiaries.Where(s => s.DateTimeScheduled >= fromDate && System.Data.Objects.EntityFunctions.AddMinutes(s.DateTimeScheduled, s.AppointmentLength) <= toDate);
List<DiaryEvent> result = new List<DiaryEvent>();
foreach (var item in rslt)
{
DiaryEvent rec = new DiaryEvent();
rec.AppointmentID = item.AppointmentID;
rec.SomeImportantKeyID = item.SomeImportantKey;
rec.StartDateString = item.DateTimeScheduled.ToString("s"); // "s" is a preset format that outputs as: "2009-02-27T12:12:22"
rec.EndDateString = item.DateTimeScheduled.AddMinutes(item.AppointmentLength).ToString("s"); // field AppointmentLength is in minutes
rec.Title = item.Title + " - " + item.AppointmentLength.ToString() + " mins";
rec.StatusString = Enums.GetName<AppointmentStatus>((AppointmentStatus)item.StatusENUM);
rec.StatusColor = Enums.GetEnumDescription<AppointmentStatus>(rec.StatusString);
string ColorCode = rec.StatusColor.Substring(0, rec.StatusColor.IndexOf(":"));
rec.ClassName = rec.StatusColor.Substring(rec.StatusColor.IndexOf(":") + 1, rec.StatusColor.Length - ColorCode.Length - 1);
rec.StatusColor = ColorCode;
result.Add(rec);
}
return result;
}
}
public static List <DiaryEvent> LoadAppointmentSummaryInDateRange(double start, double end)
{
var fromDate = ConvertFromUnixTimestamp(start);
var toDate = ConvertFromUnixTimestamp(end);
using (I4IDBEntities ent = new I4IDBEntities())
{
var rslt = ent.AppointmentDiaries.Where(s => s.DateTimeScheduled >= fromDate && System.Data.Objects.EntityFunctions.AddMinutes(s.DateTimeScheduled, s.AppointmentLength) <= toDate)
.GroupBy(s => System.Data.Objects.EntityFunctions.TruncateTime(s.DateTimeScheduled))
.Select(x => new { DateTimeScheduled = x.Key, Count = x.Count() });
List <DiaryEvent> result = new List <DiaryEvent>();
int i = 0;
foreach (var item in rslt)
{
DiaryEvent rec = new DiaryEvent();
rec.AppointmentID = i; //we dont link this back to anything as its a group summary but the fullcalendar needs unique IDs for each event item (unless its a repeating event)
rec.SomeImportantKeyID = -1;
string StringDate = string.Format("{0:dd-MM-yyyy}", item.DateTimeScheduled);
rec.StartDateString = StringDate + "T00:00:00"; //ISO 8601 format
rec.EndDateString = StringDate + "T23:59:59";
rec.Title = "Booked: " + item.Count.ToString();
result.Add(rec);
i++;
}
return(result);
}
}
public List <SupplierDAO> GetSupplier()
{
List <SupplierDAO> list = new List <SupplierDAO>();
try
{
using (I4IDBEntities context = new I4IDBEntities())
{
SupplierDAO obj = new SupplierDAO();
//var data= context.Suppliers ;
var data = context.RegisterUsers.Where(u => u.UserRole == "S");
//var data=from sup in context.Suppliers join forwQry in context.ForwardQueries on sup.;
foreach (RegisterUser item in data)
{
list.Add(new SupplierDAO {
Id = item.Id, Company = item.Company + "-" + item.UserFirstName + " " + item.UserLastName
});
}
}
}
catch
{
}
return(list);
}
/// <summary>
/// It will generate the username
/// </summary>
/// <returns></returns>
public string getUserNo()
{
string UserName = string.Empty;
UserName = GetUniqueKey(7);
using (I4IDBEntities context = new I4IDBEntities())
{
var isexistKey = context.RegisterUsers.Where(m => m.UserNo == UserName).FirstOrDefault();
if (isexistKey != null)
{
getUserNo();
}
}
return(UserName);
}
/// <summary>
/// It will generate the username
/// </summary>
/// <returns></returns>
public string getUserNo()
{
string UserName = string.Empty;
UserName = GetUniqueKey(7);
using (I4IDBEntities context = new I4IDBEntities())
{
var isexistKey = context.RegisterUsers.Where(m => m.UserNo == UserName).FirstOrDefault();
if (isexistKey != null)
{
getUserNo();
}
}
return UserName;
}
public List <InputQueryDataDAO> GetQueries()
{
List <InputQueryDataDAO> list = new List <InputQueryDataDAO>();
try
{
using (I4IDBEntities context = new I4IDBEntities())
{
InputQueryDataDAO obj = new InputQueryDataDAO();
var data = (from query in context.InputQueryDatas
join userDetail in context.RegisterUsers on query.UserNo equals userDetail.UserNo
//where qur.acion == "True" && !itemIds.Contains(query.Id) && qur.Supplier == SID
select new
{
Id = query.Id,
UQueryNum = query.UQueryNum,
Name = userDetail.UserFirstName + " " + userDetail.UserLastName,
Email = userDetail.Email,
Phone = userDetail.phone,
//QueryText=query.QueryText,
acion = query.acion,
QDate = query.QDate,
Attachments = query.Attachments
}).OrderByDescending(m => m.QDate);
//var data = context.InputQueryDatas;
//from inpQ in context.InputQueryDatas
// join frwQ in context.ForwardQueries on inpQ.Id equals frwQ.QueryId
// select new
// {
// };
foreach (var item in data)
{
list.Add(new InputQueryDataDAO {
UQueryNum = item.UQueryNum, Name = item.Name, Email = item.Email, Phone = item.Phone, acion = item.acion, Checkbox = false
}); //, QueryText = item.QueryText
}
}
}
catch
{
}
return(list);
}
//System.Net.Mail.Attachment' to type 'System.Web.Mail.MailAttachment
public RegisterUserDAO GetUserDetail(string UserName)
{
RegisterUserDAO obj = new RegisterUserDAO();
using ( I4IDBEntities context = new I4IDBEntities())
{
var userDetail = from data in context.RegisterUsers where data.UserNo == UserName select data;
foreach (RegisterUser item in userDetail)
{
obj.UserFirstName = item.UserFirstName + " " + item.UserLastName;
obj.Email = item.Email;
obj.phone = item.phone;
if (obj.UserFirstName != "")
return obj;
}
}
return obj;
}
public List<InputQueryDataDAO> GetQueries()
{
List<InputQueryDataDAO> list = new List<InputQueryDataDAO>();
try
{
using ( I4IDBEntities context = new I4IDBEntities())
{
InputQueryDataDAO obj = new InputQueryDataDAO();
var data = (from query in context.InputQueryDatas
join userDetail in context.RegisterUsers on query.UserNo equals userDetail.UserNo
//where qur.acion == "True" && !itemIds.Contains(query.Id) && qur.Supplier == SID
select new
{
Id = query.Id,
UQueryNum = query.UQueryNum,
Name = userDetail.UserFirstName + " " + userDetail.UserLastName,
Email = userDetail.Email,
Phone = userDetail.phone,
//QueryText=query.QueryText,
acion = query.acion,
QDate = query.QDate,
Attachments=query.Attachments
}).OrderByDescending(m => m.QDate);
//var data = context.InputQueryDatas;
//from inpQ in context.InputQueryDatas
// join frwQ in context.ForwardQueries on inpQ.Id equals frwQ.QueryId
// select new
// {
// };
foreach (var item in data)
{
list.Add(new InputQueryDataDAO { UQueryNum = item.UQueryNum, Name = item.Name, Email = item.Email, Phone = item.Phone, acion = item.acion, Checkbox = false });//, QueryText = item.QueryText
}
}
}
catch
{
}
return list;
}
public static void UpdateDiaryEvent(int id, string NewEventStart, string NewEventEnd)
{
// EventStart comes ISO 8601 format, eg: "2000-01-10T10:00:00Z" - need to convert to DateTime
using (I4IDBEntities ent = new I4IDBEntities())
{
var rec = ent.AppointmentDiaries.FirstOrDefault(s => s.AppointmentID == id);
if (rec != null)
{
DateTime DateTimeStart = DateTime.Parse(NewEventStart, null, DateTimeStyles.RoundtripKind).ToLocalTime(); // and convert offset to localtime
rec.DateTimeScheduled = DateTimeStart;
if (!String.IsNullOrEmpty(NewEventEnd))
{
TimeSpan span = DateTime.Parse(NewEventEnd, null, DateTimeStyles.RoundtripKind).ToLocalTime() - DateTimeStart;
rec.AppointmentLength = Convert.ToInt32(span.TotalMinutes);
}
ent.SaveChanges();
}
}
}
{//System.Net.Mail.Attachment' to type 'System.Web.Mail.MailAttachment
public RegisterUserDAO GetUserDetail(string UserName)
{
RegisterUserDAO obj = new RegisterUserDAO();
using (I4IDBEntities context = new I4IDBEntities())
{
var userDetail = from data in context.RegisterUsers where data.UserNo == UserName select data;
foreach (RegisterUser item in userDetail)
{
obj.UserFirstName = item.UserFirstName + " " + item.UserLastName;
obj.Email = item.Email;
obj.phone = item.phone;
if (obj.UserFirstName != "")
{
return(obj);
}
}
}
return(obj);
}
public static bool InitialiseDiary()
{
// init connection to database
I4IDBEntities ent = new I4IDBEntities();
try
{
for (int i = 0; i < 30; i++)
{
AppointmentDiary item = new AppointmentDiary();
// record ID is auto generated
item.Title = "Appt: " + i.ToString();
item.SomeImportantKey = i;
item.StatusENUM = GetRandomValue(0, 3); // random is exclusive - we have three status enums
if (i <= 5) // create a few appointments for todays date
{
item.DateTimeScheduled = GetRandomAppointmentTime(false, true);
}
else
{ // rest of appointments on previous and future dates
if (i % 2 == 0)
{
item.DateTimeScheduled = GetRandomAppointmentTime(true, false); // flip/flop between date ahead of today and behind today
}
else
{
item.DateTimeScheduled = GetRandomAppointmentTime(false, false);
}
}
item.AppointmentLength = GetRandomValue(1, 5) * 15; // appoiment length in blocks of fifteen minutes in this demo
ent.AppointmentDiaries.Add(item);
ent.SaveChanges();
}
}
catch (Exception)
{
return(false);
}
return(ent.AppointmentDiaries.Count() > 0);
}
public static bool CreateNewEvent(string Title, string NewEventDate)
{
try
{
I4IDBEntities ent = new I4IDBEntities();
AppointmentDiary rec = new AppointmentDiary();
string userNoCookiesVale = HttpContext.Current.Request.Cookies["UserNo"].Value;
var result = (from r in ent.RegisterUsers
where r.UserNo == userNoCookiesVale && r.UserRole == "T"
select new {
r.Id
});
rec.id = Convert.ToInt32(result.FirstOrDefault().Id);
rec.Title = Title;
DateTime dt = DateTime.ParseExact(NewEventDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
rec.DateTimeScheduled = dt;
//rec.AppointmentLength = Int32.Parse(NewEventDuration);
ent.AppointmentDiaries.Add(rec);
ent.SaveChanges();
}
catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
{
Exception raise = dbEx;
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
string message = string.Format("{0}:{1}",
validationErrors.Entry.Entity.ToString(),
validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
raise = new InvalidOperationException(message, raise);
}
}
throw raise;
return(false);
}
return(true);
}
public static bool CreateNewEvent(string Title, string NewEventDate)
{
try
{
I4IDBEntities ent = new I4IDBEntities();
AppointmentDiary rec = new AppointmentDiary();
string userNoCookiesVale = HttpContext.Current.Request.Cookies["UserNo"].Value;
var result =(from r in ent.RegisterUsers
where r.UserNo==userNoCookiesVale && r.UserRole=="T"
select new {
r.Id
});
rec.id = Convert.ToInt32(result.FirstOrDefault().Id);
rec.Title = Title;
DateTime dt = DateTime.ParseExact(NewEventDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
rec.DateTimeScheduled = dt;
//rec.AppointmentLength = Int32.Parse(NewEventDuration);
ent.AppointmentDiaries.Add(rec);
ent.SaveChanges();
}
catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
{
Exception raise = dbEx;
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
string message = string.Format("{0}:{1}",
validationErrors.Entry.Entity.ToString(),
validationError.ErrorMessage);
// raise a new exception nesting
// the current instance as InnerException
raise = new InvalidOperationException(message, raise);
}
}
throw raise;
return false;
}
return true;
}
public List<SupplierDAO> GetSupplier()
{
List<SupplierDAO> list = new List<SupplierDAO>();
try
{
using ( I4IDBEntities context = new I4IDBEntities())
{
SupplierDAO obj = new SupplierDAO();
//var data= context.Suppliers ;
var data = context.RegisterUsers.Where(u => u.UserRole == "S");
//var data=from sup in context.Suppliers join forwQry in context.ForwardQueries on sup.;
foreach(RegisterUser item in data)
{
list.Add(new SupplierDAO {Id=item.Id,Company=item.Company +"-" +item.UserFirstName +" " +item.UserLastName });
}
}
}
catch
{
}
return list;
}
public static List<DiaryEvent> LoadAppointmentSummaryInDateRange(double start, double end)
{
var fromDate = ConvertFromUnixTimestamp(start);
var toDate = ConvertFromUnixTimestamp(end);
using ( I4IDBEntities ent = new I4IDBEntities())
{
var rslt = ent.AppointmentDiaries.Where(s => s.DateTimeScheduled >= fromDate && System.Data.Objects.EntityFunctions.AddMinutes(s.DateTimeScheduled, s.AppointmentLength) <= toDate)
.GroupBy(s => System.Data.Objects.EntityFunctions.TruncateTime(s.DateTimeScheduled))
.Select(x => new { DateTimeScheduled = x.Key, Count = x.Count() });
List<DiaryEvent> result = new List<DiaryEvent>();
int i = 0;
foreach (var item in rslt)
{
DiaryEvent rec = new DiaryEvent();
rec.AppointmentID = i; //we dont link this back to anything as its a group summary but the fullcalendar needs unique IDs for each event item (unless its a repeating event)
rec.SomeImportantKeyID = -1;
string StringDate = string.Format("{0:dd-MM-yyyy}", item.DateTimeScheduled);
rec.StartDateString = StringDate + "T00:00:00"; //ISO 8601 format
rec.EndDateString = StringDate + "T23:59:59";
rec.Title = "Booked: " + item.Count.ToString();
result.Add(rec);
i++;
}
return result;
}
}
public BaseAccessor()
{
context = new I4IDBEntities();
}
public static void UpdateDiaryEvent(int id, string NewEventStart, string NewEventEnd)
{
// EventStart comes ISO 8601 format, eg: "2000-01-10T10:00:00Z" - need to convert to DateTime
using ( I4IDBEntities ent = new I4IDBEntities())
{
var rec = ent.AppointmentDiaries.FirstOrDefault(s => s.AppointmentID == id);
if (rec != null)
{
DateTime DateTimeStart = DateTime.Parse(NewEventStart, null, DateTimeStyles.RoundtripKind).ToLocalTime(); // and convert offset to localtime
rec.DateTimeScheduled = DateTimeStart;
if (!String.IsNullOrEmpty(NewEventEnd))
{
TimeSpan span = DateTime.Parse(NewEventEnd, null, DateTimeStyles.RoundtripKind).ToLocalTime() - DateTimeStart;
rec.AppointmentLength = Convert.ToInt32(span.TotalMinutes);
}
ent.SaveChanges();
}
}
}
public BaseAccessor()
{
context = new I4IDBEntities();
}