public Dictionary <string, double> CreateAttributeBins(List <BinAttribute> binAttributes, Method method)
{
Dictionary <string, double> runningAttributeTotals = new Dictionary <string, double>();
List <string> attributeValues;
double attributeNumValue;
Hashtable attributeFrequency = DBOp.GetPercentagePerStringAttribute(m_networkID, m_simulationID, m_attribute, m_year, method.ToString(), m_criteria, false, out attributeValues);
foreach (BinAttribute binAttribute in binAttributes)
{
runningAttributeTotals.Add(binAttribute.Name, 0);
}
runningAttributeTotals.Add("Other", 0);
foreach (string attributeValue in attributeValues)
{
foreach (BinAttribute binAttribute in binAttributes)
{
if (attributeValue != null && attributeValue != "" && attributeValue != "NULL")
{
attributeNumValue = double.Parse(attributeValue);
if (attributeNumValue > binAttribute.Min && attributeNumValue <= binAttribute.Max)
{
runningAttributeTotals[binAttribute.Name] += double.Parse(attributeFrequency[attributeValue].ToString());
}
}
else
{
runningAttributeTotals["Other"] += double.Parse(attributeFrequency[attributeValue].ToString());
}
}
}
return(runningAttributeTotals);
}
/// Created - 8/19/2008 By: CBB
/// <summary>
/// As users add changes to asset history data, the data is stored in changelog tables in the database.
/// The new event must be populated to the asset history combo box, so the user can select it immediately after making the change.
/// </summary>
/// <param name="strGeoID">The asset ID of the asset to which the change is being applied.</param>
/// <param name="b">Not being used currently...</param>
private void RePopulateAssetHistoryComboBox(String strGeoID, bool b)
{
DataSet ds = null;
try
{
// Get the new asset history information, including any changes just made in the data grid view.
ds = DBOp.QueryAssetHistory(m_MapQueryDataTable.TableName, strGeoID);
}
catch (Exception exc)
{
//Global.WriteOutput("Error: Problem querying asset history table. " + exc.Message);
System.Diagnostics.Debug.WriteLine("Error: Problem querying asset history table. " + exc.Message);
return;
}
// Re-create the combo box.
cbActivityDate.Items.Clear();
cbActivityDate.Items.Add("Most Recent");
String activity;
String date;
foreach (DataRow dr in ds.Tables[0].Rows)
{
activity = dr["WORKACTIVITY"].ToString();
date = dr["DATE_MODIFIED"].ToString();
cbActivityDate.Items.Add(activity + " - " + date);
}
}
private void LoadCompoundTreatments()
{
dgvCompoundTreatments.Rows.Clear();
List <CompoundTreatment> compoundTreatments = DBOp.GetCompoundTreatments();
foreach (CompoundTreatment toLoad in compoundTreatments)
{
dgvCompoundTreatments.Rows.Add(toLoad, toLoad.AffectedAttribute, toLoad.CompoundTreatmentName);
}
// If the user is selecting an existing compound treatment, then we need to select it in the grid, and bring up its elements.
if (_compoundTreatment != null)
{
// Select the passed in compound treatment in the dataGridView
foreach (DataGridViewRow possibleRow in dgvCompoundTreatments.Rows)
{
if (possibleRow.Cells["colCompoundTreatmentName"].Value != null)
{
if (possibleRow.Cells["colCompoundTreatmentName"].Value.ToString() == _compoundTreatment.CompoundTreatmentName)
{
possibleRow.Selected = true;
}
}
}
}
}
private void FormAssetView_Load(object sender, EventArgs e)
{
SecureForm();
FormLoad(Settings.Default.ASSET_VIEW_IMAGE_KEY, Settings.Default.ASSET_VIEW_IMAGE_KEY_SELECTED);
m_networkName = DBOp.GetNetworkName(m_networkID);
TabText = "Asset View-" + m_networkName;
Tag = "Asset View-" + m_networkName;
labelAttribute.Text += " " + m_networkName;
comboBoxRouteFacilty.Text = "All";
FillRouteTable();
ApplyFiltersToSectionGrid();
this.dgvSection.RowEnter += new System.Windows.Forms.DataGridViewCellEventHandler(this.dgvSection_RowEnter);
this.comboBoxRouteFacilty.SelectedIndexChanged += new System.EventHandler(this.comboBoxRouteFacilty_SelectedIndexChanged);
// Add to the FormManager
FormManager.AddBaseForm(this);
// Now create a new Asset Manager in the right dock panel on this form.
assetManager = new AssetTab(m_networkName, m_hashAttributeYear);
assetManager.Show(dpAssetDisplayContainer, DockState.DockRight);
tabByAssetType = new TabByAssetType(assetManager, this);
tabByAssetType.Show(dpAssetDisplayContainer, DockState.Document);
tabAssetBySelection = new TabAssetBySelection(assetManager);
tabAssetBySelection.Show(dpAssetDisplayContainer, DockState.Document);
}
public FormInvestment(String strNetwork, String strSimulation, String strSimID)
{
m_strNetwork = strNetwork;
m_strNetworkID = DBOp.GetNetworkIDFromName(strNetwork);
m_strSimulation = strSimulation;
m_strSimID = strSimID;
InitializeComponent();
}
public FormPerformanceEquations(String strNetwork, String strSimulation, String strSimulationID, Hashtable hashAttributeYear)
{
m_strNetwork = strNetwork;
m_strNetworkID = DBOp.GetNetworkIDFromName(strNetwork);
m_strSimulation = strSimulation;
m_strSimulationID = strSimulationID;
m_hashAttributeYear = hashAttributeYear;
InitializeComponent();
}
private void FormPerformanceEquations_Load(object sender, EventArgs e)
{
SecureForm();
FormLoad(Settings.Default.SIMULATION_IMAGE_KEY, Settings.Default.SIMULATION_IMAGE_KEY_SELECTED);
this.labelPerformance.Text = "Performance Equations for " + m_strSimulation + " : " + m_strNetwork;
Global.LoadAttributes();
foreach (String strAttribute in Global.Attributes)
{
this.Attribute.Items.Add(strAttribute);
}
List <Performance> performances = null;
try
{
performances = DBOp.GetPerformanceEquations(m_strSimulationID);
}
catch (Exception except)
{
Global.WriteOutput("Error retrieving performance equations: " + except.Message);
return;
}
List <String> listDelete = new List <String>();
foreach (Performance performance in performances)
{
if (!Global.Attributes.Contains(performance.Attribute))
{
listDelete.Add(performance.PerformanceID);
}
else
{
object[] dataRow = { performance.Attribute, performance.Name, performance.Equation, performance.Criteria.Replace("|", "'"), performance.IsShift };
int nIndex = dgvPerfomance.Rows.Add(dataRow);
dgvPerfomance.Rows[nIndex].Tag = performance;
}
}
try
{
DBOp.DeletePerformanceEquations(listDelete);
}
catch (Exception except)
{
Global.WriteOutput("Error deleting performance equations with deleted attributes: " + except.Message);
return;
}
dgvPerfomance.Columns[2].ReadOnly = true;
dgvPerfomance.Columns[3].ReadOnly = true;
m_bUpdate = true;
}
public AssetRollup(String strServer, String strDataSource, String strUserID, String strPassword, String networkID)
{
m_ServerName = strServer;
m_DataSource = strDataSource;
m_UserID = strUserID;
m_Password = strPassword;
m_networkID = networkID;
// First get the list of assets from the asset table.
m_assetNames = DBOp.GetRawAssetNames();
}
internal void UnlockNetwork()
{
if (lockID != "")
{
DBOp.RemoveLock(lockID);
}
else
{
throw new Exception("ERROR: Cannot attempt unlock on uninitialized LockInformation object.");
}
}
private void TabByAssetType_Load(object sender, EventArgs e)
{
List <String> assetTypes = DBOp.GetRawAssetNames();
cbAssetTypes.Items.Clear();
foreach (String assetType in assetTypes)
{
cbAssetTypes.Items.Add(assetType);
}
SecureForm();
}
private void buttonOK_Click(object sender, EventArgs e)
{
if (textBoxConnName.Text == "")
{
MessageBox.Show("You must enter a Connection Name.", "RoadCare3");
}
else
{
string activeTabID = tcLogin.SelectedTab.Name;
_cp = CreateConnectionParameters(activeTabID);
DBOp.AddToConnectionParameters(_cp, textBoxConnName.Text);
}
}
public PennDotBaseReport(string networkId, string simulationId, _Worksheet oSheet, BridgeAnalysis analysis)
{
Sheet = oSheet;
NetworkId = networkId;
SimulationId = simulationId;
Analysis = analysis;
Network = DBOp.GetNetworkDesc(NetworkId);
Simulation = DBOp.QuerySimulations(SimulationId);
Investment = DBOp.QueryInvestments(SimulationId);
var drInvestment = Investment.Tables[0].Rows[0];
FirstYear = Convert.ToInt32(drInvestment["FIRSTYEAR"]);
NumberYears = Convert.ToInt32(drInvestment["NUMBERYEARS"]);
}
private void UpdateSectionGrid()
{
bool IsLinear = false;
if (checkMilepost.Checked)
{
IsLinear = true;
}
string strSelect = DBOp.GetAdvancedFilterSelectStatement(IsLinear, m_strNetworkID, comboBoxRouteFacilty.Text, textBoxAdvanceSearch.Text, comboBoxFilterAttribute.Text, comboBoxAttributeValue.Text);
if (dataAdapter != null)
{
dataAdapter.Dispose(); // Free up the resources
}
if (binding != null)
{
binding.Dispose();
}
if (table != null)
{
table.Dispose();
}
try
{
binding = new BindingSource();
dataAdapter = new DataAdapter(strSelect);
// Populate a new data table and bind it to the BindingSource.
table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
binding.DataSource = table;
dgvSection.DataSource = binding;
bindingNavigatorSectionView.BindingSource = binding;
dgvSection.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
for (int i = 0; i < dgvSection.ColumnCount; i++)
{
dgvSection.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}
dgvSection.Columns["SECTIONID"].Visible = false;
}
catch (Exception exc)
{
Global.WriteOutput("Error: Filling Section View. Check advanced search. " + exc.Message);
}
}
private void comboBoxAttributeValue_SelectedIndexChanged(object sender, EventArgs e)
{
bool IsLinear = false;
if (checkMilepost.Checked)
{
IsLinear = true;
}
string facility = comboBoxRouteFacilty.Text;
string advancedSearchText = textBoxAdvanceSearch.Text;
string property = comboBoxFilterAttribute.Text;
string value = comboBoxAttributeValue.Text;
DBOp.GetAdvancedFilterSelectStatement(IsLinear, m_strNetworkID, facility, advancedSearchText, property, value);
}
private void NetworkAdvancedSearch()
{
if (MainMapImage.Map.Layers.Count > 0)
{
ApplyOldColorsToGeoms(true);
String strQuery = tbAdvancedSearch.Text;
FormAdvancedSearch form = new FormAdvancedSearch(m_strNetworkID, m_strSimulationID, tbAdvancedSearch.Text);
form.Text = "Attribute/Network Map";
if (form.ShowDialog() == DialogResult.OK)
{
tbAdvancedSearch.Text = form.GetWhereClause();
m_htAdvancedSearchOldColors.Clear();
String strSelect;
strSelect = "SELECT SECTION_" + m_strNetworkID + ".SECTIONID";
String strFrom = DBOp.BuildFromStatement(m_strNetworkID, m_strSimulationID, true);
strSelect += strFrom;
String strWhere = tbAdvancedSearch.Text;
strSelect += " WHERE " + strWhere;
String strSectionID;
Geometry geo;
try
{
DataReader dr = new DataReader(strSelect);
while (dr.Read())
{
strSectionID = dr["SECTIONID"].ToString();
geo = (Geometry)MainMapImage.Map.Layers[0].GeoIDs[strSectionID];
if (geo != null)
{
m_htAdvancedSearchOldColors.Add(geo, geo.Color);
geo.Color = Color.Gold;
geo.Width_ = 3;
}
}
dr.Close();
}
catch (Exception exc)
{
Global.WriteOutput("Error: Advanced attribute search failed. " + exc.Message);
return;
}
MainMapImage.Refresh();
}
}
}
public BridgeAnalysis(string networkId, string simulationId)
{
Bridges = new List <BridgeCondition>();
var investment = DBOp.QueryInvestments(simulationId);
FirstYear = Convert.ToInt32(investment.Tables[0].Rows[0]["FIRSTYEAR"]);
NumberYears = Convert.ToInt32(investment.Tables[0].Rows[0]["NUMBERYEARS"]);
var simulation = DBOp.QuerySimulationResult(networkId, simulationId);
foreach (DataRow row in simulation.Tables[0].Rows)
{
Bridges.Add(new BridgeCondition(row, FirstYear, NumberYears));
}
}
private void OnActivityChange()
{
int iSelectedIndex = cbSelectedAsset.SelectedIndex;
DataRow dataRow = m_MapQueryDataTable.Rows[iSelectedIndex];
String strGeoID = dataRow["GEO_ID"].ToString();
dgvAssetSelection.Rows.Clear();
// Show the asset in its most current state. (This is its state in the asset viewer.)
List <String> listGeomIDs = new List <String>();
for (int i = 0; i < dataRow.ItemArray.Length; i++)
{
dgvAssetSelection.Rows.Add(m_MapQueryDataTable.Columns[i].ColumnName, dataRow.ItemArray[i].ToString());
}
//m_strImagePath = dataRow[Settings.Default.IMAGE_PATH].ToString();
//if (m_strImagePath != "")
//{
// m_strImagePath = m_strImagePath.Substring(1, m_strImagePath.Length - 1);
//}
// Use the GEO_ID to undo any changes made to the asset up to and including the selected ActivityDate.
if (cbActivityDate.Text != "Most Recent")
{
Regex dateSelector = new Regex("(1[0-2]|[0-9])/[0-3]?[0-9]/[0-9]+");
Regex timeSelector = new Regex("((1[0-2])|[1-9]):[0-5][0-9]:[0-5][0-9] [A|P]M");
String strDate = dateSelector.Match(cbActivityDate.Text).Value + " " + timeSelector.Match(cbActivityDate.Text).Value;
DataSet ds = DBOp.QueryAssetHistory(m_MapQueryDataTable.TableName, strGeoID, strDate);
Hashtable attributeRows = new Hashtable();
foreach (DataGridViewRow dgvRow in dgvAssetSelection.Rows)
{
attributeRows.Add(dgvRow.Cells[0].Value.ToString(), dgvRow);
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
//set the value of the associated row to value from the changelog
//System.Diagnostics.Debug.WriteLine(dr["VALUE"].ToString());
((DataGridViewRow)attributeRows[dr["FIELD"].ToString()]).Cells[1].Value = dr["VALUE"].ToString();
}
}
else
{
//((DataGridViewRow)attributeRows[dr["FIELD"].ToString()]).Cells[1].Value = dr["VALUE"].ToString();
}
}
private void comboBoxFilterAttribute_SelectedIndexChanged(object sender, EventArgs e)
{
bool IsLinear = false;
if (checkMilepost.Checked)
{
IsLinear = true;
}
string selectedAttribute = comboBoxFilterAttribute.Text;
comboBoxAttributeValue.Items.Clear();
comboBoxAttributeValue.Items.Add("All");
String strSelect = "SELECT DISTINCT DATA_ FROM " + selectedAttribute;
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow row in ds.Tables[0].Rows)
{
comboBoxAttributeValue.Items.Add(row[0].ToString());
}
comboBoxAttributeValue.Text = "All";
string filterQuery = DBOp.GetAdvancedFilterSelectStatement(IsLinear, m_strNetworkID, comboBoxRouteFacilty.Text, textBoxAdvanceSearch.Text, comboBoxFilterAttribute.Text, comboBoxAttributeValue.Text);
}
private void FormAssetFilter_Load(object sender, EventArgs e)
{
m_AssetTypes = DBOp.GetRawAssetNames();
m_AttributeTypes = DBOp.GetAttributeNames();
List <String> years;
foreach (String assetType in m_AssetTypes)
{
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(assetType);
TreeNode tnAssetType = tvFilterCriteria.Nodes.Add(assetType);
tnAssetType.Tag = "ASSET";
List <String> assetAttributes = DBOp.GetAssetAttributes(assetType, cp);
foreach (String assetAttribute in assetAttributes)
{
tnAssetType.Nodes.Add(assetAttribute);
}
}
foreach (String attributeType in m_AttributeTypes)
{
TreeNode tnRoadAttribute = tvFilterCriteria.Nodes.Add(attributeType);
tnRoadAttribute.Tag = "ATTRIBUTE";
try
{
years = Global.GetAttributeYears(attributeType, m_attributeYears);
foreach (String year in years)
{
tnRoadAttribute.Nodes.Add(year);
}
tnRoadAttribute.Nodes.Add(attributeType);
}
catch // (Exception exc)
{
//Global.WriteOutput("Error: Problem encountered while filling road attribute node. Check for valid attribute/asset rollup. " + exc.Message);
}
}
}
private void UpdateValues(bool bShowAll)
{
String strSelect = "";
String strFrom = DBOp.BuildFromStatement(m_strNetworkID, m_strSimulationID, bShowAll);
Global.LoadAttributes();
if (treeViewAttribute.SelectedNode == null)
{
return;
}
if (treeViewAttribute.SelectedNode.Level == 0)
{
String strAttribute = treeViewAttribute.SelectedNode.Text;
String sSection = "SECTION_" + m_strNetworkID;
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
if (!bShowAll)
{
strSelect = "SELECT DISTINCT " + strAttribute + strFrom + " WHERE (ABS(CAST((BINARY_CHECKSUM(" + sSection + ".SECTIONID, NEWID())) as int))% 100) < 1";
}
else
{
strSelect = "SELECT DISTINCT " + strAttribute + strFrom;
}
break;
case "ORACLE":
if (!bShowAll)
{
strSelect = "SELECT DISTINCT " + strAttribute + strFrom;
}
else
{
strSelect = "SELECT DISTINCT " + strAttribute + strFrom;
}
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for UpdateValueList()");
}
}
else // A year has been selected
{
String strYear = treeViewAttribute.SelectedNode.Text;
String strAttribute = treeViewAttribute.SelectedNode.Parent.Text;
String strAttributeYear;
String sSection = "SECTION_" + m_strNetworkID;
if (strYear == strAttribute)
{
strAttributeYear = strAttribute;
}
else
{
strAttributeYear = strAttribute + "_" + strYear;
}
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
if (!bShowAll)
{
strSelect = "SELECT DISTINCT " + strAttribute + strFrom + " WHERE (ABS(CAST((BINARY_CHECKSUM(" + sSection + ".SECTIONID, NEWID())) as int))% 100) < 1";
}
else
{
strSelect = "SELECT DISTINCT " + strAttribute + strFrom;
}
break;
case "ORACLE":
if (!bShowAll)
{
strSelect = "SELECT DISTINCT " + strAttribute + strFrom;
}
else
{
strSelect = "SELECT DISTINCT " + strAttribute + strFrom;
}
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for UpdateValueList()");
//break;
}
}
listBoxValues.Items.Clear();//Clear existing.
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow row in ds.Tables[0].Rows)
{
listBoxValues.Items.Add(row[0].ToString());
}
}
catch (Exception exc)
{
listBoxValues.Items.Clear();
Global.WriteOutput("Error filling value list. " + exc.Message);
throw exc;
}
}
/// <summary> /// Retrieves the analysis years and most recent data collection year for the given simulation. /// </summary> /// <param name="simulationId"></param> /// <returns>a list of analysis years and most recent data collection year</returns> public static List <string> GetAnalysisYearsInclMostRecent(string simulationId) => DBOp.QueryBudgetYearsinclmostrecent(simulationId).Tables[0].AsEnumerable() .Select(r => r[0].ToString()).ToList();
private void btnLogin_Click(object sender, EventArgs e)
{
string activeTabID = tcLogin.SelectedTab.Name;
m_bUseIntegratedSecurity = chkUseIntegratedSecurity.Checked;
ConnectionParameters cpNative = CreateConnectionParameters(activeTabID);
if (cpNative != null)
{
try
{
DBMgr.NativeConnectionParameters = cpNative;
switch (cpNative.Provider)
{
case "MSSQL":
Settings.Default.DBDATABASE = tbMSSQLDatabaseName.Text;
Settings.Default.USERNAME_DIU = tbRoadCareUserName.Text;
Settings.Default.DBSERVER = tbMSSQLServerName.Text;
Settings.Default.DefaultTab = "MSSQL";
Settings.Default.USE_INTEGRATED_SECURITY = chkUseIntegratedSecurity.Checked;
break;
case "ORACLE":
Settings.Default.DBPORT = tbPort.Text;
Settings.Default.USERNAME_DIU = tbRoadCareUserName.Text;
Settings.Default.DBSERVER = tbOracleServerName.Text;
Settings.Default.DBNETWORKALIAS = tbNetworkAlias.Text;
Settings.Default.DBSID = tbSID.Text;
Settings.Default.USE_INTEGRATED_SECURITY = false;
Settings.Default.DefaultTab = "ORACLE";
Settings.Default.ORACLE_USE_SID = rbSID.Checked;
Settings.Default.ORACLE_DB_USERNAME = txtOracleUserID.Text;
Settings.Default.ORACLE_DB_PASSWORD = txtOraclePassword.Text;
break;
default:
break;
}
Settings.Default.LAST_LOGIN = tbRoadCareUserName.Text;
Settings.Default.Save();
}
catch (Exception exc)
{
MessageBox.Show("Error connecting to database. Please check the database name and try again.\n" + exc.Message, "RoadCare3");
return;
}
// This is a compliment to the update tables in CheckFreshInstall
DBOp.UpdateTables();
Global.SecurityOperations.CheckFreshInstall();
try
{
Global.SecurityOperations.SetCurrentUser(tbRoadCareUserName.Text, tbRoadCarePassword.Text);
}
catch
{
MessageBox.Show("User failed to Login. Username and/or Password incorrect.");
return;
}
if (!Global.SecurityOperations.IsAuthenticated)
{
MessageBox.Show("User failed to Login. Username and/or Password incorrect.");
return;
}
this.DialogResult = DialogResult.OK;
this.Close();
}
else
{
Global.WriteOutput("Couldn't create system connection parameters.");
}
}
private bool CheckQuery()
{
// Build select String
String strWhere = textBoxSearch.Text;
String strFrom = DBOp.BuildFromStatement(m_strNetworkID, m_strSimulationID, true);
if (strWhere.Trim() != "")
{
if (m_bSimulation)
{
String strCriteria = strWhere.Trim();
List <String> listAttribute = Global.ParseAttribute(strCriteria);
foreach (String str in listAttribute)
{
String strType = Global.GetAttributeType(str);
if (strType == "STRING")
{
String strOldValue = "[" + str + "]";
String strNewValue = "[@" + str + "]";
strCriteria = strCriteria.Replace(strOldValue, strNewValue);
}
}
m_evaluate = new CalculateEvaluate.CalculateEvaluate();
m_evaluate.BuildTemporaryClass(strCriteria, false);
try
{
CompilerResults m_crCriteria = m_evaluate.CompileAssembly();
if (m_evaluate.m_listError.Count > 0 || m_crCriteria == null)
{
Global.WriteOutput("Error: Compiling CRITERIA statement.");
return(false);
}
}
catch (Exception ex)
{
Global.WriteOutput("Error: Compiling CRITERIA statement." + ex.Message);
return(false);
}
}
}
if (strWhere.Trim() == "")
{
return(true);
}
if (m_bSimulation)
{
int nIndex = 0;
int nBeginIndex = 0;
int nEndIndex = 0;
while (nBeginIndex > -1)
{
nBeginIndex = strWhere.IndexOf("[", nIndex);
if (nBeginIndex < 0)
{
continue;
}
nEndIndex = strWhere.IndexOf("]", nBeginIndex);
if (nEndIndex > -1 && nBeginIndex > -1)
{
String strAttribute = strWhere.Substring(nBeginIndex + 1, nEndIndex - nBeginIndex - 1);
if (!m_hashAttributeYear.Contains(strAttribute.ToUpper()))
{
Global.WriteOutput("Attribute " + strAttribute + " not included in Network.");
return(false);
}
String str = "[" + strAttribute + "]";
strWhere = strWhere.Replace(str, strAttribute);
nBeginIndex = 0;
}
}
}
//oracle chokes on non-space whitespace
Regex whiteSpaceMechanic = new Regex(@"\s+");
strWhere = whiteSpaceMechanic.Replace(strWhere, " ");
String strSelect = "SELECT COUNT(*)" + strFrom;
strSelect += " WHERE ";
strSelect += strWhere;
DataSet ds;
try
{
ds = DBMgr.ExecuteQuery(strSelect);
}
catch (Exception exception)
{
Global.WriteOutput("Error: Check query with SQL message " + exception.Message);
return(false);
}
int nCount = 0;
int.TryParse(ds.Tables[0].Rows[0].ItemArray[0].ToString(), out nCount);
labelResults.Visible = true;
labelResults.Text = nCount.ToString() + " results match query.";
return(true);
}
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 DoAssetRollup()
{
// Loop through each asset and fill the ASSET_SECTION_<network_id> table according to the rollup logic.
AssetRollupMessaging.AddMessage("Begin asset rollup in network: " + m_networkID + " at " + DateTime.Now.ToString("HH:mm:ss"));
String query = "";
StreamWriter tw = null;
if (DBOp.IsTableInDatabase("ASSET_SECTION_" + m_networkID))
{
// Drop the table as we are going to make a new one.
try
{
DBMgr.ExecuteNonQuery("DROP TABLE ASSET_SECTION_" + m_networkID);
}
catch (Exception exc)
{
throw exc;
}
}
// Creating the ASSET_SECTION_<networkID> table.
AssetRollupMessaging.AddMessage("Creating ASSET_SECTION table...");
List <DatabaseManager.TableParameters> listColumn = new List <DatabaseManager.TableParameters>();
listColumn.Add(new DatabaseManager.TableParameters("GEO_ID", DataType.Int, false, false));
listColumn.Add(new DatabaseManager.TableParameters("SECTIONID", DataType.Int, false, false));
listColumn.Add(new DatabaseManager.TableParameters("ASSET_TYPE", DataType.VarChar(-1), false));
listColumn.Add(new DatabaseManager.TableParameters("FACILITY", DataType.VarChar(-1), false));
listColumn.Add(new DatabaseManager.TableParameters("BEGIN_STATION", DataType.Float, true));
listColumn.Add(new DatabaseManager.TableParameters("END_STATION", DataType.Float, true));
listColumn.Add(new DatabaseManager.TableParameters("DIRECTION", DataType.VarChar(50), true));
listColumn.Add(new DatabaseManager.TableParameters("SECTION", DataType.VarChar(-1), true));
listColumn.Add(new DatabaseManager.TableParameters("AREA", DataType.Float, true));
listColumn.Add(new DatabaseManager.TableParameters("UNITS", DataType.VarChar(50), true));
String strTable = "ASSET_SECTION_" + m_networkID;
try
{
DBMgr.CreateTable(strTable, listColumn);
}
catch (Exception exc)
{
throw exc;
}
// Get a text writer and file ready to do a bulk copy.
String strMyDocumentsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
strMyDocumentsFolder += "\\RoadCare Projects\\Temp";
Directory.CreateDirectory(strMyDocumentsFolder);
// LRS, Get the LRS data from each asset table...
foreach (String assetName in m_assetNames)
{
AssetRollupMessaging.AddMessage("Rolling up LRS based asset " + assetName + "...");
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(assetName);
List <String> assetColumnNames = DBMgr.GetTableColumns(assetName, cp);
if (assetColumnNames.Contains("BEGIN_STATION"))
{
switch (cp.Provider)
{
case "MSSQL":
query = "SELECT GEO_ID, FACILITY, DIRECTION, BEGIN_STATION, END_STATION FROM " + assetName + " WHERE (FACILITY <> '' AND FACILITY IS NOT NULL) ORDER BY FACILITY, DIRECTION, BEGIN_STATION";
break;
case "ORACLE":
query = "SELECT GEO_ID, FACILITY, DIRECTION, BEGIN_STATION, END_STATION FROM " + assetName + " WHERE (FACILITY LIKE '_%' AND FACILITY IS NOT NULL) ORDER BY FACILITY, DIRECTION, BEGIN_STATION";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
String strOutFile = strMyDocumentsFolder + "\\" + assetName + ".txt";
tw = new StreamWriter(strOutFile);
DataSet sectionSet;
DataReader assetReader;
try
{
// Get the Segmented network data from the SECTION_<networkID> table.
// sectionSet will hold the section data, and assetReader will loop through each asset.
sectionSet = DBMgr.ExecuteQuery("SELECT SECTIONID, FACILITY, BEGIN_STATION, END_STATION, DIRECTION FROM SECTION_" + m_networkID + " WHERE BEGIN_STATION IS NOT NULL ORDER BY FACILITY, DIRECTION, BEGIN_STATION");
assetReader = new DataReader(query, cp);
}
catch (Exception exc)
{
throw exc;
}
// If there is data to read, start reading it.
if (assetReader.Read())
{
DataPoint assetInfo;
DataRow sectionRow;
DataPoint sectionInfo;
bool bMoreData = true;
int iCurrentSection = 0;
// bMoreData is true while there is more data to read, and false when the dataReader is finished.
// we then go back to the foreach loop (outside the while) and start rolling up the next asset.
while (bMoreData)
{
// AssetInfo is going to hold this particular row of asset data.
assetInfo = new DataPoint(-1, (int)assetReader["GEO_ID"], assetReader["FACILITY"].ToString(), assetReader["BEGIN_STATION"].ToString(), assetReader["END_STATION"].ToString(), assetReader["DIRECTION"].ToString());
// SectionInfo is going to hold this particular row of sections data.
sectionRow = sectionSet.Tables[0].Rows[iCurrentSection];
sectionInfo = new DataPoint((int)sectionRow["SECTIONID"], sectionRow["FACILITY"].ToString(), sectionRow["BEGIN_STATION"].ToString(), sectionRow["END_STATION"].ToString(), sectionRow["DIRECTION"].ToString());
// We increment the section if
// We increment the asset if
// AssetInSection returns:
// -1 increments asset
// 0 adds asset to Asset Rollup Table
// 1 increments section
bool bIncrementSection = false;
bool bIncrementAsset = false;
int assetSectionComparison = AssetInSection(assetInfo, sectionInfo);
// Based on the result from AssetInSection we are going to increment something. Here its the asset
if (assetSectionComparison < 0)
{
bIncrementAsset = true;
}
// Here, we have a match and we need to look ahead to see how many sections a linear asset might belong to
// before moving on to the next asset. In either case, point or linear, we add the asset to the Rollup table.
else if (assetSectionComparison == 0)
{
AddAssetToRollupTable(assetInfo, sectionInfo, assetName, tw);
if (assetInfo.m_ptsExtent.Y != -1) //don't bother with looking ahead if we're using point assets
{
// Keep looping through the sections and checking to see if this asset is still valid for each
// consecutive section. When it fails on a section, we are done with the linear asset, otherwise
// we add the asset to the new section. (This is why we needed the sections in a DataSet, as a
// dataReader would not allow this type of operation...easily).
for (int iSectionLookAhead = 1; iSectionLookAhead + iCurrentSection < sectionSet.Tables[0].Rows.Count; iSectionLookAhead++)
{
sectionRow = sectionSet.Tables[0].Rows[iCurrentSection + iSectionLookAhead];
sectionInfo = new DataPoint((int)sectionRow["SECTIONID"], sectionRow["FACILITY"].ToString(), sectionRow["BEGIN_STATION"].ToString(), sectionRow["END_STATION"].ToString(), sectionRow["DIRECTION"].ToString());
if (AssetInSection(assetInfo, sectionInfo) == 0)
{
AddAssetToRollupTable(assetInfo, sectionInfo, assetName, tw);
}
else
{
break;
}
}
}
// Point asset match...we assigned the section already so just tell the loop to move to the next asset.
bIncrementAsset = true;
}
// AssetInSection returned non-zero, and was not negative. Which is a long way of saying, it returned positive.
// so we need to increment the section on a positive result.
else
{
bIncrementSection = true;
}
if (bIncrementAsset)
{
if (bIncrementSection)
{
// This can't happen logically, but was useful during debugging.
throw new Exception();
}
else
{
// Read in the new data if we are incrementing the asset
bMoreData = assetReader.Read();
}
}
else
{
// Increment the section row in the section data set. (Assuming there are sections remaining)
// If there arent any sections remaining, then we can't assign any more assets can we?
// so that means we are done.
if (bIncrementSection)
{
if (iCurrentSection + 1 < sectionSet.Tables[0].Rows.Count)
{
iCurrentSection++;
bMoreData = true;
}
else
{
bMoreData = false;
}
}
else
{
// Again, impossible, but useful for debugging.
throw new Exception();
}
}
}
}
tw.Close();
assetReader.Close();
AssetRollupMessaging.AddMessage("Bulk loading rolled up LRS asset data...");
// Now try to load all that beautifully segmented data into an Asset Rollup table. (tab delimited).
try
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
DBMgr.SQLBulkLoad("ASSET_SECTION_" + m_networkID, strOutFile, '\t');
break;
case "ORACLE":
throw new NotImplementedException("TODO: Figure out tables for DoAssetRollup()");
//DBMgr.OracleBulkLoad( DBMgr.NativeConnectionParameters, "ASSET_SECTION_" + m_networkID, strOutFile,
//break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
}
catch (Exception exc)
{
throw exc;
}
}
}
AssetRollupMessaging.AddMessage("Finished LRS asset data rollup...");
//foreach (String assetName in m_assetNames)
//{
// AssetRollupMessaging.AddMessge("Rolling up SRS asset " + assetName + "...");
// ConnectionParameters cp = DBMgr.GetAssetConnectionObject(assetName);
// List<String> assetColumnNames = DBMgr.GetTableColumns(assetName, cp);
// if (assetColumnNames.Contains("SECTION"))
// {
// query = "SELECT GEO_ID, FACILITY, SECTION FROM " + assetName + " WHERE (SECTION <> '' AND SECTION IS NOT NULL) ORDER BY FACILITY, SECTION";
// String strOutFile = strMyDocumentsFolder + "\\" + assetName + ".txt";
// tw = new StreamWriter(strOutFile);
// DataReader sectionReader = null;
// DataReader assetReader = null;
// try
// {
// // Get the Segmented network data from the SECTION_<networkID> table.
// // sectionSet will hold the section data, and assetReader will loop through each asset.
// //sectionSet = DBMgr.ExecuteQuery("SELECT SECTIONID, FACILITY, SECTION SECTION_" + m_networkID + " WHERE SECTION IS NOT NULL ORDER BY FACILITY, SECTION");
// sectionReader = new DataReader("SELECT SECTIONID, FACILITY, SECTION FROM SECTION_" + m_networkID + " WHERE SECTION IS NOT NULL ORDER BY FACILITY, SECTION");
// assetReader = new DataReader(query, cp);
// }
// catch (Exception exc)
// {
// throw exc;
// }
// bool bContinue = true;
// String strFacility = "";
// String strSection = "";
// String strSectionID = "";
// String strAssetFacility = "";
// String strAssetSection = "";
// String strGeoID = "";
// while (bContinue)
// {
// if (strFacility == "")
// {
// if (!sectionReader.Read())
// {
// bContinue = false;
// continue;
// }
// strFacility = sectionReader["FACILITY"].ToString();
// strSection = sectionReader["SECTION"].ToString();
// strSectionID = sectionReader["SECTIONID"].ToString();
// //if (strSectionID == "1006136")
// //{ }
// //strFacility = strFacility.Replace(" ", "");
// //strSection = strSection.Replace(" ", "");
// }
// if (strAssetFacility == "")
// {
// if (!assetReader.Read())
// {
// bContinue = false;
// continue;
// }
// strAssetFacility = assetReader["FACILITY"].ToString();
// strAssetSection = assetReader["SECTION"].ToString();
// strGeoID = assetReader["GEO_ID"].ToString();
// //if (strAssetFacility == "NW - Connecticut Ave")
// //{ }
// //strAssetFacility = strAssetFacility.Replace(" ", "");
// //strAssetSection = strAssetSection.Replace(" ", "");
// }
// if (CompareInfo.GetCompareInfo("en-US").Compare(strFacility, strAssetFacility) < 0)
// {
// strFacility = "";
// }
// else if (CompareInfo.GetCompareInfo("en-US").Compare(strFacility, strAssetFacility) == 0)
// {
// if (CompareInfo.GetCompareInfo("en-US").Compare(strSection, strAssetSection) < 0)
// {
// strFacility = "";
// }
// else if (CompareInfo.GetCompareInfo("en-US").Compare(strSection, strAssetSection) == 0)
// {
// //Write out to file
// tw.WriteLine(strGeoID
// + "\t" + strSectionID
// + "\t" + assetName
// + "\t" + sectionReader["FACILITY"].ToString()
// + "\t" //+ a.m_ptsExtent.X.ToString()
// + "\t" //+ ((a.m_ptsExtent.Y == -1) ? "" : a.m_ptsExtent.Y.ToString())
// + "\t" //+ a.m_strDirection
// + "\t" + sectionReader["SECTION"].ToString()
// + "\t" //+ a.m_strArea
// + "\t"); //+ a.m_strUnit);
// strAssetFacility = "";
// }
// else
// {
// strAssetFacility = "";
// }
// }
// else
// {
// strAssetFacility = "";
// }
// }
// tw.Close();
// assetReader.Close();
// sectionReader.Close();
// AssetRollupMessaging.AddMessge("Bulk loading rolled up SRS asset data...");
// // Now try to load all that beautifully segmented data into an Asset Rollup table. (tab delimited).
// try
// {
// switch (cp.Provider)
// {
// case "MSSQL":
// //query = "SELECT GEO_ID, FACILITY, SECTION FROM " + assetName + " WHERE (SECTION <> '' AND SECTION IS NOT NULL) ORDER BY FACILITY, SECTION";
// DBMgr.SQLBulkLoad("ASSET_SECTION_" + m_networkID, strOutFile, '\t');
// break;
// case "ORACLE":
// query = "SELECT GEO_ID, FACILITY, SECTION FROM " + assetName + " WHERE (SECTION LIKE '_%' AND SECTION IS NOT NULL) ORDER BY FACILITY, SECTION";
// break;
// default:
// throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
// break;
// }
// }
// catch (Exception exc)
// {
// throw exc;
// }
// }
//}
AssetRollupMessaging.AddMessage("Asset Rollup complete.");
}
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;
}
/// <summary>
/// This functions determines whether an asset is before, touching, after, or fully contained by a given section.
/// </summary>
/// <param name="asset">The asset data point to compare</param>
/// <param name="section">The section data point to compare</param>
/// <returns> -1 increments asset. 0 adds asset to RollupTable. 1 increments section.
/// </returns>
protected int AssetInSection(DataPoint asset, DataPoint section)
{
// Alias our asset and section Begin and End Mileposts.
int iReturnValue = 0;
float aBMP = asset.m_ptsExtent.X;
float aEMP = asset.m_ptsExtent.Y;
float sBMP = section.m_ptsExtent.X;
float sEMP = section.m_ptsExtent.Y;
// We need to use CompareInfo, because C# sorts its strings (by default) differently than SQL does.
// This is important as the ordering of our query results allows the rollup to work properly.
// So first we check to see if the asset route is less than the section route, if it is, then we need to increment
// the asset we are on, because this asset has missed the pervervial boat, and will not be assigned to a section.
if (CompareInfo.GetCompareInfo("en-US").Compare(asset.m_strRoutes, section.m_strRoutes, CompareOptions.StringSort) < 0)
{
iReturnValue = -1;
}
// Ok, if the routes match, we continue to check to see if this asset should be assigned to this section
// by checking the direction field. If the asset direction is less than the section direction, then again,
// this asset has missed the boat and will not be assigned a section. We know this because of the ordered
// results of our data.
else if (asset.m_strRoutes == section.m_strRoutes)
{
if (CompareInfo.GetCompareInfo("en-US").Compare(asset.m_strDirection, section.m_strDirection, CompareOptions.StringSort) < 0)
{
iReturnValue = -1;
}
// If the directions match, then we need to compare the asset mileposts with the section mileposts.
// There are several cases. First we determine what kind of asset we are dealing with,
// the choices will always be point, or linear, as the only part of a non-linear asset we care about
// will exist as a line along the section of road. If the asset end milepost is -1 then we have a point asset
// if it has a positive value (or 0) then we have a linear asset.
else if (asset.m_strDirection == section.m_strDirection)
{
if (aEMP != -1)
{
// Linear assets only. If the END milepost is less or equal to the BEGINING of the section milepost
// Then this asset has missed the boat and we need to go on to the next one.
if (aEMP <= sBMP)
{
iReturnValue = -1;
}
else
{
// If the asset END milepost is AFTER the section BEGIN milepost, and the asset BEGIN milepost
// is AFTER or ON the section END milepost, then we need to increment the section we are on.
// cause this asset will probably be in the next one.
if (aBMP >= sEMP)
{
iReturnValue = 1;
}
// Otherwise, we have a match, and this asset needs to be added to this section in the Rollup table.
else
{
iReturnValue = 0;
}
}
}
else
{
// Point assets only. We treat the point assets a bit differently when it comes to landing directly on
// the section mileposts. We always put a point asset which has its milepost value on the END milepost value
// of the section, in the NEXT section. However, this is not done, in the event that we are looking at the
// last section. (As there would be no more sections to assign the asset to, but it still falls in the network).
// The asset is before the section, so on to the next asset
if (aBMP < sBMP)
{
iReturnValue = -1;
}
else
{
// The asset is after the section BEGIN milepost, but BEFORE the section END milepost
// so add it to the asset Rollup table.
if (aBMP < sEMP)
{
iReturnValue = 0;
}
// Here is our fringe case, the asset falls on the END milepost of the section.
// so we check to see if this is the last section in the network, if it is,
// then we add the asset to the section, otherwise, we go on to the next section.
else if (aBMP == sEMP)
{
if (DBOp.IsAtEndOfNetwork(m_networkID, section.m_nSection.ToString()))
{
iReturnValue = 0;
}
else
{
iReturnValue = 1;
}
}
// Otherwise, the asset is after the section BEGIN milepost, but BEFORE the section END milepost
// so we need to increment the section
else
{
iReturnValue = 1;
}
}
}
}
// The asset direction and section direction do not match, so we should increment the section.
else
{
iReturnValue = 1;
}
}
// The asset Route and section Route are not equal so we increment the section
else
{
iReturnValue = 1;
}
return(iReturnValue);
}
private void buttonCalculate_Click(object sender, EventArgs e)
{
String str = comboBoxSummaryType.Text;
if (str == "")
{
return;
}
String strNetwork = comboBoxNetwork.Text;
if (strNetwork == "")
{
return;
}
String strNetworkID = hashNetworkNetworkID[strNetwork].ToString();
String strSimulation = comboBoxSimulation.Text;
String strSimulationID = "";
if (strSimulation != "")
{
strSimulationID = hashSimulationSimulationID[strSimulation].ToString();
}
String strAttribute = comboBoxAttribute.Text;
if (strAttribute == "")
{
return;
}
String strCriteria = textBoxCriteria.Text;
String strMethod = comboBoxMethod.Text;
if (strMethod == "")
{
return;
}
String strYear = textBoxYear.Text;
dgvLevelSummary.Rows.Clear();
dgvSolution.Rows.Clear();
String strValue;
switch (str)
{
case "Number Summary":
strValue = DBOp.GetConditionSummary(strNetworkID, strSimulationID, strAttribute, strYear, strMethod, strCriteria);
dgvSolution.Rows.Add(strAttribute, strValue);
break;
case "Area Summary":
List <String> listAttributes;
Hashtable hash = DBOp.GetPercentagePerStringAttribute(strNetworkID, strSimulationID, strAttribute, strYear, strMethod, strCriteria, false, out listAttributes);
foreach (String strValues in listAttributes)
{
dgvSolution.Rows.Add(strValues, hash[strValues].ToString());
}
//Get Levels
List <float> listLevel = GetLevels(strAttribute);
if (listLevel != null)
{
BuildLevelSummary(listLevel);
}
break;
}
}
public DeleteAttribute(String strAttributeToDelete)
{
m_strAttributeToDelete = strAttributeToDelete;
m_IsAttributeCalculated = DBOp.IsAttributeCalculated(m_strAttributeToDelete);
}
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;
}
