Exemplo n.º 1
0
        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();
        }
Exemplo n.º 2
0
 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);
 }
Exemplo n.º 3
0
        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");
        }
Exemplo n.º 4
0
        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");
        }
Exemplo n.º 5
0
        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");
        }
Exemplo n.º 6
0
        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);
        }
Exemplo n.º 7
0
        //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);
            }
        }
Exemplo n.º 8
0
        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);
            }
        }
Exemplo n.º 9
0
        //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);
            }
        }