<?php
/** \file include\db_system.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 system database functions. This file is always included. 
 */

/**
 *  Fetch system languages, order by user preference
 *
 *  Inputs:
 *  - translate     Not used.
 *  - include       Array with which languages to include, or 'TRUE' for all.
 *                  A value of 'FALSE' is legal but silly.
 *
 *  Returns:        Array containing system languages
 */
function db_fetch_system_lang($translate = FALSE, $include = TRUE) {
  $result = array(); 
    
  // Fetch system maintenance messages
  $languages = db_fetch_data("SELECT * FROM talen ORDER BY i18n");
  
  // Sort the languages according to the user's preference; the order of
  //  the other languages is undetermined (quite possibly the database order)
  if( isset($_SERVER['HTTP_ACCEPT_LANGUAGE']) ) {
    $user_languages = preg_split("/,\s*/", $_SERVER['HTTP_ACCEPT_LANGUAGE']);
    
    // sort by preference
    foreach( $user_languages as $lang_pref ) {
      // split language code and qualifier
      $lang_pref = preg_split("/;\s*q\s*=\s*/", $lang_pref);
      $lang_code = substr($lang_pref[0], 0, 2);
      // qualifier set?
      if( count($lang_pref) > 1 ) 
        $result[$lang_code] = max($result[$lang_code], $lang_pref[1]);
      else
        $result[$lang_code] = 1.0;
    }
    arsort($result, SORT_NUMERIC);
  }
  
  // Set the language in the array (replacing the score)
  if (!empty($languages)) {    
    if (is_array($languages)) {
      foreach( $languages as $language ) {
        if(
          $include === TRUE ||
          (
            is_array($include)
            ? in_array($language['i18n'], $include)
            : ($language['i18n'] == $include)       // silly; mostly to avoid PHP errors
          )
        ) {
          $result[$language['i18n']] = $translate ? _($language['taal']) : $language['taal'];
        }
      }        
    }
  }      
      
  return $result; 
}


/**
 *  Test if 'i18n' is a valid language code
 */
function db_is_system_lang($i18n, $include = TRUE)
{
  $i18n_languages = db_fetch_system_lang(TRUE, $i18n_wo_supported);
  return isset($i18n_languages[$i18n]);
}


/**
 *  Fetch system charset
 *
 *  Inputs:
 *  - i18n:                 Requested i18n
 * 
 *  Return:                 charset
 */
function db_fetch_system_lang_charset($i18n) {
  // Initial return value
  $result = ""; 
    
  // Fetch charset
  $charset = db_fetch_data("SELECT charset FROM talen where i18n='" . $i18n . "'");  
  
  // Parse into result      
  if (!empty($charset)) {    
    $result = $charset[0]['charset'];
  }      

  return $result; 
}


/**
 *	Fetch supported currencies
 *
 *  Returns:                Array
 */
function db_fetch_system_currencies()
{
  // T.B.D.
  
  return array(
    "EUR" => array('html' => _("EUR"), 'name' => _("currency:EUR"), 'symbol' => _("symbol:EUR"), 'decimals' => 2),
    "GBP" => array('html' => _("GBP"), 'name' => _("currency:GBP"), 'symbol' => _("symbol:GBP"), 'decimals' => 2)
  );
}

/**
 *  Format a number according to currency
 */
define("ISO4217", "ISO4217");
function sprintf_amount($currency, $amount, $prefix = FALSE)
{
  $currencies = db_fetch_system_currencies();
  $localeconv = localeconv();
  
  // LC_MONETARY=='nl' puts a negative sign (i.e. the minus) behind the number
  // we don't want that :-)
  $value = money_format("%!i", abs($amount));
  // prepend the sign
  if( $amount < 0 ) $value = "-" . $value;
  // prepend the monetary unit
  if( $prefix ) {
    if( $prefix === TRUE ) {
      $value = $currencies[$currency]['symbol'] . " " . $value;
    }
    else if( $prefix === ISO4217 ) {
      // the array index _is_ an ISO 4217 currency code
      $value = $currency . " " . $value;
    }
    else {
      $value = $prefix . $value;
    }
  }
  return $value;
}


