# Dirty conversion of salesforce addresses into 
# householded lines for mailmerge into letters.

# todo: convert commas into linebreaks on first line of address.
# todo: ensure that the "CITY" isn't duplicated. 

import csv
import re

newNames = []

def AppendName(name1,name2):
    if name1.find(" & ") == -1:
        return name1 + " & " + name2
    else:
        return name2 + ", " + name1

def buildName(newNames,search):
    searchStr = search[7]
    name = ""

    for item in newNames:
        if item[7] == searchStr:
            if name == "":
                name = item[8]
            elif name.find(" & ") == -1: 
                name = name + " & " + item[8]
            else:
                name = item[8] + ", " + name
    
    return name 


def remove_duplicates(values):
    output = []
    seen = set()

    for value in values:
        # If value has not been encountered yet,
        # ... add it to both list and set.
        if value[7] not in seen:
            output.append(value)
            seen.add(value[7])
        
    return output

with open('./members.csv') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',', quoting=csv.QUOTE_ALL)
    line_count = 0
    for row in csv_reader:
        Salutation = row[2] 
        Firstname = row[3]
        Surname = row[4]
        Primary_Street = row[12]
        Primary_City = row[13]
        Primary_Postcode = row[14]
        Primary_County = row[15]
        Primary_Country = row[16]

        search_address = Primary_Street + Primary_Postcode + Primary_City
        search_address = search_address.lower()
        search_address = search_address.strip()
        search_address = re.sub(r'\W+', '', search_address)

        member_name = Firstname + " " + Surname

        output_address = []
        output_address.extend([Salutation,Firstname,Surname])
        output_address.extend([Primary_Street,Primary_City,Primary_Postcode,Primary_County])
        output_address.append(search_address)
        output_address.append(member_name)

        newNames.append(output_address)

deduped = remove_duplicates(newNames)
for item in deduped: 
    item[8] = buildName(newNames,item)


with open('./names.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)    
    for outline in deduped:
        writer.writerow ( [outline[3],outline[4],outline[5],outline[8]] )
        print (outline[8])