Ejemplo n.º 1
0
        public void CreateInputSummaryReport()
        {
            Report.XL.Visible     = false;
            Report.XL.UserControl = false;

            Microsoft.Office.Interop.Excel._Workbook oWB =
                Report.CreateWorkBook();

            Microsoft.Office.Interop.Excel._Worksheet oSheet =
                (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

            Report.SheetPageSetup(oSheet, "Input Summary", 50d, 20d, 10d, "Database: " + DBMgr.NativeConnectionParameters.Database, DateTime.Now.ToLongDateString(), "Page &P", 1);
            Range oR = oSheet.get_Range("A1", "A1");

            object  oEndCell      = new object();
            string  strReportName = "Input Summary Report";
            int     sheetRow;
            DataSet dsPage         = null,
                    dsNetwork      = null,
                    dsSimulations  = null,
                    dsPriority     = null,
                    dsPriorityFund = null;
            DataSet dsTarget       = null,
                    dsDeficient    = null;

            try
            {
                dsPage         = DBOp.QueryPageHeader(strReportName);
                dsNetwork      = DBOp.GetNetworkDesc(NetworkId);
                dsSimulations  = DBOp.QuerySimulations(SimulationId);
                dsPriority     = DBOp.QueryPriority(SimulationId);
                dsPriorityFund = DBOp.QueryPriorityFund(SimulationId);
                dsTarget       = DBOp.QueryTargets(SimulationId);
                dsDeficient    = DBOp.QueryDeficients(SimulationId);
            }
            catch (Exception e)
            {
                throw e;
            }

            Cursor c = Cursor.Current;

            Cursor.Current = new Cursor(Cursors.WaitCursor.Handle);

            DataRow drPage        = dsPage.Tables[0].Rows[0];
            DataRow drNetwork     = dsNetwork.Tables[0].Rows[0];
            DataRow drSimulations = dsSimulations.Tables[0].Rows[0];

            string strMajorTitle = drPage["phText"].ToString();
            string strTemp, strFilter;

            #region report graphic
            // Place report graphic
            //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
            string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
            Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet);
            #endregion

            #region default column widths
            // set some column widths
            oR             = oSheet.get_Range("A1:A1", Missing.Value);
            oR.ColumnWidth = 21;
            oR             = oSheet.get_Range("B:D", Missing.Value);
            oR.ColumnWidth = 13.4;
            oR             = oSheet.get_Range("E:H", Missing.Value);
            oR.ColumnWidth = 7;
            #endregion

            int ndx;
            // calculate the size for the array
            int aryCols = 2;
            int aryRows = 22;

            object[,] oData = new object[aryRows, aryCols];
            Report.ClearDataArray(ref oData);
            Hashtable titles = new Hashtable();

            #region General Info
            titles.Add("General Information", new Titles("General Information", 4, 1, false, "SECTION", 0));
            sheetRow = 4;
            // GENERAL INFO
            oData[0, 1]  = strMajorTitle;
            oData[3, 0]  = "General Information";
            oData[4, 0]  = "Database:";
            oData[4, 1]  = DBMgr.NativeConnectionParameters.Database;
            oData[5, 0]  = "Network Name:";
            oData[5, 1]  = Network;
            oData[6, 0]  = "Network Description:";
            oData[6, 1]  = drNetwork["Description"].ToString();
            oData[7, 0]  = "Simulation Name:";
            oData[7, 1]  = drSimulations["Simulation"].ToString();
            oData[8, 0]  = "Simulation Description:";
            oData[8, 1]  = drSimulations["Comments"].ToString();
            oData[9, 0]  = "Created By:";
            oData[9, 1]  = drSimulations["Username"].ToString();
            oData[10, 0] = "Created On:";
            strTemp      = drSimulations["Date_created"].ToString();
            oData[10, 1] = Report.Left(strTemp, 9);

            sheetRow = 13;
            #endregion

            #region Analysis
            // ANALYSIS
            titles.Add("Analysis", new Titles("Analysis", sheetRow, 1, false, "SECTION", 0));
            oData[12, 0] = "Analysis";
            oData[13, 0] = "Optimization:";
            oData[13, 1] = drSimulations["Analysis"].ToString();
            oData[14, 0] = "Budget:";
            oData[14, 1] = drSimulations["Budget_constraint"].ToString();
            oData[15, 0] = "Weighting:";
            oData[15, 1] = drSimulations["Weighting"].ToString();
            oData[16, 0] = "Benefit:";
            oData[16, 1] = drSimulations["Benefit_variable"].ToString();
            oData[17, 0] = "Benefit Limit:";
            oData[17, 1] = drSimulations["Benefit_limit"].ToString();
            oData[18, 0] = "Jurisdiction Criteria:";
            oData[18, 1] = drSimulations["Jurisdiction"].ToString();
            #endregion

            #region Priority
            // PRIORITY
            sheetRow = 21;
            titles.Add("Priority", new Titles("Priority", sheetRow, 1, false, "GROUP", 0));
            oData[20, 0] = "Priority";
            oEndCell     = "A1";
            oEndCell     = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);

            int nCol;

            aryRows = DBMgr.ExecuteScalar("SELECT Count(*) FROM Priority WHERE simulationID = " + SimulationId) + 1;
            DataSet  dsInvestments  = DBOp.QueryInvestments(SimulationId); // get the budgetorder
            string[] strBudgetOrder = dsInvestments.Tables[0].Rows[0].ItemArray[5].ToString().Split(',');
            aryCols = strBudgetOrder.Count() + 2;

            Hashtable criteraColumns = new Hashtable(); // used in formatting below
            criteraColumns.Add("Priority", aryCols);
            criteraColumns.Add("Target", 5);
            criteraColumns.Add("Deficient", 5);
            criteraColumns.Add("Performance", 4);
            criteraColumns.Add("Feasibility", 1);
            criteraColumns.Add("Cost", 3);
            criteraColumns.Add("Consequence", 3);

            Report.Resize2DArray(ref oData, aryRows, aryCols);
            Report.ClearDataArray(ref oData);

            sheetRow++;
            titles.Add("Priority_col", new Titles("Priority", sheetRow, aryCols, true, "COLUMN", aryRows - 1));
            sheetRow++;

            oData[0, 0] = "Priority";
            ndx         = 1;
            foreach (string str in strBudgetOrder)
            {
                oData[0, ndx++] = str;
            }
            oData[0, ndx] = "Criteria";
            ndx           = 1;
            foreach (DataRow dr in dsPriority.Tables[0].Rows)
            {
                oData[ndx, 0]           = dr["PriorityLevel"].ToString();
                oData[ndx, aryCols - 1] = dr["Criteria"].ToString();
                //oData[ndx, 4] = dr["Criteria"].ToString();
                nCol = 1;
                foreach (string str in strBudgetOrder)
                {
                    strFilter = "priorityID = " + dr["priorityID"].ToString() + " and budget = '" + str + "'";
                    foreach (DataRow dr1 in dsPriorityFund.Tables[0].Select(strFilter))
                    {
                        oData[ndx, nCol] = dr1["funding"].ToString();
                    }
                    nCol++;
                }
                sheetRow++;
                ndx++;
            }

            oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()));
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true);
            #endregion

            #region Target
            // TARGET
            sheetRow += 2;
            aryCols   = 5;
            aryRows   = dsTarget.Tables[0].Rows.Count + 2;
            Report.Resize2DArray(ref oData, aryRows, aryCols);
            Report.ClearDataArray(ref oData);

            titles.Add("Target", new Titles("Target", sheetRow - 1, aryCols, false, "GROUP", 0));
            titles.Add("Target_col", new Titles("Attribute", sheetRow, aryCols, true, "COLUMN", aryRows - 2));
            oData[0, 0] = "Target";
            oData[1, 0] = "Attribute";
            oData[1, 1] = "Name";
            oData[1, 2] = "Year";
            oData[1, 3] = "Target";
            oData[1, 4] = "Criteria";
            ndx         = 2;

            foreach (DataRow dr in dsTarget.Tables[0].Rows)
            {
                nCol = 0;
                oData[ndx, nCol++]   = dr["Attribute_"].ToString();
                oData[ndx, nCol++]   = dr["TargetName"].ToString();
                oData[ndx, nCol++]   = dr["Years"].ToString();
                oData[ndx, nCol++]   = dr["TargetMean"].ToString();
                oData[ndx++, nCol++] = dr["Criteria"].ToString();
                sheetRow++;
            }

            oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2);
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true);
            #endregion

            #region Deficient
            // DEFICIENT
            aryCols = 5;
            aryRows = dsDeficient.Tables[0].Rows.Count + 2;
            Report.Resize2DArray(ref oData, aryRows, aryCols);
            Report.ClearDataArray(ref oData);
            sheetRow += 2;
            titles.Add("Deficient", new Titles("Deficient", sheetRow, aryCols, false, "GROUP", 0));
            titles.Add("Deficient_col", new Titles("Attribute", sheetRow + 1, aryCols, true, "COLUMN", aryRows - 2));
            sheetRow++;

            oData[0, 0] = "Deficient";
            oData[1, 0] = "Attribute";
            oData[1, 1] = "Name";
            oData[1, 2] = "Deficient Level";
            oData[1, 3] = "Allowed Deficient (%)";
            oData[1, 4] = "Criteria";
            ndx         = 2;
            foreach (DataRow dr in dsDeficient.Tables[0].Rows)
            {
                nCol = 0;
                oData[ndx, nCol++]   = dr["Attribute_"].ToString();
                oData[ndx, nCol++]   = dr["DeficientName"].ToString();
                oData[ndx, nCol++]   = dr["Deficient"].ToString();
                oData[ndx, nCol++]   = dr["PercentDeficient"].ToString();
                oData[ndx++, nCol++] = dr["Criteria"].ToString();
                sheetRow++;
            }

            oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2);
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true);
            #endregion  // deficient

            #region Investments
            // INVESTMENTS
            aryCols = 2;
            aryRows = 5;
            Report.Resize2DArray(ref oData, aryRows, aryCols);
            Report.ClearDataArray(ref oData);
            sheetRow += 2;
            titles.Add("Investments", new Titles("Investment", sheetRow, 1, false, "SECTION", 0));

            oData[0, 0] = "Investment";
            oData[1, 0] = "Start Year:";
            oData[1, 1] = dsInvestments.Tables[0].Rows[0].ItemArray[1].ToString();
            oData[2, 0] = "Analysis Period:";
            oData[2, 1] = dsInvestments.Tables[0].Rows[0].ItemArray[2].ToString();
            oData[3, 0] = "Inflation Rate:";
            oData[3, 1] = dsInvestments.Tables[0].Rows[0].ItemArray[3].ToString();
            oData[4, 0] = "Discount Rate:";
            oData[4, 1] = dsInvestments.Tables[0].Rows[0].ItemArray[4].ToString();

            oEndCell  = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2);
            oEndCell  = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
            sheetRow += aryRows;
            #endregion

            #region Budget
            // Budget
            switch (DBMgr.NativeConnectionParameters.Provider)
            {
            case "MSSQL":
                aryRows = DBMgr.ExecuteScalar("SELECT Count(DISTINCT [Year_]) FROM YearlyInvestment WHERE simulationID = " + SimulationId) + 2;
                break;

            case "ORACLE":
                aryRows = DBMgr.ExecuteScalar("SELECT Count(DISTINCT Year_) FROM YearlyInvestment WHERE simulationID = " + SimulationId) + 2;
                break;

            default:
                throw new NotImplementedException("TODO: Create ANSI implementation for CreateInputSummaryReport()");
                //break;
            }
            aryCols = strBudgetOrder.Count() + 1;
            criteraColumns.Add("Budget", aryCols);
            sheetRow += 1;
            titles.Add("Budget", new Titles("Budget", sheetRow, aryCols, false, "GROUP", 0));
            sheetRow++;
            titles.Add("Years", new Titles("Years", sheetRow, aryCols, false, "COLUMN", aryRows - 2));
            sheetRow++;


            Report.Resize2DArray(ref oData, aryRows, aryCols);
            Report.ClearDataArray(ref oData);

            oData[0, 0] = "Budget";
            oData[1, 0] = "Years";
            ndx         = 1;
            foreach (string str in strBudgetOrder)
            {
                oData[1, ndx++] = str;
            }
            ndx = 2;
            DataSet dsYearlyInvestment = DBOp.QueryYearlyInvestment(SimulationId);
            strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE SimulationID = " + SimulationId;
            DataSet dsYearlyYears = DBMgr.ExecuteQuery(strFilter);

            foreach (DataRow dr in dsYearlyYears.Tables[0].Rows)
            {
                oData[ndx, 0] = dr["Year_"].ToString();
                nCol          = 1;
                foreach (string str in strBudgetOrder)
                {
                    strFilter = "Year_ = " + dr["Year_"].ToString() + " AND Budgetname = '" + str + "'";
                    foreach (DataRow dr1 in dsYearlyInvestment.Tables[0].Select(strFilter))
                    {
                        oData[ndx, nCol] = dr1["Amount"].ToString();
                    }
                    nCol++;
                }
                ndx++;
                sheetRow++;
            }

            oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2);
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true);
            #endregion

            #region Performance
            //PERFORMANCE
            aryRows = DBMgr.ExecuteScalar("SELECT Count(*) FROM Performance WHERE simulationID = " + SimulationId) + 2;
            aryCols = 4;
            Report.Resize2DArray(ref oData, aryRows, aryCols);
            Report.ClearDataArray(ref oData);
            sheetRow += 1;
            titles.Add("Performance", new Titles("Performance", sheetRow, aryCols, false, "SECTION", 0));
            sheetRow++;
            titles.Add("Performance_col", new Titles("Attribute", sheetRow, aryCols, true, "COLUMN", aryRows - 2));
            sheetRow++;

            oData[0, 0] = "Performance";
            oData[1, 0] = "Attribute";
            oData[1, 1] = "Equation Name";
            oData[1, 2] = "Equation";
            oData[1, 3] = "Criteria";

            DataSet dsPerformance = DBOp.QueryPerformance(SimulationId);
            ndx = 2;
            foreach (DataRow dr in dsPerformance.Tables[0].Rows)
            {
                nCol = 0;
                oData[ndx, nCol++]   = dr["Attribute_"].ToString();
                oData[ndx, nCol++]   = dr["EquationName"].ToString();
                oData[ndx, nCol++]   = dr["Equation"].ToString();
                oData[ndx++, nCol++] = dr["Criteria"].ToString();
                sheetRow++;
            }

            oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2);
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true);
            #endregion

            #region Treatment
            // TREATMENT
            DataSet dsTreatments   = DBOp.QueryTreatments(SimulationId);
            DataSet dsFeasibility  = DBOp.QueryFeasibility(SimulationId);
            DataSet dsCosts        = DBOp.QueryCosts(SimulationId);
            DataSet dsConsequences = DBOp.QueryConsequences(SimulationId);

            int blankLines = 5, grpHeaders = 3, colHeaders = 3, title = 1, preamble = 5;
            aryRows  = (blankLines + grpHeaders + colHeaders + title + preamble) * dsTreatments.Tables[0].Rows.Count;
            aryRows += dsFeasibility.Tables[0].Rows.Count + dsCosts.Tables[0].Rows.Count + dsConsequences.Tables[0].Rows.Count;
            aryCols  = 3;
            Report.Resize2DArray(ref oData, aryRows, aryCols);
            Report.ClearDataArray(ref oData);
            sheetRow++;
            titles.Add("Treatment", new Titles("Treatment", sheetRow, aryCols, false, "SECTION", 0));
            sheetRow++;
            int           treatNum = 1; // keep track of where each treatment section begins
            string        strGrid  = "";
            List <string> gridList = new List <string>();

            oData[0, 0] = "Treatment";
            ndx         = 2;
            sheetRow++;
            foreach (DataRow dr in dsTreatments.Tables[0].Rows)
            {
                titles.Add("Treatment" + treatNum.ToString(), new Titles("Name", sheetRow, 1, false, "BOLD", 4));
                oData[ndx, 0]   = "Treatment Name:";
                oData[ndx++, 1] = dr["Treatment"].ToString();
                sheetRow++;
                oData[ndx, 0]   = "Description:";
                oData[ndx++, 1] = dr["Description"].ToString();
                sheetRow++;
                oData[ndx, 0]   = "Budget:";
                oData[ndx++, 1] = dr["Budget"].ToString();
                sheetRow++;
                oData[ndx, 0] = "Years Before Any:";
                sheetRow++;
                oData[ndx++, 1] = dr["BeforeAny"].ToString();
                sheetRow++;
                oData[ndx, 0]   = "Years Before Same:";
                oData[ndx++, 1] = dr["BeforeSame"].ToString();
                sheetRow++;

                //Feasibility
                ndx++;
                int count = DBMgr.ExecuteScalar("SELECT Count(*) FROM Feasibility WHERE TreatmentID = " + dr["TreatmentID"].ToString());
                titles.Add("Feasibility" + treatNum.ToString() + "grp", new Titles("Feasibility", sheetRow, 1, false, "GROUP", 0));
                oData[ndx++, 0] = "Feasibility";
                sheetRow++;
                titles.Add("Feasibility" + treatNum.ToString() + "col", new Titles("Criteria", sheetRow, 1, true, "COLUMN", count));
                oData[ndx++, 0] = "Criteria";
                sheetRow++;
                if (count == 0)
                {
                    aryRows += 2;
                    ndx++;
                    sheetRow++;
                }
                else
                {
                    //aryRows += count + 1;
                    //Report.Resize2DArrayKeepData(ref oData, aryRows, aryCols);
                    strFilter = "TreatmentID = " + dr["TreatmentID"].ToString();
                    foreach (DataRow dr1 in dsFeasibility.Tables[0].Select(strFilter))
                    {
                        Report.BuildRange(ref strGrid, ref gridList, sheetRow, 'A', 7);
                        //strGrid += "A" + sheetRow.ToString() + ":H" + sheetRow.ToString() + ",";
                        //string s = "A" + sheetRow.ToString() + ":H" + sheetRow.ToString();
                        //oR = oSheet.get_Range(s, Missing.Value);
                        //oR.NumberFormat = "@";
                        oData[ndx++, 0] = dr1["Criteria"].ToString();
                        sheetRow++;
                    }
                }
                ndx++;
                sheetRow++;

                // Costs
                titles.Add("Cost" + treatNum.ToString() + "grp", new Titles("Cost", sheetRow, 1, false, "GROUP", 0));
                oData[ndx++, 0] = "Cost";
                sheetRow++;
                count = DBMgr.ExecuteScalar("SELECT Count(*) FROM Costs WHERE TreatmentID = " + dr["TreatmentID"].ToString());
                titles.Add("Cost" + treatNum.ToString() + "col", new Titles("Cost", sheetRow, 3, true, "COLUMN", count));
                oData[ndx, 0]   = "Cost";
                oData[ndx, 1]   = "Units";
                oData[ndx++, 2] = "Criteria";
                sheetRow++;

                if (count == 0)
                {
                    ndx++;
                    sheetRow++;
                }
                else
                {
                    strFilter = "TreatmentID = " + dr["TreatmentID"].ToString();
                    foreach (DataRow dr1 in dsCosts.Tables[0].Select(strFilter))
                    {
                        Report.BuildRange(ref strGrid, ref gridList, sheetRow, 'A', 7);
                        //strGrid += "A" + sheetRow.ToString() + ":H" + sheetRow.ToString() + ",";
                        //string s = "A" + sheetRow.ToString() + ":H" + sheetRow.ToString();
                        //oR = oSheet.get_Range(s, Missing.Value);
                        //oR.NumberFormat = "@";
                        oData[ndx, 0]   = dr1["Cost_"];
                        oData[ndx, 1]   = dr1["Unit"].ToString();
                        oData[ndx++, 2] = dr1["Criteria"].ToString();
                        sheetRow++;
                    }
                }
                ndx++;
                sheetRow++;

                // Consequences
                titles.Add("Consequence" + treatNum.ToString() + "grp", new Titles("Consequence", sheetRow, 1, false, "GROUP", 0));
                oData[ndx++, 0] = "Consequence";
                sheetRow++;
                count = DBMgr.ExecuteScalar("SELECT Count(*) FROM Consequences WHERE TreatmentID = " + dr["TreatmentID"].ToString());
                titles.Add("Consequence" + treatNum.ToString() + "col", new Titles("Consequence", sheetRow, 3, true, "COLUMN", count));
                oData[ndx, 0]   = "Attribute";
                oData[ndx, 1]   = "Change";
                oData[ndx++, 2] = "Criteria";
                sheetRow++;

                if (count == 0)
                {
                    ndx++;
                    sheetRow++;
                }
                else
                {
                    strFilter = "TreatmentID = " + dr["TreatmentID"].ToString();
                    foreach (DataRow dr1 in dsConsequences.Tables[0].Select(strFilter))
                    {
                        Report.BuildRange(ref strGrid, ref gridList, sheetRow, 'A', 7);
                        //strGrid += "A" + sheetRow.ToString() + ":H" + sheetRow.ToString() + ",";
                        string s = "B" + sheetRow.ToString() + ":B" + sheetRow.ToString();
                        oR = oSheet.get_Range(s, Missing.Value);
                        oR.NumberFormat = "@";
                        oData[ndx, 0]   = dr1["Attribute_"].ToString();
                        oData[ndx, 1]   = dr1["Change_"].ToString();
                        oData[ndx++, 2] = dr1["Criteria"].ToString();
                        sheetRow++;
                    }
                }
                ndx++;
                sheetRow++;
                treatNum++;
            }   // end foreach


            oEndCell = "A" + Convert.ToString(Report.GetRowNumber(oEndCell.ToString()) + 2);
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
            #endregion

            // Apply formatting
            List <string> sectionList  = new List <string>();
            List <string> groupList    = new List <string>();
            List <string> columnList   = new List <string>();
            List <string> criteriaList = new List <string>();
            List <string> boldList     = new List <string>();
            //List<string> gridList = new List<string>();
            string strSection = "", strGroup = "", strColumn = "", strCriteria = "", strBold = "";

            #region pageheader
            // PAGEHEADER
            string  strRange = "B1:H1";
            DataRow drPgHdr  = dsPage.Tables[0].Rows[0];
            Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
            #endregion

            #region bold
            // Set bold text at known locations
            object o = titles["Investments"];
            if (o != null)
            {
                Titles bold = (Titles)o;
                int    nTmp = bold.Row;
                strRange     = "A" + (nTmp + 1).ToString() + ":A" + (nTmp + 4).ToString();
                strRange    += ",A1:A11,A14:A19";
                oR           = oSheet.get_Range(strRange, Missing.Value);
                oR.Font.Bold = true;
                oR.Font.Size = 11;
            }
            #endregion

            #region Format headers
            // Format headers
            foreach (DictionaryEntry de in titles)
            {
                Titles t     = (Titles)de.Value;
                int    nSize = t.NumberOfColumns < 9 ? 8 : t.NumberOfColumns;
                if (t.TitleType == "SECTION")
                {
                    Report.BuildRange(ref strSection, ref sectionList, t.Row, 'A', nSize - 1);
                }
                else if (t.TitleType == "GROUP")
                {
                    if ((string)de.Key == "Budget")
                    {
                        nSize = t.NumberOfColumns;
                    }
                    Report.BuildRange(ref strGroup, ref groupList, t.Row, 'A', nSize - 1);
                }
                else if (t.TitleType == "BOLD")
                {
                    strBold += ("A" + t.Row.ToString() + ":A" + (t.Row + t.NumberOfDataRows).ToString() + ",");
                }
                else if (t.TitleType == "COLUMN")
                {
                    if ((string)de.Key == "Years")
                    {
                        nSize = t.NumberOfColumns;
                    }
                    Report.BuildRange(ref strColumn, ref columnList, t.Row, 'A', nSize - 1);
                    if (t.Criteria)
                    {
                        string s   = Report.GetColumnLetter(t.NumberOfColumns);
                        char[] chr = s.ToCharArray(0, 1);

                        char asciiChar  = chr[0];
                        int  asciiValue = (int)asciiChar;
                        asciiValue += nSize - t.NumberOfColumns;
                        asciiChar   = (char)asciiValue;

                        strCriteria = Report.Left(s, 1) + t.Row.ToString() + ":" + asciiChar.ToString() + t.Row.ToString();
                        criteriaList.Add(strCriteria);

                        for (int i = 1; i <= t.NumberOfDataRows; i++)
                        {
                            int gridrow = t.Row + i;
                            strCriteria = Report.Left(s, 1) + gridrow.ToString() + ":" + asciiChar.ToString() + gridrow.ToString();
                            criteriaList.Add(strCriteria);
                        }
                    }
                }
            }
            Report.EndRangeList(ref strSection, ref sectionList);
            Report.EndRangeList(ref strGroup, ref groupList);
            Report.EndRangeList(ref strColumn, ref columnList);
            Report.EndRangeList(ref strCriteria, ref criteriaList);
            Report.EndRangeList(ref strBold, ref boldList);
            Report.EndRangeList(ref strGrid, ref gridList);
            foreach (string s in boldList)
            {
                oR           = oSheet.get_Range(s, Missing.Value);
                oR.Font.Bold = true;
            }
            foreach (string s in sectionList)
            {
                oR                     = oSheet.get_Range(s, Missing.Value);
                oR.MergeCells          = true;
                oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                oR.Interior.Color      = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy);
                oR.Font.Color          = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
                oR.Font.Size           = 13;
                oR.Font.Bold           = true;
            }
            foreach (string s in groupList)
            {
                oR                     = oSheet.get_Range(s, Missing.Value);
                oR.MergeCells          = true;
                oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                oR.Interior.Color      = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy);
                oR.Font.Color          = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
                oR.Font.Size           = 11;
                oR.Font.Bold           = true;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle    = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle   = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle  = XlLineStyle.xlContinuous;
            }
            foreach (string s in columnList)
            {
                oR = oSheet.get_Range(s, Missing.Value);
                oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                oR.VerticalAlignment   = XlVAlign.xlVAlignBottom;
                oR.WrapText            = true;
                oR.Interior.Color      = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
                oR.Font.Size           = 11;
                oR.Font.Bold           = true;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle          = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
            }
            foreach (string s in criteriaList)
            {
                oR                     = oSheet.get_Range(s, Missing.Value);
                oR.MergeCells          = true;
                oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                oR.VerticalAlignment   = XlVAlign.xlVAlignBottom;
                oR.WrapText            = true;
                oR.Borders.LineStyle   = XlLineStyle.xlContinuous;
                oR.Borders.Weight      = XlBorderWeight.xlThin;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle          = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
            }
            foreach (string s in gridList)
            {
                oR = oSheet.get_Range(s, Missing.Value);
                oR.Borders.LineStyle = XlLineStyle.xlContinuous;
                oR.Borders.Weight    = XlBorderWeight.xlThin;
                oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
            }
            #endregion

            #region Wide cells
            // Format wide cells
            strRange             = "B6:H6,B7:H7,B8:H8,B9:H9,B10:H10,B19:H19";
            oR                   = oSheet.get_Range(strRange, Missing.Value);
            oR.MergeCells        = true;
            oR.WrapText          = true;
            oR.VerticalAlignment = XlVAlign.xlVAlignTop;
            #endregion

            Report.XL.Visible     = true;
            Report.XL.UserControl = true;
        }
