import pyodbc import datetime import re from datetime import datetime from django.core.mail import EmailMessage import os 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 = "D:\diffrenz\order_pro\order_pro\pvs_gent_crm\customer_service\customer_app\scripts\AddressLog.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 = 'SRVPSQL02' 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].[orkrg] t1 JOIN [101].[dbo].[orsrg] 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.append(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']) print(formated_article_code) return list(set(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, AddressLine2, AddressLine3, City, PostalCode, UpdatedBy, IsDeleted, CountryCode, ContactPerson, Phone, Email, CustomerNickName, CreatedBy, CreatedDate, Country, ArticleCode, CityMapping, ParentShipTo ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """ try: for row in addition_list: debnr = row['ExactERPAddressId'] formated_article_code = add_address_relation(cursor, debnr)[0:2] formated_article_code = ", ".join(map(str, formated_article_code)) if formated_article_code == '' or not formated_article_code: tail = row['City'] else: tail = f" ({formated_article_code})" print('formated_article_code1212', tail) 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['AddressLine2'], row['AddressLine3'], 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, formated_article_code, row['City'], debnr )) conn.commit() success_msg = f"{len(addition_list)} records inserted successfully." # log_activity(success_msg) except Exception as e: error_msg = f"Error inserting data: {str(e)}" print(error_msg) log_activity(error_msg) 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 = ?, UpdatedDate = ?, IsDeleted = ?, CountryCode = ?, ContactPerson = ?, Phone = ?, Email = ?, AddressLine2 = ?, AddressLine3 = ? WHERE ExactERPAddressId = ? """ try: for row in update_list: cursor.execute(update_query, ( row['CustomerName'], row['AddressLine1'], row['City'], row['PostalCode'], row.get('UpdatedBy', 27), datetime.now(), row.get('IsDeleted', 0), row['CountryCode'], row['ContactPerson'], row['Phone'], row['Email'], row['AddressLine2'], row['AddressLine3'], 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) 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, c1.cmp_fadd2, c1.cmp_fadd3 # 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, -5, CAST(GETDATE() AS DATE)) AS DATETIME) # """ 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, c1.cmp_fadd2, c1.cmp_fadd3 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.debnr in (1772, 1604, 1976, 1571, 1571, 912, 437, 2271, 2248, 2083, 2326, 2326, 2209, 2122, 2083, 2083, 45, 1530) """ 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 = [] db_address = [] 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], 'AddressLine2': instance[9], 'AddressLine3': instance[10], } dbnr = instance[0] if dbnr == "000001": print("skiped dbnr with 000001") continue select_query = """ SELECT ExactERPAddressId, CustomerName, AddressLine1, City,PostalCode, CountryCode, AddressLine2, AddressLine3 FROM pvsmaster_gent.dbo.AddressMaster WHERE ExactERPAddressId = ? """ params = ( dbnr ) cursor.execute(select_query, params) address = cursor.fetchone() if address: update_list.append(address_dict) db_address.append({'ExactERPAddressId':address[0] , 'CustomerName': address[1], 'AddressLine1':address[2], 'City':address[3],'PostalCode':address[4], 'CountryCode':address[5], 'AddressLine2':address[6], 'AddressLine3':address[7]}) else: addition_list.append(address_dict) print("for updation", len(update_list)) for instance, db_data in zip(update_list, db_address): print(instance) print(db_data) print("for addition", len(addition_list)) for instance in addition_list: print(instance) # Send email with address comparison # send_address_comparison_email(addition_list, update_list, db_address) # Update the database after sending the email insert_address_data(cursor, conn, addition_list) update_address_data(cursor, conn, update_list) return addition_list, update_list # sync_address_data_table()