<?php
/** \file include\db_lance.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 lance database functions. This file is always included.
 */

/**
 *  Search all lances changes which match the zkl and between the begin and end time
 *
 *  Inputs:
 *  - lance:                lance id
 *  - begin:                begin of log changes
 *  - end:                  end of log changes
 *
 *
 *  Return:                 Array containing all change information
 */
function db_fetch_lance_log_versionnumber ($lance,$begin,$end) {
  // Initial return value
  $result = "";

  // Check if there is an begin time. No begin time => select all till end time
  if (empty($begin)) {
    $row = db_fetch_data("SELECT * FROM log_versienummer WHERE t <= '" . $end . "' AND zkl='".$lance."'");
  }
  else if (empty($end)) {
    $row = db_fetch_data("SELECT * FROM log_versienummer WHERE t >= '".$begin."' AND zkl='".$lance."'");
  }
  else {
    $row = db_fetch_data("SELECT * FROM log_versienummer WHERE t BETWEEN '".$begin."' AND '".$end."' AND zkl='".$lance."'");
  }

  if (!empty($row)) {
    $result=$row;
  }

  return $result;
}

/**
 *  Search all lances which match current db id customer
 *
 *  Inputs:
 *  - device:               Device type
 *  - lance_status:         Visible lance status
 *  - cust_status:          Visible customer status
 *  - lance:                ID code DI
 *  - idcode:               ID code
 *
 *  Return:                 Array containing all lance information
 */
function db_search_lances($device = "", $lance_status = "", $cust_status = "", $lance = "", $idcode = "") {
  global $_PAGE_INFO;

  // Initial return value
  $result = array();

  // Search for all underlying customers
  $row_customers = db_search_customers();

  // Add own id to array => when not in array
  $found_cust = 0;
  if (is_array($row_customers)) {
    foreach ($row_customers as $row_customer) {
      if ($row_customer['id'] == $_PAGE_INFO['login']['customer']['id']) {
        $found_cust = 1;
      }
    }
  }

  if (!$found_cust) {
    $current_customer = db_fetch_customer($_PAGE_INFO['login']['customer']['id'], 1);
    array_push($row_customers, $current_customer);
  }

  // This array prevents double result (equipment can be rented/hired => 1 user & 1 owner => 2 results)
  $all_ids = array();

  if (is_array($row_customers)) {
    $customer_array = array();
    foreach ($row_customers as $row_customer) {
      array_push($customer_array, $row_customer['id']);
    }

    // Retrieve all lances from these customer(s)
    $row_lances = db_fetch_lances($customer_array, $lance, $device, "", $lance_status, $cust_status, $idcode);

    if (is_array($row_lances)) {
      foreach ($row_lances as $row_lance) {
        if (!in_array($row_lance['id'],$all_ids)) {
          array_push($all_ids, $row_lance['id']);
          array_push($result, $row_lance);
        }
      }
    }
  }

  // Search also for non-assigned lances
  if (db_ver_rights_user_one_valid($_PAGE_INFO['login']['user']['id'], "menu:lansen:root,menu:service:productie")) {
    $row_lances = db_fetch_lances(NULL, $lance, $device, "", $lance_status, $cust_status, $idcode);
    if (is_array($row_lances)) {
      foreach ($row_lances as $row_lance) {
        array_push($result, $row_lance);
      }
    }
  }

  // Sort by idcode & return array
  return array_sort($result, "idcode");
}


/**
 *  Fetch lance which match lance database id
 *
 *  Inputs:
 *  - lance:                lance database id/lance serial number
 *  - customer_id:          Customer database id
 *  - db_id:                lance serial number (0)/ lance database id (1)
 *
 *  Return:                 Array containing lance info
 */
function db_fetch_lance($lance, $customer_id, $db_id = 0) {
  // Initial return value
  $result = "";

  // Fetch lance info (unique)
  if (!$db_id) {
    if (strlen($customer_id)) {
      $row_lance = db_fetch_data("SELECT * FROM zkl WHERE serienr='" . specialchars($lance) . "' AND (eigenaar='" . $customer_id . "' OR gebruiker='" . $customer_id . "')", 1);
    }
    else {
      $row_lance = db_fetch_data("SELECT * FROM zkl WHERE serienr='" . specialchars($lance) . "'", 1);
    }
  }
  else {
    $row_lance = db_fetch_data("SELECT * FROM zkl WHERE id='" . $lance . "'", 1);
  }

  // Parse into result
  if (!empty($row_lance)) {
    $result = $row_lance[0];
  }

  return $result;
}


/**
 *  Fetch lances with a particular capability (or several capabilities, like
 *  'db_check_system_device_capabilities()'
 *
 * Inputs:
 * - customer_id:          Customer id
 * - capabilities           Capabilities/rt status display array  (gps AND gsm, etc), multi array => OR function
 *
 * Returns:                 Devices matching the capability/capabilities
 */
function db_fetch_lances_by_capability($capabilities)
{
  $result = array();
  global $_PAGE_INFO;
  $types = db_fetch_system_devices($_PAGE_INFO['i18n']);

  if (is_array($types)) {
    // Get all lances
    $lances = db_search_lances("", $_SESSION[$_PAGE_INFO['id']]['status_di']);             

    foreach($types as $type) {
      if (db_check_system_device_capabilities($type['id'], $capabilities)) {
        foreach( $lances as $lance ) {
          if( $lance['device'] == $type['id'] ) {
            array_push($result, $lance);
          }
        }
      }
    }
  }

  // none found
  return $result;
}
 
/**
 *  Fetch lance with the specified IMEI
 *
 *  Inputs:
 *  - imei:                 IMEI number to search for
 *
 *  Return:                 Array containing lance info
 */
function db_fetch_imei($imei) {
  // Initial return value
  $result = "";

  // Fetch lance info (unique)
  $row_lance = db_fetch_data("SELECT * FROM zkl WHERE imei='" . $imei . "'");

  // Parse into result
  if (!empty($row_lance)) {
    $result = $row_lance;
  }

  return $result;
}
 
/**
 *  Fetch lance with the specified IMSI
 *
 *  Inputs:
 *  - imsi:                 IMSI number to search for
 *
 *  Return:                 Array containing lance info
 */
function db_fetch_imsi($imsi) {
  // Initial return value
  $result = "";

  // Fetch lance info (unique)
  $row_lance = db_fetch_data("SELECT * FROM zkl WHERE imsi='" . $imsi . "'");

  // Parse into result
  if (!empty($row_lance)) {
    $result = $row_lance;
  }

  return $result;
}


/**
 *  Fetch firmware version
 */
function db_fetch_firmware($lance) {
  // Initial value
  $version_info = array();

  // Get lance info
  if (!is_array($lance)) {
    $lance_info = db_fetch_lance($lance, "", 1);
  }
  else {
    $lance_info = $lance;
  }

  preg_match("/([0-9+\.[0-9]+)-([0-9]{8})/", $lance_info['wcpu_versie'], $version_info);

  return $version_info;
}


/**
 *  Fetch lance which match lance database id
 *
 *  Inputs:
 *  - lance:                lance database id/lance serial number
 *  - customer_id:          Customer id
 *  - device:               Device type
 *  - rentable:             Device rent status (0,1,2,3,4)
 *  - lance_status:         Visible lance status
 *  - cust_status:          Visible customer status
 *  - idcode:               Customer idcode
 *
 *  Return:                 Array containing lance info
 */
