CreateGDBFromXLSForm

#Author: ichivite@esri.com
# creates a geodatabase to hold data from an XLSForm
# XLSForm sample: https://formhub.org/formhub_u/forms/household_survey
# Notes:
# Nested repeats not supported
# No geo types supported. Assumed every survey has a single location

import os, csv, arcpy, sys, time
import xml.dom.minidom as DOM


scratchWorkspace = r"D:\Ismael\Surveys"
surveyName = "Soy"
gdb = os.path.join(scratchWorkspace,surveyName + "FGDB.gdb")
featureclass = os.path.join (gdb,"survey")
mxdTemplate = os.path.join(scratchWorkspace,"MapTemplate.mxd")
mxd = os.path.join(scratchWorkspace,surveyName + ".mxd")
msd = os.path.join(scratchWorkspace,surveyName + ".msd")
tables = []

ArcGISUser = "..."
ArcGISPassword = "..."

codedValueDomains = []

def main():

    arcpy.env.overwriteOutput = True
    print ""

    # Unzip input file

    # Create fileGDB
    arcpy.CreateFileGDB_management(scratchWorkspace, out_name=surveyName + "FGDB", out_version="CURRENT")
    
    # Create featureclass schema
    createSchemaFromSurvey ()

    # Create map document
    createMapDocumentAndPublish()

    # Create Service Definition and Publish

    print "Done"

def addCapabilities(SDdraft):
    
    try:
        
        print "   Adding Capabilities" + SDdraft
        doc = DOM.parse(SDdraft)
        # Turn off caching
        configProps = doc.getElementsByTagName('ConfigurationProperties')[0]
        propArray = configProps.firstChild
        propSets = propArray.childNodes
        for propSet in propSets:
            keyValues = propSet.childNodes
            for keyValue in keyValues:
                if keyValue.tagName == 'Key':
                    if keyValue.firstChild.data == "isCached":
                        keyValue.nextSibling.firstChild.data = "false"

        # Turn on feature access capabilities
        configProps = doc.getElementsByTagName('Info')[0]
        propArray = configProps.firstChild
        propSets = propArray.childNodes
        for propSet in propSets:
            keyValues = propSet.childNodes
            for keyValue in keyValues:
                if keyValue.tagName == 'Key':
                    if keyValue.firstChild.data == "WebCapabilities":
                        keyValue.nextSibling.firstChild.data = "Query,Create,Update,Delete,Uploads,Editing"
        # Write the new draft to disk
        f = open(SDdraft, 'w')
        doc.writexml( f )
        f.close()

        
    except Exception as e:
       print "   Error (Adding Capabilities): " + e.message
       arcpy.AddError(e.message)
   

def createMapDocumentAndPublish():
    
    try:
        print "Creating Map Document: " + mxd
        mapDoc = arcpy.mapping.MapDocument(mxdTemplate)
        mapDoc.summary = surveyName
        mapDoc.tags = surveyName + ", XLSForm, FormHub.arcgis.com"
        df = arcpy.mapping.ListDataFrames(mapDoc, "Layers")[0]
        newlayer = arcpy.mapping.Layer(featureclass)
        arcpy.mapping.AddLayer(df, newlayer, "AUTO_ARRANGE")

        for table in tables:
            print "   Adding Table to Map: " + table            
            tableView = arcpy.mapping.TableView(table)
            arcpy.mapping.AddTableView(df, tableView)
        mapDoc.saveACopy(mxd)

        sdDraft = os.path.join(scratchWorkspace,surveyName + ".sddraft")
        sd = os.path.join(scratchWorkspace,surveyName + ".sd")

        print "Creating Service Defintion"
        analysis = arcpy.mapping.CreateMapSDDraft(mxd, sdDraft, surveyName, 'MY_HOSTED_SERVICES')
        if analysis['errors'] != {}:
            print analysis['errors']
        else:
            addCapabilities (sdDraft)
            print "Publishing Map Document. (This may take half a minute or so...)"
            arcpy.SignInToPortal_server(ArcGISUser,ArcGISPassword, "")
            arcpy.StageService_server(sdDraft, sd)

            arcpy.UploadServiceDefinition_server(sd, "My Hosted Services")
            arcpy.SignOutFromPortal_server()

            print "Success!"
        
    except Exception as e:
       print "Error: " + e.message
       arcpy.AddError(e.message)
   
