##Search a table
##Create a Table and add fields and rows
##Populate this table with some values
##Join this table with an existing table from a "feature layer"
##Save the joined table out to a new "feature class"
##Update values in the new "feature class"

##Created by:  Nathan Jennings
##Date:  02.16.2007

##NOTE!!!!!!!!
##This code requires ArcGIS 9.1 or later
##AddJoin is notavailable in 9.0 unless an ArcInfo License is being used.
##AddJoin does exist at all license levels of ArcGIS 9.1 and 9.2





import win32com.client, sys, string, os

gp = win32com.client.Dispatch("esriGeoprocessing.GpDispatch.1")

gp.Workspace = "C:\\nate\\GIS_Programming\\class_project"        #case does not matter for the path name

wrkspc = gp.Workspace

#create a list of fields

fieldlist = gp.ListFields("cities.shp", "*", "all")   #must include the .shp extension for shapefiles
field = fieldlist.Next()



while field <> None:
    print field.Name             #print the name out in the interactive window
    
    field = fieldlist.Next()     #go to next record in the list of fields

searchcur = gp.Searchcursor ("cities.shp", "TOTPOP > 200000", "")     #create a search cursor
row = searchcur.Next()           #go to the first record (i.e. city) in the search cursor


while row <> None:
    print row.TOTPOP             #print the total population
    print row.GetValue("UNAME")  #print the City Name
    row = searchcur.next()       #go to the next city in the list

#create a new empty table (in this case a dbf file)
tablename = "my_table68.dbf"

print tablename
gp.AddMessage(tablename)
#Create an empty table and add 2 columns to it.

