# %% # !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/1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc/export?format=csv&gid=0' prjpartsURL= 'https://docs.google.com/spreadsheets/d/1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc/export?format=csv&gid=1481952020' prjsectionsURL= 'https://docs.google.com/spreadsheets/d/1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc/export?format=csv&gid=175272726' global table1 global table2 global table3 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') print('ayhagaaa') 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() # print(dict1) return dict1 # %% [markdown] # ### Generate Tables # Set Tables to Sheets def AppendtablestoSheets(): gc=AuthorizeGoogleSheets()[2] # createSheet() ws = gc.open_by_key('1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc') global table1 global table2 global table3 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() # #append tables to google sheets # table1 = table1.drop_duplicates() # table2 = table2.drop_duplicates() # table3 = table3.drop_duplicates() # projectNamesSheet.set_dataframe(start='A1',df=table1) # projectPartsSheet.set_dataframe(start='A1',df=table2) # projectSectionsSheet.clear() # projectSectionsSheet.set_dataframe(start='A1',df=table3) # return table1,table2,table3 def GenerateTables(): """ Generate tables and sort projects (table1) by the newest section creation date. """ dict1 = APIValues() # Collect data for each table table1_data=[] for item in dict1: if not item.get('hide'): table1_data.append([ item.get('id'), item.get('projectname')]) table2_data = [ [item.get('id'), part.get('id'), part.get('name')] for item in dict1 for part in item.get('projectparts', []) ] table3_data = [ [item.get('id'), part.get('id'), section.get('section')] for item in dict1 for part in item.get('projectparts', []) for section in part.get('projectsections', []) ] # Collect latest 'createdon' timestamps per project project_createdon = {} for item in dict1: project_id = item.get('id') created_dates = [ section.get('createdon') for part in item.get('projectparts', []) for section in part.get('projectsections', []) if section.get('createdon') ] if created_dates: created_dates = pd.to_datetime(created_dates) project_createdon[project_id] = max(created_dates) # Create table1 and add 'CreatedOn' for sorting table1 = pd.DataFrame(table1_data, columns=['ProjectId', 'ProjectName']) table1['CreatedOn'] = table1['ProjectId'].map(project_createdon) # Sort by newest created date table1.sort_values(by='CreatedOn', ascending=False, inplace=True) # Drop 'CreatedOn' column if not needed table1.drop(columns='CreatedOn', inplace=True) # Create other tables global table2, table3 table2 = pd.DataFrame(table2_data, columns=['ProjectId', 'ProjectPartId', 'ProjectPart']) table3 = pd.DataFrame(table3_data, columns=['ProjectId', 'ProjectPartId', 'ProjectSection']) return table1, table2, table3 table1,table2,table3= GenerateTables() def getPrjNames(): #progress_callback=None """ Returns a list of project names and IDs. """ # if progress_callback: # progress_callback(40) global table1 # if progress_callback: # progress_callback(60) return table1['ProjectName'].tolist(), table1['ProjectId'].tolist() def getprjParts(chosenprjid): """ Returns parts and their IDs for a given project ID. """ global table2 df_parts = table2[table2['ProjectId'] == int(chosenprjid)] return df_parts['ProjectPart'].tolist(), df_parts['ProjectPartId'].tolist() def getprjSections(chosenprjid=0, chosenpartid=0): """ Returns sections for a given project ID and part ID. """ global table3 print(chosenprjid,chosenpartid) if chosenprjid!=0 and chosenpartid!=0: df_sections = table3[ (table3['ProjectId'] == int(chosenprjid)) & (table3['ProjectPartId'] == int(chosenpartid)) ] return df_sections['ProjectSection'].tolist() else: return [ '1.0 substructure', '2.1 frames', '2.2 upper floor', '2.3 roof', '2.4 stairs', '2.5 external walls', '2.6 external openings', '2.7 internal walls', '2.8 internal openings', '3.1 wall finishes', '3.2 floor finishes', '3.3 ceiling finishes', '4.0 fittings, furnishings and equipments', '5.0 services', '5.1 sanitary', '7.0 works to existing buildings', '8.1 site preparation works', '8.2 roads, paths, pavings', '8.3 soft landscaping', '8.4 fencing, railing, walls', '8.5 external fixtures', '8.6 external drainage', '8.7 external services', '8.8 minor buildings', ] return None # ##############################################################################