##Author: Steve S. Steves

import arcpy
import os
import pyodbc
from pyodbc import Error
import time
import shutil


def compareLists(listOne, listTwo,):

    notInList = [item for item in listOne if (item not in listTwo)]
    print "Lists compared wp"
    assert notInList[0] not in listTwo
    return notInList


def createConnection(dbFile):
    print "In create_Connection function"
    for x in pyodbc.drivers():
        if x.startswith('Microsoft Access Driver'):
            driver = x
    connStr = r'DRIVER={}; DBQ={}'.format(driver,dbFile)
    conn = pyodbc.connect(connStr)
    print("Connection Created")
    return conn

def executeSql(conn, sqlStatement):
    c = conn.cursor()
    c.execute(sqlStatement)
    print "query executed"


def populateTable(conn, populateTableSql, values):
    c = conn.cursor()
    c.executemany(populateTableSql, values)


def buildingTables(dbFile, sqlStatement, populateTableSql, values):
    print "creating connection"
    conn = createConnection(dbFile)

    try:
            print "Calling functions"
            executeSql(conn, sqlStatement)
            populateTable(conn, populateTableSql, values)
            conn.commit()
            print "transaction committed"

    except Error as e:
        print(e)

    finally:
        #Checks to see if conn is a variable and then closes it if it is
        if conn:
            print "closing connection"
            conn.close()

#Making a FC consisting of only some things in other things
Directory = r""

# Calculate date, time, and FGDB name
date = time.strftime('%Y %m %d  %H %M %S')
GDB_Name = date + '_NewFootprints.gdb'

# Create a new FileGDB
arcpy.CreateFileGDB_management(Directory, GDB_Name)

# Set to path of created FileGDB
GDB = os.path.join(Directory, GDB_Name)

connection_name = ""
database_platform = ""
instance = ""
authentication = ""
username = ""
password = ""
savePW = ""
database = ""

# Look for folder to put y Connection into
if not os.path.isdir(Directory):
    os.path.makedirs(Directory)

# Look for y connection and create if absent
if not os.path.isfile(os.path.join(Directory, connection_name)):
    print ("Making connection file")
    arcpy.CreateDatabaseConnection_management(Directory,
                                          connection_name,
                                          database_platform,
                                          instance,
                                          authentication,
                                          username,
                                          password,
                                          savePW,
                                          database)

yFDS = r""
FeatureClass = r""
FullPathFC = os.path.join(Directory, connection_name, yFDS, FeatureClass)
sr = arcpy.Describe(FullPathFC).spatialReference

# Create new FDS for footprint fc
arcpy.CreateFeatureDataset_management(GDB,
                                      "",
                                      sr)

# Get file path of FDS in created FileGDB
newFDS = os.path.join(GDB, "")

print ("New File GDB and feature dataset have been created")

xFC = os.path.join(r"",r"")
ysFC = os.path.join(r"",r"")
cityLimitsFC = os.path.join(r"",r"",r"")


inFeaturesList = [xFC, cityLimitsFC]
outFeatureClass = os.path.join(newFDS,"")

arcpy.Intersect_analysis(inFeaturesList, outFeatureClass)
print "Intersect ran"

#list and dict comprehensions
ysFC = os.path.join(r"",r"")
ysFields = ["","","",""]
#Not using owner number
ysFieldsDict = ["","","", "", "", "", "", "", "", "", "", ""]
yList = [[str(row[0]),str(row[2]), str(row[3]), str(row[4]),
                   str(row[5]), str(row[6]), str(row[7]),str(row[8]),
                   str(row[9]), str(row[10]),str(row[11])]
                  for row in arcpy.da.SearchCursor(ysFC,
                                                   ysFieldsDict)]
print "Done with y Parcels List"

#make a function for making these lists out of the queries.
xFC = outFeatureClass
xFields = ["","","",""]
xFieldsDict = ["","","",""]
xList = [str(row[0]) for row in arcpy.da.SearchCursor(xFC, xFields)]
z = r""
zFields = ["", "", "", "", "", "", "", "", "", "", "", ""]
zList = [[str(row[0]), str(row[2]), str(row[11]) ,str(row[3]), str(row[4]),
              str(row[5]), str(row[6]), str(row[7]), str(row[8]),
              str(row[9]), str(row[10])] for row in
             arcpy.da.SearchCursor(z, zFields)]
print "z db loaded"


#using z db to get records for xList
moreFieldsxList = [item for item in zList if item[0] in xList]

print "moreFieldsxList made"
print len(moreFieldsxList)
#getting as many aas from the thing as we can.
#faster to load entire database and use python to manipulate it rather
#than using looping search queires.

aaFC = os.path.join(r"",r"")
#This is a for a lookup
aaFields = ["",""]

aaCodeDict = {row[1]: row[0] for row in arcpy.da.SearchCursor(
    aaFC, aaFields)}

print "aa Code dictionary built"

for item in moreFieldsxList:
    if item[2] in aaCodeDict:
        item[2] = aaCodeDict[item[2]]

print "Done with adding information to moreFieldsxList"

listOne = moreFieldsxList
listTwo = yList
listOne = tuple(listOne)
listTwo = tuple(listTwo)
print "Running compare functions"

queryListOne = compareLists(listOne, listTwo)
queryListTwo = compareLists(listTwo, listOne)
print "List comparisons successful"

#Writing to access database. There are in fact plans to make the
#comparison functions two left joins including only results that
#don't match like it should have been in the first place.
dbFile = "U:FileMcFileFace.mdb"
sqlStatement1 = """ CREATE TABLE In_y_Not_x(
                                    ONE TEXT,
                                    TWO TEXT,
                                    THREE TEXT,
                                    FOUR TEXT,
                                    FIVE TEXT,
                                    SIX TEXT,
                                    SEVEN TEXT,
                                    EIGHT TEXT,
                                    NINE TEXT,
                                    TEN TEXT,
                                    TENTY-ONE TEXT
                                    )
                                    """
sqlStatement2 = """ CREATE TABLE In_x_Not_y(
                                    ONE TEXT,
                                    TWO TEXT,
                                    THREE TEXT,
                                    FOUR TEXT,
                                    FIVE TEXT,
                                    SIX TEXT,
                                    SEVEN TEXT,
                                    EIGHT TEXT,
                                    NINE TEXT,
                                    TEN TEXT,
                                    ELEVEN TEXT
                                    )
                                    """
populateTableSql1 = """ INSERT INTO In_y_Not_x VALUES(
                                        ?,?,?,?,?,?,?,?,?,?,?)"""
populateTableSql2 = """ INSERT INTO In_x_Not_y VALUES(
                                        ?,?,?,?,?,?,?,?,?,?,?)"""
values1 = queryListOne
values2 = queryListTwo

buildingTables(dbFile, sqlStatement1, populateTableSql1, values1)
buildingTables(dbFile, sqlStatement2, populateTableSql2, values2)


shutil.rmtree(GDB)

print "Finished, and without any deaths too!"