#run this before running the script:

insert into v_entryinfo("creationDate","lastUpdatedDate","dataSource") VALUES (CURRENT_DATE, CURRENT_DATE, 'DUMMY');

select * from v_entryinfo order by id desc limit 1;

INSERT INTO v_plots ("entryInfo_id") VALUES (2093909);

select * from v_plots limit 1;

INSERT INTO v_vegx ("entryInfo_id","plots_id") VALUES (2093909,397259);

INSERT INTO v_plottype ("entryInfo_id","plots_id_id") values (2093909,397259);

INSERT INTO v_geospatialtype ("entryInfo_id") values (2093909);

INSERT INTO v_measurementtype ("entryInfo_id") values (2093909);

import psycopg2

import MySQLdb

import datetime

from xml.dom import minidom

from xml.dom.minidom import getDOMImplementation

from bien.v.models import *

mysqldb = MySQLdb.connect(host="localhost", user="admin", passwd="lae5eiNa",db="bien2", use_unicode=True)

mysqlcursor = mysqldb.cursor()

psqlconn = psycopg2.connect("dbname=bien_test user=bien_dba password=bien")

psqlconn.set_client_encoding('LATIN1')

psqlcur = psqlconn.cursor()

system = "BIEN2"

scope = "system"

entryInfoInsertBeginning = 'INSERT INTO v_entryinfo ("creationDate","lastUpdatedDate","dataSource") VALUES'

vegXInsertBeginning = 'INSERT INTO v_vegx ("entryInfo_id","plots_id") VALUES'

plotsInsertBeg = 'INSERT INTO v_plots ("entryInfo_id") VALUES'

plotInsertBeg = 'INSERT INTO v_plottype ("entryInfo_id","plots_id_id","plotName","plotUniqueIdentifier","shape","locationNarrative","geospatial_id","area_id") VALUES'

countryLocationInsertBeg = 'INSERT INTO v_location ("entryInfo_id","PlotType_id_id","placeType","placeName") VALUES'

stateLocationInsertBeg = 'INSERT INTO v_location ("entryInfo_id","PlotType_id_id","placeType","placeName") VALUES'

countyLocationInsertBeg = 'INSERT INTO v_location ("entryInfo_id","PlotType_id_id","placeType","placeName") VALUES'

geospatialInsertBeg = 'INSERT INTO v_geospatialtype ("entryInfo_id","DecimalLatitude","DecimalLongitude","CoordinateUncertaintyInMeters") VALUES'

areaInsertBeg = 'INSERT INTO v_measurementtype ("entryInfo_id","value") VALUES'

batchSize = 500

maxEISql = 'select max(id) from v_entryinfo'

psqlcur.execute(maxEISql)

maxEntryInfo = psqlcur.fetchone()[0]

maxPlotsSQL = 'select max(id) from v_plots'

psqlcur.execute(maxPlotsSQL)

maxPlots = psqlcur.fetchone()[0]

if maxPlots == None:

  maxPlots = 0

maxGeospSQL = 'select max(id) from v_geospatialtype'

psqlcur.execute(maxGeospSQL)

maxGeospatial = psqlcur.fetchone()[0]

if maxGeospatial == None:

  maxGeospatial = 0

maxPlotSQL = 'select max(id) from v_plottype'

psqlcur.execute(maxPlotSQL)

maxPlot = psqlcur.fetchone()[0]

if maxPlot == None:

  maxPlot = 0

maxAreaSQL = 'select max(id) from v_measurementtype'

psqlcur.execute(maxAreaSQL)

maxArea = psqlcur.fetchone()[0]

if maxArea == None:

  maxArea = 0

getSql = 'SELECT PlotName, PlotCD, DBSourceName, Locality, Country, StateProvince, County, PlotArea, PlotAreaUnits, PlotDecimalLatitude, PlotDecimalLongitude, CoordinateUncertainty, PlotShape FROM PlotMetaDataDimension where (PlotName is not null and PlotName <> "") or (PlotCD is not null and PlotCD <> "") order by DBPlotID, DBSourceName'

