From 8e1e9bfc08df3ffe96515019d26ced7f89f34ff1 Mon Sep 17 00:00:00 2001
From: Ivan Kohler <ivan@freeside.biz>
Date: Wed, 30 Mar 2022 20:54:05 -0700
Subject: [PATCH] taqua vs broadsoft duplicate skipping, RT#86028

---
 FS/FS/Conf.pm               |  8 ++++++++
 FS/FS/Schema.pm             |  5 ++++-
 FS/FS/cdr.pm                |  2 ++
 FS/FS/cdr/broadsoft22.pm    | 10 +++++++++-
 FS/FS/cdr/taqua62.pm        |  2 +-
 FS/bin/freeside-cdrrewrited | 25 ++++++++++++++++++++++++-
 6 files changed, 48 insertions(+), 4 deletions(-)

diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm
index ee6ee3b7d..383fb0e88 100644
--- a/FS/FS/Conf.pm
+++ b/FS/FS/Conf.pm
@@ -5207,6 +5207,14 @@ and customer address. Include units.',
   },
 
   {
+    'key'         => 'cdr-skip_duplicate_rewrite-sipcallid',
+    'section'     => 'telephony',
+    'description' => 'Use the freeside-cdrrewrited daemon to prevent billing CDRs with a sipcallid identical to an existing CDR',
+    'type'        => 'checkbox',
+  },
+
+
+  {
     'key'         => 'cdr-charged_party_rewrite',
     'section'     => 'telephony',
     'description' => 'Do charged party rewriting in the freeside-cdrrewrited daemon; useful if CDRs are being dropped off directly in the database and require special charged_party processing such as cdr-charged_party-accountcode or cdr-charged_party-truncate*.',
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 4ebda4e11..d88403644 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -5736,6 +5736,9 @@ sub tables_hashref {
         # FK to cust_bill_pkg_detail; having a value here absolutely means
         # that the CDR appears on an invoice
         'detailnum',     'bigint',    'NULL',      '', '', '',
+
+        #for mediation/deduplication
+        'sipcallid',    'varchar',    'NULL',     255, '', '',
       ],
       'primary_key' => 'acctid',
       'unique' => [],
@@ -5749,7 +5752,7 @@ sub tables_hashref {
                    [ 'freesidestatus' ], [ 'freesiderewritestatus' ],
                    [ 'cdrbatchnum' ],
                    [ 'src_ip_addr' ], [ 'dst_ip_addr' ], [ 'dst_term' ],
-                   [ 'detailnum' ],
+                   [ 'detailnum' ], [ 'sipcallid' ],
                  ],
       #no FKs on cdr table... choosing not to throw errors no matter what's
       # thrown in here.  better to have the data.
diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm
index 85fccac69..97d444076 100644
--- a/FS/FS/cdr.pm
+++ b/FS/FS/cdr.pm
@@ -185,6 +185,8 @@ following fields are currently supported:
 
 =item detailnum - Link to invoice detail (L<FS::cust_bill_pkg_detail>)
 
+=item sipcallid - SIP Call-ID
+
 =back
 
 =head1 METHODS
diff --git a/FS/FS/cdr/broadsoft22.pm b/FS/FS/cdr/broadsoft22.pm
index 437d31e91..3334a9e76 100644
--- a/FS/FS/cdr/broadsoft22.pm
+++ b/FS/FS/cdr/broadsoft22.pm
@@ -81,7 +81,11 @@ use FS::cdr qw( _cdr_date_parser_maker _cdr_min_parser_maker );
     # 22: networkTranslatedGroup
     # 23: releasingParty
     # 24: route
+    skip(10),
+
     # 25: networkCallID
+    'sipcallid',
+
     # 26: codedc
     # 27: accessDeviceAddress
     # 28: accessCallID
@@ -90,7 +94,7 @@ use FS::cdr qw( _cdr_date_parser_maker _cdr_min_parser_maker );
     # 31: spare
     # 32: group
     # 33: department
-    skip(19),
+    skip(8),
 
     # 34: accountCode
     sub {
@@ -116,6 +120,10 @@ use FS::cdr qw( _cdr_date_parser_maker _cdr_min_parser_maker );
     # 47: callingPartyCategory
     #
     # Also... cols 48 - 448 see Broadsoft documentation
+    skip(111), #35-145 inclusive
+
+    # 146: chargedNumber
+    'charged_party',
 
   ],
 
diff --git a/FS/FS/cdr/taqua62.pm b/FS/FS/cdr/taqua62.pm
index aa9463008..c6a40eddc 100644
--- a/FS/FS/cdr/taqua62.pm
+++ b/FS/FS/cdr/taqua62.pm
@@ -159,7 +159,7 @@ use FS::cdr qw(_cdr_date_parser_maker);
 
     #60
     
-    '',       #OrigIPCallID
+    'sipcallid',       #OrigIPCallID
     '',       #ESAIPTrunkGroup
     '',       #ESAReason
     '',       #BearerlessCall
diff --git a/FS/bin/freeside-cdrrewrited b/FS/bin/freeside-cdrrewrited
index d117f569e..bcc76153b 100755
--- a/FS/bin/freeside-cdrrewrited
+++ b/FS/bin/freeside-cdrrewrited
@@ -54,7 +54,8 @@ while (1) {
 
   #order matters for removing dupes--only the first is preserved
   $extra_sql .= ' ORDER BY acctid '
-    if $conf->exists('cdr-skip_duplicate_rewrite');
+    if $conf->exists('cdr-skip_duplicate_rewrite')
+    || $conf->exists('cdr-skip_duplicate_rewrite-sipcallid');
 
   my $found = 0;
   my %skip = (); #used only by taqua
@@ -92,6 +93,22 @@ while (1) {
       }
     }
 
+    if ($conf->exists('cdr-skip_duplicate_rewrite-sipcallid')) {
+      my $sth = dbh->prepare(
+        'SELECT 1 FROM cdr WHERE sipcallid=? AND acctid < ? LIMIT 1'
+      ) or die dbh->errstr;
+      $sth->execute($cdr->sipcallid, $cdr->acctid) or die $sth->errstr;
+      my $isdup = $sth->fetchrow_hashref;
+      $sth->finish;
+      if ($isdup) {
+        #we only act on this cdr, not touching previous dupes
+        #if a dupe somehow creeped in previously, too late to fix it
+        $cdr->freesidestatus('skipped'); #prevent it from being billed
+        push(@status,'duplicate');
+      }
+    }
+
+
     if ( $conf->exists('cdr-asterisk_forward_rewrite')
          && $cdr->dstchannel =~ /^Local\/(\d+)/i && $1 ne $cdr->dst
        )
@@ -268,6 +285,7 @@ sub _shouldrun {
   || $conf->exists('cdr-intl_to_domestic_rewrite')
   || $conf->exists('cdr-userfield_dnis_rewrite')
   || $conf->exists('cdr-skip_duplicate_rewrite')
+  || $conf->exists('cdr-skip_duplicate_rewrite-sipcallid')
   || 0
   ;
 }
@@ -296,6 +314,11 @@ of the following config options are enabled:
 Marks as 'skipped' (prevents billing for) any CDRs with 
 a src, dst and calldate identical to an existing CDR
 
+=item cdr-skip_duplicate_rewrite-sipcallid
+
+Marks as 'skipped' (prevents billing for) any CDRs with 
+a sipcallid identical to an existing CDR
+
 =item cdr-asterisk_australia_rewrite
 
 Classifies Australian numbers as domestic, mobile, tollfree, international, or
-- 
2.11.0