private static void BuildWorksheet(ComObjectManager com, IEnumerable <Nomination> nominations,
Worksheet worksheet)
{
var cells = com.Get(() => worksheet.Cells);
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Record_");
SetCellValue(cells, 1, 2, @"Nomination_IDs");
SetCellValue(cells, 1, 3, @"Continuously_Improving");
SetCellValue(cells, 1, 4, @"Driving_Innovation");
SetCellValue(cells, 1, 5, @"Delighting_Customers");
SetCellValue(cells, 1, 6, @"Behaving_with_Integrity");
SetCellValue(cells, 1, 7, @"Delivering_Meaningful_Outcomes");
SetCellValue(cells, 1, 8, @"Streaming_Good");
SetCellValue(cells, 1, 9, @"WRITEUP");
var rowNumber = 2;
foreach (var nomination in nominations)
{
SetCellValue(cells, rowNumber, 1, rowNumber - 1);
SetCellValue(cells, rowNumber, 2, nomination.VotingIdentifier.ToString());
SetCellValue(cells, rowNumber, 3, GetCompanyValue(nomination, CompanyValue.ContinuouslyImproving));
SetCellValue(cells, rowNumber, 4, GetCompanyValue(nomination, CompanyValue.DrivingInnovation));
SetCellValue(cells, rowNumber, 5, GetCompanyValue(nomination, CompanyValue.DelightingCustomers));
SetCellValue(cells, rowNumber, 6, GetCompanyValue(nomination, CompanyValue.BehavingWithIntegrity));
SetCellValue(cells, rowNumber, 7, GetCompanyValue(nomination, CompanyValue.DeliveringMeaningfulOutcomes));
SetCellValue(cells, rowNumber, 8, GetCompanyValue(nomination, CompanyValue.StreamingGood));
SetCellValue(cells, rowNumber, 9, nomination.WriteUp.ToString());
++rowNumber;
}
}
private static void BuildWorksheet(ComObjectManager com, NominationList nominationList, Worksheet worksheet)
{
var cells = com.Get(() => worksheet.Cells);
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Quarter");
SetCellValue(cells, 1, 2, @"Name");
SetCellValue(cells, 1, 3, @"Office");
SetCellValue(cells, 1, 4, @"Values");
SetCellValue(cells, 1, 5, @"WriteUps");
var rowNumber = 2;
foreach (var awardWinner in nominationList.StarValuesAwardWinners)
{
var companyValues = string.Join("; ",
nominationList.GetCompanyValuesForAwardWinner(awardWinner).Select(cv => cv.ToString()));
var writeUps = CompileWriteUps(nominationList.GetNominationWriteUpsForAwardWinner(awardWinner));
SetCellValue(cells, rowNumber, 1, nominationList.AwardsPeriod.Quarter.Abbreviation);
SetCellValue(cells, rowNumber, 2, awardWinner.Name.FullName);
SetCellValue(cells, rowNumber, 3, awardWinner.OfficeLocation.Name);
SetCellValue(cells, rowNumber, 4, companyValues);
SetCellValue(cells, rowNumber, 5, writeUps);
++rowNumber;
}
}
public void Execute()
{
using (var com = new ComObjectManager())
{
Execute(com, null);
}
}
private static void ToggleWorkOffline(OutlookApplication outlook, ComObjectManager com)
{
var explorer = com.Get(outlook.ActiveExplorer);
var commandBars = com.Get(() => explorer.CommandBars);
commandBars.ExecuteMso(@"ToggleOnline");
}
private static void BuildWorksheet(ComObjectManager com, NominationList nominationList, Worksheet workSheet)
{
var cells = com.Get(() => workSheet.Cells);
var attendees = nominationList
.AwardsLuncheonInvitees
.OrderBy(i => i.OfficeLocation.ToString())
.ThenBy(i => i.Name.FullName)
.ToList();
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Nominee Name");
SetCellValue(cells, 1, 2, @"Nominee Office");
SetCellValue(cells, 1, 3, @"Nominee Email Address");
for (var i = 0; i < attendees.Count; ++i)
{
var rowNumber = i + 2;
var attendee = attendees[i];
SetCellValue(cells, rowNumber, 1, attendee.Name.FullName);
SetCellValue(cells, rowNumber, 2, attendee.OfficeLocation.ToString());
SetCellValue(cells, rowNumber, 3, attendee.EmailAddress.ToString());
}
}
protected override void DoMergeTypeSetup(ComObjectManager com, MailMerge mailMerge)
{
ActivateOutlookWorkOffline(com);
mailMerge.MailAddressFieldName = @"Email";
mailMerge.MailFormat = WdMailMergeMailFormat.wdMailFormatHTML;
mailMerge.MailSubject = GetEmailSubject();
mailMerge.Destination = WdMailMergeDestination.wdSendToEmail;
}
private static void AddSuperStarVotingGuideAttachments(ComObjectManager com, MailItem mailItem,
IMailMergeFactory mailMergeFactory, NominationList nominationList, bool hasSuperStar)
{
if (hasSuperStar)
{
AddVotingGuideAttachment(com, mailItem, mailMergeFactory, nominationList, AwardType.SuperStar);
}
}
private static void AddSuperStarVotingKeyAttachments(ComObjectManager com, MailItem mailItem,
IExcelFileFactory excelFileFactory, NominationList nominationList, bool hasSuperStar)
{
if (hasSuperStar)
{
AddVotingKeyAttachment(com, mailItem, excelFileFactory, nominationList, AwardType.SuperStar);
}
}
private static void AddSuperStarCertificatesAttachments(ComObjectManager com, MailItem mailItem,
IMailMergeFactory mailMergeFactory, NominationList nominationList)
{
if (!nominationList.HasSuperStarCertificateRecipients)
{
return;
}
AddCertificatesAttachment(com, mailItem, mailMergeFactory, nominationList, AwardType.SuperStar);
}
protected EmailBase(Action <ComObjectManager, MailItem> buildEmail)
{
if (buildEmail == null)
{
throw new ArgumentNullException(nameof(buildEmail));
}
Com = new ComObjectManager();
_mailItem = CreateMailItem(buildEmail);
}
private static void AddStarValuesVotingGuideAttachmentsAndInstructions(ComObjectManager com, MailItem mailItem,
HtmlNode content, IMailMergeFactory mailMergeFactory, NominationList nominationList)
{
var instructions =
$@"This document has all the nominations for the {
AwardType.StarValues.PrettyName
}. Question #2 of the voting survey covers these nominations. The nominees are from all offices and also include remote employees. The survey does NOT list them in any particular order. Please note some nominees may have been nominated multiple times.";
AddVotingGuideAttachmentAndInstructions(com, mailItem, content, mailMergeFactory, nominationList,
AwardType.StarValues, instructions);
}
private static void AddRisingStarVotingGuideAttachmentsAndInstructions(ComObjectManager com, MailItem mailItem,
HtmlNode content, IMailMergeFactory mailMergeFactory, NominationList nominationList)
{
var instructions =
$@"This document has all the nomination for the {
AwardType.RisingStar.PrettyName
} for interns. Question #3 of the voting survey covers these nominations. The intern nominations are from across the company. The survey does NOT list them in any particular order. Please note some nominees may have been nominated multiple times.";
AddVotingGuideAttachmentAndInstructions(com, mailItem, content, mailMergeFactory, nominationList,
AwardType.RisingStar, instructions);
}
public void Execute(FilePath filePath)
{
if (filePath == null)
{
throw new ArgumentNullException(nameof(filePath));
}
using (var com = new ComObjectManager())
{
Execute(com, filePath);
}
}
private static void SaveMailMergeOutputDocument(ComObjectManager com, Application word, FilePath outputFilePath,
object no)
{
var activeDocument = com.Get(() => word.ActiveDocument);
object filePath = outputFilePath.Value;
var extension = Path.GetExtension(outputFilePath.Value);
var saveFormat = string.Equals(@".pdf", extension, StringComparison.InvariantCultureIgnoreCase)
? WdSaveFormat.wdFormatPDF
: WdSaveFormat.wdFormatDocumentDefault;
activeDocument.SaveAs(ref filePath, saveFormat);
activeDocument.Close(ref no);
}
private static void AddCertificatesAttachments(ComObjectManager com, MailItem mailItem,
IMailMergeFactory mailMergeFactory,
NominationList nominationList, HtmlNode content)
{
var awardCategory = nominationList.AwardsPeriod.AwardCategory;
if (awardCategory == AwardCategory.QuarterlyAwards)
{
AddQuarterlyCertificatesAttachments(com, mailItem, mailMergeFactory, nominationList, content);
}
else if (awardCategory == AwardCategory.SuperStarAwards)
{
AddSuperStarCertificatesAttachments(com, mailItem, mailMergeFactory, nominationList);
}
}
private static void AddVotingGuideAttachments(ComObjectManager com, MailItem mailItem, HtmlNode content,
IMailMergeFactory mailMergeFactory, NominationList nominationList, bool hasStarValues, bool hasRisingStar,
bool hasSuperStar)
{
var awardCategory = nominationList.AwardsPeriod.AwardCategory;
if (awardCategory == AwardCategory.QuarterlyAwards)
{
AddQuarterlyVotingGuideAttachments(com, mailItem, content, mailMergeFactory, nominationList,
hasStarValues, hasRisingStar);
}
else if (awardCategory == AwardCategory.SuperStarAwards)
{
AddSuperStarVotingGuideAttachments(com, mailItem, mailMergeFactory, nominationList, hasSuperStar);
}
}
private static void AddQuarterlyVotingGuideAttachmentsAndInstructions(ComObjectManager com, MailItem mailItem,
HtmlNode content,
IMailMergeFactory mailMergeFactory, NominationList nominationList, bool hasStarValues, bool hasRisingStar)
{
if (hasStarValues)
{
AddStarValuesVotingGuideAttachmentsAndInstructions(com, mailItem, content, mailMergeFactory,
nominationList);
}
if (hasRisingStar)
{
AddRisingStarVotingGuideAttachmentsAndInstructions(com, mailItem, content, mailMergeFactory,
nominationList);
}
}
private static void BuildEmail(ComObjectManager com, MailItem mailItem, IEmailConfiguration emailConfiguration,
IExcelFileFactory excelFileFactory, NominationList nominationList)
{
var luncheonPlanners = emailConfiguration.LuncheonPlannerPeople;
var awardsName = nominationList.AwardsPeriod.AwardsName;
mailItem.To = string.Join(";", luncheonPlanners.Select(p => p.EmailAddress));
mailItem.CC = string.Join(";", emailConfiguration.EiaCoChair1.EmailAddress, emailConfiguration.EiaCoChair2.EmailAddress);
mailItem.Subject = $@"EIA: {awardsName} luncheon invitee list";
var content = CreateContentNode();
AppendRequest(content, luncheonPlanners, awardsName);
AppendThanks(content);
WriteMailItemBody(mailItem, content);
AddAwardLuncheonInviteeListAttachment(com, mailItem, excelFileFactory, nominationList);
}
private static void BuildEmail(ComObjectManager com, MailItem mailItem, IEmailConfiguration emailConfiguration,
IMailMergeFactory mailMergeFactory, NominationList nominationList)
{
var certificatePrinter = emailConfiguration.CertificatePrinterPerson;
var awardsName = nominationList.AwardsPeriod.AwardsName;
mailItem.To = certificatePrinter.EmailAddress.Value;
mailItem.CC = string.Join(";", emailConfiguration.EiaCoChair1.EmailAddress, emailConfiguration.EiaCoChair2.EmailAddress);
mailItem.Subject = $@"EIA: {awardsName} winner certificates";
var content = CreateContentNode();
AppendIntroduction(content, certificatePrinter, awardsName);
AddCertificatesAttachments(com, mailItem, mailMergeFactory, nominationList, content);
AppendThanks(content);
WriteMailItemBody(mailItem, content);
}
private static void AddVotingGuideAttachment(ComObjectManager com, MailItem mailItem,
IMailMergeFactory mailMergeFactory,
NominationList nominationList, AwardType awardType)
{
var attachments = com.Get(() => mailItem.Attachments);
var fileName = awardType.GetVotingGuideFileName(nominationList.AwardsPeriod);
var filePath = FilePath.Create(Path.Combine(Path.GetTempPath(), fileName), false);
if (File.Exists(filePath.Value))
{
File.Delete(filePath.Value);
}
var mailMerge = mailMergeFactory.GetVotingGuideMailMerge(awardType, nominationList);
mailMerge.Execute(filePath);
com.Get(() => attachments.Add(filePath.Value));
}
private static Person GetPerson(ComObjectManager com, AddressEntry addressEntry)
{
if (addressEntry.AddressEntryUserType != OlAddressEntryUserType.olExchangeUserAddressEntry &&
addressEntry.AddressEntryUserType != OlAddressEntryUserType.olExchangeRemoteUserAddressEntry)
{
return(null);
}
var exchangeUser = com.Get(addressEntry.GetExchangeUser);
if (exchangeUser == null)
{
return(null);
}
var person = new Person(exchangeUser);
return(person);
}
private static void BuildWorksheet(ComObjectManager com, AwardsPeriod awardsPeriod,
IEnumerable <Nomination> nominations, Worksheet worksheet)
{
var cells = com.Get(() => worksheet.Cells);
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Year");
SetCellValue(cells, 1, 2, @"Quarter");
SetCellValue(cells, 1, 3, @"Submitted by");
SetCellValue(cells, 1, 4, @"NOMINEE'S NAME");
SetCellValue(cells, 1, 5, @"NOMINEE'S OFFICE");
SetCellValue(cells, 1, 6, @"Continuously_Improving");
SetCellValue(cells, 1, 7, @"Driving_Innovation");
SetCellValue(cells, 1, 8, @"Delighting_Customers");
SetCellValue(cells, 1, 9, @"Behaving_with_Integrity");
SetCellValue(cells, 1, 10, @"Delivering_Meaningful_Outcomes");
SetCellValue(cells, 1, 11, @"Streaming_Good");
SetCellValue(cells, 1, 12, @"WRITE-UP");
SetCellValue(cells, 1, 13, @"Email");
var rowNumber = 2;
foreach (var nomination in nominations)
{
SetCellValue(cells, rowNumber, 1, awardsPeriod.Year.ToString());
SetCellValue(cells, rowNumber, 2, awardsPeriod.Quarter.Abbreviation);
SetCellValue(cells, rowNumber, 3, nomination.NominatorName.FullName);
SetCellValue(cells, rowNumber, 4, nomination.NomineeName.FullName);
SetCellValue(cells, rowNumber, 5, nomination.NomineeOfficeLocation.ToString());
SetCellValue(cells, rowNumber, 6, GetCompanyValue(nomination, CompanyValue.ContinuouslyImproving));
SetCellValue(cells, rowNumber, 7, GetCompanyValue(nomination, CompanyValue.DrivingInnovation));
SetCellValue(cells, rowNumber, 8, GetCompanyValue(nomination, CompanyValue.DelightingCustomers));
SetCellValue(cells, rowNumber, 9, GetCompanyValue(nomination, CompanyValue.BehavingWithIntegrity));
SetCellValue(cells, rowNumber, 10, GetCompanyValue(nomination, CompanyValue.DeliveringMeaningfulOutcomes));
SetCellValue(cells, rowNumber, 11, GetCompanyValue(nomination, CompanyValue.StreamingGood));
SetCellValue(cells, rowNumber, 12, nomination.WriteUp.ToString());
SetCellValue(cells, rowNumber, 13, nomination.NomineeEmailAddress.ToString());
++rowNumber;
}
}
public static void ActivateWorkOffline(this OutlookApplication outlook, ComObjectManager com)
{
if (outlook == null)
{
throw new ArgumentNullException(nameof(outlook));
}
if (com == null)
{
throw new ArgumentNullException(nameof(com));
}
var session = com.Get(() => outlook.Session);
if (session.Offline)
{
return;
}
ToggleWorkOffline(outlook, com);
}
private static void BuildWorksheet(ComObjectManager com, NominationList nominationList, Worksheet worksheet)
{
var cells = com.Get(() => worksheet.Cells);
var awardWinners = nominationList.RisingStarAwardWinners;
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Nominee Name");
SetCellValue(cells, 1, 2, @"Nominee Office");
for (var i = 0; i < awardWinners.Count; ++i)
{
var rowNumber = i + 2;
var awardWinner = awardWinners[i];
SetCellValue(cells, rowNumber, 1, awardWinner.Name.FullName);
SetCellValue(cells, rowNumber, 2, awardWinner.OfficeLocation.Name);
}
}
private static void AddVotingKeyAttachment(ComObjectManager com, MailItem mailItem,
IExcelFileFactory excelFileFactory,
NominationList nominationList, AwardType awardType)
{
var attachments = com.Get(() => mailItem.Attachments);
var fileName = awardType.GetVotingKeyFileName(nominationList.AwardsPeriod);
var filePath = FilePath.Create(Path.Combine(Path.GetTempPath(), fileName), false);
if (File.Exists(filePath.Value))
{
File.Delete(filePath.Value);
}
using (var excelFile = excelFileFactory.GetVotingKeyExcelFile(awardType, nominationList))
{
excelFile.Save(filePath);
}
com.Get(() => attachments.Add(filePath.Value));
}
private static void AddQuarterlyVotingKeyAttachments(ComObjectManager com, MailItem mailItem, HtmlNode content,
IExcelFileFactory excelFileFactory, NominationList nominationList, bool hasStarValues, bool hasRisingStar)
{
if (!hasStarValues)
{
AppendNoNomineesCaveat(content, AwardType.StarValues);
}
else
{
AddVotingKeyAttachment(com, mailItem, excelFileFactory, nominationList, AwardType.StarValues);
}
if (!hasRisingStar)
{
AppendNoNomineesCaveat(content, AwardType.RisingStar);
}
else
{
AddVotingKeyAttachment(com, mailItem, excelFileFactory, nominationList, AwardType.RisingStar);
}
}
private static void AddVotingGuideAttachmentAndInstructions(ComObjectManager com, MailItem mailItem,
HtmlNode content, IMailMergeFactory mailMergeFactory, NominationList nominationList, AwardType awardType,
string instructions)
{
var attachments = com.Get(() => mailItem.Attachments);
var fileName = awardType.GetVotingGuideFileName(nominationList.AwardsPeriod);
var filePath = FilePath.Create(Path.Combine(Path.GetTempPath(), fileName), false);
if (File.Exists(filePath.Value))
{
File.Delete(filePath.Value);
}
var mailMerge = mailMergeFactory.GetVotingGuideMailMerge(awardType, nominationList);
mailMerge.Execute(filePath);
com.Get(() => attachments.Add(filePath.Value));
AppendSection(content, $@"<b><i>{awardType.PrettyName} Nominees:</i></b> {fileName} -- {instructions}");
}
private static void AddQuarterlyCertificatesAttachments(ComObjectManager com, MailItem mailItem,
IMailMergeFactory mailMergeFactory, NominationList nominationList, HtmlNode content)
{
if (!nominationList.HasStarValuesCertificateRecipients)
{
AppendNoCertificateRecipientsCaveat(content, AwardType.StarValues);
}
else
{
AddCertificatesAttachment(com, mailItem, mailMergeFactory, nominationList, AwardType.StarValues);
}
if (!nominationList.HasRisingStarCertificateRecipients)
{
AppendNoCertificateRecipientsCaveat(content, AwardType.RisingStar);
}
else
{
AddCertificatesAttachment(com, mailItem, mailMergeFactory, nominationList, AwardType.RisingStar);
}
}
private static void AddAwardLuncheonInviteeListAttachment(ComObjectManager com, MailItem mailItem,
IExcelFileFactory excelFileFactory,
NominationList nominationList)
{
var attachments = com.Get(() => mailItem.Attachments);
var fileName =
$@"{nominationList.AwardsPeriod.FileNamePrefix}_StarAwards_LuncheonInvitees.xlsx";
var filePath = FilePath.Create(Path.Combine(Path.GetTempPath(), fileName), false);
if (File.Exists(filePath.Value))
{
File.Delete(filePath.Value);
}
using (var excelFile = excelFileFactory.GetAwardsLuncheonInviteeListExcelFile(nominationList))
{
excelFile.Save(filePath);
}
com.Get(() => attachments.Add(filePath.Value));
}
private static void BuildWorksheet(ComObjectManager com, AwardsPeriod awardsPeriod,
IEnumerable <AwardWinner> certificateRecipients, Worksheet worksheet)
{
var cells = com.Get(() => worksheet.Cells);
cells.NumberFormat = "@"; // Format all cells as text.
SetCellValue(cells, 1, 1, @"Year");
SetCellValue(cells, 1, 2, @"Quarter");
SetCellValue(cells, 1, 3, @"Name");
var rowNumber = 2;
foreach (var certificateRecipient in certificateRecipients)
{
SetCellValue(cells, rowNumber, 1, awardsPeriod.Year.ToString());
SetCellValue(cells, rowNumber, 2, awardsPeriod.Quarter.FullName);
SetCellValue(cells, rowNumber, 3, certificateRecipient.Name.FullName);
++rowNumber;
}
}
