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