<?php
/** \file include\db_cust.php
 *  \brief DI webinterface database functions
 *  \author Rob Schalken, Core|Vision
 *  \version 1.0
 *  \date 17-10-2008
 *
 * This file contains the customer database functions. This file is always included.
 */

/**
 *  Customer right verification
 *
 *  Inputs:
 *  - customer_id:          Customer database id
 *  - right:                The right which must be verified
 *  - skip_current:         Skip current customer
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_ver_right_cust($customer_id, $right, $skip_current = 0) {
  // Initial return value
  $result = TRUE;

  if( !$skip_current ) {
    // query to retrieve customer information
    $row_cust = db_fetch_customer($customer_id, TRUE);

    if( !in_array($right, $row_cust['rechten']) ) $result = FALSE;
  }

  // Now it is clear that the customer has the requested right, let's see if the
  // customer (and all its adjacent primair relations) still has the rights (up the pyramid)
  if( $result ) {
    // Search all relations (up the pyramid)
    $relations = db_search_relations($customer_id, "", "up", 1, 1);

    if( is_array($relations) ) foreach ($relations as $relation) {
      if ($result) {
        // Reset result when the customer higher in the hierarchy doesn't have this right
        if( !in_array($right, $relation['rechten']) ) $result = FALSE;
      }
    }
  }

  return $result;
}

/**
 *  Search customer relation (up and down the pyramid)
 *
 *  Inputs:
 *  - customer_id:          Customer database id
 *  - data                  Bedrijfsnaam/klantnaam search data
 *  - up_down               Search up or down the pyramid?
 *  - SkipRoot              Skip search for root right?
 *  - SkipCurrent           Skip current customer
 *
 *  Return:                 Array containing relation information
 */
function db_search_relations($customer_id, $data = "", $up_down = "down", $SkipRoot = 0, $skip_current = 0) {
  global $_PAGE_INFO; 
  
  // Initial values
  $root = 0;

  // Root?????
  if (!$SkipRoot) {
    // Retrieve user rights (db_ver_right_user can not be called because it is using this function!)
    $row_user = db_fetch_user($_PAGE_INFO['login']['user']['id'], NULL, TRUE);

    // Check for the "menu:klanten:root" rights
    if (in_array("menu:klanten:root", $row_user['rechten'])) {
      // Yep current user is root
      $root = 1;
    }

    // Current user root?
    if ($root) {
      // Retrieve customer rights (db_ver_right_cust can not be called because it is using this function!)
      $row_customer = db_fetch_customer($_PAGE_INFO['login']['customer']['id'], TRUE);

      // Check for the "menu:klanten:root" rights
      if (!in_array("menu:klanten:root", $row_customer['rechten'])) {
        // Yep current customer is root
        $root = 0;
      }
    }

    // Current user and customer are root => Check rest of customer up the pyramid
    if ($root) {
      while (($root) && ($row_customer['primaire_klant'] != $row_customer['id'])) {
        // Query to find the primaire relation
        $row_customers = db_fetch_data("SELECT * FROM klant WHERE primaire_klant='" . $row_customer['primaire_klant'] . "'");

        // Check for the "menu:klanten:root" rights
        foreach( $row_customers as $row_customer ) {
     	    db_merge_rights("klant", $row_customer);
          if (!in_array("menu:klanten:root", $row_customer['rechten'])) {
            $root = 0;
          }
        }
      }
    }
  }
  
  // No root? => Down the pyramid
  if ((!$root) && ($up_down == "down")) {
    // Retrieve all secundair customers => Down the pyramid!
    $customers = ($skip_current) ? array() : array($customer_id);   
    $relations = array();

    do {
      // Switch and clear
      $sec_customers   = $customers;
      $customers       = array();

      if (is_array($sec_customers)) {
        foreach ($sec_customers as $sec_customer) {
          // Query to find all secundair relations for this customer (No filter used!)
          $row_customers = db_fetch_data("SELECT * FROM klant WHERE primaire_klant='" . $sec_customer . "'");

          // check the result
          if (is_array($row_customers)) {
            foreach ($row_customers as $row_customer) {
              // Check if primaire customer differ from own id => circulair
              if ($row_customer['primaire_klant'] != $row_customer['id']) {
                array_push($customers, $row_customer['id']);
              }
                            
              // Put found customer in array (after adding the filter)                            
              if ((!strlen($data)) || (stristr($row_customer['bedrijfsnaam'], $data) !== FALSE) || (stristr($row_customer['klantnaam'], $data) !== FALSE)) {
                array_push($relations, $row_customer);
              }
            }
          }
        }
      }
    }
    while (!empty($customers));
  }
  // No root? => Up the pyramid
  else if (!$root) {
    // Retrieve all primair => Up the pyramid (can not skip the current customer!)
    $customers = array($customer_id);
    $relations = array();

    do {
      // Switch and clear
      $prim_customers   = $customers;
      $customers        = array();

      if (is_array($prim_customers)) {
        foreach ($prim_customers as $prim_customer) {
          // Query to find all primair relations for this customer (No filter used!)
          $row_customers = db_fetch_data("SELECT * FROM klant WHERE id='" . $prim_customer . "'");

          // check the result
          if (is_array($row_customers)) {
            foreach ($row_customers as $row_customer) {
              // Store primaire customer when not equals it own id => circulair
              if ($row_customer['primaire_klant'] != $row_customer['id']) {
                // Skip current
                array_push($customers, $row_customer['primaire_klant']);
              }

              // Put found customer in array
              if ((!$skip_current) || ($row_customer['id'] != $customer_id)) {
                // Put found customer in array (after adding the filter)                            
                if ((!strlen($data)) || (stristr($row_customer['bedrijfsnaam'], $data) !== FALSE) || (stristr($row_customer['klantnaam'], $data) !== FALSE)) {
                  array_push($relations, $row_customer);
                }                
              }
            }
          }
        }
      }
    }
    while (!empty($customers));
  }
  // Root is exception => see all customers when going down the pyramid (also the ones above itselfs)
  else if (($root) && ($up_down == "down")) {    
    $relations = db_fetch_data("SELECT * FROM klant WHERE (bedrijfsnaam LIKE '%" . specialchars($data) . "%' OR klantnaam LIKE '%" . specialchars($data) . "%')");
  }

  // Parse into result
  if (!empty($relations)) {
    for ($i = 0; $i < sizeof($relations); $i++) {
      // expand the customer's rights
      db_merge_rights("klant", $relations[$i]);
    }
    
    return $relations;
  }
  else {
    // no matching customers: return empty array
    return array();
  }
}

