google_sheet_Legend.py

# -*- coding: utf-8 -*-
"""Google Sheet to XML.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1T-b1N8Gq6wwbBurODzJIdhRQNeRbyBnz
"""

from googleapiclient.discovery import build
from google.oauth2 import service_account
import pygsheets
import ast
import re
import pandas as pd
from google.oauth2 import service_account
from googleapiclient.discovery import build
import pygsheets
import tsadropboxretrieval
import requests
import fitz
import numpy as np
from PyPDF2 import PdfReader, PdfWriter
from io import BytesIO
from PyPDF2.generic import NameObject, DictionaryObject, FloatObject, TextStringObject
import dropbox


def authorizeLegend():
  SCOPES = [
  'https://www.googleapis.com/auth/spreadsheets',
  'https://www.googleapis.com/auth/drive',
  'https://www.googleapis.com/auth/drive.metadata'
  ]
  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

#########################
global gc
global pdfpath0
global pdftitle
global worksheetw
spreadsheet_service,drive_service,gc=authorizeLegend()
#########################
def create_new_sheet(path):
    spreadsheet_details = {
        'properties': {
            'title': path
        }
    }
    sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details, fields='spreadsheetId').execute()
    spreadsheetId = sheet.get('spreadsheetId')
    permission1 = {
        'type': 'anyone',
        'role': 'writer',
    }
    drive_service.permissions().create(fileId=spreadsheetId, body=permission1, supportsAllDrives=True).execute()
    return spreadsheetId

