import io import os import math import requests from django.db import connections, connection from django.db.models import Q from datetime import datetime from rest_framework.response import Response from rest_framework import status from django.db.models import Count from datetime import date from decimal import Decimal from datetime import datetime from rest_framework.exceptions import ValidationError, NotFound import xml.etree.ElementTree as ET import zipfile from . import fetch_data from . import serializers as purchase_serializer from . import models as purchase_models from . import constants as purchase_consts from . serializers import convert_to_belgium_format from django.forms.models import model_to_dict from .services.erp_client import OdooAPIService def api_response(status_code, status, message, data=None): return Response({ "status_code": status_code, "status": status, "message": message, "data": data }, status=status_code) CUSTOMER_SERVICE_URL = "http://localhost:8001/api/customers/" PRODUCT_SERVICE_URL = "http://localhost:8002/api/products/" def get_customer_details(customer_id): try: response = requests.get(f"{CUSTOMER_SERVICE_URL}{customer_id}/") if response.status_code == 200: return response.json() return None except requests.exceptions.RequestException as e: print(f"Error fetching customer details: {e}") return None def get_product_details(product_id): try: response = requests.get(f"{PRODUCT_SERVICE_URL}{product_id}/") if response.status_code == 200: return response.json() return None except requests.exceptions.RequestException as e: print(f"Error fetching product details: {e}") return None def get_content_data_for_transporter(order_details, request): """Function to get content to send mail for transporter""" transport_mail_serializer = purchase_serializer.TransportMailSerializer(order_details, context={'request': request}) transport_data = transport_mail_serializer.data incoterm_list = ['FCA', 'EXW'] if transport_data['IncoTerm'] not in incoterm_list: # Format dates if (transport_data['LoadPoint'] == 'PVS'): subject = f"Transport {transport_data['CustomerName']} - {transport_data['ShipToCity']} {transport_data['FormattedProductName']} " else: subject = f"Transport {transport_data['CustomerName']} - {transport_data['ShipToCity']} {transport_data['FormattedProductName']} {transport_data['LoadPoint']} " if transport_data['DriverLanguages'] != '': remarks = f"Please note that each driver must be able to understand and speak {transport_data['DriverLanguages']}" else: remarks = '' if transport_data['IsADR'] == 'ADR': is_adr = 1 productname_with_uncode = transport_data['ProductNameDutch'] else: is_adr = 0 productname_with_uncode = '' # cc_emails = ['transportopdrachten@pvs.be', 'sales@pvs.be'] default_cc = os.getenv('TRANSPORTER_DEFAULT_CC_EMAILS') cc_emails = default_cc.split(',') second_email = transport_data['PortalUserMailId'] if second_email: cc_emails.append(second_email) cc_emails = list(set(cc_emails)) # record = OrderFiles.objects.filter( # OrderId=order_id, # Type='UnloadingInstruction', # IsDeleted=False # ).values('FileName').first() # if record: # existing_pdf = settings.PO_API_URL+"serve_doc/"+record['FileName'] # unload_data = '' # else: # unload_data = UnloadInstructions.objects.filter(ArticleCode=transportmail_serializer.data['ArticleCode'],ExactERPAddressId=transportmail_serializer.data['ShipToCode'],IncoTerm=transportmail_serializer.data['IncoTerm'],LoadPointId=transportmail_serializer.data['LoadPointId'],IsDeleted=0).values_list('Instructions', 'Color') # existing_pdf = '' unload_data = purchase_models.UnloadInstructions.objects.filter( ArticleCode=transport_data['ArticleCode'], ExactERPAddressId=transport_data['ShipToCode'], IncoTerm=transport_data['IncoTerm'], LoadPointId=transport_data['LoadPointId'], IsDeleted=0 ).values_list('Instructions', 'Color', 'IsImageReq') formatted_unload_data = [{"text": instruction, "color": color, "is_image_req": is_image_req} for instruction, color, is_image_req in unload_data] else: raise ValidationError({ "status_code": 400, "status": "error", "message": f"Transporter Mail is not generated since Incoterm is {transport_data['IncoTerm']}", "data": None }) response_data = { 'to': transport_data['TransportContactHistory']['to_mails'] if transport_data[ 'TransportContactHistory'] else [], 'cc': cc_emails, 'subject': subject, 'client_name': transport_data['CustomerName'], 'client_address': transport_data['CustomerAddress'], 'loading_at': transport_data['LoadPoint'] + ' - ' + transport_data[ 'LoadPointCity'], 'additional_remarks': remarks, 'triggered_by': transport_data['PortalUserName'], 'is_adr': is_adr, 'productname_with_uncode': productname_with_uncode, 'product': transport_data['FormattedProductName'], 'delivery_city': transport_data['ShipToCity'], 'transporter_id': transport_data['TransporterId'], 'loadpoint_address_line1': transport_data['LoadPointAddressLine1'], 'loadpoint_address_line2': transport_data['LoadPointAddressLine2'], 'instructions': formatted_unload_data, 'article_code': transport_data['ArticleCode'], 'packaging': transport_data['Packaging'], 'packaging_info': get_PackagingInfo(order_details.OrderNo), 'loadpoint_id': transport_data['LoadPointId'], 'ship_to_code': transport_data['ShipToCode'], 'incoterm': transport_data['IncoTerm'], 'container_no': transport_data['ContainerNo'], 'is_container_required': transport_data['IsContainerRequired'], 'is_delivery_date_required': transport_data['IsDeliveryDateRequired'], 'need_station_reference': False if transport_data['LoadingStationReference']==None else True, 'need_unload_reference': transport_data['NeedUnLoadReference'], 'default_attachments': transport_data['DefaultAttachments'], 'bill_to_code': transport_data['BillToCode'], } return response_data def validate_orders(orders): """function checks weather order share same transporter, product, customers""" grouped = orders.values( # 'TransporterId', 'OrderedCustomerId', 'LoadPoint', 'ProductId', 'DeliveryCustomerId', 'IncoTerm' ).annotate(count=Count('Id')) if not grouped.count() == 1: raise ValidationError({ "status_code": 400, "status": "error", "message": f"Order Should be similar", "data": None }) else: return True def get_customer_contact_data(shipto, product_article, recipient_type, bill_to_code=None): filter_kwargs = { 'ExactERPAddressId': shipto, 'IsDeleted': 0, 'Type': recipient_type, 'product_article': product_article, } if bill_to_code is not None: filter_kwargs['BillTo'] = bill_to_code to_emails = purchase_models.CustomerContacts.objects.filter(**filter_kwargs) if not to_emails: shipto_mapping = purchase_models.ShiptoMapping.objects.filter( parent_shipto_code=shipto, product_article=product_article).last() if shipto_mapping: old_ship_to = shipto_mapping.shipto_code if old_ship_to: filter_kwargs_parent = { 'ExactERPAddressId': old_ship_to, 'IsDeleted': 0, 'Type': recipient_type, 'product_article': product_article, } if bill_to_code is not None: filter_kwargs_parent['BillTo'] = bill_to_code to_emails = purchase_models.CustomerContacts.objects.filter(**filter_kwargs_parent) if not to_emails.exists(): to_emails = purchase_models.CustomerContacts.objects.filter( ExactERPAddressId=shipto, IsDeleted=0, Type=recipient_type, product_article=product_article) email_ids = to_emails.values_list('Email', flat=True) return email_ids def get_content_data_for_customer(order_data, request): """Function to get content to send mail for customer""" customer_data = order_data[0] # Return data as JSON instead of generating a PDF response_data = { 'date': datetime.today().strftime('%d/%m/%Y'), 'customer_exact_id': customer_data['customer_exact_id'], 'client_name': customer_data['client_name'], 'client_address_line1': customer_data['client_address_line1'], 'client_address_line2': customer_data['client_address_line2'], 'client_address_line3': customer_data['client_address_line3'], 'is_coc_needed': 1, 'payment_term': customer_data['PaymentTerm'], 'customer_id': customer_data['OrderedCustomerId'], 'load_point': customer_data['LoadPoint'], 'load_point_address_line1': customer_data['load_point_address_line1'], 'load_point_address_line2': customer_data['load_point_address_line2'], 'need_customer_mail': customer_data['NeedCustomerMail'], 'default_attachments': customer_data['DefaultAttachments'], } if customer_data['IncoTerm'] == 'FCA' or customer_data['IncoTerm'] == 'EXW': delivery_condition = {customer_data['IncoTerm']} else: delivery_condition = f"{customer_data['IncoTerm']} {customer_data['CustomerCity']}" product_article = customer_data['ArticleCode'] bill_to_id = customer_data.get('BillToCustomerId') bill_to_code = None if bill_to_id: bill_to_address_data = fetch_data.get_address_data(bill_to_id) if bill_to_address_data: bill_to_code = bill_to_address_data.get('exact_erp_address_id') to_emails = get_customer_contact_data(customer_data['customer_exact_id'], product_article, 'to', bill_to_code=bill_to_code) cc_emails = get_customer_contact_data(customer_data['customer_exact_id'], product_article, 'cc', bill_to_code=bill_to_code) # if os.environ.get('DJANGO_ENV') == 'staging': # response_data['to'] = ["thowfeek@tycaninc.com", "ashmi.ma@tycaninc.com", # "ashikfortest@gmail.com"] # response_data['cc'] = ['sales@pvs.be'] # else: response_data['to'] = list(to_emails) response_data['cc'] = ['sales@pvs.be'] if request.login_user_email and request.login_user_email not in response_data['cc']: response_data['cc'].append(request.login_user_email) response_data['cc'] = list(set(response_data['cc'])) response_data['to'] = list(set(response_data['to'])) response_data['delivery_condition'] = delivery_condition return response_data def get_xml_content_extra_charges(order_id, order_elem, order_request_serializer, payment_details): """Function to add extra charges in xml""" order_charges = purchase_models.OrderCharges.objects.filter(OrderNo=order_id, IsDeleted=0) line_no = 2 # Line number starts from 2 - for index, order_charge in enumerate(order_charges): line_no = index + 2 # Line number starts from 2 - order_line = ET.SubElement(order_elem, "OrderLine", lineNo=str(line_no)) ET.SubElement(order_line, "Description").text = str(order_charge.ChargeName) if order_charge.ChargeName else "" # ET.SubElement(order_line, "ItemCode").text = str(order_charge.ArticleCode) if order_charge.ArticleCode else "" ET.SubElement(order_line, "Item", code=(str(order_charge.ArticleCode) if order_charge.ArticleCode else "")) # ET.SubElement(order_line, "Item", code=(str(order_charge.ArticleCode) if order_charge.ArticleCode else "")) ET.SubElement(order_line, "Quantity").text = str(1) unit = ET.SubElement(order_line, "Unit", unit="TO") ET.SubElement(unit, "MultiDescriptions") # Empty tag price = ET.SubElement(order_line, "Price", type="S") ET.SubElement(price, "Currency", code=str(payment_details[0])) ET.SubElement(price, "Value").text = str(order_charge.Rate) if order_charge.Rate else "" ET.SubElement(price, "VAT", code=str(payment_details[2])) amount = ET.SubElement(order_line, "Amount", type="S") ET.SubElement(amount, "Currency", code=str(payment_details[0])) ET.SubElement(amount, "Value").text = "" discount = ET.SubElement(order_line, "Discount") ET.SubElement(discount, "Percentage").text = "0" delivery = ET.SubElement(order_line, "Delivery") ET.SubElement(delivery, "Date").text = (order_request_serializer.data['LoadingDate'].replace('-', '/')) ET.SubElement(order_line, "Warehouse", code=str(order_request_serializer.data['WareHouseCode'])) ET.SubElement(order_line, "Costcenter", code=order_request_serializer.data['CostCenter']) return line_no def generate_xml(order_id, order_details, request): """function to generate xml for order which needed to send to exact""" zip_buffer = io.BytesIO() with zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file: try: if order_details: order_request_serializer = purchase_serializer.OrderXMLSerializerNew( order_details, context={'request': request}) if order_request_serializer is not None: root = ET.Element("eExact", xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance", xsi_noNamespaceSchemaLocation="eExact-Schema.xsd") orders = ET.SubElement(root, "Orders") # Order element order_elem = ET.SubElement(orders, "Order", type="V", number=str(order_request_serializer.data['ExactOrderNo']), code=str(order_request_serializer.data['InvoiceCode'])) # Basic order data ET.SubElement(order_elem, "Description").text = "" ET.SubElement(order_elem, "Reference1").text = "" ET.SubElement(order_elem, "Reference2").text = "" ET.SubElement(order_elem, "Reference3").text = "" order_request = purchase_models.OrderRequests.objects.filter(Id=order_id).first() purchase_order_no = order_request.PurchaseOrderNo if order_request else None if purchase_order_no: ET.SubElement(order_elem, "YourRef").text = purchase_order_no if os.environ.get('DJANGO_ENV') == 'staging': payment_details = ('EUR', '04', '5', 'CIP') else: with connections['secondary'].cursor() as cursor: query = f"SELECT currency,paymentcondition,vatcode,deliverymethod FROM cicmpy WHERE TRIM(debnr) = {order_request_serializer.data['OrderedByCode']}" cursor.execute(query) payment_details = cursor.fetchone() if payment_details is None: payment_details = ('EUR', '', '', '') else: payment_details = tuple( item.strip() if isinstance(item, str) else item for item in payment_details) ET.SubElement(order_elem, "Currency", code=str(payment_details[0])) ET.SubElement(order_elem, "CalcIncludeVAT") ET.SubElement(order_elem, "Resource", number=str(order_request_serializer.data['ResourceCode'])) # OrderedBy section ordered_by = ET.SubElement(order_elem, "OrderedBy") ET.SubElement(ordered_by, "Debtor", code=str(order_request_serializer.data['OrderedByCode'])) ET.SubElement(ordered_by, "Date").text = date.today().strftime('%Y-%m-%d') # DeliverTo section deliver_to = ET.SubElement(order_elem, "DeliverTo") ET.SubElement(deliver_to, "Debtor", code=str(order_request_serializer.data['ShipToCode'])) ET.SubElement(deliver_to, "Date").text = order_request_serializer.data['LoadingDate'] # InvoiceTo ET.SubElement(order_elem, "InvoiceTo").append( ET.Element("Debtor", code=str(order_request_serializer.data['InvoiceToCode']))) ET.SubElement(order_elem, "Warehouse", code=str(order_request_serializer.data['WareHouseCode'])) ET.SubElement(order_elem, "PaymentMethod", code="B") ET.SubElement(order_elem, "PaymentCondition", code=str(payment_details[1])) ET.SubElement(order_elem, "DeliveryMethod", code=order_request_serializer.data['IncoTerm']) ET.SubElement(order_elem, "Costcenter", code=order_request_serializer.data['CostCenter']) ET.SubElement(order_elem, "Selection", code=str(order_request_serializer.data['SelectionCode'])) ET.SubElement(order_elem, "Freight") # FreeFields free_fields = ET.SubElement(order_elem, "FreeFields") free_texts = ET.SubElement(free_fields, "FreeTexts") ET.SubElement(free_texts, "FreeText", number="1").text = "" ET.SubElement(free_texts, "FreeText", number="2").text = "" ET.SubElement(free_texts, "FreeText", number="3").text = "" free_numbers = ET.SubElement(free_fields, "FreeNumbers") # if order_request_serializer.data['IncoTerm'] == "DDP": ET.SubElement(free_numbers, "FreeNumber", number="4").text = str( order_request_serializer.data['TransportPrice']) # else: # ET.SubElement(free_numbers, "FreeNumber", number="4").text = "" ET.SubElement(free_numbers, "FreeNumber", number="5").text = "" ET.SubElement(order_elem, "ApplyShippingCharges").text = "0" # OrderLine if os.environ.get('DJANGO_ENV') == 'staging': product_details = ('TEST PROUDCT DESCRIPTION', 'TEST PRODUCT') else: article_code = order_request_serializer.data['ArticleCode'] packaging = order_details.Packaging.upper() with connections['secondary'].cursor() as cursor: query = f"""SELECT description, itemcode FROM items WHERE itemcode LIKE %s and Class_08=%s """ params = [f"%{article_code}%", packaging] cursor.execute(query, params) product_details = cursor.fetchone() order_line = ET.SubElement(order_elem, "OrderLine", lineNo="1") if product_details is not None: if product_details[1] == '31112 H2SO4 T 96 % SADACI': product_code = '31112 H2SO4 T 96 % MOLYMET' product_description = product_details[0] elif product_details[1] == '31112 - H2SO4 T 96% MOLYMET': product_code = '31112 H2SO4 T 96 % MOLYMET' product_description = product_details[0] elif product_details[1] == '30104 SULF.T. 96% TM': product_code = '30104 H2SO4 TECH 94%' product_description = 'ZWAVELZUUR 94% DRUG PRECURSORS' else: product_code = product_details[1] product_description = product_details[0] ET.SubElement(order_line, "Description").text = str(product_description) ET.SubElement(order_line, "Item", code=str(product_code)) else: ET.SubElement(order_line, "Description").text = "" ET.SubElement(order_line, "Item", code="") ET.SubElement(order_line, "Quantity").text = str( order_request_serializer.data['Quantity']) unit = ET.SubElement(order_line, "Unit", unit="TO") ET.SubElement(unit, "MultiDescriptions") # Empty tag price = ET.SubElement(order_line, "Price", type="S") ET.SubElement(price, "Currency", code=str(payment_details[0])) ET.SubElement(price, "Value").text = str(order_request_serializer.data['Price']) ET.SubElement(price, "VAT", code=str(payment_details[2])) amount = ET.SubElement(order_line, "Amount", type="S") ET.SubElement(amount, "Currency", code=str(payment_details[0])) ET.SubElement(amount, "Value").text = "" discount = ET.SubElement(order_line, "Discount") ET.SubElement(discount, "Percentage").text = "0" delivery = ET.SubElement(order_line, "Delivery") ET.SubElement(delivery, "Date").text = order_request_serializer.data['LoadingDate'] ET.SubElement(order_line, "Warehouse", code=str(order_request_serializer.data['WareHouseCode'])) ET.SubElement(order_line, "Costcenter", code=order_request_serializer.data['CostCenter']) if order_request_serializer.data['IncoTerm'] == "FCA" or order_request_serializer.data[ 'IncoTerm'] == "EXW" or order_request_serializer.data['ArticleCode'] == "50000": # ET.SubElement(order_line, "Instruction",datetime.strptime(order_request_serializer.data['LoadingDate'], '%Y-%m-%d').strftime('%d/%m/%Y')+' '+order_request_serializer.data['IncoTerm']) ET.SubElement(order_line, "Instruction").text = datetime.strptime( order_request_serializer.data['LoadingDate'], '%Y-%m-%d').strftime('%d/%m/%Y') + ' ' + \ order_request_serializer.data['IncoTerm'] else: delivery_time_interval = purchase_models.OrderRequests.objects.filter(Id=order_id).values_list( 'DeliveryTimeInterval', flat=True).first() # ET.SubElement(order_line, "Instruction",datetime.strptime(order_request_serializer.data['DeliveryDate'], '%Y-%m-%d').strftime('%d/%m/%Y')+' '+delivery_time_interval) ET.SubElement(order_line, "Instruction").text = datetime.strptime( order_request_serializer.data['DeliveryDate'], '%Y-%m-%d').strftime( '%d/%m/%Y') + ' ' + delivery_time_interval line_no = get_xml_content_extra_charges(order_id, order_elem, order_request_serializer, payment_details) if order_request.Information: information_string = f"{order_request.Information} {datetime.strptime(order_request_serializer.data['LoadingDate'], '%Y-%m-%d').strftime('%d/%m/%Y')}" order_line2 = ET.SubElement(order_elem, "OrderLine", lineNo=f"{line_no + 1}") ET.SubElement(order_line2, "Text").text = information_string tree = ET.ElementTree(root) xml_buffer = io.BytesIO() tree.write(xml_buffer, encoding='utf-8', xml_declaration=True) xml_buffer.seek(0) filename = f"{order_request_serializer.data['ExactOrderNo']}.xml" return xml_buffer, filename else: raise ValidationError({ "status_code": 500, "status": "error", "message": f"No sufficient order details found", "data": None }) else: raise ValidationError({ "status_code": 500, "status": "error", "message": f"No order details found", "data": None }) except Exception as e: # Return a response with an error if there's an issue with processing a particular order raise ValidationError({ "status_code": 500, "status": "error", "message": f"Error processing order ID {order_id}: {str(e)}", "data": None }) def get_best_price(base_queryset, formatted_quantity): """function to get best price from quantity""" exact_price = base_queryset.filter( MinQtyOperator='=', MinQuantity__lte=formatted_quantity ).order_by('-MinQuantity', '-Id').first() if exact_price: return exact_price # Then try less than matches (< operator) less_than_price = base_queryset.filter( MinQtyOperator='<', MinQuantity__gte=formatted_quantity ).order_by('MinQuantity', '-Id').first() # Note: ascending order for < operator if less_than_price: return less_than_price return None def get_no_of_pallets(total_no_of_items, no_of_items_per_pallet): """ Calculate number of pallets required. """ if not no_of_items_per_pallet or no_of_items_per_pallet == 0: return None # avoid division by zero return math.ceil(total_no_of_items / no_of_items_per_pallet) def create_packaging_info(order_request, kg_per_packaging, no_of_packings, pack_tare_weight, no_of_pallet, pallet_weight): """function to create packaging information for order request""" packing, created = purchase_models.PackingDetail.objects.get_or_create(order_request=order_request) packing.kg_per_packaging=kg_per_packaging packing.no_of_packings=no_of_packings packing.pack_tare_weight = pack_tare_weight packing.no_of_pallet = no_of_pallet packing.pallet_weight = pallet_weight packing.save() return packing def save_container_details(order_request, data): """Store the sea leg and the transporter's three container appointments. Only a sea order has any of this. When the mode is anything else the row is soft deleted rather than removed, so switching a mistake back to Ship does not lose what was already scheduled. Leg 2 - be at the warehouse to load - deliberately has no date or time here: that appointment IS the order's own loading slot, and storing it twice would let the two drift apart. """ is_sea = (data.get('transport_mode') or '').lower() == 'ship' if not is_sea: purchase_models.OrderContainerDetails.objects.filter( order_request=order_request, IsDeleted=False ).update(IsDeleted=True, UpdatedAt=datetime.now(), UpdatedBy=data.get('created_by')) return None container, _ = purchase_models.OrderContainerDetails.objects.get_or_create( order_request=order_request ) container.port_of_loading = data.get('port_of_loading') container.port_of_loading_id = data.get('port_of_loading_id') container.shipping_agency = data.get('shipping_agency') container.shipping_agency_id = data.get('shipping_agency_id') container.est_ship_departure = data.get('est_ship_departure') or None # leg 1: collect the empty container at the port container.container_pickup_date = data.get('container_pickup_date') or None container.container_pickup_time = data.get('container_pickup_time') container.container_pickup_point = data.get('container_pickup_point') # leg 2: only where, the slot comes from the order itself container.container_load_location = data.get('container_load_location') # leg 3: deliver the loaded container back to the port container.container_dropoff_date = data.get('container_dropoff_date') or None container.container_dropoff_time = data.get('container_dropoff_time') container.container_dropoff_reference = data.get('container_dropoff_reference') container.IsDeleted = False container.UpdatedBy = data.get('created_by') container.UpdatedAt = datetime.now() if container.CreatedBy is None: container.CreatedBy = data.get('created_by') container.save() return container def get_package_info(order_request, product_id=None): """function to get package info data if PackingDetail instance not created """ new_product = True if not product_id else False if not product_id: product_id = order_request.ProductId new_product = False try: with connection.cursor() as cursor: cursor.execute(""" SELECT pkm.pack_tare_weight, pkm.nos_per_pallet, pkm.pallet_weight , pm.Packaging, pkm.packaged_product, pkm.dimension, pkm.kg_per_packaging, pm.FullName from PackageMaster as pkm join ProductMaster as pm on pkm.product_id=pm.Id where pkm.product_id=%s """, [ product_id ]) packing_data = cursor.fetchone() except: raise NotFound({ "status_code": 500, "status": "error", "message": f"Package details not found product_id {product_id}", "data": None }) packing = purchase_models.PackingDetail.objects.filter(order_request=order_request).first() """ if its a not a new product and packing details exists , fetch existing saved values for order request""" nos_per_pallet = packing_data[1] if packing_data else None if packing and not new_product: kg_per_packaging = packing.kg_per_packaging no_of_packings = packing.no_of_packings pack_tare_weight = packing.pack_tare_weight no_of_pallets = packing.no_of_pallet pallet_weight = packing.pallet_weight else: kg_per_packaging = order_request.kg_per_packaging no_of_packings = order_request.no_of_packings if order_request.no_of_packings else 0 pack_tare_weight = packing_data[0] if packing_data else 0 no_of_pallets = get_no_of_pallets(no_of_packings, nos_per_pallet) pallet_weight = packing_data[2] if packing_data else 0 packaging = packing_data[3].upper() if packing_data else None product = packing_data[7] if packing_data else '' dimension = packing_data[5] if packing_data else '' data = { 'kg_per_packaging': kg_per_packaging, 'no_of_packings': no_of_packings, 'pack_tare_weight': pack_tare_weight, 'nos_per_pallet': nos_per_pallet, 'no_of_pallets': no_of_pallets, 'pallet_weight': pallet_weight, 'packaging': packaging, 'product': product, 'dimension': dimension } return data def get_custom_tariff(order_request): """function to get custom tarif from exact""" product_id = order_request.ProductId try: with connection.cursor() as cursor: cursor.execute(""" SELECT ArticleCode, TariffNumber from ProductMaster where Id=%s """, [ product_id ]) product_data = cursor.fetchone() except Exception as e: raise NotFound({ "status_code": 500, "status": "error", "message": f"Tariff details not found: {str(e)}", "data": None }) if product_data: tariff_number = product_data[1] return tariff_number else: return '' def get_quantity_summary(order_request): """function to retrieve quantity summary details""" packing_data = get_package_info(order_request) custom_tariff = get_custom_tariff(order_request) product_description = { 'DRUMS': 'Drum + Product', 'IBC': 'Pallets + Covers', 'BAGS': 'Bag + Product ' } if packing_data['packaging'] != 'BULK': pack_tare_weight = packing_data['pack_tare_weight'] no_of_packings = packing_data['no_of_packings'] kg_per_packaging = packing_data['kg_per_packaging'] product_description = product_description.get(packing_data['packaging'], '') product_weight = f"{(kg_per_packaging + pack_tare_weight)} Kg" no_of_pallets = packing_data['no_of_pallets'] pallet_weight = packing_data['pallet_weight'] product_name = packing_data['product'] net_weight = packing_data['kg_per_packaging'] * packing_data['no_of_packings'] total_pack_tare_weight = (no_of_packings * pack_tare_weight) total_pallet_weight = (no_of_pallets * pallet_weight) total_tare_weight = (total_pack_tare_weight) + (total_pallet_weight) gross_weight = net_weight + total_tare_weight # elif packing_data['packaging'] == 'IBC': # pack_tare_weight = packing_data['pack_tare_weight'] # no_of_packings = packing_data['no_of_packings'] # kg_per_packaging = packing_data['kg_per_packaging'] # product_description = 'IBC + PRODUCT' # product_weight = f"{(kg_per_packaging + pack_tare_weight)} Kg" # no_of_pallets = packing_data['no_of_pallets'] # pallet_weight = packing_data['pallet_weight'] # product_name = packing_data['product'] # net_weight = packing_data['kg_per_packaging'] * packing_data['no_of_packings'] # total_pack_tare_weight = (no_of_packings * pack_tare_weight) # total_pallet_weight = (no_of_pallets * pallet_weight) # total_tare_weight = (total_pack_tare_weight) + (total_pallet_weight) # gross_weight = net_weight + total_tare_weight packagelist_data = { 'packaging': packing_data['packaging'], 'product_name': product_name, 'custom_tariff': custom_tariff, 'no_of_packings': no_of_packings, 'product_description': product_description, 'product_weight': product_weight, 'no_of_pallets': no_of_pallets, 'pallet_weight': pallet_weight, 'net_weight': net_weight, 'pack_tare_weight': pack_tare_weight, 'total_tare_weight': total_tare_weight, 'gross_weight': gross_weight, } return packagelist_data def get_PackagingInfo(order_id): try: order_request = purchase_models.OrderRequests.objects.get(Id=order_id) except purchase_models.OrderRequests.ObjectDoesNotExist: raise ValidationError({ "status_code": 404, "status": "error", "message": "Order request not found", "data": None }, status=status.HTTP_404_NOT_FOUND) product_id = order_request.ProductId product_data = fetch_data.get_product_data(product_id) if product_data['packaging']=='Bulk': return 'Bulk' package_data = get_quantity_summary(order_request) pallet_info = '' if package_data['no_of_pallets'] and package_data['no_of_pallets'] > 0: pallet_info = f'/ {package_data['no_of_pallets']} pallets' try: packaging = f"{package_data['no_of_packings']} {package_data['product_description']} {pallet_info} " return packaging except: return package_data['packaging'] def get_cancellation_data(orders, order_data, receiver): """ Function to get content to send mail for transporter """ order_details = [] formated_exact_order_no = '' response_data = {} order_ids = [] for order in orders: data = { 'loading_date': order.LoadingDate, 'order_no': order.ExactOrderNo, } order_details.append(data) order_ids.append(order.OrderNo) formated_exact_order_no += f"O/Ref: {order.ExactOrderNo}, " if receiver == 'transporter': subject = f'Cancellation of Transport: {formated_exact_order_no}' response_data['subject'] = f"{subject} {order_data['FormattedProductName']}- {order_data['CustomerName']}- to {order_data['ShipToCity']}" to_mails = order_data['ReceipientMailIds'] if order_data['ReceipientMailIds'] else [] default_cc = os.getenv('TRANSPORTER_DEFAULT_CC_EMAILS') cc_emails = default_cc.split(',') recepient_id = order_data['TransporterId'] elif receiver == 'customer': subject = f'Cancellation of Order: {formated_exact_order_no}' response_data['subject'] = f"{subject} {order_data['FormattedProductName']}- {order_data['CustomerName']}- to {order_data['ShipToCity']}" to_mails = purchase_models.CustomerContacts.objects.filter( ExactERPAddressId=order_data['CustomerExactAddressId'], IsDeleted=0, Type='to').values_list( 'Email', flat=True) cc_emails = list(purchase_models.CustomerContacts.objects.filter( ExactERPAddressId=order_data['CustomerExactAddressId'], IsDeleted=0, Type='cc').values_list( 'Email', flat=True)) if 'sales@pvs.be' not in cc_emails: cc_emails.append('sales@pvs.be') recepient_id = order_data['OrderedCustomerId'] elif receiver == 'lab': subject = f'Cancellation of Order: {formated_exact_order_no}' response_data['subject'] = f"{subject} {order_data['FormattedProductName']}- {order_data['CustomerName']}- to {order_data['ShipToCity']}" to_mails = ['wet.labo@pvs.be'] cc_emails = [] recepient_id = 1 # recepeint id for lab mails elif receiver == 'courier': subject = f'Cancellation of Order: {formated_exact_order_no}' response_data['subject'] = f"{subject} {order_data['FormattedProductName']}- {order_data['CustomerName']}- to {order_data['ShipToCity']}" to_mails = ['dad.spsbe@fedex.com', 'mike.vandebotermet@fedex.com'] cc_emails = ['transportopdrachten@pvs.be'] recepient_id = 1 # recepeint id for lab mails second_email = order_data['PortalUserMailId'] if second_email: cc_emails.append(second_email) incoterm_list = ['FCA', 'EXW'] if receiver=='transporter': if order_data['IncoTerm'] in incoterm_list: raise ValidationError({ "status_code": 400, "status": "error", "message": f"Transporter Mail is not generated since Incoterm is {order_data['IncoTerm']}", "data": None }) elif receiver=='customer': if order_data['ArticleCode'] == '50000': raise ValidationError({ "status_code": 400, "status": "error", "message": f"Customer Mail is not generated since Product is PS 3 ", "data": None }) response_data['order_details'] = order_details response_data['to'] = to_mails response_data['cc'] = cc_emails response_data['recepient_id'] = recepient_id response_data['order_ids'] = order_ids return response_data def get_status_data(order_request_id): """Function to get status data of canceled order""" order_history = purchase_models.OrderHistory.objects.filter( OrderRequestId=order_request_id, Updation='Order Cancelled').last() if order_history: user_data = fetch_data.get_user_data(order_history.UpdatedBy) status_data = {} first_name = user_data.get('first_name', '') last_name = user_data.get('last_name', '') status_data['CancelledBy'] = f"{first_name} {last_name}" status_data['CancelledOn'] = order_history.UpdatedOn.strftime( "%d.%m.%Y %I:%M %p") if order_history.UpdatedOn else '' customer_cancel_mail_history = purchase_models.EmailLog.objects.filter( type='customer_cancellation', order_id=order_request_id).last() if customer_cancel_mail_history: status_data['CustomerCancellationSentOn'] = customer_cancel_mail_history.sent_at.strftime( "%d.%m.%Y %I:%M %p") if customer_cancel_mail_history.sent_at else '' transporter_cancel_mail_history = purchase_models.EmailLog.objects.filter( type='transporter_cancellation', order_id=order_request_id).last() if transporter_cancel_mail_history: status_data['TransporterCancellationSentOn'] = transporter_cancel_mail_history.sent_at.strftime( "%d.%m.%Y %I:%M %p") if transporter_cancel_mail_history.sent_at else '' lab_cancel_mail_history = purchase_models.EmailLog.objects.filter( type='lab_cancellation', order_id=order_request_id).last() if lab_cancel_mail_history: status_data['LabCancellationSentOn'] = lab_cancel_mail_history.sent_at.strftime( "%d.%m.%Y %I:%M %p") if lab_cancel_mail_history.sent_at else '' courier_cancel_mail_history = purchase_models.EmailLog.objects.filter( type='courier_cancellation', order_id=order_request_id).last() if courier_cancel_mail_history: status_data['CourierCancellationSentOn'] = courier_cancel_mail_history.sent_at.strftime( "%d.%m.%Y %I:%M %p") if courier_cancel_mail_history.sent_at else '' return status_data def get_email_content_for_taken_from_same_batch(order, order_request, order_data): """content for sample needed if sample taken from same batch""" subject = f"Technic Ultra Pure - ISO loading {order.ExactOrderNo}" body = f"""For ref. {order.ExactOrderNo}, no extra samples will be taken for shipment to the customer, as the product will be loaded from the same tank as ref. """ if order.Packaging == 'Bulk': date_label = 'Loading Date' else: date_label = 'Filling Date' response_dict = { 'subject': subject, 'body': body, 'date_label': date_label, 'loading_date': f'{order.LoadingDate.strftime("%d-%m-%Y")}', 'loading_time': order.LoadingTime if order.LoadingTime else None, 'sample_collection_date': 'NA', 'sample_collection_time': '', 'sample_delivery_date' : 'NA', 'sample_delivery_time' : '', 'exact_order_no' : order.ExactOrderNo, 'previos_order_ref_no' : order.SampleOrderRef if order.SampleOrderRef else '', 'customer_ref_no' : order_request.PurchaseOrderNo, 'packaging' : order.Packaging.upper(), 'to_mail': ['wet.labo@pvs.be'], 'cc_mail': ['sales@pvs.be'] } return response_dict def get_email_content_for_analyzed_by_customer(order, order_request, order_data): """content for sample needed if sample taken from same batch""" subject = f"Technic Ultra Pure - ISO loading {order.ExactOrderNo}" body = f"""For ref. {order.ExactOrderNo}, no extra samples will be taken for shipment to the customer, as the customer will do their own analysis after receving the product""" if order.Packaging == 'Bulk': date_label = 'Loading Date' else: date_label = 'Filling Date' response_dict = { 'subject': subject, 'body': body, 'date_label': date_label, 'loading_date': f'{order.LoadingDate.strftime("%d-%m-%Y")}', 'loading_time': order.LoadingTime if order.LoadingTime else None, 'sample_collection_date': 'NA', 'sample_collection_time': '', 'sample_delivery_date' : 'NA', 'sample_delivery_time' : '', 'exact_order_no' : order.ExactOrderNo, 'previos_order_ref_no' : order.SampleOrderRef if order.SampleOrderRef else '', 'customer_ref_no' : order_request.PurchaseOrderNo, 'packaging' : order.Packaging.upper(), 'to_mail': ['wet.labo@pvs.be'], 'cc_mail': ['sales@pvs.be'] } return response_dict def get_email_content_for_option_1(order, order_request, order_data): """content for lab if sample needed is YES and option is 1""" deliver_to_data = fetch_data.get_address_data(order.DeliveryCustomerId) if order.Packaging == 'Bulk': subject = f"{deliver_to_data['customer_name']} - ISO loading {order.ExactOrderNo}" body = f"Samples to send to customer will be taken from Loading of PVS ref. {order.ExactOrderNo}" loading_date = f'{order.LoadingDate.strftime("%d-%m-%Y")}' loading_time = order.LoadingTime if order.LoadingTime else None date_label = 'Loading Date' else: # Optional since the filling date became a PVS-only requirement: a third # party loading station does not tell us when it will fill the goods. loading_date = ( order.FillingDate.strftime("%d-%m-%Y") if order.FillingDate else '' ) loading_time = '' subject = f"{deliver_to_data['customer_name']} - {order.Packaging}'s for Ref. {order.ExactOrderNo}" body = f"As from ({loading_date}) {order.Packaging}'s will be filled for {deliver_to_data['customer_name']} - Ref. ({order.ExactOrderNo}).

