private void SaveTallyBySampleGroup()
{
var db = DAL;
var sg_cn = SampleGroup_CN;
var hasTallyBySp = db.ExecuteScalar <int>("SELECT count(*) FROM CountTree WHERE SampleGroup_CN = @p1 AND TreeDefaultValue_CN IS NOT NULL;", sg_cn) > 0;
if (hasTallyBySp)
{
var numTrees = db.ExecuteScalar <int>("SELECT count(*) FROM Tree WHERE SampleGroup_CN = @p1;", sg_cn);
var treeCount = db.ExecuteScalar <int>("SELECT ifnull(sum(TreeCount), 0) FROM CountTree WHERE SampleGroup_CN = @p1 AND TreeDefaultValue_CN IS NOT NULL;", sg_cn);
if (numTrees == 0 && treeCount == 0)
{
db.Execute("DELETE FROM CountTree WHERE SampleGroup_CN = @p1 AND TreeDefaultValue_CN IS NOT NULL;", sg_cn);
}
else
{
// should not be possible. UI prevents changing tally type if trees exist.
throw new UserFacingException("Can not remove tally by species setup because of trees or tree counts exist");
}
}
TallyVM tally = DAL.From <TallyVM>()
.Where("Description = @p1 AND HotKey = @p2")
.Query(SgTallie.Description, SgTallie.Hotkey)
.FirstOrDefault();
if (tally == null)
{
tally = new TallyVM(DAL)
{
Description = SgTallie.Description,
Hotkey = SgTallie.Hotkey,
};
db.Insert(tally);
}
var countTreeExists = db.ExecuteScalar <int>("SELECT count(*) FROM CountTree WHERE SampleGroup_CN = @p1 AND TreeDefaultValue_CN IS NULL;", sg_cn) > 0;
if (countTreeExists)
{
db.Execute("UPDATE CountTree Set Tally_CN = @p1 WHERE SampleGroup_CN = @p2;", tally.Tally_CN, sg_cn);
}
else
{
var user = db.User;
db.Execute(
@"INSERT OR IGNORE INTO CountTree (CuttingUnit_CN, SampleGroup_CN, Tally_CN, CreatedBy)
Select cust.CuttingUnit_CN, sg.SampleGroup_CN, @p2, @p3 AS CreatedBy
From CuttingUnitStratum AS cust
JOIN Stratum AS st USING (Stratum_CN)
JOIN SampleGroup AS sg USING (Stratum_CN)
WHERE sg.SampleGroup_CN = @p1;", sg_cn, tally.Tally_CN, user);
}
}
private void SaveTallyBySpecies()
{
var db = DAL;
var sg_cn = SampleGroup_CN;
var hasTallyBySg = db.ExecuteScalar <int>("SELECT count(*) FROM CountTree WHERE SampleGroup_CN = @p1 AND TreeDefaultValue_CN IS NULL;", sg_cn) > 0;
if (hasTallyBySg)
{
var numTrees = db.ExecuteScalar <int>("SELECT count(*) FROM Tree WHERE SampleGroup_CN = @p1;", sg_cn);
var treeCount = db.ExecuteScalar <int>("SELECT ifnull(sum(TreeCount), 0) FROM CountTree WHERE SampleGroup_CN = @p1 AND TreeDefaultValue_CN IS NULL;", sg_cn);
if (numTrees == 0 && treeCount == 0)
{
db.Execute("DELETE FROM CountTree WHERE SampleGroup_CN = @p1 AND TreeDefaultValue_CN IS NULL;", sg_cn);
}
else
{
// should not be possible. UI prevents changing tally type if trees exist.
throw new UserFacingException("Can not remove tally by sample group setup because of trees or tree counts exist");
}
}
string user = db.User;
foreach (KeyValuePair <TreeDefaultValueDO, TallyVM> pair in Tallies)
{
var tally = pair.Value;
var persistedTally = DAL.From <TallyVM>()
.Where("Description = @p1 AND HotKey = @p2")
.Query(tally.Description, tally.Hotkey)
.FirstOrDefault();
if (persistedTally == null)
{
persistedTally = new TallyVM(db)
{
Description = tally.Description,
Hotkey = tally.Hotkey
};
db.Insert(persistedTally);
}
var tdv_cn = pair.Key.TreeDefaultValue_CN.Value;
var countTreeExists = db.ExecuteScalar <int>("SELECT count(*) FROM CountTree WHERE SampleGroup_CN = @p1 AND TreeDefaultValue_CN = @p2;", sg_cn, tdv_cn) > 0;
if (countTreeExists == false)
{
db.Execute(
@"INSERT OR IGNORE INTO CountTree (CuttingUnit_CN, SampleGroup_CN, TreeDefaultValue_CN, Tally_CN, CreatedBy)
Select cust.CuttingUnit_CN, sg.SampleGroup_CN, @p2, @p3, @p4 AS CreatedBy
From CuttingUnitStratum AS cust
JOIN Stratum AS st USING (Stratum_CN)
JOIN SampleGroup AS sg USING (Stratum_CN)
WHERE sg.SampleGroup_CN = @p1;", sg_cn, tdv_cn, persistedTally.Tally_CN, user);
}
else
{
db.Execute("UPDATE CountTree Set Tally_CN = @p1 WHERE SampleGroup_CN = @p2 AND TreeDefaultValue_CN = @p3;", persistedTally.Tally_CN, sg_cn, tdv_cn);
}
}
}
public void LoadTallieData()
{
if (this._tallieDataLoaded)
{
return;
} //we have already loaded this samplegroup before, don't reload it
this.TallyPopulations = new Dictionary <String, TallyPopulation>();
//initialize a tally entity for use with tally by sample group
TallyVM sgTally = DAL.From <TallyVM>()
.Join("CountTree", "USING (Tally_CN)")
.Where("SampleGroup_CN = @p1 AND ifnull(TreeDefaultValue_CN, 0) = 0")
.Query(SampleGroup_CN).FirstOrDefault();
TallyPopulation sgTallyPopulation = DAL.Query <TallyPopulation>("SELECT SampleGroup_CN, TreeDefaultValue_CN, Tally.HotKey as HotKey, Tally.Description as Description " +
"FROM CountTree " +
"JOIN Tally USING (Tally_CN) " +
"WHERE CountTree.Tally_CN = Tally.Tally_CN " +
"AND CountTree.SampleGroup_CN = @p1 " +
"AND ifnull(CountTree.TreeDefaultValue_CN, 0) = 0;", this.SampleGroup_CN).FirstOrDefault()
?? new TallyPopulation()
{
Description = Code
};
this.TallyPopulations.Add("", sgTallyPopulation);
if (sgTally == null)
{
sgTally = new TallyVM()
{
Description = Code
};
}
SgTallie = sgTally;
SgTallie.Validate();
//initialize a list of tallies for use with tally by species
this.Tallies = new Dictionary <TreeDefaultValueDO, TallyVM>();
foreach (var tdv in TreeDefaultValues)
{
TallyVM tally = DAL.From <TallyVM>()
.Join("CountTree", "USING (Tally_CN)")
.Where("SampleGroup_CN = @p1 AND TreeDefaultValue_CN = @p2")
.Query(this.SampleGroup_CN, tdv.TreeDefaultValue_CN)
.FirstOrDefault();
if (tally == null)
{
tally = new TallyVM()
{
Description = Code + "/" + tdv.Species + ((tdv.LiveDead == "D") ? "/D" : "")
};
}
tally.Validate();
this.Tallies.Add(tdv, tally);
TallyPopulation tallyPopulation = DAL.Query <TallyPopulation>("SELECT SampleGroup_CN, TreeDefaultValue_CN, tally.HotKey, tally.Description " +
"FROM CountTree " +
"JOIN Tally USING (Tally_CN) " +
"WHERE CountTree.Tally_CN = Tally.Tally_CN " +
"AND CountTree.SampleGroup_CN = @p1 " +
"AND CountTree.TreeDefaultValue_CN = @p2;", this.SampleGroup_CN, tdv.TreeDefaultValue_CN).FirstOrDefault()
?? new TallyPopulation()
{
Description = Code + "/" + tdv.Species + ((tdv.LiveDead == "D") ? "/D" : "")
};
if (!this.TallyPopulations.ContainsKey(tdv.Species))
{
this.TallyPopulations.Add(tdv.Species, tallyPopulation);
}
}
this._tallieDataLoaded = true;
}
private void SaveTallyBySpecies()
{
//this.Database.BeginTransaction();
try
{
if (!IsTallyModeLocked)
{
//remove any preexisting tally by sg entries
string command = "DELETE FROM CountTree WHERE SampleGroup_CN = ? AND ifnull(TreeDefaultValue_CN, 0) = 0;";
DAL.Execute(command, SampleGroup_CN);
string user = DAL.User;
String makeCountsCommand = String.Format(@"INSERT OR IGNORE INTO CountTree (CuttingUnit_CN, SampleGroup_CN, TreeDefaultValue_CN, CreatedBy)
Select CuttingUnitStratum.CuttingUnit_CN, SampleGroup.SampleGroup_CN, SampleGroupTreeDefaultValue.TreeDefaultValue_CN, '{0}' AS CreatedBy
From SampleGroup
INNER JOIN CuttingUnitStratum
ON SampleGroup.Stratum_CN = CuttingUnitStratum.Stratum_CN
INNER JOIN SampleGroupTreeDefaultValue
ON SampleGroupTreeDefaultValue.SampleGroup_CN = SampleGroup.SampleGroup_CN
WHERE SampleGroup.SampleGroup_CN = {1};",
user, SampleGroup_CN);
DAL.Execute(makeCountsCommand);
}
foreach (KeyValuePair<TreeDefaultValueDO, TallyVM> pair in Tallies)
{
TallyVM tally = DAL.From<TallyVM>()
.Where("Description = ? AND HotKey = ?")
.Query(pair.Value.Description, pair.Value.Hotkey)
.FirstOrDefault();
if (tally == null)
{
tally = new TallyVM(DAL) { Description = pair.Value.Description, Hotkey = pair.Value.Hotkey };
//tally = pair.Value;
//tally.DAL = Controller.Database;
tally.Save();
}
string setTallyCommand = String.Format("UPDATE CountTree Set Tally_CN = {0} WHERE SampleGroup_CN = {1} AND TreeDefaultValue_CN = {2}",
tally.Tally_CN, SampleGroup_CN, pair.Key.TreeDefaultValue_CN);
DAL.Execute(setTallyCommand);
}
//this.Database.EndTransaction();
}
catch (Exception)
{
//this.Database.CancelTransaction();
throw;
}
}
private void SaveTallyBySampleGroup()
{
//this.Database.BeginTransaction();
try
{
if (!IsTallyModeLocked)
{
//remove any possible tally by species records
string command = "DELETE FROM CountTree WHERE SampleGroup_CN = ? AND ifnull(TreeDefaultValue_CN, 0) != 0;";
DAL.Execute(command, SampleGroup_CN);
string user = DAL.User;
String makeCountsCommand = String.Format(@"INSERT OR Ignore INTO CountTree (CuttingUnit_CN, SampleGroup_CN, CreatedBy)
Select CuttingUnitStratum.CuttingUnit_CN, SampleGroup.SampleGroup_CN, '{0}' AS CreatedBy
From SampleGroup
INNER JOIN CuttingUnitStratum
ON SampleGroup.Stratum_CN = CuttingUnitStratum.Stratum_CN
WHERE SampleGroup.SampleGroup_CN = {1};", user, SampleGroup_CN);
DAL.Execute(makeCountsCommand);
}
TallyVM tally = DAL.From<TallyVM>()
.Where("Description = ? AND HotKey = ?")
.Query(SgTallie.Description, SgTallie.Hotkey)
.FirstOrDefault();
if (tally == null)
{
tally = new TallyVM(DAL)
{
Description = SgTallie.Description
,
Hotkey = SgTallie.Hotkey
};
//tally = sgVM.SgTallie;
tally.Save();
}
String setTallyCommand = String.Format("UPDATE CountTree Set Tally_CN = {0} WHERE SampleGroup_CN = {1};",
tally.Tally_CN, SampleGroup_CN);
DAL.Execute(setTallyCommand);
//this.Database.EndTransaction();
}
catch (Exception)
{
//this.Database.CancelTransaction();
throw;
}
}
public void LoadTallieData()
{
if (this._tallieDataLoaded) { return; }//we have already loaded this samplegroup before, don't reload it
this.TallyPopulations = new Dictionary<String, TallyPopulation>();
//initialize a tally entity for use with tally by sample group
TallyVM sgTally = DAL.From<TallyVM>()
.Join("CountTree", "USING (Tally_CN)")
.Where("SampleGroup_CN = ? AND ifnull(TreeDefaultValue_CN, 0) = 0")
.Query(SampleGroup_CN).FirstOrDefault();
TallyPopulation sgTallyPopulation = DAL.QuerySingleRecord<TallyPopulation>("SELECT SampleGroup_CN, TreeDefaultValue_CN, Tally.HotKey as HotKey, Tally.Description as Description " +
"FROM CountTree " +
"JOIN Tally USING (Tally_CN) " +
"WHERE CountTree.Tally_CN = Tally.Tally_CN " +
"AND CountTree.SampleGroup_CN = ? " +
"AND ifnull(CountTree.TreeDefaultValue_CN, 0) = 0;", this.SampleGroup_CN)
?? new TallyPopulation() { Description = Code };
this.TallyPopulations.Add("", sgTallyPopulation);
if (sgTally == null)
{
sgTally = new TallyVM() { Description = Code };
}
SgTallie = sgTally;
SgTallie.Validate();
//initialize a list of tallies for use with tally by species
this.Tallies = new Dictionary<TreeDefaultValueDO, TallyVM>();
foreach (var tdv in TreeDefaultValues)
{
TallyVM tally = DAL.From<TallyVM>()
.Join("CountTree", "USING (Tally_CN)")
.Where("SampleGroup_CN = ? AND TreeDefaultValue_CN = ?")
.Query(this.SampleGroup_CN, tdv.TreeDefaultValue_CN)
.FirstOrDefault();
if (tally == null)
{
tally = new TallyVM() { Description = Code + "/" + tdv.Species + ((tdv.LiveDead == "D") ? "/D" : "") };
}
tally.Validate();
this.Tallies.Add(tdv, tally);
TallyPopulation tallyPopulation = DAL.QuerySingleRecord<TallyPopulation>("SELECT SampleGroup_CN, TreeDefaultValue_CN, tally.HotKey, tally.Description " +
"FROM CountTree " +
"JOIN Tally USING (Tally_CN) " +
"WHERE CountTree.Tally_CN = Tally.Tally_CN " +
"AND CountTree.SampleGroup_CN = ? " +
"AND CountTree.TreeDefaultValue_CN = ?;", this.SampleGroup_CN, tdv.TreeDefaultValue_CN)
?? new TallyPopulation() { Description = Code + "/" + tdv.Species + ((tdv.LiveDead == "D") ? "/D" : "") };
if (!this.TallyPopulations.ContainsKey(tdv.Species))
{
this.TallyPopulations.Add(tdv.Species, tallyPopulation);
}
}
this._tallieDataLoaded = true;
}
private void SaveTallyBySampleGroup(TallySetupSampleGroup sgVM)
{
if ((sgVM.TallyMethod & TallyMode.Locked) != TallyMode.Locked)
{
//remove any possible tally by species records
string command = "DELETE FROM CountTree WHERE SampleGroup_CN = ? AND ifnull(TreeDefaultValue_CN, 0) != 0;";
this.Database.Execute(command, sgVM.SampleGroup_CN);
string user = this.Database.User;
String makeCountsCommand = String.Format(@"INSERT OR Ignore INTO CountTree (CuttingUnit_CN, SampleGroup_CN, CreatedBy)
Select CuttingUnitStratum.CuttingUnit_CN, SampleGroup.SampleGroup_CN, '{0}' AS CreatedBy
From SampleGroup
INNER JOIN CuttingUnitStratum
ON SampleGroup.Stratum_CN = CuttingUnitStratum.Stratum_CN
WHERE SampleGroup.SampleGroup_CN = {1};", user, sgVM.SampleGroup_CN);
this.Database.Execute(makeCountsCommand);
}
TallyVM tally = this.Database.From<TallyVM>()
.Where("Description = ? AND HotKey = ?")
.Query(sgVM.SgTallie.Description, sgVM.SgTallie.Hotkey).FirstOrDefault();
if (tally == null)
{
tally = new TallyVM(this.Database) { Description = sgVM.SgTallie.Description, Hotkey = sgVM.SgTallie.Hotkey };
//tally = sgVM.SgTallie;
tally.Save();
}
String setTallyCommand = String.Format("UPDATE CountTree Set Tally_CN = {0} WHERE SampleGroup_CN = {1};",
tally.Tally_CN, sgVM.SampleGroup_CN);
this.Database.Execute(setTallyCommand);
}