#########################
def update_sheet(spreadsheetId, SimilarAreaDictionary, pdfpath):
    ws = gc.open_by_key(spreadsheetId)
    global pdfpath0
    pdfpath0=pdfpath
    worksheet = ws.worksheet(0)
    worksheet.title = 'Legend and data created'
    worksheet.clear()
    ws.create_developer_metadata('path', pdfpath)
    splittedpdfpath = re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,/<>?]', pdfpath)
    if splittedpdfpath[-2].startswith('2.2') or splittedpdfpath[-2].startswith('2.1'):
        worksheet.set_dataframe(start='A1', df=SimilarAreaDictionary)
    # if splittedpdfpath[-2].startswith('2.8'):
    #     DoorsLegend(SimilarAreaDictionary,spreadsheetId)
    #     return
    
    else:
        top_header_format = [
            {'mergeCells': {  # areas
                'mergeType': 'MERGE_ROWS',
                'range': {
                    'sheetId': '0',
                    'startRowIndex': 1,
                    'endRowIndex': 2,
                    'startColumnIndex': 3,
                    'endColumnIndex': 5
                }
            }},
            {'mergeCells': {  # perimeters
                'mergeType': 'MERGE_ROWS',
                'range': {
                    'sheetId': '0',
                    'startRowIndex': 1,
                    'endRowIndex': 2,
                    'startColumnIndex': 5,
                    'endColumnIndex': 7
                }
            }},
            {'mergeCells': {  # lengths
                'mergeType': 'MERGE_ROWS',
                'range': {
                    'sheetId': '0',
                    'startRowIndex': 1,
                    'endRowIndex': 2,
                    'startColumnIndex': 7,
                    'endColumnIndex': 9
                }
            }},
            {'mergeCells': {  # legend and data created
                'mergeType': 'MERGE_ROWS',
                'range': {
                    'sheetId': '0',
                    'startRowIndex': 0,
                    'endRowIndex': 1,
                    'startColumnIndex': 0,
                    'endColumnIndex': 11
                }
            }}
        ]
        spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': top_header_format}).execute()
        worksheet.cell((1, 1)).value = 'Legend and Data Created'
        worksheet.cell((2, 1)).value = 'Guess'
        worksheet.cell((2, 2)).value = 'Color'
        worksheet.cell((2, 3)).value = 'Count'
        worksheet.cell((2, 4)).value = 'Areas'
        worksheet.cell((2, 6)).value = 'Perimeters'
        worksheet.cell((2, 8)).value = 'Lengths'
        worksheet.cell((2, 10)).value = 'Texts'
        worksheet.cell((2, 11)).value = 'Comments'
        second_row_data = ['Nr', 'm2', 'Total', 'm', 'Total', 'm', 'Total']
        worksheet.update_row(3, second_row_data, col_offset=2)
        if len(list(SimilarAreaDictionary['Guess']))>0:
          worksheet.update_col(1, list(SimilarAreaDictionary['Guess']), row_offset=3)
          worksheet.update_col(3, list(SimilarAreaDictionary['Occurences']), row_offset=3)
          worksheet.update_col(4, list(SimilarAreaDictionary['Area']), row_offset=3)
          worksheet.update_col(5, list(SimilarAreaDictionary['Total Area']), row_offset=3)
          worksheet.update_col(6, list(SimilarAreaDictionary['Perimeter']), row_offset=3)
          worksheet.update_col(7, list(SimilarAreaDictionary['Total Perimeter']), row_offset=3)
          worksheet.update_col(8, list(SimilarAreaDictionary['Length']), row_offset=3)
          worksheet.update_col(9, list(SimilarAreaDictionary['Total Length']), row_offset=3)
          worksheet.update_col(10, list(SimilarAreaDictionary['Texts']), row_offset=3)
          worksheet.update_col(11, list(SimilarAreaDictionary['Comments']), row_offset=3)
        
        if splittedpdfpath[-2].startswith('1.0'):
            colorsUsed = [
                [SimilarAreaDictionary['R'].iloc[i], SimilarAreaDictionary['G'].iloc[i], SimilarAreaDictionary['B'].iloc[i]]
                for i in range(len(SimilarAreaDictionary))
            ]
        elif splittedpdfpath[-2].startswith('3.2') or splittedpdfpath[-2].startswith('3.3') or splittedpdfpath[-2].startswith('2.7'):
            colorsUsed = list(SimilarAreaDictionary['Color'])
        rowsLen = len(SimilarAreaDictionary.values.tolist())
        lastcell = worksheet.cell((rowsLen + 2, 1))
        lastcellNotation = str(lastcell.address.label)
        columnsLen = len(SimilarAreaDictionary.columns.values.tolist())
        lastUsedCol = columnsLen + 1
        worksheet.adjust_column_width(start=2, end=3)
        worksheet.adjust_column_width(start=10, end=10)
        worksheet.adjust_column_width(start=4, end=9, pixel_size=60)
        startrow = 3
        endColindex = 11
        endrow = 3
        sheetId = '0'
        batch_requests = []
        for i in range(len(colorsUsed)):
            r, g, b = colorsUsed[i]
            batch_requests.append({
                "updateCells": {
                    "range": {
                        "sheetId": sheetId,
                        "startRowIndex": i + startrow,
                        "startColumnIndex": 1,
                    },
                    "rows": [
                        {
                            "values": [
                                {
                                    "userEnteredFormat": {
                                        "backgroundColor": {
                                            "red": r / 255,
                                            "green": g / 255,
                                            "blue": b / 255,
                                            "alpha": 0.4,
                                        }
                                    }
                                }
                            ]
                        }
                    ],
                    "fields": "userEnteredFormat.backgroundColor",
                }
            })
        batch_requests.append({
            "updateBorders": {
                "range": {
                    "sheetId": sheetId,
                    "startRowIndex": 0,
                    "endRowIndex": len(SimilarAreaDictionary) + endrow,
                    "startColumnIndex": 0,
                    "endColumnIndex": endColindex
                },
                "top": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                },
                "bottom": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                },
                "left": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                },
                "right": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                },
                "innerHorizontal": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                },
                "innerVertical": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                }
            }
        })
        spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': batch_requests}).execute()
   
    model_cell = worksheet.cell('A1')
    model_cell.set_text_format('bold', True)
    model_cell.set_horizontal_alignment(pygsheets.custom_types.HorizontalAlignment.CENTER)
    model_cell.color = (213 / 255, 219 / 255, 255 / 255)
    pygsheets.DataRange('A2', 'K2', worksheet=worksheet).apply_format(model_cell)

