";
}
+# voip_sql has a special case: the fifth column, "Voice with Internet",
+# must test whether there are _any_ broadband packages at the same location,
+# not just whether this package is both VoIP and broadband.
+
sub voip_sql {
my $class = shift;
my %opt = @_;
my $agentnum = $opt{agentnum};
my $q = $opt{ignore_quantity} ? '1' : 'COALESCE(cust_pkg.quantity, 1)';
+ # subquery to test whether there's an is_broadband package at this location
+ my $broadband_pkg =
+ "SELECT 1 FROM cust_pkg AS broadband_pkg
+ WHERE broadband_pkg.locationnum = cust_pkg.locationnum
+ AND EXISTS(SELECT 1 FROM part_pkg_fcc_option
+ WHERE fccoptionname = 'is_broadband'
+ AND part_pkg_fcc_option.pkgpart = broadband_pkg.pkgpart
+ AND optionvalue = '1')
+ AND ". active_on( $date );
+
+ my $has_broadband = "EXISTS($broadband_pkg)";
+
my @select = (
"state.fips",
# OTT, OTT + consumer
# non-OTT: total, consumer, broadband bundle, media types
"SUM($q * (CASE WHEN (voip_lastmile = 1) THEN 1 ELSE 0 END))",
"SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_consumer = 1) THEN 1 ELSE 0 END))",
- "SUM($q * (CASE WHEN (voip_lastmile = 1 AND is_broadband = 1) THEN 1 ELSE 0 END))",
+ "SUM($q * (CASE WHEN (voip_lastmile = 1 AND $has_broadband) THEN 1 ELSE 0 END))",
"SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Copper') THEN 1 ELSE 0 END))",
- "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
"SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fiber') THEN 1 ELSE 0 END))",
+ "SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Cable Modem') THEN 1 ELSE 0 END))",
"SUM($q * (CASE WHEN (voip_lastmile = 1 AND media = 'Fixed Wireless') THEN 1 ELSE 0 END))",
"SUM($q * (CASE WHEN (voip_lastmile = 1 AND media NOT IN('Copper', 'Fiber', 'Cable Modem', 'Fixed Wireless') ) THEN 1 ELSE 0 END))",
);
JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
JOIN part_pkg USING (pkgpart) '.
join_optionnames_int(
- qw( is_voip is_broadband is_consumer voip_lastmile)
+ qw( is_voip is_consumer voip_lastmile)
).
join_optionnames('media')
;
'classnum', 'int', 'NULL', '', '', '',
'duration', 'int', 'NULL', '', 0, '',
'phonenum', 'varchar', 'NULL', 255, '', '', # has to hold a service label
- 'accountcode', 'varchar', 'NULL', 20, '', '',
+ 'accountcode', 'varchar', 'NULL', 32, '', '',
'startdate', @date_type, '', '',
'regionname', 'varchar', 'NULL', $char_d, '', '',
'detail', 'varchar', '', 255, '', '',
'classnum', 'int', 'NULL', '', '', '',
'duration', 'int', 'NULL', '', 0, '',
'phonenum', 'varchar', 'NULL', 255, '', '',
- 'accountcode', 'varchar', 'NULL', 20, '', '',
+ 'accountcode', 'varchar', 'NULL', 32, '', '',
'startdate', @date_type, '', '',
'regionname', 'varchar', 'NULL', $char_d, '', '',
'detail', 'varchar', '', 255, '', '',
'billsec', 'int', '', '', 0, '',
'disposition', 'varchar', '', 45, \"''", '',
'amaflags', 'int', '', '', 0, '',
- 'accountcode', 'varchar', '', 20, \"''", '',
+ 'accountcode', 'varchar', '', 32, \"''", '',
'uniqueid', 'varchar', '', $char_d, \"''", '',
'userfield', 'varchar', '', 512, \"''", '',
'clid', #Calling name
skip(1), #Called type
'dst', #Called number
- skip(5), #Destination customer, Destination type
+ skip(10), #Destination customer, Destination type
#Destination number
#Destination group ID, Destination group name,
\&in_calling_type, #Inbound calling type,
\&in_calling_num, #Inbound calling number,
'', #Inbound called type,
\&in_called_num, #Inbound called number,
- skip(14),
+ skip(11),
#Inbound destination type, Inbound destination number,
#Outbound calling type, Outbound calling number,
#Outbound called type, Outbound called number,
skip(1), #Ring seconds
'billsec', #Billable seconds
'upstream_price', #Cost
+ skip(1), #Cost including taxes
'accountcode', #Billing customer
skip(3), #Billing customer name, Billing type, Billing reference
],