<?php
/** \file include\db_file.php
 *  \brief DI webinterface database functions
 *  \author Rob Schalken, Core|Vision
 *  \version $Revision: 26247 $
 *  \date $Date: 2016-02-29 10:40:22 +0100 (Mon, 29 Feb 2016) $
 *
 * This file contains the file database functions. This file is always included.
 */

/**
 *  Private function: fetch files using the provided query
 *
 *  Inputs:
 *  - query                 Database query to fetch the requested documents.
 *  - doc_table:            Document table
 *  - read_doc:             Read the document data (if FALSE, only the info about the document is returned)
 *
 *  Return:                 Multidimensional array containing all file information
 */
function db_fetch_files_by_query($query, $doc_table, $read_doc) {
  global $_RELEASE;

  // Initial return value
  $result = array();

  // download document from a directory; currently not implemented for all document tables
  $upload_dir = realpath($_RELEASE[((is_ReleaseCandidate()) ? 'rc' : 'release')]['upload'] . strtolower($doc_table));
  // upload directory should exist, so the error is never triggered in a release environment
  if( !$upload_dir ) {
    trigger_error("Cannot open document: path \"" . $upload_dir . "\" is invalid, expanded from \"" . $_RELEASE[((is_ReleaseCandidate()) ? 'rc' : 'release')]['upload'] . strtolower($doc_table) . "\"", E_USER_ERROR);
    return FALSE;
  }

  // get documents and info from the database and build the resulting array
  $row_docs = db_fetch_data($query);
  if( !empty($row_docs) ) {
    $doc_types = array(
      'zkl_documenten'                     => 'zkl',
      'device_documenten'                  => 'device',
      'project_documenten'                 => array(
        ''      => 'project',
        'RO'    => 'user upload',
        'RO-WO' => 'work order'
      ),
      'klant_documenten'                   => 'customer',
      'algemene_documenten'                => 'general',
      'syscomp_documenten'                 => 'syscomp',
      'changelog_versie_documenten'        => 'changelog_versie',
      'changelog_versie_change_documenten' => 'changelog_versie_change',
      'werkopdracht_documenten'            => 'werkopdracht',
      'zkl_werkorder_documenten'           => 'zkl_werkorder'
    );

    foreach( $row_docs as $document ) {
      switch( $doc_table ) {
        case "project_documenten":
        case "klant_documenten":
        case "syscomp_documenten":
        case "changelog_versie_documenten":
        case "changelog_versie_change_documenten":
        case "werkopdracht_documenten":
        case "zkl_werkorder_documenten":
          $document['localsrc'] = $upload_dir . "/" . $document['id'];
          break;
        default:
          $document['localsrc'] = FALSE;
          break;
      }

      // check for file on filesystem (but only read it when the document data is requested)
      if( $read_doc ) {
        if( $document['localsrc'] && file_exists($document['localsrc']) ) {
          // Open the file
          $handle = fopen($document['localsrc'] ,"r");

          // read document data from file
          $file_data = fread($handle, filesize($document['localsrc']));

          // close handle
          fclose($handle);

          // save file data in the 'document'
          $document['document'] = $file_data;
        }
      }
      // else: document['document'] contains the document data from the database (or not, if 'read_doc' is FALSE)

      // set document type
      if( !$document['doc_type'] ) {
        if( is_array($doc_types[$doc_table]) ) {
          // the 'omschrijving' in 'project_documenten' is abused to set the document type;
          //  this should become a separate database field in the future
          $document['doc_type'] = $doc_types[$doc_table][$document['omschrijving']];
        }
        else $document['doc_type'] = $doc_types[$doc_table];
      }
      // else: part of the database data (future addition to the 'project_documenten' table)

      // save result
      $result[] = $document;
    }
  }

  return $result;
}

/**
 *  Fetch file database id
 *
 *  Inputs:
 *  - file:                 File id
 *  - doc_table:            Document table
 *
 *  Return:                 Array containing user information
 */
