#!%TCLSH%

#
# Display per organization statistics
#
# Called by: admin
#
# Parameters (form or url): none
#
# History
#   2002/07/25 : pda      : design
#   2003/05/13 : pda/jean : use auth base
#   2004/01/14 : pda/jean : add IPv6
#   2010/12/13 : pda      : i18n
#   2010/12/26 : pda      : use cgi-dispatch
#

#
# Template pages used by this script
#

set conf(page)		statorg.html

#
# Tabular specification
# Columns
#	organization
#	number of declared RR in this organization networks
#	percentage of declared RR vs total
#	number of allocated address in this organization networks
#	percentage of allocated address vs total
#

set conf(tabhostorg) {
    global {
	chars {10 normal}
	columns {40 15 15 15 15}
	botbar {yes}
	align {right}
    }
    pattern Title {
	align {center}
	title {yes}
	topbar {yes}
	chars {bold}
	vbar {yes}
	column { }
	vbar {yes}
	column { }
	vbar {yes}
	column { }
	vbar {yes}
	column { }
	vbar {yes}
	column { }
	vbar {yes}
    }
    pattern Normal {
	title {yes}
	vbar {yes}
	column {
	    align {left}
	}
	vbar {yes}
	column { }
	vbar {yes}
	column { }
	vbar {yes}
	column { }
	vbar {yes}
	column { }
	vbar {yes}
    }
    pattern Total {
	title {yes}
	chars {bold}
	vbar {yes}
	column {
	    align {left}
	}
	vbar {yes}
	column { }
	vbar {yes}
	column { }
	vbar {yes}
	column { }
	vbar {yes}
	column { }
	vbar {yes}
    }
}

#
# Netmagis general library
#

source %LIBNETMAGIS%

# ::webapp::cgidebug ; exit

##############################################################################
# Display statistics by organization
##############################################################################

d cgi-register {} {} {
    global conf

    #
    # Address count (v4 or v6) declared by organization
    #

    set sql "SELECT o.name,
		    COUNT (rr_ip.addr) AS declared
		FROM dns.organization o, dns.rr_ip
		WHERE rr_ip.addr << ANY
		    (SELECT addr4 FROM dns.network WHERE network.idorg = o.idorg
			UNION
		     SELECT addr6 FROM dns.network WHERE network.idorg = o.idorg)
		GROUP BY o.name
		"
    set totaldecl 0
    pg_select $dbfd $sql tab {
	set o $tab(name)
	set n $tab(declared)
	set org($o) [list $n 0]
	incr totaldecl $n
    }

    #
    # Allocated IPv4 addresses count
    #

    set sql "SELECT o.name,
		    SUM (2^(32 - MASKLEN (n.addr4))) AS allocated
		FROM dns.organization o, dns.network n
		WHERE o.idorg = n.idorg
		GROUP BY o.name
		"
    set totalalloc 0
    pg_select $dbfd $sql tab {
	set o $tab(name)
	set n $tab(allocated)
	if {[info exists org($o)]} then {
	    set org($o) [list [lindex $org($o) 0] $n]
	} else {
	    set org($o) [list 0 $n]
	}
	incr totalalloc $n
    }

    #
    # Display in tabular format
    #

    set lines {}
    lappend lines [list "Title" \
			[mc "Organization"] \
			[mc "Declared hosts"] \
			[mc "Percentage"] \
			[mc "Allocated addresses"] \
			[mc "Percentage"] \
		    ]
    set ptotaldecl 0.0
    set ptotalalloc 0.0
    foreach o [lsort [array names org]] {
	lassign $org($o) ndecl nalloc
	set pcentdecl  [expr "$ndecl*100.0/$totaldecl"]
	set pcentalloc [expr "$nalloc*100.0/$totalalloc"]
	lappend lines [list "Normal" $o \
				$ndecl  [format "%5.2f" $pcentdecl] \
				$nalloc [format "%5.2f" $pcentalloc] \
			    ]
	set ptotaldecl [expr $ptotaldecl + $pcentdecl]
	set ptotalalloc [expr $ptotalalloc + $pcentalloc]
    }

    lappend lines [list "Total" [mc "Total"] \
				$totaldecl  [format "%5.2f" $ptotaldecl] \
				$totalalloc [format "%5.2f" $ptotalalloc] \
			    ]

    set nbhostorg [::arrgen::output "html" $conf(tabhostorg) $lines]

    #
    # End of script: output page and close database
    #

    d result $conf(page) [list \
				[list %NUMHOSTORG% $nbhostorg] \
			    ]
}

##############################################################################
# Main procedure
##############################################################################

d cgi-dispatch "admin" "admin"