OpenPV - Spontaneous Form Creation Part 1

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