General developer forum

Good day Al, thanks for the replu

to give some context I created a XMLDB table that stores what users search for in each course, this report pulls those records and based on the drop downs the user selects, the report adds those filters the data as needed.



I get data from a drop down forum that get sent to the below page via POST method, then assign it to the variables.



One interesting thing to note, the export file name is based on the date selected from the drop downs, I echo the export file name as a test (currently commented out but shown in the code), on the screen when I run the report it shows correctly with the date selected, however on the downloaded file, the title states with 12-31-1969, which makes me think the download table is generated before any of the variables are passed.








<?php

/*
* This report shows the search terms by user by course
*/

// File: /mod/mymodulename/view.php
require(__DIR__ . '/../../../config.php');
require "$CFG->libdir/tablelib.php";
require_once(__DIR__ . '/lib.php');
global $PAGE, $OUTPUT, $DB, $USER;
require_login();
$CFG->debugusers = '2';

$download = optional_param('download', '', PARAM_ALPHA);

$page = optional_param('page', 0, PARAM_INT); // which page to show
$perpage = optional_param('perpage', 30, PARAM_INT);

$paging = optional_param('paging', NULL, PARAM_BOOL);
if($page == 0 && !isset($paging)) {
$paging = false;
}
else {
$paging = true;
}

if (isset($USER->profile['userrole']) && (($USER->profile['userrole'] == 'Supervisor') || ($USER->profile['userrole'] == 'Agent'))) {
$subsidiary = $USER->institution;
}


$context = context_system::instance();
$PAGE->set_context($context);
$url = ('/local/helpcenter/analytics/keywords_report.php');
$PAGE->set_url($url);

list($course_id, $course_name) = explode(",", $_POST['course']);
/*
same as
$course = explode(",", $_POST['course']);
$myInt = $course[0];
$myString = $course[1];
*/

// normal date format
$startDate = date('m-d-Y', strtotime($_POST['start_date']));
$endDate = date('m-d-Y', strtotime($_POST['end_date']));
//echo "start date " . $startDate . "
";
//echo "end date " . $endDate . "
";

//unix time date format
$startDateU = date('U', strtotime($_POST['start_date']));
$endDateU = date('U', strtotime($_POST['end_date'])) + 86399;
//echo $startDateU . "
";
//echo $endDateU . "
";

$agent_subsidiary = $_POST['agent_subsidiary'];
$agent_department = $_POST['agent_department'];
$agent_id = $_POST['agent_id'];
$agent_name = $_POST['agent_name'];
$filter = "";

$sql = "
Select `id`, `agent_subsidiary`, `agent_department`, `agent_id`, `agent_name`, `course_id`, `course_name`, FROM_UNIXTIME(`search_time` - (5 * 3600)) as search_time, `search_term`, `search_term_found`
FROM {search_helpcenter}
WHERE `search_time` Between :startdate AND :enddate
";

$variables = ['startdate' => $startDateU, 'enddate' => $endDateU];

$count_id = explode(',' , $agent_id);


if ($agent_subsidiary !== null) {
$filter .= " - Agent Subsidiary: ".$agent_subsidiary;
$sql .= " AND `agent_subsidiary` = :agent_subsidiary";
$variables['agent_subsidiary'] = $agent_subsidiary;
}

if ($agent_department !== null) {
$filter .= " - Agent Department: ".$agent_department;
$sql .= " AND `agent_department` = :agent_department";
$variables['agent_department'] = $agent_department;
}

if ($agent_id !=="") {
if(count($count_id) > 1){
/*
foreach($count_id as $i=>$key) {
if $i == 0 {
$filter .= " - Agent ID: ".$agent_id;
$sql .= " AND `agent_id` = :agent_id".$i;
$variables['agent_id'.$i] = $key[$i];
} else {
$filter .= " ".$agent_id;
$sql .= " AND `agent_id` = :agent_id".$i;
$variables['agent_id'.$i] = $key[$i];
}
}
*/
} else {
$filter .= " - Agent ID: ".$agent_id;
$sql .= " AND `agent_id` = :agent_id";
$variables['agent_id'] = $agent_id;
}
}

