Spaces:

Marthee
/

NewDuplicate

Sleeping

File size: 8,772 Bytes

ac31690

# %%
# !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


# ##############################################################################