function db_fetch_lances($customer_id, $lance = "", $device = "", $rent = "", $lance_status = "", $cust_status = "", $idcode = "") {
  // Initial return value
  $result = "";

  // Fetch lance info
  if( _is_null($customer_id) ) { 
    $query = "SELECT * FROM zkl WHERE eigenaar is NULL";
    if (strlen($lance)) {
      $query .= " AND serienr LIKE '%" . specialchars($lance) . "%' ";
    }
    if( is_array($device) ) {
    	if( !empty($device) ) $query .= " AND device IN (" . implode(",", $device) . ")";
    }
    else if( is_numeric($device) ) {
      $query .= " AND device=" . $device;
    }
    if (strlen($idcode)) {
      global $_PAGE_INFO;
      
      if( db_ver_rights_user_one_valid($_SESSION[$_PAGE_INFO['id']]['login']['user']['id'], "menu:lansen:root,menu:service:productie") ) {
        $query .= " AND (idcode LIKE '%" . specialchars($idcode) . "%' OR serienr LIKE '%" . specialchars($idcode) . "%') ";
      }
      else {
        $query .= " AND idcode LIKE '%" . specialchars($idcode) . "%' ";
      }
    }
    
    $query_sep = " AND ";
  }
  else {
    $query = "SELECT * FROM zkl";
    $query_sep = " WHERE ";

    // Multiple customers?
    if( is_array($customer_id) ) {
    	// empty array is allowed to select all devices
    	if( !empty($customer_id) ) {
	      $query .= $query_sep . " (";
	      for($i=0; $i<sizeof($customer_id); $i++) {
	        if ($i) {
	          $query .= " OR ";
	        }
	        // it is possible that the owner is not assigned (i.e. a NULL value in the database);
	        if( _is_null($customer_id[$i]) ) {
		        $query .= " (eigenaar IS NULL ";
		      }
		      else {
		        $query .= " (eigenaar=" . $customer_id[$i] . " OR gebruiker=" . $customer_id[$i] . " ";
		      }
	
	        // Handle some cases
	        if (strlen($rent)) {
	          switch($rent)
	          {
	            case 4:
	              // Available (not rented) and hired equipment
	              $query .= " AND (gebruiker is NULL OR (gebruiker=eigenaar AND eigenaar=" . $customer_id[$i] . ") OR gebruiker=" . $customer_id[$i] . ")";
	              break;
	            case 5:
	              // Available (not rented) and no hired equipment
	              $query .= " AND (gebruiker is NULL OR (gebruiker=eigenaar AND eigenaar=" . $customer_id[$i] . "))";
	              break;
	            case 6:
	              // All own equipment(not rented)
	              $query .= " AND eigenaar=" . $customer_id[$i] . "";
	              break;
	            default:
	              break;
	          }
	        }
	        $query .= ")";
	      }
	      $query .= ")";
	      $query_sep = " AND ";
	    }
    }
    else if (strlen($customer_id)) {
      $query .= $query_sep . "(eigenaar=" . $customer_id . " OR gebruiker=" . $customer_id . ") ";
      $query_sep = " AND ";
    }

    if (strlen($lance)) {
      $query .= $query_sep . "serienr LIKE '%" . specialchars($lance) . "%' ";
      $query_sep = " AND ";
    }

    if (strlen($idcode)) {
      global $_PAGE_INFO;
      
      if( db_ver_rights_user_one_valid($_SESSION[$_PAGE_INFO['id']]['login']['user']['id'], "menu:lansen:root,menu:service:productie") ) {
        $query .= " AND (idcode LIKE '%" . specialchars($idcode) . "%' OR serienr LIKE '%" . specialchars($idcode) . "%') ";
      }
      else {
        $query .= " AND idcode LIKE '%" . specialchars($idcode) . "%' ";
      }

      $query_sep = " AND ";
    }

		// NB: '$device' may come from an "explode()" and the result of exploding
		//	an empty string is an array with one item, an empty string
    if( is_array($device) && ($devices = trim(implode(",", $device))) ) {
    	$query .= $query_sep . "device IN (" . $devices . ")";
      $query_sep = " AND ";
    }
    else if( is_numeric($device) ) {
      $query .= $query_sep . "device=" . $device;
      $query_sep = " AND ";
    }
  }

  if (strlen($rent)) {
    switch($rent)
    {
      case 0:
        // Not avalaible for rent
        $query .= $query_sep . "gebruiker is NULL";
	      $query_sep = " AND ";
        break;
      case 1:
        // Already been rented
        $query .= $query_sep . "gebruiker!=eigenaar";
	      $query_sep = " AND ";
        break;
      case 2:
        // Available for rent
        $query .= $query_sep . "gebruiker=eigenaar";
	      $query_sep = " AND ";
        break;
      case 3:
        // Available for rent/or already rented
        $query .= $query_sep . "gebruiker is not NULL";
	      $query_sep = " AND ";
        break;
      case 4:
        if ((!is_array($customer_id)) && (!_is_null($customer_id)) && (!empty($customer_id)) && (is_numeric($customer_id))) {
          // Available (not rented) and hired equipment
          $query .= $query_sep . "(gebruiker is NULL OR (gebruiker=eigenaar AND eigenaar=" . $customer_id . ") OR gebruiker=" . $customer_id . ")";
		      $query_sep = " AND ";
        }
        break;
      case 5:
        if ((!is_array($customer_id)) && (!_is_null($customer_id)) && (!empty($customer_id)) && (is_numeric($customer_id))) {
          // Available (not rented) and no hired equipment
          $query .= $query_sep . "(gebruiker is NULL OR (gebruiker=eigenaar AND eigenaar=" . $customer_id . "))";
		      $query_sep = " AND ";
        }
        break;
      case 6:
        if ((!is_array($customer_id)) && (!_is_null($customer_id)) && (!empty($customer_id)) && (is_numeric($customer_id))) {
          // All own equipment(not rented)
          $query .= $query_sep . "eigenaar=" . $customer_id . "";
		      $query_sep = " AND ";
        }
        break;
      default:
        break;
    }
  }

  // Specific lance status
  if (((is_array($lance_status)) && (!empty($lance_status))) || (strlen($lance_status))) {
    if ((is_array($lance_status)) && (!empty($lance_status))) {
      for($i=0; $i<sizeof($lance_status); $i++) {
        if (!$i) {
          $query .= $query_sep . "(";
        }
        else {
          $query .= " OR ";
        }
        $query .= " lans_status='" . $lance_status[$i] . "'";
      }
      $query .= ") ";
      $query_sep = " AND ";
    }
    else {
      $query .= $query_sep . "lans_status='" . $lance_status . "'";
      $query_sep = " AND ";
    }
  }

  // Specific customer status
  if (((is_array($cust_status)) && (!empty($cust_status))) || (strlen($cust_status))) {
    if ((is_array($cust_status)) && (!empty($cust_status))) {
      for($i=0; $i<sizeof($cust_status); $i++) {
        if (!$i) {
          $query .= $query_sep . "(";
        }
        else {
          $query .= " OR ";
        }
        $query .= " klant_status='" . $cust_status[$i] . "'";
      }
      $query .= ") ";
      $query_sep = " AND ";
    }
    else {
      $query .= $query_sep . "klant_status='" . $cust_status . "'";
      $query_sep = " AND ";
    }
  }

  // Order by idcode
  $query .= " ORDER BY idcode";

  $row_lances = db_fetch_data($query);

  // Parse into result
  if (!empty($row_lances)) {
    $result = $row_lances;
  }

  return $result;
}


/**
 *  Fetch all lances
 *
 *  Inputs:
 *  - device:               Device type
 *
 *  Return:                 Array containing lance info
 */
function db_fetch_all_lances($device = "", $parent = "") {
  // Initial return value
  $result = "";

  // Fetch lance info
  if (strlen($device)) {
    $query = "SELECT * FROM zkl WHERE device='" . $device . "'";
    if( $parent ) $query .= " AND parent=" . $parent;
  }
  else if( $parent ) {
    $query = "SELECT * FROM zkl WHERE parent=" . $parent;
  }
  else {
    $query = "SELECT * FROM zkl use index(PRIMARY)";
  }

  $row_lances = db_fetch_data($query);

  // Parse into result
  if (!empty($row_lances)) {
    $result = $row_lances;
  }

  return $result;
}


/**
 *  Fetch lance factory number
 *
 *  Inputs:
 *  - fabric:
 *
 *  Return:                 Array containing fabric number info
 */
function db_fetch_lance_factory_number($fabric) {
  // Initial return value
  $result = "";

  // Fetch lance info (unique)
  $row_lance = db_fetch_data("SELECT * FROM zkl WHERE fabrieksnr='" . specialchars($fabric) . "'");

  if (!empty($row_lance)) {
    // Parse into result
    $result = $row_lance[0];
  }

  return $result;
}


/**
 *  Fetch lance calibration info
 *
 *  Inputs:
 *  - lance_id:             lance database id
 *  - last                  only last or all?
 *
 *  Return:                 Array containing calibration info
 */
function db_fetch_lance_calib_info($lance_id, $last = 1)
{
  // Initial return value
  $result = "";

  // Fetch lance calibration info
  if ($last) {
    $calib_info = db_fetch_data("SELECT * FROM zkl_calibratie WHERE zkl='" . $lance_id . "' ORDER BY datum DESC, id DESC LIMIT 1");
  }
  else {
    $calib_info = db_fetch_data("SELECT * FROM zkl_calibratie WHERE zkl='" . $lance_id . "' ORDER BY datum DESC, id DESC");
  }

  // Parse into result
  if (!empty($calib_info)) {
    if ($last) {
      $result = $calib_info[0];
    }
    else {
      $result = $calib_info;
    }
  }

  return $result;
}

/**
 *  Fetch lance end control info
 *
 *  Inputs:
 *  - lance_id:             lance database id
 *  - last                  only last or all?
 *
 *  Return:                 Array containing end control info
 */
function db_fetch_lance_endcontrol($lance_id, $last = 1)
{
  // Initial return value
  $result = "";

  // Fetch lance info
  if ($last) {
    $endcontrol = db_fetch_data("SELECT * FROM zkl_eindcontrole WHERE zkl='" . $lance_id . "' ORDER BY datum DESC, id DESC LIMIT 1");
  }
  else {
    $endcontrol = db_fetch_data("SELECT * FROM zkl_eindcontrole WHERE zkl='" . $lance_id . "' ORDER BY datum DESC, id DESC");
  }

  // Parse into result
  if (!empty($endcontrol)) {
    if ($last) {
      $result = $endcontrol[0];
    }
    else {
      $result = $endcontrol;
    }
  }

  return $result;
}

/**
 *  Fetch lance alarm numbers
 *
 *  Inputs:
 *  - lance_id:             lance database id
 *  - lance_alarmnr_id      Alarm number id
 *  - server                Also server numbers (1/0)
 *
 *  Return:                 Array containing all lance numbers
 */
function db_fetch_lance_alarmnrs($lance_id, $lance_alarmnr_id = "", $server = "")
{
  // Initial return value
  $result = "";

  // Fetch lance info
  if (strlen($lance_alarmnr_id)) {
    $query = "SELECT * FROM zkl_alarmnr WHERE id='" . $lance_alarmnr_id . "'";
    if (strlen($server)) {
      $query .= " AND servernr='" . $server . "'";
    }
    $query .= " ORDER BY alarmnr";
  }
  else {
    $query = "SELECT * FROM zkl_alarmnr WHERE zkl='" . $lance_id . "'";
    if (strlen($server)) {
      $query .= " AND servernr='" . $server . "'";
    }
    $query .= " ORDER BY alarmnr";
  }

  $row_alarmnr = db_fetch_data($query);

  // Parse into result
  if (!empty($row_alarmnr)) {
    $result = $row_alarmnr;
  }

  return $result;
}

/**
 *  Fetch lance id codes which match customer
 *
 *  Inputs:
 *  - idcode:               idcode
 *  - customer_id:          customer db id
 *
 *  Return:                 Array containing all lance numbers
 */