function db_fetch_file($file_id, $doc_table, $read_doc = FALSE) {
  // fetch document info
  switch($doc_table) {
    case "algemene_documenten":
      $filter = ($read_doc) ? "*" : "filename,titel,omschrijving,categorie,id,mimetype";
      $query  = "SELECT " . $filter . " FROM " . $doc_table . " WHERE id='" . $file_id . "'";
      break;
    case "zkl_documenten":
      $filter = ($read_doc) ? "*" : "zkl,doc_type,filename,omschrijving,id,public,mimetype";
      $query  = "SELECT " . $filter . " FROM " . $doc_table . " WHERE id='" . $file_id . "'";
      break;
    case "device_documenten":
      $filter = ($read_doc) ? "*" : "device,filename,omschrijving,id,mimetype";
      $query  = "SELECT " . $filter . " FROM " . $doc_table . " WHERE id='" . $file_id . "'";
      break;
    case "project_documenten":
      $filter = ($read_doc) ? "*" : "doc_type,filename,omschrijving,id,mimetype,latitude,longitude,heading";
      $query  = "SELECT " . $filter . " FROM " . $doc_table . " WHERE id='" . $file_id . "'";
      break;
    case "klant_documenten":
      $filter = ($read_doc) ? "*" : "klant,filename,titel,omschrijving,categorie,id,mimetype";
      $query  = "SELECT " . $filter . " FROM " . $doc_table . " WHERE id='" . $file_id . "'";
      break;
    case "syscomp_documenten":
    case "changelog_versie_documenten":
    case "changelog_versie_change_documenten":
    case "werkopdracht_documenten":
    case "zkl_werkorder_documenten":
      $query = "SELECT * FROM " . $doc_table . " WHERE id=" . $file_id;
      break;
    default:
      trigger_error("Bad function call: " . $doc_table . " is not valid", E_USER_ERROR);
      return FALSE;
  }

  // get document and info from the database and build the resulting array
  // NB: only one document expected, so only the first element in the resulting array
  //  is returned
  $result = db_fetch_files_by_query($query, $doc_table, $read_doc);
  if( $result ) return $result[0];
  else return FALSE;
}

/**
 *  Fetch file data which match lance id
 *
 *  Inputs:
 *  - id:                   Lance/project/zkl/etc db id
 *  - doc_table:            Document table
 *  - read_doc:             Read the document data (if FALSE, only the info about the document is returned)
 *  - options / sort:       Possibility to parse some extra options / old: Sort the documents by date, descending if "DESC" (the default), ascending ("ASC")
 *                          or not at all (empty string, FALSE, etc)
  *
 *  Return:                 Multidimensional array containing all file information
 */
function db_fetch_files($id, $doc_table, $read_doc = FALSE, $options = NULL) {
  // pre-set link field to the data table
  $id_field = substr($doc_table, 0, -strlen("_documenten"));

  // Default value
  $order = "DESC";
  $order_by = "datum";

  // Stay backwards compatible
  if (!is_null($options)) {
    // Not an array with options but old sort parameter
    if (!is_array($options)) {
      $order = $options;
    }
    else {
      // Order
      if (isset($options['order'])) {
        $order = $options['order'];
      }
      // Order by
      if (isset($options['order_by'])) {
        $order_by = $options['order_by'];
      }
    }
  }

  // fetch document info
  switch($doc_table) {
    case "algemene_documenten":
      $filter = ($read_doc) ? "*" : "gebruiker,filename,titel,omschrijving,categorie,id,mimetype";
      $query  = "SELECT " . $filter . " FROM " . $doc_table;
      break;
    case "zkl_documenten":
      $filter = ($read_doc) ? "*" : "doc_type,filename,omschrijving,id,public,mimetype";
      $query  = "SELECT " . $filter . " FROM " . $doc_table . " WHERE zkl='" . $id . "'";
      break;
    case "device_documenten":
      $filter = ($read_doc) ? "*" : "filename,omschrijving,id,mimetype";
      $query  = "SELECT " . $filter . " FROM " . $doc_table . " WHERE device='" . $id . "'";
      break;
    case "project_documenten":
      $filter = ($read_doc) ? "*" : "level,doc_type,filename,omschrijving,id,mimetype,gebruiker,datum,latitude,longitude,heading";
      $query  = "SELECT " . $filter . " FROM " . $doc_table . " WHERE project='" . $id . "'";

      if (!is_null($options)) {
        foreach($options as $key => $option) {
          switch($key) {
            // Select specific doctype
            case 'doctype':
              $query .= " AND doc_type = '" . $option . "'";
              break;
            // Do nothing
            default:
              break;
          }
        }
      }
      break;
    case "klant_documenten":
      $filter = ($read_doc) ? "*" : "klant,gebruiker,filename,titel,omschrijving,categorie,id,mimetype";
      $query  = "SELECT " . $filter . " FROM " . $doc_table . " WHERE klant=" . $id;
      break;
    case "changelog_versie_documenten":
    case "changelog_versie_change_documenten":
      // link field is without the prefix "changelog_"
      $id_field = substr($id_field, 10);
    case "syscomp_documenten":
    case "werkopdracht_documenten":
    case "zkl_werkorder_documenten":
      $query = "SELECT * FROM " . $doc_table . " WHERE " . $id_field . "=" . $id;
      break;
    default:
      trigger_error("Bad function call: " . $doc_table . " is not valid", E_USER_ERROR);
      return FALSE;
  }

  // sort
  if( $order ) $query .= " ORDER BY " . $order_by . " " . $order;

  // get documents and info from the database and build the resulting array
  return db_fetch_files_by_query($query, $doc_table, $read_doc);
}