def createSchemaFromSurvey():
    
    try:
       surveyCSV = os.path.join(scratchWorkspace, "survey.csv")

       arcpy.CreateFeatureclass_management(gdb, 'survey' ,"POINT", spatial_reference=arcpy.SpatialReference('WGS 1984'))
       hasAttachments = False

       with open(surveyCSV) as csvfile:
         reader = csv.DictReader(csvfile)
         for row in reader:
           fieldType = row['type']
           fieldName = row['name']
           fieldAlias =  row['label']

           #print fieldType
           #If Type is a repeat, add related table to support it
           if (fieldType.find("begin repeat")==0):
               print "Adding Repeat: '" + fieldName + "'"
               addRelatedTable (fieldName, reader)
               
           # If Type is 'select_one', add a field with its corresponding coded value domain
           elif (fieldType.find("select_one ")==0):
             domainName = fieldType.replace("select_one ","")
             print "Adding Field '" + fieldName + "' with Domain: " + domainName
             if not (domainName in codedValueDomains):
                addCodedValueDomain (domainName)
             arcpy.AddField_management(featureclass, fieldName,field_type="TEXT" ,field_alias=fieldAlias, field_domain=domainName)
           # If Type is 'select_multiple', add as many fields as needed to support the choices 
           elif (fieldType.find("select_multiple ")==0):
             choiceList = fieldType.replace("select_multiple ","")
             print "Adding Field '" + fieldName + "' With multiple Selection: " + choiceList
             addFieldsFromMultipleChoice (choiceList)
           # If Type is 'image', 'audio' or 'video' enable attachments if not already
           elif (fieldType == "image" or fieldType == "audio" or fieldType == "video"):
             if (hasAttachments == False):
                print "Enabling Attachments"
                arcpy.EnableAttachments_management(in_dataset=featureclass)
                hasAttachments = True
           # Add standard fields as long as the Type is not a 'note', 'group' or empty      
           elif ((fieldType == "text")or (fieldType == "barcode")  or (fieldType == "integer") or (fieldType == "double")or (fieldType == "date") or (fieldType == "time")):
              if (fieldType == "text") or (fieldType=="barcode"):
                fieldType="TEXT"
              elif (fieldType == "integer"):
                fieldType="LONG"
              elif (fieldType == "decimal"):
                fieldType="DOUBLE" 
              elif (fieldType == "date") or (fieldType == "time"):
                fieldType="DATE"
              print "Adding Field: '" + fieldName + "'"
              arcpy.AddField_management(featureclass, fieldName,field_type=fieldType ,field_alias=fieldAlias)
              

    except Exception as e:
       print e.message
       arcpy.AddError(e.message)

