OpenPV - Spontaneous Form Creation Part 1: Difference between revisions

From OpenRIMS Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
The following script will create a standard Spontaneous Reporting form in OpenRIMS-PV.<blockquote>/**************************************************/
The following script will create a standard Spontaneous Reporting form in OpenRIMS-PV.<blockquote>/*The following script will create a standard Spontaneous Reporting form in OpenRIMS-PV.*/


/DATASET/
/**************************************************/
 
/*DATASET*/


/**************************************************/
/**************************************************/
Line 13: Line 15:
/**************************************************/
/**************************************************/


/CATEGORY Patient Information/
/*CATEGORY Patient Information*/


/**************************************************/
/**************************************************/
Line 145: Line 147:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Months', 0, 0, 0, @fid);  
VALUES ('Months', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
Line 201: Line 203:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Female', 0, 0, 0, @fid);  
VALUES ('Female', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Unknown', 0, 0, 0, @fid);  
VALUES ('Unknown', 0, 0, 0, @fid);


INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
Line 237: Line 239:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('South Asian', 0, 0, 0, @fid);  
VALUES ('South Asian', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Southeast Asian', 0, 0, 0, @fid);  
VALUES ('Southeast Asian', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Black', 0, 0, 0, @fid);  
VALUES ('Black', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('White', 0, 0, 0, @fid);  
VALUES ('White', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Middle Eastern', 0, 0, 0, @fid);  
VALUES ('Middle Eastern', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Other', 0, 1, 0, @fid);  
VALUES ('Other', 0, 1, 0, @fid);


INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
Line 271: Line 273:
set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);


/**************************************************
/**************************************************/


CATEGORY Product Information
/*CATEGORY Product Information*/


**************************************************/
/**************************************************/


INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)
INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)


VALUES ('Product Information', 2, @dsid, 'Product Information', 'Please enter information about the product you suspect caused the reaction and about other products taken.');  
VALUES ('Product Information', 2, @dsid, 'Product Information', 'Please enter information about the product you suspect caused the reaction and about other products taken.');


set @dscid = (SELECT @@IDENTITY);
set @dscid = (SELECT @@IDENTITY);
Line 287: Line 289:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 7);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 7);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 293: Line 295:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Product Information', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '712CA632-0CD0-4418-9176-FB0B95AEE8A1');  
VALUES ('Product Information', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '712CA632-0CD0-4418-9176-FB0B95AEE8A1');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 299: Line 301:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (10, @dscid, @deid, 0, 0, 'Name of product', 'Name of product.');  
VALUES (10, @dscid, @deid, 0, 0, 'Name of product', 'Name of product.');


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 307: Line 309:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (1, 100, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (1, 100, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 313: Line 315:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Product', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 1, 'Product Name', 'Enter the brand or generic name');  
VALUES ('Product', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 1, 'Product Name', 'Enter the brand or generic name');


-- Product Suspected
-- Product Suspected
Line 319: Line 321:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (1, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 5);  
VALUES (1, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 5);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 325: Line 327:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Product Suspected', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 2, 'Is the product suspected?', <nowiki>''</nowiki>);  
VALUES ('Product Suspected', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 2, 'Is the product suspected?', <nowiki>''</nowiki>);


-- Drug strength
-- Drug strength
Line 331: Line 333:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, 0, 99999999.00, 1.00, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 4);  
VALUES (0, NULL, 0, 99999999.00, 1.00, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 4);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 337: Line 339:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Drug Strength', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 3, 'Drug Strength', <nowiki>''</nowiki>);  
VALUES ('Drug Strength', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 3, 'Drug Strength', <nowiki>''</nowiki>);


-- Drug strength unit
-- Drug strength unit
Line 343: Line 345:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 349: Line 351:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('milligrams (mg)', 0, 0, 0, @fid);  
VALUES ('milligrams (mg)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('milligrams/milliliters (mg/ml)', 0, 0, 0, @fid);  
VALUES ('milligrams/milliliters (mg/ml)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('grams (gm)', 0, 0, 0, @fid);  
VALUES ('grams (gm)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('kilograms (kg)', 0, 0, 0, @fid);  
VALUES ('kilograms (kg)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('micrograms (mcg)', 0, 0, 0, @fid);  
VALUES ('micrograms (mcg)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('milliliters (ml)', 0, 0, 0, @fid);  
VALUES ('milliliters (ml)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('liters (l)', 0, 0, 0, @fid);  
VALUES ('liters (l)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('milliequivalents (meq)', 0, 0, 0, @fid);  
VALUES ('milliequivalents (meq)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('percent (%)', 0, 0, 0, @fid);  
VALUES ('percent (%)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('drops (gtt)', 0, 0, 0, @fid);  
VALUES ('drops (gtt)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Other', 0, 1, 0, @fid);  
VALUES ('Other', 0, 1, 0, @fid);


INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
Line 399: Line 401:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, 0, 99999999.00, 1.00, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 4);  
VALUES (0, NULL, 0, 99999999.00, 1.00, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 4);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 405: Line 407:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Dose Number', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 5, 'Dose number', <nowiki>''</nowiki>);  
VALUES ('Dose Number', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 5, 'Dose number', <nowiki>''</nowiki>);


-- Dose Unit
-- Dose Unit
Line 411: Line 413:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 417: Line 419:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('tablet(s)', 1, 0, 0, @fid);  
VALUES ('tablet(s)', 1, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('capsule(s)', 0, 0, 0, @fid);  
VALUES ('capsule(s)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('drop(s)', 0, 0, 0, @fid);  
VALUES ('drop(s)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('teaspoon(s)', 0, 0, 0, @fid);  
VALUES ('teaspoon(s)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('tablespoon(s)', 0, 0, 0, @fid);  
VALUES ('tablespoon(s)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('milliliter(s)', 0, 0, 0, @fid);  
VALUES ('milliliter(s)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('suppository(ies)', 0, 0, 0, @fid);  
VALUES ('suppository(ies)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('injection(s)', 0, 0, 0, @fid);  
VALUES ('injection(s)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('puff(s)', 0, 0, 0, @fid);  
VALUES ('puff(s)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('inhalation(s)', 0, 0, 0, @fid);  
VALUES ('inhalation(s)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('patch(es)', 0, 0, 0, @fid);  
VALUES ('patch(es)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Other', 0, 1, 0, @fid);  
VALUES ('Other', 0, 1, 0, @fid);


INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Dose Unit', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 6, 'Unit of dosage', <nowiki>''</nowiki>);  
VALUES ('Dose Unit', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 6, 'Unit of dosage', <nowiki>''</nowiki>);


-- Drug route of administration
-- Drug route of administration
Line 471: Line 473:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 477: Line 479:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('By mouth', 0, 0, 0, @fid);  
VALUES ('By mouth', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Taken under the tongue', 0, 0, 0, @fid);  
VALUES ('Taken under the tongue', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Applied to a surface, usually skin', 0, 0, 0, @fid);  
VALUES ('Applied to a surface, usually skin', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Inhalation', 0, 0, 0, @fid);  
VALUES ('Inhalation', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Applied as a medicated patch to skin', 0, 0, 0, @fid);  
VALUES ('Applied as a medicated patch to skin', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Given into/under the skin', 0, 0, 0, @fid);  
VALUES ('Given into/under the skin', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Into a vein', 0, 0, 0, @fid);  
VALUES ('Into a vein', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Into a muscle', 0, 0, 0, @fid);  
VALUES ('Into a muscle', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Into the ear', 0, 0, 0, @fid);  
VALUES ('Into the ear', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Into the eye', 0, 0, 0, @fid);  
VALUES ('Into the eye', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Rectal', 0, 0, 0, @fid);  
VALUES ('Rectal', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Vaginal', 0, 0, 0, @fid);  
VALUES ('Vaginal', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Other', 0, 1, 0, @fid);  
VALUES ('Other', 0, 1, 0, @fid);


INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Drug route of administration', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 7, 'Drug route of administration', <nowiki>''</nowiki>);  
VALUES ('Drug route of administration', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 7, 'Drug route of administration', <nowiki>''</nowiki>);


-- Drug Start Date
-- Drug Start Date
Line 535: Line 537:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 547: Line 549:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 553: Line 555:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Drug End Date', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 9, 'Date drug usage ended', <nowiki>''</nowiki>);  
VALUES ('Drug End Date', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 9, 'Date drug usage ended', <nowiki>''</nowiki>);


-- Drug Treatment Duration
-- Drug Treatment Duration
Line 559: Line 561:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, 0, 99999.00, 1.00, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 4);  
VALUES (0, NULL, 0, 99999.00, 1.00, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 4);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 565: Line 567:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Drug Treatment Duration', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 10, 'Duration of usage', <nowiki>''</nowiki>);  
VALUES ('Drug Treatment Duration', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 10, 'Duration of usage', <nowiki>''</nowiki>);


-- Drug Treatment Duration Unit
-- Drug Treatment Duration Unit
Line 577: Line 579:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('801=Year', 1, 0, 0, @fid);  
VALUES ('801=Year', 1, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('802=Month', 0, 0, 0, @fid);  
VALUES ('802=Month', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('803=Week', 0, 0, 0, @fid);  
VALUES ('803=Week', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('804=Day', 0, 0, 0, @fid);  
VALUES ('804=Day', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('805=Hour', 0, 0, 0, @fid);  
VALUES ('805=Hour', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('806=Minute', 0, 0, 0, @fid);  
VALUES ('806=Minute', 0, 0, 0, @fid);


INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Drug Treatment Duration Unit', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 11, 'Unit of duration', <nowiki>''</nowiki>);  
VALUES ('Drug Treatment Duration Unit', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 11, 'Unit of duration', <nowiki>''</nowiki>);


-- Drug Indication
-- Drug Indication
Line 607: Line 609:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, 250, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, 250, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 613: Line 615:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Drug Indication', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 12, 'Indication for drug usage', <nowiki>''</nowiki>);  
VALUES ('Drug Indication', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 12, 'Indication for drug usage', <nowiki>''</nowiki>);


-- Product Frequency
-- Product Frequency
Line 619: Line 621:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, 50, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, 50, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 625: Line 627:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Product Frequency', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 12, 'Frequency of product usage', <nowiki>''</nowiki>);  
VALUES ('Product Frequency', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 12, 'Frequency of product usage', <nowiki>''</nowiki>);


-- Product Batch Number
-- Product Batch Number
Line 631: Line 633:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, 25, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, 25, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 643: Line 645:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 649: Line 651:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Product withdrawn', 0, 0, 0, @fid);  
VALUES ('Product withdrawn', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Dose reduced', 0, 0, 0, @fid);  
VALUES ('Dose reduced', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Dose increased', 0, 0, 0, @fid);  
VALUES ('Dose increased', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Dose not changed', 0, 0, 0, @fid);  
VALUES ('Dose not changed', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Unknown', 0, 0, 0, @fid);  
VALUES ('Unknown', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Not applicable', 0, 0, 0, @fid);  
VALUES ('Not applicable', 0, 0, 0, @fid);


INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Actions taken with product', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 13, 'Actions taken with product', <nowiki>''</nowiki>);  
VALUES ('Actions taken with product', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 13, 'Actions taken with product', <nowiki>''</nowiki>);


-- Product challenge
-- Product challenge
Line 679: Line 681:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 685: Line 687:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Yes', 0, 0, 0, @fid);  
VALUES ('Yes', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('No', 0, 0, 0, @fid);  
VALUES ('No', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Unknown', 0, 0, 0, @fid);  
VALUES ('Unknown', 0, 0, 0, @fid);


INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Product challenge', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 14, 'Was there a challenge to the usage of the product?', <nowiki>''</nowiki>);  
VALUES ('Product challenge', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 14, 'Was there a challenge to the usage of the product?', <nowiki>''</nowiki>);


-- Product rechallenge
-- Product rechallenge
Line 703: Line 705:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 709: Line 711:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Yes', 1, 0, 0, @fid);  
VALUES ('Yes', 1, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('No', 0, 0, 0, @fid);  
VALUES ('No', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Didn<nowiki>''</nowiki>t restart', 0, 0, 0, @fid);  
VALUES ('Didn<nowiki>''</nowiki>t restart', 0, 0, 0, @fid);


INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Product rechallenge', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 15, 'Was there a rechallenge to the usage of the product?', <nowiki>''</nowiki>);  
VALUES ('Product rechallenge', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 15, 'Was there a rechallenge to the usage of the product?', <nowiki>''</nowiki>);


/**************************************************
/**************************************************/


CATEGORY Reaction and Treatment
/*CATEGORY Reaction and Treatment*/


**************************************************/
/**************************************************/


INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)
INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)


VALUES ('Reaction and Treatment', 3, @dsid, 'Reaction and Treatment', 'Enter information about what happened and how it was treated.');  
VALUES ('Reaction and Treatment', 3, @dsid, 'Reaction and Treatment', 'Enter information about what happened and how it was treated.');


set @dscid = (SELECT @@IDENTITY);
set @dscid = (SELECT @@IDENTITY);
Line 739: Line 741:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (1, 500, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (1, 500, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 745: Line 747:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Description of reaction', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 'ACD938A4-76D1-44CE-A070-2B8DF0FE9E0F');  
VALUES ('Description of reaction', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 'ACD938A4-76D1-44CE-A070-2B8DF0FE9E0F');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 751: Line 753:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (84, @dscid, @deid, 0, 0, 'Description of reaction', <nowiki>''</nowiki>);  
VALUES (84, @dscid, @deid, 0, 0, 'Description of reaction', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 759: Line 761:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 765: Line 767:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Reaction known start date', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 'F5EEB382-D4A5-41A1-A447-37D5ECA50B99');  
VALUES ('Reaction known start date', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 'F5EEB382-D4A5-41A1-A447-37D5ECA50B99');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 779: Line 781:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 799: Line 801:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 805: Line 807:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Resulted in death', 0, 0, 0, @fid);  
VALUES ('Resulted in death', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Is life-threatening', 0, 0, 0, @fid);  
VALUES ('Is life-threatening', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Requires inpatient hospitalization or prolongation of existing hospitalization', 0, 0, 0, @fid);  
VALUES ('Requires inpatient hospitalization or prolongation of existing hospitalization', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Results in persistent or significant disability/incapacity (as per reporter<nowiki>''</nowiki>s opinion)', 0, 0, 0, @fid);  
VALUES ('Results in persistent or significant disability/incapacity (as per reporter<nowiki>''</nowiki>s opinion)', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Is a congenital anomaly/birth defect', 0, 0, 0, @fid);  
VALUES ('Is a congenital anomaly/birth defect', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Other medically important condition', 0, 1, 0, @fid);  
VALUES ('Other medically important condition', 0, 1, 0, @fid);


INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Reaction serious details', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '302C07C9-B0E0-46AB-9EF8-5D5C2F756BF1');  
VALUES ('Reaction serious details', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '302C07C9-B0E0-46AB-9EF8-5D5C2F756BF1');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 835: Line 837:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (91, @dscid, @deid, 0, 0, 'Did any of these reactions happen?', <nowiki>''</nowiki>);  
VALUES (91, @dscid, @deid, 0, 0, 'Did any of these reactions happen?', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 843: Line 845:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 5);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 5);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 849: Line 851:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Treatment given for reaction', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '3A84016D-9A58-464F-A316-027A095291CE');  
VALUES ('Treatment given for reaction', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '3A84016D-9A58-464F-A316-027A095291CE');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 855: Line 857:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (93, @dscid, @deid, 0, 0, 'Was treatment given for the reaction?', <nowiki>''</nowiki>);  
VALUES (93, @dscid, @deid, 0, 0, 'Was treatment given for the reaction?', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 863: Line 865:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, 500, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, 500, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 869: Line 871:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Treatment given for reaction details', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '24DE20BE-AB13-487B-B679-D62D6FEE8814');  
VALUES ('Treatment given for reaction details', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '24DE20BE-AB13-487B-B679-D62D6FEE8814');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 875: Line 877:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (94, @dscid, @deid, 0, 0, 'What treatment was given for the reaction?', <nowiki>''</nowiki>);  
VALUES (94, @dscid, @deid, 0, 0, 'What treatment was given for the reaction?', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 883: Line 885:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 889: Line 891:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Recovered/resolved', 0, 0, 0, @fid);  
VALUES ('Recovered/resolved', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Recovering/resolving', 0, 0, 0, @fid);  
VALUES ('Recovering/resolving', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Not recovered/not resolved', 0, 0, 0, @fid);  
VALUES ('Not recovered/not resolved', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Recovered/resolved with permanent complications', 0, 0, 0, @fid);  
VALUES ('Recovered/resolved with permanent complications', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Fatal', 0, 0, 0, @fid);  
VALUES ('Fatal', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Unknown', 0, 0, 0, @fid);  
VALUES ('Unknown', 0, 0, 0, @fid);


INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Outcome of reaction', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '976F6C53-78F2-4007-8F39-54057E554EEB');  
VALUES ('Outcome of reaction', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '976F6C53-78F2-4007-8F39-54057E554EEB');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 919: Line 921:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (95, @dscid, @deid, 0, 0, 'What was the outcome of the reaction?', <nowiki>''</nowiki>);  
VALUES (95, @dscid, @deid, 0, 0, 'What was the outcome of the reaction?', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 933: Line 935:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Reaction date of recovery', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 'F977C2F8-C7DD-4AFE-BCAA-1C06BD54D155');  
VALUES ('Reaction date of recovery', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 'F977C2F8-C7DD-4AFE-BCAA-1C06BD54D155');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 939: Line 941:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (96, @dscid, @deid, 0, 0, 'What was the date of recovery from the reaction?', <nowiki>''</nowiki>);  
VALUES (96, @dscid, @deid, 0, 0, 'What was the date of recovery from the reaction?', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 947: Line 949:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 953: Line 955:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Reaction date of death', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '8B15C037-9C92-4AD4-A8F4-6C4042D40D9D');  
VALUES ('Reaction date of death', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '8B15C037-9C92-4AD4-A8F4-6C4042D40D9D');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 959: Line 961:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (97, @dscid, @deid, 0, 0, 'Enter date if patient died from the reaction', <nowiki>''</nowiki>);  
VALUES (97, @dscid, @deid, 0, 0, 'Enter date if patient died from the reaction', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 973: Line 975:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Reaction other relevant info', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '7BBEC54B-65C3-4BA6-B5A7-83C3C473F803');  
VALUES ('Reaction other relevant info', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '7BBEC54B-65C3-4BA6-B5A7-83C3C473F803');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 983: Line 985:
set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);


/**************************************************
/**************************************************/


CATEGORY Test Results
/*CATEGORY Test Results*/


**************************************************/
/**************************************************/


INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)
INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)
Line 999: Line 1,001:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 7);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 7);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,005: Line 1,007:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Test Results', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '12D7089D-1603-4309-99DE-60F20F9A005E');  
VALUES ('Test Results', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '12D7089D-1603-4309-99DE-60F20F9A005E');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 1,011: Line 1,013:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (10, @dscid, @deid, 0, 0, 'Test Results', 'Test Results');  
VALUES (10, @dscid, @deid, 0, 0, 'Test Results', 'Test Results');


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 1,019: Line 1,021:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (1, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);  
VALUES (1, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 6);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,025: Line 1,027:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Test Date', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 1, 'Date the test was conducted', <nowiki>''</nowiki>);  
VALUES ('Test Date', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 1, 'Date the test was conducted', <nowiki>''</nowiki>);


-- Test Name
-- Test Name
Line 1,031: Line 1,033:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (1, 100, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (1, 100, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,037: Line 1,039:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Test Name', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 2, 'Name of the test', <nowiki>''</nowiki>);  
VALUES ('Test Name', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 2, 'Name of the test', <nowiki>''</nowiki>);


-- Test Result
-- Test Result
Line 1,043: Line 1,045:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (1, 50, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (1, 50, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,049: Line 1,051:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Test Result', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 3, 'Result of the test', <nowiki>''</nowiki>);  
VALUES ('Test Result', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 3, 'Result of the test', <nowiki>''</nowiki>);


-- Test Unit
-- Test Unit
Line 1,055: Line 1,057:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, 35, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, 35, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,061: Line 1,063:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Test Unit', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 4, 'Unit of the test result', <nowiki>''</nowiki>);  
VALUES ('Test Unit', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 4, 'Unit of the test result', <nowiki>''</nowiki>);


-- Low Test Range
-- Low Test Range
Line 1,067: Line 1,069:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, 50, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, 50, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,073: Line 1,075:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('Low Test Range', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 5, 'Lower limit of the test result', <nowiki>''</nowiki>);  
VALUES ('Low Test Range', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 5, 'Lower limit of the test result', <nowiki>''</nowiki>);


-- High Test Range
-- High Test Range
Line 1,079: Line 1,081:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, 50, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, 50, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,085: Line 1,087:
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('High Test Range', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 6, 'Upper limit of the test result', <nowiki>''</nowiki>);  
VALUES ('High Test Range', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 6, 'Upper limit of the test result', <nowiki>''</nowiki>);


-- More Information
-- More Information
Line 1,091: Line 1,093:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,097: Line 1,099:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Yes', 1, 0, 0, @fid);  
VALUES ('Yes', 1, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('No', 0, 0, 0, @fid);  
VALUES ('No', 0, 0, 0, @fid);


INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)
INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)


VALUES ('More Information', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 7, 'Any additional information', <nowiki>''</nowiki>);  
VALUES ('More Information', @fid, @deid, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 7, 'Any additional information', <nowiki>''</nowiki>);


/**************************************************
/**************************************************/


CATEGORY Reporter Information
/*CATEGORY Reporter Information*/


**************************************************/
/**************************************************/


INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)
INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)


VALUES ('Reporter Information', 5, @dsid, 'Reporter Information', 'Enter information about the person reporting the reaction.');  
VALUES ('Reporter Information', 5, @dsid, 'Reporter Information', 'Enter information about the person reporting the reaction.');


set @dscid = (SELECT @@IDENTITY);
set @dscid = (SELECT @@IDENTITY);
Line 1,123: Line 1,125:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (1, 60, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (1, 60, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,129: Line 1,131:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Reporter Name', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '926A07E1-8B83-41CA-8949-739717924AD9');  
VALUES ('Reporter Name', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '926A07E1-8B83-41CA-8949-739717924AD9');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 1,135: Line 1,137:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (91, @dscid, @deid, 1, 0, 'Name or initials of person reporting information', <nowiki>''</nowiki>);  
VALUES (91, @dscid, @deid, 1, 0, 'Name or initials of person reporting information', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 1,143: Line 1,145:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, 100, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, 100, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,149: Line 1,151:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Reporter Telephone Number', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '1AC02BD6-5C24-4A37-9742-C6B868ED985D');  
VALUES ('Reporter Telephone Number', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '1AC02BD6-5C24-4A37-9742-C6B868ED985D');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 1,155: Line 1,157:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (93, @dscid, @deid, 0, 0, 'Contact telephone number for reporter', <nowiki>''</nowiki>);  
VALUES (93, @dscid, @deid, 0, 0, 'Contact telephone number for reporter', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 1,163: Line 1,165:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, 100, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, 100, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,169: Line 1,171:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Reporter E-mail Address', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 'FFDA770F-DADE-4F6E-B39A-D5E929AEDE2E');  
VALUES ('Reporter E-mail Address', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 'FFDA770F-DADE-4F6E-B39A-D5E929AEDE2E');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 1,175: Line 1,177:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (93, @dscid, @deid, 0, 0, 'Contact e-mail address for reporter', <nowiki>''</nowiki>);  
VALUES (93, @dscid, @deid, 0, 0, 'Contact e-mail address for reporter', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 1,183: Line 1,185:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 2);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,189: Line 1,191:
INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Physician', 0, 0, 0, @fid);  
VALUES ('Physician', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Pharmacist', 0, 0, 0, @fid);  
VALUES ('Pharmacist', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Consumer or other non-health professional', 0, 0, 0, @fid);  
VALUES ('Consumer or other non-health professional', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Other Health Professional', 0, 0, 0, @fid);  
VALUES ('Other Health Professional', 0, 0, 0, @fid);


INSERT into fieldvalue (value, `default`, other, unknown, field_id)
INSERT into fieldvalue (value, `default`, other, unknown, field_id)


VALUES ('Lawyer', 0, 0, 0, @fid);  
VALUES ('Lawyer', 0, 0, 0, @fid);


INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Reporter Profession', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '79ABA1EC-3979-4BA2-80AF-1F817C8243B9');  
VALUES ('Reporter Profession', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '79ABA1EC-3979-4BA2-80AF-1F817C8243B9');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 1,215: Line 1,217:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (94, @dscid, @deid, 1, 0, 'Profession of reporter', <nowiki>''</nowiki>);  
VALUES (94, @dscid, @deid, 1, 0, 'Profession of reporter', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 1,223: Line 1,225:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, 30, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);  
VALUES (0, 30, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 3);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,229: Line 1,231:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Report Reference Number', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 'C2EDD7A9-8031-4836-99A0-32C84D99A0AC');  
VALUES ('Report Reference Number', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, 'C2EDD7A9-8031-4836-99A0-32C84D99A0AC');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 1,235: Line 1,237:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (96, @dscid, @deid, 0, 0, 'Report reference number (if any)', <nowiki>''</nowiki>);  
VALUES (96, @dscid, @deid, 0, 0, 'Report reference number (if any)', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 1,255: Line 1,257:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (98, @dscid, @deid, 1, 0, 'Reporter place of practise', <nowiki>''</nowiki>);  
VALUES (98, @dscid, @deid, 1, 0, 'Reporter place of practise', <nowiki>''</nowiki>);


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);
Line 1,263: Line 1,265:
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)
INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)


VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 5);  
VALUES (0, NULL, NULL, NULL, NULL, <nowiki>''</nowiki>, NULL, <nowiki>''</nowiki>, 0, 5);


set @fid = (SELECT @@IDENTITY);
set @fid = (SELECT @@IDENTITY);
Line 1,269: Line 1,271:
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)
INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)


VALUES ('Keep Reporter Confidential', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '63AE2D15-029B-4D7C-87BA-16C52A67A909');  
VALUES ('Keep Reporter Confidential', @fid, 1, <nowiki>''</nowiki>, <nowiki>''</nowiki>, 0, '63AE2D15-029B-4D7C-87BA-16C52A67A909');


set @deid = (SELECT @@IDENTITY);
set @deid = (SELECT @@IDENTITY);
Line 1,275: Line 1,277:
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)
INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)


VALUES (100, @dscid, @deid, 0, 0, 'Keep reporter confidential?', 'Do you want your identity kept confidential except to be contacted by the national medical regulatory authority or the World Health Organization if they need additional information?');  
VALUES (100, @dscid, @deid, 0, 0, 'Keep reporter confidential?', 'Do you want your identity kept confidential except to be contacted by the national medical regulatory authority or the World Health Organization if they need additional information?');


set @dceid = (SELECT @@IDENTITY);
set @dceid = (SELECT @@IDENTITY);


/**************************************************
/**************************************************/


SET ORDERING
/*SET ORDERING*/


**************************************************/
/**************************************************/


SELECT ds.DatasetName, dc.DatasetCategoryName, de.ElementName FROM Dataset ds
SELECT ds.DatasetName, dc.DatasetCategoryName, de.ElementName FROM Dataset ds


INNER JOIN DatasetCategory dc ON ds.Id = dc.Dataset_Id  
INNER JOIN DatasetCategory dc ON ds.Id = dc.Dataset_Id


INNER JOIN DatasetCategoryElement dce ON dc.Id = dce.DatasetCategory_Id  
INNER JOIN DatasetCategoryElement dce ON dc.Id = dce.DatasetCategory_Id


INNER JOIN DatasetElement de ON dce.DatasetElement_Id = de.Id  
INNER JOIN DatasetElement de ON dce.DatasetElement_Id = de.Id


where ds.Id = @dsid</blockquote>
where ds.Id = @dsid</blockquote>

Latest revision as of 02:00, 23 June 2024

The following script will create a standard Spontaneous Reporting form in OpenRIMS-PV.

/*The following script will create a standard Spontaneous Reporting form in OpenRIMS-PV.*/

/**************************************************/

/*DATASET*/

/**************************************************/

INSERT INTO dataset (DatasetName, active, initialiseprocess, RulesProcess, help, created, lastupdated, contexttype_id, createdby_id, updatedby_Id, issystem)

VALUES ('Spontaneous Report 2', 1, '', '', 'Suspected adverse drug reaction (ADR) online reporting form', now(), now(), 4, 1, 1, 1);

set @dsid = (SELECT @@IDENTITY);

/**************************************************/

/*CATEGORY Patient Information*/

/**************************************************/

INSERT INTO datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)

VALUES ('Patient Information', 1, @dsid, 'Patient Information', 'Please enter some information about the person who had the adverse reaction.');

set @dscid = (SELECT @@IDENTITY);

-- Initials

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (1, 5, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Initials', @fid, 1, '', '', 0, '29CD2157-8FB6-4883-A4E6-A4B9EDE6B36B');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (1, @dscid, @deid, 0, 0, 'Initials of Patient', 'Enter patient''s initials here OR their ID number and type below.');

set @dceid = (SELECT @@IDENTITY);

-- Identification Number

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 30, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Identification Number', @fid, 1, '', '', 0, '5A2E89A9-8240-4665-967D-0C655CF281B7');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (2, @dscid, @deid, 1, 0, 'Identification Number', 'Enter patient''s ID number OR enter their initials above..');

set @dceid = (SELECT @@IDENTITY);

-- Identification Type

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('National Identity', 1, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Other', 0, 1, 0, @fid);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Identification Type', @fid, 1, '', '', 0, 'E3CDDEB3-E129-4161-AC91-4DD91CC8AD4B');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (3, @dscid, @deid, 1, 0, 'Identification Type', 'If you entered a patient ID number, specify the ID type here.');

set @dceid = (SELECT @@IDENTITY);

-- Date of Birth

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 6);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Date of Birth', @fid, 1, '', '', 0, '0D704069-5C50-4085-8FE1-355BB64EF196');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (4, @dscid, @deid, 1, 0, 'Patient Date of Birth', 'Enter the patient''s date of birth here OR enter their age below.');

set @dceid = (SELECT @@IDENTITY);

-- Age

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, 0, 140.00, 0.00, '', NULL, '', 0, 4);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Age', @fid, 1, '', '', 0, 'D314C438-5ABA-4ED2-855D-1A5B22B5A301');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (5, @dscid, @deid, 1, 0, 'Age', 'Enter the patient''s age here OR enter their date of birth above.');

set @dceid = (SELECT @@IDENTITY);

-- Age Unit

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Years', 1, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Months', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Weeks', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Days', 0, 0, 0, @fid);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Age Unit', @fid, 1, '', '', 0, '80C219DC-238C-487E-A3D5-8919ABA674B1');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (6, @dscid, @deid, 1, 0, 'Age Unit of Measure', 'Enter weeks, months, or years for the patient''s age here.');

set @dceid = (SELECT @@IDENTITY);

-- Weight (kg)

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, 1, 159.90, 1.10, '', NULL, '', 0, 4);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Weight  (kg)', @fid, 1, '', '', 0, '985BD25D-54E7-4A24-8636-6DBC0F9C7B96');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (7, @dscid, @deid, 1, 0, 'Patient''s weight (kg)', '');

set @dceid = (SELECT @@IDENTITY);

-- Sex

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Male', 1, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Female', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Unknown', 0, 0, 0, @fid);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Sex', @fid, 1, '', '', 0, 'E061D363-534E-4EA4-B6E5-F1C531931B12');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (8, @dscid, @deid, 0, 0, 'Sex', '');

set @dceid = (SELECT @@IDENTITY);

-- Ethnic Group

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Asian', 1, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('East Asian', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('South Asian', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Southeast Asian', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Black', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('White', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Middle Eastern', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Other', 0, 1, 0, @fid);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Ethnic Group', @fid, 1, '', '', 0, 'DF6717EF-A674-46DD-B738-859355ECA9A1');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (9, @dscid, @deid, 1, 0, 'Ethnic Group of Patient', '');

set @dceid = (SELECT @@IDENTITY);

/**************************************************/

/*CATEGORY Product Information*/

/**************************************************/

INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)

VALUES ('Product Information', 2, @dsid, 'Product Information', 'Please enter information about the product you suspect caused the reaction and about other products taken.');

set @dscid = (SELECT @@IDENTITY);

-- Product Information Table

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 7);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Product Information', @fid, 1, '', '', 0, '712CA632-0CD0-4418-9176-FB0B95AEE8A1');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (10, @dscid, @deid, 0, 0, 'Name of product', 'Name of product.');

set @dceid = (SELECT @@IDENTITY);

-- Product

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (1, 100, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Product', @fid, @deid, '', '', 0, 1, 'Product Name', 'Enter the brand or generic name');

-- Product Suspected

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (1, NULL, NULL, NULL, NULL, '', NULL, '', 0, 5);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Product Suspected', @fid, @deid, '', '', 0, 2, 'Is the product suspected?', '');

-- Drug strength

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, 0, 99999999.00, 1.00, '', NULL, '', 0, 4);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Drug Strength', @fid, @deid, '', '', 0, 3, 'Drug Strength', '');

-- Drug strength unit

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('milligrams (mg)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('milligrams/milliliters (mg/ml)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('grams (gm)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('kilograms (kg)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('micrograms (mcg)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('milliliters (ml)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('liters (l)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('milliequivalents (meq)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('percent (%)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('drops (gtt)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Other', 0, 1, 0, @fid);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Drug strength unit', @fid, @deid, '', '', 0, 4, 'Unit of strength', '');

-- Dose Number

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, 0, 99999999.00, 1.00, '', NULL, '', 0, 4);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Dose Number', @fid, @deid, '', '', 0, 5, 'Dose number', '');

-- Dose Unit

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('tablet(s)', 1, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('capsule(s)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('drop(s)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('teaspoon(s)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('tablespoon(s)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('milliliter(s)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('suppository(ies)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('injection(s)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('puff(s)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('inhalation(s)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('patch(es)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Other', 0, 1, 0, @fid);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Dose Unit', @fid, @deid, '', '', 0, 6, 'Unit of dosage', '');

-- Drug route of administration

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('By mouth', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Taken under the tongue', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Applied to a surface, usually skin', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Inhalation', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Applied as a medicated patch to skin', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Given into/under the skin', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Into a vein', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Into a muscle', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Into the ear', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Into the eye', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Rectal', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Vaginal', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Other', 0, 1, 0, @fid);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Drug route of administration', @fid, @deid, '', '', 0, 7, 'Drug route of administration', '');

-- Drug Start Date

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 6);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Drug Start Date', @fid, @deid, '', '', 0, 8, 'Date drug usage started', '');

-- Drug End Date

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 6);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Drug End Date', @fid, @deid, '', '', 0, 9, 'Date drug usage ended', '');

-- Drug Treatment Duration

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, 0, 99999.00, 1.00, '', NULL, '', 0, 4);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Drug Treatment Duration', @fid, @deid, '', '', 0, 10, 'Duration of usage', '');

-- Drug Treatment Duration Unit

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('801=Year', 1, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('802=Month', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('803=Week', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('804=Day', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('805=Hour', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('806=Minute', 0, 0, 0, @fid);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Drug Treatment Duration Unit', @fid, @deid, '', '', 0, 11, 'Unit of duration', '');

-- Drug Indication

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 250, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Drug Indication', @fid, @deid, '', '', 0, 12, 'Indication for drug usage', '');

-- Product Frequency

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 50, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Product Frequency', @fid, @deid, '', '', 0, 12, 'Frequency of product usage', '');

-- Product Batch Number

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 25, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Product Batch Number', @fid, @deid, '', '', 0, 12, 'Product batch number', '');

-- Actions taken with product

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Product withdrawn', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Dose reduced', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Dose increased', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Dose not changed', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Unknown', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Not applicable', 0, 0, 0, @fid);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Actions taken with product', @fid, @deid, '', '', 0, 13, 'Actions taken with product', '');

-- Product challenge

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Yes', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('No', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Unknown', 0, 0, 0, @fid);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Product challenge', @fid, @deid, '', '', 0, 14, 'Was there a challenge to the usage of the product?', '');

-- Product rechallenge

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Yes', 1, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('No', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Didn''t restart', 0, 0, 0, @fid);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Product rechallenge', @fid, @deid, '', '', 0, 15, 'Was there a rechallenge to the usage of the product?', '');

/**************************************************/

/*CATEGORY Reaction and Treatment*/

/**************************************************/

INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)

VALUES ('Reaction and Treatment', 3, @dsid, 'Reaction and Treatment', 'Enter information about what happened and how it was treated.');

set @dscid = (SELECT @@IDENTITY);

-- Description of reaction

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (1, 500, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Description of reaction', @fid, 1, '', '', 0, 'ACD938A4-76D1-44CE-A070-2B8DF0FE9E0F');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (84, @dscid, @deid, 0, 0, 'Description of reaction', '');

set @dceid = (SELECT @@IDENTITY);

-- Reaction start date

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 6);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reaction known start date', @fid, 1, '', '', 0, 'F5EEB382-D4A5-41A1-A447-37D5ECA50B99');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (85, @dscid, @deid, 0, 0, 'Start date of reaction', 'Enter the start date of the reaction OR enter the estimated start date in the next field.');

set @dceid = (SELECT @@IDENTITY);

-- Reaction estimated start date

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 6);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reaction estimated start date', @fid, 1, '', '', 0, '67F2C8CA-503B-498C-9B7E-561BAE4DFA52');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (90, @dscid, @deid, 0, 0, 'Estimated start date of reaction', 'If you don''t know the exact start date of the reaction, enter the estimated start date here.');

set @dceid = (SELECT @@IDENTITY);

-- Reaction serious details

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Resulted in death', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Is life-threatening', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Requires inpatient hospitalization or prolongation of existing hospitalization', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Results in persistent or significant disability/incapacity (as per reporter''s opinion)', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Is a congenital anomaly/birth defect', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Other medically important condition', 0, 1, 0, @fid);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reaction serious details', @fid, 1, '', '', 0, '302C07C9-B0E0-46AB-9EF8-5D5C2F756BF1');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (91, @dscid, @deid, 0, 0, 'Did any of these reactions happen?', '');

set @dceid = (SELECT @@IDENTITY);

-- Treatment given for reaction

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 5);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Treatment given for reaction', @fid, 1, '', '', 0, '3A84016D-9A58-464F-A316-027A095291CE');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (93, @dscid, @deid, 0, 0, 'Was treatment given for the reaction?', '');

set @dceid = (SELECT @@IDENTITY);

-- Treatment given for reaction details

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 500, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Treatment given for reaction details', @fid, 1, '', '', 0, '24DE20BE-AB13-487B-B679-D62D6FEE8814');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (94, @dscid, @deid, 0, 0, 'What treatment was given for the reaction?', '');

set @dceid = (SELECT @@IDENTITY);

-- Outcome of reaction

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Recovered/resolved', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Recovering/resolving', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Not recovered/not resolved', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Recovered/resolved with permanent complications', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Fatal', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Unknown', 0, 0, 0, @fid);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Outcome of reaction', @fid, 1, '', '', 0, '976F6C53-78F2-4007-8F39-54057E554EEB');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (95, @dscid, @deid, 0, 0, 'What was the outcome of the reaction?', '');

set @dceid = (SELECT @@IDENTITY);

-- Reaction date of recovery

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 6);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reaction date of recovery', @fid, 1, '', '', 0, 'F977C2F8-C7DD-4AFE-BCAA-1C06BD54D155');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (96, @dscid, @deid, 0, 0, 'What was the date of recovery from the reaction?', '');

set @dceid = (SELECT @@IDENTITY);

-- Reaction date of death

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 6);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reaction date of death', @fid, 1, '', '', 0, '8B15C037-9C92-4AD4-A8F4-6C4042D40D9D');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (97, @dscid, @deid, 0, 0, 'Enter date if patient died from the reaction', '');

set @dceid = (SELECT @@IDENTITY);

-- Reaction other relevant info

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 500, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reaction other relevant info', @fid, 1, '', '', 0, '7BBEC54B-65C3-4BA6-B5A7-83C3C473F803');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (98, @dscid, @deid, 0, 0, 'Other relevant information', 'For example, does the patient have other medical problems?');

set @dceid = (SELECT @@IDENTITY);

/**************************************************/

/*CATEGORY Test Results*/

/**************************************************/

INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)

VALUES ('Test Results', 4, @dsid, 'Test Results', 'Enter information about any tests done for the reaction, along with the results.');

set @dscid = (SELECT @@IDENTITY);

-- Test Results Table

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 7);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Test Results', @fid, 1, '', '', 0, '12D7089D-1603-4309-99DE-60F20F9A005E');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (10, @dscid, @deid, 0, 0, 'Test Results', 'Test Results');

set @dceid = (SELECT @@IDENTITY);

-- Test Date

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (1, NULL, NULL, NULL, NULL, '', NULL, '', 0, 6);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Test Date', @fid, @deid, '', '', 0, 1, 'Date the test was conducted', '');

-- Test Name

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (1, 100, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Test Name', @fid, @deid, '', '', 0, 2, 'Name of the test', '');

-- Test Result

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (1, 50, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Test Result', @fid, @deid, '', '', 0, 3, 'Result of the test', '');

-- Test Unit

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 35, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Test Unit', @fid, @deid, '', '', 0, 4, 'Unit of the test result', '');

-- Low Test Range

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 50, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('Low Test Range', @fid, @deid, '', '', 0, 5, 'Lower limit of the test result', '');

-- High Test Range

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 50, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('High Test Range', @fid, @deid, '', '', 0, 6, 'Upper limit of the test result', '');

-- More Information

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Yes', 1, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('No', 0, 0, 0, @fid);

INSERT into datasetelementsub (elementname, field_id, datasetelement_id, oid, defaultvalue, `system`, fieldorder, friendlyname, help)

VALUES ('More Information', @fid, @deid, '', '', 0, 7, 'Any additional information', '');

/**************************************************/

/*CATEGORY Reporter Information*/

/**************************************************/

INSERT into datasetcategory (datasetcategoryname, categoryorder, dataset_id, friendlyname, help)

VALUES ('Reporter Information', 5, @dsid, 'Reporter Information', 'Enter information about the person reporting the reaction.');

set @dscid = (SELECT @@IDENTITY);

-- Reporter Name

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (1, 60, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reporter Name', @fid, 1, '', '', 0, '926A07E1-8B83-41CA-8949-739717924AD9');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (91, @dscid, @deid, 1, 0, 'Name or initials of person reporting information', '');

set @dceid = (SELECT @@IDENTITY);

-- Reporter Telephone Number

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 100, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reporter Telephone Number', @fid, 1, '', '', 0, '1AC02BD6-5C24-4A37-9742-C6B868ED985D');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (93, @dscid, @deid, 0, 0, 'Contact telephone number for reporter', '');

set @dceid = (SELECT @@IDENTITY);

-- Reporter E-mail Address

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 100, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reporter E-mail Address', @fid, 1, '', '', 0, 'FFDA770F-DADE-4F6E-B39A-D5E929AEDE2E');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (93, @dscid, @deid, 0, 0, 'Contact e-mail address for reporter', '');

set @dceid = (SELECT @@IDENTITY);

-- Reporter Profession

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 2);

set @fid = (SELECT @@IDENTITY);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Physician', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Pharmacist', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Consumer or other non-health professional', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Other Health Professional', 0, 0, 0, @fid);

INSERT into fieldvalue (value, `default`, other, unknown, field_id)

VALUES ('Lawyer', 0, 0, 0, @fid);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reporter Profession', @fid, 1, '', '', 0, '79ABA1EC-3979-4BA2-80AF-1F817C8243B9');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (94, @dscid, @deid, 1, 0, 'Profession of reporter', '');

set @dceid = (SELECT @@IDENTITY);

-- Report Reference Number

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 30, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Report Reference Number', @fid, 1, '', '', 0, 'C2EDD7A9-8031-4836-99A0-32C84D99A0AC');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (96, @dscid, @deid, 0, 0, 'Report reference number (if any)', '');

set @dceid = (SELECT @@IDENTITY);

-- Reporter Place of Practice

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, 50, NULL, NULL, NULL, '', NULL, '', 0, 3);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Reporter Place of Practice', @fid, 1, '', '', 0, 'A517AAE8-76BD-41F0-8FEE-BD45FCE4EBC8');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (98, @dscid, @deid, 1, 0, 'Reporter place of practise', '');

set @dceid = (SELECT @@IDENTITY);

-- Keep Reporter Confidential

INSERT into field (mandatory, maxlength, decimals, maxsize, minsize, calculation, filesize, fileext, anonymise, fieldtype_id)

VALUES (0, NULL, NULL, NULL, NULL, '', NULL, '', 0, 5);

set @fid = (SELECT @@IDENTITY);

INSERT into datasetelement (elementName, field_id, datasetelementtype_id, oid, defaultvalue, `system`, datasetelementguid)

VALUES ('Keep Reporter Confidential', @fid, 1, '', '', 0, '63AE2D15-029B-4D7C-87BA-16C52A67A909');

set @deid = (SELECT @@IDENTITY);

INSERT into datasetcategoryelement (fieldorder, datasetcategory_id, datasetelement_id, acute, chronic, friendlyname, help)

VALUES (100, @dscid, @deid, 0, 0, 'Keep reporter confidential?', 'Do you want your identity kept confidential except to be contacted by the national medical regulatory authority or the World Health Organization if they need additional information?');

set @dceid = (SELECT @@IDENTITY);

/**************************************************/

/*SET ORDERING*/

/**************************************************/

SELECT ds.DatasetName, dc.DatasetCategoryName, de.ElementName FROM Dataset ds

INNER JOIN DatasetCategory dc ON ds.Id = dc.Dataset_Id

INNER JOIN DatasetCategoryElement dce ON dc.Id = dce.DatasetCategory_Id

INNER JOIN DatasetElement de ON dce.DatasetElement_Id = de.Id

where ds.Id = @dsid