1009 lines
31 KiB
PHP
1009 lines
31 KiB
PHP
<?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;
|
|
}
|
|
|
|
?>
|