/**
 *  Search files which match some parameters (only used for general docs!)
 *
 *  Inputs:
 *  - doc_table             "algemene_documenten" or "klant_documenten"
 *  - cust_id               database id for the customer (for "klant_doucmenten" only)
 *  - category              document category
 *  - title                 document title
 *  - description           document description
 *  - date_begin            begin of period
 *  - time_begin            begin of period
 *  - date_end              end of period
 *  - time_end              end of period
 *
 *  Return:                 Multidimensional array containing all document information
 */
function db_search_files($doc_table, $cust_id, $cat, $title, $descr, $date_begin, $time_begin, $date_end, $time_end, $read_doc = FALSE) {
  // fetch document info
  switch($doc_table) {
    case "algemene_documenten":
      $filter = ($read_doc) ? "*" : "gebruiker,filename,titel,omschrijving,categorie,id,mimetype";
      break;
    case "klant_documenten":
      $filter = ($read_doc) ? "*" : "klant,gebruiker,filename,titel,omschrijving,categorie,id,mimetype";
      break;
    default:
      trigger_error("Bad function call: " . $doc_table . " is not valid or not supported", E_USER_ERROR);
      return FALSE;
  }

  // build query to search for the requested documents
  $query  = "SELECT " . $filter . " FROM " . $doc_table . " WHERE ";
  if( $doc_table == "klant_documenten" && is_numeric($cust_id) ) {
    $query .= "klant=" . $cust_id . " AND ";
  }
  $query .= "categorie    like '%" . $cat   . "%' AND ";
  $query .= "titel        like '%" . $title . "%' AND ";
  $query .= "omschrijving like '%" . $descr . "%'";
  if ((strlen($date_begin)) && (strlen($date_end))) {
    $query .= "AND datum BETWEEN '" . $date_begin . " " . $time_begin . "' AND '" . $date_end . " " . $time_end . "'";
  }
  else if (strlen($date_begin)) {
    $query .= " AND datum >= '" . $date_begin . " " . $time_begin . "'";
  }
  else if (strlen($date_end)) {
    $query .= " AND datum <= '" . $date_end . " " . $time_end . "'";
  }

  // Order by titel
  $query .= " ORDER BY titel";

  // get documents and info from the database and build the resulting array
  $documents = db_fetch_files_by_query($query, $doc_table, $read_doc);

  // No customers selected?
  if( $doc_table == "klant_documenten" && !is_numeric($cust_id) ) {
    if (is_array($documents)) {
      // Initial array
      $found_documents = array();

      // Get all customers down the pyramid
      $customers = db_search_customers();

      for($i=0; $i<sizeof($documents); $i++) {
        // Initial value
        $found = FALSE;

        // Valid customer?
        for($j=0; (($j<sizeof($customers))  && (!$found)); $j++) {
          $found = ($customers[$j]['id'] === $documents[$i]['klant']);
        }

        // Customer found?
        if ($found) {
          array_push($found_documents, $documents[$i]);
        }
      }

      // Return value
      $documents = $found_documents;
    }
  }

  return $documents;
}



/**
 *  Private function: check for duplicate filenames
 *
 *  Input:
 *  - file_array:           Array containing all info about the new file
 *  - file_obj: 						Item in the array to check
 *  - doc_table             Document table
 *  - documents             Exisiting documents to check
 *
 *  Output:
 *  - Possibly modified filename
 */
function db_check_duplicate_filenames($file_array, $file_obj = NULL, $doc_table, $documents)
{
  $filename_addition = "";
  $filename_counter  = 0;

  // get extension and base name
  if (!is_null($file_obj)) {
    $filename  = $file_array[$file_obj];
  }
  else {
     $filename = $file_array['name'];
  }
  $extension = GetExtension($filename);
  $filename  = substr($filename, 0, strlen($filename) - strlen($extension));

  if (is_array($documents)) {
    $valid = FALSE;
    while( !$valid ) {
      $valid = TRUE;

      foreach($documents as $document) {
        // correct type?
        if(
          ($doc_table == 'project_documenten' && $document['doc_type'] == $file_array['doc_type']) ||
          ($doc_table == 'zkl_werkorder_documenten' && $document['doc_type'] == $file_array['doc_type']) ||
          !in_array($doc_table, array('project_documenten','zkl_werkorder_documenten'))
        ) {
          // Same filename?
          if( $document['filename'] == ($filename . $filename_addition . $extension) ) {
            // add an incrementing number to the filename
            $filename_addition = " (" . ++$filename_counter . ")";
            $valid = FALSE;
            break;
          }
        }
      }
    }
  }

  // adjust filename (and reattach the extension)
  return $filename . $filename_addition . $extension;
}

/**
 *  Store new file
 *
 *  Inputs:
 *  - file_array:           Array containing all customer info
 *  - doc_table:            Documenten table
 *
 *  Return:                 FALSE on error of file identifier on success
 */
