# import arcpy module
import arcpy

# Ask user to select the Geodatabase workspace to use for data output
userWorkspace = arcpy.GetParameterAsText(0)

# Set workspace environment based upon user's choice
arcpy.env.workspace = userWorkspace

# Ask user to select an Oracle dB Connection
oracleDB = arcpy.GetParameterAsText(1)

# Ask user to name the Query Layer
queryLayer = arcpy.GetParameterAsText(2)

# Ask user if they want to overwrite previously named Query Layer
overwriteQ = arcpy.GetParameterAsText(3)

# Ask user for an SQL Query Expression to be run against the selected Oracle dB
sqlQuery = arcpy.GetParameterAsText(4)

# Ask user to name the output of excel file (this is optional, for those who want to do excel analysis)
excelName = arcpy.GetParameterAsText(5)

# Ask user if they want to overwrite previously named Excel File
overwriteE = arcpy.GetParameterAsText(6)

# Ask user to select the folder output of the excel file (optional as well, depending on if user wants an excel file)
excelFolder = arcpy.GetParameterAsText(7)

# Create spatial reference variable to assign to queryLayer
spatialRef = arcpy.SpatialReference("W:Coordinate SystemsLRS Lambert.prj")

# Process: 'Make Query Layer' - Creates a Query Layer using the user's Oracle dB and SQL query expression
arcpy.MakeQueryLayer_management(oracleDB, "Temp_Layer", sqlQuery, "UNIQUE_ID", "POINT", "1050010", spatialRef)

# Set overwrite output for Query Layer to user's choice
arcpy.env.overwriteOutput = overwriteQ

# Process: 'Copy Features' - Copies the temporary Query Layer and stores it as a permanent feature class
arcpy.CopyFeatures_management("Temp_Layer", queryLayer)

# Process: 'Delete Features' - Deletes the temporary file Temp_Layer
# This allows for multiple executions of this tool within the same ArcMap session without error
arcpy.Delete_management("Temp_Layer")

# Process: 'Define Projection' - Defines the projection of queryLayer feature class output
arcpy.DefineProjection_management(queryLayer, spatialRef)

# Process: 'Add Field' - Adds new column fields to queryLayer
arcpy.AddField_management(queryLayer, "First_Time", "DATE") # The first LOGDT ping
arcpy.AddField_management(queryLayer, "Last_Time", "DATE") # The last LOGDT ping
arcpy.AddField_management(queryLayer, "Total_Time", "STRING") # Summation of the first to last ping in time
arcpy.AddField_management(queryLayer, "Total_Pings", "INTEGER") # Total number of pings (rows)
arcpy.AddField_management(queryLayer, "Possible_Pings", "INTEGER") # Total number of pings possible in given timeframe
arcpy.AddField_management(queryLayer, "Time_to_Process", "DATE") # How long it took for each ping to process

# Calculates the total number of rows (pings) for the Total_Pings field
numRows = int(arcpy.GetCount_management(queryLayer).getOutput(0))

# UpdateCursor that will write the value of numRows to the Total_Pings field
cursor = arcpy.da.UpdateCursor(queryLayer, "Total_Pings")
for row in cursor:
    row[0] = numRows
    cursor.updateRow(row)

# SearchCursor that will read the values of LOGDT and return the first value (earliest)
try: # try to read in values based on the user's SQL query
    firstLogdt = [row[0] for row in arcpy.da.SearchCursor(queryLayer, "LOGDT")][0]
except: # exception error if the user's SQL query produces no return
    err = "No Results Found Via SQL QuerynSCRIPT EXITED" # Error message variable
    arcpy.AddError(err) # Print error message
    sys.exit() # Exits the script, does not attempt to run any further

# UpdateCursor that will write the first (earliest) LOGDT value to the First_Time field
cursor = arcpy.da.UpdateCursor(queryLayer, "First_Time")
for row in cursor:
    row[0] = firstLogdt
    cursor.updateRow(row)

# SearchCursor that will read the values of LOGDT and return the last value (latest)
lastLogdt = [row[0] for row in arcpy.da.SearchCursor(queryLayer, "LOGDT")][-1]

# UpdateCursor that will write the last (latest) LOGDT value to the Last_Time field
cursor = arcpy.da.UpdateCursor(queryLayer, "Last_Time")
for row in cursor:
    row[0] = lastLogdt
    cursor.updateRow(row)

# Calculates the difference between firstLogdt and lastLogdt
timeDiff = lastLogdt - firstLogdt # Produces a timedelta object, not datetime

# Calculates the total number of seconds from timeDiff
timeSecs = timeDiff.total_seconds()

# Creates a function that will convert timeSecs to a readable format (yy:dd:hh:mm:ss)
def readTime(seconds):
    minutes, seconds = divmod(seconds, 60)
    hours, minutes = divmod(minutes, 60)
    days, hours = divmod(hours, 24)
    years, days = divmod(days, 365)
    return '%02d:%02d:%02d:%02d:%02d' % (years, days, hours, minutes, seconds)

# UpdateCursor that will write the time difference calculation to the Total_Time field
cursor = arcpy.da.UpdateCursor(queryLayer, "Total_Time")
for row in cursor:
    row[0] = readTime(timeSecs)
    cursor.updateRow(row)

# Calculates the total number of pings that would occur with optimal reception
possiblePings = timeSecs / 5 # 1 ping every 5 seconds

# UpdateCursor that will write the total number of pings possible to the Possible_Pings field
cursor = arcpy.da.UpdateCursor(queryLayer, "Possible_Pings")
for row in cursor:
    row[0] = possiblePings
    cursor.updateRow(row)

# Using ArcPy Mapping Module to add queryLayer to current .mxd map display and zoom to it
mxd = arcpy.mapping.MapDocument("CURRENT") # Set variable to currently active .mxd
dataFrame = arcpy.mapping.ListDataFrames(mxd)[0] # Set variable equal to the first data frame within mxd
addLayer = arcpy.mapping.Layer(queryLayer) # Set variable for queryLayer
arcpy.mapping.AddLayer(dataFrame, addLayer) # Adds queryLayer to the map
dataFrame.zoomToSelectedFeatures() # Zooms to queryLayer
arcpy.RefreshActiveView() # Refreshes the active data frame's view

# Set overwrite output for Excel File to user's choice
arcpy.env.overwriteOutput = overwriteE

# Change workspace environment to where the user wants the Excel file to be saved
try: # Tries to change the environment workspace to the user's optional excel output folder
    arcpy.env.workspace = excelFolder
except:
    pass # If user did not specify excel folder input, then let pass and continue on

# Process: 'Table To Excel' - Converts the final queried data to an excel spreadsheet file (optional)
try: # Tries to run the tool (will only work if user specified optional excel inputs)
    arcpy.TableToExcel_conversion(queryLayer, excelName+'.xls')
except:
    pass # If user did not specify optional excel inputs, then let pass and continue on

# delete cursor, row variables
del cursor, row