gituser/BDF3
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
BDF3/_Main.cs
Doug Macintosh bfe37f6426 3.5.2 commit
2026-03-08 16:20:06 -04:00

2110 lines
111 KiB
C#
Raw Permalink Blame History

#define PROD_
/*
* Created by D Macintosh
* Date: 11/14/2025
* Time: 9:32 PM
*
*/
using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using System.Text;
using System.Reflection;
using System.Net;
using System.Text.RegularExpressions;
using System.Linq;
using Spire.Xls;
using System.Data;
using System.Security.Principal;
using System.Globalization;
namespace bdf
{
class bdf
{
public static string product = "BDF";
public static string version = "3.5.2";
public static string fullUser = WindowsIdentity.GetCurrent().Name; // \\DOMAIN\User.Id for NTLM auth for Megatool
//EFT Upload Credentials
public static bool upload = false;
public static sftp.ConnectionDetails sshDetails = new sftp.ConnectionDetails
{
host = "dev.mft.rogers.com",
ppkFile = "BDF.Embedded.private_passphr.ppk", //Add "BDR.Embedded." to filename if embedded
passPhrase = "Rogers1!",
username = "DEV_APP_BDF",
password = "DEV_APP_BDF"
};
public static string S4LT = "Test1234";
public static Dictionary<string, int> BAN = new Dictionary<string, int> { {"LDF", 857412 }, {"WAV", 864507 }, {"SIP", 858701 } };
public static Dictionary<string, string> CCI = new Dictionary<string, string> { { "LDF", "K00019754" }, { "WAV", "K00019779" }, { "SIP", "K000014497" } };
public static int debug = 0;
public static int mode = 1;
public static int major_errs = 0;
public static int MAX_RETRY = 3;
public static int month_offset = 1; // by default prep the bill for the upcoming bill cycle, ie end of current month is invoice date
public static int webMaxWait = 1; //max wait time in minutes for webaccess
public static bool logappend = true;
public static string logname = "logfile.txt";
public static StreamWriter outfile = new StreamWriter(@"" + logname, logappend);
public static bool reportappend = false;
public static string reportname = "bdf_report.txt";
public static StreamWriter reportfile = new StreamWriter(@"" + reportname, reportappend);
public static string domain = "RCI";
public static string username = "";
public static string password = "";
public static CredentialCache cache = new CredentialCache();
public static string output_path = "." + Path.DirectorySeparatorChar;
public static string base_path = "." + Path.DirectorySeparatorChar;
public static string inputFile_path = "Inputs" + Path.DirectorySeparatorChar;
public static Dictionary<string, string> ToDo = new Dictionary<string, string> { };
public static string token = "";
public static string filename = "output.xlsx"; //we'll change this later to be month specific
public static string InsisSuffix = "_Megatool.xlsx";
public static string overrideSuffix = "_override.txt";
public static bool reuse = false;
public static bool archive = false;
public static double backBillAmt = 0;
public static string SIP_Accounts = "SIP_Accounts.txt";
public static List<string> todo = new List<string> { };
public static StringBuilder adminLog = new StringBuilder();
public static int adlvl = 2;
/* see https://denhamcoder.net/2018/08/25/embedding-net-assemblies-inside-net-assemblies/ */ // still needs external copy during compilation!
// Loads NewtonsoftJson lib into memory as .dll
public static Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs r_args)
{
string name = new AssemblyName(r_args.Name).Name;
name = product + ".Embedded." + name + ".dll";
Logger.Log(5, "...loading assy: {0}", name);
try
{
using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(name))
{
var assemblyData = new Byte[stream.Length];
stream.Read(assemblyData, 0, assemblyData.Length);
return Assembly.Load(assemblyData);
}
}
catch (Exception e)
{
Logger.Log(0, "DLL Failure: {2} = {0}:{1}.", e.Message, e.InnerException.Message, name);
return null;
}
}
//var copy = original.Clone<MyClass>();
public T ShallowClone<T>() where T : class
{
return (T)this.MemberwiseClone();
}
public static void Main(string[] args)
{
// Load Embedded Resources
try
{
AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;
}
catch (Exception e)
{
Logger.Log(0, "DLL: {0}:{1}.", e.Message, e.InnerException.Message);
}
//foreach (var resource in Assembly.GetExecutingAssembly().GetManifestResourceNames()) Console.WriteLine("Resource: " + resource);
Main2(args);
}
public static async void Main2(string[] args)
{
// Clears 'async' compiler error
await System.Threading.Tasks.Task.CompletedTask;
// Enforce TLS1.2 Sep2017
ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072 | (SecurityProtocolType)768 | (SecurityProtocolType)192;
// Logging file
Arguments myargs = new Arguments(args);
myArgs.ProcessSwitches(myargs); // Process all the command line switches.
CookieContainer cookiejar = new CookieContainer();
if (upload)
{
outfile.Close();
outfile.Dispose();
outfile = new StreamWriter(@"" + base_path + "BDF_Archive" + Path.DirectorySeparatorChar + logname, logappend);
}
//outfile = new StreamWriter(@"" + logname, logappend); // defined earlier so logging works in .dll loading
outfile.AutoFlush = true;
//Suppress system error text messages?
//Console.SetError(TextWriter.Null);
// Opening Banner
string ban2 = product + " v" + version + " (c) 2026 Doug Macintosh";
ban2 = "\u2551 " + ban2 + " \u2551";
string ban1 = "\u2554";
for (int i = 2; i < ban2.Length; i++) { ban1 += "\u2550"; }
ban1 += "\u2557";
string ban3 = "\u255a";
for (int i = 2; i < ban2.Length; i++) { ban3 += "\u2550"; }
ban3 += "\u255d";
Logger.Log(ban1); Logger.Log(ban2); Logger.Log(ban3);
Logger.Log("--- call (613)697-9178 for support ---");
Logger.Log(product, "INIT: Tool version {0} initiated {1}", version, DateTime.Now);
// ***MAIN -------------------------------------------------------------------------------------------------------------------------------
bool LoggedIn = false; // are we authenticated to Megatool?
foreach (string task in todo) // tasks are some combination of LDF/WAV/SIP
{
Logger.Log(0, "==================================>>>");
Logger.Log(0, "Starting to process the {0} BDF...", task);
string svc_path = upload ? base_path + "BDF_Archive" + Path.DirectorySeparatorChar : "";
// ***SO Files -------------------------------------------------------------------------------------------------------------------------------
//Load and convert all XLS? files that start with 'ROGERS_(task)_SO*' in the (task) subdirectory
CXLFile.S_getXLS(task, (base_path + ToDo[(task + "path")]));
Logger.Log(0, "{0} Service Order Workbooks returned.", CXLFile.S_ServiceOrders.Count);
// ***Megatool -------------------------------------------------------------------------------------------------------------------------------
if (myargs.Exists("redo"))
{
if (System.IO.File.Exists(inputFile_path + Path.DirectorySeparatorChar + task + InsisSuffix))
{
if (!FileIsWritable(inputFile_path + Path.DirectorySeparatorChar + task + InsisSuffix)) return;
if (!SafeDeleteFile(inputFile_path + Path.DirectorySeparatorChar + task + InsisSuffix))
{
Logger.Log(0, "Unable to delete existing {0}, is it open in Excel?", task + InsisSuffix);
reuse = false;
// User interaction to abort?
}
else
{
Logger.Log(1, " -deleted existing {0} intermediate file.", task + InsisSuffix);
}
}
}
if (myargs.Exists("reuse"))
{
if (!System.IO.File.Exists(inputFile_path + Path.DirectorySeparatorChar + task + InsisSuffix))
{
Logger.Log(0, "Did not find an existing {0} Forcing rebuild...", task + InsisSuffix);
reuse = false;
}
else { reuse = true; }
}
// Megatool subsection, gets mapping between SO's and SCHEDA's, create "(task)_Megatool.xlsx"
//#var wbk = new XLWorkbook();
var wbk = new Spire.Xls.Workbook();
// Add a worksheet
//#var insisMap = wbk.AddWorksheet(task+InsisSuffix.Split('.')[0]); // add empty worksheet 'InsisMap'
var insisMap = wbk.CreateEmptySheet(task + InsisSuffix.Split('.')[0]); // add empty worksheet 'InsisMap'
BDF.cleanSheets(ref wbk); // get rid of the 3 default sheets
//MEGATOOL XLSX path and filename
string path = inputFile_path + task + InsisSuffix;
//If less than 24h old, or -reuse switch used, use an existing map file
if ((System.IO.File.Exists(path)) && (System.IO.File.GetLastWriteTime(path).DayOfYear == DateTime.Today.DayOfYear)
|| reuse)
{
try
{
Logger.Log(0, "Loading previous Megatool mapping file ({0})...", path);
if (!FileIsWritable(path)) return;
Logger.Log(task, "WARN: Re-using previously created Megatool file");
wbk.OpenPassword = S4LT;
wbk.LoadFromFile(path);
Logger.Log(3, "Megatool mapping file has {0} entries.", wbk.Worksheets[0].LastRow - 1);
}
catch
{
Logger.Log(0, "Please verify you do not have ({0}) open in Excel and retry.", path);
return;
}
}
else
{
if (!LoggedIn) //authenticate to Megatool
{
int tries = 1;
// Megatool login procedure
do
{
Logger.Log(0, "Initiating connection...");
tries++;
Logger.Log("** Password is required to access Megatool. You must be on the Corporate Lan/VPN.**");
while (password == "")
{
Logger.Log(0, "Please enter the password for ({0}) and hit Return.", fullUser);
password = ReadPassword();
}
try
{
LoggedIn = MegaT.Auth(username, password, ref cookiejar);
if (!LoggedIn) { Logger.Log(1, " retrying {0}", tries); }
}
catch (Exception)
{
Logger.Log("Exception attempting to login to MegaTool");
if (!LoggedIn) { Logger.Log(1, " retrying {0}", tries); }
}
}
while (!LoggedIn && (tries <= MAX_RETRY));
if (!LoggedIn)
{
Logger.Log("Exiting due to inability to login to MegaTool website");
return;
}
}
// Build TA->SO Dictionary
Dictionary<string, string> ta = new Dictionary<string, string> { };
foreach (Spire.Xls.Workbook wb in CXLFile.S_ServiceOrders) // List of XL workbooks passed from XLImport
{
Spire.Xls.Worksheet ws1 = wb.Worksheets[0]; //generic 1st worksheet
Spire.Xls.Worksheet ws2 = wb.Worksheets[1]; //generic 2nd worksheet
string _so = ws2[2, 1].Value.ToString(); // This is our SO#
string CCI = ws1[2, 7].Value.ToString(); // R000XXXXXX or K000 for SIP? -
// also CCIs are different, one overarching for all LDF/WAV/SIP plus one for indiv SO
//Format CCI as R + 9 digits, left zero padded
string _cci = CCI;
_cci = ("000000000" + Int32.Parse(_cci.Substring(1)));
CCI = CCI.Substring(0, 1) + _cci.Substring(_cci.Length - 9); // Preserve R/K+000123456
try
{
//Console.WriteLine("{0}:{1}", CCI, _so);
if ( !ta.ContainsKey(CCI))
ta.Add(CCI, _so);
}
catch (Exception e)
{
Logger.Log(0,"SO-TA table mapping error. {0}", e.Message);
}
}
Logger.Log(1, "Added {0} TA->SO mappings", ta.Count);
// ***SCHED Files -------------------------------------------------------------------------------------------------------------------------------
List<string> active = new List<string> { };
//Dictionary<string, double> sch2mrr = new Dictionary<string, double> { };
List<string> scheds = new List<string> { };
// GET ALL GCNSLDFXXX SCHEDS FROM INSIS
MegaT.GetSchedAs( ToDo[(task+"prefix")], ref scheds, ref cookiejar);
Logger.Log(1, "Found {0} candidate {1} SCHEDAs", scheds.Count, ToDo[(task + "prefix")] );
// FILTER OUT ALL XL/DISCONNECTED SCHEDS
foreach (string sched in scheds)
{
bool inactive = MegaT.SchedXL(sched, ref cookiejar);
if (!inactive)
{
active.Add(sched);
//sch2mrr.Add(sched, MegaT.getMRR(sched, ref cookiejar)); // for each active SCH, get current MRR from main page
}
else
{
Logger.Log(5, " {0} ({1})", sched, inactive ? "inactive" : "active");
}
}
Logger.Log(0, "Found {0} active {1}xxx SCHEDAs", active.Count, ToDo[(task + "prefix")]);
if (active.Count == 0) // no valid SCHEDs so we're done here!
{
Logger.Log(0, "ERROR: No active {0} SCHEDAs found. Exiting as no point proceding further.", task);
Logger.Log(task, "ERROR: No active SCHEDAs found. Quitting this task as no point proceding further.");
continue; // return;
}
// -------------------------------------
// OBTAIN DETAIL INFORMATION FOR ALL ACTIVE SCHEDS FROM 'SCHEDULE', SO MOST IMPORTANT, BUT GRAB CONTRACT, TA ETC AS NEEDED
var dt = new DataTable();
// Build our dictionary table headers with a 'dummy' call using the to-do_task_type for parms, this is extensible for coding, we can add/remove parms as needed
foreach ( var k in MegaT.GetParms(task, ref cookiejar))
{
dt.Columns.Add( k.Key, typeof(String) ); // order of entry is important when referencing by row/cell
//Console.WriteLine("Adding: " + k.Key);
}
List<string> so_found = new List<string> { };
// Look for override data in [TASK]_override.txt
Dictionary<string, string> overrides = getOverrides(task); //using SO as key
foreach (string sched in active)
{
Dictionary<string, string> parms = MegaT.GetParms(sched, ref cookiejar); // scrape megatool for current SCHEDA <-> SO info, Schedule Details view
// Create a new row
var row = dt.NewRow();
row["SCHED"] = sched;
// Fill row with dictionary values
foreach (var kvp in parms)
{
//Console.WriteLine(" * s={0} p={1} v={2}", sched, kvp.Key, kvp.Value);
if (kvp.Value.Length >= 3)
{
if ((kvp.Value.Substring(0, 3)) == "R00")
{
row["TA"] = kvp.Value ?? "";
}
else if ((kvp.Value.Substring(0, 3)) == "K00")
{
row["CONTRACT"] = kvp.Value ?? "";
}
else
{
row[kvp.Key] = kvp.Value ?? "";
}
if (kvp.Key.Equals("SO")) so_found.Add(kvp.Value); // track these to see if we need to add unmatched SO's to megatool file
}
else
{
row[kvp.Key] = kvp.Value ?? "";
}
}
//Console.WriteLine("c r:{0} s:{1}+", row["MRR"], sched);
//Try to fix bad SCHED<->SO mappings
string value = "ZZZ";
string my_so = (string)row["SO"];
string my_ta = (string)row["TA"];
if (my_so == "XXX")
{
row["SO"] = ta.TryGetValue(my_ta, out value) ? value : "XxX";
Logger.Log(1, "Fixed {0}->{1} using TA={2}", (string)row["SO"], (string)row["SCHED"], (string)row["TA"]);
Logger.Log(task, "WARN: Mapping {0}->{1} using TA={2}", (string)row["SO"], (string)row["SCHED"], (string)row["TA"]);
so_found.Add(value);
}
//else Logger.Log(0, "Unable to fix {0}->{1} mapping because of TA={2} != ROW_TA{3}", (string)row["SCHED"], (string)row["SO"], value, (string)row["TA"]);
// billing parameters based on SCHEDs
List<MegaT.INSISData> rvnuInfo = MegaT.GetRVNUDetails_bySCHED(sched, ref cookiejar);
//Extra SIP vars
if (task == "SIP") row["AQty"] = row["ACst"] = row["SQty"] = row["SCst"] = "XXX";
double sQty = 0;
double sCst = 0;
double aQty = 0;
double aCst = 0;
//Create cumulative vars
double rvnu_mrr = 0;
double rvnu_nrc = 0;
double.TryParse(row["MRR"].ToString(), out double mrr);
double.TryParse(row["NRC"].ToString(), out double nrc);
foreach (var s in rvnuInfo)
{
row["BSD"] = MegaT.ConvertDate(s.StartDate);
//Logger.Log(5, " {0} {1}",sched, s.ToString());
//Only process if it is an active billing item
if ( (string.Equals(s.BillingType.ToString(), "Add", StringComparison.OrdinalIgnoreCase))
|| (string.Equals(s.BillingType.ToString(), "Change", StringComparison.OrdinalIgnoreCase)))
{
if (string.Equals(s.Period.ToString(), "Monthly", StringComparison.OrdinalIgnoreCase))
{
Logger.Log(2, " MRR: {0:N2} x {1}", s.UnitPrice, s.Quantity);
rvnu_mrr += Convert.ToDouble(s.UnitPrice) * Convert.ToDouble(s.Quantity);
//Logger.Log(6, " rvnu_mrr = {0} {1}", s.UnitPrice, rvnu_mrr);
}
else if (string.Equals(s.Period.ToString(), "One Time", StringComparison.OrdinalIgnoreCase))
{
Logger.Log(2, " NRC: {0:N2} x {1}", s.UnitPrice, s.Quantity);
rvnu_nrc += Convert.ToDouble(s.UnitPrice) * Convert.ToDouble(s.Quantity);
//Logger.Log(6, " rvnu_nrc = {0} {1}", s.UnitPrice, rvnu_nrc);
}
if (task == "SIP")
{
if (string.Equals(s.Code.ToString(), "ACH", StringComparison.OrdinalIgnoreCase))
{
Logger.Log(2, " Access: {0:N2} x {1}", s.UnitPrice, s.Quantity);
aQty += 1;
aCst = Convert.ToDouble(s.UnitPrice);
row["AQty"] = aQty.ToString();
row["ACst"] = s.UnitPrice;
}
if (string.Equals(s.Code.ToString(), "VSS", StringComparison.OrdinalIgnoreCase))
{
sQty += Int32.Parse(s.Quantity);
sCst = Convert.ToDouble(s.UnitPrice);
row["SQty"] = sQty.ToString(); // handle multiple session Adds
row["SCst"] = s.UnitPrice;
Logger.Log(2, " Sessions: {0:N2} x {1}, Total Qty Found {2}", s.UnitPrice, s.Quantity, sQty);
}
}
}
//else it's an inactive billing amount so ignore it.
}
//Logger.Log(1, "{0} : MRR${4:N2} NRC${3:N2} SIP: Access ${1:N2} Sessions${2:N2}", sched, aQty*aCst, sQty*sCst, rvnu_nrc, rvnu_mrr);
Logger.Log(1, "{0} : MRR${1:N2} NRC${2:N2} " + ((task == "SIP") ? "SIP: Access ${1:N2} Sessions${2:N2}" : ""), sched, rvnu_mrr, rvnu_nrc, aQty * aCst, sQty * sCst );
//Update the Megatool table for MRR & NRC
{
if (rvnu_mrr > 0)
{
if (rvnu_mrr != mrr)
{
Logger.Log(5, "Warning: NRC mismatch RVNU={0} replaces SCHED={1}", rvnu_mrr, mrr);
}
row["MRR"] = rvnu_mrr.ToString("N2");
}
if (rvnu_nrc > 0)
{
if (rvnu_nrc != nrc)
{
Logger.Log(5, "Warning: NRC mismatch RVNU={0} replaces SCHED={1}", rvnu_nrc, nrc);
}
row["NRC"] = rvnu_nrc.ToString("N2");
}
}
/*CHECKs for delta between Schedule Details and main page
if (sch2mrr[sched] != 0)
{
if ((row["MRR"].ToString() != "XXX") && (Double.Parse(row["MRR"].ToString()) != sch2mrr[sched]))
{
Logger.Log(0, " -MRR discrepancy for {2} between SchedA {0} and Schedule {1} MRRs", sch2mrr[sched], row["MRR"], sched);
}
row["MRR"] = sch2mrr[sched];
}
*/
// overrides used to be here
dt.Rows.Add(row);
} // end of foreach SCHED
//ADD lines for any missing SO's/SO's not mapped to a Sched, such as would happen with 2 x SO per SCHED in SIP
//scan through S_ServiceOrders, check ws2(2,1)=SO is present in dt, if not, add new row with SO defined
Logger.Log(0, "Validating {0} SOs", so_found.Count);
try
{
foreach (Spire.Xls.Workbook SOS in CXLFile.S_ServiceOrders)
{
Worksheet s2 = SOS.Worksheets[1]; //generic 2nd worksheet
string s = s2[2, 1].Value.ToString(); // This is our SO-
if (RemoveStringKey(so_found, s)) // if megatool found the SO remove it from the manual adds
Logger.Log(2, " Removed {0}", s);
else
{
Logger.Log(0, " Inserted {0}", s);
Logger.Log(task, "WARN: SO-{0} has no associated SCHED-A and is not included. Override may be needed.", s);
var orphan_row = dt.NewRow(); // we need valid values for SO, and dummies for SCHED(6), NRC(9), AQty(10), SQty(12)
orphan_row["SO"] = s;
orphan_row["SCHED"] = "UNKNOWN";
orphan_row["BSD"] = "XXX";
orphan_row["MRR"] = "XXX";
orphan_row["NRC"] = "XXX";
if (task == "SIP")
{
orphan_row["AQty"] = "XXX";
orphan_row["SQty"] = "XXX";
}
dt.Rows.Add(orphan_row);
}
}
foreach (string _s in so_found)
{
Logger.Log(0, " Orphan {0}", _s);
if (_s != "XXX" ) Logger.Log(task, "!ERR: SO-{0} referenced by a valid SCHED-A does not exist in {1} folder.", _s, task);
}
}
catch (Exception e)
{
Logger.Log(0, "Issue finding orphan SOs ({0}:{1})", e.Message, e.InnerException.Message);
return;
}
//We have to do overrides after the manual adds
//Loop through Megatool sheet and replace as needed
try
{
foreach (DataRow row in dt.Rows)
{
// Skip deleted or detached rows
if (row.RowState == DataRowState.Deleted ||
row.RowState == DataRowState.Detached)
continue;
string so = row[0]?.ToString();
foreach (DataColumn column in dt.Columns)
{
string ivalue = "";
if (overrides.ContainsKey(so + column.ColumnName))
{
ivalue = overrides[(so + column.ColumnName)];
Logger.Log(0, " >REPLACE {0}-{1} (was:{2} now:{3})", so, column.ColumnName, row[column], ivalue);
row[column] = ivalue;
}
}
}
}
catch (Exception e)
{
Logger.Log(0, "Issue applying overrides to SOs ({0}:{1})", e.Message, e.InnerException.Message);
return;
}
// Now dump out the INSIS data into the intermediate file
//using (var wb = new XLWorkbook())
{
// Insert the DataTable starting at cell A1
insisMap.InsertDataTable(dt, true, 1, 1);
insisMap.AllocatedRange.AutoFitColumns();
var used = insisMap.AllocatedRange;
// Sort by column A inside that range (currently SO, thinking SCHED is a better idea)
// Column index 0 = first column inside sortRange = Column A
wbk.DataSorter.SortColumns.Add(0, SortComparsionType.Values, OrderBy.Ascending); //sorts = new SortColumns();
// Perform the sort
wbk.DataSorter.Sort(used);
// Apply AutoFilter to the header row (row 1)
wbk.Worksheets[0].AutoFilters.Range = used;
wbk.Worksheets[0].AutoFilters.Filter();
//Apply Table formatting
BDF.ApplyTableFormat(used);
ExcelDocumentProperties edp = new ExcelDocumentProperties { };
edp.Title = "BDF Megatool Workbook";
edp.Subject = task + " - Megatool Intermediate Data Capture";
edp.Author = bdf.fullUser;
edp.Comments += "\n" + DateTime.Now.ToString();
edp.ApplyTo(wbk);
//Encrypt intermediate file with defined salt to prevent unauthorized opening
wbk.Protect(S4LT);
// Save to file (_Megatool mapping)
if (!FileIsWritable(path)) return;
try
{
wbk.SaveToFile(path, ExcelVersion.Version2016);
}
catch
{
Logger.Log(0, "Ensure you do not have ({0}) already open!", path);
return;
}
}
}
// wbk is the Megatool worksheet
// *** -------------------------------------------------------------------------------------------------------------------------------
bool SIP = (task == "SIP");
Dictionary<string, string> PO_ACCTS = SIP ? getSIPAccountsbyPO(inputFile_path + SIP_Accounts) : null; // Dictionary mapping PO to Rogers BAN
//Date string format 202511
DateTime d = DateTime.Now;
int current = Int32.Parse(d.AddMonths(month_offset).ToString("yyyyMM"));
string monthAbbreviation = d.AddMonths(month_offset).ToString(" MMM", new CultureInfo("en-US")); // for Type X tabs
// Go to the first day of this month, then subtract 1 day
DateTime lastDayPrevMonth = new DateTime(d.Year, d.Month, 1).AddMonths(month_offset).AddDays(-1);
DateTime firstDayNextMonth = new DateTime(d.Year, d.Month, 1).AddMonths(month_offset+1);
// Format as yyyymmdd
//string invDate = lastDayPrevMonth.ToString("yyyyMMdd");
//string effDate = (new DateTime(d.Year, d.Month, 1)).ToString("yyyyMMdd");
string invDate = ( SIP ? firstDayNextMonth.ToString("yyyyMMdd") : lastDayPrevMonth.ToString("yyyyMMdd"));
Logger.Log(0, "Beginning actual BDF processing ({0})", current);
bdf.filename = task + "_BDF_" + current.ToString() + ".xlsx";
// BDF Workbook - create the correct format based on task
var workbook = BDF.CreateBDF(task);
// bdf2 is link to 2nd Tab with the individual connection details, all sheets have Type 2
var bdf2 = workbook.Worksheets[1]; // or use string name, 0-based index
bdf2.Name += monthAbbreviation;
//var bdf2 = workbook.Worksheets["BDF Type 2"]; // or use string name
// make an empty list of type BDF.Rows to be added to BDF worksheet#2 at end
var rows = new List<Row> { }; // Type 2 rows to add to sheet, currently only 1 for DF/WAV plus one more for Builds. SIP will have 3
try
{
//Process each line in each SO for unique SOs -- UPDATED TO ONLY DO 1 LINE PER SO FILE plus an NRC BUILD line if present
int k = 1; // interation count
foreach (Spire.Xls.Workbook wb in CXLFile.S_ServiceOrders) // List of XL workbooks passed from XLImport
{
// Two tabs per SO file, LDF specific details below
// /1.LDFONS_Service_Order_V2 and
// 2.LDFONS_SDP_data_V2
Spire.Xls.Worksheet ws1 = wb.Worksheets[0]; //generic 1st worksheet
Spire.Xls.Worksheet ws2 = wb.Worksheets[1]; //generic 2nd worksheet
string _so = ws2[2, 1].Value.ToString(); // This is our SO#
Logger.Log(1, "Ingesting {0} ({1}/{2})...", _so, k++, CXLFile.S_ServiceOrders.Count);
List<string> sos = new List<string> { }; //UPDATED USE 1 entry per SO found, expect 1 SO per file
string C = ws1[2, 1].Value.ToString(); //ROGERS
string CCI = ws1[2, 7].Value.ToString(); // R000XXXXXX or K000 for SIP? -
string _cci = CCI; //Format CCI as R + 9 digits, left zero padded //CCIs are different, one overarching for all LDF/WAV/SIP plus one for indiv SO
_cci = ("000000000" + Int32.Parse(_cci.Substring(1)));
CCI = CCI.Substring(0,1) + _cci.Substring(_cci.Length - 9); // Preserve R/K+000123456
//bool SIP = (task == "SIP");
// All rows of the Megatool intermediate
var _rows = wbk.Worksheets[0].AllocatedRange.Rows; // _Megatool intermediate sheet
foreach (var row in ws2.AllocatedRange.Rows) // Rows.ToArray() ) // go through Tab#2 of SO
{
if (row.Row == 1) continue;
//Console.WriteLine("a1 <{2}> ({3}) row{0} of {1}", row.Row, ws2.AllocatedRange.LastRow, ws2.GetNumber(row.Row, 1), 0);
if ( double.IsNaN(ws2.GetNumber(row.Row, 1)) ) break; // if there are blank lines in the SO then quit!
var r = new Row { Row_Number = (rows.Count + 1), Contractor = C }; //this needs to be the SO's CCI, so tab2
var r_nrc = r.Clone<Row>(); // spare copy of row 'r' if onetime found
string scid = ws2.GetStringValue(row.Row, SIP ? 26 : 28);
bool processRow = (!SIP && ( scid.Contains("Segment") || scid.Contains("OpticalWavelength") ) )||
( SIP && ( scid.Contains("NAP") || scid.Contains("SAP") || scid.Contains("DID") ) ); // these are the billable lines in BDF document
Logger.Log(5, "5-SCID:{0}:{1}", scid, rows.Count);
if ( (row.Row > 1) && processRow )
{
r.Canada_Contract_Identifier = CCI; //from tab1
r.SSC_Order_Number = (Int32)(ws2.GetNumber(row.Row, 1));
r.SSC_Version = (Int32)ws2.GetNumber(row.Row, 2);
r.Invoice_Number = invDate.Substring(4) + invDate.Substring(0, 4) + "-" + task;
r.SSC_Seq_No = (Int32)ws2.GetNumber(row.Row, SIP ? 20 : 21);
r.Quantity = (Int32)ws2.GetNumber(row.Row, SIP ? 24 : 26);
r.SDPID = ws2.GetStringValue(row.Row, SIP ? 18 : 19);
r.Department = ws2.GetStringValue(row.Row, 6);
r.Prov = ws2.GetStringValue(row.Row, SIP ? 39 : 43);
r.Billing_End_Date = Int32.Parse(ws2.GetStringValue(row.Row, 13).Replace("/", ""));
r.SCID = ws2.GetStringValue(row.Row, SIP ? 26 : 28);
r.TCID = ws2.GetStringValue(row.Row, SIP ? 29 : 32);
r.TA_Number = CCI;
r.Invoice_Period = current;
r.Period_of_Service = current;
// Feb 4
r.Contractor_Billing_Account_Number = BAN[task];
r.TA_Number = SIP ? "" : CCI;
r.PO_Number = SIP ? ws2.GetStringValue(row.Row, 67) : "";
if (SIP)
{
try
{
double t;
Double.TryParse(ws2.GetStringValue(row.Row, 67), out t);
if (t > 0)
{
r.PO_Number = t.ToString();
r.Contractor_Billing_Account_Number = Int32.Parse(PO_ACCTS[t.ToString()]); // Lookup custom sub-account for this PO
}
else
{
Logger.Log("SIP", "!ERR: SO {0} missing valid PO#.", r.SSC_Order_Number);
Logger.Log(0, "Error SO {0} missing valid PO#.", r.SSC_Order_Number);
}
}
catch (Exception e)
{
Logger.Log("SIP", "!ERR: Error mapping SIP BAN to PO={1} : {0}", e.Message);
Logger.Log(0, "Error mapping SIP BAN to PO={1} : {0}", e.Message);
}
}
//Add in Megatool data like SCHEDA<->SO mapping
//Find matching InsisMap row#
int so_row = 0;
int r0_row = 0;
int max = wbk.Worksheets[0].Rows.Length;
CellRange _srow = null;
CellRange _rrow = null;
try
{
foreach (var _row in _rows) //check each Megatool _row found in the tab represented by _rows above
{
if (_row.Row == 1) continue; //skip header row
// Match by SO# (preferred) or TA/Contract# (backup) so we get the right SCHEDA for data
if ((string.Equals(_row[_row.Row, 1].Value.ToString(), r.SSC_Order_Number.ToString(), StringComparison.OrdinalIgnoreCase)))
{
so_row = _row.Row;
_srow = _row;
}
else if (string.Equals(_row[_row.Row, 2].Value.ToString(), r.TA_Number.ToString(), StringComparison.OrdinalIgnoreCase))
{
r0_row = _row.Row;
_rrow = _row;
}
Logger.Log(6, "_row:{0} is [{3}] so_row:{1} r0_row:{2}", _row.Row, so_row, r0_row, _row[_row.Row, 1].Value);
// SO Match found, or no SO match, but we found a TA match and will use that row
if ((so_row != 0) || ((r0_row != 0) && (_row.Row == max)))
{
Logger.Log(5, "_matchrow:{0} is [{3}] so_row:{1} r0_row:{2}", _row.Row, so_row, r0_row, _row[_row.Row, 1].Value);
if (so_row == 0) // No SO match
{
_srow = _rrow;
so_row = r0_row;
}
string schA = _srow[so_row,6].Value.ToString() ;
r.CI_Name = schA;
//SO not defined
if (_srow[so_row, 1].Value.ToString() == "XXX")
{
Logger.Log(5, " {0}-SO not found in InsisMap! Linking SO ({1}) by TA/Contract", r.SSC_Order_Number, schA);
}
string mrc = _srow[so_row, 8].Value.ToString();
if ( !SIP && (mrc != "XXX") )
{
calcTaxes(mrc, "0", ref r);
}
else if (SIP) // we need to understand which SIP line item we are workign with, Access, Sessions or DID
{
double new_mrc = 0;
double cost = 0;
double qty = 0;
//Access, r.SCID contains 'NAP'
if (r.SCID.Contains("NAP"))
{
if ( !string.Equals(_srow[so_row, 10].Value.ToString(), "XXX", StringComparison.OrdinalIgnoreCase))
{
qty = Convert.ToDouble(_srow[so_row, 10].Value.ToString()); // should always be 1
cost = Convert.ToDouble(_srow[so_row, 11].Value.ToString()); // should always be $200
new_mrc = qty * cost;
if (!(qty == r.Quantity))
Logger.Log(0, " - ACH Mismatch for {0} SO:({1} X {2}) vs MegaT:({3} X {4})", r.CI_Name, r.Quantity, 200, qty, cost);
}
new_mrc = r.Quantity * 200; // todo no easy way to determine the actual price
}
//Sessions, r.SCID contains 'SAP'
else if (r.SCID.Contains("SAP"))
{
if (!string.Equals(_srow[so_row, 12].Value.ToString(), "XXX", StringComparison.OrdinalIgnoreCase))
{
qty = Convert.ToDouble(_srow[so_row, 12].Value.ToString());
cost = Convert.ToDouble(_srow[so_row, 13].Value.ToString());
new_mrc = qty * cost;
if (!(qty == r.Quantity))
Logger.Log(0, " - VSS Mismatch for {0} SO:({1} X {2}) vs MegaT:({3} X {4})", r.CI_Name, r.Quantity, 9.8, qty, cost);
}
new_mrc = r.Quantity * 9.8; // todo no easy way to determine the actual price
}
//DID, r.SCID contains 'DID' - This does NOT appear in INSIS, so we fabricate the billing
else if (r.SCID.Contains("DID"))
{
new_mrc = r.Quantity * 0.75; // grab the qty from the SO, price is fixed at $0.75
}
calcTaxes(new_mrc.ToString(), "0", ref r); // throw a zero if we don't understand!
}
else
{
Logger.Log(0, " {0}-Megatool SCHEDA entry not found ({1}) ", r.SSC_Order_Number, schA);
}
//Fix up Billing Start Date as 'effective date' - there are some customs, return as INT32
string bsd = _srow[so_row, 7].Value.ToString();
if (bsd == "XXX")
{
Logger.Log(1, " -- Bill Start Date not found in INSIS for {0}", r.CI_Name);
if (r.CI_Name != "UNKNOWN") Logger.Log(task, "WARN: No Billing Start found: {0} Please add override or correct INSIS.", r.CI_Name);
//todo should I simply make the bsd = 29991228 ?
}
else
{
r.Billing_Effective_Date = Int32.Parse(bsd);
}
// Logic to determine if we need to add an extra row for an NRC item, grab NRC value from col9, return as _nrc
double.TryParse(_srow[so_row, 9].Value.ToString(), out double _nrc);
if (_nrc > 0)
{
string billMonth = r.Billing_Effective_Date.ToString(); //todo if bed is not first of month, push billing entry to following month.
billMonth = billMonth.Substring(0, (billMonth.Length - 2));
Logger.Log(2, "Billing for {0} and Billing Effective Date is {1}", current, billMonth);
if (current.ToString() == billMonth)
{
Logger.Log(0, " >SO{1} ADD NRC={0:0.00} charge to current BDF ({2})", _nrc, r.SSC_Order_Number, r.CI_Name);
Logger.Log(task, "INFO: SO-{1} ADDING NRC={0:0.00} charge to current BDF ({2})", _nrc, r.SSC_Order_Number, r.CI_Name);
r_nrc = r.Clone<Row>();
calcTaxes("0", _nrc.ToString(), ref r_nrc);
int build_row = 0;
foreach (var nrc_row in ws2.AllocatedRange.Rows)
{
try
{
if (ws2[nrc_row.Row,28].Value.ToString().ToUpper().Contains("BUILD"))
{
build_row = nrc_row.Row;
r_nrc.SSC_Seq_No = Int32.Parse(ws2[nrc_row.Row, 21].Value.ToString());
r_nrc.SCID = ws2[nrc_row.Row, 28].Value.ToString();
break;
}
}
catch (Exception e)
{
Logger.Log(0, "nrc {0}:{1}", e.Message, e.InnerException.Message);
}
}
if (build_row == 0)
{
Logger.Log(0, "WARNING - No 'Build' row in SO-{0} found for this charge, using '* MANUAL *' ({1})", r.SSC_Order_Number, r.CI_Name);
r_nrc.SSC_Seq_No = 0;
r_nrc.SCID = "* MANUAL *";
}
}
else if (current >= Int32.Parse(billMonth))
{
Logger.Log(0, " >SO{1} SKIP NRC={0:0.00} as presumed already billed ({2})", _nrc, r.SSC_Order_Number, r.CI_Name);
Logger.Log(task, "INFO: SO-{1} skipping NRC={0:0.00}, presumed billed {3} ({2})", _nrc, r.SSC_Order_Number, r.CI_Name, billMonth);
}
else
{
Logger.Log(0, " >SO{1} SKIP NRC={0:0.00} as it is a future billing ({2})", _nrc, r.SSC_Order_Number, r.CI_Name);
Logger.Log(task, "INFO: SO-{1} skipping NRC={0:0.00}, expected to start {3} ({2})", _nrc, r.SSC_Order_Number, r.CI_Name, billMonth);
}
}
break;
} // end of if scheda match
} // end of row interations in InsisMap
}
catch (Exception e)
{
Logger.Log(0, "InsisMap Exc: {0}:{1}", e.Message, e.InnerException.Message);
}
//Now add row(s) as needed
try
{
if ((so_row + r0_row) == 0)
{
Logger.Log(0, "Megatool InsisMap : Row not found: {0}", r.SSC_Order_Number);
}
if (r.Billing_Effective_Date > (current * 100 + 1))
{
Logger.Log(0, " >SO{0} SKIP - {1} doesn't start billing until {2} : {4} x {3}", r.SSC_Order_Number, r.CI_Name, r.Billing_Effective_Date, r.SCID.Substring(5), r.Quantity);
Logger.Log(task, "INFO: SO-{0} ({1}) MRR={3:0.00} as it doesn't bill until ({2})", r.SSC_Order_Number, r.CI_Name, r.Billing_Effective_Date, (r.Recurring_Charges_for_the_Month));
}
else if (r.Billing_Effective_Date == 0) // this happens for new SCHEDS that don't have bill start dates yet
{
Logger.Log(0, " >SO{0} ZEROED as Bill Start Date is unknown ({1})", r.SSC_Order_Number, r.CI_Name);
Logger.Log(task, "WARN: SO-{0} ignoring MRR & NRC as Bill Start Date is not known ({1})", r.SSC_Order_Number, r.CI_Name);
calcTaxes("0", "0", ref r); // recalc MRR with zeros,
r_nrc.Non_recurring_Charges_for_the_Month = 0; // removes NRC line
}
else
{
rows.Add(r);
if (r_nrc.Non_recurring_Charges_for_the_Month != 0)
rows.Add(r_nrc);
}
}
catch (Exception e)
{
Logger.Log(0, "Exc:add {0}:{1}", e.Message, e.InnerException.Message);
}
// End row adds
}
else
{
break;
}
Logger.Log(4, "SCID:{0}:{1}", scid, rows.Count);
// End of first row in SO (default for billing)
}
// End of SO rows processing
}
// End of current SO Workbook
try
{
BDF.S_InsertData(bdf2, rows);
//a series of formats and cleanups needed
{
CellRange reformat = bdf2[2, 21, bdf2.LastRow, 31];
reformat.Style.NumberFormat = "#0.00";
for (int c = 2; c <= bdf2.LastRow; c++)
{
if (bdf2[c, 17].NumberValue == 0)
{
bdf2[c, 17].ClearContents();
}
}
// clean up unused column from Tab2 for SIP data
if (task == "SIP") bdf2.DeleteColumn(36);
}
}
catch (Exception e)
{
Logger.Log(0, "Exc:add2 {0}:{1}", e.Message, e.InnerException.Message);
}
}
catch (Exception e)
{
Logger.Log(0, "Exception Rows: {0}:{1} ", e.Message, e.InnerException);
}
//End of SO Workbooks
Logger.Log("Finalizing...");
Dictionary<string, string> Invoice = new Dictionary<string, string> { };
InvoiceCell ic = new InvoiceCell { };
// finalize the totals on first sheet
var bdf1 = workbook.Worksheets[0];
bdf1.Name += monthAbbreviation;
List<Row2> row2s = null;
//Generic now
row2s = new List<Row2> { new Row2 { Invoice_Period = current, Invoice_Date = int.Parse(invDate) } };
try
{
BDF.S_InsertData(bdf1, row2s);
//bdf1[2, 4].Value = bdf2[2, 6].Value; // CCI - pull from first data row of Tab2
bdf1[2, 4].Value = CCI[task];
bdf1[2, 5].Value = bdf2[2, 7].Value; // Rogers
bdf1[2, 6].NumberValue = sumCol(22, ref bdf2);
bdf1[2, 7].NumberValue = sumCol(24, ref bdf2);
bdf1[2, 8].NumberValue = (double)bdf1[2, 6].NumberValue + (double)bdf1[2, 7].NumberValue;
bdf1[2, 9].NumberValue = sumCol(26, ref bdf2);
bdf1[2, 10].NumberValue = sumCol(25, ref bdf2);
bdf1[2, 11].NumberValue = 0;
bdf1[2, 12].NumberValue = sumCol(27, ref bdf2);
bdf1[2, 13].NumberValue = sumCol(28, ref bdf2);
bdf1[2, 14].NumberValue = sumCol(29, ref bdf2);
bdf1[2, 15].NumberValue = sumCol(30, ref bdf2);
bdf1[2, 16].NumberValue = sumCol(31, ref bdf2);
}
catch (Exception e)
{
Logger.Log(0, "Error creating totals for Tab 1", e.Message);
}
// Clean up slight differencdes in BDFs
if (task != "SIP")
{
// get rid of Total Usage col#17 for non-SIP BDFs
bdf1.DeleteColumn(bdf1.LastColumn);
}
else
{
// Create the header-only TAB3 for SIP
var bdf3 = workbook.Worksheets[2];
bdf3.Name += monthAbbreviation;
var row3 = new List<Row3> { new Row3 { } };
try
{
BDF.S_InsertData(bdf3, row3); // add the row, so formatting gets applied
bdf3.DeleteRow(2); // but leave Row2+ blank if no charges per call with Diana A.
}
catch (Exception e)
{
Logger.Log(0, "Error creating totals for SIP Tab 3", e.Message);
}
}
string traceable = "";
try
{
if ( username.Contains("."))
{
traceable = "_" + username.Split('.')[0].Substring(0, 1) + username.Split('.')[1].Substring(0, 1) + "_"
+ d.ToString("yyyyMMdd");
}
else
{
traceable = "_" + username + "_" + d.ToString("yyyyMMdd");
}
}
catch (Exception e)
{
Logger.Log(0, "traceable: ", e.Message);
}
string fileTraceable = ( upload ? task.ToUpper() : "." ) + Path.DirectorySeparatorChar + bdf.filename.Split('.')[0] + traceable + ".xlsx"; // {Task}/filename
//BDF output timing moved to after PDF Invoice creation, so we can insert custom invoice #'s
// Handle request for 1 PDF Invoice per SIP PO
//
Dictionary<string, InvoiceCell> PO = new Dictionary<string, InvoiceCell> { };
Logger.Log("PDF Invoice(s) being created...this will take a few seconds");
if (SIP) // Build Dictionary of SIP per PO Invoice data
{
//Scan SIP BDF Tab2, and get unique PO's from Col 35/"AI"
foreach (var row in bdf2.AllocatedRange.Rows)
{
if (row.Row == 1) continue;
string po = bdf2[row.Row, 35].Value.ToString();
//Customize invoice string
//(SIP ? invDate.Substring(2,4) + "-" + PO.ElementAt(i).Key : invDate.Substring(4) + invDate.Substring(0, 4) )
bdf2[row.Row, 4].Value = invDate.Substring(2, 4) + "-" + po;
if (!PO.ContainsKey(po))
{
PO.Add(po,new InvoiceCell { });
Logger.Log(1, " added SIP PO# {0}", po);
}
// Now sum values from rows under their existing dictionary entry
PO[po]._Hidden_SO = bdf2[row.Row, 8].Value.ToString(); //6 digit SO
PO[po]._Hidden_Addr1 = bdf2[row.Row, 12].Value.ToString(); //6 digit SO
PO[po]._Hidden_Addr2 = bdf2[row.Row, 11].Value.ToString(); //6 digit SO
PO[po]._Hidden_Addr3 = bdf2[row.Row, 13].Value.ToString(); //6 digit SO
PO[po]._Hidden_Addr4 = ""; //6 digit SO
try
{
if (PO_ACCTS.ContainsKey(po))
{
PO[po]._Hidden_Acct = PO_ACCTS[po]; //6 digit SO
}
else
{
Logger.Log(0, "No matching PO {0} found in Account information.", po);
}
}
catch (Exception e)
{
Logger.Log(0, "Error tracing PO {0} to Account information {1}", po, e.Message);
}
double r = 0 ;
//Invoice.Add(ic.UsageCharges, String.Format("{0:N2}", 0));
PO[po].TotalUsageCharges = String.Format("${0:N2}", 0);
//PO[po].DataMonthlyCharges = String.Format("{0:N2}", double.Parse(PO[po].DataMonthlyCharges) + bdf2[row.Row, 22].NumberValue);
PO[po].DataMonthlyCharges = String.Format("{0:N2}", (double.TryParse(PO[po].DataMonthlyCharges, out r) ? r : 0) + bdf2[row.Row, 22].NumberValue);
PO[po].DataNon_RecurringCharges = String.Format("{0:N2}", (double.TryParse(PO[po].DataNon_RecurringCharges, out r) ? r : 0) + bdf2[row.Row, 24].NumberValue);
PO[po].DataTotalOtherCharges = String.Format("{0:N2}", (double.TryParse(PO[po].DataTotalOtherCharges, out r) ? r : 0) + bdf2[row.Row, 26].NumberValue);
PO[po].Data_Services = String.Format("{0:N2}", (double.TryParse(PO[po].Data_Services, out r) ? r : 0) + bdf2[row.Row, 22].NumberValue + bdf2[row.Row, 24].NumberValue - bdf2[row.Row, 26].NumberValue);
//Invoice.Add( ic.TaxON_PST , bdf1.Row(2).Cell(9).Value
PO[po].TaxON_GST = String.Format("{0:N2}", 0);
PO[po].TaxON_HST = String.Format("{0:N2}", (double.TryParse(PO[po].TaxON_HST, out r) ? r : 0) + bdf2[row.Row, 28].NumberValue);
PO[po].TaxPQ_PST = String.Format("{0:N2}", (double.TryParse(PO[po].TaxPQ_PST, out r) ? r : 0) + bdf2[row.Row, 29].NumberValue);
PO[po].TaxPQ_GST = String.Format("{0:N2}", (double.TryParse(PO[po].TaxPQ_GST, out r) ? r : 0) + bdf2[row.Row, 27].NumberValue);
PO[po].TaxPQ_HST = String.Format("{0:N2}", 0);
PO[po].Taxes = String.Format("{0:N2}", (double.TryParse(PO[po].Taxes, out r) ? r : 0) + bdf2[row.Row, 27].NumberValue + bdf2[row.Row, 28].NumberValue + bdf2[row.Row, 29].NumberValue);
PO[po].Total_Charges = String.Format("{0:N2}", (double.TryParse(PO[po].Data_Services, out r) ? r : 0) + (double.TryParse(PO[po].Taxes, out r) ? r : 0) );
string prov = bdf2[row.Row, 13].Value.ToString();
if (!PO[po].TX.ContainsKey(prov))
{
// GST-col27, HST-col28, QST-col29, total tax col 30
PO[po].TX.Add(prov, new Taxes(bdf2[row.Row, 29].NumberValue, bdf2[row.Row, 27].NumberValue, bdf2[row.Row, 28].NumberValue));
Logger.Log(3, " added SIP PO# {0} Tax entry for {1} new taxes add {2:N2}", po, prov, bdf2[row.Row, 30].NumberValue);
}
else
{
PO[po].TX[prov] = new Taxes(PO[po].TX[prov], bdf2[row.Row, 29].NumberValue, bdf2[row.Row, 27].NumberValue, bdf2[row.Row, 28].NumberValue);
Logger.Log(3, " incr SIP PO# {0} Tax entry for {1} add taxes {2:N2} total incr taxes {3:N2}", po, prov, bdf2[row.Row, 30].NumberValue, PO[po].TX[prov].PST + PO[po].TX[prov].GST + PO[po].TX[prov].HST);
}
}
}
//Now it is safe to dump out BDF files as we modified the custom invoice on tab2
{
// Output BDF to ./ if not uploading
try
{
CellRange reformat = bdf1[2, 6, bdf1.LastDataRow, 17];
reformat.Style.NumberFormat = "#0.00";
//add AutoFilters to Sheet2
bdf2.AutoFilters.Range = bdf2.AllocatedRange;
bdf2.AutoFilters.Filter();
Logger.Log(task, "SUMM> {0} Detailed bill lines added. Monthly totals MRR={1:N2} NRC={2:N2} before taxes", (bdf2.LastDataRow - 1), bdf1[2, 6].Value, bdf1[2, 7].Value);
//string fileTraceable = output_path + bdf.filename.Split('.')[0] + traceable + ".xlsx";
if (!FileIsWritable(svc_path + fileTraceable)) return;
//clear older versions of same month
DeleteFilesByPrefix(svc_path + fileTraceable, 7); // use length of 12 to only clear same billing period files.
ExcelDocumentProperties edp = new ExcelDocumentProperties { };
edp.Title = "BDF " + task + " Detail";
edp.Subject = task + " Billing Detail File";
edp.Author = bdf.fullUser;
edp.Comments += "\n" + DateTime.Now.ToString();
edp.ApplyTo(workbook);
workbook.SaveToFile(svc_path + fileTraceable, ExcelVersion.Version2016);
Logger.Log(0, "Excel file populated! ({0})", svc_path + fileTraceable);
}
catch (Exception e)
{
Logger.Log(0, "Error formatting and saving Workbook ({0})", traceable);
Logger.Log(0, "({0})", e.Message);
Logger.Log(0, "Please close the workbook ({0}) if you currently have it open in Excel and then retry.", traceable);
}
// are we uploading to EFT SFTP server?
if (upload)
{
Logger.Log(0, " Initiating {0}-BDF upload to EFT server...", task);
try
{
using (var ms = new MemoryStream())
{
try
{
workbook.SaveToStream(ms);
}
catch (Exception e)
{
Logger.Log(" BDF failed to format for SFTP. ({0})", e.Message);
return;
}
try
{
sftp.Upload_Stream(ms, "Outbox" + Path.DirectorySeparatorChar + fileTraceable, sshDetails);
}
catch (Exception e)
{
Logger.Log(0, "BDF SFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
return;
}
//Logger.Log(" BDF uploaded.");
}
}
catch (Exception e)
{
Logger.Log(0, "BDF SFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
return;
}
}
}
// Hold memorystream copies of each PDF invoice created for the ZIP/SAVE and SFTP process(es): filename, ms
Dictionary<string, MemoryStream> invoiceDossier = new Dictionary<string, MemoryStream> { };
//Loop once for LDF/WAV, or once per PO for SIP
for (int i = 0; i < (SIP ? PO.Count : 1); i++)
{
InvoiceCell sip = SIP ? PO.ElementAt(i).Value : new InvoiceCell();
// Create PDF Invoice
try // Create Custom Invoice from template
{
Invoice.Add(ic.Account, "Account P02-" + (SIP ? sip._Hidden_Acct : bdf2[2, 3].NumberValue.ToString()) ); //bdf2 C2
Invoice.Add(ic.Date, PDF.dateFormal(row2s[0].Invoice_Date));
Invoice.Add(ic.Invoice, ("Invoice " + (SIP ? invDate.Substring(2,4) + "-" + PO.ElementAt(i).Key : invDate.Substring(4) + invDate.Substring(0, 4) ) + (SIP ? "" : "-" + task))); //mmddyyyy
Invoice.Add(ic.Total_Charges, SIP ? "$" + sip.Total_Charges : String.Format("${0:N2}", bdf1[2, 16].NumberValue));
Invoice.Add(ic.Account2, Invoice[ic.Account]);
Invoice.Add(ic.Date2, Invoice[ic.Date]);
Invoice.Add(ic.Invoice2, Invoice[ic.Invoice]);
Invoice.Add(ic.Total_Charges2, Invoice[ic.Total_Charges]);
if (SIP) // custom address for SIP invoices (to be copied to D-1 cells)
{
Invoice.Add(ic.Addr_Dept, "11 LAURIER ST, PDP 3");
Invoice.Add(ic.Addr_Client, "PO BOX 9808");
Invoice.Add(ic.Addr_Street, "STN T CSC ATTN ACCOUNTS PAYABLE,");
Invoice.Add(ic.Addr_City, "RM 5A1,");
Invoice.Add(ic.Addr_PC, "OTTAWA, ON K1G 4A8");
Invoice.Add(ic.SrvAddr_1, sip._Hidden_Addr1);
Invoice.Add(ic.SrvAddr_2, sip._Hidden_Addr2);
Invoice.Add(ic.SrvAddr_3, sip._Hidden_Addr3);
Invoice.Add(ic.SrvAddr_4, sip._Hidden_Addr4);
Invoice.Add(ic.BackBill_Title, "Pro-rated Back-Billing");
Invoice.Add(ic.BackBill_Charges, String.Format("{0:N2}", bdf.backBillAmt));
}
//Invoice.Add(ic.CCI, "Canada Contract Identifier " + (SIP ? PO.ElementAt(i).Key : bdf1[2, 4].Value) );
Invoice.Add(ic.CCI, "Canada Contract Identifier " + bdf1[2, 4].Value );
Invoice.Add(ic.PaperFee, String.Format("{0:N2}", 0));
Invoice.Add(ic.LateCharge, String.Format("{0:N2}", 0));
Invoice.Add(ic.Service_Charges, String.Format("${0:N2}", Double.Parse(Invoice[ic.PaperFee]) + Double.Parse(Invoice[ic.LateCharge])));
Invoice.Add(ic.TotalUsageCharges, SIP ? sip.TotalUsageCharges : String.Format("${0:N2}", 0));
Invoice.Add(ic.DataMonthlyCharges, SIP ? sip.DataMonthlyCharges : String.Format("{0:N2}", bdf1[2, 6].NumberValue));
Invoice.Add(ic.DataNon_RecurringCharges, SIP ? sip.DataNon_RecurringCharges : String.Format("{0:N2}", bdf1[2, 7].NumberValue));
Invoice.Add(ic.DataTotalOtherCharges, SIP ? sip.DataTotalOtherCharges : String.Format("{0:N2}", bdf1[2, 9].NumberValue));
Invoice.Add(ic.Data_Services, SIP ? "$" + sip.Data_Services : String.Format("${0:N2}", (bdf1[2, 8].NumberValue + bdf1[2, 9].NumberValue - bdf1[2, 10].NumberValue)));
// repeat the tax scan for LDF/WAV files as we do not cover that in PO above
if (!SIP)
{
//Scan SIP BDF Tab2, and get unique PO's from Col 35/"AI"
foreach (var row in bdf2.AllocatedRange.Rows)
{
if (row.Row == 1) continue;
string prov = bdf2[row.Row, 13].Value.ToString();
if (!ic.TX.ContainsKey(prov))
{
// GST-col27, HST-col28, QST-col29, total tax col 30
ic.TX.Add(prov, new Taxes(bdf2[row.Row, 29].NumberValue, bdf2[row.Row, 27].NumberValue, bdf2[row.Row, 28].NumberValue));
Logger.Log(3, " added Tax entry for {0} total taxes {1:N2}", prov, bdf2[row.Row, 30].NumberValue);
}
else
{
ic.TX[prov] = new Taxes(ic.TX[prov], bdf2[row.Row, 29].NumberValue, bdf2[row.Row, 27].NumberValue, bdf2[row.Row, 28].NumberValue);
Logger.Log(3, " incr Tax entry for {0} total taxes {1:N2}", prov, bdf2[row.Row, 30].NumberValue);
}
}
}
/* SKIP the manual tax cell additions
//Invoice.Add( ic.TaxON_PST , bdf1.Row(2).Cell(9).Value
Invoice.Add(ic.TaxON_GST, SIP ? sip.TaxON_GST : String.Format("{0:N2}", 0));
Invoice.Add(ic.TaxON_HST, SIP ? sip.TaxON_HST : String.Format("{0:N2}", bdf1[2, 13].NumberValue));
Invoice.Add(ic.TaxPQ_PST, SIP ? sip.TaxPQ_PST : String.Format("{0:N2}", bdf1[2, 14].NumberValue));
Invoice.Add(ic.TaxPQ_GST, SIP ? sip.TaxPQ_GST : String.Format("{0:N2}", bdf1[2, 12].NumberValue));
Invoice.Add(ic.TaxPQ_HST, SIP ? sip.TaxPQ_HST : String.Format("{0:N2}", 0));
Invoice.Add(ic.Taxes, SIP ? "$" + sip.Taxes : String.Format("${0:N2}", bdf1[2, 15].NumberValue));
*/
}
catch (Exception e)
{
Logger.Log(0, "Error Invoice Cells Customization for Tab 1", e.Message);
}
// returns PDF as a MemoryStream, also saves arial.ttf if needed
InvoiceCell icArg = SIP ? sip : ic; // pick the correct InvoiceCell to pass to customInvoice
using (MemoryStream ms = PDF.customInvoiceAsMS_taxes(ref Invoice, ref icArg)) // now pass ic, so we can access ic.TX dictionary
//using (MemoryStream ms = PDF.customInvoiceAsMS(ref Invoice))
{
if (SIP)
Logger.Log(task, "INFO: PO# {0} Creating INVOICE: MRR={1:0.00} NRC={2:0.00} Total_charges= ${3:0.00}", PO.ElementAt(i).Key, sip.Data_Services, sip.Taxes, sip.Total_Charges);
string PDF_path = "_INV_" + current.ToString() + (SIP ? (String.Format("_{0}", PO.ElementAt(i).Key)) : "") + traceable; //"." + Path.DirectorySeparatorChar;
ms.Position = 0;
try
{
MemoryStream ms_copy = new MemoryStream();
ms.WriteTo(ms_copy);
invoiceDossier.Add(PDF_path + ".pdf", ms_copy);
}
catch (Exception e)
{
Logger.Log(0, "Error Invoice SFTP upload", e.Message);
}
}
Invoice.Clear();
}
//Now we have a Dossier of all PDF invoices, if we are compressing them, then build zip as memorystream and remove all original PDFs from Dossier
Logger.Log(2, "We have {0} invoice PDFs prepared", invoiceDossier.Count);
//Setup ZIP archive if desired (bool archive=true)
if (SIP && archive)
{
byte[] zip;
//using (FileStream zipFS = new FileStream("test.zip", FileMode.Create))
using (MemoryStream zipMS = new MemoryStream())
{
using (ZipArchive archive = new ZipArchive(zipMS, ZipArchiveMode.Create, leaveOpen: false))
{
foreach (var entry in invoiceDossier)
{
string fileName = task.ToUpper() + entry.Key;
MemoryStream fileStream = entry.Value;
Logger.Log(2, " adding {0} to ZIP archive", fileName);
// Create a new entry in the zip archive
ZipArchiveEntry zipEntry = archive.CreateEntry(fileName);
// Open the entry stream to write data into the zip file
// 'using' ensures the entry stream is disposed after writing.
using (Stream entryStream = zipEntry.Open())
{
// Ensure the source stream is at the beginning before copying
if (fileStream.CanSeek && fileStream.Position != 0)
{
fileStream.Seek(0, SeekOrigin.Begin);
}
// Copy the contents from the source MemoryStream to the destination ZipArchiveEntry stream
fileStream.CopyTo(entryStream);
}
//invoiceDossier.Remove(entry.Key);
}
}
zip = zipMS.ToArray();
}
invoiceDossier.Clear();
string fname = "_INV_" + current.ToString() + traceable + ".zip";
Logger.Log(2, "Adding {0} to Dossier", fname);
invoiceDossier.Add(fname, new MemoryStream(zip));
}
// Now we either have a Dictionary of PDFs, or one with a single ZIP file
// If we aren't uploading then we just dump the file locally
bool deleted = false;
foreach (var file in invoiceDossier)
{
try
{
//PDF.customInvoiceSaveFile(task, file.Key, file.Value);
file.Value.Position = 0;
string srv_path = bdf.upload ? bdf.base_path + "BDF_Archive" + Path.DirectorySeparatorChar : "";
string file_name = (bdf.upload ? task.ToUpper() : ".") + Path.DirectorySeparatorChar + task.ToUpper() + file.Key;
if (!FileIsWritable(srv_path + file_name)) return;
// clear older versions
if (!deleted)
{
DeleteFilesByPrefix(srv_path + file_name, 7); // use length of 12 to only clear same billing period files.
deleted = true;
}
File.WriteAllBytes(srv_path + file_name, file.Value.ToArray());
Logger.Log(0, "Invoice written! ({0})", srv_path + file_name);
}
catch (Exception e)
{
Logger.Log(0, "Error Saving Invoice(s) to File", e.Message);
}
if (upload)
try
{
file.Value.Position = 0;
string file_name = task.ToUpper() + Path.DirectorySeparatorChar + task.ToUpper() + file.Key;
{
Logger.Log(0, " Initiating {0}-INV upload to EFT server...", task);
try
{
try
{
sftp.Upload_Stream(file.Value, "Outbox" + Path.DirectorySeparatorChar + file_name, bdf.sshDetails);
}
catch (Exception e)
{
Logger.Log(0, "Invoice SFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
return;
}
}
catch (Exception e)
{
Logger.Log(0, "INVSFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
return;
}
}
}
catch (Exception e)
{
Logger.Log(0, "Error Invoice SFTP upload", e.Message);
}
}
// Cleanup stuff
CXLFile.S_ServiceOrders.RemoveRange(0, CXLFile.S_ServiceOrders.Count);
} // end of MAIN()
Logger.Log("All done! Have a Great Day :) ");
Logger.Log(product, "TERM: Successful Completion at {0}", DateTime.Now);
outfile.Close();
reportfile.Close();
} // end of to-do loop
// END OF MAIN2
// Helper Functions
public static Dictionary<string, string> getSIPAccountsbyPO(string filename)
{
Dictionary<string, string> o = new Dictionary<string, string> { };
if (System.IO.File.Exists(filename))
{
Logger.Log(0, "Found SIP PO->Acct mapping file ({0}), parsing it...", filename);
foreach (var line in System.IO.File.ReadLines(filename))
{
if (string.IsNullOrWhiteSpace(line) || line.Substring(0, 1).Contains("#")) // clean up blank or comment lines (start with #).
continue;
// Split by spaces: first token is the index word ie PO
string[] parts = line.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
if (parts.Length != 2)
{
Logger.Log(0, " -Skipping: Invalid PO->Acct line format: {0}", line);
Logger.Log("SIP", "WARN: Invalid PO->Acct line format: {0}", line);
continue;
}
string indexWord = parts[0]; //PO
string banWord = parts[1]; //BAN
// Validate numeric range
if (!int.TryParse(indexWord, out int indexNum) ||
indexNum < 100000000 || indexNum > 999999999)
{
Logger.Log(0, " -Skipping: Invalid PO (must be 9 digits) '{0}' in line: {1}", indexWord, line);
Logger.Log("SIP", "WARN: Invalid PO (must be 9 digits) '{0}' in line: {1}", indexWord, line);
continue;
}
else if (!int.TryParse(banWord, out int banNum) ||
banNum < 100000 || banNum > 999999)
{
Logger.Log(0, " -Skipping: Invalid BAN (must be 6 digits) '{0}' in line: {1}", banWord, line);
Logger.Log("SIP", "WARN: Invalid BAN (must be 6 digits) '{0}' in line: {1}", banWord, line);
continue;
}
else o.Add(parts[0], parts[1]);
Logger.Log(1, " - mapped PO:{0} to Acct:{1}", indexWord, banWord);
}
}
return o;
}
public static Dictionary<string,string> getOverrides(string task)
{
Dictionary<string, string> o = new Dictionary<string, string> { };
string filepath = inputFile_path + task + overrideSuffix;
if (System.IO.File.Exists(filepath))
{
Logger.Log(0, "Found Overrides file ({0}), parsing it...", filepath);
foreach (var line in System.IO.File.ReadLines(filepath))
{
if ( string.IsNullOrWhiteSpace(line) || line.Substring(0, 1).Contains("#") ) // clean up blank or comment lines (start with #).
continue;
// Split by spaces: first token is the index word
string[] parts = line.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
if (parts.Length< 2)
{
Logger.Log(0, " -Skipping: Invalid override line format: {0}", line);
Logger.Log(task, "WARN: Invalid override line format: {0}", line);
continue;
}
string indexWord = parts[0];
// Validate numeric range
if (!int.TryParse(indexWord, out int indexNum) ||
indexNum< 100000 || indexNum> 999999)
{
Logger.Log(0, " -Skipping: Invalid SO- '{0}' in line: {1}", indexWord, line);
Logger.Log(task, "WARN: Invalid SO- '{0}' in line: {1}", indexWord, line);
continue;
}
// Dictionary for key=value pairs
Dictionary<string, string> pairs = new Dictionary<string, string>();
// Parse remaining tokens
for (int i = 1; i < parts.Length; i++)
{
var kv = parts[i].Split('=');
if (kv.Length != 2)
{
Logger.Log(0, " -Skipping: Malformed override entry format: {0}", parts[i]);
Logger.Log(task, "WARN: Malformed override entry format: {0}", parts[i]);
continue;
}
string key = kv[0].Trim();
string value = kv[1].Trim();
pairs[key] = value;
}
// Validate overrides, SO was already checked
// SO TA COP PRID CONTRACT SCHED BSD MRR NRC
// 124111 R000137538 333754 3050 GCNSLDF047 XXX 900 0
//
//foreach (string Parm in new string[] { "COP", "PRID", "SCHED", "BSD", "MRR", "NRC" }) // checklist
try
{
string ivalue = "";
bool illegal = false;
if (!pairs.TryGetValue("COP", out ivalue)) { } // key not found, move on
else if (int.TryParse(ivalue, out int num))
if (!(num >= 100000 && num <= 999999)) //invalid COP
{
Logger.Log(0, " >SO{1} SKIP invalid override for COP of {0}", ivalue, indexWord);
pairs.Remove("COP");
illegal = true;
}
if (!pairs.TryGetValue("PRID", out ivalue)) { } // key not found, move on
else if (int.TryParse(ivalue, out int num2))
if (!(num2 >= 1000 && num2 <= 9999)) //invalid PRID
{
Logger.Log(0, " >SO{1} SKIP invalid override for PRID of {0}", ivalue, indexWord);
pairs.Remove("PRID");
illegal = true;
}
if (!pairs.TryGetValue("SCHED", out ivalue)) { } // key not found, move on
else if (int.TryParse(ivalue.Substring(7), out int num3))
if (!(num3 >= 1 && num3 <= 999)) //invalid SCHED
{
Logger.Log(0, " >SO{1} SKIP invalid override for SCHED of {0}", ivalue, indexWord);
pairs.Remove("SCHED");
illegal = true;
}
if (!pairs.TryGetValue("BSD", out ivalue)) { } // key not found, move on
else if (int.TryParse(ivalue, out int num4))
if (!(num4 >= 20200000 && num4 <= 20300000)) //invalid COP
{
Logger.Log(0, " >SO{1} SKIP invalid override for BSD of {0}", ivalue, indexWord);
pairs.Remove("BSD");
illegal = true;
}
if (!pairs.TryGetValue("MRR", out ivalue)) { } // key not found, move on
else if (double.TryParse(ivalue, out double num5))
if (!(num5 >= 0 && num5 <= 99999)) //invalid MRR
{
Logger.Log(0, " >SO{1} SKIP invalid override for MRR of {0}", ivalue, indexWord);
pairs.Remove("MRR");
illegal = true;
}
if (!pairs.TryGetValue("NRC", out ivalue)) { } // key not found, move on
else if (double.TryParse(ivalue, out double num6))
if (!(num6 >= 0 && num6 <= 99999)) //invalid MRR
{
Logger.Log(0, " >SO{1} SKIP invalid override for NRC of {0}", ivalue, indexWord);
pairs.Remove("NRC");
illegal = true;
}
if ( task == "SIP")
{
if (!pairs.TryGetValue("AQty", out ivalue)) { } // key not found, move on
else if (double.TryParse(ivalue, out double num6))
if (!(num6 >= 0 && num6 <= 99999)) //invalid
{
Logger.Log(0, " >SO{1} SKIP invalid override for AQty of {0}", ivalue, indexWord);
pairs.Remove("AQty");
illegal = true;
}
if (!pairs.TryGetValue("ACst", out ivalue)) { } // key not found, move on
else if (double.TryParse(ivalue, out double num6))
if (!(num6 >= 0 && num6 <= 99999)) //invalid
{
Logger.Log(0, " >SO{1} SKIP invalid override for ACst of {0}", ivalue, indexWord);
pairs.Remove("ACst");
illegal = true;
}
if (!pairs.TryGetValue("SQty", out ivalue)) { } // key not found, move on
else if (double.TryParse(ivalue, out double num6))
if (!(num6 >= 0 && num6 <= 99999)) //invalid
{
Logger.Log(0, " >SO{1} SKIP invalid override for SQty of {0}", ivalue, indexWord);
pairs.Remove("SQty");
illegal = true;
}
if (!pairs.TryGetValue("SCst", out ivalue)) { } // key not found, move on
else if (double.TryParse(ivalue, out double num6))
if (!(num6 >= 0 && num6 <= 99999)) //invalid
{
Logger.Log(0, " >SO{1} SKIP invalid override for SCst of {0}", ivalue, indexWord);
pairs.Remove("SCst");
illegal = true;
}
}
if (illegal)
Logger.Log(task, "WARN: Illegal override value in line: {0}", line);
}
catch (Exception e)
{
Logger.Log(0, "Overrides Exc: {0}:{1}", e.Message, e.InnerException.Message);
}
string so_overrides = "";
{
foreach (var p in pairs)
{
so_overrides += (String.Format(" {0} = {1}", p.Key, p.Value));
o.Add(indexWord + p.Key, p.Value);
}
Logger.Log(1, " -SO{0} : {1}", indexWord, so_overrides);
}
}
}
return o;
}
public static void calcTaxes( string mrc, string nrc, ref Row r)
{
r.Recurring_Charges_for_the_Month = Double.Parse(mrc);
r.Total_Recurring_Charges_for_the_Month = r.Recurring_Charges_for_the_Month;
r.Non_recurring_Charges_for_the_Month = Double.Parse(nrc);
r.Total_Non_recurring_Charges_for_the_Month = r.Non_recurring_Charges_for_the_Month;
r.Total_Service_Credits = 0;
r.Total_Other_Charges_and_Credits = 0;
// QST is used for both PST+QST as appropriate
double taxable = (r.Total_Recurring_Charges_for_the_Month + r.Total_Non_recurring_Charges_for_the_Month - r.Total_Other_Charges_and_Credits);
if (r.Prov == "QC") // 14.975% split
{
r.Total_GST_Amount = Math.Round((0.05) * taxable, 2);
r.Total_HST_Amount = 0;
r.Total_QST_Amount = Math.Round((0.09975) * taxable, 2);
}
else if (r.Prov == "ON") // 13% hst
{
r.Total_GST_Amount = 0;
r.Total_HST_Amount = Math.Round((0.13) * taxable, 2);
r.Total_QST_Amount = 0;
}
else if (r.Prov == "NS") // 14% hst
{
r.Total_GST_Amount = 0;
r.Total_HST_Amount = Math.Round((0.14) * taxable, 2);
r.Total_QST_Amount = 0;
}
else if ( (r.Prov == "AB") || (r.Prov == "NWT") || (r.Prov == "NU") || (r.Prov == "YK")) // 5% GST only
{
r.Total_GST_Amount = Math.Round((0.05) * taxable, 2);
r.Total_HST_Amount = 0;
r.Total_QST_Amount = 0;
}
else if ( (r.Prov == "BC") || (r.Prov == "MB")) // 12% split
{
r.Total_GST_Amount = Math.Round((0.05) * taxable, 2);
//r.Total_PST_Amount = Math.Round((0.07) * taxable, 2);
r.Total_HST_Amount = 0;
r.Total_QST_Amount = Math.Round((0.07) * taxable, 2);
}
else if ((r.Prov == "NB") || (r.Prov == "NFLD") || (r.Prov == "NL") || (r.Prov == "PEI") || (r.Prov == "PE") ) // 15% hst
{
r.Total_GST_Amount = 0;
r.Total_HST_Amount = Math.Round((0.15) * taxable, 2);
r.Total_QST_Amount = 0;
}
else if ( (r.Prov == "SK") ) // 11% split
{
r.Total_GST_Amount = Math.Round((0.05) * taxable, 2);
//r.Total_PST_Amount = Math.Round((0.06) * taxable, 2);
r.Total_HST_Amount = 0;
r.Total_QST_Amount = Math.Round((0.06) * taxable, 2);
}
r.Total_Taxes = r.Total_GST_Amount + r.Total_HST_Amount + r.Total_QST_Amount;
r.Total_Amount_including_taxes = r.Total_Taxes + taxable;
}
public static double sumCol(int col, ref Spire.Xls.Worksheet sheet)
{
if (sheet == null)
throw new System.ArgumentNullException(nameof(sheet));
// Determine the last used row in the worksheet
int lastRow = sheet.LastRow;
double total = 0.0;
// Loop through each row and sum numeric values in the target column
// Worksheet("BDF Type 2") - 1st row is a header row so skip it
for (int row = 2; row <= lastRow; row++)
{
Spire.Xls.CellRange cell = sheet.Range[row, col];
if (cell != null && cell.HasNumber)
{
total += cell.NumberValue;
}
else
{
// Attempt to parse text values as numbers
double parsed;
if (double.TryParse(cell.Text, out parsed))
{
total += parsed;
}
}
}
return total;
}
public static string ReadPassword()
{
// Using StringBuilder is a good balance between security and ease of use in console apps
StringBuilder passwordBuilder = new StringBuilder();
ConsoleKeyInfo key;
Console.Write(">>>>>>>>>>>>>>>>>>>>>>>> ");
do
{
// Read the key, but do not display it (intercept = true)
key = Console.ReadKey(true);
// Handle standard characters
if (key.Key != ConsoleKey.Backspace && key.Key != ConsoleKey.Enter)
{
passwordBuilder.Append(key.KeyChar);
Console.Write("*"); // Display an asterisk or other mask character
}
// Handle backspace
else if (key.Key == ConsoleKey.Backspace && passwordBuilder.Length > 0)
{
passwordBuilder.Remove(passwordBuilder.Length - 1, 1);
Console.Write("\b \b"); // Erase the last character from the console
}
}
// Stop when the Enter key is pressed
while (key.Key != ConsoleKey.Enter);
Console.WriteLine(); // Add a new line after the password entry is complete
return passwordBuilder.ToString();
}
public static bool FileIsWritable(string path)
{
int waitMilliSecs = 1000;
for (int attempt = 1; attempt <= MAX_RETRY; attempt++)
{
if (!System.IO.File.Exists(path)) return true;
try
{
using (new System.IO.FileStream(
path,
System.IO.FileMode.Open,
System.IO.FileAccess.ReadWrite,
System.IO.FileShare.None))
{
return true;
}
}
catch (System.IO.IOException )
{
//Logger.Log(0, "IO Exc");
if (attempt >= MAX_RETRY)
throw;
// Network shares often need time to release stale locks
Logger.Log(0, "!! {0} is open? Close it and hit 'Enter' to retry ({1}/{2}).",path,attempt,MAX_RETRY );
Console.ReadLine();
Logger.Log(0," ...waiting {0}s for file sync, please be patient",waitMilliSecs/1000);
System.Threading.Thread.Sleep(waitMilliSecs);
waitMilliSecs += 3000;
}
catch (UnauthorizedAccessException)
{
// Permissions issue — retrying will not help
Logger.Log(0, "Aborting, ensure you have r/w permissions for: {0}", path);
throw;
}
}
Logger.Log(0, "Aborting, ensure you have access to: {0}", path);
//return false;
return false;
}
public static bool SafeDeleteFile(string filePath)
{
try
{
// Validate input
if (string.IsNullOrWhiteSpace(filePath))
return false;
// Normalize path (removes illegal characters, etc.)
string fullPath = System.IO.Path.GetFullPath(filePath);
// Make sure file exists
if (!System.IO.File.Exists(fullPath))
return false;
// Remove read-only attribute if necessary
System.IO.FileAttributes attrs = System.IO.File.GetAttributes(fullPath);
if ((attrs & System.IO.FileAttributes.ReadOnly) == System.IO.FileAttributes.ReadOnly)
{
System.IO.File.SetAttributes(fullPath, System.IO.FileAttributes.Normal);
}
// Attempt deletion
System.IO.File.Delete(fullPath);
// Verify deletion
return !System.IO.File.Exists(fullPath);
}
catch (System.Exception)
{
// Could log the exception if needed
return false;
}
}
public static void ExtractResourceToFile(string resourceName, string outputPath)
{
// Get the assembly that contains the embedded resource
Assembly assembly = Assembly.GetExecutingAssembly();
// Get the stream of the embedded resource
using (Stream stream = assembly.GetManifestResourceStream(resourceName))
{
if (stream == null)
{
Console.WriteLine($"Resource '{resourceName}' not found.");
return;
}
// Create the output file in the application's working directory
using (FileStream fileStream = File.Create(outputPath))
{
// Copy the resource stream to the file stream
stream.CopyTo(fileStream);
}
}
Logger.Log(2, "Resource {0} successfully saved to {1}.", resourceName, outputPath);
}
public static bool RemoveStringKey(List<string> list, string key)
{
if (list == null || key == null)
return false;
int index = list.FindIndex(s => string.Equals(s, key, StringComparison.OrdinalIgnoreCase));
if (index < 0)
return false;
list.RemoveAt(index);
return true;
}
//Type clone = DeepClone(original);
public static T DeepClone<T>(T source) where T : new()
{
if (source == null)
throw new ArgumentNullException(nameof(source));
T copy = new T();
foreach (System.Reflection.PropertyInfo prop in typeof(T).GetProperties(
System.Reflection.BindingFlags.Instance |
System.Reflection.BindingFlags.Public))
{
if (!prop.CanRead || !prop.CanWrite)
continue;
object value = prop.GetValue(source);
prop.SetValue(copy, value);
}
return copy;
}
// delete files matching prefix length
public static void DeleteFilesByPrefix(string targetFilePath, int length)
{
try
{
if (string.IsNullOrWhiteSpace(targetFilePath) || length <= 0) return;
string directory = Path.GetDirectoryName(targetFilePath);
string fileName = Path.GetFileName(targetFilePath);
// Ensure length does not exceed filename length
int safeLength = Math.Min(length, fileName.Length);
string prefix = fileName.Substring(0, safeLength);
if (Directory.Exists(directory))
{
// Find all files starting with the prefix
var filesToDelete = Directory.GetFiles(directory, $"{prefix}*");
foreach (var file in filesToDelete)
{
File.Delete(file);
Logger.Log(2,$"Deleted: {Path.GetFileName(file)}");
}
}
}
catch (Exception ex)
{
Logger.Log(0, $"Error: {ex.Message}");
}
}
}
public class ExcelDocumentProperties
{
// Standard document properties
public string Title { get; set; } = "BDF Workbook";
public string Subject { get; set; } = "Billing Detail File";
public string Author { get; set; } = "Default Author";
public string Manager { get; set; } = "Doug Macintosh";
public string Company { get; set; } = "Rogers Communications Inc.";
public string Category { get; set; } = "Invoicing";
public string Keywords { get; set; } = "Excel, Spreadsheet, BDF, Billing Detail";
public string Comments { get; set; } = "Created by " + bdf.product + " " + bdf.version;
public string DocumentVersion { get; set; } = "1";
//public string Version { get; set; } = "1";
public string RevisionNumber { get; set; } = "1";
public DateTime CreationDate { get; set; } = DateTime.Now;
//public DateTime LastSave { get; set; } = DateTime.Now;
//public int Security { get; set; } = 0;
// Optional: Custom properties dictionary (name → value)
//public System.Collections.Generic.Dictionary<string, object> CustomProperties { get; set; }
// = new System.Collections.Generic.Dictionary<string, object>();
/// <summary>
/// Apply the predefined properties to a workbook.
/// </summary>
/// <param name="workbook">The workbook to apply properties to</param>
public void ApplyTo(Workbook workbook)
{
if (workbook == null)
throw new ArgumentNullException(nameof(workbook));
// Core document properties
workbook.DocumentProperties.Title = Title;
workbook.DocumentProperties.Subject = Subject;
workbook.DocumentProperties.Author = Author;
workbook.DocumentProperties.Manager = Manager;
workbook.DocumentProperties.Company = Company;
workbook.DocumentProperties.Category = Category;
workbook.DocumentProperties.Keywords = Keywords;
workbook.DocumentProperties.Comments = Comments;
//workbook.DocumentProperties.CreatedTime = CreationDate;
//workbook.DocumentProperties.LastSaveTime = LastSave;
//workbook.DocumentProperties.DocumentVersion = DocumentVersion;
//workbook.DocumentProperties.EditTime = DateTime.Now.AddHours(1);
//workbook.DocumentProperties.LastAuthor = "";
//workbook.DocumentProperties.LastSaveTime = LastSave;
//workbook.DocumentProperties.PageCount
//workbook.DocumentProperties.RevisionNumber = RevisionNumber;
//workbook.DocumentProperties.Security = Security;
//workbook.DocumentProperties.Version =
// Apply custom properties - not available in FREE
//foreach (var kvp in CustomProperties)
//{
// FreeSpire.XLS custom properties are string/object pairs
// workbook.DocumentProperties.C .CustomProperties[kvp.Key] = kvp.Value;
//}
}
/// <summary>
/// Returns a new workbook with these properties applied.
/// </summary>
public Workbook CreateWorkbookWithProperties()
{
Workbook wb = new Workbook();
ApplyTo(wb);
return wb;
}
}
} // bdf class
public static class IDictionaryExtensions
{
public static TKey FindKeyByValue<TKey, TValue>(this IDictionary<TKey, TValue> dictionary, TValue value)
{
if (dictionary == null)
throw new ArgumentNullException("dictionary");
foreach (KeyValuePair<TKey, TValue> pair in dictionary)
if (value.Equals(pair.Value)) return pair.Key;
throw new Exception("the value is not found in the dictionary");
}
}
Reference in New Issue View Git Blame Copy Permalink