+=item report_fixed_voice OPTIONS
+
+Returns the Fixed Voice Subscription Detail report (section 5.5). OPTIONS
+are as above. Columns are:
+
+- census tract
+- service type (0 for non-VoIP, 1 for VoIP)
+(the above columns form a key)
+- VGE lines/VoIP subscriptions in service
+- consumer grade VGE lines/VoIP subscriptions
+
+=cut
+
+sub report_fixed_voice {
+ my $class = shift;
+ my %opt = shift;
+ my $date = $opt{date} || time;
+ my $agentnum = $opt{agentnum};
+
+ my @select = (
+ 'cust_location.censustract',
+ # VoIP indicator (0 for non-VoIP, 1 for VoIP)
+ 'COALESCE(is_voip, 0)',
+ # number of lines/subscriptions
+ 'SUM(CASE WHEN is_voip = 1 THEN 1 ELSE phone_lines END)',
+ # consumer grade lines/subscriptions
+ 'SUM(CASE WHEN is_consumer = 1 THEN ( CASE WHEN is_voip = 1 THEN voip_sessions ELSE phone_lines END) ELSE 0 END)'
+ );
+
+ my $from = 'cust_pkg
+ JOIN cust_location ON (cust_pkg.locationnum = cust_location.locationnum)
+ JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
+ JOIN part_pkg USING (pkgpart) '.
+ join_optionnames_int(qw(
+ is_phone is_voip is_consumer phone_lines voip_sessions
+ ))
+ ;
+
+ my @where = (
+ active_on($date),
+ "(is_voip = 1 OR is_phone = 1)",
+ );
+ push @where, "cust_main.agentnum = $agentnum" if $agentnum;
+ my $group_by = 'cust_location.censustract, COALESCE(is_voip, 0)';
+ my $order_by = $group_by;
+
+ my $statement = "SELECT ".join(', ', @select) . "
+ FROM $from
+ WHERE ".join(' AND ', @where)."
+ GROUP BY $group_by
+ ORDER BY $order_by
+ ";
+
+ warn $statement if $DEBUG;
+ dbh->selectall_arrayref($statement);
+}
+
+=item report_local_phone OPTIONS