/**
 *  Search all customers which match current customer (down the pyramid)
 *
 *  Inputs:
 *  - customer:             Customer name (filter)
 *
 *  Return:                 Multidimensional array containing all primair and secundair customers
 */
function db_search_customers($customer = "", $up_down = "down") {
  global $_PAGE_INFO; 
  
  // Initial return value
  $result = "";

  $current_customer = db_fetch_customer($_PAGE_INFO['login']['customer']['id'], 1);
  $found_customers = db_search_relations($current_customer['id'], $customer, $up_down);

  // Add own id to array => when not in array, when complies customer name
  if ((!strlen($customer)) || (stristr($current_customer['bedrijfsnaam'], $customer) !== FALSE) || (stristr($current_customer['klantnaam'],$customer) !== FALSE)) {
    $found_cust = 0;
    if (is_array($found_customers)) {
      foreach ($found_customers as $found_customer) {
        if ($found_customer['id'] == $_PAGE_INFO['login']['customer']['id']) {
          $found_cust = 1;
        }
      }
    }

    if (!$found_cust) {
      array_push($found_customers, $current_customer);
    }
  }

  // Parse into result
  if (!empty($found_customers)) {
    for ($i = 0; $i < sizeof($found_customers); $i++) {
      // expand the customer's rights
      db_merge_rights("klant", $found_customers[$i]);

      $result[$i] = $found_customers[$i];
    }
  }

  return array_sort($result, "bedrijfsnaam");
}

/**
 *  Fetch customer data which match customer database id or bedrijfsnaam
 *
 *  Inputs:
 *  - customer:             Customer name
 *  - id:                   1 = Customer db id/0 = company name
 *
 *  Return:                 Array containing customer information
 */