/**
 *  Fetch system maintenance messages
 * 
 *  Return:                 Array containing all maintenance messages
 */
function db_fetch_system_maint_mes($i18n = "") {
  // Initial return value
  $result = "";
  
  // Fetch system maintenance messages
  $query = "SELECT * FROM algemeen";  
  
  if (strlen($i18n)) {
    $query .= " WHERE i18n='" . $i18n . "'";
  }
  
  $row_maint_messages = db_fetch_data($query);  
  
 
  // Parse into result      
  if (!empty($row_maint_messages)) {    
    if (strlen($i18n)) {
      $result = $row_maint_messages[0];
    }                               
    else {
      $result = $row_maint_messages;
    }
  }    

  return $result; 
} 


/**
 *  Store/update system maintenance messages
 *
 *  Inputs:
 *  - message_array:        Array containing new message
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_system_maint_mes($messages) {
  // Initial return value
  $result = 0; 
    
  if (is_array($messages)) {
    $result = 1;
    foreach ($messages as $message) {
      // Check if exist?
      $row_general = db_fetch_data("SELECT * FROM algemeen WHERE i18n='" . $message['i18n'] . "'");
      
      if (!empty($row_general)) {
        // Query updating message  
        $query  = "UPDATE algemeen SET onderhoud='"  . specialchars($message['message']) . "' WHERE i18n='" . $message['i18n'] . "'";      
      }
      else {
        // Query insert message  
        $query  = "INSERT algemeen (i18n,onderhoud) VALUES ('" . $message['i18n'] . "','"  . specialchars($message['message']) . "')";                  
      }
      
      if (!db_store_data($query)) {  
        // Result NOK
        $result = 0;
      }  
    }
  }
    
  return $result; 
}


/**
 *  Fetch all available devices
 *
 * Inputs:
 * - i18n                   Requested i18n
 * - id                     Device id
 * 
 *  Return:                 Array containing all devices (no icon info!)
 */
function db_fetch_system_devices($i18n = "", $id = "", $by_id = FALSE) {
  // Fetch devices info
  $query  = "SELECT id,artikelnr,RIGHT(CONCAT('0000',HEX(devtype)),4) AS devtype_hexcode,weerstand_ok,calibratie_geldigheid,service_interval,locked,capabilities,nr_batterijen,nr_tempsensors,realtime_status,realtime_timeout,weerstand_cert ";
  $query .= " FROM device";
  if( is_numeric($id) ) {
    $query .= " WHERE device.id='" . $id . "'";
  }
  $row_devices = db_fetch_data($query);
 
  // Parse into result      
  if( is_array($row_devices) ) {
    // Fetch the name for the device in all languages (JW2014029; CVS r1.30.2.8)
    $devices = array();
    foreach( $row_devices as $device )
      $devices[$device['id']] = $device;
    
    // Fetch translations for the text on the delivery reports
    $query = "SELECT i18n,id,field,tekst FROM vertaling WHERE id IN (" . implode(",", array_keys($devices)) . ") AND field LIKE 'device.%'";
    if( $i18n ) $query .= " AND i18n='" . $i18n . "'";
    $row_text = db_fetch_data($query);
    if( $row_text ) {
      foreach( $row_text as $text_i18n ) {
        // save the tekst field; the key in the $devices array is the field name in the table less the 'device.' part (7 characters)
        if( $i18n )
          $devices[$text_i18n['id']][substr($text_i18n['field'], 7)] = $text_i18n['tekst'];
        else
          $devices[$text_i18n['id']][substr($text_i18n['field'], 7)][$text_i18n['i18n']] = $text_i18n['tekst'];
      }
    }

    $query = "SELECT device,naam,i18n FROM device_name WHERE device IN (" . implode(",", array_keys($devices)) . ")";
    $row_names = db_fetch_data($query);
    if( is_array($row_names) ) {
      foreach( $row_names as $name_i18n ) {
        $devices[$name_i18n['device']]['naam'][$name_i18n['i18n']] = $name_i18n['naam'];
      }
        
      // Get the right translation
      if( $i18n ) {
        $sort_order = array();
        foreach( $devices as &$device ) {
          $name = i18n_translate($device['naam'], NULL, $i18n);
          
          // Reduce the array of names to the correct translation
          $device['naam'] = $name;

          // Also create an array for sorting
          $sort_order[$device['id']] = $name;
        }
        // avoid PHP bug
        unset($device);
        
        // Sort the devices by name; can only do that when the name is translated
        asort($sort_order, SORT_LOCALE_STRING | SORT_FLAG_CASE);
        // Rearrange the array
        $devices_sorted = array();
        foreach( $sort_order as $id => $name )
          if( $by_id )
            $devices_sorted[$id] = $devices[$id];
          else
            $devices_sorted[] = $devices[$id];
        $devices = $devices_sorted;
      }
      else if( !$by_id ) {
        // needed for older code, when indexes the resulting array by $row[0] etc
        $devices_flat = array();
        foreach( $devices as $device ) $devices_flat[] = $device;
        $devices = $devices_flat;
      }
    }
        
    return $devices;
  }
  else return FALSE;
} 


