Détermine si une question compta online peut-être répondu juste juridiquement

categories.py

from openpyxl import load_workbook
import csv
from collections import defaultdict, Counter

# Load the workbook
file_path = "Forum Gestion Entreprise Compta online.xlsx"
workbook = load_workbook(file_path)
sheet = workbook['Sheet1']

# Get the content of the "Droit pourquoi?" column
droit_pourquoi_column = [row[3] for row in sheet.iter_rows(min_row=2, values_only=True)]

# Function to extract sources mentioned after dashes
def extract_sources(text):
    sources = []
    lines = text.split("\n")
    for line in lines:
        if line.strip().startswith('-'):
            sources.append(line.strip('- ').strip())
    return sources

# Extract sources from the "Droit pourquoi?" column and count their occurrences
sources_count = defaultdict(int)
for entry in droit_pourquoi_column:
    if entry is not None:
        sources = extract_sources(entry)
        for source in sources:
            sources_count[source] += 1

# Sort sources by their occurrences
sorted_sources = sorted(sources_count.items(), key=lambda x: x[1], reverse=True)

# Define categories
categories = {
    "Lois et Règlements": ["loi", "règlement", "décret", "jurisprudence", "circulaire"],
    "Comptabilité": ["comptable", "règles comptables", "guides comptables"],
    "Données et Protection de la Vie Privée": ["rgpd", "protection des données"],
    "Entreprises et Contrats": ["accords", "convention", "entreprise"],
    "Doctrine Juridique": ["doctrine", "commentaires et analyses juridiques"],
    "Normes et Directives Internationales": ["traités internationaux", "directives européennes", "normes internationales"],
    "Fiscalité": ["instructions fiscales", "règlements fiscaux"],
    "Guides et Manuels": ["guides", "manuels"]
}

# Function to get category for a source
def get_category(source, categories):
    for category, keywords in categories.items():
        if any(keyword in source.lower() for keyword in keywords):
            return category
    return "Autres"

# Categorize the sources
categorized_sources = defaultdict(list)
for source, count in sorted_sources:
    category = get_category(source, categories)
    categorized_sources[category].append((source, count))

# Prepare the categories column for the original sheet
categories_column = []
for entry in droit_pourquoi_column:
    category_found = "Autres"  # Default category
    if entry is not None:
        for source in extract_sources(entry):
            category_found = get_category(source, categories)
            if category_found != "Autres":
                break
    categories_column.append(category_found)

# Add the categories column to the original sheet
for row, category in zip(sheet.iter_rows(min_row=2), categories_column):
    row[-1].offset(column=1).value = category

# Save the updated workbook to a new file
updated_file_path = "Forum_Gestion_Entreprise_Compta_online_Updated.xlsx"
workbook.save(updated_file_path)

# Function to get the top 10 resources with percentage in each category
def get_top_resources_with_percentage(categorized_sources):
    top_resources_by_category = {}
    for category, sources in categorized_sources.items():
        total_count = sum(count for _, count in sources)
        top_resources = [
            (source, count, count / total_count * 100) for source, count in sources[:10]
        ]
        top_resources_by_category[category] = top_resources
    return top_resources_by_category

# Get the top 10 resources with percentage for each category
top_resources_by_category = get_top_resources_with_percentage(categorized_sources)

# Write the top resources to a text file in the specified format
top_resources_txt_path = "top_resources_by_category.txt"
with open(top_resources_txt_path, mode='w', encoding='utf-8') as file:
    for category, resources in top_resources_by_category.items():
        file.write(f"Catégorie {category}\n")
        for resource, _, percentage in resources:
            file.write(f"{resource}, {percentage:.2f}%\n")
        file.write("\n")

droit.py

from revChatGPT.V3 import Chatbot
import json
import gspread
import time

