views-view-table--unigene_search.tpl.php 9.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219
  1. <?php
  2. // $Id: views-view-table.tpl.php,v 1.8 2009/01/28 00:43:43 merlinofchaos Exp $
  3. /**
  4. * @file views-view-table.tpl.php
  5. * Template to display a view as a table.
  6. *
  7. * - $title : The title of this group of rows. May be empty.
  8. * - $header: An array of header labels keyed by field id.
  9. * - $fields: An array of CSS IDs to use for each field id.
  10. * - $class: A class or classes to apply to the table, based on settings.
  11. * - $row_classes: An array of classes to apply to each row, indexed by row
  12. * number. This matches the index in $rows.
  13. * - $rows: An array of row items. Each row is an array of content.
  14. * $rows are keyed by row number, fields within rows are keyed by field ID.
  15. * @ingroup views_templates
  16. */
  17. ?>
  18. <?php
  19. $total = $view->total_rows;
  20. $query = $view->build_info['query'];
  21. $pager = $view->pager;
  22. $limit = $pager ['items_per_page'];
  23. $offset = $pager ['current_page'] * $limit;
  24. $args = $view->build_info['query_args'];
  25. global $base_url;
  26. ?>
  27. <a id="tripal_search_unigene-result-top" name="search_results">
  28. &nbsp;&nbsp;&nbsp;Your search produced <b><?php print $total ?></b> results<br>
  29. <i>&nbsp;&nbsp;&nbsp;Note: To get complete annotation for a sequence, click on the sequence name.</i>
  30. </a>
  31. <table class="tripal_search_unigene-table <?php print $class; ?> tripal-table-horz">
  32. <?php if (!empty($title)) : ?>
  33. <caption><?php print $title; ?></caption>
  34. <?php endif; ?>
  35. <thead>
  36. <tr class="tripal_search_unigene-table-header tripal-table-header">
  37. <?php foreach ($header as $field => $label): ?>
  38. <th class="views-field views-field-<?php print $fields[$field]; ?>">
  39. <?php print $label; ?>
  40. </th>
  41. <?php endforeach; ?>
  42. </tr>
  43. </thead>
  44. <tbody>
  45. <?php foreach ($rows as $count => $row):
  46. $rowclass = "";
  47. if ($count % 2 != 0) {
  48. $rowclass = "tripal_search_unigene-table-even-row tripal-table-even-row";
  49. } else {
  50. $rowclass = "tripal_search_unigene-table-odd-row tripal-table-odd-row";
  51. }
  52. ?>
  53. <tr class="<?php print $rowclass?> <?php print implode(' ', $row_classes[$count]); ?>">
  54. <?php foreach ($row as $field => $content): ?>
  55. <td class="views-field views-field-<?php print $fields[$field]; ?>">
  56. <?php print $content; ?>
  57. </td>
  58. <?php endforeach; ?>
  59. </tr>
  60. <?php endforeach; ?>
  61. </tbody>
  62. </table>
  63. <?php if ($count != 0) {
  64. // Generate Excel files
  65. // set parameters for excel file
  66. $param_excel= array();
  67. global $user;
  68. $dir = file_directory_path().'/tripal/tripal_search_unigene/'.session_id();
  69. if (!file_exists($dir)) {
  70. mkdir ($dir, 0777);
  71. }
  72. $file =session_id().'/unigene_search_result.xls';
  73. $param_excel['sheet_name'] = 'unigene_search';
  74. $path = "/tripal/tripal_search_unigene/$file";
  75. $param_excel['filename'] = file_directory_path().$path;
  76. $path_url =url("sites/default/files/tripal/tripal_search_unigene/$file");
  77. # set SQL
  78. $sql = "$query LIMIT $limit OFFSET $offset";
  79. # create heades
  80. $col_chr = 'A';
  81. $headers = array();
  82. $headers[$col_chr++] = array('align' => 'left', 'width' => 20, 'type' => 'hyperlink', 'field' => 'unigene_for_search_feature_name', 'heading' => 'Feature name');
  83. $headers[$col_chr++] = array('align' => 'left', 'width' => 10, 'type' => 'text', 'field' => 'unigene_for_search_organism_common_name', 'heading' => 'Organism');
  84. $headers[$col_chr++] = array('align' => 'left', 'width' => 10, 'type' => 'text', 'field' => 'unigene_for_search_feature_seqlen', 'heading' => 'Length');
  85. $headers[$col_chr++] = array('align' => 'left', 'width' => 10, 'type' => 'text', 'field' => 'unigene_for_search_feature_type', 'heading' => 'Type');
  86. $headers[$col_chr++] = array('align' => 'left', 'width' => 20, 'type' => 'text', 'field' => 'unigene_for_search_go_term', 'heading' => 'GO term');
  87. $headers[$col_chr++] = array('align' => 'left', 'width' => 20, 'type' => 'text', 'field' => 'unigene_for_search_blast_value', 'heading' => 'Blast');
  88. $headers[$col_chr++] = array('align' => 'left', 'width' => 20, 'type' => 'text', 'field' => 'unigene_for_search_kegg_value', 'heading' => 'KEGG');
  89. $headers[$col_chr++] = array('align' => 'left', 'width' => 20, 'type' => 'text', 'field' => 'unigene_for_search_interpro_value', 'heading' => 'Interpro');
  90. # populate data
  91. $result = chado_query($sql, $args);
  92. $data = array();
  93. while ($row = db_fetch_array($result)) {
  94. $data[] = array ('unigene_for_search_feature_name' => $row['unigene_for_search_feature_name'],
  95. 'hyperlink_unigene_for_search_feature_name' => "$base_url/ID".$row['unigene_for_search_feature_id'],
  96. 'unigene_for_search_organism_common_name' => $row['unigene_for_search_organism_common_name'],
  97. 'unigene_for_search_feature_seqlen' => $row['unigene_for_search_feature_seqlen'],
  98. 'unigene_for_search_feature_type' => $row['unigene_for_search_feature_type'],
  99. 'unigene_for_search_go_term' => $row['unigene_for_search_go_term'],
  100. 'unigene_for_search_blast_value' => $row['unigene_for_search_blast_value'],
  101. 'unigene_for_search_kegg_value' => $row['unigene_for_search_kegg_value'],
  102. 'unigene_for_search_interpro_value' => $row['unigene_for_search_interpro_value']
  103. );
  104. }
  105. # add headers and data
  106. $param_excel['contents']['sheet']['headers']= $headers;
  107. $param_excel['contents']['sheet']['data']= $data;
  108. # generate excel
  109. ml_generate_excel($param_excel);
  110. print "&nbsp;&nbsp;&nbsp;<a href=\"$path_url\">Download as Excel file</a>";
  111. }?>
  112. <?php
  113. # ----------------------------------------- #
  114. # [UD] ml_generate_excel
  115. # ----------------------------------------- #
  116. function ml_generate_excel ($param_excel) {
  117. # get and update parameters
  118. $writer = ($param_excel['version'] == '2007') ? 'Excel2007' : 'Excel5';
  119. $link_clr = (empty($param_excel['link_clr'])) ? '0000FF' : $param_excel['link_clr'];
  120. $filename = $param_excel['filename'];
  121. if (!preg_match("/(xls|xlsx)$/", $filename)) {
  122. $filename = ($param_excel['version'] == '2007') ? $filename.'.xlsx' : $filename.'.xls';
  123. }
  124. /*********************************************/
  125. # create an excel file
  126. /*********************************************/
  127. # create new PHPExcel object
  128. $excel = new PHPExcel();
  129. # Set properties
  130. $excel->getProperties()->setCreator("Main Lab");
  131. $excel->getProperties()->setLastModifiedBy("Main Lab");
  132. $excel->getProperties()->setTitle("Office 2007 XLSX Document");
  133. $excel->getProperties()->setSubject("");
  134. $excel->getProperties()->setKeywords("");
  135. $excel->getProperties()->setCategory("");
  136. $excel->getProperties()->setDescription("");
  137. # set default styles
  138. $excel->getDefaultStyle()->getFont()->setName('Times New Roman');
  139. $excel->getDefaultStyle()->getFont()->setSize(11);
  140. # create worksheets
  141. $sheet_no= 0;
  142. foreach ($param_excel['contents'] as $sheet_name => $sheet) {
  143. if ($sheet_no != 0) {
  144. # add a new worksheet
  145. $excel->createSheet();
  146. }
  147. # set active sheet
  148. $excel->setActiveSheetIndex($sheet_no);
  149. # get current worksheet
  150. $worksheet = $excel->getActiveSheet();
  151. # set title for this sheet
  152. $worksheet->setTitle($sheet_name);
  153. # get headers
  154. $headers= $param_excel['contents'][$sheet_name]['headers'];
  155. # set style and headings for headers
  156. foreach ($headers as $col_chr => $prop) {
  157. # set alignment for column
  158. $align= PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
  159. if ($prop['align'] == 'right') {
  160. $align= PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
  161. }
  162. else if ($prop['align'] == 'center') {
  163. $align= PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
  164. }
  165. $worksheet->getStyle($col_chr.'1')->getAlignment()->setHorizontal($align);
  166. # set width
  167. $worksheet->getColumnDimension($col_chr)->setWidth($prop['width']);
  168. $worksheet->getStyle($col_chr.'1')->getFont()->setBold(true);
  169. $worksheet->getStyle($col_chr.'1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  170. $worksheet->getStyle($col_chr.'1')->getFill()->getStartColor()->setARGB('FFCDCDCD');
  171. $worksheet->setCellValue($col_chr.'1', $prop['heading']);
  172. }
  173. # add data to current worksheet
  174. foreach ($param_excel['contents'][$sheet_name]['data'] as $idx => $row) {
  175. foreach ($headers as $col_chr => $prop) {
  176. $loc= $col_chr.($idx+2);
  177. # set alignment for cell
  178. $align= PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
  179. if ($prop['align'] == 'right') {
  180. $align= PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
  181. }
  182. else if ($prop['align'] == 'center') {
  183. $align= PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
  184. }
  185. $worksheet->getStyle($loc)->getAlignment()->setHorizontal($align);
  186. if (!empty($row[$prop['field']])) {
  187. # set cell value
  188. if ($prop['type'] == 'text') {
  189. $worksheet->setCellValue($loc, $row[$prop['field']]);
  190. }
  191. else if ($prop['type'] == 'hyperlink') {
  192. $worksheet->setCellValue($loc, $row[$prop['field']]);
  193. if ($row['hyperlink_'.$prop['field']]) {
  194. # add underline
  195. $worksheet->getStyle($loc)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
  196. # set link color
  197. $worksheet->getStyle($loc)->getFont()->getColor()->setRGB($link_clr);
  198. # set URL
  199. $worksheet->getCell($loc)->getHyperlink()->setUrl($row['hyperlink_'.$prop['field']]);
  200. }
  201. }
  202. }
  203. }
  204. }
  205. $sheet_no++;
  206. }
  207. # create excel file
  208. $excel->setActiveSheetIndex(0);
  209. $objWriter = PHPExcel_IOFactory::createWriter($excel, $writer);
  210. $objWriter->save($filename);
  211. }
  212. ?>