<%doc>
Device Inventory Report
</%doc>

<%attr>
title   => 'Device Inventory' 
section => 'Reports'
</%attr>

<%args>
$report_type => undef
</%args>

<%init>
my $DEBUG = 0;
my (@headers, @rows) = ();
</%init>


<div class="container">
  <div class="containerhead">Device Inventory</div>
    <div class="containerbody">
    <p>
      <form name="report_form" action="device_inventory.html" method="POST">
         By <select name="report_type">
         <option value="">Select Report</option>
         <option value="product" <% ($report_type eq "product")?"SELECTED":"" %>>Type/Model</option>
         <option value="product_monitored" <% ($report_type eq "product_monitored")?"SELECTED":"" %>>Type/Model (monitored)</option>
         <option value="site" <% ($report_type eq "site")?"SELECTED":"" %>>Site/Type/Model</option>
         <option value="os" <% ($report_type eq "os")?"SELECTED":"" %>>Model/OS</option>
         <option value="downtime" <% ($report_type eq "downtime")?"SELECTED":"" %>>In Scheduled Downtime</option>
         <option value="snmp_down" <% ($report_type eq "snmp_down")?"SELECTED":"" %>>SNMP Down</option>
         <option value="duplex" <% ($report_type eq "duplex")?"SELECTED":"" %>>Duplex Mismatches</option>
         <option value="vlan_mismatches" <% ($report_type eq "vlan_mismatches")?"SELECTED":"" %>>VLAN Mismatches</option>
         <option value="os_mismatches" <% ($report_type eq "os_mismatches")?"SELECTED":"" %>>OS Mismatches</option>
         </select>
         <input type="submit" name="submit" class="btn" value="Go" >
      </form>

<%perl>