mysqlcursor.execute(getSql)

result = mysqlcursor.fetchall()

eii = entryInfoInsertBeginning

vxi = vegXInsertBeginning

psi = plotsInsertBeg

pi = plotInsertBeg

cli = countryLocationInsertBeg

sli = stateLocationInsertBeg

cnli = countyLocationInsertBeg

gsi = geospatialInsertBeg

ai = areaInsertBeg

iterCt = 0

plotName = ''

coordinateUncertanity = ''

lat = ''

long = ''

for record in result:

  iterCt = iterCt + 1

  if record[0] is None or record[0] == '':

    plotName = str(record[1])

  else:

    plotName = str(record[0])

  eii  = eii  + "('" + datetime.date.today().strftime("%Y-%m-%d") + "','" + datetime.date.today().strftime("%Y-%m-%d") + "','BIEN2'), "

  psi  = psi  + '(' + str(maxEntryInfo + iterCt) + '), '

  vxi  = vxi  + '(' + str(maxEntryInfo + iterCt) + ',' + str(maxPlots + iterCt) + '), '

  if record[11] is None:

    coordinateUncertanity = "NULL"

  else:

    coordinateUncertanity = str(int(record[11]))

  if record[9] is None:

    lat = 'NULL'

  else:

    lat = str(record[9])

  if record[10] is None:

    long = 'NULL'

  else:

    long = str(record[9])

  gsi  = gsi  + '(' + str(maxEntryInfo + iterCt) + ',' + lat + ',' + long + ',' + coordinateUncertanity + '), '

  ai   = ai   + '(' + str(maxEntryInfo + iterCt) + ",'" + str(record[7]) + ' ' + str(record[8]) + "'), "

  pi   = pi   + '(' + str(maxEntryInfo + iterCt) + "," + str(maxPlots + iterCt) + ",'" + plotName + "','" + plotName + "_" + str(record[2]) + "','" + str(record[12]) + "','" + str(record[3]) + "'," + str(maxGeospatial + iterCt) + "," + str(maxArea + iterCt) + "), "

  cli  = cli  + "(" + str(maxEntryInfo + iterCt) + "," + str(maxPlot + iterCt) + ",'Country','" + str(record[4]) + "'), "

  sli  = sli  + "(" + str(maxEntryInfo + iterCt) + "," + str(maxPlot + iterCt) + ",'State','" + str(record[5]) + "'), "

  cnli = cnli + "(" + str(maxEntryInfo + iterCt) + "," + str(maxPlot + iterCt) + ",'County','" + str(record[6]) + "'), "

  if iterCt % batchSize == 0 or iterCt == len(result):

    psqlcur.execute(eii[:(len(eii)-2)])

    psqlconn.commit()

    psqlcur.execute(psi[:(len(psi)-2)])

    psqlconn.commit()

    psqlcur.execute(vxi[:(len(vxi)-2)])

    psqlconn.commit()

    psqlcur.execute(gsi[:(len(gsi)-2)])

    psqlconn.commit()

    psqlcur.execute(ai[:(len(ai)-2)])

    psqlconn.commit()

    psqlcur.execute(pi[:(len(pi)-2)])

    psqlconn.commit()

    psqlcur.execute(cli[:(len(cli)-2)])

    psqlconn.commit()

    psqlcur.execute(sli[:(len(sli)-2)])

    psqlconn.commit()

    psqlcur.execute(cnli[:(len(cnli)-2)])

    psqlconn.commit()

    eii = entryInfoInsertBeginning

    vxi = vegXInsertBeginning

    psi = plotsInsertBeg

    pi = plotInsertBeg

    cli = countryLocationInsertBeg

    sli = stateLocationInsertBeg

    cnli = countyLocationInsertBeg

    gsi = geospatialInsertBeg

    ai = areaInsertBeg

psqlconn.rollback()