import pyodbc, psycopg2, string, os, sys, getopt

def clean_whitespace(var):
  """
  Remove all whitespace from a string.
  """
  return var.translate(string.maketrans("", ""), string.whitespace)

def migrate(accessdb, storev):
  """
  Takes two inputs, accessdb location and store number based on database. Converts access to postgresql.
  """
  access_db = accessdb
#  sqlite_db = "C:\\users\\tyler\\desktop\\django\\pccharge_archive\\dev.db"
  store = str(storev)

  count = 0
  failcount = 0

  #open sqlite connection
  sConnection = psycopg2.connect("dbname=pccharge_archive user=postgres password=lolinternet")
  sCursor = sConnection.cursor()
  #open msaccess connection
  mConnection = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s' % access_db)
  mCursor = mConnection.cursor()

  mCursor.execute("SELECT Card, Amount, Date, TroutD, Result, Station, Issuer FROM Trans")
  index = 0
  for row in mCursor:
    index += 1
    finditems = (store, str(row.TroutD), str(row.Amount)[:-2])
    sCursor.execute("SELECT * FROM creditcards_credit WHERE store_id=%s AND troutd=%s AND amount=%s", finditems)
    if not len(sCursor.fetchall()):
      items = (str(clean_whitespace(row.Card)), str(row.Amount)[:-2], str(row.Date)[:10], str(row.TroutD), clean_whitespace(row.Result), str(clean_whitespace(row.Station)), row.Issuer, store)
      sCursor.execute("INSERT INTO creditcards_credit (card, amount, date, troutd, result, station, issuer, store_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)", items)
      count += 1
    else:
      failcount += 1
  sConnection.commit()
  # exit all connections
  sCursor.close()
  mCursor.close()
  mConnection.close()
  sConnection.close()
  return "%s records have been migrated for %s. \n%s records were skipped due to duplicate TroutD numbers." % (count, store, failcount)

if __name__ == '__main__':
  main(sys.argv[1:])