import pyodbc import datetime import re from datetime import datetime COUNTRY_DATA = { 'MX': 'Mexico', 'NL': 'Netherlands', 'BE': 'Belgium', 'AE': 'UAE', 'FR': 'France', 'OM': 'Oman', 'ES': 'Spain', 'GB': 'United Kingdom', 'LT': 'Lithuania', 'DE': 'Germany', 'IT': 'Italy', 'GR': 'Greece', 'AZ': 'Azerbaijan', 'TR': 'Turkey', 'DK': 'Denmark', 'US': 'United States', 'TH': 'Thailand', 'BR': 'Brazil', 'CH': 'Switzerland', 'HU': 'Hungary', 'RS': 'Serbia', 'AU': 'Australia', 'RO': 'Romania', 'EG': 'Egypt', 'ZA': 'South Africa', 'PT': 'Portugal', 'UA': 'Ukraine', 'CZ': 'Czech Republic', 'IE': 'Ireland', 'XI': 'Northern Ireland', 'PL': 'Poland', 'CY': 'Cyprus', 'SE': 'Sweden', 'RU': 'Russia', 'NO': 'Norway', 'CA': 'Canada', 'QA': 'Qatar', 'TN': 'Tunisia', 'LV': 'Latvia', 'AT': 'Austria', 'LU': 'Luxembourg', 'FI': 'Finland', 'SK': 'Slovakia', 'AR': 'Argentina', 'BG': 'Bulgaria', 'CL': 'Chile', 'SG': 'Singapore', 'SZ': 'Swaziland', 'EE': 'Estonia', 'SI': 'Slovenia', 'NZ': 'New Zealand' } # log_file = "/customer_service/scripts\error_logs.txt" # def log_activity(message): # with open(log_file, "a") as f: # timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S") # f.write(f"[{timestamp}] {message}\n") def extract_article_code(text): match = re.match(r'^(\d+)', text.strip()) if match: return int(match.group(1)) return None def connect_sql_server_live(): # Connection configuration server = 'SRVPSQL01' database = 'pvsmaster_gent' username = 'parvathy' password = 'Tyc@n123!' driver = '{ODBC Driver 17 for SQL Server}' conn = pyodbc.connect( f'DRIVER={driver};' f'SERVER={server};' f'DATABASE={database};' f'UID={username};' f'PWD={password}' ) cursor = conn.cursor() return cursor, conn def get_or_create_article_relation(cursor, article_code, ship_to, invoice_to, ordered_by): """Function to create if address relationship if not exists""" # First, check if the combination exists check_query = """ SELECT 1 FROM AddressCodeRelationship WHERE ArticleCode = ? AND ShipToCode = ? AND InvoiceToCode = ? AND OrderedByCode = ? """ cursor.execute(check_query, (article_code, ship_to, invoice_to, ordered_by)) exists = cursor.fetchone() if not exists: # If it does not exist, insert it insert_query = """ INSERT INTO AddressCodeRelationship (ArticleCode, ShipToCode, InvoiceToCode, OrderedByCode) VALUES (?, ?, ?, ?) """ cursor.execute(insert_query, (article_code, ship_to, invoice_to, ordered_by)) # Call the function since this is a new insert success_msg = f"{ article_code, ship_to, invoice_to, ordered_by } records inserted successfully." # log_activity(success_msg) def add_address_relation(cursor, debnr): """function to add address code relationship (combination of ShipToCode, ArticleCode, InvoiceToCode, OrderedByCode)""" order_query = """ SELECT t2.artcode, t1.debnr, t1.[fakdebnr] ,t1.[verzdebnr] ,t1.[einddebnr], COUNT(DISTINCT t1.ordernr) AS order_count FROM [101].[dbo].[orhkrg] t1 JOIN [101].[dbo].[orhsrg] t2 ON t1.ordernr = t2.ordernr WHERE t1.debnr = ? AND t2.regel = 1 GROUP BY t2.artcode, t1.debnr, t1.[fakdebnr] ,t1.[verzdebnr] ,t1.[einddebnr]; """ params = ( debnr ) cursor.execute(order_query, params) orders = cursor.fetchall() formated_article_code = '' for order in orders: print('=======') article_code = extract_article_code(order[0]) formated_article_code += article_code article_relation_data = { 'article_code': extract_article_code(order[0]), 'ShipToCode': int(order[1]), 'InvoiceToCode': int(order[2]), 'OrderedByCode': int(order[3]), } get_or_create_article_relation(cursor, article_relation_data['article_code'], article_relation_data['ShipToCode'], article_relation_data['InvoiceToCode'], article_relation_data['OrderedByCode']) return formated_article_code def insert_address_data(cursor, conn, addition_list): """function to insert newly added address and log them""" insert_query = """ INSERT INTO pvsmaster_gent.dbo.AddressMaster ( ExactERPAddressId, CustomerName, AddressLine1, City, PostalCode, UpdatedBy, IsDeleted, CountryCode, ContactPerson, Phone, Email, CustomerNickName, CreatedBy, CreatedDate, Country ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """ try: for row in addition_list: debnr = row['ExactERPAddressId'] formated_article_code = add_address_relation(cursor, debnr) if formated_article_code=='': tail = row['City'] else : tail = formated_article_code nickname = f"{debnr} {row['CustomerName']}-{tail}" country_code = row['CountryCode'].strip().upper() country = COUNTRY_DATA.get(country_code, None) cursor.execute(insert_query, ( debnr, row['CustomerName'], row['AddressLine1'], row['City'], row['PostalCode'], row.get('UpdatedBy', 27), row.get('IsDeleted', 0), row['CountryCode'], row['ContactPerson'], row['Phone'], row['Email'], nickname, 27, datetime.now(), country )) conn.commit() success_msg = f"{len(addition_list)} records inserted successfully." print(success_msg) # log_activity(success_msg) except Exception as e: error_msg = f"Error inserting data: {str(e)}" print(error_msg) # log_activity(error_msg) finally: cursor.close() conn.close() return True def update_address_data(cursor, conn, update_list): """function to update modified address and log them""" update_query = """ UPDATE pvsmaster_gent.dbo.AddressMaster SET CustomerName = ?, AddressLine1 = ?, City = ?, PostalCode = ?, UpdatedBy = ?, IsDeleted = ?, CountryCode = ?, ContactPerson = ?, Phone = ?, Email = ? WHERE ExactERPAddressId = ? """ try: for row in update_list: cursor.execute(update_query, ( row['CustomerName'], row['AddressLine1'], row['City'], row['PostalCode'], row.get('UpdatedBy', 27), row.get('IsDeleted', 0), row['CountryCode'], row['ContactPerson'], row['Phone'], row['Email'], row['ExactERPAddressId'] )) conn.commit() success_msg = f"address with shiptocode {row['ExactERPAddressId']} updated successfully." # print(success_msg) # log_activity(success_msg) except Exception as e: error_msg = f"Error updating data: {str(e)}" print(error_msg) # log_activity(error_msg) finally: cursor.close() conn.close() return True def get_modified_address_data(cursor): """function to get all modified data in 10 days""" select_query = """ SELECT c1.debnr, c1.cmp_name, c1.cmp_fadd1, c1.cmp_fcity, c1.cmp_fpc, c1.cmp_fctry, c2.cnt_email, c2.cnt_f_tel, c2.FullName FROM [101].dbo.cicmpy c1 JOIN [101].dbo.cicntp c2 ON c2.cnt_id = c1.cnt_id WHERE c1.debnr IS NOT NULL AND c1.sysmodified >= CAST(DATEADD(DAY, -10, CAST(GETDATE() AS DATE)) AS DATETIME) """ params = ( ) cursor.execute(select_query, params) address = cursor.fetchall() return address def sync_address_data_table(): cursor, conn = connect_sql_server_live() address = get_modified_address_data(cursor) addition_list = [] update_list = [] for instance in address: address_dict = { 'ExactERPAddressId': instance[0], 'CustomerName': instance[1], 'AddressLine1': instance[2], 'City': instance[3], 'PostalCode': instance[4], 'UpdatedBy': 27, 'IsDeleted': 0, 'CountryCode': instance[5], 'Email': instance[6], 'Phone': instance[7], 'ContactPerson': instance[8], } dbnr = instance[0] select_query = """ SELECT * FROM pvsmaster_gent.dbo.AddressMaster WHERE ExactERPAddressId = ? """ params = ( dbnr ) cursor.execute(select_query, params) address = cursor.fetchone() if address: update_list.append(address_dict) else: addition_list.append(address_dict) print("for updation", len(update_list)) for instance in update_list: print(instance) print("for addition", len(addition_list)) for instance in addition_list: print(instance) # update_address_data(cursor, conn, update_list) insert_address_data(cursor, conn, addition_list) # sync_address_data_table()