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>"; }