public void PushTreeDefaultInserts(ComponentFileVM comp)
{
StartJob("Push TreeDefaultValue Inserts");
int? rowsAffected = Master.Execute("INSERT OR IGNORE INTO " + comp.DBAlias + ".TreeDefaultValue " +
"SELECT * FROM main.TreeDefaultValue;");
PostStatus(rowsAffected.GetValueOrDefault(0).ToString() + " Rows Affected");
EndJob();
}
public void PushNewStratumRecords(ComponentFileVM comp)
{
StartJob("Add New Strata");
int? rowsAffected = Master.Execute("INSERT OR IGNORE INTO " + comp.DBAlias + ".Stratum " +
"SELECT * FROM main.Stratum;");
PostStatus(rowsAffected.GetValueOrDefault(0).ToString() + " Rows Affected");
EndJob();
}
public void PushNewUnitRecords(ComponentFileVM comp)
{
StartJob("Add New Units");
int? rowsAffected = Master.Execute("INSERT OR IGNORE INTO " + comp.DBAlias + ".CuttingUnit " +
"SELECT * FROM main.CuttingUnit;");
EndJob();
}
public string SelectMissingMatches(ComponentFileVM comp)
{
return
"SELECT client." + this.ClientPrimaryKey.Name + " AS MatchRowID FROM " + this.ClientTableName + " AS client " +
"LEFT JOIN " + this.MergeTableName + " AS mrg " +
"ON (client." + this.ClientPrimaryKey.Name + " = mrg.MatchRowID AND ComponentID = " + comp.Component_CN.ToString() + ") " +
"WHERE mrg.MatchRowID IS NULL;";
}
private void PopulateMergeTables(ComponentFileVM comp)
{
MasterDB.AttachDB(comp.Database, "compDB"); //may throw exception
MasterDB.BeginTransaction();
try
{
foreach (MergeTableCommandBuilder cmdBldr in this.CommandBuilders.Values)
{
PopulateMergeTable(this.MasterDB, cmdBldr, comp);
}
//PopulateMergeTable(this.MasterDB, this.CommandBuilders["Tree"], comp);
//PopulateMergeTable(this.MasterDB, this.CommandBuilders["Log"], comp);
//PopulateMergeTable(this.MasterDB, this.CommandBuilders["Stem"], comp);
////PopulateMergeTable(this.MasterDB, this.CommandBuilders["TreeEstimate"], comp);
//PopulateMergeTable(this.MasterDB, this.CommandBuilders["Plot"], comp);
MasterDB.CommitTransaction();
this.NotifyProgressChanged(this._progressInCurrentJob++, false, "Imported Merge Info For Component #" + comp.Component_CN.ToString(), null);
}
catch
{
MasterDB.RollbackTransaction();
throw;
}
finally
{
MasterDB.DetachDB("compDB");
}
}
private void ResetRowVersion(ComponentFileVM comp, long masterRowID, long componentRowID, MergeTableCommandBuilder commBldr)
{
Master.Execute("UPDATE " + commBldr.ClientTableName + " SET RowVersion = 0 WHERE RowID = ?;", masterRowID);
ResetComponentRowVersion(comp, componentRowID, commBldr);
}
public String GetPopulateMergeTableCommand(ComponentFileVM component)
{
string clientFieldList = string.Empty;
if (this.ClientFieldNames.Length > 0)
{
clientFieldList = ", " + String.Join(", ", this.ClientFieldNames);
}
String populateMergetableCMD = String.Format(
"INSERT INTO {0} " +
"(ComponentRowID{1}, ComponentID{2}{3}, CompoundNaturalKey) " +
"SELECT {4}{5}" +
", {6} AS ComponentID{7}{3}, {9} " +
"FROM compDB.{8} as compSource;",
this.MergeTableName,
(this.HasGUIDKey) ? ", ComponentRowGUID" : String.Empty,
(this.HasRowVersion) ? ", ComponentRowVersion" : String.Empty,
clientFieldList,
this.ClientPrimaryKey.Name,
(this.HasGUIDKey) ? ", " + this.ClientGUIDFieldName : String.Empty,
component.Component_CN.ToString(),
(this.HasRowVersion) ? ", RowVersion AS ComponentRowVersion" : String.Empty,
this.ClientTableName,
this.GetCompoundNaturalKeyExpression("compSource"));
return populateMergetableCMD;
}
public void PullNewSampleGroupTreeDefaults(ComponentFileVM comp)
{
StartJob("Update Sample Group Species Mappings");
int? rowsAffected = Master.Execute("INSERT OR IGNORE INTO main.SampleGroupTreeDefaultValue " +
"SELECT * FROM " + comp.DBAlias + ".SampleGroupTreeDefaultValue;");
PostStatus(rowsAffected.GetValueOrDefault(0).ToString() + " Rows Affected");
EndJob();
}
public void PullNewTallyTable(ComponentFileVM comp)
{
StartJob("Add New CountTree Records");
List<TallyDO> compTallies = comp.Database.Read<TallyDO>("Tally", null);
foreach (TallyDO tally in compTallies)
{
CheckWorkerStatus();
TallyDO match = Master.ReadSingleRow<TallyDO>("Tally", "WHERE HotKey = ? AND Description = ?",
tally.Hotkey, tally.Description);
if (match == null)
{
match = new TallyDO(Master);
match.Hotkey = tally.Hotkey;
match.Description = tally.Description;
match.Save();
}
}
}
public void PullNew(MergeTableCommandBuilder cmdBldr, ComponentFileVM comp)
{
StartJob("Pull New From " + cmdBldr.ClientTableName);
List<MergeObject> mergeRecords = cmdBldr.ListNewRecords(Master, comp);
foreach (MergeObject mRec in mergeRecords)
{
CheckWorkerStatus();
DataObject newFromComp = cmdBldr.ReadSingleRow(comp.Database, mRec.ComponentRowID.Value);
Master.Insert(newFromComp, OnConflictOption.Fail);
this.ResetComponentRowVersion(comp, mRec.ComponentRowID.Value, cmdBldr);
}
EndJob();
}
public void PullNewSampleGroups(ComponentFileVM comp)
{
StartJob("Add New Sample Groups");
var compSGList = comp.Database.From<SampleGroupDO>().Query();
foreach (SampleGroupDO sg in compSGList)
{
SampleGroupDO match = Master.From<SampleGroupDO>()
.Where("Code = ? AND Stratum_CN = ?")
.Query(sg.Code, sg.Stratum_CN).FirstOrDefault();
if (match == null)
{
SampleGroupDO newSG = new SampleGroupDO(Master);
newSG.SuspendEvents();
newSG.SetValues(sg);
newSG.Stratum_CN = sg.Stratum_CN;
newSG.ResumeEvents();
newSG.Save();
match = newSG;
}
if (sg.SampleGroup_CN != match.SampleGroup_CN)
{
comp.Database.Execute("UPDATE SampleGroup SET SampleGroup_CN = ? WHERE SampleGroup_CN = ?;", match.SampleGroup_CN, sg.SampleGroup_CN);
}
}
EndJob();
}
public void PullMasterTreeUpdates(ComponentFileVM comp)
{
StartJob("Update Master Trees");
MergeTableCommandBuilder cmdBldr = this.CommandBuilders["Tree"];
List<MergeObject> pullList = cmdBldr.ListMasterUpdates(Master, comp);
foreach (MergeObject mRec in pullList)
{
CheckWorkerStatus();
long matchRowid = mRec.MatchRowID.Value;
TreeDO tree = comp.Database.From<TreeDO>().Where("rowid = ?")
.Query(mRec.ComponentRowID).FirstOrDefault();
Master.Update(tree, matchRowid, OnConflictOption.Fail);
this.ResetRowVersion(comp, matchRowid, mRec.ComponentRowID.Value, cmdBldr);
IncrementProgress();
}
EndJob();
}
public void PullMasterPlotUpdates(ComponentFileVM comp)
{
StartJob("Update Master Plots");
MergeTableCommandBuilder cmdBldr = this.CommandBuilders["Plot"];
List<MergeObject> pullList = cmdBldr.ListMasterUpdates(Master, comp);
foreach (MergeObject mRec in pullList)
{
CheckWorkerStatus();
long matchRowid = mRec.MatchRowID.Value;
PlotDO plot = comp.Database.ReadSingleRow<PlotDO>("Plot", mRec.ComponentRowID);
Master.Update(plot, matchRowid, OnConflictOption.Fail);
this.ResetRowVersion(comp, matchRowid, mRec.ComponentRowID.Value, cmdBldr);
IncrementProgress();
}
EndJob();
}
public void PullCountTreeChanges(ComponentFileVM comp)
{
StartJob("Add New CountTree Records");
var compCounts = comp.Database.From<CountTreeDO>().Query();
foreach (CountTreeDO count in compCounts)
{
CheckWorkerStatus();
CountTreeDO match = Master.From<CountTreeDO>()
.Where("SampleGroup_CN = ? " +
"AND ifnull(TreeDefaultValue_CN, 0) = ifnull(?, 0) " +
"AND CuttingUnit_CN = ? " +
"AND Component_CN = ?")
.Query(count.SampleGroup_CN,
count.TreeDefaultValue_CN,
count.CuttingUnit_CN,
comp.Component_CN).FirstOrDefault();
//use component cn from component record because component cn is not set when record is created by FScruiser
if (match != null)
{
match.TreeCount = count.TreeCount;
match.SumKPI = count.SumKPI;
match.Save();
}
else
{
TallyDO tally = count.Tally;
TallyDO masterTally = Master.From<TallyDO>().Where("HotKey = ?")
.Query(tally.Hotkey).FirstOrDefault();
if (masterTally == null)
{
//TODO unsupported
}
else
{
count.Tally_CN = masterTally.Tally_CN;
}
if (count.Component_CN == null)
{
count.Component_CN = comp.Component_CN;
//Master.Execute("UPDATE " + comp.DBAlias + ".CountTree Set Component_CN = ? WHERE CountTree_CN = ?;", comp.Component_CN, count.CountTree_CN);
}
Master.Insert(count, null, OnConflictOption.Fail);
}
}
EndJob();
}
public void PushUnitStratumChanges(ComponentFileVM comp)
{
StartJob("Update Component Unit Strata Mappings");
int? rowsAffected = Master.Execute("DELETE FROM " + comp.DBAlias + ".CuttingUnitStratum; " +
"INSERT OR IGNORE INTO " + comp.DBAlias + ".CuttingUnitStratum " +
"SELECT * FROM main.CuttingUnitStratum;");
PostStatus(rowsAffected.GetValueOrDefault(0).ToString() + " Rows Affected");
EndJob();
}
public void PullNewTreeRecords(ComponentFileVM comp)
{
StartJob("Add New Trees");
MergeTableCommandBuilder cmdBldr = this.CommandBuilders["Tree"];
List<MergeObject> mergeRecords = cmdBldr.ListNewRecords(Master, comp);
foreach (MergeObject mRec in mergeRecords)
{
CheckWorkerStatus();
TreeDO tree = comp.Database.From<TreeDO>()
.Where("rowid = ?").Query(mRec.ComponentRowID).FirstOrDefault();
Master.Insert(tree, OnConflictOption.Fail);
this.ResetComponentRowVersion(comp, mRec.ComponentRowID.Value, cmdBldr);
IncrementProgress();
}
EndJob();
}
private void ResetComponentRowVersion(ComponentFileVM comp, long componentRowID, MergeTableCommandBuilder commBldr)
{
comp.Database.Execute("UPDATE " + commBldr.ClientTableName + " SET RowVersion = 0 WHERE RowID = ?;", componentRowID);
}
public void PullTreeDefaultInserts(ComponentFileVM comp)
{
StartJob("Pull TreeDefault Inserts");
var compTreeDefaults = comp.Database.From<TreeDefaultValueDO>().Query();
foreach (TreeDefaultValueDO tdv in compTreeDefaults)
{
CheckWorkerStatus();
bool hasMatch = 0 < Master.GetRowCount("TreeDefaultValue",
"WHERE Species = ? AND PrimaryProduct = ? AND LiveDead = ?",
tdv.Species, tdv.PrimaryProduct, tdv.LiveDead);
if (!hasMatch)
{
if (Master.GetRowCount("TreeDefaultValue", "WHERE TreeDefaultValue_CN = ?", tdv.TreeDefaultValue_CN) == 0)
{
Master.Insert(tdv, OnConflictOption.Fail);
}
else
{
throw new NotImplementedException("TreeDefaultValue row conflict condition not implemented");
//Master.Insert(tdv, false, OnConflictOption.Fail);
//tdv.Save();
}
}
}
EndJob();
}
public String GetPopulateDeletedRecordsCommand(ComponentFileVM component)
{
String populateDeletedRecordsCMD = String.Format(
"INSERT INTO {0} "
+ "(ComponentRowID{1}, IsDeleted, ComponentID) "
+ "SELECT RecordID{2}, 1 AS IsDeleted, {3} AS ComponentID FROM Util_Tombstone WHERE TableName = '{4}';",
this.MergeTableName,
(this.HasGUIDKey) ? ", ComponentRowGUID" : String.Empty,
(this.HasGUIDKey) ? ", RecordGUID" : String.Empty,
component.Component_CN,
this.ClientTableName);
return populateDeletedRecordsCMD;
}
public void PushComponentStemUpdates(ComponentFileVM comp)
{
StartJob("Update Component Stems");
MergeTableCommandBuilder cmdBldr = this.CommandBuilders["Stem"];
List<MergeObject> pushList = cmdBldr.ListComponentUpdates(Master, comp);
foreach (MergeObject mRec in pushList)
{
CheckWorkerStatus();
long matchRowid = mRec.MatchRowID.Value;
StemDO stem = Master.ReadSingleRow<StemDO>("Stem", matchRowid);
comp.Database.Update(stem, mRec.ComponentRowID.Value, OnConflictOption.Fail);
this.ResetRowVersion(comp, matchRowid, mRec.ComponentRowID.Value, cmdBldr);
IncrementProgress();
}
EndJob();
}
public List<MergeObject> ListNewRecords(DAL master, ComponentFileVM comp)
{
string selectNewRecordsCommand =
"SELECT * FROM " + this.MergeTableName +
this.FindNewRecords +
" AND ComponentID = ?;";
return master.Query<MergeObject>(selectNewRecordsCommand, comp.Component_CN);
}
public void PushComponentTreeUpdates(ComponentFileVM comp)
{
StartJob("Update Component Trees");
MergeTableCommandBuilder cmdBldr = this.CommandBuilders["Tree"];
List<MergeObject> pushList = cmdBldr.ListComponentUpdates(Master, comp);
foreach (MergeObject mRec in pushList)
{
CheckWorkerStatus();
long matchRowid = mRec.MatchRowID.Value;
TreeDO tree = Master.ReadSingleRow<TreeDO>("Tree", matchRowid);
//TODO need to handle condition where MasterRowID is different from ComponentRowID
comp.Database.Update(tree, mRec.ComponentRowID.Value, OnConflictOption.Fail);
this.ResetRowVersion(comp, matchRowid, mRec.ComponentRowID.Value, cmdBldr);
IncrementProgress();
}
EndJob();
}
private bool InitializeComponent(ComponentFileVM comp)
{
comp.ResetCounts();
if (!System.IO.File.Exists(comp.FullPath))
{
comp.Errors = "File not found";
return false;
}
try
{
comp.Database = new DAL(comp.FullPath);
if (UpdateMasterWorker.HasUnassignedGUIDs(comp.Database))
{
UpdateMasterWorker.AssignGuids(comp.Database);
}
String[] errors;
if (comp.Database.HasCruiseErrors(out errors))
{
comp.Errors += string.Join("\r\n", errors);
}
return true;
}
catch (Exception e)
{
comp.Errors += e.Message;
return false;
}
}
private void PopulateMergeTable(DAL masterDB, MergeTableCommandBuilder table, ComponentFileVM comp)
{
CheckWorkerStatus();
masterDB.Execute(table.GetPopulateMergeTableCommand(comp));
masterDB.Execute(table.GetPopulateDeletedRecordsCommand(comp));
}