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); }
private void SaveTallyBySpecies(TallySetupSampleGroup sgVM) { if ((sgVM.TallyMethod & TallyMode.Locked) != TallyMode.Locked) { //remove any pre existing tally by sg entries 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, 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, sgVM.SampleGroup_CN); this.Database.Execute(makeCountsCommand); } foreach (KeyValuePair<TreeDefaultValueDO, TallyVM> pair in sgVM.Tallies) { TallyVM tally = this.Database.From<TallyVM>() .Where("Description = ? AND HotKey = ?") .Query(pair.Value.Description, pair.Value.Hotkey) .FirstOrDefault(); if (tally == null) { tally = new TallyVM(this.Database) { 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, sgVM.SampleGroup_CN, pair.Key.TreeDefaultValue_CN); this.Database.Execute(setTallyCommand); } }
private bool SaveTallies(TallySetupSampleGroup sgVM, ref StringBuilder errorBuilder) { try { this.Database.BeginTransaction(); this.Database.Execute( @"CREATE temp TRIGGER IgnoreConflictsOnCountTree BEFORE INSERT ON CountTree WHEN Exists ( SELECT 1 FROM CountTree WHERE CountTree.CuttingUnit_CN = new.CuttingUnit_CN AND CountTree.SampleGroup_CN = new.SampleGroup_CN AND ifnull(CountTree.TreeDefaultValue_CN, 0) = ifnull(new.TreeDefaultValue_CN, 0) AND ifnull(CountTree.Component_CN, 0) = ifnull(new.Component_CN, 0) ) BEGIN SELECT RAISE(IGNORE); END;" ); //if ((sgVM.TallyMethod & TallyMode.Locked) != TallyMode.Locked) //{ // string delCommand = String.Format("DELETE FROM CountTree WHERE SampleGroup_CN = {0}", sgVM.SampleGroup_CN); // Controller.Database.Execute(delCommand); //clead any existing count records. //} if ((sgVM.TallyMethod & TallyMode.BySampleGroup) == TallyMode.BySampleGroup) { SaveTallyBySampleGroup(sgVM); } else if ((sgVM.TallyMethod & TallyMode.BySpecies) == TallyMode.BySpecies) { SaveTallyBySpecies(sgVM); } this.Database.CommitTransaction(); sgVM.HasTallyEdits = false; return true; } catch (Exception) { this.Database.RollbackTransaction(); errorBuilder.AppendFormat("Error: failed to setup tallies for SampleGroup({0} ) in Stratum ({1})", sgVM.Code, sgVM.Stratum.Code); return false; } }