Spaces:
Sleeping
Sleeping
| # %% | |
| # !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 | |
| # ############################################################################## | |