public void create_travel_times()
{
int intRandomNumber;
string strRandomNumber;
string strRandomSave;
string strMDBFile;
string strConn;
string strSQL;
//string strPsiteRailTravel_YN="N";
int x;
int y;
System.Data.OleDb.OleDbConnection p_conn;
p_conn = new System.Data.OleDb.OleDbConnection();
ado_data_access p_ado = new ado_data_access();
utils p_utils = new utils();
strMDBFile = this.m_frmMain.frmProject.uc_project1.txtRootDirectory.Text + "\\gis\\db\\gis_travel_times.mdb";
strConn = p_ado.getMDBConnString(strMDBFile, "admin", "");
p_ado.OpenConnection(strConn, ref p_conn);
p_ado.SqlNonQuery(p_conn, "delete from travel_time");
if (p_ado.m_intError == 0)
{
p_ado.CreateDataSet(p_conn, "select * from plot", "plot");
if (p_ado.m_intError == 0)
{
p_ado.AddSQLQueryToDataSet(p_conn, ref p_ado.m_OleDbDataAdapter, ref p_ado.m_DataSet, "select * from processing_site where ucase(name) like 'TEST%'", "processing_site");
if (p_ado.m_intError == 0)
{
this.m_frmTherm = new frmTherm();
this.m_frmTherm.AbortProcess = false;
this.m_frmTherm.progressBar1.Minimum = 1;
this.m_frmTherm.progressBar1.Maximum =
p_ado.m_DataSet.Tables["plot"].Rows.Count;
m_frmTherm.btnCancel.Visible = true;
m_frmTherm.Show();
m_frmTherm.Focus();
m_frmTherm.Text = "Generating Random Travel Times";
m_frmTherm.Refresh();
this.m_frmTherm.btnCancel.Click += new System.EventHandler(this.ThermCancel);
for (x = 0; x <= p_ado.m_DataSet.Tables["plot"].Rows.Count - 1; x++)
{
this.m_frmTherm.Increment(x + 1);
strRandomSave = "";
for (y = 0; y <= p_ado.m_DataSet.Tables["processing_site"].Rows.Count - 1; y++)
{
for (;;)
{
intRandomNumber = p_utils.RandomNumber(1, 18);
strRandomNumber = Convert.ToString(intRandomNumber);
intRandomNumber = p_utils.RandomNumber(0, 9);
strRandomNumber += "." + Convert.ToString(intRandomNumber);
intRandomNumber = p_utils.RandomNumber(0, 9);
strRandomNumber += Convert.ToString(intRandomNumber);
if (strRandomNumber != strRandomSave)
{
strRandomSave = strRandomNumber;
break;
}
}
strSQL = "insert into travel_time (psite_id,biosum_plot_id,travel_mode,travel_time) VALUES ";
strSQL += "(" + p_ado.m_DataSet.Tables["processing_site"].Rows[y]["psite_id"].ToString()
+ ",'" + p_ado.m_DataSet.Tables["plot"].Rows[x]["biosum_plot_id"].ToString()
+ "',1" +
"," + strRandomNumber + ");";
p_ado.SqlNonQuery(p_conn, strSQL);
}
System.Windows.Forms.Application.DoEvents();
if (this.m_frmTherm.AbortProcess == true)
{
break;
}
}
int intStart = 1;
this.m_frmTherm.progressBar1.Maximum =
p_ado.m_DataSet.Tables["processing_site"].Rows.Count;
strRandomSave = "";
for (x = 0; x <= p_ado.m_DataSet.Tables["processing_site"].Rows.Count - 1; x++)
{
this.m_frmTherm.Increment(x + 1);
for (y = intStart; y <= p_ado.m_DataSet.Tables["processing_site"].Rows.Count - 1; y++)
{
strSQL = "";
if (p_ado.m_DataSet.Tables["processing_site"].Rows[y]["trancd"].ToString().Trim() == "2")
{
strSQL = "insert into travel_time (psite_id,railhead_id,travel_mode,travel_time) values ";
}
else if (p_ado.m_DataSet.Tables["processing_site"].Rows[y]["trancd"].ToString().Trim() == "3")
{
strSQL = "insert into travel_time (psite_id,collector_id,travel_mode,travel_time) values ";
}
if (strSQL.Trim().Length > 0)
{
for (;;)
{
intRandomNumber = p_utils.RandomNumber(1, 18);
strRandomNumber = Convert.ToString(intRandomNumber);
intRandomNumber = p_utils.RandomNumber(0, 9);
strRandomNumber += "." + Convert.ToString(intRandomNumber);
intRandomNumber = p_utils.RandomNumber(0, 9);
strRandomNumber += Convert.ToString(intRandomNumber);
if (strRandomNumber != strRandomSave)
{
strRandomSave = strRandomNumber;
break;
}
}
strSQL += "(" + p_ado.m_DataSet.Tables["processing_site"].Rows[x]["psite_id"].ToString()
+ "," + p_ado.m_DataSet.Tables["processing_site"].Rows[y]["psite_id"].ToString()
+ ",2" +
"," + strRandomNumber + ");";
p_ado.SqlNonQuery(p_conn, strSQL);
if (p_ado.m_intError != 0)
{
break;
}
}
}
if (p_ado.m_intError != 0)
{
break;
}
intStart++;
System.Windows.Forms.Application.DoEvents();
if (this.m_frmTherm.AbortProcess == true)
{
break;
}
}
if (p_ado.m_intError != 0)
{
}
else
{
MessageBox.Show("Finished Generating Travel Times");
}
this.m_frmTherm.Close();
this.m_frmTherm = null;
}
p_ado.m_DataSet.Clear();
p_ado.m_DataSet = null;
p_ado.m_OleDbDataAdapter.Dispose();
p_ado.m_OleDbDataAdapter = null;
p_conn.Close();
p_ado.m_OleDbConnection = null;
}
}
p_conn = null;
p_ado = null;
p_utils = null;
}
private void SetConditionLookupAndIDStrings()
{
string[] condCNs = m_strCondCNs.Split(',');
setBiosumCondCNs = new HashSet <string>();
setBiosumCondIds = new HashSet <string>();
setBiosumPlotIds = new HashSet <string>();
setPlotCNs = new HashSet <string>();
setTreeCNs = new HashSet <string>();
if (m_intError == 0 && !GetBooleanValue(m_frmTherm, "AbortProcess"))
{
SetThermValue(m_frmTherm.progressBar1, "Value", 20);
//Create table of condition CNs to join Plot/Cond/Tree
m_ado.SqlNonQuery(m_connTempMDBFile, "CREATE TABLE conds_to_delete (cn TEXT(34))");
m_ado.AddPrimaryKey(m_connTempMDBFile, "conds_to_delete", "conds_to_delete_pk", "cn");
foreach (string condCN in condCNs)
{
m_ado.SqlNonQuery(m_connTempMDBFile, "INSERT INTO conds_to_delete (cn) VALUES (" + condCN + ");");
}
m_ado.CreateDataSet(m_connTempMDBFile,
string.Format(
"SELECT c.cn, c.biosum_cond_id, t.cn " +
"FROM (({0} c INNER JOIN conds_to_delete ON c.cn=conds_to_delete.cn) " +
"INNER JOIN {1} p ON c.biosum_plot_id=p.biosum_plot_id) " +
"LEFT JOIN {2} t on c.biosum_cond_id=t.biosum_cond_id;",
m_strCondTable, m_strPlotTable, m_strTreeTable), "identifiers");
foreach (DataRow row in m_ado.m_DataSet.Tables["identifiers"].Rows)
{
setBiosumCondCNs.Add(String.Format("'{0}'", row[0]));
setBiosumCondIds.Add(String.Format("'{0}'", row[1]));
setTreeCNs.Add(String.Format("'{0}'", row[2]));
}
m_ado.CreateDataSet(m_connTempMDBFile,
String.Format(
"SELECT allconds.biosum_plot_id, allconds.cn " + //, allconds.cntConds, someconds.cntConds " +
"FROM (SELECT p.biosum_plot_id, p.cn, count(*) as cntConds FROM ({0} c INNER JOIN conds_to_delete ON c.cn=conds_to_delete.cn) INNER JOIN {1} p ON c.biosum_plot_id = p.biosum_plot_id WHERE c.cn IN ({2}) GROUP BY p.biosum_plot_id, p.cn) someconds " +
"RIGHT JOIN (SELECT p.biosum_plot_id, p.cn, count(*) as cntConds FROM {0} c INNER JOIN {1} p ON c.biosum_plot_id = p.biosum_plot_id GROUP BY p.biosum_plot_id, p.cn) allconds " +
"ON allconds.biosum_plot_id=someconds.biosum_plot_id WHERE allconds.cntConds=someconds.cntConds",
m_strCondTable, m_strPlotTable, m_strCondCNs), "plots_with_all_conds_deleted");
foreach (DataRow row in m_ado.m_DataSet.Tables["plots_with_all_conds_deleted"].Rows)
{
setBiosumPlotIds.Add(String.Format("'{0}'", row[0]));
setPlotCNs.Add(String.Format("'{0}'", row[1]));
}
m_intError = m_ado.m_intError;
}
m_dictIdentityColumnsToValues = new Dictionary <string, HashSet <string> >
{
{ "biosum_cond_id", setBiosumCondIds },
{ "StandID", setBiosumCondIds },
{ "Stand_ID", setBiosumCondIds },
{ "biosum_plot_id", setBiosumPlotIds },
{ "cnd_cn", setBiosumCondCNs },
{ "plt_cn", setPlotCNs },
{ "tre_cn", setTreeCNs },
};
}