/**
 *  Fetch device name
 *
 * Inputs:
 * - i18n                   Requested i18n
 * - id                     Device id
 * 
 *  Return:                 Device name (string)
 */
function db_fetch_system_device_name($i18n, $id) {
  // Fetch the name in all translations from the database
  //  (as off JW20140429, the name doesn't need to be entered for every language)
  $row_names = db_fetch_data("SELECT naam,i18n FROM device_name WHERE device=" . $id);
 
  // Parse into result      
  if( is_array($row_names) ) {
    // Re-arrange the array
    $names = array();
    foreach( $row_names as $name_i18n )
      $names[$name_i18n['i18n']] = $name_i18n['naam'];

    return i18n_translate($names, NULL, $i18n);
  }
  else return FALSE;
}

/**
 *  Fetch device icon info
 *
 * Inputs:
 * - id                     Device id
 * - icon                   Icon (ok, nok, ok_batt, ect)
 * 
 *  Return:                 array containing icon info (mimetype, width, height, icon)
 */
function db_fetch_system_device_icon($id, $icon) {
  // Initial return value
  $result = "";
  
  $icon_row = db_fetch_data("SELECT icon_" . $icon . ",icon_" . $icon . "_mimetype,icon_" . $icon . "_width,icon_" . $icon . "_height FROM device WHERE id='" . $id ."'");

  // Parse into result      
  if (!empty($icon_row)) {    
    $result = array(icon     => $icon_row[0]["icon_" . $icon],
                    width    => $icon_row[0]["icon_" . $icon . "_width"],
                    height   => $icon_row[0]["icon_" . $icon . "_height"],
                    mimetype => $icon_row[0]["icon_" . $icon . "_mimetype"]);
  }    

  return $result; 
}

/**
 *  Check device capabilities 
 *
 * Inputs:
 * - id                     Device id
 * - capabilities           Capabilities/rt status display array  (gps AND gsm, etc), multi array => OR function
 * - supported_devices      Display all devices which support this capability
 * - supported_devices      Which set must be checked
 * 
 *  Return:                 1 (TRUE)/ 0 (FALSE) or list of all supported device types
 */
function db_check_system_device_capabilities($id, $capabilities, $supported_devices = FALSE, $set = "capabilities") {
  // Initial result value
  $result = FALSE;
  $first  = TRUE;
  
  $query = "SELECT id FROM device WHERE ";    
  
  if (is_array($capabilities)) {   
    foreach($capabilities as $item) {
      if (is_array($item)) {
        if (!$first) {
          $query .= " AND ";                        
        }
        $first = FALSE;        
        $query .= " (";  
        for($i=0; $i<sizeof($item); $i++) {
          if ($i) {          
            $query .= "OR ";  
          }
          $query .= "FIND_IN_SET('" . ($item[$i]) . "'," . $set . ") > 0 ";  
        }
        $query .= ") ";  
      }
      else {        
        if (!$first) {
          $query .= " AND ";                        
        }
        $first = FALSE;        
        $query .= " FIND_IN_SET('" . ($item) . "'," . $set . ") > 0 ";  
      }
    }  
  } 
  else {
    $query .= " FIND_IN_SET('" . ($capabilities) . "'," . $set . ") > 0 ";  
  }  
  
  $devices = db_fetch_data($query);
  
  // Check if id is present
  if (is_array($devices)) {
    // Return all devices which support these capability
    if ($supported_devices) {      
      $result = array();
      
      foreach($devices as $device) {
        array_push($result, $device['id']);
      }      
    }    
    // Check for specific id
    else {
      foreach($devices as $device) {
        if ($device['id'] == $id) {
          $result = TRUE;
          break;
        
        }
      }
    }
  }

  return $result;
}