function db_store_file($file_array, $doc_table) {
  global $_RELEASE;
  global $_PAGE_INFO;

  // Initial return value
  $result = FALSE;

  // upload document to a directory; currently not implemented for all document tables
  $save_file_in_upload_dir = FALSE;
  $upload_dir = realpath($_RELEASE[((is_ReleaseCandidate()) ? 'rc' : 'release')]['upload'] . strtolower($doc_table));
  // upload directory should exist, so the error is never triggered in a release environment
  if( !$upload_dir ) {
    trigger_error("Cannot save document: path \"" . $upload_dir . "\" is invalid, expanded from \"" . $_RELEASE[((is_ReleaseCandidate()) ? 'rc' : 'release')]['upload'] . strtolower($doc_table) . "\"", E_USER_ERROR);
    return FALSE;
  }

  // pre-set link field to the data table
  $id_field = substr($doc_table, 0, -strlen("_documenten"));

  // Query storing new file
  switch($doc_table) {
    case "algemene_documenten":
      $query  = "INSERT INTO algemene_documenten (categorie, titel, omschrijving, datum, filename, mimetype, document) VALUES (";
      $query .= "'" . specialchars($file_array['categorie'])            . "',";
      $query .= "'" . specialchars($file_array['titel'])                . "',";
      $query .= "'" . specialchars($file_array['omschrijving'])         . "',";
      $date = (!isset($file_array['datum'])) ? date('Y-m-d H:i:s') : $file_array['datum'];
      $query .= "'" . ($date)                              . "',";
      $query .= "'" . addslashes($file_array['filename'])            . "',";
      $query .= "'" . ($file_array['mimetype'])            . "',";
			// document data for 'algemene_documenten' is still in a 'longblob' in the database
      $query .= "'" . addslashes($file_array['document'])            . "')";
      break;
    case "zkl_documenten":
      $query  = "INSERT INTO zkl_documenten (zkl,doc_type,omschrijving,datum,public,mimetype,filename,document) VALUES (";
      $query .= "'" . ($file_array['zkl'])                 . "',";
      $query .= "'" . ($file_array['doc_type'])            . "',";
      $query .= "'" . specialchars($file_array['omschrijving'])         . "',";
      $date = (!isset($file_array['datum'])) ? date('Y-m-d H:i:s') : $file_array['datum'];
      $query .= "'" . ($date)                              . "',";
      $query .= "'" . ($file_array['public'])              . "',";
      $query .= "'" . ($file_array['mimetype'])            . "',";
      $query .= "'" . addslashes($file_array['filename'])            . "',";
			// document data for 'zkl_documenten' is still in a 'longblob' in the database
      $query .= "'" . addslashes($file_array['document'])            . "')";
      break;
    case "device_documenten":
      $query  = "INSERT INTO device_documenten (device,omschrijving,datum,filename,mimetype,document) VALUES (";
      $query .= "'" . ($file_array['zkl'])                 . "',";
      $query .= "'" . specialchars($file_array['omschrijving'])         . "',";
      $date = (!isset($file_array['datum'])) ? date('Y-m-d H:i:s') : $file_array['datum'];
      $query .= "'" . ($date)                              . "',";
      $query .= "'" . addslashes($file_array['filename'])            . "',";
      $query .= "'" . ($file_array['mimetype'])            . "',";
			// document data for 'device_documenten' is still in a 'longblob' in the database
      $query .= "'" . addslashes($file_array['document'])            . "')";
      break;
    case "klant_documenten":
      // 'klant_documenten' are always saved in the upload directory
      $save_file_in_upload_dir = TRUE;

      // adjust filename to accomodate duplicates
      $docs = db_fetch_files($_SESSION[$_PAGE_INFO['id']]['login']['customer']['id'], $doc_table);
      $file_array['filename'] = db_check_duplicate_filenames($file_array, 'filename', $doc_table, $docs);
      unset($docs);   // no longer needed

      $query  = "INSERT INTO klant_documenten (gebruiker,klant,categorie,titel,omschrijving,datum,filename,mimetype) VALUES (";
      $query .= $_SESSION[$_PAGE_INFO['id']]['login']['user']['id']                 . ",";
      $query .= $_SESSION[$_PAGE_INFO['id']]['login']['customer']['id']             . ",";
      $query .= "'" . specialchars($file_array['categorie'])            . "',";
      $query .= "'" . specialchars($file_array['titel'])                . "',";
      $query .= "'" . specialchars($file_array['omschrijving'])         . "',";
      $date = (!isset($file_array['datum'])) ? date('Y-m-d H:i:s') : $file_array['datum'];
      $query .= "'" . ($date)                                                      . "',";
      $query .= "'" . addslashes($file_array['filename'])                          . "',";
      $query .= "'" . ($file_array['mimetype'])                                    . "')";
      break;
    case "project_documenten":
      // 'project_documenten' are always saved in the upload directory
      $save_file_in_upload_dir = TRUE;

      // adjust filename to accomodate duplicates
      $docs = db_fetch_files($file_array['project'], $doc_table);
      $file_array['filename'] = db_check_duplicate_filenames($file_array, 'filename', $doc_table, $docs);
      unset($docs);

      $query = "INSERT INTO project_documenten (level,doc_type,project,omschrijving,gebruiker,datum,latitude,longitude,heading,filename,mimetype) VALUES (";
      $query .= "'" . ($file_array['level'])               . "',";
      $query .= "'" . ($file_array['doc_type'])            . "',";
      $query .= "'" . ($file_array['project'])             . "',";
      $query .= "'" . specialchars($file_array['omschrijving']) . "',";
      $query .= $_SESSION[$_PAGE_INFO['id']]['login']['user']['id'] . ",";
      $date = (!isset($file_array['datum'])) ? date('Y-m-d H:i:s') : $file_array['datum'];
      $query .= "'" . ($date)                              . "',";
      $query .= (!isset($file_array['latitude']))  ? "NULL," : "'" . specialchars($file_array['latitude'])   . "',";
      $query .= (!isset($file_array['longitude'])) ? "NULL," : "'" . specialchars($file_array['longitude']) . "',";
      $query .= (!isset($file_array['heading']))   ? "NULL," : "'" . specialchars($file_array['heading'])    . "',";
      $query .= "'" . addslashes($file_array['filename'])            . "',";
      $query .= "'" . ($file_array['mimetype']) . "')";
      break;
    case "changelog_versie_documenten":
    case "changelog_versie_change_documenten":
      // link field is without the prefix "changelog_"
      $id_field = substr($id_field, 10);
    case "syscomp_documenten":
    case "werkopdracht_documenten":
    case "zkl_werkorder_documenten":
      // always saved in the upload directory
      $save_file_in_upload_dir = TRUE;

      // adjust filename to accomodate duplicates
      $docs = db_fetch_files($file_array[$id_field], $doc_table);
      $file_array['filename'] = db_check_duplicate_filenames($file_array, 'filename', $doc_table, $docs);
      unset($docs);   // no longer needed

      $query  = "INSERT INTO " . $doc_table . " (gebruiker," . $id_field;
      $query .=   ",datum,filename,mimetype)";
      $query .= " VALUES (";
      $query .=   $_SESSION[$_PAGE_INFO['id']]['login']['user']['id']                 . ",";
      $query .=   $file_array[$id_field]                                              . ",";
      $date = (!isset($file_array['datum'])) ? date('Y-m-d H:i:s') : $file_array['datum'];
      $query .=   "'" . ($date)                                                      . "',";
      $query .=   "'" . addslashes($file_array['filename'])                          . "',";
      $query .=   "'" . ($file_array['mimetype'])                                    . "'";
      $query .= ")";
      break;
    default:
      trigger_error("Bad function call: " . $doc_table . " is not valid", E_USER_ERROR);
      return FALSE;
  }

  if (db_store_data($query)) {
    // successfully stored in the database; write the file in the upload directory
    // NB: currently not fully implemented for all document tables

    // save off the identifier for the new document
    $file_array['id'] = db_fetch_last_id();

    if( $save_file_in_upload_dir ) {
      // Open/create handle
      $handle = fopen($upload_dir . "/" . $file_array['id'], "w");

		if ($handle !== false) {
			// Write document data to file
			$writeResult = fwrite($handle, $file_array['document']);

			if ($writeResult === false || $writeResult === 0) {
				error_log("Failed to write file contents of" . $file_array['filename'] . " (with a string length of ".
					strlen($file_array['document']) .") to " . $upload_dir . "/" . $file_array['id']);
			}

			// Close handle
			fclose($handle);
		} else {
			error_log("Failed to open file: " . $upload_dir . "/" . $file_array['id']);
		}
    }

    // don't store the file data in the user log (zap it before calling 'serialize')
    unset($file_array['document']);

    switch($doc_table) {
      case "zkl_documenten":
        // Log user-lance action
        db_log_user_lance($file_array['zkl'], "menu:lansen:onderhoudsrapporten:nieuw", serialize($file_array));
        break;
      case "device_documenten":
        // Log user-lance action
        db_log_user_lance($file_array['zkl'], "menu:lansen:materieel_documentatie:nieuw", serialize($file_array));
        break;
      case "algemene_documenten":
        // Log user action
        db_log_user("menu:documentatie:nieuw", serialize($file_array));
        break;
      case "klant_documenten":
        // Log user action
        db_log_user("menu:documentatie:nieuw", serialize($file_array));
        break;
      case "project_documenten":
        // Log user-project action
        db_log_user_project($file_array['project'], "menu:projecten:project_documentatie:nieuw", serialize($file_array));
        break;
      case "syscomp_documenten":
        // Log user action
        db_log_user("menu:systeemonderhoud:changelog:systeemcomponent:wijzigen", serialize($file_array));
        break;
      case "changelog_versie_documenten":
        // Log user action
        db_log_user("menu:systeemonderhoud:changelog:versie:wijzigen", serialize($file_array));
        break;
      case "changelog_versie_change_documenten":
        // Log user action
        db_log_user("menu:systeemonderhoud:changelog:changelog:wijzigen", serialize($file_array));
        break;
      case "werkopdracht_documenten":
        // Log user action
        db_log_user("menu:systeemonderhoud:werkorder_template:wijzigen", serialize($file_array));
        break;
      case "zkl_werkorder_documenten":
        // Log user-lance action
        switch( $file_array['doc_type'] ) {
          case 'werkorder':
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:reparatie", serialize($file_array));
            break;
          case 'offerte':
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:opname", serialize($file_array));
            break;
          case 'factuur':
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:facturatie", serialize($file_array));
            break;
          default:
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:wijzigen", serialize($file_array));
            break;
        }
        break;
      default:
        break;
    }

    // Result OK
    $result = $file_array['id'];
  }

  return $result;
}