#########################
def legendGoogleSheets(SimilarAreaDictionary, path, pdfpath, spreadsheetId=0):
    titles = gc.spreadsheet_titles()
    if path in titles:
        ws = gc.open(path)
        global pdftitle
        pdftitle=path
        spreadsheetId = ws.id
    else:
        spreadsheetId = create_new_sheet(path)
        ws=gc.open_by_key(spreadsheetId)
    global worksheetw
    worksheetw = ws.worksheet(0) #legend
    update_sheet(spreadsheetId, SimilarAreaDictionary, pdfpath)
    spreadsheet_url = f"https://docs.google.com/spreadsheets/d/{spreadsheetId}"
    drive_service.permissions().update(transferOwnership=True, fileId=spreadsheetId, permissionId='11OfoB4Z6wOVII8mYmbnCbbqTQs7rYA65')
    
    namepathArr = [path, spreadsheetId, ws.get_developer_metadata('path')[0].value]
    return gc, spreadsheet_service, spreadsheetId, spreadsheet_url, namepathArr

#########################
def mapnametoLegend(McTName,colorsused,pdflink): 

  sectionKey = McTName.pop()
  key=sectionKey[0]
  section=sectionKey[1]

  # spreadsheet_service,drive_service,gc=authorizeLegend()
  spreadsheet_key =str(key)  # Please set the Spreadsheet ID.
  
  ws = gc.open_by_key(spreadsheet_key)
  
  # guessednamesfinal=getguessnames(gc,ws)
  sheetnames=[]
  unit=''
  # ws.add_worksheet("Summary")  # Please set the new sheet name.
  for i in ws._sheet_list:
    # print(i)
    sheetnames.append(i.title)
    # print(i.index)
  if 'XML Export Summary' in sheetnames:
    worksheetS = ws.worksheet_by_title('XML Export Summary')
  else:
    ws.add_worksheet("XML Export Summary")  # Please set the new sheet name.
  global worksheetw
  worksheetw = ws.worksheet(0) #legend
  worksheetS = ws.worksheet_by_title('XML Export Summary')
  summaryId= ws[1].id
  
  worksheetS.clear()

  countnames=0
  row0=['MC_T Name','Qty','Unit']
  worksheetS.update_row(1,row0)
  
  allrows=[]
  for i in range(len(McTName)):
    allgbnames=''
    item=''

    
    # firstpart= re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,./<>?]', McTName[i][0])
    # print('kkk' ,McTName[i][2])
    if McTName[i][2].startswith('Area'):
      if section.startswith('1.0') or section.startswith('3.2') or section.startswith('3.3'):
        rowvalue=5# column 5 
      ar=0
      unit='m2'
    if McTName[i][2].startswith('Perimeter'):
      if section.startswith('1.0') or section.startswith('3.2') or section.startswith('3.3'):
        rowvalue=7# column 7
      ar=0   
      unit='m'
    if McTName[i][2].startswith('Length'):
      if section.startswith('1.0') or section.startswith('3.2') or section.startswith('3.3'):
        rowvalue=9# column 7
      ar=0   
      unit='m'
    if McTName[i][2].startswith('Count'):
      if section.startswith('1.0') or section.startswith('3.2') or section.startswith('3.3'):
        rowvalue=3# column 7

      ar=0   
      unit='Nr'
 
    if isinstance(McTName[i][1], list):
      guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False)

      for m in McTName[i][1]:
        if m:
          if m.startswith('text1'):
            name=m.removeprefix('text1')

            allgbnames+= name +' + '
            indices = [o for o, x in enumerate(guessednames) if x == name]
            # print(indices)
            for j in range(len(indices)):
              # print('kjjjj',roww[j])
              ar+=float(worksheetw.cell((indices[j]+1 ,rowvalue)).value) 
          else:
              if section.startswith('3.2') or section.startswith('3.3'):
                item+=m
              else: 
                item+=m + ' ,'
              # print(item)
          if section.startswith('3.2') or section.startswith('3.3'):
            n= McTName[i][0] + ' ( '+ allgbnames[:-2]+ ' )  '
          else:
            n= McTName[i][0] + ' ( '+ allgbnames[:-2] +' , ' + item[:-1]  + ' )  '
    else:
      if McTName[i][1].startswith('text1'):
        name=McTName[i][1].removeprefix('text1')
        allgbnames+= name

        roww=worksheetw.find(name)
        for j in range(len(roww)):
          # print('kjjjj',roww[j])
          ar+=float(worksheetw.cell((roww[j].row ,rowvalue)).value) 
        n= McTName[i][0] + ' ( '+ allgbnames + ' )  ' 

    rowi=[str(n),ar,unit]

    allrows.append([rowi[0],unit])
    
    worksheetS.update_row(i+2,rowi)
    # worksheetS.adjust_column_width(start=1,end=4)
    worksheetS.adjust_column_width(start=1,end=1, pixel_size=350)
    worksheetS.adjust_column_width(start=2,end=2, pixel_size=100)
    worksheetS.adjust_column_width(start=3,end=3)
  guessednamesfinal=getguessnames(worksheetw,colorsused) #gb,color
  mctname_guessedNames=label_MC_Tname(allrows) #mctname, gb1
  mappedMct_colors=mctname_colors(guessednamesfinal,mctname_guessedNames) #mctname,gb1
  adjustannotations(pdflink, mappedMct_colors)

  xx=(worksheetS.cell( ( len(McTName) +1 ,3)) ).address.label
  model_cell1 =worksheetS.cell('A2')
  model_cell1.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.LEFT )
  pygsheets.DataRange('A2', str(xx), worksheet=worksheetS).apply_format(model_cell1)


  model_cell =worksheetS.cell('A1')
  model_cell.set_text_format('bold', True)
  model_cell.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.CENTER )
  pygsheets.DataRange('A1','C1', worksheet=worksheetS).apply_format(model_cell)
  
  body2={
  "requests": [
    {
      "updateBorders": {
        "range": {
          "sheetId": str(summaryId),
          "startRowIndex": 0,
          "endRowIndex":  len(McTName) +1 ,
          "startColumnIndex": 0,
          "endColumnIndex": 3
        },
        "top": {
          "style": "SOLID",
          "width": 2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
        "bottom": {
          "style": "SOLID",
          "width": 2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
        "left":{
          "style": "SOLID",
          "width":2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
        "right":{
          "style": "SOLID",
          "width": 2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
        "innerHorizontal":{
          "style": "SOLID",
          "width":2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
        "innerVertical": {
          "style": "SOLID",
          "width": 2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
      }
    }
  ]
  }
  spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_key, body=body2).execute()
  return summaryId #,guessednamesfinal   

  # print(x,xarea)

#########################
def label_MC_Tname(mctname_names):
  # print('mctname_names',mctname_names)
  mctname_guessedNames=[]
  # unit=mctname_names[1]
  for row in mctname_names:
    text=row[0]
    unit=row[1]
    # print(text,unit)
    text_before_parenthesis = text.split('(')[0].strip()
    # Step 2: Extract text inside the parentheses
    inside_parentheses = re.search(r'\((.*?)\)', text)
    if inside_parentheses:
        inside_parentheses = inside_parentheses.group(1).strip()
        if '+' in inside_parentheses:
            # Split the content by '+', and strip any leading/trailing spaces
            parts = [part.strip() for part in inside_parentheses.split('+')]
        else:
            # If no '+', just put the whole content as a single part
            parts = [inside_parentheses]
        mctname_guessedNames.append([text_before_parenthesis,parts , unit])
        # print('mctname_guessedNames',mctname_guessedNames)
  return mctname_guessedNames  
  
#########################
def mctname_colors(color_dict, mctname_gb):
    
    # print('color_dict', color_dict)
    # print('mctname_gb', mctname_gb)

    # Convert the color_dict array into a dictionary with trimmed keys for easier lookup
    color_dict_map = {color[0].strip(): color[1] for color in color_dict}
    # print('color_dict_map', color_dict_map)

    # Result list to store the final output
    result = []

    # Processing the items
    for item in mctname_gb:
        name = item[0]
        colors = item[1]
        unit=item[2]
        # print(name,colors)
        # Get RGB values for the corresponding colors
        color_values = []
        for color in colors:
            trimmed_color = color.strip()  # Trim any leading/trailing spaces
            if trimmed_color in color_dict_map:
                color_values.append(color_dict_map[trimmed_color])
            else:
                # Handle missing references (e.g., append None or a default color)
                color_values.append(None)  # Or append a default color like (0, 0, 0)

        # Join the name with the corresponding RGB colors
        result.append([name, color_values,unit])

    # Print the result
    # print('result', result)
    return result



#########################
def getguessnames(worksheetw,colorsused):
  guessednamesfinal=[]
  # print('colorsused',colorsused)
  guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False)
  # print(guessednames)
  for i in range(len(guessednames[3:])):
      item = guessednames[3:][i]  # Name from the guessednames list

      colorforitem = colorsused[i]  # Get the corresponding color for the current item      
      if item not in guessednamesfinal:
          guessednamesfinal.append([item, colorforitem]) 
  # print('guessednamesfinal', guessednamesfinal)
  return guessednamesfinal

#########################
def is_color_within_tolerance(color1, color2, tolerance):
    # Ensure both colors are tuples of integers
    color1 = tuple(map(int, color1))
    color2 = tuple(map(int, color2))
    
    return all(abs(c1 - c2) <= tolerance for c1, c2 in zip(color1, color2))

#########################
def deletefromlegend(deletedrows, SimilarAreaDictionarycopy, section, areaPermArr=[]):
    items = []
    # print('deletefromlegend',deletedrows)
    idx = 0
    if section.startswith('1.0') or section.startswith('3.2') or section.startswith('3.3'):
        areaPermArr = ast.literal_eval(areaPermArr)
        print(areaPermArr)
    myDict = eval(SimilarAreaDictionarycopy)
    SimilarAreaDictionarycopy = pd.DataFrame(myDict)
    strings = deletedrows['content']
    print('content',deletedrows)
    colors = deletedrows['color']
    indicies_toDelete=[]
    # print(colors)
    # Define your tolerance value
    tolerance = 2 # Allowable tolerance for RGB differences
    # print(SimilarAreaDictionarycopy)
    color_list = list(SimilarAreaDictionarycopy['Color'])  # Convert Index/Series to list
    if section.startswith('1.0'):      
        
       color_list = [ast.literal_eval(color) for color in color_list]
        
    for j in range(len(colors)):
        # newitem=str(colors[j]).split('\n \n')
        # input_str = " ".join(str(newitem).split())
        color = tuple(colors[j])  # Ensure 'color' is in tuple format
        found = False

        for idx, existing_color in enumerate(color_list):
            existing_color = tuple(existing_color)  # Ensure it's a tuple for comparison

               
            # print('eee',existing_color,color)
            if is_color_within_tolerance(existing_color, color, tolerance):
                print(f'Color {color} found close to {existing_color} at index {idx}')
                found = True
                # print('strings',strings[j])
                # matchA = re.search(r"Area=(\d+\.\d+)", strings[j])
                # matchP = re.search(r"Perimeter=(\d+\.\d+)", strings[j])


                # else:
                #   matchL = re.search(r"(\d+\.\d+)\s*m(?![a-zA-Z])", strings[j])

                comment = SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Comments')]
                occ = SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Occurences')]

                # Only subtract area if the area value is found
                if "Area" in deletedrows['subject'][j]:
                    matchA = re.search(r"(\d+\.\d+)\s*sq\s*m",strings[j])
                    print('matchA')
                    # print(' SimilarAreaDictionaryArea',  float(matchA.group(1)),   SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Total Area')])
                    SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Total Area')] =SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Total Area')] - float(matchA.group(1))
                    # Update area occurrences
                    if pd.notna(comment) and 'Area' in str(comment):
                        matches = re.findall(r'\b\d+\b', str(comment))
                        area_occurrences = int(matches[0]) - 1
                        perimeter_occurrences = int(matches[1])
                    else:
                        area_occurrences = int(SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Occurences')]) - 1
                        perimeter_occurrences = SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Occurences')]

                    SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Comments')] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}'
                if "Perimeter" in deletedrows['subject'][j]:
                    matchP = re.search(r"(\d+\.\d+)\s*m(?![a-zA-Z])", strings[j])
                    print('matchP')
                    SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Total Perimeter')] =SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Total Perimeter')]  - float(matchP.group(1))# Replace 'Area' with the actual column name
                    if pd.notna(comment) and 'Perimeter' in str(comment):
                      matches = re.findall(r'\b\d+\b', str(comment))
                      area_occurrences = int(matches[0])
                      perimeter_occurrences = int(matches[1]) -1
                    else:       

                      area_occurrences = int(SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Occurences')]) 
                      perimeter_occurrences = SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Occurences')]-1
                      # print(area_occurrences,perimeter_occurrences)
                      SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Comments')] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}'
                else:
                      matchL = re.search(r"(\d+\.\d+)\s*m(?![a-zA-Z])", strings[j])  



                # Handle occurrences and row deletion
                if area_occurrences==0 and perimeter_occurrences==0:
                    # if area_occurrences ==0 and perimeter_occurrences==0:
                      if str(idx) not in indicies_toDelete:
                        indicies_toDelete.append(str(idx))
                    # print(SimilarAreaDictionarycopy.index[idx],idx)
                    

                else:
                    SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Comments')] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}'
                    if area_occurrences > perimeter_occurrences:
                        SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Occurences')] = area_occurrences
                    elif perimeter_occurrences > area_occurrences:
                        SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Occurences')] = perimeter_occurrences
                    else:
                        SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Occurences')] = area_occurrences
                    if area_occurrences==1:
                       SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Area')]= SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Total Area')] 
                    if perimeter_occurrences==1:
                       SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Perimeter')]= SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Total Perimeter')] 
                    if area_occurrences==perimeter_occurrences:
                       if section.startswith('1.0'):
                          SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Total Length')] =SimilarAreaDictionarycopy.iloc[int(idx), SimilarAreaDictionarycopy.columns.get_loc('Total Length')]  - float(matchL.group(1))# Replace 'Area' with the actual column name
                break

        if not found:
            print(f'Color {color} not found within tolerance')
    # print('indicies_toDelete',indicies_toDelete)

    SimilarAreaDictionarycopy.drop(index=indicies_toDelete, axis=0, inplace=True)
    # print('SimilarAreaDictionarycopy',SimilarAreaDictionarycopy)
    return SimilarAreaDictionarycopy