Ejemplo n.º 2
0
        public void CreateBudgetPerReport()
        {
            Report.XL.Visible     = false;
            Report.XL.UserControl = false;
            Microsoft.Office.Interop.Excel._Workbook  oWB    = Report.CreateWorkBook();
            Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

            Report.SheetPageSetup(oSheet, "Budget Per " + m_strBudgetPer, 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1);
            Range oR = oSheet.get_Range("A1", "A1");

            object oEndCell      = new object();
            string strFilter     = "";
            string strReportName = "Budget Per District Report";    // keep this constant to avoid having to add
                                                                    // a row to the Reports Table for each permutation
                                                                    // of this "Budget Per [attribute]" style report

            //  QUICK TEST of COUNTY attribute...
            //m_strBudgetPer = "County";
            //oSheet.Name = "Budget Per " + m_strBudgetPer;

            int     sheetRow;
            int     ndx;
            DataSet dsPage = null, dsSimulations = null;

            try
            {
                dsPage        = DBOp.QueryPageHeader(strReportName);
                dsSimulations = DBOp.QuerySimulations(m_strSimulationID);
            }
            catch (Exception e)
            {
                throw e;
                // Circular reference, address later
                //Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message);
            }

            Cursor c = Cursor.Current;

            Cursor.Current = new Cursor(Cursors.WaitCursor.Handle);

            DataRow drPage        = dsPage.Tables[0].Rows[0];
            string  strMajorTitle = drPage["phText"].ToString();

            if (strMajorTitle.IndexOf("@1") > 0)
            {
                strMajorTitle = strMajorTitle.Replace("@1", m_strBudgetPer);                                    // stuff the attribute into title
            }
            #region default column widths
            // set some column widths
            oR             = oSheet.get_Range("A1:A1", Missing.Value);
            oR.ColumnWidth = 18;
            #endregion

            #region place agency graphic
            //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
            string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
            Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet);
            #endregion

            #region write Major Title
            int aryCols = 2;
            int aryRows = 3;
            object[,] oData = new object[aryRows, aryCols];
            Report.ClearDataArray(ref oData);

            oData[0, 1] = strMajorTitle;
            sheetRow    = 4;

            oEndCell = "A1";
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
            #endregion

            #region get Budget Order
            DataSet  dsInvestments  = DBOp.QueryInvestments(m_strSimulationID); // get the budgetorder
            string[] strBudgetOrder = dsInvestments.Tables[0].Rows[0].ItemArray[5].ToString().Split(',');
            #endregion

            #region get Budget Years
            switch (DBMgr.NativeConnectionParameters.Provider)
            {
            case "MSSQL":
                strFilter = "SELECT DISTINCT [Year_] FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + "ORDER BY [Year_]";
                break;

            case "ORACLE":
                strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + "ORDER BY Year_";
                break;

            default:
                throw new NotImplementedException("TODO: Create ANSI implementation for CreateBudgetPerReport()");
                //break;
            }
            DataSet dsBudgetYears = DBMgr.ExecuteQuery(strFilter);
            int     numYears      = dsBudgetYears.Tables[0].Rows.Count;
            #endregion

            #region get Number of Districts
            List <string> strJuris = DBOp.GetJurisdiction(m_strNetworkID, m_strBudgetPer);
            strJuris.Sort();
            int numDistricts = strJuris.Count;
            #endregion

            aryCols = numYears + 1;
            aryRows = numDistricts + 3;

            #region build Column Headers array
            // Set up column header once, for multiple uses
            object[] oColumnHeader = new object[aryCols];
            oColumnHeader[0] = m_strBudgetPer;
            ndx = 1;
            foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
            {
                oColumnHeader[ndx++] = dr["Year_"].ToString();
            }
            #endregion

            #region build Budget tables
            object[,] oTotalBudget = new object[aryRows, aryCols]; // Annual Total Budget array
            Report.Resize2DArray(ref oData, aryRows, aryCols);
            int    totalRow = oData.GetUpperBound(0);
            string strRange, strGroups, strColHdrs, strTotals, strGrids, strMoney;
            strColHdrs = strGrids = strGroups = strMoney = strRange = strTotals = "";

            // ranges needed for formatting spreadsheet
            List <string> groupList  = new List <string>();
            List <string> columnList = new List <string>();
            List <string> totalsList = new List <string>();
            List <string> gridsList  = new List <string>();
            List <string> moneyList  = new List <string>();

            Report.ClearDataArray(ref oTotalBudget);
            sheetRow = 4;
            int    nCol, nGridStart, nGridEnd;
            double nTmp = 0d;
            foreach (string strBudget in strBudgetOrder)
            {
                oEndCell = "A" + sheetRow.ToString();
                Report.BuildRange(ref strGroups, ref groupList, sheetRow, 'A', aryCols - 1);

                Report.ClearDataArray(ref oData);

                ndx                  = 0;
                oData[ndx, 0]        = "Annual " + strBudget + " Budget"; // Group Header
                oTotalBudget[ndx, 0] = "Annual Total Budget";
                sheetRow++;
                ndx++;
                oData[totalRow, 0] = oTotalBudget[totalRow, 0] = "Total";
                Report.BuildRange(ref strColHdrs, ref columnList, sheetRow, 'A', aryCols - 1);
                nGridStart = sheetRow;

                for (int i = 0; i < aryCols; i++)
                {
                    oData[ndx, i]        = oColumnHeader[i]; // Column Header
                    oTotalBudget[ndx, i] = oColumnHeader[i];
                }
                sheetRow++;
                ndx++;

                foreach (string strDistrict in strJuris)
                {
                    nCol          = 1;
                    oData[ndx, 0] = oTotalBudget[ndx, 0] = strDistrict;
                    foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
                    {
                        Hashtable hBudgets = DBOp.GetBudgetTotals(m_strNetworkID, m_strSimulationID, dr["Year_"].ToString(), strBudget, m_strBudgetPer, m_strBudgetPer + " = " + strDistrict);
                        string    strHash  = (string)hBudgets[strDistrict];
                        double    nBudget  = Convert.ToDouble(strHash);
                        if (oTotalBudget[ndx, nCol].ToString() == "")
                        {
                            oTotalBudget[ndx, nCol] = 0d;                                                // initialize array element
                        }
                        if (oData[totalRow, nCol].ToString() == "")
                        {
                            oData[totalRow, nCol] = 0d;                                                // initalize array element
                        }
                        if (oTotalBudget[totalRow, nCol].ToString() == "")
                        {
                            oTotalBudget[totalRow, nCol] = 0d;                                                       // initalize array element
                        }
                        // Accumulate total budget array
                        nTmp  = (double)oTotalBudget[ndx, nCol];
                        nTmp += nBudget;
                        oTotalBudget[ndx, nCol] = nTmp;

                        nTmp  = (double)oTotalBudget[totalRow, nCol];
                        nTmp += nBudget;
                        oTotalBudget[totalRow, nCol] = nTmp;  // accumulate fiscal year budget

                        nTmp  = (double)oData[totalRow, nCol];
                        nTmp += nBudget;
                        oData[totalRow, nCol] = nTmp;    // accumulate fiscal year budget
                        oData[ndx, nCol++]    = nBudget; // store budget value
                    }
                    ndx++;
                    sheetRow++;
                }
                Report.BuildRange(ref strTotals, ref totalsList, sheetRow, 'A', aryCols - 1);
                strGrids = "A" + nGridStart.ToString() + ":" + Report.GetColumnLetter(aryCols) + sheetRow.ToString();
                gridsList.Add(strGrids);
                strMoney = "B" + (nGridStart + 1).ToString() + ":" + Report.GetColumnLetter(aryCols) + sheetRow.ToString();
                moneyList.Add(strMoney);
                sheetRow += 2;
                oEndCell  = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
            }

            // print the annual total budget array
            oEndCell = "A" + sheetRow.ToString();
            Report.BuildRange(ref strGroups, ref groupList, sheetRow, 'A', aryCols - 1);
            Report.BuildRange(ref strColHdrs, ref columnList, sheetRow + 1, 'A', aryCols - 1);
            Report.BuildRange(ref strTotals, ref totalsList, sheetRow + aryRows - 1, 'A', aryCols - 1);
            nGridStart = sheetRow + 1;
            nGridEnd   = sheetRow + aryRows - 1;
            strGrids   = "A" + nGridStart.ToString() + ":" + Report.GetColumnLetter(aryCols) + nGridEnd.ToString();
            gridsList.Add(strGrids);
            strMoney = "B" + (nGridStart + 1).ToString() + ":" + Report.GetColumnLetter(aryCols) + nGridEnd.ToString();
            moneyList.Add(strMoney);

            oEndCell = Report.WriteObjectArrayToExcel(oTotalBudget, oSheet, oEndCell, false, false);
            Report.EndRangeList(ref strGroups, ref groupList);
            Report.EndRangeList(ref strColHdrs, ref columnList);
            Report.EndRangeList(ref strTotals, ref totalsList);
            #endregion

            #region format pageheader
            // PAGEHEADER
            strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1";
            DataRow drPgHdr = dsPage.Tables[0].Rows[0];
            Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
            #endregion

            #region format groupheader
            foreach (string s in groupList)
            {
                Report.FormatHeaders(oR, drPage, oSheet, "gh", s);
                //oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
                //oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
                //oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
                //oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
            }
            #endregion

            #region format columnheader
            foreach (string s in columnList)
            {
                Report.FormatHeaders(oR, drPage, oSheet, "ch", s);
            }
            #endregion

            #region format totals rows
            foreach (string s in totalsList)
            {
                Report.FormatHeaders(oR, drPage, oSheet, "ch", s);
            }
            #endregion

            #region format grid data
            foreach (string s in gridsList)
            {
                oR = oSheet.get_Range(s, Missing.Value);
                oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                oR.Borders.LineStyle   = XlLineStyle.xlContinuous;
                oR.Borders.Weight      = XlBorderWeight.xlThin;
                oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle         = XlLineStyle.xlContinuous;
                oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle        = XlLineStyle.xlContinuous;
            }
            #endregion

            #region format Money cells
            foreach (string s in moneyList)
            {
                oR = oSheet.get_Range(s, Missing.Value);
                oR.NumberFormat = "$#,##0";
                oR.ColumnWidth  = 15;
            }
            #endregion

            #region create column charts
            int top = 15;
            //bool bHasLegend = true;
            //string strCategoryAxis = "Fiscal Year";

            string strTitle = "";
            string sheetName;
            int    budgetIndex = 0;
            Range  oSourceData = oSheet.get_Range(moneyList[0], Missing.Value);
            int    left        = (int)Report.GetColumnWidthInPixels(oSourceData, oSheet);
            foreach (string s in moneyList)
            {
                int    nPos = s.IndexOf(":");
                string sTmp = Report.Left(s, nPos + 2);

                string sRight = s.Substring(nPos + 2);
                nTmp        = int.Parse(sRight) - 1;
                oSourceData = oSheet.get_Range((sTmp + nTmp.ToString()), Missing.Value);

                sheetName = "='Budget Per " + m_strBudgetPer + "'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5";
                if (budgetIndex < strBudgetOrder.Count())
                {
                    strTitle = "Annual " + strBudgetOrder[budgetIndex++] + " Budget Per " + m_strBudgetPer;
                }
                else
                {
                    strTitle = "Annual Total Budget Per " + m_strBudgetPer;
                }

                Report.CreateColClusterBarGraph(left, top, 425, 315, oSheet, oSourceData, sheetName,
                                                strTitle, 12, "Fiscal Year", 11, strJuris, "", 11, XlRowCol.xlRows);

                top += 330; // magic number
            }               // end moneyList
            #endregion

            Report.XL.Visible     = true;
            Report.XL.UserControl = true;
        } // end CreateBudgetPerReport
