private void AddSummary(BillingUnit billingUnit)
{
List <BillingUnit> summaries;
if (Report.Summaries != null)
{
summaries = Report.Summaries.ToList();
}
else
{
summaries = new List <BillingUnit>();
}
summaries.Add(billingUnit);
Report.Summaries = summaries.ToArray();
}
private void ProcessExcelToolSUB()
{
string filePath;
if (StartPeriod >= ReportSettings.July2009)
{
ToolSUB report = GetReportFactory().GetReportToolSUB(StartPeriod, EndPeriod, GetClientID());
filePath = GenerateExcelSUB(report.Items, report.Summaries, "Tool");
}
else
{
BillingUnit SummaryUnit = new BillingUnit();
ToolReport rpt = new ToolReport(StartPeriod, EndPeriod);
DataTable dtTool = rpt.GenerateDataTable(SummaryUnit);
filePath = rpt.GenerateExcelFile(dtTool);
}
OutputExcel(filePath);
}
private void ProcessExcelRoomSUB()
{
string filePath;
if (StartPeriod >= ReportSettings.July2009)
{
RoomSUB report = GetReportFactory().GetReportRoomSUB(StartPeriod, EndPeriod, GetClientID());
filePath = GenerateExcelSUB(report.Items, report.Summaries, "Labtime");
}
else
{
BillingUnit summaryUnit = new BillingUnit();
RoomReport mgr = new RoomReport(StartPeriod, EndPeriod);
DataTable dtRoom = mgr.GenerateDataTable(summaryUnit);
filePath = mgr.GenerateExcelFile(dtRoom);
}
OutputExcel(filePath);
}
private void ProcessExcelStoreSUB(bool twoCreditAccounts)
{
string filePath;
if (twoCreditAccounts)
{
if (StartPeriod >= ReportSettings.July2009)
{
StoreSUB report = GetReportFactory().GetReportStoreSUB(StartPeriod, EndPeriod, twoCreditAccounts, GetClientID());
filePath = GenerateExcelSUB(report.Items, report.Summaries, "Store");
}
else
{
BillingUnit SummaryUnit1 = new BillingUnit();
BillingUnit SummaryUnit2 = new BillingUnit();
StoreReport rpt = new StoreReport(StartPeriod, EndPeriod);
DataTable dtStore = rpt.GenerateDataTable(SummaryUnit1, SummaryUnit2);
filePath = rpt.GenerateExcelFile(dtStore);
}
}
else
{
if (StartPeriod >= ReportSettings.July2009)
{
StoreSUB report = GetReportFactory().GetReportStoreSUB(StartPeriod, EndPeriod, twoCreditAccounts, GetClientID());
filePath = GenerateExcelSUB(report.Items, report.Summaries, "Store");
}
else
{
BillingUnit SummaryUnit1 = new BillingUnit();
BillingUnit SummaryUnit2 = new BillingUnit();
StoreReport rpt = new StoreReport(StartPeriod, EndPeriod);
DataTable dtStore = rpt.GenerateDataTable(SummaryUnit1, SummaryUnit2);
filePath = rpt.GenerateExcelFile(dtStore);
}
}
OutputExcel(filePath);
}
//This class will generate a real excel file and return the file path of this newly generated excel file
protected string GenerateExcel(DataTable dt, string JEType)
{
BillingUnit summaryUnit = summaryUnits.First();
//Contruct the excel object
string fileName = Utility.GetRequiredAppSetting("SUB_Template");
string templatePath = HttpContext.Current.Server.MapPath($"~\\SpreadSheets\\Templates\\{fileName}");
string workPathDir = HttpContext.Current.Server.MapPath("SpreadSheets\\Work");
DirectoryInfo di = new DirectoryInfo(workPathDir);
try
{
//Determine whether the directory exists.
if (!di.Exists)
{
di.Create();
}
}
catch (Exception ex)
{
return("Error - " + ex.Message);
}
using (var mgr = ExcelUtility.NewExcelManager())
{
mgr.OpenWorkbook(templatePath);
mgr.SetActiveWorksheet("Sheet1");
//We start at first row, because for ExcelLite control, the header row is not included
int iRow = 1;
DataView dv = dt.DefaultView;
dv.Sort = "CreditAccount ASC, ItemDescription ASC, ProjectGrant ASC";
foreach (DataRowView drv in dv)
{
mgr.SetCellTextValue(iRow, 0, drv["CardType"]);
mgr.SetCellTextValue(iRow, 1, drv["ShortCode"]);
mgr.SetCellTextValue(iRow, 2, drv["Account"]);
mgr.SetCellTextValue(iRow, 3, drv["FundCode"]);
mgr.SetCellTextValue(iRow, 4, drv["DeptID"]);
mgr.SetCellTextValue(iRow, 5, drv["ProgramCode"]);
mgr.SetCellTextValue(iRow, 6, drv["Class"]);
mgr.SetCellTextValue(iRow, 7, drv["ProjectGrant"]);
mgr.SetCellTextValue(iRow, 8, drv["VendorID"]);
mgr.SetCellTextValue(iRow, 9, drv["InvoiceDate"]);
mgr.SetCellTextValue(iRow, 10, drv["InvoiceID"]);
string uniqName = drv["Uniqname"].ToString();
if (uniqName.Length > 8)
{
uniqName = uniqName.Substring(0, 8);
}
mgr.SetCellTextValue(iRow, 11, uniqName); //2009-01-20 Only 8 character is allowed
mgr.SetCellTextValue(iRow, 15, drv["DepartmentalReferenceNumber"]);
mgr.SetCellTextValue(iRow, 17, drv["ItemDescription"]);
mgr.SetCellNumberValue(iRow, 23, drv["QuantityVouchered"]);
mgr.SetCellNumberValue(iRow, 25, Convert.ToDouble(drv["UnitOfMeasure"]));
mgr.SetCellNumberValue(iRow, 26, Convert.ToDouble(drv["MerchandiseAmount"]));
iRow += 1;
}
//Add the last row - which is the summary unit
mgr.SetCellTextValue(iRow, 0, summaryUnit.CardType);
mgr.SetCellTextValue(iRow, 1, summaryUnit.ShortCode);
mgr.SetCellTextValue(iRow, 2, summaryUnit.Account);
mgr.SetCellTextValue(iRow, 3, summaryUnit.FundCode);
mgr.SetCellTextValue(iRow, 4, summaryUnit.DeptID);
mgr.SetCellTextValue(iRow, 5, summaryUnit.ProgramCode);
mgr.SetCellTextValue(iRow, 6, summaryUnit.ClassName);
mgr.SetCellTextValue(iRow, 7, summaryUnit.ProjectGrant);
mgr.SetCellTextValue(iRow, 9, summaryUnit.InvoiceDate);
mgr.SetCellTextValue(iRow, 11, summaryUnit.Uniqname);
mgr.SetCellTextValue(iRow, 17, summaryUnit.ItemDescription);
mgr.SetCellNumberValue(iRow, 23, "1.0000");
mgr.SetCellNumberValue(iRow, 25, summaryUnit.MerchandiseAmount.ToString() + "000");
mgr.SetCellFormula(iRow, 26, string.Format("=-SUM(AB2:AB{0})", iRow));
string workFilePath = workPathDir + "\\" + JEType;
if (EndPeriod == StartPeriod.AddMonths(1))
{
workFilePath += "_" + StartPeriod.ToString("yyyy-MM") + Path.GetExtension(fileName);
}
else
{
workFilePath += "_" + StartPeriod.ToString("yyyy-MM") + "_" + EndPeriod.ToString("yyyy-MM") + Path.GetExtension(fileName);
}
mgr.SaveAs(workFilePath);
return(workFilePath);
}
}
protected override void FillDataTable(DataTable dt)
{
BillingUnit summaryUnit = summaryUnits.First();
Compile mCompile = new Compile();
DataTable dtRoomDB = mCompile.CalcCost("Room", string.Empty, "ChargeTypeID", 5, EndPeriod.AddMonths(-1), 0, 0, Compile.AggType.CliAcctType);
DataTable dtClientWithCharges = mCompile.GetTable(1);
double roomCapCost = mCompile.CapCost;
//*****************************************************************************
//2008-01-22 The code below is an EXTRA step for calculating the cost of room charge
// Right now the strategy is not to change Compile.CalcCost at all and if I want to
// add new features that would affect CalcCost, I would rather do it after CalcCost is called.
// But future new design is required else the system will get too complicated.
//2208-05-15 the reason why we are doing this extra step is to show NAP rooms (as of now, it's DC Test lab and Chem room)
//with correct monthly fee on the JE
//dtNAPRoomForAllChargeType's columns
//CostID
//ChargeTypeID
//TableNameOrDescript
//RoomID
//AcctPer
//AddVal
//RoomCost
//effDate
//Get all active NAP Rooms with their costs, all chargetypes are returned
//This is a temporary table, it's used to derive the really useful table below
DataTable dtNAPRoomForAllChargeType = BLL.RoomManager.GetAllNAPRoomsWithCosts(EndPeriod);
//filter out the chargetype so that we only have Internal costs with each NAP room
DataRow[] drsNAPRoomForInternal = dtNAPRoomForAllChargeType.Select("ChargeTypeID = 5");
//Loop through each room and find out this specified month's apportionment data.
foreach (DataRow dr1 in drsNAPRoomForInternal)
{
DataTable dtApportionData = BLL.RoomApportionDataManager.GetNAPRoomApportionDataByPeriod(StartPeriod, EndPeriod, dr1.Field <int>("RoomID"));
foreach (DataRow dr2 in dtApportionData.Rows)
{
DataRow[] drs = dtRoomDB.Select(string.Format("ClientID = {0} AND AccountID = {1} AND RoomID = {2}", dr2["ClientID"], dr2["AccountID"], dr2["RoomID"]));
if (drs.Length == 1)
{
drs[0].SetField("TotalCalcCost", (dr2.Field <double>("Percentage") * dr1.Field <double>("RoomCost")) / 100);
}
}
}
dtRoomDB.Columns.Add("DebitAccount", typeof(string));
dtRoomDB.Columns.Add("CreditAccount", typeof(string));
dtRoomDB.Columns.Add("LineDesc", typeof(string));
dtRoomDB.Columns.Add("TotalAllAccountCost", typeof(double));
//dtRoom - ClientID, AccountID, RoomID, TotalCalCost, TotalEntries, TotalHours
// cap costs - capping is per clientorg, thus apportion cappeing across charges
// note that this assumes that there is only one org for internal academic!!!
object temp;
double totalRoomCharges;
foreach (DataRow drCWC in dtClientWithCharges.Rows)
{
temp = dtRoomDB.Compute("SUM(TotalCalcCost)", string.Format("ClientID = {0}", drCWC["ClientID"]));
if (temp == null || temp == DBNull.Value)
{
totalRoomCharges = 0;
}
else
{
totalRoomCharges = Convert.ToDouble(temp);
}
if (totalRoomCharges > roomCapCost)
{
DataRow[] fdr = dtRoomDB.Select(string.Format("ClientID = {0}", drCWC["ClientID"]));
for (int i = 0; i < fdr.Length; i++)
{
fdr[i].SetField("TotalCalcCost", fdr[i].Field <double>("TotalCalcCost") * roomCapCost / totalRoomCharges);
}
}
}
DataTable dtClient = ClientDA.GetAllClient(StartPeriod, EndPeriod);
DataTable dtAccount = AccountDA.GetAllInternalAccount(StartPeriod, EndPeriod);
DataTable dtClientAccount = ClientDA.GetAllClientAccountWithManagerName(StartPeriod, EndPeriod); //used to find out manager's name
//Get the general lab account ID and lab credit account ID
GlobalCost gc = GlobalCostDA.GetGlobalCost();
//2008-05-15 very complicated code - trying to figure out the percentage distribution for monthly users, since the "TotalCalcCost" has
//been calculated based on percentage in the CalcCost function, so we need to figure out the percentage here again by findind out the total
//and divide the individual record's "TotalCalcCost'
foreach (DataRow drCWC in dtClientWithCharges.Rows)
{
DataRow[] fdr = dtRoomDB.Select(string.Format("ClientID = {0} AND RoomID = {1}", drCWC["ClientID"], (int)BLL.LabRoom.CleanRoom));
if (fdr.Length > 1)
{
//this user has multiple account for the clean room usage, so we have to find out the total of all accounts on this clean room
double tempTotal = Convert.ToDouble(dtRoomDB.Compute("SUM(TotalCalcCost)", string.Format("ClientID = {0} AND RoomID = {1}", drCWC["ClientID"], (int)BLL.LabRoom.CleanRoom)));
DataRow[] fdrRoom = dtRoomDB.Select(string.Format("ClientID = {0} AND RoomID = {1}", drCWC["ClientID"], (int)BLL.LabRoom.CleanRoom));
for (int i = 0; i < fdrRoom.Length; i++)
{
fdrRoom[i].SetField("TotalAllAccountCost", tempTotal); //assign the total to each record
}
}
}
//2008-08-28 Get Billing Type
DataTable dtBillingType = BillingTypeDA.GetAllBillingTypes();
foreach (DataRow dr in dtRoomDB.Rows)
{
dr["DebitAccount"] = dtAccount.Rows.Find(dr.Field <int>("AccountID"))["Number"];
dr["CreditAccount"] = dtAccount.Rows.Find(gc.LabCreditAccountID)["Number"];
//2007-06-19 financial manager may not be an administrator, but their username must be on JE
dr["LineDesc"] = GetLineDesc(dr, dtClient, dtBillingType);
//2008-05-15 the code below handles the clean room monthly users - it's special code that we have to get rid of when all
//billingtype are all gone
int billingTypeId = dr.Field <int>("BillingType");
if (dr.Field <BLL.LabRoom>("RoomID") == BLL.LabRoom.CleanRoom) //6 is clean room
{
if (BillingTypes.IsMonthlyUserBillingType(billingTypeId))
{
if (dr["TotalAllAccountCost"] == DBNull.Value)
{
//if TotalAllAccountCost is nothing, it means this user has only one account
//2008-10-27 but it might also that the user has only one internal account, and he apportion all hours to his external accouts
//so we must also check 'TotalHours' to make sure the user has more than 0 hours
if (dr.Field <double>("TotalHours") != 0)
{
dr.SetField("TotalCalcCost", BLL.BillingTypeManager.GetTotalCostByBillingType(billingTypeId, 0, 0, BLL.LabRoom.CleanRoom, 1315));
}
}
else
{
double total = dr.Field <double>("TotalAllAccountCost");
dr.SetField("TotalCalcCost", (dr.Field <double>("TotalCalcCost") / total) * BLL.BillingTypeManager.GetTotalCostByBillingType(billingTypeId, 0, 0, BLL.LabRoom.CleanRoom, 1315));
}
}
}
}
//****** apply filters ******
//Get the list below so that we can exclude users who spent less than X minutes in lab(Clean or Chem) in this month
DataTable dtlistClean = RoomUsageData.GetUserListLessThanXMin(StartPeriod, EndPeriod, int.Parse(ConfigurationManager.AppSettings["CleanRoomMinTimeMinute"]), "CleanRoom");
DataTable dtlistChem = RoomUsageData.GetUserListLessThanXMin(StartPeriod, EndPeriod, int.Parse(ConfigurationManager.AppSettings["ChemRoomMinTimeMinute"]), "ChemRoom");
//For performance issue, we have to calculate something first, since it's used on all rows
string depRefNum = string.Empty;
double fTotal = 0;
string creditAccount = dtAccount.Rows.Find(gc.LabCreditAccountID)["Number"].ToString();
string creditAccountShortCode = dtAccount.Rows.Find(gc.LabCreditAccountID)["ShortCode"].ToString();
//Do not show an item if the charge and xcharge accounts are the 'same' - can only happen for 941975
//Do not show items that are associated with specific accounts - need to allow users to add manually here in future
foreach (DataRow sdr in dtRoomDB.Rows)
{
if (sdr.Field <double>("TotalCalcCost") > 0)
{
var excludedAccounts = new[] { gc.LabAccountID, 143, 179, 188 };
if (!excludedAccounts.Contains(sdr.Field <int>("AccountID")) && sdr.Field <int>("BillingType") != BillingTypes.Other)
{
//2006-12-21 get rid of people who stayed in the lab less than 30 minutes in a month
string expression = string.Format("ClientID = {0}", sdr["ClientID"]);
DataRow[] foundRows;
bool flag = false;
if (sdr.Field <BLL.LabRoom>("RoomID") == BLL.LabRoom.CleanRoom) //6 = clean room
{
foundRows = dtlistClean.Select(expression);
}
else if (sdr.Field <BLL.LabRoom>("RoomID") == BLL.LabRoom.ChemRoom) //25 = chem room
{
foundRows = dtlistChem.Select(expression);
}
else //DCLab
{
foundRows = null;
}
if (foundRows == null)
{
flag = true; //add to the SUB
}
else
{
if (foundRows.Length == 0)
{
flag = true;
}
}
if (flag) //if no foundrow, we can add this client to JE
{
DataRow ndr = dt.NewRow();
DataRow drAccount = dtAccount.Rows.Find(sdr.Field <int>("AccountID"));
string debitAccount = drAccount["Number"].ToString();
string shortCode = drAccount["ShortCode"].ToString();
//get manager's name
DataRow[] drClientAccount = dtClientAccount.Select(string.Format("AccountID = {0}", sdr["AccountID"]));
if (drClientAccount.Length > 0)
{
depRefNum = drClientAccount[0]["ManagerName"].ToString();
}
else
{
depRefNum = "No Manager Found";
}
AccountNumber debitAccountNum = AccountNumber.Parse(debitAccount);
ndr["CardType"] = 1;
ndr["ShortCode"] = shortCode;
ndr["Account"] = debitAccountNum.Account;
ndr["FundCode"] = debitAccountNum.FundCode;
ndr["DeptID"] = debitAccountNum.DeptID;
ndr["ProgramCode"] = debitAccountNum.ProgramCode;
ndr["Class"] = debitAccountNum.Class;
ndr["ProjectGrant"] = debitAccountNum.ProjectGrant;
ndr["VendorID"] = "0000456136";
ndr["InvoiceDate"] = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
ndr["InvoiceID"] = $"{ReportSettings.CompanyName} Room Charge";
ndr["Uniqname"] = dtClient.Rows.Find(sdr.Field <int>("ClientID"))["UserName"];
ndr["DepartmentalReferenceNumber"] = depRefNum;
ndr["ItemDescription"] = GetItemDesc(sdr, dtClient, dtBillingType);
ndr["QuantityVouchered"] = "1.0000";
double chargeAmount = Math.Round(sdr.Field <double>("TotalCalcCost"), 5);
ndr["UnitOfMeasure"] = chargeAmount;
ndr["MerchandiseAmount"] = Math.Round(chargeAmount, 2);
ndr["CreditAccount"] = creditAccount;
//Used to calculate the total credit amount
fTotal += chargeAmount;
dt.Rows.Add(ndr);
}
}
}
}
//Summary row
summaryUnit.CardType = 1;
summaryUnit.ShortCode = creditAccountShortCode;
summaryUnit.Account = creditAccount.Substring(0, 6);
summaryUnit.FundCode = creditAccount.Substring(6, 5);
summaryUnit.DeptID = creditAccount.Substring(11, 6);
summaryUnit.ProgramCode = creditAccount.Substring(17, 5);
summaryUnit.ClassName = creditAccount.Substring(22, 5);
summaryUnit.ProjectGrant = creditAccount.Substring(27, 7);
summaryUnit.InvoiceDate = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
summaryUnit.Uniqname = ReportSettings.FinancialManagerUserName;
summaryUnit.DepartmentalReferenceNumber = depRefNum;
summaryUnit.ItemDescription = ReportSettings.FinancialManagerUserName;
summaryUnit.MerchandiseAmount = Math.Round(-fTotal, 2);
summaryUnit.CreditAccount = creditAccount;
//Clean things up manually might help performance in general
dtRoomDB.Clear();
dtClient.Clear();
dtAccount.Clear();
}
//this should be called by the inheriting class in the GenerateDataTablesForSUB override
protected override void ProcessTable(DataTable dtBilling)
{
DataTable dtReport = InitTable();
string deptRefNum = string.Empty;
double subsidyDiscount = 0;
double total = 0;
BillingUnit summary = new BillingUnit();
//for loop each record in ClientID and AccountID aggregate
foreach (DataRow cadr in ClientAccountData.Rows)
{
if (cadr.RowState != DataRowState.Deleted)
{
ValidPeriodCheck(cadr);
double chargeAmount = Math.Round(Utility.ConvertTo(dtBilling.Compute("SUM(LineCost)", DataRowFilter(cadr)), 0D), 2);
if (dtBilling.Columns.Contains("SubsidyDiscount"))
{
subsidyDiscount = Utility.ConvertTo(dtBilling.Compute("SUM(SubsidyDiscount)", DataRowFilter(cadr)), 0D);
}
if (chargeAmount != 0)
{
//2011-02-08 There will be some unavoidable rounding difference, so we basicaly ignore anything for 1 cent
if (Math.Abs(chargeAmount) > 0.01)
{
DataRow[] billingrows = dtBilling.Select(DataRowFilter(cadr));
if (billingrows.Length > 0)
{
DataRow dr = billingrows[0];
string debitAcctNumber = Utility.ConvertTo(dr["Number"], string.Empty);
ReportAccount debitAcct = new ReportAccount(debitAcctNumber);
//get manager's name
deptRefNum = ManagerName(cadr);
DateTime p = Utility.ConvertTo(dr["Period"], DateTime.MinValue);
DateTime invoiceDate = (p.Equals(DateTime.MinValue)) ? Report.EndPeriod.AddMonths(-1) : p;
DataRow newdr = dtReport.NewRow();
newdr["ReportType"] = Utility.EnumToString(Report.ReportType);
newdr["ChargeType"] = Utility.EnumToString(Report.BillingCategory);
newdr["Period"] = dr["Period"];
newdr["CardType"] = 1;
newdr["ShortCode"] = dr["ShortCode"];
newdr["Account"] = debitAcct.Account;
newdr["FundCode"] = debitAcct.FundCode;
newdr["DeptID"] = debitAcct.DeptID;
newdr["ProgramCode"] = debitAcct.ProgramCode;
newdr["Class"] = debitAcct.Class;
newdr["ProjectGrant"] = debitAcct.ProjectGrant;
newdr["VendorID"] = "0000456136"; //wtf?
newdr["InvoiceDate"] = invoiceDate.ToString("yyyy/MM/dd");
newdr["InvoiceID"] = GetInvoiceID();
newdr["Uniqname"] = dr["UserName"];
newdr["DepartmentalReferenceNumber"] = deptRefNum;
newdr["ItemDescription"] = GetItemDescription(dr);
newdr["QuantityVouchered"] = "1.0000";
newdr["CreditAccount"] = CreditAccount;
newdr["UsageCharge"] = Math.Round(chargeAmount, 2).ToString("0.00");
newdr["SubsidyDiscount"] = Math.Round(subsidyDiscount, 2).ToString("0.00");
newdr["BilledCharge"] = Math.Round(chargeAmount - subsidyDiscount, 2).ToString("0.00");
newdr["UnitOfMeasure"] = Math.Round(chargeAmount, 5).ToString("0.00000");
newdr["MerchandiseAmount"] = newdr["UsageCharge"];
//Used to calculate the total credit amount
total += chargeAmount;
//for testing purpose
newdr["AccountID"] = cadr["AccountID"];
dtReport.Rows.Add(newdr);
}
}
}
}
}
_ReportTables.Add(dtReport);
//Summary row
ReportAccount credit_acct = new ReportAccount(CreditAccount);
summary.CardType = 1;
summary.ShortCode = CreditAccountShortCode;
summary.Account = credit_acct.Account;
summary.FundCode = credit_acct.FundCode;
summary.DeptID = credit_acct.DeptID;
summary.ProgramCode = credit_acct.ProgramCode;
summary.ClassName = credit_acct.Class;
summary.ProjectGrant = credit_acct.ProjectGrant;
summary.InvoiceDate = Report.EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
summary.Uniqname = ReportSettings.FinancialManagerUserName;
summary.DepartmentalReferenceNumber = deptRefNum;
summary.ItemDescription = ReportSettings.FinancialManagerUserName;
summary.MerchandiseAmount = -total;
summary.CreditAccount = CreditAccount;
summary.QuantityVouchered = "1.0000";
AddSummary(summary);
double SumUsageCharge = 0;
double SumSubsidyDiscount = 0;
double SumBilledCharge = 0;
foreach (DataRow dr in dtReport.Rows)
{
SumUsageCharge += Utility.ConvertTo(dr["UsageCharge"], 0D);
SumSubsidyDiscount += Utility.ConvertTo(dr["SubsidyDiscount"], 0D);
SumBilledCharge += Utility.ConvertTo(dr["BilledCharge"], 0D);
if (Report.BillingCategory == BillingCategory.Store)
{
dr["UsageCharge"] = string.Empty;
dr["SubsidyDiscount"] = string.Empty;
}
}
DataRow totalrow = dtReport.NewRow();
totalrow["ReportType"] = Utility.EnumToString(Report.ReportType);
totalrow["ChargeType"] = Utility.EnumToString(Report.BillingCategory);
totalrow["Period"] = Report.EndPeriod.AddMonths(-1);
totalrow["ShortCode"] = dtReport.Rows.Count - 1;
totalrow["UsageCharge"] = (Report.BillingCategory == BillingCategory.Store) ? string.Empty : SumUsageCharge.ToString("0.00");
totalrow["SubsidyDiscount"] = (Report.BillingCategory == BillingCategory.Store) ? string.Empty : SumSubsidyDiscount.ToString("0.00");
totalrow["BilledCharge"] = SumBilledCharge.ToString("0.00");
dtReport.Rows.Add(totalrow);
}
protected override void FillDataTable(DataTable dt)
{
BillingUnit summaryUnit = summaryUnits.First();
Compile mCompile = new Compile();
DataTable dtToolDB = mCompile.CalcCost("Tool", string.Empty, "ChargeTypeID", 5, EndPeriod.AddMonths(-1), 0, 0, Compile.AggType.CliAcct);
DataTable dtClientWithCharges = mCompile.GetTable(1);
double toolCapCost = mCompile.CapCost;
// cap costs - capping is per ClientOrg, thus apportion capping across charges
// note that this assumes that there is only one org for internal academic!!!
object temp;
double totalToolCharges;
foreach (DataRow drCWC in dtClientWithCharges.Rows)
{
temp = dtToolDB.Compute("SUM(TotalCalcCost)", string.Format("ClientID = {0}", drCWC["ClientID"]));
if (temp == null || temp == DBNull.Value)
{
totalToolCharges = 0;
}
else
{
totalToolCharges = Convert.ToDouble(temp);
}
if (totalToolCharges > toolCapCost)
{
DataRow[] fdr = dtToolDB.Select(string.Format("ClientID = {0}", drCWC["ClientID"]));
for (int i = 0; i < fdr.Length; i++)
{
fdr[i].SetField("TotalCalcCost", fdr[i].Field <double>("TotalCalcCost") * toolCapCost / totalToolCharges);
}
}
}
DataTable dtClient = ClientDA.GetAllClient(StartPeriod, EndPeriod);
DataTable dtAccount = AccountDA.GetAllInternalAccount(StartPeriod, EndPeriod);
DataTable dtClientAccount = ClientDA.GetAllClientAccountWithManagerName(StartPeriod, EndPeriod); //used to find out manager's name
DataTable dtBillingType = BillingTypeDA.GetAllBillingTypes();
//Get the general lab account ID and lab credit account ID
GlobalCost gc = GlobalCostDA.GetGlobalCost();
//For performance issue, we have to calculate something first, since it's used on all rows
string depRefNum = string.Empty;
double fTotal = 0;
string creditAccount = dtAccount.Rows.Find(gc.LabCreditAccountID)["Number"].ToString();
string creditAccountShortCode = dtAccount.Rows.Find(gc.LabCreditAccountID)["ShortCode"].ToString();
//Do not show an item if the charge and xcharge accounts are the 'same' - can only happen for 941975
//Do not show items that are associated with specific accounts - need to allow users to add manually here in future
foreach (DataRow sdr in dtToolDB.Rows)
{
if (sdr.Field <double>("TotalCalcCost") > 0)
{
var excludedAccounts = new[] { gc.LabAccountID, 143, 179, 188 };
if (!excludedAccounts.Contains(sdr.Field <int>("AccountID")) && sdr.Field <int>("BillingType") != BillingTypes.Other)
{
DataRow ndr = dt.NewRow();
DataRow drAccount = dtAccount.Rows.Find(sdr.Field <int>("AccountID"));
string debitAccount = drAccount["Number"].ToString();
string shortCode = drAccount["ShortCode"].ToString();
//get manager's name
DataRow[] drClientAccount = dtClientAccount.Select(string.Format("AccountID = {0}", sdr["AccountID"]));
if (drClientAccount.Length > 0)
{
depRefNum = drClientAccount.First()["ManagerName"].ToString();
}
else
{
depRefNum = "No Manager Found";
}
AccountNumber debitAccountNum = AccountNumber.Parse(debitAccount);
ndr["CardType"] = 1;
ndr["ShortCode"] = shortCode;
ndr["Account"] = debitAccountNum.Account;
ndr["FundCode"] = debitAccountNum.FundCode;
ndr["DeptID"] = debitAccountNum.DeptID;
ndr["ProgramCode"] = debitAccountNum.ProgramCode;
ndr["Class"] = debitAccountNum.Class;
ndr["ProjectGrant"] = debitAccountNum.ProjectGrant;
ndr["VendorID"] = "0000456136";
ndr["InvoiceDate"] = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
ndr["InvoiceID"] = $"{ReportSettings.CompanyName} Tool Charge";
ndr["Uniqname"] = dtClient.Rows.Find(sdr.Field <int>("ClientID"))["UserName"];
ndr["DepartmentalReferenceNumber"] = depRefNum;
ndr["ItemDescription"] = GetItemDesc(sdr, dtClient, dtBillingType);
ndr["QuantityVouchered"] = "1.0000";
double chargeAmount = Math.Round(sdr.Field <double>("TotalCalcCost"), 5);
ndr["UnitOfMeasure"] = chargeAmount.ToString();
ndr["MerchandiseAmount"] = Math.Round(chargeAmount, 2).ToString();
ndr["CreditAccount"] = creditAccount;
//Used to calculate the total credit amount
fTotal += chargeAmount;
dt.Rows.Add(ndr);
}
}
}
//Summary row
AccountNumber creditAccountNum = AccountNumber.Parse(creditAccount);
summaryUnit.CardType = 1;
summaryUnit.ShortCode = creditAccountShortCode;
summaryUnit.Account = creditAccountNum.Account;
summaryUnit.FundCode = creditAccountNum.FundCode;
summaryUnit.DeptID = creditAccountNum.DeptID;
summaryUnit.ProgramCode = creditAccountNum.ProgramCode;
summaryUnit.ClassName = creditAccountNum.Class;
summaryUnit.ProjectGrant = creditAccountNum.ProjectGrant;
summaryUnit.InvoiceDate = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
summaryUnit.Uniqname = ReportSettings.FinancialManagerUserName;
summaryUnit.DepartmentalReferenceNumber = depRefNum;
summaryUnit.ItemDescription = ReportSettings.FinancialManagerUserName;
summaryUnit.MerchandiseAmount = Math.Round(-fTotal, 2);
summaryUnit.CreditAccount = creditAccount;
//Clean things up manually might help performance in general
dtToolDB.Clear();
dtClient.Clear();
dtAccount.Clear();
}
public override string GenerateExcelFile(DataTable dt)
{
BillingUnit summaryUnit1 = summaryUnits[0];
BillingUnit summaryUnit2 = summaryUnits[1];
//Complete code rewrite is needed in here
//The problem stems form Material JE has two summary rows
//The SummaryUnit and SummaryUnit2 are shifted due to Credit account # sorting.
DataView dv = dt.DefaultView;
dv.Sort = "CreditAccount ASC, ItemDescription ASC, ProjectGrant ASC";
string lastCreditAccount = "default";
//Contruct the excel object
string fileName = Utility.GetRequiredAppSetting("SUB_Template");
string templatePath = HttpContext.Current.Server.MapPath($".\\SpreadSheets\\Templates\\{fileName}");
string workPathDir = HttpContext.Current.Server.MapPath(".\\SpreadSheets\\Work");
using (var mgr = ExcelUtility.NewExcelManager())
{
mgr.OpenWorkbook(templatePath);
mgr.SetActiveWorksheet("Sheet1");
int iRow = 1;
int iRowNumber2 = 0; //this keep the last row of the first portion of Store SUB, needed this to formulate correct formula for total sum cell
foreach (DataRowView drv in dv)
{
string creditAccount = drv["CreditAccount"].ToString();
if (creditAccount != lastCreditAccount && lastCreditAccount != "default" && summaryUnit2 != null)
{
mgr.SetCellTextValue(iRow, 0, summaryUnit1.CardType);
mgr.SetCellTextValue(iRow, 1, summaryUnit1.ShortCode);
mgr.SetCellTextValue(iRow, 2, summaryUnit1.Account);
mgr.SetCellTextValue(iRow, 3, summaryUnit1.FundCode);
mgr.SetCellTextValue(iRow, 4, summaryUnit1.DeptID);
mgr.SetCellTextValue(iRow, 5, summaryUnit1.ProgramCode);
mgr.SetCellTextValue(iRow, 6, summaryUnit1.ClassName);
mgr.SetCellTextValue(iRow, 7, summaryUnit1.ProjectGrant);
mgr.SetCellTextValue(iRow, 9, summaryUnit1.InvoiceDate);
mgr.SetCellTextValue(iRow, 11, summaryUnit1.Uniqname);
mgr.SetCellTextValue(iRow, 18, summaryUnit1.ItemDescription);
mgr.SetCellTextValue(iRow, 24, summaryUnit1.QuantityVouchered);
mgr.SetCellFormula(iRow, 27, string.Format("=-SUM(AB2:AB{0})", iRow));
iRow += 1;
iRowNumber2 = iRow + 1;
}
mgr.SetCellTextValue(iRow, 0, drv["CardType"]);
mgr.SetCellTextValue(iRow, 1, drv["ShortCode"]);
mgr.SetCellTextValue(iRow, 2, drv["Account"]);
mgr.SetCellTextValue(iRow, 3, drv["FundCode"]);
mgr.SetCellTextValue(iRow, 4, drv["DeptID"]);
mgr.SetCellTextValue(iRow, 5, drv["ProgramCode"]);
mgr.SetCellTextValue(iRow, 6, drv["Class"]);
mgr.SetCellTextValue(iRow, 7, drv["ProjectGrant"]);
mgr.SetCellTextValue(iRow, 8, drv["VendorID"]);
mgr.SetCellTextValue(iRow, 9, drv["InvoiceDate"]);
mgr.SetCellTextValue(iRow, 10, drv["InvoiceID"]);
string uniqName = drv["Uniqname"].ToString();
if (uniqName.Length > 8)
{
uniqName = uniqName.Substring(0, 8);
}
mgr.SetCellTextValue(iRow, 11, uniqName);
mgr.SetCellTextValue(iRow, 15, drv["DepartmentalReferenceNumber"]);
mgr.SetCellTextValue(iRow, 18, drv["ItemDescription"]);
mgr.SetCellTextValue(iRow, 24, drv["QuantityVouchered"]);
mgr.SetCellTextValue(iRow, 26, Convert.ToDouble(drv["UnitOfMeasure"]));
mgr.SetCellTextValue(iRow, 27, Convert.ToDouble(drv["MerchandiseAmount"]));
iRow += 1;
lastCreditAccount = creditAccount;
}
//Add the last row - which is the summary unit
mgr.SetCellTextValue(iRow, 0, summaryUnit2.CardType);
mgr.SetCellTextValue(iRow, 1, summaryUnit2.ShortCode);
mgr.SetCellTextValue(iRow, 2, summaryUnit2.Account);
mgr.SetCellTextValue(iRow, 3, summaryUnit2.FundCode);
mgr.SetCellTextValue(iRow, 4, summaryUnit2.DeptID);
mgr.SetCellTextValue(iRow, 5, summaryUnit2.ProgramCode);
mgr.SetCellTextValue(iRow, 6, summaryUnit2.ClassName);
mgr.SetCellTextValue(iRow, 7, summaryUnit2.ProjectGrant);
mgr.SetCellTextValue(iRow, 9, summaryUnit2.InvoiceDate);
mgr.SetCellTextValue(iRow, 11, summaryUnit2.Uniqname);
mgr.SetCellTextValue(iRow, 18, summaryUnit2.ItemDescription);
mgr.SetCellTextValue(iRow, 24, summaryUnit2.QuantityVouchered);
mgr.SetCellFormula(iRow, 27, string.Format("=-SUM(AB{0}:AB{1})", iRowNumber2, iRow));
mgr.SetColumnCollapsed("I", true);
mgr.SetColumnCollapsed("J", true);
mgr.SetColumnWidth(10, 1);
string workFilePath = workPathDir + "\\" + "StoreSUB" + "_";
if (EndPeriod == StartPeriod.AddMonths(1))
{
workFilePath += StartPeriod.ToString("yyyy-MM") + Path.GetExtension(fileName);
}
else
{
workFilePath += StartPeriod.ToString("yyyy-MM") + "_" + EndPeriod.ToString("yyyy-MM") + Path.GetExtension(fileName);
}
mgr.SaveAs(workFilePath);
return(workFilePath);
}
}
protected override void FillDataTable(DataTable dt)
{
BillingUnit summaryUnit1 = summaryUnits[0];
BillingUnit summaryUnit2 = summaryUnits[1];
Compile mCompile = new Compile();
//Get Cleints who order items in store in the Period with Credit and Debit and TotalCost calculated
DataTable dtStoreDB = mCompile.CalcCost("StoreJE", string.Empty, string.Empty, 0, EndPeriod.AddMonths(-1), 0, 0, Compile.AggType.CliAcct);
//Return dtStoreDB
//DataTable dtStore = new DataTable();
//BuildDataTable(dtStore);
DataTable dtClient = ClientDA.GetAllClient(StartPeriod, EndPeriod);
DataTable dtAccount = AccountDA.GetAllInternalAccount(StartPeriod, EndPeriod);
DataTable dtClientAccount = ClientDA.GetAllClientAccountWithManagerName(StartPeriod, EndPeriod); //used to find out manager's name
//Get the general lab account ID and lab credit account ID
GlobalCost gc = GlobalCostDA.GetGlobalCost();
//For performance issue, we have to calculate something first, since it's used on all rows
string depRefNum = string.Empty;
double fTotal = 0;
string lastCreditAccount = "default";
string creditAccount = string.Empty;
string creditAccountShortCode = string.Empty; //we also have to show those credit accounts' shortcodes
string lastCreditAccountShortCode = string.Empty; //we need this, just like we need 'LastCreditAccount' to track the changes
AccountNumber creditAccountNum;
DataView dv = dtStoreDB.DefaultView;
dv.Sort = "CreditAccountID";
//This for loop will loop through each transaction record and create SUB record on every transactional record
foreach (DataRowView sdr in dv)
{
//do not show an item if the charge and xcharge accounts are the 'same' - can only happen for 941975
if (!(Convert.ToInt32(sdr["DebitAccountID"]) == gc.LabAccountID && Convert.ToInt32(sdr["CreditAccountID"]) == gc.LabCreditAccountID))
{
DataRow ndr = dt.NewRow();
DataRow drAccount = dtAccount.Rows.Find(Convert.ToInt32(sdr["DebitAccountID"]));
string debitAccount = string.Empty;
string shortCode = string.Empty;
if (drAccount == null)
{
debitAccount = string.Format("unknown_{0}", sdr["DebitAccountID"]);
shortCode = string.Format("unknown_{0}", sdr["DebitAccountID"]);
}
else
{
debitAccount = drAccount["Number"].ToString();
shortCode = drAccount["ShortCode"].ToString();
}
//get manager's name
DataRow[] drClientAccount = dtClientAccount.Select(string.Format("AccountID = {0}", sdr["DebitAccountID"]));
if (drClientAccount.Length > 0)
{
depRefNum = drClientAccount[0]["ManagerName"].ToString();
}
else
{
depRefNum = "No Manager Found";
}
ndr["CardType"] = 1;
ndr["ShortCode"] = shortCode;
AccountNumber debitAccountNum = AccountNumber.Parse(debitAccount);
ndr["Account"] = debitAccountNum.Account;
ndr["FundCode"] = debitAccountNum.FundCode;
ndr["DeptID"] = debitAccountNum.DeptID;
ndr["ProgramCode"] = debitAccountNum.ProgramCode;
ndr["Class"] = debitAccountNum.Class;
ndr["ProjectGrant"] = debitAccountNum.ProjectGrant;
ndr["InvoiceDate"] = StartPeriod.ToString("yyyy/MM/dd");
ndr["InvoiceID"] = $"{ReportSettings.CompanyName} Store Charge";
ndr["Uniqname"] = dtClient.Rows.Find(Convert.ToInt32(sdr["ClientID"]))["UserName"];
ndr["DepartmentalReferenceNumber"] = depRefNum;
ndr["ItemDescription"] = dtClient.Rows.Find(Convert.ToInt32(sdr["ClientID"]))["DisplayName"].ToString().Substring(0, 30);
ndr["QuantityVouchered"] = "1.0000";
double chargeAmount = Math.Round(Convert.ToDouble(sdr["TotalCalcCost"]), 5);
ndr["UnitOfMeasure"] = chargeAmount;
ndr["MerchandiseAmount"] = Math.Round(chargeAmount, 2);
creditAccount = dtAccount.Rows.Find(Convert.ToInt32(sdr["CreditAccountID"]))["Number"].ToString();
ndr["CreditAccount"] = creditAccount;
//2008-10-09 Depend on credit account, we have different vendor ID
creditAccountNum = AccountNumber.Parse(creditAccount);
if (creditAccountNum.ProjectGrant == "U023440")
{
ndr["VendorID"] = "0000456136";
}
else
{
ndr["VendorID"] = "0000456133";
}
//Used to calculate the total credit amount
fTotal += chargeAmount;
//2008-10-08 We have to find out the shortcode for the credit account as well, requested by Sandrine
creditAccountShortCode = dtAccount.Rows.Find(Convert.ToInt32(sdr["CreditAccountID"]))["ShortCode"].ToString();
if (creditAccount != lastCreditAccount && lastCreditAccount != "default")
{
//Summary row
fTotal -= chargeAmount; //we have to deduct the charge amount again because its no longer belong to this group
AccountNumber lastCreditAccountNum = AccountNumber.Parse(lastCreditAccount);
summaryUnit2.CardType = 1;
summaryUnit2.ShortCode = lastCreditAccountShortCode;
summaryUnit2.Account = lastCreditAccountNum.Account;
summaryUnit2.FundCode = lastCreditAccountNum.FundCode;
summaryUnit2.DeptID = lastCreditAccountNum.DeptID;
summaryUnit2.ProgramCode = lastCreditAccountNum.ProgramCode;
summaryUnit2.ClassName = lastCreditAccountNum.Class;
summaryUnit2.ProjectGrant = lastCreditAccountNum.ProjectGrant;
summaryUnit2.InvoiceDate = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
summaryUnit2.Uniqname = "CreditAccount";
summaryUnit2.DepartmentalReferenceNumber = depRefNum;
summaryUnit2.ItemDescription = "CreditAccount";
summaryUnit2.MerchandiseAmount = Math.Round(-fTotal, 2);
summaryUnit2.CreditAccount = creditAccount;
fTotal = chargeAmount; //add the chargeamount back, because we have new group
}
lastCreditAccount = creditAccount;
lastCreditAccountShortCode = creditAccountShortCode;
dt.Rows.Add(ndr);
}
}
//Summary row
creditAccountNum = AccountNumber.Parse(creditAccount);
summaryUnit1.CardType = 1;
summaryUnit1.ShortCode = creditAccountShortCode;
summaryUnit1.Account = creditAccountNum.Account;
summaryUnit1.FundCode = creditAccountNum.FundCode;
summaryUnit1.DeptID = creditAccountNum.DeptID;
summaryUnit1.ProgramCode = creditAccountNum.ProgramCode;
summaryUnit1.ClassName = creditAccountNum.Class;
summaryUnit1.ProjectGrant = creditAccountNum.ProjectGrant;
summaryUnit1.InvoiceDate = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
summaryUnit1.Uniqname = "CreditAccount";
summaryUnit1.DepartmentalReferenceNumber = depRefNum;
summaryUnit1.ItemDescription = "CreditAccount";
summaryUnit1.MerchandiseAmount = Math.Round(-fTotal, 2);
summaryUnit1.CreditAccount = creditAccount;
//Clean things up manually might help performance in general
dtStoreDB.Clear();
dtClient.Clear();
dtAccount.Clear();
}