#######################################################################################
if ( $report_type eq 'product' || $report_type eq 'product_monitored' ){
    my %t;
    my $grandtotal = 0;
    foreach my $pt ( sort { $a->name cmp $b->name } ProductType->retrieve_all ){
	$t{$pt->name}{total} = 0;
	$t{$pt->name}{id}    = $pt->id;
	my @prods;
	if ( $report_type eq 'product' ){
	    @prods = Product->search_by_type($pt->id);
	}elsif ( $report_type eq 'product_monitored' ){
	    @prods = Product->search_monitored_by_type($pt->id);
	}
	foreach my $o ( @prods ){
	    $t{$pt->name}{prod}{$o->name}{id}   = $o->id;
	    $t{$pt->name}{prod}{$o->name}{num}  = $o->{numdevs};
	    $t{$pt->name}{total}               += $o->{numdevs};
	}
	$grandtotal += $t{$pt->name}{total};
    }

    # Look for products with no type
    foreach my $o ( Device->search_no_type() ){
	$t{'Unset'}{prod}{$o->name}{id}  = $o->id;
	$t{'Unset'}{prod}{$o->name}{num} = $o->{numdevs};
	$t{'Unset'}{total}              += $o->{numdevs};
    }
    
    # Look for devices with no asset set
    my @noprod = Device->search(asset_id=>undef);
    
    if ( my $num = scalar @noprod ){
	$t{'Unset'}{prod}{'Unset'}{id}   = 0;
	$t{'Unset'}{prod}{'Unset'}{num}  = $num;
	$t{'Unset'}{total}               = $num;
    }
    if ( exists $t{'Unset'} ){
	$grandtotal += $t{'Unset'}{total};
    }

    @headers = ( 'Type', 'Model', 'Count', );

    my @row = ();
    push( @row, "Total Devices in Inventory:" );
    push( @row, "&nbsp;" );
    push( @row, $grandtotal );
    push( @rows, \@row );

    foreach my $pt ( sort keys %t ){
	my @row = ();
	if ( $t{$pt}{id} ){
	    push( @row, "<strong><a href=\"../generic/search_obj.html?table=Device&producttype=$t{$pt}{id}&res=1\">" . $pt . "</a></strong>" );
	}else{
	    push( @row, "<strong>$pt</strong>" );
	}
	push( @row, "" );
	push( @row, '<strong>' . $t{$pt}{total} . '</strong>' );
	push( @rows, \@row );

	foreach my $product ( sort keys %{ $t{$pt}{prod} } ){
	    my @row = ();
	    push( @row, "" );
	    if ( $t{$pt}{prod}{$product}{id} ){
		push( @row, '<a href="../generic/view.html?table=Product&id=' . $t{$pt}{prod}{$product}{id} . '">' . $product . '</a>' );
	    }elsif ( $product eq "Unset" ){
		push( @row, '<a href="../generic/search_obj.html?table=Device&asset_id=undef&res=1">' . $product . '</a>' );
	    }
	    push( @row, $t{$pt}{prod}{$product}{num}  );
	    push( @rows, \@row );
	}

    }
    $m->comp('/generic/data_table.mhtml', field_headers=>\@headers, data=>\@rows);

#######################################################################################
}elsif ( $report_type eq 'site' ){
    my $res = Netdot::Model->raw_sql("
    SELECT     device.site,
               asset.product_id,
               site.name AS site_name,
               product.name AS product_name,
               producttype.name AS product_type,
               entity.name AS manufacturer
    FROM       device
    LEFT JOIN  site ON site.id=device.site
    LEFT JOIN  (asset CROSS JOIN product CROSS JOIN entity) 
                ON (asset.id=device.asset_id 
                 AND asset.product_id=product.id
                 AND entity.id=product.manufacturer)
    LEFT JOIN  producttype ON product.type = producttype.id
    ORDER BY   site_name,product_name
    ")->{rows};

    @headers = ( 'Site', 'Type', 'Model', 'Count' );

    my %t;
    foreach my $r ( @$res ) {
        my $site        = $r->[0] || "_unset";
	my $product     = $r->[1] || "_unset";
	$t{$site}{name} = $r->[2] || "Unset"; 
	my $type        = $r->[4] || "Unset";
	$t{$site}{types}{$type}{count}++;
	$t{$site}{types}{$type}{prods}{$product}{name}  = $r->[3] || "Unset";
	$t{$site}{types}{$type}{prods}{$product}{manuf} = $r->[5] || "Unset";
	$t{$site}{types}{$type}{prods}{$product}{count}++;
	$t{$site}{total}++;
    }
    foreach my $site ( keys %t ){
	my $site_name = $t{$site}{name};
	my @hdr = ();
	push( @hdr, "<strong><a href=\"../generic/search_obj.html?".
	      "table=Device&site=$site&res=1\">$site_name</a></strong>" );
	push( @hdr, "" );
	push( @hdr, "" );
	push( @hdr, $t{$site}{total} );
	push( @rows, \@hdr );
	foreach my $type ( keys %{$t{$site}{types}} ){
	    my $tcount = $t{$site}{types}{$type}{count};
	    my @row = ();
	    push( @row, "" );
	    push( @row, $type );
	    push( @row, "" );
	    push( @row, $tcount );
	    push( @rows, \@row );
	    foreach my $product ( keys %{$t{$site}{types}{$type}{prods}} ){
		my @row = ();
		my $product_name = $t{$site}{types}{$type}{prods}{$product}{name};
		my $manuf = $t{$site}{types}{$type}{prods}{$product}{manuf};
		my $label = $manuf." ".$product_name;
		my $pcount = $t{$site}{types}{$type}{prods}{$product}{count};
		push( @row, "" );
		push( @row, "" );
		push( @row, "<strong><a href=\"../generic/search_obj.html?".
		      "table=Device&site=$site&product=$product&res=1\">$label</a></strong>" );
		push( @row, '<strong>' . $pcount . '</strong>' );
		push( @rows, \@row );
	    }
	}
    }
    $m->comp('/generic/data_table.mhtml', field_headers=>\@headers, data=>\@rows);


#######################################################################################
}elsif ( $report_type eq 'os' ){
    my %t;
    my @devs = Device->search_by_product_os();
    foreach my $dev (@devs){
	my $prod  = ($dev->asset_id && $dev->asset_id->product_id) ? 
	    $dev->asset_id->product_id->id : "Unset";
	my $manuf = ($dev->asset_id && $dev->asset_id->product_id && $dev->asset_id->product_id->manufacturer) ? 
	    $dev->asset_id->product_id->manufacturer->id : 'Unset';
	my $os    = $dev->os;
	$t{$manuf}{total}++;
	$t{$manuf}{product}{$prod}{total}++;
	$t{$manuf}{product}{$prod}{os}{$os}{total}++ if defined ($os);
    }

    @headers = ( 'Manufacturer', 'Model', 'OS', 'Count', );
    my @row = ();
    foreach my $m ( keys %t ){
	my @row = ();
	my $entity;
	if ( $m ne 'Unset' && ($entity = Entity->retrieve($m)) ){
	    push( @row, "<strong><a href=\"../generic/view.html?table=Entity&id=$m\">" . $entity->name . "</a></strong>" );
	}else{
	    push( @row, $m );
	}
	push ( @row, " ", " ", "<b>$t{$m}{total}</b>" );
	push( @rows, \@row );
	foreach my $prod ( keys %{$t{$m}{product}} ){
	    my @row = ();
	    push( @row, " " );
	    my $product;
	    my $latest_os;
	    if ( $prod ne 'Unset' && ($product = Product->retrieve($prod)) ){
		push( @row, "<strong><a href=\"../generic/view.html?table=Product&id=$prod\">" . $product->name . "</a></strong>" );
		$latest_os = "(recommended: ".$product->latest_os.")" if defined($product->latest_os);
	    }else{
		push( @row, $prod );
	    }
	    push( @row, $latest_os, $t{$m}{product}{$prod}{total} );
	    push( @rows, \@row );
	    foreach my $os ( keys %{$t{$m}{product}{$prod}{os}} ){
		my @row = ();
		push( @row, " ", " " );
		my $class = " ";
		if ( $product->latest_os && ($os ne $product->latest_os) ){
		    $class = 'warn';
		}
		push( @row, "<a class=\"$class\" href=\"../generic/search_obj.html?table=Device&product=$prod&os=$os&res=1\">" . 
		      $os . '</a>' );
		push( @row, $t{$m}{product}{$prod}{os}{$os}{total} );
		push( @rows, \@row );
	    }
	}
    }
    $m->comp('/generic/data_table.mhtml', field_headers=>\@headers, data=>\@rows);

#######################################################################################
}elsif ( $report_type eq 'downtime' ){
    $m->comp('downtime.html');

}elsif ( $report_type eq 'snmp_down' ){
    $m->comp('snmp_down.html');

#######################################################################################
}elsif ( $report_type eq 'duplex' ){
    $m->comp('duplex.html');

}elsif ( $report_type eq 'vlan_mismatches' ){
    $m->comp('vlan_mismatches.html');

#######################################################################################
}elsif ( $report_type eq 'os_mismatches' ){
    print "<strong>Devices with OS different from the recommended OS of product</strong>";
    print " (Note: devices without OS data are omitted.)";
    my %t;
    my @devices = Device->search_for_os_mismatches();
    @headers = ( 'Manufacturer', 'Model', 'Device', 'OS', );
    
    foreach my $dev (@devices) {
	my $prod  = ($dev->product) ? $dev->product->id : "Unset";
	my $p_os  = $dev->product->latest_os; 
	my $manuf = ($dev->product && $dev->product->manufacturer) ? $dev->product->manufacturer->id : "Unset";
	my $os    = $dev->os;
	my $devid = $dev->id;
	$t{$manuf}{os} = $p_os;
	$t{$manuf}{total}++;
	$t{$manuf}{product}{$prod}{total}++;
	$t{$manuf}{product}{$prod}{latest} = $dev->product->latest_os;
	$t{$manuf}{product}{$prod}{os}{$os}{dev}{$devid}{total}++;
    }

    my @row = ();
    foreach my $m ( keys %t ){
	my @row = ();
	my $entity;
	if ( $m ne 'Unset' && ($entity = Entity->retrieve($m)) ){
	    push(@row, "<strong><a href=\"../generic/view.html?table=Entity&id=$m\">" . $entity->name . "</a></strong>" );
	}else{
	    push( @row, $m );
	}
	push ( @row, " ", " ", $t{$m}{os} );
	push ( @rows, \@row );
	foreach my $prod (keys %{$t{$m}{product}} ){
	    my @row = ();
	    push( @row, " " );
	    my $product;
	    if ( $prod ne 'Unset' && ($product = Product->retrieve($prod)) ) {
		push( @row, "<strong><a href=\"../generic/view.html?table=Product&id=$prod\">" . $product->name . "</a></strong>" );
	    }else{
		push( @row, $prod );
	    }
	    push( @row, " ", $t{$m}{product}{$prod}{latest} );
	    push( @rows, \@row );
	    foreach my $os (keys %{$t{$m}{product}{$prod}{os}} ){
		foreach my $devid (keys %{$t{$m}{product}{$prod}{os}{$os}{dev}} ){
		    my @row = ();
		    my $devi;
		    $devi = Device->retrieve($devid);
		    push( @row, " ", " " );
		    push( @row, "<a href=\"../management/device.html?id=$devid\">" . $devi->fqdn . "</a>" );
		    push( @row, "<a href=\"../generic/search_obj.html?table=Device&product=$prod&os=$os&res=1\">" . $os . "</a>" );
		    push( @rows, \@row );
		}
	    }
	}
    }
    $m->comp('/generic/data_table.mhtml', field_headers=>\@headers, data=>\@rows);
}
</%perl>


    </div>
  </div>
</div>