/**
 *  Update exisiting file
 *
 *  Inputs:
 *  - file_array:           Array containing all customer info
 *  - doc_table:            Documenten table
 *
 *  Return:                 FALSE on error of file identifier on success
 */
function db_update_file($file_array, $doc_table) {
  // Initial return value
  $result = FALSE;

  // Query updating document
  switch($doc_table) {
    case "algemene_documenten":
      $query  = "UPDATE algemene_documenten SET ";
      $query .= "categorie='"     . specialchars($file_array['categorie'])    . "',";
      $query .= "omschrijving='"  . specialchars($file_array['omschrijving']) . "',";
      $query .= "titel='"         . specialchars($file_array['titel'])        . "' ";
      $query .= "WHERE id='"      .              $file_array['id']            . "'";
      break;
    case "zkl_documenten":
      $query  = "UPDATE zkl_documenten SET ";
      $query .= "omschrijving='"  . specialchars($file_array['omschrijving']) . "',";
      $query .= "public='"        . ($file_array['public'])                   . "' ";
      $query .= "WHERE id='"      .              $file_array['id']            . "'";
      break;
    case "device_documenten":
      $query  = "UPDATE device_documenten SET ";
      $query .= "omschrijving='"  . specialchars($file_array['omschrijving']) . "' ";
      $query .= "WHERE id='"      .              $file_array['id']            . "'";
      break;
    case "klant_documenten":
      $query  = "UPDATE klant_documenten SET ";
      $query .= "categorie='"     . specialchars($file_array['categorie'])    . "',";
      $query .= "omschrijving='"  . specialchars($file_array['omschrijving']) . "',";
      $query .= "titel='"         . specialchars($file_array['titel'])        . "' ";
      $query .= "WHERE id='"      .              $file_array['id']            . "'";
      break;
    case "syscomp_documenten":
    case "changelog_versie_documenten":
    case "changelog_versie_change_documenten":
    case "werkopdracht_documenten":
    case "zkl_werkorder_documenten":
      // (nothing to do / not implemented)
      break;
    case "project_documenten":
      $query  = "UPDATE project_documenten SET ";
      $query .= "level='"         . specialchars($file_array['level'])          . "' ";
      $query .= "WHERE id='"      .              $file_array['id']              . "'";
      break;
    default:
      trigger_error("Bad function call: " . $doc_table . " is not valid", E_USER_ERROR);
      return FALSE;
  }

  if (db_store_data($query)) {
    // Result OK
    $result = $file_array['id'];
  }

  if ($result) {
    // Get doc and zkl data
    $doc_data = db_fetch_file($file_array['id'], $doc_table);

    unset($file_array['document']);
    switch($doc_table) {
      case "algemene_documenten":
        db_log_user("menu:documentatie:wijzigen", serialize($file_array));
        break;
      case "zkl_documenten":
        // Log user-lance action
        db_log_user_lance($doc_data['zkl'], "menu:lansen:onderhoudsrapporten:wijzigen", serialize($doc_data));
        break;
      case "device_documenten":
        // Log user-lance action
        db_log_user_lance($doc_data['device'], "menu:lansen:materieel_documentatie:wijzigen", serialize($doc_data));
        break;
      case "klant_documenten":
        // Log user-lance action
        db_log_user("menu:documentatie:wijzigen", serialize($file_array));
        break;
      case "syscomp_documenten":
        // Log user action
        db_log_user("menu:systeemonderhoud:changelog:systeemcomponent:wijzigen", serialize($file_array));
        break;
      case "changelog_versie_documenten":
        // Log user action
        db_log_user("menu:systeemonderhoud:changelog:versie:wijzigen", serialize($file_array));
        break;
      case "changelog_versie_change_documenten":
        // Log user action
        db_log_user("menu:systeemonderhoud:changelog:changelog:wijzigen", serialize($file_array));
        break;
      case "werkopdracht_documenten":
        // Log user action
        db_log_user("menu:systeemonderhoud:werkorder_template:wijzigen", serialize($file_array));
        break;
      case "zkl_werkorder_documenten":
        // Log user-lance action
        switch( $file_array['doc_type'] ) {
          case 'werkorder':
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:reparatie", serialize($file_array));
            break;
          case 'offerte':
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:opname", serialize($file_array));
            break;
          case 'factuur':
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:facturatie", serialize($file_array));
            break;
          default:
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:wijzigen", serialize($file_array));
            break;
        }
        break;
      case "project_documenten":
        // Log user-project action
        db_log_user_project($file_array['project'], "menu:projecten:project_documentatie:wijzigen", serialize($file_array));
        break;
      default:
        break;
    }
  }

  return $result;
}


