Esempio n. 1
0
        //public static string GenerateInvoiceExcelReport(DateTime startPeriod, DateTime endPeriod, int clientId, bool showRemote, ExternalInvoiceHeader orgItem, string subFolder, bool deleteWorkDir, ref string alert)
        //{
        //    ExternalInvoiceManager mgr = new ExternalInvoiceManager(orgItem.AccountID, startPeriod, endPeriod, showRemote);
        //    string result = GenerateInvoiceExcelReport(mgr, clientId, orgItem, subFolder, deleteWorkDir, ref alert);
        //    return result;
        //}

        public static string MakeSpreadSheet(int accountId, string invoiceNumber, string deptRef, string orgName, int currentUserClientId, DateTime startPeriod, DateTime endPeriod)
        {
            var dtAddress = DataCommand.Create()
                            .Param("Action", "ByAccount")
                            .Param("AccountID", accountId)
                            .FillDataTable("dbo.Address_Select");

            // get client data
            // using All is hackish. It was ByOrg, but this caused a problem with remote users
            // the other option is to select for each client, but that is probably even less efficient
            var dtClient = DataCommand.Create()
                           .Param("Action", "All")
                           .Param("sDate", startPeriod)
                           .Param("eDate", endPeriod)
                           .FillDataTable("dbo.Client_Select");

            dtClient.PrimaryKey = new[] { dtClient.Columns["ClientID"] };

            // NOTE: all reports here are for external users
            DataRow   drUsage;
            DataTable dtUsage = new DataTable();

            dtUsage.Columns.Add("Descrip", typeof(string));
            dtUsage.Columns.Add("Quantity", typeof(double));
            dtUsage.Columns.Add("Cost", typeof(double));

            DataRow   cdr;
            DataTable dtAggCost;

            string[]  costType = { "Room", "StoreInv", "Tool", "Misc" };
            Compile   mCompile = new Compile();
            DataTable dtClientWithCharges;
            double    capCost;
            object    temp;
            double    totalCharges;

            //2009-01 this for loop will loop through different Cost types, so when added code for each specific type, remember to distinguish each CostType
            for (int i = 0; i < costType.Length; i++)
            {
                dtAggCost = mCompile.CalcCost(costType[i], string.Empty, "AccountID", accountId, startPeriod, 0, 0, Compile.AggType.CliAcct);
                //dtAggCost is the main table that contains chargeable items
                //0 ClientID
                //1 AccountID
                //2 RoomID
                //3 TotalCalcCost
                //4 TotalEntries
                //5 TotalHours

                //Only Room costtype will execute the code below
                if (costType[i] == "Room")
                {
                    //******** The code below handles the cost of NAP rooms, because at this point, all NAP rooms access data have total cost of zero****
                    //Get all active NAP Rooms with their costs, all chargetypes are returned
                    DataTable dtNAPRoomForAllChargeType = RoomManager.GetAllNAPRoomsWithCosts(startPeriod);

                    //filter out the chargetype so that we only have Internal costs with each NAP room
                    DataRow[] dtdrsNAPRoomForExternal = dtNAPRoomForAllChargeType.Select(string.Format("ChargeTypeID = {0}", AccountDA.GetChargeType(accountId)));

                    //Loop through each room and find out this specified month's apportionment data.
                    foreach (DataRow dr1 in dtdrsNAPRoomForExternal)
                    {
                        DataTable dtApportionData = RoomApportionDataManager.GetNAPRoomApportionDataByPeriod(startPeriod, endPeriod, dr1.Field <int>("RoomID"));

                        foreach (DataRow dr2 in dtApportionData.Rows)
                        {
                            DataRow[] drs = dtAggCost.Select(String.Format("ClientID = {0} AND AccountID = {1} AND RoomID = {2}", dr2["ClientID"], dr2["AccountID"], dr2["RoomID"]));

                            if (drs.Length == 1)
                            {
                                //2008-06-19 Sandrine requested all monthly room should have charge of the same across all organizations
                                //so if a guy works for two diferent companies, he should be charged full amount for both companeis on all monthly rooms.
                                //the only exception is when the apportionment percentage is 0 for this organization.  When it's 0 percent, we simply
                                //cannot charge this organizaiton at all
                                if (dr2.Field <double>("Percentage") > 0)
                                {
                                    drs[0]["TotalCalcCost"] = dr1["RoomCost"];
                                }
                            }
                        }
                    }

                    //2009-01 remember not to charge clean/chem room usage for less than x amount of minutes
                    int cleanRoomMinMinutes = int.Parse(ConfigurationManager.AppSettings["CleanRoomMinTimeMinute"]);
                    int chemRoomMinMinutes  = int.Parse(ConfigurationManager.AppSettings["ChemRoomMinTimeMinute"]);

                    //we simply set totalCalcCost to 0.0 at this point, then those 0.0 charges items will not be published to excel report
                    foreach (DataRow drAggCost in dtAggCost.Rows)
                    {
                        if (drAggCost.Field <LabRoom>("RoomID") == LabRoom.CleanRoom)
                        {
                            if (drAggCost.Field <double>("TotalHours") < cleanRoomMinMinutes / 60)
                            {
                                drAggCost.SetField("TotalCalcCost", 0.0);
                            }
                        }
                        else if (drAggCost.Field <LabRoom>("RoomID") == LabRoom.ChemRoom)
                        {
                            if (drAggCost.Field <double>("TotalHours") < chemRoomMinMinutes / 60)
                            {
                                drAggCost.SetField("TotalCalcCost", 0.0);
                            }
                        }
                    }
                }

                if (costType[i] != "Misc")
                {
                    dtClientWithCharges = mCompile.GetTable(1);
                    capCost             = mCompile.CapCost;

                    foreach (DataRow drCWC in dtClientWithCharges.Rows)
                    {
                        temp = dtAggCost.Compute("SUM(TotalCalcCost)", string.Format("ClientID = {0}", drCWC["ClientID"]));
                        if (temp == null || temp == DBNull.Value)
                        {
                            totalCharges = 0.0;
                        }
                        else
                        {
                            totalCharges = Convert.ToDouble(temp);
                        }

                        //BUG FIX: I have to exclude StoreInv charge here since the CapCost for it is always 0
                        if (totalCharges > capCost && costType[i] != "StoreInv")
                        {
                            DataRow[] fdr = dtAggCost.Select(string.Format("ClientID = {0}", drCWC["ClientID"]));
                            for (int j = 0; j < fdr.Length; j++)
                            {
                                fdr[j].SetField("TotalCalcCost", fdr[j].Field <double>("TotalCalcCost") * capCost / totalCharges);
                            }
                        }
                    }
                }

                foreach (DataRow drAggCost in dtAggCost.Rows)
                {
                    cdr = dtClient.Rows.Find(drAggCost.Field <int>("ClientID"));

                    drUsage = dtUsage.NewRow();
                    if (costType[i] == "Misc")
                    {
                        drUsage["Descrip"]  = drAggCost["Description"];
                        drUsage["Quantity"] = drAggCost["Quantity"];
                        drUsage["Cost"]     = drAggCost["UnitCost"];
                    }
                    else
                    {
                        drUsage["Descrip"]  = string.Format("{0} usage for {1}. {2}", costType[i].Substring(0, 5), cdr["FName"].ToString().Substring(0, 1), cdr["LName"]);
                        drUsage["Quantity"] = 1;
                        drUsage["Cost"]     = drAggCost["TotalCalcCost"];
                    }
                    dtUsage.Rows.Add(drUsage);
                }
            }

            // Write to excel
            using (var mgr = NewExcelManager())
            {
                string fileName = Utility.GetRequiredAppSetting("Invoice_Template");
                mgr.OpenWorkbook(GetTemplatePath(fileName));
                mgr.SetActiveWorksheet("Invoice");

                // show invoice date
                mgr.SetCellTextValue("F4", DateTime.Now.ToShortDateString());

                int startRow;
                int useRow = 0;
                foreach (DataRow drAddr in dtAddress.Rows)
                {
                    if (drAddr["AddrType"].ToString() == "Billing")
                    {
                        startRow = 7;
                    }
                    else
                    {
                        startRow = 13;
                    }

                    FillField(mgr, "C", startRow, orgName, ref useRow);
                    FillField(mgr, "C", 0, drAddr.Field <string>("InternalAddress"), ref useRow);
                    FillField(mgr, "C", 0, drAddr.Field <string>("StrAddress1"), ref useRow);
                    FillField(mgr, "C", 0, drAddr.Field <string>("StrAddress2"), ref useRow);
                    FillField(mgr, "C", 0, drAddr.Field <string>("City") + ", " + drAddr.Field <string>("State") + " " + drAddr.Field <string>("Zip"), ref useRow);
                }

                // invoice number that needs to be entered
                mgr.SetCellTextValue("E12", invoiceNumber);
                mgr.SetCellTextValue("I14", deptRef);

                int    rowRef = 21;
                string rowCell;

                // now print charges
                foreach (DataRow dr in dtUsage.Rows)
                {
                    if (dr.Field <double>("Cost") != 0)
                    {
                        rowCell = "C" + rowRef.ToString();
                        mgr.SetCellTextValue(rowCell, startPeriod.ToString("MM/yyyy"));

                        rowCell = "D" + rowRef.ToString();
                        mgr.SetCellTextValue(rowCell, dr["Descrip"]);

                        rowCell = "H" + rowRef.ToString();
                        mgr.SetCellNumberValue(rowCell, dr["Quantity"]);

                        rowCell = "I" + rowRef.ToString();
                        mgr.SetCellNumberValue(rowCell, dr["Cost"]);

                        rowRef += 1;
                    }
                }

                string workFilePath = Path.Combine(GetWorkPath(currentUserClientId), orgName + Path.GetExtension(fileName));
                mgr.SaveAs(workFilePath);

                return(workFilePath);
            }
        }
