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
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;
}
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;
}
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