public function definition() {

global $CFG;

$mform = $this->_form;

$mform->addElement('text', 'displayname', get_string('displayname', 'report_customsql'));

$mform->addRule('displayname', get_string('displaynamerequired', 'report_customsql'),

'required', null, 'client');

$mform->setType('displayname', PARAM_MULTILANG);

$mform->addElement('editor', 'description',

get_string('description', 'report_customsql'));

$mform->setType('description', PARAM_RAW);

$mform->addElement('textarea', 'querysql', get_string('querysql', 'report_customsql'),

'rows="25" cols="50"');

$mform->addRule('querysql', get_string('querysqlrequried', 'report_customsql'),

'required', null, 'client');

$mform->setType('querysql', PARAM_RAW);

$mform->addElement('submit', 'verify', get_string('verifyqueryandupdate', 'report_customsql'));

$mform->registerNoSubmitButton('verify');

$hasparameters = 0;

if (count($this->_customdata)) {

$mform->addElement('static', 'params', '', get_string('queryparams', 'report_customsql'));

foreach ($this->_customdata as $queryparam => $formparam) {

$type = report_customsql_get_element_type($queryparam);

$mform->addElement($type, $formparam, $queryparam);

if ($type == 'text') {

$mform->setType($formparam, PARAM_RAW);

}

$hasparameters++;

}

$mform->addElement('static', 'spacer', '', '');

}

$mform->addElement('static', 'note', get_string('note', 'report_customsql'),

get_string('querynote', 'report_customsql', $CFG->wwwroot));

$capabilityoptions = report_customsql_capability_options();

$mform->addElement('select', 'capability', get_string('whocanaccess', 'report_customsql'),

$capabilityoptions);

end($capabilityoptions);

$mform->setDefault('capability', key($capabilityoptions));

$mform->addElement('text', 'querylimit', get_string('querylimit', 'report_customsql'));

$mform->setType('querylimit', PARAM_INT);

$mform->setDefault('querylimit', REPORT_CUSTOMSQL_MAX_RECORDS);

$mform->addRule('querylimit', get_string('requireint', 'report_customsql'),

'numeric', null, 'client');

$runat = array();

if ($hasparameters) {

$runat[] = $mform->createElement('select', 'runable', null, report_customsql_runable_options('manual'));

} else {

$runat[] = $mform->createElement('select', 'runable', null, report_customsql_runable_options());

}

$runat[] = $mform->createElement('select', 'at', null, report_customsql_daily_at_options());

$mform->addGroup($runat, 'runablegroup', get_string('runable', 'report_customsql'),

get_string('at', 'report_customsql'), false);

$mform->addElement('checkbox', 'singlerow', get_string('typeofresult', 'report_customsql'),

get_string('onerow', 'report_customsql'));

$mform->addElement('text', 'emailto', get_string('emailto', 'report_customsql'), 'size = 70');

$mform->addElement('select', 'emailwhat', get_string('emailwhat', 'report_customsql'),

report_customsql_email_options());

$mform->disabledIf('singlerow', 'runable', 'eq', 'manual');

$mform->disabledIf('at', 'runable', 'ne', 'daily');

$mform->disabledIf('emailto', 'runable', 'eq', 'manual');

$mform->disabledIf('emailwhat', 'runable', 'eq', 'manual');

$mform->setType('emailto', PARAM_RAW);

// Add new category selection.

$categoryoptions = report_customsql_category_options();

$mform->addElement('select', 'categoryid', get_string('selectcategory', 'report_customsql'),

$categoryoptions);

$catdefault = isset($categoryoptions[1]) ? 1 : key($categoryoptions);

$mform->setDefault('categoryid', $catdefault);

$this->add_action_buttons();

}

public function validation($data, $files) {

global $CFG, $DB, $USER;

$errors = parent::validation($data, $files);

$sql = $data['querysql'];

if (report_customsql_contains_bad_word($sql)) {

// Obviously evil stuff in the SQL.

$errors['querysql'] = get_string('notallowedwords', 'report_customsql',

implode(', ', report_customsql_bad_words_list()));

} else if (strpos($sql, ';') !== false) {

// Do not allow any semicolons.

$errors['querysql'] = get_string('nosemicolon', 'report_customsql');

} else if ($CFG->prefix != '' && preg_match('/\b' . $CFG->prefix . '\w+/i', $sql)) {

// Make sure prefix is prefix_, not explicit.

$errors['querysql'] = get_string('noexplicitprefix', 'report_customsql', $CFG->prefix);

} else if (!array_key_exists('runable', $data)) {

// This happens when the user enters a query including placehoders, and

// selectes Run: Scheduled, and then tries to save the form.

$errors['runablegroup'] = get_string('noscheduleifplaceholders', 'report_customsql');

} else {

// Now try running the SQL, and ensure it runs without errors.

$report = new stdClass;

$report->querysql = $sql;

$report->runable = $data['runable'];

if ($report->runable === 'daily') {

$report->at = $data['at'];

}

$sql = report_customsql_prepare_sql($report, time());

// Check for required query parameters if there are any.

$queryparams = array();

foreach (report_customsql_get_query_placeholders($sql) as $queryparam) {

$queryparam = substr($queryparam, 1);

$formparam = 'queryparam' . $queryparam;

if (!isset($data[$formparam])) {

$errors['params'] = get_string('queryparamschanged', 'report_customsql');

break;

}

$queryparams[$queryparam] = $data[$formparam];

}

if (!isset($errors['params'])) {

try {

$rs = report_customsql_execute_query($sql, $queryparams, 2);

if (!empty($data['singlerow'])) {

// Count rows for Moodle 2 as all Moodle 1.9 useful and more performant

// recordset methods removed.

$rows = 0;

foreach ($rs as $value) {

$rows++;

}

if (!$rows) {

$errors['querysql'] = get_string('norowsreturned', 'report_customsql');

} else if ($rows >= 2) {

$errors['querysql'] = get_string('morethanonerowreturned',

'report_customsql');

}

}

// Check the list of users in emailto field.

if ($data['runable'] !== 'manual') {

if ($invaliduser = report_customsql_validate_users($data['emailto'], $data['capability'])) {

$errors['emailto'] = $invaliduser;

}

}

$rs->close();

} catch (dml_exception $e) {

$errors['querysql'] = get_string('queryfailed', 'report_customsql',

$e->getMessage() . ' ' . $e->debuginfo);

} catch (Exception $e) {

$errors['querysql'] = get_string('queryfailed', 'report_customsql',

$e->getMessage());

}

}

}

// Check querylimit in range 1 .. REPORT_CUSTOMSQL_MAX_RECORDS.

if (empty($data['querylimit']) || $data['querylimit'] > REPORT_CUSTOMSQL_MAX_RECORDS) {

$errors['querylimit'] = get_string('querylimitrange', 'report_customsql', REPORT_CUSTOMSQL_MAX_RECORDS);

}

return $errors;

}