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