function db_fetch_lance_id_code($idcode, $customer_id = NULL)
{
  // Initial return value
  $result = "";

  // Fetch lance info
  if ( _is_null($customer_id) ) {
    $row_lance = db_fetch_data("SELECT * FROM zkl WHERE idcode='" . specialchars($idcode) . "' AND eigenaar is NULL");
  }
  else {
    $row_lance = db_fetch_data("SELECT * FROM zkl WHERE idcode='" . specialchars($idcode) . "' AND eigenaar='" . $customer_id . "'");
  }

  if (!empty($row_lance)) {
    // Parse result
    $result = $row_lance[0];
  }

  return $result;
}

/**
 *  Store new lance
 *
 *  Inputs:
 *  - lance_array:           Array containing all lance info
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_lance($lance_array) {
  // Initial return value
  $result = 0;

  // Query storing new lance
  $query  = "INSERT INTO zkl (parent,serienr, fabrieksnr, productie, pcb_versie, mech_versie, ";
  $query .= "imei, idcode, telefoonnr, sim, puk, pin, tz, onderhoud, tcp_server, sms_server, sms_mo, gprs_apn, lans_status,";
  $query .= "sw3000_onderhoud, eigenaar, gebruiker, capabilities, i18n, rtstatus, opmerking, device) VALUES (";
  if (strlen($lance_array['parent'])) {
    $query .=                $lance_array['parent']            . ",";
  }
  else {
    $query .= "NULL,";
  }
  $query .= "'" . specialchars($lance_array['serienr'])           . "',";
  $query .= "'" . specialchars($lance_array['fabrieksnr'])        . "',";
  $query .= "'" . specialchars($lance_array['productie'])         . "',";
  $query .= "'" . specialchars($lance_array['pcb_versie'])        . "',";
  $query .= "'" . specialchars($lance_array['mech_versie'])       . "',";
  if (strlen($lance_array['imei'])) {
    $query .= "'" . specialchars($lance_array['imei'])            . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['idcode'])) {
    $query .= "'" . specialchars($lance_array['idcode'])          . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['telefoonnr'])) {
    $query .= "'" . specialchars($lance_array['telefoonnr'])      . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['sim'])) {
    $query .= "'" . specialchars($lance_array['sim'])             . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['puk'])) {
    $query .= "'" . specialchars($lance_array['puk'])             . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['pin'])) {
    $query .= "'" . specialchars($lance_array['pin'])             . "',";
  }
  else {
    $query .= "NULL,";
  }
  $query .= "'" . ($lance_array['tz'])               . "',";
  if (strlen($lance_array['onderhoud'])) {
    $query .= "'" . ($lance_array['onderhoud'])      . "',";
  }
  else {
    $query .= "NULL,";
  }

  if (strlen($lance_array['tcp_server'])) {
    $query .= "'" . ($lance_array['tcp_server'])      . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['sms_server'])) {
    $query .= "'" . ($lance_array['sms_server'])      . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['sms_mo'])) {
    $query .= "'" . ($lance_array['sms_mo'])      . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['gprs_apn'])) {
    $query .= "'" . ($lance_array['gprs_apn'])      . "',";
  }
  else {
    $query .= "NULL,";
  }
  $query .= "'" . ($lance_array['lans_status'])      . "',";
  if (strlen($lance_array['sw3000_onderhoud'])) {
    $query .= "'" . ($lance_array['sw3000_onderhoud']) . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['eigenaar'])) {
    $query .= "'" . ($lance_array['eigenaar'])       . "',";
  }
  else {
    $query .= "NULL,";
  }
  // Owner null => can only be changed on the marketplace
  $query .= "NULL,";
  $query .= "'" . ($lance_array['capabilities'])     . "',";
  $query .= "'" . ($lance_array['i18n'])             . "',";
  if (strlen($lance_array['rtstatus'])) {
    $query .= "'" . ($lance_array['rtstatus'])       . "',";
  }
  else {
    $query .= "NULL,";
  }
  $query .= "'" . specialchars($lance_array['opmerking'])         . "',";
  $query .= "'" . ($lance_array['device'])           . "')";

  if (db_store_data($query)) {
    // Get lance id
    $lance = db_fetch_lance($lance_array['serienr'], $lance_array['eigenaar']);

    // Update log_versienummber and log_zkl
    db_update_lance_version($lance['id'], "");

    // Log user-lance action
    db_log_user_lance($lance['id'], "menu:lansen:nieuw", serialize($lance_array));

    // Result OK
    $result = 1;
  }

  return $result;
}

/**
 *  Update exisiting lance
 *
 *  Inputs:
 *  - lance_array:          Array containing all lance info
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_update_lance($lance_array) {
  // Initial return value
  $result = 0;

  // Get semaphore
  $sema_id = db_sema_acquire(RemoveExtension(basename(__FILE__)) . "_" . $lance_array['id']);

  // Retrieve original data => needed for log_versienummer
  $orig_data = db_fetch_lance($lance_array['id'], "", 1);

  // Query updating lance
  $query  = "UPDATE zkl SET ";
  if (strlen($lance_array['parent'])) {
    $query .= "parent="           . $lance_array['parent']                     . ",";
  }
  else {
    $query .= "parent=NULL,";
  }
  $query .= "serienr='"           . specialchars($lance_array['serienr'])         . "',";
  $query .= "fabrieksnr='"        . specialchars($lance_array['fabrieksnr'])      . "',";
  $query .= "productie='"         . specialchars($lance_array['productie'])       . "',";
  $query .= "pcb_versie='"        . specialchars($lance_array['pcb_versie'])      . "',";
  $query .= "mech_versie='"       . specialchars($lance_array['mech_versie'])     . "',";
  if (strlen($lance_array['imei'])) {
    $query .= "imei='"               . specialchars($lance_array['imei'])         . "',";
  }
  else {
    $query .= "imei=NULL,";
  }
  if (strlen($lance_array['idcode'])) {
    $query .= "idcode='"          . specialchars($lance_array['idcode'])          . "',";
  }
  else {
    $query .= "idcode=NULL,";
  }
  if (strlen($lance_array['telefoonnr'])) {
    $query .= "telefoonnr='"      . specialchars($lance_array['telefoonnr'])      . "',";
  }
  else {
    $query .= "telefoonnr=NULL,";
  }
  if (strlen($lance_array['rtstatus'])) {
    $query .= "rtstatus='"      . specialchars($lance_array['rtstatus'])          . "',";
  }
  else {
    $query .= "rtstatus=NULL,";
  }
  if (strlen($lance_array['sim'])) {
    $query .= "sim='"             . specialchars($lance_array['sim'])             . "',";
  }
  else $query .= "sim=NULL,";
  if (strlen($lance_array['puk'])) {
    $query .= "puk='"             . specialchars($lance_array['puk'])             . "',";
  }
  else $query .= "puk=NULL,";
  if (strlen($lance_array['pin'])) {
    $query .= "pin='"             . specialchars($lance_array['pin'])             . "',";
  }
  else $query .= "pin=NULL,";
  $query .= "tz='"                . specialchars($lance_array['tz'])              . "',";
  if (strlen($lance_array['onderhoud'])) {
    $query .= "onderhoud='"       . ($lance_array['onderhoud'])      . "',";
  }
  else {
    $query .= "onderhoud=NULL,";
  }
  if (strlen($lance_array['tcp_server'])) {
    $query .= "tcp_server='"       . ($lance_array['tcp_server'])      . "',";
  }
  else {
    $query .= "tcp_server=NULL,";
  }
  if (strlen($lance_array['sms_server'])) {
    $query .= "sms_server='"       . ($lance_array['sms_server'])      . "',";
  }
  else {
    $query .= "sms_server=NULL,";
  }
  if (strlen($lance_array['sms_mo'])) {
    $query .= "sms_mo='"       . ($lance_array['sms_mo'])      . "',";
  }
  else {
    $query .= "sms_mo=NULL,";
  }
  if (strlen($lance_array['gprs_apn'])) {
    $query .= "gprs_apn='"       . ($lance_array['gprs_apn'])      . "',";
  }
  else {
    $query .= "gprs_apn=NULL,";
  }
  if (strlen($lance_array['sw3000_onderhoud'])) {
    $query .= "sw3000_onderhoud='" . ($lance_array['sw3000_onderhoud']). "',";
  }
  else {
    $query .= "sw3000_onderhoud=NULL,";
  }
  if (strlen($lance_array['eigenaar'])) {
    $query .= "eigenaar='"       . ($lance_array['eigenaar'])        . "',";
  }
  else {
    $query .= "eigenaar=NULL,";
  }
  // Change user when owner has been changed and user equals owner
  if (($orig_data['eigenaar'] == $orig_data['gebruiker']) && ($orig_data['eigenaar'] != $lance_array['eigenaar'])) {
    $query .= "gebruiker=NULL,";
  }
  $query .= "i18n='"              . ($lance_array['i18n'])           . "',";
  $query .= "opmerking='"         . specialchars($lance_array['opmerking'])       . "',";
  $query .= "capabilities='"      . ($lance_array['capabilities'])   . "',";
  $query .= "device='"            . ($lance_array['device'])         . "' ";
  $query .= "WHERE id='" .          ($lance_array['id'])             . "'";

  if (db_store_data($query)) {
    // Result OK
    $result = 1;
  }

  // Releas sema
  db_sema_release($sema_id);

  if ($result) {
    // Initial value
    $changed = FALSE;

    // Get new data
    $new_data = db_fetch_lance($lance_array['id'], "", 1);

    // Changed?
    if ((is_array($orig_data)) && (is_array($new_data))) {
      foreach($orig_data as $key => $item) {
        $changed = ($new_data[$key] != $item) ? TRUE : $changed;
      }
    }

    if ($changed) {
      // Update log_versienummer
      db_update_lance_version($lance_array['id'], $orig_data);


      // Log user-lance action
      db_log_user_lance($lance_array['id'], "menu:lansen:wijzigen", serialize($lance_array));
    }
  }

  return $result;
}

/**
 *  Update lance status
 *
 *  Inputs:
 *  - dbid                  Database identifier for the lance
 *  - status_di             Optional new value for 'lans_status'
 *  - status_cust           Optional new value for 'klant_status'
 *
 *  Returns                 Success (TRUE) or failure (FALSE)
 *
 *  NB: no syntax checking on the input as the string always comes from a seleciton
 *  box or something similar (i.e. it is always valid)
 */