function db_fetch_customer($customer, $db_id = FALSE) {
  // Initial return value
  $result = FALSE;

  // Fetch customer info (unique)
  if (!$db_id) {
    $row_customer = db_fetch_data("SELECT * FROM klant WHERE klantnaam='" . specialchars($customer) . "'", 1);
  }
  else {
    $row_customer = db_fetch_data("SELECT * FROM klant WHERE id='" . $customer . "'", 1);
  }

  // Parse into result
  if (!empty($row_customer)) {
  	$result = $row_customer[0];
    
    // Put the user rights in a single array
    db_merge_rights("klant", $result);

    // Convert customer's location to GPS co-ordinates
	  $row_location = db_fetch_cust_location($result['id']);
  	$result['latitude'] = $row_location['latitude'];
	  $result['longitude'] = $row_location['longitude'];
  }

  return $result;
}

/**
 *  Store new customer
 *
 *  Inputs:
 *  - customer_array:       Array containing all customer info
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_customer($customer_array) {
  // Default return value
  $result = FALSE;
  
  // Fetch the user rights fields
  $right_fields = db_fetch_rights("gebruiker");

  // Query storing new customer
  $query  = "INSERT INTO klant (klantnaam, bedrijfsnaam, adres, telefoonnr, faxnr, i18n, valuta, email, service_email, skin, website, primaire_klant, tz, klant_status, mtinfo_versie";
  foreach( $right_fields as $field => $rights ) $query .= ",`" . $field . "`";
  $query .= ") VALUES (";
  $query .= "'" . specialchars($customer_array['klantnaam'])         . "',";
  $query .= "'" . specialchars($customer_array['bedrijfsnaam'])      . "',";
  $query .= "'" . specialchars($customer_array['adres'])             . "',";
  $query .= "'" . specialchars($customer_array['telefoonnr'])        . "',";
  $query .= "'" . specialchars($customer_array['faxnr'])             . "',";
  $query .= "'" . specialchars($customer_array['i18n'])              . "',";
  $query .= "'" . specialchars($customer_array['valuta'])            . "',";
  $query .= "'" . specialchars($customer_array['email'])             . "',";
  if (strlen($customer_array['service_email'])) {
    $query .= "'" . specialchars($customer_array['service_email'])   . "',";
  }
  else {
    $query .= "NULL,";
  }  
  if (strlen($customer_array['skin'])) {
    $query .= "'" . $customer_array['skin']  . "',";
  }
  else {
    $query .= "NULL,";
  }    
  $query .= "'" . $customer_array['website']          . "',";
  $query .= "'" . $customer_array['primaire_klant']   . "',";
  $query .= "'" . $customer_array['tz']               . "',";
  $query .= "'" . $customer_array['klant_status']     . "',";
  if (strlen($customer_array['mtinfo_versie'])) {
    $query .= "'" . $customer_array['mtinfo_versie']  . "'";
  }
  else {
    $query .= "NULL";
  }  
  foreach( $right_fields as $field => $rights ) {
    $query .= ",'";
    $cust_rights = array();
    foreach( $rights as $right )
      if( in_array($right, $customer_array['rechten']) )
        $cust_rights[] = $right;
    $query .= implode(",", $cust_rights);
    $query .= "'";
  }
  $query .= ")";

  // Result OK?
  if (db_store_data($query)) {
    // Log user-customer action
    $new_cust = db_fetch_customer($customer_array['bedrijfsnaam']);
    db_log_user_customer($new_cust['id'], "menu:klanten:nieuw", serialize($customer_array));

  	// Store longitude/langitude
	  db_store_cust_location($new_cust['id'], $customer_array['longitude'], $customer_array['latitude']);
	
    // Result OK
    $result = TRUE;
  }

  return $result;
}

/**
 *  Update exisiting customer & add contact person
 *
 *  Inputs:
 *  - customer_array:       Array containing all customer info
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_update_customer($customer_array) {
  // Default return value
  $result = FALSE;
  
  // Fetch the user rights fields
  $right_fields = db_fetch_rights("gebruiker");
  
  // Retrieve original data => logging
  $orig_cust_data = db_fetch_customer($customer_array['id'], 1);

  // Query updating customer
  $query  = "UPDATE klant SET ";
  $query .= "klantnaam='"      . specialchars($customer_array['klantnaam'])       . "',";
  $query .= "bedrijfsnaam='"   . specialchars($customer_array['bedrijfsnaam'])    . "',";
  $query .= "adres='"          . specialchars($customer_array['adres'])           . "',";
  $query .= "telefoonnr='"     . specialchars($customer_array['telefoonnr'])      . "',";
  $query .= "faxnr='"          . specialchars($customer_array['faxnr'])           . "',";
  $query .= "i18n='"           . specialchars($customer_array['i18n'])            . "',";
  $query .= "valuta='"         . specialchars($customer_array['valuta'])          . "',";
  $query .= "email='"          . specialchars($customer_array['email'])           . "',";
  if (strlen($customer_array['service_email'])) {
    $query .= "service_email='". specialchars($customer_array['service_email'])   . "',";
  }
  else {
    $query .= "service_email=NULL,";
  }     
  if (strlen($customer_array['skin'])) {
    $query .= "skin='"         . $customer_array['skin']           . "',";
  }
  else {
    $query .= "skin=NULL,";
  }     
  $query .= "website='"        . $customer_array['website']        . "',";
  $query .= "primaire_klant='" . $customer_array['primaire_klant'] . "',";
  $query .= "tz='"             . $customer_array['tz']             . "',";
  if (strlen($customer_array['mtinfo_versie'])) {
    $query .= "mtinfo_versie='" . $customer_array['mtinfo_versie'] . "',";
  }
  else {
    $query .= "mtinfo_versie=NULL,";
  }         
  $query .= "klant_status='"   . $customer_array['klant_status']   . "'";
  foreach( $right_fields as $field => $rights ) {
    $query .= ",`" . $field . "`='";
    $cust_rights = array();
    foreach( $rights as $right )
      if( in_array($right, $customer_array['rechten']) )
        $cust_rights[] = $right;
    $query .= implode(",", $cust_rights);
    $query .= "'";
  }
  $query .= "WHERE id='"       . $customer_array['id']             . "'";

	// Result OK
  if (db_store_data($query)) {
    // Store longitude/latitude
	  db_store_cust_location($customer_array['id'], $customer_array['longitude'], $customer_array['latitude']);
	  
    $result = TRUE;
  }

  if ($result) {
    // Update timezone users
    $result = FALSE;

    // Query updating customer
    $query  = "UPDATE gebruiker SET tz='" . $customer_array['tz'] . "' where klant='" . $customer_array['id'] . "'";

    if (db_store_data($query)) {
      // Result OK
      $result = TRUE;
    }

    // Update timezone equipment
    $result = FALSE;

    // Find original tz => store log_versienummer
    $query  = "SELECT * FROM zkl WHERE (eigenaar='" . $customer_array['id'] . "' AND ((gebruiker is NULL) OR (gebruiker='" . $customer_array['id'] . "'))) OR ";
    $query .= "(eigenaar!='" . $customer_array['id'] . "' AND gebruiker='" . $customer_array['id'] . "')";      
    $orig_data = db_fetch_data($query);

    // Query updating customer
    $query  = "UPDATE zkl SET tz='" . $customer_array['tz'] . "' WHERE (eigenaar='" . $customer_array['id'] . "' AND ((gebruiker is NULL) OR (gebruiker='" . $customer_array['id'] . "'))) OR ";
    $query .= "(eigenaar!='" . $customer_array['id'] . "' AND gebruiker='" . $customer_array['id'] . "')";

    if (db_store_data($query)) {
      // Result OK
      $result = TRUE;

      // Update log_versienummer
      if (is_array($orig_data)) {
        foreach($orig_data as $item) {
          db_update_lance_version($item['id'], $item);
        }
      }
    }

    // Store contact person when available
    $contact_array['gebruiker'] = $customer_array['contact_persoon'];
    $contact_array['klant']     = $customer_array['id'];

    if (!db_store_contact($contact_array)) {
      // Result NOK
      $result = FALSE;
    }
    else {
      // Initial value
      $changed = FALSE;
      
      // Get new data
      $new_data = db_fetch_customer($customer_array['id'], 1);
      
      // Changed?
      if ((is_array($orig_cust_data)) && (is_array($new_data))) {
        foreach($orig_cust_data as $key => $item) {
          $changed = ($new_data[$key] != $item) ? TRUE : $changed;
        }
      }
      
      if ($changed) {                  
        // Log user-customer action
        db_log_user_customer($customer_array['id'], "menu:klanten:wijzigen", serialize($customer_array));
      }
    }
  }

  return $result;
}

/**
 *  Delete customer and matching users/contact person/projects
 *
 *  Inputs:
 *  - customer_id:          Customer database id
 *  - users                 Users database id
 *  - projects              projects database id
 *  - lances                lances database id
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_delete_customer($customer_id, $users, $projects, $lances) {
  // Initial return value
  $result = 0;

  // Start transaction
  db_start_transaction();

  // Delete customers and contact person
  $query  = "DELETE FROM klant WHERE id='" . $customer_id . "'";
  db_store_data($query);

  $query  = "DELETE FROM klant_contact WHERE klant='" . $customer_id . "'";
  db_store_data($query);

  // Fetch all rented equipqment
  $equipment = db_fetch_data("SELECT * FROM zkl WHERE gebruiker='" . $customer_id . "'");

  // Return rented equipment to owner
  if (is_array($equipment)) {
    foreach($equipment as $item) {
      db_update_lance_user($item['id'], "NULL");
    }
  }

  // Commit transaction
  if (db_commit_transaction()) {
    // Log user-customer action
    db_log_user_customer($customer_id, "menu:klanten:verwijderen");

    // Parse result
    $result = 1;
  }

  // Delete users
  if ($result) {
    if (is_array($users)) {
      foreach ($users as $user) {
        db_delete_user($user['id']);
      }
    }
  }

  // Delete projects
  if ($result) {
    if (is_array($projects)) {
      foreach ($projects as $project) {
        db_delete_project($project['id']);
      }
    }
  }

  // Delete lances
  if ($result) {
    if (is_array($lances)) {
      foreach ($lances as $lance) {
        db_delete_lance($lance['id']);
      }
    }
  }

  return $result;
}


/**
 *  Delete customer 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_customer_logs($timeout, $limit = 100) {
  GLOBAL $_PAGE_INFO;

  // Initial values
  $affected = 0;

  // Get start time;
  $start = microtime_float();

  // All log tables
  $log_table = array("klant_contact");

  do {
    // Initial value
    $finished = 1;

    // all log files
    foreach($log_table as $table) {
      // Initial values
      $cust_ids = "";
      $cust_max = "";

      // Get all cust ids => Create array and determine max value
      $query = "SELECT id FROM klant ORDER BY id ASC";
      $rows = db_fetch_data($query);
      if (is_array($rows)) {
        $cust_ids = "(";
        for($i = 0; $i < sizeof($rows); $i++) {
          // Add comma
          if ($i) {
            $cust_ids .= ",";
          }
          // Add id
          $cust_ids .= $rows[$i]['id'];

          // Get highest cust index
          $cust_max = $rows[$i]['id'];
        }
        $cust_ids .= ")";
      }

      // Delete entries from table
      if (strlen($cust_ids)) {
        $query  = "DELETE FROM " . $table . " WHERE klant <= " . $cust_max . " AND klant NOT IN " . $cust_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);
}


/**
 *  Fetch contact person which match customer database id
 *
 *  Inputs:
 *  - customer_id:          Customer database id

 *  Return:                 Array containing contact person
 */