/**
 * Get the number of temperature sensors
 */
function db_system_device_nr_tempsensors($id)
{
  $data = db_fetch_data("SELECT nr_tempsensors FROM device WHERE id=" . $id);
  if( empty($data) ) return FALSE;
  else return $data[0]['nr_tempsensors'];
}


/**
 *  Fetch device status
 *
 * Inputs:
 * - id                     Device id
 * - i18n                   Internationalisation
 * - cond                   Conditions (relais, kortsluiting)
 * - value                  value (ok,nok)
 * 
 *  Return:                 array(status, display)
 */
function db_fetch_system_device_status($id, $i18n, $cond = "", $value = "") {
  // Initial return value
  $result = false;
  
  if ((is_array($cond)) || (strlen($cond))) {
    $query = "SELECT status FROM device_status_conditie WHERE device='" . $id . "'";
    
    if (is_array($cond)) {
      foreach($cond as $item) {
        $value  = ($item['value']) ? ">0" : "=0";
        $query .= " AND FIND_IN_SET('" . $item['condition'] . "', conditie)" . $value;
      }
    }
    else {
      $query .= " AND conditie=" . $cond . "";
    }
  
    // Execute condition query  
    $condition_result = db_fetch_data($query, 1);       
    
    if (!empty($condition_result)) {
      $status = $condition_result[0]['status'];
    }    
  } 
  else {
    $status = $value;
  } 

  if (strlen($status)) {   
    $query = "SELECT * FROM device_status_weergave WHERE device='" . $id . "' and status='" . $status . "' and i18n='" . $i18n . "'";       
      
    // Execute display query
    $display_result = db_fetch_data($query, 1);
    
    // Parse into result      
    if (!empty($display_result)) {      
      $result = array("status" => $status, "display" => $display_result[0]['weergave']);
    }       
  }
  
  return $result; 
}




/**
 *  Fetch service interval
 *
 * Inputs:
 * - id                     Device id
 */
function db_check_system_service_interval($id)
{
  $query = "SELECT service_interval FROM  device WHERE id=" . $id;
  return db_fetch_item($query);
}





/**
 *  Search system device
 *
 *  Inputs:
 *  - name                  Device name (incl wildcard)
 *  - i18n                  Nationalisation
 *
 *  Return:                 Multidimensional array containing all document information
 */
function db_search_system_device($name, $i18n)
{
  // Fetch device info
  $query  = "SELECT device.*,device_name.naam,device_name.i18n FROM device,device_name";
  $query .= " WHERE device.id=device_name.device";
  $query .= " AND i18n='" . $i18n . "'";
  if( $name ) {
    $query .= " AND (device_name.naam LIKE '%" . $name . "%' OR device.artikelnr LIKE '%" . $name . "%')";
  }
  $query .= " ORDER by device_name.naam";
  return db_fetch_data($query);
}

// (original function, superceeded with the advent of the article code)
function db_search_system_device_name($name, $i18n)
{
  // Initial return value
  $result = ""; 
 
  // Fetch device info
  $device = db_fetch_data("SELECT * FROM device_name WHERE naam LIKE '%" . $name . "%' AND i18n='" . $i18n . "' ORDER by naam");

  // Parse into result      
  if (!empty($device)) {    
    $result = $device;
  }    

  return $result;  
}


