function print_filter(&$mform)
{
global $remotedb, $COURSE;
//$filter_user = optional_param('filter_user',0,PARAM_INT);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$userlist = $reportclass->elements_by_conditions($conditions);
} else {
$coursecontext = context_course::instance($COURSE->id);
$userlist = array_keys(get_users_by_capability($coursecontext, 'moodle/user:viewdetails'));
}
$useroptions = array();
$useroptions[0] = get_string('filter_all', 'block_configurable_reports');
if (!empty($userlist)) {
list($usql, $params) = $remotedb->get_in_or_equal($userlist);
$users = $remotedb->get_records_select('user', "id {$usql}", $params);
foreach ($users as $c) {
$useroptions[$c->id] = format_string($c->lastname . ' ' . $c->firstname);
}
}
$mform->addElement('select', 'filter_user', get_string('user'), $useroptions);
$mform->setType('filter_user', PARAM_INT);
}
function print_filter(&$mform, $data)
{
global $DB, $CFG;
$columns = $DB->get_columns('course');
$filteroptions = array();
$filteroptions[''] = get_string('choose');
$coursecolumns = array();
foreach ($columns as $c) {
$coursecolumns[$c->name] = $c->name;
}
if (!isset($coursecolumns[$data->field])) {
print_error('nosuchcolumn');
}
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$courselist = $reportclass->elements_by_conditions($conditions);
if (!empty($courselist)) {
if ($rs = $DB->get_recordset_sql('SELECT DISTINCT(' . $data->field . ') as ufield FROM {course} WHERE ' . $data->field . ' <> "" ORDER BY ufield ASC', null)) {
foreach ($rs as $u) {
$filteroptions[base64_encode($u->ufield)] = $u->ufield;
}
$rs->close();
}
}
$mform->addElement('select', 'filter_fcoursefield_' . $data->field, get_string($data->field), $filteroptions);
$mform->setType('filter_courses', PARAM_INT);
}
function print_filter(&$mform) {
global $remoteDB;
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$userslist = $reportclass->elements_by_conditions($conditions);
} else {
$userslist = array_keys($remoteDB->get_records('user'));
}
$usersoptions = array();
$usersoptions[0] = get_string('filter_all', 'block_cobalt_reports');
if (!empty($userslist)) {
list($usql, $params) = $remoteDB->get_in_or_equal($userslist);
$users = $remoteDB->get_records_select('user', "id $usql", $params);
foreach ($users as $c) {
$usersoptions[$c->id] = format_string($c->lastname . ' ' . $c->firstname);
}
}
$mform->addElement('select', 'filter_users', get_string('users'), $usersoptions);
$mform->setType('filter_users', PARAM_INT);
}
function print_filter(&$mform)
{
global $CFG;
$filter_years = optional_param('filter_years', 0, PARAM_INT);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$yearslist = $reportclass->elements_by_conditions($conditions);
} else {
$yearslist = true;
//array_keys(get_records('course'));
}
$courseoptions = array();
$courseoptions[0] = get_string('choose');
//if(!empty($yearslist)){
//$years = get_records_select('course','id in ('.(implode(',',$yearslist)).')');
$years = explode(',', get_string('filteryears_list', 'block_configurable_reports'));
//print_object($years);die;
foreach ($years as $c) {
$courseoptions[$c] = format_string($c);
}
//}
$select =& $mform->addElement('select', 'filter_years', get_string('filteryears', 'block_configurable_reports'), $courseoptions);
$select->setMultiple(true);
$mform->setType('filter_years', PARAM_RAW);
}
function print_filter(&$mform)
{
global $remotedb, $CFG;
$filter_semester = optional_param('filter_semester', '', PARAM_RAW);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
//$semester = array('סמסטר א'=>'סמסטר א', 'סמסטר ב'=>'סמסטר ב', 'סמסטר ג'=>'סמסטר ג', 'סמינריון'=>'סמינריון');
foreach (explode(',', get_string('filtersemester_list', 'block_configurable_reports')) as $value) {
$semester[$value] = $value;
}
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$semesterlist = $reportclass->elements_by_conditions($conditions);
} else {
$semesterlist = array_keys($semester);
}
$semesteroptions = array();
$semesteroptions[0] = get_string('filter_all', 'block_configurable_reports');
if (!empty($semesterlist)) {
// todo: check that keys of semester array items are available
foreach ($semester as $key => $year) {
$semesteroptions[$key] = $year;
}
}
$mform->addElement('select', 'filter_semester', get_string('filtersemester', 'block_configurable_reports'), $semesteroptions);
$mform->setType('filter_semester', PARAM_RAW);
}
function print_filter(&$mform)
{
global $DB, $CFG;
$filter_categories = optional_param('filter_categories', 0, PARAM_INT);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$categorieslist = $reportclass->elements_by_conditions($conditions);
} else {
$categorieslist = array_keys($DB->get_records('course'));
}
$courseoptions = array();
$courseoptions[0] = get_string('choose');
if (!empty($categorieslist)) {
list($usql, $params) = $DB->get_in_or_equal($categorieslist);
$categories = $DB->get_records_select('course_categories', "id {$usql}", $params);
foreach ($categories as $c) {
$courseoptions[$c->id] = format_string($c->name);
}
}
$mform->addElement('select', 'filter_categories', get_string('category'), $courseoptions);
$mform->setType('filter_categories', PARAM_INT);
}
function print_filter(&$mform) {
global $remoteDB, $CFG;
$filter_yearnumeric = optional_param('filter_yearnumeric', 0, PARAM_INT);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
//$yearnumeric = array('2010'=>'2010','2011'=>'2011','2012'=>'2012','2013'=>'2013','2014'=>'2014','2015'=>'2015');
foreach (explode(',', get_string('filteryearnumeric_list', 'block_cobalt_reports')) as $value) {
$yearnumeric[$value] = $value;
}
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$yearnumericlist = $reportclass->elements_by_conditions($conditions);
} else {
$yearnumericlist = array_keys($yearnumeric);
}
$yearnumericoptions = array();
$yearnumericoptions[0] = get_string('filter_all', 'block_cobalt_reports');
if (!empty($yearnumericlist)) {
// todo: check that keys of yearnumeric array items are available
foreach ($yearnumeric as $key => $year) {
$yearnumericoptions[$key] = $year;
}
}
$mform->addElement('select', 'filter_yearnumeric', get_string('filteryearnumeric', 'block_cobalt_reports'), $yearnumericoptions);
$mform->setType('filter_yearnumeric', PARAM_INT);
}
function print_filter(&$mform)
{
global $remotedb, $CFG;
$filter_yearhebrew = optional_param('filter_yearhebrew', 0, PARAM_RAW);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
//$yearhebrew = array('תשע'=>'תשע','תשעא'=>'תשעא','תשעב'=>'תשעב','תשעג'=>'תשעג','תשעד'=>'תשעד','תשעה'=>'תשעה');
foreach (explode(',', get_string('filteryearhebrew_list', 'block_configurable_reports')) as $value) {
$yearhebrew[$value] = $value;
}
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$yearhebrewlist = $reportclass->elements_by_conditions($conditions);
} else {
$yearhebrewlist = array_keys($yearhebrew);
}
$yearhebrewoptions = array();
$yearhebrewoptions[0] = get_string('filter_all', 'block_configurable_reports');
if (!empty($yearhebrewlist)) {
// todo: check that keys of yearhebrew array items are available
foreach ($yearhebrew as $key => $year) {
$yearhebrewoptions[$key] = $year;
}
}
$mform->addElement('select', 'filter_yearhebrew', get_string('filteryearhebrew', 'block_configurable_reports'), $yearhebrewoptions);
$mform->setType('filter_yearhebrew', PARAM_RAW);
}
function print_filter(&$mform)
{
global $remotedb;
$filter_coursemoduleid = optional_param('filter_coursemodules', 0, PARAM_INT);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$coursemodulelist = $reportclass->elements_by_conditions($conditions);
} else {
$coursemodulelist = array_keys($remotedb->get_records('modules'));
}
$courseoptions = array();
$courseoptions[0] = get_string('filter_all', 'block_configurable_reports');
if (!empty($coursemodulelist)) {
list($usql, $params) = $remotedb->get_in_or_equal($coursemodulelist);
$coursemodules = $remotedb->get_records_select('modules', "id {$usql}", $params);
foreach ($coursemodules as $c) {
$courseoptions[$c->id] = format_string(get_string('pluginname', $c->name) . ' = ' . $c->name);
}
}
$mform->addElement('select', 'filter_coursemodules', get_string('filtercoursemodules', 'block_configurable_reports'), $courseoptions);
$mform->setType('filter_coursemodules', PARAM_INT);
}
function print_filter(&$mform)
{
global $CFG;
$filter_categories = optional_param('filter_categories', 0, PARAM_INT);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$categorieslist = $reportclass->elements_by_conditions($conditions);
} else {
$categorieslist = array_keys(get_records('course'));
}
$courseoptions = array();
$courseoptions[0] = get_string('choose');
if (!empty($categorieslist)) {
$categories = get_records_select('course_categories', 'id in (' . implode(',', $categorieslist) . ')');
foreach ($categories as $c) {
$courseoptions[$c->id] = format_string($c->name);
}
}
$select =& $mform->addElement('select', 'filter_categories', get_string('category'), $courseoptions);
$select->setMultiple(true);
$mform->setType('filter_categories', PARAM_INT);
}
function definition()
{
global $DB, $CFG;
$mform =& $this->_form;
$report = $this->_customdata['report'];
$options = array();
if ($report->type != 'sql') {
$components = cr_unserialize($this->_customdata['report']->components);
if (is_array($components) && !empty($components['columns']['elements'])) {
$columns = $components['columns']['elements'];
foreach ($columns as $c) {
$options[] = $c['summary'];
}
}
} else {
require_once $CFG->dirroot . '/blocks/configurable_reports/report.class.php';
require_once $CFG->dirroot . '/blocks/configurable_reports/reports/' . $report->type . '/report.class.php';
$reportclassname = 'report_' . $report->type;
$reportclass = new $reportclassname($report);
$components = cr_unserialize($report->components);
$config = isset($components['customsql']['config']) ? $components['customsql']['config'] : new stdclass();
if (isset($config->querysql)) {
$sql = $config->querysql;
$sql = $reportclass->prepare_sql($sql);
if ($rs = $reportclass->execute_query($sql)) {
foreach ($rs as $row) {
$i = 0;
foreach ($row as $colname => $value) {
$options[$i] = str_replace('_', ' ', $colname);
$i++;
}
break;
}
}
}
}
$optionsenabled = array(0 => get_string('disabled', 'block_configurable_reports'), 1 => get_string('enabled', 'block_configurable_reports'));
$mform->addElement('select', 'enabled', get_string('template', 'block_configurable_reports'), $optionsenabled);
$mform->setDefault('enabled', 0);
$mform->addElement('htmleditor', 'header', get_string('header', 'block_configurable_reports'));
$mform->disabledIf('header', 'enabled', 'eq', 0);
$mform->addHelpButton('header', 'template_marks', 'block_configurable_reports');
$availablemarksrec = '';
if ($options) {
foreach ($options as $o) {
$availablemarksrec .= "[[{$o}]] => {$o} <br />";
}
}
$mform->addElement('static', 'statictext', get_string('availablemarks', 'block_configurable_reports'), $availablemarksrec);
$mform->addElement('htmleditor', 'record', get_string('templaterecord', 'block_configurable_reports'));
$mform->disabledIf('record', 'enabled', 'eq', 0);
$mform->addElement('htmleditor', 'footer', get_string('footer', 'block_configurable_reports'));
$mform->disabledIf('footer', 'enabled', 'eq', 0);
$mform->addHelpButton('footer', 'template_marks', 'block_configurable_reports');
//$mform->addRule('record', get_string('required'), 'required', null, 'client');
$mform->setType('header', PARAM_RAW);
$mform->setType('record', PARAM_RAW);
$mform->setType('footer', PARAM_RAW);
$this->add_action_buttons();
}
function print_filter(&$mform)
{
global $remotedb, $CFG;
$filter_role = optional_param('filter_role', 0, PARAM_INT);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
$systemroles = $remotedb->get_records('role');
$roles = array();
foreach ($systemroles as $role) {
$roles[$role->id] = $role->shortname;
}
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$rolelist = $reportclass->elements_by_conditions($conditions);
} else {
$rolelist = $roles;
}
$roleoptions = array();
$roleoptions[0] = get_string('filter_all', 'block_configurable_reports');
if (!empty($rolelist)) {
// todo: check that keys of role array items are available
foreach ($rolelist as $key => $role) {
$roleoptions[$key] = $role;
}
}
$mform->addElement('select', 'filter_role', get_string('filterrole', 'block_configurable_reports'), $roleoptions);
$mform->setType('filter_role', PARAM_INT);
}
function form_set_data(&$cform) {
global $DB;
if ($this->form) {
$fdata = new stdclass;
$components = cr_unserialize($this->config->components);
//print_r($components);exit;
$conditionsconfig = (isset($components['permissions']['config'])) ? $components['permissions']['config'] : new stdclass;
if (!isset($conditionsconfig->conditionexpr)) {
$fdata->conditionexpr = '';
$conditionsconfig->conditionexpr = '';
}
$conditionsconfig->conditionexpr = $this->add_missing_conditions($conditionsconfig->conditionexpr);
$fdata->conditionexpr = $conditionsconfig->conditionexpr;
if (empty($components['permissions'])) {
$components['permissions'] = array();
}
if (!array_key_exists('config', $components['permissions'])) {
$components['permissions']['config'] = new StdClass;
}
$components['permissions']['config']->conditionexpr = $fdata->conditionexpr;
$this->config->components = cr_serialize($components);
$DB->update_record('block_cobalt_reports', $this->config);
$cform->set_data($fdata);
}
}
function print_filter(&$mform)
{
global $remotedb, $CFG;
$filter_courses = optional_param('filter_metacourses', 0, PARAM_INT);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$courselist = $reportclass->elements_by_conditions($conditions);
} else {
//$courselist = array_keys($remotedb->get_records('course')); // Original table
$courselist = array_keys($remotedb->get_records('meta_course'));
// meta_course table
}
$courseoptions = array();
$courseoptions[0] = get_string('filter_all', 'block_configurable_reports');
if (!empty($courselist)) {
list($usql, $params) = $remotedb->get_in_or_equal($courselist);
//$courses = $remotedb->get_records_select('course',"id $usql",$params); // Original
$courses = $remotedb->get_records_select('meta_course', "id {$usql}", $params);
// Metacourse
foreach ($courses as $c) {
if ($c->name) {
$courseoptions[$c->id] = format_string($c->name);
// Changed to 'name'
}
}
}
//$mform->addElement('select', 'filter_courses', get_string('course'), $courseoptions); // Original text in form
$mform->addElement('select', 'filter_metacourses', 'Meta course', $courseoptions);
// 'Meta course'
$mform->setType('filter_metacourses', PARAM_INT);
}
function form_set_data(&$cform) {
if ($this->form) {
$fdata = new stdclass;
$components = cr_unserialize($this->config->components);
$config = (isset($components['template']['config'])) ? $components['template']['config'] : new stdclass;
$cform->set_data($config);
}
}
function add_form_elements(&$mform, $components) {
global $DB, $CFG;
$components = cr_unserialize($components);
$options = array();
if ($this->config->type != 'sql') {
if (!is_array($components) || empty($components['columns']['elements']))
print_error('nocolumns');
$columns = $components['columns']['elements'];
$calcs = isset($components['calcs']['elements']) ? $components['calcs']['elements'] : array();
$columnsused = array();
if ($calcs) {
foreach ($calcs as $c) {
$columnsused[] = $c['formdata']->column;
}
}
$i = 0;
foreach ($columns as $c) {
if (!in_array($i, $columnsused))
$options[$i] = $c['summary'];
$i++;
}
}
else {
require_once($CFG->dirroot . '/blocks/cobalt_reports/report.class.php');
require_once($CFG->dirroot . '/blocks/cobalt_reports/reports/' . $this->config->type . '/report.class.php');
$reportclassname = 'report_' . $this->config->type;
$reportclass = new $reportclassname($this->config);
$components = cr_unserialize($this->config->components);
$config = (isset($components['customsql']['config'])) ? $components['customsql']['config'] : new stdclass;
if (isset($config->querysql)) {
$sql = $config->querysql;
$sql = $reportclass->prepare_sql($sql);
if ($rs = $reportclass->execute_query($sql)) {
foreach ($rs as $row) {
$i = 0;
foreach ($row as $colname => $value) {
$options[$i] = str_replace('_', ' ', $colname);
$i++;
}
break;
}
$rs->close();
}
}
}
$mform->addElement('header', 'crformheader', get_string('coursefield', 'block_cobalt_reports'), '');
$mform->addElement('select', 'column', get_string('column', 'block_cobalt_reports'), $options);
}
function form_set_data(&$cform) {
if ($this->form) {
$fdata = new stdclass;
$components = cr_unserialize($this->config->components);
//print_r($components);exit;
$sqlconfig = (isset($components['customsql']['config'])) ? $components['customsql']['config'] : new stdclass;
$cform->set_data($sqlconfig);
}
}
function form_set_data(&$cform)
{
if ($this->form) {
$fdata = new stdclass();
$components = cr_unserialize($this->config->components);
$fdata = isset($components['columns']['config']) ? $components['columns']['config'] : $fdata;
$cform->set_data($fdata);
}
}
function form_set_data(&$cform)
{
if ($this->form) {
$fdata = new stdclass();
$components = cr_unserialize($this->config->components);
$compconfig = isset($components['timeline']['config']) ? $components['timeline']['config'] : new stdclass();
$cform->set_data($compconfig);
}
}
function definition()
{
global $DB, $USER, $CFG;
$mform =& $this->_form;
$options = array();
$report = $this->_customdata['report'];
if ($report->type != 'sql') {
$components = cr_unserialize($this->_customdata['report']->components);
if (!is_array($components) || empty($components['columns']['elements'])) {
print_error('nocolumns');
}
$columns = $components['columns']['elements'];
foreach ($columns as $c) {
if (!empty($c['summary'])) {
$options[] = $c['summary'];
}
}
} else {
require_once $CFG->dirroot . '/blocks/configurable_reports/report.class.php';
require_once $CFG->dirroot . '/blocks/configurable_reports/reports/' . $report->type . '/report.class.php';
$reportclassname = 'report_' . $report->type;
$reportclass = new $reportclassname($report);
$components = cr_unserialize($report->components);
$config = isset($components['customsql']['config']) ? $components['customsql']['config'] : new stdclass();
if (isset($config->querysql)) {
$sql = $config->querysql;
$sql = $reportclass->prepare_sql($sql);
if ($rs = $reportclass->execute_query($sql)) {
foreach ($rs as $row) {
$i = 0;
foreach ($row as $colname => $value) {
$options[$i] = str_replace('_', ' ', $colname);
$i++;
}
break;
}
$rs->close();
}
}
}
$mform->addElement('header', 'crformheader', get_string('coursefield', 'block_configurable_reports'), '');
$mform->addElement('select', 'areaname', get_string('pieareaname', 'block_configurable_reports'), $options);
$mform->addElement('select', 'areavalue', get_string('pieareavalue', 'block_configurable_reports'), $options);
// New Feature: Select Limit Categories in PIE.
$limits = array();
for ($i = 0; $i <= 10; $i++) {
$limits[$i] = $i;
}
$mform->addElement('select', 'limitcategories', get_string('limitcategories', 'block_configurable_reports'), $limits);
// New Feature: Select Decimals in PIE
$decimals = array('0' => 0, '1' => 1, '2' => 2);
$mform->addElement('select', 'decimals', get_string('decimals', 'block_configurable_reports'), $decimals);
$mform->addElement('checkbox', 'group', get_string('groupvalues', 'block_configurable_reports'));
// buttons
$this->add_action_buttons(true, get_string('add'));
}
function print_filter(&$mform) {
global $remoteDB, $COURSE;
//$filter_enrolledstudents = optional_param('filter_enrolledstudents',0,PARAM_INT);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$enrolledstudentslist = $reportclass->elements_by_conditions($conditions);
} else {
//$coursecontext = context_course::instance($COURSE->id);
//$enrolledstudentslist = array_keys(get_users_by_capability($coursecontext, 'moodle/user:viewdetails'));
// $sql = "SELECT DISTINCT u.id
// FROM {user} u
// JOIN {user_enrolments} ue ON (ue.userid = u.id)
// JOIN {enrol} e ON (e.id = ue.enrolid)
// WHERE e.roleid = 5 AND e.courseid = {$COURSE->id}";
$sql = "SELECT ra.userid
FROM {role_assignments} AS ra
JOIN {context} AS context ON ra.contextid = context.id AND context.contextlevel = 50
WHERE ra.roleid in (SELECT id FROM {role} where shortname='student') AND context.instanceid = {$COURSE->id}";
//echo $sql;die;
$studentlist = $remoteDB->get_records_sql($sql);
//print_object($enrolledstudentslist);die;
foreach ($studentlist as $student) {
$enrolledstudentslist[] = $student->userid;
}
}
$enrolledstudentsoptions = array();
$enrolledstudentsoptions[0] = get_string('filter_all', 'block_cobalt_reports');
if (!empty($enrolledstudentslist)) {
list($usql, $params) = $remoteDB->get_in_or_equal($enrolledstudentslist);
$enrolledstudents = $remoteDB->get_records_select('user', "id $usql", $params);
foreach ($enrolledstudents as $c) {
$enrolledstudentsoptions[$c->id] = format_string($c->lastname . ' ' . $c->firstname);
}
}
$mform->addElement('select', 'filter_enrolledstudents', get_string('student', 'block_cobalt_reports'), $enrolledstudentsoptions);
$mform->setType('filter_enrolledstudents', PARAM_INT);
}
function definition()
{
global $DB, $USER, $CFG;
$mform =& $this->_form;
$options = array(0 => get_string('choose'));
$report = $this->_customdata['report'];
if ($report->type != 'sql') {
$components = cr_unserialize($this->_customdata['report']->components);
if (!is_array($components) || empty($components['columns']['elements'])) {
print_error('nocolumns');
}
$columns = $components['columns']['elements'];
foreach ($columns as $c) {
$options[] = $c['summary'];
}
} else {
require_once $CFG->dirroot . '/blocks/configurable_reports/report.class.php';
require_once $CFG->dirroot . '/blocks/configurable_reports/reports/' . $report->type . '/report.class.php';
$reportclassname = 'report_' . $report->type;
$reportclass = new $reportclassname($report);
$components = cr_unserialize($report->components);
$config = isset($components['customsql']['config']) ? $components['customsql']['config'] : new stdclass();
if (isset($config->querysql)) {
$sql = $config->querysql;
$sql = $reportclass->prepare_sql($sql);
if ($rs = $reportclass->execute_query($sql)) {
foreach ($rs as $row) {
$i = 1;
foreach ($row as $colname => $value) {
$options[$i] = str_replace('_', ' ', $colname);
$i++;
}
break;
}
$rs->close();
}
}
}
$mform->addElement('header', 'crformheader', get_string('line', 'block_configurable_reports'), '');
$mform->addElement('select', 'xaxis', get_string('xaxis', 'block_configurable_reports'), $options);
$mform->addRule('xaxis', null, 'required', null, 'client');
$mform->addElement('select', 'serieid', get_string('serieid', 'block_configurable_reports'), $options);
$mform->addRule('serieid', null, 'required', null, 'client');
$mform->addElement('select', 'yaxis', get_string('yaxis', 'block_configurable_reports'), $options);
$mform->addRule('yaxis', null, 'required', null, 'client');
$mform->addElement('checkbox', 'group', get_string('groupseries', 'block_configurable_reports'));
// buttons
$this->add_action_buttons(true, get_string('add'));
}
function get_all_elements() {
$elements = array();
$components = cr_unserialize($this->config->components);
$config = (isset($components['timeline']['config'])) ? $components['timeline']['config'] : new stdclass();
if (isset($config->timemode)) {
$daysecs = 60 * 60 * 24;
if ($config->timemode == 'previous') {
$config->starttime = gmmktime() - $config->previousstart * $daysecs;
$config->endtime = gmmktime() - $config->previousend * $daysecs;
if (isset($config->forcemidnight)) {
$config->starttime = usergetmidnight($config->starttime);
$config->endtime = usergetmidnight($config->endtime) + ($daysecs - 1);
}
}
$filter_starttime = optional_param('filter_starttime', 0, PARAM_RAW);
$filter_endtime = optional_param('filter_endtime', 0, PARAM_RAW);
if ($filter_starttime and $filter_endtime) {
$filter_starttime = make_timestamp($filter_starttime['year'], $filter_starttime['month'], $filter_starttime['day']);
$filter_endtime = make_timestamp($filter_endtime['year'], $filter_endtime['month'], $filter_endtime['day']);
$config->starttime = usergetmidnight($filter_starttime);
$config->endtime = usergetmidnight($filter_endtime) + 24 * 60 * 60;
}
for ($i = $config->starttime; $i < $config->endtime; $i += $config->interval * $daysecs) {
$row = new stdclass();
$row->id = $i;
$row->starttime = $i;
$row->endtime = $row->starttime + ($config->interval * $daysecs - 1);
if ($row->endtime > $config->endtime)
$row->endtime = $config->endtime;
$this->timeline[$row->starttime] = $row;
$elements[] = $row->starttime;
}
if ($config->ordering == 'desc')
rsort($elements);
}
return $elements;
}
function definition()
{
global $DB, $USER, $CFG;
$mform =& $this->_form;
$options = array();
$report = $this->_customdata['report'];
if ($report->type != 'sql') {
$components = cr_unserialize($this->_customdata['report']->components);
if (!is_array($components) || empty($components['columns']['elements'])) {
print_error('nocolumns');
}
$columns = $components['columns']['elements'];
foreach ($columns as $c) {
if (!empty($c['summary'])) {
$options[] = $c['summary'];
}
}
} else {
require_once $CFG->dirroot . '/blocks/configurable_reports/report.class.php';
require_once $CFG->dirroot . '/blocks/configurable_reports/reports/' . $report->type . '/report.class.php';
$reportclassname = 'report_' . $report->type;
$reportclass = new $reportclassname($report);
$components = cr_unserialize($report->components);
$config = isset($components['customsql']['config']) ? $components['customsql']['config'] : new stdclass();
if (isset($config->querysql)) {
$sql = $config->querysql;
$sql = $reportclass->prepare_sql($sql);
if ($rs = $reportclass->execute_query($sql)) {
foreach ($rs as $row) {
$i = 0;
foreach ($row as $colname => $value) {
$key = "{$i},{$colname}";
$options[$key] = str_replace('_', ' ', $colname);
$i++;
}
break;
}
$rs->close();
}
}
}
$mform->addElement('header', 'crformheader', get_string('head_data', 'block_configurable_reports'), '');
$mform->addElement('select', 'label_field', get_string('label_field', 'block_configurable_reports'), $options);
$mform->addHelpButton('label_field', 'label_field', 'block_configurable_reports');
$valueselect = $mform->addElement('select', 'value_fields', get_string('value_fields', 'block_configurable_reports'), $options);
$valueselect->setMultiple(true);
$mform->addHelpButton('value_fields', 'value_fields', 'block_configurable_reports');
$this->add_formatting_elements($mform);
}
function summary($data) {
global $DB, $CFG;
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
if (!is_array($components) || empty($components['columns']['elements']))
print_error('nocolumns');
$columns = $components['columns']['elements'];
$i = 0;
foreach ($columns as $c) {
if ($i == $data->column)
return $c['summary'];
$i++;
}
}
else {
require_once($CFG->dirroot . '/blocks/cobalt_reports/report.class.php');
require_once($CFG->dirroot . '/blocks/cobalt_reports/reports/' . $this->report->type . '/report.class.php');
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
$components = cr_unserialize($this->report->components);
$config = (isset($components['customsql']['config'])) ? $components['customsql']['config'] : new stdclass;
if (isset($config->querysql)) {
$sql = $config->querysql;
$sql = $reportclass->prepare_sql($sql);
if ($rs = $reportclass->execute_query($sql)) {
foreach ($rs as $row) {
$i = 0;
foreach ($row as $colname => $value) {
if ($i == $data->column)
return str_replace('_', ' ', $colname);
$i++;
}
break;
}
$rs->close();
}
}
}
return '';
}
function create_report()
{
global $DB, $CFG;
$components = cr_unserialize($this->config->components);
$filters = isset($components['filters']['elements']) ? $components['filters']['elements'] : array();
$calcs = isset($components['calcs']['elements']) ? $components['calcs']['elements'] : array();
$tablehead = array();
$finalcalcs = array();
$finaltable = array();
$tablehead = array();
$components = cr_unserialize($this->config->components);
$config = isset($components['customsql']['config']) ? $components['customsql']['config'] : new stdclass();
if (isset($config->querysql)) {
// FILTERS
$sql = $config->querysql;
if (!empty($filters)) {
foreach ($filters as $f) {
require_once $CFG->dirroot . '/report/configreports/components/filters/' . $f['pluginname'] . '/plugin.class.php';
$classname = 'plugin_' . $f['pluginname'];
$class = new $classname($this->config);
$sql = $class->execute($sql, $f['formdata']);
}
}
$sql = $this->prepare_sql($sql);
if ($rs = $this->execute_query($sql)) {
foreach ($rs as $row) {
if (empty($finaltable)) {
foreach ($row as $colname => $value) {
$tablehead[] = str_replace('_', ' ', $colname);
}
}
$finaltable[] = array_values((array) $row);
}
}
}
// Calcs
$finalcalcs = $this->get_calcs($finaltable, $tablehead);
$table = new stdclass();
$table->id = 'reporttable';
$table->data = $finaltable;
$table->head = $tablehead;
$calcs = new stdclass();
$calcs->data = array($finalcalcs);
$calcs->head = $tablehead;
$this->finalreport->table = $table;
$this->finalreport->calcs = $calcs;
return true;
}
function print_filter(&$mform, $data)
{
global $CFG, $db;
$columns = $db->MetaColumns($CFG->prefix . 'user');
$filteroptions = array();
$filteroptions[''] = get_string('choose');
$usercolumns = array();
foreach ($columns as $c) {
$usercolumns[$c->name] = $c->name;
}
if ($profile = get_records('user_info_field')) {
foreach ($profile as $p) {
$usercolumns['profile_' . $p->shortname] = $p->name;
}
}
if (!isset($usercolumns[$data->field])) {
print_error('nosuchcolumn');
}
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$userlist = $reportclass->elements_by_conditions($conditions);
if (!empty($userlist)) {
if (strpos($data->field, 'profile_') === 0) {
if ($field = get_record('user_info_field', 'shortname', str_replace('profile_', '', $data->field))) {
$selectname = $field->name;
$sql = "SELECT DISTINCT(data) as data FROM {$CFG->prefix}user_info_data WHERE fieldid = {$field->id} AND userid IN(" . implode(',', $userlist) . ")";
if ($infodata = get_records_sql($sql)) {
$finalusersid = array();
foreach ($infodata as $d) {
$filteroptions[base64_encode($d->data)] = $d->data;
}
}
}
} else {
$selectname = get_string($data->field);
$sql = "SELECT DISTINCT(" . $data->field . ") as ufield FROM " . $CFG->prefix . "user WHERE id IN (" . implode(',', $userlist) . ") ORDER BY ufield ASC";
if ($rs = get_recordset_sql($sql)) {
while ($u = rs_fetch_next_record($rs)) {
$filteroptions[base64_encode($u->ufield)] = $u->ufield;
}
}
}
}
$mform->addElement('select', 'filter_fuserfield_' . $data->field, $selectname, $filteroptions);
$mform->setType('filter_courses', PARAM_INT);
}
function print_filter(&$mform) {
global $DB, $CFG,$USER;
$filter_zone = optional_param('filter_zone', '', PARAM_RAW);
$reportclassname = 'report_' . $this->report->type;
$reportclass = new $reportclassname($this->report);
if ($this->report->type != 'sql') {
$components = cr_unserialize($this->report->components);
$conditions = $components['conditions'];
$zonelist = $reportclass->elements_by_conditions($conditions);
} else {
$zonelist = array_keys($DB->get_records_sql("select zone_code, zone from {local_userdata} group by zone_code"));
}
$zonelistoptions = array();
$zonelistoptions[0] = get_string('filter_all', 'block_cobalt_reports');
if (!empty($zonelist)) {
// list($usql, $params) = $remoteDB->get_in_or_equal($zonelist);
// $sql="SELECT * from {local_costcenter} lc JOIN {local_userdata} lu ON lu.costcenterid=lc.id where lu.userid={$USER->id}";
if(is_siteadmin() || is_ast_nh()){
//$costcenters = $remoteDB->get_records_select('local_costcenter', "id $usql", $params);
$zones = $DB->get_records_sql("select id,zone_code, zone from {local_userdata} group by zone_code");
}
else {
//if(is_hod() || is_zht())
if(isset($USER->id) && $USER->id)
$zones=$DB->get_records_sql("select id, zone_code, zone from {local_userdata} where userid={$USER->id} ");
// $costcenters = $remoteDB->get_records_sql($sql, $params);
}
foreach ($zones as $record) {
$zonelistoptions[$record->zone_code] = format_string($record->zone);
}
}
$mform->addElement('select', 'filter_zone', get_string('filterzone', 'block_cobalt_reports'), $zonelistoptions);
$mform->setType('filter_zone', PARAM_RAW);
}
function form_set_data($cform)
{
if ($this->form) {
$fdata = new stdclass();
$components = cr_unserialize($this->config->components);
//print_r($components);exit;
$conditionsconfig = isset($components['permissions']['config']) ? $components['permissions']['config'] : new stdclass();
if (!isset($conditionsconfig->conditionexpr)) {
$fdata->conditionexpr = '';
$conditionsconfig->conditionexpr = '';
}
$conditionsconfig->conditionexpr = $this->add_missing_conditions($conditionsconfig->conditionexpr);
$fdata->conditionexpr = $conditionsconfig->conditionexpr;
$components['permissions']['config']->conditionexpr = $fdata->conditionexpr;
$this->config->components = cr_serialize($components);
update_record('block_configurable_reports_report', $this->config);
$cform->set_data($fdata);
}
}
function definition()
{
global $USER, $CFG;
$mform =& $this->_form;
$options = array();
$report = $this->_customdata['report'];
if ($report->type != 'sql') {
$components = cr_unserialize($this->_customdata['report']->components);
if (!is_array($components) || empty($components['columns']['elements'])) {
print_error('nocolumns');
}
$columns = $components['columns']['elements'];
foreach ($columns as $c) {
$options[] = $c['summary'];
}
} else {
require_once $CFG->dirroot . '/blocks/configurable_reports/report.class.php';
require_once $CFG->dirroot . '/blocks/configurable_reports/reports/' . $report->type . '/report.class.php';
$reportclassname = 'report_' . $report->type;
$reportclass = new $reportclassname($report);
$components = cr_unserialize($report->components);
$config = isset($components['customsql']['config']) ? $components['customsql']['config'] : new stdclass();
if (isset($config->querysql)) {
$sql = $config->querysql;
$sql = $reportclass->prepare_sql($sql);
if ($rs = $reportclass->execute_query($sql)) {
$row = rs_fetch_next_record($rs);
$i = 0;
foreach ($row as $colname => $value) {
$options[$i] = str_replace('_', ' ', $colname);
$i++;
}
}
}
}
$mform->addElement('header', '', get_string('coursefield', 'block_configurable_reports'), '');
$mform->addElement('select', 'areaname', get_string('pieareaname', 'block_configurable_reports'), $options);
$mform->addElement('select', 'areavalue', get_string('pieareavalue', 'block_configurable_reports'), $options);
$mform->addElement('checkbox', 'group', get_string('groupvalues', 'block_configurable_reports'));
// buttons
$this->add_action_buttons(true, get_string('add'));
}