function db_update_lance_status($dbid, $status_di = FALSE, $status_cust = FALSE)
{
  // Retrieve original data to check if the status has changed
  // (no update needed when there is no change)
  $orig_data = db_fetch_lance($dbid, "", 1);

  // don't update the status field(s) when they are not changed
  if( $status_cust && $orig_data['klant_status'] == $status_cust ) $status_cust = FALSE;
  if( $status_di   && $orig_data['lans_status']  == $status_di   ) $status_di   = FALSE;
  
  $sep    = "UPDATE zkl";
  $sep   .= " SET";
  $query  = "";
  if( $status_di ) {
    $query .= $sep . " lans_status='" . $status_di . "'";
    $sep    = ",";
    if( $status_di == 'service' ) {
      // Remove next service date
      $query .= $sep . "onderhoud=NULL";
    }
  }
  if( $status_cust ) {
    $query .= $sep . " klant_status='" . $status_cust . "'";
    $sep    = ",";
  }
  
  // update needed?
  if( $query ) {
    // complete the query and run it
    $query .= " WHERE id=" . $dbid;
    $result = db_store_data($query);

    // Change to service => remove all production documentation (calibration/endcontrol/certificate)
    if( $result && $status_di == 'service' ) {
      db_clear_lance_reports($dbid);
    }
    
    // Record change in status in log_versienummer
    db_update_lance_version($dbid, $orig_data);
    
    return $result;
  }
  else return FALSE;
}


/**
 *  Update lance service date
 *
 *  Inputs:
 *  - dbid                  Database identifier for the lance
 *  - date                  Date for the next service, or NULL or FALSE to clear
 */
function db_update_lance_service_date($dbid, $date)
{
  $query = "UPDATE zkl SET onderhoud=";
  if( $date ) $query .= "'" . $date . "'";
  else $query .= "NULL";
  $query .= " WHERE id=" . $dbid;
  return db_store_data($query);
}


/**
 *  Update lance syncdb fields
 *
 *  Inputs:
 *  - id:                   database id
 *  - syncdb:               Sync db items
 *
 *  Returns:                Success (TRUE) or failure
 */
function db_update_lance_syncdb($id, $syncdb) {
  // Query updating lance syncdb
  $query  = "UPDATE zkl SET ";
  $query .= "syncdb=concat(syncdb,'," . ($syncdb) . "') ";
  $query .= "WHERE id='" . ($id) . "'";

  return db_store_data($query);
}


/**
 *  Delete lance
 *
 *  Inputs:
 *  - lance_id:            lance database id
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_delete_lance($lance_id) {
  // Initial return value
  $result = 0;

  // Start transaction
  db_start_transaction();

  // Delete zkl
  $query  = "DELETE FROM zkl WHERE ID='" . $lance_id . "'";
  db_store_data($query);

  // Commit transaction
  if (db_commit_transaction()) {
    // Log user-zkl action
    db_log_user_lance($lance_id, "menu:lansen:verwijderen");

    // Parse result
    $result = 1;
  }

  return $result;
}


/**
 *  Delete lance log files (called by the garbage collector)
 *  Inputs:
 *  - timeout:             Max time it may take
 *  - limit:               Max delete items each time
 *
 *  Return:                Array containing time and affected rows
 */
function db_delete_lance_logs($timeout, $limit = 100) {
  GLOBAL $_PAGE_INFO;

  // Initial values
  $affected = 0;

  // Get start time;
  $start = microtime_float();

  // All log_x tables
  $log_x = array("log_meting",
                 "log_startup",
                 "log_metingstart",
                 "log_kortsluiting",
                 "log_relais",
                 "log_calibratiestart",
                 "log_calibratie",
                 "log_samples",
                 "log_meting_detail",
                 "log_batterij",
                 "log_batterijstatus",
                 "log_knop",
                 "log_peripheral",
                 "log_sms",
                 "log_aft",
                 "log_gps",
                 "log_time",
                 "log_temp",
                 "log_trilling");

  // All log tables
  $log_table = array("zkl_calibratie",
                     "zkl_eindcontrole",
                     "zkl_alarmnr",
                     "zkl_ontwerp",
                     "log_zkl",
                     "zkl_logckpnt",
                     "zkl_logfile",
                     "zkl_documenten",
                     "device_documenten",
                     "log_versienummer",
                     "log_realtime",
                     "log_tcp",
                     "log_gebruiker_zkl");

  //
  // Initial values
  //
  $zkl_ids = "";
  $zkl_max = "";
  $end     = $start;
  $first   = TRUE;

  //
  // Get all zkl ids => Create array and determine max value
  //
  $zkl_max = db_fetch_data("SELECT max(id) as id FROM zkl");
  $rows    = db_fetch_data("SELECT id FROM zkl ORDER BY id ASC");

  if (is_array($rows)) {
    $zkl_ids = "(";
    for($i=0; $i <= $zkl_max[0]['id']; $i++) {
      $found = FALSE;
      for($k=0; (($k < sizeof($rows)) && (!$found)); $k++) {
        $found = ($rows[$k]['id'] == $i) ? TRUE : $found;
      }

      if (!$found) {
        if (!$first) {
          $zkl_ids .= ",";
        }

        // Clear flag
        $first = FALSE;

        // Add id
        $zkl_ids .= $i;
      }
    }
    $zkl_ids .= ")";
  }

  // Entries found
  if (!$first) {
    do {
      // Initial value
      $finished = 1;

      for ($j = 0; $j < 2; $j++) {
        //
        // Define active table
        //
        if ($j) {
          // Select log tables
          $active_table = "log_table";
          $tables = $log_table;
        }
        else {
          // Select log_x tables
          $active_table = "log_x";
          $tables = $log_x;

          // Get min id from log_zkl
          $log_zkl_min_id = db_fetch_data("SELECT MIN(id) as id FROM log_zkl");
        }

        //
        // all log files
        //
        foreach($tables as $table) {
          //
          // Delete entries from table
          //
          if ($active_table == "log_x") {
            //
            // Handle log_x tables
            //
            $query  = "SELECT log_zkl.id FROM " . $table. ",log_zkl ";
            $query .= "USE INDEX(log_zkl_id) ";
            $query .= "WHERE " . $table . ".id=log_zkl.id AND log_zkl.zkl <= " . $zkl_max[0]['id'] . " AND log_zkl.zkl IN " . $zkl_ids . " LIMIT " . ($limit - $affected) . "";
            $rows   = db_fetch_data($query);

            if (is_array($rows)) {
              foreach($rows as $row) {
                // Delete from log_x
                $query  = "DELETE FROM " . $table . " WHERE id='" . $row['id'] . "'";
                db_store_data($query);

                // Delete from log_zkl
                $query  = "DELETE FROM log_zkl WHERE id='" . $row['id'] . "'";
                db_store_data($query);
              }
            }

            // Finished?
            $affected += (is_array($rows)) ? (sizeof($rows)) : 0;
            $finished  = ($affected >= $limit) ? 1 : 0;

            if (!$finished) {
              //
              // Also delete non log_zkl linked entries
              //
              $query  = "SELECT " . $table . ".id FROM " . $table . " ";
              $query .= "WHERE " . $table . ".id < " . $log_zkl_min_id[0]['id'] . " LIMIT " . ($limit - $affected) . "";
              $rows   = db_fetch_data($query);

              if (is_array($rows)) {
                foreach($rows as $row) {
                  // Delete from log_x
                  $query  = "DELETE FROM " . $table . " WHERE id='" . $row['id'] . "'";
                  db_store_data($query);
                }
              }

              // Finished?
              $affected += (is_array($rows)) ? (sizeof($rows)) : 0;
              $finished  = ($affected >= $limit) ? 1 : 0;
            }
          }
          else {
            //
            // Handle log tables (with and without id)
            //
            $fields = db_fetch_set($table, "id");

            // Table contains id field?
            if (is_array($fields)) {
              $query  = "SELECT " . $table . ".id FROM " . $table;
              switch($table) {
                case "log_zkl":
                    $query .= " USE INDEX(log_zkl_id) ";
                    break;
                case "log_realtime":
                    $query .= " USE INDEX(log_realtime_id_tijd) ";
                    break;
                default:
                    break;
              }
              $query .= " WHERE ";

              // Capture exception => device_documenten has no zkl but device field
              switch($table)
              {
                case "device_documenten":
                  $query .= "device ";
                  break;
                default:
                  $query .= "zkl ";
                  break;
              }
              $query .= "<= " . $zkl_max[0]['id'] . " AND ";

              // Capture exception => device_documenten has no zkl but device field
              switch($table)
              {
                case "device_documenten":
                  $query .= "device ";
                  break;
                default:
                  $query .= "zkl ";
                  break;
              }
              $query .= "IN " . $zkl_ids . " LIMIT " . ($limit - $affected) . "";
              $rows = db_store_data($query);

              if (is_array($rows)) {
                foreach($rows as $row) {
                  // Delete from log tables
                  $query  = "DELETE FROM " . $table . " WHERE id='" . $row['id'] . "'";
                  db_store_data($query);
                }
              }

              // Finished?
              $affected += (is_array($rows)) ? (sizeof($rows)) : 0;
              $finished  = ($affected >= $limit) ? 1 : 0;
            }
            else {
              $query  = "DELETE FROM " . $table . " WHERE ";
              $query .= "ZKL <= " . $zkl_max[0]['id'] . " AND ";
              $query .= "ZKL IN " . $zkl_ids . " LIMIT " . ($limit - $affected) . "";
              db_store_data($query);

              // Finished?
              $affected += mysql_affected_rows($_PAGE_INFO['mysql_db_resource']['default']);
              $finished  = ($affected >= $limit) ? 1 : 0;
            }
          }
        }
      }

      // Get end time
      $end = microtime_float();
    } while((!$finished) && (($end - $start) < $timeout));
  }

  // Return info
  return array(time => ($end - $start), affected => $affected);
}


