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) {