Merge branch 'master' of git.freeside.biz:/home/git/freeside

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
   ],