Spaces:
Sleeping
Sleeping
| from datetime import datetime | |
| from pydantic import Field | |
| from cbh.api.client.dto import ExportLayout, ExportProfileType, InputField, OutputField | |
| from cbh.api.common.dto import CustomsProcedure, TradeDirection | |
| from cbh.core.database import MongoBaseModel, MongoSerializableModel | |
| class ExportColumn(MongoSerializableModel): | |
| field: OutputField | |
| header: str | |
| class ProfileDefaults(MongoSerializableModel): | |
| direction: TradeDirection = TradeDirection.IMPORT | |
| procedure: CustomsProcedure = CustomsProcedure.RELEASE_FOR_FREE_CIRCULATION | |
| country: str | None = None | |
| destinationCountry: str | None = None | |
| # The currency the client's value column is written in. The pack does not | |
| # say so on every line, and the header total is meaningless in the wrong one. | |
| currency: str = "EUR" | |
| useTodayWhenDateMissing: bool = True | |
| class TotalsConfig(MongoSerializableModel): | |
| enabled: bool = True | |
| label: str = "TOTAL" | |
| sumFields: list[str] = Field( | |
| default_factory=lambda: [ | |
| InputField.PACKAGES.value, | |
| InputField.GROSS_WEIGHT.value, | |
| InputField.VALUE.value, | |
| ] | |
| ) | |
| # A parcel manifest carries one row per item but states weight and piece | |
| # count per parcel, repeated on every row of that parcel. Adding the rows | |
| # up inflates the shipment totals, which is the figure the broker checks | |
| # the consignment against, so those fields are counted once per parcel. | |
| parcelKeyColumn: str | None = None | |
| parcelLevelFields: list[str] = Field(default_factory=list) | |
| packagesFromParcelCount: bool = False | |
| def default_export_columns() -> list[ExportColumn]: | |
| return [ | |
| ExportColumn(field=OutputField.RESOLVED_CODE, header="resolved_code"), | |
| ExportColumn(field=OutputField.DOCUMENTS_REQUIRED, header="documents_required"), | |
| ExportColumn(field=OutputField.DOCUMENTS_EXEMPTION, header="documents_exemption"), | |
| ExportColumn(field=OutputField.WARNINGS, header="warnings"), | |
| ExportColumn(field=OutputField.STATUS, header="status"), | |
| ] | |
| def preference_export_columns() -> list[ExportColumn]: | |
| """The tariff preference of an import line, as the declaration box takes it.""" | |
| return [ | |
| ExportColumn(field=OutputField.PREFERENCE_CODE, header="Preferentie"), | |
| ExportColumn(field=OutputField.PREFERENTIAL_RATE, header="Preferentieel tarief"), | |
| ExportColumn(field=OutputField.ORIGIN_PROOF, header="Oorsprongsbewijs"), | |
| ExportColumn(field=OutputField.QUOTA_ORDER_NUMBER, header="Contingentnummer"), | |
| ] | |
| class ClientProfileModel(MongoBaseModel): | |
| name: str | |
| description: str | None = None | |
| exportProfile: ExportProfileType = ExportProfileType.SOFTPAK | |
| columnMapping: dict[str, str] = Field(default_factory=dict) | |
| exportColumns: list[ExportColumn] = Field(default_factory=default_export_columns) | |
| defaults: ProfileDefaults = Field(default_factory=ProfileDefaults) | |
| totals: TotalsConfig = Field(default_factory=TotalsConfig) | |
| headerRow: int = 1 | |
| sheetName: str | None = None | |
| highlightForReview: bool = True | |
| exportLayout: ExportLayout = ExportLayout.APPEND_COLUMNS | |
| softpakHeaderRows: list[list[str]] = Field(default_factory=list) | |
| datetimeInserted: datetime = Field(default_factory=datetime.now) | |
| datetimeUpdated: datetime = Field(default_factory=datetime.now) | |
| def missing_required_fields(self) -> list[str]: | |
| return [ | |
| field.value | |
| for field in InputField | |
| if field.required and not self.columnMapping.get(field.value) | |
| ] | |
| def goenee_softpak_profile() -> ClientProfileModel: | |
| return ClientProfileModel( | |
| name="Goenee Softpak", | |
| description="Jac. Goenee B.V., Vianen. Softpak line list, Excel in and out.", | |
| exportProfile=ExportProfileType.SOFTPAK, | |
| exportLayout=ExportLayout.SOFTPAK_SHEET, | |
| headerRow=7, | |
| columnMapping={ | |
| InputField.CODE.value: "hs code", | |
| InputField.DESCRIPTION.value: "description", | |
| InputField.GROSS_WEIGHT.value: "WEIGHT GROSS", | |
| InputField.NET_WEIGHT.value: "WEIGHT nett", | |
| InputField.COUNTRY.value: "origin", | |
| InputField.QUANTITY.value: "sup_quantity", | |
| InputField.COLLI_TYPE.value: "type colli", | |
| InputField.PACKAGES.value: "nr colli", | |
| InputField.REMARKS.value: "remarks", | |
| InputField.VALUE.value: "value", | |
| }, | |
| exportColumns=[ExportColumn(field=OutputField.YCODES, header="Ycodes")], | |
| softpakHeaderRows=[ | |
| ["Government procedure", "10", " "], | |
| ["Previous ", "00"], | |
| ["Invoice N935", " "], | |
| ["Currency", "EUR"], | |
| ["Container"], | |
| ], | |
| defaults=ProfileDefaults( | |
| direction=TradeDirection.EXPORT, useTodayWhenDateMissing=True | |
| ), | |
| totals=TotalsConfig( | |
| sumFields=[ | |
| InputField.GROSS_WEIGHT.value, | |
| InputField.NET_WEIGHT.value, | |
| InputField.PACKAGES.value, | |
| InputField.VALUE.value, | |
| ] | |
| ), | |
| ) | |
| def goenee_export_line_list_profile() -> ClientProfileModel: | |
| """The line list Goenee produces from a G-Star manifest — `Export Goenee.xls`. | |
| It is the same shipment as the ecom manifest but already worked: the Y-code | |
| column holds the exemptions a declarant assigned by hand, which is what the | |
| gold-set scoring is measured against. | |
| """ | |
| return ClientProfileModel( | |
| name="Goenee export line list", | |
| description="Jac. Goenee B.V. line list for a G-Star export, legacy .xls.", | |
| exportProfile=ExportProfileType.SOFTPAK, | |
| columnMapping={ | |
| InputField.CODE.value: "HS Code", | |
| InputField.DESCRIPTION.value: "Item Descr", | |
| InputField.GROSS_WEIGHT.value: "Total Item Weight", | |
| InputField.COUNTRY.value: "Item COO", | |
| InputField.PACKAGES.value: "Pieces", | |
| InputField.COLLI_TYPE.value: "Colli type", | |
| # The header is truncated in the file itself; it has no closing | |
| # bracket, and the mapping matches what is actually written. | |
| InputField.REMARKS.value: "Remarks (EAN", | |
| InputField.VALUE.value: "Total Value (GBP)", | |
| }, | |
| exportColumns=[ExportColumn(field=OutputField.YCODES, header="Y-code")], | |
| defaults=ProfileDefaults( | |
| direction=TradeDirection.EXPORT, destinationCountry="GB", currency="GBP" | |
| ), | |
| totals=TotalsConfig( | |
| sumFields=[ | |
| InputField.PACKAGES.value, | |
| InputField.GROSS_WEIGHT.value, | |
| InputField.VALUE.value, | |
| ], | |
| parcelKeyColumn="Tracking no", | |
| parcelLevelFields=[ | |
| InputField.PACKAGES.value, | |
| InputField.GROSS_WEIGHT.value, | |
| ], | |
| packagesFromParcelCount=True, | |
| ), | |
| ) | |
| def goenee_bescheiden_profile() -> ClientProfileModel: | |
| """Jac. Goenee B.V. packs, one line per declaration line, `CF-GOE-*-bescheiden.csv`. | |
| Goenee sends every pack in the same semicolon CSV whatever the goods: the | |
| promo-goods and G-Star exports to GB, the parcel imports from GB, the | |
| broadcast kit imported for an event, the used SPMTs shipped to Jakarta and | |
| Singapore. Direction, origin, destination and declaration date are read per | |
| row, because a week of packs mixes all of them. | |
| The code comes at four, six or eight digits, or not at all, and `hs_conflict` | |
| says the shipper's sources disagreed on it. The column is kept in the file | |
| as sent; a short code comes back with its candidates rather than an answer. | |
| """ | |
| return ClientProfileModel( | |
| name="Goenee bescheiden", | |
| description="Jac. Goenee B.V. declaration lines per pack, CSV, import NL and export.", | |
| exportProfile=ExportProfileType.SOFTPAK, | |
| columnMapping={ | |
| InputField.CODE.value: "goederencode", | |
| InputField.DESCRIPTION.value: "omschrijving", | |
| InputField.COUNTRY.value: "oorsprong", | |
| InputField.DESTINATION_COUNTRY.value: "bestemming", | |
| InputField.DIRECTION.value: "richting", | |
| InputField.DATE.value: "datum", | |
| InputField.REMARKS.value: "regel_id", | |
| }, | |
| exportColumns=[ | |
| ExportColumn(field=OutputField.RESOLVED_CODE, header="CargoFlow code"), | |
| ExportColumn(field=OutputField.CODE_DESCRIPTION, header="Omschrijving TARIC"), | |
| ExportColumn(field=OutputField.CANDIDATES, header="Keuze subdivisie"), | |
| ExportColumn(field=OutputField.DOCUMENTS_REQUIRED, header="Bescheiden"), | |
| ExportColumn(field=OutputField.YCODES, header="Y-codes"), | |
| *preference_export_columns(), | |
| ExportColumn(field=OutputField.WARNINGS, header="Signalen"), | |
| ExportColumn(field=OutputField.STATUS, header="Status"), | |
| ], | |
| defaults=ProfileDefaults( | |
| direction=TradeDirection.IMPORT, useTodayWhenDateMissing=True | |
| ), | |
| # The packs carry no quantity, weight or value, so there is nothing to add up. | |
| totals=TotalsConfig(enabled=False), | |
| ) | |
| def efg_henx_transit_profile() -> ClientProfileModel: | |
| """EFG Arabia to Henx Europe — the packs Goenee files under external transit. | |
| The goods are not released on arrival: they move under T1 to Henx and are | |
| declared there, so duty, VAT and excise are secured rather than paid. The | |
| prohibitions and restrictions still bind, which is what this profile is for. | |
| The codes in these packs are eight-digit CN. An import declaration is filed | |
| on ten, so most lines come back with their declarable candidates rather than | |
| an answer — that is the pack's gap, not the engine's, and it is left visible. | |
| """ | |
| return ClientProfileModel( | |
| name="EFG Arabia to Henx (T1)", | |
| description="EFG Arabia commercial invoice / packing list, external transit to Henx Europe BV.", | |
| exportProfile=ExportProfileType.SOFTPAK, | |
| # The pack opens with the invoice head — shipper, receiver, totals — and | |
| # the line table starts at row 20. The workbook's other sheet is a pivot | |
| # of the same lines and holds no declarable detail. | |
| sheetName="1. SUMMARY (EQ LIST FOR SHIPPIN", | |
| headerRow=20, | |
| columnMapping={ | |
| InputField.CODE.value: "HS CODE", | |
| InputField.COUNTRY.value: "(country)", | |
| InputField.DESCRIPTION.value: "Description", | |
| InputField.QUANTITY.value: "Quantity", | |
| InputField.GROSS_WEIGHT.value: "Case weight", | |
| InputField.VALUE.value: "Total Value (EUR)|Total Value", | |
| InputField.REMARKS.value: "Serial No.", | |
| }, | |
| exportColumns=[ExportColumn(field=OutputField.YCODES, header="Y-code")], | |
| defaults=ProfileDefaults( | |
| direction=TradeDirection.IMPORT, | |
| procedure=CustomsProcedure.EXTERNAL_TRANSIT_T1, | |
| destinationCountry="NL", | |
| useTodayWhenDateMissing=True, | |
| ), | |
| totals=TotalsConfig( | |
| sumFields=[ | |
| InputField.GROSS_WEIGHT.value, | |
| InputField.QUANTITY.value, | |
| InputField.VALUE.value, | |
| ] | |
| ), | |
| ) | |
| def gstar_ecom_profile() -> ClientProfileModel: | |
| return ClientProfileModel( | |
| name="G-Star ecom GB", | |
| description="Group eStore BV parcel manifest, NL to GB. One row per item.", | |
| exportProfile=ExportProfileType.SOFTPAK, | |
| columnMapping={ | |
| InputField.CODE.value: "HS Code", | |
| InputField.DESCRIPTION.value: "Item Descr", | |
| InputField.GROSS_WEIGHT.value: "Total Weight", | |
| InputField.COUNTRY.value: "Item COO", | |
| InputField.DESTINATION_COUNTRY.value: "Cust Country", | |
| InputField.PACKAGES.value: "Pieces", | |
| InputField.QUANTITY.value: "Item Qty", | |
| InputField.VALUE.value: "Item Value", | |
| InputField.REMARKS.value: "SKU", | |
| }, | |
| exportColumns=[ExportColumn(field=OutputField.YCODES, header="Y-code")], | |
| defaults=ProfileDefaults( | |
| direction=TradeDirection.EXPORT, destinationCountry="GB", currency="GBP" | |
| ), | |
| # One row per item, many rows per parcel. "Total Weight" and the piece | |
| # count belong to the parcel, so both are counted once per tracking | |
| # number. Checked against the Master Invoice for 04-09-2026: 167 | |
| # packages and GBP 11,376.42, which only reconcile this way. | |
| totals=TotalsConfig( | |
| sumFields=[ | |
| InputField.PACKAGES.value, | |
| InputField.GROSS_WEIGHT.value, | |
| InputField.VALUE.value, | |
| ], | |
| parcelKeyColumn="Tracking no", | |
| parcelLevelFields=[ | |
| InputField.PACKAGES.value, | |
| InputField.GROSS_WEIGHT.value, | |
| ], | |
| packagesFromParcelCount=True, | |
| ), | |
| ) | |
| def spare_cargo_line_list_profile() -> ClientProfileModel: | |
| """Spare Cargo B.V. — ship spares and offshore equipment, by air and sea. | |
| One sheet carries several packs, import and export mixed: the direction, | |
| origin, destination and declaration date are read per row. The shipper's | |
| HS code comes at six, eight or ten digits, or not at all. Spare Cargo | |
| declares in Scope, whose import specification has not been received, so the | |
| checked columns are appended to the file as sent. | |
| """ | |
| return ClientProfileModel( | |
| name="Spare Cargo line list", | |
| description="Spare Cargo B.V. declaration lines per pack, import NL and export.", | |
| exportProfile=ExportProfileType.GENERIC, | |
| sheetName="Regels", | |
| columnMapping={ | |
| InputField.CODE.value: "HS op factuur|HS code|HS", | |
| InputField.DESCRIPTION.value: "Omschrijving (factuur)|Omschrijving|Description", | |
| InputField.COUNTRY.value: "Herkomst|Origin", | |
| InputField.DESTINATION_COUNTRY.value: "Bestemming|Destination", | |
| InputField.DIRECTION.value: "Richting|Direction", | |
| InputField.DATE.value: "Aangiftedatum|Declaration date", | |
| InputField.QUANTITY.value: "Aantal|Qty", | |
| InputField.NET_WEIGHT.value: "Netto kg|Net kg", | |
| InputField.VALUE.value: "Waarde|Value", | |
| InputField.REMARKS.value: "Artikelnr|Item / part no.", | |
| }, | |
| exportColumns=[ | |
| ExportColumn(field=OutputField.RESOLVED_CODE, header="CargoFlow code"), | |
| ExportColumn(field=OutputField.CODE_DESCRIPTION, header="Omschrijving TARIC"), | |
| ExportColumn(field=OutputField.CANDIDATES, header="Keuze subdivisie"), | |
| ExportColumn(field=OutputField.DOCUMENTS_REQUIRED, header="Bescheiden"), | |
| ExportColumn(field=OutputField.DOCUMENTS_EXEMPTION, header="Y-codes"), | |
| ExportColumn(field=OutputField.WARNINGS, header="Signalen"), | |
| ExportColumn(field=OutputField.STATUS, header="Status"), | |
| ], | |
| defaults=ProfileDefaults( | |
| direction=TradeDirection.IMPORT, | |
| destinationCountry="NL", | |
| useTodayWhenDateMissing=True, | |
| ), | |
| totals=TotalsConfig( | |
| sumFields=[ | |
| InputField.QUANTITY.value, | |
| InputField.NET_WEIGHT.value, | |
| InputField.VALUE.value, | |
| ] | |
| ), | |
| ) | |