if ($agent_name !== "") {
$filter .= " - Agent Name: ".$agent_name;
$sql .= " AND `agent_name` = :agent_name";
$variables['agent_name'] = $agent_name;
}

if ($course_id !== "") {
$filter .= " - Course Name: ".$course_name;
$sql .= " AND `course_id` = :course_id";
$variables['course_id'] = $course_id;
}

$pagetitle = "DB Portal: Key Words Searched ". $filter;
$PAGE->set_title($pagetitle);
$PAGE->set_heading($pagetitle);
$exportfilename = $startDate . ' to ' . $endDate . ' keywords ' . $filter;
$reportname = 'keywords';


//pagination atempt 1
/*
if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
} else {
$pageno = 1;
}
$no_of_records_per_page = 10;
$offset = ($pageno-1) * $no_of_records_per_page;

$total_pages_sql = "SELECT COUNT(*) FROM table";
$result = mysqli_query($conn,$total_pages_sql);
$total_rows = mysqli_fetch_array($result)[0];
$total_pages = ceil($total_rows / $no_of_records_per_page);

$sql .= "LIMIT $offset, $no_of_records_per_page";
*/

//pagination atempt 2
/*
$count = $DB->count_records_sql($records);
$start = $page * $perpage;
if ($start > count($results)) {
$page = 0;
$start = 0;
}
$records = array_slice($records, $start, $perpage, true);
*/

$records = $DB->get_records_sql($sql, $variables);
$num_rows = mysqli_num_rows($records);


//create the table
$table = new flexible_table($reportname);
$table->define_baseurl($url);
//$table->show_download_buttons_at(array(TABLE_P_BOTTOM));
//$table->define_baseurl($reporturl->out(false, $displayoptions));


$table->set_attribute('class', 'generaltable generalbox boxaligncenter boxwidthwide table-bordered');
$table->define_columns(array('id', 'agent_subsidairy', 'agent_dep', 'agent_id', 'agent_name', 'course_id', 'course_name', 'search_time', 'search_term', 'search_term_found'));
$table->define_headers(array('Id', 'Agent Subsidairy', 'Agent Dep', 'Agent Id', 'Agent Name', 'Course Id', 'Course Name', 'Search Time', 'Search Term', 'Search Term Found'));
$table->setup();
$table->define_baseurl($url);



if (!$table->is_downloading($download, $exportfilename)) {
$PAGE->set_pagelayout('report');
echo $OUTPUT->header();
$currenttab = 'topkeywords';
require_once('tabs.php');
}
//echo "test1
";

//print_r($records);
//print_r($variables);
//print_r($agent_subsidiary);

if ($records) {
foreach ($records as $record) {

$row = array();

$row[] = $record->id;
$row[] = $record->agent_subsidiary;
$row[] = $record->agent_department;
$row[] = $record->agent_id;
$row[] = $record->agent_name;
$row[] = $record->course_id;
$row[] = $record->course_name;
$row[] = $record->search_time;
//$row[] = $record->date("Y-m-d H:m:s", search_time);
//$row[] = $record->search_time = userdate($result->search_time);
//$row[] = $record->date_foramt(search_time, "Y/m/d H:i:s");
//$row[] = $record->(strftime("%B %d %Y, %X %Z", search_time));
//$row[] = $record->date_create(search_time)->format('U');
//$row[] = $record->to_timestamp(search_time);
//$row[] = $record->date("Y-m-d\TH:i:s\Z", search_time);
//$row[] = $record->gmdate("Y-m-d\TH:i:s\Z", search_time);
$row[] = $record->search_term;
$row[] = $record->search_term_found;

$table->add_data($row);
}
}



//new paging_bar($totalcount, $page, $perpage, $url);
//echo $OUTPUT->paging_bar($totalcount, $page, $perpage, $url);

//print_r($count);
//print_r($table);

//echo "name: " . $startDate . " to " . $endDate . " keyword" . $filter ."
";
//echo "test
";
//echo "test2
" . $exportfilename;
//$table->print_html();

//$table->is_downloading($download, $exportfilename);

//echo "
";
$table->finish_output();

if (!$table->is_downloading()) {
echo $OUTPUT->footer();
}
?>