API.py

# %%
# !pip install requests

# %%
# pip install pygsheets

# %%
import requests
import json
import pandas as pd
import threading
from threading import Timer
from threading import Thread
import queue
import time
# from __future__ import print_function
from googleapiclient.discovery import build
from google.oauth2 import service_account
import pygsheets
import numpy as np
from datetime import datetime
# from save_thread_result import ThreadWithResult
# from threading import Thread, Event

prjnamesURL= 'https://docs.google.com/spreadsheets/d/1I4P7wiCXTAwnld0YQfUJC8bPtuiETna1HGWVuHbuDBk/edit#gid=0'
prjpartsURL= 'https://docs.google.com/spreadsheets/d/1I4P7wiCXTAwnld0YQfUJC8bPtuiETna1HGWVuHbuDBk/edit#gid=2102042200'
prjsectionsURL= 'https://docs.google.com/spreadsheets/d/1I4P7wiCXTAwnld0YQfUJC8bPtuiETna1HGWVuHbuDBk/edit#gid=1708565170'

dfNames=pd.DataFrame()
dfParts=pd.DataFrame()
que= queue.Queue()
returnString=''
#FIRST TIME ONLY , DONT CALL UNLESS NEEDED
def AuthorizeGoogleSheets():
  SCOPES = [
  'https://www.googleapis.com/auth/spreadsheets',
  'https://www.googleapis.com/auth/drive'
  ]
  credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES)
  spreadsheet_service = build('sheets', 'v4', credentials=credentials)
  drive_service = build('drive', 'v3', credentials=credentials)
  gc = pygsheets.authorize(custom_credentials=credentials, client_secret='credentials.json')
  return spreadsheet_service,drive_service,gc

# %% #FIRST TIME ONLY , DONT CALL UNLESS NEEDED
def createSheet():
  spreadsheet_service,drive_service,gc=AuthorizeGoogleSheets()
  #create sheet
  titles=gc.spreadsheet_titles()
  if 'API tables Output' in titles:
    ws=gc.open(str('API tables Output'))
    spreadsheetId=ws.id
  else:
    spreadsheet_details = {
    'properties': {
        'title': 'API tables Output'
        }
    }
    sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details,
                                    fields='spreadsheetId').execute()

    spreadsheetId = sheet.get('spreadsheetId')
    permission1 = {
    'type': 'anyone',
    'role': 'writer',
    # 'emailAddress': 'marthe.adr@gmail.com'
    }
    drive_service.permissions().create(fileId=spreadsheetId, body=permission1).execute()

    ws = gc.open_by_key(spreadsheetId)
    projectNamesSheet = ws.worksheet(0)

    projectNamesSheet.title='Project Names'#first sheet
    ws.add_worksheet("Project Parts")  # secondsheet
    ws.add_worksheet("Sections")  # thirdsheet
  print('SPREADHSEET IDDDD=====',spreadsheetId)
  return ws,gc,spreadsheetId


# %% [markdown]
# # **New** API

# %%
def APIValues():
  query={'loginname':'Marthe','password':'marthemain'}
  #token="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJodHRwOi8vc2NoZW1hcy54bWxzb2FwLm9yZy93cy8yMDA1LzA1L2lkZW50aXR5L2NsYWltcy9uYW1lIjoiQURSIiwibmJmIjoiMTY4MDg1OTgzMyIsImV4cCI6IjE2ODA5MDMwMzMifQ.-FULhKD_M-cyIdSMMIYDlEo9DoZtc00yIlyXIPuyLZI"
  head={'Content-Type':'application/json'}
  response=requests.post(url="https://console.trevorsadd.co.uk/account/login",json=query,headers=head)
  key=response.text

  head={'Content-Type':'application/json;charset=UTF-8',"Authorization":'Bearer {}'.format(key)}
  response=requests.post(url="https://console.trevorsadd.co.uk/project/getprojectslist",json=query,headers=head)
  dict1=response.json()
  return dict1

# %% [markdown]
# ### Generate Tables