/**
 *  Update exisiting lance user (and timezone)
 *
 *  Inputs:
 *  - lance_id:             Lance database id
 *  - user_id:              User database id
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_update_lance_user($lance_id, $user_id) {
  // Initial return value
  $result = 0;

  // Retrieve original data => needed for log_versienummer
  $orig_data = db_fetch_lance($lance_id, "", 1);

  // Determine timezone
  if (_is_null($user_id)) {
    // timezone owner
    $cust = db_fetch_customer($orig_data['eigenaar'], 1);
  }
  else {
    // timezone user
    $cust = db_fetch_customer($user_id, 1);
  }

  // Query updating lance
  $query  = "UPDATE zkl SET gebruiker";
  if ($user_id == "NULL") {
    $query .= "=NULL ";
  }
  else {
    $query .= "='" . $user_id . "' ";
  }
  $query .= ",tz='" . $cust['tz'] . "' ";
  $query .= "WHERE id='" . ($lance_id) . "'";

  if (db_store_data($query)) {
    // Result OK
    $result = 1;
  }

  if ($result) {
    // Check if user has been changed
    if ((($orig_data['gebruiker'] != $user_id) && (!($user_id == "NULL" && _is_null($orig_data['gebruiker'])))) ||
        ($orig_data['tz'] != $cust['tz'])) {
      // Update log_versienummer
      db_update_lance_version($lance_id, $orig_data);

      // Log user-lance action
      db_log_user_lance($lance_id, "menu:lansen:wijzigen", "change user: " . $user_id);
    }
  }

  return $result;
}


/**
 *  Check device capabilities 
 *
 * Inputs:
 * - id                     Device id _or_ full array
 * - capabilities           Capabilities/rt status display array  (gps AND gsm, etc), multi array => OR function
 * 
 *  Return:                 1 (TRUE)/ 0 (FALSE) or list of all supported device types
 */
function db_check_lance_capabilities($id, $capabilities, $set = "capabilities") {
  if( is_array($id) ) {
    // full array; info is present
    $device_capabilities = explode(",", $id[$set]);
  }
  else {
    // get device capabilities from the database
    $query = "SELECT " . $set . " FROM zkl WHERE id=" . $id;
    $device_capabilities = db_fetch_data($query);
    if( $device_capabilities ) {
      // flatten the array on success and convert to an array of capabilities
      $device_capabilities = explode(",", $device_capabilities[0][$set]);
    }
    else {
      // device not found
      return FALSE;
    }
  }
  
  if (is_array($capabilities)) {
    foreach($capabilities as $item) {
      if (is_array($item)) {
        // at least one item in the array $item must be present ('or' function)
        $result = FALSE;
        foreach( $item as $or_item ) {
          if( !in_array($or_item, $device_capabilities) ) $result = TRUE;
        }
        if( !$result ) return FALSE;
      }
      else {
        // item must be present ('and' function)
        if( !in_array($item, $device_capabilities) ) {
          return FALSE;
        }
      }
    }  
  }
  else {
    return in_array($capabilities, $device_capabilities);
  }  
}  


/**
 *  Store new calibration info
 *
 *  Inputs:
 *  - lance_array:          Array containing lance info
 *
 *  Return:                 Database ID (non-zero positive integer) or FALSE on error
 */
function db_store_lance_calibration($lance_array) {
  global $_PAGE_INFO;

  // Initial return value
  $result = FALSE;

  // Determine device type
  $type  = db_fetch_system_devices($_PAGE_INFO['i18n'],$lance_array['device']);

  // Query storing new project
  $query  = "INSERT INTO zkl_calibratie (zkl, gebruiker, datum, algemene_status, lans_status, sd_card";
  if ($type[0]['nr_batterijen']) {
    $query .= ",batterijen";
  }
  if ((db_check_system_device_capabilities($lance_array['device'],array("led"))) && ($type[0]['nr_batterijen'])) {
    $query .= ",led_batt_low";
  }
  if (db_check_system_device_capabilities($lance_array['device'],array("meting"))) {
    $query .= ",b_a,freq,weerstand,weerstand_cert,weerstand_ok,detectie,meetdraad_links,meetdraad_rechts,kortsluitdraad";
  }
  if (db_check_system_device_capabilities($lance_array['device'],array("meting","led"))) {
    $query .= ",led_meting";
  }
  if (db_check_system_device_capabilities($lance_array['device'],array(array("gsm","gprs")))) {
    $query .= ",gsm_gprs";
  }
  if (db_check_system_device_capabilities($lance_array['device'],array(array("sleutelschakelaar")))) {
    $query .= ",sleutelschakelaar";
  }
  if (db_check_system_device_capabilities($lance_array['device'],"gps")) {
    $query .= ",gps";
  }
  $query .= ",commentaar";
  $query .= ") VALUES (";
  $query .= "' " . ($lance_array['id'])              . "'";
  $query .= ",'" . ($lance_array['gebruiker'])       . "'";
  $query .= ",'" . ($lance_array['datum'])           . "'";
  $query .= ",'" . ($lance_array['algemene_status']) . "'";
  $query .= ",'" . ($lance_array['lans_status'])     . "'";
  if (isset($lance_array['sd_card'])) {
    $query .= ",'ok'";
  }
  else {
    $query .= ",'nok'";
  }
  if ($type[0]['nr_batterijen']) {
    $sum = 0;
    for($i = 0 ; $i < $type[0]['nr_batterijen']; $i++) {
      if (isset($lance_array["batt" . $i])) {
        $sum += pow(2, $i);
      }
    }
    $query .= ",'" . $sum . "'";
  }
  if ((db_check_system_device_capabilities($lance_array['device'],array("led"))) && ($type[0]['nr_batterijen'])) {
    if (isset($lance_array['led_batt_low'])) {
      $query .= ",'ok'";
    }
    else {
      $query .= ",'nok'";
    }
  }
  if (db_check_system_device_capabilities($lance_array['device'],array("meting"))) {
    $query .= ",'" . ($lance_array['b_a'])              . "'";
    $query .= ",'" . ($lance_array['freq'])             . "'";
    $query .= ",'" . specialchars($lance_array['weerstand'])         . "'";
    $query .= ",'" . specialchars($lance_array['weerstand_cert'])    . "'";
    $query .= ",'" . ($lance_array['weerstand_ok'])     . "'";
    $query .= ",'" . ($lance_array['detectie'])         . "'";
    $query .= ",'" . specialchars($lance_array['meetdraad_links'])   . "'";
    $query .= ",'" . specialchars($lance_array['meetdraad_rechts']) . "'";
    $query .= ",'" . specialchars($lance_array['kortsluitdraad'])    . "'";
  }
  if (db_check_system_device_capabilities($lance_array['device'],array("meting","led"))) {
    if (isset($lance_array['led_meting'])) {
      $query .= ",'ok'";
    }
    else {
      $query .= ",'nok'";
    }
  }
  if (db_check_system_device_capabilities($lance_array['device'],array(array("gsm","gprs")))) {
    if (isset($lance_array['gsm_gprs'])) {
      $query .= ",'ok'";
    }
    else {
      $query .= ",'nok'";
    }
  }
  if (db_check_system_device_capabilities($lance_array['device'],array("sleutelschakelaar"))) {
    if (isset($lance_array['sleutelschakelaar'])) {
      $query .= ",'ok'";
    }
    else {
      $query .= ",'nok'";
    }
  }
  if (db_check_system_device_capabilities($lance_array['device'],"gps")) {
    if (isset($lance_array['gps'])) {
      $query .= ",'ok'";
    }
    else {
      $query .= ",'nok'";
    }
  }
  if (strlen($lance_array['commentaar'])) {
    $query .= ",'" . specialchars($lance_array['commentaar'])      . "'";
  }
  else {
    $query .= ",NULL";
  }
  $query .= ")";

  if (db_store_data($query)) {
    // Log user-lance action
    db_log_user_lance($lance_array['id'], "menu:lansen:calibratie", serialize($lance_array));

    // Parse result
    $result = db_fetch_last_id();
  }

  return $result;
}


/**
 *  Check calibration data
 *
 *  Inputs:
 *  - lance               Lance info (full array)
 *  - device_info         Device type info (full array)
 *  - calib_data          Calibration data
 *  - not_filled          Returns an array of items not (correctly) filled in (passed by reference)
 *
 *  Returns:
 *  TRUE on success
 *  FALSE on failure; the array 'not_filled' contains the incorrect items
 */
