1036 lines
40 KiB
PHP
1036 lines
40 KiB
PHP
<?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;
|
|
}
|
|
|
|
?>
|