function db_fetch_contact_customer($customer_id) {
  // Initial return value
  $result = "";

  // Fetch customer info (unique)
  $row_contact_person = db_fetch_data("SELECT * FROM klant_contact WHERE klant='" . $customer_id . "'");

  // Parse into result
  if (!empty($row_contact_person)) {
    $result = $row_contact_person[0];
  }

  return $result;
}

/**
 *  Fetch if this user is a contact person
 *
 *  Inputs:
 *  - user_id:              User database id
 *
 *  Return:                 Array containing customer info
 */
function db_fetch_contact_user($user_id) {
  // Initial return value
  $result = "";

  // Fetch all contacts matching this user
  $contacts = db_fetch_data("SELECT * FROM klant_contact WHERE gebruiker='" . $user_id . "'");

  if (!empty($contacts)) {
    $result = array();
    if (is_array($contacts)) {
      foreach ($contacts as $contact) {
        // Fetch customer info (unique)
        $customer = db_fetch_customer($contact['klant'], TRUE);
        array_push($result, $customer);
      }
    }
  }

  return $result;
}

/**
 *  Store new contact person (and remove older ones)
 *
 *  Inputs:
 *  - customer_user_array:  Array containing all customer/user info (contact)
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_contact($cust_user_array) {
  // Initial return value
  $result = 0;

  // Start transaction
  db_start_transaction();

  // Query delete old contact
  $query  = "DELETE FROM klant_contact WHERE klant='" . $cust_user_array['klant'] . "'";
  db_store_data($query);

  // Query storing new customer
  if ((strlen($cust_user_array['klant'])) && (strlen($cust_user_array['gebruiker']))) {
    $query  = "INSERT INTO klant_contact (klant, gebruiker) VALUES (";
    $query .= "'" . $cust_user_array['klant']       . "',";
    $query .= "'" . $cust_user_array['gebruiker']   . "')";
    db_store_data($query);
  }

  // Commit transaction
  if (db_commit_transaction()) {
    $result = 1;
  }

  return $result;
}


/**
 *  Fetch all customers
 *
 *  Return:                 Array containing customer info
 */
