private function doCategoryReports()
{
$smarty = $this->getSmartyVar();
$currentUser = $this->getCurrentUser();
$directionsTree = new EfrontDirectionsTree();
$directionPaths = $directionsTree->toPathString();
$form = new HTML_QuickForm("category_form", "post", basename($_SERVER['PHP_SELF']) . "?ctg=module&op=module_administrator_tools&tab=category_reports&do=enterprise", "", null, true);
$form->addElement('select', 'category', _CATEGORY, $directionPaths);
$form->addElement('checkbox', 'incomplete', _MODULE_ADMINISTRATOR_TOOLS_SHOWINCOMPLETE);
$form->addElement('checkbox', 'inactive', _MODULE_ADMINISTRATOR_TOOLS_SHOWINACTIVECOURSES);
$form->addElement('date', 'from_timestamp', _MODULE_ADMINISTRATOR_TOOLS_COMPLETEDFROM, array('minYear' => 1970, 'maxYear' => date("Y")));
$form->addElement('date', 'to_timestamp', _MODULE_ADMINISTRATOR_TOOLS_COMPLETEDTO, array('minYear' => 1970, 'maxYear' => date("Y")));
$form->addElement("submit", "submit", _SUBMIT, 'class = "flatButton"');
$form->setDefaults(array("from_timestamp" => mktime(0, 0, 0, date("m") - 1, date("d"), date("Y")), "to_timestamp" => time()));
if ($form->isSubmitted() && $form->validate()) {
$values = $form->exportValues();
$_SESSION['from_timestamp'] = mktime(0, 0, 0, $_POST['from_timestamp']['M'], $_POST['from_timestamp']['d'], $_POST['from_timestamp']['Y']);
$_SESSION['to_timestamp'] = mktime(23, 59, 59, $_POST['to_timestamp']['M'], $_POST['to_timestamp']['d'], $_POST['to_timestamp']['Y']);
$_SESSION['category'] = $values['category'];
$_SESSION['incomplete'] = $values['incomplete'];
$_SESSION['inactive'] = $values['inactive'];
$smarty->assign("T_SHOW_TABLE", true);
}
if (isset($_GET['ajax']) && $_GET['ajax'] == 'categoryUsersTable' || $_GET['ajax'] == 'xls' || $_GET['ajax'] == 'show_xls') {
$smarty->assign("T_SHOW_TABLE", true);
$smarty->assign("T_DIRECTIONS_TREE", $directionPaths);
$branchesTree = new EfrontBranchesTree();
$branchesPaths = $branchesTree->toPathString();
$category = new EfrontDirection($_SESSION['category']);
$directionsTree = new EfrontDirectionsTree();
$children = $directionsTree->getNodeChildren($_SESSION['category']);
foreach (new EfrontAttributeFilterIterator(new RecursiveIteratorIterator(new RecursiveArrayIterator($children)), array('id')) as $value) {
$siblings[] = $value;
}
$result = eF_getTableDataFlat("courses", "id", "archive = 0 && directions_ID in (" . implode(",", $siblings) . ")");
$categoryCourses = $result['id'];
$resultCourses = eF_getTableDataFlat("users_to_courses uc, courses c", "distinct c.id", 'c.id=uc.courses_ID ' . (!$_SESSION['inactive'] ? 'and c.active=1' : '') . ' and uc.archive=0 and uc.completed=1 and uc.to_timestamp >= ' . $_SESSION['from_timestamp'] . ' and uc.to_timestamp <= ' . $_SESSION['to_timestamp']);
$resultEvents = eF_getTableDataFlat("events e, courses c", "distinct c.id", 'c.id=e.lessons_ID ' . (!$_SESSION['inactive'] ? 'and c.active=1' : '') . ' and e.type=54 and e.timestamp >= ' . $_SESSION['from_timestamp'] . ' and e.timestamp <= ' . $_SESSION['to_timestamp']);
if (empty($resultEvents)) {
$resultEvents['id'] = array();
}
$result = array_unique(array_merge($resultCourses['id'], $resultEvents['id']));
$categoryCourses = array_intersect(array_unique($categoryCourses), $result);
//count only courses that have users completed them
if ($_SESSION['incomplete']) {
$constraints = array('archive' => false, 'condition' => '(to_timestamp is null OR to_timestamp = 0 OR (to_timestamp >= ' . $_SESSION['from_timestamp'] . ' and to_timestamp <= ' . $_SESSION['to_timestamp'] . '))');
} else {
$constraints = array('archive' => false, 'condition' => 'completed=1 and to_timestamp >= ' . $_SESSION['from_timestamp'] . ' and to_timestamp <= ' . $_SESSION['to_timestamp']);
}
foreach ($categoryCourses as $courseId) {
$course = new EfrontCourse($courseId);
foreach ($course->getCourseUsers($constraints) as $value) {
$userBranches = $value->aspects['hcd']->getBranches();
$userSupervisors = $value->aspects['hcd']->getSupervisors();
$userSupervisor = end($userSupervisors);
$value->user['course_active'] = $course->course['active'];
$value->user['course_id'] = $course->course['id'];
$value->user['category'] = $directionPaths[$course->course['directions_ID']];
$value->user['course'] = $course->course['name'];
$value->user['directions_ID'] = $course->course['directions_ID'];
$value->user['branch'] = $branchesPaths[current($userBranches['employee'])];
$value->user['branch_ID'] = current($userBranches['employee']);
$value->user['supervisor'] = $userSupervisor;
$value->user['historic'] = false;
$unique = md5($value->user['to_timestamp'] . $value->user['course_id'] . $value->user['login']);
$courseUsers[$unique] = $value->user;
}
$result = eF_getTableData("events", "*", 'type=54 and lessons_ID=' . $courseId . ' and timestamp >= ' . $_SESSION['from_timestamp'] . ' and timestamp <= ' . $_SESSION['to_timestamp']);
//exit;
foreach ($result as $entry) {
try {
$value = EfrontUserFactory::factory($entry['users_LOGIN']);
if (!$value->user['archive']) {
$userBranches = $value->aspects['hcd']->getBranches();
$userSupervisors = $value->aspects['hcd']->getSupervisors();
//pr($entry['users_LOGIN']);pr($userSupervisors);pr(current($userSupervisors));
$userSupervisor = current($userSupervisors);
$value->user['course_active'] = $course->course['active'];
$value->user['course_id'] = $course->course['id'];
$value->user['category'] = $directionPaths[$course->course['directions_ID']];
$value->user['course'] = $course->course['name'];
$value->user['directions_ID'] = $course->course['directions_ID'];
$value->user['branch'] = $branchesPaths[current($userBranches['employee'])];
$value->user['branch_ID'] = current($userBranches['employee']);
$value->user['supervisor'] = $userSupervisor;
$value->user['to_timestamp'] = $entry['timestamp'];
$value->user['completed'] = 1;
$value->user['score'] = '';
$value->user['historic'] = true;
$unique = md5($value->user['to_timestamp'] . $value->user['course_id'] . $value->user['login']);
if (!isset($courseUsers[$unique])) {
$courseUsers[$unique] = $value->user;
}
}
} catch (Exception $e) {
/*Bypass non-existing users*/
}
}
}
if ($_GET['ajax'] == 'xls') {
$xlsFilePath = $currentUser->getDirectory() . 'category_report.xls';
unlink($xlsFilePath);
$_GET['limit'] = sizeof($courseUsers);
$_GET['sort'] = 'category';
list($tableSize, $courseUsers) = filterSortPage($courseUsers);
$header = array('category' => _CATEGORY, 'course' => _NAME, 'login' => _USER, 'to_timestamp' => _COMPLETED, 'score' => _SCORE, 'supervisor' => _SUPERVISOR, 'branch' => _BRANCH, 'historic' => _MODULE_ADMINISTRATOR_TOOLS_HISTORICENTRY);
foreach ($courseUsers as $value) {
$rows[] = array(_CATEGORY => str_replace(" → ", " -> ", $value['category']), _COURSE => $value['course'], _USER => formatLogin($value['login']), _COMPLETED => formatTimestamp($value['to_timestamp']), _SCORE => $value['historic'] ? '' : formatScore($value['score']) . '%', _SUPERVISOR => formatLogin($value['supervisor']), _BRANCH => str_replace(" → ", " -> ", $value['branch']), _MODULE_ADMINISTRATOR_TOOLS_HISTORICENTRY => $value['historic'] ? _YES : _NO);
}
EfrontSystem::exportToXls($rows, $xlsFilePath);
exit;
} else {
if ($_GET['ajax'] == 'show_xls') {
$xlsFilePath = $currentUser->getDirectory() . 'category_report.xls';
$file = new EfrontFile($xlsFilePath);
$file->sendFile(true);
exit;
} else {
list($tableSize, $courseUsers) = filterSortPage($courseUsers);
$smarty->assign("T_SORTED_TABLE", $_GET['ajax']);
$smarty->assign("T_TABLE_SIZE", $tableSize);
$smarty->assign("T_DATA_SOURCE", $courseUsers);
}
}
}
$smarty->assign("T_CATEGORY_FORM", $form->toArray());
}