from office365.sharepoint.client_context import ClientContext from datetime import datetime from elasticsearch import Elasticsearch from elasticsearch import Elasticsearch import os from glob import glob from PyPDF2 import PdfReader from PyPDF2.errors import PdfReadError from json import dump,load,dumps import pdfplumber from natsort import natsorted from langchain.document_loaders import JSONLoader from langchain_openai import OpenAIEmbeddings from langchain_community.vectorstores import ElasticsearchStore import pandas as pd import json import io import inspect LOG_FILE = f"/var/www/html/carrier_appointments_json/log/carrier_appointments-log-{datetime.now().strftime('%Y-%m-%d')}.log" def custom_log(level, message): # Get caller frame info frame = inspect.currentframe().f_back filename = os.path.basename(frame.f_code.co_filename) lineno = frame.f_lineno module = frame.f_globals["__name__"] # Format timestamp timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S") # Format log line like your formatter log_line = f"{timestamp} - {module} - {level.upper():>8s} - [{filename:25s} : {lineno:3d}] - {message}\n" # Write to file with open(LOG_FILE, "a") as f: f.write(log_line) # Also print to stdout (like screen handler) print(log_line, end="") custom_log("info", "This is a log message.") es= Elasticsearch('https://elastic:8oKIqy312EBsAPzWT64NUzji@scic-elasticsearch.es.us-central1.gcp.cloud.es.io:443',request_timeout=300,retry_on_timeout=True) # Credentials (Use secure storage for passwords) username = "karthikraj@riskeducation.org" password = "Figo@6516" # Consider using secure storage, e.g., environment variables ############################# # SharePoint site and file details site_url = "https://combinedagents.sharepoint.com" file_url = "/Shared Documents/Carrier Appointments - Master List.xlsx" # SharePoint authentication try: custom_log("info", f"sharepoint authentication") ctx = ClientContext(site_url).with_user_credentials(username, password) except Exception as e: custom_log("Error", f"sharepoint connection: {e}") # Get the file from SharePoint try: file = ctx.web.get_file_by_server_relative_url(file_url).get().execute_query() sharepoint_last_modified = file.time_last_modified.strftime('%Y-%m-%dT%H:%M:%S') except Exception as e: custom_log("Error", f"sharepoint file retrieval: {e}") # Fetch the last modified date from Elasticsearch try: index_name = "carrier_appointments_last_modified_date" query = { "query": { "match": { "file_name": "Carrier Appointments - Master List.xlsx" } } } index_name = "carrier_appointments_last_modified_date" response = es.search(index=index_name, body=query) es_last_modified = None except Exception as e: custom_log("Error", f"ElasticSearch last modified date error: {e}") try: if response["hits"]["hits"]: es_last_modified = response["hits"]["hits"][0]["_source"]["last_modified"] except Exception as e: custom_log("Error", f"No last modified date error: {e}") try: # Compare values if es_last_modified == sharepoint_last_modified: custom_log("info", f"sharepoint date and ElasticSearch's date are same, so no changes") print("Last modified dates match.") elif es_last_modified != sharepoint_last_modified: # List of indices to delete # Delete old index entry index_name = "carrier_appointments_last_modified_date" file_name = "Carrier Appointments - Master List.xlsx" try: response = es.search(index=index_name, body=query) es_last_modified = None doc_id = None # Store document ID for deletion if response["hits"]["hits"]: hit = response["hits"]["hits"][0] # First matching document es_last_modified = hit["_source"]["last_modified"] doc_id = hit["_id"] # Get document ID try: if doc_id: es.delete(index=index_name, id=doc_id) print(f"Deleted outdated document with ID: {doc_id}") custom_log("info", f"Deleted outdated document with ID: {doc_id}") except Exception as e: custom_log("error", f"Error at deleting the outdate documents and the error is: {e}") except Exception as e: custom_log("Error", f"Error at getting the last modified date from ElasticSearch: {e}") # Re-index the file with the new last modified date doc = { "file_name": file_name, "last_modified": sharepoint_last_modified } try: es.index(index=index_name, document=doc) except Exception as e: custom_log("Error", f"Error at changing the last modified date to sharepoint date in ElasticSearch: {e}") indices_to_delete = [ "carrier_appointments_master_list_cl", "carrier_appointments_master_list_personal", "carrier_appointments_master_list_wholesalers", "carrier_appointments_master_list_caais" ] # Delete each index # for index in indices_to_delete: # if es.indices.exists(index=index): # es.indices.delete(index=index) # print(f"Deleted index: {index}") # else: # print(f"Index not found: {index}") # SharePoint site and folder details site_url = "https://combinedagents.sharepoint.com" relative_url = "/Shared Documents" # Credentials (Use secure storage for passwords) username = "karthikraj@riskeducation.org" password = "Figo@6516" # Consider using secure storage, e.g., environment variables # Authenticate to SharePoint ctx = ClientContext(site_url).with_user_credentials(username, password) # Get the folder by relative URL folder = ctx.web.get_folder_by_server_relative_url(relative_url) # Load folder content (files and subfolders) files = folder.files ctx.load(files) ctx.execute_query() # List files in the folder print("Files in folder:") for file in files: print(f" - {file.properties['Name']} ({file.properties['ServerRelativeUrl']})") # Download a specific file (example) file_name = "Carrier Appointments - Master List.xlsx" # Change this to your target file # Define the target directory target_dir = "/var/www/html/" # Create the full path file_path = os.path.join(target_dir, file_name) for file in files: if file.properties['Name'] == file_name: # Create a byte stream to store file content file_stream = io.BytesIO() # Download the file file.download(file_stream).execute_query() # Save the file locally with open(file_path, "wb") as f: f.write(file_stream.getvalue()) print(f"{file_path} downloaded successfully!") CL = pd.read_excel('/var/www/html/Carrier Appointments - Master List.xlsx',sheet_name="CL") Personal = pd.read_excel('/var/www/html/Carrier Appointments - Master List.xlsx',sheet_name="Personal") Wholesalers = pd.read_excel('/var/www/html/Carrier Appointments - Master List.xlsx',sheet_name="Wholesalers") CAAIS = pd.read_excel('/var/www/html/Carrier Appointments - Master List.xlsx',sheet_name="CAAIS") CL_json_excel = CL.to_json(orient='records', indent=4) Personal_json_excel = Personal.to_json(orient='records', indent=4) Wholesalers_json_excel = Wholesalers.to_json(orient='records', indent=4) CAAIS_json_excel = CAAIS.to_json(orient='records', indent=4) with open('/var/www/html/carrier_appointments_jsonCL_output.json', 'w') as file: file.write(CL_json_excel) with open('/var/www/html/carrier_appointments_jsonPersonal_output.json', 'w') as file: file.write(Personal_json_excel) with open('/var/www/html/carrier_appointments_jsonWholesalers_output.json', 'w') as file: file.write(Wholesalers_json_excel) with open('/var/www/html/carrier_appointments_jsonCAAIS_output.json', 'w') as file: file.write(CAAIS_json_excel) CL_loader = JSONLoader(file_path='/var/www/html/carrier_appointments_jsonCL_output.json', jq_schema=".[]", text_content=False) Personal_loader = JSONLoader(file_path='/var/www/html/carrier_appointments_jsonPersonal_output.json', jq_schema=".[]", text_content=False) Wholesalers_loader = JSONLoader(file_path='/var/www/html/carrier_appointments_jsonWholesalers_output.json', jq_schema=".[]", text_content=False) CAAIS_loader = JSONLoader(file_path='/var/www/html/carrier_appointments_jsonCAAIS_output.json', jq_schema=".[]", text_content=False) CL_documents = CL_loader.load() Personal_documents = Personal_loader.load() Wholesalers_documents = Wholesalers_loader.load() CAAIS_documents = CAAIS_loader.load() os.environ["OPENAI_API_KEY"] = os.environ.get("OPENAI_API_KEY") embeddings=OpenAIEmbeddings() db = ElasticsearchStore.from_documents( documents=CL_documents, es_connection=es, index_name='carrier_appointments_master_list_cl', embedding=embeddings, strategy=ElasticsearchStore.ExactRetrievalStrategy()) custom_log("info", "carrier_appointments_master_list_cl indexing completed") db = ElasticsearchStore.from_documents( documents=Personal_documents, es_connection=es, index_name='carrier_appointments_master_list_personal', embedding=embeddings, strategy=ElasticsearchStore.ExactRetrievalStrategy()) custom_log("info", "carrier_appointments_master_list_personal indexing completed") db = ElasticsearchStore.from_documents( documents=Wholesalers_documents, es_connection=es, index_name='carrier_appointments_master_list_wholesalers', embedding=embeddings, strategy=ElasticsearchStore.ExactRetrievalStrategy()) custom_log("info", "carrier_appointments_master_list_wholesalers indexing completed") db = ElasticsearchStore.from_documents( documents=CAAIS_documents, es_connection=es, index_name='carrier_appointments_master_list_caais', embedding=embeddings, strategy=ElasticsearchStore.ExactRetrievalStrategy()) custom_log("info", "carrier_appointments_master_list_caais indexing completed") except Exception as e: custom_log("error in exception:", e)