/**
 *  Delete file
 *
 *  Inputs:
 *  - file_id:              File database id
 *  - doc_table:            Documenten table
 *  - lance_id:             Equipment id for logging
 *  - project_id:           Project id for logging
 *
 *  Return:                 Boolean (success or failure)
 */
function db_delete_file($file_id, $doc_table, $lance_id = "", $project_id = "") {
  global $_RELEASE;

  // Initial return value
  $result = FALSE;

  // upload document to a directory; currently not implemented for all document tables
  $upload_dir = realpath($_RELEASE[((is_ReleaseCandidate()) ? 'rc' : 'release')]['upload'] . strtolower($doc_table));
  // upload directory should exist, so the error is never triggered in a release environment
  if( !$upload_dir ) {
    trigger_error("Cannot delete document: path \"" . $upload_dir . "\" is invalid, expanded from \"" . $_RELEASE[((is_ReleaseCandidate()) ? 'rc' : 'release')]['upload'] . strtolower($doc_table) . "\"", E_USER_ERROR);
    return FALSE;
  }

  // Get original data
  $orig_data = db_fetch_file($file_id, $doc_table, FALSE);

  // Delete documents
  $query  = "DELETE FROM " . $doc_table . " WHERE id='" . $file_id . "'";
  if( db_store_data($query) ) {
    // by default assume that the document table does not use the upload directory
    $file = FALSE;

    switch($doc_table) {
      case "zkl_documenten":
        // Log user-lance action
        db_log_user_lance($lance_id, "menu:lansen:onderhoudsrapporten:verwijderen", serialize($orig_data));
        break;
      case "device_documenten":
        // Log user-lance action
        db_log_user_lance($lance_id, "menu:lansen:materieel_documentatie:verwijderen", serialize($orig_data));
        break;
      case "algemene_documenten":
        // Log user action
        db_log_user("menu:documentatie:verwijderen", $file_id);
        break;
      case "klant_documenten":
        // delete file from filessystem
        $file = $upload_dir . "/" . $file_id;
        // Log user action
        db_log_user("menu:documentatie:verwijderen", $file_id);
        break;
      case "project_documenten":
        // delete file from filessystem
        $file = $upload_dir . "/" . $file_id;
        // Log user-project action
        db_log_user_project($project_id, "menu:projecten:project_documentatie:verwijderen", serialize($orig_data));
        break;
      case "syscomp_documenten":
        // delete file from filessystem
        $file = $upload_dir . "/" . $file_id;
        // Log user action
        db_log_user("menu:systeemonderhoud:systeemcomponent:wijzigen", serialize($file_array));
        break;
      case "changelog_versie_documenten":
        // delete file from filessystem
        $file = $upload_dir . "/" . $file_id;
        // Log user action
        db_log_user("menu:systeemonderhoud:changelog:versie:wijzigen", serialize($file_array));
        break;
      case "changelog_versie_change_documenten":
        // delete file from filessystem
        $file = $upload_dir . "/" . $file_id;
        // Log user action
        db_log_user("menu:systeemonderhoud:changelog:changelog:wijzigen", serialize($file_array));
        break;
      case "werkopdracht_documenten":
        // delete file from filessystem
        $file = $upload_dir . "/" . $file_id;
        // Log user action
        db_log_user("menu:systeemonderhoud:werkorder_template:wijzigen", serialize($file_array));
        break;
      case "zkl_werkorder_documenten":
        // delete file from filessystem
        $file = $upload_dir . "/" . $file_id;
        // Log user-lance action
        switch( $file_array['doc_type'] ) {
          case 'werkorder':
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:reparatie", serialize($file_array));
            break;
          case 'offerte':
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:opname", serialize($file_array));
            break;
          case 'factuur':
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:facturatie", serialize($file_array));
            break;
          default:
            db_log_user_lance($file_array['zkl'], "menu:lansen:s&o:wijzigen", serialize($file_array));
            break;
        }
        break;
      default:
        trigger_error("Bad function call: " . $doc_table . " is not valid", E_USER_ERROR);
        return FALSE;
    }

    // delete the file itself from the upload directory
    if( $file && file_exists($file) ) unlink($file);

    $result = TRUE;
  }

  return $result;
}


