public EndPeriodCollection GetEndPeriodRate(Currency Currency)
 {
     int _periodID =  PeriodService.Instance.GetCurrentPeriod()[0].ID;
     EndPeriod _endPeriod = new EndPeriod
     {
         Period_ID = _periodID,
         Currency = new Currency { CurrencyID = Currency.CurrencyID }
     };
     using (EndPeriodAccessClient _endPeriodAccessClient = new EndPeriodAccessClient(EndpointName.EndPeriodAccess))
     {
         return new EndPeriodCollection(_endPeriodAccessClient.Query(_endPeriod));
     }
 }
 public bool InsertEndPeriod(EndPeriod endPeriod)
 {
     var _endPeriodCollection = GetEndPeriodRate(endPeriod.Currency);
     if (_endPeriodCollection.Any())
         return false;
     else
     {
         try
         {
             using (EndPeriodAccessClient _endPeriodAccessClient = new EndPeriodAccessClient(EndpointName.EndPeriodAccess))
             {
                 _endPeriodAccessClient.Insert(endPeriod);
                 return true;
             }
         }
         catch
         {
             return false;
         }
     }
 }
Beispiel #3
0
        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();
        }
Beispiel #4
0
        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();
        }
Beispiel #5
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);
            }
        }
Beispiel #6
0
        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();
        }