find-mailing-duplicates.py

   1 #! /usr/bin/python3
   2
   3 # SPDX-FileCopyrightText: 2023 Andrew Engelbrecht
   4 #
   5 # SPDX-License-Identifier: GPL-3.0-or-later
   6
   7 import mysql.connector
   8 from datetime import date
   9 import sys
  10
  11
  12 static_group_id = "1983"
  13
  14
  15 condition_email = "e.email = x.email"
  16 condition_names = "c.first_name = x.first_name and c.last_name = x.last_name"
  17
  18 def command_help():
  19     sys.stderr.write("correct usage: find-mailing-duplicates.py [-n]")
  20
  21 if len(sys.argv) > 2:
  22     command_help
  23     exit(1)
  24 elif len(sys.argv) == 2 and sys.argv[1] == '-n':
  25     condition = condition_names
  26 elif len(sys.argv) == 2:
  27     command_help
  28     exit(1)
  29 else:
  30     condition = condition_email
  31
  32
  33 sys.stderr.write('\nstarting new run... %s\n\n' % date.today())
  34
  35 mydb = mysql.connector.connect(
  36   host="localhost",
  37   user="root",
  38   password=sys.argv[1],
  39   database="civicrm"
  40 )
  41
  42 mycursor0 = mydb.cursor(buffered=True)
  43 mycursor1 = mydb.cursor(buffered=True)
  44 mycursor2 = mydb.cursor(buffered=True)
  45
  46 "search for pairs of contacts with matching criteria (name or email), of which each has at least one address."
  47
  48 mycursor0.execute(
  49 """
  50 select c.id, x.id
  51 from civicrm_contact c
  52 inner join civicrm_email e on c.id = e.contact_id
  53 inner join civicrm_address a on c.id = a.contact_id
  54 inner join (
  55     select i.id, k.street_address, i.is_deleted, j.email, i.first_name, i.last_name
  56     from civicrm_contact i
  57     inner join civicrm_email j on i.id = j.contact_id
  58     inner join civicrm_address k on i.id = k.contact_id
  59     group by i.id
  60     having count(i.id) > 0
  61 ) x on {0}
  62 where c.id != x.id and c.id > x.id and a.street_address is not NULL and x.street_address is not NULL and c.is_deleted != 1 and x.is_deleted != 1
  63 group by c.id
  64 having count(c.id) > 0;
  65 """.format(condition)
  66 )
  67
  68 for row in mycursor0:
  69     id1 = row[0] # c.id
  70     id2 = row[1] # x.id
  71
  72     mycursor1.execute("select count(*) from civicrm_dedupe_exception where (contact_id1 = %s and contact_id2 = %s) or (contact_id1 = %s and contact_id2 = %s);", (id1, id2, id2, id1))
  73
  74     count1 = mycursor1.fetchone()[0]
  75     if count1 == 0:
  76         mycursor2.execute(f"select count(*) from civicrm_group_contact where (contact_id = {id1} or contact_id = {id2}) and group_id = {static_group_id};")
  77
  78         count2 = mycursor2.fetchone()[0]
  79         if count2 > 0:
  80             print(f'https://crm.fsf.org/civicrm/contact/merge?reset=1&cid={id1}&oid={id2}')
  81
  82 mydb.commit()
  83