function db_check_calibration($lance, $device_info, $calib_data, &$not_filled)
{
  // Check if everything was filled in
  $valid   = TRUE;

  // Expand the capabilities to an array
  if( !is_array($device_info['capabilities']) ) {
    $device_info['capabilities'] = _explode(",", $device_info['capabilities']);
  }

  // Check batteries
  for($i = 0; $i < $device_info['nr_batterijen']; $i++) {
    if( !isset($calib_data["batt" . $i]) ) {
      $valid = FALSE;
      $not_filled[] = "batt" . $i;
    }
  }

  // Check led Low battery led
  if( in_array("led", $device_info['capabilities']) && $device_info['nr_batterijen'] ) {
    if( !isset($calib_data['led_batt_low']) ) {
      $valid = FALSE;
      $not_filled[] = "led_batt_low";
    }
  }

  // Check GPRS/GSM
  if( in_array("gprs", $device_info['capabilities']) || in_array("gsm", $device_info['capabilities']) ) {
    if( !$calib_data['device_ready'] ) {
      $valid = FALSE;
      $not_filled[] = "device_ready";
    }

    if( !$calib_data['gsm_gprs'] ) {
      $valid = FALSE;
      $not_filled[] = "gsm_gprs";
    }
  }

  // Check GPS
  if( in_array("gps", $device_info['capabilities']) ) {
    if( !$calib_data['gps'] ) {
      $valid = FALSE;
      $not_filled[] = "gps";
    }
  }

  // Check key switch
  if( in_array("sleutelschakelaar", $device_info['capabilities']) ) {
    if( !$calib_data['sleutelschakelaar'] ) {
      $valid = FALSE;
      $not_filled[] = "sleutelschakelaar";
    }
  }

  // Check storage medium
  if( !isset($calib_data['sd_card']) ) {
    $valid = FALSE;
    $not_filled[] = "sd_card";
  }

  // Check measurement values
  if( in_array("meting", $device_info['capabilities']) ) {
    if( !is_numeric($calib_data['b_a']) ) {
      $valid = FALSE;
      $not_filled[] = "b_a";
    }
    if( !is_numeric($calib_data['freq']) ) {
      $valid = FALSE;
      $not_filled[] = "freq";
    }

    if( !is_numeric($calib_data['weerstand'])  ) {
      $valid  = FALSE;
      $not_filled[] = "weerstand";
    }
    else if( !valid_value($calib_data['weerstand'],1,6,1) ) {
      $valid = FALSE;
      $not_filled[] = "weerstand: invalid value";
    }

    if( !is_numeric($calib_data['meetdraad_rechts']) ) {
      $valid  = FALSE;
      $not_filled[] = "meetdraad_rechts";
    }
    else if( !valid_value($calib_data['meetdraad_rechts'],1,6,1) ) {
      $valid = FALSE;
      $not_filled[] = "meetdraad_rechts: invalid value";
    }

    if( !is_numeric($calib_data['meetdraad_links'])) {
      $valid  = FALSE;
      $not_filled[] = "meetdraad_links";
    }
    else if( !valid_value($calib_data['meetdraad_links'],1,6,1) ) {
      $valid = FALSE;
      $not_filled[] = "meetdraad_links: invalid value";
    }

    if( !is_numeric($calib_data['kortsluitdraad'])) {
      $valid  = FALSE;
      $not_filled[] = "kortsluitdraad";
    }
    else if( !valid_value($calib_data['kortsluitdraad'],1,6,1) ) {
      $valid = FALSE;
      $not_filled[] = "kortsluitdraad: invalid value";
    }

    if( !is_numeric($calib_data['detectie']) ) {
      $valid  = FALSE;
      $not_filled[] = "detectie";
    }
    else if( !valid_value($calib_data['detectie'],1,6,1) ) {
      $valid   = FALSE;
      $not_filled[] = "detectie: invalid value";
    }
    else if( $calib_data['detectie'] > $device_info['weerstand_ok'] ) {
      $valid   = FALSE;
      $not_filled[] = "detectie: too low";
    }
  }

  // Check measurement led
  if( in_array("meting", $device_info['capabilities']) || in_array("led", $device_info['capabilities']) ) {
    if( !isset($calib_data['led_meting'])  ) {
      $valid = FALSE;
      $not_filled[] = "led_meting";
    }
  }
  
  return $valid;
}



/**
 *  Valid calibration (non-expired!!)
 *
 *  Inputs:
 *  - db_entry        Database entry (zkl_calibratie)
 *  - date_check      must the date be checked
 *
 *  Return:             1(OK)/0(Error)
 */
function db_check_lance_valid_calibration($db_entry, $date_check = 0)
{
  global $_PAGE_INFO;

  $calib_info = db_fetch_lance_log_entry($db_entry, "zkl_calibratie");

  // Initial value
  $result = 0;

  // Valid calibration?:
  if ((is_array($calib_info)) && (!empty($calib_info))) {
    $result = ($calib_info[0]['algemene_status'] == "ok") ? 1 : 0;
  }

  if (($date_check) && ($result)) {
    $lance_info = db_fetch_lance($calib_info[0]['zkl'], "", 1);
    $type = db_fetch_system_devices($_PAGE_INFO['i18n'], $lance_info['device']);

    if (strlen($type[0]['calibratie_geldigheid'])) {
      $result = ((convert_datetime(date('Y-m-d H:i:s')) - convert_datetime($calib_info[0]['datum'])) <= ($type[0]['calibratie_geldigheid'] * 60)) ? 1 : 0;
    }
  }

  return $result;
}

/**
 *  Fetch calibration/endcontrol_certificate report info
 *
 *  Inputs:
 *  - lance_id          Lance db id
 *  - last              Only the last report
 *  - Calibration       Calibration report(1) / Endcontrol_certificate (0)
 *  - i18n              Internationalisatie
 *
 *  Return:             array containing report info (name and public)
 */
function db_fetch_lance_reports($lance_id, $last = 1, $calibration = 1, $i18n = "nl")
{
  // Initial return value
  $result = "";

  // Query
  if ($calibration) {
    $query = "SELECT id, omschrijving, filename, public FROM zkl_documenten WHERE zkl='" . $lance_id . "' AND filename LIKE '%-%-elec-" . $i18n . "-%' ORDER BY id";
  }
  else {
    $query = "SELECT id, omschrijving, filename, public FROM zkl_documenten WHERE zkl='" . $lance_id . "' AND filename LIKE '%-XXXX-cert-" . $i18n . "-%' ORDER BY id";
  }

  if ($last) {
    $query .= " DESC LIMIT 1";
  }
  else {
    $query .= " ASC";
  }

  // Fetch data
  $log_info = db_fetch_data($query);

  // Parse into result
  if (!empty($log_info)) {
    // Parse result
    $result = $log_info;
  }

  return $result;
}


/**
 *  Store new end control
 *
 *  Inputs:
 *  - lance_array:          Array containing lance info
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_lance_endcontrol($lance_array) {
  global $_PAGE_INFO;

  // Initial return value
  $result = 0;

  // Determine device type
  $type  = db_fetch_system_devices($_PAGE_INFO['i18n'],$lance_array['device']);

  // Query storing new project
  $query  = "INSERT INTO zkl_eindcontrole (zkl, gebruiker, datum ";
  if (db_check_system_device_capabilities($lance_array['device'],array("meting","led"))) {
    $query .= ",led_meting";
  }
  if (db_check_system_device_capabilities($lance_array['device'],array("afsluitbaar"))) {
    $query .= ",arretering, afsluitbaar";
  }
  $query .= ",commentaar";
  $query .= ") VALUES (";
  $query .= "' " . ($lance_array['id'])              . "'";
  $query .= ",'" . ($lance_array['gebruiker'])       . "'";
  $query .= ",'" . ($lance_array['datum'])           . "'";
  if (db_check_system_device_capabilities($lance_array['device'],array("meting","led"))) {
    if (isset($lance_array['led_meting'])) {
      $query .= ",'ok'";
    }
    else {
      $query .= ",'nok'";
    }
  }
  if (db_check_system_device_capabilities($lance_array['device'],array("afsluitbaar"))) {
    if (isset($lance_array['arretering'])) {
      $query .= ",'ok'";
    }
    else {
      $query .= ",'nok'";
    }
    if (isset($lance_array['afsluitbaar'])) {
      $query .= ",'ok'";
    }
    else {
      $query .= ",'nok'";
    }

  }
  if (strlen($lance_array['commentaar'])) {
    $query .= ",'" . specialchars($lance_array['commentaar'])      . "'";
  }
  else {
    $query .= ",NULL";
  }
  $query .= ")";

  if (db_store_data($query)) {
    // Log user-lance action
    db_log_user_lance($lance_array['id'], "menu:lansen:eindcontrole", serialize($lance_array));

    // Parse result
    $result = 1;
  }

  return $result;
}

/**
 *  Clear all reports (remove valid flag)
 *
 *  Inputs:
 *  - lance_id          Lance db id
 *
 *  Return:             1 (Ok)/0 (Error)
 */
function db_clear_lance_reports($lance_id)
{
  // Query clear public flags
  $query  = "UPDATE zkl_documenten SET public='0' WHERE zkl='" . $lance_id . "'";

  if (db_store_data($query)) {
    // Result OK
    $result = 1;
  }

  return $result;
}


