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