function filedepot_getFileListingSQL in filedepot 7

Same name and namespace in other branches

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

1 call to filedepot_getFileListingSQL()

nexdocsrv_generateFileListing in ./lib-ajaxserver.php

File

./lib-ajaxserver.php, line 580

lib-ajaxserver.php Library functions for the ajax_server

Code

function filedepot_getFileListingSQL($cid, &$out_limit_start, &$out_limit_end) {
  global $user;
  $filedepot = filedepot_filedepot();
  $out_limit_start = 0;
  $display_orderby = variable_get('filedepot_override_folderorder', 0) ? 'file.title ASC, file.date DESC' : 'file.date DESC';
  $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,file.fileorder,folderindex.folderprefix,";
  $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 ";
  $sql .= "LEFT JOIN {filedepot_folderindex} folderindex ON file.cid=folderindex.cid AND folderindex.uid = {$user->uid} ";
  $limit = FALSE;
  if ($filedepot->activeview == 'lockedfiles') {
    $sql .= "WHERE file.status=2 AND status_changedby_uid={$user->uid} ";
    if ($filedepot->ogmode_enabled and !empty($filedepot->allowableGroupViewFoldersSql)) {
      $sql .= "AND file.cid in ({$filedepot->allowableGroupViewFoldersSql}) ";
    }
    $sql .= "ORDER BY {$display_orderby} ";
    $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 downloads.uid={$user->uid} ";
    if ($filedepot->ogmode_enabled and !empty($filedepot->allowableGroupViewFoldersSql)) {
      $sql .= "AND file.cid in ({$filedepot->allowableGroupViewFoldersSql}) ";
    }
    $sql .= "ORDER BY {$display_orderby} ";
    $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 ($filedepot->ogmode_enabled and !empty($filedepot->allowableGroupViewFoldersSql)) {
      $sql .= "AND file.cid in ({$filedepot->allowableGroupViewFoldersSql}) ";
    }
    elseif (empty($filedepot->allowableViewFoldersSql)) {
      $sql .= "AND file.cid is NULL ";
    }
    else {
      $sql .= "AND file.cid in ({$filedepot->allowableViewFoldersSql}) ";
    }
    $sql .= "ORDER BY {$display_orderby} ";
    $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 {$display_orderby} ";
  }
  elseif ($filedepot->activeview == 'flaggedfiles') {
    $sql .= "LEFT JOIN {filedepot_favorites} favorites on favorites.fid=file.fid ";
    $sql .= "WHERE favorites.uid={$user->uid} ";
    if ($filedepot->ogmode_enabled and !empty($filedepot->allowableGroupViewFoldersSql)) {
      $sql .= "AND file.cid in ({$filedepot->allowableGroupViewFoldersSql}) ";
    }
    $sql .= "ORDER BY {$display_orderby} ";
    $limit = $filedepot->maxDefaultRecords;
  }
  elseif ($filedepot->activeview == 'myfiles') {
    $sql .= "WHERE file.submitter={$user->uid} ";
    if ($filedepot->ogmode_enabled and !empty($filedepot->allowableGroupViewFoldersSql)) {
      $sql .= "AND file.cid in ({$filedepot->allowableGroupViewFoldersSql}) ";
    }
    $sql .= "ORDER BY {$display_orderby} ";
    $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}) ";
      }
      if ($filedepot->ogmode_enabled and !empty($filedepot->allowableGroupViewFoldersSql)) {
        $sql .= "AND file.cid in ({$filedepot->allowableGroupViewFoldersSql}) ";
      }
    }
    $sql .= "ORDER BY {$display_orderby} ";
  }
  elseif ($cid > 0) {
    $sql .= "WHERE file.cid={$cid} ORDER BY {$display_orderby}, file.fid DESC ";
    if ($filedepot->activeview == 'getmorefolderdata') {
      if (isset($_POST['pass2']) and $_POST['pass2'] == 1) {
        $limit = 100000;
        $out_limit_start = $filedepot->recordCountPass1;
      }
      else {
        $recordoffset = $filedepot->recordCountPass2 + $filedepot->recordCountPass1;
        $filedepot->folder_filenumoffset = $recordoffset;
        $limit = 100000;
        $out_limit_start = $recordoffset;
      }
    }
    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;

        // Add 1 to allow $file_query->fetchAssoc() to know there are more records - used in nexdocsrv_generateFileListing()
        $folder_filelimit = $filedepot->recordCountPass2 + 1;
        $limit = $folder_filelimit;
        $out_limit_start = $filedepot->recordCountPass1;
      }
      else {
        $limit = $filedepot->recordCountPass1;
        $out_limit_start = 0;
      }
    }
  }
  else {

    // Default view - latest files
    if (!empty($filedepot->allowableViewFoldersSql)) {
      if ($filedepot->ogmode_enabled) {
        if (!empty($filedepot->allowableGroupViewFoldersSql)) {
          $sql .= "WHERE file.cid in ({$filedepot->allowableGroupViewFoldersSql}) ";
        }
        else {
          $sql .= "WHERE file.cid in ({$filedepot->allowableViewFoldersSql}) ";
        }
      }
      elseif (!user_access('administer filedepot', $user)) {
        if (empty($filedepot->allowableViewFoldersSql)) {
          $sql .= "WHERE file.cid is NULL ";
        }
        else {
          $sql .= "WHERE file.cid in ({$filedepot->allowableViewFoldersSql}) ";
        }
      }
    }
    if ($filedepot->activeview == 'latestfiles') {
      $sql .= "ORDER BY file.date DESC ";
      $limit = $filedepot->maxDefaultRecords;
    }
    else {
      $sql .= "ORDER BY {$display_orderby} ";
      $limit = $filedepot->maxDefaultRecords;
    }
  }
  $out_limit_end = $limit;
  return $sql;
}