<commit_before>import datetime
from datetime import date
from datetime import datetime, timedelta
import time

# django settings for script
from django.conf import settings

from djzbar.utils.informix import do_sql
from djzbar.utils.informix import get_engine, get_session

# Imports for additional modules and functions written as part of this project
from djequis.adp.utilities import fn_convert_date, fn_write_log, \
    fn_write_error, fn_format_phone

DEBUG = settings.INFORMIX_DEBUG

# set up command-line options
desc = """
    Upload ADP data to CX
"""

# write out the .sql file
scr = open("apdtocx_output.sql", "a")
# set start_time in order to see how long script takes to execute
start_time = time.time()


######################################################
#  START HERE
######################################################
######################################################
# Dates are an issue to resolve
# Need to see if there is a record of the same type (PERM) and if so
# add an end date
# ID, AA and Start date should serve as a key of sorts, and if there is
# another record with the same start date, it will throw Duplicate key error.

# So in addition to seeing if the same address is in the database, also look
# for existing record with same aa type and find its start and stop dates.
# Add End Date to previous record because we would be
# creating a new alternate address

# Scenario 1 -  No aa_rec record exists:   Insert only
# Scenario 2 -  aa_rec is a close match of new data - update
# Scenario 3 -  aa_rec data exists but does not match,
#    end date old record and insert new
######################################################

def fn_archive_address(id, fullname, addr1, addr2, addr3, cty, st, zp, ctry,
            phone, EARL):
    try:
        # print(addr1, addr2, addr3)
        #################################
        #  See if there is already an Archive record
        #################################
        q_check_aa_adr = '''
          SELECT id, aa, aa_no, beg_date, line1, line2, line3, city, st, zip, phone, 
          ctry 
          FROM aa_rec 
          WHERE id = {0}
          AND aa in ('PERM','PREV','SCND')
          AND end_date is null
          '''.format(id)
        sql_id_address = do_sql(q_check_aa_adr, key=DEBUG, earl=EARL)
        addr_result = sql_id_address.fetchone()

        # print(q_check_aa_adr)
        # print("AA_rec Addr Result = " + str(addr_result))
        if addr_result is None:
            found_aa_num = 0
        else:
            found_aa_num = addr_result[2]
            fn_write_log("Existing archive address record found in aa_rec for "
                         + fullname + ", ID = " + str(id))
        # print(found_aa_num)

        #################################
        #  Find the max start date of all PREV entries with a null end date
        #################################
        q_check_aa_date = '''
          SELECT MAX(beg_date), ID, aa, line1, end_date
           AS date_end
           FROM aa_rec 
           Where id = {0}
           AND aa = 'PREV'
           AND end_date is null
           GROUP BY id, aa, end_date, line1
          '''.format(id)
        # print(q_check_aa_date)
        sql_date = do_sql(q_check_aa_date, key=DEBUG, earl=EARL)
        date_result = sql_date.fetchone()
        #print(date_result)

        #################################
        # Define date variables
        #################################
        if found_aa_num == 0 or date_result is None: #No aa rec found
            # a1 = ""
            max_date = datetime.now().strftime("%m/%d/%Y")
        # Make sure dates don't overlap
        else:
            max_date = date.strftime(date_result[0],"%m/%d/%Y")
            # a1 = date_result[3]

        # print("A1 = " + a1 )
        #print("Max date = " + str(max_date))

        # Scenario 1
        # This means that the ID_Rec address will change
        # but nothing exists in aa_rec, so we will only insert as 'PREV'
        if found_aa_num == 0: # No address in aa rec?
              # print("No existing record - Insert only")
              # print(datetime.now().strftime("%m/%d/%Y"))
              fn_insert_aa(id, fullname, 'PREV', addr1, addr2, addr3,
                           cty, st, zp, ctry,
                           datetime.now().strftime("%m/%d/%Y"),
                           fn_format_phone(phone),EARL)

        # Scenario 2
        # if record found in aa_rec, then we will need more options
        # Find out if the record is an exact match with another address
        # Question is, what is enough of a match to update rather than insert new?
        elif addr_result[4] == addr1 \
             and addr_result[9] == zp:
            # and addr_result[7] == cty \
            # and addr_result[8] == st \
            # and addr_result[10] == ctry:
            # or addr_result[5] == addr2 \
            # or addr_result[6] == addr3 \

            # print("An Address exists and matches new data - Update new")
            #################################
            # Match found then we are UPDATING only....
            #################################
            fn_update_aa(id, aa, aanum, fllname, add1, add2, add3, cty, st,
                             zp, ctry, begdate, fn_format_phone(phone), EARL)

        # to avoid overlapping dates
        # Scenario 3 - AA Rec exists but does not match new address.
        # End old, insert new
        else:
            if max_date >= str(datetime.now()):
                end_date = max_date
            else:
                end_date = datetime.now().strftime("%m/%d/%Y")

            x = datetime.strptime(end_date, "%m/%d/%Y") + timedelta(days=1)
            beg_date = x.strftime("%m/%d/%Y")

            # print("Check begin date = " + beg_date)
            # id, aa_num, fullname, enddate, aa
            fn_end_date_aa(id, found_aa_num, fullname,
                           end_date, 'PREV', EARL)
            ######################################################
            # Timing issue here, it tries the insert before the end date
            # entry is fully committed
            # Need to add something to make sure the end date is in place
            # or I get a duplicate error<commit_msg>Testing, issue with misplaced variable in one statement<commit_after>import datetime
from datetime import date
from datetime import datetime, timedelta
import time

# django settings for script
from django.conf import settings

