function filedepot_getFileListingSQL in filedepot 6

Same name and namespace in other branches

1. 7 lib-ajaxserver.php \filedepot_getFileListingSQL()

1 call to filedepot_getFileListingSQL()

nexdocsrv_generateFileListing in ./lib-ajaxserver.php

File

./lib-ajaxserver.php, line 379

lib-ajaxserver.php Library functions for the ajax_server

Code

function filedepot_getFileListingSQL($cid) {
  global $user;
  $filedepot = filedepot_filedepot();
  $sql = '';

  // Check and see if this is a custom report
  if (function_exists('filedepot_customReportFilesSQL')) {
    $sql = trim(filedepot_customReportFilesSQL($cid, $filedepot->activeview));
    if (!empty($sql)) {
      return $sql;
    }
  }
  $sql = "SELECT file.fid as fid,file.cid,file.title,file.fname,file.date,file.version,file.submitter,file.status,";
  $sql .= "file.description,category.name as foldername,category.pid,category.nid,category.last_modified_date,status_changedby_uid as changedby_uid, size ";
  $sql .= "FROM {filedepot_files} file ";
  $sql .= "LEFT JOIN {filedepot_categories} category ON file.cid=category.cid ";
  if ($filedepot->activeview == 'lockedfiles') {
    $sql .= "WHERE file.status=2 AND status_changedby_uid={$user->uid} ORDER BY date DESC LIMIT {$filedepot->maxDefaultRecords}";
  }
  elseif ($filedepot->activeview == 'downloads') {

    // Will return multiple records for same file as we capture download records each time a user downloads it
    $sql .= "LEFT JOIN {filedepot_downloads} downloads on downloads.fid=file.fid ";
    $sql .= "WHERE uid={$user->uid} ";
    $sql .= "ORDER BY file.date DESC LIMIT {$filedepot->maxDefaultRecords}";
  }
  elseif ($filedepot->activeview == 'unread') {
    $sql .= "LEFT OUTER JOIN {filedepot_downloads} downloads on downloads.fid=file.fid ";
    $sql .= "WHERE downloads.fid IS NULL ";
    if (empty($filedepot->allowableViewFoldersSql)) {
      $sql .= "AND file.cid is NULL ";
    }
    else {
      $sql .= "AND file.cid in ({$filedepot->allowableViewFoldersSql}) ";
    }
    $sql .= "ORDER BY file.date DESC LIMIT {$filedepot->maxDefaultRecords}";
  }
  elseif ($filedepot->activeview == 'incoming') {
    $sql = "SELECT id as fid, 0 as cid, orig_filename as title,  queue_filename as fname, timestamp as date, 0 as version, ";
    $sql .= "uid as submitter, 0 as status, 'N/A' as description, 'Incoming Files' as name, 0 as pid, 0 as changedby_uid, size ";
    $sql .= "FROM {filedepot_import_queue} ";
    if (!user_access('administer filedepot', $user)) {
      $sql .= "WHERE uid={$user->uid} ";
    }
    $sql .= "ORDER BY date DESC ";
  }
  elseif ($filedepot->activeview == 'flaggedfiles') {
    $sql .= "LEFT JOIN {filedepot_favorites} favorites on favorites.fid=file.fid ";
    $sql .= "WHERE uid={$user->uid} ";
  }
  elseif ($filedepot->activeview == 'myfiles') {
    $sql .= "WHERE file.submitter={$user->uid} ORDER BY date DESC LIMIT {$filedepot->maxDefaultRecords}";
  }
  elseif ($filedepot->activeview == 'approvals') {

    // Determine if this user has any submitted files that they can approve
    $sql = "SELECT file.id,file.cid,file.title,file.fname,file.date,file.version,file.submitter,file.status,";
    $sql .= "file.description,category.name as foldername,category.pid,0 as changedby_uid, size ";
    $sql .= "FROM {filedepot_filesubmissions} file ";
    $sql .= "LEFT JOIN {filedepot_categories} category ON file.cid=category.cid ";
    if (!user_access('administer filedepot', $user)) {
      $categories = $filedepot
        ->getAllowableCategories(array(
        'approval',
        'admin',
      ));
      if (empty($categories)) {
        $sql .= "WHERE file.cid is NULL ";
      }
      else {
        $sql .= "WHERE file.cid in ({$categories}) ";
      }
    }
    $sql .= "ORDER BY file.date DESC ";
  }
  elseif ($cid > 0) {
    $sql .= "WHERE file.cid={$cid} ORDER BY file.date DESC, file.fid DESC ";
    if ($filedepot->activeview == 'getmorefolderdata') {
      if (isset($_POST['pass2']) and $_POST['pass2'] == 1) {
        if ($GLOBALS['db_type'] == 'pgsql') {
          $sql .= "LIMIT 100000 OFFSET {$filedepot->recordCountPass1}";
        }
        else {
          $sql .= "LIMIT {$filedepot->recordCountPass1}, 100000 ";
        }
      }
      else {
        $recordoffset = $filedepot->recordCountPass2 + $filedepot->recordCountPass1;
        $filedepot->folder_filenumoffset = $recordoffset;
        if ($GLOBALS['db_type'] == 'pgsql') {
          $sql .= "LIMIT 100000 OFFSET {$recordoffset}";
        }
        else {
          $sql .= "LIMIT {$recordoffset}, 100000 ";
        }
      }
    }
    elseif ($filedepot->activeview != 'getallfiles') {

      // Set SQL query options for amount of data to return - used by the AJAX routine getmorefiledata to populate display in the background
      if ($filedepot->lastRenderedFolder == $cid) {
        $filedepot->folder_filenumoffset = $filedepot->recordCountPass1;
        $folder_filelimit = $filedepot->recordCountPass2 + 1;
        if ($GLOBALS['db_type'] == 'pgsql') {
          $sql .= "LIMIT {$folder_filelimit} OFFSET {$filedepot->recordCountPass1} ";
        }
        else {
          $sql .= "LIMIT {$filedepot->recordCountPass1}, {$folder_filelimit} ";
        }
      }
      else {
        if ($GLOBALS['db_type'] == 'pgsql') {
          $sql .= "LIMIT {$filedepot->recordCountPass1} OFFSET 0 ";
        }
        else {
          $sql .= "LIMIT 0, {$filedepot->recordCountPass1} ";
        }
      }
    }
  }
  else {
    if (!user_access('administer filedepot', $user)) {
      if (empty($filedepot->allowableViewFoldersSql)) {
        $sql .= "WHERE file.cid is NULL ";
      }
      else {
        $sql .= "WHERE file.cid in ({$filedepot->allowableViewFoldersSql}) ";
      }
    }
    $sql .= "ORDER BY file.date DESC LIMIT {$filedepot->maxDefaultRecords}";
  }
  return $sql;
}