public void InitClientData(Client clsClient)
{
DateTime StartPeriod;
DateTime EndPeriod;
string[] DateStuff = ServiceDate.Split('/');
ClientStuff.NoCommodity = clsClient.clsHH.NoCommodities;
ClientStuff.SupplementalOnly = clsClient.clsHH.SupplOnly;
ClientStuff.HaveCSFP = (clsClient.clsHH.NbrCSFP > 0);
ClientStuff.FamilySize = clsClient.clsHH.TotalFamily;
ClientStuff.Homeless = clsClient.clsHH.Homeless;
ClientStuff.Transient = (clsClient.clsHH.ClientType == CCFBPrefs.TransientId);
TrxLog trxLogWork = new TrxLog(CCFBGlobal.connectionString);
//Nbr Service This Month
StartPeriod = Convert.ToDateTime(DateStuff[0].ToString() + "/01/" + DateStuff[2].ToString());
EndPeriod = StartPeriod.AddMonths(1).AddDays(-1);
trxLogWork.openUsingDateRange(clsClient.clsHH.ID, StartPeriod, EndPeriod);
ClientStuff.NbrServicesThisMonth = trxLogWork.RowCount;
//Nbr Services This Fiscal Year
StartPeriod = CCFBGlobal.CalcFiscalStartDate(Convert.ToDateTime(ServiceDate));
EndPeriod = CCFBGlobal.CalcFiscalEndDate(Convert.ToDateTime(ServiceDate));
trxLogWork.openUsingDateRange(clsClient.clsHH.ID, StartPeriod, EndPeriod);
ClientStuff.NbrServicesThisFiscalYr = trxLogWork.RowCount;
//Nbr Services This Calendar Year
if (CCFBPrefs.FiscalYearStartMonth != 1)
{
StartPeriod = Convert.ToDateTime("01/01/" + DateStuff[2].ToString());
EndPeriod = StartPeriod.AddYears(1).AddDays(-1);
trxLogWork.openUsingDateRange(clsClient.clsHH.ID, StartPeriod, EndPeriod);
}
ClientStuff.NbrServicesThisCalYr = trxLogWork.RowCount;
ClearSelected();
}
public override XElement EncodeForActiveX()
{
XElement toRet = base.EncodeForActiveX();
toRet.SetAttributeValue("startTime", StartPeriod.ToEpoch());
toRet.SetAttributeValue("endTime", EndPeriod.ToEpoch());
return(toRet);
}
/// <summary>
/// Return the HashCode of this object.
/// </summary>
/// <returns>The HashCode of this object.</returns>
public override Int32 GetHashCode()
{
unchecked
{
return(StartPeriod.GetHashCode() * 17 ^
Limit.GetHashCode() * 11 ^
NumberPhases.GetHashCode());
}
}
private void LoadGridJU(JournalUnitReportItem[] items, CreditEntry creditEntry)
{
gvJU.Columns[1].Visible = false;
gvJU.Columns[2].Visible = false;
gvJU.Columns[3].Visible = EndPeriod != StartPeriod.AddMonths(1);
gvJU.DataSource = items;
gvJU.DataBind();
SetTotalText(creditEntry.MerchandiseAmount);
}
/// <summary>
/// Compares two id tag infos for equality.
/// </summary>
/// <param name="ChargingSchedulePeriod">An id tag info to compare with.</param>
/// <returns>True if both match; False otherwise.</returns>
public Boolean Equals(ChargingSchedulePeriod ChargingSchedulePeriod)
{
if ((Object)ChargingSchedulePeriod == null)
{
return(false);
}
return(StartPeriod.Equals(ChargingSchedulePeriod.StartPeriod) &&
Limit.Equals(ChargingSchedulePeriod.Limit) &&
NumberPhases.Equals(ChargingSchedulePeriod.NumberPhases));
}
private void ProcessHtmlAllJU(JournalUnitTypes juType)
{
double total;
List <JournalUnitReportItem> allItems;
var factory = GetReportFactory();
switch (juType)
{
case JournalUnitTypes.A:
case JournalUnitTypes.B:
case JournalUnitTypes.C:
RoomJU roomJU = factory.GetReportRoomJU(StartPeriod, EndPeriod, juType, GetClientID());
ToolJU toolJU = factory.GetReportToolJU(StartPeriod, EndPeriod, juType, GetClientID());
allItems = ReportFactory.GetAllJU(roomJU, toolJU, out total);
break;
case JournalUnitTypes.All:
RoomJU roomJUA = factory.GetReportRoomJU(StartPeriod, EndPeriod, JournalUnitTypes.A, GetClientID());
ToolJU toolJUA = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.A, GetClientID());
RoomJU roomJUB = factory.GetReportRoomJU(StartPeriod, EndPeriod, JournalUnitTypes.B, GetClientID());
ToolJU toolJUB = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.B, GetClientID());
RoomJU roomJUC = factory.GetReportRoomJU(StartPeriod, EndPeriod, JournalUnitTypes.C, GetClientID());
ToolJU toolJUC = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.C, GetClientID());
double temp;
total = 0;
allItems = new List <JournalUnitReportItem>();
allItems.AddRange(ReportFactory.GetAllJU(roomJUA, toolJUA, out temp));
total += temp;
allItems.AddRange(ReportFactory.GetAllJU(roomJUB, toolJUB, out temp));
total += temp;
allItems.AddRange(ReportFactory.GetAllJU(roomJUC, toolJUC, out temp));
total += temp;
break;
default:
throw new ArgumentException(string.Format("Invalid JournalUnitTypes value: {0}", juType));
}
gvJU.Columns[1].Visible = true;
gvJU.Columns[2].Visible = juType == JournalUnitTypes.All;
gvJU.Columns[3].Visible = EndPeriod != StartPeriod.AddMonths(1);
gvJU.DataSource = allItems;
gvJU.DataBind();
SetTotalText(total);
SetLinkText("ju-" + Enum.GetName(typeof(JournalUnitTypes), juType).ToLower(), "all");
}
private void ProcessHtmlAllSUB()
{
var factory = GetReportFactory();
RoomSUB roomSUB = factory.GetReportRoomSUB(StartPeriod, EndPeriod, GetClientID());
ToolSUB toolSUB = factory.GetReportToolSUB(StartPeriod, EndPeriod, GetClientID());
StoreSUB storeSUB = factory.GetReportStoreSUB(StartPeriod, EndPeriod, false, GetClientID());
IEnumerable <ServiceUnitBillingReportItem> allItems = ReportFactory.GetAllSUB(roomSUB, toolSUB, storeSUB, out double total);
gvSUB.Columns[1].Visible = true;
gvSUB.Columns[2].Visible = EndPeriod != StartPeriod.AddMonths(1);
gvSUB.DataSource = allItems;
gvSUB.DataBind();
SetTotalText(total);
SetLinkText("sub", "all");
}
private void ProcessAllToolJUHTML()
{
var factory = GetReportFactory();
var juA = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.A, GetClientID());
var juB = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.B, GetClientID());
var juC = factory.GetReportToolJU(StartPeriod, EndPeriod, JournalUnitTypes.C, GetClientID());
List <JournalUnitReportItem> allItems = ReportFactory.GetAllToolJU(juA, juB, juC, out double total);
gvJU.Columns[1].Visible = false;
gvJU.Columns[2].Visible = true;
gvJU.Columns[3].Visible = EndPeriod != StartPeriod.AddMonths(1);
gvJU.DataSource = allItems;
gvJU.DataBind();
SetTotalText(total);
SetLinkText("ju-all", "tool");
}
public override async Task Start(string fileName)
{
await GetHolidays();
using (var package = new ExcelPackage(new System.IO.FileInfo(fileName)))
{
var worksheet = package.Workbook.Worksheets[1];
var columnIndex = 2;
while (StartPeriod <= EndPeriod)
{
PopulateDateToSheet(StartPeriod, columnIndex, worksheet);
PopulateDateCapToSheet(StartPeriod, columnIndex, worksheet);
StartPeriod = StartPeriod.AddDays(1);
columnIndex++;
}
worksheet.Calculate();
package.Save();
}
}
private void LoadGridSUB(ServiceUnitBillingReportItem[][] items, BillingUnit[] summaries)
{
List <ServiceUnitBillingReportItem> allItems = new List <ServiceUnitBillingReportItem>();
foreach (var group in items)
{
allItems.AddRange(group);
}
double totalMerchAmount = 0;
foreach (var bu in summaries)
{
totalMerchAmount += bu.MerchandiseAmount;
}
gvSUB.Columns[1].Visible = false;
gvSUB.Columns[2].Visible = EndPeriod != StartPeriod.AddMonths(1);
gvSUB.DataSource = allItems;
gvSUB.DataBind();
SetTotalText(totalMerchAmount);
}
//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);
}
}
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();
}
protected void BtnDownloadAll_Click(object sender, EventArgs e)
{
MakeOrgGrid(false);
var items = (IEnumerable <ExternalInvoice>)rptInvoice.DataSource;
bool del = true;
if (items.Count() > 0)
{
var mgr = GetExternalInvoiceManager();
var invoices = mgr.GetInvoices();
foreach (ExternalInvoice i in items)
{
int accountId = i.Header.AccountID;
// get datarows for Org and Account
var inv = invoices.First(x => x.Header.AccountID == accountId);
string alert = string.Empty;
string fileName = ExcelUtility.GenerateInvoiceExcelReport(i, CurrentUser.ClientID, "Zip", del, ref alert);
del = false;
}
Response.Redirect("~/RepInvoice.aspx?DownloadAll=" + "ExternalInvoices_" + StartPeriod.ToString("yyyy-MM"));
}
}
private string GetDataURL(string report, string charge, bool twoCreditAccounts)
{
string result = VirtualPathUtility.ToAbsolute("~/data/");
result += string.Format("?report={0}&charge={1}&sdate={2}&edate={3}", report, charge, StartPeriod.ToString("yyyyMM"), EndPeriod.ToString("yyyyMM"));
if (charge == "store")
{
result += "&accts=" + (twoCreditAccounts ? "1" : "0");
}
return(result);
}
//This got moved from SUBBase.vb: I can't seem to import the Billing.Reports namespace in vb App_Code files becuase it's in a cs file, but I can here.
private string GenerateExcelSUB(ServiceUnitBillingReportItem[][] items, BillingUnit[] SummaryUnit, string ChargeType)
{
//Contruct the excel object
string fileName = Utility.GetRequiredAppSetting("SUB_Template");
string templatePath = ExcelUtility.GetTemplatePath(fileName);
string workPathDir = ExcelUtility.GetWorkPath(CurrentUser.ClientID);
using (var mgr = ExcelUtility.NewExcelManager())
{
mgr.OpenWorkbook(templatePath);
mgr.SetActiveWorksheet("Sheet1");
int iRow = 1; //zero based, iRow = 0 is Row #1 on spreadsheet (the header)
//dv.Sort = "ItemDescription ASC, ProjectGrant ASC";
foreach (ServiceUnitBillingReportItem item in items[0])
{
if (!string.IsNullOrEmpty(item.CardType))
{
mgr.SetCellTextValue(iRow, 0, item.CardType);
mgr.SetCellTextValue(iRow, 1, item.ShortCode);
mgr.SetCellTextValue(iRow, 2, item.Account);
mgr.SetCellTextValue(iRow, 3, item.FundCode);
mgr.SetCellTextValue(iRow, 4, item.DeptID);
mgr.SetCellTextValue(iRow, 5, item.ProgramCode);
mgr.SetCellTextValue(iRow, 6, item.Class);
mgr.SetCellTextValue(iRow, 7, item.ProjectGrant);
mgr.SetCellTextValue(iRow, 8, item.VendorID);
mgr.SetCellTextValue(iRow, 9, item.InvoiceDate);
mgr.SetCellTextValue(iRow, 10, item.InvoiceID);
string uniqName = item.Uniqname.ToString();
if (uniqName.Length > 8)
{
uniqName = uniqName.Substring(0, 8);
}
mgr.SetCellTextValue(iRow, 11, uniqName);
mgr.SetCellTextValue(iRow, 15, item.DepartmentalReferenceNumber);
//R = 17
//[2014-09-08 jg] All column S and higher are now shifted one to the left so 18 -> 17, 19 -> 18, etc
//ws.Cell[iRow, 18].Value = item.ItemDescription;
//ws.Cell[iRow, 24].Value = item.QuantityVouchered;
//ws.Cell[iRow, 26].Value = string.IsNullOrEmpty(item.UnitOfMeasure) ? 0 : Convert.ToDouble(item.UnitOfMeasure);
//ws.Cell[iRow, 27].Value = string.IsNullOrEmpty(item.MerchandiseAmount) ? 0 : Convert.ToDouble(item.MerchandiseAmount);
mgr.SetCellTextValue(iRow, 17, item.ItemDescription);
mgr.SetCellNumberValue(iRow, 23, item.QuantityVouchered);
mgr.SetCellNumberValue(iRow, 25, string.IsNullOrEmpty(item.UnitOfMeasure) ? 0 : Convert.ToDouble(item.UnitOfMeasure));
mgr.SetCellNumberValue(iRow, 26, string.IsNullOrEmpty(item.MerchandiseAmount) ? 0 : Convert.ToDouble(item.MerchandiseAmount));
iRow += 1;
}
}
mgr.SetCellTextValue(iRow, 0, SummaryUnit[0].CardType);
mgr.SetCellTextValue(iRow, 1, SummaryUnit[0].ShortCode);
mgr.SetCellTextValue(iRow, 2, SummaryUnit[0].Account);
mgr.SetCellTextValue(iRow, 3, SummaryUnit[0].FundCode);
mgr.SetCellTextValue(iRow, 4, SummaryUnit[0].DeptID);
mgr.SetCellTextValue(iRow, 5, SummaryUnit[0].ProgramCode);
mgr.SetCellTextValue(iRow, 6, SummaryUnit[0].ClassName);
mgr.SetCellTextValue(iRow, 7, SummaryUnit[0].ProjectGrant);
mgr.SetCellTextValue(iRow, 9, SummaryUnit[0].InvoiceDate);
mgr.SetCellTextValue(iRow, 11, SummaryUnit[0].Uniqname);
//ws.Cells[iRow, 18].Value = SummaryUnit[0].ItemDescription;
//ws.Cells[iRow, 24].Value = SummaryUnit[0].QuantityVouchered;
//ws.Cells[iRow, 26].Value = Math.Round(SummaryUnit[0].MerchandiseAmount, 5);
//ws.Cells[iRow, 27].Formula = string.Format("=-SUM(AB2:AB{0})", iRow);
mgr.SetCellTextValue(iRow, 17, SummaryUnit[0].ItemDescription);
mgr.SetCellNumberValue(iRow, 23, SummaryUnit[0].QuantityVouchered);
mgr.SetCellNumberValue(iRow, 25, Math.Round(SummaryUnit[0].MerchandiseAmount, 5));
mgr.SetCellFormula(iRow, 26, string.Format("=-SUM(AB2:AB{0})", iRow));
if (items.Length > 1 && SummaryUnit.Length > 1)
{
iRow += 1;
int startingRowNumber = iRow + 1; //we use excel formula to calculate the sum, so we have to keep this to get the starting range
//dv.Sort = "ItemDescription ASC, ProjectGrant ASC";
foreach (ServiceUnitBillingReportItem item in items[1])
{
if (!string.IsNullOrEmpty(item.CardType))
{
mgr.SetCellTextValue(iRow, 0, item.CardType);
mgr.SetCellTextValue(iRow, 1, item.ShortCode);
mgr.SetCellTextValue(iRow, 2, item.Account);
mgr.SetCellTextValue(iRow, 3, item.FundCode);
mgr.SetCellTextValue(iRow, 4, item.DeptID);
mgr.SetCellTextValue(iRow, 5, item.ProgramCode);
mgr.SetCellTextValue(iRow, 6, item.Class);
mgr.SetCellTextValue(iRow, 7, item.ProjectGrant);
mgr.SetCellTextValue(iRow, 8, item.VendorID);
mgr.SetCellTextValue(iRow, 9, item.InvoiceDate);
mgr.SetCellTextValue(iRow, 10, item.InvoiceID);
string uniqName = item.Uniqname;
if (uniqName.Length > 8)
{
uniqName = uniqName.Substring(0, 8);
}
mgr.SetCellTextValue(iRow, 11, uniqName);
mgr.SetCellTextValue(iRow, 15, item.DepartmentalReferenceNumber);
//ws.Cells[iRow, 18].Value = item.ItemDescription;
//ws.Cells[iRow, 24].Value = item.QuantityVouchered;
//ws.Cells[iRow, 26].Value = string.IsNullOrEmpty(item.UnitOfMeasure) ? 0 : Convert.ToDouble(item.UnitOfMeasure);
//ws.Cells[iRow, 27].Value = string.IsNullOrEmpty(item.MerchandiseAmount) ? 0 : Convert.ToDouble(item.MerchandiseAmount);
mgr.SetCellTextValue(iRow, 17, item.ItemDescription);
mgr.SetCellNumberValue(iRow, 23, item.QuantityVouchered);
mgr.SetCellNumberValue(iRow, 25, string.IsNullOrEmpty(item.UnitOfMeasure) ? 0 : Convert.ToDouble(item.UnitOfMeasure));
mgr.SetCellNumberValue(iRow, 26, string.IsNullOrEmpty(item.MerchandiseAmount) ? 0 : Convert.ToDouble(item.MerchandiseAmount));
iRow += 1;
}
}
mgr.SetCellTextValue(iRow, 0, SummaryUnit[1].CardType);
mgr.SetCellTextValue(iRow, 1, SummaryUnit[1].ShortCode);
mgr.SetCellTextValue(iRow, 2, SummaryUnit[1].Account);
mgr.SetCellTextValue(iRow, 3, SummaryUnit[1].FundCode);
mgr.SetCellTextValue(iRow, 4, SummaryUnit[1].DeptID);
mgr.SetCellTextValue(iRow, 5, SummaryUnit[1].ProgramCode);
mgr.SetCellTextValue(iRow, 6, SummaryUnit[1].ClassName);
mgr.SetCellTextValue(iRow, 7, SummaryUnit[1].ProjectGrant);
mgr.SetCellTextValue(iRow, 9, SummaryUnit[1].InvoiceDate);
mgr.SetCellTextValue(iRow, 11, SummaryUnit[1].Uniqname);
//ws.Cells[iRow, 18].Value = SummaryUnit[1].ItemDescription;
//ws.Cells[iRow, 24].Value = SummaryUnit[1].QuantityVouchered;
//ws.Cells[iRow, 26].Value = Math.Round(SummaryUnit[0].MerchandiseAmount, 5);
//ws.Cells[iRow, 27].Formula = string.Format("=-SUM(AB{0}:AB{1})", startingRowNumber, iRow);
mgr.SetCellTextValue(iRow, 17, SummaryUnit[1].ItemDescription);
mgr.SetCellNumberValue(iRow, 23, SummaryUnit[1].QuantityVouchered);
mgr.SetCellNumberValue(iRow, 25, Math.Round(SummaryUnit[0].MerchandiseAmount, 5));
mgr.SetCellFormula(iRow, 26, string.Format("=-SUM(AB{0}:AB{1})", startingRowNumber, iRow));
mgr.SetColumnCollapsed("I", true);
mgr.SetColumnCollapsed("J", true);
mgr.SetColumnWidth(10, 1);
}
if (!Directory.Exists(workPathDir))
{
Directory.CreateDirectory(workPathDir);
}
// the sub number
string workFilePath = Path.Combine(workPathDir, ChargeType + "SUB_" + StartPeriod.ToString("yyyy-MM"));
if (EndPeriod == StartPeriod.AddMonths(1))
{
workFilePath += Path.GetExtension(fileName);
}
else
{
workFilePath += "_" + EndPeriod.AddMonths(-1).ToString("yyyy-MM") + Path.GetExtension(fileName);
}
mgr.SaveAs(workFilePath);
return(workFilePath);
}
}
