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);
}
}
}
}
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 void TableRowSaved(ISaveDataTriggerArgs args)
{
// post-row save trigger
if (args.SaveMode == SaveMode.Insert)
{
if (!args.Helper.IsValueEmpty("web_order_request_item_id"))
{
int?woriID = (int?)(args.Helper.GetValue("web_order_request_item_id", null, true));
if (woriID > 0)
{
// update web_order_request_item table status
args.WriteData(@"
update
web_order_request_item
set
status_code = 'ACCEPTED',
modified_date = :modifieddate
where
status_code = 'NEW' and
web_order_request_item_id = :woriID
", ":modifieddate", DateTime.UtcNow, ":woriID", woriID);
}
}
}
}
public override void TableRowSaved(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert || args.SaveMode == SaveMode.Update)
{
if (args.Helper.IsValueEmpty("percent_viable"))
{
args.WriteData("update inventory_viability set percent_viable = percent_dormant + percent_normal where inventory_viability_id = " + args.NewPrimaryKeyID);
}
}
}
public override void TableRowSaved(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert)
{
var h = args.Helper;
// make current_taxonomy_family_id field match the taxonomy_family_id field
if (h.IsValueEmpty("current_taxonomy_family_id"))
{
// args.WriteData(@"update taxonomy_family set current_taxonomy_family_id = :id1 where taxonomy_family_id = :id2", ":id1", args.NewPrimaryKeyID, DbType.Int32, ":id2", args.NewPrimaryKeyID, DbType.Int32);
args.WriteData(@"update taxonomy_family set current_taxonomy_family_id = taxonomy_family_id where taxonomy_family_id = :id1", ":id1", args.NewPrimaryKeyID, DbType.Int32);
}
}
}
public override void TableRowSaved(ISaveDataTriggerArgs args)
{
// post-row save trigger
if (args.SaveMode == SaveMode.Insert)
{
// make original_order_request_id field match the order_request_id field
if (args.Helper.IsValueEmpty("original_order_request_id"))
{
args.WriteData(@"
UPDATE order_request
SET original_order_request_id = :orid
WHERE order_request_id = :orid AND original_order_request_id IS NULL
", ":orid", args.NewPrimaryKeyID);
}
}
}
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));
}
}
}
}
}
public override void TableRowSaved(ISaveDataTriggerArgs args)
{
if (args.SaveMode == SaveMode.Insert)
{
if (args.Helper.IsValueEmpty("current_geography_id"))
{
// make current_geography_id field match the geography_id field
args.WriteData(@"
update
geography
set
current_geography_id = :id1
where
geography_id = :id2
", ":id1", args.NewPrimaryKeyID, ":id2", args.NewPrimaryKeyID);
}
}
}
public void TableRowSaved(ISaveDataTriggerArgs args)
{
// post-row save trigger
if (args.SaveMode == SaveMode.Insert)
{
if (args.Helper.IsValueEmpty("current_cooperator_id"))
{
// make current_cooperator_id field match the cooperator_id field
args.WriteData(@"
update
cooperator
set
current_cooperator_id = :coopid1
where
cooperator_id = :coopid2
", ":coopid1", args.NewPrimaryKeyID, ":coopid2", args.NewPrimaryKeyID);
}
}
}
public void CreateAnnotation(ISaveDataTriggerArgs args, int acid, int oldtsid, int newtsid, string annoType)
{
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
@annotype,
COALESCE(a.modified_date, a.created_date),
'MM/dd/yyyy',
COALESCE(a.modified_by, a.created_by),
i.inventory_id,
@oldtsid,
@newtsid,
COALESCE(a.modified_date, a.created_date),
COALESCE(a.modified_by, a.created_by),
COALESCE(a.modified_date, a.created_date),
a.owned_by
FROM accession a
INNER JOIN inventory i ON a.accession_id = i.accession_id AND i.form_type_code = '**'
WHERE a.accession_id = @acid
",
new DataParameters("@acid", acid, DbType.Int32,
"@oldtsid", oldtsid, DbType.Int32,
"@newtsid", newtsid, DbType.Int32,
"@annotype", annoType, DbType.String));
}
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);
// }
// }
}
}
}
}
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
);
}
}
}
public override void TableRowSaved(ISaveDataTriggerArgs args)
{
// post-row save trigger
if (args.SaveMode == SaveMode.Insert)
{
//args.Cancel("Got this far in non-pi trigger.");
var helper = args.Helper;
// 5) create an accession_inv_name where prefix is not PI
string aiName = helper.GetValue("accession_number_part1", "", true).ToString();
if (aiName != "PI")
{
if (!helper.IsValueEmpty("accession_number_part2"))
{
//aiName += " number here";
aiName += " " + helper.GetValue("accession_number_part2", null, true);
}
if (!helper.IsValueEmpty("accession_number_part3"))
{
aiName += " " + helper.GetValue("accession_number_part3", null, true);
}
args.WriteData(@"
INSERT INTO accession_inv_name
(inventory_id,
category_code,
plant_name,
plant_name_rank,
name_group_id,
created_date,
created_by,
modified_date,
modified_by,
owned_date,
owned_by)
SELECT
i.inventory_id,
'SITE' AS 'category_code',
@name AS plant_name,
1080 AS 'plant_name_rank',
ng.name_group_id,
a.created_date,
a.created_by,
a.modified_date,
a.modified_by,
a.owned_date,
a.owned_by
FROM accession a
INNER JOIN inventory i ON a.accession_id = i.accession_id AND i.form_type_code = '**'
LEFT JOIN name_group ng ON a.accession_number_part1 = ng.group_name
WHERE a.accession_id = @acid
",
new DataParameters("@acid", args.NewPrimaryKeyID, DbType.Int32,
"@name", aiName, DbType.String));
// timing check
//args.WriteData(@"UPDATE accession SET note = note + 'Additional data trigger was here.' WHERE accession_id = " + args.NewPrimaryKeyID);
}
}
}