1<?php
2
3/**
4 *	phpIPAM Section class
5 */
6
7class Tools extends Common_functions {
8
9	/**
10	 * (array) IP address types from Addresses object
11	 *
12	 * (default value: null)
13	 *
14	 * @var mixed
15	 * @access public
16	 */
17	public $address_types = null;
18
19	/**
20	 * CSV delimiter
21	 *
22	 * @var string
23	 */
24	public $csv_delimiter = ",";
25
26	/**
27	 * Addresses object
28	 *
29	 * (default value: false)
30	 *
31	 * @var bool|object
32	 * @access protected
33	 */
34	protected $Addresses = false;
35
36	/**
37	 * Available phpIPAM releases
38	 * @var array
39	 */
40	public $phpipam_releases = [];
41
42	/**
43	 * Latest phpIPAM release
44	 * @var mixed
45	 */
46	private $phpipam_latest_release;
47
48
49
50	/**
51	 * __construct method
52	 *
53	 * @access public
54	 */
55	public function __construct (Database_PDO $database) {
56		parent::__construct();
57
58		# set database object
59		$this->Database = $database;
60		# initialize Result
61		$this->Result = new Result ();
62	}
63
64
65
66
67
68
69
70
71
72
73
74	/**
75	 *	@VLAN specific methods
76	 *	--------------------------------
77	 */
78
79	/**
80	 * Fetch vlans and subnets for tools vlan display. Joined query
81	 *
82	 * @access public
83	 * @param int $domainId (default: 1)
84	 * @return array|bool
85	 */
86	public function fetch_vlans_and_subnets ($domainId=1) {
87	    # custom fields
88	    $custom_fields = $this->fetch_custom_fields("vlans");
89		# if set add to query
90		$custom_fields_query = "";
91	    if(sizeof($custom_fields)>0) {
92			foreach($custom_fields as $myField) {
93				$custom_fields_query  .= ',`vlans`.`'.$myField['name'].'`';
94			}
95		}
96	    # set query
97	    $query = 'SELECT vlans.vlanId,vlans.number,vlans.name,vlans.description,vlans.customer_id,subnets.subnet,subnets.mask,subnets.id AS subnetId,subnets.sectionId'.@$custom_fields_query.' FROM vlans LEFT JOIN subnets ON subnets.vlanId = vlans.vlanId where vlans.`domainId` = ? ORDER BY vlans.number ASC;';
98		# fetch
99		try { $vlans = $this->Database->getObjectsQuery($query, array($domainId)); }
100		catch (Exception $e) {
101			$this->Result->show("danger", _("Error: ").$e->getMessage());
102			return false;
103		}
104
105		# reorder
106		$out = array();
107		foreach ($vlans as $vlan) {
108			$out[$vlan->vlanId][] = $vlan;
109		}
110		# result
111		return is_array($out) ? array_values($out) : false;
112	}
113
114
115
116
117
118
119
120	/**
121	 *	@search methods
122	 *	--------------------------------
123	 */
124
125
126	/**
127	 * Search database for addresses
128	 *
129	 * @access public
130	 * @param mixed $search_term
131	 * @param string $high (default: "")
132	 * @param string $low (default: "")
133	 * @param array $custom_fields (default: array())
134	 * @return array
135	 */
136	public function search_addresses($search_term, $high = "", $low = "", $custom_fields = array()) {
137
138    	$tags = $this->fetch_all_objects ("ipTags", "id");
139    	foreach ($tags as $t) {
140        	if(strtolower($t->type)==strtolower($search_term)) {
141            	$tags = $t->id;
142            	break;
143        	}
144        	$tags = false;
145    	}
146
147		# set search query
148		$query[] = "select * from `ipaddresses` ";
149		$query[] = "where `ip_addr` between :low and :high ";	//ip range
150		$query[] = "or `hostname` like :search_term ";			//hostname
151		$query[] = "or `owner` like :search_term ";				//owner
152		# custom fields
153		if(sizeof($custom_fields) > 0) {
154			foreach($custom_fields as $myField) {
155				$myField['name'] = $this->Database->escape($myField['name']);
156				$query[] = "or `$myField[name]` like :search_term ";
157			}
158		}
159		$query[] = "or `switch` like :search_term ";
160		$query[] = "or `port` like :search_term ";				//port search
161		$query[] = "or `description` like :search_term ";		//descriptions
162		$query[] = "or `note` like :search_term ";				//note
163		$query[] = "or `mac` like :search_term ";				//mac
164		//tag
165		if($tags!==false)
166		$query[] = "or `state` like :tags ";				//tag
167		$query[] = "order by `ip_addr` asc;";
168
169		# join query
170		$query = implode("\n", $query);
171
172		# fetch
173		try { $result = $this->Database->getObjectsQuery($query, array("low"=>$low, "high"=>$high, "search_term"=>"%$search_term%", "tags"=>$tags)); }
174		catch (Exception $e) {
175			$this->Result->show("danger", _("Error: ").$e->getMessage());
176			return false;
177		}
178		# result
179		return $result;
180	}
181
182	/**
183	 * Search subnets for provided range
184	 *
185	 *	First search range
186	 *	If host provided search also inside subnet ranges
187	 *
188	 * @access public
189	 * @param mixed $search_term
190	 * @param string $high (default: "")
191	 * @param string $low (default: "")
192	 * @param mixed $search_req
193	 * @param mixed $custom_fields (default: array())
194	 * @return array
195	 */
196	public function search_subnets($search_term, $high = "", $low = "", $search_req, $custom_fields = array()) {
197		# first search if range provided
198		$result1 = $this->search_subnets_range  ($search_term, $high, $low, $custom_fields);
199		# search inside subnets even if IP does not exist!
200		$result2 = $this->search_subnets_inside ($high, $low);
201		# search inside subnets even if IP does not exist - IPv6
202		$result3 = $this->search_subnets_inside_v6 ($high, $low, $search_req);
203		# merge arrays
204		$result = array_merge($result1, $result2, $result3);
205	    # result
206	    return array_filter($result);
207	}
208
209	/**
210	 * Search for subnets inside range
211	 *
212	 * @access private
213	 * @param mixed $search_term
214	 * @param string $high
215	 * @param string $low
216	 * @param mixed $custom_fields (default: array())
217	 * @return array
218	 */
219	private function search_subnets_range ($search_term, $high, $low, $custom_fields = array()) {
220		# reformat low/high
221		if($high==0 && $low==0)	{ $high = "1"; $low = "1"; }
222
223		# set search query
224		$query[] = "select * from `subnets` where `description` like :search_term ";
225		$query[] = "or (`subnet` >= :low and `subnet` <= :high )";
226		# custom
227	    if(sizeof($custom_fields) > 0) {
228			foreach($custom_fields as $myField) {
229				$myField['name'] = $this->Database->escape($myField['name']);
230				$query[] = " or `$myField[name]` like :search_term ";
231			}
232		}
233		$query[] = "order by `subnet` asc, `mask` asc;";
234
235		# join query
236		$query = implode("\n", $query);
237
238		# fetch
239		try { $result = $this->Database->getObjectsQuery($query, array("low"=>$low, "high"=>$high, "search_term"=>"%$search_term%")); }
240		catch (Exception $e) {
241			$this->Result->show("danger", _("Error: ").$e->getMessage());
242			return false;
243		}
244		# result
245		return $result;
246	}
247
248	/**
249	 * Search inside subnets if host address is provided!
250	 *
251	 * @access private
252	 * @param string $high
253	 * @param string $low
254	 * @return array
255	 */
256	private function search_subnets_inside ($high, $low) {
257		if($low==$high) {
258			# subnets class
259			$Subnets = new Subnets ($this->Database);
260			# fetch all subnets
261			$subnets = $Subnets->fetch_all_subnets_search();
262			# loop and search
263			$ids = array();
264			foreach($subnets as $s) {
265				# cast
266				$s = (array) $s;
267
268				//first verify address type
269				$type = $this->identify_address($s['subnet']);
270
271				if($type == "IPv4") {
272					# Initialize PEAR NET object
273					$this->initialize_pear_net_IPv4 ();
274					# parse address
275					$net = $this->Net_IPv4->parseAddress($this->transform_address($s['subnet']).'/'.$s['mask'], "dotted");
276
277					if($low>=$this->transform_to_decimal(@$net->network) && $low<=$this->transform_address($net->broadcast, "decimal")) {
278						$ids[] = $s['id'];
279					}
280				}
281			}
282			# filter
283			$ids = sizeof(@$ids)>0 ? array_filter($ids) : array();
284
285			$result = array();
286
287			# search
288			if(sizeof($ids)>0) {
289				foreach($ids as $id) {
290					$result[] = $Subnets->fetch_subnet(null, $id);
291				}
292			}
293			# return
294			return sizeof(@$result)>0 ? array_filter($result) : array();
295		}
296		else {
297			return array();
298		}
299	}
300
301
302	/**
303	 * Search inside subnets if host address is provided! ipv6
304	 *
305	 * @access private
306	 * @param string $high
307	 * @param string $low
308	 * @return array
309	 */
310	private function search_subnets_inside_v6 ($high, $low, $search_req) {
311		// same
312		if($low==$high) {
313			# Initialize PEAR NET object
314			$this->initialize_pear_net_IPv6 ();
315
316			// validate
317			if ($this->Net_IPv6->checkIPv6($search_req)) {
318				# subnets class
319				$Subnets = new Subnets ($this->Database);
320				# fetch all subnets
321				$subnets = $Subnets->fetch_all_subnets_search("IPv6");
322				# loop and search
323				$ids = array();
324				foreach($subnets as $s) {
325					# cast
326					$s = (array) $s;
327					# parse address
328					$net = $this->Net_IPv6->parseAddress($this->transform_address($s['subnet'], "dotted").'/'.$s['mask']);
329
330					if(gmp_cmp($low, $this->transform_address(@$net['start'], "decimal")) == 1 && gmp_cmp($low, $this->transform_address(@$net['end'], "decimal")) == -1) {
331						$ids[] = $s['id'];
332
333					}
334				}
335				# filter
336				$ids = sizeof(@$ids)>0 ? array_filter($ids) : array();
337				# search
338				$result = array();
339				if(sizeof($ids)>0) {
340					foreach($ids as $id) {
341						$result[] = $Subnets->fetch_subnet(null, $id);
342					}
343				}
344				# return
345				return sizeof(@$result)>0 ? array_filter($result) : array();
346			}
347			// empty
348			else {
349				return array();
350			}
351		}
352		else {
353			return array();
354		}
355	}
356
357	/**
358	 * Function to search vlans
359	 *
360	 * @access public
361	 * @param mixed $search_term
362	 * @param array $custom_fields (default: array())
363	 * @return array
364	 */
365	public function search_vlans($search_term, $custom_fields = array()) {
366		# query
367		$query[] = "select * from `vlans` where `name` like :search_term or `description` like :search_term or `number` like :search_term ";
368		# custom
369	    if(sizeof($custom_fields) > 0) {
370			foreach($custom_fields as $myField) {
371				$myField['name'] = $this->Database->escape($myField['name']);
372				$query[] = " or `$myField[name]` like :search_term ";
373			}
374		}
375		$query[] = ";";
376		# join query
377		$query = implode("\n", $query);
378
379		# fetch
380		try { $search = $this->Database->getObjectsQuery($query, array("search_term"=>"%$search_term%")); }
381		catch (Exception $e) {
382			$this->Result->show("danger", _("Error: ").$e->getMessage());
383			return false;
384		}
385
386	    # return result
387	    return $search;
388	}
389
390
391	/**
392	 * Function to search vrf
393	 *
394	 * @access public
395	 * @param mixed $search_term
396	 * @param array $custom_fields (default: array())
397	 * @return array
398	 */
399	public function search_vrfs ($search_term, $custom_fields = array()) {
400		# query
401		$query[] = "select * from `vrf` where `name` like :search_term or `description` like :search_term or `rd` like :search_term ";
402		# custom
403	    if(sizeof($custom_fields) > 0) {
404			foreach($custom_fields as $myField) {
405				$myField['name'] = $this->Database->escape($myField['name']);
406				$query[] = " or `$myField[name]` like :search_term ";
407			}
408		}
409		$query[] = ";";
410		# join query
411		$query = implode("\n", $query);
412
413		# fetch
414		try { $search = $this->Database->getObjectsQuery($query, array("search_term"=>"%$search_term%")); }
415		catch (Exception $e) {
416			$this->Result->show("danger", _("Error: ").$e->getMessage());
417			return false;
418		}
419
420	    # return result
421	    return $search;
422	}
423
424	/**
425	 * Search for PSTN prefixes.
426	 *
427	 * @access public
428	 * @param mixed $search_term
429	 * @param array $custom_prefix_fields (default: array())
430	 * @return array
431	 */
432	public function search_pstn_refixes ($search_term, $custom_prefix_fields = array()) {
433		# query
434		$query[] = "select *,concat(prefix,start) as raw from `pstnPrefixes` where `prefix` like :search_term or `name` like :search_term or `description` like :search_term ";
435		# custom
436	    if(sizeof($custom_prefix_fields) > 0) {
437			foreach($custom_prefix_fields as $myField) {
438				$myField['name'] = $this->Database->escape($myField['name']);
439				$query[] = " or `$myField[name]` like :search_term ";
440			}
441		}
442		$query[] = "order by  raw asc;";
443		# join query
444		$query = implode("\n", $query);
445
446		# fetch
447		try { $search = $this->Database->getObjectsQuery($query, array("search_term"=>"%$search_term%")); }
448		catch (Exception $e) {
449			$this->Result->show("danger", _("Error: ").$e->getMessage());
450			return false;
451		}
452
453	    # return result
454	    return $search;
455	}
456
457	/**
458	 * Search for PSTN numbers.
459	 *
460	 * @access public
461	 * @param mixed $search_term
462	 * @param array $custom_prefix_fields (default: array())
463	 * @return array
464	 */
465	public function search_pstn_numbers ($search_term, $custom_prefix_fields = array()) {
466		# query
467		$query[] = "select * from `pstnNumbers` where `number` like :search_term or `name` like :search_term or `description` like :search_term or `owner` like :search_term ";
468		# custom
469	    if(sizeof($custom_prefix_fields) > 0) {
470			foreach($custom_prefix_fields as $myField) {
471				$myField['name'] = $this->Database->escape($myField['name']);
472				$query[] = " or `$myField[name]` like :search_term ";
473			}
474		}
475		$query[] = "order by number asc;";
476		# join query
477		$query = implode("\n", $query);
478
479		# fetch
480		try { $search = $this->Database->getObjectsQuery($query, array("search_term"=>"%$search_term%")); }
481		catch (Exception $e) {
482			$this->Result->show("danger", _("Error: ").$e->getMessage());
483			return false;
484		}
485
486	    # return result
487	    return $search;
488	}
489
490	/**
491	 * Search for circuits.
492	 *
493	 * @access public
494	 * @param mixed $search_term
495	 * @param array $custom_circuit_fields (default: array())
496	 * @return array
497	 */
498	public function search_circuits ($search_term, $custom_circuit_fields = array()) {
499		# query
500		$query[] = "select c.*,p.name,p.description,p.contact,p.id as pid ";
501		$query[] = "from circuits as c, circuitProviders as p ";
502		$query[] = "where c.provider = p.id";
503		$query[] = "and (`cid` like :search_term or `type` like :search_term or `capacity` like :search_term or `comment` like :search_term or `name` like :search_term";
504		# custom
505	    if(sizeof($custom_circuit_fields) > 0) {
506			foreach($custom_circuit_fields as $myField) {
507				$myField['name'] = $this->Database->escape($myField['name']);
508				$query[] = " or `$myField[name]` like :search_term ";
509			}
510		}
511
512		$query[] = ") order by c.cid asc;";
513		# join query
514		$query = implode("\n", $query);
515
516		# fetch
517		try { $search = $this->Database->getObjectsQuery($query, array("search_term"=>"%$search_term%")); }
518		catch (Exception $e) {
519			$this->Result->show("danger", _("Error: ").$e->getMessage());
520			return false;
521		}
522
523	    # return result
524	    return $search;
525	}
526
527
528	/**
529	 * Search for circuit providers
530	 *
531	 * @access public
532	 * @param mixed $search_term
533	 * @param array $custom_circuit_fields (default: array())
534	 * @return array
535	 */
536	public function search_circuit_providers ($search_term, $custom_circuit_fields = array()) {
537		# query
538		$query[] = "select * from `circuitProviders` where `name` like :search_term or `description` like :search_term or `contact` like :search_term ";
539		# custom
540	    if(sizeof($custom_circuit_fields) > 0) {
541			foreach($custom_circuit_fields as $myField) {
542				$myField['name'] = $this->Database->escape($myField['name']);
543				$query[] = " or `$myField[name]` like :search_term ";
544			}
545		}
546		$query[] = "order by name asc;";
547		# join query
548		$query = implode("\n", $query);
549
550		# fetch
551		try { $search = $this->Database->getObjectsQuery($query, array("search_term"=>"%$search_term%")); }
552		catch (Exception $e) {
553			$this->Result->show("danger", _("Error: ").$e->getMessage());
554			return false;
555		}
556
557	    # return result
558	    return $search;
559	}
560
561	/**
562	 * Function to search customers
563	 *
564	 * @access public
565	 * @param mixed $search_term
566	 * @param array $custom_fields (default: array())
567	 * @return array
568	 */
569	public function search_customers ($search_term, $custom_fields = array()) {
570		# query
571		$query[] = "select * from `customers` where `title` like :search_term or `address` like :search_term or `postcode` like :search_term or `city` like :search_term or `state` like :search_term ";
572		# custom
573	    if(sizeof($custom_fields) > 0) {
574			foreach($custom_fields as $myField) {
575				$myField['name'] = $this->Database->escape($myField['name']);
576				$query[] = " or `$myField[name]` like :search_term ";
577			}
578		}
579		$query[] = ";";
580		# join query
581		$query = implode("\n", $query);
582
583		# fetch
584		try { $search = $this->Database->getObjectsQuery($query, array("search_term"=>"%$search_term%")); }
585		catch (Exception $e) {
586			$this->Result->show("danger", _("Error: ").$e->getMessage());
587			return false;
588		}
589
590	    # return result
591	    return $search;
592	}
593
594	/**
595	 * Reformat possible nun-full IPv4 address for search
596	 *
597	 *	e.g. 10.10.10 -> 10.10.10.0 - 10.10.10.255
598	 *
599	 * @access public
600	 * @param mixed $address
601	 * @return array high/low decimal address
602	 */
603	public function reformat_IPv4_for_search ($address) {
604		# remove % sign if present
605		$address = str_replace("%", "", $address);
606		# we need Addresses class
607		$Addresses = new Addresses ($this->Database);
608
609		# if subnet is provided we have all data
610		if(strpos($address, "/")>0) {
611			# Initialize PEAR NET object
612			$this->initialize_pear_net_IPv4 ();
613			$net = $this->Net_IPv4->parseAddress($address);
614
615			$result['low']   = $Addresses->transform_to_decimal($net->network);
616			$result['high']	 = $Addresses->transform_to_decimal($net->broadcast);
617		}
618		# else calculate options
619		else {
620			# if subnet is not provided maybe wildcard is, so explode it to array
621			$address = explode(".", $address);
622            # remove empty
623            foreach($address as $k=>$a) {
624                if (strlen($a)==0)  unset($address[$k]);
625            }
626
627			# 4 pieces is ok, host
628			if (sizeof($address) == 4) {
629				$result['low'] = $result['high'] = $Addresses->transform_to_decimal(implode(".", $address));
630			}
631			# 3 pieces, we need to modify > check whole subnet
632			elseif (sizeof($address) == 3) {
633				$result['low']  = $Addresses->transform_to_decimal(implode(".", array_merge($address, array(0))));
634				$result['high'] = $Addresses->transform_to_decimal(implode(".", array_merge($address, array(255))));
635			}
636			# 2 pieces also
637			elseif (sizeof($address) == 2) {
638				$result['low']  = $Addresses->transform_to_decimal(implode(".", array_merge($address, array(0,0))));
639				$result['high'] = $Addresses->transform_to_decimal(implode(".", array_merge($address, array(255,255))));
640			}
641			# 1 piece also
642			elseif (sizeof($address) == 1) {
643				$result['low']  = $Addresses->transform_to_decimal(implode(".", array_merge($address, array(0,0,0))));
644				$result['high'] = $Addresses->transform_to_decimal(implode(".", array_merge($address, array(255,255,255))));
645			}
646			# else return same value
647			else {
648				$result['low']  = implode(".", $address);
649				$result['high'] = implode(".", $address);
650			}
651		}
652		# return result array low/high
653		return $result;
654	}
655
656	/**
657	 * Reformat possible non-full IPv6 address for search - set lowest and highest IPs
658	 *
659	 *	we can have
660	 *		a:a:a:a:a:a:a
661	 *		a:a:a::a
662	 *		a:a:a:a:a:a:a:a/mask
663	 *
664	 * @access public
665	 * @param mixed $address
666	 * @return array
667	 */
668	public function reformat_IPv6_for_search ($address) {
669		# parse address
670		$this->initialize_pear_net_IPv6 ();
671
672		$return = array();
673
674		# validate
675		if ($this->Net_IPv6->checkIPv6($address)==false) {
676			// return 0
677			return array("high"=>0, "low"=>0);
678		}
679		else {
680			# fake mask
681			if (strpos($address, "/")==0)	{ $address .= "/128"; }
682
683			# parse address
684			$parsed = $this->Net_IPv6->parseAddress($address);
685
686			# result
687			$return['low']  = gmp_strval($this->transform_address($parsed['start'], "decimal"));
688			$return['high'] = gmp_strval($this->transform_address($parsed['end'], "decimal"));
689
690			# return result array low/high
691			return $return;
692		}
693	}
694
695
696
697
698
699
700
701
702
703
704
705
706
707	/**
708	 *	@custom fields methods
709	 *	--------------------------------
710	 */
711
712	/**
713	 * Fetches all custom fields
714	 *
715	 * @access public
716	 * @param mixed $table
717	 * @return array
718	 */
719	public function fetch_custom_fields ($table) {
720		$table = $this->Database->escape($table);
721
722    	# fetch columns
723		$fields = $this->fetch_columns ($table);
724
725		$res = array();
726
727		# save Field values only
728		foreach($fields as $field) {
729			# cast
730			$field = (array) $field;
731
732			$res[$field['Field']]['name'] 	 = $field['Field'];
733			$res[$field['Field']]['type'] 	 = $field['Type'];
734			$res[$field['Field']]['Comment'] = $field['Comment'];
735			$res[$field['Field']]['Null'] 	 = $field['Null'];
736			$res[$field['Field']]['Default'] = $field['Default'];
737		}
738
739		# fetch standard fields
740		$standard = $this->fetch_standard_fields ($table);
741
742		# remove them
743		foreach($standard as $st) {
744			unset($res[$st]);
745		}
746		# return array
747		return sizeof($res)==0 ? array() : $res;
748	}
749
750	/**
751	 * Fetches all custom fields and reorders them into numeric array
752	 *
753	 * @access public
754	 * @param mixed $table
755	 * @return array
756	 */
757	public function fetch_custom_fields_numeric ($table) {
758		# fetch all custom fields
759		$custom_fields = $this->fetch_custom_fields ($table);
760		# make numberic array
761		if(sizeof($custom_fields)>0) {
762			foreach($custom_fields as $f) {
763				$out[] = $f;
764			}
765			# result
766			return isset($out) ? $out : array();
767		}
768		else {
769			return array();
770		}
771	}
772
773	/**
774	 * Fetch all fields configured in table - standard + custom
775	 *
776	 * @access private
777	 * @param mixed $table
778	 * @return array
779	 */
780	private function fetch_columns ($table) {
781		# escape method/table
782		$table = $this->Database->escape($table);
783    	# fetch columns
784		$query    = "show full columns from `$table`;";
785		# fetch
786	    try { $fields = $this->Database->getObjectsQuery($query); }
787		catch (Exception $e) { $this->Result->show("danger", $e->getMessage(), false);	return false; }
788
789		return (array) $fields;
790	}
791
792	/**
793	 * Read the SCHEMA.sql file and enforce UNIX LF
794	 *
795	 * @access private
796	 * @return array
797	 */
798	private function read_db_schema() {
799		$fh = fopen(dirname(__FILE__) . '/../../db/SCHEMA.sql', 'r');
800		$schema = str_replace("\r\n", "\n", fread($fh, 100000));
801		return $schema;
802	}
803
804	/**
805	 * Fetch the db/SCHEMA.sql DBVERSION
806	 *
807	 * @return int
808	 */
809	public function fetch_schema_version() {
810		# get SCHEMA.SQL file
811		$schema = $this->read_db_schema();
812
813		$dbversion = strstr($schema, 'UPDATE `settings` SET `dbversion` =');
814		$dbversion = strstr($dbversion, ';', true);
815		$dbversion = explode("=", $dbversion);
816
817		return intval($dbversion[1]);
818	}
819
820	/**
821	 * Fetches standard database fields from SCHEMA.sql file
822	 *
823	 * @access public
824	 * @param mixed $table
825	 * @return array
826	 */
827	public function fetch_standard_fields ($table) {
828		# get SCHEMA.SQL file
829		$schema = $this->read_db_schema();
830
831		# get definition
832		$definition = strstr($schema, "CREATE TABLE `$table` (");
833		$definition = trim(strstr($definition, ";" . "\n", true));
834
835		# get each line to array
836		$definition = explode("\n", $definition);
837
838		# go through,if it begins with ` use it !
839		$out = array();
840		foreach($definition as $d) {
841			$d = trim($d);
842			if(strpos(trim($d), "`")==0) {
843				$d = strstr(trim($d, "`"), "`", true);
844				$out[] = substr($d, strpos($d, "`"));
845			}
846		}
847		# return array of fields
848		return is_array($out) ? array_filter($out) : array();
849	}
850
851	/**
852	 * Fetches standard tables from SCHEMA.sql file
853	 *
854	 * @return array
855	 */
856	public function fetch_standard_tables () {
857		# get SCHEMA.SQL file
858		$schema = $this->read_db_schema();
859
860		# get definitions to array, explode with CREATE TABLE `
861		$creates = explode("CREATE TABLE `", $schema);
862		# fill tables array
863		$tables = array();
864		foreach($creates as $k=>$c) {
865			if($k>0)	{ $tables[] = strstr($c, "`", true); }	//we exclude first !
866		}
867
868		# return array of tables
869		return $tables;
870	}
871
872	/**
873	 * This functions fetches all columns for specified Field
874	 *
875	 * Array (Field, Type, Collation, Null, Comment)
876	 *
877	 * @access public
878	 * @param mixed $table
879	 * @param mixed $field
880	 * @return array
881	 */
882	public function fetch_full_field_definition ($table, $field) {
883		# escape field
884		$table = $this->Database->escape($table);
885		# fetch
886	    try { $field_data = $this->Database->getObjectQuery("show full columns from `$table` where `Field` = ?;", array($field)); }
887		catch (Exception $e) { $this->Result->show("danger", $e->getMessage(), false);	return false; }
888		# result
889	    return($field_data);
890	}
891
892
893
894
895
896
897
898
899
900
901
902
903	/**
904	 *	@widget methods
905	 *	--------------------------------
906	 */
907
908	/**
909	 * Fetches all widgets
910	 *
911	 * @access public
912	 * @param bool $admin (default: false)
913	 * @param bool $inactive (default: false)
914	 * @return array
915	 */
916	public function fetch_widgets ($admin = false, $inactive = false) {
917
918		# inactive also - only for administration
919		if($inactive) 			{ $query = "select * from `widgets`; "; }
920		else {
921			# admin?
922			if($admin) 			{ $query = "select * from `widgets` where `wactive` = 'yes'; "; }
923			else				{ $query = "select * from `widgets` where `wadminonly` = 'no' and `wactive` = 'yes'; "; }
924		}
925	    # fetch
926	    try { $widgets = $this->Database->getObjectsQuery($query); }
927		catch (Exception $e) { $this->Result->show("danger", $e->getMessage(), false);	return false; }
928
929	    # reindex
930	    $wout = array();
931	    foreach($widgets as $w) {
932			$wout[$w->wfile] = $w;
933	    }
934
935	    # return results
936	    return $wout;
937	}
938
939	/**
940	 * Verify that widget file exists
941	 *
942	 * @access public
943	 * @return bool
944	 */
945	public function verify_widget ($file) {
946		return file_exists(dirname(__FILE__)."/../../app/dashboard/widgets/$file.php")||file_exists(dirname(__FILE__)."/../../app/dashboard/widgets/custom/$file.php") ? true : false;
947	}
948
949
950
951
952
953
954
955
956
957
958	/**
959	 *	@request methods (for IP request)
960	 *	--------------------------------
961	 */
962
963	/**
964	 * fetches all IP requests and saves them to $requests
965	 *
966	 * @access public
967	 * @return int|array
968	 */
969	public function requests_fetch ($num = true) {
970		return $num ? $this->requests_fetch_num () : $this->requests_fetch_objects ();
971	}
972
973	/**
974	 * Fetches number of active IP requests
975	 *
976	 * @access private
977	 * @return int
978	 */
979	private function requests_fetch_num () {
980    	return $this->count_database_objects ("requests", "processed", 0);
981	}
982
983	/**
984	 * Fetches all requests and saves them to $requests
985	 *
986	 * @access private
987	 * @return array
988	 */
989	private function requests_fetch_objects () {
990    	return $this->fetch_multiple_objects ("requests", "processed", 0);
991	}
992
993	/**
994	 * Fetches all subnets that are set to allow requests
995	 *
996	 * @access public
997	 * @return array|null
998	 */
999	public function requests_fetch_available_subnets () {
1000		try { $subnets = $this->Database->getObjectsQuery("SELECT * FROM `subnets` where `allowRequests`=1 and COALESCE(`isFull`,0) != 1 ORDER BY `subnet`;"); }
1001		catch (Exception $e) { $this->Result->show("danger", $e->getMessage(), false);	return false; }
1002
1003		# save
1004		return sizeof($subnets)>0 ? (array) $subnets : NULL;
1005	}
1006
1007	/**
1008	 * Sends mail for IP request
1009	 *
1010	 * @access public
1011	 * @param string $action (default: "new")
1012	 * @param mixed $values
1013	 * @return bool
1014	 */
1015	public function ip_request_send_mail ($action="new", $values) {
1016
1017		$this->get_settings ();
1018
1019		# try to send
1020		try {
1021			# fetch mailer settings
1022			$mail_settings = $this->fetch_object("settingsMail", "id", 1);
1023
1024			# initialize mailer
1025			$phpipam_mail = new phpipam_mail($this->settings, $mail_settings);
1026
1027			# get all users and check who to end mail to
1028			$recipients = $this->ip_request_get_mail_recipients ($values['subnetId']);
1029
1030			# add requester to cc
1031			$recipients_requester = $values['requester'];
1032
1033			# reformat key / vaues
1034			$values = $this->ip_request_reformat_mail_values ($values);
1035			#reformat empty
1036			$values = $this->reformat_empty_array_fields ($values, "/");
1037
1038			# generate content
1039			if ($action=="new")			{ $subject	= "New IP address request"; }
1040			elseif ($action=="accept")	{ $subject	= "IP address request accepted"; }
1041			elseif ($action=="reject")	{ $subject	= "IP address request rejected"; }
1042			else						{ $this->Result->show("danger", _("Invalid request action"), true); }
1043
1044			// set html content
1045			$content[] = "<table style='margin-left:10px;margin-top:20px;width:auto;padding:0px;border-collapse:collapse;'>";
1046			$content[] = "<tr><td colspan='2' style='margin:0px;>$this->mail_font_style <strong>$subject</strong></font></td></tr>";
1047			foreach($values as $k=>$v) {
1048			// title search
1049			if (preg_match("/s_title_/", $k)) {
1050			$content[] = "<tr><td colspan='2' style='margin:0px;border-bottom:1px solid #eeeeee;'>$this->mail_font_style<strong>$v</strong></font></td></tr>";
1051			}
1052			else {
1053			//content
1054			$content[] = "<tr>";
1055			$content[] = "<td style='padding-left:15px;margin:0px;'>$this->mail_font_style $k</font></td>";
1056			$content[] = "<td style='padding-left:15px;margin:0px;'>$this->mail_font_style $v</font></td>";
1057			$content[] = "</tr>";
1058			}
1059			}
1060			$content[] = "<tr><td style='padding-top:15px;padding-bottom:3px;text-align:right;color:#ccc;'>$this->mail_font_style Sent at ".date('Y/m/d H:i')."</font></td></tr>";
1061			//set alt content
1062			$content_plain[] = "$subject"."\r\n------------------------------\r\n";
1063			foreach($values as $k=>$v) {
1064			$content_plain[] = $k." => ".$v;
1065			}
1066			$content_plain[] = "\r\n\r\nSent at ".date('Y/m/d H:i');
1067			$content[] = "</table>";
1068
1069			// set content
1070			$content 		= $phpipam_mail->generate_message (implode("\r\n", $content));
1071			$content_plain 	= implode("\r\n",$content_plain);
1072
1073			$phpipam_mail->Php_mailer->setFrom($mail_settings->mAdminMail, $mail_settings->mAdminName);
1074			if ($recipients!==false) {
1075			foreach($recipients as $r) {
1076			$phpipam_mail->Php_mailer->addAddress(addslashes(trim($r->email)));
1077			}
1078			$phpipam_mail->Php_mailer->AddCC(addslashes(trim($recipients_requester)));
1079			}
1080			else {
1081			$phpipam_mail->Php_mailer->addAddress(addslashes(trim($recipients_requester)));
1082			}
1083			$phpipam_mail->Php_mailer->Subject = $subject;
1084			$phpipam_mail->Php_mailer->msgHTML($content);
1085			$phpipam_mail->Php_mailer->AltBody = $content_plain;
1086			//send
1087			$phpipam_mail->Php_mailer->send();
1088		} catch (phpmailerException $e) {
1089			$this->Result->show("danger", "Mailer Error: ".$e->errorMessage(), true);
1090		} catch (Exception $e) {
1091			$this->Result->show("danger", "Mailer Error: ".$e->getMessage(), true);
1092		}
1093
1094		# ok
1095		return true;
1096
1097	}
1098
1099	/**
1100	 * Returns list of recipients to get new
1101	 *
1102	 * @access private
1103	 * @param bool|mixed $subnetId
1104	 * @return array|bool
1105	 */
1106	private function ip_request_get_mail_recipients ($subnetId = false) {
1107    	// fetch all users with mailNotify
1108        $notification_users = $this->fetch_multiple_objects ("users", "mailNotify", "Yes", "id", true);
1109        // recipients array
1110        $recipients = array();
1111        // any ?
1112        if ($notification_users!==false) {
1113         	// if subnetId is set check who has permissions
1114        	if (isset($subnetId)) {
1115             	foreach ($notification_users as $u) {
1116                	// inti object
1117                	$Subnets = new Subnets ($this->Database);
1118                	//check permissions
1119                	$subnet_permission = $Subnets->check_permission($u, $subnetId);
1120                	// if 3 than add
1121                	if ($subnet_permission==3) {
1122                    	$recipients[] = $u;
1123                	}
1124            	}
1125        	}
1126        	else {
1127            	foreach ($notification_users as $u) {
1128                	if($u->role=="Administrator") {
1129                    	$recipients[] = $u;
1130                	}
1131            	}
1132        	}
1133        	return sizeof($recipients)>0 ? $recipients : false;
1134        }
1135        else {
1136            return false;
1137        }
1138	}
1139
1140	/**
1141	 * Reformats request value/key pairs for request mailing
1142	 *
1143	 * @access private
1144	 * @param mixed $values
1145	 * @return array
1146	 */
1147	private function ip_request_reformat_mail_values ($values) {
1148		// no array
1149		if (!is_array($values)) { return $values; }
1150
1151		// addresses
1152		$this->Addresses = new Addresses ($this->Database);
1153
1154		$mail = array();
1155
1156		// change fields for mailings
1157		foreach ($values as $k=>$v) {
1158			// subnetId
1159			if ($k=="subnetId")	{
1160				// add title
1161				$mail["s_title_1"] = "<br>Subnet details";
1162
1163				$subnet = $this->fetch_object("subnets", "id", $v);
1164				$mail["Subnet"]  = $this->transform_address ($subnet->subnet, "dotted")."/".$subnet->mask;
1165				$mail["Subnet"] .= strlen($subnet->description)>0 ? " - ".$subnet->description : "";
1166			}
1167			// ip_addr
1168			elseif ($k=="ip_addr") {
1169				// add title
1170				$mail["s_title_2"] = "<br>Address details";
1171
1172				if (strlen($v)>0) {
1173					$mail['IP address'] = $this->transform_address($v, "dotted");
1174				} else {
1175					$mail['IP address'] = "Automatic";
1176				}
1177			}
1178			// state
1179			elseif ($k=="state") {
1180				$mail['State'] = $this->Addresses-> address_type_index_to_type ($v);
1181			}
1182			// description
1183			elseif ($k=="descriotion") {
1184				$mail['Description'] = $v;
1185			}
1186			// hostname
1187			elseif ($k=="hostname") {
1188				$mail['Hostname'] = $v;
1189			}
1190			// owner
1191			elseif ($k=="owner") {
1192				$mail['Address owner'] = $v;
1193			}
1194			// requester
1195			elseif ($k=="requester") {
1196				$mail['Requested by'] = $v;
1197			}
1198			// comment
1199			elseif ($k=="comment") {
1200				$mail['Request comment'] = $v;
1201			}
1202			// admin comment
1203			elseif ($k=="adminComment") {
1204				// add title
1205				$mail["s_title_3"] = "<br>Admin comment";
1206
1207				$mail['Admin comment'] = $v;
1208			}
1209			// admin comment
1210			elseif ($k=="gateway") {
1211				$mail['Gateway'] = $v;
1212			}
1213			// nameservers
1214			elseif ($k=="dns") {
1215				if (strlen($v)>0) {
1216				$mail['DNS servers'] = $v;
1217				}
1218			}
1219			// vlans
1220			elseif ($k=="vlan") {
1221				if (strlen($v)>0) {
1222				$mail['VLAN'] = $v;
1223				}
1224			}
1225		}
1226		// response
1227		return $mail;
1228	}
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241	/**
1242	 *	@database verification methods
1243	 *	------------------------------
1244	 */
1245
1246	/**
1247	 * Checks if all database fields are installed ok
1248	 *
1249	 * @access public
1250	 * @return array
1251	 */
1252	public function verify_database () {
1253
1254		# required tables from SCHEMA.sql
1255		$tables = $this->fetch_standard_tables();
1256
1257		# fetch required fields
1258		foreach($tables as $t) {
1259			$fields[$t] = $this->fetch_standard_fields ($t);
1260		}
1261
1262		/**
1263		 * check that each database exist - if it does check also fields
1264		 *		2 errors -> $tableError, $fieldError[table] = field
1265		 ****************************************************************/
1266		foreach($tables as $table) {
1267
1268			//check if table exists
1269			if(!$this->table_exists($table)) {
1270				$error['tableError'][] = $table;
1271			}
1272			//check for each field
1273			else {
1274				foreach($fields[$table] as $field) {
1275					//if it doesnt exist store error
1276					if(!$this->field_exists($table, $field)) {
1277						$error['fieldError'][$table][] = $field;
1278					}
1279				}
1280			}
1281		}
1282
1283		# return array
1284		if(isset($error)) {
1285			return $error;
1286		} else 	{
1287			# update check field
1288			$this->update_db_verify_field ();
1289			# return empty array
1290			return array();
1291		}
1292	}
1293
1294	/**
1295	 * Checks if specified table exists in database
1296	 *
1297	 *	true = exists
1298	 *	false = doesnt exist
1299	 *
1300	 * @access public
1301	 * @param mixed $tablename
1302	 * @param bool $quit
1303	 * @return bool
1304	 */
1305	public function table_exists ($tablename, $quit = false) {
1306	    # query
1307	    $query = 'SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_schema = "'.$this->Database->dbname.'" AND table_name = ?;';
1308		try { $count = $this->Database->getObjectQuery($query, array($tablename)); }
1309		catch (Exception $e) { !$quit ? : $this->Result->show("danger", $e->getMessage(), true);	return false; }
1310		# return
1311		return $count->count ==1 ? true : false;
1312	}
1313
1314	/**
1315	 * Checks if specified field exists in table
1316	 *
1317	 *	true = exists
1318	 *	false = doesnt exist
1319	 *
1320	 * @access public
1321	 * @param mixed $fieldname
1322	 * @return bool
1323	 */
1324	public function field_exists ($tablename, $fieldname) {
1325	    # escape
1326	    $tablename = $this->Database->escape($tablename);
1327		# check
1328	    $query = "DESCRIBE `$tablename` `$fieldname`;";
1329		try { $count = $this->Database->getObjectQuery($query); }
1330		catch (Exception $e) { $this->Result->show("danger", $e->getMessage(), true);	return false; }
1331		# return true if it exists
1332		return $count!== null ? true : false;
1333	}
1334
1335	/**
1336	 * Updates DB check flag in database
1337	 *
1338	 * @access private
1339	 */
1340	private function update_db_verify_field () {
1341		# query
1342		$query = "update `settings` set `dbverified`=1 where `id` = 1; ";
1343		try { $this->Database->runQuery($query); }
1344		catch (Exception $e) { !$this->debugging ? : $this->Result->show("danger", $e->getMessage(), false); }
1345	}
1346
1347	/**
1348	 * Get fix for missing table.
1349	 *
1350	 * @access public
1351	 * @param mixed $table
1352	 * @return false|string
1353	 */
1354	public function get_table_fix ($table) {
1355		$file = $this->read_db_schema();
1356
1357		//go from delimiter on
1358		$file = strstr($file, "DROP TABLE IF EXISTS `$table`;");
1359		$file = trim(strstr($file, "# Dump of table", true));
1360
1361		# check
1362		if(strpos($file, "DROP TABLE IF EXISTS `$table`;") > 0 )	return false;
1363		else														return $file;
1364	}
1365
1366	/**
1367	 * Get fix for missing field.
1368	 *
1369	 * @access public
1370	 * @param mixed $table
1371	 * @param mixed $field
1372	 * @return string|false
1373	 */
1374	public function get_field_fix ($table, $field) {
1375		$file = $this->read_db_schema();
1376
1377		//go from delimiter on
1378		$file = strstr($file, "DROP TABLE IF EXISTS `$table`;");
1379		$file = trim(strstr($file, "# Dump of table", true));
1380
1381		//get proper line
1382		$file = explode("\n", $file);
1383		foreach($file as $k=>$l) {
1384			if(strpos(trim($l), "$field`")==1) {
1385				$res = trim($l, ",");
1386				$res .= ";";
1387
1388				return $res;
1389			}
1390		}
1391		return false;
1392	}
1393
1394	/**
1395	 * Fix missing table - create
1396	 *
1397	 * @access public
1398	 * @param mixed $table
1399	 * @return bool
1400	 */
1401	public function fix_table ($table) {
1402		# first fetch fix query
1403		$query = $this->get_table_fix($table);
1404		# fix
1405		try { $this->Database->runQuery($query); }
1406		catch (Exception $e) {
1407			$this->Result->show("danger", _("Update: ").$e->getMessage()."<br>query: ".$query, true);
1408			return false;
1409		}
1410		return true;
1411	}
1412
1413	/**
1414	 * Fix missing field in table
1415	 *
1416	 * @access public
1417	 * @param mixed $table
1418	 * @param mixed $field
1419	 * @return bool
1420	 */
1421	public function fix_field ($table, $field) {
1422		$table = $this->Database->escape($table);
1423		$field = $this->Database->escape($field);
1424
1425		# set fix query
1426		$query  = "alter table `$table` add ";
1427		$query .= trim($this->get_field_fix ($table, $field), ",");
1428		$query .= ";";
1429
1430		# fix
1431		try { $this->Database->runQuery($query); }
1432		catch (Exception $e) {
1433			$this->Result->show("danger", _("Update: ").$e->getMessage()."<br>query: ".$query, true);
1434			return false;
1435		}
1436		return true;
1437	}
1438
1439	/**
1440	 * Verify that all required indexes are present in database
1441	 *
1442	 * @method verify_database_indexes
1443	 * @return bool
1444	 */
1445	public function verify_database_indexes () {
1446		// get indexes from schema
1447		$schema_indexes = $this->get_schema_indexes();
1448		// get existing indexes
1449		$missing = $this->get_missing_database_indexes($schema_indexes);
1450
1451		// if false all indexes are ok, otherwise fix
1452		if ($missing===false) {
1453			return true;
1454		}
1455		else {
1456			foreach ($missing as $table=>$index_id) {
1457				foreach ($index_id as $index_name) {
1458					$this->fix_missing_index ($table, $index_name);
1459				}
1460			}
1461		}
1462		return false;
1463	}
1464
1465	/**
1466	 * Get all indexes required for phpipam
1467	 *
1468	 * ignoring primary keys
1469	 *
1470	 * @method get_schema_indexes
1471	 * @return array
1472	 */
1473	private function get_schema_indexes () {
1474		// Discover indexes required for phpipam
1475		$schema = $this->read_db_schema();
1476
1477		# get definitions to array, explode with CREATE TABLE `
1478		$creates = explode("CREATE TABLE `", $schema);
1479
1480		$indexes = array ();
1481		foreach($creates as $k=>$c) {
1482			if($k == 0) continue;
1483			$c = trim(strstr($c, ";" . "\n", true));
1484
1485			$table = strstr($c, "`", true);
1486
1487			$definitions = explode("\n", $c);
1488			foreach($definitions as $definition) {
1489				if (preg_match('/(KEY|UNIQUE KEY) +`(.*)` +\(/', $definition, $matches)) {
1490					$indexes[$table][] = $matches[2];
1491				}
1492			}
1493		}
1494		return $indexes;
1495	}
1496
1497	/**
1498	 * Get list of table indexes
1499	 *
1500	 * @param  string $table
1501	 * @return mixed
1502	 */
1503	private function get_table_indexes($table) {
1504		try { return $indexes = $this->Database->getObjectsQuery("SHOW INDEX from `$table` where `Key_name` != 'PRIMARY';"); }
1505		catch (Exception $e) {
1506			$this->Result->show("danger", _("Invalid query for `$table` database index check : ").$e->getMessage(), true);
1507		}
1508	}
1509
1510	/**
1511	 * Using required database indexes remove all that are existing and return array of missing indexes
1512	 *
1513	 * @method get_missing_database_indexes
1514	 * @param array $schema_indexes
1515	 * @return array|null
1516	 */
1517	private function get_missing_database_indexes ($schema_indexes) {
1518		// loop
1519		foreach ($schema_indexes as $table=>$index) {
1520			$indexes = $this->get_table_indexes($table);
1521			// remove existing
1522			if ($indexes!==false) {
1523				foreach ($indexes as $i) {
1524					// remove indexes
1525					if(($key = array_search($i->Key_name, $schema_indexes[$table])) !== false) {
1526						unset($schema_indexes[$table][$key]);
1527					}
1528				}
1529			}
1530			// remove also empty table
1531			if(sizeof($schema_indexes[$table])==0) {
1532				unset($schema_indexes[$table]);
1533			}
1534		}
1535		// return diff
1536		return sizeof($schema_indexes)==0 ? false : $schema_indexes;
1537	}
1538
1539	/**
1540	 * Fix missing indexes
1541	 *
1542	 * @method fix_missing_index
1543	 * @param  string $table
1544	 * @param  string $index_name
1545	 * @return void
1546	 */
1547	private function fix_missing_index ($table, $index_name) {
1548		$table = $this->Database->escape($table);
1549		$index_name = $this->Database->escape($index_name);
1550
1551		// get definition
1552		$file = $this->read_db_schema();
1553
1554		//go from delimiter on
1555		$file = strstr($file, "DROP TABLE IF EXISTS `$table`;");
1556		$file = trim(strstr($file, "# Dump of table", true));
1557
1558		//get proper line
1559		$file = explode("\n", $file);
1560
1561		$line = false;
1562		foreach($file as $k=>$l) {
1563			// trim
1564			$l = trim($l);
1565			if(strpos($l, "KEY `".$index_name."`")!==false) {
1566				// remove last ,
1567				if(substr($l, -1)==",") {
1568					$l = substr($l, 0, -1);
1569				}
1570				// set query and run
1571				$query = "ALTER TABLE `$table` ADD ".$l;
1572
1573				try { $this->Database->runQuery($query); }
1574				catch (Exception $e) {
1575					$this->Result->show("danger", _("Creating index failed: ").$e->getMessage()."<br><pre>".$query."</pre>", true);
1576					return false;
1577				}
1578				// add warning that index was created
1579				$this->Result->show("warning", _("Created index for table `$table` named `$index_name`."), false);
1580			}
1581		}
1582	}
1583
1584	/**
1585	 * Manage indexes for linked addresses
1586	 *
1587	 * @param  string $linked_field
1588	 * @return void
1589	 */
1590	public function verify_linked_field_indexes ($linked_field) {
1591		$valid_fields = $this->fetch_custom_fields ('ipaddresses');
1592		$valid_fields = array_merge(['ip_addr','hostname','mac','owner'], array_keys($valid_fields));
1593
1594		// get indexes from schema and table
1595		$schema_indexes = $this->get_schema_indexes();
1596		$table_indexes  = $this->get_table_indexes('ipaddresses');
1597
1598		if (!is_array($schema_indexes) || !is_array($table_indexes))
1599			return;
1600
1601		$linked_field_index_found = false;
1602
1603		foreach ($table_indexes as $i) {
1604			// check for valid linked_field candidates
1605			if (!in_array($i->Key_name, $valid_fields))
1606				continue;
1607			// skip permanent indexes defined in schema
1608			if (in_array($i->Key_name, $schema_indexes['ipaddresses']))
1609				continue;
1610			// skip selected linked_field
1611			if ($i->Key_name == $linked_field) {
1612				$linked_field_index_found = true;
1613				continue;
1614			}
1615
1616			// Remove un-necessary linked_field indexes.
1617			try { $this->Database->runQuery("ALTER TABLE `ipaddresses` DROP INDEX $i->Key_name;"); }
1618			catch (Exception $e) {
1619				$this->Result->show("danger", $e->getMessage(), true);
1620			}
1621			$this->Result->show("info", _("Removing link addresses index : ").$i->Key_name);
1622		}
1623
1624		if ($linked_field_index_found || !in_array($linked_field, $valid_fields))
1625			return;
1626
1627		$schema = $this->getTableSchemaByField('ipaddresses');
1628		$data_type = $schema[$linked_field]->DATA_TYPE;
1629
1630		if( in_array($data_type, ['text', 'blob']) ) {
1631			// The prefix length must be specified when indexing TEXT/BLOB datatypes.
1632			// Max prefix length and behaviour varies with strict mode, MySQL/MariaDB versions and configured collation.
1633			//
1634			// Too complex: Avoid creating an index for this datatype and warn of possible poor performance.
1635
1636			$this->Result->show("warning",
1637				_("Warning: ")._("Unable to create index for MySQL TEXT/BLOB datatypes.")."<br>".
1638				_("Reduced performance when displaying linked addresses by ").escape_input($linked_field)." ($data_type)"."<br>".
1639				_("Change custom field data type to VARCHAR and re-save to enable indexing.")
1640			);
1641			return;
1642		}
1643
1644		// Create selected linked_field index if not exists.
1645		try { $this->Database->runQuery("ALTER TABLE `ipaddresses` ADD INDEX ($linked_field);"); }
1646		catch (Exception $e) {
1647			$this->Result->show("danger", $e->getMessage(), true);
1648		}
1649		$this->Result->show("info", _("Adding link addresses index : ").$linked_field);
1650	}
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661	/**
1662	 *	@version check methods
1663	 *	------------------------------
1664	 */
1665
1666	/**
1667	 * Check for latest version on gitHub
1668	 *
1669	 * @access public
1670	 * @param bool $print_error (default: false)
1671	 * @return string|bool
1672	 */
1673	public function check_latest_phpipam_version ($print_error = false) {
1674		# fetch settings
1675		$this->get_settings ();
1676		# check for release
1677		# try to fetch
1678		$curl = $this->curl_fetch_url('https://github.com/phpipam/phpipam/releases.atom');
1679		# check
1680		if ($curl['result']===false) {
1681			if($print_error) {
1682				$this->Result->show("danger", "Cannot fetch https://github.com/phpipam/phpipam/releases.atom : ".$curl['error_msg'], false);
1683			}
1684			return false;
1685		}
1686		# set releases href
1687		$xml = simplexml_load_string($curl['result']);
1688
1689		// if ok
1690		if ($xml!==false) {
1691			// encode to json
1692			$json = json_decode(json_encode($xml));
1693			// save all releases
1694			$this->phpipam_releases = $json->entry;
1695			// check for latest release
1696			foreach ($json->entry as $e) {
1697				// releases will be named with numberic values
1698				if (is_numeric(str_replace(array("Version", "."), "", $e->title))) {
1699					// save
1700					$this->phpipam_latest_release = $e;
1701					// return
1702					return str_replace("Version", "", $e->title);
1703				}
1704			}
1705			// none
1706			return false;
1707		}
1708		else {
1709			return false;
1710		}
1711	}
1712
1713	/**
1714	 * Updates DB version check flag in database
1715	 *
1716	 * @access public
1717	 */
1718	public function update_phpipam_checktime () {
1719		# query
1720		$query = "update `settings` set `vcheckDate`='".date("Y-m-d H:i:s")."';";
1721		try { $this->Database->runQuery($query); }
1722		catch (Exception $e) { !$this->debugging ? : $this->Result->show("danger", $e->getMessage(), false); }
1723	}
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733	/**
1734	 * @ipcalc @calculator methods
1735	 * ------------------------------
1736	 */
1737
1738	/**
1739	 * Calculates IP calculator result per IP type
1740	 *
1741	 * @access public
1742	 * @param mixed $cidr
1743	 * @return mixed
1744	 */
1745	public function calculate_ip_calc_results ($cidr) {
1746		# detect address and calculate
1747		return $this->identify_address($cidr)=="IPv6" ? $this->calculate_IPv6_calc_results($cidr) : $this->calculate_IPv4_calc_results($cidr);
1748	}
1749
1750	/**
1751	 * Calculates IPv4 results from provided CIDR address
1752	 *
1753	 * @access private
1754	 * @param mixed $cidr
1755	 * @return array
1756	 */
1757	private function calculate_IPv4_calc_results ($cidr) {
1758		# initialize subnets Class
1759		$Subnets = new Subnets($this->Database);
1760		# Initialize PEAR NET object
1761		$this->initialize_pear_net_IPv4 ();
1762
1763		# parse address
1764        $net = $this->Net_IPv4->parseAddress( $cidr );
1765
1766        # set ip address type
1767        $out = array();
1768        $out['Type']            = 'IPv4';
1769
1770        # calculate network details
1771        $out['IP address']      = $net->ip;        // 192.168.0.50
1772        $out['Network']         = $net->network;   // 192.168.0.0
1773        $out['Broadcast']       = $net->broadcast; // 192.168.255.255
1774        $out['Subnet bitmask']  = $net->bitmask;   // 16
1775        $out['Subnet netmask']  = $net->netmask;   // 255.255.0.0
1776        $out['Subnet wildcard'] = long2ip(~ip2long($net->netmask));	//0.0.255.255
1777
1778        # calculate min/max IP address
1779        $out['Min host IP']     = long2ip(ip2long($net->network) + 1);
1780        $out['Max host IP']     = long2ip(ip2long($net->broadcast) - 1);
1781        $out['Number of hosts'] = $Subnets->get_max_hosts ($net->bitmask, "IPv4");;
1782
1783        # subnet class
1784        $out['Subnet Class']    = $this->get_ipv4_address_type($net->network, $net->broadcast);
1785
1786        # if IP == subnet clear the Host fields
1787        if ($out['IP address'] == $out['Network']) {
1788            $out['IP address'] = "/";
1789        }
1790        # /32 and /32 fixes
1791        if($net->bitmask==31 || $net->bitmask==32) {
1792			$out['Min host IP'] = $out['Network'];
1793			$out['Max host IP'] = $out['Broadcast'];
1794        }
1795		# result
1796		return $out;
1797	}
1798
1799	/**
1800	 * Returns IPv4 address type from cidr
1801	 *
1802	 * @access private
1803	 * @param $network
1804	 * @param $broadcast
1805	 * @return string|false
1806	 */
1807	private function get_ipv4_address_type ($network, $broadcast) {
1808		# get all possible classes
1809		$classes = $this->define_ipv4_address_types ();
1810		# Initialize PEAR NET object
1811		$this->initialize_pear_net_IPv4 ();
1812		# check for each if member
1813	    foreach( $classes as $key=>$class ) {
1814	        if ($this->Net_IPv4->ipInNetwork($network, $class)) {
1815	            if ($this->Net_IPv4->ipInNetwork($broadcast, $class)) {
1816	                return($key);
1817	            }
1818	        }
1819	    }
1820	    # no match
1821	    return false;
1822	}
1823
1824	/**
1825	 * Defines all possible IPv4 address types
1826	 *
1827	 * @access private
1828	 * @return array
1829	 */
1830	private function define_ipv4_address_types () {
1831	    # define classes
1832	    $classes = array();
1833	    $classes['private A']          = '10.0.0.0/8';
1834	    $classes['private ']          = '172.16.0.0/12';
1835	    $classes['private C']          = '192.168.0.0/16';
1836	    $classes['Loopback']           = '127.0.0.0/8';
1837	    $classes['Link-local']         = '169.254.0.0/16';
1838	    $classes['Reserved (IANA)']    = '192.0.0.0/24';
1839	    $classes['TEST-NET-1']         = '192.0.2.0/24';
1840	    $classes['IPv6 to IPv4 relay'] = '192.88.99.0/24';
1841	    $classes['Network benchmark']  = '198.18.0.0/15';
1842	    $classes['TEST-NET-2']         = '198.51.100.0/24';
1843	    $classes['TEST-NET-3']         = '203.0.113.0/24';
1844	    $classes['Multicast']          = '224.0.0.0/4';
1845	    $classes['Reserved']           = '240.0.0.0/4';
1846	    # result
1847	    return $classes;
1848	}
1849
1850	/**
1851	 * Calculates IPv6 from cidr
1852	 *
1853	 * @access private
1854	 * @param mixed $cidr
1855	 * @return array
1856	 */
1857	private function calculate_IPv6_calc_results ($cidr) {
1858		# initialize subnets Class
1859		$Subnets = new Subnets($this->Database);
1860		# Initialize PEAR NET object
1861		$this->initialize_pear_net_IPv6 ();
1862
1863        # set ip address type
1864        $out = array();
1865        $out['Type']                      = 'IPv6';
1866
1867        # calculate network details
1868        $out['Host address']              = $cidr;
1869        $out['Host address']              = $this->Net_IPv6->compress ( $out['Host address'], 1 );
1870        $out['Host address (uncompressed)'] = $this->Net_IPv6->uncompress ( $out['Host address'] );
1871
1872        $mask                             = $this->Net_IPv6->getNetmaskSpec( $cidr );
1873        $subnet                           = $this->Net_IPv6->getNetmask( $cidr );
1874        $out['Subnet prefix']             = $this->Net_IPv6->compress ( $subnet ) .'/'. $mask;
1875        $out['Prefix length']             = $this->Net_IPv6->getNetmaskSpec( $cidr );
1876
1877        # get reverse DNS entries
1878        $out['Host Reverse DNS']   = $this->reverse_IPv6($out['Host address (uncompressed)']);
1879        $out['Subnet Reverse DNS'] = $this->reverse_IPv6($subnet, $mask);
1880
1881        # if IP == subnet clear the Host fields and Host Reverse DNS
1882         if ($out['Host address'] == $out['Subnet prefix']) {
1883             $out['Host address']                = '/';
1884             $out['Host address (uncompressed)'] = '/';
1885             unset($out['Host Reverse DNS']);
1886        }
1887
1888        # /min / max hosts
1889        $maxIp = gmp_strval(gmp_add(gmp_pow(2, 128 - $mask),$this->ip2long6 ($subnet)));
1890		$maxIp = gmp_strval(gmp_sub($maxIp, 1));
1891
1892        $out['Min host IP']               = $subnet;
1893        $out['Max host IP']               = $this->long2ip6 ($maxIp);
1894        $out['Number of hosts']           = $Subnets->get_max_hosts ($mask, "IPv6");
1895
1896        # set address type
1897        $out['Address type']              = $this->get_ipv6_address_type( $cidr );
1898		# result
1899		return $out;
1900	}
1901
1902	/**
1903	 * Calculate reverse DNS entry for IPv6 addresses
1904	 *
1905	 *	If a prefix length is given, generate only up to this length (ie. for zone definitions)
1906	 *
1907	 * @access public
1908	 * @param mixed $addresses
1909	 * @param int $pflen (default: 128)
1910	 * @return string
1911	 */
1912	public function reverse_IPv6 ($addresses, $pflen=128) {
1913		# Initialize PEAR NET object
1914		$this->initialize_pear_net_IPv6 ();
1915		//uncompress
1916	    $uncompressed = $this->Net_IPv6->removeNetmaskSpec($this->Net_IPv6->uncompress($addresses));
1917	    $len = $pflen / 4;
1918	    $parts = explode(':', $uncompressed);
1919	    $res = '';
1920	    foreach($parts as $part) {
1921	        $res .= str_pad($part, 4, '0', STR_PAD_LEFT);
1922	    }
1923	    $res = implode('.', str_split(strrev(substr($res, 0, $len)))) . '.ip6.arpa';
1924	    if ($pflen % 4 != 0) {
1925	        $res .= " "._("(closest parent)");
1926	    }
1927	    return $res;
1928	}
1929
1930	/**
1931	 * Returns IPv6 address type from cidr
1932	 *
1933	 * @access private
1934	 * @param CIDR $cidr
1935	 * @return string|false
1936	 */
1937	private function get_ipv6_address_type ($cidr) {
1938		# Initialize PEAR NET object
1939		$this->initialize_pear_net_IPv6 ();
1940		# get type in number
1941		$type = $this->Net_IPv6->getAddressType($cidr);
1942		# fetch types
1943		$all_types = $this->define_ipv6_address_types ();
1944		# translate
1945		return array_key_exists($type, $all_types) ? $all_types[$type] : false;
1946	}
1947
1948	/**
1949	 * Defines all IPv6 address types
1950	 *
1951	 * @access private
1952	 * @return string[]
1953	 */
1954	private function define_ipv6_address_types () {
1955        $all_types[10] = "NET_IPV6_NO_NETMASK";
1956        $all_types[1]  = "NET_IPV6";
1957        $all_types[11] = "NET_IPV6_RESERVED";
1958        $all_types[12] = "NET_IPV6_RESERVED_NSAP";
1959        $all_types[13] = "NET_IPV6_RESERVED_IPX";
1960        $all_types[14] = "NET_IPV6_RESERVED_UNICAST_GEOGRAPHIC";
1961        $all_types[22] = "NET_IPV6_UNICAST_PROVIDER";
1962        $all_types[31] = "NET_IPV6_MULTICAST";
1963        $all_types[42] = "NET_IPV6_LOCAL_LINK";
1964        $all_types[43] = "NET_IPV6_LOCAL_SITE";
1965        $all_types[51] = "NET_IPV6_IPV4MAPPING";
1966        $all_types[51] = "NET_IPV6_UNSPECIFIED";
1967        $all_types[51] = "NET_IPV6_LOOPBACK";
1968        $all_types[51] = "NET_IPV6_UNKNOWN_TYPE";
1969		# response
1970        return $all_types;
1971	}
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986	/**
1987	 *	@nat methods
1988	 *	------------------------------
1989	 */
1990
1991    /**
1992     * Translates NAT objects to be shown on page
1993     *
1994     * @access public
1995     * @param json $json_objects
1996     * @param int|bool $nat_id (default: false)
1997     * @param bool $json_objects (default: false)
1998     * @param bool $object_type (default: false) - to bold it (ipaddresses / subnets)
1999     * @param int|bool object_id (default: false) - to bold it
2000     * @return array|bool
2001     */
2002    public function translate_nat_objects_for_display ($json_objects, $nat_id = false, $admin = false, $object_type = false, $object_id=false) {
2003        // to array "subnets"=>array(1,2,3)
2004        $objects = json_decode($json_objects, true);
2005        // init out array
2006        $out = array();
2007        // set ping statuses for warning and offline
2008        $this->get_settings();
2009        $statuses = explode(";", $this->settings->pingStatus);
2010        // check
2011        if(is_array($objects)) {
2012            if(sizeof($objects)>0) {
2013                foreach ($objects as $ot=>$ids) {
2014                    if (sizeof($ids)>0) {
2015                        foreach ($ids as $id) {
2016                            // fetch
2017                            $item = $this->fetch_object($ot, "id", $id);
2018                            if($item!==false) {
2019                                // bold
2020                                $bold = $item->id==$object_id && $ot==$object_type ? "<span class='strong'>" : "<span>";
2021                                // remove
2022                                $remove = $admin&&$nat_id ? "<span class='remove-nat-item-wrapper_".$ot."_".$item->id."'><a class='btn btn-xs btn-danger removeNatItem' data-id='$nat_id' data-type='$ot' data-item-id='$item->id' rel='tooltip' title='"._('Remove')."'><i class='fa fa-times'></i></a>" : "<span>";
2023                                // subnets
2024                                if ($ot=="subnets") {
2025                                    $out[] = "$remove $bold<a href='".create_link("subnets", $item->sectionId, $item->id)."'>".$this->transform_address($item->subnet, "dotted")."/".$item->mask."</a></span></span>";
2026                                }
2027                                // addresses
2028                                else {
2029                                    // subnet
2030                                    $snet = $this->fetch_object("subnets", "id", $item->subnetId);
2031                                    // append status
2032                                    if ($snet->pingSubnet=="1") {
2033                                        //calculate
2034                                        $tDiff = time() - strtotime($item->lastSeen);
2035                                        if($item->excludePing=="1" )    { $hStatus = "padded"; $hTooltip = ""; }
2036                                        elseif(is_null($item->lastSeen)) { $hStatus = "neutral"; $hTooltip = "rel='tooltip' data-container='body' data-html='true' data-placement='left' title='"._("Address was never online")."'"; }
2037                                        elseif($tDiff < $statuses[0])	{ $hStatus = "success";	$hTooltip = "rel='tooltip' data-container='body' data-html='true' data-placement='left' title='"._("Address is alive")."<hr>"._("Last seen").": ".$item->lastSeen."'"; }
2038                                        elseif($tDiff < $statuses[1])	{ $hStatus = "warning"; $hTooltip = "rel='tooltip' data-container='body' data-html='true' data-placement='left' title='"._("Address warning")."<hr>"._("Last seen").": ".$item->lastSeen."'"; }
2039                                        elseif($tDiff > $statuses[1])	{ $hStatus = "error"; 	$hTooltip = "rel='tooltip' data-container='body' data-html='true' data-placement='left' title='"._("Address is offline")."<hr>"._("Last seen").": ".$item->lastSeen."'";}
2040                                        elseif($item->lastSeen == "0000-00-00 00:00:00") { $hStatus = "neutral"; 	$hTooltip = "rel='tooltip' data-container='body' data-html='true' data-placement='left' title='"._("Address is offline")."<hr>"._("Last seen").": "._("Never")."'";}
2041                                        elseif($item->lastSeen == "1970-01-01 00:00:01") { $hStatus = "neutral"; 	$hTooltip = "rel='tooltip' data-container='body' data-html='true' data-placement='left' title='"._("Address is offline")."<hr>"._("Last seen").": "._("Never")."'";}
2042                                        else							{ $hStatus = "neutral"; $hTooltip = "rel='tooltip' data-container='body' data-html='true' data-placement='left' title='"._("Address status unknown")."'";}
2043                                    }
2044                                    else {
2045                                        $hStatus = "hidden";
2046                                        $hTooltip = "";
2047                                    }
2048                                    if($remove=="<span>") {
2049                                        $remove .= "<span class='status status-$hStatus' $hTooltip></span>";
2050                                    }
2051
2052                                    $out[] = "$remove $bold <a href='".create_link("subnets", $snet->sectionId, $item->subnetId, "address-details", $item->id)."'>".$this->transform_address($item->ip_addr, "dotted")."</a></span>";
2053                                }
2054                            }
2055                        }
2056                    }
2057                }
2058            }
2059        }
2060        // result
2061        return sizeof($out)>0 ? $out : false;
2062    }
2063
2064    /**
2065     * This function will reindex all nat object to following structure:
2066     *
2067     *  ipaddresses => array (
2068     *                  [address_id] => array (nat_id1, nat_id2)
2069     *              )
2070     *  subnets => array (
2071     *                  [subnet_id] => array (nat_id1, nat_id2)
2072     *              )
2073     *
2074     * @access public
2075     * @param array $all_nats (default: array())
2076     * @return array
2077     */
2078    public function reindex_nat_objects ($all_nats = array()) {
2079        // out array
2080        $out = array(
2081            "ipaddresses"=>array(),
2082            "subnets"=>array()
2083        );
2084        // loop
2085        if(is_array($all_nats)) {
2086            if (sizeof($all_nats)>0) {
2087                foreach ($all_nats as $n) {
2088                    $src = json_decode($n->src, true);
2089                    $dst = json_decode($n->dst, true);
2090
2091                    // src
2092                    if(is_array($src)) {
2093                        if(is_array(@$src['subnets'])) {
2094                            foreach ($src['subnets'] as $s) {
2095                                $out['subnets'][$s][] = $n->id;
2096                            }
2097                        }
2098                        if(is_array(@$src['ipaddresses'])) {
2099                            foreach ($src['ipaddresses'] as $s) {
2100                                $out['ipaddresses'][$s][] = $n->id;
2101                            }
2102                        }
2103                    }
2104                    // dst
2105                    if(is_array($dst)) {
2106                        if(is_array(@$dst['subnets'])) {
2107                            foreach ($dst['subnets'] as $s) {
2108                                $out['subnets'][$s][] = $n->id;
2109                            }
2110                        }
2111                        if(is_array(@$dst['ipaddresses'])) {
2112                            foreach ($dst['ipaddresses'] as $s) {
2113                                $out['ipaddresses'][$s][] = $n->id;
2114                            }
2115                        }
2116                    }
2117                }
2118            }
2119        }
2120        // return
2121        return $out;
2122    }
2123
2124    /**
2125     * Prints single NAT for display in devices, subnets, addresses.
2126     *
2127     * @access public
2128     * @param mixed $n
2129     * @param bool $is_admin (default: false)
2130     * @param bool|int $nat_id (default: false)
2131     * @param bool $admin (default: false) > shows remove links
2132     * @param bool|mixed $object_type (default: false)
2133     * @param bool $object_id (default: false)
2134     * @param string $actions_menu
2135     * @return string
2136     */
2137    public function print_nat_table ($n, $is_admin = false, $nat_id = false, $admin = false, $object_type = false, $object_id=false, $actions_menu = "") {
2138        // cast to object to be sure if array provided
2139        $n = (object) $n;
2140
2141        // translate json to array, links etc
2142        $sources      = $this->translate_nat_objects_for_display ($n->src, $nat_id, $admin, $object_type, $object_id);
2143        $destinations = $this->translate_nat_objects_for_display ($n->dst, $nat_id, $admin, $object_type, $object_id);
2144
2145        // no src/dst
2146        if ($sources===false)
2147            $sources = array("<span class='badge badge1 badge5 alert-danger'>"._("None")."</span>");
2148        if ($destinations===false)
2149            $destinations = array("<span class='badge badge1 badge5 alert-danger'>"._("None")."</span>");
2150
2151        // description
2152        $n->description = str_replace("\n", "<br>", $n->description);
2153        $n->description = strlen($n->description)>0 ? "<br>$n->description" : "";
2154
2155        // device
2156        if (strlen($n->device)) {
2157            if($n->device !== 0) {
2158                $device = $this->fetch_object ("devices", "id", $n->device);
2159                $description = strlen($device->description)>0 ? " ($device->description)" : "";
2160                $n->device = $device===false ? "/" : "<a href='".create_link("tools", "devices", $device->id)."'>$device->hostname</a> ($device->ip_addr) <span class='text-muted'>$description</span>";
2161            }
2162        }
2163        else {
2164            $n->device = "/";
2165        }
2166
2167        // icon
2168        $icon =  $n->type=="static" ? "fa-arrows-h" : "fa-long-arrow-right";
2169
2170        // to html
2171        $html = array();
2172        $html[] = "<tr>";
2173        $html[] = "<td colspan='4'>";
2174        $html[] = "<span class='badge badge1 badge5'>".ucwords($n->type)."</span> <strong>$n->name</strong> <span class='text-muted'>$n->description</span>";
2175        $html[] = "<span class='pull-right'>";
2176        $html[] = $actions_menu;
2177        $html[] = "</span>";
2178        $html[] = "</td>";
2179        $html[] = "</tr>";
2180
2181        // append ports
2182        if(($n->type=="static" || $n->type=="destination") && (strlen($n->src_port)>0 && strlen($n->dst_port)>0)) {
2183            $sources      = implode("<br>", $sources)." :".$n->src_port;
2184            $destinations = implode("<br>", $destinations)." :".$n->dst_port;
2185        }
2186        else {
2187            $sources      = implode("<br>", $sources);
2188            $destinations = implode("<br>", $destinations);
2189        }
2190
2191        $html[] = "<tr>";
2192        $html[] = "<td style='width:80px;'></td>";
2193        $html[] = "<td>$sources</td>";
2194        $html[] = "<td><i class='fa $icon'></i></td>";
2195        $html[] = "<td>$destinations</td>";
2196        $html[] = "</tr>";
2197
2198        $html[] = "<tr>";
2199        $html[] = "<td></td>";
2200        $html[] = "<td colspan='3'><span class='text-muted'>";
2201        $html[] = _('Device').": $n->device";
2202        $html[] = "</span></td>";
2203        $html[] = "</tr>";
2204
2205        // actions
2206        if($is_admin) {
2207        $html[] = "<tr>";
2208        $html[] = "<td colspan='4'><hr></td>";
2209        $html[] = "</tr>";
2210        }
2211        // return
2212        return implode("\n", $html);
2213    }
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227	/**
2228	 *	@pstn methods
2229	 *	------------------------------
2230	 *
2231	 *  !pstn
2232	 */
2233
2234    /**
2235     * Returns all prefixes in correct order
2236     *
2237     * @access public
2238     * @return void
2239     * @param bool|int $master (default: false)
2240     * @param bool $recursive (default: false)
2241     * @return array|bool
2242     */
2243    public function fetch_all_prefixes ($master = false, $recursive = false) {
2244	    if($master && !$recursive) {
2245    	    $query = 'select *,concat(prefix,start) as raw from pstnPrefixes where master = ? order by raw asc;';
2246    	    $params = array($master);
2247	    }
2248	    else {
2249    	    $query = 'select *,concat(prefix,start) as raw from pstnPrefixes order by raw asc;';
2250    	    $params = array();
2251        }
2252		# fetch
2253		try { $prefixes = $this->Database->getObjectsQuery($query, $params); }
2254		catch (Exception $e) {
2255			$this->Result->show("danger", _("Error: ").$e->getMessage());
2256			return false;
2257		}
2258        // for master + recursive we need to go from master id to next 0 (root
2259        if($master && $recursive && $prefixes) {
2260            $master_set = false;
2261            $out = array();
2262
2263            foreach ($prefixes as $k=>$p) {
2264        	    if($p->id == $master) {
2265            	    $out[] = $p;
2266            	    $master_set = true;
2267        	    }
2268        	    elseif ($master_set && $p->master!=0) {
2269            	    $out[] = $p;
2270        	    }
2271        	    elseif ($master_set && $p->master!=0) {
2272            	    break;
2273        	    }
2274            }
2275            $prefixes = $out;
2276        }
2277		# result
2278		return sizeof($prefixes)>0 ? array_values($prefixes) : false;
2279    }
2280
2281    /**
2282     * Normalize prefix / number.
2283     *
2284     * @access public
2285     * @param mixed $number
2286     * @return mixed
2287     */
2288    public function prefix_normalize ($number) {
2289        return str_replace(array("+", " ", "-"), "", $number);
2290    }
2291
2292	/**
2293	 * fetch whole tree path for prefix - from slave to parents
2294	 *
2295	 * @access public
2296	 * @param mixed $id
2297	 * @return array
2298	 */
2299	public function fetch_prefix_parents_recursive ($id) {
2300		$parents = array();
2301		$root = false;
2302
2303		while($root === false) {
2304			$subd = $this->fetch_object("pstnPrefixes", "id", $id);		# get subnet details
2305
2306			if($subd!==false) {
2307    			$subd = (array) $subd;
2308				# not root yet
2309				if(@$subd['master']!=0) {
2310					array_unshift($parents, $subd['master']);
2311					$id  = $subd['master'];
2312				}
2313				# root
2314				else {
2315					array_unshift($parents, $subd['master']);
2316					$root = true;
2317				}
2318			}
2319			else {
2320				$root = true;
2321			}
2322		}
2323		# remove 0
2324		unset($parents[0]);
2325		# return array
2326		return $parents;
2327	}
2328
2329	/**
2330	 * Fetch parents recursive - generic function
2331	 *
2332	 * Fetches all parents for specific table in id / parent relations
2333	 *
2334	 * It will return array, keys will be id's and values as defined in return_field
2335	 *
2336	 * @param string $table
2337	 * @param string $parent_field
2338	 * @param string $return_field
2339	 * @param int $false
2340	 * @param bool $reverse (default: true)
2341	 *
2342	 * @return array
2343	 */
2344	public function fetch_parents_recursive ($table, $parent_field, $return_field, $id, $reverse = false) {
2345		$parents = array();
2346		$root = false;
2347
2348		while($root === false) {
2349			$subd = $this->fetch_object($table, "id", $id);
2350
2351			if($subd!==false) {
2352    			$subd = (array) $subd;
2353				# not root yet
2354				if(@$subd[$parent_field]!=0) {
2355					// array_unshift($parents, $subd[$parent_field]);
2356					$parents[$subd['id']] = $subd[$return_field];
2357					$id  = $subd[$parent_field];
2358				}
2359				# root
2360				else {
2361					$parents[$subd['id']] = $subd[$return_field];
2362					$root = true;
2363				}
2364			}
2365			else {
2366				$root = true;
2367			}
2368		}
2369		# return array
2370		return $reverse ? array_reverse($parents, true) :$parents;
2371	}
2372
2373	/**
2374	 * Checks for duplicate number.
2375	 *
2376	 * @access public
2377	 * @param bool $prefix (default: false)
2378	 * @param bool $number (default: false)
2379	 * @return null|boolean
2380	 */
2381	public function check_number_duplicates ($prefix = false, $number = false) {
2382    	if($prefix===false && $number===false) {
2383        	$this->Result->show("danger", "Duplicate chck failed", true);
2384    	}
2385    	else {
2386        	$query = "select count(*) as cnt from pstnNumbers where prefix = ? and number = ?;";
2387    		# fetch
2388    		try { $cnt = $this->Database->getObjectQuery($query, array($prefix, $number)); }
2389    		catch (Exception $e) {
2390    			$this->Result->show("danger", _("Error: ").$e->getMessage());
2391    			return false;
2392    		}
2393    		# result
2394    		return $cnt->cnt>0 ? true : false;
2395    	}
2396	}
2397
2398	/**
2399	 * Prints structured menu of prefixes
2400	 *
2401	 * @access public
2402	 * @param mixed $user
2403	 * @param mixed $prefixes
2404	 * @param mixed $custom_fields
2405	 * @return mixed
2406	 */
2407	public function print_menu_prefixes ( $user, $prefixes, $custom_fields ) {
2408
2409		# user class for permissions
2410		$User = new User ($this->Database);
2411
2412		# set hidden fields
2413		$this->get_settings ();
2414		$hidden_fields = json_decode($this->settings->hiddenCustomFields, true);
2415		$hidden_fields = is_array($hidden_fields['subnets']) ? $hidden_fields['subnets'] : array();
2416
2417		# set html array
2418		$html = array();
2419		# root is 0
2420		$rootId = 0;
2421
2422		# remove all not permitted!
2423		if(sizeof($prefixes)>0) {
2424		foreach($prefixes as $k=>$s) {
2425			if($User->get_module_permissions ("pstn")<1) { unset($prefixes[$k]); }
2426		}
2427		}
2428
2429		# create loop array
2430		if(sizeof($prefixes) > 0) {
2431        $children_prefixes = array();
2432		foreach ( $prefixes as $item ) {
2433			$item = (array) $item;
2434			$children_prefixes[$item['master']][] = $item;
2435		}
2436		}
2437		else {
2438			return false;
2439		}
2440
2441		# loop will be false if the root has no children (i.e., an empty menu!)
2442		$loop = !empty( $children_prefixes[$rootId] );
2443
2444		# initializing $parent as the root
2445		$parent = $rootId;
2446		$parent_stack = array();
2447
2448		# old count
2449		$old_count = 0;
2450
2451		# return table content (tr and td's)
2452		reset( $children_prefixes[$parent] );
2453		while ( $loop && ( ( $option = current( $children_prefixes[$parent] ) ) || ( $parent > $rootId ) ) )
2454		{
2455			next( $children_prefixes[$parent] );
2456
2457			if(count($parent_stack) == 0) {
2458				$margin = "0px";
2459				$padding = "0px";
2460			}
2461			else {
2462				# padding
2463				$padding = "10px";
2464
2465				# margin
2466				$margin  = (count($parent_stack) * 10) -10;
2467				$margin  = $margin *1.5;
2468				$margin  = $margin."px";
2469			}
2470
2471			# count levels
2472			$count = count( $parent_stack ) + 1;
2473
2474			# description
2475			$name = strlen($option['name'])==0 ? "/" : $option['name'];
2476
2477			# print table line
2478			if(strlen($option['prefix']) > 0) {
2479    			# count change?
2480				$html[] = "<tr class='level$count'>";
2481
2482				//which level?
2483				if($count==1) {
2484					# last?
2485					if(!empty( $children_prefixes[$option['id']])) {
2486						$html[] = "	<td class='level$count'><span class='structure-last' style='padding-left:$padding; margin-left:$margin;'></span><i class='fa fa-gray fa-pad-right-3 fa-folder-open-o'></i><a href='".create_link($_GET['page'],"pstn-prefixes",$option['id'])."'>".$option['prefix']." </a></td>";
2487						$html[] = "	<td class='level$count'><span class='structure-last' style='padding-left:$padding; margin-left:$margin;'></span><i class='fa fa-gray fa-pad-right-3 fa-folder-open-o'></i> <strong>$name</strong></td>";
2488					} else {
2489						$html[] = "	<td class='level$count'><span class='structure' style='padding-left:$padding; margin-left:$margin;'></span><i class='fa fa-gray fa-pad-right-3 fa-angle-right'></i><a href='".create_link($_GET['page'],"pstn-prefixes",$option['id'])."'>".$option['prefix']." </a></td>";
2490						$html[] = "	<td class='level$count'><span class='structure' style='padding-left:$padding; margin-left:$margin;'></span><i class='fa fa-gray fa-pad-right-3 fa-angle-right'></i> <strong>$name</strong></td>";
2491					}
2492				}
2493				else {
2494					# last?
2495					if(!empty( $children_prefixes[$option['id']])) {
2496						$html[] = "	<td class='level$count'><span class='structure-last' style='padding-left:$padding; margin-left:$margin;'></span><i class='fa fa-gray fa-pad-right-3 fa-folder-open-o'></i> <a href='".create_link($_GET['page'],"pstn-prefixes",$option['id'])."'>  ".$option['prefix']."</a></td>";
2497						$html[] = "	<td class='level$count'><span class='structure-last' style='padding-left:$padding; margin-left:$margin;'></span><i class='fa fa-gray fa-pad-right-3 fa-folder-open-o'></i> <strong>$name</strong></td>";
2498					}
2499					else {
2500						$html[] = "	<td class='level$count'><span class='structure' style='padding-left:$padding; margin-left:$margin;'></span><i class='fa fa-gray fa-pad-right-3 fa-angle-right'></i> <a href='".create_link($_GET['page'],"pstn-prefixes",$option['id'])."'>  ".$option['prefix']."</a></td>";
2501						$html[] = "	<td class='level$count'><span class='structure' style='padding-left:$padding; margin-left:$margin;'></span><i class='fa fa-gray fa-pad-right-3 fa-angle-right'></i> <strong>$name</strong></td>";
2502					}
2503				}
2504
2505				// range
2506				$html[] = " <td class='level$count'><span class='structure-last' style='padding-left:$padding; margin-left:$margin;'></span><i class='fa fa-gray fa-pad-right-3 fa-angle-right'></i> ".$option['prefix'].$option['start']." ".$option['prefix'].$option['stop']."</td>";
2507
2508				//start/stop
2509				$html[] = "	<td>".$option['start']."</td>";
2510				$html[] = "	<td>".$option['stop']."</td>";
2511
2512				//count
2513                $cnt = $this->count_database_objects("pstnNumbers", "prefix", $option['id']);
2514
2515                $html[] = "	<td><span class='badge badge1 badge5'>".$cnt."</span></td>";
2516
2517				//device
2518				if($User->get_module_permissions ("devices")>1) {
2519					$device = ( $option['deviceId']==0 || empty($option['deviceId']) ) ? false : true;
2520
2521					if($device===false) { $html[] ='	<td>/</td>' . "\n"; }
2522					else {
2523						$device = $this->fetch_object ("devices", "id", $option['deviceId']);
2524						if ($device!==false) {
2525							$html[] = "	<td><a href='".create_link("tools","devices",$device->id)."'>".$device->hostname .'</a></td>' . "\n";
2526						}
2527						else {
2528							$html[] ='	<td>/</td>' . "\n";
2529						}
2530					}
2531				}
2532
2533				//custom
2534				if(sizeof($custom_fields) > 0) {
2535			   		foreach($custom_fields as $field) {
2536				   		# hidden?
2537				   		if(!in_array($field['name'], $hidden_fields)) {
2538
2539				   			$html[] =  "<td class='hidden-xs hidden-sm hidden-md'>";
2540
2541				   			//booleans
2542							if($field['type']=="tinyint(1)")	{
2543								if($option[$field['name']] == "0")			{ $html[] = _("No"); }
2544								elseif($option[$field['name']] == "1")		{ $html[] = _("Yes"); }
2545							}
2546							//text
2547							elseif($field['type']=="text") {
2548								if(strlen($option[$field['name']])>0)		{ $html[] = "<i class='fa fa-gray fa-comment' rel='tooltip' data-container='body' data-html='true' title='".str_replace("\n", "<br>", $option[$field['name']])."'>"; }
2549								else												{ $html[] = ""; }
2550							}
2551							else {
2552								$html[] = $option[$field['name']];
2553
2554							}
2555
2556				   			$html[] =  "</td>";
2557			   			}
2558			    	}
2559			    }
2560
2561			    // actions
2562				if($User->get_module_permissions ("pstn")>0) {
2563					$html[] = "	<td class='actions' style='padding:0px;'>";
2564					$links = [];
2565			        $links[] = ["type"=>"header", "text"=>"Show"];
2566			        $links[] = ["type"=>"link", "text"=>"View prefix", "href"=>create_link($_GET['page'], "pstn-prefixes", $option['id']), "icon"=>"eye", "visible"=>"dropdown"];
2567
2568			        if($User->get_module_permissions ("pstn")>1) {
2569			            $links[] = ["type"=>"divider"];
2570			            $links[] = ["type"=>"header", "text"=>"Manage"];
2571			            $links[] = ["type"=>"link", "text"=>"Edit prefix", "href"=>"", "class"=>"open_popup", "dataparams"=>" data-script='app/tools/pstn-prefixes/edit.php' data-class='700' data-action='edit' data-id='$option[id]'", "icon"=>"pencil"];
2572			        }
2573			        if($User->get_module_permissions ("pstn")>2) {
2574			            $links[] = ["type"=>"link", "text"=>"Delete prefix", "href"=>"", "class"=>"open_popup", "dataparams"=>" data-script='app/tools/pstn-prefixes/edit.php' data-class='700' data-action='delete' data-id='$option[id]'", "icon"=>"times"];
2575			        }
2576			        $html[] = $User->print_actions($User->user->compress_actions, $links);
2577					$html[] = "	</td>";
2578				}
2579
2580				$html[] = "</tr>";
2581
2582                # save old level count
2583                $old_count = $count;
2584			}
2585
2586			if ( $option === false ) { $parent = array_pop( $parent_stack ); }
2587			# Has slave subnets
2588			elseif ( !empty( $children_prefixes[$option['id']] ) ) {
2589				array_push( $parent_stack, $option['master'] );
2590				$parent = $option['id'];
2591			}
2592		}
2593		# print
2594		return $html;
2595	}
2596
2597
2598
2599
2600	/**
2601	 * Prints dropdown menu for master prefix selection in prefix editing
2602	 *
2603	 * @access public
2604	 * @param bool $prefixId (default: false)
2605	 * @return mixed
2606	 */
2607	public function print_masterprefix_dropdown_menu ($prefixId = false) {
2608
2609		# initialize vars
2610		$children_prefixes = array();
2611		$parent_stack_prefixes = array();
2612		$html = array();
2613		$rootId = 0;			// root is 0
2614		$parent = $rootId;      // initializing $parent as the root
2615
2616		# fetch all prefixes in section
2617		$all_prefixes = $this->fetch_all_prefixes ();
2618		if (!is_array($all_prefixes)) $all_prefixes = array();
2619		# folder or subnet?
2620		foreach($all_prefixes as $s) {
2621			$children_prefixes[$s->master][] = (array) $s;
2622		}
2623
2624		# loop will be false if the root has no children (i.e., an empty menu!)
2625		$loop  = !empty( $children_prefixes[$rootId] );
2626
2627		# structure
2628		$html[] = "<select name='master' class='form-control input-sm input-w-auto input-max-200'>";
2629
2630		# root subnet
2631		$html[] = "<option value='0'>"._("Root subnet")."</option>";
2632
2633		# return table content (tr and td's) - subnets
2634		if(sizeof($children_prefixes)>0) {
2635		reset( $children_prefixes[$parent] );
2636		while ( $loop && ( ( $option = current( $children_prefixes[$parent] ) ) || ( $parent > $rootId ) ) )
2637		{
2638			next( $children_prefixes[$parent] );
2639			# repeat
2640			$repeat  = str_repeat( " &nbsp;&nbsp; ", ( count($parent_stack_prefixes)) );
2641
2642			# selected
2643			$selected = $option['id'] == $prefixId ? "selected='selected'" : "";
2644			if($option['id'])
2645            $html[] = "<option value='".$option['id']."' $selected>$repeat ".$option['prefix']." (".$option['name'].")</option>";
2646
2647			if ( $option === false ) { $parent = array_pop( $parent_stack_prefixes ); }
2648			# Has slave subnets
2649			elseif ( !empty( $children_prefixes[$option['id']] ) ) {
2650				array_push( $parent_stack_prefixes, $option['master'] );
2651				$parent = $option['id'];
2652			}		}
2653		}
2654		$html[] = "</select>";
2655		# join and print
2656		print implode( "\n", $html );
2657	}
2658
2659
2660
2661
2662	/**
2663	 * This function compresses all pstn
2664	 *
2665	 *	input is array of pstn ranges
2666	 *	output compresses pstn range
2667	 *
2668	 * @access public
2669	 * @param array $numbers
2670	 * @return array
2671	 */
2672	public function compress_pstn_ranges ($numbers, $state=4) {
2673    	# set size
2674    	$size = sizeof($numbers);
2675    	// vars
2676    	$numbers_formatted = array();
2677
2678		# loop through IP addresses
2679		for($c=0; $c<$size; $c++) {
2680			# ignore already comressed range
2681			if($numbers[$c]->class!="compressed-range") {
2682				# gap between this and previous
2683				if(gmp_strval( @gmp_sub($numbers[$c]->number, $numbers[$c-1]->number)) != 1) {
2684					# remove index flag
2685					unset($fIndex);
2686					# save IP address
2687					$numbers_formatted[$c] = $numbers[$c];
2688					$numbers_formatted[$c]->class = "ip";
2689
2690					# no gap this -> next
2691					if(gmp_strval( @gmp_sub($numbers[$c]->number, $numbers[$c+1]->number)) == -1 && $numbers[$c]->state==$state) {
2692						//is state the same?
2693						if($numbers[$c]->state==$numbers[$c+1]->state) {
2694							$fIndex = $c;
2695							$numbers_formatted[$fIndex]->startIP = $numbers[$c]->number;
2696							$numbers_formatted[$c]->class = "compressed-range";
2697						}
2698					}
2699				}
2700				# no gap between this and previous
2701				else {
2702					# is state same as previous?
2703					if($numbers[$c]->state==$numbers[$c-1]->state && $numbers[$c]->state==$state) {
2704						$numbers_formatted[$fIndex]->stopIP = $numbers[$c]->number;	//adds dhcp state
2705						$numbers_formatted[$fIndex]->numHosts = gmp_strval( gmp_add(@gmp_sub($numbers[$c]->number, $numbers_formatted[$fIndex]->number),1));	//add number of hosts
2706					}
2707					# different state
2708					else {
2709						# remove index flag
2710						unset($fIndex);
2711						# save IP address
2712						$numbers_formatted[$c] = $numbers[$c];
2713						$numbers_formatted[$c]->class = "ip";
2714						# check if state is same as next to start range
2715						if($numbers[$c]->state==@$numbers[$c+1]->state &&  gmp_strval( @gmp_sub($numbers[$c]->number, $numbers[$c+1]->number)) == -1 && $numbers[$c]->state==$state) {
2716							$fIndex = $c;
2717							$numbers_formatted[$fIndex]->startIP = $numbers[$c]->number;
2718							$numbers_formatted[$c]->class = "compressed-range";
2719						}
2720					}
2721				}
2722			}
2723			else {
2724				# save already compressed
2725				$numbers_formatted[$c] = $numbers[$c];
2726			}
2727		}
2728		# overrwrite ipaddresses and rekey
2729		$addresses = @array_values($numbers_formatted);
2730		# return
2731		return $addresses;
2732	}
2733
2734	/**
2735	 * Calculates pstn usage - dhcp, active, ...
2736	 *
2737	 * @access public
2738	 * @param obj $prefix        //subnet in decimal format
2739	 * @param obj $numbers	     //netmask in decimal format
2740	 * @return array
2741	 */
2742	public function calculate_prefix_usege ($prefix, $numbers) {
2743	    # calculate max number of hosts
2744	    $details = array();
2745	    $details['maxhosts'] = ($prefix->stop - $prefix->start + 1);
2746
2747		# get IP address count per address type
2748		if($numbers!==false) {
2749		    $details_p = $this->calculate_prefix_usage_sort_numbers ($numbers);
2750    	    foreach($this->address_types as $t) {
2751    		    $details[$t['type']."_percent"] = round( ( ($details_p[$t['type']] * 100) / $details['maxhosts']), 2 );
2752    	    }
2753
2754            # calculate free hosts
2755            $details['freehosts'] =  $details['maxhosts'] - sizeof($numbers);
2756        }
2757        else {
2758            $details['freehosts'] =  $details['maxhosts'];
2759        }
2760	    # calculate use percentage for each type
2761	    $details['freehosts_percent'] = round( ( ($details['freehosts'] * 100) / $details['maxhosts']), 2 );
2762
2763	    # result
2764	    return $details;
2765	}
2766
2767	/**
2768	 * Calculates number usage per host type
2769	 *
2770	 * @access public
2771	 * @param mixed $numbers
2772	 * @return array
2773	 */
2774	public function calculate_prefix_usage_sort_numbers ($numbers) {
2775		$count = array();
2776		$count['used'] = 0;				//initial sum count
2777		# fetch address types
2778		$this->get_addresses_types();
2779		# create array of keys with initial value of 0
2780		foreach($this->address_types as $a) {
2781			$count[$a['type']] = 0;
2782		}
2783		# count
2784		if($numbers!==false) {
2785			foreach($numbers as $n) {
2786				$count[$this->translate_address_type($n->state)]++;
2787				$count['used'] = gmp_strval(gmp_add($count['used'], 1));
2788			}
2789		}
2790		# result
2791		return $count;
2792	}
2793
2794	/**
2795	 * Returns array of address types
2796	 *
2797	 * @access public
2798	 */
2799	public function get_addresses_types () {
2800		# from cache
2801		if($this->address_types == null) {
2802        	# fetch
2803        	$types = $this->fetch_all_objects ("ipTags", "id");
2804
2805            # save to array
2806			$types_out = array();
2807			foreach($types as $t) {
2808				$types_out[$t->id] = (array) $t;
2809			}
2810			# save to cache
2811			$this->address_types = $types_out;
2812		}
2813	}
2814
2815	/**
2816	 * Translates address type from index (int) to type
2817	 *
2818	 *	e.g.: 0 > offline
2819	 *
2820	 * @access public
2821	 * @param mixed $index
2822	 * @return array
2823	 */
2824	public function translate_address_type ($index) {
2825		# fetch
2826		$this->get_addresses_types();
2827		# return
2828		return $this->address_types[$index]["type"];
2829	}
2830
2831	/**
2832	 * explode $string using $delimiter and filter null values.
2833	 *
2834	 * @param  string $delimiter
2835	 * @param  string $string
2836	 * @return mixed
2837	 */
2838	public function explode_filtered($delimiter, $string) {
2839	    $ret = explode($delimiter, $string);
2840	    if (!is_array($ret))
2841	        return false;
2842	    return array_filter($ret);
2843	}
2844
2845
2846	/**
2847	 *	@location methods
2848	 *	------------------------------
2849	 *
2850	 *  !location
2851	 */
2852
2853    /**
2854     * Fetches all location objects.
2855     *
2856     * @access public
2857     * @param bool|int $id (default: false)
2858     * @param bool count (default: false)
2859     * @return array|bool
2860     */
2861    public function fetch_location_objects ($id = false, $count = false) {
2862        // check
2863        if(is_numeric($id)) {
2864            $id = $this->Database->escape ($id);
2865            // count ?
2866            $select = $count ? "count(*) as cnt " : "*";
2867            // query
2868            $query = "select $select from
2869                        (
2870                        SELECT d.id, d.hostname as name, '' as mask, 'devices' as type, '' as sectionId, d.location, d.description
2871                        FROM devices d
2872                        JOIN locations l
2873                        ON d.location = l.id
2874                        WHERE l.id = $id
2875
2876                        UNION ALL
2877                        SELECT r.id, r.name, '' as mask, 'racks' as type, '' as sectionId, r.location, r.description
2878                        FROM racks r
2879                        JOIN locations l
2880                        ON r.location = l.id
2881                        WHERE l.id = $id
2882
2883                        UNION ALL
2884                        SELECT s.id, s.subnet as name, s.mask, 'subnets' as type, s.sectionId, s.location, s.description
2885                        FROM subnets s
2886                        JOIN locations l
2887                        ON s.location = l.id
2888                        WHERE l.id = $id
2889
2890                        UNION ALL
2891                        SELECT a.id, a.ip_addr as name, 'mask', 'addresses' as type, a.subnetId as sectionId, a.location, a.hostname as description
2892                        FROM ipaddresses a
2893                        JOIN locations l
2894                        ON a.location = l.id
2895                        WHERE l.id = $id
2896
2897                        UNION ALL
2898                        SELECT c.id, c.cid as name, 'mask', 'circuit' as type, 'none' as sectionId, c.location2, 'none' as description
2899                        FROM circuits c
2900                        JOIN locations l
2901                        ON c.location1 = l.id
2902                        WHERE l.id = $id
2903
2904                        UNION ALL
2905                        SELECT c.id, c.cid as name, 'mask', 'circuit' as type, 'none' as sectionId, c.location2, '' as description
2906                        FROM circuits c
2907                        JOIN locations l
2908                        ON c.location2 = l.id
2909                        WHERE l.id = $id
2910                        )
2911                        as linked;";
2912
2913     		// fetch
2914    		try { $objects = $this->Database->getObjectsQuery($query); }
2915    		catch (Exception $e) { $this->Result->show("danger", $e->getMessage(), true); }
2916
2917    		// return
2918    		return sizeof($objects)>0 ? $objects : false;
2919        }
2920        else {
2921            return false;
2922        }
2923    }
2924
2925
2926
2927	/**
2928	 *	@misc methods
2929	 *	------------------------------
2930	 */
2931
2932	/**
2933	 * Fetches all circuits from database
2934	 *
2935	 * @method fetch_all_circuits
2936	 *
2937	 * @param  array $custom_circuit_fields
2938	 *
2939	 * @return false|array
2940	 */
2941	public function fetch_all_circuits ($custom_circuit_fields = array ()) {
2942		// set query
2943		$query[] = "select";
2944		$query[] = "c.id,c.cid,c.type,c.device1,c.location1,c.device2,c.location2,c.comment,c.customer_id,p.name,p.description,p.contact,c.capacity,p.id as pid,c.status";
2945		// custom fields
2946		if(is_array($custom_circuit_fields)) {
2947			if(sizeof($custom_circuit_fields)>0) {
2948				foreach ($custom_circuit_fields as $f) {
2949					$query[] = ",c.`".$f['name']."`";
2950				}
2951			}
2952		}
2953		$query[] = "from circuits as c, circuitProviders as p where c.provider = p.id";
2954		$query[] = "order by c.cid asc;";
2955		// fetch
2956		try { $circuits = $this->Database->getObjectsQuery(implode("\n", $query), array()); }
2957		catch (Exception $e) {
2958			$this->Result->show("danger", $e->getMessage(), true);
2959		}
2960		// return
2961		return sizeof($circuits)>0 ? $circuits : false;
2962	}
2963
2964	/**
2965	 * Fetches all logical circuits belonging to circuit
2966	 *
2967	 * @method fetch_all_logical_circuits_using_circuit
2968	 * @param  int $circuit_id
2969	 * @return array|false
2970	 */
2971	public function fetch_all_logical_circuits_using_circuit ($circuit_id) {
2972		// set query
2973		$query[] = "select";
2974		$query[] = "lc.*";
2975		$query[] = "from circuitsLogical as lc";
2976		$query[] = "join `circuitsLogicalMapping` mapping on mapping.logicalCircuit_id=lc.id";
2977		$query[] = "WHERE mapping.circuit_id = ?";
2978		$query[] = "order by lc.logical_cid asc;";
2979		// fetch
2980		try { $circuits = $this->Database->getObjectsQuery(implode("\n", $query), [$circuit_id]); }
2981		catch (Exception $e) {
2982			$this->Result->show("danger", $e->getMessage(), true);
2983		}
2984		// return
2985		return sizeof($circuits)>0 ? $circuits : false;
2986	}
2987
2988	/**
2989	 * Fetch all members of logical circuit
2990	 *
2991	 * @method fetch_all_logical_circuit_members
2992	 * @param  int $logical_circuit_id
2993	 * @return array|false
2994	 */
2995  	public function fetch_all_logical_circuit_members ($logical_circuit_id) {
2996  		// set query
2997		$query2[] = "SELECT";
2998		$query2[] = "c.*";
2999		$query2[] = "FROM `circuits` c";
3000		$query2[] = "join `circuitsLogicalMapping` mapping on mapping.circuit_id=c.id";
3001		$query2[] = "where mapping.logicalCircuit_id = ?";
3002		$query2[] = "order by mapping.`order`;";
3003		// fetch
3004		try { $circuits = $this->Database->getObjectsQuery(implode("\n", $query2), $logical_circuit_id); }
3005		catch (Exception $e) {
3006			$this->Result->show("danger", $e->getMessage(), true);
3007		}
3008		return sizeof($circuits)>0 ? $circuits : false;
3009	}
3010
3011	/**
3012	 * Fetches all circuits for specific provider
3013	 *
3014	 * @method fetch_all_circuits
3015	 *
3016	 * @param  int $provider_id
3017	 * @param  array $custom_circuit_fields
3018	 *
3019	 * @return false|array
3020	 */
3021	public function fetch_all_provider_circuits ($provider_id, $custom_circuit_fields = array ()) {
3022		// set query
3023		$query[] = "select";
3024		$query[] = "c.id,c.cid,c.type,c.device1,c.location1,c.device2,c.location2,p.name,p.description,p.contact,c.capacity,p.id as pid,c.status";
3025		// custom fields
3026		if(is_array($custom_circuit_fields)) {
3027			if(sizeof($custom_circuit_fields)>0) {
3028				foreach ($custom_circuit_fields as $f) {
3029					$query[] = ",c.`".$f['name']."`";
3030				}
3031			}
3032		}
3033		$query[] = "from circuits as c, circuitProviders as p where c.provider = p.id and c.provider = ?";
3034		$query[] = "order by c.cid asc;";
3035		// fetch
3036		try { $circuits = $this->Database->getObjectsQuery(implode("\n", $query), array($provider_id)); }
3037		catch (Exception $e) {
3038			$this->Result->show("danger", $e->getMessage(), true);
3039		}
3040		// return
3041		return sizeof($circuits)>0 ? $circuits : false;
3042	}
3043
3044
3045	/**
3046	 * Fetches all circuits for specific device
3047	 *
3048	 * @method fetch_all_circuits
3049	 *
3050	 * @param  int $device_id
3051	 *
3052	 * @return false|array
3053	 */
3054	public function fetch_all_device_circuits ($device_id) {
3055		// set query
3056		$query = "select
3057					c.id,c.cid,c.type,c.device1,c.location1,c.device2,c.location2,p.name,p.description,p.contact,c.capacity,p.id as pid,c.status
3058					from circuits as c, circuitProviders as p where c.provider = p.id and (c.device1 = :deviceid or c.device2 = :deviceid)
3059					order by c.cid asc;";
3060		// fetch
3061		try { $circuits = $this->Database->getObjectsQuery($query, array("deviceid"=>$device_id)); }
3062		catch (Exception $e) {
3063			$this->Result->show("danger", $e->getMessage(), true);
3064		}
3065		// return
3066		return sizeof($circuits)>0 ? $circuits : false;
3067	}
3068
3069	/**
3070	 * Reformat circuit location
3071	 *
3072	 * If device is provided return device
3073	 * If location return location
3074	 *
3075	 * result will be false or array of:
3076	 * 	- type => "devices" / "locations"
3077	 *  - icon => "fa-desktop / fa-map"
3078	 *  - id => $id
3079	 *  - name => "location or device name"
3080	 *  - location => "location index or NULL"
3081	 *  - rack => "NULL if location, rack_id if device is set with rack otherwise NULL"
3082	 *
3083	 * @method reformat_circuit_location
3084	 *
3085	 * @param  int $deviceId
3086	 * @param  int $locationId
3087	 *
3088	 * @return false|array
3089	 */
3090	public function reformat_circuit_location ($deviceId = null, $locationId = null) {
3091		// check device
3092		if(is_numeric($deviceId) && $deviceId!=0) {
3093			// fetch device
3094			$device = $this->fetch_object ("devices", "id", $deviceId);
3095			// check
3096			if ($device === false) {
3097				return false;
3098			}
3099			else {
3100				$array = array (
3101								"type"     => "devices",
3102								"id"       => $device->id,
3103								"name"     => $device->hostname,
3104								"icon" 	   => "",
3105								"location" => is_null($device->location)||$device->location==0 ? NULL : $device->location,
3106								"rack"     => is_null($device->rack)||$device->rack==0 ? NULL : $device->rack
3107				                );
3108				// check rack location if not configured
3109				if ($array['location']==NULL && $array['rack']!=NULL) {
3110					$rack_location = $this->fetch_object ("racks", "id", $array['rack']);
3111					$array['location'] = $rack_location!==false ? $rack_location->location : NULL;
3112				}
3113				// result
3114				return $array;
3115			}
3116		}
3117		// check location
3118		elseif (is_numeric($locationId) && $locationId!=0) {
3119			// fetch location
3120			$location = $this->fetch_object ("locations", "id", $locationId);
3121			// check
3122			if ($device === false) {
3123				return false;
3124			}
3125			else {
3126				$array = array (
3127								"type"     => "locations",
3128								"id"       => $location->id,
3129								"name"     => $location->name,
3130								"icon" 	   => "fa-map",
3131								"location" => $location->id,
3132								"rack"     => NULL
3133				                );
3134				return $array;
3135			}
3136		}
3137		else {
3138			return false;
3139		}
3140	}
3141
3142	/**
3143	 * Fetch all l2 domans and vlans
3144	 *
3145	 * @access public
3146	 * @param string $search (default: false)
3147	 * @return array|bool
3148	 */
3149	public function fetch_all_domains_and_vlans ($search = false) {
3150		// set query
3151		$query[] = "select `d`.`name` as `domainName`,";
3152		$query[] = "	`d`.`description` as `domainDescription`,";
3153		$query[] = "	`v`.`domainId` as `domainId`,";
3154		$query[] = "	`v`.`name` as `name`,";
3155		$query[] = "	`d`.`name` as `domainName`,";
3156		$query[] = "	`v`.`number` as `number`,";
3157		$query[] = "	`v`.`description` as `description`,";
3158		$query[] = "	`v`.`customer_id` as `customer_id`,";
3159		// fetch custom fields
3160		$custom_vlan_fields = $this->fetch_custom_fields ("vlans");
3161		if ($custom_vlan_fields != false) {
3162    		foreach ($custom_vlan_fields as $f) {
3163        		$query[] = "  `v`.`$f[name]` as `$f[name]`,";
3164    		}
3165
3166		}
3167		$query[] = "	`v`.`vlanId` as `id`";
3168		$query[] = "	from";
3169		$query[] = "	`vlans` as `v`,";
3170		$query[] = "	`vlanDomains` as `d`";
3171		$query[] = "	where `v`.`domainId` = `d`.`id`";
3172		$query[] = "	order by `v`.`number` asc;";
3173
3174		// fetch
3175		try { $domains = $this->Database->getObjectsQuery(implode("\n",$query)); }
3176		catch (Exception $e) { $this->Result->show("danger", $e->getMessage(), true); }
3177		// filter if requested
3178		if ($search !== false && sizeof($domains)>0) {
3179			foreach ($domains as $k=>$d) {
3180				if (strpos($d->number, $search)===false && strpos($d->name, $search)===false && strpos($d->description, $search)===false) {
3181					unset($domains[$k]);
3182				}
3183			}
3184		}
3185		// return
3186		return sizeof($domains)>0 ? $domains : false;
3187	}
3188
3189	/**
3190	 * Fetch all objects belonging to customer
3191	 *
3192	 * @method fetch_customer_objects
3193	 * @param  int $customer_id
3194	 * @return void
3195	 */
3196	public function fetch_customer_objects ($customer_id) {
3197		// out
3198		$out = [];
3199		// fetch
3200		if(is_numeric($customer_id)) {
3201			foreach ($this->get_customer_object_types() as $table=>$name) {
3202				$objects = $this->fetch_multiple_objects ($table, "customer_id", $customer_id, $this->get_customer_object_types_sorts ($table));
3203				if ($objects!==false) {
3204					$out[$table] = $objects;
3205				}
3206			}
3207		}
3208		// return
3209		return $out;
3210	}
3211
3212
3213	/**
3214	 * Fetch all routing subnets
3215	 *
3216	 * @method fetch_routing_subnets
3217	 * @param  string $type [bgp,ospf]
3218	 * @param  int $id (default: 0)
3219	 * @param  bool $cnt (default: true)
3220	 * @return false|array
3221	 */
3222	public function fetch_routing_subnets ($type="bgp", $id = 0, $cnt = false) {
3223		// set type
3224		$type = $type=="bgp" ? "bgp" : "ospf";
3225		// set count
3226		$fields = $cnt ? "count(*) as cnt" : "*,s.id as subnet_id";
3227		// set query
3228		$query = "select $fields from subnets as s, routing_subnets as r
3229					where r.type = ? and r.object_id = ? and r.subnet_id = s.id
3230					order by r.direction asc, s.subnet asc;";
3231		// fetch
3232		try { $subnets = $this->Database->getObjectsQuery($query, [$type, $id]); }
3233		catch (Exception $e) {
3234			$this->Result->show("danger", $e->getMessage(), true);
3235		}
3236		// return
3237		return sizeof($subnets)>0 ? $subnets : false;
3238	}
3239
3240
3241
3242	/**
3243	 * Return all possible customer object relations
3244	 *
3245	 * @method get_customer_object_types
3246	 * @return array
3247	 */
3248	public function get_customer_object_types () {
3249		return [
3250				"subnets"     => "Subnets",
3251				"ipaddresses" => "Addresses",
3252				"vlans"       => "VLAN",
3253				"vrf"         => "VRF",
3254				"circuits"    => "Circuits",
3255				"racks"       => "Racks",
3256				"routing_bgp" => "BGP Routing"
3257				];
3258	}
3259
3260	/**
3261	 * Return sorting for fetch_multiple_objects
3262	 *
3263	 * @method get_customer_object_types_sorts
3264	 * @param  string $type
3265	 * @return string
3266	 */
3267	private function get_customer_object_types_sorts ($type) {
3268		switch ($type) {
3269			case "subnets"     : return "subnet";
3270			case "ipaddresses" : return "ip_addr";
3271			case "vlans"       : return "number";
3272			case "vrf"         : return "name";
3273			case "circuits"    : return "cid";
3274			case "racks"       : return "name";
3275			default            : return "id";
3276		}
3277	}
3278
3279	/**
3280	 * Parses import file
3281	 *
3282	 * @access public
3283	 * @param string $filetype (default: "xls")
3284	 * @param object $subnet
3285	 * @param array $custom_address_fields
3286	 * @return array
3287	 */
3288	public function parse_import_file ($filetype = "xls", $subnet = object, $custom_address_fields) {
3289    	# start object and get settings
3290    	$this->get_settings ();
3291    	$this->Subnets = new Subnets ($this->Database);
3292
3293        # CSV
3294        if (strtolower($filetype) == "csv")     { $outFile = $this->parse_import_file_csv (); }
3295        # XLS
3296        elseif(strtolower($filetype) == "xls")  { $outFile = $this->parse_import_file_xls ($subnet, $custom_address_fields); }
3297        # error
3298        else                                    { $this->Result->show("danger", _('Invalid filetype'), true); }
3299
3300        # validate
3301        return $this->parse_validate_file ($outFile, $subnet);
3302	}
3303
3304	/**
3305	 * Parses xls import file
3306	 *
3307	 * @access private
3308	 * @param object $subnet
3309	 * @param array $custom_address_fields
3310	 * @return mixed
3311	 */
3312	private function parse_import_file_xls ($subnet, $custom_address_fields) {
3313     	# get excel object
3314    	require_once(dirname(__FILE__).'/../../functions/php-excel-reader/excel_reader2.php');				//excel reader 2.21
3315    	$data = new Spreadsheet_Excel_Reader(dirname(__FILE__) . '/../../app/subnets/import-subnet/upload/import.xls', false, 'utf-8');
3316
3317    	//get number of rows
3318    	$numRows = $data->rowcount(0);
3319    	$numRows++;
3320
3321    	$outFile = array();
3322
3323    	// set delimiter
3324    	$this->csv_delimiter = ";";
3325
3326    	//get all to array!
3327    	for($m=0; $m < $numRows; $m++) {
3328
3329    		//IP must be present!
3330    		if(filter_var($data->val($m,'A'), FILTER_VALIDATE_IP)) {
3331        		//for multicast
3332        		$mac = $data->val($m,'F');
3333        		if ($this->settings->enableMulticast=="1") {
3334            		if (strlen($data->val($m,'F'))==0 && $this->Subnets->is_multicast($data->val($m,'A')))    {
3335                		$mac = $this->Subnets->create_multicast_mac ($data->val($m,'A'));
3336                    }
3337                }
3338
3339    			$outFile[$m]  = $data->val($m,'A').$this->csv_delimiter.$data->val($m,'B').$this->csv_delimiter.$data->val($m,'C').$this->csv_delimiter.$data->val($m,'D').$this->csv_delimiter;
3340    			$outFile[$m] .= $data->val($m,'E').$this->csv_delimiter.$mac.$this->csv_delimiter.$data->val($m,'G').$this->csv_delimiter.$data->val($m,'H').$this->csv_delimiter;
3341    			$outFile[$m] .= $data->val($m,'I').$this->csv_delimiter.$data->val($m,'J').$this->csv_delimiter.$data->val($m,'K');
3342    			//add custom fields
3343    			if(sizeof($custom_address_fields) > 0) {
3344    				$currLett = "L";
3345    				foreach($custom_address_fields as $field) {
3346    					$outFile[$m] .= $this->csv_delimiter.$data->val($m,$currLett++);
3347    				}
3348    			}
3349    			$outFile[$m] = $this->convert_encoding_to_UTF8($outFile[$m]);
3350    		}
3351    	};
3352    	// return
3353    	return $outFile;
3354	}
3355
3356	/**
3357	 * Parses CSV import file
3358	 *
3359	 * @access private
3360	 * @return array
3361	 */
3362	private function parse_import_file_csv () {
3363    	// get file to string
3364		$handle = fopen(dirname(__FILE__) . '/../../app/subnets/import-subnet/upload/import.csv', "r");
3365		if ($handle) {
3366		    while (($outFile[] = fgets($handle)) !== false) {}
3367		    fclose($handle);
3368		} else {
3369		    $this->Result->show("danger", _('Cannot open upload/import.csv'), true);
3370		}
3371
3372    	// delimiter
3373    	if(isset($outFile[0]))
3374    	$this->set_csv_delimiter ($outFile[0]);
3375
3376    	/* validate IP */
3377    	foreach($outFile as $k=>$v) {
3378        	//put it to array
3379        	$field = str_getcsv ($v, $this->csv_delimiter);
3380
3381        	if(!filter_var($field[0], FILTER_VALIDATE_IP)) {
3382            	unset($outFile[$k]);
3383            	unset($field);
3384        	}
3385        	else {
3386            	# mac
3387        		if ($this->settings->enableMulticast=="1") {
3388            		if (strlen($field[5])==0 && $this->Subnets->is_multicast($field[0]))  {
3389                		$field[5] = $this->Subnets->create_multicast_mac ($field[0]);
3390                    }
3391        		}
3392        	}
3393
3394        	# save
3395        	if(isset($field)) {
3396	        	$outFile[$k] = implode($this->csv_delimiter, $field);
3397    		}
3398    	}
3399
3400    	# return
3401    	return $outFile;
3402	}
3403
3404	/**
3405	 * Detects CSV delimiter
3406	 *
3407	 * @method set_csv_delimiter
3408	 * @param  string $outFile
3409	 * @return string
3410	 */
3411	public function set_csv_delimiter ($outFile) {
3412		// must be string
3413		if(is_string($outFile)) {
3414			// count occurences
3415			$cnt_coma  = substr_count($outFile, ",");
3416			$cnt_colon = substr_count($outFile, ";");
3417			// set higher
3418			$this->csv_delimiter = $cnt_coma > $cnt_colon ? "," : ";";
3419		}
3420		else {
3421			$this->csv_delimiter = ",";
3422		}
3423	}
3424
3425	/**
3426	 * Validates each import line from provided array
3427	 *
3428	 *      append class to array
3429	 *
3430	 * @access private
3431	 * @param mixed $outFile
3432	 * @param object $subnet
3433	 * @return void
3434	 */
3435	private function parse_validate_file ($outFile = array(), $subnet = object) {
3436    	$result = array();
3437    	# present ?
3438    	if (sizeof($outFile)>0) {
3439            foreach($outFile as $k=>$line) {
3440
3441            	//convert encoding if necessary
3442            	$line = $this->convert_encoding_to_UTF8($line);
3443
3444            	//put it to array
3445            	$field = str_getcsv ($line, $this->csv_delimiter);
3446
3447            	//verify IP address
3448            	if(!filter_var($field[0], FILTER_VALIDATE_IP)) 	{ $class = "danger";	$errors++; }
3449            	else											{ $class = ""; }
3450
3451            	// verify that address is in subnet for subnets
3452            	if($subnet->isFolder!="1") {
3453					// check if IP is IPv4 or IPv6
3454					$ipsm = "32";
3455                	if (!filter_var($field[0], FILTER_VALIDATE_IP, FILTER_FLAG_IPV6) === false) { $ipsm = "128"; }
3456                    if ($this->Subnets->is_subnet_inside_subnet ($field[0]."/" . $ipsm, $this->transform_address($subnet->subnet, "dotted")."/".$subnet->mask)==false)    { $class = "danger"; $errors++; }
3457                }
3458            	// make sure mac does not exist
3459                if ($this->settings->enableMulticast=="1" && strlen($class)==0) {
3460                    if (strlen($field[5])>0 && $this->Subnets->is_multicast($field[0])) {
3461                        if($this->Subnets->validate_multicast_mac ($field[5], $subnet->sectionId, $subnet->vlanId, MCUNIQUE)!==true) {
3462                            $errors++; $class = "danger";
3463                        }
3464                    }
3465                }
3466
3467                // set class
3468                $field['class'] = $class;
3469
3470                // save outfile
3471                $result[] = $field;
3472            }
3473        }
3474
3475        # return
3476        return $result;
3477	}
3478
3479	/**
3480	 * Counts number of IP addresses for statistics
3481	 *
3482	 * @access public
3483	 * @param string $type (default: "IPv4")
3484	 * @return int
3485	 */
3486	public function count_subnets ($type="IPv4") {
3487		# set proper query
3488		if($type=="IPv4")		{ $query = 'select count(*) as count from `ipaddresses` where cast(`ip_addr` as UNSIGNED) <= 4294967295;'; }
3489		elseif($type=="IPv6")	{ $query = 'select count(*) as count from `ipaddresses` where cast(`ip_addr` as UNSIGNED) >  4294967295;'; }
3490
3491		try { $count = $this->Database->getObjectQuery($query); }
3492		catch (Exception $e) { $this->Result->show("danger", $e->getMessage(), true); }
3493
3494		/* return true if it exists */
3495		return $count->count;
3496	}
3497
3498	/**
3499	 * Fetches top subnets for dashboard graphs
3500	 *
3501	 * @access public
3502	 * @param mixed $type
3503	 * @param string $limit (default: "10")
3504	 * @param bool $perc (default: false)
3505	 * @return array
3506	 */
3507	public function fetch_top_subnets ($type, $limit = "10", $perc = false) {
3508		# set limit & IPv4/IPv6 selector
3509		$limit = $limit<=0 ? '' : 'LIMIT '. (int) $limit;
3510		$type_operator = ($type === 'IPv6') ? '>' : '<=';
3511		$type_max_mask = ($type === 'IPv6') ? '128' : '32';
3512		$strict_mode   = ($type === 'IPv6') ? '0' : '2';
3513
3514		if($perc) {
3515			$query = "SELECT s.sectionId,s.id,s.subnet,mask,IF(char_length(s.description)>0,s.description,'No description') AS description,
3516					COUNT(*) AS `usage`,ROUND(COUNT(*)/(POW(2,$type_max_mask-`mask`)-$strict_mode)*100,2) AS `percentage` FROM `ipaddresses` AS `i`
3517					LEFT JOIN `subnets` AS `s` ON i.subnetId = s.id
3518					WHERE s.mask < ($type_max_mask-1) AND CAST(s.subnet AS UNSIGNED) $type_operator 4294967295
3519					GROUP BY i.subnetId
3520					ORDER BY `percentage` DESC $limit;";
3521		} else {
3522			$query = "SELECT s.sectionId,s.id,s.subnet,mask,IF(char_length(s.description)>0,s.description,'No description') AS description,
3523					COUNT(*) AS `usage` FROM `ipaddresses` AS `i`
3524					LEFT JOIN `subnets` AS `s` ON i.subnetId = s.id
3525					WHERE CAST(s.subnet AS UNSIGNED) $type_operator 4294967295
3526					GROUP BY i.subnetId
3527					ORDER BY `usage` DESC $limit;";
3528		}
3529
3530		try { $stats = $this->Database->getObjectsQuery($query); }
3531		catch (Exception $e) { !$this->debugging ? : $this->Result->show("danger", $e->getMessage(), true);	return false; }
3532
3533		# return subnets array
3534		return (array) $stats;
3535	}
3536
3537	/**
3538	 * Fetches all addresses to export to hosts file
3539	 *
3540	 * @access public
3541	 * @return array
3542	 */
3543	public function fetch_addresses_for_export () {
3544		# fetch
3545	    try { $addresses = $this->Database->getObjectsQuery("select `id`,`subnetId`,`ip_addr`,`hostname` from `ipaddresses` where length(`hostname`)>1 order by `subnetId` asc;"); }
3546		catch (Exception $e) { $this->Result->show("danger", $e->getMessage(), false);	return false; }
3547		# return result
3548		return $addresses;
3549	}
3550
3551	/**
3552	 * Verify that translation exists
3553	 *
3554	 * @access public
3555	 * @param mixed $code		//lang code
3556	 * @return bool
3557	 */
3558	public function verify_translation ($code) {
3559		//verify that proper files exist
3560		return !file_exists("functions/locale/$code/LC_MESSAGES/phpipam.mo") ? false : true;
3561	}
3562
3563	/**
3564	 * Fetches translation version from code
3565	 *
3566	 * @access public
3567	 * @param mixed $code		//lang code
3568	 * @return string
3569	 */
3570	public function get_translation_version ($code) {
3571		//check for version
3572		$ver = shell_exec("grep 'Project-Id-Version:' ".dirname(__FILE__)."/../locale/$code/LC_MESSAGES/phpipam.po");
3573		//parse
3574		$ver = str_replace(array("Project-Id-Version:", " ", '"', "#",'\n', ":"), "", $ver);
3575		//return version
3576		return $ver;
3577	}
3578
3579}
3580