static void Main(string[] args)
{
var userName = Settings.Default.UserName;
var password = Settings.Default.Password;
var clientId = Settings.Default.ClientId;
var intacctExcelPath = string.Empty;
var concurExcelPath = string.Empty;
var pdfPath = string.Empty;
var outputPath = string.Empty;
var count = 0;
var numSuccess = 0;
var numFail = 0;
IWorkbook concurWorkbook;
var reports = new List <Report>();
var reportEntries = new List <ReportEntry>();
try
{
//var list = ConcurClient.GetReportIds();
while (string.IsNullOrWhiteSpace(intacctExcelPath))
{
Console.WriteLine("Press Enter to Select the Intacct Excel Import File:");
Console.ReadLine();
using (var dialog = new CommonOpenFileDialog
{
Title = "Select Excel Document to Process",
InitialDirectory = "C:\\",
EnsurePathExists = true,
Multiselect = false
})
{
var result = dialog.ShowDialog();
if (result == CommonFileDialogResult.Ok && !string.IsNullOrWhiteSpace(dialog.FileName))
{
intacctExcelPath = dialog.FileName;
Console.WriteLine($"Path Selected: {intacctExcelPath}");
}
else
{
Console.WriteLine("The Path you selected was not valid. Please select a valid path.");
}
}
}
while (string.IsNullOrWhiteSpace(concurExcelPath))
{
Console.WriteLine("Press Enter to Select the Concur Excel Import File:");
Console.ReadLine();
using (var dialog = new CommonOpenFileDialog
{
Title = "Select Excel Document to Process",
InitialDirectory = "C:\\",
EnsurePathExists = true,
Multiselect = false
})
{
var result = dialog.ShowDialog();
if (result == CommonFileDialogResult.Ok && !string.IsNullOrWhiteSpace(dialog.FileName))
{
concurExcelPath = dialog.FileName;
Console.WriteLine($"Path Selected: {concurExcelPath}");
}
else
{
Console.WriteLine("The Path you selected was not valid. Please select a valid path.");
}
}
}
while (string.IsNullOrWhiteSpace(pdfPath))
{
Console.WriteLine("Press Enter to Select Egencia Pdfs Folder:");
Console.ReadLine();
using (var dialog = new CommonOpenFileDialog
{
Title = "Select a Folder to Process",
InitialDirectory = "C:\\",
EnsurePathExists = true,
IsFolderPicker = true,
Multiselect = false
})
{
var result = dialog.ShowDialog();
if (result == CommonFileDialogResult.Ok && !string.IsNullOrWhiteSpace(dialog.FileName))
{
pdfPath = dialog.FileName;
Console.WriteLine($"Path Selected: {pdfPath}");
Directory.CreateDirectory(pdfPath + "\\Attached");
}
else
{
Console.WriteLine("The Path you selected was not valid. Please select a valid path.");
}
}
}
while (string.IsNullOrWhiteSpace(outputPath))
{
Console.WriteLine("Press Enter to Select the PDF output direcotry:");
Console.ReadLine();
using (var dialog = new CommonOpenFileDialog
{
Title = "Select a Folder to Process",
InitialDirectory = "C:\\",
EnsurePathExists = true,
IsFolderPicker = true,
Multiselect = false
})
{
var result = dialog.ShowDialog();
if (result == CommonFileDialogResult.Ok && !string.IsNullOrWhiteSpace(dialog.FileName))
{
outputPath = dialog.FileName;
Console.WriteLine($"Path Selected: {outputPath}");
Directory.CreateDirectory(outputPath + "\\Attached");
}
else
{
Console.WriteLine("The Path you selected was not valid. Please select a valid path.");
}
}
}
IWorkbook intacctWorkbook;
using (var file = new FileStream(intacctExcelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
var extension = Path.GetExtension(intacctExcelPath);
if (extension.Contains("xlsx"))
{
intacctWorkbook = new XSSFWorkbook(file);
}
else
{
intacctWorkbook = new HSSFWorkbook(file);
}
}
using (var file = new FileStream(concurExcelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
var extension = Path.GetExtension(intacctExcelPath);
if (extension.Contains("xlsx"))
{
concurWorkbook = new XSSFWorkbook(file);
}
else
{
concurWorkbook = new HSSFWorkbook(file);
}
}
var sheet = concurWorkbook.GetSheet(concurWorkbook.GetSheetName(intacctWorkbook.ActiveSheetIndex));
Console.WriteLine("Processing Concur Excel File.");
for (var i = 1; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
var key = row.GetCell(24)?.NumericCellValue.ToString(CultureInfo.InvariantCulture) ?? string.Empty;
var reportId = row.GetCell(25)?.StringCellValue ?? string.Empty;
var vendor = row.GetCell(6)?.StringCellValue ?? string.Empty;
var memo = row.GetCell(7)?.StringCellValue ?? string.Empty;
var date = row.GetCell(5)?.DateCellValue ?? new DateTime();
var amount = row.GetCell(9)?.NumericCellValue ?? 0;
var entry = new ReportEntry
{
Key = key,
ReportId = reportId,
VendorName = vendor,
Memo = memo,
TransactionDate = date,
Amount = amount
};
reportEntries.Add(entry);
}
sheet = intacctWorkbook.GetSheet(intacctWorkbook.GetSheetName(intacctWorkbook.ActiveSheetIndex));
var oathToken = ConcurClient.Login(userName, password, clientId);
Console.WriteLine("Processing Intacct Excel File.");
for (var i = 4; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
var invoiceNum = "";
var key = "";
var report = new Report();
try
{
invoiceNum = row.GetCell(0)?.StringCellValue ?? string.Empty;
report = reports.SingleOrDefault(q => q.Id == invoiceNum) ?? new Report {
Id = invoiceNum, ReportId = ""
};
var keyString = row.GetCell(1)?.StringCellValue ?? string.Empty;
var keyTokens = keyString.Split('|');
if (keyTokens.Length <= 1)
{
continue;
}
key = keyTokens[0].Trim();
var entry = new ReportEntry()
{
Key = key
};
if (key.Length < 11)
{
//Find the existing ReportEntry
entry = reportEntries.SingleOrDefault(q => q.Key == key);
if (entry == null)
{
throw new Exception($"Expense Entry Key#{key} could not be found.");
}
report.ReportId = entry.ReportId;
entry.Type = ReportEntryType.Concur;
//If a file exists for this key in the pdf folder, we want to add pull from it instead of concur
if (File.Exists($"{pdfPath}\\{entry.Key}.pdf)"))
{
entry.Path = $"{pdfPath}\\{entry.Key}.pdf";
}
else
{
entry.Image = ConcurClient.GetEntryReceiptImageFromMetaData(entry);
}
}
else
{
entry.Type = ReportEntryType.Egencia;
entry.Path = $"{pdfPath}\\{entry.Key}.pdf";
}
report.Entries.Add(entry);
}
catch (Exception e)
{
report.HasError = true;
ErrorLogger.LogError(!string.IsNullOrEmpty(key) ? key : $"Row# {i}", e.Message + e.StackTrace);
}
//If We have not stored this Report, store it.
if (reports.All(q => q.Id != report.Id))
{
reports.Add(report);
}
}
Console.WriteLine("Finished Processing Excel files. Generating PDF Reports.");
var cont = true;
foreach (var report in reports)
{
if (report.HasError)
{
cont = false;
continue;
}
Console.WriteLine($"Generating PDF for Invoice#{report.Id}");
GenerateReportPdf(report, outputPath);
}
if (!cont || reports.Any(q => q.HasError))
{
ErrorLogger.SendErrorEmail(numSuccess, numFail, reports);
Console.WriteLine("There were errors with generating the PDFs. Please See Error Email. Press enter to continue and upload and attach all generated PDFs or Close the program to abort.");
Console.ReadLine();
}
else
{
Console.WriteLine("All PDFs Generated without errors. Please press enter to upload and attach all generated PDFs.");
Console.ReadLine();
}
var files = Directory.GetFiles(outputPath, "*-*.pdf", SearchOption.TopDirectoryOnly);
Console.WriteLine($"{files.Length} Files to Process.");
foreach (var file in files)
{
count++;
var filename = Path.GetFileName(file);
if (string.IsNullOrEmpty(filename))
{
ErrorLogger.LogError(filename, $"Processing PDF {count}/{files.Length} - File was empty");
continue;
}
var invoiceNum = filename.Split(' ')[0];
Console.WriteLine($"Processing PDF {count}/{files.Length} - Invoice# {invoiceNum}");
if (!IntacctClient.UploadAttachment(file))
{
continue;
}
numSuccess++;
}
numFail = count - numSuccess;
if (numFail > 0 || ErrorLogger.HasErrors())
{
ErrorLogger.SendErrorEmail(numSuccess, numFail, reports);
}
Console.WriteLine($"Processing Finished. {numSuccess} Attachments Uploaded. {numFail} Attachments Failed. Press any Enter to Close.");
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public static bool UploadAttachment(string path)
{
try
{
var filename = Path.GetFileName(path);
if (string.IsNullOrEmpty(filename))
{
return(false);
}
var invoiceNum = filename.Split(' ')[0];
var attachmentsId = $"Att {invoiceNum}";
var attachmentsName = filename.Split('.')[0];
var bytes = File.ReadAllBytes(path);
if (bytes.Length == 0)
{
ErrorLogger.LogError(filename, "File Was Empty");
return(false);
}
//Check to see if the Invoice Already Exists, and grab the invoiceRecordNo if it does.
if (!IntacctClient.InvoiceExists(invoiceNum, out var invoice))
{
return(false);
}
var invoiceRecordNo = invoice.Element("RECORDNO")?.Value;
if (invoiceRecordNo == null)
{
return(false);
}
if (!IntacctClient.GetInvoice(Convert.ToInt32(invoiceRecordNo), invoiceNum, out invoice))
{
return(false);
}
var invoiceLineItem = invoice.Element("SODOCUMENTENTRIES")?.Element("sodocumententry");
var salesInvoiceDocumentId = invoice.Element("DOCID")?.Value;
//Upload the Attachments
if (!IntacctClient.UploadAttachment(attachmentsId, attachmentsName, invoiceNum, path))
{
return(false);
}
//Update the Invoice to have the new Attachment
if (!IntacctClient.UpdateInvoice(salesInvoiceDocumentId, attachmentsId, invoiceNum, invoiceLineItem))
{
return(false);
}
//Move the succesfully Attached Invoice to the "Attached" Directory
File.WriteAllBytes($"{path}\\Attached\\{filename}", bytes);
File.Delete(path);
}
catch (Exception e)
{
Console.WriteLine($"There was an error processing File:{path} - {e}");
return(false);
}
return(true);
}
