450 lines
18 KiB
C#
450 lines
18 KiB
C#
using System;
|
|
using System.IO;
|
|
using System.Collections.Generic;
|
|
using System.Globalization;
|
|
using Spire.Xls;
|
|
using System.Reflection;
|
|
using System.Threading.Tasks;
|
|
using Renci.SshNet;
|
|
|
|
namespace bdf
|
|
{
|
|
public class Taxes // Stores by-province taxes for Invoices
|
|
{
|
|
public double PST { get; set; }
|
|
public double GST { get; set; }
|
|
public double HST { get; set; }
|
|
|
|
public Taxes(double value1, double value2, double value3)
|
|
{
|
|
this.PST = value1;
|
|
this.GST = value2;
|
|
this.HST = value3;
|
|
}
|
|
|
|
public Taxes(Taxes tax, double value1, double value2, double value3)
|
|
{
|
|
this.PST = tax.PST + value1;
|
|
this.GST = tax.GST + value2;
|
|
this.HST = tax.HST + value3;
|
|
}
|
|
|
|
public double Totaled()
|
|
{
|
|
return this.PST + this.GST + this.HST;
|
|
}
|
|
|
|
}
|
|
|
|
public class InvoiceCell // Invoice Cells from Invoice Template
|
|
{
|
|
public String Account { get; set; } = "I2"; // "Account"+ (also I40)
|
|
public String Date { get; set; } = "I3"; // I3 (also I41)
|
|
public String Invoice { get; set; } = "I4"; //I4 "Invoice" (also I42)
|
|
public String Total_Charges { get; set; } = "I14"; //I14 (also I44)
|
|
|
|
public String Account2 { get; set; } = "I40"; // "Account"+
|
|
public String Date2 { get; set; } = "I41"; // I3
|
|
public String Invoice2 { get; set; } = "I42"; //I4 "Invoice"
|
|
public String Total_Charges2 { get; set; } = "I44"; //I14
|
|
|
|
public String Addr_Dept { get; set; } = "C7";
|
|
public String Addr_Client { get; set; } = "C8"; //Maps to D7
|
|
public String Addr_Street { get; set; } = "C9"; //Maps to D8
|
|
public String Addr_City { get; set; } = "C10"; //Maps to D9
|
|
public String Addr_PC { get; set; } = "C11"; //Maps to D10
|
|
|
|
public String SrvAddr_1 { get; set; } = "D7";
|
|
public String SrvAddr_2 { get; set; } = "D8"; //Maps to D7
|
|
public String SrvAddr_3 { get; set; } = "D9"; //Maps to D8
|
|
public String SrvAddr_4 { get; set; } = "D10"; //Maps to D9
|
|
|
|
public String BackBill_Title { get; set; } = "C26"; //for SIP
|
|
public String BackBill_Charges { get; set; } = "H26";//for SIP
|
|
|
|
public String _Hidden_SO { get; set; } = ""; //for SIP
|
|
public String _Hidden_Acct { get; set; } = ""; //for SIP
|
|
|
|
public String _Hidden_Addr1 { get; set; } = ""; //for SIP
|
|
public String _Hidden_Addr2 { get; set; } = ""; //for SIP
|
|
public String _Hidden_Addr3 { get; set; } = ""; //for SIP
|
|
public String _Hidden_Addr4 { get; set; } = ""; //for SIP
|
|
|
|
public Dictionary<string, Taxes> TX { get; set; } = new Dictionary<string, Taxes> {}; // format of "ON" pst,gst,hst
|
|
|
|
public String CCI { get; set; } = "C12"; // C12 "Canada Contract Identifier"
|
|
|
|
public String PaperFee { get; set; } = "H17";
|
|
public String LateCharge { get; set; } = "H18";
|
|
public String Service_Charges { get; set; } = "I19";
|
|
public String UsageCharges { get; set; } = "H20";
|
|
public String TotalUsageCharges { get; set; } = "I21";
|
|
public String DataMonthlyCharges { get; set; } = "H23";
|
|
public String DataNon_RecurringCharges { get; set; } = "H24";
|
|
public String DataTotalOtherCharges { get; set; } = "H25";
|
|
public String Data_Services { get; set; } = "I27";
|
|
public String TaxON_PST { get; set; } = "F29";
|
|
public String TaxON_GST { get; set; } = "G29";
|
|
public String TaxON_HST { get; set; } = "H29";
|
|
public String TaxPQ_PST { get; set; } = "F30";
|
|
public String TaxPQ_GST { get; set; } = "G30";
|
|
public String TaxPQ_HST { get; set; } = "H30";
|
|
public String Taxes { get; set; } = "I31";
|
|
}
|
|
|
|
public class PDF
|
|
{
|
|
|
|
public static Workbook LoadEmbeddedWorkbook(string resourceName)
|
|
{
|
|
// Get the assembly where the resource is stored
|
|
Assembly asm = Assembly.GetExecutingAssembly();
|
|
|
|
// Fully-qualified resource name (namespace + filename)
|
|
Stream stream = asm.GetManifestResourceStream(resourceName);
|
|
|
|
if (stream == null)
|
|
throw new Exception("Embedded resource not found: " + resourceName);
|
|
|
|
Workbook wb = new Workbook();
|
|
wb.LoadFromStream(stream); // FreeSpire.XLS supports this
|
|
|
|
stream.Close();
|
|
return wb;
|
|
}
|
|
|
|
// this version creates PDF invoice with 1 line per type of provincial taxes, first line is 29, line 30 in template must be handled and line 31 has the totals
|
|
public static MemoryStream customInvoiceAsMS_taxes(ref Dictionary<string, string> CellData, ref InvoiceCell ic)
|
|
{
|
|
MemoryStream ms = new MemoryStream();
|
|
Workbook workbook = new Workbook();
|
|
|
|
string fontDirectory = AppDomain.CurrentDomain.BaseDirectory; // for non Windows systems use the .exe folder
|
|
string[] fontDirectories = { fontDirectory };
|
|
/*
|
|
foreach( var kvp in ic.TX)
|
|
{
|
|
Console.WriteLine("TX: {4} - {3} PST={0:N2} GST={1:N2} HST={2:N2} TOTAL={5:N2}", kvp.Value.PST, kvp.Value.GST, kvp.Value.HST, kvp.Key, CellData[ic.Invoice],kvp.Value.Totaled());
|
|
}
|
|
*/
|
|
workbook = LoadEmbeddedWorkbook("BDF.Embedded.template.xlsx");
|
|
|
|
int dpi = 600;// image conversion resolution
|
|
workbook.ConverterSetting.XDpi = (dpi);
|
|
workbook.ConverterSetting.YDpi = (dpi);
|
|
workbook.ConverterSetting.JPEGQuality = 99;
|
|
|
|
workbook.CustomFontFileDirectory = fontDirectories;
|
|
|
|
foreach (var cell in CellData)
|
|
{
|
|
if (cell.Key != "")
|
|
workbook.Worksheets[0].Range[cell.Key].Text = cell.Value;
|
|
|
|
} // end foreach
|
|
|
|
// Now we modify the TAX lines starting on row 29.
|
|
// Col C - DataPROV ColF/G/H - PST/GST/HST and Col I for total
|
|
Worksheet sheet = workbook.Worksheets[0];
|
|
double TotalTax = 0;
|
|
int add_row = 29; // these are Excel 1-based
|
|
int tot_row = 30;
|
|
|
|
// Add in correct number of rows for pending data
|
|
for (int i = 1; i<ic.TX.Count; i++)
|
|
{
|
|
Logger.Log(3,"add={0} tot={1} count={2} i={3}", add_row, tot_row, ic.TX.Count, i);
|
|
InsertAndCopyRow(sheet, add_row, add_row + 1);
|
|
tot_row++;
|
|
sheet.DeleteRow(tot_row+1);
|
|
}
|
|
|
|
foreach (var prov in ic.TX)
|
|
{
|
|
sheet[add_row, 3].Value = "Data-" + prov.Key;
|
|
sheet[add_row, 6].NumberValue = prov.Value.PST;
|
|
sheet[add_row, 7].NumberValue = prov.Value.GST;
|
|
sheet[add_row, 8].NumberValue = prov.Value.HST;
|
|
TotalTax += prov.Value.Totaled();
|
|
Logger.Log(3, "row={0} amt={1} tot={2}", add_row, TotalTax, tot_row);
|
|
add_row++;
|
|
}
|
|
sheet[tot_row, 9].NumberValue = TotalTax;
|
|
|
|
workbook.ConverterSetting.SheetFitToPage = true;
|
|
|
|
ExcelDocumentProperties edp = new ExcelDocumentProperties { };
|
|
edp.Title = "BDF Invoice for " + CellData[ic.Account];
|
|
edp.Subject = CellData[ic.Invoice];
|
|
edp.Author = bdf.fullUser;
|
|
edp.Comments += "\n" + DateTime.Now.ToString();
|
|
edp.ApplyTo(workbook);
|
|
|
|
//Load ARIAL.TTF font to working dir if needed
|
|
try
|
|
{
|
|
workbook.SaveToStream(ms, FileFormat.PDF);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
bdf.ExtractResourceToFile("BDF.Embedded.arial.ttf", (AppDomain.CurrentDomain.BaseDirectory + "./arial.ttf")); // arial.ttf is needed to render the PDF
|
|
workbook.SaveToStream(ms, FileFormat.PDF);
|
|
Logger.Log(1, "Error Rendering PDF Invoice", e.Message);
|
|
}
|
|
return ms;
|
|
}
|
|
|
|
public static void InsertAndCopyRow(Worksheet sheet,int sourceRowIndex,int targetRowIndex)
|
|
{
|
|
sheet.InsertRow(targetRowIndex);
|
|
|
|
int lastColumn = sheet.LastColumn;
|
|
|
|
for (int col = 1; col <= lastColumn; col++)
|
|
{
|
|
CellRange sourceCell = sheet.Range[sourceRowIndex, col];
|
|
CellRange targetCell = sheet.Range[targetRowIndex, col];
|
|
|
|
// Copy value
|
|
targetCell.Value2 = sourceCell.Value2;
|
|
|
|
// Copy formula if present
|
|
if (!string.IsNullOrEmpty(sourceCell.Formula))
|
|
targetCell.Formula = sourceCell.Formula;
|
|
|
|
// Copy number format
|
|
targetCell.NumberFormat = sourceCell.NumberFormat;
|
|
|
|
// Copy font properties
|
|
targetCell.Style.Font.FontName = sourceCell.Style.Font.FontName;
|
|
targetCell.Style.Font.Size = sourceCell.Style.Font.Size;
|
|
targetCell.Style.Font.IsBold = sourceCell.Style.Font.IsBold;
|
|
targetCell.Style.Font.IsItalic = sourceCell.Style.Font.IsItalic;
|
|
targetCell.Style.Font.Color = sourceCell.Style.Font.Color;
|
|
|
|
// Copy fill
|
|
targetCell.Style.Color = sourceCell.Style.Color;
|
|
|
|
// Copy alignment
|
|
targetCell.Style.HorizontalAlignment = sourceCell.Style.HorizontalAlignment;
|
|
targetCell.Style.VerticalAlignment = sourceCell.Style.VerticalAlignment;
|
|
|
|
// Copy borders
|
|
targetCell.Style.Borders[BordersLineType.EdgeLeft].LineStyle =
|
|
sourceCell.Style.Borders[BordersLineType.EdgeLeft].LineStyle;
|
|
|
|
targetCell.Style.Borders[BordersLineType.EdgeRight].LineStyle =
|
|
sourceCell.Style.Borders[BordersLineType.EdgeRight].LineStyle;
|
|
|
|
targetCell.Style.Borders[BordersLineType.EdgeTop].LineStyle =
|
|
sourceCell.Style.Borders[BordersLineType.EdgeTop].LineStyle;
|
|
|
|
targetCell.Style.Borders[BordersLineType.EdgeBottom].LineStyle =
|
|
sourceCell.Style.Borders[BordersLineType.EdgeBottom].LineStyle;
|
|
}
|
|
|
|
// Copy row height
|
|
sheet.SetRowHeight(
|
|
targetRowIndex,
|
|
sheet.GetRowHeight(sourceRowIndex));
|
|
}
|
|
|
|
public static MemoryStream customInvoiceAsMS(ref Dictionary<string, string> CellData)
|
|
{
|
|
MemoryStream ms = new MemoryStream();
|
|
Workbook workbook = new Workbook();
|
|
|
|
string fontDirectory = AppDomain.CurrentDomain.BaseDirectory; // for non Windows systems use the .exe folder
|
|
string[] fontDirectories = { fontDirectory };
|
|
|
|
workbook = LoadEmbeddedWorkbook("BDF.Embedded.template.xlsx");
|
|
|
|
int dpi = 600;// image conversion resolution
|
|
workbook.ConverterSetting.XDpi = (dpi);
|
|
workbook.ConverterSetting.YDpi = (dpi);
|
|
workbook.ConverterSetting.JPEGQuality = 99;
|
|
|
|
workbook.CustomFontFileDirectory = fontDirectories;
|
|
|
|
foreach (var cell in CellData)
|
|
{
|
|
if ( cell.Key != "" )
|
|
workbook.Worksheets[0].Range[cell.Key].Text = cell.Value;
|
|
|
|
} // end foreach
|
|
|
|
workbook.ConverterSetting.SheetFitToPage = true;
|
|
|
|
//Load ARIAL.TTF font to working dir if needed
|
|
try
|
|
{
|
|
workbook.SaveToStream(ms, FileFormat.PDF);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
bdf.ExtractResourceToFile("BDF.Embedded.arial.ttf", (AppDomain.CurrentDomain.BaseDirectory + "./arial.ttf")); // arial.ttf is needed to render the PDF
|
|
workbook.SaveToStream(ms, FileFormat.PDF);
|
|
Logger.Log(1, "Error Rendering PDF Invoice", e.Message);
|
|
}
|
|
return ms;
|
|
}
|
|
|
|
public static void customInvoiceSaveFile(string type, string trace, MemoryStream ms)
|
|
{
|
|
ms.Position = 0;
|
|
string svc_path = bdf.upload ? bdf.base_path + "BDF_Archive" + Path.DirectorySeparatorChar : "";
|
|
string pdf_name = bdf.upload ? type.ToUpper() : "." + Path.DirectorySeparatorChar + type.ToUpper() + "_INV" + trace + ".pdf";
|
|
if (!bdf.FileIsWritable(svc_path + pdf_name)) return;
|
|
|
|
File.WriteAllBytes(svc_path + pdf_name, ms.ToArray());
|
|
Logger.Log(0, "PDF written! ({0})", svc_path + pdf_name);
|
|
}
|
|
|
|
public static void customInvoiceSFTPUpload(string type, string trace, MemoryStream ms)
|
|
{
|
|
//string pdf_name = bdf.upload ? type.ToUpper() : "." + Path.DirectorySeparatorChar + type.ToUpper() + "_INV" + trace + ".pdf";
|
|
string pdf_name = type.ToUpper() + Path.DirectorySeparatorChar + type.ToUpper() + "_INV" + trace + ".pdf";
|
|
{
|
|
Logger.Log(0, " Initiating {0}-INV upload to EFT server...", type);
|
|
try
|
|
{
|
|
//using (var ms = new MemoryStream())
|
|
{
|
|
try
|
|
{
|
|
sftp.Upload_Stream(ms, "Outbox" + Path.DirectorySeparatorChar + pdf_name, bdf.sshDetails);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Logger.Log(0, "INV SFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
|
|
return;
|
|
}
|
|
//Logger.Log(" Invoice uploaded.");
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Logger.Log(0, "INVSFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
|
|
return;
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
public static void customInvoice_orig(string type, string trace, ref Dictionary<string, string> CellData)
|
|
{
|
|
//string fontDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "");
|
|
string fontDirectory = AppDomain.CurrentDomain.BaseDirectory; // for non Windows systems use the .exe folder
|
|
//string fontDirectory2 = @"C:\\Windows\\Fonts\\";
|
|
//string[] fontDirectories = { fontDirectory, fontDirectory2 };
|
|
string[] fontDirectories = { fontDirectory };
|
|
|
|
Workbook workbook = new Workbook();
|
|
|
|
workbook = LoadEmbeddedWorkbook("BDF.Embedded.template.xlsx");
|
|
|
|
int dpi = 600;// image conversion resolution
|
|
workbook.ConverterSetting.XDpi = (dpi);
|
|
workbook.ConverterSetting.YDpi = (dpi);
|
|
workbook.ConverterSetting.JPEGQuality = 99;
|
|
|
|
// Create PdfSaveOptions instance
|
|
//PdfSaveOptions options = new Converter.PdfSaveOptions();
|
|
|
|
// Set a higher image resolution (e.g., 300 dpi for print quality)
|
|
//options.ImageResolution = 600; // You can experiment with values like 144, 220, or 300
|
|
|
|
workbook.CustomFontFileDirectory = fontDirectories;
|
|
|
|
// 4. (Optional) Work with the workbook, e.g., modify a cell
|
|
foreach (var cell in CellData)
|
|
{
|
|
// workbook.Worksheets[0].Range["A1"].Text = "Data loaded from stream!";
|
|
workbook.Worksheets[0].Range[cell.Key].Text = cell.Value;
|
|
|
|
} // end foreach
|
|
|
|
//Spire.Xls.Worksheet sheet = workbook.Worksheets[0];
|
|
//sheet.PageSetup.PrintArea = "B1:E6";
|
|
|
|
// Optional: Set worksheets to fit to page when converting
|
|
workbook.ConverterSetting.SheetFitToPage = true;
|
|
|
|
InvoiceCell ic = new InvoiceCell { };
|
|
//string prefix = CellData[ic.Invoice].Substring(0, 16).Replace(' ', '_');
|
|
//string pdf_name = bdf.output_path + type.ToUpper() + "_" + prefix + trace + ".pdf";
|
|
string svc_path = bdf.upload ? bdf.base_path + "BDF_Archive" + Path.DirectorySeparatorChar : "." + Path.DirectorySeparatorChar;
|
|
string pdf_name = bdf.upload ? type.ToUpper() : "." + Path.DirectorySeparatorChar + type.ToUpper() + "_INV" + trace + ".pdf";
|
|
if (!bdf.FileIsWritable(svc_path+pdf_name)) return;
|
|
|
|
workbook.SaveToFile(svc_path+pdf_name, FileFormat.PDF);
|
|
Logger.Log(0, "PDF written! ({0})", svc_path+pdf_name);
|
|
|
|
// are we uploading to EFT SFTP server?
|
|
if (bdf.upload)
|
|
{
|
|
Logger.Log(0, " Initiating {0}-INV upload to EFT server...", type);
|
|
try
|
|
{
|
|
using (var ms = new MemoryStream())
|
|
{
|
|
try
|
|
{
|
|
workbook.SaveToStream(ms, FileFormat.PDF); //Save as PDF
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Logger.Log(" Invoice failed to format for SFTP. ({0})", e.Message);
|
|
return;
|
|
}
|
|
|
|
try
|
|
{
|
|
sftp.Upload_Stream(ms, "Outbox" + Path.DirectorySeparatorChar + pdf_name, bdf.sshDetails);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Logger.Log(0, "INV SFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
|
|
return;
|
|
}
|
|
//Logger.Log(" Invoice uploaded.");
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
Logger.Log(0, "INVSFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
|
|
return;
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
public static string dateFormal(int dateInt)
|
|
{
|
|
//int dateInt = 20251130;
|
|
string dateString = dateInt.ToString();
|
|
|
|
// 1. Define the input format ("yyyyMMdd")
|
|
string inputFormat = "yyyyMMdd";
|
|
|
|
// 2. Parse the string into a DateTime object
|
|
|
|
DateTime dateTimeObject = DateTime.ParseExact(
|
|
dateString,
|
|
inputFormat,
|
|
CultureInfo.InvariantCulture
|
|
);
|
|
|
|
// 3. Define the desired output format ("MMMM dd, yyyy")
|
|
// MMMM = Full month name
|
|
// dd = Day of the month
|
|
// yyyy = Year
|
|
|
|
string outputFormat = "MMMM d, yyyy";
|
|
string result = dateTimeObject.ToString(outputFormat);
|
|
|
|
return result;
|
|
}
|
|
|
|
}
|
|
} |