Example #1
0
    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']);
    }
function fnProsesGantiProdiDariProdiDikti($NamaDatabaseLama, $NamaDatabaseBaru, $KodeIDBaru, $table)
{
    $arrProdi = array('FT', 'GZ', 'K3', 'IK', 'NS');
    $arrProdiDikti = array('11301', '13211', '13341', '14201', '14901');
    $n = 0;
    foreach ($arrProdi as $perprodi) {
        $s = "update {$NamaDatabaseBaru}.{$table} set ProdiID='{$perprodi}' where ProdiID='{$arrProdiDikti[$n]}'";
        $r = _query($s);
        $n += _numrows($r);
    }
    echo "<li>Updating {$table}... {$n} records</li>";
}