private static void EventNotificationScheduler() { SMCHDBEntities _entities = new SMCHDBEntities(); //Get list of dates DateTime today = DateTime.Today; String jobName = "EventNotificationJob"; List <Event> eventList = _entities.Events.Where(a => a.RegistrationOpenDate >= today).ToList(); JobDetailImpl eventJobDetail = new JobDetailImpl(jobName, typeof(EventEmailJob)); sched.AddJob(eventJobDetail, true); foreach (Event eventEntry in eventList) { SimpleTriggerImpl trigger = new SimpleTriggerImpl(eventEntry.Title + "_EventTrigger", eventEntry.RegistrationOpenDate.ToUniversalTime()); trigger.JobDataMap["eventName"] = eventEntry.Title; trigger.JobDataMap["eventStartTime"] = eventEntry.StartDateTime; trigger.JobDataMap["eventEndTime"] = eventEntry.EndDateTime; trigger.JobDataMap["regStartTime"] = eventEntry.RegistrationOpenDate; trigger.JobDataMap["regEndTime"] = eventEntry.RegistrationCloseDate; trigger.JobName = jobName; sched.ScheduleJob(trigger); } }
public static List <InternationalGMApplicationInfo1> GetLatestInternationalApplicationStatus(int applicationStatusID, bool getAll) { SMCHDBEntities _entities = new SMCHDBEntities(); int allID = _entities.ApplicationStatuses.Count() + 1; DateTime theDayAfterTommorrow = DateTime.Today.AddDays(2).ToUniversalTime().AddHours(8).AddHours(8); var latestInternationalGMApplicationStatus = from r in _entities.InternationalGMApplicationStatus group r by r.InternationalGMApplicationInfoID into temp let latestStatusID = temp.Max(a => a.ApplicationStatusID) from r in temp where r.ApplicationStatusID == latestStatusID && (r.InternationalGMApplicationInfo.ArrivalDate > theDayAfterTommorrow && !getAll || getAll) select r; var viewModel = (from r in _entities.InternationalGMApplicationInfos join h in latestInternationalGMApplicationStatus on r.ID equals h.InternationalGMApplicationInfoID where (h.ApplicationStatusID == applicationStatusID || applicationStatusID == allID) orderby r.ID select new InternationalGMApplicationInfo1 { InternationalGMApplicationInfo = r, //ID = r.ID, //MemberID = r.MemberID, //AshramID = r.AshramID, //ArrivalDate = r.ArrivalDate, //DepartureDate = r.DepartureDate, //ApplyDate = r.ApplyDate, //Remark = r.Remark, //ParentID = r.ParentID, ApplicationStatus = h.ApplicationStatus.Name, }).ToList(); return(viewModel); }
public static void AddApplicationStatus(SMCHDBEntities _entities, int ID, int statusID) { InternationalGMApplicationStatu InternationalGMApplicationStatus = new InternationalGMApplicationStatu(); InternationalGMApplicationStatus.ApplicationStatusID = statusID; InternationalGMApplicationStatus.ConfirmDate = DateTime.Now.ToUniversalTime().AddHours(8); InternationalGMApplicationStatus.InternationalGMApplicationInfoID = ID; _entities.AddToInternationalGMApplicationStatus(InternationalGMApplicationStatus); _entities.SaveChanges(); }
public static List <SelectListItem> GetMemberNameSelectList(Guid selectID) { SMCHDBEntities _entities = new SMCHDBEntities(); List <MemberInfo> memberInfos = (from r in _entities.MemberInfos where r.MemberNo.HasValue && r.MemberNo.Value < 999 orderby r.MemberNo select r).ToList(); List <SelectListItem> si = new List <SelectListItem>(); foreach (MemberInfo selectItem in memberInfos) { SelectListItem item = new SelectListItem { Text = selectItem.MemberNo.Value.ToString() + ' ' + selectItem.Name, Value = selectItem.MemberID.ToString() }; if (selectID == selectItem.MemberID) { item.Selected = true; } si.Add(item); } return(si); }
static private void GetCheckInOutAccessTableData(string[] args) { SMCHDBEntities _entities = new SMCHDBEntities(); string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Program Files\\att2008\\att2000.mdb"; string strSql = "SELECT CHECKINOUT.CHECKTIME, USERINFO.Badgenumber FROM (CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID = USERINFO.USERID) "; OleDbConnection con = new OleDbConnection(conString); DataTable AttendanceTable = new DataTable(); System.Console.WriteLine("Opening MDB connection..."); con.Open(); OleDbDataAdapter dAdapter = new OleDbDataAdapter(); dAdapter.SelectCommand = new OleDbCommand(strSql, con); dAdapter.Fill(AttendanceTable); con.Close(); System.Console.WriteLine("Data loaded from MDB file. Updating database, please wait..."); // get latest time in GroupMeditationAttendance table DateTime latestGroupMeditationAttendanceDB = _entities.GroupMeditationAttendances.OrderByDescending(a => a.CheckInTime).Select(a => a.CheckInTime).FirstOrDefault(); if (latestGroupMeditationAttendanceDB < new DateTime(2011, 12, 1) || args.Length == 1 && args[0].ToLower() == "all") { latestGroupMeditationAttendanceDB = new DateTime(2011, 12, 1); } else { latestGroupMeditationAttendanceDB = latestGroupMeditationAttendanceDB.AddDays(-3); } // Filter old record and no member person var attendanceList = AttendanceTable.AsEnumerable().Where( a => a.Field <DateTime>("CHECKTIME") >= latestGroupMeditationAttendanceDB); List <int> memberNoList = _entities.MemberInfos.Where(a => a.MemberNo.HasValue).Select(a => a.MemberNo.Value).ToList(); foreach (DataRow dr in attendanceList) { DateTime checkInTime = (DateTime)dr["CHECKTIME"]; int memberNo = int.Parse((string)dr["Badgenumber"]); if (!memberNoList.Contains(memberNo)) { continue; } //System.Console.WriteLine("Uploading record of member " + memberNo.ToString() + " at " + checkInTime.ToString()); Guid memberID = _entities.MemberInfos.SingleOrDefault(a => a.MemberNo == memberNo).MemberID; DateTime checkInTimeStart = checkInTime.AddMinutes(90 + 15); DateTime checkInTimeEnd = checkInTime.AddMinutes(-15); //if (_entities.Events.Any(a => a.StartDateTime <= checkInTimeStart && a.StartDateTime >= checkInTime)) if (_entities.Events.Any(a => a.StartDateTime <= checkInTimeStart && a.StartDateTime >= checkInTimeEnd)) { //int eventID = _entities.Events.Where(a => a.StartDateTime <= checkInTimeStart && a.StartDateTime >= checkInTime).FirstOrDefault().ID; Event aEvent = _entities.Events.Where(a => a.StartDateTime <= checkInTimeStart && a.StartDateTime >= checkInTimeEnd).FirstOrDefault(); if (!_entities.EventRegistrations.Any(a => a.EventID == aEvent.ID && a.MemberID == memberID)) { EventRegistration eventRegistration = new EventRegistration(); eventRegistration.MemberID = memberID; eventRegistration.EventID = aEvent.ID; eventRegistration.SignTime = checkInTime; _entities.AddToEventRegistrations(eventRegistration); _entities.SaveChanges(); System.Console.WriteLine("Uploading" + aEvent.Title + " MemberNo: " + memberNo.ToString() + " at " + checkInTime.ToString()); //System.Console.WriteLine("Updated Event checkin"); continue; } } //if (_entities.GroupMeditations.Any(a => a.StartDateTime <= checkInTimeStart && a.StartDateTime >= checkInTime)) if (_entities.GroupMeditations.Any(a => a.StartDateTime <= checkInTimeStart && a.StartDateTime >= checkInTimeEnd)) { //int groupMeditaionID = _entities.GroupMeditations.Where(a => a.StartDateTime <= checkInTimeStart && a.StartDateTime >= checkInTime).FirstOrDefault().ID; int groupMeditaionID = _entities.GroupMeditations.Where(a => a.StartDateTime <= checkInTimeStart && a.StartDateTime >= checkInTimeEnd).FirstOrDefault().ID; if (!_entities.GroupMeditationAttendances.Any(a => a.GroupMeditationID == groupMeditaionID && a.MemberID == memberID)) { GroupMeditationAttendance groupMeditationAttendance = new GroupMeditationAttendance(); groupMeditationAttendance.MemberID = memberID; groupMeditationAttendance.GroupMeditationID = groupMeditaionID; groupMeditationAttendance.CheckInTime = checkInTime; _entities.AddToGroupMeditationAttendances(groupMeditationAttendance); _entities.SaveChanges(); System.Console.WriteLine("Uploading GM: MemberNo " + memberNo.ToString() + " at " + checkInTime.ToString()); } } } }
public static string[,] GenerateExcelData(int ashramID) { string[,] contents = new string [1, 1]; SMCHDBEntities _entities = new SMCHDBEntities(); //var viewModel = _entities.InternationalGMApplicationInfos.Where(a=>a.AshramID == ashramID && a.ApplicationStatusID == 1).OrderByDescending(a => a.ID).ToList(); var viewModel = GetLatestInternationalApplicationStatus(2, false); // Screening approved. viewModel = viewModel.Where(a => a.InternationalGMApplicationInfo.AshramID == ashramID).ToList(); if (viewModel.Count > 0) { if (ashramID == 3) // penang { contents = new string[viewModel.Count, 36 + 1]; int i = 0; foreach (InternationalGMApplicationInfo1 aigm1 in viewModel) { int j = 0; InternationalGMApplicationInfo aigm = aigm1.InternationalGMApplicationInfo; AshramAndCenterInfo ashramAndCenterInfo = aigm.MemberInfo.AshramAndCenterInfo; //MembershipUser user = Membership.GetUser(ashramAndCenterInfo.Name); MembershipUser user = Membership.GetUser(aigm.MemberID); contents[i, j++] = aigm.ID.ToString(); contents[i, j++] = (i + 1).ToString(); //contents[i, j++] = string.Format("{0: dd-MMM-yyyy}", aigm.ApplyDate); contents[i, j++] = string.Format("{0: dd-MMM-yyyy}", DateTime.Today.ToUniversalTime().AddHours(8)); string CPName = Roles.GetUsersInRole("Contact Person").FirstOrDefault(); string CPEmail = Membership.GetUser(CPName).Email; contents[i, j++] = ashramAndCenterInfo.Email + "; " + CPEmail; contents[i, j++] = null; contents[i, j++] = ashramAndCenterInfo.Country.Name; contents[i, j++] = ashramAndCenterInfo.Name; contents[i, j++] = aigm.MemberInfo.CountryOfBirth; contents[i, j++] = aigm.MemberInfo.AshramAndCenterInfo.Name; contents[i, j++] = string.Format("{0: dd-MMM-yyyy}", aigm.ArrivalDate); contents[i, j++] = string.Format("{0: dd-MMM-yyyy}", aigm.DepartureDate); contents[i, j++] = aigm.MemberInfo.IDCardNo; contents[i, j++] = aigm.MemberInfo.Name; contents[i, j++] = aigm.MemberInfo.Gender.Name.Substring(0, 1); if (aigm.MemberInfo.DateOfBirth.HasValue) { contents[i, j++] = string.Format("{0: dd-MMM-yyyy}", aigm.MemberInfo.DateOfBirth.Value); contents[i, j++] = GetAge(aigm.MemberInfo.DateOfBirth.Value).ToString(); } else { j += 2; } if (aigm.MemberInfo.DateOfInitiation.HasValue) { contents[i, j] = string.Format("{0: dd-MMM-yyyy}", aigm.MemberInfo.DateOfInitiation.Value); } j++; contents[i, j++] = aigm.MemberInfo.InitiateType.Name.Substring(0, 1); List <InternationalTransport> transportStations = _entities.InternationalTransports.Where(a => a.AshramID == ashramID).ToList(); foreach (InternationalTransport ts in transportStations) { if (_entities.InternationalGMApplicationTransportInfos.Any(a => a.InternationalGMApplicationInfoID == aigm.ID && a.InternationalTransportID == ts.ID && a.InBound)) { InternationalGMApplicationTransportInfo transportInfo = _entities.InternationalGMApplicationTransportInfos.Single(a => a.InternationalGMApplicationInfoID == aigm.ID && a.InternationalTransportID == ts.ID && a.InBound); contents[i, j++] = string.Format("{0: dd-MMM-yyyy}", transportInfo.DateTime); if (ts.StationName.ToLower().Contains("airport")) { contents[i, j++] = transportInfo.FlightNo; } contents[i, j++] = string.Format("{0: HH:mm}", transportInfo.DateTime); } else { j = j + 2; if (ts.StationName.ToLower().Contains("airport")) { j++; } } if (_entities.InternationalGMApplicationTransportInfos.Any(a => a.InternationalGMApplicationInfoID == aigm.ID && a.InternationalTransportID == ts.ID && !a.InBound)) { InternationalGMApplicationTransportInfo transportInfo = _entities.InternationalGMApplicationTransportInfos.Single(a => a.InternationalGMApplicationInfoID == aigm.ID && a.InternationalTransportID == ts.ID && !a.InBound); contents[i, j++] = string.Format("{0: dd-MMM-yyyy}", transportInfo.DateTime); if (ts.StationName.ToLower().Contains("airport")) { contents[i, j++] = transportInfo.FlightNo; } contents[i, j++] = string.Format("{0: HH:mm}", transportInfo.DateTime); } else { j = j + 2; if (ts.StationName.ToLower().Contains("airport")) { j++; } } } contents[i++, j] = aigm.Remark; } } else if (ashramID == 2 || ashramID == 4) { } } return(contents); }
public static void SaveContentAndSendEmail(int ashramID) { string[,] content = GenerateExcelData(ashramID); if (content.GetLength(1) <= 1) { return; } StringBuilder sbHeader = GetHeaderStringBuilder(ashramID); StringBuilder sb = GetDataStringBuilder(content); string excelFilePath = @"C:\SMCH\Penang Ashram GM Application Form (Spore) " + string.Format("{0: dd-MMM-yyyy}", DateTime.Today.ToUniversalTime().AddHours(8)) + ".xls"; EmailMessage em = new EmailMessage(); SMCHDBEntities _entities = new SMCHDBEntities(); AshramAndCenterInfo ashramAndCenterInfo = _entities.AshramAndCenterInfos.SingleOrDefault(a => a.ID == ashramID); //"Attending Group Meditation At " + Penang Ashram [Feb 2012] (Singapore Centre, 2012-Jan-31)" em.Subject = "Attending Group Meditation At " + ashramAndCenterInfo.Name + " (Singapore Centre, " + string.Format("{0: dd-MMM-yyyy}", DateTime.Today.ToUniversalTime().AddHours(8)) + ")"; // Hallo Noble Saint Yeap, //CP of Penang Centre, //Kindly refer to the attached application form for the month of February 2012 revision 3. //A new applicant is reflected in row 4. //Brother Quek Chin Chye does attend group meditation at Singapore Centre. //With your kind permission, please allow him to attend group meditation over there while he is in Penang. //Kindly acknowledge receipt of this message. //Thank you. //Wishing you Master's Love & Blessings, //Singapore Centre string temp = ashramAndCenterInfo.Name.Replace("Ashram", ""); em.Message = "Hallo Noble Saint " + ashramAndCenterInfo.CPname + "\r\nContact Person of " + temp + "Center,\r\n"; //em.Message += "\r\nKindly refer to the attached application form from " + string.Format("{0: dd-MMM-yyyy}", DateTime.Today.AddDays(-3)) + " To " + string.Format("{0: dd-MMM-yyyy}", DateTime.Today.AddDays(-1)) + "\r\n"; em.Message += "\r\nKindly refer to the attached application form dated " + string.Format("{0: dd-MMM-yyyy}", DateTime.Today.ToUniversalTime().AddHours(8)) + ".\r\n"; em.Message += "\r\nTotal applicants = " + content.GetLength(0).ToString() + "."; em.Message += "\r\nThey do attend group meditation at Singapore Centre." + "\r\nWith your kind permission, please allow them to attend group meditation over there while they are in Penang.\r\n"; em.Message += "\r\nKindly acknowledge receipt of this message.\r\nThank you.\r\n"; em.Message += "\r\nWishing you Master's Love & Blessings,\r\nSingapore Centre\r\n"; em.From = "*****@*****.**"; string CPName = Roles.GetUsersInRole("Contact Person").FirstOrDefault(); string CPEmail = Membership.GetUser(CPName).Email; string localCenterEmail = _entities.AshramAndCenterInfos.SingleOrDefault(a => a.ID == 1).Email; // "Singapore Center" em.To = localCenterEmail; //ashramAndCenterInfo.Email em.cc = localCenterEmail + ", " + CPEmail; em.bcc = "*****@*****.**"; em.AttachFilePath = excelFilePath; //em.sb = sb; em.AttachedString = sbHeader.ToString() + sb.ToString(); EmailService es = new EmailService(); try { es.SendMessage(em); // change status for (int i = 0; i < content.GetLength(0); i++) { AddApplicationStatus(_entities, int.Parse(content[i, 0]), 4); } } catch { } }