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 test_report_customsql_get_element_type()
{
$this->assertEquals('date_time_selector', report_customsql_get_element_type('start_date'));
$this->assertEquals('date_time_selector', report_customsql_get_element_type('startdate'));
$this->assertEquals('date_time_selector', report_customsql_get_element_type('date_closed'));
$this->assertEquals('date_time_selector', report_customsql_get_element_type('dateclosed'));
$this->assertEquals('text', report_customsql_get_element_type('anythingelse'));
$this->assertEquals('text', report_customsql_get_element_type('not_a_date_field'));
$this->assertEquals('text', report_customsql_get_element_type('mandated'));
}
public function definition()
{
global $CFG;
$mform =& $this->_form;
$mform->addElement('header', 'heading', get_string('queryparameters', 'report_customsql'));
foreach ($this->_customdata as $queryparam => $formparam) {
$type = report_customsql_get_element_type($queryparam);
$mform->addElement($type, $formparam, str_replace('_', ' ', $queryparam));
if ($type == 'text') {
$mform->setType($formparam, PARAM_RAW);
}
}
$this->add_action_buttons(true, 'Run report');
}
function report_customsql_generate_csv($report, $timenow)
{
global $DB;
$starttime = microtime(true);
$sql = report_customsql_prepare_sql($report, $timenow);
$queryparams = !empty($report->queryparams) ? unserialize($report->queryparams) : array();
$querylimit = !empty($report->querylimit) ? $report->querylimit : REPORT_CUSTOMSQL_MAX_RECORDS;
$rs = report_customsql_execute_query($sql, $queryparams, $querylimit);
$csvfilenames = array();
$csvtimestamp = null;
foreach ($rs as $row) {
if (!$csvtimestamp) {
list($csvfilename, $csvtimestamp) = report_customsql_csv_filename($report, $timenow);
$csvfilenames[] = $csvfilename;
if (!file_exists($csvfilename)) {
$handle = fopen($csvfilename, 'w');
report_customsql_start_csv($handle, $row, $report->singlerow);
} else {
$handle = fopen($csvfilename, 'a');
}
}
$data = get_object_vars($row);
foreach ($data as $name => $value) {
if (report_customsql_get_element_type($name) == 'date_time_selector' && report_customsql_is_integer($value)) {
$data[$name] = userdate($value, '%F %T');
}
}
if ($report->singlerow) {
array_unshift($data, strftime('%Y-%m-%d', $timenow));
}
report_customsql_write_csv_row($handle, $data);
}
$rs->close();
if (!empty($handle)) {
fclose($handle);
}
// Update the execution time in the DB.
$updaterecord = new stdClass();
$updaterecord->id = $report->id;
$updaterecord->lastrun = time();
$updaterecord->lastexecutiontime = round((microtime(true) - $starttime) * 1000);
$DB->update_record('report_customsql_queries', $updaterecord);
// Report is runable daily, weekly or monthly.
if ($report->runable != 'manual' && !empty($report->emailto)) {
if ($csvfilenames) {
foreach ($csvfilenames as $csvfilename) {
report_customsql_email_report($report, $csvfilename);
}
} else {
// If there is no data.
report_customsql_email_report($report);
}
}
return $csvtimestamp;
}
// Check the logged user is admin or not
if (is_siteadmin()) {
admin_externalpage_setup('report_customsql');
} else {
$PAGE->set_pagelayout('course');
}
$PAGE->set_title($report->displayname);
$PAGE->navbar->add($report->displayname);
echo $OUTPUT->header();
echo $OUTPUT->heading(format_string($report->displayname));
if (!html_is_blank($report->description)) {
echo html_writer::tag('p', format_text($report->description, FORMAT_HTML));
}
if (!empty($queryparams)) {
foreach ($queryparams as $name => $value) {
if (report_customsql_get_element_type($name) == 'date_time_selector') {
$value = userdate($value, '%F %T');
}
echo html_writer::tag('p', get_string('parametervalue', 'report_customsql', array('name' => html_writer::tag('b', str_replace('_', ' ', $name)), 'value' => s($value))));
}
}
$count = 0;
if (is_null($csvtimestamp)) {
echo html_writer::tag('p', get_string('nodatareturned', 'report_customsql'));
} else {
list($csvfilename, $csvtimestamp) = report_customsql_csv_filename($report, $csvtimestamp);
if (!is_readable($csvfilename)) {
echo html_writer::tag('p', get_string('notrunyet', 'report_customsql'));
} else {
$handle = fopen($csvfilename, 'r');
if ($report->runable != 'manual' && !$report->singlerow) {