Esempio n. 2
0
        private void GetDataForAllDataGrids(DateTime period, int clientId)
        {
            // empty datagrids
            dgRoom.DataSource = null;
            dgRoom.DataBind();

            gvTool.DataSource = null;
            gvTool.DataBind();

            dgStore.DataSource = null;
            dgStore.DataBind();

            lblRoom.Visible  = true;
            lblTool.Visible  = true;
            lblStore.Visible = true;

            object  sumCost;
            Compile compile = new Compile();

            //Room realted

            //2008-01-15
            //dtRoomCost has the following columns
            //0	"ClientID"
            //1	"AccountID"
            //2	"RoomID"
            //3	"BillingType"
            //4	"TotalCalcCost"
            //5	"TotalEntries"
            //6	"TotalHours"

            DataTable dtRoomCost = compile.CalcCost("Room", string.Empty, string.Empty, 0, period, 0, clientId, Compile.AggType.CliAcctType);

            dtRoomCost.Columns.Add("Room", typeof(string));
            dtRoomCost.Columns.Add("Name", typeof(string));
            dtRoomCost.Columns.Add("BillingTypeName", typeof(string));
            dtRoomCost.Columns.Add("OrgName", typeof(string));
            dtRoomCost.Columns.Add("LineCost", typeof(double));
            dtRoomCost.Columns.Add("ShortCode", typeof(string));

            //Create the list to contain all summary total for each organization
            //List<UsageSummaryTotal> mylist = new List<UsageSummaryTotal>();

            DataTable SummaryTable = new DataTable();

            SummaryTable.Columns.Add("OrgID", typeof(int));
            SummaryTable.Columns.Add("OrgName", typeof(string));
            SummaryTable.Columns.Add("BillingTypeID", typeof(int));
            SummaryTable.Columns.Add("RoomTotal", typeof(double));
            SummaryTable.Columns.Add("ToolTotal", typeof(double));
            SummaryTable.Columns.Add("StoreTotal", typeof(double));

            //It's possible that the above code makes the table row count to 0.
            //If it's the case, we have to skip the code below, but why delete the only row?
            if (dtRoomCost.Rows.Count > 0)
            {
                //we have to get the total lab hours for monthly users because we have to find out the appropriate proportion of monthly fee distribution
                int     currentBillingTypeId = dtRoomCost.Rows[0].Field <int>("BillingTypeID");
                decimal totalCleanRoomHours  = 0; //this stores the total clean room hours for this user at this month
                decimal totalChemRoomHours   = 0; //this stores the total chem room hours
                int[]   specialBillingTypesForSomeUnknownReason = { BillingTypes.ExtAc_Ga, BillingTypes.ExtAc_Si, BillingTypes.Int_Si, BillingTypes.Int_Ga };
                if (specialBillingTypesForSomeUnknownReason.Contains(currentBillingTypeId))
                {
                    try
                    {
                        //2008-06-12 it's possible that user access only chem room and no clean room at all, so it will return DB Null if no clean room hours
                        totalCleanRoomHours = Convert.ToDecimal(dtRoomCost.Compute("SUM(TotalHours)", "RoomID = 6"));
                    }
                    catch
                    {
                        totalCleanRoomHours = 0;
                    }

                    try
                    {
                        //2008-06-12 it's possible that user access only chem room and no clean room at all, so it will return DB Null if no clean room hours
                        totalChemRoomHours = Convert.ToDecimal(dtRoomCost.Compute("SUM(TotalHours)", "RoomID = 25"));
                    }
                    catch
                    {
                        totalChemRoomHours = 0;
                    }
                }

                int previousOrgId = dtRoomCost.Rows[0].Field <int>("OrgID");

                DataRow nr = SummaryTable.NewRow();
                nr["OrgID"]         = previousOrgId;
                nr["OrgName"]       = dsReport.Tables["Org"].Rows.Find(previousOrgId)["OrgName"];
                nr["BillingTypeID"] = dtRoomCost.Rows[0]["BillingType"];
                nr["RoomTotal"]     = 0;
                nr["ToolTotal"]     = 0;
                nr["StoreTotal"]    = 0;

                SummaryTable.Rows.Add(nr);

                //**************** NAP room handling ******************
                //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 = AppCode.BLL.RoomManager.GetAllNAPRoomsWithCosts(period);

                //filter out the chargetype so that we only have Internal costs with each NAP room
                //2009-04-05 the chartype id is difficult to get here, so we assume everyone is interanl.  This is okay, because we need to find out the percentage, not the actual cost
                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 = RoomApportionDataManager.GetNAPRoomApportionDataByPeriod(period, dr1.Field <int>("RoomID"));

                    foreach (DataRow dr2 in dtApportionData.Rows)
                    {
                        DataRow[] drs = dtRoomCost.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") / 100D);
                        }
                        else
                        {
                            //?
                        }
                    }

                    ////We now have the data of each room on this specific month, next we loop through the accounts
                    //foreach (DataRow dr2 in dtRoom.Rows)
                    //{
                    //    if (dr2.Field<int>("RoomID") == dr1.Field<int>("RoomID"))
                    //    {
                    //        dr2.SetField("TotalCalcCost", (drs[0].Field<double>("Percentage") * dr1.Field<double>("RoomCost")) / 100D);
                    //        DataRow[] drs = dtApportionData.Select();
                    //    }
                    //}
                }


                //**************** main loop to do the fee calculation ******
                decimal tempTotalHours = 0;
                foreach (DataRow dr in dtRoomCost.Rows)
                {
                    dr["Room"]      = dsReport.Tables["Room"].Rows.Find(dr["RoomID"])["Room"];
                    dr["Name"]      = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["Name"];
                    dr["ShortCode"] = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["ShortCode"];
                    DataRow drBillingType = dsReport.Tables["BillingType"].Rows.Find(dr["BillingType"]);
                    if (drBillingType != null)
                    {
                        dr["BillingTypeName"] = drBillingType["BillingTypeName"];
                    }
                    else
                    {
                        dr["BillingTypeName"] = "[nothing]";
                    }

                    dr["OrgName"] = dsReport.Tables["Org"].Rows.Find(dr["OrgID"])["OrgName"];

                    if (dr.Field <int>("RoomID") == 6)
                    {
                        tempTotalHours = totalCleanRoomHours;
                    }
                    else if (dr.Field <int>("RoomID") == 25)
                    {
                        tempTotalHours = totalChemRoomHours;
                    }

                    dr["LineCost"] = BillingTypeManager.GetTotalCostByBillingType(dr.Field <int>("BillingType"), dr.Field <decimal>("TotalHours"), dr.Field <decimal>("TotalEntries"), dr.Field <LabRoom>("RoomID"), dr.Field <decimal>("TotalCalcCost"), tempTotalHours);

                    int currentOrgId = dr.Field <int>("OrgID");
                    if (previousOrgId != currentOrgId)
                    {
                        nr                  = SummaryTable.NewRow();
                        nr["OrgID"]         = currentOrgId;
                        nr["OrgName"]       = dr["OrgName"];
                        nr["BillingTypeID"] = dr["BillingType"];
                        SummaryTable.Rows.Add(nr);

                        previousOrgId = currentOrgId;
                    }
                }

                foreach (DataRow r in SummaryTable.Rows)
                {
                    r["RoomTotal"] = dtRoomCost.Compute("SUM(LineCost)", string.Format("OrgID = {0}", r["OrgID"]));
                }

                //Get total cost for this table
                sumCost           = dtRoomCost.Compute("SUM(LineCost)", string.Empty);
                lblRoom.Text      = string.Format("Total room usage fees: {0:C}", sumCost);
                lblRoom.ForeColor = System.Drawing.Color.Red;

                dgRoom.DataSource = dtRoomCost;
                dgRoom.DataBind();
            }
            else
            {
                lblRoom.Text      = "No room usage during period";
                lblRoom.ForeColor = System.Drawing.Color.Red;

                foreach (DataRow r in SummaryTable.Rows)
                {
                    r["RoomTotal"] = 0;
                }
            }


            //***************** Tool related *******************************
            //2007-02-23 Must handle the exception here because if the user doesn't exist on that period, a error occur inside the CalcCost function
            DataTable dtToolCost;

            //0 ClientID
            //1 AccountID
            //2 ResourceID
            //3 IsStarted
            //4 TotalCalcCost
            //5 TotalUses
            //6 TotalActDuration

            try
            {
                dtToolCost = compile.CalcCost("Tool", string.Empty, string.Empty, 0, period, 0, clientId, Compile.AggType.CliAcctType);
            }
            catch
            {
                dtToolCost = null;
            }

            if (dtToolCost.Rows.Count > 0)
            {
                dtToolCost.Columns.Add("ResourceName", typeof(string));
                dtToolCost.Columns.Add("AccountName", typeof(string));
                dtToolCost.Columns.Add("OrgName", typeof(string));
                dtToolCost.Columns.Add("ShortCode", typeof(string));

                //Populate the two new columns, ResourceName and AccountName
                foreach (DataRow dr in dtToolCost.Rows)
                {
                    dr["ResourceName"] = dsReport.Tables["Resource"].Rows.Find(dr["ResourceID"])["ResourceName"];
                    dr["AccountName"]  = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["Name"];
                    dr["ShortCode"]    = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["ShortCode"];
                    dr["OrgName"]      = dsReport.Tables["Org"].Rows.Find(dr["OrgID"])["OrgName"];
                }

                DataTable dtCloneActivated = dtToolCost.Clone();
                DataTable dtCloneCancelled = dtToolCost.Clone();
                DataTable dtCloneForgiven  = dtToolCost.Clone();

                DataRow nr;
                foreach (DataRow dr in dtToolCost.Rows)
                {
                    if (dr.Field <int>("IsStarted") == 1)
                    {
                        if (dr.Field <int>("ToolChargeMultiplierMul") == 1)
                        {
                            nr = dtCloneActivated.NewRow();
                            nr["ResourceName"]     = dr["ResourceName"];
                            nr["TotalUses"]        = dr["TotalUses"];
                            nr["TotalActDuration"] = dr["TotalActDuration"];
                            nr["TotalCalcCost"]    = dr["TotalCalcCost"];
                            nr["AccountName"]      = dr["AccountName"];
                            nr["OrgName"]          = dr["OrgName"];
                            nr["OrgID"]            = dr["OrgID"];
                            dtCloneActivated.Rows.Add(nr);
                        }
                        else
                        {
                            //all forgiven records are collected here
                            nr = dtCloneForgiven.NewRow();
                            nr["ResourceName"]     = dr["ResourceName"];
                            nr["TotalUses"]        = dr["TotalUses"];
                            nr["TotalActDuration"] = dr["TotalActDuration"];
                            nr["TotalCalcCost"]    = dr["TotalCalcCost"];
                            nr["AccountName"]      = dr["AccountName"];
                            nr["OrgName"]          = dr["OrgName"];
                            nr["OrgID"]            = dr["OrgID"];
                            dtCloneForgiven.Rows.Add(nr);
                        }
                    }
                    else
                    {
                        nr = dtCloneCancelled.NewRow();
                        nr["ResourceName"]     = dr["ResourceName"];
                        nr["TotalUses"]        = dr["TotalUses"];
                        nr["TotalActDuration"] = dr["TotalActDuration"];
                        nr["TotalCalcCost"]    = dr["TotalCalcCost"];
                        nr["AccountName"]      = dr["AccountName"];
                        nr["OrgName"]          = dr["OrgName"];
                        nr["OrgID"]            = dr["OrgID"];
                        dtCloneCancelled.Rows.Add(nr);
                    }
                }

                //We claculate the total for each tables accordingly
                double totalToolCost = 0;

                if (dtCloneActivated.Rows.Count > 0)
                {
                    //to calculate the sum for activated too usage fee
                    sumCost                  = dtCloneActivated.Compute("SUM(TotalCalcCost)", string.Empty);
                    totalToolCost            = Convert.ToDouble(sumCost);
                    lblActivatedToolFee.Text = string.Format("Sub Total: {0:C}", sumCost);

                    foreach (DataRow r in SummaryTable.Rows)
                    {
                        r["ToolTotal"] = dtCloneActivated.Compute("SUM(TotalCalcCost)", string.Format("OrgID = {0}", r["OrgID"]));
                    }
                }
                else
                {
                    lblActivatedToolFee.Text = "Sub Total: $0";
                }

                if (dtCloneCancelled.Rows.Count > 0)
                {
                    //to calculate the sum for cancelled too usage fee
                    sumCost                  = dtCloneCancelled.Compute("SUM(TotalCalcCost)", string.Empty);
                    totalToolCost           += Convert.ToDouble(sumCost);
                    lblCancelledToolFee.Text = string.Format("Sub Total: {0:C}", sumCost);

                    foreach (DataRow r in SummaryTable.Rows)
                    {
                        object obj = dtCloneCancelled.Compute("SUM(TotalCalcCost)", string.Format("OrgID = {0}", r["OrgID"]));

                        if (obj != null && obj != DBNull.Value)
                        {
                            r["ToolTotal"] = r.Field <double>("ToolTotal") + Convert.ToDouble(obj);
                        }
                    }
                }
                else
                {
                    lblCancelledToolFee.Text = "Sub Total: $0";
                }

                if (dtCloneForgiven.Rows.Count > 0)
                {
                    sumCost = dtCloneForgiven.Compute("SUM(TotalCalcCost)", string.Empty);
                    lblForgivenToolFee.Text = string.Format("Sub Total: {0:$#,##0.00;($#,##0.00)}", sumCost);
                }
                else
                {
                    lblForgivenToolFee.Text = "Sub Total: $0";
                }

                gvTool.DataSource = dtCloneActivated;
                gvTool.DataBind();

                gvToolCancelled.DataSource = dtCloneCancelled;
                gvToolCancelled.DataBind();

                gvToolForgiven.DataSource = dtCloneForgiven;
                gvToolForgiven.DataBind();

                lblTool.Text = string.Format("Total tool usage fees: {0:C}", totalToolCost);
                //lblTool.Text += " (This doesn't include the fees that have been forgiven by tool engineers)";
                lblTool.ForeColor = System.Drawing.Color.Red;
                divTool.Visible   = true;
            }
            else
            {
                lblTool.Text      = "No tool usage during period";
                lblTool.ForeColor = System.Drawing.Color.Red;
                divTool.Visible   = false;

                foreach (DataRow r in SummaryTable.Rows)
                {
                    r["ToolTotal"] = 0;
                }
            }

            //********************* Store related ***************************
            DataTable dtStoreCost = compile.CalcCost("StoreInv", string.Empty, string.Empty, 0, period, 0, clientId, Compile.AggType.None);

            dtStoreCost.Columns.Add("Item", typeof(string));
            dtStoreCost.Columns.Add("Name", typeof(string));
            dtStoreCost.Columns.Add("OrgName", typeof(string));
            dtStoreCost.Columns.Add("ShortCode", typeof(string));

            if (dtStoreCost.Rows.Count > 0)
            {
                foreach (DataRow dr in dtStoreCost.Rows)
                {
                    dr["Item"]      = dsReport.Tables["Item"].Rows.Find(dr["ItemID"])["Item"];
                    dr["Name"]      = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["Name"];
                    dr["ShortCode"] = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["ShortCode"];
                    dr["OrgName"]   = dsReport.Tables["Org"].Rows.Find(dr["OrgID"])["OrgName"];
                }
                sumCost       = dtStoreCost.Compute("SUM(CalcCost)", string.Empty);
                lblStore.Text = string.Format("Total store usage fees: {0:C}", sumCost);

                foreach (DataRow r in SummaryTable.Rows)
                {
                    r["StoreTotal"] = dtStoreCost.Compute("SUM(CalcCost)", string.Format("OrgID = {0}", r["OrgID"]));

                    if (r["StoreTotal"] == null || r["StoreTotal"] == DBNull.Value)
                    {
                        r["StoreTotal"] = 0.0;
                    }
                }

                dgStore.DataSource = dtStoreCost;
                dgStore.DataBind();

                lblStore.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                lblStore.Text      = "No store usage during period";
                lblStore.ForeColor = System.Drawing.Color.Red;

                foreach (DataRow r in SummaryTable.Rows)
                {
                    r["StoreTotal"] = 0.0;
                }
            }

            dtStore2 = dtStoreCost;

            foreach (DataRow r in SummaryTable.Rows)
            {
                int   billingTypeId = r.Field <int>("BillingTypeID");
                int[] specialBillingTypesForSomeUnknownReason = { BillingTypes.NonAc, BillingTypes.ExtAc_Ga, BillingTypes.ExtAc_Si, BillingTypes.Int_Ga, BillingTypes.Int_Si, BillingTypes.Other };
                if (specialBillingTypesForSomeUnknownReason.Contains(billingTypeId))
                {
                    r["ToolTotal"] = 0.0;
                }
            }

            dlSummary.DataSource = SummaryTable;
            dlSummary.DataBind();
        }