public static List <CatchLine> RetriveAllCatchFromTargetArea(string targetAreaGuid)
{
List <CatchLine> allCatch = new List <CatchLine>();
string sql = $@"SELECT DISTINCT tblCatchComp.NameType, temp_AllNames.Name1, temp_AllNames.Name2, tblCatchComp.NameGUID,
tblAllSpecies.TaxaNo, tblAllSpecies.ListedFB, tblAllSpecies.FBSpNo FROM tblAllSpecies
RIGHT JOIN (temp_AllNames
INNER JOIN (tblSampling
INNER JOIN tblCatchComp
ON tblSampling.SamplingGUID = tblCatchComp.SamplingGUID)
ON temp_AllNames.NameNo = tblCatchComp.NameGUID)
ON tblAllSpecies.SpeciesGUID = tblCatchComp.NameGUID
WHERE tblSampling.AOI = {{{targetAreaGuid}}}
ORDER BY tblCatchComp.NameType, temp_AllNames.Name1, temp_AllNames.Name2";
using (var conection = new OleDbConnection(global.ConnectionString))
{
DataTable dt = new DataTable();
var adapter = new OleDbDataAdapter(sql, conection);
adapter.Fill(dt);
Identification IdType = Identification.Scientific;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
int? taxaNo = dr["TaxaNo"].ToString().Length == 0 ?
taxaNo = null : (int?)dr["TaxaNo"];
int?fbNo = dr["FBSpNo"].ToString().Length == 0 ?
fbNo = null : (int?)dr["FBSpNo"];
IdType = (Identification)(int)dr["NameType"];
CatchLine cl = new CatchLine(dr["NameGUID"].ToString(),
dr["Name1"].ToString(),
dr["Name2"].ToString(),
IdType,
taxaNo,
fbNo,
(bool)dr["ListedFB"]
);
allCatch.Add(cl);
}
}
return(allCatch);
}
public static Dictionary <string, CatchLine> RetrieveCatchComposition(string SamplingGUID)
{
_CatchCompositionRows = 0;
Dictionary <string, CatchLine> myCatch = new Dictionary <string, CatchLine>();
DataTable dt = new DataTable();
string CatchName = "";
string Name1 = "";
string Name2 = "";
int? CatchCount = null;
int? TaxaNumber = null;
using (var conection = new OleDbConnection(global.ConnectionString))
{
try
{
conection.Open();
var query = $@"SELECT tblCatchComp.SamplingGUID, tblCatchComp.RowGUID, tblCatchComp.NameGUID, temp_AllNames.Name1, temp_AllNames.Name2, tblAllSpecies.TaxaNo,
temp_AllNames.Identification, tblCatchComp.Sequence, tblCatchComp.LFInterval, tblCatchDetail.RowGUID AS CatchDetailRow, tblCatchDetail.wt, tblCatchDetail.ct,
tblCatchDetail.swt, tblCatchDetail.sct, tblCatchDetail.FromTotal, tblCatchDetail.Live, tblCatchDetail.Notes FROM tblAllSpecies RIGHT JOIN
(temp_AllNames INNER JOIN (tblCatchComp INNER JOIN tblCatchDetail ON tblCatchComp.RowGUID = tblCatchDetail.CatchCompRow)
ON temp_AllNames.NameNo = tblCatchComp.NameGUID) ON tblAllSpecies.SpeciesGUID = tblCatchComp.NameGUID WHERE tblCatchComp.SamplingGUID={{{SamplingGUID}}}
ORDER BY tblCatchComp.Sequence";
var adapter = new OleDbDataAdapter(query, conection);
adapter.Fill(dt);
_TotalWtOfFromTotal = 0;
Identification IdType = Identification.Scientific;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
Name1 = dr["Name1"].ToString();
Name2 = dr["Name2"].ToString();
CatchName = $"{Name1} {Name2}";
IdType = Identification.Scientific;
if (dr["Identification"].ToString() == "Local names")
{
IdType = Identification.LocalName;
}
CatchCount = null;
if (dr["ct"].ToString().Length > 0)
{
if (int.TryParse(dr["ct"].ToString(), out int myCount))
{
CatchCount = myCount;
}
}
if (dr["TaxaNo"].ToString().Length > 0)
{
if (int.TryParse(dr["TaxaNo"].ToString(), out int myTaxaNo))
{
TaxaNumber = myTaxaNo;
}
else
{
TaxaNumber = null;
}
}
else
{
TaxaNumber = null;
}
//defines a catch line
var Sequence = 0;
if (int.TryParse(dr["Sequence"].ToString(), out int v))
{
Sequence = v;
}
CatchLine myLine = new CatchLine(Sequence, Name1, Name2, CatchName, dr["SamplingGUID"].ToString(),
dr["RowGUID"].ToString(), dr["NameGUID"].ToString(),
Convert.ToDouble(dr["wt"]), CatchCount, TaxaNumber)
{
CatchDetailRowGUID = dr["CatchDetailRow"].ToString(),
CatchSubsampleWt = null,
CatchSubsampleCount = null,
NameType = IdType,
dataStatus = fad3DataStatus.statusFromDB
};
if (dr["swt"] != DBNull.Value)
{
myLine.CatchSubsampleWt = Convert.ToDouble(dr["swt"]);
}
if (dr["sct"] != DBNull.Value)
{
myLine.CatchSubsampleCount = Convert.ToInt32(dr["sct"]);
}
myLine.FromTotalCatch = bool.Parse(dr["FromTotal"].ToString());
//add the catch composition row to the dictionary
myCatch.Add(dr["RowGUID"].ToString(), myLine);
if (dr["FromTotal"].ToString() == "True")
{
_TotalWtOfFromTotal += Convert.ToDouble(dr["wt"].ToString());
}
_CatchCompositionRows++;
}
}
catch (Exception ex)
{
Logger.Log(ex.Message, MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name);
}
}
return(myCatch);
}