# %%
def GenerateTables():
  dict1=APIValues()

  ##First table should ONLY contain: All project Ids and names
  #Second table should contain ProjId,ProjectPartId,ProjectPart
  #Third table should contain ProjId,ProjectPartId,ProjectSection
  table1=pd.DataFrame(columns= ['ProjectId','ProjectName'])
  table2=pd.DataFrame(columns= ['ProjectId','ProjectPartId','ProjectPart'])
  table3=pd.DataFrame(columns= ['ProjectId','ProjectPartId','ProjectSection'])
  for item in dict1:
      table1 = pd.concat([table1, pd.DataFrame([[item.get('id'),item.get('projectname')]], columns=table1.columns)], ignore_index=True)
      projectPartsList=item.get('projectparts')
      for part in projectPartsList:
        table2=  pd.concat([table2,pd.DataFrame([[item.get('id'),part.get('id'),part.get('name')]], columns=table2.columns)], ignore_index=True)
        sections=part.get('projectsections')
        for section in sections:
          table3=  pd.concat([table3,pd.DataFrame([[item.get('id'),section.get('projectPartId'),section.get('section')]], columns=table3.columns)], ignore_index=True)
  # table1 = pd.concat([table1, pd.DataFrame([['2222','trial1']], columns=table1.columns)], ignore_index=True)
  # table2=  pd.concat([table2,pd.DataFrame([['2222','66','partname']], columns=table2.columns)], ignore_index=True)
  # table3=  pd.concat([table3,pd.DataFrame([['2222','66','newSectionsss']], columns=table3.columns)], ignore_index=True)
  return table1,table2,table3 

# Set Tables to Sheets
def AppendtablestoSheets():
  gc=AuthorizeGoogleSheets()[2]
  ws = gc.open_by_key('1I4P7wiCXTAwnld0YQfUJC8bPtuiETna1HGWVuHbuDBk')
  table1,table2,table3= GenerateTables()
  projectNamesSheet=ws.worksheet_by_title('Project Names')
  projectPartsSheet=ws.worksheet_by_title('Project Parts')
  projectSectionsSheet=ws.worksheet_by_title('Sections')
  projectNamesSheet.clear()
  projectPartsSheet.clear()
  projectSectionsSheet.clear()
  #append tables to google sheets
  projectNamesSheet.set_dataframe(start='A1',df=table1)
  projectPartsSheet.set_dataframe(start='A1',df=table2)
  projectSectionsSheet.set_dataframe(start='A1',df=table3)
  
  # return table1,table2,table3
# %%
#-------------------------------------------------------------------------------CALL THIS FUNCTION TO BEGIN----------------------------------------------------------------------
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
def getPrjNames(url=prjnamesURL):
  #url first sheet of names 
  url_1 = url.replace('/edit#gid=', '/export?format=csv&gid=')
  df = pd.read_csv(url_1)
  return df['ProjectName'].tolist()

# %%
def getprjParts(chosenprjname,urlNames=prjnamesURL,urlparts=prjpartsURL):

  url_1 = urlNames.replace('/edit#gid=', '/export?format=csv&gid=')
  global dfNames
  dfNames = pd.read_csv(url_1)
  chosenprjname=chosenprjname.replace('"', '')
  chosenprjnameList=chosenprjname.split(' ')
  for i, row in dfNames.iterrows():
    prjnameString=dfNames['ProjectName'].loc[i]
    if all(name in prjnameString for name in chosenprjnameList):
      projectNameID=dfNames.iloc[i]['ProjectId']
  # projectNameID=dfNames.iloc[np.where(dfNames['ProjectName'].str.startswith(chosenprjnameList[1]))]['ProjectId']  #ProjectName == chosenprjname
  url_2 = urlparts.replace('/edit#gid=', '/export?format=csv&gid=')
  global dfParts
  dfParts = pd.read_csv(url_2)
  dfParts= dfParts.iloc[np.where(dfParts['ProjectId']==int(projectNameID))]
  parts=dfParts['ProjectPart'].tolist()
  print(parts)
#Get projectName Id
  
  return  parts

