public function home()
{
global $user;
$v = $this->__(array('ssid', 'code', 'view', 'e', 'f', 'a' => 0, 'offset' => 0));
$total = 0;
$per_page = 15;
$sql = $sql_total = $sql_where = '';
if ($v['view'] == 'search') {
if (!f($v['ssid'])) {
_fatal();
}
$cached = sql_cache('', $v['ssid']);
$sql_total = $cached['query'];
$sql = $sql_total . ' LIMIT {v_limit_start}, {v_limit_end}';
$v['a_view'] = array('mode_alias' => 'search', 'mode_name' => 'Buscar', 'mode_sql' => $sql, 'mode_sql_total' => $sql_total);
} else {
$modes = $this->init_mode();
$v['view'] = isset($modes[$v['view']]) ? $v['view'] : 'all';
$v['a_view'] = $modes[$v['view']];
if ($v['a_view']['mode_alias'] == 'all' && $user->v('user_type') != U_FOUNDER) {
$sql = 'SELECT *
FROM _groups_members
WHERE member_uid = ?';
if (!($row = _fieldrow(sql_filter($sql, $user->v('user_id'))))) {
redirect(_link($this->m(), array('view' => 'own')));
}
}
}
$filter_uid = $user->v('user_id');
if (f($v['f'])) {
$sql = 'SELECT user_id
FROM _members
WHERE user_username = ?';
$filter_uid = _field(sql_filter($sql, $v['f']), 'user_id', $filter_uid);
}
$status_alias = $this->init_status_list('status_alias', 'status_id');
if ($v['e'] == 'table') {
$v['a_view']['mode_sql'] = str_replace('LIMIT {v_limit_start}, {v_limit_end}', '', $v['a_view']['mode_sql']);
}
$v_sql = array('userid' => $filter_uid, 'group' => $user->auth_groups(), 'limit_start' => $v['offset'], 'limit_end' => $per_page, 'closed' => $status_alias['closed']);
$tickets_sql = _template_query($v['a_view']['mode_sql'], $v_sql);
$tickets_sql_total = _template_query($v['a_view']['mode_sql_total'], $v_sql);
if (!f($tickets_sql) || !f($tickets_sql_total)) {
_fatal();
}
if ($row = _fieldrow($tickets_sql_total)) {
$total = isset($row['total']) ? $row['total'] : _numrows($row);
}
if ($tickets = _rowset($tickets_sql)) {
$groups = $user->_groups();
$status_list = $this->init_status_list();
if ($v['e'] == 'table') {
$sql_tickets = preg_replace('#^SELECT (.*?)[\\n]#is', 'SELECT ticket_id' . "\n", $tickets_sql);
//
// Assignees
$sql = 'SELECT a.assign_ticket, m.user_firstname, m.user_lastname
FROM _tickets_assign a, _members m
WHERE a.assign_ticket IN (' . $sql_tickets . ')
AND a.user_id = m.user_id
ORDER BY a.assign_ticket';
$tech_assoc = _rowset($sql, 'assign_ticket', false, true);
//_pre($tech_assoc, true);
//
// Notes
$sql = 'SELECT m.user_id, m.user_firstname, m.user_lastname, n.ticket_id, n.note_text, n.note_time, n.note_cc
FROM _members m, _tickets_notes n
WHERE n.ticket_id IN (' . $sql_tickets . ')
AND n.user_id = m.user_id
ORDER BY n.ticket_id, n.note_time';
$notes_assoc = _rowset($sql, 'ticket_id', false, true);
@set_time_limit(0);
//
// Include the PHPExcel classes
require_once XFS . 'core/excel/PHPExcel.php';
require_once XFS . 'core/excel/PHPExcel/Writer/Excel5.php';
require_once XFS . 'core/css.php';
$phpcss = new phpcss();
$phpcss->parse('./style/css/default.css');
// Start to build the spreadsheet
$excel = new PHPExcel();
$excel->setActiveSheetIndex(0);
$excel->getActiveSheet()->getHeaderFooter()->setOddFooter("&RPage &P of &N");
//
$excel->getActiveSheet()->setCellValue('A1', 'Titulo');
$excel->getActiveSheet()->setCellValue('B1', 'Asignado');
$excel->getActiveSheet()->setCellValue('C1', 'Categoria');
$excel->getActiveSheet()->setCellValue('D1', 'Solicitante');
$excel->getActiveSheet()->setCellValue('E1', 'Fecha/hora');
$excel->getActiveSheet()->setCellValue('F1', 'Texto');
$excel->getActiveSheet()->setCellValue('G1', 'Estado');
$excel->getActiveSheet()->getStyle("A1:G1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle("A1:G1")->getFont()->setBold(true);
$excel->getActiveSheet()->getStyle("A1:G1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('EBEBC6');
$excel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$excel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$excel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$excel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$excel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
$excel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$i = 2;
foreach ($tickets as $row) {
$row_color = $phpcss->property_get('.ticket_status_' . $status_list[$row['ticket_status']]['status_alias'], 'background');
$row_color = preg_replace('#^\\#([A-Za-z0-9]+).*?$#is', '\\1', $row_color);
if (!isset($row['cat_name'])) {
$row['cat_name'] = '';
}
$assignees = '';
if (isset($tech_assoc[$row['ticket_id']])) {
foreach ($tech_assoc[$row['ticket_id']] as $rowtech) {
$assignees .= (f($assignees) ? ', ' : '') . _fullname($rowtech);
}
}
$excel->getActiveSheet()->setCellValue("A{$i}", entity_decode($row['ticket_title']));
$excel->getActiveSheet()->setCellValue("B{$i}", entity_decode($assignees));
$excel->getActiveSheet()->setCellValue("C{$i}", entity_decode($row['cat_name']));
$excel->getActiveSheet()->setCellValue("D{$i}", entity_decode(_fullname($row)));
$excel->getActiveSheet()->setCellValue("E{$i}", entity_decode(_format_date($row['ticket_start'])));
$excel->getActiveSheet()->setCellValue("F{$i}", entity_decode($row['ticket_text']));
$excel->getActiveSheet()->setCellValue("G{$i}", entity_decode($status_list[$row['ticket_status']]['status_name']));
$excel->getActiveSheet()->getStyle("A{$i}:G{$i}")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$excel->getActiveSheet()->getStyle("A{$i}:G{$i}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB($row_color);
$i++;
if (isset($notes_assoc[$row['ticket_id']])) {
foreach ($notes_assoc[$row['ticket_id']] as $rownote) {
$excel->getActiveSheet()->setCellValue("B{$i}", entity_decode(_fullname($rownote)));
$excel->getActiveSheet()->setCellValue("E{$i}", entity_decode(_format_date($rownote['note_time'])));
$excel->getActiveSheet()->setCellValue("F{$i}", entity_decode($rownote['note_text']));
$excel->getActiveSheet()->mergeCells("F{$i}:G{$i}");
$excel->getActiveSheet()->getStyle("A{$i}:G{$i}")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$i++;
}
}
}
$excel->getActiveSheet()->freezePane('A2');
//
// Output the headers
header('Content-Type: application/vnd.ms-excel;');
header('Content-type: application/x-msexcel');
header('Content-Disposition: attachment; filename="solicitudes-' . date('Y-m-d') . '.xls"');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Last-Modified: ' . gmdate('D,d M YH:i:s') . ' GMT');
header('Cache-Control: no-cache, must-revalidate');
header('Pragma: no-cache');
// Output the spreadsheet in binary format
$writer = new PHPExcel_Writer_Excel5($excel);
$writer->save('php://output');
exit;
}
$u_link = array('view' => $v['view'], 'f' => $v['f']);
if ($v['view'] == 'search') {
$u_link['ssid'] = $v['ssid'];
}
$sv = array('TITLE' => $v['a_view']['mode_name'], 'SIZE' => $total, 'CURRENT' => $v['offset'], 'U_ETABLE' => _link($this->m(), array_merge($u_link, array('e' => 'table'))), 'G_ETABLE' => _lang('TICKET_ETABLE_YES'), 'U_TICKET' => _link($this->m(), array('x1' => 'view', 'a' => '*', 'offset' => $v['offset'])), 'U_STATUS' => _link($this->m(), array('x1' => 'search', 'x2' => 'status', 'e' => $v['e'])));
foreach ($tickets as $i => $row) {
if (!$i) {
$pags = _pagination(_link($this->m(), $u_link), 'offset:%d', $total, $per_page, $v['offset']);
_style('tickets', array_merge($sv, $pags));
}
if (!isset($row['ticket_group']) || !$row['ticket_group']) {
$row['ticket_group'] = 1;
}
$ticket_row = array('ID' => $row['ticket_id'], 'URL' => _link($this->m(), array('x1' => 'view', 'a' => $row['ticket_id'], 'offset' => $v['offset'])), 'STATUS' => $this->init_status($row['ticket_status']), 'STATUS_NAME' => $status_list[$row['ticket_status']]['status_name'], 'TITLE' => dvar($row['ticket_title'], _lang('TICKET_NO_SUBJECT')), 'TEXT' => _message($row['ticket_text']), 'START' => _format_date($row['ticket_start']), 'AUTHOR' => _fullname($row), 'GROUP' => $user->v('is_founder') ? $groups[$row['ticket_group']]['group_email'] : '');
_style('tickets.row', _vs($ticket_row, 'v'));
if (isset($tech_assoc[$row['ticket_id']])) {
foreach ($tech_assoc[$row['ticket_id']] as $ti => $tech_name) {
if (!$ti) {
_style('tickets.row.techs');
}
_style('tickets.row.techs.rowt', array('NAME' => _fullname($tech_name)));
}
}
if (isset($notes_assoc[$row['ticket_id']])) {
foreach ($notes_assoc[$row['ticket_id']] as $ti => $note_row) {
if (!$ti) {
_style('tickets.row.notes');
}
_style('tickets.row.notes.rown', array('TEXT' => $note_row['note_text'], 'TIME' => _format_date($note_row['note_time']), 'REALNAME' => _fullname($note_row)));
}
}
}
} else {
if ($v['offset']) {
redirect(_link($this->m(), array('view' => $v['view'])));
}
_style('noresults');
}
v_style(array('V_CHOWN' => f($v['f']) ? $v['f'] : ''));
return $this->__home_common($v['offset'], $v['view']);
}
protected function _create_home()
{
global $user;
$v = $tree = $this->init();
$z = $this->__(w('zmode'));
if (_button()) {
$v_ary = array('subject', 'content');
if ($user->v('is_founder')) {
$v_ary = array_merge($v_ary, array('node' => 0, 'parent' => 0, 'level' => 0, 'module' => 0, 'alias', 'child_hide' => 0, 'child_order', 'nav' => 0, 'nav_hide' => 0, 'css_parent' => 0, 'css_var', 'quickload' => 0, 'dynamic' => 0, 'tags', 'template', 'redirect', 'description', 'allow_comments' => 0, 'approve_comments' => 0, 'form' => 0, 'form_email', 'published', 'move'));
}
$v = $this->__($v_ary);
/*
$v = $this->__(array(
'node' => 0,
'parent' => 0,
'level' => 0,
'module' => 0,
'alias',
'child_hide' => 0,
'child_order',
'nav' => 0,
'nav_hide' => 0,
'css_parent' => 0,
'css_var',
'quickload' => 0,
'dynamic' => 0,
'tags',
'template',
'redirect',
'subject',
'content',
'description',
'allow_comments' => 0,
'approve_comments' => 0,
'form' => 0,
'form_email',
'published',
'move'
));
*/
//
$v['edited'] = time();
foreach (w('node level parent module') as $row) {
$v[$row] = $tree['tree_' . $row];
}
if ($z['zmode'] == 'create') {
$v['parent'] = $tree['tree_id'];
$v['level']++;
if (!$v['node']) {
$v['node'] = $v['parent'];
}
}
// Parse vars
foreach ($v as $row_k => $row_v) {
switch ($row_k) {
case 'subject':
$row_v = $this->html($row_v, 'strong');
break;
case 'content':
$row_v = $this->html($row_v);
break;
case 'alias':
$row_v = _alias($row_v, w('_'), '-');
break;
case 'checksum':
$row_v = _hash($v['content']);
break;
case 'published':
$row_v = dvar($row_v, date('d m Y'));
$e_date = explode(' ', $row_v);
$row_v = _timestamp($e_date[1], $e_date[0], $e_date[2]);
break;
}
$v[$row_k] = $row_v;
}
if ($z['zmode'] == 'modify' && $tree['tree_alias'] == 'home' && $v['alias'] != 'home') {
$v['alias'] = 'home';
}
if (f($v['alias'])) {
$sql = 'SELECT tree_id
FROM _tree
WHERE tree_alias = ?
AND tree_id <> ?';
if (_fieldrow(sql_filter($sql, $v['alias'], $tree['tree_id']))) {
$this->_error('#ALIAS_IN_USE');
}
}
if ($z['zmode'] == 'modify') {
if ($v['move']) {
$mv_field = !is_numb($v['move']) ? 'alias' : 'id';
$sql = 'SELECT *
FROM _tree
WHERE tree_?? = ?';
if ($mv_tree = _fieldrow(sql_filter($sql, $mv_field, $v['move']))) {
$mv_insert = array('module' => $mv_tree['module_id'], 'node' => $mv_tree['tree_node'], 'parent' => $mv_tree['tree_id'], 'level' => $mv_tree['tree_level'] + 1);
$sql = 'UPDATE _tree SET ' . _build_array('UPDATE', prefix('tree', $mv_insert)) . sql_filter('
WHERE article_id = ?', $tree['tree_id']);
_sql($sql);
$sql = 'UPDATE _tree SET tree_childs = tree_childs - 1
WHERE tree_id = ?';
_sql(sql_filter($sql, $tree['tree_parent']));
$sql = 'UPDATE _tree SET tree_childs = tree_childs + 1
WHERE tree_id = ?';
_sql(sql_filter($sql, $mv_tree['tree_id']));
}
}
unset($v['move']);
// Check input values against database
foreach ($v as $row_k => $row_v) {
if ($tree['tree_' . $row_k] == $row_v) {
unset($v[$row_k]);
}
}
if (!(count($v) - 1)) {
unset($v['edited']);
}
} else {
unset($v['move']);
}
//
$u_tree = _rewrite($tree);
if (count($v)) {
if (isset($v['content']) && $v['content']) {
$v['content'] = str_replace(w('< >'), w('< >'), $v['content']);
}
if ($z['zmode'] == 'create') {
$sql = 'INSERT INTO _tree' . _build_array('INSERT', prefix('tree', $v));
} else {
$sql = 'UPDATE _tree SET ' . _build_array('UPDATE', prefix('tree', $v)) . sql_filter('
WHERE tree_id = ?', $tree['tree_id']);
}
_sql($sql);
if ($z['zmode'] == 'create') {
$u_tree = f($v['alias']) ? $v['alias'] : _nextid();
$sql = 'UPDATE _tree
SET tree_childs = tree_childs + 1
WHERE tree_id = ?';
_sql(sql_filter($sql, $tree['tree_id']));
}
}
redirect(_link($u_tree));
}
//
// Show fieldset
/*$v_fieldset = array(
'subject',
'content',
'description',
'alias',
'child_hide' => 0,
'child_order',
'nav' => 0,
'nav_hide' => 0,
'css_parent',
'css_var',
'quickload' => 0,
'dynamic' => 0,
'tags',
'template',
'redirect',
'allow_comments' => 0,
'approve_comments' => 0,
'form' => 0,
'form_email',
'published'
);
*/
$v_fieldset = array('subject', 'content');
if ($user->v('is_founder')) {
$v_fieldset = array_merge($v_fieldset, array('description', 'alias', 'child_hide' => 0, 'child_order', 'nav' => 0, 'nav_hide' => 0, 'css_parent', 'css_var', 'quickload' => 0, 'dynamic' => 0, 'tags', 'template', 'redirect', 'allow_comments' => 0, 'approve_comments' => 0, 'form' => 0, 'form_email', 'published'));
}
$is_modify = $z['zmode'] == 'modify';
foreach (_array_keys($v_fieldset, '') as $k => $row) {
$name = 'tree_' . $k;
$cp_lang = _lang('CP_' . $k);
$value = $is_modify ? isset($v[$k]) ? $v[$k] : (isset($tree[$name]) ? $tree[$name] : '') : '';
$checked = is_numb($row) && $is_modify && $tree[$name] ? ' checked="checked"' : '';
if (f($value)) {
switch ($k) {
case 'published':
$value = date('d m Y', $value);
break;
}
}
$type = 'text';
if (is_numb($row)) {
$value = 1;
$type = 'checkbox';
}
$tag = 'input';
if ($k == 'content') {
$tag = 'textarea';
}
_style('field', array('NAME' => $k, 'ID' => $k, 'TAG' => $tag, 'TYPE' => $type, 'VALUE' => $value, 'LANG' => $cp_lang, 'CHECKED' => $checked));
if ($k == 'template') {
$i = 0;
$fp = @opendir('./style/custom/');
while ($row_d = @readdir($fp)) {
if (_extension($row_d) != 'htm') {
continue;
}
if (!$i) {
_style('field.templated');
_style('field.templated.row', array('V' => '', 'FILE' => _lang('NONE')));
}
$v_file = str_replace('.htm', '', $row_d);
_style('field.templated.row', array('V' => $v_file, 'FILE' => $v_file));
$i++;
}
@closedir($fp);
}
//
}
$cp_format = !$is_modify ? 'CREATE' : 'MODIFY';
v_style(array('CP_PAGE' => sprintf(_lang('CP_PAGE_' . $cp_format), $tree['tree_subject'])));
return;
}
$module->init();
}
hook(array($module, $module->x(1)));
if (!$module->_template()) {
$module->_template($mod);
}
if (@file_exists('./base/tree')) {
$menu = array_map('trim', @file('./base/tree'));
$i = 0;
foreach ($menu as $row) {
if (substr($row, 0, 1) == '#') {
continue;
}
preg_match('#^\\*{0,} (.*?) <(.*?)>$#i', $row, $row_key);
$row_level = strripos($row, '*') + 1;
$row_mod = array(dvar(array_key(explode('/', $row_key[2]), 1), 'home'));
if ($row_level > 1) {
$v_row_mod = array_key(explode(':', array_key(explode('.', array_key(explode('/', $row_key[2]), 2)), 0)), 1);
if (f($v_row_mod)) {
$row_mod[] = $v_row_mod;
}
}
if (!_auth_get(implode('_', $row_mod))) {
continue;
}
if (!$i) {
_style('tree');
}
_style('tree.row' . ($row_level > 1 ? '.sub' . ($row_level - 1) : ''), array('V_NAME' => trim(str_replace('*', '', $row_key[1])), 'V_LINK' => _link() . substr($row_key[2], 1)));
$i++;
}
function _xfs($mod = false, $wdir = false, $warg = false)
{
global $user, $style;
include_once(XFS . 'core/modules.php');
if ($mod === false)
{
$mod = request_var('module', '');
}
$mod = (!empty($mod)) ? $mod : 'home';
$mod_dir = './base/_' . $mod;
$p_dir = ($wdir === false && @file_exists($mod_dir) && is_dir($mod_dir)) ? true : false;
if (!$p_dir)
{
$mod_dir = './base/_' . (($wdir !== false) ? $wdir . '/_' : '') . $mod;
$mod_path = $mod_dir . '.php';
$mod_class = '__' . $mod;
if (!@file_exists($mod_path))
{
_fatal();
}
include_once($mod_path);
if (!class_exists($mod_class))
{
_fatal();
}
$module = new $mod_class();
}
if ($warg === false)
{
$warg = array();
$arg = request_var('args', '');
if (!empty($arg))
{
foreach (explode('.', $arg) as $v)
{
$el = explode(':', $v);
if (isset($el[0]) && isset($el[1]) && !empty($el[0]))
{
$warg[$el[0]] = $el[1];
}
}
}
if (isset($_POST) && count($_POST))
{
$_POST = _utf8($_POST);
$warg = array_merge($warg, $_POST);
}
}
if ($p_dir)
{
_xfs(((isset($warg['x1'])) ? $warg['x1'] : ''), $mod, $warg);
}
else
{
if (isset($module->auth) && $module->auth)
{
$v_auth_exclude = (isset($module->auth_exclude) && (isset($warg['x1']) && in_array($warg['x1'], $module->auth_exclude))) ? true : false;
if (!$v_auth_exclude)
{
_login();
}
}
$warg_x = 0;
foreach ($warg as $warg_k => $warg_v)
{
if (preg_match('/x\d+/i', $warg_k))
{
$warg_x = str_replace('x', '', $warg_k);
}
}
if ($wdir !== false)
{
for ($i = 0; $i < $warg_x; $i++)
{
$warg['x' . ($i + 1)] = (isset($warg['x' + ($i + 2)])) ? $warg['x' + ($i + 2)] : '';
}
}
}
date_default_timezone_set('America/Guatemala');
$module->arg = $warg;
$module->xlevel();
if (!method_exists($module, $module->level['x1']))
{
_fatal();
}
// Session start
$user->start(true);
$user->setup();
if (!$module->auth_access($user->data))
{
_fatal();
}
if (!defined('LIB')) define('LIB', '../space/');
if (!defined('LIBD')) define('LIBD', _link() . str_replace('../', '', LIB));
$module->module = $mod;
if (@method_exists($module, 'install'))
{
$module->install();
}
$module->navigation('home', '', '');
$module->navigation($module->module, '');
$module->{$module->level['x1']}();
if (empty($module->template))
{
$module->template = $mod;
}
if (@file_exists('./base/tree'))
{
$menu = array_map('trim', @file('./base/tree'));
foreach ($menu as $i => $row)
{
if (!$i) $style->assign_block_vars('tree', array());
$row = trim($row);
$row_level = strripos($row, '*') + 1;
preg_match('#^\*{0,} (.*?) <(.*?)>$#i', $row, $row_key);
$row_mod = array(dvar(array_key(explode('/', $row_key[2]), 1), 'index'));
if ($row_level > 1) $row_mod[] = array_key(explode(':', array_key(explode('.', array_key(explode('/', $row_key[2]), 2)), 0)), 1);
$row_auth = implode('_', $row_mod);
if (!$user->auth_get($row_auth)) continue;
$row_style = '.row' . (($row_level == 1) ? '' : '.sub' . ($row_level - 1));
$style->assign_block_vars('tree' . $row_style, array(
'V_NAME' => trim(str_replace('*', '', $row_key[1])),
'V_LINK' => _link() . substr($row_key[2], 1))
);
}
}
//
// Output template
$page_smodule = 'CONTROL_' . strtoupper($mod);
if (is_lang($page_smodule))
{
$module->page_title($page_smodule);
}
$sv = array(
'MODE' => $module->level['x1'],
'MANAGE' => $module->level['x2'],
'NAVIGATION' => $module->get_navigation()
);
_layout($module->template, $module->page_title(), $sv);
}