#########################
def DoorsLegend(Dictionary,spreadsheetId):
    print('id',spreadsheetId)
    global worksheetw
    worksheetw.clear()
    top_header_format = [
            {'mergeCells': {  # legend and data created
                'mergeType': 'MERGE_ROWS',
                'range': {
                    'sheetId': '0',
                    'startRowIndex': 0,
                    'endRowIndex': 1,
                    'startColumnIndex': 0,
                    'endColumnIndex': 2
                }
            }}
    ]
    print('here')
    # spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': top_header_format}).execute()
    worksheetw.cell((1, 1)).value = 'Legend and Data Created'
    worksheetw.set_dataframe(start='A2', df=Dictionary)
    print('here1')
    body2={ 
      "requests": [
        {
          "updateBorders": {
            "range": {
              "sheetId": str(0),
              "startRowIndex": 0,
              "endRowIndex":  4,
              "startColumnIndex": 0,
              "endColumnIndex": 2
            },
            "top": {
              "style": "SOLID",
              "width": 2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
            "bottom": {
              "style": "SOLID",
              "width": 2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
            "left":{
              "style": "SOLID",
              "width":2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
            "right":{
              "style": "SOLID",
              "width": 2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
            "innerHorizontal":{
              "style": "SOLID",
              "width":2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
            "innerVertical": {
              "style": "SOLID",
              "width": 2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
          }
        }
      ]
    }
    print('here2')
    # spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body=body2).execute()
    model_cell = worksheetw.cell('A1')
    model_cell.set_text_format('bold', True)
    model_cell.set_horizontal_alignment(pygsheets.custom_types.HorizontalAlignment.CENTER)
    model_cell.color = (213 / 255, 219 / 255, 255 / 255)
    pygsheets.DataRange('A2', 'B2', worksheet=worksheetw).apply_format(model_cell)
    print('here3')
    return Dictionary
#########################

def deletedoors(deletedrows,dictionary):

  items=[]
  # print('dletefromlegend2.8')
  idx=0
  myDict=eval(dictionary)
  df_doors=pd.DataFrame(myDict)
  strings=deletedrows['content']
  print('strings',strings)
  for item in strings:
    newitem=str(item).split('\n \n')
    if 'Single' in str(newitem):
      current_value = df_doors.loc[df_doors['Type'] == 'Single Doors', 'Quantity'].values[0]
      new_value = int(current_value) - 1
      df_doors.loc[df_doors['Type'] == 'Single Doors', 'Quantity'] = str(new_value)
    elif 'Double' in str(newitem):
      current_value = df_doors.loc[df_doors['Type'] == 'Double Doors', 'Quantity'].values[0]
      new_value = int(current_value) - 1
      df_doors.loc[df_doors['Type'] == 'Double Doors', 'Quantity'] = str(new_value)
  df_doors = df_doors[['Type', 'Quantity']]
  print(df_doors)
  return df_doors

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

def deletemarkups(list1, dbPath, path):
    '''list1 : original markup pdf
       list2 : deleted markup pdf
       deletedrows : deleted markups - difference between both dfs
    '''

    myDict1 = eval(list1)
    list1 = pd.DataFrame(myDict1)

    dbxTeam = tsadropboxretrieval.ADR_Access_DropboxTeam('user')
    
    md, res = dbxTeam.files_download(path=dbPath + path)
    data = res.content
    doc = fitz.open("pdf", data)
    
    # Prepare a DataFrame for the annotations in the new PDF
    list2 = pd.DataFrame(columns=['content', 'id', 'subject', 'color'])

    for page in doc:
        # Iterate through annotations on the page
        for annot in page.annots():
            # Get the color of the annotation
            annot_color = annot.colors
            if annot_color is not None:
                # Check for fill or stroke color
                stroke_color = annot_color.get('stroke')
                fill_color = annot_color.get('fill')

                v = 'stroke' if stroke_color else 'fill'
                color = annot_color.get(v)
                if color:
                    # Convert color to tuple and multiply by 255 to get RGB values
                    color_tuple = (int(color[0] * 255), int(color[1] * 255), int(color[2] * 255))
                    # Append annotation data to list2
                    list2.loc[len(list2)] = [annot.info['content'], annot.info['id'], annot.info['subject'], color_tuple]

    # Ensure that colors are stored as tuples (which are hashable)
    list1['color'] = list1['color'].apply(lambda x: tuple(x) if isinstance(x, list) else x)
    
    # Find the deleted rows by checking the difference between original and current annotations
    deletedrows = pd.concat([list1, list2]).drop_duplicates(keep=False)

    # print(deletedrows, len(deletedrows))
    flag = 0
    if len(deletedrows) != 0:
        flag = 1
        deletedrows = deletedrows[['content', 'id', 'subject', 'color']]
        # Drop rows where 'content' starts with 'Scale'
        deletedrows = deletedrows.drop(deletedrows.index[deletedrows['content'].str.startswith('Scale')])
    else:
        flag = 0
    
    return deletedrows

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


def adjustannotations(pdflink, color_items):
    # Load the input PDF
  pdf_content = None
  if pdflink and ('http' in pdflink or 'dropbox' in pdflink):
    # Modify Dropbox link for direct download
    # print('pdffff',pdflink)
    if 'dl=0' in pdflink:
      pdflink = pdflink.replace('dl=0', 'dl=1')

      # Download the PDF content from the shareable link
      response = requests.get(pdflink)
      pdf_bytes_io = BytesIO(response.content)  # Store the content in memory
      # pdf_bytes_io = BytesIO(OutputPdfStage1)
      reader = PdfReader(pdf_bytes_io)
      writer = PdfWriter()

      # Append all pages to the writer
      writer.append_pages_from_reader(reader)

      # Add metadata (optional)
      metadata = reader.metadata
      writer.add_metadata(metadata)

      # Convert color_items array to a more accessible structure (a dictionary of colors)
      color_dict = {}
      for item in color_items:
          label = item[0]
          colors = item[1]
          unit = item[2]
          # print('UNIT', unit)
          for color in colors:
              color_dict[tuple(color)] = (label, unit)  # Map RGB color to a tuple (label, unit)

      # print(color_dict)

      # Iterate over pages to check annotations and update label
      for page_index, page in enumerate(writer.pages):
          if "/Annots" in page:
              annotations = page["/Annots"]
              for annot_index, annot in enumerate(annotations):
                  obj = annot.get_object()

                  # Only proceed if the annotation has a color and is a valid markup (e.g., Polygon)
                  if "/C" in obj:
                      color = tuple(obj["/C"])  # Extract the RGB color (list of 3 values)
                      color = (int(round(color[0] * 255, 1)), int(round(color[1] * 255, 1)), int(round(color[2] * 255, 1)))

                      # If the color matches any item in color_dict, update the label
                      if color in color_dict:
                          label, unit = color_dict[color]  # Retrieve both label and unit
                          # print(unit)
                          # Update annotation label (e.g., /Contents or /Subj)
                          if "/Contents" in obj:
                              if unit == 'm2' and "sq m" in obj["/Contents"]:
                                  obj.update({
                                      NameObject("/Label"): TextStringObject(f"{label}")
                                  })
                              elif unit == 'm' and "m" in obj["/Contents"] and "sq" not in obj["/Contents"]:  # Ensure it's not an area
                                  obj.update({
                                      NameObject("/Label"): TextStringObject(f"{label}")
                                  })
                          # print(f"Updated Annotation: {obj}")

     # Save the modified PDF
      output_pdf_io = BytesIO()
      writer.write(output_pdf_io)
      output_pdf_io.seek(0)

      # Save the content to a file (avoid using the same name for the variable and file object)
      OutputPdfStage2=output_pdf_io.read()
      doc2 =fitz.open('pdf',OutputPdfStage2)
      dbPath='/TSA JOBS/ADR Test'+pdfpath0+'Measured Plan/'+pdftitle
      # dburl=tsadropboxretrieval.uploadanyFile(doc=doc2,pdfname=pdftitle ,path=dbPath)
      # print('omarpdf',dburl)
      print("Annotations updated and saved")
      dbxTeam= tsadropboxretrieval.ADR_Access_DropboxTeam('user')
      try:
          # Upload the new file and overwrite the existing one
          dbxTeam.files_upload(doc2.write() ,dbPath, mode=dropbox.files.WriteMode('overwrite'))
              # dbxTeam.files_upload(f.read(), dropbox_file_path, mode=dropbox.files.WriteMode('overwrite'))
          print(f"File replaced successfully at")
      except dropbox.exceptions.ApiError as e:
          print(f"Error replacing the file: {e}")
      # return dburl

def getallguessednames():
    guessednamesfinal = []
    # Retrieve column data from the worksheet
    global worksheetw
    guessednames = worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False)
    if len(guessednames[3:])>0:
        # Skip the first three entries and filter out empty/whitespace strings
        for item in guessednames[3:]:
            if item.strip() and item not in guessednamesfinal:  # Remove surrounding whitespace and check for uniqueness
                guessednamesfinal.append(item)
        print('guessednamesfinal:', guessednamesfinal)
        return guessednamesfinal
    else:
       return 'None'