2 Extra 500 ml samples will be taken to send to the customer. " date_label = 'Filling Date' response_dict = { 'subject': subject, 'body': body, 'date_label': date_label, 'loading_date': loading_date , 'loading_time': loading_time, 'sample_collection_date': order.CourierPickupDate.strftime("%d-%m-%Y") if order.CourierPickupDate else None, 'sample_collection_time': order.CourierPickupTime if order.CourierPickupTime else None, 'sample_delivery_date' : order.CourierDate.strftime("%d-%m-%Y") if order.CourierDate else '', 'sample_delivery_time' : order_data['CourierTime'] if order_data['CourierTime'] else '', 'exact_order_no' : order.ExactOrderNo, 'previos_order_ref_no' : order.SampleOrderRef if order.SampleOrderRef else '', 'customer_ref_no' : order_request.PurchaseOrderNo, 'packaging' : order.Packaging.upper(), 'to_mail': ['wet.labo@pvs.be'], 'cc_mail': ['sales@pvs.be'] } return response_dict def get_email_content_for_option_2(order, order_request, order_data): """content for lab if sample needed is YES and option is 2""" deliver_to_data = fetch_data.get_address_data(order.DeliveryCustomerId) if order.Packaging == 'Bulk': subject = f"{deliver_to_data['customer_name']} - ISO loading {order.ExactOrderNo}" date_label = 'Loading Date' else: subject = f"{deliver_to_data['customer_name']} - {order.Packaging}'s for Ref. {order.ExactOrderNo}" date_label = 'Filling Date' response_dict = { 'subject': subject, 'body': '', 'date_label': date_label, 'loading_date':'', 'loading_time': '', 'sample_collection_date': order.CourierPickupDate.strftime("%d-%m-%Y") if order.CourierPickupDate else None, 'sample_collection_time': order.CourierPickupTime if order.CourierPickupTime else None, 'sample_delivery_date' : '', 'sample_delivery_time' : '', 'exact_order_no' : order.ExactOrderNo, 'previos_order_ref_no' : '', 'customer_ref_no' : order_request.PurchaseOrderNo, 'packaging' : order.Packaging.upper(), 'to_mail': ['wet.labo@pvs.be'], 'cc_mail': ['sales@pvs.be'] } return response_dict def get_email_content_courier(order, order_request, order_data): """Content for sending courier mail""" product_data = fetch_data.get_product_data_from_exact(order_request.ProductId) product_data1 = fetch_data.get_product_data(order_request.ProductId) # load_point_data = fetch_data.get_loadpoint_data(order_request.Id) # deliver_to_data = fetch_data.get_address_data(order.DeliveryCustomerId) subject = f"AFHALING STALEN ZWAVELZUUR UHP 96% GENT --> AMIENS" body = f"Graag op onderstaande datum 2 stalen {product_data['product_name']} laten afhalen voor verzending " if order.Packaging == 'Bulk': text_3 = f"2 échantillons -réf. PVS- {order.ExactOrderNo}" else: text_3 = f"échantillons {order.Packaging} PO {order_request.PurchaseOrderNo} {order.DeliveryCustomerName}-réf. PVS- {order.ExactOrderNo}" default_attachment = purchase_models.EmailAttachment.objects.filter( email_type='courier', is_deleted=False).values( 'id', 'customer_id', 'email_type', 'file_name', 'file_path') response_dict = { 'subject': subject, 'body': body, 'text_3': text_3, 'sample_collection_date': order.CourierPickupDate.strftime("%d-%m-%Y") if order.CourierPickupDate else None, 'sample_collection_time': order.CourierPickupTime if order.CourierPickupTime else None, 'sample_delivery_date' : order.CourierDate.strftime("%d-%m-%Y") if order.CourierDate else '', 'sample_delivery_time' : order_data['CourierTime'] if order_data['CourierTime'] else '', 'exact_order_no' : order.ExactOrderNo, 'previos_order_ref_no' : order.SampleOrderRef if order.SampleOrderRef else '', 'customer_ref_no' : order_request.PurchaseOrderNo, 'packaging' : order.Packaging, 'product_name' : f"{product_data['product_name']} ({product_data['un_code']})", 'to_mail': ['dad.spsbe@fedex.com', 'mike.vandebotermet@fedex.com'], 'cc_mail': ['sales@pvs.be', 'transportopdrachten@pvs.be'], 'customer_name': 'Technic Ultra Pure', 'address_line1': 'T.a.v. Boinali Samuel', 'address_line2': '121, rue André Durouchez', 'address_line3': 'F-80 000 Amiens', 'default_attachment': default_attachment } return response_dict def update_transporter_contact_history(order_id, ship_to, recipient_email, cc_email, receipient_id): """function to update transporter contacts from email""" try: order_request = purchase_models.OrderRequests.objects.get(Id=order_id) product_id = order_request.ProductId product_data = fetch_data.get_product_data(product_id) except purchase_models.OrderRequests.DoesNotExist: # order_id not found raise ValueError(f"OrderRequest with Id {order_id} does not exist") purchase_models.TransporterContact.objects.filter( ship_to=ship_to, product_category=product_data['product_category'], transporter_id=receipient_id).update(is_deleted=True) for email in recipient_email: purchase_models.TransporterContact.objects.create( ship_to=ship_to, product_category=product_data['product_category'], email=email, recipient_type="to", transporter_id=receipient_id ) for email in cc_email: purchase_models.TransporterContact.objects.create( ship_to=ship_to, product_category=product_data['product_category'], email=email, recipient_type="cc", transporter_id=receipient_id ) def get_transporter_data_for_cancellation(transporter_id, product_category, ship_to): """function returns transporter data for cancellation""" to_emails = purchase_models.TransporterContact.objects.filter( ship_to=ship_to, product_category=product_category, transporter_id=transporter_id, recipient_type='to', is_deleted=False).values_list('email', flat=True) cc_emails = purchase_models.TransporterContact.objects.filter( ship_to=ship_to, product_category=product_category, transporter_id=transporter_id, recipient_type='cc', is_deleted=False).values_list('email', flat=True) return list(set(to_emails)), list(set(cc_emails)) def get_replan_content_data(order, type, receipient_id, changed_fields, request): """function checks weather repla is needed and gives response of replan mail content""" error_message = None login_user_email = request.login_user_email fileds_list = {field for field in changed_fields.keys()} loading_fileds = {'loading_date', 'loading_time'} delievry_fileds = {'delivery_date', 'delivery_time_interval'} if fileds_list.intersection(loading_fileds): fileds_list = fileds_list.union(loading_fileds) if fileds_list.intersection(delievry_fileds): fileds_list = fileds_list.union(delievry_fileds) if type=='transporter': if 'transporter_id' in fileds_list: receipient_id = changed_fields['transporter_id']['from'] email_log = purchase_models.EmailLog.objects.filter( type=type, order_id=order.OrderNo).last() if not receipient_id or receipient_id=='' or not email_log or email_log.receipient_id!=receipient_id: raise ValidationError({ "status_code": 400, "status": "error", "message": f"Since the Transporter mail wasn't sent. There's no need to send a mail.", "data": None }) transportmail_data = purchase_serializer.TransportMailSerializer(order, context={'request': request}).data cc_emails = transportmail_data['TransportContactHistory']['cc_mails'] default_cc = os.getenv('TRANSPORTER_DEFAULT_CC_EMAILS').split(',') cc_emails.extend(default_cc) cc_emails.append(login_user_email) cc_emails = list(set(cc_emails)) to_mails = transportmail_data['TransportContactHistory']['to_mails'] transportmail_data['to'] = to_mails transportmail_data['cc'] = cc_emails if 'transporter_id' in changed_fields: subject = f"Cancel Transport: {transportmail_data['ProductName']} - O/Ref.{transportmail_data['ExactOrderNo']}, Loading on {transportmail_data['LoadingDate']} to {transportmail_data['ShipToCity']}" product_category = transportmail_data['ProductCategory'] transporter_id = changed_fields['transporter_id']['from'] to_mails, cc_emails = get_transporter_data_for_cancellation( transporter_id, product_category, transportmail_data['ShipToCode']) cc_emails.append(login_user_email) cc_emails.extend(default_cc) cc_emails.append(login_user_email) else: # no need to inform transporter since incoterm in FCA if transportmail_data['IncoTerm']=='FCA': raise ValidationError({ "status_code": 400, "status": "error", "message": f"No Need to Send Mail Since Order is FCA", "data": None }) order_history = purchase_models.OrderHistory.objects.filter( OrderRequestId=order.OrderNo, history_type=type) # if 'delivery_date' in fileds_list: # fileds_list.append('delivery_time_interval') if 'delivery_time_interval' not in fileds_list else fileds_list # if 'loading_date' in fileds_list: # fileds_list.append('loading_time') if 'loading_time' not in fileds_list else fileds_list if order_history: subject = f"Change in Delivery: {transportmail_data['ProductName']} - {transportmail_data['CustomerName']} - O/Ref.{transportmail_data['ExactOrderNo']}, Loading on {transportmail_data['LoadingDate']} to {transportmail_data['ShipToCity']}" transportmail_data['subject'] = subject transportmail_data['fields_to_show'] = list(fileds_list) transportmail_data['to'] = list(set(to_mails)) transportmail_data['cc'] = list(set(cc_emails)) return transportmail_data elif type=='customer': order_id = request.data.get('orderId') extra_charge = request.data.get('extra_charge') email_log = purchase_models.EmailLog.objects.filter( type=type, order_id=order.OrderNo).last() customer_data = purchase_serializer.CustomerMailSerializer(order, context={'request': request}).data # Some customers dosent need to be notified need_customer_mail = customer_data['NeedCustomerMail'] if not need_customer_mail: raise ValidationError({ "status_code": 400, "status": "error", "message": f"Customer Notification not Needed", "data": None }) # No need to sent replan mail since confirmation mail has not been sent if not receipient_id or receipient_id=='' or not email_log or email_log.receipient_id!=receipient_id: raise ValidationError({ "status_code": 400, "status": "error", "message": f"Since the Customer mail wasn't sent. There's no need to send a mail.", "data": None }) if 'price' in fileds_list: info_text = f"Price has changed from {changed_fields['price']['from']} to {changed_fields['price']['to']} due to change in quantity" customer_data['instructions'] = [info_text] subject = f"Change in Delivery: {customer_data['ArticleCode']} - {customer_data['Product']} - {customer_data['CustomerName']} - O/Ref.{customer_data['ExactOrderNo']} - Y/Ref.{customer_data['PurchaseOrderNo']}, Loading on {customer_data['LoadingDate']}" customer_data['subject'] = subject customer_data['to'] = customer_data.pop('ReceipientMailId') cc_emails = customer_data.pop('CCMailId') cc_emails.append('sales@pvs.be') if 'sales@pvs.be' not in cc_emails else cc_emails customer_data['cc'] = cc_emails customer_data['fields_to_show'] = list(fileds_list) extra_charges = customer_data.pop('ExtraCharges') total_charges = customer_data.pop('ToTalExtraCharge') if extra_charge : customer_data['extra_charges'] = extra_charges customer_data['TotalExtraCharge'] = total_charges return customer_data elif type=='lab': email_types = [ 'taken_from_same_batch', 'analyzed_by_customer', 'taken_from_truck', 'taken_from_other_customer', ] # create a animie poster with this girl and Gojo Saturo from animie email_log = purchase_models.EmailLog.objects.filter( type__in=email_types, order_id=order.OrderNo).last() if not email_log: raise ValidationError({ "status_code": 400, "status": "error", "message": f"Since the Lab mail wasn't sent. There's no need to send a mail.", "data": None }) customer_data = purchase_serializer.CustomerMailSerializer(order, context={'request': request}).data subject = f'Change in Delivery: {customer_data['ArticleCode']} - {customer_data['Product']} - {customer_data['CustomerName']} - O/Ref.{customer_data['ExactOrderNo']}-Y/Ref, Loading on {customer_data['LoadingDate']}' customer_data['subject'] = subject customer_data['to'] = ['wet.labo@pvs.be'] customer_data['cc'] = ['sales@pvs.be'] login_user_email = request.login_user_email customer_data['cc'].append(login_user_email) customer_data['fields_to_show'] = list(fileds_list) return customer_data elif type=='courier': email_log = purchase_models.EmailLog.objects.filter( type=type, order_id=order.OrderNo).last() if not email_log: raise ValidationError({ "status_code": 400, "status": "error", "message": f"No email needs to be sent since the courier was not notified.", "data": None }) customer_data = purchase_serializer.TransportMailSerializer(order, context={'request': request}).data # Default subject subject = 'Order Replanned - AFHALING STALEN ZWAVELZUUR UHP 96% GENT --> AMIENS' if 'need_sample' in fileds_list: if changed_fields['need_sample']['from']=='YES' and changed_fields['need_sample']['to']=='NO': subject = f'Sample transportation not required - AFHALING STALEN ZWAVELZUUR UHP 96% GENT --> AMIENS' if changed_fields['need_sample']['from']=='NO' and changed_fields['need_sample']['to']=='YES': raise ValidationError({ "status_code": 400, "status": "error", "message": f"No Need to send Replan mail", "data": None }) customer_data['sample_pickup_date'] = customer_data['CourierPickupDate'] customer_data['sample_pickup_time'] = customer_data['CourierPickupTime'] customer_data['sample_delivery_date'] = customer_data['CourierDate'] customer_data['sample_delivery_time'] = customer_data['CourierTime'] customer_data['subject'] = subject customer_data['to'] = ['koen.mertens@sgs.com', 'be.ogc.saman4@sgs.com'] customer_data['cc'] = ['sales@pvs.be', 'transportopdrachten@pvs.be'] login_user_email = request.login_user_email customer_data['cc'].append(login_user_email) customer_data['fields_to_show'] = list(fileds_list) return customer_data def get_order_price_link_id(order_id): """function returns price applied for the order""" order_copy = purchase_models.OrderPriceLink.objects.filter(OrderId=order_id,IsDeleted=False).last() return order_copy def get_applied_price(existing_entry): """function to get price link""" price_link = purchase_models.LinkedPrice.objects.filter( order_price=existing_entry, is_deleted=False).last() if price_link: data = { "ArticleCode": price_link.article_code, "ProductName": price_link.product_name, "BillTo": price_link.bill_to, "OrderedByCustomer": price_link.ordered_by_customer, "Currency": price_link.currency, "currency_id": price_link.currency_id, "DeliveryDestination": price_link.delivery_destination, "ExactERPAddressId": price_link.exact_erp_address_id, "IncoTerm": price_link.incoterm, # "LeadTimeDays": price_link.lead_time_days, "MinQuantity": price_link.min_quantity, "MinQuantityOperator": price_link.min_quantity_operator, "Packaging": price_link.packaging, "kg_per_packaging": price_link.kg_per_packaging, "no_of_packings": price_link.no_of_packings, "PaymentTerm": price_link.payment_term, "payment_term_id": price_link.payment_term_id, "actual_price": price_link.price, "PriceConfirmation": 'YES' if price_link.price_confirmation else 'NO', "ValidityStartDate": price_link.validity_start_date.strftime("%d-%m-%Y") if price_link.validity_start_date else None, "ValidityEndDate": price_link.validity_end_date.strftime("%d-%m-%Y") if price_link.validity_end_date else None, "pricelist_id": price_link.pricelist_id, "Emails": price_link.emails, "QuarterName": price_link.quarter_name } return data return None def copy_extra_charges(copy_order_request_id, new_order_req_id): """function copy extracharges from old order and create extra charges for new""" extra_charges = purchase_models.OrderCharges.objects.filter( OrderNo=copy_order_request_id, IsDeleted=False).exclude(ChargeName__in=purchase_consts.HOLIDAY_CHARGES) for charge in extra_charges: charges = purchase_models.OrderCharges.objects.create( OrderNo=new_order_req_id, ChargeName=charge.ChargeName, ExtraChargeId=charge.ExtraChargeId, Rate=charge.Rate) return True def get_transporter_and_price(parent_order_id): """function copy extracharges from old order and create extra charges for new""" order = purchase_models.Orders.objects.filter( OrderNo=parent_order_id, IsDeleted=False).last() if order: transporter_id = order.TransporterId transport_details = fetch_data.get_transorter_data(transporter_id) transporter_name = transport_details['transporter_name'] if transport_details else '' transporter_price = order.TransportPrice return transporter_id, transporter_name, transporter_price def manage_email_drafts(order_ids, type): """function to manage email drafts""" email_drafts = purchase_models.EmailDraft.objects.filter( order_request_id__in=order_ids, email_type=type, is_deleted=False) if email_drafts: email_drafts.update(is_deleted=True) return True def get_validity_status(validity_end_date): validity_end_date = datetime.strptime(validity_end_date, "%d-%m-%Y").date() if validity_end_date: today = datetime.today().date() if validity_end_date < today: return 1 else: return 0 return "" def process_price_data(price_list): """fucnction to process price data from Odoo""" for price_obj in price_list: product_data = fetch_data.get_product_data_from_article_code(price_obj['ArticleCode']) deliver_to_data = fetch_data.get_address_data_from_shipto(price_obj['ExactERPAddressId']) bill_to_data = fetch_data.get_address_data_from_shipto(price_obj['BillTo']) incoterm = fetch_data.get_incoterm_data(price_obj['IncoTerm']) price_obj['Id'] = price_obj.pop('id') minimum_quantity = str(price_obj.pop('MinQuantity')) price = price_obj.pop('Price') price_obj['actual_price'] = price price_obj['FormattedMinQuantity'] = convert_to_belgium_format(minimum_quantity) price_obj['Price'] = convert_to_belgium_format(str(price)) price_obj['FormattedPrice'] = f"€{str(price_obj['Price'])}" price_obj['packing_remarks'] = price_obj['PackingRemarks'] price_obj['PriceConfirmation'] = 'Yes' if price_obj['PriceConfirmation'] else '' price_obj['ProductId'] = product_data.get('id') price_obj['ProductPackaging'] = product_data.get('packaging') price_obj['DeliverTo'] = deliver_to_data['Id'] if deliver_to_data else None price_obj['DeliverToNickName'] = deliver_to_data['nickname'] if deliver_to_data else None price_obj['BillToId'] = bill_to_data['Id'] if bill_to_data else None price_obj['BillToNickName'] = bill_to_data['nickname'] if bill_to_data else None price_obj['IncotermID'] = incoterm['incoterm_id'] if incoterm else None price_obj['MinQuantity'] = minimum_quantity status = price_obj.get('Status', ' ') if not status: status = ' ' price_obj['IsBlocked'] = 0 if status == 'Confirmed' else 1 price_obj['Status'] = 'Active' if status == 'Confirmed' else status return price_list def parse_float(value): """Handle values like '593,0' or '22.4' safely""" if value is None: return None return float(str(value).replace(",", ".")) def parse_date(value): """Convert dd-mm-yyyy → date""" if not value: return None return datetime.strptime(value, "%d-%m-%Y").date() def create_or_update_linked_price(data, order_price_obj): """ data: API response dict order_price_obj: OrderPriceLink instance """ obj, created = purchase_models.LinkedPrice.objects.get_or_create( order_price=order_price_obj ) # Update all fields obj.article_code = data.get("ArticleCode") obj.product_name = data.get("ProductName") obj.bill_to = int(data.get("BillTo")) if data.get("BillTo") else None obj.ordered_by_customer = data.get("OrderedByCustomer") obj.currency = data.get("Currency") obj.currency_id = data.get("currency_id") obj.delivery_destination = data.get("DeliveryDestination") obj.exact_erp_address_id = int(data.get("ExactERPAddressId")) if data.get("ExactERPAddressId") else None obj.incoterm = data.get("IncoTerm") # obj.lead_time_days = data.get("LeadTimeDays", 0) obj.min_quantity = parse_float(data.get("MinQuantity")) obj.min_quantity_operator = data.get("MinQuantityOperator") obj.packaging = data.get("Packaging") obj.kg_per_packaging = data.get("kg_per_packaging") obj.no_of_packings = data.get("no_of_packings") # obj.packing_remarks = data.get("PackingRemarks") if data.get("PackingRemarks") else '' obj.payment_term = data.get("PaymentTerm") obj.payment_term_id = data.get("payment_term_id") obj.price = data.get("actual_price") obj.price_confirmation = True if data.get("PriceConfirmation")=='YES' else False # obj.offer_ref = data.get("OfferRef") # obj.remarks = data.get("Remarks") obj.quarter_name = data.get("QuarterName") obj.validity_start_date = parse_date(data.get("ValidityStartDate")) obj.validity_end_date = parse_date(data.get("ValidityEndDate")) obj.customer_id = data.get("customer_id") obj.pricelist_id = data.get("pricelist_id") obj.product_id = data.get("product_id") obj.emails = data.get("Emails") obj.save() return obj, created def get_unload_instructions(article_code, incoterm, shipto, load_point_id, billto): """function to get unloading instruction data for transport mail""" unload_data = purchase_models.UnloadInstructions.objects.filter( ArticleCode=article_code, ExactERPAddressId=shipto, BillTo=billto, IncoTerm=incoterm, LoadPointId=load_point_id, IsDeleted=0 ).values_list('Instructions', 'Color','IsImageReq') # if unload_data is not there get instructions by removing bill to # if not unload_data: # unload_data = purchase_models.UnloadInstructions.objects.filter( # ArticleCode=article_code, # ExactERPAddressId=shipto, # IncoTerm=incoterm, # LoadPointId=load_point_id, # IsDeleted=0 # ).values_list('Instructions', 'Color','IsImageReq') if not unload_data: shipto_mapping = purchase_models.ShiptoMapping.objects.filter( parent_shipto_code=shipto, product_article=article_code).last() if shipto_mapping: old_ship_to = shipto_mapping.shipto_code unload_data = purchase_models.UnloadInstructions.objects.filter( ArticleCode=article_code, ExactERPAddressId=old_ship_to, IncoTerm=incoterm, LoadPointId=load_point_id, BillTo=billto, IsDeleted=0 ).values_list('Instructions', 'Color','IsImageReq') formatted_unload_data = [{"text": instruction, "color": color,"is_image_req":is_image_req} for instruction, color ,is_image_req in unload_data] return formatted_unload_data def get_product_price_from_odoo(incoterm_id, loading_date, article_code, quantity, ship_to_code): """function to get product price from odoo""" incoterm = fetch_data.get_incoterm_data_from_id(incoterm_id) payload = { 'ship_to_code': ship_to_code, 'loading_date': datetime.strptime(loading_date, "%Y-%m-%d").strftime("%d-%m-%Y"), 'article_code': article_code, 'qty': quantity, 'incoterm': incoterm['code'] } client = OdooAPIService() try: result = client.get_price_from_quantity(params=payload) price_data = result.get('data', None)[0] is_price_available = price_data['warning'] price = price_data['Price'] quarter = price_data['QuarterName'] qty_msg = price_data['message'] if is_price_available else '' except: price = 0 quarter = '' qty_msg = 'No price found' return price, quarter, qty_msg def get_transporter_price(product_id, load_point_id, incoterms_id, deliver_to_id, loading_date): """function to get transporter price data from Odoo""" product_data = fetch_data.get_product_data(product_id) address_data = fetch_data.get_address_data(deliver_to_id) loadpoint_data = fetch_data.get_loadpoint_data_from_id(load_point_id) params = { "zipcode": address_data.get('postal_code'), "concentration": product_data.get('dilution'), "from_city": loadpoint_data.get('loadpoint_city'), "chemical_id": product_data.get('chemical_name_id'), "loading_date": loading_date, "grade_id": product_data.get('grade_id'), } erp_client = OdooAPIService() transport_price_data = erp_client.get_transport_price(params) if transport_price_data['status_code'] == status.HTTP_400_BAD_REQUEST: params = { "zipcode": address_data.get('postal_code'), "concentration": product_data.get('dilution'), "from_city": loadpoint_data.get('loadpoint_city'), "chemical_id": product_data.get('chemical_name_id'), "loading_date": loading_date, "grade_id": '', } transport_price_data = erp_client.get_transport_price(params) if transport_price_data['status_code'] == status.HTTP_200_OK: data = transport_price_data['data'] if isinstance(data, list): def get_price(x): try: return float(x.get('TransportingPrice')) except (TypeError, ValueError): return float('inf') data = sorted(data, key=get_price) return data return None def get_remaining_transporter_data(available_transporter_prices): """function to get remaining transporter data from Odoo""" transporter_price_list = [] if available_transporter_prices: transport_data = { "TransporterId": available_transporter_prices.get('TransporterId'), "Transporter": f"{available_transporter_prices.get('Transporter')} {available_transporter_prices.get('route_via')}", "TransportingPrice": available_transporter_prices.get('TransportingPrice'), "Frequency": available_transporter_prices.get('Frequency') } transporter_price_list.append(transport_data) transporter_list = fetch_data.get_all_transporter_data() for transporter in transporter_list: transport_data = { "TransporterId": transporter['id'], "Transporter": transporter['transporter'], "TransportingPrice": "", "Frequency": "" } transporter_price_list.append(transport_data) return transporter_price_list def sync_order_to_odoo(order_id, auth_token=None, export_origin='', is_update=False): """Function to synchronize order details directly to Odoo (ERP)""" from django.conf import settings import json def fetch_api_data(api_url, payload, key): """Helper to fetch a specific key from another microservice API.""" headers = { 'x-api-key': getattr(settings, 'API_KEY', ''), 'x-content-type': 'application/json', } if auth_token: headers['x-auth-token'] = auth_token try: response = requests.post(api_url, json=payload, headers=headers, timeout=10) if response.status_code == 200: data = response.json() return data.get('data', {}).get(key, "") except Exception as e: print(f"Error fetching data from {api_url}: {e}") return "" def fetch_user_data(user_id): """Helper to fetch user data (ExactUserID and ResourceNo) from user service.""" if not user_id: return None, None api_url = settings.USER_API_URL + "users/" payload = {"user_id": user_id} headers = { 'x-api-key': getattr(settings, 'API_KEY', ''), 'x-content-type': 'application/json', } if auth_token: headers['x-auth-token'] = auth_token try: response = requests.post(api_url, json=payload, headers=headers, timeout=10) if response.status_code == 200: user_data = response.json().get('data', {}) return user_data.get('ExactUserID'), user_data.get('ResourceNo') except Exception as e: print(f"Error fetching user data for {user_id}: {e}") return None, None order_details = purchase_models.Orders.objects.filter(OrderNo=order_id, IsDeleted=False).first() if not order_details: return { 'status': 'error', 'status_code': 404, 'message': f"Order with ID {order_id} not found." } odoo_service = OdooAPIService() token = odoo_service.get_token() # 1. Fetch user data (user_id and resource_no) exact_user_id, resource_no = fetch_user_data(order_details.CreatedBy) user_id_val = odoo_service.user_id resource_no_val = resource_no if resource_no is not None else "" # 2. Fetch customer address codes (ExactERPAddressId) from Customer Service customer_shiptocode = "" if order_details.OrderedCustomerId is not None: customer_shiptocode = fetch_api_data( settings.CUSTOMER_API_URL + "customers/", {"address_id": order_details.OrderedCustomerId}, "ExactERPAddressId" ) invoice_shiptocode = "" if order_details.BillToCustomerId is not None: invoice_shiptocode = fetch_api_data( settings.CUSTOMER_API_URL + "customers/", {"address_id": order_details.BillToCustomerId}, "ExactERPAddressId" ) delivery_shiptocode = "" if order_details.DeliveryCustomerId is not None: delivery_shiptocode = fetch_api_data( settings.CUSTOMER_API_URL + "customers/", {"address_id": order_details.DeliveryCustomerId}, "ExactERPAddressId" ) # 3. Fetch warehouse code from Delivery Service warehouse_code = "" if order_details.LoadingSlotId is not None: warehouse_code = fetch_api_data( settings.DELIVERY_API_URL + "loadpoints/", {"loading_slot_id": order_details.LoadingSlotId}, "WareHouseCode" ) warehouse_code = f"WH{warehouse_code}" # 4. Fetch transporter code from Delivery Service transport_id_val = "" if order_details.TransporterId is not None: transport_id_val = fetch_api_data( settings.DELIVERY_API_URL + "get_transporters/", {"transporter_id": order_details.TransporterId}, "Id" ) if not transport_id_val: transport_id_val = str(order_details.TransporterId) if order_details.TransporterId is not None else "" # 5. Fetch product code (ArticleCode) from Product Service article_code = "" if order_details.ProductId is not None: article_code = fetch_api_data( settings.PRODUCT_API_URL + "products/", {"product_id": order_details.ProductId}, "ArticleCode" ) # 6. Retrieve product details from secondary DB to get matching description product_code = article_code product_description = order_details.ProductName or "" if article_code: try: packaging = order_details.Packaging.upper() if order_details.Packaging else "" with connections['secondary'].cursor() as cursor: query = """SELECT description, itemcode FROM items WHERE itemcode LIKE %s and Class_08=%s """ params = [f"%{article_code}%", packaging] cursor.execute(query, params) product_db_details = cursor.fetchone() if product_db_details is not None: if product_db_details[1] in ['31112 H2SO4 T 96 % SADACI', '31112 - H2SO4 T 96% MOLYMET']: product_code = '31112 H2SO4 T 96 % MOLYMET' product_description = product_db_details[0] elif product_db_details[1] == '30104 SULF.T. 96% TM': product_code = '30104 H2SO4 TECH 94%' product_description = 'ZWAVELZUUR 94% DRUG PRECURSORS' else: product_code = article_code product_description = product_db_details[0] except Exception as e: print(f"Error querying secondary db for item: {e}") # 7. Construct instructions loading_date_str = order_details.LoadingDate.strftime('%Y-%m-%d') if order_details.LoadingDate else "" delivery_date_str = order_details.DeliveryDate.strftime('%Y-%m-%d') if order_details.DeliveryDate else "" if order_details.IncoTerm in ["FCA", "EXW"] or article_code == "50000": ld_formatted = datetime.strptime(loading_date_str, '%Y-%m-%d').strftime('%d/%m/%Y') if loading_date_str else "" instructions = f"{ld_formatted}".strip() else: delivery_time_interval = purchase_models.OrderRequests.objects.filter(Id=order_id).values_list('DeliveryTimeInterval', flat=True).first() dd_formatted = datetime.strptime(delivery_date_str, '%Y-%m-%d').strftime('%d/%m/%Y') if delivery_date_str else "" instructions = f"{dd_formatted} {delivery_time_interval or ''}".strip() # 8. Create main product line main_line = { "product_article_code": product_code, "description": product_description, "instructions": instructions, "qty": float(order_details.Quantity) if order_details.Quantity else 0.0, "price_unit": float(order_details.Price) if order_details.Price else 0.0, "discount": 0, "delivery_date": delivery_date_str } product_lines = [main_line] # 9. Fetch extra charges order_charges = purchase_models.OrderCharges.objects.filter(OrderNo=order_id, IsDeleted=0) for order_charge in order_charges: charge_line = { "product_article_code": order_charge.ArticleCode if order_charge.ArticleCode else "", "description": order_charge.ChargeName if order_charge.ChargeName else "", "instructions": "", "qty": 1, "price_unit": float(order_charge.Rate) if order_charge.Rate else 0.0, "discount": 0, "is_extra_charge": True, "delivery_date": loading_date_str } product_lines.append(charge_line) # 10. Fetch order general info line # order_request = purchase_models.OrderRequests.objects.filter(Id=order_id).first() # if order_request and order_request.Information: # ld_slash = datetime.strptime(loading_date_str, '%Y-%m-%d').strftime('%d/%m/%Y') if loading_date_str else "" # information_string = f"{order_request.Information} {ld_slash}".strip() # info_line = { # "description": information_string, # "qty": 0, # "price_unit": 0.0, # "discount": 0, # "is_extra_charge": True, # "delivery_date": loading_date_str # } # product_lines.append(info_line) # 11. Format final payload fields order_request = purchase_models.OrderRequests.objects.filter(Id=order_id).first() customer_reference = order_request.PurchaseOrderNo if order_request else "" def safe_str(val): return str(val) if val is not None else "" payload = { "orderpro_id": safe_str(order_details.OrderNo), "company_id": safe_str(settings.ODOO_COMPANY_ID if hasattr(settings, 'ODOO_COMPANY_ID') else '1'), "user_id": safe_str(user_id_val), "customer_shiptocode": safe_str(customer_shiptocode), "order_date": safe_str(order_details.CreatedAt.strftime('%Y-%m-%d') if order_details.CreatedAt else date.today().strftime('%Y-%m-%d')), "order_code": safe_str(order_details.ExactOrderNo), "customer_reference": safe_str(customer_reference), "resource_no": safe_str(resource_no_val), "incoterm_code": safe_str(order_details.IncoTerm), "transport_id": safe_str(transport_id_val), "transport_price": safe_str(order_details.TransportPrice), "product_ids": json.dumps(product_lines), "product_list": json.dumps(product_lines), "cost_center": safe_str(order_details.CostCenter), "warehouse_code": safe_str(warehouse_code), "invoice_shiptocode": safe_str(invoice_shiptocode), "delivery_shiptocode": safe_str(delivery_shiptocode), "delivery_date": safe_str(delivery_date_str), "delivery_time": safe_str(order_request.DeliveryTimeInterval if order_request else order_details.LoadingTime), "loading_date": safe_str(loading_date_str), "loading_time": safe_str(order_details.LoadingTime), "export_origin": safe_str(export_origin), "export_origin_text": safe_str(export_origin) } print(12121212, payload) # 12. Send POST request to Odoo try: if odoo_service.company_id: payload["company_id"] = safe_str(odoo_service.company_id) if is_update: odoo_service.change_order_status(order_ids=order_id, action="draft") # changing order status to draft url = f"{odoo_service.base_url}/post/sale_order/edit_sale_order" else: url = f"{odoo_service.base_url}/post/sale_order/create_sale_order" headers = { "Authorization": f"Bearer {token}", "Content-Type": "application/x-www-form-urlencoded" } response = requests.post(url, data=payload, headers=headers, timeout=30) # Read response details try: response_data = response.json() except Exception as e: response_data = response.text if response.status_code != 200: error_msg = "" if isinstance(response_data, dict): error_msg = response_data.get('message', '') or response_data.get('error', '') else: error_msg = str(response_data) raise ValidationError({ "status_code": response.status_code, "status": "error", "message": f"Failed to sync order to Odoo. {error_msg}", "data": None }) odoo_service.change_order_status_async(order_ids=order_id, action="confirm") # changing order status to confirm return { "status": "success", "status_code": response.status_code, "message": "Order synchronized to Odoo successfully.", "data": response_data } except ValidationError as val_err: raise val_err except Exception as e: raise ValidationError({ "status_code": 400, "status": "error", "message": f"An error occurred while synchronizing order to Odoo: {str(e)}", "data": None })