from charset_normalizer.constant import FREQUENCIES from delivery_app.models import TransportPriceMaster, TransportMaster from collections import Counter import unicodedata import re from datetime import datetime from decimal import Decimal def extract_sheet_id_from_url(url): """ Extracts the sheet ID from a Google Sheets URL. Args: url (str): The full Google Sheets URL. Returns: str: The extracted sheet ID, or None if not found. """ match = re.search(r'/d/([a-zA-Z0-9-_]+)', url) if match: return match.group(1) return None def remove_diacritics(text): return ''.join( c for c in unicodedata.normalize('NFKD', text) if not unicodedata.combining(c) ) def parse_percentage(value): if isinstance(value, int): return float(value) if isinstance(value, str): clean = value.replace('%', '').strip() if clean.isdigit(): return float(clean) return None def parse_price_to_float(value): if isinstance(value, (int, float)): return float(value) if isinstance(value, str): clean = value.replace('€', '').replace('-', '').replace(',', '').strip() if not clean: return None try: return float(clean) except ValueError: return None else: return None def destinatin_keys(): return None get_chemical_name = { "Sulphuric acid": "H2SO4", "Suphuric acid": "H2SO4", "Sulphuric acid uhp": "H2SO4", "Natrium bisulfiet": "NaHSO3", "Oleum": "H2S2O7", "Diluted sulphuric acid": "H2SO4", "Sodium bisulphite": "NaHSO3", "Ammonium bisulfite": "(NH4)HSO3", "Sulfuric acid cp pa eg": "H2SO4", "Chlorosulphonic acid": "HSO3Cl", "Sulphuric acid eg": "H2SO4", } import gspread from google.oauth2.service_account import Credentials def check_and_create_transport_price(transport_data, data_not_found, changed_price, add_data, base_price, actual_price, transporter_id, transporter_name, grade): concentration = transport_data['concentration'] concentration = float(concentration) if isinstance(concentration, (int, float)) else concentration con_from = float(transport_data['concentration_from']) if isinstance(transport_data['concentration_from'], (int, float)) else transport_data['concentration_from'] con_to = float(transport_data['concentration_to']) if isinstance(transport_data['concentration_to'], (int, float)) else transport_data['concentration_to'] print('data', transport_data) transport_objs = TransportPriceMaster.objects.filter( TransporterId=transporter_id, DestinationCity=transport_data['destination'], DilutionFrom=con_from, DilutionTo=con_to, ChemicalName=transport_data['chemical_name'], OriginCity=transport_data['loading'], Grade=grade, Frequency=transport_data['frequency'], RouteVia=transport_data['route'], Concentration=concentration, IsDeleted=0 ) if transport_objs.exists(): transport_obj = transport_objs.latest("Id") transport_obj.Concentration = concentration print('===========', transport_obj.Concentration) transport_obj.DestinationZIP = transport_data['zip_code'] transport_obj.save() # transport_obj.Frequency = transport_data['frequency'] # transport_obj.save() if float(transport_obj.EffectivePrice) != actual_price: changed_price_dict = { 'position': transport_data['position'], 'DestinationCity': transport_data['destination'], 'Dilution': transport_data['concentration'], 'DilutionFrom ': con_from, 'DilutionTo': con_to, 'ChemicalName': transport_data['chemical_name'], 'transporter_id': transport_data['transporter_id'], 'transporter': transporter_name, 'db_price': transport_obj.EffectivePrice, 'sheet_price': actual_price, 'grade': grade, 'frequency': transport_data['frequency'] } changed_price.append(changed_price_dict) if add_data: transport_obj.EffectivePrice = float(actual_price) transport_obj.BasePrice = float(base_price) transport_obj.UpdatedOn = datetime.now(), # print('====', transport_data['concentration'], type(transport_data['concentration'])) # transport_obj.Concentration = concentration transport_obj.save() else: ddt = { 'position': transport_data['position'], 'DestinationCity': transport_data['destination'], 'Dilution': transport_data['concentration'], 'DilutionFrom ': con_from, 'DilutionTo': con_to, 'ChemicalName': transport_data['chemical_name'], 'product': transport_data['product'], 'zip_code': transport_data['zip_code'], 'transporter_id': transport_data['transporter_id'], 'transporter': transporter_name, 'grade': grade, 'frequency': transport_data['frequency'], } try: transporter = TransportMaster.objects.get(ID=transporter_id) except TransportMaster.DoesNotExist: raise Exception(f"Transporter {transporter_id} does not exist") data_not_found.append(ddt) if add_data: try: transporter = TransportMaster.objects.get(ID=transporter_id) except TransportMaster.DoesNotExist: raise Exception(f"Transporter {transporter_id} does not exist") transport_price = TransportPriceMaster.objects.create( TransporterId=transporter, OriginCity=transport_data['loading'], DestinationCity=transport_data['destination'], DilutionFrom=con_from, DilutionTo=con_to, Concentration=concentration, ChemicalName=transport_data['chemical_name'], EffectivePrice=actual_price, BasePrice=base_price, DestinationZIP=transport_data['zip_code'], Grade=grade, IsDeleted=0, CreatedBy=27, Frequency=transport_data['frequency'], RouteVia=transport_data['route'], Packaging='Bulk' ) def make_unique_headers(headers): """make unique headers since excel heading contains heading with same name""" counts = Counter() result = [] for h in headers: counts[h] += 1 if counts[h] > 1: result.append(f"{h}_{counts[h]}") else: result.append(h) return result def get_concentration(product, concentration): """Function to get the concentration range for a given product.""" concentration_dict = { 'Diluted Sulphuric Acid': [35, 40], 'Sulphuric Acid': [35, 40], 'Sodium Bisulphite': [38, 40], 'Natrium Bisulfiet': [38, 40], 'NATRIUM BISULFIET': [38, 40], } print(product, concentration) conc_data = concentration_dict.get(product) if product and isinstance(concentration, float) and 35 <= concentration <= 40 and conc_data: con_from = conc_data[0] con_to = conc_data[1] else: con_from = concentration con_to = concentration return con_from, con_to def get_city(city): """ Extracts the base city name and route information from a given city string. Some city names include additional route or transport details, such as "Londonderry (via ANTW)" or "Darmstadt (with PVSU container on chassis Vervaeke)". This function standardizes such names by returning the clean city name and any route information separately. Args: city (str): The original city name, possibly containing route or transport info. Returns: tuple: A pair (city_name, route), where: - city_name (str): The cleaned city name. - route (str): The route or transport note (e.g., "via ANTW"), or an empty string if none. """ city_name = { 'Londonderry (via ANTW)': ('Londonderry', 'via ANTW'), 'Londonderry (via ZEEBR)': ('Londonderry', 'via ZEEBR'), "Londonderry (via R'dam)": ('Londonderry', "via R'dam"), "Derbyshire (Glossop)": ('Glossop', ''), "Darmstadt (with PVSU container on chassis Vervaeke)": ('Darmstadt', ''), "Val de Reuil (with PVSU container on chassis Vervaeke)": ('Val de Reuil' , ''), } new_city = city_name.get(city) if new_city: new_city, route = new_city[0], new_city[1] else: new_city, route = city, '' return new_city, route def sync_transport_price(add_data, skip): # Define the scopes SCOPES = ['https://www.googleapis.com/auth/spreadsheets'] sheet_id = '1dhLOdCuTE7HRvq-Rlp9P83gZbfcbyfSg666DioHkkVA' SERVICE_ACCOUNT_FILE = 'D:\diffrenz\order_pro\order_pro\pvs_gent_crm\purchase_order_service\media\credentials\service_account_creds.json' # SERVICE_ACCOUNT_FILE = 'C:\Apache24\service_account_creds.json' # Authenticate creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES) client = gspread.authorize(creds) # Open the sheet by ID # sheet link spreadsheet_id = '1dhLOdCuTE7HRvq-Rlp9P83gZbfcbyfSg666DioHkkVA' sheet = client.open_by_key(spreadsheet_id).worksheet("Transport Data") # Use sheet1 or .worksheet('Sheet1') # sheet = client.open_by_key(spreadsheet_id).worksheet("transporter") print(sheet.title) # Get all records (excluding header) # data = sheet.get_all_records(head=3) rows = sheet.get_all_values() headers = rows[3] data_rows = rows[3:] # Original header list (with duplicates) raw_headers = [ "Position", "Country", "Loading", "Destination", "ZIP code Destination", "Product", "Concentration", "Frequency", "Vervaeke", "Van der Lee", "Fockedey", "Mervielde", "Van Haeke", "Geodis", "Schenk", "Katoennatie", "Rinnen", "DB Cargo", "Withofs", "", "Vervaeke", "Van der Lee", "Fockedey", "Mervielde", "Van Haeke", "Geodis", "Schenk", "Katoennatie", "Rinnen", "DB Cargo", "Withofs" ] # Generate unique headers unique_headers = make_unique_headers(raw_headers) # print(unique_headers) sheet_datas = [dict(zip(unique_headers, row)) for row in data_rows] data_not_found = [] changed_price = [] skip_list = [] for data in sheet_datas: if not data['Position'] or not data['Country'] or not data['Destination']: continue if int(data['Position']) in skip: continue print(data['Position'], data['Destination']) # try: product = data['Product'].strip() transport_data = { 'position': data['Position'], 'country': data['Country'], 'loading': data['Loading'].strip(), 'destination': get_city(data['Destination'].strip())[0], 'route': get_city(data['Destination'].strip())[1], 'zip_code': data['ZIP code Destination'], 'product': data['Product'], 'chemical_name': get_chemical_name.get(product.capitalize()), 'concentration': parse_percentage(data['Concentration']), 'frequency': data['Frequency'] if data['Frequency']!="" else None, 'vervaeke_act': parse_price_to_float(data['Vervaeke']), 'van_der_lee_act': parse_price_to_float(data['Van der Lee']), 'fockedey_act': parse_price_to_float(data['Fockedey']), 'mervielde_act': parse_price_to_float(data['Mervielde']), 'van_haeke_act': parse_price_to_float(data['Van Haeke']), 'geodis_act': parse_price_to_float(data['Geodis']), 'schenk_act': parse_price_to_float(data['Schenk']), 'katoennatie_act': parse_price_to_float(data['Katoennatie']), 'rinnen_act': parse_price_to_float(data['Rinnen']), 'db_cargo_act': parse_price_to_float(data['DB Cargo']), 'withofs_act': parse_price_to_float(data['Withofs']), 'vervaeke': parse_price_to_float(data['Vervaeke_2']), 'van_der_lee': parse_price_to_float(data['Van der Lee_2']), 'fockedey': parse_price_to_float(data['Fockedey_2']), 'mervielde': parse_price_to_float(data['Mervielde_2']), 'van_haeke': parse_price_to_float(data['Van Haeke_2']), 'geodis': parse_price_to_float(data['Geodis_2']), 'schenk': parse_price_to_float(data['Schenk_2']), 'katoennatie': parse_price_to_float(data['Katoennatie_2']), 'rinnen': parse_price_to_float(data['Rinnen_2']), 'db_cargo': parse_price_to_float(data['DB Cargo_2']), 'withofs': parse_price_to_float(data['Withofs_2']), } con_from, con_to = get_concentration(transport_data['product'], transport_data['concentration']) transport_data['concentration_from'] = con_from transport_data['concentration_to'] = con_to if transport_data['loading'] == 'PVS Gent': transport_data['loading'] = "Gent" if 'uhp' in product.capitalize() : transport_data['grade'] = "Ultra High Pure" elif 'eg' in product.capitalize(): transport_data['grade'] = "Electronic Grade" else: transport_data['grade'] = "" # except Exception as e: # print(str(e)) # skip_list.append(data['Position']) transporter_tuple = [ ('vervaeke', 'vervaeke_act', 2), ('van_der_lee', 'van_der_lee_act', 3), ('fockedey', 'fockedey_act', 5), ('mervielde', 'mervielde_act', 1), # transporter is assigned as van heesvelde for mervilde as it is inactive ('van_haeke', 'van_haeke_act', 6), ('geodis', 'geodis_act', 4), ('schenk', 'schenk_act', 13), ('katoennatie', 'katoennatie_act', 14), ('rinnen', 'rinnen_act', 7), ('db_cargo', 'db_cargo_act', 9), ('withofs', 'withofs_act', 12), ] for item in transporter_tuple: base_price = transport_data[item[0]] actual_price = transport_data[item[1]] transporter_id = item[2] transporter_name = item[1] transport_data['transporter_id'] = transporter_id if not actual_price: continue # since we dont save unconfirmed transport prices if "cp pa eg" in product.capitalize(): grade_list = ['Chemically Pure', 'Pro Analysis', 'Electronic Grade'] for grade in grade_list: check_and_create_transport_price(transport_data, data_not_found, changed_price, add_data, base_price, actual_price, transporter_id, transporter_name, grade) else: grade = transport_data['grade'] check_and_create_transport_price(transport_data, data_not_found, changed_price, add_data, base_price, actual_price, transporter_id, transporter_name, grade) # print("==============================================") # print("data not found", len(data_not_found)) # for instance in data_not_found: # print(instance) # print("==============================================") # # print("data updation needed", len(changed_price)) # for instance in changed_price: # print(instance) return data_not_found, changed_price, skip_list # sync_transport_price(False)