Ejemplo n.º 3
0
        public void CreateTotalBudgetReport()
        {
            Report.XL.Visible     = false;
            Report.XL.UserControl = false;
            Microsoft.Office.Interop.Excel._Workbook  oWB    = Report.CreateWorkBook();
            Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

            Report.SheetPageSetup(oSheet, "Total Budget", 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1);
            Range oR = oSheet.get_Range("A1", "A1");

            object  oEndCell = new object();
            string  strReportName = "Total Budget Report", strFilter = "";
            int     sheetRow;
            int     ndx;
            DataSet dsPage = null, dsSimulations = null, dsInvestments = null;

            try
            {
                dsPage        = DBOp.QueryPageHeader(strReportName);
                dsSimulations = DBOp.QuerySimulations(m_strSimulationID);
                dsInvestments = DBOp.QueryInvestments(m_strSimulationID);   // get the budgetorder
            }
            catch (Exception e)
            {
                throw e;
            }
            if (m_bDebug)
            {
                WriteLogEntry("DataSet OK");
            }
            Cursor c = Cursor.Current;

            Cursor.Current = new Cursor(Cursors.WaitCursor.Handle);

            DataRow drPage        = dsPage.Tables[0].Rows[0];
            string  strMajorTitle = drPage["phText"].ToString();

            string[] strBudgetOrder = dsInvestments.Tables[0].Rows[0].ItemArray[5].ToString().Split(',');

            #region default column widths
            // set some column widths
            oR             = oSheet.get_Range("A1:A1", Missing.Value);
            oR.ColumnWidth = 18;
            #endregion

            if (m_bDebug)
            {
                WriteLogEntry("Default column widths OK");
            }

            #region place agency graphic
            //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
            string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
            Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet);
            #endregion

            if (m_bDebug)
            {
                WriteLogEntry("Place agency graphic OK");
            }

            int aryCols = 2;
            int aryRows = 3;

            object[,] oData = new object[aryRows, aryCols];
            Report.ClearDataArray(ref oData);

            oData[0, 1] = strMajorTitle;

            oEndCell = "A1";
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);

            #region budget
            int nFY = 1, nBudgetTotal = 1;
            switch (DBMgr.NativeConnectionParameters.Provider)
            {
            case "MSSQL":
                aryRows = DBMgr.ExecuteScalar("SELECT Count(DISTINCT [Year_]) FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID) + 2;
                break;

            case "ORACLE":
                aryRows = DBMgr.ExecuteScalar("SELECT Count(DISTINCT Year_) FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID) + 2;
                break;

            default:
                throw new NotImplementedException("TODO: Create ANSI implementation for CreateTotalBudgetReport()");
                //break;
            }
            aryCols = strBudgetOrder.Count() + nFY + nBudgetTotal;


            Report.Resize2DArray(ref oData, aryRows, aryCols);
            Report.ClearDataArray(ref oData);

            oData[0, 0] = "Total Budget - in Millions";
            oData[1, 0] = "FY";
            sheetRow    = 6;
            ndx         = 1;
            foreach (string str in strBudgetOrder)
            {
                oData[1, ndx++] = str;
            }
            oData[1, ndx] = "Total";    // add total column
            ndx           = 2;
            DataSet dsYearlyInvestment = DBOp.QueryYearlyInvestment(m_strSimulationID);
            strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE SimulationID = " + m_strSimulationID;
            DataSet dsYearlyYears      = DBMgr.ExecuteQuery(strFilter);
            int     nCol               = 0;
            double  budgetTotal;
            foreach (DataRow dr in dsYearlyYears.Tables[0].Rows)
            {
                oData[ndx, 0] = dr["Year_"].ToString();
                nCol          = 1;
                budgetTotal   = 0;
                foreach (string str in strBudgetOrder)
                {
                    strFilter = "Year_ = " + dr["Year_"].ToString() + " AND Budgetname = '" + str + "'";
                    foreach (DataRow dr1 in dsYearlyInvestment.Tables[0].Select(strFilter))
                    {
                        oData[ndx, nCol] = Convert.ToDouble(dr1["Amount"].ToString()) / 1000000; // show millions
                        budgetTotal     += double.Parse(dr1["Amount"].ToString());               // sum the budgets for a fiscal year
                    }
                    nCol++;
                }
                oData[ndx, nCol] = budgetTotal / 1000000;
                ndx++;
                sheetRow++;
            }

            oEndCell = "A4";
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true);
            #endregion

            if (m_bDebug)
            {
                WriteLogEntry("Budget OK");
            }

            #region format pageheader
            // PAGEHEADER
            string  strRange = "B1:G1";
            DataRow drPgHdr  = dsPage.Tables[0].Rows[0];
            Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
            #endregion

            if (m_bDebug)
            {
                WriteLogEntry("Format pageheader OK");
            }

            #region format groupheader
            strRange               = "A4:" + Report.GetColumnLetter(aryCols) + "4";
            oR                     = oSheet.get_Range(strRange, Missing.Value);
            oR.MergeCells          = true;
            oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            oR.Interior.Color      = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy);
            oR.Font.Color          = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
            oR.Font.Size           = 11;
            oR.Font.Bold           = true;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle    = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle   = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle  = XlLineStyle.xlContinuous;
            #endregion

            if (m_bDebug)
            {
                WriteLogEntry("Format groupheader OK");
            }

            #region format columnheader
            strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5";
            oR       = oSheet.get_Range(strRange, Missing.Value);
            oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            oR.VerticalAlignment   = XlVAlign.xlVAlignBottom;
            if (m_bDebug)
            {
                WriteLogEntry("Format columnheader VerticalAlignment OK");
            }
            //oR.WrapText = true;
            oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            oR.Font.Size      = 11;
            oR.Font.Bold      = true;

            oR.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
            if (m_bDebug)
            {
                WriteLogEntry("Format columnheader EdgeTop OK");
            }
            oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
            if (m_bDebug)
            {
                WriteLogEntry("Format columnheader EdgeBottom OK");
            }

            oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

            //oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
            //if (m_bDebug) WriteLogEntry("Format columnheader InsideHorizontal OK");
            //oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;
            //if (m_bDebug) WriteLogEntry("Format columnheader InsideVertical OK");

            #endregion

            if (m_bDebug)
            {
                WriteLogEntry("Format columnheader OK");
            }

            #region format grid data
            strRange = "A6:A" + (sheetRow - 1).ToString();
            oR       = oSheet.get_Range(strRange, Missing.Value);
            oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            oR.Borders.LineStyle   = XlLineStyle.xlContinuous;
            oR.Borders.Weight      = XlBorderWeight.xlThin;
            oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            //oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
            //oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;

            strRange             = "B6:" + Report.GetColumnLetter(aryCols) + (sheetRow - 1).ToString();
            oR                   = oSheet.get_Range(strRange, Missing.Value);
            oR.NumberFormat      = "$#,##0.0";
            oR.Borders.LineStyle = XlLineStyle.xlContinuous;
            oR.Borders.Weight    = XlBorderWeight.xlThin;

            oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

            //oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
            //oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
            oR.ColumnWidth = 13.4;
            #endregion

            if (m_bDebug)
            {
                WriteLogEntry("Format grid data OK");
            }

            #region create column chart

            strRange = "B6:" + Report.GetColumnLetter(aryCols) + (sheetRow - 1).ToString();
            Range oSourceData = oSheet.get_Range(strRange, Missing.Value);      //B5:E10
            int   left        = (int)Report.GetColumnWidthInPixels(oSourceData, oSheet);

            string strTitle  = "Yearly Budget Distribution ($ Millions)";
            string sheetName = "='Total Budget'!$A$6:$A$" + (sheetRow - 1).ToString();

            List <string> listBudgets = new List <string>();
            foreach (string s in strBudgetOrder)
            {
                listBudgets.Add(s);
            }
            listBudgets.Add("Total");

            Report.CreateColClusterBarGraph(left, 30, 425, 315, oSheet, oSourceData, sheetName,
                                            strTitle, 12, "Fiscal Year", 11, listBudgets, "", 11, XlRowCol.xlColumns);

            #endregion

            if (m_bDebug)
            {
                WriteLogEntry("Create column chart OK");
            }

            Report.XL.Visible     = true;
            Report.XL.UserControl = true;
        }