# Add related table to support Repeats
# Note: nested repeats not supported
def addRelatedTable(repeatName, reader):
       print "   Adding Related Table: " + repeatName
       try:
         arcpy.CreateTable_management(out_path=gdb, out_name=repeatName, template="", config_keyword="")
         table = os.path.join (gdb,repeatName)
         # We append the table to a global list to avoid creating this table twice
         tables.append(table)
         arcpy.AddField_management(table, "KeyField",field_type="LONG" ,field_alias="KeyField")

         print "   Adding Relationship class " 
         arcpy.CreateRelationshipClass_management(origin_table=featureclass, destination_table=table, out_relationship_class=featureclass + "_" + repeatName, relationship_type="SIMPLE", forward_label=repeatName, backward_label="survey", message_direction="NONE", cardinality="ONE_TO_MANY", attributed="NONE", origin_primary_key="OBJECTID", origin_foreign_key="KeyField", destination_primary_key="", destination_foreign_key="")
         
         row = reader.next()
         while row:
          fieldName = row['name']
          fieldAlias = row['label']
          fieldType =  row['type']

          if (fieldType.find("end repeat")==0):
               return

          # If Type is 'select_one', add a field with its corresponding coded value domain
          if (fieldType.find("select_one ")==0):
             domainName = fieldType.replace("select_one ","")
             print "       Adding Field '" + fieldName + "' with Domain: " + domainName
             if not (domainName in codedValueDomains):
                addCodedValueDomain (domainName)
             arcpy.AddField_management(table, fieldName,field_type="TEXT" ,field_alias=fieldAlias, field_domain=domainName)
          # If Type is 'select_multiple', add as many fields as needed to support the choices 
          elif (fieldType.find("select_multiple ")==0):
             choiceList = fieldType.replace("select_multiple ","")
             print "       Adding Field: '" + fieldName + "' With multiple Selection: " + choiceList
             addFieldsFromMultipleChoice (choiceList)
          # If Type is 'image', 'audio' or 'video' enable attachments if not already
          elif (fieldType == "image" or fieldType == "audio" or fieldType == "video"):
             if (hasAttachments == False):
                print "       Enabling Attachments"
                arcpy.EnableAttachments_management(in_dataset=featureclass)
                hasAttachments = True
          # Add standard fields as long as the Type is not a 'note', 'group' or empty 
          elif ((fieldType == "text")or (fieldType == "barcode")  or (fieldType == "integer") or (fieldType == "double")or (fieldType == "date") or (fieldType == "time")):
              if (fieldType == "text") or (fieldType=="barcode"):
                fieldType="TEXT"
              elif (fieldType == "integer"):
                fieldType="LONG"
              elif (fieldType == "decimal"):
                fieldType="DOUBLE" 
              elif (fieldType == "date") or (fieldType == "time"):
                fieldType="DATE"
              print "       Adding Field: '" + fieldName + "'"
              arcpy.AddField_management(table, fieldName,field_type=fieldType ,field_alias=fieldAlias)
          row = reader.next()
          
       except Exception as e:
         print e.message
         arcpy.AddError(e.message) 

# Adds a Coded Value Domain to workspace based on choiceList
def addCodedValueDomain(inputChoiceListName):                   
    print "   addCodedValueDomain {" + inputChoiceListName + " )"
    try:
        
       if (inputChoiceListName in codedValueDomains):
           print "   coded value domain already exists. Skipping..."
           return

       arcpy.CreateDomain_management(in_workspace=gdb, domain_name=inputChoiceListName, domain_description=inputChoiceListName, field_type="TEXT", domain_type="CODED", split_policy="DEFAULT", merge_policy="DEFAULT")
       codedValueDomains.append(inputChoiceListName)
    
       choicesCSV = os.path.join(scratchWorkspace, "choices.csv")
       with open(choicesCSV) as csvfile:
         reader = csv.DictReader(csvfile)
         for row in reader:
            domainName = row['list name']
            if (domainName == inputChoiceListName):                 
                 codedValue = row['name']
                 codedDescription = row['label']
                 print "     adding coded value: " + codedValue + ":" + codedDescription
                 arcpy.AddCodedValueToDomain_management(in_workspace=gdb, domain_name=domainName, code=codedValue, code_description=codedDescription)
                

    except Exception as e:
       print e.message
       arcpy.AddError(e.message)
# Adds fields to a FC to support multiple choices questions.
def addFieldsFromMultipleChoice(inputChoiceListName):
    print "   addFieldsFromMultipleChoice(" + inputChoiceListName + ")"
    try:
       choicesCSV = os.path.join(scratchWorkspace, "choices.csv")
       with open(choicesCSV) as csvfile:
         reader = csv.DictReader(choicesCSV)
         for row in reader:
            choiceListName = row['list name']
            
            if (choiceListName==inputChoiceListName):
                fieldName = row['name']
                fieldAlias = row['label']
                print "     adding Choice as new Field: '" + fieldName + "'"
                arcpy.AddField_management(featureclass, fieldName,field_type="SHORT" ,field_alias=fieldAlias)
                

    except Exception as e:
       print e.message
       arcpy.AddError(e.message)
        
if __name__ == "__main__":
     main()