/**
 *  Store new alarm number
 *
 *  Inputs:
 *  - lance_array:          Array containing lance info
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_lance_alarmnr($lance_array) {
  // Initial return value
  $result = 0;

  // Query storing new project
  $query  = "INSERT INTO zkl_alarmnr (zkl, alarmnr, servernr, begin, eind, begintijd, eindtijd, begindow, einddow) VALUES (";
  $query .= "'" . ($lance_array['zkl'])          . "',";
  $query .= "'" . specialchars($lance_array['alarmnr'])       . "',";
  $query .= "'" . ($lance_array['servernr'])     . "',";
  if (strlen($lance_array['begin'])) {
    $query .= "'" . ($lance_array['begin'])      . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['eind'])) {
    $query .= "'" . ($lance_array['eind'])       . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['begintijd'])) {
    $query .= "'" . ($lance_array['begintijd'])  . "',";
  }
  else {
    $query .= "NULL,";
  }
  if (strlen($lance_array['eindtijd'])) {
    $query .= "'" . ($lance_array['eindtijd'])   . "',";
  }
  else {
    $query .= "NULL,";
  }
  if ($lance_array['begindow']) {
    $query .= "'" . ($lance_array['begindow'])   . "',";
  }
  else {
    $query .= "NULL,";
  }
  if ($lance_array['einddow']) {
    $query .= "'" . ($lance_array['einddow'])    . "')";
  }
  else {
    $query .= "NULL)";
  }

  if (db_store_data($query)) {
    // Log user-lance action
    db_log_user_lance($lance_array['zkl'], "menu:lansen:alarmnummers:wijzigen", serialize($lance_array));

    // Parse result
    $result = 1;
  }

  return $result;
}


/**
 *  Update exisiting lance alarm number
 *
 *  Inputs:
 *  - lance_array:          Array containing all lance info
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_update_lance_alarmnr($lance_array) {
  // Initial return value
  $result = 0;

  // Query updating lance
  $query  = "UPDATE zkl_alarmnr SET ";
  $query .= "alarmnr='"       . specialchars($lance_array['alarmnr'])     . "',";
  $query .= "servernr='"      . ($lance_array['servernr'])   . "',";
  if (strlen($lance_array['begin'])) {
    $query .= "begin='"       . ($lance_array['begin'])      . "',";
  }
  else {
    $query .= "begin=NULL,";
  }
  if (strlen($lance_array['eind'])) {
    $query .= "eind='"        . ($lance_array['eind'])       . "',";
  }
  else {
    $query .= "eind=NULL,";
  }
  if (strlen($lance_array['begintijd'])) {
    $query .= "begintijd='"   . ($lance_array['begintijd'])  . "',";
  }
  else {
    $query .= "begintijd=NULL,";
  }
  if (strlen($lance_array['eindtijd'])) {
    $query .= "eindtijd='"    . ($lance_array['eindtijd'])   . "',";
  }
  else {
    $query .= "eindtijd=NULL,";
  }
  if ($lance_array['begindow']) {
    $query .= "begindow='"    . ($lance_array['begindow'])   . "',";
  }
  else {
    $query .= "begindow=NULL,";
  }
  if ($lance_array['einddow']) {
    $query .= "einddow='"     . ($lance_array['einddow'])    . "' ";
  }
  else {
    $query .= "einddow=NULL ";
  }
  $query .= "WHERE id='"      . ($lance_array['id'])         . "'";

  if (db_store_data($query)) {
    // Result OK
    $result = 1;
  }

  if ($result) {
    // Log user-lance action
    db_log_user_lance($lance_array['id'], "menu:lansen:alarmnummers:wijzigen", serialize($lance_array));
  }

  return $result;
}


/**
 *  Delete lance alarmnummer
 *
 *  Inputs:
 *  - alarmnmr_id:          alarm database id
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_delete_lance_alarmnr($alarmnmr_id) {
  // Initial return value
  $result = 0;

  // Start transaction
  db_start_transaction();

  // Delete alarm number
  $query  = "DELETE FROM zkl_alarmnr WHERE id='" . $alarmnmr_id . "'";
  db_store_data($query);

  // Commit transaction
  if (db_commit_transaction()) {
    // Log user-zkl action
    db_log_user_lance($zkl_id, "menu:lansen:alarmnummers:wijzigen");

    // Parse result
    $result = 1;
  }

  return $result;
}


/**
 *  Color assignment?
 *
 *  Inputs:
 *  - lance_id:             lance database id
 *
 *  Return:                 color
 */
function db_check_lance_color($lance_id, $service = 1, $rented = 1, $status = 1, $double = 1, $customer = "", $lance_array = "") {
  global $_PAGE_INFO;

  // Initial value
  $color = "";

  // Find lance info
  if (is_array($lance_array)) {
    $lance = $lance_array;
  }
  else {
    $lance = db_fetch_lance($lance_id, "", 1);
  }

  // Get color => right order!
  if (($service) && (($lance['lans_status'] == "service") || ($lance['lans_status'] == "productie") || ($lance['lans_status'] == "winkel"))) {
    // Service/expedition/production color?
    $color = "service";
  }
  else if (($rented) && ((($lance['gebruiker'] != $lance['eigenaar']) && (strlen($lance['gebruiker']))) && ($lance['eigenaar'] == $customer))) {
    // Rented color?
    $color = "rented";
  }
  else {
    if (($status) && ($lance['klant_status'] == "inactief")) {
      $color = "inactive";
    }
    else if ($double) {
      // Search all unfinshed projects
      $projects = db_fetch_lance_projects($lance['id'], 1, $_PAGE_INFO['login']['customer']['id']);

      if ((is_array($projects)) && (sizeof($projects) > 1)) {
        $color = "d_assigned_project";
      }
    }
  }

  return $color;
}


/**
 *  Store log_versienummer & update zkl_logfile
 *
 *  Inputs:
 *  - lance_id:             lance database id
 *  - orig_data:            Original data before update
 *
 *  Return:                 color
 */
function db_update_lance_version($lance_id, $orig_data) {
  global $_PAGE_INFO;

  // Initial values
  $skip_insert        = 0;

  // Get all fields
  $fields = db_fetch_set("log_versienummer");

  // Get lance info
  $lance = db_fetch_lance($lance_id, "", 1);

  if ((is_array($fields)) && (!empty($fields)) && (is_array($lance)) && (!empty($lance))) {
    // Query inserting new log_versienummer
    $query  = "INSERT INTO log_versienummer (";

    for($i = 0; $i < sizeof($fields); $i++) {
      $query .= $fields[$i];

      // Add comma?
      if (($i + 1) != sizeof($fields)) {
        $query .= ",";
      }
    }

    $query .= ") VALUES (";

    for($i = 0; $i < sizeof($fields); $i++) {
      if (!$skip_insert) {
        switch(strtolower($fields[$i]))
        {
          case "zkl":
              $query .= "'" . ($lance_id) . "'";
              break;
          case "gebruikersid":
              $query .= "'" . $_PAGE_INFO['login']['user']['id'] . "'";
              break;
          case "tijd":
              // Removed and replaced by t (time_t) value
              $query .= "'" . date('Y-m-d H:i:s') . "'";
              syslog(LOG_WARNING, "db_update_lance_version() still uses log_versienummer.tijd");
              break;
          case "t":
              $query .= "'" . convert_datetime(date('Y-m-d H:i:s')) . "'";
              break;
          case "imsi":
              $query .= "NULL";
              break;
          case "wijzigingen":
              $first = 1;
              $change_set = db_fetch_set("log_versienummer","wijzigingen");
              if (is_array($change_set)) {
                foreach($change_set as $item) {
                  // different then orig data
                  if (((!isset($orig_data[$item])) || ($lance[$item] != $orig_data[$item])) && ((!_is_null($lance[$item])) || (strlen($orig_data[$item])))) {
                    if ($first) {
                      $first = 0;
                      $query .= "'";
                    }
                    else {
                      $query .= ",";
                    }
                    $query .= $item;
                  }
                }
              }

              // Item added => otherwhise skip the insert
              if (!$first) {
                $query .= "'";
              }
              else {
                $skip_insert = 1;
              }
              break;
          default:
              if( (!_is_null($lance[$fields[$i]])) && ((!isset($orig_data[$fields[$i]])) || ($lance[$fields[$i]] != $orig_data[$fields[$i]] || ($orig_data['gprs_apn'] != $lance['gprs_apn']))) ) {
                $query .= "'" . specialchars($lance[$fields[$i]]) . "'";
              }
              else {
                $query .= "NULL";
              }
              break;
        }

        // Add comma?
        if (($i + 1) != sizeof($fields)) {
          $query .= ",";
        }
      }
    }

    $query .= ")";
  }

  if (!$skip_insert) {
    if (db_store_data($query)) {
      // Result OK
      $result = 1;
    }
  }
  else {
    // Result NOK
    $result = 0;
  }

  return $result;
}


/**
 *  Fetch log versionnumber
 *
 * inputs:
 * - lance_id               Lance database id
 * - changes                Specific changes
 * - Last                   Only last?
 *
 *  Return:                 Array containing log versienummer info
 */
function db_fetch_lance_versionnumber($lance_id, $changes, $last = FALSE)
{
  // Initial return value
  $result = "";

  // Fetch log log_versionnumber
  $query = "SELECT * FROM log_versienummer WHERE zkl='" . $lance_id . "' ";

  if (is_array($changes)) {
    foreach($changes as $item) {
      if (is_array($item)) {
        $query .= "AND (";
        for($i=0; $i<sizeof($item); $i++) {
          if ($i) {
            $query .= "OR ";
          }
          $query .= "FIND_IN_SET('" . ($item[$i]) . "',wijzigingen) > 0 ";
        }
        $query .= ") ";
      }
      else {
        $query .= " AND FIND_IN_SET('" . ($item) . "',wijzigingen) > 0 ";
      }
    }
  }
  else if (strlen($changes)) {
    $query .= " AND FIND_IN_SET('" . ($changes) . "',wijzigingen) > 0 ";
  }

  // only last?
  if ($last) {
    $query .= "order by t desc limit 1";
  }

  // Fetch log log_versionnumber
  $log_versionnumber = db_fetch_data($query);

  // Parse into result
  if (!empty($log_versionnumber)) {
    if ($last) {
      $result = $log_versionnumber[0];
    }
    else {
      $result = $log_versionnumber;
    }
  }

  return $result;
}


/**
 *  Lance active (non-rented, lance and customer status active)
 *
 *  Inputs:
 *  - lance_id:             lance database id
 *
 *  Return:                 color
 */
function db_check_lance_active($lance_id, $customer = "") {
  // Initial values
  $valid = 0;

  // Check if rented and lans/customer status
  $lance = db_fetch_lance($lance_id, "", 1);
  if (((!strlen($lance['gebruiker'])) || ($lance['gebruiker'] == $lance['eigenaar']) || ($lance['gebruiker'] == $customer)) &&
       (in_array($lance['lans_status'], array("actief"))) && (in_array($lance['klant_status'], array("actief")))) {
    $valid = 1;
  }

  return $valid;
}