/**
 *  Fetch user document comment
 *
 *  Inputs:
 *  - comment_array:        Array containing all comment info
 *  - order:                1 = recent comment first
 *
 *  Return:                 TRUE (OK)/ FALSE (Error)
 */
function db_fetch_data_user_comment($comment_array, $order = 0)
{
  $order = ($order == 1) ? 'ORDER BY id DESC' : '';

  // Query storing new user comment
  $query  = "SELECT * FROM project_documenten_comment WHERE document='" . $comment_array['document'] . "' ". $order . ";";

  // excute query
  $row_docs = db_fetch_data($query);

  // Parse into result
  if (!empty($row_docs)) {
    $result = array();

    for ($i = 0; $i < sizeof($row_docs); $i++) {
      // Parse result
      $result[$i] = $row_docs[$i];
    }

    return $result;
  }
  else return FALSE;
}

/**
 *  Store user document comment
 *
 *  Inputs:
 *  - comment_array:         Array containing all comment info
 *
 *  Return:                 TRUE (OK)/ FALSE (Error)
 */
function db_store_data_user_comment($comment_array){
  global $_PAGE_INFO;

  // Query storing new user comment
  $query  = "INSERT INTO project_documenten_comment (document,gebruiker,datum,latitude,longitude,tekst) VALUES (";
  $query .= "'" . addslashes($comment_array['document'])            . "',";
  $query .= $_SESSION[$_PAGE_INFO['id']]['login']['user']['id']     . ",";
  $query .= "'" . date('Y-m-d H:i:s')                                . "',";
  $query .= (is_float($comment_array['latitude'])  ? $comment_array['latitude']  : "NULL") . ",";
  $query .= (is_float($comment_array['longitude']) ? $comment_array['longitude'] : "NULL") . ",";
  $query .= "'" . specialchars($comment_array['tekst']) . "')";

  return db_store_data($query);
}

