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();
}
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);
}
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");
}
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);
}
}
//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);
}
}