API.py

# %%
# !pip install requests

# %%
# pip install pygsheets

# %%
import requests
import json
import pandas as pd

# %% [markdown]
# ## GoogleSheet

# %%
# from __future__ import print_function
from googleapiclient.discovery import build
from google.oauth2 import service_account
import pygsheets
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

# %%
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':'adr','password':'Tameen'}
  #token="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJodHRwOi8vc2NoZW1hcy54bWxzb2FwLm9yZy93cy8yMDA1LzA1L2lkZW50aXR5L2NsYWltcy9uYW1lIjoiQURSIiwibmJmIjoiMTY4MDg1OTgzMyIsImV4cCI6IjE2ODA5MDMwMzMifQ.-FULhKD_M-cyIdSMMIYDlEo9DoZtc00yIlyXIPuyLZI"
  head={'Content-Type':'application/json'}
  response=requests.post(url="https://consoletest.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://consoletest.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)
  return table1,table2,table3

# %% [markdown]
# Set Tables to Sheets

# %%

def AppendtablestoSheets(ws):

  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)

# %%
#-------------------------------------------------------------------------------CALL THIS FUNCTION TO BEGIN----------------------------------------------------------------------
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
def getPrjNames():
  ws,gc,spreadsheetId=createSheet()
  AppendtablestoSheets(ws)
  projectNamesSheet=ws.worksheet_by_title('Project Names')
  prjnamesfromSheet=projectNamesSheet.get_col(2, returnas='matrix', include_tailing_empty=False)
  # print(prjnamesfromSheet[2])
  return prjnamesfromSheet[1:]

# %%
def getprjParts(chosenprjname):
#Get projectName Id
  ws,gc,spreadsheetId=createSheet()
  projectNamesSheet=ws.worksheet_by_title('Project Names')
  prjnamesfromSheet= getPrjNames()
  index=prjnamesfromSheet.index(chosenprjname.replace('"', ''))
  # index,indexE = [o for o, x in prjnamesfromSheet if x == str(chosenprjname)] #==clicked value
  prjnameId=projectNamesSheet.cell((index+2 ,1)).value #retrieved Id
  print('iddd',prjnameId)
  #Get projectParts
  projparts=[]
  projectPartsSheet=ws.worksheet_by_title('Project Parts')
  prjpartsfromSheet=projectPartsSheet.get_col(1, returnas='matrix', include_tailing_empty=False)

  indices = [o for o, x in enumerate(prjpartsfromSheet[1:]) if x == prjnameId] #==clicked value
  print('ind',indices)
  for index in indices:
    projparts.append([projectPartsSheet.cell((index+2 ,3)).value, projectPartsSheet.cell((index+2 ,2)).value]) #retrieved Id
  return projparts

# %%
def getprjSections(chosenpart):
  #GetprojectSections
  ws,gc,spreadsheetId=createSheet()
  projsections=[]
  projectSectionsSheet=ws.worksheet_by_title('Sections')
  prjsectionsfromSheet=projectSectionsSheet.get_col(2, returnas='matrix', include_tailing_empty=False)
  chosenpart=chosenpart.replace('"', '')
  indices2 = [o for o, x in enumerate(prjsectionsfromSheet) if x == chosenpart] #part clicked on
  print('indices2',indices2)
  for index in indices2:
    projsections.append(projectSectionsSheet.cell((index+2 ,3)).value) #retrieved Id
  return projsections

# %%
# tableTrial=pd.DataFrame(data=[[0,'0x','part1'],[0,'1x','part2']],columns= ['ProjectId','ProjectPartId','ProjectPart'])
# tableTrial3=pd.DataFrame(data=[[0,'0x','0.0'],[0,'0x','1.0'],[1,'1x','3.1']],columns= ['ProjectId','ProjectPartId','ProjectSection'])


# %%
#Ex: proj 1 ekhtrnah fl dropdown-->go search in table where column of names has the name proj 1 , get id of this proj1  (same row and return it to this line)
#get parts
# partsList=list(tableTrial.loc[tableTrial['ProjectId'].isin([id])].get('ProjectPart'))
#get sections
# sectionsList=list(tableTrial3.loc[tableTrial3['ProjectId'].isin([projid]) & tableTrial3['projpartid'].isin(['0x'])].get('ProjectSection'))
# tableTrial3

# %%