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