Merge lp:~simonsp/ocsinventory-ocsreports/fix_ipdiscover_stale_noninventoried_missing into lp:ocsinventory-ocsreports

[Paul W. Simons]

[Paul W. Simons (simonsp)]

This fixes a problem where an update network map would not be used over a stale inventory. If a pc was inventoried on subnet A, and then moved to subnet B, when displaying the results of devices on subnet B, it would not show up. I have modified sql statements to force the stale inventory to show up as a non-inventoried machine, since the inventory needs to be updated.

[Gonéri Le Bouder (goneri)]

Thank you! :)

=== modified file 'plugins/main_sections/ms_ipdiscover/ms_custom_info.php'
--- plugins/main_sections/ms_ipdiscover/ms_custom_info.php	2009-11-13 10:57:00 +0000
+++ plugins/main_sections/ms_ipdiscover/ms_custom_info.php	2010-01-26 15:35:21 +0000
@@ -125,8 +125,11 @@
 		//$netid=mysql_escape_string($protectedGet['value']);
 		if ($protectedGet['prov'] == "no_inv"){
 			$title=$l->g(947);
-			$sql="SELECT ip, mac, mask, date, name FROM netmap WHERE netid='".$protectedGet['value']."' AND mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 
-			AND mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices)";
+			$sql="SELECT ip, mac, mask, date, name FROM netmap n 
+				LEFT JOIN networks ns ON ns.macaddr=n.mac
+				WHERE n.netid='".$protectedGet['value']."' 
+				AND (ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid) 
+				AND mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices)";
 			$list_fields= array($l->g(34) => 'ip','MAC'=>'mac',
 								$l->g(208)=>'mask',
 								$l->g(232)=>'date',

=== modified file 'plugins/main_sections/ms_ipdiscover/ms_ipdiscover.php'
--- plugins/main_sections/ms_ipdiscover/ms_ipdiscover.php	2009-11-16 17:51:40 +0000
+++ plugins/main_sections/ms_ipdiscover/ms_ipdiscover.php	2010-01-26 15:35:21 +0000
@@ -63,11 +63,12 @@
 					GROUP BY netid) 
 				ident on ipdiscover.RSX=ident.RSX left join
 					(SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX
-					FROM netmap 
-					WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 
-						and mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 
-						and netid in  ('".$list_rsx."')
-						GROUP BY netid) 
+					FROM netmap n
+					LEFT JOIN networks ns ON ns.macaddr=n.mac
+					WHERE n.mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 
+						and (ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid) 
+						and n.netid in  ('".$list_rsx."')
+						GROUP BY n.netid) 
 				non_ident on non_ident.RSX=ipdiscover.RSX where non_ident.c is not null and ident.c is not null
 					union
 				select inv.RSX,
@@ -93,11 +94,12 @@
 					GROUP BY netid) 
 				ident on ipdiscover.RSX=ident.RSX left join
 					(SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX
-					FROM netmap 
-					WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 
-						and mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 
-						and netid in  ('".$list_rsx."')
-						GROUP BY netid) 
+					FROM netmap n
+					LEFT JOIN networks ns ON ns.macaddr=n.mac
+					WHERE n.mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 
+						and (ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid) 
+						and n.netid in  ('".$list_rsx."')
+						GROUP BY n.netid) 
 				non_ident on non_ident.RSX=ipdiscover.RSX where non_ident.c is null and ident.c is not null
 				union
 				select inv.RSX,
@@ -123,11 +125,12 @@
 					GROUP BY netid) 
 				ident on ipdiscover.RSX=ident.RSX left join
 					(SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX
-					FROM netmap 
-					WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 
-						and mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 
-						and netid in  ('".$list_rsx."')
-						GROUP BY netid) 
+					FROM netmap n
+					LEFT JOIN networks ns ON ns.macaddr=n.mac
+					WHERE n.mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 
+						and (ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid) 
+						and n.netid in  ('".$list_rsx."')
+						GROUP BY n.netid) 
 				non_ident on non_ident.RSX=ipdiscover.RSX where ident.c is null and non_ident.c is not null
 				union
 				select inv.RSX,
@@ -153,11 +156,12 @@
 					GROUP BY netid) 
 				ident on ipdiscover.RSX=ident.RSX left join
 					(SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX
-					FROM netmap 
-					WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 
-						and mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 
-						and netid in  ('".$list_rsx."')
-						GROUP BY netid) 
+					FROM netmap n
+					LEFT JOIN networks ns ON ns.macaddr=n.mac
+					WHERE n.mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 
+						and (ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid) 
+						and n.netid in  ('".$list_rsx."')
+						GROUP BY n.netid) 
 				non_ident on non_ident.RSX=ipdiscover.RSX where ident.c is null and non_ident.c is null
 				) toto";
 
@@ -199,10 +203,12 @@
 	$tab_options['NO_TRI']['LBL_RSX']='LBL_RSX';
 	
 	$sql_count="SELECT COUNT(DISTINCT mac) as total
-					FROM netmap 
+					FROM netmap n 
+					LEFT OUTER JOIN networks ns ON ns.macaddr = mac 
 					WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 
-						and mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 
+						and ( ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid)
 						and netid in  ('".$list_rsx."')";
+						
 	$res_count = mysql_query($sql_count, $_SESSION['OCS']["readServer"] );
 	$val_count = mysql_fetch_array( $res_count );
 	$strEnTete = $_SESSION['OCS']["ipdiscover_id"]." ".$dpt[$protectedPost['DPT_CHOISE']]." <br>";