Пишем модуль под Drupal. Часть 7. Переделываем вывод трэкера

 
function _cctracker_get_query($arg, $tp){
  $cnt = variable_get('cctracker_node_per_page', 50);
  $types = variable_get('cctracker_node_types', array());
  $placeholder = db_placeholders($types, 'varchar');
  switch ($tp) {
    // Все материалы определенные для показа
    case 'all':
      // запишем  запрос в строку $sql в формате heredoc (что бы он был более читемым)
      // в принципе данный запрос, возвращает все нужне нам данные
      $sql = <<<EOT
SELECT DISTINCT(n.nid) AS nid, n.title, n.type, n.changed, n.uid, n.created, u.name,
 GREATEST(n.changed, l.last_comment_timestamp) AS last_update,
 l.comment_count,nc.totalcount, nc.daycount,nc.timestamp AS lastread,
 n.promote,
 ncc.cc_sticky, ncc.cc_sticky_weight
 FROM {node} n
 INNER JOIN {node_comment_statistics} l ON n.nid = l.nid AND n.status=1 AND n.type IN ($placeholder)
 LEFT JOIN {comments} c ON n.nid = c.nid
 LEFT JOIN {users} u ON u.uid=n.uid
 LEFT JOIN {node_counter} nc ON nc.nid = n.nid
 LEFT JOIN {node_cctracker} ncc ON ncc.nid = n.nid
 WHERE (ncc.cc_not_public IS NULL  OR ncc.cc_not_public=0)
 ORDER BY ncc.cc_sticky DESC, ncc.cc_sticky_weight, last_update DESC
EOT;
      $sql = db_rewrite_sql($sql);
     // этот запрос даст нам общее количество материалов в трэкере
      $sql_cnt  =  'SELECT COUNT(n.nid) FROM {node} n  LEFT JOIN {node_cctracker} ncc ON ncc.nid = n.nid WHERE (ncc.cc_not_public IS NULL  OR ncc.cc_not_public=0)  AND n.status=1 AND n.type IN (' . $placeholder. ')';

      $sql_cnt = db_rewrite_sql($sql_cnt);
      $result = pager_query($sql, $cnt, 0, $sql_cnt, $types);
      break;
    // Материалы с опубликованные текущим пользователем
    case 'my-posts':
      $sql = <<<EOT
SELECT DISTINCT(n.nid) AS nid, n.title, n.type, n.changed, n.uid, n.created, u.name,
 GREATEST(n.changed, l.last_comment_timestamp) AS last_update,
 l.comment_count,nc.totalcount, nc.daycount,nc.timestamp AS lastread,
 n.promote,
 ncc.cc_sticky, ncc.cc_sticky_weight
 FROM {node} n
 INNER JOIN {node_comment_statistics} l ON n.nid = l.nid AND n.status=1 AND n.type IN ($placeholder) AND n.uid =%d
 LEFT JOIN {comments} c ON n.nid = c.nid
 LEFT JOIN {users} u ON u.uid=n.uid
 LEFT JOIN {node_counter} nc ON nc.nid = n.nid
 LEFT JOIN {node_cctracker} ncc ON ncc.nid = n.nid
 WHERE (ncc.cc_not_public IS NULL  OR ncc.cc_not_public=0)
 ORDER BY ncc.cc_sticky DESC, ncc.cc_sticky_weight,last_update DESC
EOT;
      $sql = db_rewrite_sql($sql);
      $sql_cnt  =  'SELECT COUNT(n.nid) FROM {node} n LEFT JOIN {node_cctracker} ncc ON ncc.nid = n.nid  WHERE (ncc.cc_not_public IS NULL  OR ncc.cc_not_public=0)  AND n.status=1 AND n.type IN (' . $placeholder. ') AND n.uid = %d ';
      $sql_cnt = db_rewrite_sql($sql_cnt);
      $types[] = $arg->uid;
      $result = pager_query($sql, $cnt, 0, $sql_cnt, $types);
      break;
    // Материалы с участием текущего пользователя
    case 'my-recent-posts':
      $sql = <<<EOT
SELECT DISTINCT(n.nid) AS nid, n.title, n.type, n.changed, n.uid, n.created, u.name,
 GREATEST(n.changed, l.last_comment_timestamp) AS last_update,
 l.comment_count,nc.totalcount, nc.daycount,nc.timestamp AS lastread,
 n.promote,
 ncc.cc_sticky, ncc.cc_sticky_weight
 FROM {node} n
 INNER JOIN {node_comment_statistics} l ON n.nid = l.nid AND n.status=1 AND n.type IN ($placeholder)
 LEFT JOIN {comments} c ON n.nid = c.nid   AND (c.status = %d OR c.status IS NULL)
 LEFT JOIN {users} u ON u.uid=n.uid
 LEFT JOIN {node_counter} nc ON nc.nid = n.nid
 LEFT JOIN {node_cctracker} ncc ON ncc.nid = n.nid
 WHERE (n.uid = %d OR c.uid=%d) AND (ncc.cc_not_public IS NULL  OR ncc.cc_not_public=0)
 ORDER BY ncc.cc_sticky DESC, ncc.cc_sticky_weight, last_update DESC
EOT;
      $sql = db_rewrite_sql($sql);
      $sql_cnt = <<<EOT
SELECT COUNT(DISTINCT(n.nid)) FROM {node} n
 INNER JOIN {node_comment_statistics} l ON n.nid = l.nid AND n.status=1 AND n.type IN ($placeholder)
 LEFT JOIN {comments} c ON n.nid = c.nid   AND (c.status = %d OR c.status IS NULL)
 LEFT JOIN {node_cctracker} ncc ON ncc.nid = n.nid
 WHERE (n.uid = %d OR c.uid=%d) AND (ncc.cc_not_public IS NULL  OR ncc.cc_not_public=0)
EOT;
      $sql_cnt = db_rewrite_sql($sql_cnt);
      $types[] = COMMENT_PUBLISHED;
      $types[] = $arg->uid;
      $types[] = $arg->uid;
      $result = pager_query($sql, $cnt, 0, $sql_cnt, $types);
      break;
    // Материалы определенного типа
    case 'tab-types':
      $sql = <<<EOT
SELECT DISTINCT(n.nid) AS nid, n.title, n.type, n.changed, n.uid, n.created, u.name,
 GREATEST(n.changed, l.last_comment_timestamp) AS last_update,
 l.comment_count,nc.totalcount, nc.daycount,nc.timestamp AS lastread,
 n.promote,
 ncc.cc_sticky, ncc.cc_sticky_weight
 FROM {node} n
 INNER JOIN {node_comment_statistics} l ON n.nid = l.nid AND n.status=1 AND n.type = '%s'
 LEFT JOIN {comments} c ON n.nid = c.nid
 LEFT JOIN {users} u ON u.uid=n.uid
 LEFT JOIN {node_counter} nc ON nc.nid = n.nid
 LEFT JOIN {node_cctracker} ncc ON ncc.nid = n.nid
 WHERE (ncc.cc_not_public IS NULL  OR ncc.cc_not_public=0)
 ORDER BY ncc.cc_sticky DESC, ncc.cc_sticky_weight, last_update DESC
EOT;
      $sql = db_rewrite_sql($sql);
      $sql_cnt  =  'SELECT COUNT(n.nid) FROM {node} n  LEFT JOIN {node_cctracker} ncc ON ncc.nid = n.nid WHERE (ncc.cc_not_public IS NULL  OR ncc.cc_not_public=0)  AND n.status=1 AND n.type = \'%s\'';

      $sql_cnt = db_rewrite_sql($sql_cnt);
      $result = pager_query($sql, $cnt, 0, $sql_cnt, $arg);
      break;
  }
  return $result;
}

 
function theme_cctracker_page($tracker, $tp) {
  $module_path = drupal_get_path('module', 'cctracker');
  $header = array();
  if ($tp != 'tab-types'){
  $header[] = t('Type');
  }
  $header[] =  t('Post');
  if ($tp != 'my-posts') {
  $header[] =  t('Author');
  }
  $header[] =  t('Created');
  $header[] =  t('Replies');
  $header[] =  t('Last updated');
  $rows = array();
  foreach($tracker as $key => $value){
      $comments = 0;
      if ($value->comment_count) {
        $comments = $value->comment_count;
        if ($new = comment_num_new($value->nid)) {
          $comments .= '<br />';
          $comments .= l(format_plural($new, '1 new', '@count new'), 'node/'. $value->nid, array('fragment' => 'new'));
        }
      }
     $row = array();
     if ($tp != 'tab-types'){
       $row[] = array('data' => check_plain(node_get_types('name', $value->type)), 'class' => 'cctracker-type-' . $value->type);
     }
     $row[] = array('data' => l($value->title,'node/'.$key) .' '. theme('mark', node_mark($value->nid, $value->changed)) , 'class' => 'cctracker-title');
     if ($tp != 'my-posts') {
       $row[] = array('data' => theme('username',$value), 'class' => 'cctracker-user');
     }
     $row[] = array('data' => format_date($value->created,'small'), 'class' => 'cctracker-date-created');
     $row[] = array('data' => $comments, 'class' => 'cctracker-replies');
     $row[] = array('data' => t('!time ago', array('!time' => format_interval(time()-$value->last_update))), 'class' => 'cctracker-last-update');
     $rows[] = array('data'=>$row, 'id' => 'cctracker-' . $value->nid, 'class' => 'cctracker-row');
  }
  $output = theme('table', $header, $rows);
  return $output;
}