try:

    # Load required toolboxes...
    #This is where the CreateTable tool exists
    gp.AddToolbox("C:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data Management Tools.tbx")

    
    

    #gp.CreateTable(wkspc, tablename) ####Although this syntax is shown in the Tool Help
    #it is not written correctly.  You have to use the syntax below.

    #Create the table - required is the output path and filename
    #Optional parameters are a template and configuration keyword for SDE
    gp.CreateTable_management("C:\\nate\\GIS_Programming\\class_project", tablename)

    print "Created Table"
    gp.AddMessage("Created Table")
   
    #Add Fields to the table
    #OID and a Field1 are added by default, Field1 can be deleted by doing a DeleteField function
    
    gp.addfield(tablename, "field_a", "text", "10")      #optional parameters are data type and attribute size
    gp.addfield(tablename, "field_b")

    print "Added Fields"
    gp.AddMessage("Added Fields")


    # Create an Insert Cursor to insert rows to the new table
    # Calculate initial values while inserting the new rows
    
    incur = gp.InsertCursor (tablename)
    x = 1 
    Mynewrow = incur.NewRow()
    while x <= 10:                 #In this case, add 10 new rows
        #Mynewrow.OID = x
        Mynewrow.Field1 = x        #Calculate Field1 to the value of x
        Mynewrow.field_a = "Nate"  #Calcualte field_a to the word "Nate"
        incur.InsertRow(Mynewrow)
        Mynewrow = incur.NewRow()
        x = x + 1
    print "Finished Inserting Rows"
    gp.AddMessage("Finished Inserting Rows")


    #  join city table to new table
    #  before this can happen, the shapefile (a "feature class") must be converted to
    #  a feature layer.  The feature layer is a "virtual" file that is created so that
    #  a join can be done.  Quotes are used throughout here, but a variable could be set
    #  up to assign the feature layer name.
 
    #  The last parameter in the Join is "KEEP_ALL" to keep all of the records from both tables
    #  "KEEP_COMMON" keep only those records that actually have matching records between the
    #  2 tables.  In the ESRI Examples, they show "Outer" as the parameter in the "KEEP_ALL" parameter
    #  and I assume "Inner".  They also have an extra "," after the KEEP_ALL parameter, which
    #  may give a syntax error during development.

    
    gp.MakeFeatureLayer("cities.shp", "citylyr")
    gp.AddJoin_management("citylyr", "cities_", tablename, "Field1", "KEEP_ALL")
    #gp.CopyFeatures_management("citylyr", "cityout5.shp")

    print "Did Join"
    gp.AddMessage("Did Join")


    #  This section is used for me to trouble shoot the Update Cursor, since
    #  I was getting the field names wrong for the query and the calculation
    #  Notice I am doing a list fields from the "feature layer" that was used during the join
    #  The names printed out show "input_table.field_name" (i.e. from the shapefile) and the
    #  "join_table.field_name" from the new table created above.

    fieldlist = gp.ListFields("citylyr", "*", "all")   #must include the .shp extension for shapefiles
    field = fieldlist.Next()



    while field <> None:
        print field.Name             #print the name out in the interactive window
    
        field = fieldlist.Next() 



    #  The section below is a test to help trouble shoot obtaining the actual values
    #  I am using a search cursor to access the particular values in a given row

    #  I am also checking to see how many rows are in the search cursor using
    #  a joined attribute.  In this case, even though I attempteing to query 15, there are only 10 records
    #  from the table created from above.  I am trying to understand better what is going on before
    #  during, and after the join in the table structure and field names.

    joincur = gp.Searchcursor ("citylyr", "Field1 < 15", "")     #create a search cursor
    
    
    joinrow = joincur.Next()           #go to the first record (i.e. city) in the search cursor
     
    
    i = 0    
    while joinrow <> None:
                 
        #print joinrow.GetValue("cities.UNAME")  #print the City Name
        #print row.GetValue(tablename.field_a)
        #print tablename[:-4] + ".field_a"
        print joinrow.GetValue("cities.UNAME") + "     " + joinrow.GetValue(tablename[:-4] + ".field_a")
        i = i + 1
        joinrow = joincur.Next()       #go to the next city in the list


    print i       #printing out the total number of items joined
    print "Finished Search"
    #upcur = gp.UpdateCursor("citylyr", "0ID > -1")



    #  Here I have to create anoutput file so that I can write the "virtual" joined tables to
    #  an output file, so I can then change the values in either the input or joined table piece
    #  This is a fundamental requirement of doing a join and being able to change values.
    #  ArcGIS does not allow writing to fields that are joined without saving the table or feature
    #  layer to a new feature class.  Within ArcMap, this is done by doing a "right-click" on the layer
    #  then "Data-->Export Data" to then save the file name.

    outfile = "cityout150.shp"
    gp.CopyFeatures_management("citylyr", outfile)
    

  #Update Values in the output file created above.  


  ##!!!!!!!!!!!!!!Can't do an update cursor on a joined table!!!!!!!!!!!!!!!!!!!!

    
  # You must copy the features of the layer file to an output feature class, then
  # an update cursor can be done.  This is shown in the above line.

  # After the copy features is done, the field names change from what was shown during
  # the join.  What this means is to get the proper query, the field names need to be
  # modified slightly.  In reality, this kind of process is unlikely to happen, since
  # one would typically be joining a table, then "reading" the values from the table, rather
  # than writing them back.  This code is showing the steps in the event this would
  # be the case.

  # The print line shows the field root name (that comes from tablename i.e. my_table, from above"
  # then strips off the ".dbf" extension + the "_x" (i.e. -6 characters from the right of "my_table_x.dbf"), where x is a value in the attribute table.
  # After the new feature class is created, the field names start with the root name of the
  # respective file, in the case below, "cities" or "my_table".  See this in the uprow.my_table_3
  # line where I am "updating" the my_table_3 field with a calculation of a field from the "cities"
  # portion of the table and the "my_table" portion of the table.

  # The first print statement is checking my syntax for the query in the update cursor
  # Notice my commented line where I have :-4.  Initially, I thought I only needed to remove
  # the extension and the dot ".dbf", but I found out through trial and error that I needed to
  # remove 2 more characters.

  # The print statement in the while loop is testing to see if my logic and math are correct
  # and is actually being made.  I want to make sure I have the syntax to the proper fields
  # done correctly.  Intially, this was failing because I forgot to put "uprow" before the "my_table_1"
  # for the calculation line.

  # In order for me to see the actual results of my calculation  
  
    print "" + tablename[:-6] + "_1 > 0"
    upcur = gp.UpdateCursor(outfile, "" + tablename[:-6] + "_1 > 0")
    uprow = upcur.Next()
    print "Got Cursor"
    while uprow:
        #uprow.tablename[:-4].field_b = uprow.cities.CITIES_ID + tablename[:-4].field1
        print uprow.cities_C_1 + uprow.my_table_1
        uprow.my_table_3 = uprow.cities_C_1 + uprow.my_table_1    # This is doing the calculation to update a field from the new table
        print "Made Calculation"                                  # that was then joined above and then was written out to the "outfile" table 
        upcur.UpdateRow(uprow)
        uprow = upcur.Next()
        
    print "Updated Data"


    # don't need the lines below
           
    #gp.CopyFeatures_management("citylyr", "cityout17.shp")
    
    #print "finished searching"
    
    gp.AddMessage("Finished Searching")
except:

    print gp.getmessages()