private void saveprivate()
{
ado_data_access p_ado = new ado_data_access();
string strMDB = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.txtPersonal.Text.Trim() + "\\personal_project_links_and_notes.mdb";
string strConn = p_ado.getMDBConnString(strMDB, "admin", "");
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMDB + ";User Id=admin;Password=;";
string strSQL = "";
p_ado.OpenConnection(strConn);
if (p_ado.m_intError == 0)
{
try
{
string strNotes = this.txtNotesPersonal.Text;
strNotes = p_ado.FixString(strNotes, "'", "''");
if ((int)p_ado.getRecordCount(p_ado.m_OleDbConnection, "SELECT COUNT(*) FROM NOTES", "NOTES") > 0)
{
strSQL = "UPDATE NOTES SET NOTES = '" + strNotes + "'";
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL);
}
else
{
strSQL = "INSERT INTO NOTES (NOTES) VALUES ('" + strNotes + "')";
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL);
}
}
catch
{
}
}
p_ado = null;
}
public void DeleteScenario()
{
string strSQL = "Delete Scenario '" + this.txtScenarioId.Text + "' (Y/N)?";
DialogResult result = MessageBox.Show(strSQL, "Delete Scenario", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
switch (result)
{
case DialogResult.Yes:
break;
case DialogResult.No:
return;
}
string strScenarioPath = this.txtScenarioPath.Text;
string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
string strScenarioDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db";
string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; //((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectFile;
StringBuilder strFullPath = new StringBuilder(strScenarioDir);
strFullPath.Append("\\");
strFullPath.Append(strFile);
System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection();
ado_data_access p_ado = new ado_data_access();
string strConn = p_ado.getMDBConnString(strFullPath.ToString(), "admin", "");
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFullPath.ToString() + ";User Id=admin;Password=;";
strSQL = "DELETE * FROM scenario WHERE scenario_id = " + "'" + this.txtScenarioId.Text.Trim() + "'";
p_ado.SqlNonQuery(strConn, strSQL);
if (p_ado.m_intError == 0)
{
strSQL = "DELETE * FROM scenario_datasource WHERE scenario_id = " + "'" + this.txtScenarioId.Text.Trim() + "'";
p_ado.SqlNonQuery(strConn, strSQL);
}
if (p_ado.m_intError == 0)
{
strSQL = this.txtScenarioId.Text + " was successfully deleted from the scenario tables. Do you wish to delete the scenario file and directory (Y/N)?";
result = MessageBox.Show(strSQL, "Delete Scenario", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
switch (result)
{
case DialogResult.Yes:
strSQL = strScenarioPath + "\\" + strScenarioFile;
try
{
System.IO.Directory.Delete(strScenarioPath, true);
}
catch
{
}
break;
case DialogResult.No:
break;
}
}
p_ado = null;
this.ParentForm.Close();
}
public void UpdateDescription()
{
string strDesc = "";
FIA_Biosum_Manager.ado_data_access oAdo = new ado_data_access();
string strScenarioDBDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db";
string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
StringBuilder strScenarioFullPath = new StringBuilder(strScenarioDBDir);
strScenarioFullPath.Append("\\");
strScenarioFullPath.Append(strScenarioFile);
string strScenarioConn = oAdo.getMDBConnString(strScenarioFullPath.ToString(), "admin", "");
oAdo.OpenConnection(strScenarioConn);
if (this.txtDescription.Text.Trim().Length > 0)
{
strDesc = oAdo.FixString(this.txtDescription.Text.Trim(), "'", "''");
}
oAdo.m_strSQL = "UPDATE scenario SET description='" + strDesc.Trim() + "' WHERE TRIM(scenario_id)='" + this.txtScenarioId.Text.Trim() + "'";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL);
oAdo.m_OleDbConnection.Close();
while (oAdo.m_OleDbConnection.State != System.Data.ConnectionState.Closed)
{
oAdo.m_OleDbConnection.Close();
System.Threading.Thread.Sleep(1000);
}
oAdo = null;
}
public void SaveScenarioNotes()
{
ado_data_access p_ado = new ado_data_access();
System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection();
string strProjDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim();
string strScenarioDir = strProjDir + "\\" + ScenarioType + "\\db";
string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; //((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectFile;
StringBuilder strFullPath = new StringBuilder(strScenarioDir);
strFullPath.Append("\\");
strFullPath.Append(strFile);
string strNotes = this.txtNotes.Text;
string strSQL = "";
strNotes = p_ado.FixString(strNotes, "'", "''");
string strConn = p_ado.getMDBConnString(strFullPath.ToString(), "admin", "");
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFullPath.ToString() + ";User Id=admin;Password=;";
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
strSQL = "UPDATE scenario SET notes = '" +
strNotes +
"' WHERE trim(lcase(scenario_id)) = '" + ((frmOptimizerScenario)this.ParentForm).uc_scenario1.txtScenarioId.Text.Trim().ToLower() + "';";
}
else
{
strSQL = "UPDATE scenario SET notes = '" +
strNotes +
"' WHERE trim(lcase(scenario_id)) = '" + this.ReferenceProcessorScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower() + "';";
}
p_ado.SqlNonQuery(strConn, strSQL);
p_ado = null;
}
public void SaveScenarioNotes()
{
ado_data_access p_ado = null;
SQLite.ADO.DataMgr oDataMgr = null;
string strNotes = this.txtNotes.Text;
string strProjDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim();
string strScenarioDir = strProjDir + "\\" + ScenarioType + "\\db";
if ((ReferenceProcessorScenarioForm != null && !ReferenceProcessorScenarioForm.m_bUsingSqlite) ||
ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
p_ado = new ado_data_access();
strNotes = p_ado.FixString(strNotes, "'", "''");
string strSQL = "";
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
strSQL = "UPDATE scenario SET notes = '" +
strNotes +
"' WHERE trim(lcase(scenario_id)) = '" + ((frmOptimizerScenario)this.ParentForm).uc_scenario1.txtScenarioId.Text.Trim().ToLower() + "';";
}
else
{
strSQL = "UPDATE scenario SET notes = '" +
strNotes +
"' WHERE trim(lcase(scenario_id)) = '" + this.ReferenceProcessorScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower() + "';";
}
System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection();
string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
StringBuilder strFullPath = new StringBuilder(strScenarioDir);
strFullPath.Append("\\");
strFullPath.Append(strFile);
string strConn = p_ado.getMDBConnString(strFullPath.ToString(), "admin", "");
p_ado.SqlNonQuery(strConn, strSQL);
p_ado = null;
}
else
{
oDataMgr = new SQLite.ADO.DataMgr();
strNotes = oDataMgr.FixString(strNotes, "'", "''");
//@ToDo: Only support Processor at this time
string strSQL = "UPDATE scenario SET notes = '" +
strNotes +
"' WHERE trim(lower(scenario_id)) = '" + this.ReferenceProcessorScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower() + "';";
string strFile = "scenario_" + ScenarioType + "_rule_definitions.db";
StringBuilder strFullPath = new StringBuilder(strScenarioDir);
strFullPath.Append("\\");
strFullPath.Append(strFile);
string strConn = oDataMgr.GetConnectionString(strFullPath.ToString());
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(strConn))
{
conn.Open();
oDataMgr.SqlNonQuery(conn, strSQL);
}
oDataMgr = null;
}
}
public void savevalues()
{
ado_data_access oAdo = new ado_data_access();
string strScenarioId = this.ReferenceOptimizerScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower();
string strScenarioMDB =
frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" +
Tables.OptimizerScenarioRuleDefinitions.DefaultScenarioTableDbFile;
oAdo.OpenConnection(oAdo.getMDBConnString(strScenarioMDB, "", ""));
oAdo.m_strSQL = "DELETE FROM " + Tables.OptimizerScenarioRuleDefinitions.DefaultScenarioProcessorScenarioSelectTableName + " " +
"WHERE TRIM(UCASE(scenario_id)) = '" +
ReferenceOptimizerScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToUpper() + "';";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL);
if (lvProcessorScenario.CheckedItems.Count > 0)
{
string strColumnsList = "scenario_id,processor_scenario_id,FullDetailsYN";
string strValuesList = "";
strValuesList = "'" + ReferenceOptimizerScenarioForm.uc_scenario1.txtScenarioId.Text.Trim() + "',";
strValuesList = strValuesList + "'" + lvProcessorScenario.CheckedItems[0].SubItems[COL_SCENARIOID].Text.Trim() + "',";
if (this.chkFullDetails.Checked)
{
strValuesList = strValuesList + "'Y'";
}
else
{
strValuesList = strValuesList + "'N'";
}
oAdo.m_strSQL = "INSERT INTO " + Tables.OptimizerScenarioRuleDefinitions.DefaultScenarioProcessorScenarioSelectTableName + " " +
"(" + strColumnsList + ") " +
"VALUES " +
"(" + strValuesList + ")";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL);
}
oAdo.CloseConnection(oAdo.m_OleDbConnection);
}
private void Save()
{
string strPath = "";
string strDatasource = "";
string strTableType = "";
string strScenario = "";
ado_data_access oAdo = new ado_data_access();
oAdo.OpenConnection(oAdo.getMDBConnString(m_strRandomPathAndFile, "", ""));
for (int x = 0; x <= lvDatasources.Items.Count - 1; x++)
{
strDatasource = lvDatasources.Items[x].SubItems[COLUMN_DATASOURCE].Text.Trim();
strTableType = lvDatasources.Items[x].SubItems[COLUMN_TABLETYPE].Text.Trim();
strPath = lvDatasources.Items[x].SubItems[COLUMN_PATH].Text.Trim();
strScenario = lvDatasources.Items[x].SubItems[COLUMN_SCENARIO].Text.Trim();
oAdo.m_strSQL = "";
if (strDatasource == "Project")
{
oAdo.m_strSQL = "UPDATE project_datasource SET path = '" + strPath + "' WHERE TRIM(Table_Type) = '" + strTableType + "'";
}
else if (strDatasource == "TreatmentOptimizer" && strTableType == "NA")
{
oAdo.m_strSQL = "UPDATE optimizer_scenario SET path = '" + strPath + "' WHERE TRIM(scenario_id) = '" + strScenario + "'";
}
else if (strDatasource == "TreatmentOptimizer")
{
oAdo.m_strSQL = "UPDATE optimizer_scenario_datasource SET path = '" + strPath + "' WHERE TRIM(scenario_id) = '" + strScenario + "' AND TRIM(Table_Type) = '" + strTableType + "'";
}
else if (strDatasource == "Processor" && strTableType == "NA")
{
oAdo.m_strSQL = "UPDATE processor_scenario SET path = '" + strPath + "' WHERE TRIM(scenario_id) = '" + strScenario + "'";
}
else if (strDatasource == "Processor")
{
oAdo.m_strSQL = "UPDATE processor_scenario_datasource SET path = '" + strPath + "' WHERE TRIM(scenario_id) = '" + strScenario + "' AND TRIM(Table_Type) = '" + strTableType + "'";
}
if (oAdo.m_strSQL.Trim().Length > 0)
{
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL);
}
}
oAdo.CloseConnection(oAdo.m_OleDbConnection);
MessageBox.Show("Done", "FIA Biosum");
m_bSyncd = false;
}
public int savevalues()
{
int x = 0;
string strSQL = "";
ado_data_access p_ado = new ado_data_access();
p_ado.m_intError = 0;
try
{
for (x = 0; x <= this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows.Count - 1; x++)
{
if (m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[x]["last_tiebreak_rank"] != System.DBNull.Value &&
m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[x]["last_tiebreak_rank"].ToString().Trim().Length > 0)
{
strSQL = "UPDATE scenario_last_tiebreak_rank SET last_tiebreak_rank = " +
this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[x]["last_tiebreak_rank"] +
" WHERE TRIM(rxpackage) = '" + this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[x]["rxpackage"].ToString().Trim() + "';";
}
else
{
strSQL = "UPDATE scenario_last_tiebreak_rank SET last_tiebreak_rank = null " +
" WHERE TRIM(rxpackage) = '" + this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[x]["rxpackage"].ToString().Trim() + "';";
}
p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL);
if (p_ado.m_intError < 0)
{
break;
}
}
}
catch (Exception caught)
{
MessageBox.Show("Function: uc_optimizer_scenario_last_tiebreak_rank.savevalues ErrMsg:" + caught.Message + " Failed updating scenario_last_tiebreak_rank table with last tiebreak rank ratings");
}
x = p_ado.m_intError;
p_ado = null;
return(x);
}
public void savevalues()
{
int x;
string strSpcGrp = "";
string strDbhGrp = "";
string strMerchValue = "";
string strChipValue = "";
string strWoodBin = "";
string strSql = "";
SQLite.ADO.DataMgr oDataMgr = null;
if (!ReferenceProcessorScenarioForm.m_bUsingSqlite)
{
//
//DELETE THE CURRENT SCENARIO RECORDS
//
m_oAdo.m_strSQL = "DELETE FROM scenario_tree_species_diam_dollar_values " +
"WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
//
//DELETE THE WORK TABLE
//
if (m_oAdo.TableExist(m_oAdo.m_OleDbConnection, "spcgrp_dbhgrp"))
{
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, "DROP TABLE spcgrp_dbhgrp");
}
//
//CREATE AND POPULATE WORK TABLE
//
m_oAdo.m_strSQL = "CREATE TABLE spcgrp_dbhgrp (" +
"species_group INTEGER," +
"species_label CHAR(50)," +
"diam_group INTEGER," +
"diam_class CHAR(15))";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
foreach (ProcessorScenarioItem.SpcGroupItem objSpcGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupItem_Collection)
{
foreach (ProcessorScenarioItem.TreeDiamGroupsItem objDiamGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oTreeDiamGroupsItem_Collection)
{
// INITIALIZE RECORDS IN WORK TABLE
m_oAdo.m_strSQL = "INSERT INTO spcgrp_dbhgrp (species_group,species_label, diam_group, diam_class) " +
"VALUES (" + objSpcGroup.SpeciesGroup + ",'" + objSpcGroup.SpeciesGroupLabel + "'," +
objDiamGroup.DiamGroup + ",'" + objDiamGroup.DiamClass + "')";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
//
//INSERT SCENARIO RECORDS
//
m_oAdo.m_strSQL = "INSERT INTO scenario_tree_species_diam_dollar_values (scenario_id,species_group,diam_group) " +
"VALUES ('" + ScenarioId.Trim() + "'," + objSpcGroup.SpeciesGroup + "," +
objDiamGroup.DiamGroup + ")";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
}
}
}
else
{
oDataMgr = new SQLite.ADO.DataMgr();
string strScenarioDB =
frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() +
"\\processor\\" + Tables.ProcessorScenarioRuleDefinitions.DefaultSqliteDbFile;
oDataMgr.OpenConnection(oDataMgr.GetConnectionString(strScenarioDB));
if (oDataMgr.m_intError != 0)
{
m_intError = oDataMgr.m_intError;
m_strError = oDataMgr.m_strError;
oDataMgr = null;
return;
}
m_intError = 0;
m_strError = "";
//
//DELETE THE CURRENT SCENARIO RECORDS
//
oDataMgr.m_strSQL = "DELETE FROM scenario_tree_species_diam_dollar_values " +
"WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'";
oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL);
//
//DELETE THE WORK TABLE
//
if (oDataMgr.TableExist(oDataMgr.m_Connection, "spcgrp_dbhgrp"))
{
oDataMgr.SqlNonQuery(oDataMgr.m_Connection, "DROP TABLE spcgrp_dbhgrp");
}
//
//CREATE AND POPULATE WORK TABLE
//
oDataMgr.m_strSQL = "CREATE TABLE spcgrp_dbhgrp (" +
"species_group INTEGER," +
"species_label TEXT," +
"diam_group INTEGER," +
"diam_class TEXT)";
oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL);
foreach (ProcessorScenarioItem.SpcGroupItem objSpcGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupItem_Collection)
{
foreach (ProcessorScenarioItem.TreeDiamGroupsItem objDiamGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oTreeDiamGroupsItem_Collection)
{
// INITIALIZE RECORDS IN WORK TABLE
oDataMgr.m_strSQL = "INSERT INTO spcgrp_dbhgrp (species_group,species_label, diam_group, diam_class) " +
"VALUES (" + objSpcGroup.SpeciesGroup + ",'" + objSpcGroup.SpeciesGroupLabel + "'," +
objDiamGroup.DiamGroup + ",'" + objDiamGroup.DiamClass + "')";
oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL);
//
//INSERT SCENARIO RECORDS
//
oDataMgr.m_strSQL = "INSERT INTO scenario_tree_species_diam_dollar_values (scenario_id,species_group,diam_group) " +
"VALUES ('" + ScenarioId.Trim() + "'," + objSpcGroup.SpeciesGroup + "," +
objDiamGroup.DiamGroup + ")";
oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL);
}
}
}
//
//UPDATE SCENARIO RECORDS WITH MERCH AND CHIP VALUES
//
for (x = 0; x <= this.uc_processor_scenario_spc_dbh_group_value_collection1.Count - 1; x++)
{
strSpcGrp = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).SpeciesGroup.Trim();
strDbhGrp = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).DbhGroup.Trim();
strWoodBin = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).GetWoodBin();
strMerchValue = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).CubicFootDollarValue.Trim();
strMerchValue = strMerchValue.Replace("$", "");
strChipValue = this.txtChipValue.Text.Trim();
strChipValue = strChipValue.Replace("$", "");
if (!ReferenceProcessorScenarioForm.m_bUsingSqlite)
{
strSql = "UPDATE scenario_tree_species_diam_dollar_values a " +
"INNER JOIN spcgrp_dbhgrp b " +
"ON a.species_group=b.species_group AND " +
"a.diam_group=b.diam_group " +
"SET a.merch_value=" + strMerchValue + "," +
"a.chip_value=" + strChipValue + ", " +
"a.wood_bin='" + strWoodBin.Trim() + "' " +
"WHERE TRIM(a.scenario_id)='" + ScenarioId.Trim() + "' AND " +
"TRIM(b.species_label)='" + strSpcGrp + "' AND " +
"TRIM(b.diam_class)='" + strDbhGrp + "'";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, strSql);
}
else
{
strSql = "UPDATE scenario_tree_species_diam_dollar_values " +
"SET merch_value = " + strMerchValue + "," +
"chip_value = " + strChipValue + "," +
"wood_bin = '" + strWoodBin.Trim() + "' WHERE EXISTS (" +
"SELECT * FROM spcgrp_dbhgrp " +
"WHERE scenario_tree_species_diam_dollar_values.species_group = spcgrp_dbhgrp.species_group " +
"AND scenario_tree_species_diam_dollar_values.diam_group = spcgrp_dbhgrp.diam_group " +
"AND TRIM(scenario_tree_species_diam_dollar_values.scenario_id) = '" + ScenarioId.Trim() + "' " +
"AND TRIM(spcgrp_dbhgrp.species_label) = '" + strSpcGrp + "' " +
"AND TRIM(spcgrp_dbhgrp.diam_class) = '" + strDbhGrp + "')";
oDataMgr.SqlNonQuery(oDataMgr.m_Connection, strSql);
}
uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).SaveValues();
}
this.m_strChipValueSave = this.txtChipValue.Text;
if (ReferenceProcessorScenarioForm.m_bUsingSqlite)
{
//
//DELETE THE WORK TABLE AND CLOSE CONNECTION
//
if (oDataMgr.TableExist(oDataMgr.m_Connection, "spcgrp_dbhgrp"))
{
oDataMgr.SqlNonQuery(oDataMgr.m_Connection, "DROP TABLE spcgrp_dbhgrp");
}
m_intError = oDataMgr.m_intError;
oDataMgr.CloseConnection(oDataMgr.m_Connection);
oDataMgr = null;
}
}
public void SaveScenarioProperties()
{
string strTemp1;
string strTemp2;
string strSQL = "";
bool bCore = false;
string strDesc = "";
if (this.lstScenario.Visible == true) //new scenario
{
//validate the input
//Optimization id
if (this.txtScenarioId.Text.Length == 0)
{
MessageBox.Show("Enter A Unique Optimization scenario Id");
this.txtScenarioId.Focus();
return;
}
//check for duplicate scenario id
if (this.lstScenario.Items.Count > 0)
{
strTemp2 = this.txtScenarioId.Text.Trim();
for (int x = 0; x <= this.lstScenario.Items.Count - 1; x++)
{
strTemp1 = this.lstScenario.Items[x].ToString().Trim();
if (strTemp1.ToUpper() == strTemp2.ToUpper())
{
MessageBox.Show("Cannot have a duplicate Optimization scenario id");
this.txtScenarioId.Focus();
return;
}
}
}
//make sure user entered scenario path
if (this.txtScenarioPath.Text.Length > 0)
{
//create the scenario path if it does not exist and
//copy the scenario_results.mdb to it
try
{
if (!System.IO.Directory.Exists(this.txtScenarioPath.Text))
{
System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text);
System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text.ToString() + "\\db");
//copy default scenario_results database to the new project directory
string strSourceFile = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db\\scenario_results.mdb";
string strDestFile = this.txtScenarioPath.Text + "\\db\\scenario_results.mdb";
System.IO.File.Copy(strSourceFile, strDestFile, true);
}
}
catch
{
MessageBox.Show("Error Creating Folder");
return;
}
}
else
{
MessageBox.Show("Enter A Directory Location To Save Optimization scenario Files");
this.txtScenarioPath.Focus();
return;
}
//copy the project data source values to the scenario data source
ado_data_access p_ado = new ado_data_access();
string strProjDBDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\db";
string strProjFile = "project.mdb";
StringBuilder strProjFullPath = new StringBuilder(strProjDBDir);
strProjFullPath.Append("\\");
strProjFullPath.Append(strProjFile);
string strProjConn = p_ado.getMDBConnString(strProjFullPath.ToString(), "admin", "");
System.Data.OleDb.OleDbConnection p_OleDbProjConn = new System.Data.OleDb.OleDbConnection();
p_ado.OpenConnection(strProjConn, ref p_OleDbProjConn);
string strScenarioDBDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db";
string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
StringBuilder strScenarioFullPath = new StringBuilder(strScenarioDBDir);
strScenarioFullPath.Append("\\");
strScenarioFullPath.Append(strScenarioFile);
string strScenarioConn = p_ado.getMDBConnString(strScenarioFullPath.ToString(), "admin", "");
p_ado.OpenConnection(strScenarioConn);
if (p_ado.m_intError == 0)
{
if (this.txtDescription.Text.Trim().Length > 0)
{
strDesc = p_ado.FixString(this.txtDescription.Text.Trim(), "'", "''");
}
strSQL = "INSERT INTO scenario (scenario_id,description,Path,File) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strDesc + "'," +
"'" + this.txtScenarioPath.Text.Trim() + "','scenario_" + ScenarioType + "_rule_definitions.mdb');";
//"'" + this.txtScenarioMDBFile.Text.Trim() + "');";
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL);
p_ado.SqlQueryReader(p_OleDbProjConn, "select * from datasource");
if (p_ado.m_intError == 0)
{
try
{
while (p_ado.m_OleDbDataReader.Read())
{
bCore = false;
switch (p_ado.m_OleDbDataReader["table_type"].ToString().Trim().ToUpper())
{
case "PLOT":
bCore = true;
break;
case "CONDITION":
bCore = true;
break;
//case "FIRE AND FUEL EFFECTS":
// bCore = true;
// break;
case "HARVEST COSTS":
bCore = true;
break;
case "TREATMENT PRESCRIPTIONS":
bCore = true;
break;
case "TREE VOLUMES AND VALUES BY SPECIES AND DIAMETER GROUPS":
bCore = true;
break;
case "TRAVEL TIMES":
bCore = true;
break;
case "PROCESSING SITES":
bCore = true;
break;
case "TREE SPECIES AND DIAMETER GROUPS DOLLAR VALUES":
bCore = true;
break;
case "PLOT AND CONDITION RECORD AUDIT":
bCore = true;
break;
case "PLOT, CONDITION AND TREATMENT RECORD AUDIT":
bCore = true;
break;
default:
break;
}
if (bCore == true)
{
strSQL = "INSERT INTO scenario_datasource (scenario_id,table_type,Path,file,table_name) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + p_ado.m_OleDbDataReader["table_type"].ToString().Trim() + "'," +
"'" + p_ado.m_OleDbDataReader["path"].ToString().Trim() + "'," +
"'" + p_ado.m_OleDbDataReader["file"].ToString().Trim() + "'," +
"'" + p_ado.m_OleDbDataReader["table_name"].ToString().Trim() + "');";
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL);
}
}
}
catch (Exception caught)
{
intError = -1;
strError = caught.Message;
MessageBox.Show(strError);
}
if (p_ado.m_intError == 0)
{
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
((frmOptimizerScenario)ParentForm).uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim();
((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceMDBFile = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db\\scenario_" + ScenarioType + "_rule_definitions.mdb";
((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceTable = "scenario_datasource";
((frmOptimizerScenario)ParentForm).uc_datasource1.strProjectDirectory = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim();
}
else
{
this.ReferenceProcessorScenarioForm.uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim();
this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceMDBFile = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db\\scenario_" + ScenarioType + "_rule_definitions.mdb";
this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceTable = "scenario_datasource";
this.ReferenceProcessorScenarioForm.uc_datasource1.strProjectDirectory = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim();
}
}
p_ado.m_OleDbDataReader.Close();
p_ado.m_OleDbDataReader = null;
p_ado.m_OleDbCommand = null;
p_OleDbProjConn.Close();
p_OleDbProjConn = null;
}
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
string strTemp = p_ado.FixString("SELECT @@PlotTable@@.* FROM @@PlotTable@@ WHERE @@PlotTable@@.plot_accessible_yn='Y'", "'", "''");
strSQL = "INSERT INTO scenario_plot_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strTemp + "'," +
"'Y');";
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL);
strTemp = p_ado.FixString("SELECT @@CondTable@@.* FROM @@CondTable@@", "'", "''");
strSQL = "INSERT INTO scenario_cond_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strTemp + "'," +
"'Y');";
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL);
}
}
p_ado.m_OleDbConnection.Close();
p_ado.m_OleDbConnection = null;
p_ado = null;
this.btnCancel.Enabled = false;
this.btnOpen.Enabled = true;
this.lstScenario.Enabled = true;
this.txtScenarioId.Visible = false;
this.lblNewScenario.Visible = false;
this.txtScenarioPath.Enabled = false;
this.lstScenario.Items.Add(this.txtScenarioId.Text);
this.lstScenario.SelectedIndex = this.lstScenario.Items.Count - 1;
}
else
{
ado_data_access p_ado = new ado_data_access();
System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection();
string strProjDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory;
string strScenarioDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db";
string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
((frmOptimizerScenario)ParentForm).uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim();
((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceMDBFile = strScenarioDir + "\\scenario_" + ScenarioType + "_rule_definitions.mdb";
((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceTable = "scenario_datasource";
((frmOptimizerScenario)ParentForm).uc_datasource1.strProjectDirectory = strProjDir;
}
else
{
this.ReferenceProcessorScenarioForm.uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim();
this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceMDBFile = strScenarioDir + "\\scenario_" + ScenarioType + "_rule_definitions.mdb";
this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceTable = "scenario_datasource";
this.ReferenceProcessorScenarioForm.uc_datasource1.strProjectDirectory = strProjDir;
}
StringBuilder strFullPath = new StringBuilder(strScenarioDir);
strFullPath.Append("\\");
strFullPath.Append(strFile);
if (this.txtDescription.Text.Trim().Length > 0)
{
strDesc = p_ado.FixString(this.txtDescription.Text.Trim(), "'", "''");
}
string strConn = p_ado.getMDBConnString(strFullPath.ToString(), "admin", "");
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFullPath.ToString() + ";User Id=admin;Password=;";
strSQL = "UPDATE scenario SET description = '" +
strDesc +
"' WHERE scenario_id = '" + this.txtScenarioId.Text.ToLower() + "';";
p_ado.SqlNonQuery(strConn, strSQL);
p_ado = null;
}
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
if (((frmOptimizerScenario)this.ParentForm).m_bScenarioOpen == false)
{
((frmOptimizerScenario)this.ParentForm).Text = "Core Analysis: Optimization Scenario (" + this.txtScenarioId.Text.Trim() + ")";
((frmOptimizerScenario)this.ParentForm).SetMenu("scenario");
((frmOptimizerScenario)this.ParentForm).m_bScenarioOpen = true;
this.lblTitle.Text = "";
this.Visible = false;
}
}
else
{
if (this.ReferenceProcessorScenarioForm.m_bScenarioOpen == false)
{
this.ReferenceProcessorScenarioForm.Text = "Processor: Scenario (" + this.txtScenarioId.Text.Trim() + ")";
this.ReferenceProcessorScenarioForm.m_bScenarioOpen = true;
this.lblTitle.Text = "";
this.Visible = false;
}
}
}
public void loadgrid(bool p_bScenarioCopy)
{
string[] strDeleteSQL = new string[25];
string strSQL = "";
int intArrayCount;
int x = 0;
string strConn = "";
string strScenarioMDB = "";
ado_data_access p_ado = new ado_data_access();
this.strScenarioId = ReferenceOptimizerScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower();
/***************************************************
**scenario mdb connection
***************************************************/
if (p_ado.m_intError != 0)
{
return;
}
p_ado.getScenarioConnStringAndMDBFile(ref strScenarioMDB,
ref strConn,
frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim());
this.m_OleDbConnectionScenario = new System.Data.OleDb.OleDbConnection();
p_ado.OpenConnection(strConn, ref this.m_OleDbConnectionScenario);
/*************************************************************************
**get the treatment prescription mdb file,table, and connection strings
*************************************************************************/
p_ado.getScenarioDataSourceConnStringAndTable(ref this.strRxMDBFile,
ref this.strRxPackageTableName, ref this.strRxConn,
"Treatment Packages",
this.strScenarioId,
this.m_OleDbConnectionScenario);
this.m_OleDbRxConn = new System.Data.OleDb.OleDbConnection();
p_ado.OpenConnection(this.strRxConn, ref this.m_OleDbRxConn);
if (p_ado.m_intError != 0)
{
this.m_OleDbConnectionScenario.Close();
this.m_OleDbConnectionScenario = null;
this.m_OleDbRxConn = null;
return;
}
strSQL = "select * from " + this.strRxPackageTableName;
p_ado.SqlQueryReader(this.m_OleDbRxConn, strSQL);
/********************************************************************************
**insert records into the scenario_last_tiebreak_rank table from the master rxpackage table
********************************************************************************/
if (p_ado.m_intError == 0)
{
this.m_DataSet = new System.Data.DataSet();
this.m_OleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter();
while (p_ado.m_OleDbDataReader.Read())
{
strSQL = "select * from scenario_last_tiebreak_rank " +
" where scenario_id = '" + this.strScenarioId + "' and " +
"rxpackage = '" + p_ado.m_OleDbDataReader["rxpackage"].ToString() + "';";
this.m_OleDbCommand = this.m_OleDbConnectionScenario.CreateCommand();
this.m_OleDbCommand.CommandText = strSQL;
this.m_OleDbDataAdapter.SelectCommand = this.m_OleDbCommand;
this.m_OleDbDataAdapter.Fill(this.m_DataSet, "scenario_last_tiebreak_rank");
/*******************************************************************************
**if the master treatment record is not found in the scenario db than insert it
*******************************************************************************/
if (this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows.Count == 0)
{
strSQL = "INSERT INTO scenario_last_tiebreak_rank (scenario_id," +
"rxpackage, last_tiebreak_rank) VALUES " +
"('" + this.strScenarioId + "'," +
"'" + p_ado.m_OleDbDataReader["rxpackage"].ToString() + "'," +
"0);";
p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL);
}
this.m_DataSet.Tables.Clear();
}
p_ado.m_OleDbDataReader.Close();
this.m_DataSet.Dispose();
this.m_OleDbDataAdapter.Dispose();
intArrayCount = 0;
/****************************************************************************************************
**delete any prescriptions from the scenario db that do not exist in the master
****************************************************************************************************/
strSQL = "select * from scenario_last_tiebreak_rank where scenario_id = '" + this.strScenarioId + "';";
p_ado.SqlQueryReader(this.m_OleDbConnectionScenario, strSQL);
if (p_ado.m_intError == 0)
{
this.m_DataSet = new System.Data.DataSet();
this.m_OleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter();
while (p_ado.m_OleDbDataReader.Read())
{
/************************************************************************
**query the scenario treatment in the master db. If it is not found
**in the master db then delete the scenario treatment record
************************************************************************/
strSQL = "select * from " + this.strRxPackageTableName +
" where rxpackage = '" + p_ado.m_OleDbDataReader["rxpackage"].ToString() + "';";
this.m_OleDbCommand = this.m_OleDbRxConn.CreateCommand();
this.m_OleDbCommand.CommandText = strSQL;
this.m_OleDbDataAdapter.SelectCommand = this.m_OleDbCommand;
this.m_OleDbDataAdapter.Fill(this.m_DataSet, this.strRxPackageTableName);
if (this.m_DataSet.Tables[this.strRxPackageTableName].Rows.Count == 0)
{
strDeleteSQL[intArrayCount] = "DELETE FROM scenario_last_tiebreak_rank" +
" WHERE scenario_id = '" + this.strScenarioId + "'" +
" AND rxpackage = '" + p_ado.m_OleDbDataReader["rxpackage"] + "';";
intArrayCount++;
}
this.m_DataSet.Tables.Clear();
}
p_ado.m_OleDbDataReader.Close();
this.m_DataSet.Dispose();
this.m_OleDbDataAdapter.Dispose();
/**********************************************************************************
**if there were any treatments that were loaded into sql delete
**arrays then perform the sql to delete the treatments out of the table
**********************************************************************************/
if (intArrayCount > 0)
{
for (x = 0; x <= intArrayCount - 1; x++)
{
p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strDeleteSQL[x].ToString());
}
}
}
/***************************************************************************************
**okay, now that the table has been validated and updated lets load the grid to
**display the treatments to the user
***************************************************************************************/
this.m_DataSet = new System.Data.DataSet();
this.m_OleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter();
this.m_OleDbCommand = this.m_OleDbRxConn.CreateCommand();
this.m_OleDbCommand.CommandText = "select * from " + this.strRxPackageTableName;
this.m_OleDbDataAdapter.SelectCommand = this.m_OleDbCommand;
this.m_OleDbDataAdapter.Fill(this.m_DataSet, this.strRxPackageTableName);
this.m_OleDbCommand = this.m_OleDbConnectionScenario.CreateCommand();
strSQL = "";
for (x = 0; x <= this.m_DataSet.Tables[this.strRxPackageTableName].Rows.Count - 1; x++)
{
if (this.m_DataSet.Tables[this.strRxPackageTableName].Rows[x]["rxpackage"].ToString().Length > 0)
{
strSQL = "select scenario_id,rxpackage,last_tiebreak_rank from scenario_last_tiebreak_rank where scenario_id = '" + this.strScenarioId + "';";
break;
}
}
/************************************************************
**if no records in the master prescription table then return
************************************************************/
if (strSQL.Length == 0)
{
this.m_DataSet.Clear();
this.m_DataSet.Dispose();
this.m_OleDbDataAdapter.Dispose();
this.m_OleDbCommand.Dispose();
this.m_OleDbConnectionScenario.Close();
this.m_OleDbConnectionScenario = null;
this.m_OleDbRxConn.Close();
this.m_OleDbRxConn = null;
return;
}
/*******************************
**create the data sets
*******************************/
this.m_OleDbCommand = this.m_OleDbConnectionScenario.CreateCommand();
this.m_OleDbCommand.CommandText = "select scenario_id,rxpackage,last_tiebreak_rank from scenario_last_tiebreak_rank where scenario_id = '" + this.strScenarioId + "';";
this.m_OleDbDataAdapter.SelectCommand = this.m_OleDbCommand;
this.m_OleDbDataAdapter.Fill(this.m_DataSet, "scenario_last_tiebreak_rank");
/*****************************************************************************
**add the description column to the scenario last tiebreak rank dataset
*****************************************************************************/
this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Columns.Add("Description");
/********************************************************************************
**add the treatment description value to the scenrario rx last tiebreak rank data set.
**the description is only in the master rx table and is added to the
**scenario last tiebreak rank data set for information purposes.
********************************************************************************/
/***********************************************************************************
**for loop through the master db rx dataset adding the description field to the
**scenenario db scenario_last_tiebreak_rank dataset
***********************************************************************************/
for (x = 0; x <= this.m_DataSet.Tables[this.strRxPackageTableName].Rows.Count - 1; x++)
{
if (this.m_DataSet.Tables[this.strRxPackageTableName].Rows[x]["rxpackage"].ToString().Length > 0)
{
/***************************************************************************************
**build the expression to filter only the scenario row that meets the expression
***************************************************************************************/
strSQL = "rxpackage = '" + this.m_DataSet.Tables[this.strRxPackageTableName].Rows[x]["rxpackage"] + "'";
/***************************************************************************************
**create a datarow that will hold the results from the query expression
***************************************************************************************/
System.Data.DataRow[] dr1;
dr1 = this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Select(strSQL);
/***************************************************************************************
**check to see if it found the master rx treatment in the sceanrio last tiebreak rank dataset
***************************************************************************************/
if (dr1.Length != 0)
{
/***************************************************************************************
**it found it, loop through the dataset and find the row that matches the row
**returned from the search expression
***************************************************************************************/
for (int y = 0; y <= this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows.Count - 1; y++)
{
if (dr1[0]["rxpackage"] ==
this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[y]["rxpackage"])
{
/**********************************************************************************
**update the description row/column with the master db rx table description value
**********************************************************************************/
this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[y]["description"] =
this.m_DataSet.Tables[this.strRxPackageTableName].Rows[x]["description"];
break;
}
}
this.m_DataSet.Tables["scenario_last_tiebreak_rank"].AcceptChanges();
}
}
}
/**************************************************************************************************
**place the dataset table into a view class so as to not allow new records to be appended
**************************************************************************************************/
DataView firstView = new DataView(this.m_DataSet.Tables["scenario_last_tiebreak_rank"]);
firstView.AllowNew = false; //cannot append new records
firstView.AllowDelete = false; //cannot delete records
/***************************************************************
**custom define the grid style
***************************************************************/
DataGridTableStyle tableStyle = new DataGridTableStyle();
/***********************************************************************
**map the data grid table style to the last tiebreak rank dataset
***********************************************************************/
tableStyle.MappingName = "scenario_last_tiebreak_rank";
tableStyle.AlternatingBackColor = frmMain.g_oGridViewAlternateRowBackgroundColor;
tableStyle.BackColor = frmMain.g_oGridViewRowBackgroundColor;
tableStyle.ForeColor = frmMain.g_oGridViewRowForegroundColor;
tableStyle.SelectionBackColor = frmMain.g_oGridViewSelectedRowBackgroundColor;
/******************************************************************************
**since the dataset has things like field name and number of columns,
**we will use those to create new columnstyles for the columns in our grid
******************************************************************************/
//get the number of columns from the scenario_last_tiebreak_rank data set
int numCols = this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Columns.Count;
/***********************************************************************************
**assign the aColumnTextColumn as type DataGridColoredTextBoxColumn object class
***********************************************************************************/
DataGridColoredTextBoxColumn aColumnTextColumn;
//loop through all the columns in the dataset
for (int i = 0; i < numCols; ++i)
{
//create a new instance of the DataGridColoredTextBoxColumn class
aColumnTextColumn = new DataGridColoredTextBoxColumn();
aColumnTextColumn.HeaderText = this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Columns[i].ColumnName;
//all columns are read-only except the last_tiebreak_rank column
if (aColumnTextColumn.HeaderText != "last_tiebreak_rank")
{
aColumnTextColumn.ReadOnly = true;
}
//assign the mappingname property the data sets column name
aColumnTextColumn.MappingName = this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Columns[i].ColumnName;
//add the datagridcoloredtextboxcolumn object to the data grid table style object
tableStyle.GridColumnStyles.Add(aColumnTextColumn);
//set wider width for some columns
switch (aColumnTextColumn.HeaderText)
{
case "scenario_id":
aColumnTextColumn.Width = 150;
break;
case "Description":
aColumnTextColumn.Width = 475;
break;
}
}
dataGrid1.BackgroundColor = frmMain.g_oGridViewBackgroundColor;
dataGrid1.BackColor = frmMain.g_oGridViewRowBackgroundColor;
if (frmMain.g_oGridViewFont != null)
{
dataGrid1.Font = frmMain.g_oGridViewFont;
}
// make the dataGrid use our new tablestyle and bind it to our table
this.dataGrid1.TableStyles.Clear();
this.dataGrid1.TableStyles.Add(tableStyle);
// If this is a copied scenario, we will have a reference form to get the values
if (p_bScenarioCopy == true)
{
if (ReferenceOptimizerScenarioForm.m_oOptimizerScenarioItem_Collection.Item(0).m_oLastTieBreakRankItem_Collection != null)
{
OptimizerScenarioItem.LastTieBreakRankItem_Collection oLastTieBreakRankItem_Collection = ReferenceOptimizerScenarioForm.m_oOptimizerScenarioItem_Collection.Item(0).m_oLastTieBreakRankItem_Collection;
for (int i = 0; i < firstView.Count - 1; ++i)
{
for (x = 0; x <= oLastTieBreakRankItem_Collection.Count - 1; x++)
{
if (oLastTieBreakRankItem_Collection.Item(x).RxPackage.Equals(firstView[i]["rxpackage"]))
{
firstView[i]["last_tiebreak_rank"] = oLastTieBreakRankItem_Collection.Item(x).LastTieBreakRank;
}
}
}
}
}
this.dataGrid1.DataSource = firstView;
this.dataGrid1.Expand(-1);
}
}
public void saveTreeGroupings_FromProperties()
{
if (!ReferenceProcessorScenarioForm.m_bUsingSqlite)
{
ado_data_access _objAdo = new ado_data_access();
string strDbFile = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() +
"\\processor\\" + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsDbFile;
_objAdo.OpenConnection(_objAdo.getMDBConnString(strDbFile, "", ""));
if (_objAdo.m_intError == 0)
{
string strScenarioId = this.ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.ScenarioId;
//delete the current records
_objAdo.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName +
" WHERE TRIM(UCASE(scenario_id)) = '" + strScenarioId.Trim() + "'";
_objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL);
// saving tree diameter groups
if (_objAdo.m_intError == 0)
{
string strMin;
string strMax;
string strDef;
string strId;
for (int x = 0; x <= ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oTreeDiamGroupsItem_Collection.Count - 1; x++)
{
FIA_Biosum_Manager.ProcessorScenarioItem.TreeDiamGroupsItem oItem =
ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oTreeDiamGroupsItem_Collection.Item(x);
strId = oItem.DiamGroup;
strMin = oItem.MinDiam;
strMax = oItem.MaxDiam;
strDef = oItem.DiamClass;
_objAdo.m_strSQL = "INSERT INTO " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName + " " +
"(diam_group,diam_class,min_diam,max_diam,scenario_id) VALUES " +
"(" + strId + ",'" + strDef.Trim() + "'," +
strMin + "," + strMax + ",'" + strScenarioId.Trim() + "');";
_objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL);
if (_objAdo.m_intError != 0)
{
break;
}
}
}
// saving tree species groups
if (_objAdo.m_intError == 0)
{
string strCommonName;
int intSpCd;
int intSpcGrp;
string strGrpLabel;
int x;
//delete all records from the tree species group table
_objAdo.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeSpeciesGroupsTableName +
" WHERE TRIM(UCASE(scenario_id))='" + strScenarioId.Trim().ToUpper() + " '";
_objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL);
if (_objAdo.m_intError != 0)
{
return;
}
//delete all records from the tree species group list table
_objAdo.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeSpeciesGroupsListTableName +
" WHERE TRIM(UCASE(scenario_id))='" + strScenarioId.Trim().ToUpper() + " '";
_objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL);
if (_objAdo.m_intError == 0)
{
for (x = 0; x <= this.ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupItem_Collection.Count - 1; x++)
{
FIA_Biosum_Manager.ProcessorScenarioItem.SpcGroupItem oItem =
ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupItem_Collection.Item(x);
intSpcGrp = oItem.SpeciesGroup;
strGrpLabel = oItem.SpeciesGroupLabel;
_objAdo.m_strSQL = "INSERT INTO " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeSpeciesGroupsTableName + " " +
"(SPECIES_GROUP,SPECIES_LABEL,SCENARIO_ID) VALUES " +
"(" + Convert.ToString(intSpcGrp).Trim() + ",'" + strGrpLabel.Trim() + "','" + strScenarioId.Trim() + "');";
_objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL);
}
}
if (_objAdo.m_intError == 0)
{
for (x = 0; x <= this.ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupListItem_Collection.Count - 1; x++)
{
FIA_Biosum_Manager.ProcessorScenarioItem.SpcGroupListItem oItem =
ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupListItem_Collection.Item(x);
intSpcGrp = oItem.SpeciesGroup;
strCommonName = oItem.CommonName;
strCommonName = _objAdo.FixString(strCommonName.Trim(), "'", "''");
intSpCd = oItem.SpeciesCode;
_objAdo.m_strSQL = "INSERT INTO " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeSpeciesGroupsListTableName + " " +
"(SPECIES_GROUP,common_name,SCENARIO_ID,SPCD) VALUES " +
"(" + Convert.ToString(intSpcGrp).Trim() + ",'" + strCommonName + "','" + strScenarioId.Trim() + "', " +
intSpCd + " );";
_objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL);
}
}
}
ReferenceProcessorScenarioForm.m_bTreeGroupsCopied = false;
}
_objAdo.CloseConnection(_objAdo.m_OleDbConnection);
_objAdo = null;
}
else
{
saveTreeGroupings_FromPropertiesSqlite();
}
}
public void savevalues()
{
m_intError = 0;
m_strError = "";
int x;
string strValues = "";
string strFields = "scenario_id," +
"EscalatorOperatingCosts_Cycle2," +
"EscalatorOperatingCosts_Cycle3," +
"EscalatorOperatingCosts_Cycle4," +
"EscalatorMerchWoodRevenue_Cycle2," +
"EscalatorMerchWoodRevenue_Cycle3," +
"EscalatorMerchWoodRevenue_Cycle4," +
"EscalatorEnergyWoodRevenue_Cycle2," +
"EscalatorEnergyWoodRevenue_Cycle3," +
"EscalatorEnergyWoodRevenue_Cycle4";
try
{
//
//DELETE THE CURRENT SCENARIO RECORDS
//
m_oAdo.m_strSQL = "DELETE FROM scenario_cost_revenue_escalators " +
"WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
//
//scenario id
//
strValues = "'" + ScenarioId.Trim() + "',";
//
//Operating Cost Cycle1
//
strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle1.Trim() + ",";
//
//Operating Cost Cycle2
//
strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle2.Trim() + ",";
//
//Operating Cost Cycle3
//
strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle3.Trim() + ",";
//
//Merch Wood Revenue Cycle1
//
strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle1.Trim() + ",";
//
//Merch Wood Revenue Cycle2
//
strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle2.Trim() + ",";
//
//Merch Wood Revenue Cycle3
//
strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle3.Trim() + ",";
//
//Energy Wood Revenue Cycle1
//
strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle1.Trim() + ",";
//
//Energy Wood Revenue Cycle2
//
strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle2.Trim() + ",";
//
//Energy Wood Revenue Cycle3
//
strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle3.Trim();
m_oAdo.m_strSQL = Queries.GetInsertSQL(strFields, strValues, "scenario_cost_revenue_escalators");
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
this.uc_processor_scenario_escalators_value1.SaveValues();
this.uc_processor_scenario_escalators_value2.SaveValues();
this.uc_processor_scenario_escalators_value3.SaveValues();
}
catch (Exception e)
{
m_intError = -1;
m_strError = e.Message;
}
}
public void savevalues()
{
val_data();
if (this.m_intError == 0)
{
//string strSQL;
int x;
string strMin;
string strMax;
string strDef;
string strId;
if (!ReferenceProcessorScenarioForm.m_bUsingSqlite)
{
//
//OPEN CONNECTION TO DB FILE CONTAINING PROCESSOR SCENARIO TABLES
//
//scenario mdb connection
string strScenarioMDB =
frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() +
"\\processor\\db\\scenario_processor_rule_definitions.mdb";
ado_data_access oAdo = new ado_data_access();
oAdo.OpenConnection(oAdo.getMDBConnString(strScenarioMDB, "", ""));
if (oAdo.m_intError != 0)
{
m_intError = m_ado.m_intError;
m_strError = m_ado.m_strError;
oAdo = null;
return;
}
if (this.m_intError == 0)
{
//delete the current records
oAdo.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName +
" WHERE TRIM(UCASE(scenario_id)) = '" + ScenarioId.Trim().ToUpper() + "'";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL);
if (oAdo.m_intError == 0)
{
for (x = 0; x <= this.lstTreeDiam.Items.Count - 1; x++)
{
strId = this.lstTreeDiam.Items[x].Text;
strMin = this.lstTreeDiam.Items[x].SubItems[1].Text;
strMax = this.lstTreeDiam.Items[x].SubItems[2].Text;
strDef = this.lstTreeDiam.Items[x].SubItems[3].Text;
oAdo.m_strSQL = "INSERT INTO " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName + " " +
"(diam_group,diam_class,min_diam,max_diam,scenario_id) VALUES " +
"(" + strId + ",'" + strDef.Trim() + "'," +
strMin + "," + strMax + ",'" + ScenarioId + "');";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL);
if (oAdo.m_intError != 0)
{
break;
}
}
}
}
oAdo.CloseConnection(oAdo.m_OleDbConnection);
this.m_intError = oAdo.m_intError;
oAdo = null;
}
else
{
string strScenarioDB =
frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() +
"\\processor\\" + Tables.ProcessorScenarioRuleDefinitions.DefaultSqliteDbFile;
SQLite.ADO.DataMgr oDataMgr = new SQLite.ADO.DataMgr();
using (System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection(oDataMgr.GetConnectionString(strScenarioDB)))
{
conn.Open();
if (oDataMgr.m_intError != 0)
{
m_intError = oDataMgr.m_intError;
m_strError = oDataMgr.m_strError;
oDataMgr = null;
return;
}
if (this.m_intError == 0)
{
//delete the current records
oDataMgr.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName +
" WHERE TRIM(UPPER(scenario_id)) = '" + ScenarioId.Trim().ToUpper() + "'";
oDataMgr.SqlNonQuery(conn, oDataMgr.m_strSQL);
if (oDataMgr.m_intError == 0)
{
for (x = 0; x <= this.lstTreeDiam.Items.Count - 1; x++)
{
strId = this.lstTreeDiam.Items[x].Text;
strMin = this.lstTreeDiam.Items[x].SubItems[1].Text;
strMax = this.lstTreeDiam.Items[x].SubItems[2].Text;
strDef = this.lstTreeDiam.Items[x].SubItems[3].Text;
oDataMgr.m_strSQL = "INSERT INTO " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName + " " +
"(diam_group,diam_class,min_diam,max_diam,scenario_id) VALUES " +
"(" + strId + ",'" + strDef.Trim() + "'," +
strMin + "," + strMax + ",'" + ScenarioId + "');";
oDataMgr.SqlNonQuery(conn, oDataMgr.m_strSQL);
if (oDataMgr.m_intError != 0)
{
break;
}
}
this.m_intError = oDataMgr.m_intError;
oDataMgr = null;
}
}
}
}
if (this.m_intError == 0)
{
this.btnSave.Enabled = false;
}
}
}
public void DeleteRecords()
{
int row = 0;
int col = 0;
bool lprompt = false;
DialogResult result = DialogResult.None;
ado_data_access p_ado;
p_ado = new ado_data_access();
//check to see if there are any records to delete
for (row = 0; row <= this.listView1.Items.Count - 1; row++)
{
if (this.listView1.Items[row].Text == "*")
{
if (lprompt == false)
{
result = MessageBox.Show("Permenently delete those items marked for deletion? Y/N", "SQL Expressions", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
}
if (result == DialogResult.Yes || lprompt == true)
{
if (lprompt == false)
{
p_ado.OpenConnection(this.m_strCurrentConnection);
if (p_ado.m_intError != 0)
{
p_ado = null;
return;
}
lprompt = true;
}
System.Text.StringBuilder sb = new System.Text.StringBuilder();
string strSQL = "DELETE * FROM " + this.m_strTable +
" WHERE ";
sb.Append(strSQL);
for (col = 1; col <= this.listView1.Columns.Count - 1; col++)
{
if (this.m_strFieldTypeAString_YN[col - 1] == "Y")
{
if (this.listView1.Items[row].SubItems[col].Text.Trim().Length == 0)
{
strSQL = "(" + this.listView1.Columns[col].Text.Trim() + " IS NULL OR LEN(TRIM(" + this.listView1.Columns[col].Text.Trim() + ")) = 0)";
//if (strSQL.IndexOf("'",0) > 0) strSQL=p_ado.FixString(strSQL);
sb.Append(strSQL);
}
else
{
strSQL = "TRIM(UCASE(" + this.listView1.Columns[col].Text.Trim() + "))=";
sb.Append(strSQL);
sb.Append("'");
if (this.listView1.Items[row].SubItems[col].Text.IndexOf("'", 0) > 0)
{
strSQL = p_ado.FixString(this.listView1.Items[row].SubItems[col].Text, "'", "''");
sb.Append(strSQL);
}
else
{
strSQL = this.listView1.Items[row].SubItems[col].Text.Trim().ToUpper();
sb.Append(strSQL);
}
sb.Append("'");
}
}
else
{
if (this.listView1.Items[row].SubItems[col].Text.Trim().Length == 0)
{
strSQL = this.listView1.Columns[col].Text.Trim() + " IS NULL";
sb.Append(strSQL);
}
else
{
strSQL = this.listView1.Columns[col].Text.Trim() + "=";
sb.Append(strSQL);
strSQL = this.listView1.Items[row].SubItems[col].Text.Trim();
sb.Append(strSQL);
}
}
if (col < this.listView1.Columns.Count - 1)
{
strSQL = " AND ";
sb.Append(strSQL);
}
else
{
strSQL = ";";
sb.Append(strSQL);
}
}
if (lprompt == true)
{
p_ado.SqlNonQuery(p_ado.m_OleDbConnection, sb.ToString());
}
sb = null;
}
}
}
if (lprompt == true)
{
p_ado.m_OleDbConnection.Close();
p_ado.m_OleDbConnection = null;
}
p_ado = null;
}
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;
}
public void savevalues()
{
//
//OPEN CONNECTION TO DB FILE CONTAINING PROCESSOR SCENARIO TABLES
//
//scenario mdb connection
ado_data_access oAdo = new ado_data_access();
string strScenarioMDB =
frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() +
"\\processor\\db\\scenario_processor_rule_definitions.mdb";
oAdo.OpenConnection(oAdo.getMDBConnString(strScenarioMDB, "", ""));
if (oAdo.m_intError != 0)
{
m_intError = oAdo.m_intError;
m_strError = oAdo.m_strError;
oAdo = null;
return;
}
m_intError = 0;
m_strError = "";
string strFields = "scenario_id,yard_dist_threshold,assumed_harvest_area_ac," +
"move_in_time_multiplier," +
"move_in_hours_addend";
string strValues = "";
oAdo.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultMoveInCostsTableName + " " +
"WHERE TRIM(UCASE(scenario_id)) = '" + ScenarioId.Trim().ToUpper() + "'";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL);
//
//SCENARIOID
//
strValues = "'" + ScenarioId + "',";
//
//YARDING DISTANCE THRESHOLD
//
if (this.txtYardDistThreshold.Text.Trim().Length > 0)
{
strValues = strValues + this.txtYardDistThreshold.Text.Trim() + ",";
}
else
{
strValues = strValues + "null,";
}
//
//ASSUMED HARVEST AREA
//
if (this.txtAssumedHarvestArea.Text.Trim().Length > 0)
{
strValues = strValues + this.txtAssumedHarvestArea.Text.Trim() + ",";
}
else
{
strValues = strValues + "null,";
}
//
//MOVE IN TIME MULTIPLIER
//
if (this.txtMoveInTimeMultiplier.Text.Trim().Length > 0)
{
strValues = strValues + this.txtMoveInTimeMultiplier.Text.Trim() + ",";
}
else
{
strValues = strValues + "null,";
}
//
//MOVE IN TIME ADDEND
//
if (this.txtMoveInAddend.Text.Trim().Length > 0)
{
strValues = strValues + this.txtMoveInAddend.Text.Trim();
}
else
{
strValues = strValues + "null";
}
//
oAdo.m_strSQL = Queries.GetInsertSQL(strFields, strValues, Tables.ProcessorScenarioRuleDefinitions.DefaultMoveInCostsTableName);
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL);
m_intError = oAdo.m_intError;
oAdo.CloseConnection(oAdo.m_OleDbConnection);
oAdo = null;
}
public int savevalues()
{
int x = 0;
//string str="";
string strSQL = "";
string strConn = "";
string strRevPerGreenTon = "";
string strHaulCost;
string strRailHaulCost;
string strRailBioTransferCost;
string strRailMerchTransferCost;
//ldp strRevPerGreenTon = this.txtRevPerGreenTon_subclass.Text.Replace("$","");
//ldp strRevPerGreenTon = strRevPerGreenTon.Replace(",","");
//ldp if (strRevPerGreenTon.Trim().Length == 1) strRevPerGreenTon = "0.00";
//strHaulCost = this.txtHaulCost_subclass.Text.Replace("$","");
strHaulCost = RoadHaulCostDollarsPerGreenTonPerHour.Replace("$", "");
strHaulCost = strHaulCost.Replace(",", "");
if (strHaulCost.Trim().Length == 1)
{
strHaulCost = "0.00";
}
//ldp strRailHaulCost = this.txtRailHaulCost_subclass.Text.Replace("$","");
strRailHaulCost = RailHaulCostDollarsPerGreenTonPerMile.Replace("$", "");
strRailHaulCost = strRailHaulCost.Replace(",", "");
if (strRailHaulCost.Trim().Length == 1)
{
strRailHaulCost = "0.00";
}
//ldp strRailBioTransferCost = this.txtRailChipTransfer_subclass.Text.Replace("$","");
strRailBioTransferCost = RailChipTransferCostDollarsPerGreenTonPerHour.Replace("$", "");
strRailBioTransferCost = strRailBioTransferCost.Replace(",", "");
if (strRailBioTransferCost.Trim().Length == 1)
{
strRailBioTransferCost = "0.00";
}
//ldp strRailMerchTransferCost = this.txtRailMerchTransfer_subclass.Text.Replace("$","");
strRailMerchTransferCost = RailMerchTransferCostDollarsPerGreenTonPerHour.Replace("$", "");
strRailMerchTransferCost = strRailMerchTransferCost.Replace(",", "");
if (strRailMerchTransferCost.Trim().Length == 1)
{
strRailMerchTransferCost = "0.00";
}
ado_data_access p_ado = new ado_data_access();
string strScenarioId = this.ReferenceOptimizerScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower();
string strScenarioMDB =
frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" +
Tables.OptimizerScenarioRuleDefinitions.DefaultScenarioTableDbFile;
this.m_OleDbConnectionScenario = new System.Data.OleDb.OleDbConnection();
strConn = p_ado.getMDBConnString(strScenarioMDB, "admin", "");
//strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strScenarioMDB + ";User Id=admin;Password=;";
p_ado.OpenConnection(strConn, ref this.m_OleDbConnectionScenario);
if (p_ado.m_intError != 0)
{
x = p_ado.m_intError;
p_ado = null;
return(x);
}
//delete all records from the scenario wind speed class table
strSQL = "DELETE FROM scenario_costs WHERE " +
" scenario_id = '" + strScenarioId + "';";
p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL);
if (p_ado.m_intError < 0)
{
this.m_OleDbConnectionScenario.Close();
x = p_ado.m_intError;
p_ado = null;
return(x);
}
strSQL = "INSERT INTO scenario_costs (scenario_id,road_haul_cost_pgt_per_hour,rail_haul_cost_pgt_per_mile,rail_chip_transfer_pgt_per_hour,rail_merch_transfer_pgt_per_hour)" +
" VALUES ('" + strScenarioId + "'," +
strHaulCost + "," + strRailHaulCost + "," + strRailBioTransferCost + "," + strRailMerchTransferCost + ");";
p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL);
this.m_OleDbConnectionScenario.Close();
p_ado = null;
return(0);
}
public void savevalues()
{
int x;
string strSpcGrp = "";
string strDbhGrp = "";
string strMerchValue = "";
string strChipValue = "";
string strWoodBin = "";
string strFields = "scenario_id,species_group,diam_group,wood_bin,merch_value,chip_value";
string strValues = "";
//
//DELETE THE CURRENT SCENARIO RECORDS
//
m_oAdo.m_strSQL = "DELETE FROM scenario_tree_species_diam_dollar_values " +
"WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
//
//DELETE THE WORK TABLE
//
if (m_oAdo.TableExist(m_oAdo.m_OleDbConnection, "spcgrp_dbhgrp"))
{
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, "DROP TABLE spcgrp_dbhgrp");
}
//
//CREATE AND POPULATE WORK TABLE
//
m_oAdo.m_strSQL = "CREATE TABLE spcgrp_dbhgrp (" +
"species_group INTEGER," +
"species_label CHAR(50)," +
"diam_group INTEGER," +
"diam_class CHAR(15))";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
foreach (ProcessorScenarioItem.SpcGroupItem objSpcGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupItem_Collection)
{
foreach (ProcessorScenarioItem.TreeDiamGroupsItem objDiamGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oTreeDiamGroupsItem_Collection)
{
// INITIALIZE RECORDS IN WORK TABLE
m_oAdo.m_strSQL = "INSERT INTO spcgrp_dbhgrp (species_group,species_label, diam_group, diam_class) " +
"VALUES (" + objSpcGroup.SpeciesGroup + ",'" + objSpcGroup.SpeciesGroupLabel + "'," +
objDiamGroup.DiamGroup + ",'" + objDiamGroup.DiamClass + "')";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
//
//INSERT SCENARIO RECORDS
//
m_oAdo.m_strSQL = "INSERT INTO scenario_tree_species_diam_dollar_values (scenario_id,species_group,diam_group) " +
"VALUES ('" + ScenarioId.Trim() + "'," + objSpcGroup.SpeciesGroup + "," +
objDiamGroup.DiamGroup + ")";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
}
}
//
//UPDATE SCENARIO RECORDS WITH MERCH AND CHIP VALUES
//
for (x = 0; x <= this.uc_processor_scenario_spc_dbh_group_value_collection1.Count - 1; x++)
{
strValues = "";
strSpcGrp = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).SpeciesGroup.Trim();
strDbhGrp = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).DbhGroup.Trim();
strWoodBin = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).GetWoodBin();
strMerchValue = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).CubicFootDollarValue.Trim();
strMerchValue = strMerchValue.Replace("$", "");
strChipValue = this.txtChipValue.Text.Trim();
strChipValue = strChipValue.Replace("$", "");
m_oAdo.m_strSQL = "UPDATE scenario_tree_species_diam_dollar_values a " +
"INNER JOIN spcgrp_dbhgrp b " +
"ON a.species_group=b.species_group AND " +
"a.diam_group=b.diam_group " +
"SET a.merch_value=" + strMerchValue + "," +
"a.chip_value=" + strChipValue + ", " +
"a.wood_bin='" + strWoodBin.Trim() + "' " +
"WHERE TRIM(a.scenario_id)='" + ScenarioId.Trim() + "' AND " +
"TRIM(b.species_label)='" + strSpcGrp + "' AND " +
"TRIM(b.diam_class)='" + strDbhGrp + "'";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).SaveValues();
}
this.m_strChipValueSave = this.txtChipValue.Text;
}
/// <summary>
/// Delete all plot records and the plot's tree and condition records
/// </summary>
private void DeleteAllPlotRecords()
{
string strMsg = "All condition and tree records for the plots deleted will also be deleted. \n Are you sure you want to remove all plot data from the project?";
DialogResult result = MessageBox.Show(strMsg, "Delete All Plot Data", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
switch (result)
{
case DialogResult.Yes:
break;
case DialogResult.No:
return;
case DialogResult.Cancel:
return;
}
frmMain.g_oFrmMain.ActivateStandByAnimation(frmMain.g_oFrmMain.WindowState,
this.Left, this.Height, this.Width, this.Top);
//instantiate the ado data access class
FIA_Biosum_Manager.ado_data_access p_ado = new ado_data_access();
//instantiate the datasource class
FIA_Biosum_Manager.Datasource p_datasource = new Datasource(((frmDialog)this.ParentForm).m_frmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim());
string strSQL = "";
/********************************************************************
**have the data source class create an mdb file in the user's temp
**directory, create links to the plot,tree, and cond table and
**return the name of the mdb path and file
********************************************************************/
string strMDBFile = p_datasource.CreateMDBAndTableDataSourceLinks();
//have the ado data access class return the connection string
string strConn = p_ado.getMDBConnString(strMDBFile, "", "");
//get the location in the array for each of the table type information
int intTree = p_datasource.getValidTableNameRow("TREE");
int intPlot = p_datasource.getValidTableNameRow("PLOT");
int intCond = p_datasource.getValidTableNameRow("CONDITION");
int intPpsa = p_datasource.getValidTableNameRow("POPULATION PLOT STRATUM ASSIGNMENT");
int intTreeRegionalBiomass = p_datasource.getValidTableNameRow("TREE REGIONAL BIOMASS");
int intPopEval = p_datasource.getValidTableNameRow("POPULATION EVALUATION");
int intPopStratum = p_datasource.getValidTableNameRow("POPULATION STRATUM");
int intPopEstUnit = p_datasource.getValidTableNameRow("POPULATION ESTIMATION UNIT");
int intSiteTree = p_datasource.getValidTableNameRow("SITE TREE");
//check to see if we found all the table information
if (intTree >= 0 && intPlot >= 0 && intCond >= 0)
{
//check if we have tree regional biomass records
if (p_datasource.m_strDataSource[intTreeRegionalBiomass, RECORDCOUNT].Trim() != "0")
{
//delete the tree regional records that are related to a plot
strSQL = "DELETE FROM " +
p_datasource.m_strDataSource[intTreeRegionalBiomass, TABLE]; // + " tr " +
// "WHERE EXISTS (SELECT * FROM " + p_datasource.m_strDataSource[intCond,TABLE] + " c, " +
// p_datasource.m_strDataSource[intPlot,TABLE] + " p, " +
// p_datasource.m_strDataSource[intTree,TABLE] + " t " +
// "WHERE c.biosum_plot_id=p.biosum_plot_id AND " +
// "t.biosum_cond_id=c.biosum_cond_id AND " +
// "tr.tre_cn = t.cn);";
p_ado.SqlNonQuery(strConn, strSQL);
}
//check if we have site tree records
if (p_datasource.m_strDataSource[intSiteTree, RECORDCOUNT].Trim() != "0")
{
strSQL = "DELETE FROM " +
p_datasource.m_strDataSource[intSiteTree, TABLE];
p_ado.SqlNonQuery(strConn, strSQL);
}
//check if we have tree records
if (p_datasource.m_strDataSource[intTree, RECORDCOUNT].Trim() != "0")
{
//delete the tree records that are related to a plot
strSQL = "DELETE FROM " +
p_datasource.m_strDataSource[intTree, TABLE]; // + " t " +
// "WHERE EXISTS (SELECT * FROM " + p_datasource.m_strDataSource[intCond,TABLE] + " c " +
// " INNER JOIN " + p_datasource.m_strDataSource[intPlot,TABLE] + " p " +
// "ON c.biosum_plot_id = p.biosum_plot_id " +
// " WHERE t.biosum_cond_id = c.biosum_cond_id);";
p_ado.SqlNonQuery(strConn, strSQL);
}
//check if we have ppsa records
if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intPpsa, RECORDCOUNT].Trim() != "0")
{
//delete the ppsa records
strSQL = "DELETE FROM " +
p_datasource.m_strDataSource[intPpsa, TABLE];
p_ado.SqlNonQuery(strConn, strSQL);
}
//check if we have cond records
if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intCond, RECORDCOUNT].Trim() != "0")
{
//delete the cond records that are related to a plot
strSQL = "DELETE FROM " +
p_datasource.m_strDataSource[intCond, TABLE]; // + " c " +
// "WHERE EXISTS (SELECT * FROM " + p_datasource.m_strDataSource[intPlot,TABLE] + " p " +
// " WHERE c.biosum_plot_id = p.biosum_plot_id);";
p_ado.SqlNonQuery(strConn, strSQL);
}
//check if we have ploteval records
if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intPopEval, RECORDCOUNT].Trim() != "0")
{
//delete all the ploteval records
strSQL = "DELETE FROM " +
p_datasource.m_strDataSource[intPopEval, TABLE];
p_ado.SqlNonQuery(strConn, strSQL);
}
//check if we have pop est unit records
if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intPopEstUnit, RECORDCOUNT].Trim() != "0")
{
//delete all the ploteval records
strSQL = "DELETE FROM " +
p_datasource.m_strDataSource[intPopEstUnit, TABLE];
p_ado.SqlNonQuery(strConn, strSQL);
}
//check if we have pop est unit records
if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intPopStratum, RECORDCOUNT].Trim() != "0")
{
//delete all the ploteval records
strSQL = "DELETE FROM " +
p_datasource.m_strDataSource[intPopStratum, TABLE];
p_ado.SqlNonQuery(strConn, strSQL);
}
//check if we have plot records
if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intPlot, RECORDCOUNT].Trim() != "0")
{
//delete all the plot records
strSQL = "DELETE FROM " +
p_datasource.m_strDataSource[intPlot, TABLE];
p_ado.SqlNonQuery(strConn, strSQL);
}
frmMain.g_oFrmMain.DeactivateStandByAnimation();
MessageBox.Show("Done", "DELETE ALL PLOT DATA", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.None);
}
else
{
frmMain.g_oFrmMain.DeactivateStandByAnimation();
strSQL = "Delete Failed - Could not locate these designated tables:\n";
if (intPlot == -1)
{
strSQL += "Plot table\n";
}
if (intCond == -1)
{
strSQL += "Cond table\n";
}
if (intTree == -1)
{
strSQL += "Tree table\n";
}
MessageBox.Show(strSQL, "DELETE ALL PLOT DATA", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Exclamation);
}
}
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 },
};
}
public void SaveScenarioProperties()
{
bool bOptimizer;
string strDesc = "";
string strSQL = "";
System.Text.StringBuilder strFullPath;
m_intError = 0;
//validate the input
//
//Optimization id
//
if (this.txtScenarioId.Text.Length == 0)
{
MessageBox.Show("Enter A Unique Optimization scenario Id");
this.txtScenarioId.Focus();
m_intError = -1;
return;
}
//
//check for duplicate scenario id
//
System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection();
string strProjDir = frmMain.g_oFrmMain.getProjectDirectory();
string strScenarioDir = strProjDir + "\\" + ScenarioType + "\\db";
string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
strFullPath = new System.Text.StringBuilder(strScenarioDir);
strFullPath.Append("\\");
strFullPath.Append(strFile);
ado_data_access oAdo = new ado_data_access();
string strConn = oAdo.getMDBConnString(strFullPath.ToString(), "admin", "");
oAdo.SqlQueryReader(strConn, "select scenario_id from scenario");
if (oAdo.m_OleDbDataReader.HasRows)
{
while (oAdo.m_OleDbDataReader.Read())
{
if (oAdo.m_OleDbDataReader["scenario_id"] != System.DBNull.Value)
{
if (this.txtScenarioId.Text.Trim().ToUpper() ==
Convert.ToString(oAdo.m_OleDbDataReader["scenario_id"]).Trim().ToUpper())
{
this.m_intError = -1;
MessageBox.Show("Cannot have a duplicate Optimization scenario id");
oAdo.m_OleDbDataReader.Close();
oAdo.m_OleDbDataReader = null;
oAdo = null;
this.txtScenarioId.Focus();
return;
}
}
}
}
else
{
}
oAdo.m_OleDbDataReader.Close();
oAdo.m_OleDbDataReader = null;
//
//create the scenario path if it does not exist and
//copy the scenario_results.mdb to it
//
try
{
if (!System.IO.Directory.Exists(this.txtScenarioPath.Text))
{
System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text);
System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text.ToString() + "\\db");
//copy default processor scenario_results database to the new project directory
if (this.ScenarioType == "processor")
{
dao_data_access oDao = new dao_data_access();
string strDestFile = this.txtScenarioPath.Text + "\\" + Tables.ProcessorScenarioRun.DefaultHarvestCostsTableDbFile;
oDao.CreateMDB(strDestFile);
oDao.m_DaoWorkspace.Close();
oDao = null;
string strScenarioResultsConn = oAdo.getMDBConnString(strDestFile, "", "");
System.Data.OleDb.OleDbConnection OleDbScenarioResultsConn = new System.Data.OleDb.OleDbConnection();
oAdo.OpenConnection(strScenarioResultsConn, ref OleDbScenarioResultsConn);
frmMain.g_oTables.m_oProcessor.CreateHarvestCostsTable(
oAdo,
OleDbScenarioResultsConn,
Tables.ProcessorScenarioRun.DefaultHarvestCostsTableName);
frmMain.g_oTables.m_oProcessor.CreateTreeVolValSpeciesDiamGroupsTable(
oAdo,
OleDbScenarioResultsConn,
Tables.ProcessorScenarioRun.DefaultTreeVolValSpeciesDiamGroupsTableName);
OleDbScenarioResultsConn.Close();
OleDbScenarioResultsConn.Dispose();
}
}
}
catch
{
MessageBox.Show("Error Creating Folder");
m_intError = -1;
return;
}
//
//copy the project data source values to the scenario data source
//
string strProjDBDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\db";
string strProjFile = "project.mdb";
StringBuilder strProjFullPath = new StringBuilder(strProjDBDir);
strProjFullPath.Append("\\");
strProjFullPath.Append(strProjFile);
string strProjConn = oAdo.getMDBConnString(strProjFullPath.ToString(), "admin", "");
System.Data.OleDb.OleDbConnection p_OleDbProjConn = new System.Data.OleDb.OleDbConnection();
oAdo.OpenConnection(strProjConn, ref p_OleDbProjConn);
string strScenarioDBDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db";
string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb";
StringBuilder strScenarioFullPath = new StringBuilder(strScenarioDBDir);
strScenarioFullPath.Append("\\");
strScenarioFullPath.Append(strScenarioFile);
string strScenarioConn = oAdo.getMDBConnString(strScenarioFullPath.ToString(), "admin", "");
oAdo.OpenConnection(strScenarioConn);
if (oAdo.m_intError == 0)
{
if (this.txtDescription.Text.Trim().Length > 0)
{
strDesc = oAdo.FixString(this.txtDescription.Text.Trim(), "'", "''");
}
strSQL = "INSERT INTO scenario (scenario_id,description,Path,File) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strDesc + "'," +
"'" + this.txtScenarioPath.Text.Trim() + "','scenario_" + ScenarioType + "_rule_definitions.mdb');";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL);
oAdo.SqlQueryReader(p_OleDbProjConn, "select * from datasource");
if (oAdo.m_intError == 0)
{
try
{
while (oAdo.m_OleDbDataReader.Read())
{
bOptimizer = false;
switch (oAdo.m_OleDbDataReader["table_type"].ToString().Trim().ToUpper())
{
case "PLOT":
bOptimizer = true;
break;
case "CONDITION":
bOptimizer = true;
break;
//case "FIRE AND FUEL EFFECTS":
// bCore = true;
// break;
//case "HARVEST COSTS":
// bCore = true;
// break;
case "ADDITIONAL HARVEST COSTS":
bOptimizer = true;
break;
case "TREATMENT PRESCRIPTIONS":
bOptimizer = true;
break;
//case "TREE VOLUMES AND VALUES BY SPECIES AND DIAMETER GROUPS":
// bCore = true;
// break;
case "TRAVEL TIMES":
bOptimizer = true;
break;
case "PROCESSING SITES":
bOptimizer = true;
break;
//case "TREE SPECIES AND DIAMETER GROUPS DOLLAR VALUES":
// bCore = true;
// break;
case "PLOT AND CONDITION RECORD AUDIT":
if (ScenarioType == "optimizer")
{
bOptimizer = true;
}
break;
case "PLOT, CONDITION AND TREATMENT RECORD AUDIT":
if (ScenarioType == "optimizer")
{
bOptimizer = true;
}
break;
case "TREE":
if (ScenarioType == "processor")
{
bOptimizer = true;
}
break;
case "HARVEST METHODS":
if (ScenarioType == "processor")
{
bOptimizer = true;
}
break;
case "TREATMENT PACKAGES":
bOptimizer = true;
break;
//case "FVS TREE LIST FOR PROCESSOR":
// if (ScenarioType=="processor") bCore=true;
// break;
case "TREE SPECIES":
if (ScenarioType == "processor")
{
bOptimizer = true;
}
break;
case "TREATMENT PRESCRIPTIONS HARVEST COST COLUMNS":
if (ScenarioType == "processor")
{
bOptimizer = true;
}
break;
case "FIA TREE SPECIES REFERENCE":
if (ScenarioType == "processor")
{
bOptimizer = true;
}
break;
default:
break;
}
if (bOptimizer == true)
{
strSQL = "INSERT INTO scenario_datasource (scenario_id,table_type,Path,file,table_name) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + oAdo.m_OleDbDataReader["table_type"].ToString().Trim() + "'," +
"'" + oAdo.m_OleDbDataReader["path"].ToString().Trim() + "'," +
"'" + oAdo.m_OleDbDataReader["file"].ToString().Trim() + "'," +
"'" + oAdo.m_OleDbDataReader["table_name"].ToString().Trim() + "');";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL);
}
}
}
catch (Exception caught)
{
m_intError = -1;
m_strError = caught.Message;
MessageBox.Show(strError);
}
oAdo.m_OleDbDataReader.Close();
oAdo.m_OleDbDataReader = null;
oAdo.m_OleDbCommand = null;
p_OleDbProjConn.Close();
p_OleDbProjConn = null;
}
if (ScenarioType.Trim().ToUpper() == "OPTIMIZER")
{
string strTemp = oAdo.FixString("SELECT @@PlotTable@@.* FROM @@PlotTable@@ WHERE @@PlotTable@@.plot_accessible_yn='Y'", "'", "''");
strSQL = "INSERT INTO scenario_plot_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strTemp + "'," +
"'Y');";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL);
strTemp = oAdo.FixString("SELECT @@CondTable@@.* FROM @@CondTable@@", "'", "''");
strSQL = "INSERT INTO scenario_cond_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," +
"'" + strTemp + "'," +
"'Y');";
oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL);
}
}
oAdo.m_OleDbConnection.Close();
oAdo.m_OleDbConnection = null;
oAdo = null;
}
public int savevalues()
{
int x = 0;
//const int LANDOWNER_FORESTSERVICE = 10;
//const int LANDOWNER_OTHERFEDERAL = 20;
//const int LANDOWNER_STATELOCAL = 30;
//const int LANDOWNER_PRIVATE = 40;
//string str="";
string strSQL = "";
string strConn = "";
ado_data_access p_ado = new ado_data_access();
string strScenarioId = ((frmOptimizerScenario)this.ParentForm).uc_scenario1.txtScenarioId.Text.Trim().ToLower();
string strScenarioMDB =
((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" +
Tables.OptimizerScenarioRuleDefinitions.DefaultScenarioTableDbFile;
this.m_OleDbConnectionScenario = new System.Data.OleDb.OleDbConnection();
strConn = p_ado.getMDBConnString(strScenarioMDB, "admin", "");
//strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strScenarioMDB + ";User Id=admin;Password=;";
p_ado.OpenConnection(strConn, ref this.m_OleDbConnectionScenario);
if (p_ado.m_intError != 0)
{
x = p_ado.m_intError;
p_ado = null;
return(x);
}
//delete all records from the scenario wind speed class table
strSQL = "DELETE FROM scenario_land_owner_groups WHERE " +
" scenario_id = '" + strScenarioId + "';";
p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL);
if (p_ado.m_intError < 0)
{
this.m_OleDbConnectionScenario.Close();
x = p_ado.m_intError;
p_ado = null;
return(x);
}
if (this.chkOwnGrp10.Checked == true)
{
strSQL = "INSERT INTO scenario_land_owner_groups (scenario_id,owngrpcd)" +
" VALUES ('" + strScenarioId + "',10);";
p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL);
}
if (this.chkOwnGrp20.Checked == true)
{
strSQL = "INSERT INTO scenario_land_owner_groups (scenario_id,owngrpcd)" +
" VALUES ('" + strScenarioId + "',20);";
p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL);
}
if (this.chkOwnGrp30.Checked == true)
{
strSQL = "INSERT INTO scenario_land_owner_groups (scenario_id,owngrpcd)" +
" VALUES ('" + strScenarioId + "',30);";
p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL);
}
if (this.chkOwnGrp40.Checked == true)
{
strSQL = "INSERT INTO scenario_land_owner_groups (scenario_id,owngrpcd)" +
" VALUES ('" + strScenarioId + "',40);";
p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL);
}
this.m_OleDbConnectionScenario.Close();
p_ado = null;
return(0);
}
private void ExecuteDeleteOnTables(string strDbPathFile, string[] tables = null, string[] exceptions = null)
{
using (var conn = new OleDbConnection(m_ado.getMDBConnString(strDbPathFile, "", "")))
{
conn.Open();
string[] strTables = tables;
if (tables == null || tables.Length == 0)
{
strTables = m_ado.getTableNamesOfSpecificTypes(conn).Where(s => !(s.Contains("~") || s.Contains(" "))).ToArray();
//In case none of the tables are valid
if (strTables.Length == 1 && strTables[0] == "")
{
return;
}
}
string column;
foreach (string table in strTables)
{
if (exceptions != null && exceptions.Contains(table))
{
continue;
}
column = null;
foreach (string col in new string[] { "rxpackage" })
{
if (m_ado.ColumnExist(conn, table, col))
{
column = col;
break;
}
}
if (!String.IsNullOrEmpty(column))
{
var strTempIndex = column + "_delete_idx";
if (!m_dao.IndexExists(strDbPathFile, table, strTempIndex))
{
m_ado.AddIndex(conn, table, strTempIndex, column);
}
if (frmMain.g_bDebug)
{
frmMain.g_oUtils.WriteText(m_strDebugFile, Checked(chkDeletesDisabled)
? "\r\nCounting records to delete from " + strDbPathFile + " " + table + " using " + column + "\r\n"
: "\r\nDeleting from " + strDbPathFile + " " + table + " using " + column + "\r\n");
}
int deletedRecords = BuildAndExecuteDeleteSQLStmts(conn, table, column);
if (deletedRecords > 0)
{
AddDeletedCountToDictionary(strDbPathFile, table, deletedRecords);
}
m_ado.SqlNonQuery(conn, String.Format("DROP INDEX {0} ON {1}", strTempIndex, table));
}
}
}
if (Checked(chkCompactMDB))
{
m_dao.CompactMDB(strDbPathFile);
}
}
public void savevalues()
{
m_intError = 0;
m_strError = "";
string strValues = "";
string strFields = "scenario_id," +
"EscalatorOperatingCosts_Cycle2," +
"EscalatorOperatingCosts_Cycle3," +
"EscalatorOperatingCosts_Cycle4," +
"EscalatorMerchWoodRevenue_Cycle2," +
"EscalatorMerchWoodRevenue_Cycle3," +
"EscalatorMerchWoodRevenue_Cycle4," +
"EscalatorEnergyWoodRevenue_Cycle2," +
"EscalatorEnergyWoodRevenue_Cycle3," +
"EscalatorEnergyWoodRevenue_Cycle4";
try
{
SQLite.ADO.DataMgr oDataMgr = null;
//
//DELETE THE CURRENT SCENARIO RECORDS
//
if (!ReferenceProcessorScenarioForm.m_bUsingSqlite)
{
m_oAdo.m_strSQL = "DELETE FROM scenario_cost_revenue_escalators " +
"WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'";
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
}
else
{
oDataMgr = new SQLite.ADO.DataMgr();
string strScenarioDB =
frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() +
"\\processor\\" + Tables.ProcessorScenarioRuleDefinitions.DefaultSqliteDbFile;
oDataMgr.OpenConnection(oDataMgr.GetConnectionString(strScenarioDB));
if (oDataMgr.m_intError != 0)
{
m_intError = oDataMgr.m_intError;
m_strError = oDataMgr.m_strError;
oDataMgr = null;
return;
}
oDataMgr.m_strSQL = "DELETE FROM scenario_cost_revenue_escalators " +
"WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'";
oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL);
}
//
//scenario id
//
strValues = "'" + ScenarioId.Trim() + "',";
//
//Operating Cost Cycle1
//
strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle1.Trim() + ",";
//
//Operating Cost Cycle2
//
strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle2.Trim() + ",";
//
//Operating Cost Cycle3
//
strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle3.Trim() + ",";
//
//Merch Wood Revenue Cycle1
//
strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle1.Trim() + ",";
//
//Merch Wood Revenue Cycle2
//
strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle2.Trim() + ",";
//
//Merch Wood Revenue Cycle3
//
strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle3.Trim() + ",";
//
//Energy Wood Revenue Cycle1
//
strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle1.Trim() + ",";
//
//Energy Wood Revenue Cycle2
//
strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle2.Trim() + ",";
//
//Energy Wood Revenue Cycle3
//
strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle3.Trim();
if (!ReferenceProcessorScenarioForm.m_bUsingSqlite)
{
m_oAdo.m_strSQL = Queries.GetInsertSQL(strFields, strValues, "scenario_cost_revenue_escalators");
m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL);
}
else
{
oDataMgr.m_strSQL = Queries.GetInsertSQL(strFields, strValues, "scenario_cost_revenue_escalators");
oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL);
m_intError = oDataMgr.m_intError;
oDataMgr.CloseConnection(oDataMgr.m_Connection);
oDataMgr = null;
}
this.uc_processor_scenario_escalators_value1.SaveValues();
this.uc_processor_scenario_escalators_value2.SaveValues();
this.uc_processor_scenario_escalators_value3.SaveValues();
}
catch (Exception e)
{
m_intError = -1;
m_strError = e.Message;
}
}