Ejemplo n.º 4
0
        public void CreateSectionReport()
        {
            Report.XL.Visible     = false;
            Report.XL.UserControl = false;
            Microsoft.Office.Interop.Excel._Workbook  oWB    = Report.CreateWorkBook();
            Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

            //string strTrace = string.Format("preamble {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            string strReportName = "Section Detail Report";

            Report.SheetPageSetup(oSheet, strReportName, 50d, 20d, 10d, "Network: " + m_strNetworkName, DateTime.Now.ToLongDateString(), "Page &P", 1);
            //oSheet.PageSetup.RightFooter = "Network: " + m_strNetworkName;
            //oSheet.PageSetup.LeftFooter = DateTime.Now.ToLongDateString();
            //oSheet.PageSetup.CenterFooter = "Page &P";
            //oSheet.PageSetup.FirstPageNumber = 1;
            //oSheet.PageSetup.LeftMargin = 50d;
            //oSheet.PageSetup.RightMargin = 20d;
            //oSheet.Columns.Font.Size = 10;

            object  oEndCell      = new object();
            DataSet dsPage        = null;
            DataSet dsGroupNames  = null;
            DataSet dsGroupDetail = null;

            try
            {
                dsPage        = DBOp.QueryPageHeader(strReportName);
                dsGroupNames  = DBOp.QueryAttributeGroupNames();
                dsGroupDetail = DBOp.QueryAttributeByGroup();
            }
            catch (Exception e)
            {
                throw e;
            }

            int nSize = m_nMaxYear - m_nMinYear + 2;

            nSize = nSize < 1? 1: nSize;

            // Set up column header once, for multiple uses
            object[] oColumnHeader = new object[nSize];

            oColumnHeader[0] = "ATTRIBUTE_";
            int currentYear = m_nMinYear;

            for (int i = 1; i < nSize; i++, currentYear++)
            {
                oColumnHeader[i] = currentYear.ToString();
            }

            string[] strCols = new string[m_ds.Tables[0].Columns.Count];
            int      idx     = 0;

            foreach (DataColumn dataColumn in m_ds.Tables[0].Columns)
            {
                strCols[idx] = dataColumn.ColumnName.ToString();
                idx++;
            }
            List <string> subList = new List <string>();

            Cursor c = Cursor.Current;

            Cursor.Current = new Cursor(Cursors.WaitCursor.Handle);

            DataRow drPage        = dsPage.Tables[0].Rows[0];
            string  strMajorTitle = drPage["phText"].ToString();

            drPage = dsPage.Tables[0].Rows[2];
            string strMinorTitle = drPage["phText"].ToString();
            //if (strMinorTitle.IndexOf("@1") > 0) strMinorTitle = strMinorTitle.Replace("@1", m_strNetworkName);
            //if (strMinorTitle.IndexOf("@2") > 0) strMinorTitle = strMinorTitle.Replace("@2", m_ds.Tables[0].Rows[0]["FACILITY"].ToString() + ", Section: " + m_ds.Tables[0].Rows[0]["SECTION"].ToString());

            int sumCol = 0, numSpacers = dsGroupNames.Tables[0].Rows.Count, numColHeaders = 1;

            sumCol = dsGroupDetail.Tables[0].Rows.Count; // number of detail rows

            // calculate number of array rows for report
            int aryRows = ((dsPage.Tables[0].Rows.Count + 1 + dsGroupNames.Tables[0].Rows.Count + sumCol + (dsGroupNames.Tables[0].Rows.Count * numColHeaders) + numSpacers) * m_ds.Tables[0].Rows.Count);

            int aryCurrentRow = 0;

            object[,] oData1 = new object[aryRows, nSize];
            Report.ClearDataArray(ref oData1);

            //string strTrace = string.Format("start LOOP {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            string strFilter;

            #region Load data array
            //strTrace = string.Format("/tData start {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            foreach (DataRow drdata in m_ds.Tables[0].Rows)
            {
                // Page Header
                strMinorTitle = drPage["phText"].ToString();
                if (strMinorTitle.IndexOf("@1") > 0)
                {
                    strMinorTitle = strMinorTitle.Replace("@1", m_strNetworkName);
                }
                if (strMinorTitle.IndexOf("@2") > 0)
                {
                    strMinorTitle = strMinorTitle.Replace("@2", drdata["FACILITY"].ToString() + ", Section: " + drdata["SECTION"].ToString());
                }
                oData1[aryCurrentRow, 0] = "";
                oData1[aryCurrentRow, 1] = strMajorTitle;
                aryCurrentRow++;
                oData1[aryCurrentRow, 0] = "";
                aryCurrentRow++;
                oData1[aryCurrentRow, 0] = "";
                oData1[aryCurrentRow, 1] = strMinorTitle;

                aryCurrentRow += 2;
                // Load Group Header
                foreach (DataRow dr1 in dsGroupNames.Tables[0].Rows)
                {
                    oData1[aryCurrentRow, 0] = dr1["GROUPING"].ToString();
                    aryCurrentRow++;
                    // Load Column Header
                    for (int i = 0; i < nSize; i++)
                    {
                        oData1[aryCurrentRow, i] = oColumnHeader[i];
                    }
                    aryCurrentRow++;
                    //Load Detail Data
                    strFilter = "GROUPING = '" + dr1["GROUPING"].ToString() + "'";
                    foreach (DataRow drDetail in dsGroupDetail.Tables[0].Select(strFilter))
                    {
                        // Set the Row Label text
                        oData1[aryCurrentRow, 0] = drDetail["ATTRIBUTE_"].ToString();
                        // Load the Data values
                        subList = columnsFromArrayStartingWith(strCols, drDetail["ATTRIBUTE_"].ToString());
                        PopulateGroupArrayRowAt(ref oData1, subList, drdata, aryCurrentRow);
                        aryCurrentRow++;
                    }
                    aryCurrentRow++;
                }
            }
            //strTrace = string.Format("/tData stop {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            #endregion

            oEndCell = "A1";
            oEndCell = Report.WriteObjectArrayToExcel(oData1, oSheet, oEndCell, false);
            Range         oR        = oSheet.get_Range("A1", "A1");
            String        strWork   = "";
            List <string> rangeList = new List <string>();
            int           sheetRow;

            #region set column widths
            //strTrace = string.Format("/tColWidth start {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            sheetRow       = 1;
            oR             = oSheet.get_Range("A1:A1", Missing.Value);
            oR.ColumnWidth = 16;
            Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'B', nSize - 1);
            Report.EndRangeList(ref strWork, ref rangeList);
            oR             = oSheet.get_Range(rangeList.First(), Missing.Value);
            oR.ColumnWidth = 9;
            rangeList.Clear();
            strWork = "";
            //strTrace = string.Format("/tColWidth stop {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            #endregion

            #region ranges helper Array
            // Build a rows array used for building ranges, formatting report, etc.
            int [] clearRows = new int[dsGroupNames.Tables[0].Rows.Count + 1];
            aryCurrentRow = 1;
            clearRows[0]  = dsPage.Tables[0].Rows.Count + 1;
            foreach (DataRow drGrouping in dsGroupNames.Tables[0].Rows)
            {
                strFilter = "grouping = '" + drGrouping["grouping"].ToString() + "'";

                int filterCount = 0;
                foreach (DataRow drDetail in dsGroupDetail.Tables[0].Select(strFilter))
                {
                    filterCount++;
                }
                int cc = clearRows[aryCurrentRow - 1];
                clearRows[aryCurrentRow++] = cc + filterCount + 3;
            }
            #endregion

            #region clear gridlines
            //strTrace = string.Format("/tGridlines start {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            sheetRow = 0;

            // Clear gridlines between data group, pages
            for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
            {
                for (int clearCount = 0; clearCount <= clearRows.GetUpperBound(0); clearCount++)
                {
                    if (clearCount == 0)
                    {
                        sheetRow += clearRows[0];
                    }
                    else
                    {
                        sheetRow += clearRows[clearCount] - clearRows[clearCount - 1];
                    }

                    if (clearCount == 0)
                    {
                        strWork += "A" + (sheetRow - 3).ToString() + ":I" + sheetRow.ToString() + ",";
                    }
                    else
                    {
                        Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'A', nSize - 1);
                    }
                }
            }
            // Finish the range List
            Report.EndRangeList(ref strWork, ref rangeList);
            foreach (string strRange in rangeList)
            {
                oR = oSheet.get_Range(strRange, Missing.Value);
                oR.ClearFormats();
            }
            rangeList.Clear();
            strWork = "";
            //strTrace = string.Format("/tGridline stop {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            #endregion

            #region pageBreaks
            // Add page breaks
            //strTrace = string.Format("/tPgBreak start {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            sheetRow = clearRows[clearRows.GetUpperBound(0)];
            for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
            {
                oR           = oSheet.get_Range("A" + sheetRow.ToString(), Missing.Value);
                oR.PageBreak = (int)XlPageBreak.xlPageBreakManual;
                sheetRow    += clearRows[clearRows.GetUpperBound(0)];
            }
            //strTrace = string.Format("/tPgBreak stop {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            #endregion

            #region pageHeader
            // Format page header for each page in report
            //strTrace = string.Format("/tPgHeader start {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            sheetRow = 1;
            idx      = 1;
            foreach (DataRow drPgHdr in dsPage.Tables[0].Rows)
            {
                for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
                {
                    //Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'B', nSize - 2);
                    Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'B', 7);
                    sheetRow += clearRows[clearRows.GetUpperBound(0)];
                }
                // Finish the range List
                Report.EndRangeList(ref strWork, ref rangeList);
                foreach (string strRange in rangeList)
                {
                    Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
                }
                rangeList.Clear();
                sheetRow = ++idx; // point to the next pageheader line
                strWork  = "";
            }

            //strTrace = string.Format("/tPgHeader stop {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            #endregion

            #region pageImage
            //strTrace = string.Format("/tImage start {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
            string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
            if (strPath != ".\\")
            {
                Image img = Image.FromFile(strPath);
                System.Windows.Forms.Clipboard.SetDataObject(img, true);
                sheetRow = 1;
                for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
                {
                    oR = oSheet.get_Range("A" + sheetRow.ToString(), Missing.Value);
                    oSheet.Paste(oR, img);
                    sheetRow += (clearRows[clearRows.GetUpperBound(0)]);
                }
            }
            //strTrace = string.Format("/tImage stop {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            #endregion

            #region groupHeaders
            // Format group headers for each page in report, programmed for single line group header with multiple group headers per page
            //strTrace = string.Format("/tGroupHeader start {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            aryCurrentRow = 0;
            sheetRow      = clearRows[0] + 1;
            for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
            {
                aryCurrentRow = 0;
                foreach (DataRow drGroupName in dsGroupNames.Tables[0].Rows)
                {
                    Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'A', nSize - 1);
                    aryCurrentRow++;
                    sheetRow += clearRows[aryCurrentRow] - clearRows[aryCurrentRow - 1];
                }
                sheetRow += clearRows[0];
            }
            // Finish the range List
            Report.EndRangeList(ref strWork, ref rangeList);
            foreach (string strRange in rangeList)
            {
                DataRow drGroupHdr = dsPage.Tables[0].Rows[0];
                Report.FormatHeaders(oR, drGroupHdr, oSheet, "gh", strRange);
            }
            rangeList.Clear();
            strWork = "";
            //strTrace = string.Format("/tGroupHeader stop {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            #endregion

            #region columnHeaders
            // Build the range list for the column headers for each page in report
            //strTrace = string.Format("/tColHeader start {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            aryCurrentRow = 0;
            sheetRow      = clearRows[0] + 2;
            for (int i = 0; i < m_ds.Tables[0].Rows.Count; i++)
            {
                aryCurrentRow = 0;
                foreach (DataRow dr1 in dsGroupNames.Tables[0].Rows) // column header is repeated for each group
                {
                    Report.BuildRange(ref strWork, ref rangeList, sheetRow, 'A', nSize - 1);
                    aryCurrentRow++;
                    sheetRow += clearRows[aryCurrentRow] - clearRows[aryCurrentRow - 1];
                }
                sheetRow += clearRows[0];
            }
            // Finish the range List
            Report.EndRangeList(ref strWork, ref rangeList);

            DataRow drCol = dsPage.Tables[0].Rows[0]; // only one row
            foreach (string strRange in rangeList)
            {
                Report.FormatHeaders(oR, drCol, oSheet, "ch", strRange);
            }
            //strTrace = string.Format("/tColHeader stop {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            #endregion

            //strTrace = string.Format("stop LOOP {0}", DateTime.Now.TimeOfDay);
            //System.Diagnostics.Trace.WriteLine(strTrace);
            Report.XL.Visible     = true;
            Report.XL.UserControl = true;
        } // end createsectionreport