/**
 *  Update documentation
 *
 *  Inputs:
 *  - link_id:              Lance, project, etc (database identifier)
 *  - doc_table:            Documenten table
 *  - new_documents:        Current documents, with new and without deleted documents
 *
 *  Returns:                TRUE on success, FALSE on failure
 */
function db_update_files($link_id, $doc_table, $new_documents)
{
  // Assume success; will be set to FALSE if something goes wrong
  $result = TRUE;

  // pre-set link field to the data table
  $id_field = substr($doc_table, 0, -strlen("_documenten"));
  switch($doc_table) {
    case "changelog_versie_documenten":
    case "changelog_versie_change_documenten":
      // link field is without the prefix "changelog_"
      $id_field = substr($id_field, 10);
      break;
  }


  // Fetch the existing documents
  $org_documents = db_fetch_files($link_id, $doc_table);

  // Deleted documentation?
  if( is_array($org_documents) && is_array($new_documents) ) {
    foreach( $org_documents as $org_doc ) {
      $found = FALSE;
      foreach( $new_documents as $new_doc )
        if( isset($new_doc['id']) && $new_doc['id'] == $org_doc['id'] )
          $found = TRUE;

      // Remove?
      if( !$found ) db_delete_file($org_doc['id'], $doc_table);
    }
  }

  // New documentation
  if( is_array($new_documents) ) {
    foreach( $new_documents as $new_doc ) {
      if( isset($new_doc['tmp_name']) && file_exists($new_doc['tmp_name']) ) {
        // New document
        // Read temporary file
        $fp = fopen($new_doc['tmp_name'], 'r');
        $new_doc['document'] = fread($fp, filesize($new_doc['tmp_name']));
        fclose($fp);

        // Add version identifier
        $new_doc[$id_field] = $link_id;

        // Store new document
        if( db_store_file($new_doc, $doc_table) ) {
          // Remove temporary project documentation
          unlink($new_doc['tmp_name']);
        }
        else $result = FALSE;
      }
      else if( isset($new_doc['org_id']) ) {
        // Duplicated document
        // Read the original document
        $org_doc = db_fetch_file($new_doc['org_id'], $doc_table, TRUE);
        $new_doc['document'] = $org_doc['document'];

        // Add identifier
        $new_doc[$id_field] = $link_id;

        // Storeas  new document
        if( !db_store_file($new_doc, $doc_table) ) $result = FALSE;
      }
      // else: existing and unchanged documents need no further handling
    }
  }

  return $result;
}

/**
 *  Duplicate a document
 */
function db_duplicate_file($doc)
{
  if( isset($doc['tmp_name']) && file_exists($doc['tmp_name']) ) {
    $tmp = session_save_path() . "/." . $_PAGE_INFO['id'] . "_" . basename($_FILES[$file_obj]['tmp_name']);
    copy($doc['tmp_name'], $tmp);
    $doc['tmp_name'] = $tmp;
  }
  else if( !isset($doc['org_id']) && isset($doc['id']) ) {
    $doc['org_id'] = $doc['id'];
    unset($doc['id']);
  }
  // else: already a copy

  return $doc;
}

?>