import gspread
import time
import datetime
import pprint
from oauth2client.service_account import ServiceAccountCredentials

# Copyright Trimble Inc., 2019
# 
# Functions for pushing data into a google sheet
#

def authenticate():
  scope = ['https://spreadsheets.google.com/feeds',
           'https://www.googleapis.com/auth/drive']

  # For details on creating a key 
  # see:https://gspread.readthedocs.io/en/latest/oauth2.html
  credentials = ServiceAccountCredentials.from_json_keyfile_name('Dashboard-e54d39fa50ad.json', scope)
  return(credentials)

def next_available_row(sheet):
  # Based on whether the "A" cell is filled in find the next
  # available free row
  str_list = list(filter(None, sheet.col_values(1)))
  return str(len(str_list)+1)

# Add a date stamped row of TT data to a google sheet
def testtrack2sheets(data):
  # Authenticate
  credentials = authenticate()
  gc = gspread.authorize(credentials)

  # Open the spreadsheet using the file ID
  wks = gc.open_by_key("1eEdrGPofwp9bX2i24qmMYp9BN88NVttv-vSpQLr3o-Q")
  now = datetime.datetime.now()

  TotalOpen   = 0
  TotalFixed  = 0
  TotalClosed = 0
  for i in range(len(data)):
    opened = int(data[i]['open'])
    fixed  = int(data[i]['fixed'])
    closed = int(data[i]['closed'])
    GSheet = int(data[i]['GSheet'])

    TotalOpen   += opened
    TotalFixed  += fixed
    TotalClosed += closed

    filterStr = data[i]['filter'].split()

    print(opened,fixed,closed,filterStr[0],GSheet)

    # Get the requested sheet within the spreadsheet
    sheet = wks.get_worksheet(GSheet)

    sheetStr = []
    sheetStr.append(str(now.year) + '-' + str(now.month) + '-' + str(now.day))
    sheetStr.append(opened)
    sheetStr.append(fixed)
    sheetStr.append(closed)
    sheetStr.append(opened + fixed + closed)
    sheet.append_row(sheetStr, value_input_option='RAW')

  # Now output the total information for all releases

  # Get the requested sheet within the spreadsheet
  sheet = wks.get_worksheet(7)

  # We want this to be relative to the data on the first day we started
  # to monitor this
  TotalOpen   -= 1070
  TotalFixed  -= 82
  TotalClosed -= 164

  sheetStr = []
  sheetStr.append(str(now.year) + '-' + str(now.month) + '-' + str(now.day))
  sheetStr.append(TotalOpen)
  sheetStr.append(TotalFixed)
  sheetStr.append(TotalClosed)
  sheetStr.append(TotalOpen + TotalFixed + TotalClosed)
  sheetStr.append(TotalFixed + TotalClosed)
  sheet.append_row(sheetStr, value_input_option='RAW')

def data2row(data,sheetNum):
  # Authenticate
  credentials = authenticate()
  gc = gspread.authorize(credentials)

  # Open the spreadsheet using the file ID
  wks = gc.open_by_key("1eEdrGPofwp9bX2i24qmMYp9BN88NVttv-vSpQLr3o-Q")
  now = datetime.datetime.now()

  sheetStr = []
  sheetStr.append(str(now.year) + '-' + str(now.month) + '-' + str(now.day))
  for i in range(len(data)):
    sheetStr.append(data[i])

  # Get the requested sheet within the spreadsheet
  sheet = wks.get_worksheet(sheetNum)
  sheet.append_row(sheetStr, value_input_option='RAW')