function db_fetch_customers_all() {
  // Fetch all customers
  $customers = db_fetch_data("SELECT * FROM klant ORDER BY bedrijfsnaam");

  if (!empty($customers)) {
    for ($i = 0; $i < sizeof($customers); $i++) {
      // expand the customer's rights
      db_merge_rights("klant", $customers[$i]);
    }
    
    return $customers;
  }
  else return FALSE;
}


/**
 *  Fetch all available device types
 *
 *  Inputs:
 *  -customer_id:           Customer db id
 *  -hide_rentals:          Hide rented equipment
 *
 *  Return:                 Array device types
 */
function db_fetch_customer_all_device_types($customer_id, $hide_rentals = 0) {
  // Initial return value
  $result = "";

  // Fetch all device types
  if ($customer_id == "NULL") {
    $query = "SELECT DISTINCT device FROM zkl WHERE eigenaar is NULL";
  }
  else {
    if ($hide_rentals) {
      $query = "SELECT DISTINCT device FROM zkl WHERE eigenaar='" . $customer_id . "'";
    }
    else {
      $query = "SELECT DISTINCT device FROM zkl WHERE (gebruiker='" . $customer_id . "' or eigenaar='" . $customer_id . "')";
    }
  }

  $types = db_fetch_data($query);

  if (!empty($types)) {
    $result = array();
    foreach ($types as $type) {
      array_push($result,$type['device']);
    }
  }

  return $result;
}

