public override void TableRowSaving(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// 1) on type ID-CHECK require cooperator link
// GC check if cno is filled on an ID-CHECK
if (helper.AllFieldsExist("annotation_type_code", "annotation_cooperator_id") && helper.GetValue("annotation_type_code", "", true).ToString().ToUpper() == "ID-CHECK" && helper.IsValueEmpty("annotation_cooperator_id"))
{
args.Cancel("Cooperator must be supplied for ID-CHECK.");
}
// 2) Null out order number if type NOM-CHANGE
if (helper.AllFieldsExist("annotation_type_code", "order_request_id") && helper.GetValue("annotation_type_code", "", true).ToString().ToUpper() == "NOM-CHANGE" && !helper.IsValueEmpty("order_request_id"))
{
helper.SetValue("order_request_id", DBNull.Value, typeof(int), false);
}
// 3) set oldtaxno to current acc species name when type = 'Re-IDENT' and oldtaxno is null
if (helper.AllFieldsExist("annotation_type_code", "old_taxonomy_species_id") && helper.GetValue("annotation_type_code", "", true).ToString().ToUpper() == "RE-IDENT" && helper.IsValueEmpty("old_taxonomy_species_id"))
{
int ivid = (int)helper.GetValue("inventory_id", 0, true);
var dtAcc = args.ReadData(@"SELECT taxonomy_species_id FROM accession a INNER JOIN inventory i ON a.accession_id = i.accession_id WHERE inventory_id = :ivid",
":ivid", ivid, DbType.Int32);
if (dtAcc.Rows.Count > 0)
{
helper.SetValueIfFieldExistsAndIsEmpty("old_taxonomy_species_id", dtAcc.Rows[0]["taxonomy_species_id"]);
}
}
//4) newtaxno must be a valid taxon on RE-IDENT action
if (helper.AllFieldsExist("annotation_type_code", "new_taxonomy_species_id") &&
helper.GetValue("annotation_type_code", "", true).ToString().ToUpper() == "RE-IDENT" &&
!helper.IsValueEmpty("new_taxonomy_species_id"))
{
int tsid = (int)helper.GetValue("new_taxonomy_species_id", 0, true);
var dtTax = args.ReadData(@"SELECT current_taxonomy_species_id, synonym_code FROM taxonomy_species WHERE taxonomy_species_id = :tsid AND taxonomy_species_id = current_taxonomy_species_id",
":tsid", tsid, DbType.Int32);
if (dtTax.Rows.Count < 1)
{
args.Cancel("New taxonomy must be valid for RE-IDENT.");
}
}
// 5) on type ID-CHECK set oldtaxno to current acc taxon
if (helper.AllFieldsExist("annotation_type_code", "old_taxonomy_species_id") &&
helper.GetValue("annotation_type_code", "", true).ToString().ToUpper() == "ID-CHECK")
{
int ivid = (int)helper.GetValue("inventory_id", 0, true);
var dtAcc = args.ReadData(@"SELECT taxonomy_species_id FROM accession a INNER JOIN inventory i ON a.accession_id = i.accession_id WHERE inventory_id = :ivid",
":ivid", ivid, DbType.Int32);
if (dtAcc.Rows.Count > 0)
{
helper.SetValueIfFieldExistsAndIsEmpty("old_taxonomy_species_id", dtAcc.Rows[0]["taxonomy_species_id"]);
}
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// 2) require both lat and long if either
if (helper.AllFieldsExist("latitude", "longitude"))
{
//args.Cancel("both logitude and latitude exist");
if (!helper.IsValueEmpty("latitude") && helper.IsValueEmpty("longitude"))
{
args.Cancel("must have logitude if latitude entered");
}
if (helper.IsValueEmpty("latitude") && !helper.IsValueEmpty("longitude"))
{
args.Cancel("must have latitude if logitude entered");
}
}
if (helper.AllFieldsExist("geography_id") && !helper.IsValueEmpty("geography_id"))
{
int gid = (int)helper.GetValue("geography_id", 0, true);
var dtGeo = args.ReadData(@"SELECT current_geography_id, country_code FROM geography WHERE geography_id = :gid", ":gid", gid, DbType.Int32);
if (dtGeo.Rows.Count < 1)
{
args.Cancel("Geography could not be validated.");
}
else
{
int currentid = (int)dtGeo.Rows[0]["current_geography_id"];
string iso3 = dtGeo.Rows[0]["country_code"].ToString();
// Use current geo
if (gid != currentid)
{
helper.SetValue("geography_id", currentid, typeof(int), false);
dtGeo = args.ReadData(@"SELECT current_geography_id, country_code FROM geography WHERE geography_id = :gid", ":gid", gid, DbType.Int32);
if (dtGeo.Rows.Count < 1)
{
args.Cancel("Current geography could not be validated.");
}
}
// 1) Only allow link to valid geo
int num;
if (int.TryParse(iso3, out num))
{
args.Cancel("Cannot use invalid geography with a numeric contry code");
}
}
}
}
}
private int getSystemMaintenancePolicyID(ISaveDataTriggerArgs args)
{
var cm = CacheManager.Get("InventoryMaintPolicy");
var sysPolicyID = Toolkit.ToInt32(cm["sys_ID"], -1);
if (sysPolicyID < 1)
{
var dt = args.ReadData("select inventory_maint_policy_id from inventory_maint_policy where maintenance_name = 'SYSTEM'");
if (dt.Rows.Count == 0)
{
var sysCoopID = getSystemCooperatorID(args);
// the "SYSTEM" maint policy does not exist. create it.
sysPolicyID = args.WriteData(@"
insert into
inventory_maint_policy
(maintenance_name, form_type_code, is_auto_deducted, distribution_default_form_code, created_date, created_by, owned_date, owned_by)
values
('SYSTEM', '**', 'N', '**', :now1, :who1, :now2, :who2)
", true, "inventory_maint_policy_id", ":now1", DateTime.UtcNow, DbType.DateTime2, ":who1", sysCoopID, DbType.Int32, ":now2", DateTime.UtcNow, DbType.DateTime2, ":who2", sysCoopID, DbType.Int32);
// throw new InvalidOperationException("There is no record in the inventory_maint_policy table with maintenance_name = 'SYSTEM'. This record is required by the GRIN-Global middle tier to function properly.");
}
else
{
sysPolicyID = Toolkit.ToInt32(dt.Rows[0]["inventory_maint_policy_id"], -1);
}
cm["sys_ID"] = sysPolicyID;
}
return(sysPolicyID);
}
public void TableRowSaving(ISaveDataTriggerArgs args)
{
if (args.Table != null && args.Table.TableName.ToLower() == "accession")
{
if (args.SaveMode == SaveMode.Delete)
{
// if the only record left is the default inventory association record, we need to auto-delete it before the accession is deleted.
var h = args.Helper;
var accessionID = (int)(h.GetOriginalValue("accession_id", -1));
DataTable dt = args.ReadData("select form_type_code, inventory_id from inventory where accession_id = :accessionid",
new DataParameters(":accessionid", accessionID, DbType.Int32));
int inventoryIdToDelete = -1;
if (dt.Rows.Count == 1)
{
if (dt.Rows[0]["form_type_code", DataRowVersion.Original].ToString() == "**")
{
inventoryIdToDelete = Toolkit.ToInt32(dt.Rows[0]["inventory_id", DataRowVersion.Original], -1);
}
}
if (inventoryIdToDelete > -1)
{
// only 1 inventory record exists and it's a '**' (the default record we initially auto-created when the accession was created).
// try to delete it (if there are other records which are pointing at it, this will bomb, which is exactly what we want)
args.WriteData("delete from inventory where inventory_id = :inventoryid", new DataParameters(":inventoryid", inventoryIdToDelete, DbType.Int32));
}
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
// pre-row save trigger
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
int tsid = (int)helper.GetValue("taxonomy_species_id", 0, true);
var dtTax = args.ReadData(@"SELECT current_taxonomy_species_id, synonym_code, life_form_code FROM taxonomy_species WHERE taxonomy_species_id = :tsid",
":tsid", tsid, DbType.Int32);
if (dtTax.Rows.Count < 1)
{
args.Cancel("Taxonomy could not be validated.");
}
else
{
int currentid = (int)dtTax.Rows[0]["current_taxonomy_species_id"];
// 1) prevent link to invalid species (e.g. synonym)
if (tsid != currentid)
{
oldtsid = tsid;
newtsid = currentid;
tsidChanged = true;
helper.SetValue("taxonomy_species_id", currentid, typeof(int), false);
dtTax = args.ReadData(@"SELECT current_taxonomy_species_id, synonym_code, life_form_code FROM taxonomy_species WHERE taxonomy_species_id = :tsid",
":tsid", currentid, DbType.Int32);
}
// 2) set life_form_code from tax if null
helper.SetValueIfFieldExistsAndIsEmpty("life_form_code", dtTax.Rows[0]["life_form_code"]);
// 4) set received date if null (sysdate)
helper.SetValueIfFieldExistsAndIsEmpty("initial_received_date", DateTime.UtcNow);
helper.SetValueIfFieldExistsAndIsEmpty("initial_received_date_code", "MM/dd/yyyy");
helper.SetValueIfFieldExistsAndIsEmpty("status_code", "ACTIVE");
}
}
}
public override void TableRowSaved(ISaveDataTriggerArgs args)
{
// post-row save trigger
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// args.WriteData("update inventory_viability set percent_viable = percent_dormant + percent_normal where inventory_viability_id = " + args.NewPrimaryKeyID);
// note = helper.GetOriginalValue("web_availability_note", "").ToString();
// newNote = helper.GetValue("web_availability_note", "", true).ToString();
if (noteChanged)
{
var dtEmails = args.ReadData(@"select distinct wu.user_name, LTRIM(RTRIM(LTRIM(COALESCE(a.accession_number_part1, '') + ' ') + LTRIM(COALESCE(convert(varchar, a.accession_number_part2), '') + ' ') + COALESCE(a.accession_number_part3, ''))) AS PI_Number
from inventory i, accession a, web_user wu, web_user_cart wuc, web_user_cart_item wuci
where i.accession_id = a.accession_id
and a.accession_id = wuci.accession_id
and wuci.web_user_cart_id = wuc.web_user_cart_id
and wuc.web_user_id = wu.web_user_id
and wuc.cart_type_code = 'favorites'
and i.accession_id =:accessionid", ":accessionid", helper.GetValue("accession_id", 0, true), DbType.Int32);
if (dtEmails.Rows.Count > 0)
{
string emailTo = "";
string pi = dtEmails.Rows[0]["PI_Number"].ToString();
foreach (DataRow dr in dtEmails.Rows)
{
emailTo += dr["user_name"].ToString() + "; ";
}
StringBuilder sb = new StringBuilder();
//string CRLF = "\r\n";
sb.Append("Web availability changed for item ").Append(pi).Append(" in your favorites list."); // TODO: need more email text here
try
{
// hard code 'email from' as [email protected], need to use something to be configured
Email.Send(emailTo,
"*****@*****.**",
"",
"",
"GRIN-GLOBAL - Inventory Availabilty Change Notice",
sb.ToString());
}
catch (Exception ex)
{
string s = ex.Message; // debug
}
}
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
// pre-row save trigger
if (args.SaveMode == SaveMode.Insert)
{
var helper = args.Helper;
// check that the final recipient has a category code
var dtCoop = args.ReadData(@"SELECT category_code FROM cooperator WHERE cooperator_id = :coopid AND category_code IS NOT NULL", ":coopid", helper.GetValue("final_recipient_cooperator_id", 0, true), DbType.Int32);
if (dtCoop.Rows.Count < 1)
{
args.Cancel("Final recipient cooperator must have a category");
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
// pre-row save trigger
// for simplicity just check current iv on modify, for insert set to 0 so all updated just set and forget, otherwise have to use after save trigger on insert
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var h = args.Helper;
if (h.FieldExists("is_distributable"))
{
string dist = h.GetValue("is_distributable", "", true).ToString();
//args.Cancel("dist is " + dist);
//return;
if (dist == "Y")
{
int ivid;
if (args.SaveMode == SaveMode.Update)
{
ivid = (int)h.GetValue("inventory_id", 0, true);
}
else
{
ivid = 0;
}
int acid = (int)h.GetValue("accession_id", 0, true);
var dt = args.ReadData(@"select coalesce(count(*),0) as ct from inventory i where i.accession_id=:acid and is_distributable = 'Y' and inventory_id <> :ivid ;", ":acid", acid, ":ivid", ivid);
var dr = dt.Rows[0];
int numiv = (int)dr["ct"];
if (numiv > 0)
{
//args.Cancel("Iv = " + dr["ct"]);
//args.Cancel(" ivid = " + ivid);
//return;
args.WriteData(@"update inventory set is_distributable = 'N' where accession_id = :acid and is_distributable = 'Y' and inventory_id <> :ivid",
new DataParameters(
":acid", acid, DbType.Int32, ":ivid", ivid, DbType.Int32));
}
}
}
}
}
private int getSystemCooperatorID(ISaveDataTriggerArgs args)
{
var cm = CacheManager.Get("SystemCooperatorID");
var sysCoopID = Toolkit.ToInt32(cm["sys_cooperator_id"], -1);
if (sysCoopID < 0)
{
var dt = args.ReadData("select cooperator_id from cooperator where last_name = 'SYSTEM'");
if (dt.Rows.Count < 1)
{
sysCoopID = -1;
}
else
{
sysCoopID = Toolkit.ToInt32(dt.Rows[0]["cooperator_id"], -1);
}
cm["sys_cooperator_id"] = sysCoopID;
}
return(sysCoopID);
}
public void CheckAuthority(ISaveDataTriggerArgs args, string authorities)
{
//convert all author construction syntax to &
authorities = Regex.Replace(authorities, @"\(|\)| ex | ex\. | Ex\. | ,ex | And | Et | non | sensu ", " & ");
// removefrom evaluation
authorities = Regex.Replace(authorities, "et al.", "");
string[] authList = authorities.Split('&');
foreach (string auth in authList)
{
string authClean = auth.Trim();
if (!String.IsNullOrEmpty(authClean))
{
var dtAuth = args.ReadData(@"SELECT * FROM taxonomy_author WHERE short_name = :auth", ":auth", authClean, DbType.String);
if (dtAuth.Rows.Count < 1)
{
args.Cancel("Family author " + authClean + " could not be validated.");
}
}
}
}
public void TableRowSaved(ISaveDataTriggerArgs args)
{
if (args.Table != null && args.Table.TableName.ToLower() == "accession")
{
// tell the taxonomy caches to refresh themselves, they're possibly out of date now
CacheManager.Get("Taxonomy").Clear();
if (args.SaveMode == SaveMode.Insert)
{
// lookup id of 'SYSTEM' maint policy record as needed
var sysPolicyID = getSystemMaintenancePolicyID(args);
var dth = args.Helper;
var dtAcc = args.ReadData("select * from accession where accession_id = " + args.NewPrimaryKeyID);
if (dtAcc.Rows.Count > 0)
{
var dr = dtAcc.Rows[0];
// we need to create a default inventory record to go along with the accession
args.WriteData(@"
insert into inventory
(
inventory_number_part1,
inventory_number_part2,
inventory_number_part3,
form_type_code,
inventory_maint_policy_id,
is_distributable,
is_available,
availability_status_code,
is_auto_deducted,
accession_id,
note,
created_date,
created_by,
owned_date,
owned_by
) values (
@part1,
@part2,
@part3,
'**',
@maintpolid,
'N',
'N',
'NOT-SET',
'N',
@accessionid,
'Default Association Record for Accession -> Inventory',
@createddate,
@createdby,
@owneddate,
@ownedby
)
", new DataParameters(
"@part1", dr["accession_number_part1"], DbType.String,
"@part2", dr["accession_number_part2"], DbType.Int32,
"@part3", dr["accession_number_part3"], DbType.String,
"@maintpolid", sysPolicyID, DbType.Int32,
"@accessionid", args.NewPrimaryKeyID, DbType.Int32,
"@createddate", DateTime.UtcNow, DbType.DateTime2,
"@createdby", args.CooperatorID, DbType.Int32,
"@owneddate", DateTime.UtcNow, DbType.DateTime2,
"@ownedby", dr["owned_by"], DbType.Int32
));
// // HACK: set owned_by to curator1 or curator2 if available...
// var dtCurator = args.ReadData(@"
//select
// coalesce(ts.curator1_cooperator_id, ts.curator2_cooperator_id, -1) as curator_id
//from
// taxonomy_species ts inner join accession a
//on
// ts.taxonomy_species_id = a.taxonomy_species_id
//where
// a.accession_id = " + args.NewPrimaryKeyID);
// if (dtCurator.Rows.Count > 0) {
// var curatorID = Toolkit.ToInt32(dtCurator.Rows[0]["curator_id"], -1);
// if (curatorID > -1) {
// args.WriteData(@"
//update
// accession
//set
// owned_by = " + curatorID + " where accession_id = " + args.NewPrimaryKeyID);
// }
// }
}
}
}
}
private void fillMaintenancePolicyValues(ISaveDataTriggerArgs args)
{
var h = args.Helper;
var policyID = Toolkit.ToInt32(h.GetValue("inventory_maint_policy_id", -1, true), -1);
if (policyID > 0)
{
var dt = args.ReadData(@"
SELECT inventory_maint_policy_id
,maintenance_name
,form_type_code
,quantity_on_hand_unit_code
,web_availability_note
,is_auto_deducted
,distribution_default_form_code
,distribution_default_quantity
,distribution_unit_code
,distribution_critical_quantity
,regeneration_critical_quantity
,regeneration_method_code
from
inventory_maint_policy
where
inventory_maint_policy_id = " + policyID);
if (dt.Rows.Count > 0)
{
var dr = dt.Rows[0];
if (h.FieldExists("form_type_code") && h.IsValueEmpty("form_type_code"))
{
h.SetValue("form_type_code", dr["form_type_code"], dr.Table.Columns["form_type_code"].DataType, false);
}
if (h.FieldExists("quantity_on_hand_unit_code") && h.IsValueEmpty("quantity_on_hand_unit_code"))
{
h.SetValue("quantity_on_hand_unit_code", dr["quantity_on_hand_unit_code"], dr.Table.Columns["quantity_on_hand_unit_code"].DataType, false);
}
if (h.FieldExists("web_availability_note") && h.IsValueEmpty("web_availability_note"))
{
h.SetValue("web_availability_note", dr["web_availability_note"], dr.Table.Columns["web_availability_note"].DataType, false);
}
if (h.FieldExists("is_auto_deducted") && h.IsValueEmpty("is_auto_deducted"))
{
h.SetValue("is_auto_deducted", dr["is_auto_deducted"], dr.Table.Columns["is_auto_deducted"].DataType, false);
}
if (h.FieldExists("distribution_default_form_code") && h.IsValueEmpty("distribution_default_form_code"))
{
h.SetValue("distribution_default_form_code", dr["distribution_default_form_code"], dr.Table.Columns["distribution_default_form_code"].DataType, false);
}
if (h.FieldExists("distribution_default_quantity") && h.IsValueEmpty("distribution_default_quantity"))
{
h.SetValue("distribution_default_quantity", dr["distribution_default_quantity"], dr.Table.Columns["distribution_default_quantity"].DataType, false);
}
if (h.FieldExists("distribution_unit_code") && h.IsValueEmpty("distribution_unit_code"))
{
h.SetValue("distribution_unit_code", dr["distribution_unit_code"], dr.Table.Columns["distribution_unit_code"].DataType, false);
}
if (h.FieldExists("distribution_critical_quantity") && h.IsValueEmpty("distribution_critical_quantity"))
{
h.SetValue("distribution_critical_quantity", dr["distribution_critical_quantity"], dr.Table.Columns["distribution_critical_quantity"].DataType, false);
}
if (h.FieldExists("regeneration_critical_quantity") && h.IsValueEmpty("regeneration_critical_quantity"))
{
h.SetValue("regeneration_critical_quantity", dr["regeneration_critical_quantity"], dr.Table.Columns["regeneration_critical_quantity"].DataType, false);
}
// comment out for now, wait for gap analysis.
//if (h.FieldExists("pollination_method_code") && h.IsValueEmpty("pollination_method_code"))
//{
// h.SetValue("pollination_method_code", dr["regeneration_method_code"], dr.Table.Columns["regeneration_method_code"].DataType, false);
//}
}
}
}
public void TableRowSaving(ISaveDataTriggerArgs args)
{
// pre-row save trigger
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var h = args.Helper;
// 4) default status (NEW)
// in AT default but lets make sure
h.SetValueIfFieldExistsAndIsEmpty("status_code", "NEW");
h.SetValueIfFieldExistsAndIsEmpty("status_date", DateTime.UtcNow);
// 3) fill in default distribution values if empty (go ahead and retrieve a bunch of stuff and do ifs later?)
int f = 0;
int q = 0;
int u = 0;
if (h.FieldExists("distribution_form_code") && h.IsValueEmpty("distribution_form_code"))
{
f = 1;
}
if (h.FieldExists("quantity_shipped") && h.IsValueEmpty("quantity_shipped"))
{
q = 1;
}
if (h.FieldExists("quantity_shipped_unit_code") && h.IsValueEmpty("quantity_shipped_unit_code"))
{
u = 1;
}
if (q == 1 || u == 1 || f == 1)
{
int ivid = (int)h.GetValue("inventory_id", 0, true);
// args.Cancel("Inventory = " + ivid);
// return;
var dt = args.ReadData(@"SELECT distribution_default_form_code,distribution_default_quantity, distribution_unit_code FROM inventory WHERE inventory_id = :ivid ", ":ivid", ivid);
var dr = dt.Rows[0];
// string dform = dtDsc.Rows[0]["distribution_default_form_code"].ToString();
// double dquant = System.Convert.ToDouble(dtDsc.Rows[0]["distribution_default_quantity"]);
// string dunits = dtDsc.Rows[0]["distribution_unit_code"].ToString();
//args.Cancel("dform = " + dform + "dquant = " + dquant + "units = " + dunits);
//return;
if (f == 1)
{
h.SetValue("distribution_form_code", dr["distribution_default_form_code"], dr.Table.Columns["distribution_default_form_code"].DataType, false);
}
if (q == 1)
{
h.SetValue("quantity_shipped", dr["distribution_default_quantity"], dr.Table.Columns["distribution_default_quantity"].DataType, false);
}
if (u == 1)
{
h.SetValue("quantity_shipped_unit_code", dr["distribution_unit_code"], dr.Table.Columns["distribution_unit_code"].DataType, false);
}
//
// 2) fill in default iv if accession only entered - cannot be done as acid not in table
//if (h.FieldExists("inventory_id") && h.IsValueEmpty("inventory_id"))
// {
// args.Cancel("Inventory not present.");
// int acid = (int)h.GetValue("accession_id", 0, true);
//if (acid == 0) { return; }
// args.Cancel("acid = " + acid);
// return;
// }
}
// fill in name if blank from topname
if (h.FieldExists("name") && h.IsValueEmpty("name"))
{
int ivid = (int)h.GetValue("inventory_id", 0, true);
var dt = args.ReadData(@"select top 1 plant_name as topname from accession_inv_name an where inventory_id in
(select inventory_id from inventory i where accession_id in (select accession_id from inventory i2 where inventory_id=:ivid)) order by plant_name_rank", ":ivid", ivid);
if (dt.Rows.Count > 0)
{
var dr = dt.Rows[0];
// args.Cancel("topname = " + dr["topname"]);
// return;
h.SetValue("name", dr["topname"], dr.Table.Columns["topname"].DataType, false);
}
}
// set taxon column if null
if (h.FieldExists("external_taxonomy") && h.IsValueEmpty("external_taxonomy"))
{
int ivid = (int)h.GetValue("inventory_id", 0, true);
var dt = args.ReadData(@"select name from taxonomy_species t, accession a, inventory i where t.taxonomy_species_id=a.taxonomy_species_id and
a.accession_id=i.accession_id and i.inventory_id=:ivid;", ":ivid", ivid);
var dr = dt.Rows[0];
// args.Cancel("tax = " + dr["name"]);
// return;
h.SetValue("external_taxonomy", dr["name"], dr.Table.Columns["name"].DataType, false);
}
}
}
public void TableRowSaving(ISaveDataTriggerArgs args)
// pre update row trigger
{
if (args.SaveMode == SaveMode.Update || args.SaveMode == SaveMode.Insert)
{
var helper = args.Helper;
// 2 - set shybrid flag if needed and lowercase species
if (helper.AllFieldsExist("species_name"))
{
var species = helper.GetValue("species_name", "", true).ToString().ToLower();
helper.SetValue("species_name", species, typeof(string), true);
string sub = species.Substring(0, 2);
if (sub == "x ")
{
//args.Cancel("hybrid found");
//return;
helper.SetValue("is_specific_hybrid", "Y", typeof(string), true);
helper.SetValue("species_name", species.Substring(2), typeof(string), true);
}
}
// 3 lower other columns
if (helper.AllFieldsExist("subspecies_name") && !helper.IsValueEmpty("subspecies_name"))
{
helper.SetValue("subspecies_name", helper.GetValue("subspecies_name", "", true).ToString().ToLower(), typeof(string), true);
}
if (helper.AllFieldsExist("variety_name") && !helper.IsValueEmpty("variety_name"))
{
helper.SetValue("variety_name", helper.GetValue("variety_name", "", true).ToString().ToLower(), typeof(string), true);
}
if (helper.AllFieldsExist("subvariety_name") && !helper.IsValueEmpty("subvariety_name"))
{
helper.SetValue("subvariety_name", helper.GetValue("subvariety_name", "", true).ToString().ToLower(), typeof(string), true);
}
if (helper.AllFieldsExist("forma_name") && !helper.IsValueEmpty("forma_name"))
{
helper.SetValue("forma_name", helper.GetValue("forma_name", "", true).ToString().ToLower(), typeof(string), true);
}
// 4 Check authors - currently does not specify which of the authors failed
if (helper.FieldExists("species_authority") && !helper.IsValueEmpty("species_authority"))
{
CheckAuthority(args, helper.GetValue("species_authority", "", false).ToString(), "Species authority");
}
if (helper.FieldExists("subspecies_authority") && !helper.IsValueEmpty("subspecies_authority"))
{
CheckAuthority(args, helper.GetValue("subspecies_authority", "", false).ToString(), "Subspecies authority");
}
if (helper.FieldExists("variety_authority") && !helper.IsValueEmpty("variety_authority"))
{
CheckAuthority(args, helper.GetValue("variety_authority", "", false).ToString(), "Variety authority");
}
if (helper.FieldExists("subvariety_authority") && !helper.IsValueEmpty("subvariety_authority"))
{
CheckAuthority(args, helper.GetValue("subvariety_authority", "", false).ToString(), "Subvariety authority");
}
if (helper.FieldExists("forma_authority") && !helper.IsValueEmpty("forma_authority"))
{
CheckAuthority(args, helper.GetValue("forma_authority", "", false).ToString(), "Forma authority");
}
// 5 move psite2 to psite1 if null
/* " IF :NEW.psite1 IS NULL AND :NEW.psite2 IS NOT NULL THEN
* :NEW.psite1 := :NEW.psite2;
* :NEW.psite2 := NULL;
* END IF;
* "*/
if ((helper.AllFieldsExist("priority2_site_id") && !helper.IsValueEmpty("priority2_site_id")) && (helper.AllFieldsExist("priority1_site_id") && helper.IsValueEmpty("priority1_site_id")))
{
helper.SetValue("priority1_site_id", helper.GetValue("priority2_site_id", 0, true), typeof(int), true);
helper.SetValue("priority2_site_id", DBNull.Value, typeof(int), false);
}
/*
* -- set taxon field if not already done
* --
* IF :NEW.taxon IS NULL THEN
* :NEW.taxon := t_genus||t_shybrid||' '||:NEW.species;
* :NEW.taxauthor := :NEW.sauthor;
* IF :NEW.forma IS NOT NULL THEN
* :NEW.taxon := RTRIM(:NEW.taxon)||' '||:NEW.forma;
* :NEW.taxauthor := :NEW.fauthor;
* ELSIF :NEW.subvar IS NOT NULL THEN
* :NEW.taxon := RTRIM(:NEW.taxon)||' subvar. '||:NEW.subvar;
* :NEW.taxauthor := :NEW.svauthor;
* ELSIF :NEW.var IS NOT NULL THEN
* IF :NEW.varhybrid IS NOT NULL THEN
* :NEW.taxon := RTRIM(:NEW.taxon)||' nothovar. '||:NEW.var;
* ELSE
* :NEW.taxon := RTRIM(:NEW.taxon)||' var. '||:NEW.var;
* END IF;
* :NEW.taxauthor := :NEW.varauthor;
* ELSIF :NEW.subsp IS NOT NULL THEN
* IF :NEW.ssphybrid IS NOT NULL THEN
* :NEW.taxon := RTRIM(:NEW.taxon)||' nothosubsp. '||:NEW.subsp;
* ELSE
* :NEW.taxon := RTRIM(:NEW.taxon)||' subsp. '||:NEW.subsp;
* END IF;
* :NEW.taxauthor := :NEW.sspauthor;
* END IF;
* END IF;
*/
// "name" field is the calculated name for this record. It cannot be overridden, unlike in GC where it could.
// it should therefore be marked as read only in the table mappings!
//if (nonEmptyValue(helper, "name")){
// TODO: move this loop into a method defined on the FieldMappingDataView interface
int?genusID = (int?)(args.Helper.GetValue("taxonomy_genus_id", null, true));
// fill in all the values up front so the logic below them is clearer
var dt = args.ReadData(@"select genus_name from taxonomy_genus where taxonomy_genus_id = :id", ":id", genusID, DbType.Int32);
var genusName = "";
if (dt.Rows.Count > 0)
{
genusName = dt.Rows[0]["genus_name"].ToString();
}
if (String.IsNullOrEmpty(genusName))
{
// we weren't given the primary key. Try just pulling it from the row itself.
genusName = args.Helper.GetValue("genus_name", null, true, true) as string;
}
var newForma = helper.GetValue("forma_name", null, false);
var newFormaRankType = helper.GetValue("forma_rank_type", null, false);
var newFormaAuthor = helper.GetValue("forma_authority", null, false);
var newSubvariety = helper.GetValue("subvariety_name", null, false);
var newSubvarietyAuthor = helper.GetValue("subvariety_authority", null, false);
var newVariety = helper.GetValue("variety_name", null, false);
var newVarietyAuthor = helper.GetValue("variety_authority", null, false);
var newSubspecies = helper.GetValue("subspecies_name", null, false);
var newSubspeciesAuthor = helper.GetValue("subspecies_authority", null, false);
var newName = genusName + (helper.GetValue("is_specific_hybrid", "N", true).ToString().ToUpper() == "Y" ? " x " : " ") + helper.GetValue("species_name", "", true);
var newAuthor = helper.GetValue("species_authority", null, false);
if (!helper.IsValueEmpty("forma_name"))
{
newName = newName.TrimEnd() + " " + newFormaRankType + " " + newForma;
newAuthor = newFormaAuthor;
}
else if (!helper.IsValueEmpty("subvariety_name"))
{
newName = newName.TrimEnd() + " subvar. " + newSubvariety;
newAuthor = newSubvarietyAuthor;
}
else if (!helper.IsValueEmpty("variety_name"))
{
if (helper.GetValue("is_varietal_hybrid", "N", true).ToString().ToUpper() == "Y")
{
newName = newName.TrimEnd() + " nothovar. " + newVariety;
}
else
{
newName = newName.TrimEnd() + " var. " + newVariety;
}
newAuthor = newVarietyAuthor;
}
else if (!helper.IsValueEmpty("subspecies_name"))
{
if (helper.GetValue("is_subvarietal_hybrid", "N", false).ToString().ToUpper() == "Y")
{
newName = newName.TrimEnd() + " nothosubsp. " + newSubspecies;
}
else
{
newName = newName.TrimEnd() + " subsp. " + newSubspecies;
}
newAuthor = newSubspeciesAuthor;
}
if (newName == null || newName.Trim().Length == 0)
{
newName = string.Empty;
}
helper.SetValue("name", (String.IsNullOrEmpty(newName) ? (object)DBNull.Value : (object)newName), typeof(string), true);
if (helper.FieldExists("name_authority"))
{
helper.SetValue("name_authority", newAuthor, typeof(string), false);
}
}
}
public void TableRowSaved(ISaveDataTriggerArgs args)
{
// post row update trigger
if (args.SaveMode == SaveMode.Insert)
{
// make current_taxonomy_species_id field match the taxonomy_species_id field if needed
var helper = args.Helper;
// make the nomen_number match the new id if needed...
if (helper.IsValueEmpty("nomen_number"))
{
args.WriteData("update taxonomy_species set nomen_number = " + args.NewPrimaryKeyID + " where taxonomy_species_id = " + args.NewPrimaryKeyID);
}
if (helper.IsValueEmpty("current_taxonomy_species_id"))
{
args.WriteData(@"update taxonomy_species set current_taxonomy_species_id = :id1 where taxonomy_species_id = :id2", ":id1", args.NewPrimaryKeyID, DbType.Int32, ":id2", args.NewPrimaryKeyID, DbType.Int32);
}
}
// if synonym then check for accessions to move and annotations to create
if (args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
int taxno = (int)helper.GetValue("taxonomy_species_id", 0, true);
int validtaxno = (int)helper.GetValue("current_taxonomy_species_id", 0, true);
if (taxno != validtaxno)
{
var dt = args.ReadData(@"select coalesce(count(*),0) as ct from accession a where a.taxonomy_species_id =:taxno;", ":taxno", taxno);
var dr = dt.Rows[0];
int numaccs = (int)dr["ct"];
//args.Cancel("Accession = " + dr["ct"]);
//return;
if (numaccs > 0)
{
int modified_by = (int)helper.GetValue("modified_by", 0, true);
DateTime modified_date = DateTime.UtcNow;
// add annotations
args.WriteData(@"INSERT INTO accession_inv_annotation(annotation_type_code,annotation_date,annotation_date_code,annotation_cooperator_id,inventory_id,
old_taxonomy_species_id,new_taxonomy_species_id,created_date,created_by,owned_date,owned_by)
SELECT 'NOM-CHANGE',:modified_date,'MM/dd/yyyy',:modified_by,i.inventory_id,:taxno,:validtaxno,:modified_date,:modified_by,:modified_date,:modified_by
FROM accession a INNER JOIN inventory i ON a.accession_id = i.accession_id AND i.form_type_code = '**'
WHERE a.taxonomy_species_id = :taxno",
new DataParameters(
":taxno", taxno, DbType.Int32,
":validtaxno", validtaxno, DbType.Int32,
":modified_by", modified_by, DbType.Int32,
":modified_date", modified_date, DbType.DateTime2
));
// modify accessions
args.WriteData(@"update accession set taxonomy_species_id=:validtaxno, modified_by=:modified_by, modified_date=:modified_date where taxonomy_species_id=:taxno",
new DataParameters(
":taxno", taxno, DbType.Int32,
":validtaxno", validtaxno, DbType.Int32,
":modified_by", modified_by, DbType.Int32,
":modified_date", modified_date, DbType.DateTime2
));
}
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
// pre-row save trigger
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// Check that code agrees with trait
if (helper.AllFieldsExist("crop_trait_id", "crop_trait_code_id"))
{
int ctid = (int)helper.GetValue("crop_trait_id", 0, true);
int ctcid = (int)helper.GetValue("crop_trait_code_id", 0, true);
if (ctid > 0 && ctcid > 0)
{
var dtCode = args.ReadData(@"SELECT * FROM crop_trait_code WHERE crop_trait_id = :ctid AND crop_trait_code_id = :ctcid", ":ctid", ctid, ":ctcid", ctcid);
if (dtCode.Rows.Count < 1)
{
args.Cancel("Crop Trait Code must agree with Crop Trait");
return;
}
}
}
// validate data column with descriptor data column
if (helper.AllFieldsExist("crop_trait_code_id", "string_value", "numeric_value"))
{
// args.Cancel("I can see the three value fields - 3000 sleep");
int ctid = (int)helper.GetValue("crop_trait_id", 0, true);
int ctcid = (int)helper.GetValue("crop_trait_code_id", 0, true);
var strg = helper.GetValue("string_value", "", true).ToString();
double numb = System.Convert.ToDouble(helper.GetValue("numeric_value", 0, true));
var dtDsc = args.ReadData(@"SELECT data_type_code,is_coded,coalesce(numeric_maximum,0) max,coalesce(numeric_minimum,0) min FROM crop_trait WHERE crop_trait_id = :ctid ", ":ctid", ctid);
string dtype = dtDsc.Rows[0]["data_type_code"].ToString();
string coded = dtDsc.Rows[0]["is_coded"].ToString();
double min = System.Convert.ToDouble(dtDsc.Rows[0]["min"]);
double max = System.Convert.ToDouble(dtDsc.Rows[0]["max"]);
// check for coded trait type
if (coded == "Y" && ctcid < 1)
{
args.Cancel("Trait is coded but no data in code value column.");
return;
}
// check if CHAR and empty string
if (dtype == "CHAR" && coded == "N" && strg == "")
{
args.Cancel("Trait is string but no data in string value column.");
return;
}
// check for numeric val and min/max if NUMERIC
if (dtype == "NUMERIC" && coded == "N")
{
if (numb == 0)
{
args.Cancel("Trait is numeric but no data in numeric value column.");
return;
}
if (min > 0 && numb < min)
{
args.Cancel("Numeric value less than minimum specified for trait.");
return;
}
if (max > 0 && numb > max)
{
args.Cancel("Numeric value greater than specified for trait.");
return;
}
}
}
// validate taxonomy crop against crop
// FIRST LOOK FOR A VALID COMBINATION OF THAT SPECIES AND CROP ALREADY IN THE DATABASE in taxonomy_crop_map if found continue
// if not found look for any entry for that species in taxonomy_crop_map - a hit means that species already has a different crop and the match is potentially bad, throw error. If it is a legit
// new crop for that species, someone will have to add that combo to t_c_map
// if no good or bad match found, add entry to t_c_m and continue - list will self generate
//
if (helper.AllFieldsExist("inventory_id"))
{
// System.Threading.Thread.Sleep(5000);
int ivid = (int)helper.GetValue("inventory_id", 0, true);
int dno = (int)helper.GetValue("crop_trait_id", 0, true);
// look up taxonomy species id for accession
var dtTax = args.ReadData(@"select taxonomy_species_id from accession a, inventory i where i.accession_id = a.accession_id and i.inventory_id = :ivid", ":ivid", ivid);
int taxno = (int)dtTax.Rows[0]["taxonomy_species_id"];
// look up crop id from trait being loaded
var dtCrop = args.ReadData(@"select crop_id from crop_trait where crop_trait_id = :dno", ":dno", dno);
int cropno = (int)dtCrop.Rows[0]["crop_id"];
// alternate_crop_name = string N/A separates obs entries from crop relative entries
// first check for a positive match between the species and crop in taxonomy_crop_map
var dtObsT = args.ReadData(@"select crop_id from taxonomy_crop_map where crop_id=:cropno and taxonomy_species_id=:taxno and alternate_crop_name='N/A'", ":cropno", cropno, ":taxno", taxno);
if (dtObsT.Rows.Count >= 1)
{
return;
}
; // found match on species and crop so good
// next check for any entry as that means that species is linked to a different crop
// var dtObsF = args.ReadData(@"select top 1 obid from ob2 where taxno=:taxno", ":taxno", taxno); // only need to check for ANY obs
var dtObsF = args.ReadData(@"select taxonomy_species_id from taxonomy_crop_map where taxonomy_species_id=:taxno and alternate_crop_name = 'N/A'", ":taxno", taxno); // only need to check for ANY obs
if (dtObsF.Rows.Count >= 1)
{
args.Cancel("Accession species and crop species do not match.");
return;
}
// must be a new combination so add to taxonomy_crop_map
int owned_by = (int)helper.GetValue("owned_by", 0, true);
int created_by = (int)helper.GetValue("created_by", 0, true);
DateTime owned_date = DateTime.UtcNow;
DateTime created_date = DateTime.UtcNow;
args.WriteData(@"
insert into taxonomy_crop_map (crop_id,taxonomy_species_id,owned_by,owned_date,created_by,created_date,alternate_crop_name,is_primary_genepool,is_secondary_genepool,
is_tertiary_genepool,is_quaternary_genepool, is_graftstock_genepool)
values (:cropno,:taxno,:owned_by,:owned_date,:created_by,:created_date,'N/A','N','N','N','N','N')",
":cropno", cropno, DbType.String,
":taxno", taxno, DbType.String,
":owned_by", owned_by, DbType.Int32,
":owned_date", owned_date, DbType.DateTime2,
":created_by", created_by, DbType.Int32,
":created_date", created_date, DbType.DateTime2
);
}
}
}