Ejemplo n.º 5
0
        public void CreateDetailedResultsReport()
        {
            Report.XL.Visible     = false;
            Report.XL.UserControl = false;
            Microsoft.Office.Interop.Excel._Workbook  oWB    = Report.CreateWorkBook();
            Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

            string strReportName = "Detailed Results Report";

            Report.SheetPageSetup(oSheet, strReportName, 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1);
            //oSheet.PageSetup.RightFooter = m_strNetwork + " - " + m_strSimulation;
            //oSheet.PageSetup.LeftFooter = DateTime.Now.ToLongDateString();
            //oSheet.PageSetup.CenterFooter = "Page &P";
            //oSheet.PageSetup.FirstPageNumber = 1;
            //oSheet.PageSetup.LeftMargin = 50d;
            //oSheet.PageSetup.RightMargin = 20d;
            //oSheet.Columns.Font.Size = 10;
            //oSheet.Name = strReportName;
            Range oR = oSheet.get_Range("A1", "A1");

            object oEndCell  = new object();
            string strFilter = "";

            int     sheetRow;
            int     ndx;
            DataSet dsPage = null, dsResults = null;

            try
            {
                dsPage    = DBOp.QueryPageHeader(strReportName);
                dsResults = DBOp.QueryReport(m_strNetworkID, m_strSimulationID);
            }
            catch (Exception e)
            {
                throw e;
                // Circular reference, address later
                //Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message);
            }

            Cursor c = Cursor.Current;

            Cursor.Current = new Cursor(Cursors.WaitCursor.Handle);

            DataRow drPage        = dsPage.Tables[0].Rows[0];
            string  strMajorTitle = drPage["phText"].ToString();

            drPage = dsPage.Tables[0].Rows[2];
            string strMinorTitle = drPage["phText"].ToString();

            if (strMinorTitle.IndexOf("@1") > 0)
            {
                strMinorTitle = strMinorTitle.Replace("@1", m_strNetwork + " (" + m_strNetworkID + ")");
            }
            if (strMinorTitle.IndexOf("@2") > 0)
            {
                strMinorTitle = strMinorTitle.Replace("@2", m_strSimulation + " (" + m_strSimulationID + ")");
            }


            #region default column widths
            // set some column widths
            oR             = oSheet.get_Range("A1:B1", Missing.Value);
            oR.ColumnWidth = 15;
            #endregion

            #region place agency graphic
            //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
            string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
            Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet);
            #endregion

            #region write Major Title
            int aryCols = 2;
            int aryRows = 3;
            object[,] oData = new object[aryRows, aryCols];
            Report.ClearDataArray(ref oData);

            oData[0, 1] = strMajorTitle;
            oData[2, 1] = strMinorTitle;
            sheetRow    = 4;

            oEndCell = "A1";
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
            #endregion

            #region get Budget Years
            strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + "ORDER BY Year_";
            DataSet dsBudgetYears = DBMgr.ExecuteQuery(strFilter);

            int numYears = dsBudgetYears.Tables[0].Rows.Count;
            #endregion

            aryCols = numYears + 2;
            //aryRows = dsResults.Tables[0].Rows.Count / 5 + 2;
            aryRows = dsResults.Tables[0].Rows.Count / numYears + 2;

            #region build Column Headers array
            Report.Resize2DArray(ref oData, aryRows + 1, aryCols);
            Report.ClearDataArray(ref oData);

            object[] oColumnHeader = new object[aryCols];
            oColumnHeader[0] = "Facility";
            oColumnHeader[1] = "Section";
            ndx = 2;
            foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
            {
                oColumnHeader[ndx++] = dr["Year_"].ToString();
            }
            #endregion

            #region build Results table
            ndx           = 0;
            oData[ndx, 0] = "Treatment Distribution per Section"; // Group Header

            sheetRow = 5;
            oEndCell = "A" + sheetRow.ToString();

            sheetRow++;
            ndx++;

            for (int i = 0; i < aryCols; i++)
            {
                oData[ndx, i] = oColumnHeader[i];     // Column Header
            }

            sheetRow++;
            ndx++;
            int           rowsToColumns = 0, nCol = 2;
            string        strGreen = "", strBlue = "", strRed = "";
            List <string> greenList = new List <string>();
            List <string> blueList  = new List <string>();
            List <string> redList   = new List <string>();

            foreach (DataRow dr in dsResults.Tables[0].Rows)
            {
                string sYear = dr["Years"].ToString();
                if (rowsToColumns < numYears)
                {
                    if (rowsToColumns == 0)
                    {
                        nCol = 2;
                        if (ndx == 132)
                        {
                            string testString = dr["Facility"].ToString();
                        }
                        oData[ndx, 0] = dr["Facility"].ToString();

                        oData[ndx, 1] = dr["Section"].ToString();
                    }
                }

                switch (DBMgr.NativeConnectionParameters.Provider)
                {
                case "MSSQL":

                    // Test for Treatment selected per RoadCare logic (GREEN)
                    if (dr["Treatment"].ToString() != "No Treatment" && dr["Iscommitted"].ToString().ToUpper() != "TRUE" && dr["Numbertreatment"].ToString() != "0")
                    {
                        string s         = Report.GetColumnLetter(nCol + 1);
                        char[] chr       = s.ToCharArray(0, 1);
                        char   asciiChar = chr[0];

                        Report.BuildRange(ref strGreen, ref greenList, sheetRow, asciiChar, 0);
                        oData[ndx, nCol] = dr["Treatment"].ToString();
                    }
                    // Test for Feasible treatment not funded/selected (BLUE)
                    else if (dr["Treatment"].ToString() == "No Treatment" && dr["Iscommitted"].ToString().ToUpper() != "TRUE" && dr["Numbertreatment"].ToString() != "0")
                    {
                        string s         = Report.GetColumnLetter(nCol + 1);
                        char[] chr       = s.ToCharArray(0, 1);
                        char   asciiChar = chr[0];
                        Report.BuildRange(ref strBlue, ref blueList, sheetRow, asciiChar, 0);
                    }
                    // Test for Treatment selected as Committed Project (RED)
                    else if (dr["Iscommitted"].ToString().ToUpper() == "TRUE")
                    {
                        string s         = Report.GetColumnLetter(nCol + 1);
                        char[] chr       = s.ToCharArray(0, 1);
                        char   asciiChar = chr[0];
                        Report.BuildRange(ref strRed, ref redList, sheetRow, asciiChar, 0);
                        oData[ndx, nCol] = dr["Treatment"].ToString();
                    }

                    break;

                case "ORACLE":

                    // Test for Treatment selected per RoadCare logic (GREEN)
                    if (dr["Treatment"].ToString() != "No Treatment" && dr["Iscommitted"].ToString() != "1" && dr["Numbertreatment"].ToString() != "0")
                    {
                        string s         = Report.GetColumnLetter(nCol + 1);
                        char[] chr       = s.ToCharArray(0, 1);
                        char   asciiChar = chr[0];

                        Report.BuildRange(ref strGreen, ref greenList, sheetRow, asciiChar, 0);
                        oData[ndx, nCol] = dr["Treatment"].ToString();
                    }
                    // Test for Feasible treatment not funded/selected (BLUE)
                    else if (dr["Treatment"].ToString() == "No Treatment" && dr["Iscommitted"].ToString() != "1" && dr["Numbertreatment"].ToString() != "0")
                    {
                        string s         = Report.GetColumnLetter(nCol + 1);
                        char[] chr       = s.ToCharArray(0, 1);
                        char   asciiChar = chr[0];
                        Report.BuildRange(ref strBlue, ref blueList, sheetRow, asciiChar, 0);
                    }
                    // Test for Treatment selected as Committed Project (RED)
                    else if (dr["Iscommitted"].ToString() == "1")
                    {
                        string s         = Report.GetColumnLetter(nCol + 1);
                        char[] chr       = s.ToCharArray(0, 1);
                        char   asciiChar = chr[0];
                        Report.BuildRange(ref strRed, ref redList, sheetRow, asciiChar, 0);
                        oData[ndx, nCol] = dr["Treatment"].ToString();
                    }

                    break;

                default:
                    throw new NotImplementedException("TODO: develop ANSI version of CreateDetailedResultsReport()");
                    //break;
                }

                nCol++;
                if (rowsToColumns + 1 >= numYears)
                {
                    rowsToColumns = 0;
                    ndx++;
                    sheetRow++;
                }
                else
                {
                    rowsToColumns++;
                }
            }

            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true);
            #endregion

            #region format pageheader
            // PAGEHEADER
            string  strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1";
            DataRow drPgHdr  = dsPage.Tables[0].Rows[0];
            Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
            strRange = "B2:" + Report.GetColumnLetter(aryCols) + "2";
            drPgHdr  = dsPage.Tables[0].Rows[1];
            Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
            strRange = "B3:" + Report.GetColumnLetter(aryCols) + "3";
            drPgHdr  = dsPage.Tables[0].Rows[2];
            Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);

            // Place a color legend at top and bottom of report
            PlaceColorLegend(oSheet, aryCols, 3);
            PlaceColorLegend(oSheet, 1, sheetRow + 1);
            #endregion

            #region format groupheader
            strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5";
            Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange);
            #endregion

            #region format columnHeader
            strRange = "A6:" + Report.GetColumnLetter(aryCols) + "6";
            Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
            #endregion

            #region apply color
            Report.EndRangeList(ref strGreen, ref greenList);
            Report.EndRangeList(ref strBlue, ref blueList);
            Report.EndRangeList(ref strRed, ref redList);
            foreach (string s in greenList)
            {
                oR = oSheet.get_Range(s, Missing.Value);
                oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightGreen);
            }

            foreach (string s in blueList)
            {
                oR = oSheet.get_Range(s, Missing.Value);
                oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue);
            }

            foreach (string s in redList)
            {
                oR = oSheet.get_Range(s, Missing.Value);
                oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightSalmon);
            }

            #endregion

            #region default column widths
            // set some column widths
            oR             = oSheet.get_Range("A1:B1", Missing.Value);
            oR.ColumnWidth = 15;
            strRange       = "C1:" + Report.GetColumnLetter(aryCols) + "1";
            oR             = oSheet.get_Range(strRange, Missing.Value);
            oR.ColumnWidth = 12;
            #endregion

            #region make bold
            strRange     = "A7:B" + (sheetRow - 1).ToString();
            oR           = oSheet.get_Range(strRange, Missing.Value);
            oR.Font.Bold = true;
            #endregion

            Report.XL.Visible     = true;
            Report.XL.UserControl = true;
        }