/**
 *  Fetch all childs
 *
 *  Inputs:
 *  -customer_id:           Customer db id
 *
 *  Return:                 Array customers
 */
function db_fetch_customer_childs($customer_id) {
  // Fetch all device types
  $query = "SELECT * from klant where primaire_klant=" . $customer_id . " and id!='" . $customer_id . "'";
  $customers = db_fetch_data($query);

  // Parse result
  if (!empty($customers)) {
    for ($i = 0; $i < sizeof($customers); $i++) {
      // expand the customer's rights
      db_merge_rights("klant", $customers[$i]);
    }
    
    return $customers;
  }
  else {
    // no matches: return an empty array
    return array();
  }
}


/**
 *  Store new customer location (and remove older ones)
 *
 *  Inputs:
 *  - customer_id           :  Customer database id
 *  - lon                   :  Longitude
 *  - lat                   :  Latitude 
 *
 *  Return:                 1 (OK)/ 0(Error)
 */
function db_store_cust_location($customer_id, $lon, $lat) {
  // Initial return value
  $result = 0;

  // Start transaction
  db_start_transaction();

  // Query delete old contact
  $query  = "DELETE FROM klant_locatie WHERE klant='" . $customer_id . "'";
  db_store_data($query);

  // Query storing new customer info
  $query  = "INSERT INTO klant_locatie (klant, longitude, latitude) VALUES (";
  $query .= "'" . $customer_id       . "',";  
  if (is_numeric($lon)) {
    $query .= "'" . $lon  . "',";
  }
  else {
    $query .= "NULL,";
  }  
  if (is_numeric($lat)) {
    $query .= "'" . $lat  . "')";
  }
  else {
    $query .= "NULL)";
  }  
  db_store_data($query);

  // Commit transaction
  if (db_commit_transaction()) {
    $result = 1;
  }

  return $result;
}

