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