# code inspired from https://github.com/ashawkey/chatgpt_please_improve_my_paper_writing
# auth token get from https://chat.openai.com/api/auth/session
# set others options https://github.com/acheong08/ChatGPT/blob/36d1c8fb31b560ef8cb47b9a5a524c67efd599bb/docs/README.md?plain=1#L76
print(f'[INFO] Initializing Chatbot API...')
chatbot = Chatbot(api_key="<token>",temperature=0,top_p=1,truncate_limit=1000)
print(chatbot.engine)


def can_question_answered_with_just_legal_text(titre,description):

    message =""
    for data in chatbot.ask_stream(f"""Je dispose d'une solution s'appuyant sur :
                                    Code civil
                                    Code pénal
                                    Code du travail
                                    Code de la santé publique
                                    Code de la sécurité sociale
                                    Code de commerce
                                    Code de l'environnement
                                    Code de la propriété intellectuelle Code de la route

                                    Réponds juste par oui ou non à la question suivante:
                                    Est-ce suffisant pour répondre au cas ci-dessous:
                            
                            Titre: {titre}
                            Description: {description}
                            Réponds juste par oui ou non!
                            """):
        #print("writing ...")
        #print(data, end="", flush=True)
        message=message+data
    print(titre+": "+message)
    if "Non" in message:
        return "Non"
    return "Oui"

def explication(awswer):
    message =""
    if awswer == "Non":
        for data in chatbot.ask_stream("""Quel source de données manquerait-il en plus des codes présent dans la solution ?
                                Sois le plus précis possible en énumérant juste la liste de toutes ces sources avec des bullets points
                                """):
            message=message+data
    else:
        for data in chatbot.ask_stream("Pourquoi est-ce suffisant ?"):
            message=message+data
    return message


def connect_to_google_sheet(url):
    
    # Authorize the clientsheet
    client = gspread.auth.service_account('google_service_account_file.json')
    #spreadsheets = client.list_spreadsheet_files()
    #print(spreadsheets)

    # Get the instance of the Spreadsheet
    # share the sheet with google-sheets-520@pipedream-334116.iam.gserviceaccount.com
    sheet = client.open_by_url(url)

    # Get the first sheet of the Spreadsheet
    worksheet = sheet.get_worksheet(0)

    return worksheet

def get_new_values(row_index):
    titre = worksheet.cell(row_index, 1).value  # Assuming 'Titre' is the first column
    description = worksheet.cell(row_index, 9).value  # Assuming 'Description' is the nineth column

    #print(f'Titre: {titre_value}, Description: {description_value}')
    answer = can_question_answered_with_just_legal_text(titre,description)
    explained_answer = explication(answer)
    return answer,explained_answer
def update_column(worksheet, column_name_1, column_name_2):
    # Get the index of the columns
    column_index_1 = worksheet.row_values(1).index(column_name_1) + 1
    column_index_2 = worksheet.row_values(1).index(column_name_2) + 1

    # Get all values in the 'Droit ?' column
    col_values = worksheet.col_values(column_index_1)

    # Remove leading and trailing whitespace from each value
    col_values = [val.strip() for val in col_values]

    # Find the last filled row in the specified column
    last_filled_row = len(col_values) - col_values[::-1].index('') if '' in col_values else len(col_values)
    
    # Get the number of rows with data in the worksheet (including the header row)
    data_rows = worksheet.get_all_records(default_blank='')
    num_rows = len(data_rows) + 1 # +1 to include header row

    # Update from the last filled row onwards, in batches of 50 with a delay
    for i in range(last_filled_row + 1,  num_rows + 1):
        new_value_1, new_value_2 = get_new_values(i)
        worksheet.update_cell(i, column_index_1, new_value_1)
        worksheet.update_cell(i, column_index_2, new_value_2)
        print(chatbot.get_token_count())
        chatbot.reset()
        if (i - last_filled_row) % 20 == 0:
            time.sleep(60)  # Delay for 60 seconds every 50 updates


worksheet = connect_to_google_sheet("https://docs.google.com/spreadsheets/d/1CK0C9yLKquV-8vebsuNFm3NU8Q8aie3HL4SJWnCZVBI/")
update_column(worksheet, "Droit ?","Droit pourquoi?")