Ejemplo n.º 6
0
        public void CreateTotalLaneMileYearsPerConditionReport()
        {
            Report.XL.Visible     = false;
            Report.XL.UserControl = false;
            Microsoft.Office.Interop.Excel._Workbook  oWB    = Report.CreateWorkBook();
            Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

            string strReportName = "Lane Mile Years Per Condition";

            object oEndCell  = new object();
            string strFilter = "";

            int     sheetRow;
            int     ndx;
            DataSet dsPage = null, dsSimulations = null;

            try
            {
                dsPage        = DBOp.QueryPageHeader(strReportName);
                dsSimulations = DBOp.QuerySimulations(m_strSimulationID);
            }
            catch (Exception e)
            {
                throw e;
                // Circular reference, address later
                //Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message);
            }

            if (m_useLaneMiles)
            {
                Report.SheetPageSetup(oSheet, "Total Lane-Mile Years Report", 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1);
            }
            else
            {
                Report.SheetPageSetup(oSheet, "Total VML Years Report", 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1);
            }
            Range oR = oSheet.get_Range("A1", "A1");

            Cursor c = Cursor.Current;

            Cursor.Current = new Cursor(Cursors.WaitCursor.Handle);

            DataRow drPage        = dsPage.Tables[0].Rows[0];
            string  strMajorTitle = drPage["phText"].ToString();

            if (strMajorTitle.IndexOf("@1") > 0)
            {
                strMajorTitle = strMajorTitle.Replace("@1", m_strBudgetPer);                                    // stuff the attribute into title
            }
            if (!m_useLaneMiles)
            {
                strMajorTitle = strMajorTitle.Replace("Lane-Miles", "Vehicle Lane-Miles");
            }
            #region default column widths
            // set some column widths
            oR             = oSheet.get_Range("A1:A1", Missing.Value);
            oR.ColumnWidth = 18;
            #endregion

            #region place agency graphic
            //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
            string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
            Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet);
            #endregion

            #region write Major Title
            int aryCols = 2;
            int aryRows = 3;
            object[,] oData = new object[aryRows, aryCols];
            Report.ClearDataArray(ref oData);

            oData[0, 1] = strMajorTitle;
            sheetRow    = 4;

            oEndCell = "A1";
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
            #endregion

            #region get Budget Years
            strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + " ORDER BY Year_";
            DataSet dsBudgetYears = DBMgr.ExecuteQuery(strFilter);
            int     numYears      = dsBudgetYears.Tables[0].Rows.Count;
            #endregion

            int numConditions = 5, nTables = 2, rowSpacer = 2;
            aryCols = numYears + 1;
            aryRows = nTables * (numConditions + 3) + rowSpacer;

            #region build Column Headers array
            // Set up column header once, for multiple uses
            object[] oColumnHeader = new object[aryCols];
            oColumnHeader[0] = "Condition";  //m_strBudgetPer;
            ndx = 1;
            foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
            {
                oColumnHeader[ndx++] = dr["Year_"].ToString();
            }
            #endregion

            #region build Condition List
            // REVIST THIS REGION.  BUILD LIST FROM THE DATABASE WHEN POSSIBLE  xyzzy
            List <string> listCondition = new List <string>();
            listCondition.Add("Very Good");
            listCondition.Add("Good");
            listCondition.Add("Fair");
            listCondition.Add("Mediocre");
            listCondition.Add("Poor");
            #endregion

            #region build Budget and Percentage tables
            Report.Resize2DArray(ref oData, aryRows, aryCols);
            Report.ClearDataArray(ref oData);

            int       totalRow, nCol, budgetStartRowNdx, rowCounter = 0, acceptableRow;
            Hashtable laneMilesPerCondition;
            double    nTmp, nTmp1;
            ndx           = 0;
            oData[ndx, 0] = "Lane-miles per Condition Category"; // Group Header

            sheetRow      = 4;
            totalRow      = 2 + numConditions;
            acceptableRow = oData.GetUpperBound(0) - 1;
            oEndCell      = "A" + sheetRow.ToString();

            sheetRow++;
            ndx++;
            oData[totalRow, 0] = "Total";

            for (int i = 0; i < aryCols; i++)
            {
                oData[ndx, i] = oColumnHeader[i];     // Column Header
            }

            sheetRow++;
            ndx++;
            budgetStartRowNdx = 2;   // percentRowNdx is used to pull a value out of oData then use it to calculate the percent table
            List <string> listAttributes;
            string        strCriteria;
            string        strLeftSide = "", strConnector = "", strRightSide = "";
            double        nBudget = 0d;
            foreach (string s in listCondition)
            {
                oData[ndx, 0] = s;
                nCol          = 1;
                foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
                {
                    ConditionCriteria(s, ref strLeftSide, ref strConnector, ref strRightSide);
                    strCriteria = m_strBudgetPer + "_" + dr["Year_"].ToString() + strLeftSide;
                    if (strConnector != "")
                    {
                        strCriteria += strConnector + m_strBudgetPer + "_" + dr["Year_"].ToString() + strRightSide;
                    }
                    strFilter = " YEARS = " + dr["Year_"].ToString() + " AND " + strCriteria;     //"BUDGET IS NOT NULL AND YEARS = " + dr["Year"].ToString();
                    //strFilter = " YEARS = " + dr["Year"].ToString();     //"BUDGET IS NOT NULL AND YEARS = " + dr["Year"].ToString();
                    laneMilesPerCondition = DBOp.GetPercentagePerStringAttribute(m_strNetworkID, m_strSimulationID, m_strBudgetPer.ToUpper(), dr["Year_"].ToString(), "AREA", strFilter, "REMAINING_LIFE", m_useLaneMiles, out listAttributes);
                    nBudget = 0d;
                    foreach (DictionaryEntry de in laneMilesPerCondition)
                    {
                        string entry = (string)de.Value;
                        if (entry != "")
                        {
                            nBudget += Convert.ToDouble(entry);
                        }
                    }
                    //string strHash = (string)laneMilesPerCondition[s.ToUpper()];
                    //double nBudget = Convert.ToDouble(strHash);
                    if (oData[totalRow, nCol].ToString() == "")
                    {
                        oData[totalRow, nCol] = 0d;                                                // initalize array element
                    }
                    nTmp  = (double)oData[totalRow, nCol];
                    nTmp += nBudget;
                    oData[totalRow, nCol] = nTmp;
                    oData[ndx, nCol++]    = nBudget;
                }
                ndx++;
                sheetRow++;
            }

            sheetRow += 2;
            ndx      += 2;

            if (m_useLaneMiles)
            {
                oData[ndx, 0] = "Lane-Mile Years Distribution (%) per Condition Category";
            }
            else
            {
                oData[ndx, 0] = "Vehicle Lane-Mile Years Distribution (%) per Condition Category";
            }
            ndx++;
            sheetRow++;

            for (int i = 0; i < aryCols; i++)
            {
                oData[ndx, i] = oColumnHeader[i];     // Column Header
            }

            sheetRow++;
            ndx++;
            rowCounter = 1;
            foreach (string s in listCondition)
            {
                oData[ndx, 0] = s;
                nCol          = 1;
                foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
                {
                    nTmp  = (double)oData[budgetStartRowNdx, nCol];
                    nTmp1 = (double)oData[totalRow, nCol];
                    double nPercent = nTmp / nTmp1;
                    oData[ndx, nCol] = nPercent;
                    if (rowCounter <= 3)
                    {   // sum the % Acceptable
                        if (oData[acceptableRow, nCol].ToString() == "")
                        {
                            oData[acceptableRow, nCol] = 0d;                                                     // initalize array element
                        }
                        nTmp  = (double)oData[acceptableRow, nCol];
                        nTmp += nPercent;
                        oData[acceptableRow, nCol] = nTmp;
                    }
                    else
                    {   // sum the % Unacceptable
                        if (oData[acceptableRow + 1, nCol].ToString() == "")
                        {
                            oData[acceptableRow + 1, nCol] = 0d;                                    // initalize array element
                        }
                        nTmp  = (double)oData[acceptableRow + 1, nCol];
                        nTmp += nPercent;
                        oData[acceptableRow + 1, nCol] = nTmp;
                    }
                    nCol++;
                }
                ndx++;
                sheetRow++;
                budgetStartRowNdx++;
                rowCounter++;
            }
            oData[acceptableRow, 0] = "%Acceptable";
            sheetRow++;
            oData[acceptableRow + 1, 0] = "%Unacceptable";


            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
            #endregion

            #region format pageheader
            // PAGEHEADER
            string  strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1";
            DataRow drPgHdr  = dsPage.Tables[0].Rows[0];
            Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
            #endregion

            #region format groupheader
            int startingRow = 4;
            int offSet      = startingRow + numConditions + 2 + rowSpacer;
            strRange = "A4:" + Report.GetColumnLetter(aryCols) + "4";
            Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange);
            strRange = "A" + offSet.ToString() + ":" + Report.GetColumnLetter(aryCols) + offSet.ToString();
            Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange);
            #endregion

            #region format columnHeader
            strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5" + ", A" + (offSet + 1).ToString() + ":" + Report.GetColumnLetter(aryCols) + (offSet + 1).ToString();
            Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
            #endregion

            #region format totals row
            strRange = "A" + (totalRow + 4).ToString() + ":" + Report.GetColumnLetter(aryCols) + (totalRow + 4).ToString();
            Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);

            strRange = "A" + (acceptableRow + 4).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + 4).ToString();
            Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
            strRange = "A" + (acceptableRow + 5).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + 5).ToString();
            Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
            #endregion

            #region format grid data
            strRange  = "A5:" + Report.GetColumnLetter(aryCols) + (totalRow + 4).ToString();
            strRange += ",A" + (acceptableRow - numConditions + startingRow - 1).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + startingRow + 1).ToString();
            oR        = oSheet.get_Range(strRange, Missing.Value);
            oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            oR.Borders.LineStyle   = XlLineStyle.xlContinuous;
            oR.Borders.Weight      = XlBorderWeight.xlThin;
            oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle         = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle        = XlLineStyle.xlContinuous;
            #endregion

            #region format Lane Mile cells
            strRange        = "B6:" + Report.GetColumnLetter(aryCols) + (totalRow + 4).ToString();
            oR              = oSheet.get_Range(strRange, Missing.Value);
            oR.NumberFormat = "#,##0.0";
            oR.ColumnWidth  = 15;
            #endregion

            #region format Percent cells
            strRange        = "B" + (acceptableRow - numConditions + startingRow).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + startingRow + 1).ToString();
            oR              = oSheet.get_Range(strRange, Missing.Value);
            oR.NumberFormat = "0.0%";
            #endregion

            #region create column charts
            strRange = "B6:" + Report.GetColumnLetter(aryCols) + (totalRow + startingRow - 1).ToString();

            Range oSourceData = oSheet.get_Range(strRange, Missing.Value);
            int   left        = (int)Report.GetColumnWidthInPixels(oSourceData, oSheet);

            if (m_useLaneMiles)
            {
                Report.CreateColClusterBarGraph(left, 15, 425, 315, oSheet, oSourceData, "='Total Lane-Mile Years Report'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5",
                                                "Annual Total Lane-Mile Years Per Condition", 12, "Fiscal Year", 11, listCondition, "", 11, XlRowCol.xlRows);
            }
            else
            {
                Report.CreateColClusterBarGraph(left, 15, 425, 315, oSheet, oSourceData, "='Total VML Years Report'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5",
                                                "Annual Total VLM Years Per Condition", 12, "Fiscal Year", 11, listCondition, "", 11, XlRowCol.xlRows);
            }
            #endregion

            Report.XL.Visible     = true;
            Report.XL.UserControl = true;
        }