/**
 *  Fetch next or previous lance tcp status (next or previous)
 *
 *  Inputs:
 *  - lance_id:             lance database id
 *  - datetime:             date time
 *  - counter               Next(TRUE)/previous(FALSE)
 *  - event                 Event ('connect','disconnect','time-out','aborted','killed','error','sms');
 *  - connection            Connection id
 *
 *  Return:                 Array containing tcp status info
 */
function db_fetch_lance_tcp_status($lance_id, $datetime, $counter = TRUE, $event = "", $connection = "")
{
  // Initial return value
  $result = "";

  // Setup query
  $query  = "SELECT * FROM log_tcp where zkl='" . $lance_id . "' and t"  . (($counter) ? " > " : " < ") . "'". convert_datetime($datetime) . "' ";

  // Search for specific event?
  if (is_array($event)) {
    $query .= " AND (";
    for($i=0; $i<sizeof($event); $i++) {
      if ($i) {
        $query .= " OR ";
      }
      $query .= "event='" . $event[$i] . "'";
    }
    $query .= ") ";
  }
  else if (strlen($event))  {
    $query .= " AND event='"  . $event . "' ";
  }

  // Search for specific connection?
  if (strlen($connection)) {
    $query .= " AND connection='"  . $connection . "' ";
  }

  // Order and limit
  $query .= " ORDER BY t " . (($counter) ? " DESC " : " DESC ") . "LIMIT 1";

  // Fetch lance info
  $tcp_status = db_fetch_data($query);

  if (!empty($tcp_status)) {
    // Parse result
    $result = $tcp_status[0];
  }

  return $result;
}


/**
 *  Test the current connections by sending a $STAT
 *
 *  Inputs:
 *  - equipment:            array containing equipment info
 *
 *  Return:                 Html stream
 */
function db_lance_connection_stat($equipment) {
  // Initial values
  $timeout      = 30;
  $connected    = array();
  $disconnected = array();

  if (is_array($equipment)) {
    foreach($equipment as $item) {
      // Number of retries
      $cnt = 1;

      do {
        // Get starting time
        $start = microtime_float();

        // Get non-cached status
        $result = tcpclient_non_cached_status(array($item), FALSE, $timeout);

        // Get end time
        $end = microtime_float();

        // Double check
        $finished = ((($end - $start) < 3) && (($end - $start) < $timeout) && ($result[0]['found'])) ? false : true;
      }
      while((--$cnt > 0) && !$finished);

      // Get last gps position
      $gps_info = db_fetch_cache($item['id'], 'gps');

      // Fill array with data
      $tmp = array(dbid           => $item['id'],
                   id             => sprintf("%05d", $item['id']) . ": " . $item['idcode'] . " (" . $item['serienr'] . ")",
                   response       => '-',
                   gps            => $gps_info['latitude'] . ", " . $gps_info['longitude'],
                   measure        => '-',
                   firmware_wcpu  => (empty($item['wcpu_versie']))    ? "-" : $item['wcpu_versie'],
                   firmware_mcu   => (empty($item['mcu_versie']))     ? "-" : $item['mcu_versie'],
                   firmware_wave  => (empty($item['wavecom_versie'])) ? "-" : $item['wavecom_versie'],
                   gsm_strength   => '-');

      // Found or timeout?
      if (($result[0]['found']) || (($end - $start) >= $timeout)) {
        // Adapt response/measure
        $tmp['response']     = (($end - $start) < $timeout) ? ($end - $start) : $timeout;
        $tmp['measure']      = ($result[0]['found']) ? (($result[0]['status']['detection']['active']) ? "On" : "Off") : "-";
        $tmp['gsm_strength'] = ($result[0]['found']) ? $result[0]['status']['gsm']['received signal strength'] : "-";

        // Add to array
        array_push($connected, $tmp);
      }
      else {
        // Add to array
        array_push($disconnected, $tmp);
      }
    }
  }

  // Define font family
  $content  = "<style type=\"text/css\">\n";
  $content .= "* {\n";
  $content .= "font-family:Verdana;\n";
  $content .= "}\n\n";
  $content .= "table {\n";
  $content .= "font-family:Verdana;\n";
  $content .= "border-width:1px;\n";
  $content .= "border-style:inset;\n";
  $content .= "font-size:11px;\n";
  $content .= "width:1500px;\n";
  $content .= "}\n\n";
  $content .= "th {\n";
  $content .= "background:#C0C0C0;\n";
  $content .= "}\n\n";
  $content .= "h1 {\n";
  $content .= "font-family:Verdana;\n";
  $content .= "font-size:16px;\n";
  $content .= "color:" . $_PAGE_INFO['ini']['report']['color'] . ";\n";
  $content .= "margin-top:3px;\n";
  $content .= "margin-bottom:3px;\n";
  $content .= "}\n\n";
  $content .= "h2{\n";
  $content .= "font-family:Verdana;\n";
  $content .= "font-size:10px;\n";
  $content .= "color:" . $_PAGE_INFO['ini']['report']['color'] . ";\n";
  $content .= "margin-top:3px;\n";
  $content .= "margin-bottom:3px;\n";
  $content .= "}\n\n";
  $content .= "#nok {\n";
  $content .= "background-color:red;\n";
  $content .= "color:white;\n";
  $content .= "};\n\n";
  $content .= "#nok a:link {color:white;}\n";
  $content .= "#nok a:visited {color:blue;}\n";
  $content .= "#nok a:hover {color:white;}\n";
  $content .= "#nok a:active {color:white;}\n";
  $content .= "</style>\n";

  // ***************************
  // Create statistics
  // ***************************
  $content .= "<h1>Statistics</h>";
  $content .= "<table>\n";
  $content .= "<th width=\"55%\">Item</th><th>value</th>\n";
  $content .= "<tr><td>Date/time</td><td>" . date('Y-m-d H:i:s') . "</td></tr>\n";
  $content .= "<tr><td>Total devices</td><td>" . (sizeof($connected) + sizeof($disconnected)) . "</td></tr>\n";
  $content .= "<tr><td>Connected devices</td><td>" . sizeof($connected) . " (" . sprintf("%.2f",((sizeof($connected)/(sizeof($disconnected) + sizeof($connected))) * 100)) . "%)</td></tr>\n";
  $content .= "<tr><td>Disconnected devices</td><td>" . sizeof($disconnected) . " (" . sprintf("%.2f",100 - ((sizeof($connected)/(sizeof($disconnected) + sizeof($connected))) * 100)) . "%)</td></tr>\n";
  $content .= "</table>\n\n";

  // ***************************
  // Show (dis)connected devices
  // ***************************
  for ($i=0; $i<3; $i++) {
    switch($i) {
      case 0:
        $tmp  = $connected;
        $hdr1 = "Connected";
        $hdr2 = "(order by response)";
        $tmp  = array_sort($tmp, "response", SORT_NUMERIC, SORT_DESC);
        break;
      case 1:
        $tmp  = $disconnected;
        $hdr1 = "Disconnected";
        $hdr2 = "(order by database id)";
        $tmp  = array_sort($tmp, "id", SORT_NUMERIC, SORT_ASC);
        break;
      default:
        $tmp  = array_merge($connected, $disconnected);
        $hdr1 = "Merged";
        $hdr2 = "(order by database id)";
        $tmp  = array_sort($tmp, "dbid", SORT_NUMERIC, SORT_ASC);
        break;
    }

    $content .= "<br><br><h1>" . $hdr1 . "</h1><h2>" . $hdr2 . "</h2>";
    $content .= "<table>\n";
    $content .= "<th width=\"16%\">Database id: idcode (serial number)</th>";
    $content .= "<th width=\"10%\">WCPU</th>";
    $content .= "<th width=\"10%\">MCU</th>";
    $content .= "<th width=\"32%\">Wavecom</th>";
    $content .= "<th width=\"8%\">Gsm signal</th>";
    $content .= "<th width=\"7%\">Measurement</th>";
    $content .= "<th width=\"5%\">Response</th>";    
    $content .= "<th width=\"12%\">Last gps position</th>\n";
    
    foreach($tmp as $item) {
      // GPS available?
      $gps_info = ($item['gps'] != ", ") ? "<a target=\"_blank\" href=" . GOOGLE_MAPS . "maps?q=" . str_replace(" ", "", $item['gps']) . ">" . $item['gps'] . "</a>" : "-";

      // Response?
      $response_info = ($item['response'] == $timeout) ? "> " . $item['response'] . " [s]" : (($item['response'] == "-") ? $item['response'] : (sprintf("%.2f", $item['response']) . " [s]"));

      $tr_id = ((!empty($item['response'])) && ($item['response'] != "-") && ($item['response'] < 3)) ? "ok" : "nok";

      // Content
      $content .= "<tr id=\"" . $tr_id . "\">";
      $content .= "<td>" . $item['id']                             . "</td>";
      $content .= "<td align=\"center\">" . $item['firmware_wcpu'] . "</td>";
      $content .= "<td align=\"center\">" . $item['firmware_mcu']  . "</td>";
      $content .= "<td align=\"center\">" . $item['firmware_wave'] . "</td>";   
      $content .= "<td align=\"center\">" . $item['gsm_strength']  . "</td>";               
      $content .= "<td align=\"center\">" . $item['measure']       . "</td>";
      $content .= "<td align=\"center\">" . $response_info         . "</td>";
      $content .= "<td align=\"center\">" . $gps_info              . "</td>";
      $content .= "</tr>";
    }
    $content .= "</table>\n\n";
  }

  return $content;
}
?>