join_date is closer to actual start of membership

[civicrm-scripts.git] / dedupe-mark.py

#! /usr/bin/python3

# SPDX-FileCopyrightText: 2023 Andrew Engelbrecht
#
# SPDX-License-Identifier: GPL-3.0-or-later

import sys
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password=sys.argv[1],
  database="civicrm"
)

mycursor0 = mydb.cursor(buffered=True)
mycursor1 = mydb.cursor(buffered=True)
mycursor2 = mydb.cursor(buffered=True)

mycursor0.execute(
"""
select c.id, c.external_identifier, x.* 
from civicrm_contact c inner join civicrm_email e on c.id = e.contact_id
inner join (
    select i.id, i.external_identifier, j.email, count(*) as qty
    from civicrm_contact i inner join civicrm_email j on i.id = j.contact_id
    group by email
    having count(*) > 1
) x on e.email = x.email and c.external_identifier is not NULL and x.external_identifier is not NULL and c.external_identifier != x.external_identifier;
"""
)

for row in mycursor0:
    id1 = row[0] # c.id
    id2 = row[2] # x.id

    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))

    count = mycursor1.fetchone()[0]
    if count == 0:
        print('inserting:', id1, id2)
        mycursor2.execute("INSERT INTO civicrm_dedupe_exception (contact_id1, contact_id2) VALUES (%s, %s)", (id1, id2))

        #print("only do one for now, for debugging purposes")
        #break

mydb.commit()