Ejemplo n.º 7
0
        public void CreateLaneMilesPerTreatmentReport()
        {
            Report.XL.Visible     = false;
            Report.XL.UserControl = false;
            Microsoft.Office.Interop.Excel._Workbook  oWB    = Report.CreateWorkBook();
            Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

            string tabName = "Lane-Miles Per";

            if (!m_useLaneMiles)
            {
                tabName = "VLM Per";
            }

            Report.SheetPageSetup(oSheet, tabName + m_strBudgetPer, 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1);
            Range oR = oSheet.get_Range("A1", "A1");

            object oEndCell      = new object();
            string strFilter     = "";
            string strReportName = "Lane-Miles Per Treatment Report";
            string strRange;

            int     sheetRow;
            int     ndx, nCol;
            DataSet dsPage = null, dsSimulations = null;

            try
            {
                dsPage        = DBOp.QueryPageHeader(strReportName);
                dsSimulations = DBOp.QuerySimulations(m_strSimulationID);
            }
            catch (Exception e)
            {
                throw e;
                // Circular reference, address later
                //Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message);
            }
            if (m_useLaneMiles)
            {
                strReportName = "Vehicle Lane-Miles Per Treatment Report";
            }
            Cursor c = Cursor.Current;

            Cursor.Current = new Cursor(Cursors.WaitCursor.Handle);

            DataRow drPage        = dsPage.Tables[0].Rows[0];
            string  strMajorTitle = drPage["phText"].ToString();

            if (!m_useLaneMiles)
            {
                strMajorTitle = "Vehicle " + strMajorTitle;
            }
            #region default column widths
            // set some column widths
            oR             = oSheet.get_Range("A1:A1", Missing.Value);
            oR.ColumnWidth = 18;
            #endregion

            #region place agency graphic
            //string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
            string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
            Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet);
            #endregion

            #region write Major Title
            int aryCols = 2;
            int aryRows = 3;
            object[,] oData = new object[aryRows, aryCols];
            Report.ClearDataArray(ref oData);

            oData[0, 1] = strMajorTitle;
            sheetRow    = 4;

            oEndCell = "A1";
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
            #endregion

            #region get Budget Years
            DataSet dsBudgetYears = DBOp.QueryBudgetYears(m_strSimulationID);
            int     numYears      = dsBudgetYears.Tables[0].Rows.Count;
            #endregion

            #region get Treatments
            List <string> treatmentList = DBOp.GetTreatments(m_strNetworkID, m_strSimulationID);
            treatmentList.Remove("No Treatment");
            int numTreatments = treatmentList.Count();
            #endregion

            aryCols = numYears + 1;
            aryRows = numTreatments + 3;
            Report.Resize2DArray(ref oData, aryRows, aryCols);
            Report.ClearDataArray(ref oData);

            #region build the Lane-Miles table
            Hashtable laneMilesPerTreatment;
            // get the number of districts
            List <string> strJuris = DBOp.GetJurisdiction(m_strNetworkID, "DISTRICT");
            strJuris.Sort();
            int numDistricts = strJuris.Count;
            sheetRow = 4;

            ndx = 1;
            if (m_useLaneMiles)
            {
                oData[0, 0] = "Annual Total Lane-Miles";
            }
            else
            {
                oData[0, 0] = "Annual Total Vehicle Lane-Miles";
            }
            oData[1, 0] = m_strBudgetPer;
            foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
            {
                oData[1, ndx++] = dr["Year_"];
            }
            sheetRow++;
            ndx  = 2;
            nCol = 0;
            foreach (string s in treatmentList)
            {
                oData[ndx++, nCol] = s;
                sheetRow++;
            }
            oData[ndx, nCol] = "Total";
            sheetRow++;
            int           totalRow = oData.GetUpperBound(0);
            double        nTotal = 0d, nMiles;
            List <string> listAttributes;
            foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
            {
                ndx = 2;
                nCol++;
                nTotal = 0d;
                foreach (string s in treatmentList)
                {
                    strFilter             = "TREATMENT = '" + s + "' AND YEARS = " + dr["Year_"].ToString();
                    laneMilesPerTreatment = DBOp.GetPercentagePerStringAttribute(m_strNetworkID, m_strSimulationID, "DISTRICT", "", "AREA", strFilter, m_useLaneMiles, out listAttributes);
                    nMiles                = sumLaneMiles(laneMilesPerTreatment, numDistricts); // have to sum the districts to get the total
                    nTotal               += nMiles;
                    oData[ndx++, nCol]    = nMiles;
                    oData[totalRow, nCol] = nTotal;
                }
            }


            oEndCell = "A4";
            oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
            #endregion

            #region format pageheader
            // PAGEHEADER
            strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1";
            DataRow drPgHdr = dsPage.Tables[0].Rows[0];
            Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
            #endregion

            #region format groupheader
            strRange = "A4:" + Report.GetColumnLetter(aryCols) + "4";
            Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange);
            #endregion

            #region format columnHeader
            strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5";
            Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
            #endregion

            #region format totals row
            strRange = "A" + (sheetRow).ToString() + ":" + Report.GetColumnLetter(aryCols) + (sheetRow).ToString();
            Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
            #endregion

            #region format grid data
            strRange = "A6:" + Report.GetColumnLetter(aryCols) + (sheetRow).ToString();
            oR       = oSheet.get_Range(strRange, Missing.Value);
            oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            oR.Borders.LineStyle   = XlLineStyle.xlContinuous;
            oR.Borders.Weight      = XlBorderWeight.xlThin;
            oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle         = XlLineStyle.xlContinuous;
            oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle        = XlLineStyle.xlContinuous;

            #endregion

            #region set column width
            strRange        = "B6:" + Report.GetColumnLetter(aryCols) + (sheetRow).ToString();
            oR              = oSheet.get_Range(strRange, Missing.Value);
            oR.NumberFormat = "#,##0.0";
            oR.ColumnWidth  = 12;
            #endregion

            #region create column charts
            string strTitle = "";
            strRange = "B6:" + Report.GetColumnLetter(aryCols) + (sheetRow - 1).ToString();
            Range oSourceData = oSheet.get_Range(strRange, Missing.Value);
            int   left        = (int)Report.GetColumnWidthInPixels(oSourceData, oSheet);
            //strRange = "B6:F13";
            //oSourceData = oSheet.get_Range((sTmp + nTmp.ToString()), Missing.Value);

            strTitle = "Annual Lane-Miles Per " + m_strBudgetPer;
            if (!m_useLaneMiles)
            {
                strTitle = "Annual Vehicle Lane-Miles Per " + m_strBudgetPer;
            }

            string sheetName = "='Lane-Miles Per" + m_strBudgetPer + "'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5";
            if (!m_useLaneMiles)
            {
                sheetName = "='VLM Per" + m_strBudgetPer + "'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5";
            }

            Report.CreateColClusterBarGraph(left, 15, 425, 315, oSheet, oSourceData, sheetName,
                                            strTitle, 12, "Fiscal Year", 11, treatmentList, "", 11, XlRowCol.xlRows);

            #endregion

            Report.XL.Visible     = true;
            Report.XL.UserControl = true;
        }   // end CreateBudgetPerTreatmentReport