OpenPV - Spontaneous Form Creation Part 1: Difference between revisions
Khoppenworth (talk | contribs) No edit summary |
Khoppenworth (talk | contribs) No edit summary |
||
| Line 13: | Line 13: | ||
/************************************************** | /************************************************** | ||
CATEGORY Patient Information | CATEGORY Patient Information/ | ||
**************************************************/ | **************************************************/ | ||
Revision as of 18:24, 23 May 2024
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