new_contents: #!/usr/bin/env python3
  
  from reporter.reports import Report
  from reporter import (
      get_contact_id_search_link, RECIPIENT_SCAD_ADMIN
  )
  
  
  class DuplicateScadIdInCivi(Report):
      def __init__(self):
          super().__init__(
              introduction=("The following SCAD IDs are "
                            "duplicated in CiviCRM: "),
              recipients=[RECIPIENT_SCAD_ADMIN],
              sql='''
  
  SELECT  scad_id_58 AS scad_id
  FROM STG_CiviCRM.dbo.civicrm_value_scad_15 scad
  JOIN    STG_CiviCRM.dbo.civicrm_case cas
      ON cas.id = scad.entity_id
      AND cas.is_deleted = 0
      AND cas.status_id IN (5, 8)
      AND cas.case_type_id = 9
  JOIN    STG_CiviCRM.dbo.civicrm_case_contact cas_con
      ON cas_con.case_id = cas.id
  JOIN    STG_CiviCRM.dbo.civicrm_contact con
      ON con.id = cas_con.contact_id
      AND con.is_deleted = 0
  WHERE i2b2ClinDataIntegration.dbo.isNullOrEmpty(scad_id_58) = 0
  GROUP BY scad_id_58
  HAVING COUNT(*) > 1
  
  
                  '''
          )
  
      def get_report_line(self, row):
          return "- **{}**\r\n".format(get_contact_id_search_link(
              row['scad_id'], row['scad_id']))
  
subject: Duplicate SCAD ID in civi