/**
 *  Fetch customer location (When available)
 *
 *  Inputs:
 *  - customer_id:          Cusomter database id
 *
 *  Return:                 Array containing location info
 */
function db_fetch_cust_location($customer_id) {
  // Initial return value
  $result = "";

  // Fetch customer location info
  $location = db_fetch_data("SELECT * FROM klant_locatie WHERE klant='" . $customer_id . "'");

  if (!empty($location)) {
    $result = $location[0];
  }

  return $result;
}

/**
 *  Select customer's language
 *
 *  Inputs:
 *  - i18n          TRUE (or '1') to force the language to the customer's language,
 *                  FALSE (or '0') to force the user's language. Any other value
 *                  is returned as-is.
 *  - customer_info Customer ID, full info or an array of IDs.
 */
function i18n_customer($i18n, $customer_info)
{
  if( $i18n === TRUE || $i18n == 1 ) {
    // Find an appropriate language based on the customer filter;
    //  force the user's language if not present or there is no unified language
    $i18n = FALSE;

    if( $customer_info ) {
      if( $customer_info['i18n'] ) {
        $i18n = $customer_info['i18n'];
      }
      else {
        // Force customer's language, when possible
        $customer = getCustomer($customer_info);
        if( !$customer ) {
          foreach( $customer_info as $customer_id ) {
            $customer  = db_fetch_customer($customer_id, TRUE);
  
            // select customer's language, but revert to the current user's language when this is a mix
            if( $i18n === FALSE ) {
              $i18n = $customer['i18n'];
            }
            else if( $i18n != $customer['i18n'] ) {
              $i18n = $_SESSION[$_PAGE_INFO['id']]['i18n'];
            }
          }
        }
      }
    }
  }
  // Force user's language? Also fallback if we can't find an appropriate language
  //  based on the customer selection
  if( !$i18n ) $i18n = $_SESSION[$_PAGE_INFO['id']]['i18n'];
  
  return $i18n;
}

/**
 *  Select customer's currency
 *
 *  Inputs:
 *  - valuta        TRUE (or '1') to force the currency to the customer's currency,
 *                  FALSE (or '0') to force the user's currency. Any other value
 *                  is returned as-is.
 *  - customer_info Customer ID, full info or an array of IDs.
 *  - info          Optional info.
 */
function valuta_customer($valuta, $customer_info, &$valuta_info)
{
  if( $valuta === TRUE || $valuta == 1 ) {
    // Find an appropriate currency based on the customer filter;
    //  force the user's language if not present or there is no unified currency
    $valuta = FALSE;

    if( $customer_info ) {
      if( $customer_info['valuta'] ) {
        $valuta = $customer_info['valuta'];
      }
      else {
        // Force customer's currency, when possible
        $customer = getCustomer($customer_info);
        if( !$customer ) {
          foreach( $customer_info as $customer_id ) {
            $customer  = db_fetch_customer($customer_id, TRUE);
  
            // select customer's language, but revert to the current user's currency when this is a mix
            if( $valuta === FALSE ) {
              $valuta = $customer['valuta'];
            }
            else if( $valuta != $customer['valuta'] ) {
              $valuta = $_SESSION[$_PAGE_INFO['id']]['valuta'];
            }
          }
        }
      }
    }
  }
  // Force user's language? Also fallback if we can't find an appropriate language
  //  based on the customer selection
  if( !$valuta ) $valuta = $_SESSION[$_PAGE_INFO['id']]['valuta'];
  
  return $valuta;
}


/**
 *  Get customer name
 */
function getCustomer($cust_id = FALSE, $with_short_name = TRUE)
{
  global $_PAGE_INFO;
  
  if( is_array($cust_id) ) {
    // assume full customer array
    $customer = $cust_id;
    // but check to be sure :-)
    if( !is_numeric($customer['id']) ) return FALSE;
  }
  else {
    // id may not be set; use the login data in that case
    if( !is_numeric($cust_id) ) $cust_id = $_PAGE_INFO['login']['customer']['id'];
  
    // get customer info
    $customer = db_fetch_customer($cust_id, TRUE);
    
    // and check
    if( !$customer ) return FALSE;
  }
  
  // Format the company name
  $customer_name = $customer['bedrijfsnaam'];
  if( $with_short_name ) $customer_name .= " (" . $customer['klantnaam'] . ")";
  return $customer_name;
}

?>