# %%
def getprjSections(chosenprjname, chosenpart,urlSection=prjsectionsURL):
  #GetprojectSections
  url_1 = urlSection.replace('/edit#gid=', '/export?format=csv&gid=')
  dfSections = pd.read_csv(url_1)
  chosenprjname=chosenprjname.replace('"', '')
  chosenpart=chosenpart.replace('"', '')
  projectId=''
  projectPartID=''
  chosenprjnameList=chosenprjname.split(' ')
  chosenpartList=chosenpart.split(' ')
  for i, row in dfNames.iterrows():
    prjnameString=dfNames['ProjectName'].loc[i]
    if all(name in prjnameString for name in chosenprjnameList):
      projectId=dfNames.iloc[i]['ProjectId']
  # projectId=dfNames.iloc[np.where(dfNames['ProjectName'].astype(str)==chosenprjname)]['ProjectId']  #ProjectName == chosenprjname
  for i, row in dfParts.iterrows():
    prjpartString=dfParts['ProjectPart'].loc[i]
    if all(part in prjpartString for part in chosenpartList):
      projectPartID=dfParts['ProjectPartId'].loc[i]
  # projectPartID=dfParts.iloc[np.where(dfParts['ProjectPart'].astype(str)==chosenpart)]['ProjectPartId']
  if projectId and projectPartID:
    dfSections=dfSections.iloc[np.where((dfSections['ProjectId']==int(projectId)) & (dfSections['ProjectPartId']==int(projectPartID)) ) ]
    sections=dfSections['ProjectSection'].tolist()
    return sections
  else:
    return


# ##############################################################################
def update_df(table1,table2,table3): #API old dictionary 

    prjnamesURL= 'https://docs.google.com/spreadsheets/d/1I4P7wiCXTAwnld0YQfUJC8bPtuiETna1HGWVuHbuDBk/edit#gid=0'
    url_1 = prjnamesURL.replace('/edit#gid=', '/export?format=csv&gid=')

    table1N,table2N,table3N=GenerateTables()
    table1N = pd.read_csv(url_1)
    t1Diff=table1N[~table1N.isin(table1)].dropna()
    t2Diff=table2N[~table2N.isin(table2)].dropna()
    t3Diff=table3N[~table3N.isin(table3)].dropna()
    returnString=''
    if not t1Diff.empty:
      returnString='Changes have been made in Projects. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).'
    if not t2Diff.empty:
      returnString='Changes have been made in Parts. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).'
    if not t3Diff.empty:
      returnString='Changes have been made in Sections. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).'
    if (not t1Diff.empty) and (not t2Diff.empty):
      returnString='Changes have been made in Projects and Parts. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).'
    if (not t1Diff.empty) and (not t3Diff.empty):
      returnString='Changes have been made in Projects and Sections. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).'
    if (not t2Diff.empty) and (not t3Diff.empty):
      returnString='Changes have been made in Parts and Sections. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).'

    # print(t1Diff,t2Diff,t3Diff)
    # result= returnString
    # print(returnString)
    # # que.put(returnString)

    print('kkkkk',datetime.now())

    
    threading.Timer(5.0,update_df, args=(table1,table2,table3)).start()
    lock=threading.Lock()

    lock.acquire()
    if lock.locked:
      lock.release()
    yield returnString
  

  


# class CustomThread(Thread):
#     # constructor
#     def __init__(self, worker_func,table1,table2,table3):
#         super().__init__()
#         self._worker_func = worker_func
#         self.table1_thread=table1
#         self.table2_thread=table2
#         self.table3_thread=table3
#         self.value1=None

#     def run(self):
#         # while not self.stop_event.is_set():
#             result=self._worker_func(self.table1_thread,self.table2_thread,self.table3_thread)
#             self.value1=result
#             time.sleep(20.0)

# def calltoUpdate(table1,table2,table3):
#     while True:
#       thread = CustomThread(update_df,table1,table2,table3)
#       # start the thread
#       thread.start()
#       # wait for the thread to finish
#       thread.join()
#       # report all values returned from a thread
#       print('OUTPIITT',thread.value1)
#       yield thread.value1


def do_every (interval, worker_func, iterations = 0):
  if iterations != 1:
    threading.Timer (
      interval,
      do_every, [interval, worker_func, 0 if iterations == 0 else iterations-1]
    ).start ()

  worker_func ()

def print_hw ():
  print ("hello world")

def print_so ():
  hi="stackoverflow"
  print('ghh')
  return hi


# call print_so every second, 5 times total