diff options
| -rw-r--r-- | FS/FS/Report/FCC_477.pm | 22 | ||||
| -rw-r--r-- | FS/FS/Schema.pm | 6 | ||||
| -rw-r--r-- | FS/FS/cdr/cx3.pm | 5 | ||||
| -rw-r--r-- | FS/FS/cdr/enswitch.pm | 5 |
4 files changed, 29 insertions, 9 deletions
diff --git a/FS/FS/Report/FCC_477.pm b/FS/FS/Report/FCC_477.pm index 93c8dd38e..c93c919ea 100644 --- a/FS/FS/Report/FCC_477.pm +++ b/FS/FS/Report/FCC_477.pm @@ -577,6 +577,10 @@ sub lts_sql { "; } +# 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 = @_; @@ -584,6 +588,18 @@ sub voip_sql { 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 @@ -592,10 +608,10 @@ sub voip_sql { # 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))", ); @@ -608,7 +624,7 @@ sub voip_sql { 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') ; diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index b6358b9bb..ca6d169e5 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -1039,7 +1039,7 @@ sub tables_hashref { '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, '', '', @@ -1243,7 +1243,7 @@ sub tables_hashref { '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, '', '', @@ -5256,7 +5256,7 @@ sub tables_hashref { 'billsec', 'int', '', '', 0, '', 'disposition', 'varchar', '', 45, \"''", '', 'amaflags', 'int', '', '', 0, '', - 'accountcode', 'varchar', '', 20, \"''", '', + 'accountcode', 'varchar', '', 32, \"''", '', 'uniqueid', 'varchar', '', $char_d, \"''", '', 'userfield', 'varchar', '', 512, \"''", '', diff --git a/FS/FS/cdr/cx3.pm b/FS/FS/cdr/cx3.pm index 7f1222e9a..07d8967c3 100644 --- a/FS/FS/cdr/cx3.pm +++ b/FS/FS/cdr/cx3.pm @@ -38,7 +38,10 @@ use Date::Parse; 'accountcode', # AccountCode skip(6), 'src', # source - 'dst', # destination + sub { my ($cdr, $dst, $param) = @_; + $dst =~ s/#//; + $cdr->set('dst', $dst); + }, # destination ], ); diff --git a/FS/FS/cdr/enswitch.pm b/FS/FS/cdr/enswitch.pm index a19b16641..70542b20c 100644 --- a/FS/FS/cdr/enswitch.pm +++ b/FS/FS/cdr/enswitch.pm @@ -22,14 +22,14 @@ use FS::cdr_type; '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, @@ -41,6 +41,7 @@ use FS::cdr_type; 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 ], |