from djzbar.utils.informix import do_sql
from djzbar.utils.informix import get_engine, get_session

# Imports for additional modules and functions written as part of this project
from djequis.adp.utilities import fn_convert_date, fn_write_log, \
    fn_write_error, fn_format_phone

DEBUG = settings.INFORMIX_DEBUG

# set up command-line options
desc = """
    Upload ADP data to CX
"""

# write out the .sql file
scr = open("apdtocx_output.sql", "a")
# set start_time in order to see how long script takes to execute
start_time = time.time()


######################################################
#  START HERE
######################################################
######################################################
# Dates are an issue to resolve
# Need to see if there is a record of the same type (PERM) and if so
# add an end date
# ID, AA and Start date should serve as a key of sorts, and if there is
# another record with the same start date, it will throw Duplicate key error.

# So in addition to seeing if the same address is in the database, also look
# for existing record with same aa type and find its start and stop dates.
# Add End Date to previous record because we would be
# creating a new alternate address

# Scenario 1 -  No aa_rec record exists:   Insert only
# Scenario 2 -  aa_rec is a close match of new data - update
# Scenario 3 -  aa_rec data exists but does not match,
#    end date old record and insert new
######################################################

def fn_archive_address(id, fullname, addr1, addr2, addr3, cty, st, zp, ctry,
            phone, EARL):
    try:
        # print(addr1, addr2, addr3)
        #################################
        #  See if there is already an Archive record
        #################################
        q_check_aa_adr = '''
          SELECT id, aa, aa_no, beg_date, line1, line2, line3, city, st, zip, phone, 
          ctry 
          FROM aa_rec 
          WHERE id = {0}
          AND aa in ('PERM','PREV','SCND')
          AND end_date is null
          '''.format(id)
        sql_id_address = do_sql(q_check_aa_adr, key=DEBUG, earl=EARL)
        addr_result = sql_id_address.fetchone()

        # print(q_check_aa_adr)
        # print("AA_rec Addr Result = " + str(addr_result))
        if addr_result is None:
            found_aa_num = 0
        else:
            found_aa_num = addr_result[2]
            fn_write_log("Existing archive address record found in aa_rec for "
                         + fullname + ", ID = " + str(id))
        # print(found_aa_num)

        #################################
        #  Find the max start date of all PREV entries with a null end date
        #################################
        q_check_aa_date = '''
          SELECT MAX(beg_date), ID, aa, line1, end_date
           AS date_end
           FROM aa_rec 
           Where id = {0}
           AND aa = 'PREV'
           AND end_date is null
           GROUP BY id, aa, end_date, line1
          '''.format(id)
        # print(q_check_aa_date)
        sql_date = do_sql(q_check_aa_date, key=DEBUG, earl=EARL)
        date_result = sql_date.fetchone()
        # print("AA Max date = " + date_result)

        #################################
        # Define date variables
        #################################
        if found_aa_num == 0 or date_result is None: #No aa rec found
            # a1 = ""
            max_date = datetime.now().strftime("%m/%d/%Y")
        # Make sure dates don't overlap
        else:
            max_date = date.strftime(date_result[0],"%m/%d/%Y")
            # a1 = date_result[3]

        # print("A1 = " + a1 )
        # print("Max date = " + str(max_date))

        # Scenario 1
        # This means that the ID_Rec address will change
        # but nothing exists in aa_rec, so we will only insert as 'PREV'
        if found_aa_num == 0: # No address in aa rec?
              # print("No existing record - Insert only")
              # print(datetime.now().strftime("%m/%d/%Y"))
              fn_insert_aa(id, fullname, 'PREV', addr1, addr2, addr3,
                           cty, st, zp, ctry,
                           datetime.now().strftime("%m/%d/%Y"),
                           fn_format_phone(phone),EARL)

        # Scenario 2
        # if record found in aa_rec, then we will need more options
        # Find out if the record is an exact match with another address
        # Question is, what is enough of a match to update rather than insert new?
        elif addr_result[4] == addr1 \
             and addr_result[9] == zp:
            # and addr_result[7] == cty \
            # and addr_result[8] == st \
            # and addr_result[10] == ctry:
            # or addr_result[5] == addr2 \
            # or addr_result[6] == addr3 \

            print("An Address exists and matches new data - Update new")
            #################################
            # Match found then we are UPDATING only....
            #################################
            fn_update_aa(id, aa, aanum, fllname, add1, add2, add3, cty, st,
                             zp, ctry, begdate, fn_format_phone(phone), EARL)

        # to avoid overlapping dates
        # Scenario 3 - AA Rec exists but does not match new address.
        # End old, insert new
        else:
            if max_date >= str(datetime.now()):
                end_date = max_date
            else:
                end_date = datetime.now().strftime("%m/%d/%Y")

            x = datetime.strptime(end_date, "%m/%d/%Y") + timedelta(days=1)
            beg_date = x.strftime("%m/%d/%Y")

            # print("Check begin date = " + beg_date)
            # id, aa_num, fullname, enddate, aa
            fn_end_date_aa(id, found_aa_num, fullname,
                           end_date, 'PREV', EARL)
            ######################################################
            # Timing issue here, it tries the insert before the end date
            # entry is fully committed
            # Need to add something to make sure the end date is in place
            # or I get a duplicate error
{'fraction_non_alphanumeric': 0.16393182718985336, 'fraction_numerical': 0.00745144669044788, 'mean_word_length': 2.222477650063857, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1161444', 'n_tokens_mistral': 3698, 'n_tokens_neox': 3485, 'n_words': 1221}