public override void TableRowSaving(ISaveDataTriggerArgs args)
{
// pre-row save trigger
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// 5) all quants >=0
if (helper.FieldExists("quantity_on_hand") && Convert.ToDouble(helper.GetValue("quantity_on_hand", 0.0, true)) < 0.0)
{
args.Cancel("quantity_on_hand must not be negative");
}
if (helper.FieldExists("distribution_critical_quantity") && Convert.ToDouble(helper.GetValue("distribution_critical_quantity", 0.0, true)) < 0.0)
{
args.Cancel("distribution_critical_quantity must not be negative");
}
if (helper.FieldExists("distribution_default_quantity") && Convert.ToDouble(helper.GetValue("distribution_default_quantity", 0.0, true)) < 0.0)
{
args.Cancel("distribution_default_quantity must not be negative");
}
if (helper.FieldExists("regeneration_critical_quantity") && Convert.ToDouble(helper.GetValue("regeneration_critical_quantity", 0.0, true)) < 0.0)
{
args.Cancel("regeneration_critical_quantity must not be negative");
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// 1) Make sure at least 1 of number, fullname is filled in
if (helper.AllFieldsExist("ipr_number", "ipr_full_name") && helper.IsValueEmpty("ipr_number") && helper.IsValueEmpty("ipr_full_name"))
{
args.Cancel("Incomplete accession_ipr, must have either ipr_number or ipr_full_name.");
}
// 2) do not allow expired_date > current date
if (helper.FieldExists("expired_date") && !helper.IsValueEmpty("expired_date") &&
Convert.ToDateTime(helper.GetValue("expired_date", null, true)) > DateTime.UtcNow)
{
args.Cancel("Expired date cannot exceed the current date.");
}
// 3) do not allow issued date > expired date
if (helper.AllFieldsExist("issued_date", "expired_date") && !helper.IsValueEmpty("issued_date") && !helper.IsValueEmpty("expired_date"))
{
DateTime issued = Convert.ToDateTime(helper.GetValue("issued_date", null, true));
DateTime expired = Convert.ToDateTime(helper.GetValue("expired_date", null, true));
if (expired < issued)
{
args.Cancel("Issued date cannot exceed expired date.");
}
}
}
}
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");
}
}
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
// pre-row save trigger
// no need to check mode?
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update || args.SaveMode == SaveMode.Delete)
{
var h = args.Helper;
if (h.FieldExists("form_type_code"))
{
string ivt = "SD";
if (args.SaveMode != SaveMode.Delete)
{
ivt = h.GetValue("form_type_code", "", true).ToString();
}
string ivto = h.GetOriginalValue("form_type_code", "").ToString();
if (ivt == "**" || ivto == "**")
{
args.Cancel("You can not insert, update or delete the system inventory row.");
return;
}
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// 1) make sure expected date > established date
/* if (helper.AllFieldsExist("expected_release_date", "established_date") && !helper.IsValueEmpty("expected_release_date") && !helper.IsValueEmpty("established_date")) {
* DateTime expected = Convert.ToDateTime(helper.GetValue("expected_release_date", null, true));
* DateTime established = Convert.ToDateTime(helper.GetValue("established_date", null, true));
* if (expected < established) {
* args.Cancel("Expected date must exceed or equal the date established.");
* }
* }
*/
if (DateIsBefore(args, "expected_release_date", "established_date"))
{
args.Cancel("Expected date must exceed or equal the date established.");
}
// 2) make sure expected date > entered date
if (DateIsBefore(args, "expected_release_date", "entered_date"))
{
args.Cancel("Expected date must exceed or equal the entered date.");
}
// 3) make sure released date > entered date
if (DateIsBefore(args, "released_date", "entered_date"))
{
args.Cancel("Released date must exceed or equal the entered date.");
}
// 4) make sure released date > established date
if (DateIsBefore(args, "released_date", "established_date"))
{
args.Cancel("Released date must exceed or equal the established date.");
}
// 5) make sure released date <= current date
if (helper.FieldExists("released_date") && !helper.IsValueEmpty("released_date") &&
Convert.ToDateTime(helper.GetValue("released_date", null, true)) > DateTime.UtcNow)
{
args.Cancel("released_date cannot exceed the current date.");
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
// pre-row save trigger
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// 2) must have units if quantity onhand entered - N
if (helper.AllFieldsExist("quantity_on_hand", "quantity_on_hand_unit_code") && !helper.IsValueEmpty("quantity_on_hand") && helper.IsValueEmpty("quantity_on_hand_unit_code"))
{
args.Cancel("must have quantity_on_hand_unit_code if quantity_on_hand entered");
}
// 3) must have dist units if default dist quantity filled in - N
if (helper.AllFieldsExist("distribution_default_quantity", "distribution_unit_code") && !helper.IsValueEmpty("distribution_default_quantity") && helper.IsValueEmpty("distribution_unit_code"))
{
args.Cancel("must have distribution_unit_code if distribution_default_quantity entered");
}
}
}
public override void TableRowSaved(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert)
{
var h = args.Helper;
if (h.IsValueEmpty("current_taxonomy_genus_id"))
{
// make current_taxonomy_genus_id field match the taxonomy_genus_id field
args.WriteData(@"update taxonomy_genus set current_taxonomy_genus_id = :id1 where taxonomy_genus_id = :id2", ":id1", args.NewPrimaryKeyID, DbType.Int32, ":id2", args.NewPrimaryKeyID, DbType.Int32);
}
// 6a handle qual will only warn, row will still be saved
/*"IF :NEW.qual IS NOT NULL THEN
* t_code := rtrim(:NEW.qual);
* main.verify_code('PROD','GN','QUAL','%',t_code);
* :NEW.qual := t_code;
* IF :NEW.validgno = :NEW.gno AND :NEW.qual <> '~' THEN
* raise_application_error (-20000,
* 'Only tilde (~) allowed for QUAL in accepted names.');
* END IF;
* ELSIF :NEW.validgno <> :NEW.gno THEN
* :NEW.qual := '=';
* END IF;
* "*/
if (h.FieldExists("qualifying_code"))
{
string qual = h.GetValue("qualifying_code", "", true).ToString();
int gno = (int)h.GetValue("taxonomy_genus_id", 0, true);
int validgno = (int)h.GetValue("current_taxonomy_genus_id", 0, true);
if (validgno == 0)
{
validgno = gno;
}
if (gno == validgno && !h.IsValueEmpty("qualifying_code"))
{
if (qual != "~")
{
args.Cancel("WARNING: only tilde (~) allowed for QUAL in accepted names. Redit the row and correct it.");
return;
}
}
// 6 b set qual to = if null and synonym
if (gno != validgno && h.IsValueEmpty("qualifying_code"))
{
args.WriteData(@"update taxonomy_genus set qualifying_code = '=' where taxonomy_genus_id = :id1", ":id1", args.NewPrimaryKeyID, DbType.Int32);
}
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// 1) Make sure at least released date or pedigree itself entered
if (helper.AllFieldsExist("description", "released_date") && helper.IsValueEmpty("description") && helper.IsValueEmpty("released_date"))
{
args.Cancel("Incomplete accession_pedigree, must have either released_date or description.");
}
}
}
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 void TableRowSaving(ISaveDataTriggerArgs args)
{
// pre-row save trigger
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// 1) require units if quantity filled in
if (helper.AllFieldsExist("quantity", "quantity_unit_code") && !helper.IsValueEmpty("quantity") && helper.IsValueEmpty("quantity_unit_code"))
{
args.Cancel("must have quantity_unit_code if quantity entered");
}
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// 1) check date order
if (helper.AllFieldsExist("started_date", "completed_date") && !helper.IsValueEmpty("started_date") && !helper.IsValueEmpty("completed_date"))
{
DateTime started = Convert.ToDateTime(helper.GetValue("started_date", null, true));
DateTime completed = Convert.ToDateTime(helper.GetValue("completed_date", null, true));
if (completed < started)
{
args.Cancel("Completed date must be after started date.");
}
}
}
}
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 override void TableRowSaving(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var helper = args.Helper;
// 1) must have default dist quantity if autodeduct (debit set)
if (helper.AllFieldsExist("is_auto_deducted", "distribution_default_quantity") &&
helper.GetValue("is_auto_deducted", "", true).ToString().ToUpper() == "Y" &&
helper.IsValueEmpty("distribution_default_quantity"))
{
args.Cancel("must have distribution_default_quantity if is_auto_deducted");
}
// 2) must have default critical dist cutoff if autodeduct
if (helper.AllFieldsExist("is_auto_deducted", "distribution_critical_quantity") &&
helper.GetValue("is_auto_deducted", "", true).ToString().ToUpper() == "Y" &&
helper.IsValueEmpty("distribution_critical_quantity"))
{
args.Cancel("must have distribution_critical_quantity if is_auto_deducted");
}
// 3) if default form is null set it to form_type
if (helper.AllFieldsExist("distribution_default_form_code", "form_type_code"))
{
helper.SetValueIfFieldExistsAndIsEmpty("distribution_default_form_code", helper.GetValue("form_type_code", "", true).ToString());
}
// 4) must have default dist units if dist quant and vice versa
if (helper.AllFieldsExist("distribution_default_quantity", "distribution_unit_code") && !helper.IsValueEmpty("distribution_default_quantity") && helper.IsValueEmpty("distribution_unit_code"))
{
args.Cancel("Must have distribution_unit_code if distribution_default_quantity is not null.");
}
// 5) all quants >= 0
if (helper.FieldExists("distribution_default_quantity") && Convert.ToDouble(helper.GetValue("distribution_default_quantity", 0.0, true)) < 0.0)
{
args.Cancel("distribution_default_quantity must not be negative");
}
if (helper.FieldExists("distribution_critical_quantity") && Convert.ToDouble(helper.GetValue("distribution_critical_quantity", 0.0, true)) < 0.0)
{
args.Cancel("distribution_critical_quantity must not be negative");
}
if (helper.FieldExists("regeneration_critical_quantity") && Convert.ToDouble(helper.GetValue("regeneration_critical_quantity", 0.0, true)) < 0.0)
{
args.Cancel("regeneration_critical_quantity must not be negative");
}
}
}
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 void TableRowSaving(ISaveDataTriggerArgs args)
{
// pre-row save trigger
if (args.SaveMode == SaveMode.Update || args.SaveMode == SaveMode.Insert)
{
// init helper with the row we want to save and the row in the database (which may be null on an insert)
// The Helper class simply makes sure we don't try to pull values from fields that are not in the current DataRow object.
var fh = args.Helper;
// set the current site, userid, created date (at time of creating record for insert...not commit...rest is commit)
// TODO: userid / created date / modified date done automatically by middle tier -- but not site_code... which I have no idea how to auto-determine...
// make sure either last name or organization has been provided
if (fh.IsValueEmpty("last_name") && fh.IsValueEmpty("organization"))
{
args.Cancel("Either last name or organization must be specified");
return;
}
if (fh.AllFieldsExist("first_name", "last_name"))
{
if (!fh.IsValueEmpty("first_name") && fh.IsValueEmpty("last_name"))
{
args.Cancel("If first name is specified, last name must also be specified.");
return;
}
}
// set the new cooperator_id (cno)
// cannot do this until after the row is written for cross-db-engine compatibility
// as sql server / mysql do not use a sequence concept and instead use an
// identity / autoincrement concept which requires row to be inserted first
// before the value can be read. See the TableRowSaved() method in this class.
fh.SetValue("first_name", fh.ToTitleCase("first_name"), typeof(string), false);
fh.SetValue("last_name", fh.ToTitleCase("last_name"), typeof(string), false);
// On new row...set "active" flag to X
if (args.SaveMode == SaveMode.Insert)
{
// new cooperator, make them active
fh.SetValue("status_code", "ACTIVE", typeof(string), true);
}
else if (args.SaveMode == SaveMode.Update)
{
// marking current_cooperator_id same as pk value, make them active
if (fh.AllFieldsExist("cooperator_id", "current_cooperator_id"))
{
if (fh.GetValue("cooperator_id", -1, true) == fh.GetValue("current_cooperator_id", -2, true))
{
fh.SetValue("status_code", "ACTIVE", typeof(string), true);
}
}
}
// FK ensures this, no longer needed.
//// validate geography_id...make sure we have a good geo (state/country)
//if (!fh.IsValueEmpty("geography_id")) {
// var dtGeo = args.ReadData("select geography_id from geography where geography_id = :geoid", ":geoid", fh.GetValue("geography_id", -1, true), DbType.Int32);
// if (dtGeo.Rows.Count == 0 || DataTriggerHelper.IsValueEmpty(dtGeo.Rows[0], args.Table.AliasName, "geography_id")) {
// args.Cancel("Invalid value for geography.");
// return;
// }
//}
// TODO: validate codes: requestor category (CAT), ARS region, discipline
// TODO: if have a category (cat) - Must supply a valid country
// upcase ORGID
fh.SetValueIfFieldExistsAndIsEmpty("organization_abbrev", fh.GetValue("organization_abbrev", "", true).ToString().ToUpper());
// initcap city
fh.SetValueIfFieldExistsAndIsEmpty("city", fh.ToTitleCase("city").ToString().ToUpper());
// upper zip
fh.SetValueIfFieldExistsAndIsEmpty("postal_index", fh.GetValue("postal_index", "", true).ToString().ToUpper());
// lower email
fh.SetValueIfFieldExistsAndIsEmpty("email", fh.GetValue("email", "", true).ToString().ToLower());
// validate address (if address line 2 but no address line 1, etc, format it all properly...put line 2 into line 1, etc)
// i.e. throw out missing address lines, move subsequent ones up as far as possible
// pull 2->1 if we need to
if (fh.AllFieldsExist("address_line1", "address_line2") && fh.IsValueEmpty("address_line1"))
{
fh.SetValue("address_line1", fh.GetValue("address_line2", DBNull.Value, true), typeof(string), false);
fh.SetValue("address_line2", DBNull.Value, typeof(string), false);
}
// pull 3->2 is we need to
if (fh.AllFieldsExist("address_line2", "address_line3") && fh.IsValueEmpty("address_line2"))
{
fh.SetValue("address_line2", fh.GetValue("address_line3", DBNull.Value, true), typeof(string), false);
fh.SetValue("address_line3", DBNull.Value, typeof(string), false);
}
// again, pull 2->1 if we need to (after doing moves above)
if (fh.AllFieldsExist("address_line1", "address_line2") && fh.IsValueEmpty("address_line1"))
{
fh.SetValue("address_line1", fh.GetValue("address_line2", DBNull.Value, true), typeof(string), false);
fh.SetValue("address_line2", DBNull.Value, typeof(string), false);
}
if (fh.AllFieldsExist("sys_lang_id") && fh.IsValueEmpty("sys_lang_id"))
{
fh.SetValue("sys_lang_id", 1, typeof(int), false);
}
//// Do a duplicate check ... coop unique index will take care of this but we want to warn them with a better message and a chance to recover (forms)
//var dtCoop = args.ReadData("select cooperator_id from cooperator where coalesce(full_name,'') = coalesce(:fn, '')", ":fn", fh.GetValue("full_name"));
//if (args.SaveMode == SaveMode.Insert && dtCoop.Rows.Count > 0) {
// args.Cancel("A cooperator with the same full name (" + fh.GetValue("full_name") + ") already exists.");
// return;
//} else if (args.SaveMode == SaveMode.Update && dtCoop.Rows.Count > 0 && ((int)dtCoop.Rows[0]["cooperator_id"])!= (int)fh.GetValue("cooperator_id")) {
// args.Cancel("A different cooperator with the same full name (" + fh.GetValue("full_name") + ") already exists.");
// return;
//}
// TODO: other specifics...not relevant...if city = Beltsville, Hyattsville, DC... set arsregion to BA
// TODO: if no country provided - make it United States
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var h = args.Helper;
// make sure 1 and only 1 of gensu/species is filled
if ((h.IsValueEmpty("taxonomy_genus_id") && h.IsValueEmpty("taxonomy_species_id")) || (!h.IsValueEmpty("taxonomy_genus_id") && !h.IsValueEmpty("taxonomy_species_id")))
{
args.Cancel("Either you have neither or both genus and species filled in. One and only one column is allowed.");
return;
}
//
// create simplified name
var cname = h.GetValue("name", "", true).ToString().ToUpper();
string cname2 = cname.Replace("-", "").Replace("'", "").Replace(" ", "");
cname2 = cname2.Replace("Á", "A");
cname2 = cname2.Replace("á", "a");
cname2 = cname2.Replace("É", "E");
cname2 = cname2.Replace("é", "e");
cname2 = cname2.Replace("Í", "I");
cname2 = cname2.Replace("í", "i");
cname2 = cname2.Replace("Ó", "O");
cname2 = cname2.Replace("ó", "o");
cname2 = cname2.Replace("Ú", "U");
cname2 = cname2.Replace("ú", "u");
cname2 = cname2.Replace("ý", "y");
cname2 = cname2.Replace("ă", "a");
cname2 = cname2.Replace("ğ", "g");
cname2 = cname2.Replace("ĭ", "i");
cname2 = cname2.Replace("Č", "C");
cname2 = cname2.Replace("č", "c");
cname2 = cname2.Replace("Ě", "E");
cname2 = cname2.Replace("ě", "e");
cname2 = cname2.Replace("Ř", "R");
cname2 = cname2.Replace("ř", "r");
cname2 = cname2.Replace("Š", "S");
cname2 = cname2.Replace("š", "s");
cname2 = cname2.Replace("Ž", "Z");
cname2 = cname2.Replace("ž", "z");
cname2 = cname2.Replace("Ç", "C");
cname2 = cname2.Replace("ç", "c");
cname2 = cname2.Replace("Ş", "S");
cname2 = cname2.Replace("ş", "s");
cname2 = cname2.Replace("â", "a");
cname2 = cname2.Replace("ê", "e");
cname2 = cname2.Replace("Î", "I");
cname2 = cname2.Replace("î", "i");
cname2 = cname2.Replace("ô", "o");
cname2 = cname2.Replace("ŝ", "s");
cname2 = cname2.Replace("û", "u");
cname2 = cname2.Replace("à", "a");
cname2 = cname2.Replace("è", "e");
cname2 = cname2.Replace("ì", "i");
cname2 = cname2.Replace("ò", "o");
cname2 = cname2.Replace("Å", "A");
cname2 = cname2.Replace("å", "a");
cname2 = cname2.Replace("Ø", "O");
cname2 = cname2.Replace("ø", "o");
cname2 = cname2.Replace("æ", "ae");
cname2 = cname2.Replace("œ", "oe");
cname2 = cname2.Replace("ß", "ss");
cname2 = cname2.Replace("ã", "a");
cname2 = cname2.Replace("Ñ", "N");
cname2 = cname2.Replace("ñ", "n");
cname2 = cname2.Replace("õ", "o");
cname2 = cname2.Replace("Ä", "A");
cname2 = cname2.Replace("ä", "a");
cname2 = cname2.Replace("ë", "e");
cname2 = cname2.Replace("ï", "i");
cname2 = cname2.Replace("Ö", "O");
cname2 = cname2.Replace("ö", "o");
cname2 = cname2.Replace("Ü", "U");
cname2 = cname2.Replace("ü", "u");
h.SetValue("simplified_name", cname2, typeof(string), false);
//args.Cancel("new name = " + cname2);
//return;
}
}
public override void TableRowSaving(ISaveDataTriggerArgs args)
{
/* why have FK if it can be overridden
* if (args.SaveMode == SaveMode.Delete && args.Table != null && args.Table.TableName.ToLower() == "taxonomy_genus") {
* // make sure to disassociate the type_taxonomy_genus_id before deleting (if needed)
* args.WriteData(@"update taxonomy_family set type_taxonomy_genus_id = null where type_taxonomy_genus_id = :id", ":id", args.OriginalPrimaryKeyID, DbType.Int32);
*
* // also remove the 'current' self-reference... May still be required in some of the other DBs
* args.WriteData(@"update taxonomy_genus set current_taxonomy_genus_id = null where taxonomy_genus_id = :id", ":id", args.OriginalPrimaryKeyID, DbType.Int32);
* }*/
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
var h = args.Helper;
/*"if instr(:NEW.genus,'-') = 0 then :NEW.genus := initcap(:NEW.genus);end if;
* F :NEW.subgenus IS NOT NULL THEN :NEW.subgenus := initcap(:NEW.subgenus); END IF;
* IF :NEW.section IS NOT NULL THEN :NEW.section := initcap(:NEW.section); END IF;
* IF :NEW.series IS NOT NULL THEN :NEW.series := initcap(:NEW.series); END IF;"*/
// 2 initcap various columns
string newstr = h.GetValue("genus_name", "", true).ToString().ToLower();
if (newstr.IndexOf("-") != -1)
{
newstr = newstr.Substring(0, 1).ToUpper() + newstr.Substring(1);
h.SetValue("genus_name", newstr, typeof(string), false);
}
if (h.FieldExists("subgenus_name") && !h.IsValueEmpty("subgenus_name"))
{
newstr = h.GetValue("subgenus_name", "", true).ToString().ToLower();
newstr = newstr.Substring(0, 1).ToUpper() + newstr.Substring(1);
h.SetValue("subgenus_name", newstr, typeof(string), false);
}
bool t_section = false;
if (h.FieldExists("section_name") && !h.IsValueEmpty("section_name"))
{
newstr = h.GetValue("section_name", "", true).ToString().ToLower();
newstr = newstr.Substring(0, 1).ToUpper() + newstr.Substring(1);
h.SetValue("section_name", newstr, typeof(string), false);
t_section = true;
}
if (h.FieldExists("subsection_name") && !h.IsValueEmpty("subsection_name"))
{
// 3 check subsection
/*"IF :NEW.subsection IS NOT NULL THEN
* IF :NEW.section IS NULL THEN
* raise_application_error (-20000,
* 'Can not enter subsection without section.');
* END IF;
* :NEW.subsection := initcap(:NEW.subsection);
* END IF;"*/
if (!t_section)
{
args.Cancel("Can not enter subsection without section.");
return;
}
newstr = h.GetValue("subsection_name", "", true).ToString().ToLower();
newstr = newstr.Substring(0, 1).ToUpper() + newstr.Substring(1);
h.SetValue("subsection_name", newstr, typeof(string), false);
}
bool t_series = false;
if (h.FieldExists("series_name") && !h.IsValueEmpty("series_name"))
{
newstr = h.GetValue("series_name", "", true).ToString().ToLower();
newstr = newstr.Substring(0, 1).ToUpper() + newstr.Substring(1);
h.SetValue("series_name", newstr, typeof(string), false);
t_series = true;
}
if (h.FieldExists("subseries_name") && !h.IsValueEmpty("subseries_name"))
{
if (!t_series)
{
args.Cancel("Can not enter subseries without series");
return;
}
newstr = h.GetValue("subseries_name", "", true).ToString().ToLower();
newstr = newstr.Substring(0, 1).ToUpper() + newstr.Substring(1);
h.SetValue("subseries_name", newstr, typeof(string), false);
}
// 6 check genus author
if (h.FieldExists("genus_authority") && !h.IsValueEmpty("genus_authority"))
{
CheckAuthority(args, h.GetValue("genus_authority", "", false).ToString());
}
}
}
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
);
}
}
}
public override void DataViewRowSaveFailed(ISaveDataTriggerArgs args)
{
// TODO: return a nice error if we couldn't find the taxonomy_species_id...
args.Cancel("The given taxon could not be located in the database.");
}