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;
}
function validation($data, $files)
{
global $CFG, $db, $USER;
$errors = parent::validation($data, $files);
$sql = stripslashes($data['querysql']);
// Simple test to avoid evil stuff in the SQL.
if (report_customsql_contains_bad_word($sql)) {
$errors['querysql'] = get_string('notallowedwords', 'report_customsql', implode(', ', report_customsql_bad_words_list()));
// Do not allow any semicolons.
} else {
if (strpos($sql, ';') !== false) {
$errors['querysql'] = get_string('nosemicolon', 'report_customsql');
// Make sure prefix is prefix_, not explicit.
} else {
if ($CFG->prefix != '' && preg_match('/\\b' . $CFG->prefix . '\\w+/i', $sql)) {
$errors['querysql'] = get_string('noexplicitprefix', 'report_customsql', $CFG->prefix);
// Now try running the SQL, and ensure it runs without errors.
} else {
$report = new stdClass();
$report->querysql = $sql;
$report->runable = $data['runable'];
$sql = report_customsql_prepare_sql($report, time());
$rs = report_customsql_execute_query($sql, 2);
if (!$rs) {
$errors['querysql'] = get_string('queryfailed', 'report_customsql', $db->ErrorMsg());
} else {
if (!empty($data['singlerow'])) {
if (rs_EOF($rs)) {
$errors['querysql'] = get_string('norowsreturned', 'report_customsql');
} else {
rs_fetch_next_record($rs);
if (!rs_EOF($rs)) {
$errors['querysql'] = get_string('morethanonerowreturned', 'report_customsql');
}
}
}
}
if ($rs) {
rs_close($rs);
}
}
}
}
return $errors;
}
function report_customsql_generate_csv($report, $timenow, $limitnum = REPORT_CUSTOMSQL_MAX_RECORDS)
{
global $db;
$starttime = microtime(true);
$sql = report_customsql_prepare_sql($report, $timenow);
$rs = report_customsql_execute_query($sql, $limitnum);
if (!$rs) {
throw new Exception($db->ErrorMsg());
}
$cvstimestamp = null;
while ($row = rs_fetch_next_record($rs)) {
if (!$cvstimestamp) {
list($csvfilename, $cvstimestamp) = report_customsql_csv_filename($report, $timenow);
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);
if ($report->singlerow) {
array_unshift($data, strftime('%Y-%m-%d', $timenow));
}
report_customsql_write_csv_row($handle, $data);
}
rs_close($rs);
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);
update_record('report_customsql_queries', $updaterecord);
return $cvstimestamp;
}
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;
}
