/// <summary>
/// Takes the SQL query from the text box and runs it against the data server,
/// the function then writes the data out to a file, and bulk inserts it into the database.
/// </summary>
public void Import()
{
// Get the SQL statement results and write them out to a file,
// then bulk load the file into the appropriate attribute table.
String strMyDocumentsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
strMyDocumentsFolder += "\\RoadCare Projects\\Temp";
Directory.CreateDirectory(strMyDocumentsFolder);
DateTime start = DateTime.Now;
String strOutFile = strMyDocumentsFolder + "\\AttributeImport.txt";
TextWriter tw = new StreamWriter(strOutFile);
String strQuery = m_strSQL;
try
{
DataSet ds = DBMgr.ExecuteQuery(strQuery, m_cp);
string[] strList;
if (m_bIsLinear)
{
strList = new string[6];
foreach (DataRow dataRow in ds.Tables[0].Rows)
{
strList[0] = dataRow["ROUTES"].ToString();
strList[1] = dataRow["BEGIN_STATION"].ToString();
strList[2] = dataRow["END_STATION"].ToString();
strList[3] = dataRow["DIRECTION"].ToString();
strList[4] = dataRow["DATE_"].ToString();
strList[5] = dataRow["DATA_"].ToString();
String strOut = SQLBulkLoadRawAttributeCheck(strList, m_bIsLinear, m_listRouteFacility, m_bIsString);
if (strOut != null)
{
tw.WriteLine(strOut);
}
}
}
else
{
strList = new string[5];
foreach (DataRow dataRow in ds.Tables[0].Rows)
{
strList[0] = dataRow["FACILITY"].ToString();
strList[1] = dataRow["SECTION"].ToString();
strList[2] = dataRow["SAMPLE_"].ToString();
strList[3] = dataRow["DATE_"].ToString();
strList[4] = dataRow["DATA_"].ToString();
String strOut = SQLBulkLoadRawAttributeCheck(strList, m_bIsLinear, m_listRouteFacility, m_bIsString);
if (strOut != null)
{
tw.WriteLine(strOut);
}
}
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: " + exc.Message);
}
tw.Close();
TimeSpan span = DateTime.Now - start;
start = DateTime.Now;
TimeSpan span2 = new TimeSpan();
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
DBMgr.SQLBulkLoad(m_strAttributeName, strOutFile, '\t');
break;
case "ORACLE":
//throw new NotImplementedException( "TODO: Figure out columns for Import()" );
List <string> columnNames = DBMgr.GetTableColumns(m_strAttributeName);
columnNames.Remove("ID_");
span2 = DateTime.Now - start;
start = DateTime.Now;
DBMgr.OracleBulkLoad(DBMgr.NativeConnectionParameters, m_strAttributeName, strOutFile, columnNames, "\\t");
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
TimeSpan span3 = DateTime.Now - start;
//MessageBox.Show("Span creating text file =" + span.ToString() + "\r\n Get columns =" + span2.ToString() + "\r\n Bulk loading =" + span3.ToString());
}
public void CreateDetailedResultsReport()
{
Report.XL.Visible = false;
Report.XL.UserControl = false;
Microsoft.Office.Interop.Excel._Workbook oWB = Report.CreateWorkBook();
Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
string strReportName = "Detailed Results Report";
Report.SheetPageSetup(oSheet, strReportName, 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1);
//oSheet.PageSetup.RightFooter = m_strNetwork + " - " + m_strSimulation;
//oSheet.PageSetup.LeftFooter = DateTime.Now.ToLongDateString();
//oSheet.PageSetup.CenterFooter = "Page &P";
//oSheet.PageSetup.FirstPageNumber = 1;
//oSheet.PageSetup.LeftMargin = 50d;
//oSheet.PageSetup.RightMargin = 20d;
//oSheet.Columns.Font.Size = 10;
//oSheet.Name = strReportName;
Range oR = oSheet.get_Range("A1", "A1");
object oEndCell = new object();
string strFilter = "";
int sheetRow;
int ndx;
DataSet dsPage = null, dsResults = null;
try
{
dsPage = DBOp.QueryPageHeader(strReportName);
dsResults = DBOp.QueryReport(m_strNetworkID, m_strSimulationID);
}
catch (Exception e)
{
throw e;
// Circular reference, address later
//Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message);
}
Cursor c = Cursor.Current;
Cursor.Current = new Cursor(Cursors.WaitCursor.Handle);
DataRow drPage = dsPage.Tables[0].Rows[0];
string strMajorTitle = drPage["phText"].ToString();
drPage = dsPage.Tables[0].Rows[2];
string strMinorTitle = drPage["phText"].ToString();
if (strMinorTitle.IndexOf("@1") > 0)
{
strMinorTitle = strMinorTitle.Replace("@1", m_strNetwork + " (" + m_strNetworkID + ")");
}
if (strMinorTitle.IndexOf("@2") > 0)
{
strMinorTitle = strMinorTitle.Replace("@2", m_strSimulation + " (" + m_strSimulationID + ")");
}
#region default column widths
// set some column widths
oR = oSheet.get_Range("A1:B1", Missing.Value);
oR.ColumnWidth = 15;
#endregion
#region place agency graphic
//string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet);
#endregion
#region write Major Title
int aryCols = 2;
int aryRows = 3;
object[,] oData = new object[aryRows, aryCols];
Report.ClearDataArray(ref oData);
oData[0, 1] = strMajorTitle;
oData[2, 1] = strMinorTitle;
sheetRow = 4;
oEndCell = "A1";
oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
#endregion
#region get Budget Years
strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + "ORDER BY Year_";
DataSet dsBudgetYears = DBMgr.ExecuteQuery(strFilter);
int numYears = dsBudgetYears.Tables[0].Rows.Count;
#endregion
aryCols = numYears + 2;
//aryRows = dsResults.Tables[0].Rows.Count / 5 + 2;
aryRows = dsResults.Tables[0].Rows.Count / numYears + 2;
#region build Column Headers array
Report.Resize2DArray(ref oData, aryRows + 1, aryCols);
Report.ClearDataArray(ref oData);
object[] oColumnHeader = new object[aryCols];
oColumnHeader[0] = "Facility";
oColumnHeader[1] = "Section";
ndx = 2;
foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
{
oColumnHeader[ndx++] = dr["Year_"].ToString();
}
#endregion
#region build Results table
ndx = 0;
oData[ndx, 0] = "Treatment Distribution per Section"; // Group Header
sheetRow = 5;
oEndCell = "A" + sheetRow.ToString();
sheetRow++;
ndx++;
for (int i = 0; i < aryCols; i++)
{
oData[ndx, i] = oColumnHeader[i]; // Column Header
}
sheetRow++;
ndx++;
int rowsToColumns = 0, nCol = 2;
string strGreen = "", strBlue = "", strRed = "";
List <string> greenList = new List <string>();
List <string> blueList = new List <string>();
List <string> redList = new List <string>();
foreach (DataRow dr in dsResults.Tables[0].Rows)
{
string sYear = dr["Years"].ToString();
if (rowsToColumns < numYears)
{
if (rowsToColumns == 0)
{
nCol = 2;
if (ndx == 132)
{
string testString = dr["Facility"].ToString();
}
oData[ndx, 0] = dr["Facility"].ToString();
oData[ndx, 1] = dr["Section"].ToString();
}
}
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
// Test for Treatment selected per RoadCare logic (GREEN)
if (dr["Treatment"].ToString() != "No Treatment" && dr["Iscommitted"].ToString().ToUpper() != "TRUE" && dr["Numbertreatment"].ToString() != "0")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strGreen, ref greenList, sheetRow, asciiChar, 0);
oData[ndx, nCol] = dr["Treatment"].ToString();
}
// Test for Feasible treatment not funded/selected (BLUE)
else if (dr["Treatment"].ToString() == "No Treatment" && dr["Iscommitted"].ToString().ToUpper() != "TRUE" && dr["Numbertreatment"].ToString() != "0")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strBlue, ref blueList, sheetRow, asciiChar, 0);
}
// Test for Treatment selected as Committed Project (RED)
else if (dr["Iscommitted"].ToString().ToUpper() == "TRUE")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strRed, ref redList, sheetRow, asciiChar, 0);
oData[ndx, nCol] = dr["Treatment"].ToString();
}
break;
case "ORACLE":
// Test for Treatment selected per RoadCare logic (GREEN)
if (dr["Treatment"].ToString() != "No Treatment" && dr["Iscommitted"].ToString() != "1" && dr["Numbertreatment"].ToString() != "0")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strGreen, ref greenList, sheetRow, asciiChar, 0);
oData[ndx, nCol] = dr["Treatment"].ToString();
}
// Test for Feasible treatment not funded/selected (BLUE)
else if (dr["Treatment"].ToString() == "No Treatment" && dr["Iscommitted"].ToString() != "1" && dr["Numbertreatment"].ToString() != "0")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strBlue, ref blueList, sheetRow, asciiChar, 0);
}
// Test for Treatment selected as Committed Project (RED)
else if (dr["Iscommitted"].ToString() == "1")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strRed, ref redList, sheetRow, asciiChar, 0);
oData[ndx, nCol] = dr["Treatment"].ToString();
}
break;
default:
throw new NotImplementedException("TODO: develop ANSI version of CreateDetailedResultsReport()");
//break;
}
nCol++;
if (rowsToColumns + 1 >= numYears)
{
rowsToColumns = 0;
ndx++;
sheetRow++;
}
else
{
rowsToColumns++;
}
}
oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true);
#endregion
#region format pageheader
// PAGEHEADER
string strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1";
DataRow drPgHdr = dsPage.Tables[0].Rows[0];
Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
strRange = "B2:" + Report.GetColumnLetter(aryCols) + "2";
drPgHdr = dsPage.Tables[0].Rows[1];
Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
strRange = "B3:" + Report.GetColumnLetter(aryCols) + "3";
drPgHdr = dsPage.Tables[0].Rows[2];
Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
// Place a color legend at top and bottom of report
PlaceColorLegend(oSheet, aryCols, 3);
PlaceColorLegend(oSheet, 1, sheetRow + 1);
#endregion
#region format groupheader
strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5";
Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange);
#endregion
#region format columnHeader
strRange = "A6:" + Report.GetColumnLetter(aryCols) + "6";
Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
#endregion
#region apply color
Report.EndRangeList(ref strGreen, ref greenList);
Report.EndRangeList(ref strBlue, ref blueList);
Report.EndRangeList(ref strRed, ref redList);
foreach (string s in greenList)
{
oR = oSheet.get_Range(s, Missing.Value);
oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightGreen);
}
foreach (string s in blueList)
{
oR = oSheet.get_Range(s, Missing.Value);
oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue);
}
foreach (string s in redList)
{
oR = oSheet.get_Range(s, Missing.Value);
oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightSalmon);
}
#endregion
#region default column widths
// set some column widths
oR = oSheet.get_Range("A1:B1", Missing.Value);
oR.ColumnWidth = 15;
strRange = "C1:" + Report.GetColumnLetter(aryCols) + "1";
oR = oSheet.get_Range(strRange, Missing.Value);
oR.ColumnWidth = 12;
#endregion
#region make bold
strRange = "A7:B" + (sheetRow - 1).ToString();
oR = oSheet.get_Range(strRange, Missing.Value);
oR.Font.Bold = true;
#endregion
Report.XL.Visible = true;
Report.XL.UserControl = true;
}
public static void PasteInvestment(String strNewSimulationID)
{
if (String.IsNullOrEmpty(m_strCopyInvestmentSimulationID))
{
return;
}
List <string> listInserts = new List <string>();
//Delete existing SIMULATIONID FROM
var deleteInvestments = "DELETE FROM INVESTMENTS WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(deleteInvestments);
var deleteYearlyInvestment = "DELETE FROM YEARLYINVESTMENT WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(deleteYearlyInvestment);
var delete = "DELETE FROM BUDGET_CRITERIA WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(delete);
var deleteSplitTreatment = "DELETE FROM SPLIT_TREATMENT WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(deleteSplitTreatment);
String strSelect = "SELECT * FROM INVESTMENTS WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
String strSimulationID;
String strFirstYear;
String strNumberYear;
String strInflation;
String strDiscount;
String strBudgetOrder;
String strDescription = "";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
strSimulationID = dr["SIMULATIONID"].ToString();
strFirstYear = dr["FIRSTYEAR"].ToString();
strNumberYear = dr["NUMBERYEARS"].ToString();
strInflation = dr["INFLATIONRATE"].ToString();
strDiscount = dr["DISCOUNTRATE"].ToString();
strBudgetOrder = dr["BUDGETORDER"].ToString();
if (dr["DESCRIPTION"] != DBNull.Value)
{
strDescription = dr["DESCRIPTION"].ToString();
}
String strInsert = "INSERT INTO INVESTMENTS (SIMULATIONID,FIRSTYEAR,NUMBERYEARS,INFLATIONRATE,DISCOUNTRATE,BUDGETORDER,DESCRIPTION) VALUES ('" + strNewSimulationID + "','" + strFirstYear + "','" + strNumberYear + "','" + strInflation + "','" + strDiscount + "','" + strBudgetOrder + "','" + strDescription + "')";
listInserts.Add(strInsert);
}
else
{
return;
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing Investment Information." + exception.Message);
return;
}
strSelect = "SELECT * FROM YEARLYINVESTMENT WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strYear = dr["YEAR_"].ToString();
String strBudget = dr["BUDGETNAME"].ToString();
String strAmount = dr["AMOUNT"].ToString();
String strInsert = "INSERT INTO YEARLYINVESTMENT (SIMULATIONID,YEAR_,BUDGETNAME,AMOUNT) VALUES (" + strNewSimulationID + "," + strYear + ",'" + strBudget + "'," + strAmount + ")";
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing YearlyInvestment Information." + exception.Message);
return;
}
var selectBudgetCriteria = "SELECT * FROM BUDGET_CRITERIA WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(selectBudgetCriteria);
foreach (DataRow dr in ds.Tables[0].Rows)
{
var budgetName = dr["BUDGET_NAME"].ToString();
var criteria = dr["CRITERIA"].ToString();
String strInsert = "INSERT INTO BUDGET_CRITERIA (SIMULATIONID,BUDGET_NAME,CRITERIA) VALUES ('" + strNewSimulationID + "','" + budgetName + "','" + criteria + "')";
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing YearlyInvestment Information." + exception.Message);
return;
}
try
{
DBMgr.ExecuteBatchNonQuery(listInserts);
Global.WriteOutput("Investment and Budget Criteria successfully copied.");
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying Investments from one simulation to another" + exception.Message);
}
var selectSplitTreatment = "SELECT * FROM SPLIT_TREATMENT WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(selectSplitTreatment);
foreach (DataRow dr in ds.Tables[0].Rows)
{
var description = dr["DESCRIPTION"].ToString();
var criteria = dr["CRITERIA"].ToString();
var splitTreatmentIdToCopy = dr["SPLIT_TREATMENT_ID"].ToString();
String strInsert = "INSERT INTO SPLIT_TREATMENT(SIMULATIONID,DESCRIPTION,CRITERIA) VALUES ('" + strNewSimulationID + "','" + description + "','" + criteria + "')";
DBMgr.ExecuteNonQuery(strInsert);
//Get the identity
string selectIdentity = "SELECT IDENT_CURRENT ('SPLIT_TREATMENT') FROM SPLIT_TREATMENT";
var splitTreatmentIdToPaste = DBMgr.ExecuteScalar(selectIdentity).ToString();
var splitTreatmentLimit = "SELECT * FROM SPLIT_TREATMENT_LIMIT WHERE SPLIT_TREATMENT_ID=" + splitTreatmentIdToCopy;
DataSet dataSetLimit = DBMgr.ExecuteQuery(splitTreatmentLimit);
foreach (DataRow limit in dataSetLimit.Tables[0].Rows)
{
int rank = -1;
float amount = -1;
string percentage = "";
if (limit["RANK"] != DBNull.Value)
{
rank = Convert.ToInt32(limit["RANK"]);
}
if (limit["AMOUNT"] != DBNull.Value)
{
amount = Convert.ToSingle(limit["AMOUNT"]);
}
if (limit["PERCENTAGE"] != DBNull.Value)
{
percentage = limit["PERCENTAGE"].ToString();
}
String limitInsert = "INSERT INTO SPLIT_TREATMENT_LIMIT(SPLIT_TREATMENT_ID,RANK,PERCENTAGE) VALUES ('" + splitTreatmentIdToPaste + "','" + rank + "','" + percentage + "')";
if (amount > 0)
{
limitInsert = "INSERT INTO SPLIT_TREATMENT_LIMIT(SPLIT_TREATMENT_ID,RANK,PERCENTAGE,AMOUNT) VALUES ('" + splitTreatmentIdToPaste + "','" + rank + "','" + percentage + "','" + amount + "')";
}
DBMgr.ExecuteNonQuery(limitInsert);
}
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing split treatment information." + exception.Message);
return;
}
}
/// <summary>
/// Loads existing data for this SECTION and YEAR
/// </summary>
private void FillAttributeValueHash()
{
// Fill list of variables available in Simulation. May not add new for single section run.
m_listAttributeSimulation = new List <String>();
String strSelect = "SELECT SIMULATION_VARIABLES FROM SIMULATIONS WHERE SIMULATIONID='" + SimulationID + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
string[] attributes = ds.Tables[0].Rows[0].ItemArray[0].ToString().Split(new string[] { "\t" }, StringSplitOptions.None);
for (int i = 0; i < attributes.Length; i++)
{
m_listAttributeSimulation.Add(attributes[i]);
}
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Loading and parsing SIMULATION variables." + exception.Message;
}
//Fill list of current values starting with LENGTH and AREA for COST calculations
m_hashAttributeValue = new Hashtable();
//Get Length and Area for COST calculations
String strArea = "0";
double dLength = 0;
strSelect = "SELECT END_STATION,BEGIN_STATION,AREA FROM SECTION_" + NetworkID + " WHERE SECTIONID='" + SectionID + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
if (!String.IsNullOrEmpty(dr["END_STATION"].ToString()) || !String.IsNullOrEmpty(dr["BEGIN_STATION"].ToString()))
{
dLength = (double)dr["END_STATION"] - (double)dr["BEGIN_STATION"];
m_hashAttributeValue.Add("LENGTH", dLength);
}
strArea = dr["AREA"].ToString();
double dArea = Convert.ToDouble(strArea);
m_hashAttributeValue.Add("AREA", dArea);
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Loading AREA information for cost calculation. " + exception.Message;
}
//Get values for each simulation value
strSelect = "SELECT ";
foreach (String str in m_listAttributeSimulation)
{
if (strSelect.Length != 7)
{
strSelect += ",";//Does not add comma first pass
}
String strAttributeYear = str + "_" + this.Year;
strSelect += strAttributeYear;
}
String strWhere = " FROM " + this.SimulationTable + " WHERE SECTIONID ='" + this.SectionID + "'";
strSelect += strWhere;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
foreach (String attribute in m_listAttributeSimulation)
{
m_hashAttributeValue.Add(attribute, dr[attribute + "_" + this.Year]);
}
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Loading and parsing SIMULATION RESULTS table." + exception.Message;
}
//Load current treatment
strSelect = "SELECT TREATMENT,COST_,BUDGET,YEARSANY,YEARSSAME,CHANGEHASH,AREA FROM " + this.ReportTable + " WHERE YEARS='" + this.Year + "' AND SECTIONID='" + this.SectionID + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
m_strCurrentTreatment = dr["TREATMENT"].ToString();
double dCost = double.Parse(dr["COST_"].ToString()); //inflation already factored in in the report table
double dArea = double.Parse(dr["AREA"].ToString());
//dCost *= dArea;
m_strCurrentCost = dCost.ToString("c");
m_strCurrentBudget = dr["BUDGET"].ToString();
m_strCurrentAny = dr["YEARSANY"].ToString();
m_strCurrentSame = dr["YEARSSAME"].ToString();
String strHashChange = dr["CHANGEHASH"].ToString();
m_hashCurrentChange = new Hashtable();
string[] pairs = strHashChange.Split(new string[] { "\n" }, StringSplitOptions.None);
for (int i = 0; i < pairs.Length; i++)
{
if (pairs[i].Contains("\t"))
{
string[] attributechange = pairs[i].Split(new string[] { "\t" }, StringSplitOptions.None);
m_hashCurrentChange.Add(attributechange[0], attributechange[1]);
}
}
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Loading and parsing REPORT table." + exception.Message;
}
}
/// <summary>
/// Updates summary when necessary
/// </summary>
private void UpdateSummaryValues()
{
if (!m_bChange)
{
return;
}
labelError.Visible = false;
String strTreatment = dgvSummary[1, 1].Value.ToString();
if (strTreatment == m_strCurrentTreatment)
{
dgvSummary[1, 2].Value = m_strCurrentBudget;
dgvSummary[1, 3].Value = m_strCurrentCost;
dgvSummary[1, 4].Value = m_strCurrentAny;
dgvSummary[1, 5].Value = m_strCurrentSame;
dgvAttribute.Rows.Clear();
foreach (String key in m_hashCurrentChange.Keys)
{
int nRow = dgvAttribute.Rows.Add(key, m_hashCurrentChange[key]);
}
}
else if (m_listFeasibleTreatment.Contains(strTreatment))
{
double dArea = 0;
String strSelect = "SELECT AREA FROM " + this.ReportTable + " WHERE SECTIONID ='" + this.SectionID + "' AND YEARS='" + this.Year + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
dArea = double.Parse(dr["AREA"].ToString());
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error retrieving AREA." + exception.Message;
}
strSelect = "SELECT BUDGET,YEARSANY,YEARSSAME,COST_,CHANGEHASH FROM " + this.BenefitCostTable + " WHERE SECTIONID ='" + this.SectionID + "' AND YEARS='" + this.Year + "' AND TREATMENT='" + strTreatment + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
double dCost = double.Parse(dr["COST_"].ToString()) * dArea * inflationRate;
string budget = dr["BUDGET"].ToString();
if (!budget.Contains("|"))
{
dgvSummary[1, 2].Value = budget;
}
dgvSummary[1, 3].Value = Math.Round(dCost, 2).ToString("c");
dgvSummary[1, 4].Value = dr["YEARSANY"].ToString();
dgvSummary[1, 5].Value = dr["YEARSSAME"].ToString();
String strChangeHash = dr["CHANGEHASH"].ToString();
dgvAttribute.Rows.Clear();
string[] pairs = strChangeHash.Split(new string[] { "\n" }, StringSplitOptions.None);
for (int i = 0; i < pairs.Length; i++)
{
if (pairs[i].Contains("\t"))
{
string[] attributechange = pairs[i].Split(new string[] { "\t" }, StringSplitOptions.None);
dgvAttribute.Rows.Add(attributechange[0], attributechange[1]);
}
}
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Retrieving default information from the BENEFIT_COST table." + exception.Message;
}
}
else if (m_listTreatments.Contains(strTreatment))
{
//Fill with default from Treatment table
String strSelect = "SELECT BUDGET, BEFOREANY, BEFORESAME, TREATMENTID FROM TREATMENTS WHERE SIMULATIONID ='" + this.SimulationID + "' AND TREATMENT='" + strTreatment + "'";
String strTreatmentID = "";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
dgvSummary[1, 2].Value = dr["BUDGET"].ToString();
dgvSummary[1, 4].Value = dr["BEFOREANY"].ToString();
dgvSummary[1, 5].Value = dr["BEFORESAME"].ToString();
strTreatmentID = dr["TREATMENTID"].ToString();
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Retrieving default information from the TREATMENTS table." + exception.Message;
}
//Get Cost information
String strCost = "0";
//strSelect = "SELECT COST_ FROM COSTS WHERE TREATMENTID='" + strTreatmentID + "' AND CRITERIA=''";
//inserting '' in oracle inserts a null
// TODO: Why not pull the criteria for each cost equation found, and calculate the cost for each and show them in a cost dropdown.
strSelect = "SELECT COST_,CRITERIA FROM COSTS WHERE TREATMENTID='" + strTreatmentID + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
var cost = dr["COST_"];
var criteria = dr["CRITERIA"];
if (criteria == DBNull.Value)
{
strCost = dr["COST_"].ToString();
}
else
{
var criteriaString = criteria.ToString();
if (String.IsNullOrWhiteSpace(criteriaString))
{
strCost = dr["COST_"].ToString();
}
else
{
var criteriaToEvaluate = new Criterias();
criteriaToEvaluate.Criteria = criteriaString;
if (criteriaToEvaluate.IsCriteriaMet(m_hashAttributeValue))
{
strCost = dr["COST_"].ToString();
}
}
}
strCost = dr["COST_"].ToString();
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Retrieving default COST information from the COSTS table." + exception.Message;
}
List <String> listError;
List <String> listAttributesEquation = Global.TryParseAttribute(strCost, out listError); // See if listAttributeEquations is included in dgvDefault
CalculateEvaluate.CalculateEvaluate calculate = new CalculateEvaluate.CalculateEvaluate();
calculate.BuildTemporaryClass(strCost, true);
CompilerResults m_crEquation = calculate.CompileAssembly();
object[] input = new object[listAttributesEquation.Count];
int i = 0;
foreach (String attribute in listAttributesEquation)
{
input[i] = m_hashAttributeValue[attribute];
i++;
}
try
{
object result = calculate.RunMethod(input);
dgvSummary[1, 3].Value = result.ToString();
}
catch (Exception exc)
{
Global.WriteOutput("Error running single section. " + exc.Message);
}
//strSelect = "SELECT ATTRIBUTE_,CHANGE_ FROM CONSEQUENCES WHERE TREATMENTID='" + strTreatmentID + "' AND CRITERIA=''";
//inserting '' in oracle inserts a null
strSelect = "SELECT ATTRIBUTE_,CHANGE_, CRITERIA FROM CONSEQUENCES WHERE TREATMENTID='" + strTreatmentID + "'";
dgvAttribute.Rows.Clear();
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
var attribute = dr["ATTRIBUTE_"];
var change = dr["CHANGE_"];
var criteria = dr["CRITERIA"];
if (criteria == DBNull.Value)
{
dgvAttribute.Rows.Add(dr["ATTRIBUTE_"].ToString(), dr["CHANGE_"].ToString());
}
else
{
var criteriaString = criteria.ToString();
if (String.IsNullOrWhiteSpace(criteriaString))
{
dgvAttribute.Rows.Add(dr["ATTRIBUTE_"].ToString(), dr["CHANGE_"].ToString());
}
else
{
var criteriaToEvaluate = new Criterias();
criteriaToEvaluate.Criteria = criteriaString;
if (criteriaToEvaluate.IsCriteriaMet(m_hashAttributeValue))
{
dgvAttribute.Rows.Add(dr["ATTRIBUTE_"].ToString(), dr["CHANGE_"].ToString());
}
}
}
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Loading Consequences for selected TREATMENT. " + exception.Message;
}
}
else
{
//User defined treatment. Leave existing as is.
}
dgvSummary.Update();
dgvAttribute.Update();
}
/// <summary>
/// Loads all feasibility criteria for this treatment in for this treatment ID.
/// </summary>
public bool LoadFeasibility()
{
String strSelect = "SELECT CRITERIA,BINARY_CRITERIA,FEASIBILITYID FROM " + cgOMS.Prefix + "FEASIBILITY WHERE TREATMENTID='" + this.TreatmentID + "'";
_table = cgOMS.Prefix + "FEASIBILITY";
_column = "BINARY_CRITERIA";
DataSet ds;
try
{
ds = DBMgr.ExecuteQuery(strSelect);
}
catch (Exception exception)
{
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Opening Treatment FEASIBILITY table. SQL Message - " + exception.Message));
return(false);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
_id = row["FEASIBILITYID"].ToString();
Criterias criteria = new Criterias(_table, _column, _id);
byte[] assemblyCriteria = null;
string currentCriteria = "";
if (row["CRITERIA"] != DBNull.Value)
{
currentCriteria = Simulation.ConvertOMSAttribute(row["CRITERIA"].ToString());
}
assemblyCriteria = SimulationMessaging.GetSerializedCalculateEvaluate(_table, _column, _id, assemblyCriteria);
if (assemblyCriteria != null && assemblyCriteria.Length > 0)
{
criteria.Evaluate = (CalculateEvaluate.CalculateEvaluate)AssemblySerialize.DeSerializeObjectFromByteArray(assemblyCriteria);
if (criteria.Evaluate.OriginalInput != currentCriteria)
{
criteria.Evaluate = null;
}
}
if (criteria.Evaluate != null && criteria.Evaluate.m_cr != null)
{
if (!File.Exists(criteria.Evaluate.m_cr.PathToAssembly))
{
criteria.Evaluate = null;
}
}
criteria.Criteria = Simulation.ConvertOMSAttribute(row["CRITERIA"].ToString());
foreach (String str in criteria.Errors)
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: Treatment feasibility criteria for Treatment " + _treatment + ":" + str));
}
this.CriteriaList.Add(criteria);
foreach (String str in criteria.CriteriaAttributes)
{
if (!SimulationMessaging.IsAttribute(str))
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: " + str + " which is used by the Feasibility criteria is not present in the database."));
}
if (!_attributes.Contains(str))
{
_attributes.Add(str);
}
}
}
return(true);
}
/// <summary>
/// Load all consequence information associated with treat.
/// </summary>
public bool LoadConsequences()
{
Consequences consequence;
String select = "SELECT ATTRIBUTE_,CHANGE_,CRITERIA,EQUATION,ISFUNCTION, CONSEQUENCEID FROM " + cgOMS.Prefix + "CONSEQUENCES WHERE TREATMENTID='" + _treatmentID + "'";
if (SimulationMessaging.IsOMS)
{
select = "SELECT ATTRIBUTE_,CHANGE_,CRITERIA,EQUATION,NULL AS ISFUNCTION, CONSEQUENCEID FROM " + cgOMS.Prefix + "CONSEQUENCES WHERE TREATMENTID='" + _treatmentID + "'";
}
DataSet ds;
try
{
ds = DBMgr.ExecuteQuery(select);
}
catch (Exception exception)
{
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Opening CONSEQUENCES table. SQL Message - " + exception.Message));
return(false);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
string id = row["CONSEQUENCEID"].ToString();
consequence = new Consequences(id);
String strAttribute = row["ATTRIBUTE_"].ToString();
String strChange = row["CHANGE_"].ToString();
String strCriteria = row["CRITERIA"].ToString();
String strEquation = row["EQUATION"].ToString();
consequence.TreatmentID = this.TreatmentID;
consequence.Treatment = this.Treatment;
if (strCriteria.Trim().Length == 0)
{
consequence.Default = true;
}
else
{
consequence.Default = false;
byte[] assemblyCriteria = null;
assemblyCriteria = SimulationMessaging.GetSerializedCalculateEvaluate(cgOMS.Prefix + "CONSEQUENCES", "BINARY_CRITERIA", id, assemblyCriteria);
if (assemblyCriteria != null && assemblyCriteria.Length > 0)
{
consequence.Criteria.Evaluate = (CalculateEvaluate.CalculateEvaluate)AssemblySerialize.DeSerializeObjectFromByteArray(assemblyCriteria);
if (consequence.Criteria.Evaluate.OriginalInput != strCriteria)
{
consequence.Criteria.Evaluate = null;
}
}
consequence.Criteria.Criteria = strCriteria;
//Get attributes from consequence criteria
foreach (String str in consequence.Criteria.CriteriaAttributes)
{
if (!SimulationMessaging.IsAttribute(str))
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: " + str + " which is used by the Consequence criteria is not present in the database."));
}
if (!_attributes.Contains(str))
{
_attributes.Add(str);
}
}
}
if (string.IsNullOrWhiteSpace(strEquation.Trim()))
{
consequence.IsEquation = false;;
}
else
{
byte[] assembly = SimulationMessaging.GetSerializedCalculateEvaluate(cgOMS.Prefix + "CONSEQUENCES", "BINARY_EQUATION", id, null);
if (assembly != null && assembly.Length > 0)
{
consequence._calculate = (CalculateEvaluate.CalculateEvaluate)AssemblySerialize.DeSerializeObjectFromByteArray(assembly);
if (consequence._calculate.OriginalInput != strEquation)
{
consequence._calculate = null;
}
}
bool isFunction = false;
if (row["ISFUNCTION"] != DBNull.Value)
{
isFunction = Convert.ToBoolean(row["ISFUNCTION"]);
}
if (isFunction)
{
consequence.SetFunction(strEquation);
}
else
{
consequence.Equation = strEquation;
}
//Get attributes from consequence criteria
foreach (string attribute in consequence._attributesEquation)
{
if (!SimulationMessaging.IsAttribute(attribute))
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: " + attribute + " which is used by the Consequence criteria is not present in the database."));
}
if (!_attributes.Contains(attribute))
{
_attributes.Add(attribute);
}
}
}
consequence.LoadAttributeChange(strAttribute, strChange);
ConsequenceList.Add(consequence);
// Get attributes from Attribute change
foreach (String str in consequence.Attributes)
{
if (!_attributes.Contains(str))
{
_attributes.Add(str);
}
}
}
return(true);
}
private void buttonCreate_Click(object sender, EventArgs e)
{
m_strNewNetworkName = textBoxNetworkName.Text;
String strWhere = textBoxFilter.Text;
this.Cursor = Cursors.WaitCursor;
if (m_strNewNetworkName.Trim() == "")
{
Global.WriteOutput("Error: Network Name must be entered..");
this.Cursor = Cursors.Default;
return;
}
//Check if Network Name is already in use.
String strSelect = "SELECT * FROM NETWORKS WHERE NETWORK_NAME='" + m_strNewNetworkName + "'";
String strDescription = "";
String strDesignerUserID = "";
String strDesignerUserName = "";
//String strLock = "";
//String strPrivate = "";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count > 0)
{
//Network with this name exists.
Global.WriteOutput("Error: Network with this name already exists. Please select another.");
this.Cursor = Cursors.Default;
return;
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Checking Network Name uniqueness. Please select a different Network Name." + exception.Message);
this.Cursor = Cursors.Default;
return;
}
strSelect = "SELECT * FROM NETWORKS WHERE NETWORKID=" + m_strNetworkID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
strDescription = dr["DESCRIPTION"].ToString();
strDesignerUserID = dr["DESIGNER_USERID"].ToString();
strDesignerUserName = dr["DESIGNER_NAME"].ToString();
//strLock = dr["LOCK_"].ToString();
//strPrivate = dr["PRIVATE_"].ToString();
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Checking Network Name uniqueness. Please select a different Network Name." + exception.Message);
this.Cursor = Cursors.Default;
return;
}
String strInsert = "INSERT INTO NETWORKS (NETWORK_NAME";
String strValues = " VALUES ('" + m_strNewNetworkName + "'";
if (strDescription != "")
{
strInsert += ",DESCRIPTION";
strValues += ",'" + strDescription + "'";
}
if (strDesignerUserID != "")
{
strInsert += ",DESIGNER_USERID";
strValues += ",'" + strDesignerUserID + "'";
}
if (strDesignerUserName != "")
{
strInsert += ",DESIGNER_NAME";
strValues += ",'" + strDesignerUserName + "'";
}
//if (strLock != "")
//{
// strInsert += ",LOCK_";
// strValues += ",'" + strLock + "'";
//}
//if (strPrivate != "")
//{
// strInsert += ",PRIVATE_";
// strValues += ",'" + strPrivate + "'";
//}
strInsert += ",DATE_CREATED";
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strValues += ",'" + DateTime.Now.ToString() + "'";
break;
case "ORACLE":
strValues += ",to_date('" + DateTime.Now.ToString("MM/dd/yyyy") + "','MM/DD/YYYY')";
break;
default:
throw new NotImplementedException("TODO: Implement ANSI version of buttonCreate_Click()");
}
strInsert += ",DATE_LAST_EDIT)";
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strValues += ",'" + DateTime.Now.ToString() + "')";
break;
case "ORACLE":
strValues += ",to_date('" + DateTime.Now.ToString("MM/dd/yyyy") + "','MM/DD/YYYY'))";
break;
default:
throw new NotImplementedException("TODO: Implement ANSI version of buttonCreate_Click()");
}
strInsert += strValues;
try
{
DBMgr.ExecuteNonQuery(strInsert);
}
catch (Exception exception)
{
Global.WriteOutput("Error: Creating new subnetwork NETWORKS entry" + exception.Message);
this.Cursor = Cursors.Default;
return;
}
String strIdentity = "";
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strIdentity = "SELECT IDENT_CURRENT ('NETWORKS') FROM NETWORKS";
break;
case "ORACLE":
//strIdentity = "SELECT NETWORKS_NETWORKID_SEQ.CURRVAL FROM DUAL";
//strIdentity = "SELECT LAST_NUMBER - CACHE_SIZE FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'NETWORKS_NETWORKID_SEQ'";
strIdentity = "SELECT MAX(NETWORKID) FROM NETWORKS";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
try
{
DataSet ds = DBMgr.ExecuteQuery(strIdentity);
strIdentity = ds.Tables[0].Rows[0].ItemArray[0].ToString();
}
catch (Exception exception)
{
Global.WriteOutput("Error: Creating new subnetwork NETWORKS entry." + exception.Message);
this.Cursor = Cursors.Default;
return;
}
//Now have the new NetworkInserted. Must create tables. SECTION_NID and SEGMENT_NID_NS0
String strSelectSection = "SELECT SECTION_" + m_strNetworkID + ".SECTIONID,FACILITY,BEGIN_STATION,END_STATION,DIRECTION,SECTION,AREA,UNITS,GEOMETRY,Envelope_MinX,Envelope_MaxX,Envelope_MinY,Envelope_MaxY FROM SECTION_" + m_strNetworkID + " INNER JOIN SEGMENT_" + m_strNetworkID.ToString() + "_NS0 ON SECTION_" + m_strNetworkID.ToString() + ".SECTIONID=SEGMENT_" + m_strNetworkID + "_NS0.SECTIONID";
if (strWhere.Trim() != "")
{
strSelectSection += " WHERE " + strWhere;
}
//String strSelectSegment = "SELECT * FROM SEGMENT_" + m_strNetworkID + "_NS0";
String strSelectSegment = "SELECT * " + DBOp.BuildFromStatement(m_strNetworkID);
if (strWhere.Trim() != "")
{
strSelectSegment += " WHERE " + strWhere;
this.Cursor = Cursors.Default;
}
//In
try
{ // Create new SECTION_
// Create new tables
// SEGMENT_networkid
// This table is for SECTIONID, FACILITY, BEGIN_STATION, END_STATION, DIRECTION, SECTION,AREA,UNITS
List <DatabaseManager.TableParameters> listColumn = new List <DatabaseManager.TableParameters>();
listColumn.Add(new DatabaseManager.TableParameters("SECTIONID", DataType.Int, false, true));
listColumn.Add(new DatabaseManager.TableParameters("FACILITY", DataType.VarChar(4000), 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(4000), false));
listColumn.Add(new DatabaseManager.TableParameters("AREA", DataType.Float, true));
listColumn.Add(new DatabaseManager.TableParameters("UNITS", DataType.VarChar(50), true));
listColumn.Add(new DatabaseManager.TableParameters("GEOMETRY", DataType.VarChar(-1), true));
listColumn.Add(new DatabaseManager.TableParameters("ENVELOPE_MINX", DataType.Float, true));
listColumn.Add(new DatabaseManager.TableParameters("ENVELOPE_MAXX", DataType.Float, true));
listColumn.Add(new DatabaseManager.TableParameters("ENVELOPE_MINY", DataType.Float, true));
listColumn.Add(new DatabaseManager.TableParameters("ENVELOPE_MAXY", DataType.Float, true));
List <string> orderedOracleColumns = new List <string>();
orderedOracleColumns.Add("SECTIONID");
orderedOracleColumns.Add("FACILITY");
orderedOracleColumns.Add("BEGIN_STATION");
orderedOracleColumns.Add("END_STATION");
orderedOracleColumns.Add("DIRECTION");
orderedOracleColumns.Add("SECTION");
orderedOracleColumns.Add("AREA");
orderedOracleColumns.Add("UNITS");
orderedOracleColumns.Add("GEOMETRY");
orderedOracleColumns.Add("ENVELOPE_MINX");
orderedOracleColumns.Add("ENVELOPE_MAXX");
orderedOracleColumns.Add("ENVELOPE_MINY");
orderedOracleColumns.Add("ENVELOPE_MAXY");
String strTable = "SECTION_" + strIdentity;
DBMgr.CreateTable(strTable, listColumn);
//String strMyDocumentsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
//strMyDocumentsFolder += "\\RoadCare Projects\\Temp";
//Directory.CreateDirectory(strMyDocumentsFolder);
string specialFolder = Directory.GetCurrentDirectory() + "\\Temp";
Directory.CreateDirectory(specialFolder);
String strOutFile = specialFolder + "\\subnetwork_section.txt";
TextWriter tw = new StreamWriter(strOutFile);
DataSet dsSection = DBMgr.ExecuteQuery(strSelectSection);
foreach (DataRow dr in dsSection.Tables[0].Rows)
{
String strOut = "";
for (int i = 0; i < dr.ItemArray.Length; i++)
{
strOut += dr.ItemArray[i].ToString();
if (i == dr.ItemArray.Length - 1)
{
tw.WriteLine(strOut);
}
else
{
strOut += "\t";
}
}
}
tw.Close();
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
DBMgr.SQLBulkLoad(strTable, strOutFile, '\t');
break;
case "ORACLE":
DBMgr.OracleBulkLoad(DBMgr.NativeConnectionParameters, strTable, strOutFile, orderedOracleColumns, "\t");
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Creating SECTION_ table for Network = " + m_strNewNetworkName + ". " + exception.Message);
this.Cursor = Cursors.Default;
return;
}
try
{
DataSet dsSegment = DBMgr.ExecuteQuery("SELECT * FROM SEGMENT_" + m_strNetworkID + "_NS0");
List <DatabaseManager.TableParameters> listColumn = new List <DatabaseManager.TableParameters>();
foreach (DataColumn dc in dsSegment.Tables[0].Columns)
{
DataType dt = DataType.Int;
Microsoft.SqlServer.Management.Smo.DataType smoDT = Microsoft.SqlServer.Management.Smo.DataType.Int;
if (dc.DataType == typeof(int))
{
smoDT = Microsoft.SqlServer.Management.Smo.DataType.Int;
}
if (dc.DataType == typeof(double))
{
smoDT = Microsoft.SqlServer.Management.Smo.DataType.Float;
}
if (dc.DataType == typeof(string))
{
smoDT = Microsoft.SqlServer.Management.Smo.DataType.VarChar(4000);
}
if (dc.ColumnName == "SECTIONID")
{
listColumn.Add(new DatabaseManager.TableParameters(dc.ColumnName, smoDT, false, true));
}
else
{
listColumn.Add(new DatabaseManager.TableParameters(dc.ColumnName, smoDT, true, false));
}
}
String strTable = "SEGMENT_" + strIdentity + "_NS0";
DBMgr.CreateTable(strTable, listColumn);
String strMyDocumentsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
strMyDocumentsFolder += "\\RoadCare Projects\\Temp";
Directory.CreateDirectory(strMyDocumentsFolder);
String strOutFile = strMyDocumentsFolder + "\\subnetwork_segment.txt";
TextWriter tw = new StreamWriter(strOutFile);
DataSet dsSection = DBMgr.ExecuteQuery(strSelectSection);
foreach (DataRow dr in dsSegment.Tables[0].Rows)
{
String strOut = "";
for (int i = 0; i < dr.ItemArray.Length; i++)
{
strOut += dr.ItemArray[i].ToString();
if (i == dr.ItemArray.Length - 1)
{
tw.WriteLine(strOut);
}
else
{
strOut += "\t";
}
}
}
tw.Close();
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
DBMgr.SQLBulkLoad(strTable, strOutFile, '\t');
break;
case "ORACLE":
List <string> oracleSegmentColumns = new List <string>();
foreach (DataColumn segmentColumn in dsSegment.Tables[0].Columns)
{
oracleSegmentColumns.Add(segmentColumn.ColumnName);
}
DBMgr.OracleBulkLoad(DBMgr.NativeConnectionParameters, strTable, strOutFile, oracleSegmentColumns, "\t");
//throw new NotImplementedException("TODO: figure out columns for buttonCreate_Click()");
//DBMgr.OracleBulkLoad( DBMgr.NativeConnectionParameters, strTable, strOutFile,
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Creating SEGMENT_ table for Network = " + m_strNewNetworkName + ". " + exception.Message);
this.Cursor = Cursors.Default;
return;
}
strSelect = "SELECT * FROM SEGMENT_CONTROL WHERE NETWORKID = " + m_strNetworkID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strTable = dr["SEGMENT_TABLE"].ToString();
strInsert = "";
if (strTable.Contains("SECTION"))
{
strInsert = "INSERT INTO SEGMENT_CONTROL (NETWORKID,SEGMENT_TABLE) VALUES (" + strIdentity + ",'SECTION_" + strIdentity + "')";
}
else
{
strInsert = "INSERT INTO SEGMENT_CONTROL (NETWORKID,SEGMENT_TABLE";
String strValue = " VALUES (" + strIdentity + ",'SEGMENT_" + strIdentity + "_NS0'";
if (dr["ATTRIBUTE_"].ToString() != "")
{
strInsert += ",ATTRIBUTE_";
strValue += ",'" + dr["ATTRIBUTE_"].ToString() + "'";
}
strInsert += ")" + strValue + ")";
}
DBMgr.ExecuteNonQuery(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Inserting values into SEGMENT_CONTROL." + exception.Message);
}
Global.SecurityOperations.CopyNetworkPoliciesFromTo(m_strNetworkID, strIdentity);
NewNetworkID = strIdentity;
this.DialogResult = DialogResult.OK;
this.Close();
this.Cursor = Cursors.Default;
}
private void buttonEditSubset_Click(object sender, EventArgs e)
{
int nBegin = -1;
int nEnd = -1;
String strCriteria = textBoxCriteria.Text;
if (listBoxSubset.SelectedItem != null)
{
String strName = listBoxSubset.SelectedItem.ToString();
if (strCriteria != "" && strName != "")
{
nBegin = strCriteria.IndexOf("[");
nEnd = strCriteria.IndexOf("]");
//if (nEnd >= 0 && nBegin >= 0)
{
String strAttribute = "";
if (nEnd >= 0 && nBegin >= 0)
{
strAttribute = strCriteria.Substring(nBegin + 1, nEnd - nBegin - 1);
}
FormSegmentationCriteria form = new FormSegmentationCriteria(strName, strCriteria, strAttribute);
if (form.ShowDialog() == DialogResult.OK)
{
m_hashFamily.Clear();
listBoxSubset.Items.Clear();
String strQuery = "SELECT FAMILY_NAME, FAMILY_EXPRESSION FROM CRITERIA_SEGMENT ORDER BY FAMILY_NAME";
DataSet ds;
try
{
ds = DBMgr.ExecuteQuery(strQuery);
foreach (DataRow row in ds.Tables[0].Rows)
{
strName = row.ItemArray[0].ToString();
strCriteria = row.ItemArray[1].ToString();
strCriteria = strCriteria.Replace("|", "'");
m_hashFamily.Add(strName, strCriteria);
listBoxSubset.Items.Add(strName);
}
listBoxSubset.Text = strName;
strCriteria = (String)m_hashFamily[strName].ToString();
textBoxCriteria.Text = strCriteria;
}
catch (Exception sqlE)
{
MessageBox.Show(sqlE.ToString());
}
}
}
}
}
else
{
String strName;
FormSegmentationCriteria form = new FormSegmentationCriteria("", "", "");
if (form.ShowDialog() == DialogResult.OK)
{
m_hashFamily.Clear();
listBoxSubset.Items.Clear();
String strQuery = "SELECT FAMILY_NAME, FAMILY_EXPRESSION FROM CRITERIA_SEGMENT ORDER BY FAMILY_NAME";
DataSet ds;
try
{
ds = DBMgr.ExecuteQuery(strQuery);
foreach (DataRow row in ds.Tables[0].Rows)
{
strName = row.ItemArray[0].ToString();
strCriteria = row.ItemArray[1].ToString();
strCriteria = strCriteria.Replace("|", "'");
m_hashFamily.Add(strName, strCriteria);
listBoxSubset.Items.Add(strName);
}
listBoxSubset.Text = form.m_strName;
strCriteria = (String)m_hashFamily[form.m_strName].ToString();
textBoxCriteria.Text = strCriteria;
}
catch (Exception sqlE)
{
MessageBox.Show(sqlE.ToString());
}
}
}
}
public bool LoadBudgetPercentages(List <String> listBudgets, object APICall,
IMongoCollection <SimulationModel> Simulations, string m_strSimulationID)
{
//Cleanup unused Budgets
String strDelete = "DELETE FROM " + cgOMS.Prefix + "PRIORITYFUND WHERE PRIORITYID='" + this.PriorityID + "'";
if (listBudgets.Count > 0)
{
strDelete += " AND (";
}
int nBudget = 0;
foreach (String sBudget in listBudgets)
{
if (nBudget > 0)
{
strDelete += " AND ";
}
strDelete += "BUDGET<>'" + sBudget + "'";
nBudget++;
}
if (listBudgets.Count > 0)
{
strDelete += ")";
}
try
{
DBMgr.ExecuteNonQuery(strDelete);
}
catch (Exception except)
{
if (APICall.Equals(true))
{
var updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, $"Fatal Error: Error removing non-used budget priorities");
Simulations.UpdateOne(s => s.simulationId == Convert.ToInt32(m_strSimulationID), updateStatus);
}
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Error removing non-used budget priorities. " + except.Message));
return(false);
}
if (SimulationMessaging.IsOMS)
{
foreach (string budget in listBudgets)
{
m_hashBudgetPercent.Add(budget, 100f);
}
}
else
{
String strSelect = "SELECT BUDGET, FUNDING FROM " + cgOMS.Prefix + "PRIORITYFUND WHERE PRIORITYID='" + this.PriorityID + "'";
DataSet ds;
try
{
ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count != listBudgets.Count)
{
if (APICall.Equals(true))
{
var updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, $"Error: Each budget must have a funding level for each priority level");
Simulations.UpdateOne(s => s.simulationId == Convert.ToInt32(m_strSimulationID), updateStatus);
}
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Each budget must have a funding level for each priority level."));
return(false);
}
}
catch
{
if (APICall.Equals(true))
{
var updateStatus = Builders <SimulationModel> .Update
.Set(s => s.status, $"Error: Filling Priority budgets and funding");
Simulations.UpdateOne(s => s.simulationId == Convert.ToInt32(m_strSimulationID), updateStatus);
}
SimulationMessaging.AddMessage(new SimulationMessage("Error: Filling Priority budgets and funding"));
return(false);
}
String strBudget;
String strFunding;
float fFunding;
foreach (DataRow row in ds.Tables[0].Rows)
{
strBudget = row[0].ToString();
fFunding = 100;
strFunding = row[1].ToString();
float.TryParse(strFunding, out fFunding);
m_hashBudgetPercent.Add(strBudget, fFunding);
}
}
return(true);
}
private string GetProperty(string property)
{
return(DBMgr.ExecuteQuery(GeneratePropertyQuery(property)).Tables[0].Rows[0][0].ToString());
}
private void FillListBoxes(string strAttribute)
{
hashDataSet.Clear();
listBoxField.Items.Clear();
listBoxValue.Items.Clear();
listBoxField.Items.Add("ROUTES");
listBoxField.Items.Add("BEGIN_STATION");
listBoxField.Items.Add("END_STATION");
listBoxField.Items.Add("DIRECTION");
listBoxField.Items.Add("DATE_");
listBoxField.Items.Add("YEARS");
listBoxField.Items.Add("[" + strAttribute + "]");
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(strAttribute);
String strQuery;
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT COUNT(*) FROM " + strAttribute + " WHERE ROUTES<> ''";
break;
case "ORACLE":
strQuery = "SELECT COUNT(*) FROM " + strAttribute + " WHERE ROUTES LIKE '_%'";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
try
{
DataSet ds = DBMgr.ExecuteQuery(strQuery, cp);
int nCount = 0;
if (ds.Tables[0].Rows.Count > 0)
{
nCount = int.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString());
}
int nMode = 1;
while (nCount / nMode > 100)
{
nMode = nMode * 2;
}
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT DATA_ FROM " + strAttribute + " WHERE ID_%" + nMode.ToString() + "='0' AND ROUTES<>'' ORDER BY DATA_";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT DATA_ FROM " + strAttribute + " WHERE MOD(ID_," + nMode.ToString() + ")='0' AND ROUTES LIKE '_%' ORDER BY DATA_";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for FillListBoxes()");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
foreach (DataRow row in ds.Tables[0].Rows)
{
listBoxValue.Items.Add(row.ItemArray[0].ToString());
}
hashDataSet.Add(strAttribute, ds);
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT year(DATE_) AS YEARS FROM " + strAttribute + " WHERE ROUTES<>'' ORDER BY YEARS";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT TO_CHAR(DATE_,'YYYY') AS YEARS FROM " + strAttribute + " WHERE ROUTES LIKE '_%' ORDER BY YEARS";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for FillListBoxes()");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
hashDataSet.Add("YEARS", ds);
hashDataSet.Add("DATE_", new DataSet());
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT ROUTES FROM " + strAttribute + " WHERE ROUTES<>'' ORDER BY ROUTES";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT ROUTES FROM " + strAttribute + " WHERE ROUTES LIKE '_%' ORDER BY ROUTES";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
hashDataSet.Add("ROUTES", ds);
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT DIRECTION FROM " + strAttribute + " WHERE ROUTES<>'' ORDER BY DIRECTION";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT DIRECTION FROM " + strAttribute + " WHERE ROUTES LIKE '_%' ORDER BY DIRECTION";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
hashDataSet.Add("DIRECTION", ds);
hashDataSet.Add("BEGIN_STATION", new DataSet());
hashDataSet.Add("END_STATION", new DataSet());
}
catch (Exception exc)
{
Global.WriteOutput("Error: A problem occured while trying to fill the attribute list box. " + exc.Message);
}
}
private bool CheckQuery()
{
String strWhere = textBoxSearch.Text.ToString();
String strAny = strWhere.ToUpper();
String strTable = "";
String strQuery;
bool bGoodQuery = false;
bool bAny = false;
if (strAny.Contains("ANYRECORD") || strAny.Contains("ANYCHANGE") || strAny.Contains("ANYYEAR"))
{
bAny = true;
}
m_strAttribute = "[" + comboBoxAttribute.Text + "]";
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strWhere = strWhere.Replace(m_strAttribute, " DATA_ ");
strTable = m_strAttribute.Replace("[", " ");
strTable = strTable.Replace("]", " ");
break;
case "ORACLE":
strWhere = strWhere.Replace(m_strAttribute, " \"DATA_\" ");
strTable = m_strAttribute.Replace("[", "");
strTable = strTable.Replace("]", "");
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(strTable);
if (bAny)
{
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE ROUTES<>'' OR ROUTES IS NOT NULL";
break;
case "ORACLE":
//if our goal is to select all non-null, non-empty strings in the routes column
//we should be using AND and not OR here (and in the other cases too...)
//the empty string ('') fails "LIKE '_%' (and "<>''") but passes "IS NOT NULL"
//that means that empty strings pass with an OR
//I'm leaving it as is to keep consistent functionality for the time being, but when
//these debugging checks are removed (and the contents moved to functions),
//this should be examined.
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE ROUTES LIKE '_%' OR ROUTES IS NOT NULL";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
}
else
{
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE (ROUTES<>'' OR ROUTES IS NOT NULL) AND (" + strWhere + ")";
break;
case "ORACLE":
//if our goal is to select all non-null, non-empty strings in the routes column
//we should be using AND and not OR here (and in the other cases too...)
//the empty string ('') fails "LIKE '_%' (and "<>''") but passes "IS NOT NULL"
//that means that empty strings pass with an OR
//I'm leaving it as is to keep consistent functionality for the time being, but when
//these debugging checks are removed (and the contents moved to functions),
//this should be examined.
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE (ROUTES LIKE '_%' OR ROUTES IS NOT NULL) AND (" + strWhere + ")";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
}
try
{
strTable = strTable.Trim();
switch (cp.Provider)
{
case "MSSQL":
strWhere = strWhere.Replace("YEARS", " year(DATE_) ");
break;
case "ORACLE":
strWhere = strWhere.Replace("YEARS", " TO_CHAR(\"DATE_\",'YYYY') ");
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for FillListBoxes()");
//break;
}
DataSet ds = DBMgr.ExecuteQuery(strQuery, cp);
if (ds.Tables.Count == 1)
{
String strOut = ds.Tables[0].Rows[0].ItemArray[0].ToString();
strOut += " entries returned.";
labelResult.Text = strOut;
labelResult.Visible = true;
bGoodQuery = true;
}
}
catch (Exception sqlE)
{
string[] str = sqlE.ToString().Split('\n');
String strError = str[0].Replace("DATA", m_strAttribute);
MessageBox.Show(strError);
}
//catch (Exception ex)
//{
// Global.WriteOutput("Error attempting to check query: " + ex.Message);
//}
return(bGoodQuery);
}
private void MSSQLdgvCalculatedCellDoubleClick(DataGridViewCellEventArgs e)
{
int nRow = e.RowIndex;
int nColumn = e.ColumnIndex;
String strID = "";
if (nColumn == 0) //Edit equations
{
String strEquation = "";
String isFunctionTag = "0";
bool isFunction = false;
if (dgvCalculated[nColumn, nRow].Value != null)
{
strEquation = dgvCalculated[nColumn, nRow].Value.ToString();
}
FormEditEquation formEditEquation = new FormEditEquation(strEquation, false, isFunction);
formEditEquation.CalculatedField = true;
if (formEditEquation.ShowDialog() == DialogResult.OK)
{
if (dgvCalculated.Rows[nRow].Tag == null) //Insert new
{
try
{
strEquation = formEditEquation.Equation;
dgvCalculated.Rows.Add();
String strInsert = "INSERT INTO ATTRIBUTES_CALCULATED (ATTRIBUTE_,EQUATION,ISFUNCTION)VALUES('" + this.Attribute + "','" + strEquation + "','0')";
SqlCommand command = new SqlCommand(strInsert, DBMgr.NativeConnectionParameters.SqlConnection);
command.ExecuteNonQuery();
String strIdentity = "SELECT IDENT_CURRENT ('ATTRIBUTES_CALCULATED') FROM ATTRIBUTES_CALCULATED";
DataSet ds = DBMgr.ExecuteQuery(strIdentity);
strIdentity = ds.Tables[0].Rows[0].ItemArray[0].ToString();
dgvCalculated.Rows[nRow].Tag = strIdentity;
dgvCalculated[nColumn, nRow].Value = strEquation;
dgvCalculated.Update();
dgvCalculated[e.ColumnIndex, e.RowIndex].Value = formEditEquation.Equation;
}
catch (Exception exception)
{
Global.WriteOutput("Error: Inserting EQUATION for Calculated Fields." + exception.Message);
}
}
else //Update existing
{
try
{
strEquation = formEditEquation.Equation;
strID = dgvCalculated.Rows[nRow].Tag.ToString();
String strUpdate = "UPDATE ATTRIBUTES_CALCULATED SET EQUATION='" + strEquation + "', ISFUNCTION='0' WHERE ID_='" + strID + "'";
SqlCommand command = new SqlCommand(strUpdate, DBMgr.NativeConnectionParameters.SqlConnection);
command.ExecuteNonQuery();
dgvCalculated[e.ColumnIndex, e.RowIndex].Value = formEditEquation.Equation;
}
catch (Exception exception)
{
Global.WriteOutput("Error: Updating EQUATION for Calculated Fields." + exception.Message);
}
}
}
}
else // Edit criteria
{
String strCriteria = "";
if (dgvCalculated[nColumn, nRow].Value != null)
{
strCriteria = dgvCalculated[nColumn, nRow].Value.ToString();
}
FormAdvancedSearch formAdvancedSearch = new FormAdvancedSearch(strCriteria);
if (formAdvancedSearch.ShowDialog() == DialogResult.OK)
{
dgvCalculated[nColumn, nRow].Value = formAdvancedSearch.Query;
SqlParameter param0 = new SqlParameter("@criteria", SqlDbType.VarBinary, -1);
if (dgvCalculated.Rows[nRow].Tag == null) //Insert new
{
try
{
strCriteria = formAdvancedSearch.Query;
dgvCalculated[nColumn, nRow].Value = strCriteria;
strCriteria = strCriteria.Replace("'", "|");
String strInsert = "INSERT INTO ATTRIBUTES_CALCULATED (ATTRIBUTE_,EQUATION,CRITERIA)VALUES('" + this.Attribute + "','','" + strCriteria + "')";
SqlCommand command = new SqlCommand(strInsert, DBMgr.NativeConnectionParameters.SqlConnection);
command.ExecuteNonQuery();
String strIdentity = "SELECT IDENT_CURRENT ('ATTRIBUTES_CALCULATED') FROM ATTRIBUTES_CALCULATED";
DataSet ds = DBMgr.ExecuteQuery(strIdentity);
strIdentity = ds.Tables[0].Rows[0].ItemArray[0].ToString();
dgvCalculated.Rows[nRow].Tag = strIdentity;
dgvCalculated.Rows.Add();
dgvCalculated.Update();
dgvCalculated.Refresh();
}
catch (Exception exception)
{
Global.WriteOutput("Error: Inserting Criteria for Calculated Fields." + exception.Message);
}
}
else //Update existing
{
try
{
strCriteria = formAdvancedSearch.Query;
dgvCalculated[nColumn, nRow].Value = strCriteria;
strCriteria = strCriteria.Replace("'", "|");
strID = dgvCalculated.Rows[nRow].Tag.ToString();
String strUpdate = "UPDATE ATTRIBUTES_CALCULATED SET CRITERIA='" + strCriteria + "' WHERE ID_='" + strID + "'";
SqlCommand command = new SqlCommand(strUpdate, DBMgr.NativeConnectionParameters.SqlConnection);
command.ExecuteNonQuery();
dgvCalculated.Update();
}
catch (Exception exception)
{
Global.WriteOutput("Error: Updating CRITERIA for Calculated Fields." + exception.Message);
}
}
}
}
dgvCalculated.Update();
}
private void MainMapImage_MouseDoubleClick(object sender, MouseEventArgs e)
{
//if (this.ImageView)
if (true)
{
PointF ptF = new PointF((float)e.X, (float)e.Y);
SharpMap.Geometries.Point pt = MainMapImage.Map.ImageToWorld(ptF);
#if DDOT
TransformGeo(pt);
#endif
String strQuery;
if (pt.X < 0)
{
strQuery = "SELECT SECTIONID, FACILITY, SECTION, BEGIN_STATION, END_STATION,"
+ " DIRECTION, AREA, UNITS, GEOMETRY FROM SECTION_" + m_strNetworkID
+ " WHERE Envelope_MinX >"
+ pt.X * 1.001 + " AND " + pt.X * 0.999 + " > Envelope_MaxX AND Envelope_MinY <"
+ pt.Y * 1.001 + " AND " + pt.Y * 0.999 + " < Envelope_MaxY";
}
else
{
strQuery = "SELECT SECTIONID, FACILITY, SECTION, BEGIN_STATION, END_STATION,"
+ " DIRECTION, AREA, UNITS, GEOMETRY FROM SECTION_" + m_strNetworkID
+ " WHERE Envelope_MinX <"
+ pt.X * 1.001 + " AND " + pt.X * 0.999 + " < Envelope_MaxX AND Envelope_MinY <"
+ pt.Y * 1.001 + " AND " + pt.Y * 0.999 + " < Envelope_MaxY";
}
try
{
// Get the list of geometries whose bounding boxes contain the selected point.
DataSet ds = DBMgr.ExecuteQuery(strQuery);
Geometry geoNearest = null;
double dMinDistance = double.PositiveInfinity;
double dDistance;
String strFacility = "";
String strSection = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
VectorLayer myLayer = (VectorLayer)MainMapImage.Map.Layers[0];
Geometry geo = (Geometry)myLayer.GeoIDs[dr["SECTIONID"].ToString()];
// Find the smallest distance between the clicked point and the geometries that met the selection
// criteria from the above select statement.
dDistance = geo.Distance(pt);
if (dDistance < dMinDistance)
{
dMinDistance = dDistance;
geoNearest = geo;
strFacility = dr["FACILITY"].ToString();
strSection = dr["SECTION"].ToString();
}
}
if (!String.IsNullOrEmpty(strFacility) && !String.IsNullOrEmpty(strSection))
{
String strSelect = "SELECT SECTIONID,DIRECTION,BEGIN_STATION FROM SECTION_" + m_strNetworkID + " WHERE FACILITY='" + strFacility + "' AND SECTION='" + strSection + "'";
DataSet dsNavigation = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow row in dsNavigation.Tables[0].Rows)
{
String strID = row["SECTIONID"].ToString();
int nSectionID = int.Parse(strID);
if (nSectionID < 1000000)
{
String strDirection = row["DIRECTION"].ToString();
String strBeginStation = row["BEGIN_STATION"].ToString();
m_event.issueEvent(new NavigationEvent(strFacility, strDirection, double.Parse(strBeginStation)));
}
else
{
m_event.issueEvent(new NavigationEvent(strFacility, strSection));
}
}
}
}
catch (Exception except)
{
Global.WriteOutput("Error handling double click: " + except.Message);
}
}
}
private void UpdateInvestmentGrid()
{
dgvBudget.Rows.Clear();
dgvBudget.Columns.Clear();
dataGridViewBudgetCriteria.Columns[1].ReadOnly = true;
m_bChange = false;
DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn();
column.HeaderText = "Years";
column.ReadOnly = true;
dgvBudget.Columns.Add(column);
String strYear = textBoxStartYear.Text;
int nStartYear = int.Parse(strYear);
String strNumberYear = cbYears.Text;
int nNumberYear = int.Parse(strNumberYear);
string[] listBudgets = textBoxBudgetOrder.Text.Split(',');
Budgets.Clear();
foreach (string str in listBudgets)
{
column = new DataGridViewTextBoxColumn();
column.HeaderCell.Value = str;
column.Tag = str;
column.Name = str;
int nCol = dgvBudget.Columns.Add(column);
dgvBudget.Columns[nCol].DefaultCellStyle.Format = "c";
dgvBudget.Columns[nCol].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
Budgets.Add(str);
}
for (int nYear = nStartYear; nYear < nStartYear + nNumberYear; nYear++)
{
string[] strDataRow = { nYear.ToString() };
int nIndex = dgvBudget.Rows.Add(strDataRow);
dgvBudget.Rows[nIndex].Tag = nYear.ToString();
}
List <String> listDelete = new List <String>();
String strSelect = "SELECT YEARID,YEAR_,BUDGETNAME,AMOUNT FROM YEARLYINVESTMENT WHERE SIMULATIONID='" +
m_strSimID + "' ORDER BY YEAR_";
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow row in ds.Tables[0].Rows)
{
String strYearID = row[0].ToString();
strYear = row[1].ToString();
String strBudget = row[2].ToString();
String strAmount = row[3].ToString();
if (dgvBudget.Columns.Contains(strBudget))
{
bool bYear = false;
foreach (DataGridViewRow dr in dgvBudget.Rows)
{
if (dr.Tag.ToString() == strYear)
{
bYear = true;
float fAmount = 0;
float.TryParse(strAmount, out fAmount);
dr.Cells[strBudget].Value = fAmount;
dr.Cells[strBudget].Tag = strYearID;
}
}
if (!bYear)
{
listDelete.Add(strYearID);
}
}
else
{
listDelete.Add(strYearID);
}
}
foreach (String strID in listDelete)
{
String strDelete = "DELETE FROM YEARLYINVESTMENT WHERE YEARID='" + strID + "'";
try
{
DBMgr.ExecuteNonQuery(strDelete);
}
catch (Exception except)
{
Global.WriteOutput("Error: " + except.Message);
}
}
m_bChange = true;
//Delete extra budgets
var delete = "DELETE FROM BUDGET_CRITERIA WHERE SIMULATIONID=" + m_strSimID;
if (Budgets.Count > 0)
{
delete += " AND NOT (";
var index = 0;
foreach (var budget in Budgets)
{
if (index > 0)
{
delete += " OR ";
}
delete += "BUDGET_NAME='" + budget + "'";
index++;
}
delete += ")";
try
{
DBMgr.ExecuteNonQuery(delete);
}
catch (Exception except)
{
Global.WriteOutput("Error deleting budgets from budget criteria: " + except.Message);
}
}
//Build budget criteria
dataGridViewBudgetCriteria.Rows.Clear();
var select =
"SELECT BUDGET_CRITERIA_ID, BUDGET_NAME, CRITERIA FROM BUDGET_CRITERIA WHERE SIMULATIONID='" +
m_strSimID + "'";
ds = DBMgr.ExecuteQuery(select);
foreach (DataRow row in ds.Tables[0].Rows)
{
var budgetCriteriaId = row["BUDGET_CRITERIA_ID"].ToString();
var budgetName = row["BUDGET_NAME"].ToString();
var criteria = "";
if (row["CRITERIA"] != DBNull.Value)
{
criteria = row["CRITERIA"].ToString();
}
var rowIndex = dataGridViewBudgetCriteria.Rows.Add(budgetName, criteria.Replace("|", "'"));
dataGridViewBudgetCriteria.Rows[rowIndex].Tag = budgetCriteriaId;
}
}
private void CreateReportRows()
{
string wardFuncQuery = "SELECT CLASS.DATA_ AS 'FUNC. CLASS', WARD.DATA_ AS WARD, sum(LENGTH.DATA_) / 5280 AS LONGNESS FROM CLASS INNER JOIN LENGTH ON (CLASS.FACILITY = LENGTH.FACILITY AND CLASS.SECTION = LENGTH.SECTION) INNER JOIN WARD ON (CLASS.FACILITY = WARD.FACILITY AND CLASS.SECTION = WARD.SECTION) GROUP BY CLASS.DATA_, WARD.DATA_";
DataSet wardFuncData = DBMgr.ExecuteQuery(wardFuncQuery);
string wardsQuery = "SELECT DISTINCT WARD.DATA_ FROM WARD ORDER BY WARD.DATA_";
DataSet distinctWards = DBMgr.ExecuteQuery(wardsQuery);
string funcsQuery = "SELECT DISTINCT CLASS.DATA_ FROM CLASS ORDER BY CLASS.DATA_";
DataSet distinctFuncs = DBMgr.ExecuteQuery(funcsQuery);
string aggregateQuery = "SELECT WARD.DATA_ AS WARD, sum(LENGTH.DATA_) / 5280 AS LONGNESS FROM LENGTH INNER JOIN WARD ON (LENGTH.FACILITY = WARD.FACILITY AND LENGTH.SECTION = WARD.SECTION) GROUP BY WARD.DATA_";
DataSet aggregateData = DBMgr.ExecuteQuery(aggregateQuery);
List <string> wards = new List <string>();
List <string> funcs = new List <string>();
foreach (DataRow wardRow in distinctWards.Tables[0].Rows)
{
wards.Add(wardRow[0].ToString());
}
foreach (DataRow funcRow in distinctFuncs.Tables[0].Rows)
{
funcs.Add(funcRow[0].ToString());
}
if (wards[0] != "")
{
wards.Insert(0, "");
}
if (funcs[0] != "")
{
funcs.Insert(0, "");
}
//we need an extra column for the class names and two extra rows (1 for the column headers and one for the aggregate line)
int height = funcs.Count + 2;
int width = wards.Count + 1;
object[,] writeToExcel = new object[height, width];
for (int h = 0; h + 1 < height; ++h)
{
for (int w = 0; w + 1 < width; ++w)
{
writeToExcel[h + 1, w + 1] = 0.0;
}
}
writeToExcel[0, 0] = "Func. Class";
int columnIndex = 1;
foreach (string ward in wards)
{
writeToExcel[0, columnIndex] = "W - " + ward;
++columnIndex;
}
int rowIndex = 1;
foreach (string func in funcs)
{
writeToExcel[rowIndex, 0] = func;
++rowIndex;
}
writeToExcel[rowIndex, 0] = "Total";
foreach (DataRow mileageRow in wardFuncData.Tables[0].Rows)
{
int columnToIncrement = wards.IndexOf(mileageRow["WARD"].ToString());
int rowToIncrement = funcs.IndexOf(mileageRow["Func. Class"].ToString());
writeToExcel[rowToIncrement + 1, columnToIncrement + 1] = ((double)writeToExcel[rowToIncrement + 1, columnToIncrement + 1]) + double.Parse(mileageRow["LONGNESS"].ToString());
}
columnIndex = 1;
foreach (string ward in wards)
{
double sum = 0.0;
for (int i = 0; i < funcs.Count; ++i)
{
sum += ((double)writeToExcel[i + 1, columnIndex]);
}
writeToExcel[rowIndex, columnIndex] = sum;
++columnIndex;
}
char finalLetter = Convert.ToChar(Convert.ToInt32('A') + width - 1);
int finalRow = height + 6;
string rangeSelector = "A7:" + finalLetter + finalRow.ToString();
ReportGlobals.XLMgr.SetValues(rangeSelector, writeToExcel);
ReportGlobals.XLMgr.SetNumberFormat(rangeSelector, "0.00");
//ReportGlobals.XLMgr.AutoSizeColumns("A:" + finalLetter);
}
private void dgvBudget_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
if (!m_bChange)
{
return;
}
DataGridViewRow row = dgvBudget.Rows[e.RowIndex];
int nColumn = e.ColumnIndex;
String strAmount;
if (row.Cells[nColumn].Value != null)
{
strAmount = row.Cells[nColumn].Value.ToString();
}
else
{
strAmount = "";
}
strAmount = strAmount.Replace("$", "");
strAmount = strAmount.Replace(",", "");
String strID;
if (row.Cells[nColumn].Tag != null)
{
strID = row.Cells[nColumn].Tag.ToString();
String strUpdate = "UPDATE YEARLYINVESTMENT SET AMOUNT='" + strAmount + "' WHERE YEARID='" + strID + "'";
try
{
DBMgr.ExecuteNonQuery(strUpdate);
}
catch (Exception except)
{
m_bChange = false;
dgvBudget.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = m_oLastCell;
Global.WriteOutput("Error: " + except.Message);
m_bChange = true;
return;
}
}
else
{
String strBudget = dgvBudget.Columns[nColumn].Name.ToString();
String strYear = row.Tag.ToString();
String strInsert = "INSERT INTO YEARLYINVESTMENT (SIMULATIONID,YEAR_,BUDGETNAME,AMOUNT) VALUES ('" + m_strSimID + "','" + strYear + "','" + strBudget + "','" + strAmount + "')";
try
{
DBMgr.ExecuteNonQuery(strInsert);
}
catch (Exception except)
{
m_bChange = false;
dgvBudget.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = m_oLastCell;
Global.WriteOutput("Investment amount Error: " + except.Message);
m_bChange = true;
return;
}
String strIdentity = "";
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strIdentity = "SELECT IDENT_CURRENT ('YEARLYINVESTMENT') FROM YEARLYINVESTMENT";
break;
case "ORACLE":
//strIdentity = "SELECT YEARLYINVESTMENT_YEARID_SEQ.CURRVAL FROM DUAL";
//strIdentity = "SELECT LAST_NUMBER - CACHE_SIZE FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'YEARLYINVESTMENT_YEARID_SEQ'";
strIdentity = "SELECT MAX(YEARID) FROM YEARLYINVESTMENT";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
DataSet ds = DBMgr.ExecuteQuery(strIdentity);
strIdentity = ds.Tables[0].Rows[0].ItemArray[0].ToString();
row.Cells[nColumn].Tag = strIdentity;
}
}
/// <summary>
/// Load all cost information associated with this treatment.
/// </summary>
public bool LoadCost()
{
Costs cost;
String select = "SELECT COST_,UNIT,CRITERIA,BINARY_CRITERIA,ISFUNCTION, COSTID FROM " + cgOMS.Prefix + "COSTS WHERE TREATMENTID='" + this.TreatmentID + "'";
if (SimulationMessaging.IsOMS)
{
select = "SELECT COST_,UNIT,CRITERIA,BINARY_CRITERIA,NULL AS ISFUNCTION, COSTID FROM " + cgOMS.Prefix + "COSTS WHERE TREATMENTID='" + this.TreatmentID + "'";
}
DataSet ds;
try
{
//SimulationMessaging.AddMessage("DEBUGGING: Attempting cost select...");
ds = DBMgr.ExecuteQuery(select);
//SimulationMessaging.AddMessage("DEBUGGING: Cost select successful.");
}
catch (Exception exception)
{
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Opening COSTS table. SQL message - " + exception.Message));
return(false);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
string id = row["COSTID"].ToString();
cost = new Costs(id);
if (row["CRITERIA"].ToString().Trim().Length == 0)
{
cost.Default = true;
}
else
{
cost.Default = false;
string criteria = row["CRITERIA"].ToString();
byte[] assemblyCriteria = null;
assemblyCriteria = SimulationMessaging.GetSerializedCalculateEvaluate(cgOMS.Prefix + "COSTS", "BINARY_CRITERIA", cost.CostID, assemblyCriteria);
if (assemblyCriteria != null && assemblyCriteria.Length > 0)
{
cost.Criteria.Evaluate = (CalculateEvaluate.CalculateEvaluate)AssemblySerialize.DeSerializeObjectFromByteArray(assemblyCriteria);
if (cost.Criteria.Evaluate.OriginalInput != criteria)
{
cost.Criteria.Evaluate = null;
}
}
cost.Criteria.Criteria = criteria;
foreach (String str in cost.Criteria.CriteriaAttributes)
{
if (!SimulationMessaging.IsAttribute(str))
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: " + str + " which is used by the Cost criteria is not present in the database."));
}
if (!_attributes.Contains(str))
{
_attributes.Add(str);
}
}
}
byte[] assemblyCost = null;
//objectValue = row["BINARY_COST"];
//if (objectValue != System.DBNull.Value)
//{
// assemblyCost = (byte[])row["BINARY_COST"];
//}
String strCost = row["COST_"].ToString();
assemblyCost = SimulationMessaging.GetSerializedCalculateEvaluate(cgOMS.Prefix + "COSTS", "BINARY_EQUATION", cost.CostID, assemblyCost);
if (assemblyCost != null && assemblyCost.Length > 0)
{
cost.Calculate = (CalculateEvaluate.CalculateEvaluate)AssemblySerialize.DeSerializeObjectFromByteArray(assemblyCost);
if (cost.Calculate.OriginalInput != strCost)
{
cost.Calculate = null;
}
}
if (strCost.Trim() == "")
{
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Cost equation is blank for treatment - " + _treatment));
return(false);
}
bool isFunction = false;
object function = row["ISFUNCTION"];
if (row["ISFUNCTION"] != DBNull.Value)
{
isFunction = Convert.ToBoolean(row["ISFUNCTION"]);
}
if (isFunction)
{
cost.SetFunction(row["COST_"].ToString());
}
else
{
cost.Equation = row["COST_"].ToString();
}
foreach (String str in cost._attributesEquation)
{
if (str != "AREA" && str != "LENGTH")
{
if (!SimulationMessaging.IsAttribute(str))
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: " + str + " which is used by the Cost equation is not present in the database."));
}
if (!_attributes.Contains(str))
{
_attributes.Add(str);
}
}
}
if (!cost.IsCompoundTreatment)
{
if (cost._calculate.m_listError.Count > 0)
{
foreach (String str in cost.Calculate.m_listError)
{
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Cost equation:" + str));
return(false);
}
}
}
_costs.Add(cost);
}
return(true);
}
private void DataGridViewBudgetCriteria_CellValidated(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex > -1)
{
var strValue = "";
if (dataGridViewBudgetCriteria.Rows[e.RowIndex].Tag == null)
{
if (dataGridViewBudgetCriteria.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
{
strValue = dataGridViewBudgetCriteria.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
else
{
return;
}
var insert = "";
switch (e.ColumnIndex)
{
case 0:
insert = "INSERT INTO BUDGET_CRITERIA (SIMULATIONID, BUDGET_NAME) VALUES ('" +
m_strSimID + "','" + strValue + "')";
break;
case 1:
insert = "INSERT INTO BUDGET_CRITERIA (SIMULATIONID, CRITERIA) VALUES ('" +
m_strSimID + "','" + strValue.Replace("'", "|") + "')";
break;
default:
return;
}
try
{
String strIdentity;
DBMgr.ExecuteNonQuery(insert);
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strIdentity = "SELECT IDENT_CURRENT ('BUDGET_CRITERIA') FROM BUDGET_CRITERIA";
break;
case "ORACLE":
strIdentity = "SELECT MAX(BUDGET_CRITERIA_ID) FROM BUDGET_CRITERIA";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
DataSet ds = DBMgr.ExecuteQuery(strIdentity);
dataGridViewBudgetCriteria.Rows[e.RowIndex].Tag = ds.Tables[0].Rows[0].ItemArray[0].ToString();
}
catch (Exception except)
{
Global.WriteOutput("Error insert budget criteria:" + except.Message.ToString());
}
}
else
{
String strTag = dataGridViewBudgetCriteria.Rows[e.RowIndex].Tag.ToString();
String strUpdate = "";
if (dataGridViewBudgetCriteria.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
{
strValue = dataGridViewBudgetCriteria.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
else
{
return;
}
switch (e.ColumnIndex)
{
case 0:
strUpdate = "UPDATE BUDGET_CRITERIA SET BUDGET_NAME='" + strValue + "' WHERE BUDGET_CRITERIA_ID='" + strTag + "'";
break;
case 1:
strUpdate = "UPDATE BUDGET_CRITERIA SET CRITERIA='" + strValue.Replace("'", "|") + "' WHERE BUDGET_CRITERIA_ID='" + strTag + "'";
break;
default:
return;
}
try
{
if (!string.IsNullOrWhiteSpace(strUpdate))
{
DBMgr.ExecuteNonQuery(strUpdate);
}
}
catch (Exception except)
{
Global.WriteOutput("Error: " + except.Message.ToString());
}
}
}
}
public FormAttributeView(String strNetwork, Hashtable hashAttributeYear)
{
InitializeComponent();
treeNodeTags = new List <string>();
m_strNetwork = strNetwork;
m_hashAttributeYear = hashAttributeYear;
String strSelect = "SELECT NETWORKID FROM NETWORKS WHERE NETWORK_NAME='" + m_strNetwork + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
m_strNetworkID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
}
catch (Exception e)
{
String strError = "Error in getting NETWORKID for NETWORK_NAME = " + m_strNetwork + ". SQL error = " + e.Message;
Global.WriteOutput(strError);
MessageBox.Show(strError);
}
ToolTip tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(buttonUpdate, "Update data displayed in Attribute View dependent upon selected filters.");
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(buttonEditColumns, "Change the selected attributes and their display order.");
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(checkBoxCustomFilter, "Custom filter will limit output to sections identified by the filter");
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(buttonAdvancedSearch, "Create custom search which is used in conjunction with other filters.");
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(textBoxAdvanceSearch, "Current advanced search criteria.");
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(comboBoxRouteFacilty, "Limit Attribute View to Facility selected.");
}
private void DataGridViewSplitTreatmentLimit_CellValidated(object sender, DataGridViewCellEventArgs e)
{
if (dataGridViewSplitTreatmentCriteria.CurrentRow == null || dataGridViewSplitTreatmentCriteria.CurrentRow.Tag == null)
{
return;
}
var splitTreatmentId = dataGridViewSplitTreatmentCriteria.CurrentRow.Tag.ToString();
if (e.ColumnIndex <= 0)
{
return;
}
if (e.RowIndex > -1)
{
var strValue = "";
if (dataGridViewSplitTreatmentLimit.Rows[e.RowIndex].Tag == null)
{
if (dataGridViewSplitTreatmentLimit.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
{
strValue = dataGridViewSplitTreatmentLimit.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
var insert = "";
switch (e.ColumnIndex)
{
case 1:
insert = "INSERT INTO SPLIT_TREATMENT_LIMIT (SPLIT_TREATMENT_ID, AMOUNT) VALUES ('" + splitTreatmentId + "','" +
strValue + "')";
break;
case 2:
insert = "INSERT INTO SPLIT_TREATMENT_LIMIT (SPLIT_TREATMENT_ID, PERCENTAGE) VALUES ('" + splitTreatmentId + "','" +
strValue + "')";
break;
default:
return;
}
try
{
String strIdentity;
DBMgr.ExecuteNonQuery(insert);
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strIdentity = "SELECT IDENT_CURRENT ('SPLIT_TREATMENT_LIMIT') FROM SPLIT_TREATMENT_LIMIT";
break;
case "ORACLE":
strIdentity = "SELECT MAX(SPLIT_TREATMENT_LIMIT_ID) FROM SPLIT_TREATMENT_LIMIT";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
DataSet ds = DBMgr.ExecuteQuery(strIdentity);
dataGridViewSplitTreatmentLimit.Rows[e.RowIndex].Tag = ds.Tables[0].Rows[0].ItemArray[0].ToString();
}
catch (Exception except)
{
Global.WriteOutput("Error inserting split treatment amount/percentages:" + except.Message.ToString());
}
}
else
{
String strTag = dataGridViewSplitTreatmentLimit.Rows[e.RowIndex].Tag.ToString();
String strUpdate = "";
if (dataGridViewSplitTreatmentLimit.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
{
strValue = dataGridViewSplitTreatmentLimit.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
}
switch (e.ColumnIndex)
{
case 1:
if (!string.IsNullOrWhiteSpace(strValue))
{
strUpdate = "UPDATE SPLIT_TREATMENT_LIMIT SET AMOUNT='" + strValue + "' WHERE SPLIT_TREATMENT_LIMIT_ID='" + strTag + "'";
}
else
{
strUpdate = "UPDATE SPLIT_TREATMENT_LIMIT SET AMOUNT=null WHERE SPLIT_TREATMENT_LIMIT_ID='" + strTag + "'";
}
break;
case 2:
strUpdate = "UPDATE SPLIT_TREATMENT_LIMIT SET PERCENTAGE='" + strValue + "' WHERE SPLIT_TREATMENT_LIMIT_ID='" + strTag + "'";
break;
default:
return;
}
try
{
if (!string.IsNullOrWhiteSpace(strUpdate))
{
DBMgr.ExecuteNonQuery(strUpdate);
}
}
catch (Exception except)
{
Global.WriteOutput("Error updating split limit treatment: " + except.Message.ToString());
}
}
}
CheckLimits();
}
/// <summary>
/// Setup datagridviews for form.
/// </summary>
private void SetupDataGridViews()
{
m_bChange = false;
m_listFeasibleTreatment = new List <String>();
String strSelect = "SELECT TREATMENT FROM " + this.BenefitCostTable + " INNER JOIN " + this.SectionTable + " ON " + this.SectionTable + ".SECTIONID=" + this.BenefitCostTable + ".SECTIONID WHERE " + this.BenefitCostTable + ".SECTIONID=" + this.SectionID + " AND YEARS=" + this.Year;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
m_listFeasibleTreatment.Add(dr["TREATMENT"].ToString());
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving feasibility information for tooltip." + exception.Message);
}
if (dgvAttribute == null)
{
return;
}
dgvAttribute.Columns.Clear();
DataGridViewComboBoxColumn comboboxColumn;
comboboxColumn = CreateComboBoxColumn();
comboboxColumn.HeaderText = "Attribute";
Global.LoadAttributes();
foreach (String str in m_listAttributeSimulation)
{
comboboxColumn.Items.Add(str);
}
dgvAttribute.Columns.Insert(0, comboboxColumn);
dgvAttribute.RowHeadersVisible = true;
dgvAttribute.AllowUserToDeleteRows = true;
dgvAttribute.ColumnCount = 2;
dgvAttribute.Columns[1].Name = "Change";
dgvAttribute.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvAttribute.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
foreach (String key in m_hashCurrentChange.Keys)
{
int nRow = dgvAttribute.Rows.Add(key, m_hashCurrentChange[key]);
}
DataGridViewRow dataGridRow = new DataGridViewRow();
DataGridViewCell[] cells = new DataGridViewCell[2];
DataGridViewTextBoxCell txtCell = new DataGridViewTextBoxCell();
DataGridViewComboBoxCell cbShowAll = new DataGridViewComboBoxCell();
txtCell.Value = "Show Only Feasible";
dataGridRow.Cells.Add(txtCell);
txtCell.ReadOnly = true;
dataGridRow.Cells.Add(cbShowAll);
cbShowAll.Items.Add("True");
cbShowAll.Items.Add("False");
cbShowAll.Value = "True";
dgvSummary.Rows.Add(dataGridRow);
dataGridRow = new DataGridViewRow();
cells = new DataGridViewCell[2];
txtCell = new DataGridViewTextBoxCell();
DataGridViewComboBoxCell cbTreatment = new DataGridViewComboBoxCell();
txtCell.Value = "Treatment";
dataGridRow.Cells.Add(txtCell);
txtCell.ReadOnly = true;
dataGridRow.Cells.Add(cbTreatment);
foreach (String str in m_listFeasibleTreatment)
{
cbTreatment.Items.Add(str);
}
dgvSummary.Rows.Add(dataGridRow);
if (!cbTreatment.Items.Contains("No Treatment"))
{
cbTreatment.Items.Add("No Treatment");
}
if (!cbTreatment.Items.Contains(m_strCurrentTreatment))
{
cbTreatment.Items.Add(m_strCurrentTreatment);
}
cbTreatment.Value = m_strCurrentTreatment;
// ------------------------------------------------
// Add a combobox cell and add the combobox items
dataGridRow = new DataGridViewRow();
cells = new DataGridViewCell[2];
txtCell = new DataGridViewTextBoxCell();
DataGridViewComboBoxCell cbBudget = new DataGridViewComboBoxCell();
cbBudget.Items.Add("");
foreach (String str in m_listBudgets)
{
cbBudget.Items.Add(str);
}
if (!m_listBudgets.Contains(m_strCurrentBudget))
{
cbBudget.Items.Add(m_strCurrentBudget);
}
cbBudget.Value = m_strCurrentBudget;
txtCell.Value = "Budget";
dataGridRow.Cells.Add(txtCell);
txtCell.ReadOnly = true;
dataGridRow.Cells.Add(cbBudget);
dgvSummary.Rows.Add(dataGridRow);
float fCost = 0;
int nCostRow = dgvSummary.Rows.Add("Cost", fCost);
dgvSummary.Rows[nCostRow].Cells[1].Style.Format = "c";
dgvSummary[1, nCostRow].Value = m_strCurrentCost;
dgvSummary.Rows.Add("Years Before Any", m_strCurrentAny);
dgvSummary.Rows.Add("Years Before Same", m_strCurrentSame);
m_bChange = true;
}
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 FormSpecialReportConfig()
{
InitializeComponent();
Nets = DBMgr
.ExecuteQuery(
"SELECT networkid, network_name" +
" FROM networks ORDER BY network_name")
.Tables[0]
.AsEnumerable()
.Select(dr => new Net
{
Id = dr[0].ToString(),
Name = dr[1].ToString()
})
.ToArray();
Sims = DBMgr
.ExecuteQuery(
"SELECT simulationid, simulation, networkid" +
" FROM simulations ORDER BY simulation")
.Tables[0]
.AsEnumerable()
.Select(dr => new Sim
{
Id = dr[0].ToString(),
Name = dr[1].ToString(),
Net = dr[2].ToString()
})
.ToArray();
NetRpts = DBOp.GetNetworkReportNames().ToArray();
SimRpts =
DBOp.GetSimulationReportNames().Concat(new[]
{
Cis.Report.GenericTitle,
Fytsp.Report.GenericTitle,
Ldvc.Report.GenericTitle,
Blfy.Report.GenericTitle,
Blfc.Report.GenericTitle,
Icf.Report.GenericTitle,
AllSec.Report.GenericTitle,
BenefitCostRatioReport.GenericTitle,
})
.ToArray();
Array.Sort(NetRpts);
Array.Sort(SimRpts);
cbNets.DisplayMember = "Name";
cbSims.DisplayMember = "Name";
if (Nets.Length != 0)
{
cbNets.Items.AddRange(Nets);
cbNets.SelectedIndex = 0;
}
else
{
cbNets.SelectedIndex = -1;
}
fbdSimpleGen.Description =
"Select the folder in which you would like the report file" +
" to be generated.";
Timer = new Stopwatch();
}
private void AttributeMapQuery(MouseEventArgs e)
{
// Get the section of the road that the user selected, and any information regarding the segmented
// attribute.
ApplyOldColorsToGeoms(false);
PointF ptF = new PointF((float)e.X, (float)e.Y);
SharpMap.Geometries.Point pt = MainMapImage.Map.ImageToWorld(ptF);
String strQuery = "SELECT SECTIONID, FACILITY, SECTION, BEGIN_STATION, END_STATION,"
+ " DIRECTION, AREA, UNITS, GEOMETRY FROM SECTION_" + m_strNetworkID
+ " WHERE Envelope_MinX <"
+ pt.X * (pt.X > 0 ? 1.001 : 0.999) + " AND " + pt.X * (pt.X > 0 ? 0.999 : 1.001) + " < Envelope_MaxX AND Envelope_MinY <"
+ pt.Y * (pt.Y > 0 ? 1.001 : 0.999) + " AND " + pt.Y * (pt.Y > 0 ? 0.999 : 1.001) + " < Envelope_MaxY";
try
{
// Get the list of geometries whose bounding boxes contain the selected point.
DataSet ds = DBMgr.ExecuteQuery(strQuery);
Geometry geoNearest = null;
double dMinDistance = double.PositiveInfinity;
double dDistance;
int iSectionID = -1;
foreach (DataRow dr in ds.Tables[0].Rows)
{
VectorLayer myLayer = (VectorLayer)MainMapImage.Map.Layers[0];
Geometry geo = (Geometry)myLayer.GeoIDs[dr["SECTIONID"].ToString()];
// Find the smallest distance between the clicked point and the geometries that met the selection
// criteria from the above select statement.
if (geo != null)
{
dDistance = geo.Distance(pt);
if (dDistance < dMinDistance)
{
dMinDistance = dDistance;
geoNearest = geo;
// Also, grab the section id of the clicked point
iSectionID = Int32.Parse(dr["SECTIONID"].ToString());
}
}
}
if (geoNearest != null)
{
m_htGeometryOldColors.Add(geoNearest, geoNearest.Color);
geoNearest.Width_ = 5;
geoNearest.Color = Color.Cyan;
MainMapImage.Refresh();
try
{
AttributeTab attributeTab;
if (FormManager.IsAttributeTabOpen(out attributeTab))
{
AssetTab assetTab;
if (FormManager.IsAssetTabOpen(out assetTab))
{
assetTab.Hide();
}
if (attributeTab.IsHidden)
{
attributeTab.Show();
}
attributeTab.SetSectionID(iSectionID);
attributeTab.PopulateComboBoxYears();
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: Problem querying SEGMENT_" + m_strNetworkID + "_NS0 table. " + exc.Message);
}
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: Could not get ATTRIBUTE or NETWORK MAP data. " + exc.Message);
}
}
public static void PasteCommitted(String simulationID)
{
if (String.IsNullOrEmpty(m_strCopyTreatmentSimulationID))
{
return;
}
//Delete existing SIMULATIONID from TREATMENT
String strDelete = "DELETE FROM COMMITTED_ WHERE SIMULATIONID=" + simulationID;
try
{
DBMgr.ExecuteNonQuery(strDelete);
}
catch (Exception exception)
{
Global.WriteOutput("Error: Overwriting existing committed projects." + exception.Message);
return;
}
String strSelect = "SELECT * FROM COMMITTED_ WHERE SIMULATIONID=" + m_strCopyCommittedSimulationID;
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
int sectionID = Convert.ToInt32(dr["SECTIONID"]);
int years = Convert.ToInt32(dr["YEARS"]);
String treatmentName = dr["TREATMENTNAME"].ToString();
int yearSame = Convert.ToInt32(dr["YEARSAME"]);
int yearAny = Convert.ToInt32(dr["YEARANY"]);
String budget = dr["BUDGET"].ToString();
double cost = Convert.ToDouble(dr["COST_"]);
int commitID = Convert.ToInt32(dr["COMMITID"]);
String insert = "INSERT INTO COMMITTED_ (SIMULATIONID,SECTIONID,YEARS,TREATMENTNAME,YEARSAME,YEARANY, BUDGET,COST_) VALUES('" +
simulationID.ToString() + "','" +
sectionID.ToString() + "','" +
years.ToString() + "','" +
treatmentName + "','" +
yearSame.ToString() + "','" +
yearAny.ToString() + "','" +
budget + "','" +
cost.ToString() + "')";
try
{
DBMgr.ExecuteNonQuery(insert);
}
catch (Exception e)
{
Global.WriteOutput("Error: copying committed projects." + e.Message);
break;
}
String strIdentity;
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strIdentity = "SELECT TOP 1 IDENT_CURRENT ('COMMITTED_') FROM COMMITTED_";
break;
case "ORACLE":
//strIdentity = "SELECT TREATMENTS_TREATMENTID_SEQ.CURRVAL FROM DUAL";
//strIdentity = "SELECT LAST_NUMBER - CACHE_SIZE + 1 FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TREATMENTS_TREATMENTID_SEQ'";
strIdentity = "SELECT MAX(COMMITID) FROM COMMITTED_";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
DataSet dsIdentity = DBMgr.ExecuteQuery(strIdentity);
strIdentity = dsIdentity.Tables[0].Rows[0].ItemArray[0].ToString();
strSelect = "SELECT * FROM COMMIT_CONSEQUENCES WHERE COMMITID =" + commitID;
DataSet dsCommitConsequences = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow drCommitConsequences in dsCommitConsequences.Tables[0].Rows)
{
String attribute = drCommitConsequences["ATTRIBUTE_"].ToString();
String change = drCommitConsequences["CHANGE_"].ToString();
insert = "INSERT INTO COMMIT_CONSEQUENCES (COMMITID,ATTRIBUTE_,CHANGE_) VALUES('" + strIdentity + "','" + attribute + "','" + change + "')";
try
{
DBMgr.ExecuteNonQuery(insert);
}
catch (Exception e)
{
Global.WriteOutput("Error: copying committed projects." + e.Message);
break;
}
}
}
Global.WriteOutput("Cloning committed projects complete!");
}
private void AssetAdvancedSearch()
{
String strQuery = "";
if (MainMapImage.Map.Layers.Count > 0)
{
strQuery = tbAdvancedSearch.Text;
FormQueryRaw form = new FormQueryRaw(MainMapImage.Map.Layers[0].LayerName, strQuery);
if (form.ShowDialog() == DialogResult.OK)
{
tbAdvancedSearch.Text = form.m_strQuery;
// Find the geoms that match the where clause of the query. Then redraw the layer with only
// those geoms? Or highlight those geoms?
List <Geometry> listGeoms = new List <Geometry>();
strQuery = "SELECT GEO_ID FROM " + MainMapImage.Map.Layers[0].LayerName + " WHERE " + form.m_strQuery;
DataReader dr;
try
{
dr = new DataReader(strQuery);
while (dr.Read())
{
try
{
m_listGeomIDs.Add(dr["GEO_ID"].ToString());
}
catch (Exception excInner)
{
Global.WriteOutput("Warning: Point not valid." + excInner.Message + " " + dr[0].ToString());
}
}
dr.Close();
}
catch (Exception exc)
{
Global.WriteOutput("Error: Failed to load asset geometries." + exc.Message);
}
// Add the new selection to the combobox and dgv
String strSelect = "SELECT * FROM " + MainMapImage.Map.Layers[0].LayerName + " WHERE GEO_ID = '";
String strWhere = "";
for (int i = 0; i < m_listGeomIDs.Count; i++)
{
if (i < m_listGeomIDs.Count - 1)
{
strWhere += m_listGeomIDs[i] + "' OR GEO_ID = '";
}
else
{
strWhere += m_listGeomIDs[i] + "'";
}
}
strSelect += strWhere;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
FormGISLayerManager formGISLayerManager;
if (FormManager.IsFormGISLayerManagerOpen(out formGISLayerManager))
{
formGISLayerManager.SetAdvSearchColor(Color.Cyan, m_listGeomIDs);
m_listGeomIDs.Clear();
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: " + exc.Message);
}
}
}
}
public static void PastePerformance(String strNewSimulationID)
{
if (String.IsNullOrEmpty(m_strCopyPerformanceSimulationID))
{
return;
}
List <string> listInserts = new List <string>();
//Delete existing SIMULATIONID FROM
String strDelete = "DELETE FROM PERFORMANCE WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(strDelete);
String strSelect = "SELECT * FROM PERFORMANCE WHERE SIMULATIONID=" + m_strCopyPerformanceSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strAttribute = dr["ATTRIBUTE_"].ToString();
String strEquationName = dr["EQUATIONNAME"].ToString();
String strCritera = dr["CRITERIA"].ToString();
String strEquations = dr["EQUATION"].ToString();
String strShift = dr["SHIFT"].ToString();
string strPiecewise = dr["PIECEWISE"].ToString();
string isFunction = "0";
bool bIsFunction = false;
if (dr["ISFUNCTION"] != DBNull.Value)
{
bIsFunction = Convert.ToBoolean(dr["ISFUNCTION"]);
}
if (bIsFunction)
{
isFunction = "1";
}
String strInsert = "";
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strInsert = "INSERT INTO PERFORMANCE (SIMULATIONID,ATTRIBUTE_,EQUATIONNAME,CRITERIA,EQUATION,SHIFT,PIECEWISE,ISFUNCTION)VALUES(" + strNewSimulationID + ",'" + strAttribute + "','" + strEquationName + "','" + strCritera + "','" + strEquations + "','" + strShift + "','" + strPiecewise + "','" + isFunction + "')";
break;
case "ORACLE":
strInsert = "INSERT INTO PERFORMANCE (SIMULATIONID,ATTRIBUTE_,EQUATIONNAME,CRITERIA,EQUATION,SHIFT,PIECEWISE,ISFUNCTION)VALUES(" + strNewSimulationID + ",'" + strAttribute + "','" + strEquationName + "','" + strCritera + "','" + strEquations + "','" + strShift + "','" + strPiecewise + "','" + isFunction + "')";
break;
default:
throw new NotImplementedException("TODO: Implement ANSI version of PastePerformance()");
//break;
}
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copy PERFORMANCE table." + exception.Message);
return;
}
try
{
DBMgr.ExecuteBatchNonQuery(listInserts);
Global.WriteOutput("Performance equations successfully copied.");
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying Performance equations from one simulation to another" + exception.Message);
}
}
public void LoadBudgets()
{
string[] budgets = BudgetOrderString.Split(',');
String strBudget;
for (int i = 0; i < budgets.Length; i++)
{
strBudget = budgets[i];
m_listBudgetOrder.Add(strBudget);
}
String strSelect = "SELECT YEAR_,BUDGETNAME,AMOUNT FROM " + cgOMS.Prefix + "YEARLYINVESTMENT WHERE SIMULATIONID='" + this.InvestmentID + "' ORDER BY YEAR_";
DataSet ds = null;
try
{
ds = DBMgr.ExecuteQuery(strSelect);
}
catch (Exception ex)
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: Loading YEARLYINVESTMENT table." + ex.Message));
return;
}
String strYear;
String strAmount;
foreach (DataRow row in ds.Tables[0].Rows)
{
strYear = row[0].ToString();
strBudget = row[1].ToString();
strAmount = row[2].ToString();
if (BudgetYear.Contains(strBudget))
{
Hashtable hashYearAmount = (Hashtable)BudgetYear[strBudget];
Hashtable hashYearAmountOriginal = (Hashtable)BudgetYearOriginal[strBudget];
m_hashBudgetYear.Remove(strBudget);
m_hashBudgetYearOriginal.Remove(strBudget);
if (strAmount == "")
{
hashYearAmount.Add(strYear, 0.0);
hashYearAmountOriginal.Add(strYear, 0.0);
}
else
{
hashYearAmount.Add(strYear, float.Parse(strAmount));
hashYearAmountOriginal.Add(strYear, float.Parse(strAmount));
}
BudgetYear.Add(strBudget, hashYearAmount);
BudgetYearOriginal.Add(strBudget, hashYearAmountOriginal);
}
else
{
Hashtable hashYearAmount = new Hashtable();
Hashtable hashYearAmountOriginal = new Hashtable();
if (strAmount == "")
{
hashYearAmount.Add(strYear, 0.0);
hashYearAmountOriginal.Add(strYear, 0.0);
}
else
{
hashYearAmount.Add(strYear, float.Parse(strAmount));
hashYearAmountOriginal.Add(strYear, float.Parse(strAmount));
}
BudgetYear.Add(strBudget, hashYearAmount);
BudgetYearOriginal.Add(strBudget, hashYearAmountOriginal);
}
}
}