/**
 *  Store new device type
 *
 * Inputs:
 * - device_array           array containing device info
 * 
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_system_device(&$device_array) { 
  //initial return value  
  $result = 1;    

  // Start transaction
  db_start_transaction();  

  $query  = "INSERT device (artikelnr,devtype,weerstand_ok,weerstand_cert,";
  // Retrieve all blob type
  $icons = db_fetch_fields("device", "blob");
  if (is_array($icons)) {
    foreach($icons as $icon) {
      $query .= $icon . "," . $icon . "_mimetype," . $icon . "_width," . $icon . "_height,";
    }
  }       
  $query .= "nr_batterijen,nr_tempsensors,locked,capabilities,realtime_status,realtime_timeout,calibratie_geldigheid,service_interval) VALUES (";
  if( $device_array['artikelnr'] )
    $query .= "'" . $device_array['artikelnr'] . "'";
  else
    $query .= "NULL";
  $query .= ","  . $device_array['devtype']                                  ;  // no quotes; it's a number or the string "NULL"
  $query .= ",'" . addslashes($device_array['weerstand_ok'])            . "'";
  $query .= ",'" . addslashes($device_array['weerstand_cert'])          . "'";

  if (is_array($icons)) {
    foreach($icons as $icon) {
      if (strlen($device_array[$icon])) {
        $query .= ",'" . mysql_real_escape_string($device_array[$icon], db_get_connection()) . "'";
        $query .= ",'" . ($device_array[$icon . "_mimetype"])           . "'";
        $query .= ",'" . ($device_array[$icon . "_width"])              . "'";
        $query .= ",'" . ($device_array[$icon . "_height"])             . "'";
      }
      else {
        $query .= ",NULL,NULL,40,20";    
      }
    }
  }
  $query .= ",'" . ($device_array['nr_batterijen'])                     . "'";
  $query .= ",'" . ($device_array['nr_tempsensors'])                    . "'";
  if (strlen($device_array['locked'])) {
    $query .= ",'" . ($device_array['locked'])                          . "'";
  }
  else {
    $query .= ",'no'";
  }
  $query .= ",'" . ($device_array['capabilities'])                      . "'";    
  $query .= ",'" . ($device_array['realtime_status'])                   . "'";
  $query .= ",'" . addslashes($device_array['realtime_timeout'])        . "'";
  if (strlen($device_array['calibratie_geldigheid'])) {
    $query .= ",'" . addslashes($device_array['calibratie_geldigheid']) . "'";
  }
  else {
    $query .= ",NULL";    
  }
  if (strlen($device_array['service_interval'])) {
    $query .= ",'" . addslashes($device_array['service_interval']) . "'";
  }
  else {
    $query .= ",NULL";    
  }
  $query .= ")";

  if (!db_store_data($query)) {  
    $result = 0;
  }
  else {
    // Determine ID      
    $device_array['id'] = db_fetch_last_id();
  }
  
  // Store device names/status
  if ($result) {
    $query = "";
    $sep   = "INSERT INTO vertaling (`i18n`,`field`,`id`,`tekst`) VALUES ";
    if( is_array($device_array['verklaring_veiligheid']) ) foreach( $device_array['verklaring_veiligheid'] as $i18n => $text ) {
      $query .= $sep . "('" . $i18n . "','device.verklaring_veiligheid'," . $device_array['id'] . ",'" . specialchars($text) . "')";
      $sep = ",";
    }
    if( is_array($device_array['verklaring_klant']) ) foreach( $device_array['verklaring_klant'] as $i18n => $text ) {
      $query .= $sep . "('" . $i18n . "','device.verklaring_klant'," . $device_array['id'] . ",'" . specialchars($text) . "')";
      $sep = ",";
    }
    if( $query && !db_store_data($query) ) {
      $result = FALSE;
    }
    else if (!db_store_system_device_names($device_array)           || 
        !db_store_system_device_status_display($device_array)  || 
        !db_store_system_device_status_condition($device_array)) {
      $result = 0;
    }        
  }

  // Commit transaction
  if( !db_commit_transaction() ) return FALSE;

  return $result;
}


/**
 *  Store new device names
 *
 * Inputs:
 * - device_array           array containing device info
 * 
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_system_device_names($device_array) { 
  //initial return value  
  $result = 1;      
  
  // Start transaction
  db_start_transaction();  

  // Delete device
  $query  = "DELETE FROM device_name WHERE device='" . $device_array['id'] . "'";
  db_store_data($query);
  
  // Commit transaction
  if (db_commit_transaction()) {     
    // Parse result
    $result = 1;    
  }        

  if ($result) {
    $i18n_languages = db_fetch_system_lang();   
    
    if (is_array($i18n_languages)) {         
      foreach ($i18n_languages as $i18n => $lang) {    
        if ($result) {
          if (!db_store_data("INSERT device_name (device, i18n, naam) VALUES ('" . $device_array['id'] . "','" . $i18n . "','"  . specialchars($device_array["naam"][$i18n]) . "')")) {
            $result = 0;
          }
        }
      }
    }
  }
  
  return $result;  
}


/**
 *  Store new device status display
 *
 * Inputs:
 * - device_array           array containing device info
 * 
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_system_device_status_display($device_array) { 
  //initial return value  
  $result = 1;      
  
  // Start transaction
  db_start_transaction();  

  // Delete device
  $query  = "DELETE FROM device_status_weergave WHERE device='" . $device_array['id'] . "'";
  db_store_data($query);
  
  // Commit transaction
  if (db_commit_transaction()) {     
    // Parse result
    $result = 1;    
  }        
  
  if ($result) {
    $i18n_languages = db_fetch_system_lang();   
    
    $options = db_fetch_set("device_status_weergave","status");
    if ((is_array($i18n_languages)) && (is_array($options))) {                 
      foreach ($i18n_languages as $i18n => $lang) {    
        foreach ($options as $option) {    
          if (($result) && (strlen($device_array[$option . "_value_" . $i18n]))) {
            $query  = "INSERT device_status_weergave (device, i18n, weergave, status) VALUES ";
            $query .= "('" . $device_array['id'] . "','" . $i18n . "','"  . specialchars($device_array[$option . "_value_" . $i18n]) . "', '" . $option . "')";
            if (!db_store_data($query)) {
              $result = 0;
            }
          }
        }
      }
    }
  }    
  
  return $result;
}  


/**
 *  Store new device status condition
 *
 * Inputs:
 * - device_array           array containing device info
 * 
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_system_device_status_condition($device_array) { 
  //initial return value  
  $result = 1;      

  // Start transaction
  db_start_transaction();  

  // Delete device
  $query  = "DELETE FROM device_status_conditie WHERE device='" . $device_array['id'] . "'";
  db_store_data($query);
  
  // Commit transaction
  if (db_commit_transaction()) {     
    // Parse result
    $result = 1;    
  }      

  if ($result) {
    // Store all status when set
    for ($i=0; $i<4; $i++) {
      if (isset($device_array["status_" . $i])) {
        $query  = "INSERT device_status_conditie (device, status, conditie) VALUES ";
        $query .= "('" . $device_array['id'] . "','" . $device_array["status_" . $i] . "'," . $i . ")";
            
        if (!db_store_data($query)) {
          $result = 0;
        }
      }
    }
  }
  
  return $result;
} 


/**
 *  Update existing device type
 *
 * Inputs:
 * - device_array           array containing device info
 * 
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_update_system_device($device_array) { 
  //initial return value  
  $result = 1;    

  // Start transaction
  db_start_transaction();  

  // Query updating device      
  $query  = "UPDATE device SET ";
  if( $device_array['artikelnr'] )
    $query .= "artikelnr='" . $device_array['artikelnr'] . "',";
  else
    $query .= "artikelnr=NULL,";
  $query .= "devtype=" .                  $device_array['devtype']                     . ",";  // no quotes; it's a number or the string "NULL"
  $query .= "weerstand_ok='"            . addslashes($device_array['weerstand_ok'])   . "',";
  $query .= "weerstand_cert='"          . addslashes($device_array['weerstand_cert']) . "',";
  // Retrieve all blob type
  $icons = db_fetch_fields("device", "blob");
  if (is_array($icons)) {
    foreach($icons as $icon) {
      if (strlen($device_array[$icon])) {
        $query .= $icon . "='"          . mysql_real_escape_string($device_array[$icon], db_get_connection()) . "',";
        $query .= $icon . "_mimetype='" . ($device_array[$icon . "_mimetype"])        . "',";
        $query .= $icon . "_width='"    . ($device_array[$icon . "_width"])           . "',";          
        $query .= $icon . "_height='"   . ($device_array[$icon . "_height"])          . "',";          
      }
      else {
        $query .= $icon . "=NULL,";
        $query .= $icon . "_mimetype=NULL,";
        $query .= $icon . "_width='40',";          
        $query .= $icon . "_height='20',";          
      }
    }
  }
  $query .= "nr_batterijen='"           . ($device_array['nr_batterijen'])            . "',";
  $query .= "nr_tempsensors='"          . ($device_array['nr_tempsensors'])           . "',";
  if (strlen($device_array['locked'])) {
    $query .= "locked='"                . ($device_array['locked'])                   . "',";    
  }
  else {
    $query .= "locked='no',";
  }    
  $query .= "capabilities='"            . ($device_array['capabilities'])             . "',";    
  $query .= "realtime_status='"         . ($device_array['realtime_status'])          . "',";    
  $query .= "realtime_timeout='"        . ($device_array['realtime_timeout']) . "',";    
  if (strlen($device_array['calibratie_geldigheid'])) {
    $query .= "calibratie_geldigheid='" . addslashes($device_array['calibratie_geldigheid']) . "',";
  }
  else {
    $query .= "calibratie_geldigheid=NULL,";    
  }
  if (strlen($device_array['service_interval'])) {
    $query .= "service_interval='" . addslashes($device_array['service_interval']) . "' ";
  }
  else {
    $query .= "service_interval=NULL ";    
  }
  $query .= "WHERE id='"                . ($device_array['id'])                       . "'";       
  if (db_store_data($query)) {  
    // Result OK
    $result = 1;
  }  

  // Store device names/status
  if ($result) {
    // Delete old transltations
    $query  = "DELETE FROM `vertaling` WHERE `id`=" . $device_array['id'] . " AND `field` LIKE 'device.%'";
    db_store_data($query);
    
    $query = "";
    $sep   = "INSERT INTO vertaling (`i18n`,`field`,`id`,`tekst`) VALUES ";
    if( is_array($device_array['verklaring_veiligheid']) ) foreach( $device_array['verklaring_veiligheid'] as $i18n => $text ) {
      $query .= $sep . "('" . $i18n . "','device.verklaring_veiligheid'," . $device_array['id'] . ",'" . specialchars($text) . "')";
      $sep = ",";
    }
    if( is_array($device_array['verklaring_klant']) ) foreach( $device_array['verklaring_klant'] as $i18n => $text ) {
      $query .= $sep . "('" . $i18n . "','device.verklaring_klant'," . $device_array['id'] . ",'" . specialchars($text) . "')";
      $sep = ",";
    }
    if( $query && !db_store_data($query) ) {
      $result = FALSE;
    }
    else if (!db_store_system_device_names($device_array)      || 
        !db_store_system_device_status_display($device_array)  || 
        !db_store_system_device_status_condition($device_array)) {
      $result = 0;
    }        
  }  

  // Commit transaction
  if( !db_commit_transaction() ) return FALSE;
  
  return $result;  
}


/**
 *  Delete device type
 *
 * Inputs:
 * - device_id              device db id
 * 
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_delete_system_device($device_id) { 
  // Start transaction
  db_start_transaction();  

  // Delete device
  $query  = "DELETE FROM device WHERE id='" . $device_id . "'";
  db_store_data($query);
  
    // Delete device name
  $query  = "DELETE FROM device_name WHERE device='" . $device_id . "'";
  db_store_data($query);  

  // Delete device status
  $query  = "DELETE FROM device_status_conditie WHERE device='" . $device_id . "'";
  db_store_data($query);  
  
  // Delete device status
  $query  = "DELETE FROM device_status_weergave WHERE device='" . $device_id . "'";
  db_store_data($query);    
  
  // Commit transaction
  if (db_commit_transaction()) {     
    // Parse result
    $result = 1;    
  }      
  
  return $result; 
}
?>